Список файлов на листе Excel

Рубрика: 7. Полезняшки Excel

В Excel существуют неочевидная возможность вывести на лист список имен файлов из заданной папки. [1] Способ связан с использованием макрофункции (подробнее см. Функция Получить.Ячейку). Этот метод существенно проще написания макроса VBA. Начните с создания именованной формулы. Пройдите по меню Формулы –> Определенные имена –> Присвоить имя, и в окне Создание имени введите данные, как указано на рисунке:

%d1%80%d0%b8%d1%81-1-%d0%b4%d0%b8%d0%b0%d0%bb%d0%be%d0%b3%d0%be%d0%b2%d0%be%d0%b5-%d0%be%d0%ba%d0%bd%d0%be-%d1%81%d0%be%d0%b7%d0%b4%d0%b0%d0%bd%d0%b8%d1%8f-%d0%b8%d0%bc%d0%b5%d0%bd%d0%be%d0%b2%d0%b0

Рис. 1. Диалоговое окно создания именованной формулы

Скачать заметку в формате Word или pdf, примеры в формате Excel (с макросами)

Обратите внимание: функция =ФАЙЛЫ(Лист1!$A$1) не относится к стандартным функциям рабочего листа. Это макрофункция, написанная в старом XLM-стиле и предназначенная для использования на специальном листе макросов. Она принимает один аргумент – путь к каталогу и описание файла (или файловый шаблон) и возвращает массив имен файлов, содержащихся в данном каталоге и удовлетворяющих описанию файла (шаблону). Обычные формулы рабочего листа не могут использовать подобные XLM- функции, а определенные имена — могут.

Определив именованную формулу, введите в ячейку А1 путь к каталогу и описание файла. Например, D:\Dropbox\!Сайт\7_Библиотека\*.*

Следующая формула отобразит первый найденный файл: =ИНДЕКС(Список;1). Если изменить второй аргумент на 2, то будет отображен второй найденный файл и т.д. Следующая формула, введенная в ячейку В1 (рис. 2), и скопированная вдоль столбца, позволит вывести все файлы: =ИНДЕКС(Список;СТРОКА()).

%d1%80%d0%b8%d1%81-2-%d0%b8%d1%81%d0%bf%d0%be%d0%bb%d1%8c%d0%b7%d0%be%d0%b2%d0%b0%d0%bd%d0%b8%d0%b5-xlm-%d0%bc%d0%b0%d0%ba%d1%80%d0%be%d1%84%d1%83%d0%bd%d0%ba%d1%86%d0%b8%d0%b8-%d0%b2-%d0%b8%d0%bc

Рис. 2. Использование xlm-макрофункции в именованной формуле позволяет создать на листе список имен файлов

Функция СТРОКА, используемая здесь, генерирует ряд последовательных натуральных чисел: 1, 2, 3 и т.д. Эти числа используются в качестве второго аргумента функции ИНДЕКС. Когда все файлы будут выведены на лист, формула начнет возвращать ошибку: #ССЫЛКА! Если изменить каталог или описание файла в ячейке А1, то формулы обновляются и выводят имена новых файлов.

Книгу Excel нужно сохранить как файл с поддержкой макросов (в формате *.xlsm).

[1] По материалам книги Джон Уокенбах. Excel 2013. Трюки и советы. – СПб.: Питер, 2014. – С. 44, 45

Комментарии: 4 комментария

В Excel 2016 функции ФАЙЛЫ() не получается выполнить

Игорь, проверил в Excel 2016. Всё работает. Пришлите файл в личку, посмотрю.

В 2016:
=ИНДЕКС(Список;1) — работает, а
=ИНДЕКС(Список;СТРОКА()) — нет ((

Разобрался! Извиняюсь. У меня список шел не от первой строки. Надо задать нумерацию с произвольного места…


Прокомментировать