Seo для excel: Seo Excel — надстройка для оптимизаторов. – Excel для SEO. Основы с примерами

Содержание

Microsoft Excel для SEO специалистов

Каждый SEO специалист в процессе своей работы взаимодействует с различными инструментами, сервисами, плагинами, модулями и прочим. В данный статье мы опишем самый главный инструмент SEO специалиста - Microsoft Excel и его важные функции.

Для чего используются те или иные формулы, функции, диаграммы и сводные таблицы? Потому что каждый день SEO специалист встречается с большим объемом данных, которые требуется структурировать, упрощать и получать в доступном виде, и самое главное - уменьшение затрачиваемого времени на задачи.

Урок 1:

 

1.1. Текст по столбцам:

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

Использование текста по столбцам на примере выгруженного файла из Google Search Console содержащим наиболее часто ссылающиеся домены на сайт

Текст по столбцам на примере выгруженного файла из Google Search Console содержащим наиболее часто ссылающиеся домены на сайт

 

1.2. Удаление дубликатов:

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

Удаление дубликатов на примере отчетов Google Analytics и Яндекс Метрики

Удаление дубликатов на примере отчета бесплатный поисковый трафик Google Analytics и отчета поисковые фразы из Яндекс Метрики

 

1.3. Фиксация первой строки / столбца:

1.3.1 Фиксируем первую строку:

Фиксация первой строки в Excel

Фиксация первой строки

 

1.3.2. Фиксируем первый столбец:

Фиксация первого столбца в Excel

Фиксация первого столбца

 

1.3.3. Фиксируем первый столбец и строку одновременно:

Одновременная фиксация первой строки и первого столбца в Excel

Одновременная фиксация первой строки и первого столбца

 

1.3.4. Фиксируем >1 строки / столбца

Фиксация > 1 строки / столбца в Excel

Фиксация > 1 строки / столбца

 

1.4. Копирование значений из колонки, вычисленной по формуле:
Использование вставки значения в ячейке, вычисленной по формуле

Копирование значения ячейки, вычисленной по формуле

 

1.5. Условное форматирование:
Использование условного форматирования для подсветки числа меньше 11 (зеленым) и больше 10 (красным)

Форматирование таблицы с обозначением следующих условий: если число меньше 11 (подсвечивать зеленой заливкой), если больше 10 (подсвечивать красной заливкой)

 

1.6. Настройки:

1.6.1. Автосохранение:

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

Изменение стандартного времени автосохранения в Excel

Изменение стандартного времени автосохранения

1.6.2. Создание дополнительной вкладки:

Дополнительные вкладки помогают сгруппировать наиболее необходимые команды на одной вкладке.

Создание дополнительной вкладки в Excel

Создание дополнительной вкладки

 

1.7. СЦЕПИТЬ:

=СЦЕПИТЬ("Redirect 301 ";A2;" ";B2)

Использование функции СЦЕПИТЬ на примере настройки 301 редиректов со старой структуры на новую

Функция СЦЕПИТЬ на примере настройки 301 редиректов со старой структуры на новую

 

1.8. СЧЁТЗ:

=СЧЁТЗ(A2:A560) - подсчитывает количество непустых ячеек в диапазоне.

Использование функции СЧЁТЗ на примере подсчета непустых ячеек с условием что в диапазоне имеются пустые ячейки

Функция СЧЁТЗ на примере подсчета непустых ячеек с условием что в диапазоне имеются пустые ячейки

 

1.9. СЧЁТЕСЛИ:

=СЧЁТЕСЛИ(B2:B18;"<11") - подсчитывает количество ячеек в диапазоне соответствующих заданному условию.

Использование функции СЧЁТЕСЛИ на примере подсчета количества ячеек в которых число меньше 11

Функция СЧЁТЕСЛИ на примере подсчета количества ячеек в которых число меньше 11

 

1.10. Фиксация положения одной из ячеек в формуле:

=СЧЁТЗ(A2:A18) - подсчитывает количество непустых ячеек в диапазоне.

=СЧЁТЕСЛИ(B2:B18;"<11")/$A$19 - фиксирует положение ячейки (столбца и строки) в формуле.

Фиксация положения одной из ячеек в формуле при копировании

Копирование формулы с фиксацией положения одной из ячеек

 

Статистика Excel для SEO и анализа данных

Команда SEOnews

Сегодня практически каждый понимает, что нет таких данных, которые было бы невозможно получить. Чтобы получить данные по сайту, используются бесплатные инструменты или тратятся огромные суммы денег на платные инструменты, чтобы получить еще больше информации. Неважно какую информацию вы ищите, вы точно ее найдете и вопрос лишь в том — бесплатно или за деньги.

У всех инструментов есть кое-что общее — это кнопка «Экспорт». Возможно, это самое важное свойство всех инструментов. Сэкспортировав данные в Excel, специалист может сортировать и фильтровать их так, как ему нужно. Большинство из нас регулярно пользуется Excel, но только его стандартными функциями. Однако Excel может гораздо больше!

Виргил Гик (Virgil Ghic) рассказал о наиболее распространенных приемах обработки статистических данных. И самое главное — вам не придется их запоминать, все они встроены в Excel.

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

В статистике можно выделить два главных направления: описательная статистика и логически выведенная статистика.

Описательная (дескриптивная) статистика используется в том случае, когда вам известны все значения в наборе данных. Например, вы задаете 1000 респондентов вопрос, любят ли они апельсины, и предоставляете им два варианта ответа: Да и Нет. Затем собираете данные и выясняете, что 900 человек ответили Да и 100 — Нет. Пропорция будет следующей: 90% составил ответ Да и 10% — ответ Нет. Достаточно легко, не правда ли?

Но как быть в том случае, когда у нас нет всех данных?

В случае когда у вас только часть данных на помощь придет логически выведенная статистика. Она используется тогда, когда вы знаете только небольшую часть всех данных и вам необходимо сделать предположение о всем объеме данных.

Давайте предположим, что вы хотите рассчитать количество просмотров email за последние два года, но вы располагаете данными только за последние шесть месяцев. Предположим, что из 1000 email-адресов письма открыли только 200 получателей, значит остальные 800 — не открывали. Следовательно мы имеем соотношение 20% открывших к 80% неоткрывших. Эти данные верны для периода в шесть месяцев, но они также могут быть верны и для периода в два года. Логически выведенная статистика поможет нам понять, насколько верно наше предположение.

Доля открытых писем может составлять 20%, а может немного отличаться. Допустим, она варьируется +/-3%, тогда доля открытых писем будет составлять от 17% до 23%. Но насколько мы уверены в этих данных? Кроме того, какой процент случайной выборки из всего набора данных будет находиться в диапазоне от 17% −23%?

В статистике считается приемлемым уровень достоверности в 95%. Это означает, что 95% выборочных данных, взятых из всего набора данных, будет соответствовать 17-23%, оставшиеся 5% будут либо выше 23%, либо ниже 17%. Но мы уверены в том, что для 95% доля открытых писем составляет 20% +/- 3%.

Термин данные (data) предполагает любую величину, обозначающую объект или событие, например, посетители, исследования, письма.

Термин набор данных (data set) состоит из двух компонентов: Единица наблюдения (observation unit) может означать посетителей и переменные, представляющие демографические данные ваших посетителей (возраст, зарплата, образование). Совокупность (population) предполагает каждого члена вашей группы, а в веб-аналитике — всех посетителей. Предположим, посетителей 10 000.

Выборка

(sample) — часть вашей совокупности, представленная на основе определенной даты или сконвертированных посетителей и т.д. В статистике наиболее ценной является случайная выборка (random sample).

Распределение данных (data distribution) определяется частотой, согласно которой представлены значения в наборе данных. Представив частоту на графике с диапазоном значений на горизонтальной оси и частотой по вертикальной оси, мы получим кривую распределения. Наиболее распространенным является нормальное распределение или колоколообразная кривая.

Самый простой способ понять это — представить на количестве посетителей веб-сайта. Например, в среднем ежедневное количество посещений сайта составляет 2000, иногда бывает больше — 3000 посещений или меньше — 1000.

Здесь пригодится теория вероятности (probability theory).

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

Самым распространенным примером вероятности, известным многим, является подбрасывание монеты. У монеты две стороны: орел и решка. Какова вероятность того, что монета ляжет той или другой стороной? Существует две возможности, таким образом 100%/2=50%

Достаточно теории, перейдет к практике.

Excel — прекрасный инструмент, который поможет нам в работе со статистикой. Отметим, что это не лучший инструмент, но зато все знают, как им пользоваться, поэтому рассмотрим именно Excel.

Во-первых, установите надсройку Analysis ToolPack.

Откройте Excel, перейдите в Опции -> Add-ins->внизу списка вы найдёте

Нажимайте Go ->выберите Analysis ToolPack->и нажимайте OK.

Теперь в панеле выберите опцию Данные и найдите там Анализ данных.

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

Среднее, Медиана и Мода

Среднее (mean) это статистическое значение среднего значения, например, средним для 4,5,6 будет 5. Как рассчитать среднее значение в Excel? =average(число1,число2 и т.д.)

Mean=AVERAGE(AC16:AC21)

Путем вычисления среднего мы определяем, сколько мы продали в среднем. Эта информация полезна, если нет экстремальных значений (или выбросов). Почему?

Например, мы продали в среднем товаров на $3000, но на самом деле нам повезло, т.к. 6 сентября покупатели потратили больше. В предыдущие шесть дней товара было куплено в среднем лишь на $618. Исключив крайние значения от среднего, можно получить более репрезентативные даные.

Медиана (median) это значение, которое делит набор данных на две равные части. Например, для набора данных 224, 298, 304 медианой является — 298. Для того чтобы вычислить среднее для большого набора данных, можно использовать следующую формулу =MEDIAN(224,298,304).

Когда может пригодиться медиана? Медиана полезна, когда у вас есть неравномерное распределение, например, цена ваших конфет варьируется от $3 до $15 за упаковку, но также у вас есть очень дорогие конфеты за $100, которые покупают редко. В конце месяца вы делаете отчет, и вы видите, что вы продали в основном дешевые конфеты и только пару упаковок за $100. В этом случае вам будет полезен расчет медианы.

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

Мода (mode) самое распространенное значение, например, мода для: 4,6,7,7,7,7,9,10 это 7.

Рассчитать моду в Excel вы можете с помощью формулы =MODE(4,6,7,7,7,7,9,10).

Но имейте в виду, что Excel выдает за моду наименьшее значение из возможных. Например, вы рассчитываете моду для следующего набора данных: 2,2,2,4,5,6,7,7,7,8,9, сразу отметим, что здесь две моды — 2 и 7, но Excel покажет вам только наименьшее значение — 2.

Когда можно использовать функцию моды? Расчет моды полезен только для целых чисел, например 1, 2 и 3. И нежелателен для дробных чисел, таких как 1,744; 2,443; 3,323, т.к. числа могут дублироваться.

Гистограммы

Предположим, недавно в вашем блоге была опубликована сотня гостевых постов, некоторые из них хорошего качества, другие не очень. Возможно, вы захотели узнать, какие из постов получили по 10, 20, 30 обратных ссылок или вам интересны твиты, лайки, расшаривания, а может и просто посещения.

Мы разделили все это на группы с помощью графического представления данных под названием гистограмма. Виргил Гик (Virgil Ghic) приводит пример с посещениями и постами, как один из менее сложных. Он настроил свой аккаунт в Google Analytics следующим образом: у него есть профиль, в который собирается статистика только по его блогу, ничего больше. Если у вас нет такого же профиля, тогда вы можете использовать сегменты.

Это несложно.

Далее идем в экспорт ->CSV

Открываем Excel и создаем два столбца: Целевая страница и Посещения. Также создаем список, в соответствии с которым будем категоризировать данные. В данном случае мы определяем, сколько статей имеют 100, 300, 500 и т.д. посещений.

Данные -> Анализ данных->Гистограммы->OK

  • Входной интервал (input range) будет столбец с посещениями.
  • Интервал карманов (bin range) — это группы.
  • Выходной интервал (output range), кликните на ячейку, где вы хотите создать гистограмму.
  • Проверьте график выхода (chart output).
  • Нажимаем OK.

Вы получили гистограмму, которая отражает количество статей, сгруппированных по посещениям. Чтобы лучше разобраться в гистограмме, нужно кликнуть на любую ячейку в столбцах Bin и Frequency и отфильтровать частоту от меньшего к большему.

Анализировать данные теперь еще проще. Возвращаемся и фильтруем все статьи от меньшего или равного 100 посещениям (Визиты, выпадающее меню->Числовые фильтры->Между...0-100->Ok) в прошлом месяце и обновляем.

Источники посещений

Насколько значим данный отчет для вас?

Он достаточно хорош, но не более того. Мы можем проанализировать рост и снижение посещений, но ... какова доля посещений с YouTube в общей статистике посещений за февраль? Конечно, можно разбираться, но это дополнительная работа, и это очень неудобно, когда этот вопрос вам задает клиент по телефону. Чтобы ваши графики были максимально полезны, создавайте описательные отчеты.

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

Что мы видим в мае: доля переходов с Facebook в общей статистике посещений больше обычного. Почему? Возможно, в мае рекламная кампания оказалась более эффективной, чем в другие месяцы, это и привело к росту трафика с Facebook. Если дело в рекламной кампании, давайте повторим ее.

Однако правильней будет провести хи-квадрат тест (Chi-Square Test), который позволит нам понять была ли это счастливая случайность или эффективная маркетинговая кампания.

Фактический столбец — количество посещений, Ожидаемый столбец — среднее из «фактического» столбца. Формула хи-квадрат теста следующая: =1-CHITEST(N10:N16,O10:O16), где N10:N16 — это значения из Актуального столбца, а O10:O16 — значения из Ожидаемого.

Результат в 100% является уровнем достоверности, свидетельствующий о вероятности того, что рост посещений с Facebook является результатом маркетинговых кампаний.

Создавая метрики, помните, они должны быть максимально понятными и релевантными вашей бизнес-модели.

В данном видео вы найдете еще один пример использования хи-квадрат теста.

Скользящее среднее (moving average) и линейная регрессия (linear regression) для прогнозирования

Мы часто встречаем такие графики, как расположенный выше. На них могут быть представлены продажи, посещения и т.д. И они всегда выглядят именно так: прямая, идущая вверх-вниз. В такой картине данных присутствует много шума, который мы хотим сгладить для лучшего понимания данных.

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

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

Шаг 1:

Экспортируйте в Excel число посещений/продаж за долгий период времени, например, один-два года.

Шаг 2:

Данные-> Анализ данных -> Скользящее среднее ->OK

Входной интервал — это столбец с числом посещений.

Интервал — это количество дней для которых вычисляется среднее. Вам нужно создать одно скользящее среднее с большим числом, например, 30 и одно с меньшим числом, например, 7.

Выходной интервал — это столбец справа от столбца посещений.

Повторите данные шаги для интервала в 7 дней.

Теперь ваши данные выглядят примерно так:

Если вы выберете все столбцы и построите линейный график, вы получите следующее:

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

В конце линейного графика вы увидите такие значения, как Прогноз. Это прогнозируемые данные, выведенные на основе предыдущих трендов.

В Excel есть два способа создать линейную регрессию, используя формулу =FORECAST(x,known_y’s, known_x’s), где «означает дату, для которой вы создаете прогноз; «known_y’s» — это столбец посещений, «known_x’s» — столбец с датами. Данный метод не так уж сложен, но есть более простой способ сделать то же самое.

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

Убедитесь в том, что вы выбрали весь набор данных для того, чтобы результат был точный.

Существует теория при сравнении скользящего среднего для 7дней и 30дней. Как было сказано выше линия 7дней реагирует практически на все основные изменения, в то время как линии 30дней требуется больше времени, чтобы изменить свое направление. Как правило, когда скользящее среднее 7дней пересекает скользящее среднее 30дней, вы можете рассчитывать на существенное изменение, которое будет длиться дольше, чем день или два. Как можно увидеть выше, 6 апреля скользящее среднее 7дней пересекает скользящее среднее 30дней, число посещений снижается, у 6 июня линии снова пересекаются и тренды идут вверх. Этот метод полезен, когда вы теряете трафик и не уверены, тренд ли это или всего лишь суточные колебания.

Линия Тренда (trendline)

Те же результаты могут быть получены с помощью линии тренда в Excel: щелкните правой кнопкой мыши по движущейся линии -> выберите: Добавить линию тренда (Add Trendline).

Теперь вы можете выбрать Тип Регрессии (Regression Type) и использовать функцию Прогноз. Линия тренда, возможно, наиболее полезный инструмент, помогающий разобраться почему ваш трафик/продажи растут, сокращаются или остаются неизменными.

Без линейной функции мы не сможем уверенно говорить о том, что мы делаем правильно, а что нет. Добавляя линейный тренд мы видим, что наклон положительный, уравнение линии тренда объясняет, как движется наш тренд.

y=0.5516x-9541.2

X — это количество дней. Коэффициент Х — 0.5516 — положительное число. Это означает, что линия тренда идет вверх. Т.е. ежедневно мы увеличиваем количество посетителей на 0,5, это является трендом.

R^2 — это уровень точности модели. Наше число R^2 = 0,26 означает, что наша модель объясняет 26% вариаций. Проще говоря, мы уверены на 26%, что ежедневно количество посетителей увеличивается на одного.

Сезонное прогнозирование

Предположим, скоро Рождество. Прогнозирование на зимний сезон будет весьма полезно, особенно когда с этим периодом вы связываете большие надежды.

Если вы не попали под Google-фильтры Panda или Penguin и ваши продажи/посетители соответствуют сезонным тенденциям, вы можете спрогнозировать характер продаж или посещений.

Сезонное прогнозирование — это метод, который позволяем нам оценить будущие значения набора данных на основе сезонных колебаний. Сезонные наборы данных есть везде, например, магазин мороженого будет очень востребован во время летнего сезона, а сувенирный магазин может достичь максимальных продаж во время зимних праздников.

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

Следующий пример представляет собой базовую модель, но она может быть расширена до более сложных, чтобы отвечать вашей бизнес-модели.

Загрузите Пример прогнозирования в Excel

Для удобства восприятия я разобью весь процесс на этапы. Вам нужно загрузить таблицу Excel и выполнить следующие шаги:

  • Экспортируйте ваши данные; чем больше данных, тем более точным будет прогноз! Укажите даты в столбце А, а продажи в столбце В.
  • Рассчитайте индекс для каждого месяца и добавьте полученные данные в столбец С.

Для расчета индекса прокрутите вниз, справа вы найдете таблицу под названием Индекс (Index). Индекс за январь 2009 рассчитывается путем деления продаж за январь 2009 г. на среднее значение продаж за весь 2009 год.

Таким же образом рассчитайте индекс для каждого месяца каждого года.

В столбце S с 38 по 51 строки мы рассчитали средний индекс для каждого месяца.

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

  • В столбце D рассчитайте Скорректированные данные (Adjusted data) путем деления ежемесячных продаж на индекс =B10/C10.
  • Выберите значения из столбцов A, B и D и постройте линейный график.
  • Выберите скорректированную линию (в моем случае это красная линия) и добавьте линейный тренд, проверьте окошко «Показать уравнение на графике».

  • Рассчитайте несезонные значения для прошлого периода путем умножения ежемесячных продаж на коэффициент из уравнения линии тренда и добавьте константу из уравнения (столбец Е).

После создания линии тренда и представления Уравнения на графике, мы принимаем во внимание Коэффициент — число, которое умножается на X, и константу — число, которое, как правило, является отрицательным.

Проставляем коэффициент в ячейку E2, а Константу — в ячейку F2.

  • Рассчитайте Сезонные значения для прошлого периода путем умножения индекса (столбец С) на ранее рассчитанные данные (столбец Е).
  • Рассчитайте средний процент ошибки (MPE — mean percentage error) путем деления продаж на Сезонные значения для прошлого периода минус 1 (=B10/F10-1).
  • Рассчитайте средний абсолютный процент ошибки (MAPE — mean adjusted percentage error) путем возведения в квадрат данные в стобце MPE (=G10^2).

В моих ячейках F50 и F51 представлены спрогнизованные данные для ноября 2012 и декабря 2012. Ячейка H52 демонстрирует погрешность.

С помощью данного метода мы можем определить, что в декабре 2012 мы заработаем $22,022 ± 3.11%. Теперь идем к боссу и рассказываем о своих предположениях.

Стандартное отклонение

Стандартное отклонение (standard deviation) говорит о том, насколько наши значения отклоняться от среднего значения, мы можем назвать его уровнем доверия. Например, у вас есть данные по продажам за месяц и данные по ежедневным продажам, причем каждый день объем продаж разный. Вы можете использовать стандартное отклонение чтобы рассчитать, насколько вы отклонились от среднемесячного показателя.

Вот две формулы Стандартного отклонения в Excel, которые вы можете использовать

=stdev — когда у вас есть выборочные данные -> Авинаш Кошик подробно рассказывает, как работает выборка

или

=stdevp — когда у вас полная совокупность данных, т.е. когда вы анализируете каждого посетителя. Я предпочитаю именно =stdev, потому что бывают случаи, когда код отслеживания JS не работает.

Давайте посмотрим, как мы можем применить стандартное отклонение в нашей повседневной жизни.

Используя стандартное отклонение в Excel, данные можно представить более наглядным и понятным образом. Как вы видеть на графике выше, средняя ежедневная посещаемость равна 501 со стандартным отклонением 53. Но самое главное на таком графике вы видите, где вы превысили свои обычные показатели. Это позволит выделить те маркетинговые кампании, которые привели к такому всплеску, и применить/проверить их еще раз.

Для работы используйте данную ссылку: http://blog.instantcognition.com/wp-content/uploads/2007/01/controllimits_final.xls

Корреляция

Корреляция — это статистическая взаимосвязь двух или нескольких (случайных) переменных. Типичным примером корреляции в веб-аналитики может быть количество посетителей и количество продаж. Чем больше у вас целевых посетителей, тем больше будет продаж. У доктора Пита (Dr Pete) есть хорошая инфографика, посвященная корреляции vs. причинности.

В Excel мы используем следующую формулу для определения корреляции:

=correl(x,y)

Как вы видит на картинке выше, корреляция между Посещениями и Продажами равна 0.1. Что это значит?

  • от 0 до 0,3 считается слабой корреляцией
  • от 0.3 до 0,7 — нормальная
  • более 0,7 — сильная

Заключение следующее: ежедневные посещения не влияют на ежедневные продажи, что также означает, что посетители, которых вы привлекли, не являются целевыми. При принятии решения полагайтесь на ваше деловое чутье, но не игнорируйте корреляцию в 0,1.

Если вы хотите определить корреляцию между тремя и более переменными, используйте функцию корреляции в разделе Анализ данных.

Данные->Анализ данных->Корреляция

Ваш результат будет похож на один из этих:

Здесь мы видим, что ни один из элементов не коррелируют друг с другом:

  • Продажи и посетители = корреляция 0,1.
  • Продажи и расшаривания = корреляция 0,23.
  • Описательная статистика для быстрого анализа.

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

  • Данные->Анализ данных-> Описательная статистика.
  • Входной интервал — выбираем данные для анализа.
  • Выходной интервал — выбираем ячейку, где отобразится таблица.
  • Проверяем Сводную статистику.

Достаточно хороший результат:

Нам осталось разобраться с тем, что такое Эксцесс (kurtosis) и Асимметрия (skewness).

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

Асимметрия показывает, насколько искажены ваши данные — негативно или позитивно, по сравнению с нормальным распределением. Теперь представим асимметрию более наглядно:

Асимметрия: −0.28 (распределение, скорее всего, ориентировано на более высоких значений 2500 и 3000).

Эксцесс: −0.47 (небольшое пиковое отклонение от центра).

Все эти методы можно использовать при анализе данных. Самой большой сложностью со статистическими данными и Excel является возможность применения этих методов в самых различных ситуациях, не ограничиваясь посещениями или продажами. Отличный пример использования нескольких статистических подходов представил Том Энтони в своем посте об инструменте для определения ссылочного профиля (Link Profile Tool).

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


SEM & SEO макросы - !SEMTools для Excel

Инструменты для PPC и SEO специалистов

Раздел SEM & SEO содержит в себе функции необходимые для поискового маркетинга.

Чтобы не смешивать эти профессиональные функции с общими, он был выделен в отдельный блок. Раздел частично копирует структуру всей надстройки — содержит меню «извлечь», «удалить», и «изменить»
Этот раздел будет пополняться новыми функциями так как рынок поисковой рекламы динамично развивается и ему нужны новые инструменты.  

Изменить

Данный раздел содержит в себе возможности по изменению
— Типов соответствия ключевых фраз.
— Словоформ — лемматизация
— Файла загрузки рекламных кампаний — Яндекс.Директа в формат Google Ads.

Удалить

Меню удалить с функциями исключительно для поискового маркетинга содержит в себе
1. удаление стоп слов
2. удаление utm-меток и get-параметров
3. удаление модификаторов у стоп слов

Извлечь 

Меню извлечь содержит возможности по извлечению текстовой информации из URL. Сюда входит такая мета-информация, как Title, h2, коды ответа сервера.

Извлечение гиперссылок из анкорных ссылок может пригодиться, когда вы копипастите ссылки с какого нибудь сайта (например, конкурента) и хотите быстро извлечь из них непосредственно адреса ссылок.

Поисковые подсказки

Раздел содержит различные способы сбора поисковых подсказок из таких поисковых систем как Yandex, Google, YouTube, Amazon, Bing.

Семантический анализ

В этой группе макросов  три типа  возможностей . 

Частотный анализ n-грамм (последовательностей из n слов)

Анализ поисковых запросов

Поиск не релевантных слов в массиве фраз

Кластеризация

Данный макрос производит статистическую обработку семантического ядра на основе анализа частотности встречаемости слов в нем.

Сборник полезных функций Excel для работы SEO-специалиста ~ Страницы Интернета

+

SEO-специалисты ежедневно решают массу важных задач, связанных с оптимизацией. И при этом важным является использование только качественных инструментов для решения тех или иных задач. Молодые начинающие веб-специалисты, как правило, такие обычные в практике SEO проблемы, как сортировка и анализ массивов данных, работа со строками, агрегация или разбивка данных, отслеживание статистики ключевых слов, мониторинг обратных ссылок— выполняют вручную, затрачивая много времени на монотонные, часто повторяющиеся и легко автоматизируемые задачи. Некоторые SEO-специалисты в силу своего незнания ищут готовое узкофункциональное решение для каждой возникающей проблемы или еще интереснее - пишут скрипты, решающие все остро стоящие перед ними дилеммы, с которыми они сталкиваются. А другие используют дорогие профессиональные средства - такие программы, как Deductor для формирования срезов данных, TextPipe для работы со строками и многие другие, для совершенно базовых операций. 

Потому что, большинство веб-мастеров не знают о том, что множество наших проблем может решить программа Microsoft Excel (как и Google SpreadSheet, и LibreOffice). Далее — яркие тому доказательства: список 12 функций, знание которых заметно облегчить жизнь рядового SEO-специалиста.+

№1 функция: ДЛСТР (англ. LEN)+

Очень удобна для определения длины текстового содержимого ячейки или текста, заданного в формуле. Примеров практического применения данная функция имеет просто массу. Например, измерение длины анкоров или мета-тегов на предмет превышения лимита. В качестве примера возьмём 70 знаков для title.+ Добавим условное форматирование для большей наглядности:+ Далее строки с длиной меньше допустимого значения выделяем одним цветом, а больше — другим.+ Может быть не очень красиво. Зато наглядно и понятно. Особенно в случае, когда имеется несколько тысяч мета-тегов. По такому же принципу можно добавлять новые правила для параметров description.+

№ 2 функция : СЖПРОБЕЛЫ (англ. TRIM)+

Удаляет все пробелы, кроме одинарных между словами из содержимого ячейки или заданного фрагмента текста. 

Очень полезная функция, когда в процессе работы при копировании всего объема текста - появляются пробелы до/после/между слов, мешающие и стопорящие дальнейшую работу над всем текстом.+

№ 3 функция :ПРОПИСН (англ. UPPER), СТРОЧН (англ. LOWER)+

Преобразует содержимое строки или какого-либо заданного фрагмента в прописные или строчные буквы.+

№ 4 функция : ПРОПНАЧ (англ. PROPER)+

Автоматически изменяет первые буквы каждого слова в строке на прописные. 

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

Как Вы наверное знаете, при проверке основными сервисами (как следствие — и программами) все буквы запроса автоматически приводятся в строчный вид. В итоге таблица на несколько тысяч строк вида ЗАПРОС + КОМПАНИЯ, где название компании указано с маленькой буквы. Для дальнейшего использования необходимо сделать соответствующую корректировку. В этом случае данная функция ПРОПНАЧ сократит массу времени. Для её использования нужно сначала расщепить массив по 2-м столбцам (запрос и название) с помощью функции Данные → Текст по столбцам. Затем применяя функцию ПРОПНАЧ к столбцу с названиями компаний - производим сцепку с первым столбцом. 

Хочется отметить, что данное решение не единственное из возможных. Но однозначно - самое простое.+

№ 5 функция : СЦЕПИТЬ (текст1;текст2;текст3…) (англ. CONCATENATE)+

Самая полезная в практическом применении SEO функция программы Excel - СЦЕПИТЬ позволяет объединить содержимое отдельных текстовых блоков в одну строку. Это может быть, как простая сцепка 2-х ячеек, так и более сложный вариант с подставлением текстовых блоков непосредственно в формулу. 

К примеру, допустим, вам нужно отправить ссылки с 700 не совсем качественных доменов в инструмент Disavow Links. Синтаксис инструмента предполагает формат вида domain: ваш_домен.kz. Что делать? Прописывать все 700 строк вручную? Естественно, нет. Вам просто нужно ввести в строке:+

А после растянуть формулу на весь столбец.+ Вот еще наглядный пример: у вас есть столбец с URL и столбец с анкорами. И нужно сформировать полноценную ссылку следующего вида:+ Ничего сложного тут на первый взгляд нет. Но существуют некоторые «маленькие» хитрости. Например, использование кавычек в текстовом блоке, предшествующем ссылке и в блоке, идущем сразу за ней. В данном случае формула из предыдущего примера не сработает - из-за путаницы в одинарных/двойных кавычках. 
Неверно:+ Варианты решения данной задачи:+
  1. Обывательский (уровень юзера) +

    Мы просто делаем 2 дополнительных столбца или ячейки с данными: 

    Вместо первого текстового блока в формуле используем ссылку на первую ячейку, вместо второго — на вторую. В результате чего получается такая картинка: 

     

    В случае, если были указаны какие-либо определенные ячейки, а не столбцы например, не забудьте задать абсолютные адреса: 

    +

  2. Мастерский (уровень профи)+
    1. Используем одинарные кавычки. Пишем: 

       

      Хотя синтаксис ссылок с одинарными кавычками будет являться валидным, его применение в этом случае все же не совсем канонично.+

    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-ов, и нам необходимо выделить из них непосредственно домен.+

Выстроим следующую логическую цепочку: нам надо «найти» точку непосредственно на слеше после домена, после этого убрать кусок строки слева — с нулевой точки до найденной нами точки конца домена. И сделаем разобивку задачи на несколько подзадач следующим образом:

  1. Что мы ищем? Слеш. Где ищем? В ячейке с URL. С какой позиции ищем? Как минимум, с восьмой, чтобы исключить начальные слеши. 
    В итоге получаем: 

    +

  2. Выделим подстроку с доменом: с начала строки до точки вхождения слеша: 

     
    При даже небольших навыках пользования текстовыми функциями программы Excel можно творить настоящие чудеса.+


№ 10 функция: ВПР (искомое_значение, таблица, номер_столбца, тип_совпадения) (англ. VLOOKUP)+

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

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

так, что мы видим:
  • Первый параметр (А2), определяет, по какому значению происходит поиск совпадений. В нашем случае нам надо «состыковать» таблицу по отдельным доменам.
  • Второй параметр ( F2:h21) — это таблица с «эталонами». То есть та, где именно мы ищем.
  • Третий параметр (2) — номер столбца в этой «эталонной» таблице, из которого берется значения. Слева-направо, в случае с «ТИЦ», значение «2».
  • Четвёртый параметр (ЛОЖЬ) — тип совпадения. Это самое важное. Здесь таится одна из самых больших сложностей этой функции.
ЛОЖЬ показывает цель нашего поиска - точное совпадение содержимого ячейки в таблице с эталонами. ИСТИНА же означает, что при отсутствии точного совпадения, будет использовано ближайшее к нему по убыванию. Также при использовании ИСТИНЫ лучше всего производить сортировку столбца по возрастанию, иначе результат может быть не совсем корректным. И если в эталонной ячейке искомая ячейка будет встречаться несколько раз — то программа будет использовать первое значение.+ Работает. Один маленький нюансик: мы задали адрес таблицы как относительный, то есть при растягивании формулы - фокус с эталонной таблицы будет смещаться вниз на пустые ячейки. Чтобы этого не случилось, вписываем следующее:+ Как видим все работает. Теперь тоже проделываем и для соседнего столбца:+ Отлично, все сделано. Теперь обратим свое внимание на встроенный функционал программы. 

Тут, лидерство по полезности и упрощению работы SEO-специалиста, поровну делят между собой 2 основные функции: очистка от дублей и разбивка данных по столбцам и по разделителю.+

№ 11 функция: Данные → Удаление дубликатов (Data → Remove Duplicates)+

Позволяет очистить список от дублей. 

К примеру, у нас есть список доменов на 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 и надеемся, что эта информация окажется полезной для Вас, а так же спасёт не один десяток веб-мастеров от бессмысленной траты времени на рутинные задач

Используем EXCEL для SEO: полезные советы

EXCEL для SEO

Как пользоваться EXCEL для SEO, рассмотрим практические советы и примеры по оформлению документа. Обзор полезных формул.

SEO предполагает обработку больших массивов данных, чтобы делать это быстро и успевать следить за десятками страниц и сотнями запросов нужен надежный помощник. Оптимальный вариант использовать пакет EXCEL. Стандартного функционала программы с головой хватит для аналитики, учета и контроля процесса SEO продвижения.

Готовим базовый файл

Для начала работы необходимо создать базовые колонки:

  • ключ;
  • страница продвигаемого сайта;
  • релевантная страница в Яндексе;
  • результат выдачи в Яндексе;
  • частотность;
  • тайтл;
  • результат выдачи в Google;
  • данные для бюджета и аналитики.
Пример таблицы для SEO

Создаем сортировку

Для этого необходимо выбрать все столбцы с данными, перейти на вкладку «Вставка» и выбрать «Вставить таблицу». Первая строка станет заголовками колонок, а в ячейках добавиться треугольник. Нажав на него EXCEL, покажет возможные варианты сортировки.

Создание таблицы для SEOСортировка SEO таблицы

Отсеиваем дубли и повторы

При составлении семантического ядра важно отработать только уникальные ключи. Если в файле их более 1000 вручную – это долго, сложно и есть вариант что-то просмотреть. EXCEL решает проблему в пару кликов:

  • выберите колонку с ключами;
  • перейдите на вкладку «Условное форматирование»;
  • создайте правило «Форматировать повторяющиеся значения».
Создаем правило форматированияЗадаем цвет для форматирования

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

Добавляем краски

При работе с большими массивами данных удобно выделять однородные группы значений при помощи цветовых меток. Это позволяет в разы быстрее ориентироваться в таблице и следить за динамическими показателями. Реализуется функционал с помощью «Условного форматирования». Выбираем нужный столбец, переходим на вкладку, задаем правило «Форматировать ячейки, которые содержат»:

Подсвечиваем данные для анализа SEO

Если вместо «Значения ячейки» выбрать «Текст», можно подсветить запросы по заданному слову:

Подсвечиваем отдельные слова для анализа SEO

Используем формулы для прогнозирования

Подготовив ключи и цифровые данные легко можно спрогнозировать бюджет по каждому запросу. Для этого нужно использовать простую формулу: ([@[заголовок_колонки_1]] + [@[заголовок_колонки_2]])/2. Набор заголовков и математических действий ничем не ограничен. Для наглядности:

Пример использования формул для SEO

Чтобы скопировать данные, рассчитанные по формуле в другую колонку или перенести на новый лист, необходимо:

  • выделить диапазон;
  • скопировать значения;
  • для вставки воспользоваться специальными возможностями;
  • выбрать «Вставить значения».
Правильно копируем данные

Сравнение значений

Для нужд SEO часто нужно сравнивать данные, например, совпадает или нет продвигаемая страница с релевантной. Процедура выполняется при помощи логической формулы «ЕСЛИ». Чтобы проанализировать значения двух колонок вставьте формулу: =ЕСЛИ([@[ заголовок_колонки_1]]=[@[ заголовок_колонки_2]];1;0). После ввода данных напротив совпадающих значений появится цифра 1, не совпадающих – 0. Для удобства восприятия формулу можно заменить на: =ЕСЛИ([@[заголовок_колонки_1]]=[@[ заголовок_колонки_2]];"ДА";"НЕТ").

Отображение данных

Перед тем, как переходить к рассмотрению следующих формул, нужно отметить, что EXCEL умеет по-разному смотреть на значения в ячейках и воспринимать информацию как текст, дату, время, проценты или целые значения. Это удобно, поскольку автоматизирует процесс ввода данных. Чтобы отформатировать весь столбец или его часть, нужно выделить область, нажать правой кнопкой мыши и выбрать вкладку «Формат ячеек». В открывшемся меню выбрать необходимое:

Пример форматирования ячеек в SEO

Расчет средних и общих показателей

Чтобы посчитать среднее значение столбца используется формула: =СРЗНАЧ(Таблица_1[заголовок_колонки_1]). Если в документе используется несколько таблиц, то их нужно назвать. Соответственно вместо «Таблица_1» нужно писать фактическое название, а вместо «заголовок_колонки_1» - название расчетной колонки. Поскольку формула не привязана к ячейкам, вставлять ее можно в любое место на текущем листе EXCEL. Для подсчета общей суммы используется формула: =СУММ(Таблица_1[заголовок_колонки_1]).

Иногда возникает необходимость посчитать значение отдельного диапазона данных, тогда формулы меняются на:

  • =СРЗНАЧ(A1:A10)
  • =СУММ(B4:B20)

Если формулу нужно дополнить каким-либо математическим действием с привязкой к конкретной ячейке, используется знак $. Пример: мы получили средние значения по 10 группам запросов, теперь нужно рассчитать примерный бюджет. Данные о средних значениях хранятся в диапазоне K1:L10, расчетная сумма бюджета на 1 запрос – E1. Чтобы получить общие бюджеты на каждую группу вставляем в L1 формулу: =К1*$E$1 и протягиваем ее вниз до L10.

Используя этот нехитрый инструментарий можно в разы упростить работы и сэкономить время.

Автор: София Лупол

Как найти иголку в стоге сена? Формула для работы с данными в Excel

Джереми Готтлиб (Jeremy Gottlieb) — сотрудник агентства Distilled, эксперт в области технического SEO и РРС, разрабатывает стратегии контент-маркетинга, курирует создание продающих рассылок по email.

Упорядочить процесс сбора и обработки SEO-статистики подручными средствами удаётся далеко не всегда. К тому же, анализ данных вряд ли можно назвать увлекательным занятием: зачастую это монотонная, утомительная и скучная работа, которая напоминает поиск иголки в стоге сена. Однако не всё так грустно, как кажется на первый взгляд, при правильном подходе обработку данных можно легко автоматизировать.

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

Потребуется некоторое время, чтобы приспособиться к работе с формулой и запомнить её. Однако эти усилия обязательно принесут плоды. Общий вид формулы с использованием функций: ЕСЛИ и ЕЧИСЛО таков:

=if(isnumber(search(“string 1”, [beginning cell])),”Category 1”, if(isnumber(search(“string 2”, [beginning cell])),”Category 2”, “Other”)

Примечание: функция ЕЧИСЛО() в MS EXCEL, английский вариант ISNUMBER(), даёт возможность проверить, являются ли значения числами или нет. В статье приводятся варианты формул на языке оригинала.

В каких же случаях целесообразно применять её, рассмотрим далее.

Пример 1. Анализ ключевых слов

Если сбор и анализ семантики для клиентского сайта проводится вручную, то специалисту приходится вручную прорабатывать списки, состоящие из сотен и даже тысяч ключевых слов и фраз. Формула позволяет упростить работу со списком, сгруппировав слова и фразы с высокой степенью сходства. Для сбора семантики в данном случае могут применяться любые инструменты. К примеру, сервисы, разработанные Brightedge и SEMrush. Полезно использовать и «Планировщик ключевых слов от Google», с его помощью можно оценить объём поисковых запросов по каждому интересующему ключевому слову или фразе.

После того, как семантика собрана, важно сформировать файл CSV и загрузить результаты для дальнейшей обработки. Это позволит оценить эффективность ключевых слов из существующего списка и просмотреть статистику по числу запросов. Также можно объединить ключевые слова в группы. Тот, кто давно работает с «Планировщиком ключевых слов», знает, что отчёт содержит специальную колонку «Группы объявлений». Однако и здесь не всё так просто: данные отчёта без дополнительной обработки не всегда пригодны для дальнейшего использования.

1

Проблема заключается в том, что результат зачастую зависит от грамотного распределения ключевых слов по группам. В то время как одни ключевые слова в обязательном порядке должны присутствовать в запросе, другие служат лишь своеобразными «подсказками» при подборе новых актуальных запросов. На скриншоте выше показан срез ключевых фраз по запросам, связанным с тематикой «Спортивное питание». В частности, с поисковым запросом «workout supplements». Чтобы не исказить смысла, в переводе статьи будут приводиться англоязычные варианты.

Итак, количество строк в оригинальном списке — 681. Специалисту требуется узнать, какое число запросов содержит ключевое слово «supplement», а в каких запросах присутствует слово «muscle».

Первое, что требуется сделать — это удалить колонку А («Группы объявлений»), поскольку она в данном случае будет абсолютно бесполезной. Следующим шагом справа от столбца поисковых запросов можно добавить колонку «Категория». И далее, используя формулу, присвоить запросам категории «supplement» и «muscle». Для этого в ячейку C2 вводим формулу вида:

=if(isnumber(search(“supplement”,A2)),”Supplement”, if(isnumber(search(“muscle”,A2)),”Muscle”,”Other”))

Описание формулы будет следующим: «Если в ячейке A2 обнаружено ключевое слово «supplement»,то строке запроса должна быть присвоена категория Supplement. Если слово «supplement» не найдено, нужно искать ключевое слово «muscle», и в случае его обнаружения — присвоить строке категорию Мuscle. Если слова «supplement» и «muscle» не обнаружены, строке нужно присвоить категорию Other».

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

2

Применение формулы для всего массива данных позволит с лёгкостью распределить ключевые слова по категориям, избавляя специалиста от необходимости делать это вручную. Двойной клик в правом нижнем углу ячейки С2 позволит применить формулу для всех ячеек в столбце С, которым присвоено численное значение в ячейке В. На скриншоте ниже показан результат применения формулы по отношению ко всем категориям с числовыми значениями. Здесь важно обратить внимание на то, как применение формулы изменило значения в ячейке С 19. На это повлиял анализ данных в ячейках A2 и A19.

3

Хотя на скриншоте слову «muscle» изначально не была присвоена категория, впоследствии она будет присвоена этому термину.

Если же запрос занимает в таблице более одной строки, формула присвоит категорию каждой из этих строк. Однако специалисту следует ориентироваться на ту строку, в которой будет обнаружено первое совпадение.

Если же в ячейке с запросом слова «supplement» и «muscle» содержаться не будут, формула присвоит сроке значение «Other».

Следующим шагом, используя формулу, можно проводить фильтрацию внутри категории «Other», постепенно углубляясь в исследование запросов:

4

Базовые функции excel для работы сео специалиста

846

Исходный файл (скачать) пример на котором будет рассматриваться статья.

В качестве исходных данных рассмотрим файл типа «Распределение» в котором собраны продвигаемые поисковые запросы с указанием:

  • Продвигаемого URL
  • Релевантного URL
  • Позиции в Яндексе
  • Частоты
  • Позиции в Google
  • Недостающих слов в теге Title
  • Прочих

Пример исходных данных

 

СОРТИРОВКА ПО ЛЮБОМУ ПОЛЮ

Для этой операции будет достаточно преобразовать рабочую область таблицу с заголовками (Рис. 2). После чего будет доступна сортировка по любому из полей (Рис. 3) при нажатии на квадратик со стрелочкой справа от названия колонки.

таблица с заголовками

Рис. 2. Вставка таблицы с заголовками в Excel файл для дальнейшей работы.

сортировка таблицы

Рис. 3. Сортировка текстовых полей от «А до Я» и от «Я до А» в таблице в Excel. Для численных полей доступна сортировка от минимального к максимальному значению и наоборот.

ВЫДЕЛЕНИЕ ДУБЛЕЙ ИЛИ УНИКАЛЬНЫХ ЗНАЧЕНИЙ

Часто, поисковые запросы в таблице могут дублировать друг друга или наоборот, вам требуется найти все уникальные запросы, чтобы сравнить два списка. Для этого пригодится функция «Условное форматирование» * (Рис. 4) и создание нового правила для неё. Прежде чем нажать на кнопку «Условное форматирование» требуется выделить область, с которой будет происходить дальнейшая работа по выделению/форматированию значений. В нашем случае, выделена первая колонка целиком.

выделение дублей

Рис. 4. Создание нового правила для условного форматирования выделенной области.

После, выбираете «Форматировать только уникальные или повторяющие значения», задаете тип, на примере это «Повторяющиеся» и Формат, на примере это оранжевый цвет (Рис. 5).

сортировка по полю

Рис. 5. Задание оранжевого цвета для форматирования повторяющихся значений в выделенной области.

УДАЛЕНИЕ ПОВТОРЯЮЩИХСЯ ЗНАЧЕНИЙ

После применения правила повторяющиеся значения в выделенной области будут подсвечены оранжевым цветом (Рис. 6). По данному цвету можно осуществить сортировку в таблице и проработать или удалить данные строчки.

удаление дублей

Рис. 6. Удаление повторяющегося ключевого запроса после сортировки по оранжевому цвету в таблице.

ВЫДЕЛЕНИЕ ЦВЕТОВ ЗНАЧЕНИЙ В ДИАПАЗОНЕ

Для цветового выделения значений в заданном диапазоне также удобным оказывается применение условного форматирования. Для этого требуется выделить интересующие нас колонки или ячейки и создать новое правило для функции «Условное форматирование», далее выбрать «Форматировать только ячейки, которые содержат» и задать значения ячейки в требуемом диапазоне, на примере это от 1 до 10 (Рис. 7).

удаление дублей

Рис. 7. Задание форматирования зеленых цветом для ячеек между 1 и 10 через функцию условного форматирования.

Далее, ячейки в заданном диапазоне будут выделены нужным цветом (зеленым), что упрощаем визуальное восприятие таблицы (Рис. 8).

шаг 8

Рис. 8. Пример выделения в таблице нужных ячеек с позициями в ТОП-10 зеленым цветом.

ПОИСК ЗАПРОСОВ С ЗАДАННЫМ СЛОВОМ

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

поиск по слову

Рис. 9. Пример быстрого поиска и работы с поисковыми запросами, в которых содержится слово «сайт».

РАСЧЕТ ЗНАЧЕНИЯ ПО ФОРМУЛЕ

В таблице также удобным оказывается производить расчёт какого-либо показателя по формуле, опираясь на значения в других показателей. В частности, можно вычислить прогнозируемый бюджет как среднее значение между бюджетом из системы SeoPult и MegaIndex (Рис. 10). Для этого достаточно задать формулу для первой ячейки таблицы и значение вычиститься для всей таблицы.

расчет по формуле

Рис. 10. Расчёт ссылочного бюджета, в таблице Excel опираясь на значения от агрегаторов SeoPult и MegaIndex.

КОПИРОВАНИЕ ЗНАЧЕНИЙ ИЗ КОЛОНКИ, ВЫЧИСЛЕННОЙ ПО ФОРМУЛЕ

Если вы заходите теперь скопировать на другой лист или в другой файл значения из вычисляемой колонки «На ссылки», то столкнетесь с небольшими трудностями. Так как значения вычисляются по формуле, которая «забита» в ячейке, то простое копирование CTRL+C и CTRL+V окажется некорректным (скопируется именно формула, а не числа) и вам потребуется использовать функцию «Специальная вставка». Пошагово это выглядит так (Рис. 11):

  • 1. Выделяете значения, которые вам требуется скопировать мышкой.
  • 2. Нажимаете CTRL+C.
  • 3. Далее выбираете ячейку, начиная с которой вы планируете осуществить вставку.
  • 4. Нажимаете правку кнопку мышки.
  • 5. Выбираете «Специальная вставка».
  • 6. Задаете «Вставить значения».

копирование по формуле

 

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

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

СРАВНЕНИЕ ЗНАЧЕНИЙ В ДВУХ СТОЛБЦАХ

Для понимания, совпадает ли продвигаемая и релевантная в выдаче страница (и ряда других задач), требуется использовать логическую функцию «ЕСЛИ». Требуется добавить колонку сравнения «Совпадает ли?» в таблицу и вставить в первую ячейку данной колонки функцию, следующей последовательностью действий: «Формулы», далее «Логические», далее «ЕСЛИ» (Рис. 12). Задать логическое выражение, скажем [@[ ПРОДВИГАЕМЫЙ URL]]=[@[ РЕЛЕВАНТНЫЙ В Я]]» и значения функции: «1» и «0». Чтобы ускорить процесс, можно сразу вставить в столбец функцию:

=ЕСЛИ([@[ ПРОДВИГАЕМЫЙ URL]]=[@[ РЕЛЕВАНТНЫЙ В Я]];1;0)

сравнение в таблице

Рис. 12. Вызов функции логического «ЕСЛИ» в Excel для сравнения значений в двух столбцах.

После нажатия на кнопку «OK» столбец заполнится значениями «0» (если страницы не совпадают) и «1», если значения совпадают. Это позволит быстро найти все запросы, по которым релевантный и продвигаемый документ не совпадают, и начать анализ возможных причин данного поведения.

ИСПОЛЬЗОВАНИЕ ФОРМУЛ: СРЕДНЕЕ ЗНАЧЕНИЕ И СУММА ЗНАЧЕНИЙ В ЯЧЕЙКАХ

Для вычисления среднего значения какого-либо параметра (скажем, средней позиции в Яндексе по всем запросам или средней частоты запросов), а также суммы значений (скажем, суммарная точная частота или суммарный бюджет на ссылки) требуется использовать математические функции. Наиболее популярные это: вычисление среднего, вычисление медианы, вычисление суммы значений в столбце.

На Рис. 13 показана последовательность действий для вставки функции. Сначала требуется выделить ячейку, в которую требуется вывести итоговое вычисленное значение, далее выбрать интересующую вас функцию и диапазон значений, над которым планируется производить вычисления.

среднее значение

Рис. 13. Выбор ячейки и вставка нужной математической функции ячейку.

После поиска нужной функции, требуется задать аргументы (значения с которыми будет работать функция) и нажать «OK». Если вы всё сделали верно, то значение будет вычислено и вставлено автоматически. Примеры вставки функций среднего значения (Рис. 14) и суммы значений (Рис. 15) представлены на иллюстрациях ниже.

среднее значение

Рис. 14. Вставка функции вычисления среднего значения ячеек для колонки «ЯНДЕКС».

среднее знач. яндекс

Рис. 15. Вставка математической функции «Автосумма» для быстрого вычисления суммы значений в колонке.

Дополнение: В арсенале Excel (Эксель) много различных функций, которые могут пригодиться SEO-специалисту, вы можете осуществить поиск по ним вводя в строку поиска по функциям первые буквы искомой операции. Среди полезных, также могут оказаться такие функции как:

  • Поиск максимального и минимального значения в колонке.
  • Использование логических операторов: «И», «ИЛИ», «ЕСЛИ», «НЕ».
  • Работы с датой и временем, вывод текущей даты по календарю.
  • Сумма, сумма значений с условием, медиана.

ЗАДАНИЕ ФОРМАТА ЯЧЕЕК

Для задания требуемого формата ячеек (числового, денежного, финансового, временного, процентного, текстового и т.д.) достаточно использовать функцию «Формат ячеек», предварительно выделив интересующую область форматирования и нажав правую кнопку мыши (Рис. 16), во всплывающем модальном окне нажать «Формат ячеек…».

формат ячеек

Рис. 16. Пример вызова функции «Форма ячеек» для выделенной области.

После указания нужного формата значений в ячейках, нажмите «OK» (Рис. 17) и выбранный формат будет применен в выделенной области. С помощью данной функции можно избавиться от принудительного превращения некоторых значений в формат даты в Excel и задать наиболее наглядный и подходящий формат для данных (скажем, выводить вместо 0,1 → 10%, добавить разрядку групп разрядов у больших значений 340339493 → 340 339 493, скрыть лишние знаки после запятой 5,100015 → 5,1).

формат ячеек

Рис. 17. Задание двух различных форматов (числовой и процентный) для двух соседних колонок.

ФИКСАЦИЯ ПОЛОЖЕНИЯ ОДНОЙ ИЗ ЯЧЕЕК В ФОРМУЛЕ

Если вам требуется зафиксировать положение (ячейку) для одной из переменных в формуле, то требуется просто заменить в самой формуле значение вида =F2 на значение =$F$2 (вставить знак доллара). После чего, вы сможете «протягивать» формулы для всей строки или столбца с фиксацией одной из переменный (ячеек). Пример использования:

Значение=$C$36+F13*2,2

 


Источник:


Возможно вас заинтересует

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *