Эта статья поможет вам научиться извлекать значимую информацию из общей массы данных, которые вы получаете, оптимизируя сайт. Чтобы научиться обрабатывать данные, лучше прочесть руководство от начала и до конца. Однако, если вы уже знакомы с основами обработки данных в Excel, может пропустить некоторые пункты.
Люди попадают в SEO-индустрию из различных областей. Это может быть маркетинг, разработка ПО, web-дизайн, бизнес, и те, кто просто “попал в SEO”. Некоторые вас, вероятно, уже занимались обработкой данных для SEO в Microsoft Excel, но многие — нет. Если раньше вы мало работали в Excel и знаете эту программу только поверхностно, эта статья для вас.
За последние несколько лет SEO пополнилось множеством новых полезных данных, которые вы бы не получили раньше. Это произошло благодаря появившимся инструментам, таким как Open Site Explorer, новому Google Webmaster Tools и Google Analitycs, Majestic SEO многим другим. Кроме того, клиенты стали мудрее, осторожнее, и разбираются в SEO лучше, чем пару лет назад. Они хотят получать услуги, основанные на надежных данных, и разве можно их в этом винить?
Большинство SEO-оптимизаторов сталкиваются с проблемой сложного анализа данных, чтобы улучшить стратегию продвижения или создать полный всесторонний отчет, и им кажется, что функционала Microsoft Excel недостаточно. В этом руководстве мы попытаемся доказать обратное.
ГЛАВА 1. БАЗОВЫЕ ЗАДАЧИ
Вы этой главе мы рассмотрим некоторые простые функции Excel и их роль в решении повседневных задач SEO. Функции, которые мы рассмотрим:
- СЦЕПИТЬ
- Текст по столбцам
- COUNTIF
- IFERROR
СЦЕПИТЬ (Concatinate)
Определение Microsoft Excel: Соединяет несколько текстовых строк в одну текстовую строку.
Синтаксис: СЦЕПИТЬ (текст1; текст2; …).
Смысл функции становится понятен уже из ее названия — сцеплять несколько текстов в один, однако это не делает ее менее ценной.
Пример использования функции:
Текст по столбцам (Text to Columns)
Определение Microsoft Excel: Разделяет содержимое одной ячейки на отдельные столбцы.
В качестве примера возьмем отчет из программы Xenu’s Link Sleuth, и разделим полученные URL-адреса на домен, каталоги и файлы.
- Выберите столбец “Address”:
- Используйте функцию “Текст по столбцам” в меню “Данные”:
- В появившемся окне “Мастер текстов” укажите формат данных “с разделителями”. Затем выберем в качестве разделителя символ слеш:
Получится таблица, содержащая домены, каталоги и файлы из отчета в отдельных столбцах:
СЧЁТЕСЛИ(Countif)
Определение Microsoft Excel: Считает количество ячеек, содержимое которых соответствует определенным условиям.
Синтаксис: СЧЁТЕСЛИ (диапазон; критерии).
Используя упомянутый выше отчет, найдем количество файлов, являющихся html-страницами. Для столбца “Type” используем функцию “Счётесли” с условием что значение равно “text/html”:
ЕСЛИОШИБКА (Iferror)
Определение Microsoft Excel: Если формула вычисляется с ошибкой, выдается указанное в функции сообщение. В противном случае выдается результат.
Синтаксис: ЕСЛИОШИБКА (функция; значение если ошибка).
Допустим, для какой-нибудь живописной диаграммы отчета нам необходимо получить соотношение, во сколько раз количество страниц сайта со статусом 200 OK превышает количество страниц с 301-м редиректом. Используя данные нашего отчета из Xenu’s и изученную ранее функцию “СчётЕсли” мы без труда подсчитаем количество таких страниц.
Для подсчета соотношения страниц используем простое деление. При этом, если у нас нет страниц с редиректом, будет выведено сообщение, указанное в функции “ЕслиОшибка”:
ГЛАВА 2. РАБОТА С ТЕКСТОМ
Функции, на которых мы остановимся в этой главе, полезны при манипуляциях с текстом. Как вы увидите из примеров, в SEO немало ситуаций, где нужно манипулировать с текстовыми строками. Некоторые функции, о которых мы будем говорить довольно просты по-отдельности, но в их совместной работе можно легко запутаться.
Мы рассмотрим:
- ДЛСТР,
- ПОИСК/НАЙТИ,
- ЛЕВСИМВ, ПРАВСИМВ, ПСТР.
ДЛСТР (LEN)
Определение Microsoft Excel: Возвращает количество символов в текстовой строке.
Синтаксис: ДЛСТР (текст):
Эту функцию мне полезно использовать при подготовке текстов для сайта, т.к. всегда полезно знать, сколько знаков в той или иной статье.
ПОИСК/НАЙТИ
Определение Microsoft Excel:
ПОИСК — возвращает порядковый номер символа в строке (слева), на котором был найден искомый символ или подстрока. Не чувствителен к регистру.
НАЙТИ — возвращает порядковый номер символа в строке, на котором была найдена искомая подстрока. Чувствителен к регистру.
Синтаксис: ПОИСК (искомый текст; где искать текст, с какого символа начинать искать),
НАЙТИ (искомый текст; где искать текст, с какого символа начинать искать)
Между ПОИСК и НАЙТИ существуют два различия:
1. ПОИСК нечувствителен к регистру, НАЙТИ — чувствителен.
2. В функции ПОИСК можно использовать шаблоны поиска, в НАЙТИ — нет.
Большинство проблем поиска текстовых строк способна решить функция ПОИСК. Однако, не забывайте про НАЙТИ — она будет полезна, если необходимо отличать вхождения с заглавной буквы от строчной.
В примере ниже получим номер символа в URL, где упоминается подстрока “marketing” в исследуемом отчете. Получим несколько ячеек со значениями и множество ошибок:
Добавим к нашей конструкции функцию обработки ошибок ЕСЛИОШИБКА:
Функция ПОИСК используется внутри функции ЕСЛИОШИБКА. Такая конструкция называется вложенной функцией. При необходимости, внутрь функции ПОИСК можно вложить еще одну — Microsoft Exel обеспечивает практически бесконечную вложенность функций.
ЛЕВСИМВ, ПРАВСИМВ, ПСТР
Определение Microsoft Excel:
ЛЕВСИМВ возвращает указанное количество знаков с начала строки текста.
ПРАВСИМВ возвращает указанное количество знаков с конца строки текста.
ПСТР возвращает заданное число знаков из строки текста, начиная с указанной позиции.
Синтаксис:
ЛЕВСИМВ (текст; число знаков),
ПРАВСИМВ (текст; число знаков),
ПСТР (текст; начальная позиция, число знаков),
ЛЕВСИМВ и ПРАВСИМВ
Пример 1. Используем функции вместе в следующем примере — из списка URL-адресов нам нужно получить только домены:
Рассмотрим используемую вложенную функцию поподробнее.
При помощи функции ПОИСК(“/”;А5;8) получаем номер символа слеш (/), расположенного после доменной зоны в ячейке А5. Первые 2 слеша (в конструкции “http://”) мы благополучно пропускаем, задав в функции число “8” — поиск начнется с восьмого символа строки. Искомый слеш находится на 23-й позиции в ячейке, то есть, функция вернет значение 23.
Используя =ЛЕВСИМВ(A5; ПОИСК(«/»;A5;8)), мы получаем первые 23 символа из ячейки А5 (первый параметр команды ЛЕВСИМВ).
Распространив формулу на все ячейки в столбе, мы извлечем домены из всех имеющихся URL-адресов.
ПСТР
Пример 2. У нас есть интернет-магазин, у которого URL-адреса страниц состоят из названия товара и его артикула такого вида:
http://site.ru/nosogrelka-t12667.html
Необходимо получить из адресов название товаров.
Для решения этой задачи мы используем поиск (по шаблону) и функцию ПСТР. ПСТР используется так как текст мы извлекаем из середины строки. Кроме того, поскольку названия у товаров имеют разную длину, ее нужно будет рассчитать для каждой ячейки в отдельности, для чего мы и используем шаблон поиска.
Здесь стоит вспомнить синтаксис используемых функций:
ПОИСК (искомый текст; где искать текст; с какого символа начинать искать)
ПСТР (текст; начальная позиция, число знаков)
Составим формулу используя страницу: http://site.ru/nosogrelka-t12667.html
=ПСТР(A2;ПОИСК(«/»;A2;8);ПОИСК(«-t*.html»;A2)-ПОИСК(«/»;A2;8))
Результатом работы этой формулы будет: “/nosogrelka”
Функция ПОИСК(«/»;A2;8) возвращает позицию символа слеш после доменной зоны, в случае сhttp://site.ru/ оно равно 15. Таким образом, для понимания, эту формулу можно упростить до ПСТР(A2;15;ПОИСК(«-t*.html»;A2)-15). То есть, ПСТР будет выполняться с 15-го символа.
ПОИСК(«-t*.html»;A2) возвращает позицию начала артикула с расширением файла “-t12667.html”. В рассматриваемом примере значение этой функции равно 26. Теперь формула еще больше упрощается: ПСТР(A2;15;26-15).
Таким образом, мы берем часть строки, начинающуюся после доменной зоны “ru”. Длина этой строки будет равна длине URL-адреса за исключением артикула товара и домена сайта.
Свежие комментарии