7 продвинутых приемов для Excel, которые должен использовать каждый SEO-специалист

17.04.2018

  • 2460
  • 0.0


Мы перевели для вас статью Нила Пателя. Читайте и применяйте.


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

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

Большинство маркетологов-профессионалов знают нужные функции как свои пять пальцев, но все как один скажут вам, что это всего лишь вопрос времени.

Однажды овладев этими навыками, вам больше не придется возвращаться к этому вновь и вновь.

Зачем учиться пользоваться Excel для SEO

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

2 - 7 продвинутых приемов для Excel, которые должен использовать каждый SEO-специалист

Таблицы помогают организовывать все данные в одном месте,где они всегда под рукой. А когда речь идет о чем-то вроде SEO, дисциплина и организация – это основа вашего успеха, особенно если у вас сотни или даже тысячи ключевых слов.

Причина, по которой Excel является такой потрясающей программой для SEO, – это наличие расширений и плагинов, созданных специально для того, чтобы сделать процесс пользования максимально простым.

 

Например, SEOTools для Excel идет с уже встроенными функциями специально для маркетологов.

3 - 7 продвинутых приемов для Excel, которые должен использовать каждый SEO-специалист

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

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

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

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

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

1. Используйте формулу «=IF» для разбивки ключевых слов по категориям

Находясь в поиске ключевых слов, учишься спокойно относиться к длинным спискам. Привыкаешь к ним, так сказать. Даже пользуясь простой утилитой типа Google’s Keyword Planner, все равно получишь длинный список.

4 - 7 продвинутых приемов для Excel, которые должен использовать каждый SEO-специалист

Другие утилиты вроде Ahrefs или Moz могут выдать вам списки с тысячами ключевых слов, показателей объема и данными о конкуренции. А это очень, очень много данных. Поместив эти данные в таблицу, мы сможете организовать их по рядам и колонкам. Но ведь нужно еще и как-то осмыслить все это.

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

Например, если использовать более одного семантического ядра в Keyword Planner,

5 - 7 продвинутых приемов для Excel, которые должен использовать каждый SEO-специалист

то вам выдают колонку «Ad Group», куда можно экспортировать список запросов, которые отделят семантические ядра от остальных вариантов ключевых слов.

6 - 7 продвинутых приемов для Excel, которые должен использовать каждый SEO-специалист

Предположим, я хочу разбить этот список только на ключевые запросы со словами «утилиты» в них. Если делать это вручную, придется продираться сквозь более чем 700 рядов. А мне не очень-то хочется это делать. Так что вместо этого я собираюсь использовать формулу Excel =IF (=ЕСЛИ), которая сделает все за меня.

Первым делом нужно удалить колонку «Ad Group», потому что я хочу искать среди всех запросов.

7 - 7 продвинутых приемов для Excel, которые должен использовать каждый SEO-специалист

А потом я добавлю колонку «Category» (“Категория”) рядом с поисковым объёмом.

8 - 7 продвинутых приемов для Excel, которые должен использовать каждый SEO-специалист

В первой свободной клетке я пишу слово «Tools» и затем использую формулу:

=if(isnumber(search(“tools,A12)),”Tools”)

Когда ключевое слово «tools» найдется в первой колонке (мой список ключевых слов), то поместится в категорию «Tools».

9 - 7 продвинутых приемов для Excel, которые должен использовать каждый SEO-специалист

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

Если хотите только ключевые запросы со словом «local», можно создать категорию «Local». Удивительно нужная штука для организации всех ваших ключевых запросов для быстрого поиска.

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

У Moz есть хороший пример того, как можно сделать это для всех данных во всех таблицах.

Чтобы сделать это для всех рядов и колонок, используйте мульти-клеточную формулу ARRAY (МАССИВ).

10 - 7 продвинутых приемов для Excel, которые должен использовать каждый SEO-специалист

Это освободит вас от необходимости вставлять одну и ту же сложную формулу в каждую клетку таблицы. И даже если у вас не 700+ клеток, с которыми нужно иметь дело, формула все равно пригодится.

2. Создавайте сводные таблицы для обнаружения выброса данных

Анализ данных в таблицах может быть довольно хаотичным, если данные не организованы.

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

Чтобы создать такую, выделите клетку таблицы и нажмите Insert > Pivot Table (Вставить > Сводная Таблица).

11 - 7 продвинутых приемов для Excel, которые должен использовать каждый SEO-специалист

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

12 - 7 продвинутых приемов для Excel, которые должен использовать каждый SEO-специалист

Ваша таблица будет автоматически помещена в выделенную клетку, но также можно создать новую таблицу или поместить ее куда-то еще.

Когда нажмете «OK», то увидите поле, соответствующее вашим колонкам.

13 - 7 продвинутых приемов для Excel, которые должен использовать каждый SEO-специалист

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

Используйте эти таблицы для таких вещей как организация ссылок или URL, группировка внутренних ссылок по DA/PA, поиск ключевых слов в домене или создание всплывающих окон для определенных колонок.

Вот пример функциональной сводной таблицы:

14 - 7 продвинутых приемов для Excel, которые должен использовать каждый SEO-специалист  

Такая таблица не просто организует ваши данные в более читабельном виде, но и дает несколько опций для категоризации. В примере выше можно сразу заметить большие или маленькие цифровые значения CPA («cost-per-action», цена-за-действие) для определенных ключевых слов.

Это делается при помощи условного форматирования в рамках сводной таблицы (вы можете заметить цветное кодирование). Сводные таблицы типа этой могут оказаться полезными, если вы используете ключевые слова для транслирования своей стратегии Adwords, или если хотите быстро обнаружить высоко- и низкочастотные запросы и CPC («cost-per-click», цена-за-клик) для конкретных ключевых слов.

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

И выглядит такая таблица тоже неплохо.

3. Конвертируйте показатели объема при помощи SUBSTITUTE (ЗАМЕНИТЬ)

Существует множество разных способов сортировки данных в Excel. Тем не менее, ключ к продуктивности заключается в том, чтобы найти верный. Когда вы загружаете данные из утилиты для ключевых слов (типа Keyword Planner), CSV-файлы не всегда выглядят хорошо. Например, мои объемы ключевых слов выглядят так:

15 - 7 продвинутых приемов для Excel, которые должен использовать каждый SEO-специалист

Справедливости ради, они выглядят так же при использовании Keyword Planner.

16 - 7 продвинутых приемов для Excel, которые должен использовать каждый SEO-специалист

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

Excel не может отсортировать мой список, основываясь на понятиях типа «10К» или «1М». Он не дружит с К и М. Ему нужны реальные цифры. К счастью, Excel имеет нечто вроде быстрой формулы, которая поможет вам отсортировать объемы должным образом.

Во-первых, замените К и М и преобразуйте их в «000» или «000000». Создайте новую колонку с названием «От меньшего к большему» или типа того.

17 - 7 продвинутых приемов для Excel, которые должен использовать каждый SEO-специалист

Выберите клетку в новой колонке и вставьте =SUBSTITUTE (=ЗАМЕНИТЬ):

18 - 7 продвинутых приемов для Excel, которые должен использовать каждый SEO-специалист

Ваша формула должна выглядеть примерно так:

=SUBSTITUTE(C2,”K”,”000”)

Номер клетки изменится согласно ряду, который вы конвертируете.

Вот как это выглядит после вставки формулы:

19 - 7 продвинутых приемов для Excel, которые должен использовать каждый SEO-специалист

И окончательные результаты:

20 - 7 продвинутых приемов для Excel, которые должен использовать каждый SEO-специалист

Можно также заменить М на «000000» при помощи той же формулы. Выглядеть будет вот так:

=SUBSTITUTE(C2,”M”,”000000”)

Так вы разберетесь со всеми К и М в таблице.

А еще можно выполнить оба действия одновременно (если у вас диапазон от 100К до 1М, например), используя следующую формулу (изменяя номер клетки):

=SUBSTITUTE(SUBSTITUTE(C2,”K”,”000”), “M”, “000000”)

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

Формула для минимума:

  21 - 7 продвинутых приемов для Excel, которые должен использовать каждый SEO-специалист

Максимума:

22 - 7 продвинутых приемов для Excel, которые должен использовать каждый SEO-специалист

И среднего показателя:

23 - 7 продвинутых приемов для Excel, которые должен использовать каждый SEO-специалист

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

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

Эти формулы призваны экономить ваше время и энергию, в конце концов.

4. Извлекайте определенные данные при помощи «REGEXTRACT» (“ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ ТАБЛИЦЫ”)

Конечно, ключевые слова – это не только данные в таблицах SEO.

Иногда вам придется сортировать URL, названия доменов, заголовки блогов или email адреса.

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

24 - 7 продвинутых приемов для Excel, которые должен использовать каждый SEO-специалист

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

Вместо этого, примените REGEXTRACT (ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ), чтобы извлечь конкретные данные из списка.

REGEXTRACT не похожа на другие Excel формулы. Она требует специального синтакса (строка кода) для работы.

Полная формула с синтаксом для REGEXTRACT выглядит так:

=REGEXEXTRACT(A2,”^(?:https?:\/\/)?(?:[^@\n]+@)?(?:www\.)?([^:\/\n]+)”)

Смешной кусочек в конце и есть синтакс.

Можно копировать и вставить полную формулу в новую колонку (изменяя номер колонки), и в результате вы получите вот что:

25 - 7 продвинутых приемов для Excel, которые должен использовать каждый SEO-специалист

Можно воспользоваться формулой ARRAY (МАССИВ), чтобы добавить это в несколько рядов сразу.

Все вместе выглядит так:

=IFERROR(ARRAYFORMULA(REGEXEXTRACT(A2:A,”^(?:https?:\/\/)?(?:[^@\n]+@)?(?:www\.)?([^:\/\n]+)”)),“”)

Громоздко, но работает.

Вот что получаем в нашем примере:   26 - 7 продвинутых приемов для Excel, которые должен использовать каждый SEO-специалист

И в конечном итоге:

27 - 7 продвинутых приемов для Excel, которые должен использовать каждый SEO-специалист

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

Если вы все еще не уверены в процессе или в том, как использовать синтаксис вместе с REGEXTRACT, у Ahrefs есть таблица, которая показывает, как работает эта формула.

Это просто палочка-выручалочка для тех, кто не испытывает особой нежности к Excel формулам.

5. Форматируйте теги названий с помощью формулы «PROPER» (“ПРОПНАЧ”)

Еще одно утомительное занятие, с которым вы можете столкнуться при работе с таблицами для SEO, это переформатирование данных.

Возможно, теги заголовков или ключевые слова были импортированы в нижнем регистре, а вам надо, чтобы они были записаны заглавными буквами.

28 - 7 продвинутых приемов для Excel, которые должен использовать каждый SEO-специалист

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

Или, по крайней мере, я надеюсь, что вы не собираетесь это делать, потому что можно легко сменить строчные буквы на заглавные одним скопом, используя формулу PROPER, которая выглядит вот так:

=PROPER(C2)

Просто, не так ли?

Создайте новую колонку и вставьте формулу туда. Вот так:

29 - 7 продвинутых приемов для Excel, которые должен использовать каждый SEO-специалист

И конечный результат:

30 - 7 продвинутых приемов для Excel, которые должен использовать каждый SEO-специалист

Если у вас много тегов, которые нужно записать заглавными буквами, это тоже сработает.

Можно искать отдельные теги, используя формулу HLOOKUP (ГПР) (вернемся к ней позже).

31 - 7 продвинутых приемов для Excel, которые должен использовать каждый SEO-специалист

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

32 - 7 продвинутых приемов для Excel, которые должен использовать каждый SEO-специалист

Если хотите изменить ключевое слово или тэн названия в нижнем регистре на верхний (например, акроним), воспользуйтесь формулой UPPER (ПРОПИСН). Вот так:

33 - 7 продвинутых приемов для Excel, которые должен использовать каждый SEO-специалист

Вы можете мне не верить, но формула LOWER (СТРОЧН) совершит обратное действие :)

34 - 7 продвинутых приемов для Excel, которые должен использовать каждый SEO-специалист

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

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

Так что вот вам быстрый и легкий способ решить этот вопрос.

6. Совершайте поиск в больших таблицах с помощью «VLOOKUP» (“ВПР”)

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

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

35 - 7 продвинутых приемов для Excel, которые должен использовать каждый SEO-специалист

Как я уже упоминал ранее, поисковые функции Excel ограничены, если ваши данные уже не организованы каким-то образом.

И тем тяжелее, чем больше у вас рядов и колонок с данными. Есть у Excel формула и для этого, куда ж без нее.

Это VLOOKUP (ВПР), которая выглядит так:

=VLOOKUP(A4;’Lookup Table’!A2:D1000;4;FALSE)

Если вы хотите найти специальное ключевое слово среди двух листов одной таблицы, то процесс таков:

7 продвинутых приемов для Excel, которые должен использовать каждый SEO-специалист  

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

Первая секция – это название того, что вы ищете. Поставьте его в кавычки:

36 - 7 продвинутых приемов для Excel, которые должен использовать каждый SEO-специалист

Затем добавьте наименование колонки.

37 - 7 продвинутых приемов для Excel, которые должен использовать каждый SEO-специалист

После этого добавьте порядковый номер колонки. Первая колонка – под цифрой 1 и так далее.

Так как я пытаюсь найти ключевые слова в первой колонке, то напишу цифру 1, а за ней добавлю слово TRUE, так как мне нужно точное совпадение.

38 - 7 продвинутых приемов для Excel, которые должен использовать каждый SEO-специалист

Можно также использовать FALSE, если точное совпадение необязательно.

Обратите внимание на то, что VLOOKUP (ВПР) работает только если необходимые вам данные находятся в первой колонке (крайняя левая), а данные расположены в алфавитном порядке.

Так что вам возможно придётся форматировать колонки перед поиском VLOOKUP (ВПР). Но формула все равно очень пригодится.

Ее также можно использовать для поиска цены продукта или специальной категории, как, например, здесь:

39 - 7 продвинутых приемов для Excel, которые должен использовать каждый SEO-специалист

Если нужно искать по рядам, используйте формулу HLOOKUP (ГПР).

V (В) для вертикального поиска и H (Г) для горизонтального.   40 - 7 продвинутых приемов для Excel, которые должен использовать каждый SEO-специалист

Пример формулы HLOOKUP:

=HLOOKUP(D3;’Lookup Table’!A1:D10;2;FALSE)

Тут вы найдете пример этой функции:

Оба пути быстро приведут вас к тому, что вы ищете, особенно если найти нужно среди множества рядов, колонок или страниц в документе.

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

41 - 7 продвинутых приемов для Excel, которые должен использовать каждый SEO-специалист

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

Немного тренировки, и вы освоите их на «отлично». Именно поэтому они во многом превосходят традиционное поисковое окно.

7. Быстро обнаруживайте дубликаты, используя «COUNTIF»(“СЧЁТЕСЛИ”)

Не все данные в таблице будут верными.

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

Формула Excel в данном случае – COUNTIF. И выглядит она так:

=COUNTIF(A:A,A2)

Допустим, у вас есть список элементов в первой колонке (колонка А), которые вы хотите проверить на дубликаты.

Во-первых, создайте новую колонку и вставьте формулу с номером клетки из колонки А, вот так:

42 - 7 продвинутых приемов для Excel, которые должен использовать каждый SEO-специалист

Затем перетащите ее и скопируйте в другие клетки. После этого увидите вот что:

43 - 7 продвинутых приемов для Excel, которые должен использовать каждый SEO-специалист

TRUE означает, что совпадения есть, а FALSE – что стопроцентных дубликатов нет. Эта формула находит только точные совпадения.

В примере выше вы видите, что даже если слова «seo strategy» показаны несколько раз (например, seo marketing strategy), то формула не сочтет это дубликатом.

Если вы просто хотите выделить эти дубликаты, можно сделать это при помощи условного форматирования.

Нажмите Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values

Можно выбрать цвет для дубликатов, если хотите просто выделить их, а не удалить.

Если хотите их удалить, нажмите Data > Remove Duplicates (Данные > Удалить Дубликаты), а затем под колонками выделите или снимите выделение с тех, в которых хотите удалить дубликаты.

44 - 7 продвинутых приемов для Excel, которые должен использовать каждый SEO-специалист

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

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

И формула COUNTIF (СЧЁТЕСЛИ) – одна из них.

Заключение

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

Но если вы используете таблицы – особенно Excel таблицы – для SEO или маркетингового исследования, то вам просто необходимо овладеть некоторыми из них.

В особенности, присмотритесь к тем, которые помогают при поиске или очистке данных и делают это быстро, как VLOOKUP (ВПР) или COUNTIF (СЧЁТТЕСЛИ).

Умение конвертировать показатели объема с помощью SUBSTITUTE (ЗАМЕНИТЬ) просто жизненно необходимо для поиска ключевых слов.

А научившись создавать более сложные (но нужные) элементы типа сводных таблиц, вы в итоге сэкономите себе кучу времени и энергии. SEO и так непростая штука.

Сбросьте с себя часть бремени, изучив несколько приемов пользования Excel.

 

Дарья Орлова

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

Оффер, CPA, Емейл, URL, SEO, SEO-специалист
Вопросы и комментарии:
Авторизуйтесь, чтобы оставлять комментарии