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-элементов, контента, ссылочного профиля и других факторов. В своей практике...
• 9 бесплатных SEO-инструментов при работе с сайтом
Бесплатные инструменты для технического анализа Парсинг Website auditor от SEO Powersuite Перелинковка PageWeight Lite Free Version Скорость gtmetrix.com batchspeed.com Бесплатные инструменты для анализа структуры сайта...
• 12 фичей Seolib, о которых вы, возможно, не знали
Введение Турбо и AMP-страницы Релевантные и ранжируемые страницы Отчёт по конкурентам Визуализация процента и количества запросов в ТОП-10, ТОП-11–50 Мониторинг ТОПов Синонимы по запросам Геозависимость...
• Обзор сервисов для съёма позиций сайта
Критерии оценки https://seolib.ru/ https://topvisor.com/ru/ https://seranking.ru/ https://ahrefs.com/ https://allpositions.ru/ https://ru.megaindex.com/ https://www.rush-analytics.ru/ https://www.semrush.com/ https://serpstat.com/ https://tools.pixelplus.ru/ https://line.pr-cy.ru/ https://serphunt.ru/ https://www.wincher.com/ Результаты Несколько месяцев назад, отвечая на вопрос в Телеграме, я...
• Как узнать частоту ключевого запроса в Яндекс и Google
Эта статья рассчитана на новичков в SEO, а также на владельцев сайтов, которые выбрали себе запросы для продвижения, но не знают, частотные ли это запросы....