Excel имя файла

Microsoft Excel

трюки • приёмы • решения

Как получить список имен файлов, содержащихся в папке, а затем отобразить этот список на листе Excel

В этой статье будет рассказано, как получить список имен файлов, содержащихся в каталоге, а затем отобразить этот список на листе. Данный способ связан с использованием макрофункции Excel 4 XLM в именованной формуле. Такой метод удобен, поскольку позволяет относительно легко выстроить на листе список имен файлов — обычно для этого требуются сложные макросы VBA.

Откройте новую книгу, а затем выполните следующие действия для создания именованной формулы.

1. Выполните команду Формулы ► Присвоить имя, чтобы открыть диалоговое окно Создание имени.
2. Впишите в поле Имя название Список.
3. Введите следующую формулу в поле Диапазон (рис. 8.1): =ФАЙЛЫ(Лист1!$A$1) .
4. Нажмите ОК, чтобы закрыть окно Создание имени.

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

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

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

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

Обратите внимание: в ячейке А17 (и ниже) происходит ошибка. Дело в том, что в каталоге содержится всего 19 файлов и формула пытается отобразить имена несуществующих. Если изменить каталог или описание файла в ячейке А1, то формулы обновляются и выводят имена новых файлов.

Если вы пользуетесь такими приемами, то книгу нужно сохранить как файл с поддержкой макросов (в формате *.xlsm или *.xls).

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

В 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