Вопрос или проблема
Я хотел бы использовать результат функции CONCAT() в качестве заголовка для массива внутри XLOOKUP(). Формула XLOOKUP(), которую я использую и которая работает, выглядит так:
=XLOOKUP(MetricDesired,’2023-12-13_000_Report.txt’!$C:$C,’2023-12-13_000_Report.txt’!D:D)
Однако я хотел бы, чтобы массив поиска и возврата брался из нескольких файлов, индексируя “000” в имени файла до “001” и т.д. У меня есть эти имена файлов в отдельном столбце (столбец B), надеясь использовать текст в этом столбце как часть массивов, но я попробовал использовать следующую формулу, и она выдает “#N/A”:
=XLOOKUP(MetricDesired,CONCAT(“‘”,$B3,”‘!$C:$C”),CONCAT(“‘”,$B3,”‘!D:D”))
Я пробовал CONCAT() как способ автоматизировать имена файлов в извлечении данных, но подозреваю, что результат этой функции не подходит для имени массива.
Этот пример демонстрирует решение для вас:
=XLOOKUP(D1:D2&"a";A1:A3;B1:B3)
Ответ или решение
Чтобы использовать вывод функции CONCAT() как массив в Excel, необходимо понять, что функция CONCAT() не приводит к созданию динамических массивов, подходящих для использования в функциях, таких как XLOOKUP(). Вместо этого необходимо использовать функцию INDIRECT(). Это поможет формировать ссылки на диапазоны из строковых данных.
В вашем случае, когда у вас есть массив файлов в столбце B и вы хотите динамически ссылаться на столбцы C и D в этих файлах, вы можете выполнить следующие шаги:
-
Убедитесь, что имена файлов в столбце B правильно указаны. Например, если в ячейках B3, B4 и B5 находятся имена файлов
'2023-12-13_000_Report.txt','2023-12-13_001_Report.txt'и т.д., вы сможете использовать эти имена для динамического построения ссылок. -
Используйте функцию INDIRECT(), чтобы создать динамические ссылки на те диапазоны, к которым вы хотите обратиться. Пример формулы может выглядеть следующим образом:
=XLOOKUP(MetricDesired, INDIRECT("'" & $B3 & "'!$C:$C"), INDIRECT("'" & $B3 & "'!D:D"))
В данной формуле функция INDIRECT() преобразует строку, содержащую ссылку на диапазон в виде текстовой строки, в реальную ссылку на диапазон, который Excel сможет использовать.
- Убедитесь, что значение MetricDesired правильно указано и соответствует искомым данным в столбце C соответствующего файла.
Таким образом, вы сможете динамически изменять ссылки в вашем выражении XLOOKUP() в зависимости от файла, указанного в столбце B, решив вашу задачу корректно.
Важно помнить, что файлы должны быть открыты и доступны, чтобы функция INDIRECT() работала должным образом, так как она ссылается на данные в других файлах.
Если у вас есть дополнительные вопросы или потребуется помощь с конкретными примерами, не стесняйтесь спрашивать!