Поиск в excel в нескольких файлах
Обновлено: 08.07.2024
Я использую Microsoft Office 2007. Как я могу искать внутри нескольких файлов Excel?
Открыв папку, в которой находятся файлы, нажмите Ctrl + f, чтобы использовать окно поиска в папке. Тогда ищите
искать эту строку в файлах в текущем каталоге.
Похоже, что для поиска содержимого в виде простого текста доступны только двоичные данные в файлах xlsx. Это не сработало для меня. В самом деле? Когда я выполняю поиск открытого текста с использованием этого метода, я получаю несколько возвращенных файлов .xlsx, и все они действительно содержат искомую строку поиска, даже если они действительно содержат двоичные данные. Папка должна быть настроена для этого. Щелкните правой кнопкой мыши, чтобы увидеть свойства папки. Нажмите Дополнительно. Установите флажок «Разрешить индексирование файлов . » У меня не работает в Excel 2016. Он возвращает много файлов xlsx, но ни один из них не содержит мою ожидаемую строку Я создал контрольный пример фиктивных файлов с точной строкой, которую я искал. Я сделал файлы xlsx, docx и txt со строкой и без нее. Те, у кого есть строка, которую я отметил в имени файла, те, у кого тоже нет. Используя поиск контента, он правильно разделил две группы.После поиска в Интернете я обнаружил, что не существует единого универсального решения проблемы поиска в нескольких таблицах Excel в Интернете, поэтому я решил написать свою собственную таблицу Excel, которая ищет другие таблицы Excel, которые можно найти здесь .
Обратите внимание, что он был протестирован только в Excel 2010, но он должен работать в 2007 и более ранних версиях. Если нет, не стесняйтесь изменять код так, как считаете нужным.
Электронная таблица использует пользовательскую форму и код VBA (который вы можете использовать для любых целей). Естественно, чтобы это работало, нужно включить макросы и т. Д., Форма открывается после открытия электронной таблицы (вы можете получить доступ к коду, нажав Alt + F11, перейдя к пользовательской форме и дважды щелкнув кнопку «Начать поиск» в дизайн окна).
Полное объяснение функций и возможностей можно найти в файле readme для Github, но в основном оно позволяет указать два условия текстового поиска для поиска в указанном каталоге (к которому вы можете перейти), он может выполнять рекурсивный поиск в указанном каталоге и открывать электронные таблицы. которые защищены паролем (при условии, что вы предоставляете пароль).
Он будет искать отдельные листы в каждой книге при поиске на уровне ячейки. В зависимости от размера рабочей книги сканирование каждой рабочей книги может занять примерно секунду.
Он отображает все результаты поиска в боковом окне, включая любые таблицы, которые он не смог открыть. Эти результаты могут быть сохранены в текстовый файл для последующего использования.
Пользовательская форма должна быть относительно понятной, однако ее читайте на Github, чтобы глубже понять, как ее использовать. Опять же, свободно использовать, это с открытым исходным кодом.
Иногда бывает необходимо заполучить на лист Excel список файлов в заданной папке и ее подпапках. В моей практике такое встречалось неоднократно, например:
- перечислить в приложении к договору на проведение тренинга список файлов из раздаточных материалов для особо щепетильных юристов в некоторых компаниях
- создать список файлов для ТЗ проекта
- сравнить содержимое папок (оригинал и бэкап, например)
Для реализации подобной задачи можно использовать несколько способов.
Способ 1. Скелет из шкафа - функция ФАЙЛЫ
Этот способ использует древнюю функцию ФАЙЛЫ (FILES) , оставшуюся в Microsoft Excel с далеких девяностых. Вы не найдете эту функцию в общем списке функций, но для совместимости, она всё ещё остаётся внутри движка Excel, и мы вполне можем её использовать.
1. В любую ячейку листа (например, в А1) введём путь к папке, список файлов из которой мы хотим получить.
Обратите внимание, что путь должен оканчиваться шаблоном со звездочками:
- *.* - любые файлы
- *.xlsx - книги Excel (только с расширением xlsx)
- *.xl* - любые файлы Excel
- *отчет* - файлы, содержащие слово отчет в названии
2. Создадим именованный диапазон с помощью вкладки Формулы - далее кнопка Диспетчер имен - Создать (Formulas - Names Manger - Create) . В открывшемся окне введем любое имя без пробелов (например Мои_файлы) и в поле диапазона выражение:
После нажатия на ОК будет создан именованный диапазон с именем Мои_файлы, где хранится список всех файлов из указанной в А1 папки. Останется их оттуда только извлечь.
3. Чтобы извлечь имена отдельных файлов из созданной переменной, используем функцию ИНДЕКС (INDEX) , которая в Excel вытаскивает данные из массива по их номеру:
Если лениво делать отдельный столбец с нумерацией, то можно воспользоваться костылем в виде функции СТРОКИ (ROWS) , которая будет подсчитывать количество заполненных строк с начала списка автоматически:
=ИНДЕКС(Мои_файлы; ЧСТРОК($B$3:B3) )
= ЕСЛИОШИБКА( ИНДЕКС(Мои_файлы; ЧСТРОК($B$3:B3)) ; "")
Важное примечание : формально функция ФАЙЛЫ относится к макро-функциям, поэтому необходимо будет сохранить ваш файл в формате с поддержкой макросов (xlsm или xlsb).
Способ 2. Готовый макрос для ленивых
Если вы знакомы с макросами (не в смысле их программирования, а в смысле копипастинга готовых кодов на VBA), то вам, возможно, отлично зайдёт небольшой макрос, добавляющий в текущую книгу новый пустой лист и выводящий на него список всех файлов с их параметрами из заданной пользователем папки.
Для добавления макроса в вашу книгу нажмите сочетание клавиш Alt + F11 , или кнопку Visual Basic на вкладке Разработчик (Developer) , в открывшемся окне редактора Visual Basic вставьте новый модуль через меню Insert - Module и скопируйте туда текст этого макроса:
Для запуска макроса нажмите сочетание клавиш Alt + F8 ,или кнопку Макросы (Macros) на вкладке Разработчик (Developer) , выберите наш макрос FileList и нажмите кнопку Выполнить (Run) . В диалоговом окне выберите любую папку или диск и - вуаля!
Если захотите, чтобы вместо пути к файлу в столбце B выводилась живая гиперссылка, то замените 52-ю строку
Cells(r, 2).Formula = FileItem.Path
Cells(r, 2).Formula = "=HYPERLINK(""" & FileItem.Path & """)"
Способ 3. Мощь и красота - надстройка Power Query
Power Query - это очень мощная и при этом бесплатная надстройка для Excel от Microsoft, упрощающая множество задач по загрузке и трансформации данных. В нашей ситуации она тоже может здорово помочь.
Если у вас Excel 2016 или новее, то Power Query уже встроена в Excel по умолчанию, поэтому просто на вкладке Данные выберите команду Создать запрос / Получить данные - Из файла - Из папки (Create Query / Get Data - From file - From folder) . Если у вас Excel 2010-2013, то Power Query нужно будет скачать с сайта Microsoft и установить как отдельную надстройку и она появится у вас в Excel в виде отдельной вкладки Power Query. На ней будет аналогичная кнопка Из файла - Из папки (From file - From folder) .
В открывшемся окне нужно будет указать папку, содержимое которой мы хотим получить. После нажатия на ОК Power Query обшарит указанную папку и все вложенные подпапки и выдаст на экран окно с предварительным просмотром результатов:
Если внешний вид списка вас устраивает, то можно смело жать внизу кнопку Загрузить (Load) , чтобы залить эти данные на новый лист. Если же хочется дополнительно обработать список (удалить лишние столбцы, отобрать только нужные файлы и т.п.), то нужно выбрать команду Изменить / Преобразовать данные (Edit / Transform Data).
Поверх окна Excel откроется окно редактора Power Query, где мы увидим список всех наших файлов в виде таблицы:
Дальше возможны несколько вариантов:
-
Если нужны только файлы определенного типа, то их можно легко отобрать с помощью фильтра по столбцу Extension:
После того, как необходимые файлы отобраны, можно смело удалить ненужные столбцы, щелкнув по заголовку столбца правой кнопкой мыши и выбрав команду Удалить (Remove column ) . Это, кстати, уже никак не повлияет на фильтрацию или сортировку нашего списка:
Если в будущем планируется подсчитывать количество файлов в каждой папке (например, для контроля поступивших заявок или подсчета статистики по заявкам), то имеет смысл дополнительно сделать ещё пару действий:
- Щелкните правой кнопкой мыши по столбцу Folder Path и выберите команду Дублировать столбец (Duplicate Column) .
- Выделите скопированный столбец и на вкладке Преобразование (Transform) выберите Разделить столбец - По разделителю (Split Column - By delimiter)
Мы получим рядом с нашими данными еще несколько столбцов, где будут продублированы имена вложенных папок - это пригодится нам чуть позже для подсчета статистики с помощью сводной таблицы:
Получившиеся столбцы можно переименовать (Диск, Папка1, Папка2 и т.д.), просто щёлкнув дважды по заголовку каждого.
И, наконец, когда список готов, то его можно выгрузить на лист с помощью команды Главная - Закрыть и загрузить - Закрыть и загрузить в. (Home - Close & Load - Close & Load to. ) :
И, само-собой, теперь можно построить по нашей таблице сводную (вкладка Вставка - Сводная таблица), чтобы легко подсчитать количество файлов в каждой папке:
Дополнительным бонусом можно сделать еще один столбец с функцией ГИПЕРССЫЛКА (HYPERLINK) , которая создаст красивые стрелочки-ссылки для моментального перехода к каждому файлу:
Мелочь, а приятно :)
И вдвойне приятно, что в будущем, при изменении содержимого исходной папки, достаточно будет просто щелкнуть мышью по нашей таблице и выбрать команду Обновить (Refresh) - и Power Query выполнит всю цепочку запрограммированных нами единожды действий уже автоматически, отобразив все изменения в составе папки.
В Excel вы можете использовать функцию «Найти и заменить», чтобы найти и заменить слово на нескольких листах или во всей книге, но если вы хотите найти и заменить слово в нескольких открытых книгах, встроенная функция Excel вам не поможет. . Здесь я представляю удобный инструмент надстройки Excel - Kutools для вас, чтобы быстро решить эту проблему.
Быстрый поиск и замена в нескольких открытых книгах
Если вы установили Kutools for Excel, вы можете использовать Навигация панель для быстрого поиска и замены слова в нескольких открытых книгах.
После бесплатная установка Kutools for Excel, сделайте следующее:
1. Откройте книги, которые вы хотите найти, и сразу заменить определенное слово.
2. Нажмите Kutools > Навигация чтобы включить панель навигации, затем щелкните Найти и заменить кнопка для перехода к Найти и заменить раздел. Смотрите скриншот:
3. Под Заменять вкладку, введите найденный текст и замените текст на Найти то, что текстовое поле и Заменить текстовые поля затем выберите Все книги из В раскрывающийся список. Смотрите скриншот:
Наконечник: Если вы не можете узнать В выпадающий список и Книги список, нажмите кнопку enpand, чтобы развернуть расширенный Найти и заменить параметры. См. Снимок экрана ниже:
5. Нажмите Заменить все. Затем тексты во всех открытых книгах заменяются.
Примечание: По умолчанию ярлыки для включения Kutools ' Найти и заменить диалог Win + Shift + Q, но если вы знакомы с ярлыком Ctrl + F, вы можете перейти к настройке, чтобы проверить Нажатие Ctrl + F, чтобы применить поиск и замену Kutools вариант, а затем при нажатии Ctrl + F, это Найти и заменить появляется диалоговое окно.
Работы С Нами Kutools for ExcelАвтора Навигация панели, вы можете переключаться между двумя листами, перечислять все листы, столбцы и имена, добавлять изображения или формулы, которые вы обычно используете, в избранное и т. д. Для получения дополнительных сведений о панели навигации, пожалуйста, нажмите здесь.
Вы когда-нибудь задумывались о поиске определенного значения на нескольких листах или книгах в Excel? В этом руководстве представлены различные методы решения проблем, связанных с поиском на нескольких листах или поиском в нескольких книгах.
Быстрый поиск значения в нескольких открытых книгах с помощью Kutools for Excel
Поиск значения на нескольких листах книги с помощью функции поиска и замены
С помощью Excel Найти и заменить функция, вы можете найти определенное значение на нескольких листах.
1. Выберите несколько вкладок листов, на которых вы хотите найти значение, удерживая Ctrl и щелкнув листы в Панель вкладок листа по одному. Смотрите скриншот:
2. Затем нажмите Ctrl + F для Найти и заменить окна и введите значение, которое вы хотите найти, в Найти то, что текстовое поле под Найдите Вкладка, а затем нажмите кнопку Найти все кнопку, чтобы просмотреть все результаты. Смотрите скриншот:
Поиск и замена значений на листах и в книгах
Искать значение во всех книгах папки с VBA
Если вы хотите найти значение во всех закрытых книгах из папки, вы можете применить только VBA для его решения.
1. Включите новую книгу и выберите ячейку, затем нажмите Alt + F11 ключи для открытия Microsoft Visual для базовых приложений окно.
2. Нажмите Вставить > Модуль и вставьте ниже VBA в новое окно модуля.
VBA: поиск значения во всех книгах папки.
3. Затем нажмите F5 ключа или Запустите кнопку для выполнения этого VBA и Выберите папку Появится диалоговое окно, напоминающее вам о выборе папки, в которой вы хотите искать значение. Смотрите скриншот:
4. Затем щелкните OK и другое диалоговое окно появляется, чтобы напомнить вам, сколько ячеек было найдено. Смотрите скриншот:
5. Нажмите OK в Закрыть его, и все найденные ячейки будут перечислены на текущем листе с соответствующей информацией.
Наконечник: В приведенном выше VBA вы ищете значение «КТЕ» , и вы можете изменить «КТЕ» из этого xStrSearch на другое значение по мере необходимости.
Быстрый поиск значения в нескольких открытых книгах с помощью Kutools for Excel
Если вы просто хотите найти значение в нескольких открытых книгах, вы можете использовать Kutools для Excel 'продвинутый Найти и заменить утилита.
После бесплатная установка Kutools for Excel, сделайте следующее:
1. В одной из открытых книг щелкните Kutools > Навигация, а затем нажмите Найти и заменить кнопка идти на Найти и заменить панель. Смотрите скриншот:
2. Затем нажмите Найдите вкладка и введите значение, которое вы хотите найти в Найти то, что текстовое поле, а затем выберите Все книги из В раскрывающийся список и нажмите Найти все чтобы перечислить все найденные ячейки. Смотрите скриншот:
Наконечник:
Работы С Нами Kutools for Excelпродвинутый Найти и заменить Утилита, вы можете искать и заменять значения на выбранных листах в нескольких книгах, во всех книгах, активной книге, активном листе или выборе.
Читайте также: