Приращение ВПР в Excel
Я создаю файл Excel, который будет извлекать ежедневный отчет из папки на другом диске каждый день. Мне было интересно, есть ли способ легко увеличить имя файла в формуле на 1?
В настоящее время я использую следующую формулу:
=VLOOKUP("Oil Production(Sm³)",'G:\DPNA\Non_Op_Assets\Hib\Pe\Production\Reports\Daily Report\2019\01 Jan19\[190113 Daily Report.xls]Daily Report'!$A$1:$P$500,3,FALSE)*1
Мне было интересно, можно ли легко заставить 190113 увеличивать на единицу для следующих 365 ячеек.
Ответы 2
Не легко. Мне кажется, что вам нужно не только увеличить 190113, но и перейти на 190201 для 1 февраля, а также в пути к файлу указаны год и месяц.
Однако это можно построить с помощью формул. Вы можете создавать формулы, которые автоматически генерируют бит пути и имени файла.
= "Daily Report\"&YEAR(A2)&"\"&TEXT(A2,"dd MMMyy")&"\["&TEXT(A2,"yymmdd")&" Daily Report.xls]Daily Report'!$A$1:$P$500,3,FALSE)*1"
Следующий вопрос - как это использовать. Вы можете подключить это к функции Indirect, но если Indirect ссылается на внешний файл, этот файл должен быть открыт для работы Indirect. Что побеждает цель.
Итак, другой вариант состоит в том, что вы создаете полную формулу в виде текстовой строки,
= "=VLOOKUP(""Oil Production(Sm³)"",'G:\DPNA\Non_Op_Assets\Hib\Pe\Production\Daily Report\"&YEAR(A2)&"\"&TEXT(A2,"dd MMMyy")&"\["&TEXT(A2,"yymmdd")&" Daily Report.xls]Daily Report'!$A$1:$P$500,3,FALSE)*1"
затем скопируйте и вставьте формулу как значения, затем отредактируйте каждую вставленную ячейку и подтвердите формулу, которую она теперь содержит.
Обновлено: VBA - это способ перейти к массовому редактированию, но вы не сможете записать это, так как вам понадобится цикл.
Спасибо, это немного облегчает жизнь. Есть ли способ записать макрос, который может редактировать каждую вставленную ячейку?
Файлы будут открыты? Если это так, вы можете использовать indirect, я просто собрал для вас базовый пример:
=INDIRECT("'C:\Reporting Archive\[Daily Sales and Orders Report - 2018121" & ROW(A1)+5 & ".xlsx]Summary - Volume'!$C$41")
Я могу перетащить это вниз, и 2018121 в имени файла будет начинаться с 6, затем увеличиваться до 7, а затем до 8.
Если у вас нет открытых файлов, вам нужно будет использовать VBA. При чтении из закрытой книги, не открывая ее, я всегда использовал метод ExecuteExcel4Macro следующим образом:
strPath = "C:\Test\"
strFile = "Book1.xls"
strSheet = "Sheet1"
strRng = Range("A1")
strRef = "'" & strPath & "[" & strFile & "]" & strSheet & "'!" & strRng
Result = ExecuteExcel4Macro(strRef)
Возможно ли это сделать, если номер находится в каталоге поиска файлов?