В Excel существуют неочевидная возможность вывести на лист список имен файлов из заданной папки. [1] Способ связан с использованием макрофункции (подробнее см. Функция Получить.Ячейку). Этот метод существенно проще написания макроса VBA. Начните с создания именованной формулы. Пройдите по меню Формулы –> Определенные имена –> Присвоить имя, и в окне Создание имени введите данные, как указано на рисунке:
Рис. 1. Диалоговое окно создания именованной формулы
Скачать заметку в формате Word или pdf, примеры в формате Excel (с макросами)
Обратите внимание: функция =ФАЙЛЫ(Лист1!$A$1) не относится к стандартным функциям рабочего листа. Это макрофункция, написанная в старом XLM-стиле и предназначенная для использования на специальном листе макросов. Она принимает один аргумент – путь к каталогу и описание файла (или файловый шаблон) и возвращает массив имен файлов, содержащихся в данном каталоге и удовлетворяющих описанию файла (шаблону). Обычные формулы рабочего листа не могут использовать подобные XLM- функции, а определенные имена — могут.
Определив именованную формулу, введите в ячейку А1 путь к каталогу и описание файла. Например, D:\Dropbox\!Сайт\7_Библиотека\*.*
Следующая формула отобразит первый найденный файл: =ИНДЕКС(Список;1). Если изменить второй аргумент на 2, то будет отображен второй найденный файл и т.д. Следующая формула, введенная в ячейку В1 (рис. 2), и скопированная вдоль столбца, позволит вывести все файлы: =ИНДЕКС(Список;СТРОКА()).
Рис. 2. Использование xlm-макрофункции в именованной формуле позволяет создать на листе список имен файлов
Функция СТРОКА, используемая здесь, генерирует ряд последовательных натуральных чисел: 1, 2, 3 и т.д. Эти числа используются в качестве второго аргумента функции ИНДЕКС. Когда все файлы будут выведены на лист, формула начнет возвращать ошибку: #ССЫЛКА! Если изменить каталог или описание файла в ячейке А1, то формулы обновляются и выводят имена новых файлов.
Книгу Excel нужно сохранить как файл с поддержкой макросов (в формате *.xlsm).
[1] По материалам книги Джон Уокенбах. Excel 2013. Трюки и советы. – СПб.: Питер, 2014. – С. 44, 45
В Excel 2016 функции ФАЙЛЫ() не получается выполнить
Игорь, проверил в Excel 2016. Всё работает. Пришлите файл в личку, посмотрю.
В 2016:
=ИНДЕКС(Список;1) — работает, а
=ИНДЕКС(Список;СТРОКА()) — нет ((
Разобрался! Извиняюсь. У меня список шел не от первой строки. Надо задать нумерацию с произвольного места…