Поиск в Excel

Программа подстановки данных из одного файла в другой (замена функции ВПР)

Сравнение столбцов в Excel, и подстановка значений в таблицу

Программа предназначена для сравнения и подстановки значений в таблицах Excel.

 

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

 

То же самое можно сделать при помощи формулы =ВПР(), но:

  • формулы могут тормозить работу с файлом при пересчёте, если объём данных большой (много строк или столбцов)
  • если источник данных или файл, в который подставляются данные, каждый раз новый, - требуется время на прописывание или редактирование формул
  • если с файлами работают люди, «далёкие» от Excel, - их проще обучить нажимать одну кнопку, чем объяснять им, как прописывать эти формулы

 

В настройках программы можно задать:

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

 

Инструкция по использованию программы с несколькими разными наборами настроек

Инструкция по выделению цветом совпадений и отличий в сравниваемых таблицах

Инструкция по копированию несовпавших строк из одной таблицы в другую

 

Надстройка для выполнения замен в таблице Excel

Надстройка предназначена для выполнения множественных замен в выделенном диапазоне ячеек.

 

Поиск по прайс-листам

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

 

Если у вас есть множество разнотипных прайсов от разных поставщиков, и вы хотите одним нажатием кнопки найти нужный товар во всех прайсах, — то это легко сделать при помощи универсальной программы обработки прайс-листов, и специального шаблона итогового прайса с макросом, облегчающим поиск в сводной таблице:

Поиск заданного текста в ячейках, с подсветкой найденных вхождений

Поиск и подсветка результатов в Excel

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

 

При запуске макроса появляется диалоговое окно (InputBox), позволяющее задать текст для поиска.

Макрос подсвечивает красным цветом внутри ячейки текст, совпадающий с искомым
(+ выделяет найденное полужирным начертанием)

Перед началом поиска, цвет всех ячеек первого столбца сбрасывается (на черный)

Макрос поиска видимых строк и заполненных ячеек на листе Excel

Если ваш макрос выдаёт ошибку при использовании метода SpecialCells - возможно, причина в установленной защите листа Excel.

Почему разработчики Microsoft отключили работу этой функции на защищённых листах - не совсем понятно, но мы попробуем обойти это ограничение.

 

Итак, нам надо получить все заполненные ячейки из некого диапазона листа Excel. 

Обычно для этого используется вызов метода SpecialCells - например,

msgbox Range("a2:d8").SpecialCells(xlCellTypeConstants).Address

Но на защищенном листе такой код выдаст ошибку 1004.

Чтобы избавиться от ошибки, мы используем функцию SpecialCells_TypeConstants - замену встроенному методу SpecialCells(xlCellTypeConstants)

 

Поиск в Google значений из ячеек листа Excel

Макрос для поиска текста выделенных ячеек в Google

Макрос предназначен для поиска текста из выделенных ячеек в поисковой системе Google.

Функция поиска доступна из контекстного меню ячеек:

добавление пункта поиска в контекстное меню ячеек Excel

Как вы можете видеть на скриншоте, есть возможность выбора браузера.
На выбор представлены наиболее популярные браузеры: Internet Explorer, Mozilla Firefox, Opera, и Google Chrome.

Поиск адресов электронной почты (email) на листе Excel

Данный макрос предназначен для поиска адресов электронной почты на листе Excel, с последующим выводом найденных адресов на отдельный лист.

В прикреплённом файле, на первом листе ("исходные данные"), ячейки заполнены неструктурированной информацией (смесь фамилий, адресов почты, прочей ненужной информации)

Макрос вычленяет из текста ячеек адреса электронной почты, и выводит все найденные адреса email в таблицу на втором листе ("результат")

Конечно, не помешало бы ещё проверить все найденные адреса почты на корректность (на соответствие стандартам RFC 5322 и RFC 5321),
но в данном макросе это не реализовано (но обычно это и не требуется)

Для поиска адресов email используются регулярные выражения (RegExp)

Также рекомендуем обратить внимание на настройку для программы Парсер сайтов Сбор email на интернет-сайтах. Настройка позволяет проходить по списку сайтов и искать е-мейл на основной странице и странице контактов.

Быстрый поиск в двумерном массиве

В данной статье показаны 2 способа быстрого поиска значений в двумерных массивах.

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

оба способа получают на выходе отфильтрованный двумерный массив.

Способы формирования отфильтрованных массивов - разные:

первый способ использует функцию ArrAutofilterEx

второй способ - функцию ArraySearchResults

Основные отличия и особенности этих 2 способов поиска:

  • ArrAutofilterEx позволяет задавать несколько критериев поиска (фильтрации)
  • ArrAutofilterEx ищет вхождение искомого текста в значения заданных столбцов (неточное совпадение)
  • ArrAutofilterEx при каждом вызове заново в цикле перебирает все элементы массива,
    соответственно, при поиске 10 значений время работы кода увеличивается в 10 раз
  • ArraySearchResults позволяет использовать фильтрацию массива только по одному столбцу
  • ArraySearchResults ищет совпадение искомого текста со значением столбца (точное совпадение)
  • ArraySearchResults производит поиск в заранее сформированной текстовой строке
    Таким образом, перебираются все ячейки массива в цикле только один раз, и поиск 100 значений в массиве займёт ненамного больше времени, чем поиск 1 значения.

Поиск похожих записей в черном списке

Форма вывода результатов проверки на совпадения

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

 

Программа сравнения прайс-листов (поиск цен по коду товара)

Скриншот программы сравнения прайс-листов Excel

Программа предназначена для сравнения цен конкурентов из их прайсов с прайс-листом вашей организации.

ВНИМАНИЕ: Недавно разработана многофункциональная программа для обработки прайс-листов