Вопрос или проблема
У меня есть продукт, который я разрабатываю, и он требует от пользователя создания новых листов в рабочей книге для отдельных людей. Поскольку я не знаю имен, я пронумеровал будущие листы в ячейке, которая будет ссылаться на будущие листы, названные по номерам (1-50). С помощью формулы “=IF(ISERROR(INDIRECT(“ я могу ссылаться на конкретные ячейки на этом будущем листе, когда он будет создан, что отлично подходит для многих пунктов, которые мне нужны, но самая важная формула, которую мне нужно выполнить на будущем листе “1”, названном, например, в ячейке A1, это “=INDEX(A1!$B$11:B380,MATCH(H$4,A1!$A:$A380,0),0)”. Эта формула выдает ошибку “#VALUE!”, из-за ссылки на имя листа A1. Когда это жестко закодировано как ‘1’!, это работает для уже существующих листов, но не для будущих листов, которые будут созданы, что мне и нужно. Я не уверен, как интегрировать нужную формулу в формулу “=IF(ISERROR(INDIRECT(“, чтобы получить желаемый результат. Пожалуйста, помогите!
Вы можете использовать одну из следующих формул:
=IFERROR(INDEX(INDIRECT($A$1&"!B11:B380"),MATCH(H$4,INDIRECT($A$1&"!A11:A380"),0)),0)
=IFERROR(VLOOKUP(H$4,INDIRECT($A$1&"!A11:B380"),2,FALSE),0)
Адреса должны быть записаны внимательно. Они должны совпадать друг с другом.
Ответ или решение
Для выполнения формулы на отдельном листе, ссылаясь на другую ячейку в текущем листе в Microsoft Excel, вы можете использовать функцию INDIRECT, которая позволяет динамически ссылаться на диапазоны и ячейки по строковым значениям. В вашем случае, поскольку листы создаются с именами на основе чисел, вы можете сослаться на эти листы, используя номер, указанный в ячейке (например, A1), а затем формировать нужный адрес ячейки.
Вот как это можно сделать, чтобы избежать ошибки #VALUE!, когда лист еще не существует, и обеспечить выполнение нужной формулы:
Пример формулы:
Для использования INDEX и MATCH с учетом динамического имени листа, вам следует использовать следующую формулу:
=IFERROR(INDEX(INDIRECT("'" & $A$1 & "'!B11:B380"), MATCH(H$4, INDIRECT("'" & $A$1 & "'!A11:A380"), 0)), 0)Объяснение:
-
INDIRECT: Эта функция преобразует строку текста в ссылку. В данном случае,
INDIRECT("'" & $A$1 & "'!B11:B380")создает ссылку на диапазон B11:B380 на листе, имя которого указано в A1. Обратите внимание, что я использую одинарные кавычки, чтобы обработать возможные пробелы или специальные символы в именах листов. -
MATCH: Используется для поиска значения из H4 в диапазоне, указанном через
INDIRECT. -
IFERROR: Эта функция позволяет обработать ошибку (например, если листа еще не существует) и вернуть 0 или любое другое значение, вместо стандартного сообщения об ошибке.
Альтернативный вариант:
Если вам необходимо использовать VLOOKUP, то формула будет выглядеть так:
=IFERROR(VLOOKUP(H$4, INDIRECT("'" & $A$1 & "'!A11:B380"), 2, FALSE), 0)Указания:
- Убедитесь, что адреса диапазонов правильно написаны и соответствуют друг другу (т.е. диапазоны A11:A380 и B11:B380 должны соответствовать одной и той же строке).
- Проверьте, что ячейка A1 действительно содержит ожидаемое название листа.
Заключение:
Используя эти методы, вы сможете динамически ссылаться на данные на разных листах, даже если эти листы еще не созданы. Это обеспечит гибкость в вашей таблице и поможет избежать ошибок. Если у вас возникнут дополнительные вопросы или потребуется дальнейшая помощь, не стесняйтесь задавать их.