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

Категории: SEO софт

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

excel для seo

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

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

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

Содержание:

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

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

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

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

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

excel для seo

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

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

До

excel для seo

После

excel для seo

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

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

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

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

excel для seo

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

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

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

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

excel для seo

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

excel для seo

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

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

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

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

excel для seo

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

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

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

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

excel для seo

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

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

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

excel для seo

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

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

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

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

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

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

excel для seo

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

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

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

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

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

excel для SEO

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

excel для seo

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

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

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

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

excel для seo

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

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

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

Где:

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

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

excel для seo

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

excel для seo

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

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

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

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

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

excel для seo

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

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

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

excel для seo

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

excel для seo

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

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

excel для seo

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

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

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

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

excel для seo

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

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

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

excel для seo

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

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

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

excel для seo

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

excel для seo

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

excel для seo

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

excel для seo

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

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

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

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

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

excel для seo

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

excel для seo

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

excel для seo

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

Заключение

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




Подпишитесь на рассылку блога
max1net.com: