Excel для SEO. Основы с примерами

Часто на решение ряда SEO-задач у начинающего специалиста уходит неоправданно много сил и времени. Зная о возможностях Excel в применении к SEO, вы сможете оптимизировать часть рутинных процессов и продуктивно продвигаться к решению поставленных задач.

 

1. Работа с таблицами

1.1. Преобразование диапазона в таблицу
1.2. Разделение по столбцам — как выделить раздел из URL
1.3. Фильтры — поиск Title по определённым словам, поиск страниц с 301 редиректом определённого раздела
1.4. Условное форматирование — подсветка запросов с трафиком выше среднего, подсветка значений от минимальных к максимальным, подсветка дублей
1.5. Итоговая строка – примеры подсчёта итогов

2. Основные функции

2.1. Функция ВПР (англ. VLOOKUP) — как получить количество посетителей у позиций, находящихся в ТОП-10
2.2. Функция ЕСЛИ — проверка соответствия продвигаемой страницы и той, что находится в поиске
2.3. Функция СЧЁТ ЕСЛИ — подсчёт количества запросов в ТОП-5, ТОП 5-10 и ТОП-10-50
2.4. Объединение ячеек — как найти данные нужного значения

 

1. Работа с таблицами

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

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

 

 

1.1. Преобразование диапазона в таблицу

В ленте навигации выбрать вкладку «Вставка» и нажать на иконку «Таблица».

Поставить галочку «Таблица с заголовками» и выбрать нужный диапазон с данными, затем нажать «Ок».

Получим таблицу такого вида:

 

 

1.2. Разделение по столбцам — как выделить раздел из URL

Инструмент «Разделение по столбцам», который находится во вкладке «Данные», позволяет делить данные одной ячейки на несколько столбцов по выбранному признаку: по запятой, пробелу, точке и т. д.

Применение в SEO

Если сайт имеет структуру разделов и если она отображается в URL, удобно анализировать каждый раздел отдельно.

К примеру, у нас есть выгрузка всех URL сайта, тогда:

1. Копируем столбец с URL и вставляем данные в новый лист

2. Выделяем полученный столбец, нажимаем в навигационной ленте на «Данные» — «Текст по столбцам» — «Далее»

3. В появившемся окне ставим галочку напротив «Другой» и в поле ставим слеш (/), нажимаем «Готово»

Excel разбил наши адреса на столбцы.

Здесь столбцы «С-E» содержат названия разделов. Если мы скопируем эти столбцы в нашу таблицу c URL, то получим группировку по разделам.

С применением фильтрации будет удобно анализировать каждый раздел отдельно.

 

 

1.3. Фильтры — поиск Title по определённым словам, поиск страниц с 301 редиректом определённого раздела

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

Есть три способа отфильтровать данные:

1. Фильтрация по принципу простого поиска
2. Выбор значений, по которым необходимо фильтровать данные
3. Фильтрация по условиям

Применение в SEO

Фильтрация по признаку поиска

Допустим, нам необходимо получить все URL, в Title которых встречается слово «ссылки». Для этого нам достаточно написать в поле поиска столбца «Title» слово «ссылки».

Выбор значений

В нашем файле есть данные по кодам ответов сервера, и мы решили посмотреть, какие страницы раздела «Blog» имеют 301 редирект.

Для этого нужно нажать на стрелку у столбца «Группа 1» и оставить галочку только у «Blog».

В столбце «StatusCode» выбираем «301». В итоге получаем результат:

 

 

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

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

Применение в SEO

Подсветка запросов, у которых посещение выше среднего

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

«Условное форматирование» — «Правила отбора первых и последних значений» — «Выше среднего»

Подсветка значений от минимального к максимальному

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

«Условное форматирование» — «Цветовые шкалы»

Чем ниже значение, тем более красным становится цвет.

Выделение дублей

У нас есть список URL с Title из Screaming Frog, и мы хотим подсветить одинаковые Title.

«Условное форматирование» — «Правила выделения ячеек» — «Повторяющиеся значения»

Теперь при желании можно отфильтровать данные по цвету.

Результат сортировки:

 

 

1.5. Итоговая строка – примеры подсчёта итогов

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

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

Применение в SEO

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

 

2. Основные функции

 

 

2.1. Функция ВПР (англ. VLOOKUP) — как получить количество посетителей у позиций, находящихся в ТОП-10

У нас есть список запросов с позициями и данные по трафику из Яндекс.Метрики. Мы хотим знать, какие из запросов, находящихся в ТОПе, приносят нам трафик.

В этом нам может помочь специальная функция ВПР, которая ищет указанное значение в крайнем левом столбце таблицы и возвращает значение ячейки указанного столбца той же строки.

Синтаксис функции: ВПР (значение_которое_ищем; таблица_в_которой_ищем; номер_столбца_нужного_значения; [интервальный просмотр])

Применение в SEO

1. Кликаем по ячейке, в которую должны подтягиваться данные по трафику (в нашем примере это ячейка D3). Вставляем формулу = ВПР(B3;$F$2:$G$12559;2;0).

Разберём формулу:

B3 – ключевое слово, которое необходимо найти в таблице «Трафик»;
;$F$2:$G$12559 – диапазон таблицы «Трафик». Знак «$» вставлен для закрепления диапазона, чтобы при протягивании формулы он не сдвигался;
2 – второй столбец таблицы «Трафик». Именно в этом столбце находятся данные по трафику;
0 – интервальный просмотр, может принимать значение «0» (поиск точного совпадения) или «1» (поиск приблизительного);

2. Нам нужен трафик запросов из ТОП-10. Отфильтруем данные столбца «Позиции» по условию меньше или равно 10.

В итоге получаем:

#Н/Д показывает, что нужное ключевое слово в таблице «Трафик» не было найдено — т. е. по нему нет трафика. #Н/Д можно заменить на «0». Для этого добавим в нашу функцию ещё одну: ЕСЛИОШИБКА (на англ. IFERROR).

Синтаксис функции: ЕСЛИОШИБКА (проверяемое_значение; значение_если_ошибка).

В нашем случае функция будет выглядеть следующим образом: ЕСЛИОШИБКА(ВПР(B3;$F$2:$G$12559;2;0);0)

 

 

2.2. Функция ЕСЛИ — проверка соответствия продвигаемой страницы и той, что находится в поиске

Функция «ЕСЛИ» проверяет выполнение условия. При его выполнении функция возвращает одно значение и другое, если условие не выполняется.

Синтаксис функции: ЕСЛИ (Условие (логическое_выражение); Значение_если_истина; Значение_если_ложь)

Применение в SEO

С помощью функции «ЕСЛИ» мы можем посмотреть, соответствует ли продвигаемая страница той, что ранжируется в поиске.

У нас есть список URL, которые ранжируются в поиске и URL и которые мы реально хотим продвигать.

В ячейке «C2» добавляем функцию =ЕСЛИ(A1=B1;1;0)

Разберём формулу:

A1=B1 – проверяем, равен ли целевой URL релевантному;
1 – выводим «1», если равен;
0 – выводим «0», если не равен.

 

 

2.3. Функция СЧЁТ ЕСЛИ — подсчёт количества запросов в ТОП-5, ТОП 5-10 и ТОП-10-50

Считает количество ячеек, удовлетворяющих условию.

Синтаксис функции: СЧЕТЕСЛИ (диапазон_значений; условие)

Применение в SEO

Эта функция поможет посчитать количество запросов из ТОП-5, ТОП-5-10 и ТОП-10-50.

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

Для ТОП-5 функция будет выглядеть так: = СЧЁТЕСЛИ($C$3:$C$423;»<=5″),

где $C$3:$C$423; — закреплённый диапазон с позициями, «<=5» — считать, если меньше или равно «5».

Для ТОП-5-10, чтобы вычислить, сколько запросов находится в диапазоне между «5» и «10» позицией, нужно подсчитать количество запросов ниже «10» и вычесть количество позиций ниже «5». Функция выглядит так: =СЧЁТЕСЛИ($C$3:$C$423;»<=10″) — СЧЁТЕСЛИ ($C$3:$C$423;»<5″)

Для ТОП-10-50 аналогично: =СЧЁТЕСЛИ($C$3:$C$423;»<=50″)-СЧЁТЕСЛИ($C$3:$C$423;»<10″

 

 

2.4. Объединение ячеек — как найти данные нужного значения

В работе достаточно часто требуется объединить несколько ячеек в одну. Это может понадобиться при формировании файла на отклонение ссылок в Disavow Tools или при формировании шаблонов для Title.

Есть несколько способов объединить ячейки:

Вариант 1:
Функция «СЦЕПИТЬ» (англ.CONCATENATE)
Синтаксис: СЦЕПИТЬ (текст1; текст2; …)

Вариант 2:
Использование &
Синтаксис: A1&B1, здесь &сцепить ячейки A1 и B1

Пример 1:

Пример 2:

Необходимо сцепить ячейки и добавить в конце предложения «: цены в СПб»

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

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

Полезные ссылки:
Официальная справка excel
Портал по изучению excel

 

Еще по теме:

• Инструменты для SEO-Junior, как ускорить выполнение задач — советы
Работа с семантикой Сбор маркеров Serpstat Системы Метрик Сбор облака запросов Несколько потоков в Key Collector Антикапча Чистка запросов Лемматизатор «Анализ групп» в Key Collector...
• Как узнать частоту ключевого запроса в Яндекс и Google
Эта статья рассчитана на новичков в SEO, а также на владельцев сайтов, которые выбрали себе запросы для продвижения, но не знают, частотные ли это запросы....
• Когда целесообразно проводить A/B тестирование и какие инструменты использовать?
Подскажите, надо ли при обновлении страницы сайта (добавлении, удалении блоков, изменение структуры страницы) проводить ab тестирование? Если да, то насколько оно необходимо? И какие инструменты...
• Какими инструментами можно массово проверить показатели доноров для закупа ссылок?
Какими доступными инструментами можно массово проверить все необходимые показатели доноров для закупа ссылок? Ответ Для массовой проверки показателей доноров для закупки ссылок существует множество инструментов,...
• Правильно ли отображается передача DR по Ahrefs при подклейке дропа к сайту и стоит ли на это ориентироваться?
Не пойму, клею к 1 сайту дроп с сылочным DR по ahrefs 15, получаю у своего сайта DR 12. Ну норм, причем вес уже через...