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

Для чего seo специалисту нужно уметь работать с Excel

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

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

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

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

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

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

Например, при работе с Яндекс.Вордстатом вы не использовали браузерное расширение yandex wordstat assistant (с которым однозначно работать удобнее), а просто скопировали запросы и вставили в таблицу. Тогда каждое ключевое слово будет содержать ссылку на сервис и затруднять работу, поэтому нужно преобразовать их в обычный текст.

 Выделяем необходимый диапазон, кликаем правой кнопкой мышки и нажимаем “Удалить гиперссылки”.

Удаление ссылок в семантическом ядре

2. Замена символов и элементов в таблице

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

В Google таблицах она находится во вкладке “Изменить”, в таблицах Microsoft Excel в главном разделе “Найти и выделить > Заменить”. Или можно просто нажать комбинацию клавиш Ctrl+H. В открывшемся диалоговом окне в поле “Найти” пишем знак +, поле “Заменить” оставляем пустым. Нажимаем “Заменить все” и получаем список ключевых слов без дополнительных операторов.

Зачем seo специалисту функция Excel найти и заменить

3. Удаление лишних пробелов

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

Лишние пробелы при группировке запросов в семантике

4. Сортировка по частоте или алфавиту

У вас имеется две колонки с данными. В одной - это список ключевых слов, во второй - их частотность. Если необходимо совершить сортировку запросов в зависимости от их частотности, выделяем эти две колонки, изначально поставив курсор на колонку с частотностью и растянув выделение ниже. Далее нажимаем “Данные > Сортировать диапазон по столбцу B, А-Я”.

Группирование ключевых слов по частотности

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

Сортировка запросов в семантике по алфавиту

5. Поиск и выделение дубликатов ячеек

Seo специалисту эта функция будет очень полезна при группировке запросов в семантическом ядре, она позволит быстро найти повторяющиеся ключевые слова в таблице.
 
В Excel выделяем столбец, в котором хотим найти дубликаты, далее на Главной вкладке нажимаем “Условное форматирование > Правила выделения ячеек > Повторяющиеся значения”. Выбираем цвет заливки ячеек и готово. 

Использование условного форматирования в seo

В Google Таблицах это выполняется немного сложнее и есть несколько способов решения данной задачи

Например, можно выполнить следующие действия: “Формат > Условное форматирование > Добавить правило > В разделе “Правила форматирования” выбираем “Ваша формула” и вставляем вот такую формулу без кавычек “=И(НЕ(ЕПУСТО(A1)); СЧЁТЕСЛИ($A$1:$Z; "=" & A1) > 1)”. При вводе этой комбинации не будут учитываться пустые ячейки, а запросы, которые указаны в выбранном диапазоне более одного раза будут подсвечены. 

Выделение повторяющихся ключевых слов в Google таблицах

Второй способ может сразу решить несколько задач, которые необходимо выполнять SEO специалисту при группировке запросов в семантическом ядре - установить браузерное расширение Remove Duplicates (оно доступно только на английском языке).

Расширение для гугл таблиц для поиска дублей ключевых слов

С его помощью можно выделить или сразу удалить повторяющиеся ячейки. После установки, чтобы воспользоваться им выделяем нужный нам диапазон ячеек, нажимаем “Дополнения > Remove Duplicate > Find duplicates or uniques”.

Расширение для поиска дублей запросов фото 1

Можем создать резервную копию этого листа, поставив галочку напротив “Create a backup copy of the sheet”. Далее выберете тип данных, по которым необходимо будет отбирать ячейки, например, дубликаты, исключая первое вхождение.

Как использовать функцию поиска дублей в seo

Если в выбранном диапазоне присутствуют пустые ячейки, поставьте галочку напротив поля “Skip empty cells”, чтобы пропустить их. Если в колонке с ключевыми словами для семантического ядра присутствует заголовок с названием страницы, где они в последующем будут размещены, то выберете пункт “My column has header”, чтобы название не учитывалось. Также в поле “Case sensitive” можно указать вести поиск с учетом регистра или нет.

Настройка расширения для гугл таблиц фото 3

Последний шаг - выбрать, что необходимо сделать с дубликатами:

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

Выделение повторов запросов в гугл таблицах

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

6. Удаление дублей запросов 

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

Удаление дублей ключевых слов seo специалистом
 
В Google Таблицах можно воспользоваться специальной формулой для отображения только уникальных значений. В свободной колонке вводим комбинацию без кавычек “=(UNIQUE (A1:A100))”, где A1:A100 - это столбец с ключевыми словами. К сожалению, данный способ не подойдет, если в колонке содержатся слова, написанные разным регистром. 

Поиск seo специалистом уникальных запросов

Второй способ для гугл таблиц - воспользоваться дополнением Remove Duplicates, о котором писали в предыдущем пункте.

7. Поиск и удаление минус слов

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

Поиск минус слов в таблицах Эксель


В Excel “Условное форматирование” находится в главном разделе, в Google таблицах во вкладке “Данные”. Устанавливаем правила форматирования “Текст содержит” и указываем начало нашего минус слова (начало, потому что окончание слова может быть разным), выбираем цвет заливки и нажимаем “Готово”. Далее для последующей обработки выделенных ячеек, их можно отсортировать по цвету (об этом подробнее в 8-м пункте).

Условное форматирование для выделения минус слов в семантике


Еще один способ быстро найти и удалить минус слова - это использование фильтров. С их помощью можно сделать так, что в таблице будут отображаться только те ячейки, которые соответствуют заданным параметрам. Для этого выделяем диапазон ячеек с ключевыми словами, в Excel переходим в раздел “Данные”, нажимаем “Фильтр”, если работаем в Google Таблицах, тогда “Данные > Создать фильтр”.

Использование фильтров для поиска минус слов в семантике


Нажимаем на появившийся значок фильтра в верхней части столбца, выбираем “Фильтровать по условию > Текст содержит”. Получаем список запросов, которые содержат минус слово, и удаляем их.

Сортировка seo семантики по минус словам

8. Сортировка данных по цвету заливки ячеек

В программе Excel предусмотрена фильтрация данных в зависимости от цвета заливки ячеек, для этого в разделе “Данные > Фильтр” выберите условия фильтрации по цвету.

Сортировка по цвету ячейки в Экселе


В Гугл таблицах встроенной такой функции нет и для ее использования необходимо скачать еще одно браузерное расширение “Сортировка диапазона +”. Это дополнение позволит сортировать данные по цвету заливки ячейки и по цвету текста.

Расширение для Google Таблиц сортировка ключевых слов по цвету

Еще несколько функций Excel при выполнении других задач seo специалиста

1. Закрепление строки для удобства работы

Если вы занимаетесь seo, то вы знаете, что часто приходится работать с большим количеством данных. Каждый столбец в таблице соответствует определенному показателю и имеет название (заголовок). Чтобы эти названия были всегда на виду, нужно воспользоваться функцией закрепления. Нажимаем “Вид > Закрепить > 1 строку”.

Закрепить строку в Google Таблицах фото

2. Расширение ячейки до нужной ширины

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

Как расширить ячейку в гугл таблицах фото

3. Работа с регистром символов

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

Формула для первой заглавной буквы фото

Если наоборот вам нужно сделать все буквы в нижнем регистре, тогда воспользуйтесь формулой “=СТРОЧН(A1)”.

Формула для всех строчных букв в экселе

4. Подсчет количества символов в мета-тегах

Вы как seo-специалист уже сгруппировали запросы в семантическом ядре и приступили к прописыванию мета-тегов (title, description), и вам необходимо подсчитать, какое количество символов в итоге получилось. Для выполнения этой задачи воспользуйтесь формулой “=ДЛСТР(A1)”. 

Подсчет количества символов в мета тегах

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

5. Подсчет количества запросов в ТОПе

В имеющейся выгрузке с позициями сайта необходимо подсчитать, какое количество из этих запросов находится, например, в ТОП 10. Для этого воспользуемся формулой  “=СЧЁТЕСЛИ(A1:A50;"<=10")”, где “A1:A50” - это выделенный диапазон с позициями.

Проверка сколько запросов в топе выдачи

6. Проверка сколько дней прошло с какой-то даты

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

Подсчет прошедших дней в гугл таблицах

Подводим итоги

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


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

Ближайшее мероприятие с участием лично Павла Шульги
18 (Пн) ноября в 19:00 Киев / 20:00 МСК

Бесплатный мастер-класс
"8 приёмов по web-аналитике чтобы увеличить прибыль и уменьшить затраты"
Рекомендуемые статьи этой категории
Бесплатный аудит
Вашего сайта в 1 клик
Введите ссылку на Ваш сайт и мы за 31 секунду проанализируем его на ошибки, изменив которые, Вы можете увеличить посещаемость сайта на 200 - 400%