14 полезных функций Excel для SEO оптимизатора

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

Мы часто встречаемся с необходимостью чистить множество данных или, например, фильтровать e-mail, ссылки и прочее. Причем, как это часто бывает, нужно фильтровать огромное количество ссылок.

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

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

Содержание:

• #1 Удаляем все ссылки из Excel
• #2 Как красиво вставить длинные тексты в Excel
• #3 Как закрепить область в Excel
• #4 Как вырезать данные из таблицы, которые не нужны
• #5 – Находим и заменяем массово элементы в таблице
• #6 – Условное форматирование, выявляем дубликаты
• #7 – Импорт из CSV. файла
• #8 – Подсчет ячеек, отвечающих определенным критериям
• #9 – Расширить ячейку до нужной ширины
• #10 – Функция ВПР (VLOOKUP)
• #11 – Функция СЧЁТЕСЛИ (COUNTIF)
• #12 – Функция СОВПАД
• #13 – Функция Текст по столбцам (Text to Columns)
• #14 – Визуализация отчетов

#1 Удаляем все ссылки из Excel

Эта функция пригодится, когда мы экспортируем какие-нибудь данные в Excel, где будут содержаться ссылки. И нам нужно эти ссылки удалить.

Excel устроен так, что по-умолчанию он распознает текст с http:// как гиперссылку. А это значит, что Excel на автомате размечает такой текст синим цветом и подчеркивает его. А если вы кликните на такие элементы, то эти урл-адреса откроются в браузере.

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

Вы можете конвертировать все гиперссылки в обычный текст следующим образом. Сначала нажимаем одновременно клавиши Ctrl+A. Так мы выделим все ячейки на листе. После этого нажимаем правую клавишу мыши и выбираем пункт «Удалить гиперссылки».

#2 Как красиво вставить длинные тексты в Excel

Иногда содержимое ячейки слишком большое и текст, который в ней размещен, не очень удобно анализировать. Когда копируешь текст откуда-нибудь сразу в ячейку, то ячейка автоматически растягивается по вертикали. И в итоге это портит вид нашей страницы. Поэтому можно придать ячейке более привлекательный вид. Для этого после того, как вы скопируете данные в ячейку, кликните по столбцу, где содержатся нужные данные (А, В, С и т.д.). Далее кликните на кнопку «Перенести текст» на вкладке «Главная»

До

После

Теперь если нужно будет что-то записать в ячейку, которая расположена следующей за нашей с текстом (в данном случае это ячейка в столбце F, то текст в ячейке столбца E будет скрыт и будет более удобно анализировать данные.

#3 Как закрепить область в Excel

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

Прокручиваем нашу страницу в Excel в самый верх, далее идем на вкладку «Вид» и нажимаем на кнопку «Закрепить области» и далее на кнопку «Закрепить верхнюю строку». Готово! У нас сейчас закреплены заголовки

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

#4 Как вырезать данные из таблицы, которые не нужны

Фильтры – это одни из наиболее часто используемых функций в Excel. Они простые в использовании и можно быстро получить нужную информацию. SEO специалист по-любому должен уметь пользоваться этими фильтрами.

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

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

Этими данными здесь можно вообще вертеть как вздумается. Можно ставить отображение только нужных строк, содержащих, например, значение 11 (как показано на изображении выше) и т.д. и т.п.

#5 – Находим и заменяем массово элементы в таблице

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

Например, в строках содержатся url-адреса с префиксом www. и нам нужно этот префикс централизованно почистить. Вернее, удалить из всех url-адресов в таблице префикс www. Чтобы это сделать, выделите столбец путем нажатия клавиши мыши на его заголовок, а затем введите сочетание клавиш «Ctrl + H», чтобы вывести диалоговое окно «Найти и заменить». Далее здесь в поле «Найти» вводим значение «www.» и больше ничего нигде не вводим. Нажимаем кнопку «Заменить все».

Готово! Теперь мы удалили все url-адреса, которые содержат префикс «www.» в этом столбце. При необходимости аналогичную процедуру можно проделать для остальных столбцов. Это самый быстрый способ чистки данных в Excel.

#6 – Условное форматирование, выявляем дубликаты

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

Мы кликаем на заголовок столбца, после чего на вкладке «Главная» нажимаем на кнопку «Условное форматирование» — «Правила выделения ячеек» — «Повторяющиеся значения…». После этого нажимаем во всплывающем окне ОК и у нас в таблице будут выведены все повторяющиеся значения, которые были найдены в выделенном столбце. Эти повторяющиеся значения будут подсвечены.

#7 – Импорт из CSV. файла

Каждый SEO специалист использует в работе такие сервисы как Ahrefs, Semrush, Google Analytics и т.д. Во всех этих SEO сервисах есть возможность экспортировать полученные данные в CSV. файл. Когда вы открываете этот CSV. файл, вы можете работать с ним как угодно. Но если вы работали с этим типом файлов, то должны знать, что все-таки это не всегда удобно и проще работать с данными в Excel.

Поэтому проблема решается следующим образом. Открываем Excel без каких-либо данных, дальше переходим на вкладку «Данные» и нажимаем на кнопку «Получение внешних данных» во всплывающем окне выбираем «Из текста»

Далее находим через проводник наш CSV. файл и нажимаем «Импорт». Дальше у нас появится всплывающее окно мастера импорта, где в трех шагах нужно будет выбрать соответствующие настройки. На первом шаге нужно выбрать формат файла, чтобы данные верно отображались.

На втором шаге нужно выбрать разделитель, а на третьем – формат данных столбца. Ну и в конце нужно будет выбрать в какой лист (текущий или новый) вставить импортируемые данные. Готово! Теперь у нас импортирован CSV. файл.

#8 – Подсчет ячеек, отвечающих определенным критериям

Результатом нашей работы будут какие-то обобщенные данные. SEO – не исключение.

Функция СЧЁТЕСЛИ (COUNIF) достаточно проста и состоит из двух составляющих:

• Где вы хотите посчитать
• Что вы хотите посчитать

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

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

Сама по себе функция СЧЁТЕСЛИ (COUNIF) гораздо шире по своим возможностям, чем я показал выше, поэтому подробней можете посмотреть в видео

#9 – Расширить ячейку до нужной ширины

Для этой функции у меня самое короткое описание. Здесь все просто. Иногда у нас ячейки выглядят вот так

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

#10 – Функция ВПР (VLOOKUP)

Возможно, раньше вы уже слышали о знаменитой функции ВПР (VLOOKUP). Я когда-то когда только узнал о ней, долго не мог вкурить, что к чему. Поэтому не удивляйтесь, если вы нагуглите что-то про функцию ВПР в Excel и ничего сразу не поймете. Но я все равно попробую объяснить, потому что иногда эта функция бывает очень и очень нужна.

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

Предположим, мы напарсили данные по сайтам и в одной таблице у нас есть два столбика – в одной урлы сайтов, во втором – анкоры, или позиции или вообще все что угодно еще. А во второй таблице для тех же самых сайтов спарсены данные по ТИЦ (теперь это уже ИКС – индекс качества сайтов), DA, Alexa Rank. И порядок, в котором размещены домены в двух таблицах, разнится. То есть, мы вручную не сможем скопировать и вставить данные из одной таблицы в другую, чтобы они совпадали.

На изображении выше вы можете видеть, что сайты одни и те же, но их порядок в первом и втором случае разный. То есть, чтобы у нас в одну строку были все имеющиеся данные по site1.ru, нужно эти данные перенести вручную. А это очень неудобно. Ну и для того, чтобы это сделать на автомате для большого количества данных, нам как раз и пригодится функция ВПР.

Используем следующую формулу

=ВПР(A2;$E$2:$G$11;2;ЛОЖЬ)

Где:

• Параметр А2 – это первый параметр, который определяет по какому значению мы будем искать совпадения. То есть, в данном конкретном случае нам нужно состыковать две таблицы по доменам
• Параметр E2 и G11 – это стобцы, из которых мы будем брать значения
• 2 – это номер столбца в этих всех столбцах, в которых мы ищем совпадения. В данном конкретном случаем мы ищем в трех столбцах – Е, F и G. Считается слева-направо. Я хочу пристыковать к моим доменам из первой таблицы данные по ИКС (индексу качества сайтов). А столбец с этими данными идет вторым по счету (слева-направо) в искомых данных
• Ну и последний параметр в этой формуле – это «ЛОЖЬ». Это означает, что мы ищем точные совпадения. Если использовать значение «ИСТИНА», то у нас при отсутствии точных совпадений будут использоваться ближайшие значения по убыванию. И да, если будете использовать значение «ИСТИНА», то делайте сортировку столбца по возрастанию, потому что могут быть некорректные результаты.

Дальше мы выделяем ячейку, с которой желаем начать, в данном случае С2, и вводим формулу.

Ввели формулу, нажали на галочку и получаем следующий результат

Как видим, ячейка С2 приняла значение 1500, что соответствует site1.ru. Теперь если мы потянем за нижний правый уголок этой ячейки вниз и протянем до ячейки С11, то у нас автоматически заполнятся все значения для остальных ячеек.

При работе с разными источниками и с большим количеством данных функция ВПР (VLOOKUP) в Excel является просто незаменимой для SEO специалиста.

#11 – Функция СЧЁТЕСЛИ (COUNTIF)

Функция СЧЁТЕСЛИ хорошо применима для анализа ссылочного профиля (см. Стратегии построения мощного ссылочного профиля сайта). Например, мы выгрузили из Ahrefs ссылочный профиль нашего сайта. То есть у нас будет таблица CSV. или Excel с кучей анкоров. В свете нынешних репрессий со стороны поисковых систем по отношению к ссылкам и их качеству, нужно тщательно контролировать, чтобы доля естественных анкоров типа «здесь», «тут», или в виде адреса сайта «site.ru» была достаточно высокой.

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

У меня здесь встречаются анкоры в виде url-адреса сайта – site1.ru. Я хочу проверить, сколько будет в процентах содержание анкоров, включающих в себя «site1.ru». Учтите, что в данном случае не точное вхождение по названию домена, а именно анкоры, которые содержат в себе кроме всего прочего «site1.ru». Для этого используем формулу

=СЧЁТЕСЛИ(B1:B12;"*site1.ru*")/(СЧЁТЗ(B1:B12)+СЧИТАТЬПУСТОТЫ(B1:B12))

Получаем следующий результат

Ячейку для вывода результата можно выбрать любую. Кстати, как видите, у меня результат получился в виде числа, а мне нужно в процентах. Для этого в Excel достаточно нажать на сочетание клавиш Ctrl+Shift+5 и тогда наше число отобразится в виде процентов

#12 – Функция СОВПАД

Функция ЕСЛИ – еще одна полезнейшая в Excel. Думаю, если кто-то плотно работает с тем же Key Collector, парсит в нем свои сайты, а после экспортирует полученные данные в Excel, то вы должны знать, что есть столбец с релевантными URL нашего сайта, а есть столбец с URL, которые находятся в выдаче по поисковому запросу. Но так иногда бывает, что для одного и того же поискового запроса релевантный URL и URL, находящийся в выдаче, могут быть разными.

Вручную искать такие несовпадения достаточно утомительно, поэтому в данном случае на помощь придет функция СОВПАД.

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

Вручную искать такие несовпадения достаточно утомительно, поэтому в данном случае на помощь придет функция СОВПАД.

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

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

=СОВПАД(N2;O2)

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

В столбце P в каждой строке справа от моих URL, теперь указано «ЛОЖЬ» или «ИСТИНА». ИСТИНА – это значит, что данные в двух ячейках совпадают. ЛОЖЬ – не совпадают. Дальше можно отфильтровать данные по этим двум параметрам и легко найти поисковые запросы, для которых в выдаче одна страница, а релевантная страница – другая.

#13 – Функция Текст по столбцам (Text to Columns)

Еще одна полезная функция Excel для каждого SEO специалиста. Работает следующим образом. Предположим, мы пошли в Google Search Console и экспортировали оттуда данные по нашим ключевым словам. Экспортируются оттуда данные в CSV. формат, и если открыть этот файл, то я обычно вижу следующее

Чтобы правильно отобразить данные в Excel, нам необходимо сначала открыть пустой Excel так, как я показывал в пункте #7 настоящей статьи (импорт из CSV. файла). И уже после импорта этим способом у нас данные будут отображаться вот в таком виде

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

Дальше у нас появляется всплывающее окно. На первом шаге мы оставляем «с разделителями». На втором шаге в качестве символа-разделителя ставим «запятая» (в вашем случае разделитесь может быть другой).

На третьем шаге выбираем формат данных столбца как «текстовый» и нажимаем «Готово». Теперь таблица у нас примет вот такой вид

Согласитесь, SEO специалисту будет значительно проще анализировать данные в Excel в таком виде =)

#14 – Визуализация отчетов

Визуальные отчеты в Excel всегда проще воспринимаются и доносят информацию до пользователя значительно быстрее, чем просто цифры. Поэтому можно использовать функцию СЧЁТЕСЛИ (COUNTIF), чтобы полученные числовые результаты представить в виде графики.

Если вы произвели какие-то подсчеты, используя функцию СЧЁТЕСЛИ, давайте попробуем представить эти данные в виде круговой диаграммы. Представим, что у нас есть некоторые данные

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

Дальше можно поиграться с данными в «Добавить элемент диаграммы». Здесь есть такие элементы как «подписи данных». В итоге у нас может получиться следующее

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

Заключение

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