Вопрос или проблема
В моем листе есть несколько ячеек, содержащих разные ссылки на ячейки с одинаковым индексом строки.
Теперь я хочу ссылаться на эти несколько ячеек, но добавить фиксированный смещенный по строкам:
Таким образом, желаемый результат в этих ячейках – это информация, которая находится в ячейках “AG11”, “AH11”,….
Я мог бы легко вручную скопировать диапазон и изменить номер строки вручную, но мне было интересно, есть ли функция/команда, которая это делает.
Можно ли сделать что-то подобное с =AG&"6+5" или подобным синтаксисом?
Спасибо.
Прямой ответ на:
Можно ли сделать что-то подобное с =AG&”6+5″ или подобным синтаксисом?
будет:
=Indirect("ag"&6+5)
это не обновится, если вы удалите или добавите строки или столбцы
A столбец показывает формулу в B столбце
вы также можете использовать:
=Indirect(Address(Row(AG6)+5,Col(AG6)))
или
=INDIRECT(ADDRESS(6+5,33))
это последнее не обновится, если вы удалите или добавите строки или столбцы
эти примеры выше используют Google Sheets, но должны применяться и к Excel
Хотя если вы хотите одноразовое изменение большого количества ячеек, это было бы возможно через макрос или пользовательскую внешнюю программу, которая прочитает книгу и изменит соответствующие ссылки на ячейки, обе должны вернуть более красивые решения, если ваша конечная цель – просто иметь =AG11.
В качестве примера того, как это будет работать, в OpenOffice:
Sub Main
Dim sheet As Object
sheet = ThisComponent.CurrentController.ActiveSheet
For i = 1 To 5
Dim formula As String
Dim address As New com.sun.star.table.CellAddress
Dim cellName As String
formula = sheet.getCellRangeByName("B" & i).Formula
address = sheet.getCellRangeByName(Right(formula, Len(formula)-1)).getCellAddress()
cellName = Chr(address.Column + 65) & (address.Row+2)
sheet.getCellRangeByName("B" & i).Formula = "=" & cellName
Next i
End Sub
Изменит:
A столбец показывает формулу в B столбце
на:
Я не могу поделиться примером макроса в Excel, потому что я не владею им, я мог бы сделать пример внешней программы, но это может выйти за рамки этого вопроса.
Ответ или решение
Вопрос, который вы подняли, касается того, как изменить индекс строки в ссылках на ячейки в Excel с применением фиксированного смещения. Давайте рассмотрим несколько способов, которые могут помочь вам в этом вопросе.
1. Использование функции INDIRECT
Функция INDIRECT позволяет динамически формировать ссылки на ячейки в зависимости от текстового ввода, что делает её идеальным инструментом для задачи, которую вы описали:
=INDIRECT("AG" & (6 + 5))В этом примере вы создаете текстовую ссылку на ячейку AG11 (так как 6 + 5 = 11). Однако следует отметить, что эта формула не обновится автоматически, если вы удалите или добавите строки.
2. Использование сочетания функций ADDRESS, ROW и COLUMN
Еще один способ изменить индекс строки в ссылке — использовать комбинацию функций ADDRESS, ROW и COLUMN. Вот как это можно сделать:
=INDIRECT(ADDRESS(ROW(AG6) + 5, COLUMN(AG6)))Эта формула сначала определяет номер строки исходной ячейки (AG6) и увеличивает его на 5, после чего формирует адрес новой ячейки и обращается к нему через INDIRECT.
3. Прямое указание номера строки
Если вы хотите дополнительно указать конкретное смещение, вы можете использовать следующую формулу:
=INDIRECT(ADDRESS(11, COLUMN(AG6)))Здесь вам не нужно вычислять номер строки, если вы знаете его заранее.
4. Использование макросов для массового изменения
Если вам нужно провести массовые изменения и обновить несколько формул, вы можете воспользоваться макросами. Например, в OpenOffice можно написать макрос, который будет изменять ссылки в ваших ячейках. Пример кода для OpenOffice:
Sub Main
Dim sheet As Object
sheet = ThisComponent.CurrentController.ActiveSheet
For i = 1 To 5
Dim formula As String
formula = sheet.getCellRangeByName("B" & i).Formula
Dim cellName As String
Dim address As New com.sun.star.table.CellAddress
address = sheet.getCellRangeByName(Right(formula, Len(formula)-1)).getCellAddress()
cellName = Chr(address.Column + 65) & (address.Row + 5) ' Увеличиваем индекс строки на 5
sheet.getCellRangeByName("B" & i).Formula = "=" & cellName
Next i
End SubЭтот макрос обновит формулы в столбце B в зависимости от значений в столбце A.
Заключение
В вашем случае функция INDIRECT является наиболее простой в использовании и наиболее гибкой. Если же вы ведете более обширную работу с формулами, макросы могут значительно упростить вашу задачу. Применяйте подходящий метод в зависимости от вашей конкретной ситуации, и вы сможете эффективно управлять ссылками на ячейки с изменением индексов строк в ваших расчетах в Excel.