Поиск в Excel в нескольких файлах

Обновлено: 21.11.2024

Примечание. При использовании готовой книги вам нужно будет изменить ссылку на папку в запросе на папку, в которой вы сохраняете образцы файлов.

В редакторе запросов выберите Вид > Расширенный редактор и измените путь к папке.

Если вы когда-либо сталкивались с ситуацией, когда у вас есть несколько файлов данных, в каждом из которых данные распределены по нескольким листам, вам следует продолжить чтение.

В этом посте мы рассмотрим, как использовать Power Query из папки для импорта нескольких файлов с несколькими листами в каждом файле и объединения данных в одну таблицу.

В этом примере в папке есть ряд файлов продаж.

Каждый файл содержит данные о продажах для определенной страны, а названия файлов соответствуют стране, из которой получены данные о продажах (например, Ирландия.xlsx, Англия.xlsx, Люксембург.xlsx и Канада.xlsx).

Каждый файл имеет несколько листов с разными данными в одном формате. Каждый лист содержит данные о продажах для данного продавца из страны и назван по имени продавца.

Как вы понимаете, сбор данных вручную может занять очень много времени по мере роста количества файлов и листов. Вот где может проявить себя Power Query.

Шаг 1. Создайте запрос из папки

Создайте запрос из папки.

Проверьте данные предварительного просмотра, чтобы убедиться, что они находятся в правильной папке и файлах.

  1. Появится предварительный просмотр данных импорта. Убедитесь, что это правильные файлы и папки.
  2. Нажмите кнопку "Изменить".

Шаг 2. Удалите ненужные столбцы данных

Запрос "Из папки" будет включать множество данных, таких как тип расширения файла, дата изменения, расположение файла и т. д.

Они не нужны для объединения данных. Вы можете удалить их, чтобы избежать беспорядка.

  1. Выберите два ненужных столбца («Содержание» и «Имя»). Удерживая нажатой клавишу Ctrl, щелкните левой кнопкой мыши заголовки столбцов, чтобы выделить их.
  2. Перейдите на вкладку "Главная".
  3. Нажмите кнопку "Удалить столбцы".
  4. Выберите Удалить другие столбцы в раскрывающемся меню.

Шаг 3. Разделите столбец имени файла

Чтобы включить страну в данные, вам потребуется проанализировать текст в имени файла.

Поскольку соглашение об именах файлов довольно простое (название страны.xlsx), вы можете использовать функцию разделения столбца, используя точку в качестве разделителя.

  1. Выделите столбец имени файла.
  2. Перейдите на вкладку "Преобразование".
  3. Нажмите кнопку "Разделить столбец".
  4. Выберите «По разделителю» в раскрывающемся меню.
  5. Выберите «Пользовательский».
  6. Введите точку для разделителя.
  7. Выберите самый правый разделитель. Это приведет только к разделению текста имени файла с использованием самой правой найденной точки (т. е. непосредственно перед расширением файла xlsx).
  8. Нажмите кнопку ОК.

Вы можете удалить результирующий столбец, содержащий часть расширения имени разделенного файла.

  1. Выберите столбец.
  2. Перейдите на вкладку "Главная".
  3. Нажмите кнопку "Удалить столбцы".
  4. Выберите Удалить столбцы в раскрывающемся меню.

Шаг 4. Добавьте столбец для содержимого файла

Теперь вам нужно добавить столбец, чтобы ввести содержимое в запрос.

Разверните новый столбец, чтобы отобразить все элементы Контента.

Вам также необходимо развернуть результирующий столбец данных, чтобы отобразить все его элементы.

  1. Нажмите маленькую двойную стрелку справа от заголовка столбца.
  2. Выберите "Развернуть".
  3. Установите флажок Использовать исходное имя столбца в качестве префикса.
  4. Нажмите кнопку ОК.

Теперь у вас есть все необходимые столбцы плюс несколько дополнительных.

Удалите все ненужные столбцы и при необходимости измените порядок столбцов, перетащив их.

Power Query угадывает тип данных каждого столбца, но вам может потребоваться исправить их.

Этот метод позволяет легко импортировать все данные из нескольких файлов и листов в одну таблицу.

Вы также могли добавить столбец данных о стране на основе имени файла и столбец данных о продавце на основе имени листа.

Если вы добавляете файлы в папку или обновляете данные в файле, вы можете легко обновить агрегированные данные, перейдя на вкладку «Данные» и нажав кнопку «Обновить все». Это приведет к повторному запуску запроса для получения последних данных.

Вау, Power Query может быть очень мощным!

Об авторе

Джон МакДугалл

Джон — Microsoft MVP, внештатный консультант и тренер, специализирующийся на Excel, Power BI, Power Automate, Power Apps и SharePoint. Другие интересные статьи Джона можно найти в его блоге или на канале YouTube.

Я использую Microsoft Office 2007. Как выполнить поиск в нескольких файлах Excel?

6 ответов 6

Открыв папку с файлами, нажмите Ctrl+f, чтобы использовать окно поиска в папке. Затем найдите

для поиска этой строки в файлах в текущем каталоге.

Похоже, что при поиске по текстовому содержимому только файлы xlsx содержат двоичные данные. Мне это не помогло.

Правда? Когда я ищу открытый текст с помощью этого метода, я получаю несколько возвращаемых файлов .xlsx, и все они действительно содержат строку поиска, которую я искал, хотя они действительно содержат двоичные данные.

Для этого папка должна быть настроена. Щелкните правой кнопкой мыши, чтобы увидеть свойства папки. Щелкните Дополнительно. Установите флажок «Разрешить индексировать содержимое файлов».

У меня не работает в Excel 2016. Он возвращает много файлов xlsx, но ни один из них не содержит ожидаемую строку

Я создал тестовый пример фиктивных файлов с точной строкой, которую искал. Я создал файлы xlsx, docx и txt со строкой и без нее. Те, у кого есть строка, которую я отметил в имени файла, и те, у кого ее нет. Используя поиск контента, он правильно разделил две группы.

Поискав в Интернете, я обнаружил, что не существует универсального решения проблемы поиска нескольких электронных таблиц Excel в Интернете, поэтому я решил написать свою собственную таблицу Excel, которая выполняет поиск в других таблицах Excel, которую можно найти здесь.

Обратите внимание, что это было протестировано только в Excel 2010, но должно работать в 2007 и более ранних версиях. Если нет, не стесняйтесь изменять код так, как считаете нужным.

В электронной таблице используется настраиваемая пользовательская форма и код VBA (который вы можете использовать для любых целей). Естественно, для этого должны быть включены макросы и т. д., и форма появляется при открытии электронной таблицы (вы можете получить доступ к коду, нажав Alt + F11, перейдя в пользовательскую форму и дважды щелкнув кнопку «Начать поиск» в окно дизайна).

Полное описание функций и возможностей можно найти в файле readme Github, но в основном он позволяет вам указать два условия текстового поиска для поиска в указанном каталоге (к которому вы можете перейти), он может выполнять рекурсивный поиск в указанном каталоге, и открывать электронные таблицы, защищенные паролем (при условии, что вы вводите пароль).

Он будет искать отдельные листы в каждой книге на уровне ячеек. В зависимости от размера книги сканирование каждой книги может занять около секунды.

Он отображает все результаты поиска в боковом окне, включая все электронные таблицы, которые ему не удалось открыть. Эти результаты можно сохранить в текстовом файле для дальнейшего использования.

Пользовательская форма должна быть относительно понятной, однако в файле readme на Github очень подробно описано, как ее использовать. Опять же, бесплатное использование, исходный код с открытым исходным кодом.

У Амита есть папка с сотнями книг Excel. Ему нужно просмотреть все рабочие книги в поисках определенного текста, и он задается вопросом, есть ли способ выполнить поиск по всем рабочим книгам и определить имена рабочих книг, содержащих нужный текст, а также ячейки в рабочих книгах, содержащих этот текст.

Определить, какие рабочие книги содержат нужный текст, относительно легко. Все, что вам нужно сделать, это использовать возможности поиска Windows для поиска файлов в одной папке, которые содержат нужный текст. Хотя он не сообщит вам расположение ячеек, он уменьшит список файлов.

Конечно, вы можете использовать макрос для поиска за вас. (Всегда полезно использовать макрос для выполнения долгой и утомительной работы, которую в противном случае можно было бы выполнить вручную.) Далее будет выполнен пошаговый просмотр всех книг в папке и поиск того, что вы хотите найти. Он откроет любой файл, оканчивающийся на xls* (звездочка в конце означает, что он будет искать файлы xls, xlsx и xlsm).

Чтобы настроить подпрограмму в соответствии с вашими потребностями, измените переменную strPath, чтобы она отражала путь к папке, которую вы хотите обработать, и измените strSearch, чтобы она отражала текст, который вы ищете. Макрос создает новый рабочий лист и помещает «попадания» в каждую строку. Столбец A содержит имя рабочей книги, столбец B — имя рабочего листа, столбец C — адрес ячейки, а столбец D — содержимое этой ячейки.

Очевидно, что запуск любого макроса, подобного этому, занимает довольно много времени. Вы можете несколько сократить время, уменьшив количество файлов, которые необходимо искать. Лучший способ сделать это — использовать подход Windows Search (описанный в начале этого совета) для определения рабочих книг, в которых находится нужный текст. Переместите эти книги в их собственную папку, а затем выполните поиск макроса в этой папке.

Если вы хотите узнать, как использовать макросы, описанные на этой странице (или на любой другой странице на сайтах ExcelTips), я подготовил специальную страницу, содержащую полезную информацию. Нажмите здесь, чтобы открыть эту специальную страницу в новой вкладке браузера.

ExcelTips – это экономичное обучение работе с Microsoft Excel. Этот совет (5598) относится к Microsoft Excel 97, 2000, 2002 и 2003.

Биография автора

На его счету более 50 научно-популярных книг и множество журнальных статей. Аллен Уайатт является всемирно признанным автором. Он является президентом Sharon Parq Associates, компании, предоставляющей компьютерные и издательские услуги. Узнайте больше об Аллене.

Работа с большими электронными таблицами Excel никогда не была легкой, особенно когда вам нужно найти фрагмент текста в файле, содержащем тысячи, даже десятки тысяч строк. Поиск в одном файле может стоить вам часов, если не дней, если вы работаете на медленном компьютере. Что еще хуже, иногда вам нужно искать в нескольких файлах и искать несколько строк.

Приведу пример.

У меня есть воображаемый магазин, в котором продаются автозапчасти. В моем магазине более 400 000 товаров, и они находятся в двух отдельных файлах. Открыть его на старом компьютере проблематично.

Теперь, например, я хочу найти части, которые содержат текст «SPLS» по некоторым причинам. Если я пойду традиционным путем, я нажму Ctrl + F (или Command + F на Mac), чтобы начать поиск. Откровенно говоря, поиск результатов из тысяч или миллионов в моем случае — непростая задача.

Что еще хуже, если я хочу найти несколько текстов, а не только один? Что делать, если вам нужно искать несколько текстов в НЕСКОЛЬКИХ файлах? Excel предоставляет нам только одно окно поиска, и вы можете искать только одну строку текста за раз.

Нам не повезло? Есть ли лучший способ поиска:

  • Несколько текстов
  • В нескольких файлах Excel

Поиск текста в нескольких файлах Excel с помощью программного обеспечения

К счастью, есть лучший способ справиться с этой скучной задачей — использовать программы, созданные специально для этой задачи.

Давайте посмотрим на программное обеспечение и то, как оно работает.

Как видите, это программное обеспечение решает проблему поиска в больших файлах Excel. Вы видите его истинную ценность, когда у вас есть много файлов для поиска и много текста для поиска. Кроме того, чем больше файлы, тем больше времени может сэкономить программа.

Вот как это программное обеспечение работает шаг за шагом:

  1. Вы выбираете файл или файлы, которые необходимо найти, нажимая кнопку добавления файлов Excel в левом верхнем углу панели. С помощью этой кнопки можно выбрать несколько файлов Excel.
  2. Затем введите текст, который вы хотите найти в этих файлах, вам нужно ввести один поиск в строке. Ограничений нет.
  3. Нажмите "Начать поиск".

Программе требуется некоторое время, чтобы просмотреть файлы и найти текст, который вы хотите найти. Как вы можете догадаться, чем больше файлы, тем больше времени требуется, чтобы найти все совпадения. Однако по сравнению с человеком программа намного быстрее, в сотни раз.

Вы можете задаться вопросом, это только для Windows? Хорошей новостью является то, что Excel Ultimate Search отлично работает на Windows, Mac и Linux (Ubuntu, Fedora, Centos…)

Читайте также: