5 фишек Excel, которые облегчат работу Директолога

Приветствую, Друзья!

Этот пост будет интересен всем, кто хочет впервые попробовать составить семантическое ядро для сайта или для рекламной кампании в Яндекс Директ.

Я не буду подымать все тонкости и детали подобной деятельности. Мы разберем только работу в Excel, в файлах которого и формируется вся основная семантика.

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

5 фишек Excel

Фильтры

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

Так, например, он значительно упростит работу, когда нам потребуется отфильтровать минус-слова на рекламную кампанию или объявление в Директе. Из огромного столбца с ключевыми словами мы можем найти все фразы, включающие в себя то или иное минус-слово.

Список стандартных минус-слов Вы сможете найти в интернете. Для своего примера я возьму слово «бесплатно».

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

В открывшемся окне, в самом верхнем поле пишем ненужное нам слово, в данном примере это «бесплатно». Жмем ОК.

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

Яндекс Директ не пропускает фразы и пробелы в минуса, поэтому функция Фильтра еще не раз пригодится на последующих этапах подготовки кампании.

Дополнительно отмечу, что нельзя использовать функцию «Вырезать» когда мы фильтруем минус-слова. Будет намного лучше если Вы сначала их скопируете, а потом удалите клавишей Delete.

Удаление пустых ячеек

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

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

После этой манипуляции все пустые ячейки столбика удалятся.

Транслит

Во время подготовки рекламной кампании в Яндекс Директ может потребоваться подмена URL целевой страницы (лендинг, одностраничник – называйте как хотите). Однако, ссылки могут не совсем правильно выглядеть. Если, скажем, в кириллическом URL будет кусок латиницы или в URL на латинице будет кусок из кириллицы.

Забегая вперед отмечу, что Яндекс также учитывает и релевантность самой ссылки к лендингу, а это в свою очередь может повлиять на цену клика и CTR…

В общем, нам нужно сделать траслит – перевести текст из кириллицы в латиницу или наоборот. В стандартном Excel такой функции или формулы нет, поэтому мы сделаем свой пользовательский модуль.

Открываем редактор Visual Basic (горячие клавиши Alt+F11).

Жмем Insert – Module и вставляем в открывшееся окно, вот этот кусок кода:

Function Translit(Txt As String) As String

    Dim Rus As Variant
    Rus = Array("а", "б", "в", "г", "д", "е", "ё", "ж", "з", "и", "й", "к", _
    "л", "м", "н", "о", "п", "р", "с", "т", "у", "ф", "х", "ц", "ч", "ш", _
    "щ", "ъ", "ы", "ь", "э", "ю", "я", "А", "Б", "В", "Г", "Д", "Е", _
    "Ё", "Ж", "З", "И", "Й", "К", "Л", "М", "Н", "О", "П", "Р", _
    "С", "Т", "У", "Ф", "Х", "Ц", "Ч", "Ш", "Щ", "Ъ", "Ы", "Ь", "Э", "Ю", "Я")

    Dim Eng As Variant
    Eng = Array("a", "b", "v", "g", "d", "e", "jo", "zh", "z", "i", "j", _
    "k", "l", "m", "n", "o", "p", "r", "s", "t", "u", "f", "kh", "ts", "ch", _
    "sh", "sch", "''", "y", "'", "e", "yu", "ya", "A", "B", "V", "G", "D", _
    "E", "JO", "ZH", "Z", "I", "J", "K", "L", "M", "N", "O", "P", "R", _
    "S", "T", "U", "F", "KH", "TS", "CH", "SH", "SCH", "''", "Y", "'", "E", "YU", "YA")

    For I = 1 To Len(Txt)
        с = Mid(Txt, I, 1)

        flag = 0
        For J = 0 To 65
            If Rus(J) = с Then
                outchr = Eng(J)
                flag = 1
                Exit For
            End If
        Next J
        If flag Then outstr = outstr & outchr Else outstr = outstr & с
    Next I

    Translit = outstr
End Function

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

Для Excel 2013: «Формулы» — «Вставить функцию»;

Для более ранних версий Excel: «Вставка» — «Функции».

В поле «категория» выбираем пункт «Определенные пользователем», а уже в поле функций выбираем «Translit» и жмем «ОК»

В следующем окне мы указываем ту ячейку, где у нас написан текст. Достаточно указать самую первую (на моем примере это А1), а затем просто растянуть значение на весь столбик.

Важно! Транслит будет работать только в том файле, в котором Вы кодом прописали дополнительный модуль.

Подстановка URL

Следующим шагом подготовки кампании для Яндекс Директа может стать подстановка созданных транслитов непосредственно к ссылке. Для этого мы можем использовать функцию Excel под названием СЦЕПИТЬ.

Представим, что нам нужно создать уникальные урлы ссылок, причем наделать их штук 500. Не вручную же копировать из столбца с созданными транслитами.

Поэтому прописываем такую формулу:

=СЦЕПИТЬ(«Ваш_домен/»;адрес_ячейки)

«На выходе» получаем вот такие красивые и уникальные ссылки с точным вхождением ключевого слова в урл на транслите

Более того, если в эту формулу, через точку с запятой, добавить еще одну ячейку, где у нас, скажем, прописаны utm-метки, то ссылки будут сразу генерироваться вместе с ними. Красота, да и только!

Строки в столбцы

При работе в Excel бывает необходимость перевести строки в столбцы или наоборот перевести столбцы в строки. Это делается совсем просто.

Копируем необходимый столбец или строку, и вставляем в нужное место через Специальную вставку (в контекстном меню). В открывшемся окне нужно отметить пункт «Транспонировать» и нажать «ОК».

На этом все, Друзья! Скажите, а Вы хотели бы узнать еще больше фишек Excel?