Вы здесь

Excel для SEO специалиста

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

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

Потратив всего 5 минут на прочтение этой статьи, Вы откроете для себя удивительные возможности Excel для облегчения процесса продвижения сайтов и сможете сразу же использовать эти приёмы в своей работе.

ОГЛАВЛЕНИЕ

1. Преобразование диапазона в таблицу 11. Сделать все символы в строке строчными (СТРОЧН) или заглавными (ПРОПИСН)
2. Разделение по столбцам для удобного анализа и работы с иерархической структурой сайта 12. Удалить лишние символы с ячеек
3. Настраиваемая сортировка 13. Сравнение двух ячеек
4. Визуальный поиск повторяющихся ячеек (дубликатов) 14. Найти, выделить и удалить строки с пустыми ячейками
5. Быстрое удаление дубликатов 15. Закрепить строки/столбцы для удобства работы с данными
6. Проверка соответствия целевых и релевантных страниц 16. Автозаполнение данных
7. Подсчёт количества запросов в ТОП 3, 5, 10 17. Преобразование строк в столбцы и обратно.
8. Проверка длины мета-тегов. Например, Title 18. Вывести миниатюрную динамику результатов
9. Компоновщик UTM меток своими руками (функция СЦЕПИТЬ) 19. Выделить все позиции в ТОП 10
10. Получить (выделить) домены из списка URL 20. Удалить все домены в зоне .com (можно в любой другой)
21. Открыть несколько отдельных excel файлов в одной книге

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

1 шаг: В ленте навигации выбрать вкладку «Вставка» и нажать на иконку «Таблица».

2 шаг: Поставить галочку «Таблица с заголовками» и выбрать нужный диапазон с данными, затем нажать «Ок».

До:

1do.jpg

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

После:

2. Разделение по столбцам для удобного анализа и работы с иерархической структурой сайта

1 шаг: Создаём столбец со списком URL сайта

2 шаг: Выделяем полученный столбец, нажимаем в навигационной ленте на «Данные» — «Текст по столбцам» — «Далее»

3 шаг: В появившемся окне ставим галочку напротив «Другой» и в поле ставим слеш (/), нажимаем «Готово»

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

3. Настраиваемая сортировка

Пункт простой, но наиболее часто используемый в работе.

Для примера предположим, что нам необходимо вначале вывести все URL сайта, где отсутствует H1

Шаг1: Для этого выделим весь диапазон данных 

Шаг 2: В ленте инструментов выбрать «Главная - Сортировка и фильтр – Настраиваемая сортировка» 

Шаг 3: В появившемся окне настроек выбрать столбец и метод (в нашем случае «По убыванию»). Нажать ОК. 

В итоге, в самом начале списка, мы получили список страниц без H1 на сайте. 

Эту операцию можно было сделать используя метод из примера №1.

4. Визуальный поиск повторяющихся ячеек (дубликатов)

Шаг 1: Выделяем необходимый столбец с данными и на ленте инструментов выбираем «Главная – Условное форматирование – Правила выделения ячеек – Повторяющиеся значения» 

Шаг 2: Выбираем стиль заливки. Можно оставить всё как есть. Нажимаем «ОК». 

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

Далее можно с помощью клавиши «Delete» удалить эти дубликаты. И произвести быстрое удаление всех строк имеющих дубли способом описанном в пункте 5.

Если дубликатов в выделенной колонке нет, то вы больше не будете видеть красную (в нашем случае) заливку.

5. Быстрое удаление дубликатов

Шаг 1: Скопируем необходимые данные в 1 столбец. К примеру, это будут Title. 

Я уже подсветил дубли красной заливкой, методом описанным пунктом выше.

Шаг2: Выделяем весь столбец кликом по его адресу в Excel (У нас это ячейка А). У нас сразу залился весь столбец. 

Шаг 3: Выбираем инструмент «Данные – Удалить дубликаты». Для одной колонки ничего настраивать не нужно. Нажимаем "ОК". 

В результате Ексель удалил нам все дубликаты. После окончания процесса Вы получите уведомление о кол-ве удалённых значений. 

6. Проверка соответствия целевых и релевантных страниц

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

Шаг1: Выводим два столбца: целевая страница и релевантная. Это вы уже сможете сделать. Создаём третий столбец с названием «Результат» 

В ячейку (в нашем случае C2) вставляем формулу: =ЕСЛИ(A2=B2;1;0)

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

В результате мы получим столбец состоящий из 0 и 1. Единица означает что, целевая страница равна релевантной. Ноль соответственно не равна. 

Шаг 2: Для визуализации данных, можно сделать выделение ячеек с единицей зелёным цветом, методом описанным в пункте 19

7. Подсчёт количества запросов в ТОП 3, 5, 10

Для этих целей мы используем функцию СЧЕТЕСЛИ (диапазон_значений; условие)

Шаг 1: Подготовим таблицу с запросами и соответствующим им позициям 

Для ТОП 3 функция будет выглядеть: =СЧЁТЕСЛИ(C2:C50;"<=3")

Для ТОП 5 функция будет выглядеть: =СЧЁТЕСЛИ(C2:C50;"<=5")

Для ТОП 10 функция будет выглядеть: =СЧЁТЕСЛИ(C2:C50;"<=10")

Где C2:C50 – диапазон данных с позициями

<=3 это критерий подсчёта.

Получаем результат! Далее, с помощью формул можно перевести всё в проценты. 

8. Проверка длины мета-тегов. Например, Title

Шаг 1: Подготовить таблицу со перечнем URL и необходимых вам мета-тегов 

Шаг 2: Для столбца «длина title» устанавливаем формулу =ДЛСТР(B2);

Шаг 3: Тянем вниз за маленький чёрный квадрат в правом нижнем углу ячейки. В результате наша формула автоматически заработает для всех строк ниже.

Для визуализации я сделал выделение красным всех строк, длинна которых превышаем допустимый лимит для Яндекса (75 символов) 

Вторым цветом выделим строки которые меньше 15 символов.

Шаг 4: Для выделения цветом мы использовали инструмент «Главная - условное форматирование – управление правилами» и настроили правила следующим образом 

итоговая картина:

=$C2<15 
=$C2>75

9. Компоновщик UTM меток своими руками (функция СЦЕПИТЬ)

Шаг 1: Подготавливаем таблицу с данными 

Шаг 2: В строку с результатом вставляем формулу =СЦЕПИТЬ(A2;B2;C2;D2;E2;F2;G2) 

Которая соединяет все наши данные в ячейки в одно цело.

Можно использовать альтернативный вариант конкатенации по формуле =(A2&B2&C2&D2&E2&F2&G2)

10. Получить (выделить) домены из списка URL (ЛЕВСИМВ + ПОИСК)

Шаг 1: Подготавливаем таблицу с URL 

Шаг 2: В новый столбец напротив наших строк вставляем формулу

=ЛЕВСИМВ(A2; ПОИСК("/";A2;8)) 

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

11. Сделать все символы в строке строчными (СТРОЧН) или заглавными (ПРОПИСН)

Шаг1: Подготавливаем данные в одном из столбцов 

Шаг2: в первую строку столбца для результата вводим формулу

=СТРОЧН(текст)

или

=ПРОПИСН(текст) (для заглавных символов)

В результате получаем список тех же строк, но в нижнем регистре 

12. Удалить лишние символы с ячеек

Для этого мы будем использовать функции:

=ПЕЧСИМВ(текст) - удалить все непечатаемые знаки из текста.

=СЖПРОБЕЛЫ(номер_ячейки) - убрать лишние пробелы в тексте.

Данные подготавливаем также как и в примере выше.

13. Сравнение двух ячеек

На вход поступает 2 столбца с ячейками содержащие данные.

В третий столбец вставляем формулу =СОВПАД(текст1;текст2)

В результате получаем ответ (Истина или Ложь)

14. Найти, выделить и удалить строки с пустыми ячейками

Часто бывает, что если в определённой ячейке (в строке) нет данных, то нам не нужна вся строка. Надеюсь объяснил.

Но как нам найти, выделить и удалить все эти строки, если наш массив состоит из 20 000 строк. Очень просто.

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

Шаг 2: Используем инструмент «Главная – Найти и выделить – Выделить группу ячеек…»

Шаг 3: в настройках инструмента выбираем «Выделить пустые ячейки». Нажимаем «ОК» 

Шаг 4: после того, как Excel выделил нам все пустые ячейки, выбираем инструмент «Главная – удалить – удалить строки с листа». 

После чего Ексель удалит вам все необходимые строки.

15. Закрепить строки/столбцы для удобства работы с данными

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

Для этого в Excel есть функция закрепить область. Находится она «Вид – закрепить области» 

Если нужно закрепить 2 первые строки, ставим курсор на 3ю строку и нажимаем «Закрепить области».

Готово, в результате, первые две строки будут всегда закреплены сверху листа.

16. Автозаполнение данных

К примеру, Вам необходимо ввести 250 произвольных фамилий.

Делается это следующим образом: 

17. Преобразование строк в столбцы и обратно

Делается это просто: 

18. Вывести миниатюрную динамику результатов

Называется это Спарклайны — нарисованные прямо в ячейках миниатюрные диаграммы, наглядно отображающие динамику наших данных. Чтобы их создать, нажмите кнопку «График» (Line) или «Гистограмма» (Columns) в группе «Спарклайны» (Sparklines) на вкладке «Вставка» (Insert). В открывшемся окне укажите диапазон с исходными числовыми данными и ячейки, куда вы хотите вывести спарклайны.

После нажатия на кнопку «ОК» Microsoft Excel создаст их в указанных ячейках. На появившейся вкладке «Конструктор» (Design) можно дополнительно настроить их цвет, тип, включить отображение минимальных и максимальных значений и т. д.

19. Выделить все позиции в ТОП 10

Ранее мы уже работали с условным форматированием в примере № 8

Шаг 1: Для выделения нужным нам цветом позиций 1-10 (включительно) необходимо 

Воспользоваться инструментом «Главная – условное форматирование – создать правило»

Шаг 2: настраиваем правило, как показано на скриншоте. 

Готово!

20. Удалить все домены в зоне .com (можно в любой другой)

Предположим, что у нас есть список доменов. И нам нужно оставить только те, что в зоне .ru

Начнём с удаления зон .com

Шаг 1: выделим необходимый нам столбец 

Шаг 2: Нажать Ctrl+F и перейдём на вкладку «Заменить»

В первую строку ввести *.com*

Во вторую: ничего

Нажать «Заменить всё» 

После чего удалить пустые строки через способ описанный в пункте № 14

21. Открыть несколько отдельных excel файлов в одной книге

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

Шаг 1: Открываем книгу, куда хотим собрать листы из других файлов, входим в редактор Visual Basic (ALT+F11), добавляем новый пустой модуль (в меню Insert - Module) и копируем туда текст вот такого макроса: 

Sub CombineWorkbooks()      Dim FilesToOpen      Dim x As Integer         Application.ScreenUpdating = False  'отключаем обновление экрана для скорости             'вызываем диалог выбора файлов для импорта      FilesToOpen = Application.GetOpenFilename _        (FileFilter:="All files (*.*), *.*", _        MultiSelect:=True, Title:="Files to Merge")         If TypeName(FilesToOpen) = "Boolean" Then          MsgBox "Не выбрано ни одного файла!"          Exit Sub      End If             'проходим по всем выбранным файлам      x = 1      While x <= UBound(FilesToOpen)          Set importWB = Workbooks.Open(Filename:=FilesToOpen(x))          Sheets().Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)          importWB.Close savechanges:=False          x = x + 1      Wend         Application.ScreenUpdating = True  End Sub	  

Шаг 2: После этого можно вернуться в Excel и запустить созданный макрос через меню Сервис - Макрос - Макросы (Tools - Macro - Macros) или нажав ALT+F8. Отобразится диалоговое окно открытия файла, где необходимо указать один или несколько (удерживая CTRL или SHIFT) файлов, листы из которых надо добавить к текущей книге.

+ БОНУС:

В рамках написания статьи, я нашёл крутейшую надстройку для Excel, которая умеет решать 90% всех ежедневных задач для оптимизатора.

http://seotoolsforexcel.com/

SeoTools v5.1.4

SEOtools for Excel 4.3.5

Разница там в том, что с 5-ой версии инструмент стал частично платный и часть функционала ушла в платные функции. (вроде так)

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

Автора автора