Добрый день уважаемый читатель!
Эту статью я решил посвятить такой актуальной теме как SEO и непосредственно для тех SEO-специалистов, которые хотят делать свою работу более эффективно. Для тех, кто не знает, что это такое, попробую объяснить, SEO – это не что иное, как поисковая оптимизация для поднятия позиций сайта в выдаче любой из поисковых систем (более детально с этим явлением вы можете ознакомиться в Википедии). Для меня лично, тема является очень актуально, так как я пишу статьи, которые в дальнейшем нужно SEO-оптимизировать для продвижении их в поисковых системах. А еще я использую силу MS Excel для улучшения работы, так как работа с сотнями ссылок и прочими данными простыми ручными манипуляциями очень неудобна и затруднена.
Все SEO-специалисты каждый день проводят много работы, которые связаны с оптимизацией, и очень важно использовать классные и качественные инструменты которые предоставляет Excel для решения разнообразнейших задач. Я уделил этому вопросу много времени, экспериментировал сам, читал рекомендации, отзывы других специалистов и решил собрать наработки воедино. Эта статья поможет, так как много специалистов, особенно молодых да зелёных, работы по отслеживанию статистики ключевых слов, сортировка и анализ больших массивов данных, разнообразные операции со строками и многое другое, выполняют вручную, затрачивая много времени там, где стоит и нужно всё автоматизировать. Некоторые SEO-специалисты пишут программы или скрипты под определённые нужды, покупают специализированный дорогущий софт, чтобы выполнить те операции, которые с легкостью может сделать Excel. И для доказательства моей правоты я приведу вам ТОП 10 функций Excel для SEO, которые значительно облегчать жизнь и работу практически любого SEO-специалиста.
Функция СЦЕПИТЬ
Одна из самых полезных в реальном применении при оптимизации, функция СЦЕПИТЬ дает возможность соединить воедино то, что содержится в нескольких ячейках в одно значение. Детально я рассматривал эту функцию в отдельной статье «Как используется функция СЦЕПИТЬ в Excel», там она описана детально и в подробностях. Но всё же я немножко покажу специфику ее использования для SEO-специалистов.
Пример №1: у вас возникла необходимость подготовить перечень сайтов для работы в другом инструменте, но синтаксис, который использует эта программа, требует запись в виде domain:ваш_домен.net. Вручную прописывать, конечно, можно, но это когда нужно до десятка значений передать, а если сотни, а то и тысячи, нам поможет функция СЦЕПИТЬ в Excel. Для получения нужного результата достаточно использовать формулу:
=СЦЕПИТЬ(«domain:»;адрес_вашей_ячейки) И получаем нужный нам результат, а после того как потянем ее вниз, то в результате авто копирования то получим значения для всего нашего диапазона значений.
Пример №2: у вас возникает необходимость, а это бывает, создать правильные «анкоры», то есть полноценные текстовые значения с гиперссылкой. При использовании функции СЦЕПИТЬ для этого варианта, мы сможем получить быстро и надежно, много нужных нам анкоров. Можно конечно в примере показать простой вариант, когда склеивается каждый символ, но всё же думаю надо показать пример для хорошего пользователя.
Для получения нужного вам результата нужно использовать формулу следующего вида:
=СЦЕПИТЬ(«<a href=’»;A11;»‘>»;B11;»</a>») Конечно, можно получить результат и другим способом, но перегружать вас аналогичной информацией я не буду.
Функция ПРОПНАЧ в Excel
Эта функция окажет вам помощь, если вам понадобится все ваши первые буквы в словах сделать заглавными, это будет полезно, когда у вам нужно написать тайтлы. В нашем случае вам просто нужно писать без учёта регистра букв, а после прописать формулу и автоматически преобразовать весь ваш диапазон значений.
Функция СЖПРОБЕЛЫ в Excel
Функция СЖПРОБЕЛЫ поможет вам удалить все лишние пробелы, за исключением одинарных, которые содержатся между словами из указанной ячейки или отрывка текста. При практическом использовании функция будет полезна, когда при выгрузке или копировании большого массива текста происходит добавление дополнительных пробелов, которые начинают создавать проблемы в дальнейшем при обработке данных.
Функция ПРОПИСН и функция СТРОЧН в Excel
В этом пункте я написал две функции, просто потому что у них очень похожее действие только в двух разных направлениях, эти функции могут преобразовать содержимое указанной ячейки в строчные или прописные, при необходимости, буквы.
Функция ДЛСТР в Excel
Эта функция также является одной из самых важных, так как она может определить длину текста, который содержится в ячейке. Примеров применения этой возможности функции ДЛСТР много, но рассмотрим самое распространённое применение. Например, мы измерим длину наших Title в связи с превышением лимита в 70 знаков (в примере использовал «Lorem ipsum» для наглядности), и вводим формулу: Результат мы конечно получили, и получили его правильно, но теперь его надо выделить для удочитаемого отображения, в этом нам поможет условное форматирование, которое наглядно продемонстрирует нам разбежности по лимиту символов и для этого создадим новые правила: Теперь прописываем два правила, одно для тех title лимит которых не превышен, но очень маленький и находится в диапазоне до 30 символов окрашиваем в жёлтый цвет, а второе для тех, кто перелез за планку и превышает более 71 символа окрашиваем в красный цвет. Все значения, которые не будут окрашены и будут нам нужны. Правила прописываем в окне «Создания правил форматирования»: В итоге у нас в «Диспетчере правил условного форматирования» оказывается созданы 2 правила, которые мы применим к нашему диапазону значений: А вот теперь мы и получаем наш такой нужный и очень удобный результат, визуально все видно, вы можете оперативно изменять данные, подстраивая их под условия, и форматирование моментально будет срабатывать, показывая, что вы сделали всё правильно.
Функция СЧЁТЕСЛИ в Excel
О возможностях этой функции я писал в отдельной статье, но сейчас рассмотрим специфическое применение этой функции в работе SEO-специалистов. В данном примере возьмем возможность оценить, насколько анкорный лист разбавлен адресами сайта. Используем, для примера, придуманный анкор-лист: Итак, список мы создали, теперь будем оценивать процентное соотношение вхождения адреса нашего сайта proba.net в список и для этого используем формулу: =СЧЁТЕСЛИ(A2:A7;»proba.net») Как видите, пока чуда не произошло, несмотря на то, что название домена присутствует в нашем списке. Просто критерии для функции СЧЁТЕСЛИ нужно задавать максимально чётко и ясно. Согласитесь, ведь в чистом виде в нашем списке анкор «proba.net» отсутствует. Значит, нам нужно ослабить наш запрос с помощью подстановочных знаков (звёздочка для любого количества символов и знак вопроса для одного произвольного символа), но в нашем случае будем использовать «*» (он же «астериск») и формула приобретет следующий вид =СЧЁТЕСЛИ(A2:A7;»*proba.net*») Вот уже совсем другой результат! Теперь мы видим, как наша формула нашла и подсчитала количество вхождений адресов сайта. Но это еще не всё ведь мы можем процентное соотношение анкоров с адресом к общей массе анкоров. Для этого мы добавим в нашу формулу функцию СЧЁТЗ и она станет такой: =СЧЁТЕСЛИ(A2:A7;»*proba.net*»)/СЧЁТЗ(A2:A7) Это практически окончательный вариант, который вы можете использовать, но иногда возникают ошибки при выгрузке данных и тогда возникнут пустоты, которые игнорирует функция СЧЁТЗ, поскольку она считает только заполненные ячейки. Для исправления этого недостатка добавим в нашу формулу функцию СЧИТАТЬПУСТОТЫ, которая и подсчитает пустые ячейки нашего диапазона. Теперь формула имеет законченный вид: =СЧЁТЕСЛИ(A2:A7;»*proba.net*»)/(СЧЁТЗ(A2:A7)+СЧИТАТЬПУСТОТЫ(A2:A7))
Функция ПОИСК в Excel
Эта функция, как видите с ее названия, может и будет заниматься поиском того что нам нужно. В чистом виде для SEO работы она, можно так сказать, бесполезна, но вот при соединении с функциями ЛЕВСИМ и ПРАВСИМ, это уже сила, хотя последнее соединение встречается достаточно редко.
Давайте ее изучим на примере, итак, у нас есть список адресов сайтов, а по условиям задачки нам необходимо изъять из них непосредственно название домена. Как нам это сделать? Нам нужно, для начала, определить, где же прячется сам домен, он всегда ограничен слешами, как сначала, так и в конце. Поэтому, сначала отрежем тот лишний хвостик что возникает после имени домена, но поиск стоит начинать, как минимум с позиции №9, так как начальные слеши мы должны пропустить. Для поиска нам нужна формула =ПОИСК(«/»;A2;9), которая найдет знак «/», после которого нужно всё отсечь: А теперь, когда мы знаем, где заканчивается название домена, будем вынимать с адреса страницы, название домена с помощью формулы: =ЛЕВСИМВ(A2;ПОИСК(«/»;A2;9))
Функции ЛЕВСИМВ и функция ПРАВСИМВ в Excel
Суть данных функций одна, они возвращают указанное вами количество любых знаков. Для функции ЛЕВСИМВ – это будут знаки слева и направо, а для функции ПРАВСИМВ – это будет справа налево, но это бывает реже. В основном эти функции используются в боевом тандеме с функцией ПОИСК, которую мы рассмотрели выше, поэтому повторятся, я не буду, а приступим к следующему пункту.
Функция СУММЕСЛИ в Excel
Об этой функции я писал очень много и во многих обзорах, в ссылках вы найдете детальное описание и функциональность, этой потрясающей функции. В чём-то она напоминает действие функции СЧЁТЕСЛИ, но есть отличия. Оно заключается в наличии 2 параметрах, в который вы указываете диапазон, в первом – где применяется критерий, а второй – где будет суммироваться значения, которые соответствуют критерию.
Рассмотрим на примере, есть некоторые параметры, или качественные показатели которые существуют у сайта, нам надо их просуммировать для определённого сайта, для этого нам нужна формула, следующего вида: =СУММЕСЛИ(A2:A7;A9;B2:B7)
Функция ВПР в Excel
Эта функция также относится к одной из самых распространённых и мощных, с ее возможностями вы можете ознакомиться отдельно. А вот сейчас давайте рассмотрим, как ее можно применить на практике для SEO-специалистов. Итак, пример будет таковым, что у нас имеется некий список доменов, которые на нас ссылаются, а также их анкоры и показатели ТИЦ и PR для этих сайтов. Все данные у нас хранятся в разных таблицах и порядок следования значений отличается друг от друга. Поэтому тут надо соединять либо руками или с помощью функции ВПР. Производим состыковку таблиц используя формулу: =ВПР(A2;$F$2:$H$6;2;ЛОЖЬ). Рассмотрим действие формулы поподробнее:
- первый параметр А2, определяет критерий по которому мы ищем, и он должен находится в нужной нам таблице куда мы переносим значения;
- второй параметр формулы, диапазон $F$2:$H$6 это таблица где нам нужно искать (этот диапазон желательно указать абсолютными ссылками, знак доллара, чтобы при авто копировании не было ошибок и диапазон не съезжал);
- третий параметр, это номер столбика, из которого нам следует взять значение при правильном вхождении критерия;
- четвёртый параметр мы ставим показатель ЛОЖЬ, он показывает тип совпадения и означает, что мы ищем абсолютно точное совпадение. (Более точно и обширно написано в моей статье).
Производим те же манипуляции для соседнего столбика, немного корректируя формулу: =ВПР(A2;$F$2:$H$6;2;ЛОЖЬ). Как видите, изменилось только значение столбика для изъятия значений по критерию. Ну вот, в принципе, это все что я хотел написать в этой статье. Хотя я скорее всего не описал всех возможностей, которые может использовать SEO-специалист, так как их очень много, но я исправлюсь. Я думаю написать еще одну обзорную статью для работы в области SEO-оптимизации с использованием функционала Excel. Очень надеюсь на вашу помощь в советах и информации, если у вас есть что-то новенькое напишите это в комментариях, я обязательно включу в следующий обзор.
Если у вас возникнет необходимость поэкспериментировать с функциями, вы можете узнать о них в «Справочнике функций».
Если статья вам пригодилась и понравилась, делитесь с друзьями ставьте лайки. Если есть замечания или дополнения жду ваши комментарии и рад буду вам приветствовать на других страницах моего сайта TopExcel.ru.
«Нажить много денег— храбрость; сохранить их— мудрость, а умело расходовать— искусство.
«
Бертольд Авербах
