В своей работе SEO-специалисту необходимо анализировать большое количество данных из различных систем статистик, поэтому ему важно уметь пользоваться программой Excel.
В этой статье я покажу основные приёмы и функции, которые обязательно пригодятся при выполнении SEO-задач.
Все приёмы и функции будут сопровождаться примерами в аспекте SEO.
1.1. Преобразование диапазона в таблицу
1.2. Разделение по столбцам — как выделить раздел из URL
1.3. Фильтры — поиск Title по определённым словам, поиск страниц с 301 редиректом определённого раздела
1.4. Условное форматирование — подсветка запросов с трафиком выше среднего, подсветка значений от минимальных к максимальным, подсветка дублей
1.5. Итоговая строка – примеры подсчёта итогов
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 редиректом определённого раздела
Фильтрация позволяет быстро формировать выборки по необходимому признаку.
Есть три способа отфильтровать данные:
- Фильтрация по принципу простого поиска
- Выбор значений, по которым необходимо фильтровать данные
- Фильтрация по условиям
Применение в 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, дабы избавить вас от рутины и ускорить решение некоторых важных задач.