Общая формула
=GET.WORKBOOK(1)&T(NOW())
Резюме
Чтобы перечислить листы в книге Excel, вы можете использовать двухэтапный подход: (1) определить именованный диапазон, называемый «листами», с помощью старой макрос-команды и (2) использовать функцию ИНДЕКС для получения имен листов с использованием именованного диапазона. В показанном примере формула B5 имеет следующий вид:
=INDEX(MID(sheetnames,FIND(")",sheetnames)+1,255),ROWS($B$5:B5))
Примечание: я встретил эту формулу на доске сообщений MrExcel в сообщении Т. Валко.
Объяснение
Именованный диапазон "имена листов" создается с помощью этого кода:
=GET.WORKBOOK(1)&T(NOW())
GET.WORKBOOK - это макрос-команда, которая извлекает массив имен листов в текущей книге. Результирующий массив выглядит так:
("(workbook.xlsm)Sheet1","(workbook.xlsm)Sheet2","(workbook.xlsm)Sheet3","(workbook.xlsm)Sheet4","(workbook.xlsm)Sheet5")
К результату добавляется загадочное выражение:
&T(NOW())
Целью этого кода является принудительный пересчет, чтобы учесть изменения в именах листов. Поскольку NOW - непостоянная функция, она пересчитывается при каждом изменении рабочего листа. Функция NOW возвращает числовое значение, представляющее дату и время. Функция T возвращает пустую строку ("") для числовых значений, поэтому объединение не влияет на значения.
Вернувшись на лист, ячейка B6 содержит скопированную формулу:
=INDEX(MID(sheetnames,FIND(")",sheetnames)+1,255),ROWS($B$5:B5))
Работая изнутри, функция MID используется для удаления имен рабочих листов. Результирующий массив выглядит так:
("Sheet1","Sheet2","Sheet3","Sheet4","Sheet5")
Это входит в функцию ИНДЕКС как «массив». Функция СТРОКА использует расширяющиеся диапазоны для создания увеличивающегося номера строки. В каждой новой строке ИНДЕКС возвращает следующее значение массива. Если для вывода больше нет имен листов, формула вернет ошибку #REF.
Примечание. Поскольку в этой формуле используется макрос-команда, вам необходимо сохранить ее как книгу с поддержкой макросов, если вы хотите, чтобы формула продолжала обновлять имена листов после закрытия и повторного открытия файла. Если вы сохраните как обычный рабочий лист, код имени листа будет удален.
