Надстройка для Excel, которая сэкономит время отделу контекстной рекламы

Обновленная версия надстройки и обзор новых инструментов доступны в материале, опубликованном в марте 2018 года.

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

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

Так у меня накопилась большая библиотека функций и макросов, которые я публиковал обычно по отдельности, но некоторые собирал в единый инструмент. Таким стал файл с шутливым названием «Робот-распознаватель», последнюю версию которого всегда можно найти как у меня на странице в Facebook, так и обычным поиском в интернете.

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

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

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

Установка

1. Скачайте файл надстройки и сохраните его в директории, которую не планируете перемещать и точно не удалите (например, в отдельной папке прямо в корне диска). Не нужно открывать файл.

2. Перейдите в «Параметры Excel» и найдите пункт «Надстройки», нажмите на кнопку «Перейти».

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

Жмите «ОК». Готово! Теперь все должно работать, при каждом запуске Excel будет загружаться и эта надстройка, и все функции и макросы в ней. Кнопки макросов появятся в отдельной панели «Надстройки» и будут выглядеть примерно так:

Если после перезапуска программы панель «Надстройки» не открывается, возможно, ее блокирует защита операционной системы. Чтобы исправить, нажмите на файл правой кнопкой мыши, откройте свойства и кликните на кнопку «Разблокировать»:

Функционал

В первую очередь пробежимся по макросам.

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

Для вызова макросов зайдите в панель «Надстройки», выберите нужный и нажмите на кнопку.

• Удалить пунктуацию // удаляет все возможные символы, кроме пробелов, цифр и букв;

• Проставить все модификаторы // проставляет модификаторы во все слова в диапазоне;

• Проставить модификаторы кроме стоп-слов // проставляет символ «+» (модификатор широкого соответствия в AdWords) перед каждым словом в каждой строке выделенного диапазона, кроме стоп-слов (текущий список, как тут). Остальные варианты типов соответствия изобразил схематично, должно быть и так понятно;

• Исправить заглавные // преобразует строки диапазона, делая первые буквы предложений заглавными;

• Удалить UTM // удаляет в диапазоне все UTM-метки, если они есть. При этом оставляет все остальные параметры нетронутыми. Также удаляет лишние пробелы;

• Удалить слова из цифр // иногда бывает нужно, например, перед составлением частотного словаря;

• Удалить стоп-слова // удаляет все русские стоп-слова из массива (список тот же, что выше);

• Частотный словарь // этот макрос производит анализ выделенного диапазона на предмет встречаемости в нем уникальных слов и выводит результат на отдельный лист;

• Скомбинировать списки // это генератор линейных комбинаций. Позволяет составить линейные комбинации на основе нескольких списков. Списков и элементов в них может быть любое количество, но все же есть ряд ограничений:

1. Списки не должны содержать пустых ячеек,
2. Не должно быть пустых столбцов,
3. Первая строка первого списка – Ячейка «A1».

Кратко по функциям:

• =слов(A1) // количество слов в ячейке. Функция по сути «прячет под капот» громоздкий синтаксис =ДЛСТР(СЖПРОБЕЛЫ(A1)) – ДЛСТР(ПОДСТАВИТЬ(СЖПРОБЕЛЫ(A1);” “;””)) + 1, который вычисляет количество слов как разницу длины строки с пробелами и без (слов на 1 больше, чем пробелов в строке);

• =ФразовоеДиректИлиТочноеAdWords(A1) // ставит перед и после фразы квадратные скобки, предварительно убрав из фразы лишние пробелы, если есть;

• =ТочноеДиректИлиФразовоеAdWords(A1) // аналогично предыдущей расставляет кавычки;

• =ПроставитьМодификаторы(A1) // проставляет знак «+» перед каждым словом;

• =ЗакрепитьСловоформы(A1) // проставляет знак «!» перед каждым словом;

• =SentenceCase(A1) // определяет начала предложений и делает их первые буквы заглавными (полезно для написания текстов объявлений);

• =БезПунктуации(A1) // удаляет все лишние пунктуационные символы (полезно для работы с ключевыми словами);

• =БезUTMметок(A1) // удаляет UTM-метки из ссылок, не трогая никакие другие параметры ни перед, ни после них;

• =ЕстьУкраинский(A1) // полезно для быстрой фильтрации украинских запросов, распознает по ~30 различным факторам (наличие в строке определенных символов или сочетаний символов);

• =ЕстьЛатиница(A1) // проверяет, есть ли в ячейке любые символы латиницы;

• =ЕстьЦифры(A1) // аналогично предыдущей, но с цифрами;

• =ВсеСБольшой(A1) // делает заглавными все первые буквы слов;

• =ЛатиницаСБольшой(A1) // делает заглавными все первые буквы слов, содержащих латиницу;

• =СортироватьАЯВнутриЯчейки(A1) // сортирует слова внутри ячейки по алфавиту от А до Я;

• =ЗаменитьСловаИзСписка(A1;$G$1:$H$36) // сверяет слова из ячейки (в примере это A1) в массиве из двух столбцов со словами из первого столбца, и, если находит их, заменяет на слова, стоящие напротив них во втором столбце. Может быть полезна при написании текстов, когда не вмещаются некоторые слова, и их нужно сократить. Функция является зависимой от сортировки – указываемый список должен быть сортирован по алфавиту;

• =НайтиСловаИзСписка(A1;$J$1:$K$2000) // позволяет прямо в Excel производить кластеризацию по маркерам. Аналогично предыдущей функции, ищет в массиве из двух столбцов, однако, в отличие от нее, возвращает не все, а только найденные слова. Является регистрозависимой, поэтому для достижения нужного результата искомые фразы и слова в просматриваемых столбцах должны быть в одном регистре (или ПРОПИС или СТРОЧН).

Заключение

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