Многие владельцы веб-сайтов используют Excel практически ежедневно. С его помощью они могут хранить список обратных ссылок, которые необходимо отслеживать, или вести мониторинг статистики ключевых слов. Но настоящие профессионалы SEO знают, как использовать Excel для максимизации эффективности SEO-стратегии. Они настолько искусно манипулируют данными, что появляется возможность по-новому взглянуть на всю кампанию по продвижению ресурса в поисковых системах. В этой статье я постараюсь описать свои любимые трюки, позволяющие добиться такого эффекта.
Для начала мы изучим основы, а затем перейдем к более сложным вещам. Некоторые из этих базовых функций могут быть использованы в тандеме с другими, чтобы сформировать более мощные команды.
ПОИСК и НАЙТИ
Эти простые функции позволяют определить расположение искомой текстовой строки внутри другой текстовой строки. Функции идентичны, за исключением того, что в случае НАЙТИ учитывается регистр, а в случае ПОИСК – нет. ПОИСК также поддерживает символы обобщения. ПОИСК и НАЙТИ возвращают позицию первого символа в строке.
Синтаксис:
=ПОИСК(искомый_текст,просматриваемый_текст,[начальная_позиция])
=НАЙТИ(искомый_текст,просматриваемый_текст,[нач_позиция])
Функция ПОИСК может быть очень полезна, когда вы хотите отсортировать длинный список доменов по ключевым словам. В случае доменов, которые не содержат искомые ключевые слова, будет возвращено «#ЗНАЧЕНИЕ», тогда как в случае содержащих ключевое слово доменов будет возвращено число. Пример может быть таким:
=ПОИСК(“dog”;A1)
В этом примере мы ищем ключевое слово «dog» в URL. Эта функция возвращает число 12, потому что слово «dog» начинается с 12-го символа в строке с URL.
ЛЕВСИМВ, ПСТР и ПРАВСИМВ
ЛЕВСИМВ возвращает указанное количество первых символов текстовой строки.
Пример: = ЛЕВСИМВ (A1, 7) возвращает первые семь символов слева в строке из ячейки A1.
ПСТР возвращает заданное число знаков из текстовой строки, начиная с указанной позиции.
Пример: =ПСТР(A1,12,3) вернет три символа в строке, начиная с 12-го символа.
ПРАВСИМВ работает аналогичным ЛЕВСИМВ образом, но возвращает символы справа, то есть функция возвращает последний символ или несколько последних символов текстовой строки на основе заданного числа символов. Эта функция может оказаться полезной, например, при удалении блока «http://www.» из каждого доменного имени в списке доменов.
Пример: =ПРАВСИМВ(A1,14) возвращает 14 последних символов в строке.
Эти функции могут быть полезны для вычленения отдельных ключевых слов, доменных имен, данных LSI и другой информации из строки. Например, вы можете скопировать и вставить длинный список обратных ссылок в Excel, а затем создать новую функцию, которая ведет поиск по определенному ключевому слову. Затем следует скопировать и вставить функцию во все ячейки свободного столбца, чтобы задействовать каждый URL. Так вы сможете узнать количество сайтов, в доменных именах которых присутствует определенное ключевое слово.
СЦЕПИТЬ
СЦЕПИТЬ позволит вам объединять строки. Это может быть полезно при объединении отдельных частей URL в один адрес. Данная функция также может быть использована для создания запросов на построение ссылок, карт сайтов и списков ключевых слов.
Пример: = СЦЕПИТЬ(A1,B1) объединит ячейки A1 и B1.
СЛЧИС
Функция СЛЧИС создает случайное число. Она существенно упрощает рандомную сортировку списков информации. Рандомизация является одним из важнейших способов придания результатам вашей работы естественности, а это ключ к успешной кампании по построению ссылок.
Пример: поместив =СЛЧИС() в каждой строке в столбце мы сможем генерировать случайные числа.
После того как вы создали свой список случайных чисел, нужно будет скопировать список и вставить значения в новый столбец таблицы, используя опцию «значение» в параметрах вставки (см. рисунок ниже), потому что случайные числа будут генерироваться каждый раз поле сортировки поля. Путем вставки цифр в виде значений вы гарантируете, что цифры не изменятся.
Ниже приведен пример из пяти URL-адресов, отсортированных случайным образом по соответствующим сгенерированным рандомно цифрам в левом столбце.
Рандомная сортировка списка URL-адресов / доменов может быть полезна при сплит-тестирования различных шаблонов с минимизацией смещений, которые могут быть вызваны отсутствием рандомизации списка.
СМЕЩ
Вот отличный видео туториал (на английском) касательно использования функции «СМЕЩ». Согласно справке Microsoft, СМЕЩ «возвращает ссылку на диапазон, отстоящий от ячейки или диапазона ячеек на заданное число строк и столбцов». Эта функция может оказаться полезной с точки зрения SEO-оптимизатора, к примеру, при анализе флуктуаций ключевых слов в экспортированных файлах Google Analytics CSV.
Синтаксис: СМЕЩ (ссылка, смещ_по_строкам, смещ_по_столбцам, [высота], [ширина])
Продолжим работать с ключевой фразой “dog food” и экспортируем данные из Google Analytics (см. ниже):
В данном примере мы анализируем источники трафика по ключевым словам за неделю в сравнении с предыдущей неделей. Если бы нам требовалось сравнить данные за несколько недель, то проблем бы не было. Тем не менее, когда просматриваешь данные по нескольким тысячам ключевых слов в разрезе множества промежутков времени, то задача существенно усложняется.
С помощью команды СМЕЩ мы можем придать этому списку ключевых слов и диапазонов времени следующий вид:
Группировка данных для создания первой строк сложностей не представляет. После этого мы можем использовать команду СМЕЩ, чтобы переместить ключевые слова и данные по ним за обе недели с помощью следующих формул:
Обратите внимание на то, что команда СТРОКА (ROW на рисунке выше) возвращает номер ячейки, в которой она находится. Это позволяет нам переходить к другим строкам и перемещать данные.
ИНДЕКС и ПОИСКПОЗ
Многие пользователи Excel охотно работают с командой ВПР. Но эта функция ограничена тем, что искомая информация должна находиться в самой первой (слева) колонке массива данных. Команда ИНДЕКС не имеют такого ограничения. Она эффективно возвращает значение из указанной строки и колонки в пределах заданного диапазона.
Синтаксис: ИНДЕКС(массив, номер_строки, [номер_столбца])
Для примера используем формулу =ИНДЕКС(A1:A5,3). С ее помощью мы можем извлечь текст из третьей строки массива в столбце A.
ПОИСКПОЗ повышает полезность ИНДЕКС. В отличие от ИНДЕКС, которая возвращает слово, функция ПОИСКПОЗ возвращает число.
Синтаксис: ПОИСКПОЗ(искомое_значение, просматриваемый_массив, [тип_сопоставления])
- Искомое_значение – буква или фраза, которую вы хотите найти.
- Просматриваемый_массив – массив данных, который вы хотите найти.
- Тип_сопоставления определяет тип сопоставления.
К числу опций Тип_сопоставления относятся:
- «1″ Меньше чем – Это значение по умолчанию. В данном случае функция найдет соответствие меньшее или равное искомому значению при условии, что список упорядочен в порядке возрастания.
- «-1″ Больше чем – Находит наименьшее значение, которое больше или равно искомому значению, при условии, что список упорядочен в порядке убывания.
- «0″ возвращает первое найденное значение, равное вашему искомому значению.Это рекомендуемая опция для целей SEO.
Используем пример выше с массивом данных по кормам для питомцев (pet food). Следующая формула вернет расположение (номер строки) фразы «guinea pig food» в массиве данных.
=ПОИСКПОЗ(“guinea pig food”,A1:A5,0)
В этом случае функция возвращает число 4, так как ключевая фраза «guinea pig food» проходит в нашем списке под четвертым номером.
Комбинируем ИНДЕКС и ПОИСКПОЗ
Вы можете комбинировать ИНДЕКС и ПОИСКПОЗ для решения более сложных задач. Комбинируйте их, используя следующий метод:
=ИНДЕКС(массив, формула ПОИСКПОЗ)
В качества примера давайте снова возьмем наш список ключевых слов о корме для собак (dog food) с несуществующего сайта dogfoodrus.com. В приведенной ниже таблице имеются разные ключевые слова, которые являются источником органического трафика для определенных URL-адресов целевой страницы. В каждой строке приводится количество посещений и переходов, связанных с конкретным ключевым словом.
Используя команды ИНДЕКС и ПОИСКПОЗ вместе, можно создать механизм поиска ключевых слов, который позволит вам получить информацию о количестве посетителей и переходов по определенному ключевому слову. Если у вас есть только четыре или пять ключевых слов (как в примере выше), искать нужные данные вручную несложно. Однако если вы скачали файлы Analytics или Google Webmaster Tools с данными о тысячах ключевых слов, то использование приведенных ниже формул существенно упрощает поиск определенных ключевых слов и создание определенного списка.
=ИНДЕКС($B$3:$E$7,ПОИСКПОЗ(B11,$B$3:$B$7,0),3) – число посещений
=ИНДЕКС($B$3:$E$7,ПОИСКПОЗ(B11,$B$3:$B$7,0),4) – число переходов
В этом примере функция ИНДЕКС распространяется на диапазон от B3 до E7, который охватывает все импортированные данные. Функция использует команду ПОИСКПОЗ, чтобы определить строку в массиве, где найдено совпадение. Цифры 3 и 4 в конце формулы относятся к столбцам, где находятся данные, которые вам нужно скопировать.
Есть много вариаций на тему применения ИНДЕКС и ПОИСКПОЗ вместе. С их помощью можно на порядок упростить поиск информации в огромной таблице. В отличие от ВПР, для эффективной работы с ИНДЕКС и ПОИСКПОЗ не требуется, чтобы поле поиска находилось в крайнем левом столбце массива.
Заключение
Это наиболее востребованные функции Excel при работе над кампаниями по SEO-продвижению. Excel можно использовать для многих SEO-задач, в том числе для работы с обратными ссылками, для отслеживания ключевых слов, просмотров, переходов и многого другого.