Опросов на тему «Самый важный инструмент в SEO» не видел только слепой. В большинстве случаев выигрывают программы для работы с ключевыми запросами, вы все о них знаете, но при этом все почему-то забывают, что без работы в Excel не обходится продвижение не одного проекта.
Ниже, мы решили перенести весь свой накопленный опыт работы в Excel. И как всегда, получить от Вас обратную связь, тем самым ещё больше прокачать свои скилы в этой теме.
Потратив всего 5 минут на прочтение этой статьи, Вы откроете для себя удивительные возможности Excel для облегчения процесса продвижения сайтов и сможете сразу же использовать эти приёмы в своей работе.
ОГЛАВЛЕНИЕ
1. Преобразование диапазона в таблицу, для облегчения сортировки большого массива данных
1 шаг: В ленте навигации выбрать вкладку «Вставка» и нажать на иконку «Таблица».
2 шаг: Поставить галочку «Таблица с заголовками» и выбрать нужный диапазон с данными, затем нажать «Ок».
До:
В примере, у нас появляется возможность быстрого поиска страниц с 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% всех ежедневных задач для оптимизатора.
Разница там в том, что с 5-ой версии инструмент стал частично платный и часть функционала ушла в платные функции. (вроде так)
Инструмент настолько широкий, что я решил посвятить ему отдельный материал, где подробно познакомлю Вас с входящими в него инструментами. Поскольку на сайте, их описание на английском, статья будет носить в двойне полезный характер. Следите за обновлениями в наших соц сетях.