Очень часто стоит задача отыскать определенные значения в большом объеме данных. Набор инструментов в Excel для решения этой задачи достаточно разнообразен: стандартный поиск, сортировка, фильтры, условное форматирование, но бывает, что и этого недостаточно.
Задача поиска
Как правило, требуется не просто найти определенные значения, но и произвести с ними какие-либо действия, например, заменить найденные значения другими, скрыть или удалить строки с искомыми значениями или удалить ячейки по условию.
Одна из распространенных задач на тему поиска значений в Excel — очистка списка от ненужных значений. При составлении семантического ядра или создании рекламных кампаний в Яндекс.Директ или Google Adwords составляются списки ключевых слов. Эти списки могут быть очень большими и содержать много мусорных значений. Для чистки таких списков, как правило, используется специализированное программное обеспечение, но и без Excel редко обходится.
При помощи надстройки для Excel, описанной ниже, можно быстро почистить список от ненужных значений, а также распределить разные значения по отдельным листам рабочей книги Excel.
Надстройка для быстрого поиска значений
Надстройка — это инструмент, позволяющий расширить стандартные возможности Excel, и представляет собой программное решение той или иной задачи, написанное на VBA — встроенном в приложения Office языке программирования. Надстройка создает кнопку на ленте Excel, при помощи которой вызывается диалоговое окно программы.
Надстройка позволяет:
1) задавать значения для поиска (есть возможность задавать сразу несколько значений, используя знак-разделитель «;» (точка с запятой);
2) осуществлять поиск значений как с учетом регистра, так и без;
3) задавать диапазон ячеек для поиска значений:
а) используемый диапазон листа — диапазон, включающий в себя все ячейки, которые находятся между первой и последней заполненной ячейкой;
б) выделенный диапазон листа — диапазон, выделенный пользователем.
*Выбранный диапазон не должен иметь объединенных ячеек.
4) задавать условие для искомых значений:
а) содержит;
б) не содержит;
в) совпадает;
г) не совпадает.
5) осуществлять выбранное действие:
а) оставить строки с искомыми значениями и поместить их на новый лист;
б) исключить строки с искомыми значениями и поместить их на новый лист.
Диалоговое окно надстройки
Результат работы программы
Программа осуществляет поиск как одного, так и нескольких значений в строках указанного диапазона ячеек и на отдельном листе формирует список из значений, подходящих под заданные условия.
Лист с исходными значениями всегда остается без изменений, во избежание потери данных. Результат проделанной работы выгружается на отдельный лист рабочей книги.
В результате обработки ячеек заданного диапазона, формулы преобразуются в числовые значения. На новый лист переносятся только значения ячеек, без формул и без установленных форматов (шрифтов, границ, заливки и так далее).
Скорость обработки значений
Скорость поиска и последующей обработки значений зависит от размеров диапазона, от количества искомых значений и от производительности компьютера. При работе только со значениями ячеек есть возможность свести к минимуму количество обращений программы к исходным данным, поэтому скорость обработки данных достаточно высокая. Ориентировочно, если задается поиск одного значения во всех ячейках одного столбца (немногим более 1 млн. строк), то обработка данных занимает 2-3 минуты.
