Excel в поисковом продвижении (SEO): как применять таблицы для семантики, структуры и аналитики

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

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

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

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

Что в Excel понадобится для SEO-задач

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

Инструмент	Где находится	Что делает	Как применять в SEO
«Отыскать и поменять»	Вкладка «Основная» либо Ctrl+H	Стремительно меняет знаки, слова либо фрагменты текста в избранном спектре	Удалить излишние знаки, поменять части URL, убрать .html в адресах, поправить однообразные фрагменты в тегах
«Удалить дубликаты»	Вкладка «Данные»	Находит повторяющиеся строчки и оставляет неповторимые значения	Очистить перечень запросов опосля объединения нескольких выгрузок
«Фильтр»	Вкладка «Данные»	Указывает строчки по избранному условию: слову, числу, спектру	Отыскать запросы с маркерами «приобрести», «стоимость», «отзывы»; убрать фразы с низкой частотностью
«Сортировка»	Вкладка «Данные»	Упорядочивает данные по одному либо нескольким столбцам	Расставить запросы по частотности, интенту, кластерам, позициям
«Текст по столбцам»	Вкладка «Данные»	Разделяет содержимое одной ячейки на несколько столбцов	Разобрать URL на части, поделить склеенные данные, разбить фразу по словам
«Сводная таблица»	Вкладка «Вставка»	Группирует данные и считает итоги	Посчитать суммарную частотность по кластерам, собрать отчет по позициям, сопоставить группы страничек
«Условное форматирование»	Вкладка «Основная»	Подсвечивает ячейки по данным правилам	Отметить рост и падение трафика, выделить группы запросов, зрительно показать конфигурации

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

Функция	Что делает	Где полезна в SEO
СЖПРОБЕЛЫ	Удаляет излишние пробелы	Очистка главных фраз опосля выгрузки
СТРОЧН	Переводит текст в нижний регистр	Приведение запросов к одному виду
ДЛСТР	Считает количество знаков	Проверка длины Title и Description, URL
ЕСЛИ	Возвращает итог по условию	Разметка запросов по интенту (намерению), частотности, росту либо падению
ПОИСК	Отыскивает слово либо фрагмент снутри текста	Поиск маркерных слов в главных фразах
ЕЧИСЛО	Инспектирует, является ли итог числом	Употребляется вкупе с ПОИСК при кластеризации и разметке
СУММПРОИЗВ	Суммирует результаты массивов	Помогает инспектировать сходу несколько маркеров в одной формуле
СУММЕСЛИ	Складывает числа по условию	Подсчет общей частотности по группам запросов либо разделам веб-сайта
СЧЁТЕСЛИ	Считает ячейки по данному условию	Подсчет количества запросов в кластере, страничек с ошибками, URL определенного типа
ВПР	Отыскивает значение в иной таблице	Подтягивание позиций, трафика, частотности из отдельных файлов
ЕСЛИОШИБКА	Указывает данный текст заместо ошибки	Подмена #Н/Д на «Нет данных» при сведении таблиц
ИНДЕКС	Возвращает значение из спектра	Употребляется при кластеризации через отдельный лист маркеров
ПОИСКПОЗ	Находит позицию значения в спектре	Работает вкупе с ИНДЕКС
СЦЕПИТЬ	Соединяет воединыжды текст из нескольких ячеек	Сборка URL, шаблонов Title, фраз из отдельных слов
ОКРУГЛ	Округляет число	Подготовка осторожных отчетов без излишних символов опосля запятой

Как устроены формулы в Excel

Формула в Excel – это {инструкция} для обработки данных. Она гласит программке, что взять, как обработать и какой итог вывести.

К примеру:

=СЖПРОБЕЛЫ(A1)

В данной нам формуле есть три части:

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

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

A1 – аргумент функции. В этом случае это адресок ячейки, из которой Excel берет текст.

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

• ссылка на ячейку: (A1);

• текст: («приобрести»);

• число: (0);

• иная функция: (СТРОЧН(A1)).

Если аргументов несколько, в российской версии Excel они обычно делятся точкой с запятой. К примеру:

=ВПР(A2;D:E;2;0)

Тут четыре аргумента:

• значение, которое необходимо отыскать – D4;

• спектр, где идет поиск – A:K;

• номер столбца, из которого необходимо возвратить данные – 4;

• четкое совпадение – 0.

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

Работа с семантикой в Excel

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

Как загрузить главные фразы

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

Чтоб импортировать CSV:

1. Откройте вкладку «Данные».

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

3. Найдите скачанный файл.

4. В окне предпросмотра укажите шифровку «Юникод (UTF-8)».

5. Изберите разделитель: запятую либо точку с запятой.

6. Проверьте, что фразы и частотность попали в различные столбцы.

7. Нажмите «Загрузить».

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

Опосля объединения отсортируйте перечень по частотности:

1. Выделите столбцы с запросами и частотностью.

2. Перейдите во вкладку «Данные» → «Сортировка».

3. Изберите столбец с частотностью, к примеру «Число запросов».

4. Укажите порядок «по убыванию».

5. Нажмите «Ок».

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

Чистка перечня: пробелы, дубли и мусорные фразы

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

Начнем с излишних пробелов. Для этого используем функцию СЖПРОБЕЛЫ.

1. Найдите вольный столбец справа от основного перечня.

2. В первой ячейке напротив главный фразы введите формулу:

=СЖПРОБЕЛЫ(A2)

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

3. Нажмите Enter.

4. Потяните формулу вниз за правый нижний угол ячейки.

5. Excel применит ее ко всем строчкам.

Опосля этого скопируйте очищенные фразы и вставьте их поверх начального столбца через «Значения»: правая клавиша мыши → значок 123.

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

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

Далее удаляем повторы:

1. Выделите столбец с фразами.

2. Откройте вкладку «Данные».

3. Нажмите «Удалить дубликаты».

4. Изберите «Сортировать в границах выделения».

5. Проверьте, что отмечен подходящий столбец.

6. Нажмите «Ок».

Сейчас уберем нерелевантные запросы. К примеру, интернет-магазину не необходимы фразы со словами «безвозмездно», «скачать», «торрент», также бренды, которых нет в продаже.

Для этого комфортно применять фильтр:

1. Выделите столбец с фразами.

2. Откройте вкладку «Данные» → «Фильтр».

3. Нажмите стрелку в заголовке столбца.

4. Введите в поиск ненужное слово.

5. Отметьте отысканные варианты и нажмите «Ок».

6. Удалите отфильтрованные строчки вкупе с частотностью.

7. Опять откройте фильтр и изберите «Выделить все».

Повторите эти деяния для всякого стоп-слова. Так перечень станет чище, а предстоящая группировка – поточнее.

Как поделить запросы по интентам (намерению)

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

К примеру:

• «приобрести корм для кошек» – коммерческий запрос;

• «как избрать корм для котенка» – информационный;

• «корм для собак» – общий товарный запрос, который почаще относится к коммерческим.

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

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

Как работает эта формула:

• ПОИСК инспектирует, есть ли снутри фразы из ячейки A2 обозначенные маркеры;

• если слово найдено, Excel возвращает номер его позиции;

• если совпадения нет, возникает ошибка;

• ЕЧИСЛО описывает, где итог является числом;

• двойной минус превращает ИСТИНА и ЛОЖЬ в 1 и 0;

• СУММПРОИЗВ складывает получившиеся единицы;

• если сумма больше нуля, означает найден хотя бы один маркер.

В формуле поначалу проверяются информационные маркеры, а уже позже коммерческие. Это принципиально. Слово «корм» может встречаться и в товарных, и в информационных запросах. Если поставить коммерческий блок первым, фраза «как избрать корм для кошки» может неверно попасть в коммерцию. Потому информационные формулировки отсеиваются ранее.

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

Рядом с каждой фразой покажется интент: «Информационный», «Коммерческий» либо «Общий».

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

Как вынести маркеры на отдельный лист

Если маркеров много, формула становится очень длинноватой и неловкой. В этом случае лучше сделать отдельный лист.

1. Добавьте новейший лист и назовите его «Маркеры».

2. В столбец A занесите информационные маркеры.

3. В столбец B – коммерческие.

К примеру:

• A: «как избрать», «рейтинг», «обзор», «сопоставление»;

• B: «приобрести», «стоимость», «заказать», «доставка», «в наличии».

На основном листе используйте формулу:

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

Сейчас формула обращается не к списку слов снутри себя, а к спектрам на листе «Маркеры». Если пригодится добавить новейший маркер, довольно вписать его в таблицу. Формулу поменять не придется.

Опосля разметки удобнее работать с интентами раздельно. К примеру, информационные запросы можно перенести на лист «Блог» либо «Статьи»:

1. Включите фильтр в столбце «Интент».

2. Изберите значение «Информационный».

3. Скопируйте видимые строчки.

4. Вставьте их на новейший лист.

5. На основном листе удалите эти строчки.

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

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

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

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

Сделайте лист «Кластеры»:

• в столбце A укажите маркеры;

• в столбце B – наименования кластеров.

Лучше применять маркеры без окончаний, чтоб захватывать различные словоформы:

• «кошк» → «Корм для кошек»;

• «котен» → «Корм для котят»;

• «щенк» → «Корм для щенков»;

• «лечеб» → «Целебный корм»;

• «при мочекамен» → «Корм при мочекаменной заболевания».

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

На основном листе в вольном столбце введите формулу:

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

Формула отыскивает совпадение меж фразой и маркерами на листе «Кластеры», а потом возвращает заглавие первого отысканного кластера. Если совпадений нет, Excel выводит «Общее».

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

Как посчитать частотность по кластерам

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

1. Выделите таблицу с колонками «Фраза», «Частотность», «Интент», «Кластер».

2. Откройте вкладку «Вставка».

3. Нажмите «Сводная таблица».

4. Изберите вариант «Из таблицы либо спектра».

5. В правой панели перетащите поле «Кластер» в область «Строчки».

6. Поле «Число запросов» либо «Частотность» перетащите в область «Значения».

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

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

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

Как приготовить структуру веб-сайта

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

Поначалу отсортируйте таблицу:

1. Выделите все данные.

2. Перейдите во вкладку «Данные» → «Сортировка».

3. Добавьте 1-ый уровень сортировки: «Кластер», от А до Я.

4. Добавьте 2-ой уровень: «Число запросов», по убыванию.

5. Нажмите «Ок».

Excel сгруппирует фразы по кластерам, а снутри каждой группы поставит самые частотные запросы выше других. Верхняя фраза в кластере обычно становится основным ключом странички. Ее можно применять как базу для Title, H1 и URL.

Далее перенесите главные ключи на отдельный лист. Это будет черновик структуры веб-сайта.

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

• «Корм для щенков» относится к разделу «Корм для собак»;

• «Целебный корм для кошек» относится к разделу «Корм для кошек»;

• если страничка не вложена в иной раздел, можно поставить прочерк.

Потом сформируйте URL. Их можно приготовить через сервисы транслитерации либо отдельные инструменты для генерации адресов. Опосля этого возвратите итог в таблицу.

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

Аналитика в Excel: как соединять воединыжды данные и созодать отчеты

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

Как подтянуть позиции к списку запросов

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

Для этого используем ВПР.

1. Сделайте новейший лист «Позиции».

2. Скопируйте туда два столбца: фраза и позиция.

3. Вернитесь на главный лист.

4. В вольную ячейку рядом с первой фразой вставьте формулу:

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

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

Протяните формулу вниз до конца перечня.

Как создать отчет по группам позиций

Опосля того как позиции подтянуты, их можно поделить по зонам видимости: топ-3, топ-10, топ-30 и ниже топ-30.

В вольном столбце введите формулу:

=ЕСЛИ(C2