Косвенная ссылка на ячейку на другом листе (Microsoft Excel)
У Майкла есть рабочий лист с названиями месяцев (январь, февраль и т. Д.)
в столбце A. В столбце B он хочет извлечь значение из ячейки B11 любого листа, указанного в столбце A. Таким образом, если столбец A содержит месяц «март», в ячейке справа от марта (в столбце B )
он хочет подтянуть стоимость в марте! B11. Майкл считает, что функция INDIRECT должна помочь в этом, но он не может заставить ее работать.
Хорошая новость в том, что Майкл прав — для этого можно использовать функцию КОСВЕННО. Базовое использование функции выглядит так:
=INDIRECT(A1&"!B11")
Вы можете создать более «надежную» версию формулы, включив ее в функцию, которая проверяет наличие ошибок. В случае ошибки в ячейке отображается фраза «Нет данных»:
=IFERROR(INDIRECT(A1&"!B11"),"No Data")
Эти подходы напрямую используют все, что находится в ячейке A1, что работает при условии, что значением в A1 является текст и одно слово. Если в A1 может быть второе слово (например, «Июльское производство»), вам нужно немного изменить формулу, чтобы она включала апострофы вокруг всего, что находится в ячейке A11:
=IFERROR(INDIRECT("'"&A1&"'!B11"),"No Data")
Поскольку апострофы используются для заключения имени рабочего листа, в ячейке A1 не может быть текста, содержащего апострофы. Таким образом, «Производство за июль» в ячейке A1 будет работать нормально (при условии, что у вас есть рабочий лист с именем «Производство за июль»), но «Производство за июль» не будет работать из-за апострофа.
Кроме того, если все, что находится в ячейке A1, может иметь начальные или конечные пробелы, вам нужно избавиться от этих пробелов. Самый простой способ компенсировать это — использовать функцию TRIM:
=IFERROR(INDIRECT("'"&TRIM(A1)&"'!B11"),"No Data")
Все варианты, представленные до сих пор, работают нормально, если значение в A1 является реальной строкой. Они не будут работать, если значение в A1 представляет собой фактическую дату, отформатированную так, чтобы выглядеть как название месяца. Даты хранятся внутри как числа, и при использовании одной из рассмотренных выше формул будет предпринята попытка добавить серийный номер даты к ссылке на ячейку, что приведет к ошибке. Вместо этого вам нужно использовать функцию ТЕКСТ для преобразования даты в A1 в название месяца:
=IFERROR(INDIRECT(TEXT(A1,"mmmm")&"!B11"),"No Data")
Если вы ожидаете, что другие люди будут вводить названия месяцев в ячейку A1, вам следует сделать эту запись как можно более надежной. Лучший способ сделать это — использовать проверку данных, чтобы ограничить ввод данных в ячейку A1. (Как вы используете проверку данных, было описано в других выпусках ExcelTips.)
ExcelTips — ваш источник экономичного обучения Microsoft Excel.
Этот совет (12701) применим к Microsoft Excel 2007, 2010, 2013, 2016, 2019 и Excel в Office 365.