среда, 31 августа 2016 г.
Сборник полезных функций Excel для работы SEO-специалиста
+
SEO-специалисты ежедневно решают массу важных задач, связанных с оптимизацией. И при этом важным является использование только качественных инструментов для решения тех или иных задач. Молодые начинающие веб-специалисты, как правило, такие обычные в практике SEO проблемы, как сортировка и анализ массивов данных, работа со строками, агрегация или разбивка данных, отслеживание статистики ключевых слов, мониторинг обратных ссылок— выполняют вручную, затрачивая много времени на монотонные, часто повторяющиеся и легко автоматизируемые задачи. Некоторые SEO-специалисты в силу своего незнания ищут готовое узкофункциональное решение для каждой возникающей проблемы или еще интереснее - пишут скрипты, решающие все остро стоящие перед ними дилеммы, с которыми они сталкиваются. А другие используют дорогие профессиональные средства - такие программы, как Deductor для формирования срезов данных, TextPipe для работы со строками и многие другие, для совершенно базовых операций.
Потому что, большинство веб-мастеров не знают о том, что множество наших проблем может решить программа Microsoft Excel (как и Google SpreadSheet, и LibreOffice). Далее — яркие тому доказательства: список 12 функций, знание которых заметно облегчить жизнь рядового SEO-специалиста.+
Потому что, большинство веб-мастеров не знают о том, что множество наших проблем может решить программа Microsoft Excel (как и Google SpreadSheet, и LibreOffice). Далее — яркие тому доказательства: список 12 функций, знание которых заметно облегчить жизнь рядового SEO-специалиста.+
№1 функция: ДЛСТР (англ. LEN)+
Очень удобна для определения длины текстового содержимого ячейки или текста, заданного в формуле. Примеров практического применения данная функция имеет просто массу. Например, измерение длины анкоров или мета-тегов на предмет превышения лимита. В качестве примера возьмём 70 знаков для title.+
Добавим условное форматирование для большей наглядности:+
Далее строки с длиной меньше допустимого значения выделяем одним цветом, а больше — другим.+
И получаем:+
Может быть не очень красиво. Зато наглядно и понятно. Особенно в случае, когда имеется несколько тысяч мета-тегов. По такому же принципу можно добавлять новые правила для параметров description.+
№ 2 функция : СЖПРОБЕЛЫ (англ. TRIM)+
Удаляет все пробелы, кроме одинарных между словами из содержимого ячейки или заданного фрагмента текста.
Очень полезная функция, когда в процессе работы при копировании всего объема текста - появляются пробелы до/после/между слов, мешающие и стопорящие дальнейшую работу над всем текстом.+
Очень полезная функция, когда в процессе работы при копировании всего объема текста - появляются пробелы до/после/между слов, мешающие и стопорящие дальнейшую работу над всем текстом.+
№ 3 функция :ПРОПИСН (англ. UPPER), СТРОЧН (англ. LOWER)+
Преобразует содержимое строки или какого-либо заданного фрагмента в прописные или строчные буквы.+
№ 4 функция : ПРОПНАЧ (англ. PROPER)+
Автоматически изменяет первые буквы каждого слова в строке на прописные.
На первый взгляд может показаться, что данная функция бесполезна - казалось бы, зачем нужно заменять первую букву каждого слова? Но бывают случаи, когда есть необходимость проверить частотность группы ключей, содержащих например, названия компаний.
Как Вы наверное знаете, при проверке основными сервисами (как следствие — и программами) все буквы запроса автоматически приводятся в строчный вид. В итоге таблица на несколько тысяч строк вида ЗАПРОС + КОМПАНИЯ, где название компании указано с маленькой буквы. Для дальнейшего использования необходимо сделать соответствующую корректировку. В этом случае данная функция ПРОПНАЧ сократит массу времени. Для её использования нужно сначала расщепить массив по 2-м столбцам (запрос и название) с помощью функции Данные → Текст по столбцам. Затем применяя функцию ПРОПНАЧ к столбцу с названиями компаний - производим сцепку с первым столбцом.
Хочется отметить, что данное решение не единственное из возможных. Но однозначно - самое простое.+
На первый взгляд может показаться, что данная функция бесполезна - казалось бы, зачем нужно заменять первую букву каждого слова? Но бывают случаи, когда есть необходимость проверить частотность группы ключей, содержащих например, названия компаний.
Как Вы наверное знаете, при проверке основными сервисами (как следствие — и программами) все буквы запроса автоматически приводятся в строчный вид. В итоге таблица на несколько тысяч строк вида ЗАПРОС + КОМПАНИЯ, где название компании указано с маленькой буквы. Для дальнейшего использования необходимо сделать соответствующую корректировку. В этом случае данная функция ПРОПНАЧ сократит массу времени. Для её использования нужно сначала расщепить массив по 2-м столбцам (запрос и название) с помощью функции Данные → Текст по столбцам. Затем применяя функцию ПРОПНАЧ к столбцу с названиями компаний - производим сцепку с первым столбцом.
Хочется отметить, что данное решение не единственное из возможных. Но однозначно - самое простое.+
№ 5 функция : СЦЕПИТЬ (текст1;текст2;текст3…) (англ. CONCATENATE)+
Самая полезная в практическом применении SEO функция программы Excel - СЦЕПИТЬ позволяет объединить содержимое отдельных текстовых блоков в одну строку. Это может быть, как простая сцепка 2-х ячеек, так и более сложный вариант с подставлением текстовых блоков непосредственно в формулу.
К примеру, допустим, вам нужно отправить ссылки с 700 не совсем качественных доменов в инструмент Disavow Links. Синтаксис инструмента предполагает формат вида domain: ваш_домен.kz. Что делать? Прописывать все 700 строк вручную? Естественно, нет. Вам просто нужно ввести в строке:+
К примеру, допустим, вам нужно отправить ссылки с 700 не совсем качественных доменов в инструмент Disavow Links. Синтаксис инструмента предполагает формат вида domain: ваш_домен.kz. Что делать? Прописывать все 700 строк вручную? Естественно, нет. Вам просто нужно ввести в строке:+
А после растянуть формулу на весь столбец.+
Вот еще наглядный пример: у вас есть столбец с URL и столбец с анкорами. И нужно сформировать полноценную ссылку следующего вида:+
Ничего сложного тут на первый взгляд нет. Но существуют некоторые «маленькие» хитрости. Например, использование кавычек в текстовом блоке, предшествующем ссылке и в блоке, идущем сразу за ней. В данном случае формула из предыдущего примера не сработает - из-за путаницы в одинарных/двойных кавычках.
Неверно:+
Неверно:+
Варианты решения данной задачи:+
- Обывательский (уровень юзера) +
Мы просто делаем 2 дополнительных столбца или ячейки с данными:
Вместо первого текстового блока в формуле используем ссылку на первую ячейку, вместо второго — на вторую. В результате чего получается такая картинка:
В случае, если были указаны какие-либо определенные ячейки, а не столбцы например, не забудьте задать абсолютные адреса:
+ - Мастерский (уровень профи)+
- Используем одинарные кавычки. Пишем:
Хотя синтаксис ссылок с одинарными кавычками будет являться валидным, его применение в этом случае все же не совсем канонично.+ - Используем символ кавычек (chr(34), символ(34))
У двойных кавычек есть цифровой код и мы можем просто вывести их с помощью функции chr (в русской версии «символ»).
+
- Используем одинарные кавычки. Пишем:
№ 6 функция: СЧЁТЕСЛИ (диапазон; критерий) (англ. COUNTIF)+
Подсчитывает количество ячеек внутри диапазона, согласно заданному диапазону. К примеру, Вам необходимо поверхностно оценить разбавленность анкорного листа сайта URL-ами. Для наглядности возьмём не настоящий анкор лист, а придуманный. Например:+
Теперь, чтобы оценить процент URL-разбавки анкор-листа, посчитаем все вхождения домена нашего сайта - именно domen.ru (согласно нашего придуманного списка) в анкоры. Для этого в строку формул вводим следующее:+
И что же мы видим? Ноль. Хоть вроде бы вхождение домена в анкорах встречается. Но дело в том, что, в отличие от функции ПОИСК (о ней — чуть ниже), критерий для СЧЁТЕСЛИ необходимо задавать ясно и чётко. В нашем случае в списке нет анкора domen.ru. Для ослабления критериев используется либо звёздочка (любое количество символов), либо знаки вопроса (одна произвольная буква). Для наших целей больше подойдёт звёздочка (имеет название - «астериск»).+
О. чудо! Мы нашли этот показатель, теперь заодно можем подсчитать и относительный вес анкоров с вхождением URL по отношению к общему количеству анкоров.+
Наверняка, Вы заметили, что функция СЧЁТЗ считает только непустые ячейки. В случае выгрузки с сервиса анализа беклинков и большого анкор-листа, полученный нами результат будет не совсем корректным. Но для таких случаев, в замечательной программе Excel также есть дополнительная функция подсчёта и пустых ячеек в диапазоне, носящая интригующее название СЧИТАТЬПУСТОТЫ (англ. COUNTA).
+
+
В итоге получаем:+
Вот и все.+
№ 7 функция : СУМЕСЛИ (диапазон; критерий; диапазон_для_сложения) (англ. SUMIF)+
Принцип действия аналогичен предыдущему примеру функции под номером 6. Основное их отличие заключается в 2-х параметрах с диапазонами. 1-й — для применения критерия, а 2-й — для применения сложения значений.+
№ 8 функции: ЛЕВСИМВ и ПРАВСИМВ (текст; количество знаков) (англ. LEFT и RIGHT)+
Возвращают заданное количество знаков слева или соответственно - справа. В основном их используют в устоявшейся связке с функцией ПОИСК.+
№ 9 функция: ПОИСК (искомый фрагмент, просматриваемый текст, начальная позиция) (англ. SEARCH)+
Данная функция возвращает номер вхождения искомой подстроки в общую строку. К примеру, применение следующей формулы возвратит «2», так как буква «п» входит в слово «оптимизация» на второй позиции:+
Становиться очевидным, что сами по себе знания о позиции вхождения подстроки являются малополезным, даже в области SEO.
На практике использование связки ЛЕВСИМ + ПОИСК (или аналогичный ПРАВСИМВ + ПОИСК) встречается достаточно редко. И вспоминаются следующие слова - «нет ничего более беспомощного, безответственного и испорченного, чем сеошник, прибегнувший к функциям поиска по подстроке».
Но тем не менее, давайте разберем на примере: у нас есть список URL-ов, и нам необходимо выделить из них непосредственно домен.+
На практике использование связки ЛЕВСИМ + ПОИСК (или аналогичный ПРАВСИМВ + ПОИСК) встречается достаточно редко. И вспоминаются следующие слова - «нет ничего более беспомощного, безответственного и испорченного, чем сеошник, прибегнувший к функциям поиска по подстроке».
Но тем не менее, давайте разберем на примере: у нас есть список URL-ов, и нам необходимо выделить из них непосредственно домен.+
Выстроим следующую логическую цепочку: нам надо «найти» точку непосредственно на слеше после домена, после этого убрать кусок строки слева — с нулевой точки до найденной нами точки конца домена. И сделаем разобивку задачи на несколько подзадач следующим образом:
- Что мы ищем? Слеш. Где ищем? В ячейке с URL. С какой позиции ищем? Как минимум, с восьмой, чтобы исключить начальные слеши.
В итоге получаем:
+ - Выделим подстроку с доменом: с начала строки до точки вхождения слеша:
При даже небольших навыках пользования текстовыми функциями программы Excel можно творить настоящие чудеса.+
№ 10 функция: ВПР (искомое_значение, таблица, номер_столбца, тип_совпадения) (англ. VLOOKUP)+
Кратко суть функции изложить будет сложно, а в официальной справке программы приведено абсолютно непонятное объяснение. По сути, это «состыковка» значений разных таблиц на основании анализа данных в ячейках. Давайте посмотрим, как это работает на очередном придуманном примере. К примеру, у нас имеется список ссылающихся на наш сайт доменов, анкоров их ссылок, ТИЦ и PR этих сайтов. +
Мы видим, что порядок сайтов в этих двух таблицах разнится. Без использования функций перенести данные из второй таблицы в первую, кроме как самым известным нам «ручным» способом, невозможно. Но давайте попробуем использовать функцию ВПР.
- Первый параметр (А2), определяет, по какому значению происходит поиск совпадений. В нашем случае нам надо «состыковать» таблицу по отдельным доменам.
- Второй параметр ( F2:H11) — это таблица с «эталонами». То есть та, где именно мы ищем.
- Третий параметр (2) — номер столбца в этой «эталонной» таблице, из которого берется значения. Слева-направо, в случае с «ТИЦ», значение «2».
- Четвёртый параметр (ЛОЖЬ) — тип совпадения. Это самое важное. Здесь таится одна из самых больших сложностей этой функции.
ЛОЖЬ показывает цель нашего поиска - точное совпадение содержимого ячейки в таблице с эталонами. ИСТИНА же означает, что при отсутствии точного совпадения, будет использовано ближайшее к нему по убыванию. Также при использовании ИСТИНЫ лучше всего производить сортировку столбца по возрастанию, иначе результат может быть не совсем корректным. И если в эталонной ячейке искомая ячейка будет встречаться несколько раз — то программа будет использовать первое значение.+
Работает. Один маленький нюансик: мы задали адрес таблицы как относительный, то есть при растягивании формулы - фокус с эталонной таблицы будет смещаться вниз на пустые ячейки. Чтобы этого не случилось, вписываем следующее:+
Как видим все работает. Теперь тоже проделываем и для соседнего столбца:+
Отлично, все сделано. Теперь обратим свое внимание на встроенный функционал программы.
Тут, лидерство по полезности и упрощению работы SEO-специалиста, поровну делят между собой 2 основные функции: очистка от дублей и разбивка данных по столбцам и по разделителю.+
Тут, лидерство по полезности и упрощению работы SEO-специалиста, поровну делят между собой 2 основные функции: очистка от дублей и разбивка данных по столбцам и по разделителю.+
№ 11 функция: Данные → Удаление дубликатов (Data → Remove Duplicates)+
Позволяет очистить список от дублей.
К примеру, у нас есть список доменов на 1000 строк. Как вариант, можно попробовать найти и убрать все дубли вручную, можно отсортировать список в алфавитном порядке и удалить вручную с уже намного меньшими усилиями, можно так же использовать макрос для Excel, софт по работе с ключевыми словами (удаляет дубли - по умолчанию), паблик-скрипты или различные онлайн-сервисы. Понятно, что если количество строк большое (к примеру < 1 000 000 строк для Excel), то вариант со специализированным софтом или скриптами является единственно правильным. Но если строк меньше пограничного максимума, Excel справляется с задачей на отлично.
Итак, на старте имеем 1266 доменов + aweb.kz:+
К примеру, у нас есть список доменов на 1000 строк. Как вариант, можно попробовать найти и убрать все дубли вручную, можно отсортировать список в алфавитном порядке и удалить вручную с уже намного меньшими усилиями, можно так же использовать макрос для Excel, софт по работе с ключевыми словами (удаляет дубли - по умолчанию), паблик-скрипты или различные онлайн-сервисы. Понятно, что если количество строк большое (к примеру < 1 000 000 строк для Excel), то вариант со специализированным софтом или скриптами является единственно правильным. Но если строк меньше пограничного максимума, Excel справляется с задачей на отлично.
Итак, на старте имеем 1266 доменов + aweb.kz:+
Кликаем на шапке столбца, чтобы выделить его целиком или просто тянем выделение руками или же, кликнув на первой ячейке с содержимым, нажимаем Ctrl+A. Весь наш список должен быть выделен.
Переходим во вкладку «Данные» и находим пункт меню «Удалить дубликаты».+
Переходим во вкладку «Данные» и находим пункт меню «Удалить дубликаты».+
Далее нажимаем «Ок».+
Точно так же можно сделать и с помощью абсолютно бесплатного рабочего инструмента Google Docs Spreadsheet. Также берем список доменов, часть из которых дублируется. Для удаления дублей используем функцию:+
Так как массив данных у нас лежит в столбце A, в ячейку соседнего столбца вставим формулу:+
Вот и все, задача выполнена. В столбец B автоматически зальётся массив уникальных строк. Формулу растягивать не нужно - всё реализовано через функцию CONTINUE.+
№ 12 функция: Данные → Текст по столбцам (Data → Text to Columns)+
Очень нужная и полезная функция, которая позволяет разбивать различные массивы на составляющие по отдельным столбцам. Также позволяет задать любой разделитель по Вашему выбору - слеш, точку, запятую и прочие. К примеру, можно без использования регулярных выражений и функций поиска по строке легко и быстро извлечь домены из списка различных URL. Допустим, у нас есть массив данных с разделителем вида «пайп» (вертикальная черта).+
Находим во вкладке «Данные» пункт «Текст по столбцам». Кликаем по нему, предварительно выделив нужный массив данных. Появляется «Мастер распределения текстов по столбцам»:+
Жмём на кнопку «Далее». Отмечаем тип разделителя «Другой» и вставляем туда символ вертикальной черты.+
На следующем шаге выставляем значение в поле «Поместить в», иначе столбец с данными перезапишется (хотя в 99% случаев именно это нам собственно и нужно). +
Вот и все. Несмотря на всю кажущуюся простоту, разбивка на столбцы по заданному разделителю является одной из наиболее часто используемых и полезных SEO-функций программы.
Мы рассмотрели основную связку Excel + SEO и надеемся, что эта информация окажется полезной для Вас, а так же спасёт не один десяток веб-мастеров от бессмысленной траты времени на рутинные задач
0 коммент.:
Отправить комментарий