БЛОГ
Руководство по работе с Excel и Google Таблицами для SEO специалистов
В своей ежедневной работе, связанной с продвижением и оптимизацией сайтов, мы постоянно сталкиваемся с необходимостью сбора, структурирования и сортировки данных. Разберемся?
Распространённый пример — кластеризация запросов в СЯ.
В этом деле незаменимый помощник — продукт Excel из стандартного пакета MS Office и Google Sheets.
Распространённый пример — кластеризация запросов в СЯ.
В этом деле незаменимый помощник — продукт Excel из стандартного пакета MS Office и Google Sheets.
Владея хотя бы основным функционалом таблиц, можно здорово упростить себе жизнь, не рискуя при этом потерять или оставить без внимания важную информацию.
Использование Excel и Google Tables помогает seo-специалистам существенно сократить временные затраты. Вместе с тем работа с таблицами требует определенных знаний. Наличие огромного количества функций может серьезно озадачить не только новичка.
Предлагаю разобрать наиболее интересные решения Excel и его сетевого брата для эффективной работы сеошника.
Использование Excel и Google Tables помогает seo-специалистам существенно сократить временные затраты. Вместе с тем работа с таблицами требует определенных знаний. Наличие огромного количества функций может серьезно озадачить не только новичка.
Предлагаю разобрать наиболее интересные решения Excel и его сетевого брата для эффективной работы сеошника.
Удаление ссылок
Экспортированные из внешних источников данные часто тянут за собой ненужные ссылки. Чтобы удалить эту лишнюю информацию, можно воспользоваться специальной функцией Excel или Гугл таблиц.
В качестве примера приведу простое копирование текста из Яндекс. Вордстат. Конечно, лучше воспользоваться решением, которое предлагает браузер. В противном случае вставленные в таблицу ключевые слова будут содержать ссылку на источник. Но если такое произошло, не стоит отчаиваться или пытаться удалить лишний текст вручную.
В качестве примера приведу простое копирование текста из Яндекс. Вордстат. Конечно, лучше воспользоваться решением, которое предлагает браузер. В противном случае вставленные в таблицу ключевые слова будут содержать ссылку на источник. Но если такое произошло, не стоит отчаиваться или пытаться удалить лишний текст вручную.
Решить проблему можно проще и быстрее, для этого необходимо:
- Выделить все строки и столбцы с ненужными ссылками;
- Кликнуть правой кнопкой мыши по выделенному диапазону;
- Выбрать во всплывающем списке «Удалить гиперссылки».
Удаление и замена данных
Собирая и группируя запросы, чтобы создать семантическое ядро, вы скорее всего во многих ячейках обнаружите ненужный текст или символы.
Например, символ «+» (купить + спб, окна + цена и т. д.).
В этом случае поможет полезная в процессе seo-оптимизации функция «Найти и заменить», которая позволяет удалять определённый текст или знак сразу во всех ячейках диапазона.
Например, символ «+» (купить + спб, окна + цена и т. д.).
В этом случае поможет полезная в процессе seo-оптимизации функция «Найти и заменить», которая позволяет удалять определённый текст или знак сразу во всех ячейках диапазона.
Чтобы активировать функцию в Эксель, кликаем по меню Найти и выделить в главной вкладке — Заменить. Чтобы провести массовую зачистку в Гугл Таблицах, требуется кликнуть по вкладке Изменить и выбрать Найти и заменить.
В поле «Найти» вводим текст или символ, который необходимо удалить. В поле «Заменить» проставляем пробел. Далее нажимаем «Заменить все», и готово: ключевые слова в вашем списке больше не содержат лишних знаков или слов.
Точно также поступаем, если требуется заменить слово, словосочетание или знак во всех ячейках на что-то другой. В этом случае вместо пробела в поле «Заменить» вводим текст, которым нужно заменить исходный.
Функцию «Найти и заменить» также можно запустить с клавиатуры одновременным нажатием клавиш Ctrl и H.
В поле «Найти» вводим текст или символ, который необходимо удалить. В поле «Заменить» проставляем пробел. Далее нажимаем «Заменить все», и готово: ключевые слова в вашем списке больше не содержат лишних знаков или слов.
Точно также поступаем, если требуется заменить слово, словосочетание или знак во всех ячейках на что-то другой. В этом случае вместо пробела в поле «Заменить» вводим текст, которым нужно заменить исходный.
Функцию «Найти и заменить» также можно запустить с клавиатуры одновременным нажатием клавиш Ctrl и H.
Как удалить лишние пробелы
Составляя списки ключевых слов в таблицах Excel или Google Tables, многие сео-специалисты жалуются на невозможность корректной сортировки данных из-за присутствия лишних пробелов.
Для решения этой проблемы стоит воспользоваться формулой =СЖПРОБЕЛЫ(). Между скобками в строке функции указывается адрес ячейки или диапазон, из которого требуется удалить пробелы.
Сортировка данных
Составление семантики сайта в Excel и Google Таблицах удобно во многом потому, что в них возможна сортировка по какому-либо признаку, при этом соответствующие этому признаку данные также будут перемещены.
Наиболее распространённый вариант размещения данных для создания списка ключевых слов под семантическое ядро — в одном столбце указаны запросы, в другом – их частотность.
Мы можем сделать сортировку по любому из признаков – названию по алфавиту (от А до Я или от Я до А) и частотности (от минимального до максимального числа запросов или наоборот).
Для этого выделяем оба столбца, выбираем меню Данные — Сортировать диапазон по Столбцу X, А-Я, где X – название столбца, по которому необходимо произвести сортировку.
Данные во втором выделенном столбце также будут перемещены, следуя за своим «соседом».
Наиболее распространённый вариант размещения данных для создания списка ключевых слов под семантическое ядро — в одном столбце указаны запросы, в другом – их частотность.
Мы можем сделать сортировку по любому из признаков – названию по алфавиту (от А до Я или от Я до А) и частотности (от минимального до максимального числа запросов или наоборот).
Для этого выделяем оба столбца, выбираем меню Данные — Сортировать диапазон по Столбцу X, А-Я, где X – название столбца, по которому необходимо произвести сортировку.
Данные во втором выделенном столбце также будут перемещены, следуя за своим «соседом».
В последней версии Excel вместо Сортировать диапазон по… в меню Данные необходимо выбрать подменю Сортировка, в открывшейся таблице указать номер сортируемого столбца и порядок сортировки.
Excel позволяет сортировать данные по цвету ячеек. Функция задаётся также с помощью подменю Сортировка в меню Данные. В поле «Сортировка» необходимо выбрать «Цвет ячейки», после этого ячейки выстроятся по цветам.
Google Sheets изначально не позволяют выполнить раскладку по цветам. Скачиваем расширение «Сортировка диапазона+», теперь мы имеем возможность выстраивать строки по цветам ячеек или текста и в Google Таблицах.
Google Sheets изначально не позволяют выполнить раскладку по цветам. Скачиваем расширение «Сортировка диапазона+», теперь мы имеем возможность выстраивать строки по цветам ячеек или текста и в Google Таблицах.
Поиск дублируемого текста
Обнаружение и выделение дубликатов – удобная опция, которая здорово помогает в процессе группировки запросов. Умение использовать её в работе позволяет автоматически отметить повторы ключевых слов.
Выделяем нужную колонку в Экселе, выбираем меню Главная — подменю Условное форматирование — Правила выделения ячеек — Повторяющиеся значения.
Осталось определиться с выбором цветовой гаммы.
Выделяем нужную колонку в Экселе, выбираем меню Главная — подменю Условное форматирование — Правила выделения ячеек — Повторяющиеся значения.
Осталось определиться с выбором цветовой гаммы.
В таблицах Гугла обнаружить и отметить повторы можно несколькими способами. Расскажу о двух из них.
Способ 1
Последовательно выбираем Формат — Условное форматирование — Добавить правило — в Правилах форматирования кликаем «Ваша формула» — вводим в поле =И(НЕ(ЕПУСТО(XY)); СЧЁТЕСЛИ($X$Y:$Z; "=" & XY) > 1), где X — литера колонки, Y — номер строки с данными.
Теперь все ячейки, содержащие повторные запросы, будут подсвечены.
Теперь все ячейки, содержащие повторные запросы, будут подсвечены.
Способ 2
Установка приложения Remove Duplicates позволит справиться не только с поиском дублей. Это удобное и функциональное дополнение к Google Sheets, полезное для seo-специалистов.
Выделяем список, выбираем меню Дополнения — Remove Duplicates — Find duplicates or uniques, отмечаем нужный тип данных — Duplicates.
Выделяем список, выбираем меню Дополнения — Remove Duplicates — Find duplicates or uniques, отмечаем нужный тип данных — Duplicates.
При необходимости можно исключить пустые ячейки (Skip empty cells), задать поиск с учётом регистра (Case sensitive).
Далее выбираем действие:
· выделить ячейку цветом,
· создать дополнительный столбец для дублей,
· удалить или скопировать дубли,
· удалить ячейки с повторяющимися данными.
Далее выбираем действие:
· выделить ячейку цветом,
· создать дополнительный столбец для дублей,
· удалить или скопировать дубли,
· удалить ячейки с повторяющимися данными.
Как удалить дубли запросов
Функционал Excel позволяет быстро удалить дублированные ячейки. Справиться с этой задачей поможет функция «Удалить дубли», которая находится в меню Данные.
В Гугл Таблицах такой функции нет. Здесь можно воспользоваться опцией выделения уникальных значений, которая задаётся функцией =(UNIQUE (X1:X100)), где X — название столбца с ключевыми словами. Функцию необходимо ввести в любую свободную ячейку.
Исключение минус-слов
Минус-слова и фразы в запросах сведут на нет всю работу, направленную на продвижение сайта или объявления. Поэтому так важно исключить их в процессе создания СЯ.
Выбираем один из двух вариантов действий:
Способ 1
Находим минус-слова посредством опции «Условное форматирование» в главной вкладке Excel или вкладке Данные Google Sheets.
Во всплывающем списке выбираем Правила выделения ячеек — Текст содержит…, вставляем минус-слово, чтобы увидеть, где оно встречается.
При этом желательно указывать слово без окончания, чтобы программа выделила все его варианты. Последний шаг – выбрать цвет заливки.
Во всплывающем списке выбираем Правила выделения ячеек — Текст содержит…, вставляем минус-слово, чтобы увидеть, где оно встречается.
При этом желательно указывать слово без окончания, чтобы программа выделила все его варианты. Последний шаг – выбрать цвет заливки.
Способ 2
Для исключения минус-слов из списка запросов удобно использовать фильтры.
Фильтрация – весьма полезная опция, позволяющая отобразить только ячейки, в которых содержится определённая фраза, слово или символ.
Нам требуется выделить колонку, содержащую ключевые слова, открыть вкладку меню Данные и выбрать подменю Фильтр в Эксель или Создать фильтр в Гугл Таблицах — Фильтровать по условию… — Текст содержит.
В окошко вводим начальную часть минус-слова. После этих действий в таблице останутся только те ячейки, которые соответствуют данному правилу, то есть содержат ненужные нам слова. Далее просто удаляем эти ячейки и снимаем фильтр.
Фильтрация – весьма полезная опция, позволяющая отобразить только ячейки, в которых содержится определённая фраза, слово или символ.
Нам требуется выделить колонку, содержащую ключевые слова, открыть вкладку меню Данные и выбрать подменю Фильтр в Эксель или Создать фильтр в Гугл Таблицах — Фильтровать по условию… — Текст содержит.
В окошко вводим начальную часть минус-слова. После этих действий в таблице останутся только те ячейки, которые соответствуют данному правилу, то есть содержат ненужные нам слова. Далее просто удаляем эти ячейки и снимаем фильтр.
Закрепление строки
Когда мы работаем с большим объёмом данных, анализируемые списки не умещаются на экране, а большое количество задействованных колонок и похожие данные в них вносят сумбур в процесс, возможность закрепить строки сильно упрощает жизнь и экономит время. Что делаем?
Выделяем нужную строку (например, верхнюю строку с названиями столбцов), кликаем по вкладке меню Вид — Закрепить — 1 строку. Теперь при прокрутке страницы вниз эта строка всегда будет перед глазами.
Данный способ подходит и для закрепления столбцов.
Выделяем нужную строку (например, верхнюю строку с названиями столбцов), кликаем по вкладке меню Вид — Закрепить — 1 строку. Теперь при прокрутке страницы вниз эта строка всегда будет перед глазами.
Данный способ подходит и для закрепления столбцов.
Замена регистра
Иногда требуется выполнить замену строчных букв на заглавные или наоборот.
Для этого можно воспользоваться одной из двух специальных формул:
=ПРОПИСН(ЛЕВСИМВ(СТРОЧН(XY);1))&ПСТР(СТРОЧН(XY);2;ДЛСТР(XY)-1), где XY — координаты ячейки, чтобы первое слово начиналось с заглавной буквы;
=СТРОЧН(XY), чтобы все слова в выбранном поле были написаны строчными буквами.
Для этого можно воспользоваться одной из двух специальных формул:
=ПРОПИСН(ЛЕВСИМВ(СТРОЧН(XY);1))&ПСТР(СТРОЧН(XY);2;ДЛСТР(XY)-1), где XY — координаты ячейки, чтобы первое слово начиналось с заглавной буквы;
=СТРОЧН(XY), чтобы все слова в выбранном поле были написаны строчными буквами.
Расчёт знаков в мета-тегах
Важная опция, которая помогает при необходимости скорректировать текст в Title и Description, если его объём слишком большой.
Вводим в любую свободную ячейку формулу =ДЛСТР(XY), где XY — координаты ячейки, количество знаков в которой требуется рассчитать, и сразу получаем ответ.
Вводим в любую свободную ячейку формулу =ДЛСТР(XY), где XY — координаты ячейки, количество знаков в которой требуется рассчитать, и сразу получаем ответ.
Расчет запросов в ТОП-10
Для подсчёта нам потребуется внести в таблицу ключевые слова и данные о позиции этого запроса в поисковой системе.
Далее в любом свободном поле вводим формулу =СЧЁТЕСЛИ(XY:XZ;"<=10", где X – имя столбца, а Y и Z – номера начальной и конечной строки диапазона.
Теперь известно, какое количество учтённых запросов занимают топовые позиции в поисковике.
Далее в любом свободном поле вводим формулу =СЧЁТЕСЛИ(XY:XZ;"<=10", где X – имя столбца, а Y и Z – номера начальной и конечной строки диапазона.
Теперь известно, какое количество учтённых запросов занимают топовые позиции в поисковике.
Отсчет срока, прошедшего с определенной даты
Незаменимая функция, если вы работаете в команде и вам требуется время от времени проверять своевременность выполнения задач или производить контроль через конкретное время с момента начала процесса.
В таблице указываем исходную дату (это может быть дата начала работы над проектом, размещения партнером ссылки на сайт и т.д.), в свободной ячейке вводим формулу =ЕСЛИ(ЕПУСТО(XY);; РАЗНДАТ(B2; СЕГОДНЯ(); "D")), где XY – адрес ячейки, в которой указаны число, месяц и год начала отсчёта. Вместо формулы появится количество дней, истекших с заданной даты.
В таблице указываем исходную дату (это может быть дата начала работы над проектом, размещения партнером ссылки на сайт и т.д.), в свободной ячейке вводим формулу =ЕСЛИ(ЕПУСТО(XY);; РАЗНДАТ(B2; СЕГОДНЯ(); "D")), где XY – адрес ячейки, в которой указаны число, месяц и год начала отсчёта. Вместо формулы появится количество дней, истекших с заданной даты.
Таблицы Excel или Google – отличные инструменты для seo-оптимизации. Широкие возможности, которые дают эти программы, часто либо не используются по незнанию, либо вводят в тупик даже опытного специалиста.
Мы с вами рассмотрели только часть полезных для seo функций, на самом же деле их в разы больше. Расскажите, какие ещё формулы вы используете в работе при кластеризации запросов в СЯ.
Мы с вами рассмотрели только часть полезных для seo функций, на самом же деле их в разы больше. Расскажите, какие ещё формулы вы используете в работе при кластеризации запросов в СЯ.