Вопрос или проблема
У меня есть следующая проблема: я пытаюсь создать формулу для обращения к внешнему файлу Excel на основе даты. Когда я пытаюсь отформатировать дату в формате ddmmyy и использовать ее в этой формуле, я получаю ошибку #ref.
=IFERROR(COUNTIFS('R:\hal 6\nicarm\LijstenPerDagExcel\[230924.xlsx]Data'!$V$2:$V$999;
">=01/01/1970 22:00:00";'R:\hal 6\nicarm\LijstenPerDagExcel\[230924.xlsx]Data'!$A$2:$A$999;"LA")
+COUNTIFS('R:\hal 6\nicarm\LijstenPerDagExcel\[240924.xlsx]Data'!$V$2:$V$999;
"<=01/01/1970 06:00:00";'R:\hal 6\nicarm\LijstenPerDagExcel\[240924.xlsx]Data'!$A$2:$A$999;"LA");"")
Эта формула работает, но когда я пытаюсь использовать INDIRECT, чтобы сделать путь переменной, я получаю ошибку.
Ответ или решение
Для решения вашей проблемы с использованием функции INDIRECT, чтобы создать динамическую ссылку на внешние рабочие таблицы в зависимости от даты, необходимо учесть несколько важных моментов.
Функция INDIRECT не работает с закрытыми файлами Excel, поэтому, если файл, на который вы пытаетесь сослаться, закрыт, это приведет к ошибке #REF. Убедитесь, что источники данных открыты, когда вы используете INDIRECT.
Если файлы открыты, вы можете следовать следующему подходу:
-
Форматирование даты: Убедитесь, что ваша дата формируется в нужном формате. Для этого вы можете использовать функцию
TEXTдля форматирования даты. -
Динамическая ссылка с использованием
INDIRECT: Создайте строку с полным путем к вашему файлу, используя функцию&для объединения текста и переменных. Например:
=IFERROR(COUNTIFS(INDIRECT("'R:\hal 6\nicarm\LijstenPerDagExcel\[" & TEXT(A1, "ddmmyy") & ".xlsx]Data'!$V$2:$V$999"), ">=01/01/1970 22:00:00", INDIRECT("'R:\hal 6\nicarm\LijstenPerDagExcel\[" & TEXT(A1, "ddmmyy") & ".xlsx]Data'!$A$2:$A$999"), "LA")
+ COUNTIFS(INDIRECT("'R:\hal 6\nicarm\LijstenPerDagExcel\[" & TEXT(A2, "ddmmyy") & ".xlsx]Data'!$V$2:$V$999"), "<=01/01/1970 06:00:00", INDIRECT("'R:\hal 6\nicarm\LijstenPerDagExcel\[" & TEXT(A2, "ddmmyy") & ".xlsx]Data'!$A$2:$A$999"), "LA"), "")В этом примере:
A1иA2представляют даты, которые вы хотите использовать.TEXT(A1, "ddmmyy")изменяет формат даты на требуемый для имени файла.
Убедитесь, что вы правильно указываете диапазоны и условия для функции COUNTIFS.
Примечание:
Если ваши файлы периодически закрываются, можно рассмотреть альтернативные решения, чтобы избежать ошибок #REF. Например, использование Power Query для загрузки данных из внешних файлов в Excel, после чего вы сможете анализировать данные без постоянной привязки к открытым файлам.
С помощью этого подхода вы сможете динамически обновлять ссылки на внешние таблицы в зависимости от даты. Если у вас возникнут дополнительные вопросы или потребуются уточнения, пожалуйста, дайте знать.