Как использовать Excel для SEO: пошаговая инструкция с формулами

В Excel можно работать с семантикой, структурировать данные и сводить аналитику по проекту. Это привычный и, что немаловажно, бесплатный инструмент SEO-специалиста, который помогает в продвижении сайта.

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

Оглавление

• Инструменты, функции и формулы Excel для SEO
• Как работать с семантикой в Excel
• Импорт ключевых слов
• Очистка и нормализация
• Группировка запросов по интентам
• Кластеризация запросов
• Подготовка структуры страниц
• Аналитика в Excel
• Сводим данные из разных сервисов
• Отчет по позициям
• Сравниваем периоды
• Шаг 1. Готовим листы
• Шаг 2. Собираем лист «Сравнение»
• Шаг 3. Добавляем динамику
• Шаг 4. Считаем проценты
• Шаг 5. Визуализация
• Краткий итог

Инструменты, функции и формулы Excel для SEO

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

Для более тонкой работы с данными используют функции. Это встроенные программы с уникальным именем. В Эксель их сотни, для SEO можно выделить следующие:

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

Разберем устройство формулы на простом примере.

Возьмем: =СЖПРОБЕЛЫ(A1). Здесь три элемента:

• = — начало любой формулы, без него Эксель воспримет текст как обычную строку;
• СЖПРОБЕЛЫ — имя функции (убирает лишние пробелы);
• (A1) — аргумент в скобках (ссылка на ячейку A1, откуда функция берет текст для обработки).

Аргументами могут быть:

• ссылка на ячейку — (A1);
• текст в кавычках — ("купить");
• число — (0);
• другая функция — (СТРОЧН(A1)).

Если аргументов несколько, их перечисляют через точку с запятой. Например, =ВПР(A2;D:E;2;0) — здесь четыре аргумента. Эксель понимает, за что отвечает каждый, по его позиции внутри скобок. У функции ВПР порядок аргументов такой:

• первый — что ищем (A2);
• второй — где ищем (D:E);
• третий — из какого столбца взять данные (2);
• четвертый — 0 для точного совпадения (или 1 для приблизительного).

У разных функций свой порядок аргументов. Перед использованием новой всегда проверяйте ее синтаксис во всплывающей подсказке.

Как работать с семантикой в Excel

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

Импорт ключевых слов

Выгружать данные из SEO-сервисов проще в XLSX-формате. Он открывается сразу. CSV тоже подойдет, но его нужно правильно импортировать:

• Открываем вкладку «Данные» → «Получить данные» → «Из файла» → «Из текстового/CSV».

• Выбираем скачанный файл.
• В предпросмотре устанавливаем кодировку «Юникод (UTF-8)» и выбираем разделитель: запятую или точку с запятой, в зависимости от того, как разделены данные в исходном файле.
• Смотрим, чтобы в предпросмотре фразы и частотность разъехались по разным столбцам, нажимаем «Загрузить».

Чтобы охватить все формулировки, запросы выгружаем из нескольких масок. Данные собираем в общую таблицу. Готовый список сортируем по убыванию частотности:

• Выделяем столбцы с фразами и частотностью.
• Вкладка «Данные» → «Сортировка» → сортировать по столбцу «Число запросов» (ваше название столбца), порядок — по убыванию.
• Нажимаем «Ок».

Получаем такую таблицу. Самые популярные запросы вверху списка.

В конце списка будут фразы с околонулевой частотностью — их лучше сразу удалить. Они редко приносят трафик и не стоят усилий.

Читайте также:

12 нейросетей для SEO: какие сервисы помогут продвигать сайт

Очистка и нормализация

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

Лишние пробелы уберем функцией СЖПРОБЕЛЫ. Она удаляет начальные, конечные и двойные пробелы за один проход.

• Находим свободный столбец справа от данных (у нас столбец C).
• В его первой ячейке напротив первой фразы вводим формулу: =СЖПРОБЕЛЫ(A2). Здесь A2 — это ячейка с первой фразой в вашем списке. Если у вас данные начинаются с другой строки или столбца, укажите свой адрес.

• Нажимаем Enter — в ячейке появится очищенная фраза.
• Наводим курсор на правый нижний угол ячейки с формулой, зажимаем левую кнопку мыши и тянем вниз до последней строки с данными.

• Отпускаем — формула применилась ко всем фразам.

• Копируем результат и вставляем в столбец A поверх исходных фраз через «Значения» (правая кнопка → иконка 123).
• Вспомогательный столбец C удаляем.

Почему мы вставили результат через «Значения»? После использования формул результат обычно заменяют на значения (правая кнопка → иконка 123). Если оставить формулы, они могут сломаться при изменении или удалении вспомогательных столбцов.

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

Дубли удаляем встроенным инструментом:

• Выделяем столбец с фразами.
• Переходим во вкладку «Данные» → «Удалить дубликаты» → «Сортировать в пределах выделения» → «Удалить дубликаты».

• В открывшемся окне проверяем, чтобы столбец, в котором размещены фразы, был отмечен и нажимаем «Ок».

Нерелевантные фразы, например, «бесплатно», «скачать», бренды, которых нет в ассортименте, удалим с помощью фильтра.

• Выделяем столбец с фразами → вкладка «Данные» → «Фильтр».
• В первой ячейке столбца появится стрелка — нажимаем ее.

• Откроется окошко. В строке поиска вбиваем нерелевантное слово, отмечаем все найденные варианты → «Ок».

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

• Снова открываем фильтр → «Выделить все» → «Ок».

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

Группировка запросов по интентам

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

Будем использовать формулу с маркерными словами внутри:

=ЕСЛИ(СУММПРОИЗВ(—ЕЧИСЛО(ПОИСК({"как выбрать";"какой корм";"рейтинг";"обзор";"чем кормить";"сколько давать";"норма";"лучше";"сравнение";"состав";"можно ли";"вреден ли";"как перевести"};A2)))>0;"Информационный";ЕСЛИ(СУММПРОИЗВ(—ЕЧИСЛО(ПОИСК({"купить";"цена";"заказать";"доставк";"недорого";"скидк";"в наличии";"интернет магазин";"стоимость";"акци";"премиум";"влажн";"сух";"корм"};A2)))>0;"Коммерческий";"Общий"))

• В фигурных скобках — маркерные слова: в первом блоке информационные, во втором — коммерческие.
• Функция ПОИСК проверяет, встречается ли каждое маркерное слово внутри фразы в ячейке A2. Если находит — возвращает номер позиции, если нет — ошибку.
• ЕЧИСЛО превращает результат в ИСТИНА или ЛОЖЬ. Два минуса переводят их в 1 или 0.
• СУММПРОИЗВ суммирует эти единицы. Если сумма больше нуля — хотя бы один маркер из блока найден.

Фразы вроде «корм для кошек» или «корм для собак» не содержат явных маркеров — ни коммерческих, ни информационных. Но по смыслу это товарные запросы: пользователь ищет продукт, даже если не написал «купить». Поэтому мы добавили слово «корм» в коммерческий блок.

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

Вводим формулу в свободную ячейку:

Протягиваем до конца списка — напротив каждого запроса теперь указан интент.

Часть фраз может попасть в «Общие»: для них маркеров не нашлось. Чтобы продвижение сайта было эффективным, такие ключи проверяют вручную.

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

• Создаем новый лист «Маркеры»: столбец A — информационный интент, столбец B — коммерческий.

• Возвращаемся на лист с запросами и вводим формулу:

=ЕСЛИ(СУММПРОИЗВ(—ЕЧИСЛО(ПОИСК(Маркеры!$A$2:$A$14;A2)))>0;"Информационный";ЕСЛИ(СУММПРОИЗВ(—ЕЧИСЛО(ПОИСК(Маркеры!$B$2:$B$14;A2)))>0;"Коммерческий";"Общий")).

Принцип тот же, но маркеры теперь ссылаются на диапазоны листа «Маркеры». Если понадобится добавить слово, достаточно дополнить список маркеров — формулу менять не нужно.

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

• В столбце «Интент» включаем фильтр, выбираем «Информационный» → Ок.

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

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

Кластеризация запросов

Распределим фразы по темам. Принцип тот же, что с интентами, но формула другая: ИНДЕКС + ПОИСКПОЗ вместо ЕСЛИ. Интентов бывает два-три, и ЕСЛИ хватает. Кластеров может быть двадцать и более — цепочка вложенных ЕСЛИ станет нечитаемой. Формула с ИНДЕКС + ПОИСКПОЗ сама проходит по всему списку маркеров и возвращает первый совпавший кластер, сколько бы их ни было.

Создаем лист «Кластеры»:

• В столбце A — маркерные слова без окончаний, чтобы захватить все словоформы: «кошек», «котен», «щенк», «лечеб».
• В столбце B — названия кластеров: «Корм для кошек», «Корм для котят», «Корм для щенков», «Лечебный корм».

Специфичные маркеры («котен», «щенк», «лечеб», «при мочекамен») ставим выше, а общие («кошк», «собак») — ниже. ИНДЕКС+ПОИСКПОЗ обрабатывает список сверху вниз. Если общий маркер окажется выше, он перехватит фразу, и она попадет не в свой кластер. Например, «лечебный корм для кошек» уйдет в «Корм для кошек» вместо «Лечебный корм», если «кошк» стоит раньше «лечеб».

Переходим на основной лист, в свободной ячейке вводим формулу:

=ЕСЛИОШИБКА(ИНДЕКС(Кластеры!$B$1:$B$23;ПОИСКПОЗ(ИСТИНА;ЕЧИСЛО(ПОИСК(Кластеры!$A$1:$A$23;A2));0));"Общее")

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

После этого можно подвести итоги. Выделяем все столбцы с данными: «Фраза», «Частотность», «Интент», «Кластер». Переходим во вкладку «Вставка» → «Сводная таблица» → «Из таблицы или диапазона».

Откроется сводная таблица. В правой панели настраиваем структуру отчета: какие данные пойдут в строки, а какие в расчеты. Перетаскиваем мышкой слово «Кластер» в область «Строки», а слово «Число запросов» — в область «Значения». Программа автоматически подсчитает сумму частотности по каждому кластеру.

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

Для большей точности кластеризацию делают по выдаче. Для каждой фразы собирают топ-10 URL, и если у двух запросов совпадает 4 и более адресов, значит, они в одном кластере. Реализовать это в Excel сложно, поэтому на практике применяют SEO-сервисы. Такой инструмент есть и в PromoPult. Как работать с ним подробно рассказали в этом гайде.

Реклама. ООО «Клик.ру», ИНН:7743771327, ERID: 2Vtzqwe1T69

Excel остается для финальной доработки. Фильтрами проверяют спорные фразы, через ИНДЕКС и ПОИСКПОЗ подтягивают названия кластеров из готовой структуры в сырой список, а в сводных таблицах собирают итоговую картину.

Подготовка структуры страниц

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

Сначала отсортируем список. Выделяем всю таблицу → «Данные» → «Сортировка». Настраиваем два уровня:

• Первый: столбец «Кластер», от А до Я.
• Второй: столбец «Число запросов», по убыванию.

Таблица перестроится: фразы соберутся по кластерам, а внутри каждого кластера выстроятся от самой частотной к самой редкой. Первая фраза в кластере — это главный ключ. Именно он задает тему страницы и пойдет в title.

Копируем строки с главным ключом на новый лист — это основа структуры. Добавляем столбец «Родительский раздел» и указываем, к какому родительскому разделу относится каждый дочерний. Например, «Корм для щенков» — это подраздел, для которого родительским будет раздел «Корм для собак». Если страница самостоятельная и никуда не вложена, ставим прочерк.

Через сервисы транслитерации получаем URL и возвращаем результат в таблицу. Теперь у нас есть готовая иерархия: понятно, какие страницы создавать на сайте, как они связаны и какие у них адреса.

Читайте также:

15+ возможностей .htaccess, о которых вы могли не знать

Аналитика в Excel

Главное преимущество Эксель — это возможность объединить данные из разных сервисов в единую таблицу и настраивать аналитику без ограничений. Разберем несколько сценариев использования.

Сводим данные из разных сервисов

Свести данные можно через функцию ВПР. Допустим, в Экселе есть основной файл со списком ключевых фраз и частотностью. Мы хотим добавить к каждой фразе ее позицию в поиске. Отчет с позициями скачан отдельно из другого сервиса.

• Создаем новый лист «Позиции» и копируем в него фразы с позициями.
• На основном листе в ячейку C2 вставляем формулу:

=ЕСЛИОШИБКА(ВПР(A2;Позиции!$A$2:$B$21;2;0);"Нет данных")

• Тянем вниз — напротив каждой фразы появится ее позиция. Для фраз, которых нет в листе с позициями, отобразится «Нет данных».

Отчет по позициям

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

1. Группируем позиции. На основном листе в свободную ячейку (у нас D2) введем формулу группировки:

=ЕСЛИ(C2<=3;"ТОП-3";ЕСЛИ(C2<=10;"ТОП-10";ЕСЛИ(C2<=30;"ТОП-30";"Ниже ТОП-30")))

Тянем вниз до конца списка и получаем для каждой фразы ее группу видимости (топ-3, топ-10, топ-30).

2. Строим сводную таблицу: «Вставка» → «Сводная таблица». В правой панели перетаскиваем группы позиций (у нас поле «Топ») в область «Строки», а поле «Фраза» — в область «Значения». Программа подсчитает, сколько запросов в каждой группе.

3. Добавляем проценты. Перетаскиваем поле «Фраза» в «Значения» еще раз. Щелкаем по нему → «Параметры поля значений» → вкладка «Дополнительные вычисления» → «% от общей суммы».

Теперь в таблице отображается и количество фраз, и их доля в процентах от общего числа запросов.

4. Строим диаграмму. Выделяем сводную таблицу с группами позиций → «Вставка» → «Круговая диаграмма».

Получаем диаграмму: сразу видно, какой процент запросов находится в топ-3 и топ-10.

Со временем данные устаревают: появляются новые запросы, меняются позиции. Исходный список можно дополнять. Но чтобы сводная таблица отразила изменения, ее нужно обновить. Щелкаем правой кнопкой по любой ячейке → «Обновить». Эксель подтянет изменения и пересчитает итоги.

Сравниваем периоды

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

Шаг 1. Готовим листы

В одном файле создаем три листа: «Май», «Июнь», «Сравнение». На листы «Май» и «Июнь» копируем выгрузки трафика из систем аналитики: столбец A — страницы, столбец B — трафик.

Шаг 2. Собираем лист «Сравнение»

Переходим на лист «Сравнение». В A1 вписываем «Страница», в B1 — «Май», в C1 — «Июнь». Копируем все URL с листа «Май» в столбец A листа «Сравнение». В ячейку B2 вставляем формулу:

=ЕСЛИОШИБКА(ВПР(A2;Май!$A$2:$B$13;2;0);"Нет данных")

Тянем вниз — Эксель добавит трафик за май.

В ячейку C2 вставляем такую же формулу, заменив «Май» на «Июнь»: =ЕСЛИОШИБКА(ВПР(A2;Июнь!$A$2:$B$13;2;0);"Нет данных"). Теперь у нас три заполненных столбца: адреса страниц и трафик за оба месяца.

Шаг 3. Добавляем динамику

В D1 вписываем «Динамика», D2 — формулу:

=ЕСЛИ(C2>B2;"Рост";ЕСЛИ(C2=B2;"Без изменений";"Падение")).

Тянем вниз и получаем наглядную картину: по каждой странице видно, вырос трафик или упал.

Шаг 4. Считаем проценты

В E1 вписываем «Изменение, %». В E2 вставляем формулу: =(C2-B2)/B2*100. Тянем вниз и получаем столбец с динамикой, измеренной в процентах.

Если после запятой много цифр, округляем с помощью встроенного инструмента. Выделяем столбец E → вкладка «Главная» → иконка «Уменьшить разрядность» (ноль с синей стрелкой) → нажимаем, пока не останется целое число.

Шаг 5. Визуализация

Выделяем столбец D → «Главная» → «Условное форматирование» → «Правила выделения ячеек» → «Текст содержит».

В открывшемся окне указываем для слова «Рост» зеленую заливку. Повторяем действия для слова «Падение» → красная заливка.

Выделяем столбец E → «Главная» → «Условное форматирование» → «Цветовые шкалы» → «Другие правила».

В открывшемся окне выбираем «Трехцветная шкала» и настраиваем:

• Для «Минимум»: тип «Наименьшее значение», цвет — красный.
• Для «Середина»: тип «Процентиль», значение 50, цвет — белый.
• Для «Максимум»: тип «Наибольшее значение», цвет — зеленый.

Столбец раскрасится: отрицательные числа уйдут в красный, положительные — в зеленый, околонулевые останутся белыми. Чем сильнее рост или падение, тем ярче цвет.

Краткий итог

Мы разобрали базовый набор инструментов для SEO в Excel: ВПР, логические и суммирующие функции, сводные таблицы. Главное — не пытаться закрыть все задачи по продвижению сайта одной программой. Оптимальный стек выглядит так: специализированные сервисы работают как быстрые «комбайны» по сбору и кластеризации, а Эксель служит хирургическим инструментом для финальной обработки и сборки отчетов. Такой подход повышает эффективность работы и значительно экономит время.

Если нет времени или желания погружаться в тонкости Excel, подключайте модуль SEO в PromoPult. Здесь умные помощники собирают и кластеризуют семантику автоматически, а результаты продвижения сразу видны на наглядных дашбордах — не нужно знать формулы и функции. В проектах динамического SEO ИИ-алгоритм постоянно меняет ключевые слова, чтобы продвигать сайт по запросам, которые принесут максимум трафика и конверсий. Протестировать SEO в PromoPult можно бесплатно за 2 недели.

Реклама. ООО «Клик.ру», ИНН:7743771327, ERID: 2Vtzqwe1T69