Очень часто стоит задача отыскать определенные значения в большом объеме данных. Набор инструментов в Excel для решения этой задачи достаточно разнообразен: стандартный поиск, сортировка, фильтры, условное форматирование, но бывает, что и этого недостаточно.

Задача поиска

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

Одна из распространенных задач на тему поиска значений в Excel – очистка списка от ненужных значений. При составлении семантического ядра или создании рекламных кампаний в Яндекс.Директ или Google Adwords составляются списки ключевых слов. Эти списки могут быть очень большими и содержать много мусорных значений. Для чистки таких списков, как правило, используется специализированное программное обеспечение, но и без Excel редко обходится.

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

Надстройка для быстрого поиска значений

Надстройка – это инструмент, позволяющий расширить стандартные возможности Excel, и представляет собой программное решение той или иной задачи, написанное на VBA - встроенном в приложения Office языке программирования. Надстройка создает кнопку на ленте Excel, при помощи которой вызывается диалоговое окно программы.

Надстройка позволяет:

1) задавать значения для поиска (есть возможность задавать сразу несколько значений, используя знак-разделитель ";" (точка с запятой);

2) осуществлять поиск значений как с учетом регистра, так и без;

3) задавать диапазон ячеек для поиска значений:

а) используемый диапазон листа - диапазон, включающий в себя все ячейки, которые находятся между первой и последней заполненной ячейкой;

б) выделенный диапазон листа - диапазон, выделенный пользователем.

*Выбранный диапазон не должен иметь объединенных ячеек.

4) задавать условие для искомых значений:

а) содержит;

б) не содержит;

в) совпадает;

г) не совпадает.

5) осуществлять выбранное действие:

а) оставить строки с искомыми значениями и поместить их на новый лист;

б) исключить строки с искомыми значениями и поместить их на новый лист.

Диалоговое окно надстройки

поиск значений в ExcelCompleteSolution

Результат работы программы

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

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

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

Скорость обработки значений

Скорость поиска и последующей обработки значений зависит от размеров диапазона, от количества искомых значений и от производительности компьютера. При работе только со значениями ячеек есть возможность свести к минимуму количество обращений программы к исходным данным, поэтому скорость обработки данных достаточно высокая. Ориентировочно, если задается поиск одного значения во всех ячейках одного столбца (немногим более 1 млн. строк), то обработка данных занимает 2-3 минуты.