Заявка на бесплатный seo аудит конкурентов
*Действительно бесплатна и ни к чему не обязывает
Если вы оставите свой телефон, мы вас проконсультируем по seo оптимизации вашего сайта
На e-mail мы вышлем для вас отчет по аудиту ваших конкурентов
  • »
  • »

Руководство по работе
с Excel и Google Таблицами
для SEO специалистов

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

Распространённый пример — кластеризация запросов в СЯ.

В этом деле незаменимый помощник — продукт Excel из стандартного пакета MS Office и Google Sheets.
Владея хотя бы основным функционалом таблиц, можно здорово упростить себе жизнь, не рискуя при этом потерять или оставить без внимания важную информацию.

Использование Excel и Google Tables помогает seo-специалистам существенно сократить временные затраты. Вместе с тем работа с таблицами требует определенных знаний. Наличие огромного количества функций может серьезно озадачить не только новичка.

Предлагаю разобрать наиболее интересные решения Excel и его сетевого брата для эффективной работы сеошника.

1. Удаление ссылок

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

В качестве примера приведу простое копирование текста из Яндекс. Вордстат. Конечно, лучше воспользоваться решением, которое предлагает браузер. В противном случае вставленные в таблицу ключевые слова будут содержать ссылку на источник. Но если такое произошло, не стоит отчаиваться или пытаться удалить лишний текст вручную.

Решить проблему можно проще и быстрее, для этого необходимо:
1. выделить все строки и столбцы с ненужными ссылками;
2. кликнуть правой кнопкой мыши по выделенному диапазону;
3. выбрать во всплывающем списке «Удалить гиперссылки».
сео эксель гугл таблицы сео

2. Удаление и замена данных

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

Например, символ «+» (купить + спб, окна + цена и т. д.). В этом случае поможет полезная в процессе seo-оптимизации функция «Найти и заменить», которая позволяет удалять определённый текст или знак сразу во всех ячейках диапазона.
seo эксель гугл таблица seo
Чтобы активировать функцию в Эксель, кликаем по меню Найти и выделить в главной вкладке — Заменить. Чтобы провести массовую зачистку в Гугл Таблицах, требуется кликнуть по вкладке Изменить и выбрать Найти и заменить.
В поле «Найти» вводим текст или символ, который необходимо удалить. В поле «Заменить» проставляем пробел. Далее нажимаем «Заменить все», и готово: ключевые слова в вашем списке больше не содержат лишних знаков или слов.
Точно также поступаем, если требуется заменить слово, словосочетание или знак во всех ячейках на что-то другой. В этом случае вместо пробела в поле «Заменить» вводим текст, которым нужно заменить исходный.

Функцию «Найти и заменить» также можно запустить с клавиатуры одновременным нажатием клавиш Ctrl и H.

3. Как удалить лишние пробелы

Составляя списки ключевых слов в таблицах Excel или Google Tables, многие сео-специалисты жалуются на невозможность корректной сортировки данных из-за присутствия лишних пробелов. Для решения этой проблемы стоит воспользоваться формулой =СЖПРОБЕЛЫ(). Между скобками в строке функции указывается адрес ячейки или диапазон, из которого требуется удалить пробелы.
seo excel

4. Сортировка данных

Составление семантики сайта в Excel и Google Таблицах удобно во многом потому, что в них возможна сортировка по какому-либо признаку, при этом соответствующие этому признаку данные также будут перемещены.

Наиболее распространённый вариант размещения данных для создания списка ключевых слов под семантическое ядро — в одном столбце указаны запросы, в другом – их частотность. Мы можем сделать сортировку по любому из признаков – названию по алфавиту (от А до Я или от Я до А) и частотности (от минимального до максимального числа запросов или наоборот). Для этого выделяем оба столбца, выбираем меню ДанныеСортировать диапазон по Столбцу X, А-Я, где X – название столбца, по которому необходимо произвести сортировку. Данные во втором выделенном столбце также будут перемещены, следуя за своим «соседом».
excel сео google таблица сео
В последней версии Excel вместо Сортировать диапазон по… в меню Данные необходимо выбрать подменю Сортировка, в открывшейся таблице указать номер сортируемого столбца и порядок сортировки.
Excel позволяет сортировать данные по цвету ячеек. Функция задаётся также с помощью подменю Сортировка в меню Данные. В поле «Сортировка» необходимо выбрать «Цвет ячейки», после этого ячейки выстроятся по цветам.

Google Sheets изначально не позволяют выполнить раскладку по цветам. Скачиваем расширение «Сортировка диапазона+», теперь мы имеем возможность выстраивать строки по цветам ячеек или текста и в Google Таблицах.

5. Поиск дублируемого текста

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

Выделяем нужную колонку в Экселе, выбираем меню Главная — подменю Условное форматированиеПравила выделения ячеекПовторяющиеся значения. Осталось определиться с выбором цветовой гаммы.
seo tools excel
В таблицах Гугла обнаружить и отметить повторы можно несколькими способами. Расскажу о двух из них.

1. Последовательно выбираем ФорматУсловное форматированиеДобавить правило — в Правилах форматирования кликаем «Ваша формула» — вводим в поле =И(НЕ(ЕПУСТО(XY)); СЧЁТЕСЛИ($X$Y:$Z; "=" & XY) > 1), где X — литера колонки, Y — номер строки с данными. Теперь все ячейки, содержащие повторные запросы, будут подсвечены.
google таблица seo google table seo
2. Установка приложения Remove Duplicates позволит справиться не только с поиском дублей. Это удобное и функциональное дополнение к Google Sheets, полезное для seo-специалистов.
Выделяем список, выбираем меню ДополненияRemove DuplicatesFind duplicates or uniques, отмечаем нужный тип данных — Duplicates.
google table сео
google таблица seo google table seo
При необходимости можно исключить пустые ячейки (Skip empty cells), задать поиск с учётом регистра (Case sensitive).
Далее выбираем действие:
· выделить ячейку цветом,
· создать дополнительный столбец для дублей,
· удалить или скопировать дубли,
· удалить ячейки с повторяющимися данными.
google sheets seo google sheets seo

6. Как удалить дубли запросов

Функционал Excel позволяет быстро удалить дублированные ячейки. Справиться с этой задачей поможет функция «Удалить дубли», которая находится в меню Данные.

В Гугл Таблицах такой функции нет. Здесь можно воспользоваться опцией выделения уникальных значений, которая задаётся функцией =(UNIQUE (X1:X100)), где X — название столбца с ключевыми словами. Функцию необходимо ввести в любую свободную ячейку.
семантика excel
семантическое ядро excel

7. Исключение минус-слов

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

Выбираем один из двух вариантов действий:
1. Находим минус-слова посредством опции «Условное форматирование» в главной вкладке Excel или вкладке Данные Google Sheets. Во всплывающем списке выбираем Правила выделения ячеекТекст содержит…, вставляем минус-слово, чтобы увидеть, где оно встречается. При этом желательно указывать слово без окончания, чтобы программа выделила все его варианты. Последний шаг – выбрать цвет заливки.
пример семантического ядра в excel
кластеризация семантического ядра в excel
2. Для исключения минус-слов из списка запросов удобно использовать фильтры. Фильтрация – весьма полезная опция, позволяющая отобразить только ячейки, в которых содержится определённая фраза, слово или символ. Нам требуется выделить колонку, содержащую ключевые слова, открыть вкладку меню Данные и выбрать подменю Фильтр в Эксель или Создать фильтр в Гугл Таблицах — Фильтровать по условию…Текст содержит.
В окошко вводим начальную часть минус-слова. После этих действий в таблице останутся только те ячейки, которые соответствуют данному правилу, то есть содержат ненужные нам слова. Далее просто удаляем эти ячейки и снимаем фильтр.
семантическое ядро эксель
семантика эксель семантическое ядро гугл таблицы

8. Закрепление строки

Когда мы работаем с большим объёмом данных, анализируемые списки не умещаются на экране, а большое количество задействованных колонок и похожие данные в них вносят сумбур в процесс, возможность закрепить строки сильно упрощает жизнь и экономит время. Что делаем? Выделяем нужную строку (например, верхнюю строку с названиями столбцов), кликаем по вкладке меню ВидЗакрепить1 строку. Теперь при прокрутке страницы вниз эта строка всегда будет перед глазами.

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

9. Замена регистра

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

Для этого можно воспользоваться одной из двух специальных формул:
  1. =ПРОПИСН(ЛЕВСИМВ(СТРОЧН(XY);1))&ПСТР(СТРОЧН(XY);2;ДЛСТР(XY)-1), где XY — координаты ячейки, чтобы первое слово начиналось с заглавной буквы;
  2. =СТРОЧН(XY), чтобы все слова в выбранном поле были написаны строчными буквами.
кластеризация эксель
кластеризация в excel

10. Расчёт знаков в мета-тегах

Важная опция, которая помогает при необходимости скорректировать текст в Title и Description, если его объём слишком большой.

Вводим в любую свободную ячейку формулу =ДЛСТР(XY), где XY — координаты ячейки, количество знаков в которой требуется рассчитать, и сразу получаем ответ.
    мета теги гугл таблицы

    11. Расчет запросов в ТОП-10

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

    Далее в любом свободном поле вводим формулу =СЧЁТЕСЛИ(XY:XZ;"<=10", где X – имя столбца, а Y и Z – номера начальной и конечной строки диапазона.

    Теперь известно, какое количество учтённых запросов занимают топовые позиции в поисковике.
      кластеризация данных в excel

      12. Отсчет срока, прошедшего с определенной даты

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

      В таблице указываем исходную дату (это может быть дата начала работы над проектом, размещения партнером ссылки на сайт и т.д.), в свободной ячейке вводим формулу =ЕСЛИ(ЕПУСТО(XY);; РАЗНДАТ(B2; СЕГОДНЯ(); "D")), где XY – адрес ячейки, в которой указаны число, месяц и год начала отсчёта. Вместо формулы появится количество дней, истекших с заданной даты.
        ся excel
        Таблицы Excel или Google – отличные инструменты для seo-оптимизации. Широкие возможности, которые дают эти программы, часто либо не используются по незнанию, либо вводят в тупик даже опытного специалиста.

        Мы с вами рассмотрели только часть полезных для seo функций, на самом же деле их в разы больше. Расскажите, какие ещё формулы вы используете в работе при кластеризации запросов в СЯ.
        Тест можно пройти несколько раз ;)
        Узнайте как вы усвоили статью.
        Нажми на кнопку.
        Проверить
        Чем может быть полезен Excel для сео специалиста?
        Далее
        Проверить
        Узнать результат
        Где еще помимо Эксель можно собирать и сортировать данные?
        Далее
        Проверить
        Узнать результат
        Ты собрал/а запросы из Яндекс. Вордстат, как теперь удалить гиперссылки и лишние символы?
        Далее
        Проверить
        Узнать результат
        Как быстро удалить лишние символы (например, "+" или "пробел") в Гугл Таблицах?
        Далее
        Проверить
        Узнать результат
        Можно ли в Excel сортировать данные по цвету?
        Далее
        Проверить
        Узнать результат
        При сборе ключевых запросов возможно повторение одних и тех же фраз. Как от них избавится?
        Далее
        Проверить
        Узнать результат
        Таблицы с ключевыми запросами могут быть масштабными. Что можно сделать для удобства работы с ними?
        Далее
        Проверить
        Узнать результат
        Title и Description каждой страницы ограничены в символах. Можно ли в Гугл Таблицах посчитать количество символов?
        Далее
        Проверить
        Узнать результат
        Что сделать, если хочешь посчитать количество запросов в ТОП-10?
        Далее
        Проверить
        Узнать результат
        Вы точно прочитали статью внимательно. Перечитайте ее еще раз и попробуйте пройти тест еще раз.
        Пройти еще раз
        Вы точно прочитали статью внимательно. Перечитайте ее еще раз и попробуйте пройти тест еще раз.
        Пройти еще раз
        Вы точно прочитали статью внимательно. Перечитайте ее еще раз и попробуйте пройти тест еще раз.
        Пройти еще раз
        Вы точно прочитали статью внимательно. Перечитайте ее еще раз и попробуйте пройти тест еще раз.
        Пройти еще раз
        Молодец! 90% материала освоено на УРА! Обрати внимание на ошибки и разберись, что не так.
        Пройти еще раз
        Идеально! Спасибо! Мне очень приятно, что меня услышали и поняли!
        Пройти еще раз
        У нас есть еще полезные статьи, читай скорее!
        Made on
        Tilda