При работе в excel seo специалисту чаще всего требуются эти математические функции

Мы перевели для вас статью Нила Пателя. Читайте и применяйте.

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

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

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

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

Зачем учиться пользоваться Excel для SEO

Если вы до сих пор не пользуетесь никаким ПО с таблицами, то пора бы начать. Упорядочивание метрики – одна из самых важных частей разработки маркетинговой стратегии, будь то план, еженедельный SEO отчет или годовой доклад.

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

Причина, по которой Excel является такой потрясающей программой для SEO, – это наличие расширений и плагинов, созданных специально для того, чтобы сделать процесс пользования максимально простым.

Например, SEOTools для Excel идет с уже встроенными функциями специально для маркетологов.

Такие утилиты — просто находка и необходимость для тех, кому приходится пользоваться Excel без особой любви к ней.

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

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

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

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

1. Используйте формулу «=IF» для разбивки ключевых слов по категориям

Находясь в поиске ключевых слов, учишься спокойно относиться к длинным спискам. Привыкаешь к ним, так сказать. Даже пользуясь простой утилитой типа Google’s Keyword Planner, все равно получишь длинный список.

Другие утилиты вроде Ahrefs или Moz могут выдать вам списки с тысячами ключевых слов, показателей объема и данными о конкуренции. А это очень, очень много данных. Поместив эти данные в таблицу, мы сможете организовать их по рядам и колонкам. Но ведь нужно еще и как-то осмыслить все это.

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

Например, если использовать более одного семантического ядра в Keyword Planner,

то вам выдают колонку «Ad Group», куда можно экспортировать список запросов, которые отделят семантические ядра от остальных вариантов ключевых слов.

Предположим, я хочу разбить этот список только на ключевые запросы со словами «утилиты» в них. Если делать это вручную, придется продираться сквозь более чем 700 рядов. А мне не очень-то хочется это делать. Так что вместо этого я собираюсь использовать формулу Excel =IF (=ЕСЛИ), которая сделает все за меня.

Первым делом нужно удалить колонку «Ad Group», потому что я хочу искать среди всех запросов.

А потом я добавлю колонку «Category» (“Категория”) рядом с поисковым объёмом.

В первой свободной клетке я пишу слово «Tools» и затем использую формулу:

=if(isnumber(search(“tools,A12)),”Tools”)

Когда ключевое слово «tools» найдется в первой колонке (мой список ключевых слов), то поместится в категорию «Tools».

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

Если хотите только ключевые запросы со словом «local», можно создать категорию «Local». Удивительно нужная штука для организации всех ваших ключевых запросов для быстрого поиска.

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

У Moz есть хороший пример того, как можно сделать это для всех данных во всех таблицах.

Чтобы сделать это для всех рядов и колонок, используйте мульти-клеточную формулу ARRAY (МАССИВ).

Это освободит вас от необходимости вставлять одну и ту же сложную формулу в каждую клетку таблицы. И даже если у вас не 700+ клеток, с которыми нужно иметь дело, формула все равно пригодится.

2. Создавайте сводные таблицы для обнаружения выброса данных

Анализ данных в таблицах может быть довольно хаотичным, если данные не организованы.

Даже если вы фильтруете по категориям, не факт, что «неверные данные» и прочие лазутчики обнаружатся при одном взгляде на таблицу. Хорошие новости – у Excel есть легкий способ выявить положительные и отрицательные тренды в данных при помощи сводных таблиц.

Чтобы создать такую, выделите клетку таблицы и нажмите Insert > Pivot Table (Вставить > Сводная Таблица).

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

Ваша таблица будет автоматически помещена в выделенную клетку, но также можно создать новую таблицу или поместить ее куда-то еще.

Когда нажмете «OK», то увидите поле, соответствующее вашим колонкам.

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

Используйте эти таблицы для таких вещей как организация ссылок или URL, группировка внутренних ссылок по DA/PA, поиск ключевых слов в домене или создание всплывающих окон для определенных колонок.

Вот пример функциональной сводной таблицы:

Такая таблица не просто организует ваши данные в более читабельном виде, но и дает несколько опций для категоризации. В примере выше можно сразу заметить большие или маленькие цифровые значения CPA («cost-per-action», цена-за-действие) для определенных ключевых слов.

Это делается при помощи условного форматирования в рамках сводной таблицы (вы можете заметить цветное кодирование). Сводные таблицы типа этой могут оказаться полезными, если вы используете ключевые слова для транслирования своей стратегии Adwords, или если хотите быстро обнаружить высоко- и низкочастотные запросы и CPC («cost-per-click», цена-за-клик) для конкретных ключевых слов.

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

И выглядит такая таблица тоже неплохо.

3. Конвертируйте показатели объема при помощи SUBSTITUTE (ЗАМЕНИТЬ)

Существует множество разных способов сортировки данных в Excel. Тем не менее, ключ к продуктивности заключается в том, чтобы найти верный. Когда вы загружаете данные из утилиты для ключевых слов (типа Keyword Planner), CSV-файлы не всегда выглядят хорошо. Например, мои объемы ключевых слов выглядят так:

Справедливости ради, они выглядят так же при использовании Keyword Planner.

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

Excel не может отсортировать мой список, основываясь на понятиях типа «10К» или «1М». Он не дружит с К и М. Ему нужны реальные цифры. К счастью, Excel имеет нечто вроде быстрой формулы, которая поможет вам отсортировать объемы должным образом.

Во-первых, замените К и М и преобразуйте их в «000» или «000000». Создайте новую колонку с названием «От меньшего к большему» или типа того.

Выберите клетку в новой колонке и вставьте =SUBSTITUTE (=ЗАМЕНИТЬ):

Ваша формула должна выглядеть примерно так:

=SUBSTITUTE(C2,”K”,”000”)

Номер клетки изменится согласно ряду, который вы конвертируете.

Вот как это выглядит после вставки формулы:

И окончательные результаты:

Можно также заменить М на «000000» при помощи той же формулы. Выглядеть будет вот так:

=SUBSTITUTE(C2,”M”,”000000”)

Так вы разберетесь со всеми К и М в таблице.

А еще можно выполнить оба действия одновременно (если у вас диапазон от 100К до 1М, например), используя следующую формулу (изменяя номер клетки):

=SUBSTITUTE(SUBSTITUTE(C2,”K”,”000”), “M”, “000000”)

Есть еще несколько формул, чтобы найти минимум, максимум и среднее значение поисковых объемов в новой колонке.

Формула для минимума:

Максимума:

И среднего показателя:

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

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

Эти формулы призваны экономить ваше время и энергию, в конце концов.

4. Извлекайте определенные данные при помощи «REGEXTRACT» (“ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ ТАБЛИЦЫ”)

Конечно, ключевые слова – это не только данные в таблицах SEO.

Иногда вам придется сортировать URL, названия доменов, заголовки блогов или email адреса.

Возможно, вам потребуется найти конкретные посты в блоге или лэндинговые страницы с HTTP versus или HTTPS.

Всегда можно использовать панель поиска Excel и найти их по отдельности, но если у вас длинный список URL, это займет время.

Вместо этого, примените REGEXTRACT (ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ), чтобы извлечь конкретные данные из списка.

REGEXTRACT не похожа на другие Excel формулы. Она требует специального синтакса (строка кода) для работы.

Полная формула с синтаксом для REGEXTRACT выглядит так:

=REGEXEXTRACT(A2,”^(?:https?://)?(?:[^@n]+@)?(?:www.)?([^:/n]+)”)

Смешной кусочек в конце и есть синтакс.

Можно копировать и вставить полную формулу в новую колонку (изменяя номер колонки), и в результате вы получите вот что:

Можно воспользоваться формулой ARRAY (МАССИВ), чтобы добавить это в несколько рядов сразу.

Все вместе выглядит так:

=IFERROR(ARRAYFORMULA(REGEXEXTRACT(A2:A,”^(?:https?://)?(?:[^@n]+@)?(?:www.)?([^:/n]+)”)),“”)

Громоздко, но работает.

Вот что получаем в нашем примере:

И в конечном итоге:

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

Если вы все еще не уверены в процессе или в том, как использовать синтаксис вместе с REGEXTRACT, у Ahrefs есть таблица, которая показывает, как работает эта формула.

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

5. Форматируйте теги названий с помощью формулы «PROPER» (“ПРОПНАЧ”)

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

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

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

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

=PROPER(C2)

Просто, не так ли?

Создайте новую колонку и вставьте формулу туда. Вот так:

И конечный результат:

Если у вас много тегов, которые нужно записать заглавными буквами, это тоже сработает.

Можно искать отдельные теги, используя формулу HLOOKUP (ГПР) (вернемся к ней позже).

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

Если хотите изменить ключевое слово или тэн названия в нижнем регистре на верхний (например, акроним), воспользуйтесь формулой UPPER (ПРОПИСН). Вот так:

Вы можете мне не верить, но формула LOWER (СТРОЧН) совершит обратное действие

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

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

Так что вот вам быстрый и легкий способ решить этот вопрос.

6. Совершайте поиск в больших таблицах с помощью «VLOOKUP» (“ВПР”)

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

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

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

И тем тяжелее, чем больше у вас рядов и колонок с данными. Есть у Excel формула и для этого, куда ж без нее.

Это VLOOKUP (ВПР), которая выглядит так:

=VLOOKUP(A4;’Lookup Table’!A2:D1000;4;FALSE)

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

Эта формула может показаться сложной на первый взгляд, но становится проще, когда вы ее освоите.

Первая секция – это название того, что вы ищете. Поставьте его в кавычки:

Затем добавьте наименование колонки.

После этого добавьте порядковый номер колонки. Первая колонка – под цифрой 1 и так далее.

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

Можно также использовать FALSE, если точное совпадение необязательно.

Обратите внимание на то, что VLOOKUP (ВПР) работает только если необходимые вам данные находятся в первой колонке (крайняя левая), а данные расположены в алфавитном порядке.

Так что вам возможно придётся форматировать колонки перед поиском VLOOKUP (ВПР). Но формула все равно очень пригодится.

Ее также можно использовать для поиска цены продукта или специальной категории, как, например, здесь:

Если нужно искать по рядам, используйте формулу HLOOKUP (ГПР).

V (В) для вертикального поиска и H (Г) для горизонтального.

Пример формулы HLOOKUP:

=HLOOKUP(D3;’Lookup Table’!A1:D10;2;FALSE)

Тут вы найдете пример этой функции:

Оба пути быстро приведут вас к тому, что вы ищете, особенно если найти нужно среди множества рядов, колонок или страниц в документе.

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

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

Немного тренировки, и вы освоите их на «отлично». Именно поэтому они во многом превосходят традиционное поисковое окно.

7. Быстро обнаруживайте дубликаты, используя «COUNTIF»(“СЧЁТЕСЛИ”)

Не все данные в таблице будут верными.

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

Формула Excel в данном случае – COUNTIF. И выглядит она так:

=COUNTIF(A:A,A2)

Допустим, у вас есть список элементов в первой колонке (колонка А), которые вы хотите проверить на дубликаты.

Во-первых, создайте новую колонку и вставьте формулу с номером клетки из колонки А, вот так:

Затем перетащите ее и скопируйте в другие клетки. После этого увидите вот что:

TRUE означает, что совпадения есть, а FALSE – что стопроцентных дубликатов нет. Эта формула находит только точные совпадения.

В примере выше вы видите, что даже если слова «seo strategy» показаны несколько раз (например, seo marketing strategy), то формула не сочтет это дубликатом.

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

Нажмите Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values

Можно выбрать цвет для дубликатов, если хотите просто выделить их, а не удалить.

Если хотите их удалить, нажмите Data > Remove Duplicates (Данные > Удалить Дубликаты), а затем под колонками выделите или снимите выделение с тех, в которых хотите удалить дубликаты.

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

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

И формула COUNTIF (СЧЁТЕСЛИ) – одна из них.

Заключение

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

Но если вы используете таблицы – особенно Excel таблицы – для SEO или маркетингового исследования, то вам просто необходимо овладеть некоторыми из них.

В особенности, присмотритесь к тем, которые помогают при поиске или очистке данных и делают это быстро, как VLOOKUP (ВПР) или COUNTIF (СЧЁТТЕСЛИ).

Умение конвертировать показатели объема с помощью SUBSTITUTE (ЗАМЕНИТЬ) просто жизненно необходимо для поиска ключевых слов.

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

Сбросьте с себя часть бремени, изучив несколько приемов пользования Excel.

Если вы хотите научиться оптимизировать сайты и стать суперменом-сеошником, то можем предложить курс «SEO-оптимизация: продвижение сайтов в поисковых системах». После курсов проведете аудит сайта и создадите стратегию продвижения. Научитесь анализировать конкурентов, сформируете семантическое ядро. Прогнозируя результаты продвижения, сможете оптимизировать бюджет. Привлекательно? Записывайтесь!

ЗАПИСАТЬСЯ

Часто на решение ряда SEO-задач у начинающего специалиста уходит неоправданно много сил и времени. Зная о возможностях Excel в применении к SEO, вы сможете оптимизировать часть рутинных процессов и продуктивно продвигаться к решению поставленных задач.

1. Работа с таблицами

1.1. Преобразование диапазона в таблицу
1.2. Разделение по столбцам — как выделить раздел из URL
1.3. Фильтры — поиск Title по определённым словам, поиск страниц с 301 редиректом определённого раздела
1.4. Условное форматирование — подсветка запросов с трафиком выше среднего, подсветка значений от минимальных к максимальным, подсветка дублей
1.5. Итоговая строка – примеры подсчёта итогов

2. Основные функции

2.1. Функция ВПР (англ. VLOOKUP) — как получить количество посетителей у позиций, находящихся в ТОП-10
2.2. Функция ЕСЛИ — проверка соответствия продвигаемой страницы и той, что находится в поиске
2.3. Функция СЧЁТ ЕСЛИ — подсчёт количества запросов в ТОП-5, ТОП 5-10 и ТОП-10-50
2.4. Объединение ячеек — как найти данные нужного значения

1. Работа с таблицами

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

Для удобства мы рекомендуем преобразовать диапазон в таблицу. Это упрощает работу с функциями и формулами и позволяет автоматически подсчитывать итоги, работать с данными таблицы независимо от данных за её пределами, создавать несколько таблиц на одном листе и работать в них отдельно.

1.1. Преобразование диапазона в таблицу

В ленте навигации выбрать вкладку «Вставка» и нажать на иконку «Таблица».

Поставить галочку «Таблица с заголовками» и выбрать нужный диапазон с данными, затем нажать «Ок».

Получим таблицу такого вида:

1.2. Разделение по столбцам — как выделить раздел из URL

Инструмент «Разделение по столбцам», который находится во вкладке «Данные», позволяет делить данные одной ячейки на несколько столбцов по выбранному признаку: по запятой, пробелу, точке и т. д.

Применение в SEO

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

К примеру, у нас есть выгрузка всех URL сайта, тогда:

1. Копируем столбец с URL и вставляем данные в новый лист

2. Выделяем полученный столбец, нажимаем в навигационной ленте на «Данные» — «Текст по столбцам» — «Далее»

3. В появившемся окне ставим галочку напротив «Другой» и в поле ставим слеш (/), нажимаем «Готово»

Excel разбил наши адреса на столбцы.

Здесь столбцы «С-E» содержат названия разделов. Если мы скопируем эти столбцы в нашу таблицу c URL, то получим группировку по разделам.

С применением фильтрации будет удобно анализировать каждый раздел отдельно.

1.3. Фильтры — поиск Title по определённым словам, поиск страниц с 301 редиректом определённого раздела

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

Есть три способа отфильтровать данные:

1. Фильтрация по принципу простого поиска
2. Выбор значений, по которым необходимо фильтровать данные
3. Фильтрация по условиям

Применение в SEO

Фильтрация по признаку поиска

Допустим, нам необходимо получить все URL, в Title которых встречается слово «ссылки». Для этого нам достаточно написать в поле поиска столбца «Title» слово «ссылки».

Выбор значений

В нашем файле есть данные по кодам ответов сервера, и мы решили посмотреть, какие страницы раздела «Blog» имеют 301 редирект.

Для этого нужно нажать на стрелку у столбца «Группа 1» и оставить галочку только у «Blog».

В столбце «StatusCode» выбираем «301». В итоге получаем результат:

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

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

Применение в SEO

Подсветка запросов, у которых посещение выше среднего

Например, у нас есть данные трафика по запросам, и мы хотим выделить запросы, у которых посещение выше среднего.

«Условное форматирование» — «Правила отбора первых и последних значений» — «Выше среднего»

Подсветка значений от минимального к максимальному

Также при анализе удобно использовать «Цветовые шкалы», которые подсвечивают минимальные и максимальные значения в виде своеобразной тепловой карты.

«Условное форматирование» — «Цветовые шкалы»

Чем ниже значение, тем более красным становится цвет.

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

У нас есть список URL с Title из Screaming Frog, и мы хотим подсветить одинаковые Title.

«Условное форматирование» — «Правила выделения ячеек» — «Повторяющиеся значения»

Теперь при желании можно отфильтровать данные по цвету.

Результат сортировки:

1.5. Итоговая строка – примеры подсчёта итогов

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

Внизу таблицы автоматически появится строка «Итог» с возможностью выбора.

Применение в SEO

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

2. Основные функции

2.1. Функция ВПР (англ. VLOOKUP) — как получить количество посетителей у позиций, находящихся в ТОП-10

У нас есть список запросов с позициями и данные по трафику из Яндекс.Метрики. Мы хотим знать, какие из запросов, находящихся в ТОПе, приносят нам трафик.

В этом нам может помочь специальная функция ВПР, которая ищет указанное значение в крайнем левом столбце таблицы и возвращает значение ячейки указанного столбца той же строки.

Синтаксис функции: ВПР (значение_которое_ищем; таблица_в_которой_ищем; номер_столбца_нужного_значения; [интервальный просмотр])

Применение в SEO

1. Кликаем по ячейке, в которую должны подтягиваться данные по трафику (в нашем примере это ячейка D3). Вставляем формулу = ВПР(B3;$F$2:$G$12559;2;0).

Разберём формулу:

B3 – ключевое слово, которое необходимо найти в таблице «Трафик»;
;$F$2:$G$12559 – диапазон таблицы «Трафик». Знак «$» вставлен для закрепления диапазона, чтобы при протягивании формулы он не сдвигался;
2 – второй столбец таблицы «Трафик». Именно в этом столбце находятся данные по трафику;
0 – интервальный просмотр, может принимать значение «0» (поиск точного совпадения) или «1» (поиск приблизительного);

2. Нам нужен трафик запросов из ТОП-10. Отфильтруем данные столбца «Позиции» по условию меньше или равно 10.

В итоге получаем:

#Н/Д показывает, что нужное ключевое слово в таблице «Трафик» не было найдено — т. е. по нему нет трафика. #Н/Д можно заменить на «0». Для этого добавим в нашу функцию ещё одну: ЕСЛИОШИБКА (на англ. IFERROR).

Синтаксис функции: ЕСЛИОШИБКА (проверяемое_значение; значение_если_ошибка).

В нашем случае функция будет выглядеть следующим образом: ЕСЛИОШИБКА(ВПР(B3;$F$2:$G$12559;2;0);0)

2.2. Функция ЕСЛИ — проверка соответствия продвигаемой страницы и той, что находится в поиске

Функция «ЕСЛИ» проверяет выполнение условия. При его выполнении функция возвращает одно значение и другое, если условие не выполняется.

Синтаксис функции: ЕСЛИ (Условие (логическое_выражение); Значение_если_истина; Значение_если_ложь)

Применение в SEO

С помощью функции «ЕСЛИ» мы можем посмотреть, соответствует ли продвигаемая страница той, что ранжируется в поиске.

У нас есть список URL, которые ранжируются в поиске и URL и которые мы реально хотим продвигать.

В ячейке «C2» добавляем функцию =ЕСЛИ(A1=B1;1;0)

Разберём формулу:

A1=B1 – проверяем, равен ли целевой URL релевантному;
1 – выводим «1», если равен;
0 – выводим «0», если не равен.

2.3. Функция СЧЁТ ЕСЛИ — подсчёт количества запросов в ТОП-5, ТОП 5-10 и ТОП-10-50

Считает количество ячеек, удовлетворяющих условию.

Синтаксис функции: СЧЕТЕСЛИ (диапазон_значений; условие)

Применение в SEO

Эта функция поможет посчитать количество запросов из ТОП-5, ТОП-5-10 и ТОП-10-50.

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

Для ТОП-5 функция будет выглядеть так: = СЧЁТЕСЛИ($C$3:$C$423;»<=5″),

где $C$3:$C$423; — закреплённый диапазон с позициями, «<=5» — считать, если меньше или равно «5».

Для ТОП-5-10, чтобы вычислить, сколько запросов находится в диапазоне между «5» и «10» позицией, нужно подсчитать количество запросов ниже «10» и вычесть количество позиций ниже «5». Функция выглядит так: =СЧЁТЕСЛИ($C$3:$C$423;»<=10″) — СЧЁТЕСЛИ ($C$3:$C$423;»<5″)

Для ТОП-10-50 аналогично: =СЧЁТЕСЛИ($C$3:$C$423;»<=50″)-СЧЁТЕСЛИ($C$3:$C$423;»<10″

2.4. Объединение ячеек — как найти данные нужного значения

В работе достаточно часто требуется объединить несколько ячеек в одну. Это может понадобиться при формировании файла на отклонение ссылок в Disavow Tools или при формировании шаблонов для Title.

Есть несколько способов объединить ячейки:

Вариант 1:
Функция «СЦЕПИТЬ» (англ.CONCATENATE)
Синтаксис: СЦЕПИТЬ (текст1; текст2; …)

Вариант 2:
Использование &
Синтаксис: A1&B1, здесь &сцепить ячейки A1 и B1

Пример 1:

Пример 2:

Необходимо сцепить ячейки и добавить в конце предложения «: цены в СПб»

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

А мы в свою очередь продолжим писать о возможностях Excel в применении к SEO, дабы избавить вас от рутины и ускорить решение некоторых важных задач.

Полезные ссылки:
Официальная справка excel
Портал по изучению excel

Наверх

Еще по теме:

• 11 полезных расширений для SEO-специалиста в Google Chrome

Подготовили подборку из 11 SEO-расширений в Chrome, которые используем в повседневной работе, со скриншотами и описанием плюсов/минусов каждого. Такие плагины будут полезны в работе и SEO-специалисту,…

• Обзор сервисов для съёма позиций сайта

Критерии оценки https://seolib.ru/ https://topvisor.com/ru/ https://seranking.ru/ https://ahrefs.com/ https://allpositions.ru/ https://ru.megaindex.com/ https://www.rush-analytics.ru/ https://www.semrush.com/ https://serpstat.com/ https://tools.pixelplus.ru/ https://line.pr-cy.ru/ https://serphunt.ru/ https://www.wincher.com/ Результаты Несколько месяцев назад, отвечая на вопрос в Телеграме, я…

• Отслеживание отправки форм на сайте с помощью GTM

Ранее мы уже писали о том, как установить и настроить Google Tag Manager на сайт, а также как с его помощью настроить отслеживание кликов. В…

• Настройка отслеживания e-commerce с помощью Google Tag Manager

Мы продолжаем делиться информацией о том, как пользоваться Google Tag Manager для анализа трафика, конверсий и поведения пользователей. Ранее мы рассмотрели следующие способы работы с…

• CheckTrust — полезный сервис проверки качества домена и оценки ссылочного профиля

В данной статье расскажу о том, что такое CheckTrust, про его особенности, преимущества, а также о применении на практике. Введение Основные разделы Применение на практике:…

В этой ста­тье Вла­ди­мир Шван­ский рас­ска­зы­вает о том, как эффек­тивно исполь­зо­вать Excel в нашей seo-работе.

Когда меня впер­вые посе­тила мысль напи­сать ста­тью о связке Excel + SEO, передо мной встала дилемма: о чём писать, чтобы не про­слыть «капи­та­ном Оче­вид­ность» и в то же время не углуб­ляться в нюансы спе­ци­фи­че­ских инстру­мен­тов, кото­рые мно­гие SEO-спе­ци­а­ли­сты не исполь­зуют в прин­ципе. Я решил пойти самым вер­ным путем: опи­сать методы реше­ния с помо­щью Excel тех SEO-задач, кото­рые я сам решаю еже­дневно.

Но сперва — несколько слов о том, почему важно исполь­зо­вать пра­виль­ные инстру­менты для реше­ния тех или иных задач. Пер­вое, что бро­са­ется в глаза, когда ты захо­дишь на про­филь­ный форум или SEO-блог — про­блема низ­кой тех­ни­че­ской под­ко­ван­но­сти моло­дых спе­ци­а­ли­стов. Такие рас­про­стра­нён­ные в прак­ти­че­скомSEO про­блемы, как сор­ти­ровка и ана­лиз мас­си­вов дан­ных, раз­лич­ные вари­анты работы со стро­ками, агре­га­ция дан­ных и, наобо­рот, их раз­би­тие — всё это боль­шин­ство веб-масте­ров выпол­няет вруч­ную, тратя огром­ное коли­че­ство вре­мени на моно­тон­ные, одно­об­раз­ные и легко авто­ма­ти­зи­ру­е­мые задачи.

Одни пыта­ются найти гото­вое узко­функ­ци­о­наль­ное реше­ние для своей про­блемы: «Помо­гите найти про­грамму для услов­ного сло­же­ния зна­че­ний строк», «Под­ска­жите про­грамму, чтобы выде­лить домен со списка» и т. д. Дру­гие пишут скрипты-реше­ния для всех про­блем, с кото­рыми стал­ки­ва­ются. Тре­тьи исполь­зуют доро­гие про­фес­си­о­наль­ные про­граммы (Deductor для фор­ми­ро­ва­ния сре­зов дан­ных, TextPipe для работы со стро­ками и т.п.) для довольно-таки базо­вых опе­ра­ций.

А ведь боль­шин­ство наших про­блем решает Microsoft Excel (как и Google SpreadSheet, и LibreOffice). Далее — яркие тому дока­за­тель­ства.

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

При­ме­ня­ется для опре­де­ле­ния длины тек­сто­вого содер­жи­мого ячейки (или тек­ста, задан­ного в фор­муле). При­ме­не­ний, как вы пони­ма­ете, масса. Напри­мер, изме­ре­ние длины анко­ров или мета-тегов на пред­мет пре­вы­ше­ния лимита (для при­мера возь­мём 70 зна­ков для title)

Доба­вим услов­ное фор­ма­ти­ро­ва­ние для нагляд­но­сти:

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

И полу­чаем:

Не очень худо­же­ственно, зато наглядно. Осо­бенно когда дело каса­ется несколь­ких сотен/тысяч мета-тегов. По такому же прин­ципу можно добав­лять новые пра­вила для пара­мет­ров description.

Функция № 2: СЖПРОБЕЛЫ (TRIM)

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

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

Функции № 3: ПРОПИСН (UPPER), СТРОЧН (LOWER)

Транс­фор­ми­рует содер­жи­мое строки (или задан­ного фраг­мента) в про­пис­ные или строч­ные буквы.

Функция № 4: ПРОПНАЧ (PROPER)

Пре­об­ра­зует пер­вые буквы каж­дого слова в строке в про­пис­ные.

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

Как известно, при про­верке основ­ными сер­ви­сами (как след­ствие — и про­грам­мами) все буквы запроса при­во­дятся в строч­ный вид. Итог: таб­лица на несколько тысяч строк вида ЗАПРОС + КОМПАНИЯ, где назва­ние ком­па­нии при­ве­дено с малень­кой буквы. Для даль­ней­шего исполь­зо­ва­ния было необ­хо­димо при­ве­сти всё в чело­ве­че­ский вид.

Выход:

1. Рас­ще­пил мас­сив по 2-м столб­цам (запрос и назва­ние) с помо­щью функ­цииДан­ные > Текст по столб­цам.
2. При­ме­нил функ­цию ПРОПНАЧ к столбцу с назва­ни­ями ком­па­ний.
3. Про­из­вёл сцепку с пер­вым столб­цом.

Дан­ное реше­ние про­блемы не един­ствен­ное из воз­мож­ных, но точно самое про­стое.

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

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

При­мер: допу­стим, вам нужно отпра­вить ссылки с 500 не совсем каче­ствен­ных доме­нов в инстру­мент Disavow Links. Син­так­сис инстру­мента пред­по­ла­гает фор­мат вида domain:ваш_домен.com.ua. Что делать? Про­пи­сы­вать все 500 строк руками? Конечно же, нет. Всё, что вам нужно — это напи­сать:

=СЦЕПИТЬ("domain:";адрес_ячейки)

А затем рас­тя­нуть фор­мулу на весь стол­бец.

Еще один при­мер: у вас есть стол­бец с URL и стол­бец с анко­рами. Нам нужно сфор­ми­ро­вать пол­но­цен­ную ссылку сле­ду­ю­щего вида:

Это несложно, однако тут есть свои нюансы. Заклю­ча­ются они в исполь­зо­ва­нии кавы­чек в тек­сто­вом блоке, пред­ше­ству­ю­щем ссылке (и в блоке, иду­щем сразу за ней). Фор­мула из преды­ду­щего при­мера не сра­бо­тает из-за пута­ницы в одинарных/двойных кавыч­ках.

Непра­вильно:

Варианты решения

1. Несе­рьез­ный (отсут­ствует про­фес­си­о­наль­ный вызов)

Делаем два допол­ни­тель­ных столбца (или ячейки) с дан­ными (см. скрин­шот ниже):

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

=СЦЕПИТЬ(адрес_ячейки_с_началом;адрес_ячейки_с_URL;адрес_замыкающей ячейки;адрес_ячейки_анкора;"")

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

$A$1

2. Серьез­ные (при­сут­ствует про­фес­си­о­наль­ный вызов)

2.1 Исполь­зуем оди­нар­ные кавычки

Пишем:

Хотя син­так­сис ссы­лок с оди­нар­ными кавыч­ками и явля­ется валид­ным, его при­ме­не­ние не совсем кано­нично.

2.2 Исполь­зуем сим­вол кавы­чек (chr(34), символ(34))

У двой­ных кавы­чек есть циф­ро­вой код, а зна­чит, мы можем выве­сти их с помо­щью функ­ции chr (в рус­ской вер­сии «сим­вол»).

Функция № 6: СЧЁТЕСЛИ (диапазон;критерий) (англ. COUNTIF)

Под­счи­ты­вает коли­че­ство ячеек внутри диа­па­зона, удо­вле­тво­ря­ю­щих задан­ному кри­те­рию. Напри­мер, вы хотите поверх­ностно оце­нить раз­бав­лен­ность анкор­ного листа сайта URL’ами. Чтобы никого не оби­жать, возь­мём не реаль­ный анкор лист, а выду­ман­ный. Напри­мер:

Чтобы при­ки­нуть про­цент URL-раз­бавки анкор-листа, посчи­таем все вхож­де­ния домена нашего сайта (а именно domen.ru) в анкоры. Для этого вве­дем фор­мулу:

СЧЁТЕСЛИ(A1:A9;"domen.ru")

Странно, пока­зы­вает ноль. Хоть вроде бы вхож­де­ние домена в анко­рах встре­ча­ется. Дело в том, что, в отли­чие от функ­ции ПОИСК (о ней — далее), кри­те­рий для СЧЁТЕСЛИ необ­хо­димо зада­вать явно и чётко. В нашем слу­чае в списке нет анкора domen.ru. Для ослаб­ле­ния кри­те­риев исполь­зу­ется либо звёз­дочка (любое коли­че­ство сим­во­лов), либо знаки вопроса (одна про­из­воль­ная буква). Для наших целей больше подой­дёт звёз­дочка (она же «асте­риск»).

=СЧЁТЕСЛИ(A1:A9;"*domen.ru*")

Полу­чи­лось! Ну, и раз уж мы нашли этот пока­за­тель, заодно можем посчи­тать и отно­си­тель­ный вес анко­ров с вхож­де­нием URL по отно­ше­нию к общему кол-ву анко­ров.

=СЧЁТЕСЛИ(A1:A9;"*domen.ru*")/СЧЁТЗ(A1:A9)

Вни­ма­тель­ный чита­тель, конечно, заме­тит, что функ­ция СЧЁТЗ счи­тает только непу­стые ячейки. В слу­чае выгрузки с сер­виса ана­лиза беклин­ков и боль­шого анкор-листа, полу­чен­ный нами резуль­тат будет некор­рект­ным. К сча­стью, в Excel также есть функ­ция под­счёта и пустых ячеек в диа­па­зоне, нося­щая кра­си­вое назва­ние СЧИТАТЬПУСТОТЫ (англ. COUNTA).

Итого, наш финаль­ный вари­ант:

=СЧЁТЕСЛИ(A1:A9;"*domen.ru*")/(СЧЁТЗ(A1:A9)+СЧИТАТЬПУСТОТЫ(A1:A9))

Готово.

Функция № 7: СУМЕСЛИ (диапазон;критерий;диапазон_для_сложения) (англ.SUMIF)

Прин­цип такой же, как и в преды­ду­щем при­мере. Глав­ное отли­чие: два пара­метра с диа­па­зо­нами. Пер­вый — для при­ме­не­ния кри­те­рия, вто­рой — для при­ме­не­ния сло­же­ния зна­че­ний.

Функции № 8: ЛЕВСИМВ (текст;количество знаков) (англ. (LEFT), ПРАВСИМВ (текст;количество знаков) ( англ. RIGHT)

Воз­вра­щают задан­ное коли­че­ство зна­ков слева (или справа). Как пра­вило, исполь­зу­ются в усто­яв­шейся связке с функ­цией ПОИСК.

Функция № 9: ПОИСК (искомый фрагмент, просматриваемый текст,начальная позиция) (англ. SEARCH)

Воз­вра­щает номер вхож­де­ния иско­мой под­строки в общую строку. Напри­мер, при­ме­не­ние сле­ду­ю­щей фор­мулы воз­вра­тит «2», так как буква «п» вхо­дит в слово «опти­ми­за­ция» на вто­рой пози­ции:

=ПОИСК ("п";"оптимизация")

Оче­видно, что само по себе зна­ние о пози­ции вхож­де­ния под­строки явля­ется мало­по­лез­ным даже в SEO 

В моей прак­тике исполь­зо­ва­ние связки ЛЕВСИМ + ПОИСК (или ПРАВСИМВ + ПОИСК) встре­ча­лось доста­точно редко. Более того, пока я пишу опи­са­ния и при­меры этих функ­ций, в голове то и дело мель­кает афо­ризм:

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

Ведь, как известно, «нет ничего более бес­по­мощ­ного, без­от­вет­ствен­ного и испор­чен­ного, чем сеош­ник, при­бег­нув­ший к функ­циям поиска по под­строке».

Тем не менее, рас­смот­рим при­мер: у нас есть спи­сок URL-ов, и нам необ­хо­димо выде­лить из них непо­сред­ственно домен.

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

1.

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

Итог:

=ПОИСК("/";ячейка_с_URL;8)

2.

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

=ЛЕВСИМВ(ячейка_URL;ПОИСК("/";ячейка_URL;8))

При опре­де­лен­ной сно­ровке с тек­сто­выми функ­ци­ями Excel можно тво­рить насто­я­щие чудеса.

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

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

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

=ВПР(A2;F2:H11;2;ЛОЖЬ)

Пер­вый пара­метр, А2, опре­де­ляет, по какому зна­че­нию мы ищем сов­па­де­ния. В нашем слу­чае нам надо «состы­ко­вать» таб­лицу по отдель­ным доме­нам.

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

ЛОЖЬ озна­чает, что мы ищем точ­ное сов­па­де­ние содер­жи­мого ячейки в таб­лице с эта­ло­нами. ИСТИНА же озна­чает, что при отсут­ствии точ­ного сов­па­де­ния будет исполь­зо­вано бли­жай­шее к нему по убы­ва­нию. Также при исполь­зо­ва­нии ИСТИНЫ реко­мен­дую про­из­во­дить сор­ти­ровку столбца по воз­рас­та­нию, иначе резуль­тат может быть некор­рект­ным. Кстати, в том слу­чае, если в эта­лон­ной ячейке иско­мая ячейка встре­ча­ется несколько раз, будет исполь­зо­вано пер­вое зна­че­ние.

Рабо­тает! Рас­тя­нем фор­мулу на весь стол­бец и дело в шляпе? Нет. Мы задали адрес таб­лицы как отно­си­тель­ный, то есть при рас­тя­ги­ва­нии фор­мулы фокус с эта­лон­ной таб­лицы будет сме­щаться вниз на пустые ячейки. Чтобы это испра­вить, исполь­зуем:

=ВПР(A2;$F$2:$H$11;2;ЛОЖЬ)

Рабо­тает. Теперь для сосед­него столбца:

Готово. А теперь перей­дём непо­сред­ственно к встро­ен­ному функ­ци­о­налу про­граммы.

Здесь без­услов­ными лиде­рами по полез­но­сти для SEO-спе­ци­а­ли­ста явля­ются 2 функ­ции: очистка от дуб­лей и раз­би­тие дан­ных по столб­цам по раз­де­ли­телю.

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

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

Допу­стим, у нас есть спи­сок доме­нов на 1200 строк. Как вари­ант можно попро­бо­вать найти и убрать дубли «руками», можно отсор­ти­ро­вать спи­сок по алфа­виту и уда­лить «руками» с уже намного мень­шими уси­ли­ями, исполь­зо­вать мак­рос для Excel, исполь­зо­вать софт по работе с клю­че­выми сло­вами (по умол­ча­нию уда­ляет дубли), исполь­зо­вать паб­лик-скрипты или онлайн-сер­висы. Понятно, что если коли­че­ство строк боль­шое (напри­мер, более 1 048 576 строк для Excel), вари­ант со спе­ци­а­ли­зи­ро­ван­ным соф­том или скрип­тами явля­ется един­ственно воз­мож­ным. Но если строк меньше гра­нич­ного мак­си­мума, Excel рабо­тает на ура.

Итак, на старте имеем 1266 доме­нов + aweb.ua:

Кли­каем на шапке столбца, чтобы выде­лить его цели­ком (как вари­ант — тянем выде­ле­ние руками или, клик­нув на пер­вой ячейке с содер­жи­мым, нажи­маем Ctrl+A). Весь наш спи­сок дол­жен быть выде­лен.

Пере­хо­дим во вкладку «Дан­ные» и нахо­дим пункт меню «Уда­лить дуб­ли­каты».

Кли­каем «Ок».

То же самое можно сде­лать и с помо­щью абсо­лютно бес­плат­ного инстру­мента Google Docs Spreadsheet. Также возь­мём спи­сок доме­нов, часть из кото­рых дуб­ли­ру­ется. Для уда­ле­ния дуб­лей исполь­зуем функ­цию:

= UNIQUE (массив)

Так как мас­сив дан­ных у нас лежит в столбце A, в ячейку сосед­него столбца вста­вим фор­мулу:

=UNIQUE(A1:A841)

Готово. В стол­бец B авто­ма­ти­че­ски зальётся мас­сив уни­каль­ных строк. Фор­мулу рас­тя­ги­вать не надо, всё реа­ли­зо­вано через функ­цию CONTINUE.

Функция № 12: Данные > Текст по столбцам (Data > Text to Columns)

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

Допу­стим, у нас есть мас­сив дан­ных с раз­де­ли­те­лем вида «пайп» (вер­ти­каль­ная черта).

Нахо­дим во вкладке «Дан­ные» пункт «Текст по столб­цам». Кли­каем, пред­ва­ри­тельно выде­лив нуж­ный нам мас­сив дан­ных. Появ­ля­ется «Мастер рас­пре­де­ле­ния тек­стов по столб­цам»

Жмём «Далее». На вто­ром шаге отме­чаем тип раз­де­ли­теля «Дру­гой» и встав­ляем туда сим­вол вер­ти­каль­ной черты.

На сле­ду­ю­щем шаге не забудьте выста­вить зна­че­ние в поле «Поме­стить в», иначе стол­бец с дан­ными пере­за­пи­шется (хотя в 99% слу­чаев именно это нам и нужно).

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

На этом всё. В даль­ней­шем я пла­ни­рую напи­сать боль­шую ста­тью по исполь­зо­ва­нию свод­ных таб­лиц Excel в SEO — тема не менее инте­рес­ная и объ­ем­ная, чем затро­ну­тая сего­дня. А пока наде­юсь, что дан­ный мате­риал спа­сёт не один деся­ток веб-масте­ров от бес­смыс­лен­ной траты вре­мени на рутин­ные задачи и не только откроет для вас дру­же­ствен­ный мир Excel, но и вдох­но­вит на даль­ней­шие поиски реше­ний по авто­ма­ти­за­ции работы.

Чувствуете что бизнесу нужен апгрейд?

Получить бесплатную консультацию от специалиста по вашему проекту

Подробнее

Процессы обработки и систематизации больших объёмов данных – неотъемлемая часть работы специалиста по SEO. В большинстве случаев опытные профессионалы обходятся бесплатным программным обеспечением, устанавливающимся вместе с пакетом Microsoft Office. И самого пристального внимания из этого набора заслуживает Excel. Его освоить должен каждый уважающий себя специалист, который работает непосредственно с клиентами и ценит своё и чужое время.

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

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

Работу с подобным софтом удобнее всего разбирать на конкретных примерах. 

Исходный файл

Возьмём в качестве основы файл с набором поисковых запросов условного ресурса в Глобальной сети. В нём собраны следующие данные:

• релевантный запрос;

• продвигаемый URL;

• URL целевого ресурса;

• позиции в поисковой выдаче Яндекса;

• частотность запроса;

• позиции в поисковой выдаче Google;

• ключевые теги, отсутсвующие в заголовках;

• прочее.

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

Работа с таблицами

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

• Кликнуть по вкладке “Вставка”;

• Выбрать иконку “Таблица”.

• В открывшемся окне поставьте галочку “Таблица с заголовками” и задайте рабочую область. Можно выбрать любую область документа и впоследствии вы сможете изменить её размер, в случае необходимости. 

Разбиение столбцов

Иногда может возникать необходимость заменить данные одной ячейки на отдельную таблицу с сортировкой по заданному параметру. Для этого существует функция “Разделение по столбцам”. Воспользоваться ею вы можете следующим образом:

• выделите нужную ячейку;

• выберите вкладку “Данные”;

• нажмите на иконку “Текст по столбцам”;

• остаётся только задать правило для разбиения данных в новой таблице.

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

Объединение ячеек

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

• выделить ячейки, которые вы собираетесь объединить;

• нажать на вкладку “Формат”;

• перейти к пункту “Объединить ячейки”;

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

Сортировка по любому полю

Одна из наиболее простых и, тем не менее, необходимых функций, доступных в Excel. Всё, что для этого требуется – преобразовать определённую рабочую область в таблицу с заголовками. После формирования таблицы, появится доступ к сортировке информации по выбранному полю. Для этого нужно всего лишь кликнуть по квадратику со стрелкой, которая появится справа от заголовка выбранной колонки. Доступны возможности сортировки по возрастанию и убыванию, как для текстовых, так и для числовых данных.

Выделение дублей или уникальных значений

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

1. Выделите рабочую область, которую собираетесь фильтровать. 




2. После этого кликните по кнопке “Условное форматирование”.




3. В выпадающем меню выберите пункт “Создать правило”.




4. Теперь вам нужно выбрать “Форматировать только уникальные или повторяющиеся значения”.




5. В настройках правила устанавливаете “Повторяющиеся” и формат их выделения (найденные повторения будут выделяться цветом).

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

Удаление повторяющихся значений

Один из вариантов форматирования дублей – их удаление из таблицы. В целом этот процесс похож на описанный выше:

1. Выделяете рабочую область с наборами данных, которые необходимо проверить на дубли.




2. Кликаете по “Условное форматирование”.




3. Создаёте новое правило.




4. Как и в предыдущем случае, нужно выбрать “Форматировать только уникальные или повторяющиеся значения”.




5. В выпадающем меню кликните по “Повторяющиеся” и выделите их нужным цветом.




6. Теперь отсортируйте таблицу по цветам, чтобы отделить все дубли для дальнейшей работы с ними.




7. Осталось только удалить все повторяющиеся строки.

Выделение цветом значений в диапазоне

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

1. Выделите рабочую область, в которую входят все данные, требующие сортировки.




2. Нажмите на “Условное форматирование”.




3. Создайте новое правило.




4. В типе правила выбирайте “Форматировать только ячейки, которые содержат”.

5. Задайте размер и тип диапазона.




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

Подобные функции позволяют существенно упростить визуальное восприятие больших объёмов сложной информации. 

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

Таким образом можно не только отслеживать соответствие сайта требованиям поисковых систем, но и оперативно вносить необходимые изменения и корректировки. Работая с объёмными многостраничниками, проверять и редактировать всё вручную – просто непосильная задача.

Поиск запросов с заданным словом

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

1. Выделите рабочую область, в строках которой необходимо отыскать определённые слова.

2. Нажимайте “Условное форматирование”.

3. Выберите “Создать правило”.

4. В качестве типа правила задайте “Форматировать только ячейки, которые содержат”.

5. В первом выпадающем меню выберите “Текст”, затем “Содержит” и введите необходимое слово или сочетание слов.

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

Расчет значения по формуле

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

1. Выделите рабочую область с исходными данными и областью, в которой будут рассчитывать результаты.

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

Эту функцию можно по праву считать одной из наиболее востребованных во всём Excel. Возможность проведения объёмных расчётов, задавая формулу лишь один раз, позволяет ощутимо сокращать расходы времени на выполнение работы.  

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

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

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

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

• Нажмите на простое копирование (Ctrl+C).

• Выберите исходную ячейку для вставки результатов.

• Нажимайте на правую кнопку мыши и в выпадающем меню выбираете “Специальная вставка”.

• Теперь нужно лишь нажать на пункт “Вставить значения”.

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

Сравнение значений в двух столбцах

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

1. Создайте дополнительную колонку “Совпадает ли”.

2. В ячейки новой колонки необходимо добавить логическую функцию. Для этого выполните следующую последовательность действий: “Формулы” – “Логические” – “Если”. 

3. В открывшемся меню задаётся логическое выражение, которое позволяет сравнить содержимое колонок.

4. В качестве положительного или отрицательного результата сравнения можно заполнять ячейки “0” и “1”. 

5. После нажатия «Ок», весь столбец заполнится результатами сравнения.

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

Использование формул: среднее значение и сумма значений в ячейках

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

Для начала работы с математическими функциями проделайте следующую последовательность действий:

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

2. Перейдите во вкладку “Формулы”.

3. Выберите раздел “Математические”.

4. В выпадающем меню вам нужен пункт “Вставить функцию…”.

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

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

• поиск максимального значения в столбце;

• поиск минимального значения в столбце;

• работа с логическими операторами, такими как: “И”, “Или”, “Если”, “Не”;

• обработка текущей даты и времени, и построение цепочки действий, в зависимости от них;

• вычисление суммы значений с дополнительными условиями (медиана).

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

Задание формата ячеек

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

1. Выделить необходимое рабочее поле и кликнуть по функции “Формат ячеек”. Она доступна в выпадающем меню по клику правой кнопки на выделенной области. 

2. Задать нужный формат значений ячейки и нажать “Ок”.

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

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

Чтобы не плодить одинаковые ячейки, для использования постоянных в рамках формулы, вы можете привязывать ячейку к элементу. Для того, чтобы закрепить за тем или иным элементом формулы значение одной конкретной ячейки, требуется просто заменить его с =F2 на значение вида =$F$2. Знак $ позволяет обращаться к одной ячейке, независимо от того, на сколько строк или столбцов будет растянута ваша рабочая область. 

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

Полезные функции для ручного ввода

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

• ДЛСТР/LEN – определение длины ячейки с текстовым содержимым. 

• СЖПРОБЕЛЫ/TRIM – удаляет все лишние пробелы между словами и оставляет только одинарные. Позволяет работать, как с содержимым ячеек, так и с целыми фрагментами текста.

• ПРОПИСН/UPPER, СТРОЧН/LOWER – автоматизирует трансформацию содержимого заданной рабочей области в прописные или строчные буквы, соответственно.

• ПРОПНАЧ/PROPER – находит первые буквы каждого слова в строке и заменяет их на прописные. Бывает крайне необходимой при работе с большими объёмами данных о различных компаниях. При переносе контактной информации, все названия могут преобразоваться в строчные буквы, и работать в дальнейшем с ними может быть неудобно. А исправлять вручную сотни или даже тысячи названий – большой объём работы.

• СЦЕПИТЬ/CONCATENATE – объединение содержимого нескольких ячеек. Отлично работает, когда несколько отдельных текстовых блоков требуется объединить в одну строку. Возможен и более сложный вариант с подставлением отдельных элементов сразу в готовую формулу. Подходит для добавления в URL анкоров. Обращайте внимание на возможный перегруз формулы кавычками. Excel может не понять сложную структу с множественными кавычками и неправильно её интерпретирует. 

• СЧЁТЕСЛИ/COUNTIF – считает все ячейки в выделенном диапазоне, которые удовлетворяют заданному условию. Применяйте его правильно: критерии поиска необходимо указывать явно и чётко. Если же вы хотите искать с более мягкими условиями, которые включают поиск по элементам в тексте, нужно добавлять к функции символ “звёздочка” – *.

• СУММЕСЛИ/SUMIF – позволяет суммировать элементы различных ячеек выделенного диапазона с предустановленным условием. То есть, в итоге будет получаться сумма всех ячеек, которые подошли под заданные вами параметры.

• ЛЕВСИМВ/LEFT, ПРАВСИМВ/RIGHT – возвращает указанное число символов слева или справа соответственно.

• ПОИСК/SEARCH – при помощи этой функции можно отыскать конкретный символ внутри содержимого ячейки. Подобный инструмент может пригодиться в том случае, если вы, например, хотите отделить домен первого уровня в списке собранных URL-адресов. 

• ЕСЛИ/IF – обычное сравнение. Как правило, применяется в сочетании с другими функциями. То есть, в рамках формулы задаётся сравнение нескольких ячеек и, в зависимости от результатов, срабатывают или не срабатывают триггеры дальнейших манипуляций. 

• ВПР/VLOOKUP – эта функция позволяет состыковать между собой данные, содержащиеся в разных таблицах, на основе анализа значений в ячейках. Таким образом, например, можно сопоставить содержимое нескольких таблиц и определить, какие значения отличают сравниваемые от эталонной. 

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

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

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

Excel для SEO. Основы с примерами

Часто на решение ряда SEO-задач у начинающего специалиста уходит неоправданно много сил и времени. Зная о возможностях Excel в применении к SEO, вы сможете оптимизировать часть рутинных процессов и продуктивно продвигаться к решению поставленных задач.

Для кого: SEO-специалистам

Уровень подготовки: начальный

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

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

Все приёмы и функции будут сопровождаться примерами в аспекте SEO.

1. Работа с таблицами

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

Для удобства мы рекомендуем преобразовать диапазон в таблицу. Это упрощает работу с функциями и формулами и позволяет автоматически подсчитывать итоги, работать с данными таблицы независимо от данных за её пределами, создавать несколько таблиц на одном листе и работать в них отдельно.

1.1. Преобразование диапазона в таблицу

В ленте навигации выбрать вкладку «Вставка» и нажать на иконку «Таблица».

Поставить галочку «Таблица с заголовками» и выбрать нужный диапазон с данными, затем нажать «Ок».

Получим таблицу такого вида:

1.2. Разделение по столбцам — как выделить раздел из URL

Инструмент «Разделение по столбцам», который находится во вкладке «Данные», позволяет делить данные одной ячейки на несколько столбцов по выбранному признаку: по запятой, пробелу, точке и т. д.

Применение в SEO

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

К примеру, у нас есть выгрузка всех URL сайта, тогда:

1. Копируем столбец с URL и вставляем данные в новый лист

2. Выделяем полученный столбец, нажимаем в навигационной ленте на «Данные» — «Текст по столбцам» — «Далее»

3. В появившемся окне ставим галочку напротив «Другой» и в поле ставим слеш (/), нажимаем «Готово»

Excel разбил наши адреса на столбцы.

Здесь столбцы «С-E» содержат названия разделов. Если мы скопируем эти столбцы в нашу таблицу c URL, то получим группировку по разделам.

С применением фильтрации будет удобно анализировать каждый раздел отдельно.

1.3. Фильтры — поиск Title по определённым словам, поиск страниц с 301 редиректом определённого раздела

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

Есть три способа отфильтровать данные:

1. Фильтрация по принципу простого поиска
2. Выбор значений, по которым необходимо фильтровать данные
3. Фильтрация по условиям

Применение в SEO

Фильтрация по признаку поиска

Допустим, нам необходимо получить все URL, в Title которых встречается слово «ссылки». Для этого нам достаточно написать в поле поиска столбца «Title» слово «ссылки».

Выбор значений

В нашем файле есть данные по кодам ответов сервера, и мы решили посмотреть, какие страницы раздела «Blog» имеют 301 редирект.

Для этого нужно нажать на стрелку у столбца «Группа 1» и оставить галочку только у «Blog».

В столбце «StatusCode» выбираем «301». В итоге получаем результат:

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

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

Применение в SEO

Подсветка запросов, у которых посещение выше среднего

Например, у нас есть данные трафика по запросам, и мы хотим выделить запросы, у которых посещение выше среднего.

«Условное форматирование» — «Правила отбора первых и последних значений» — «Выше среднего»

Подсветка значений от минимального к максимальному

Также при анализе удобно использовать «Цветовые шкалы», которые подсвечивают минимальные и максимальные значения в виде своеобразной тепловой карты.

«Условное форматирование» — «Цветовые шкалы»

Чем ниже значение, тем более красным становится цвет.

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

У нас есть список URL с Title из Screaming Frog, и мы хотим подсветить одинаковые Title.

«Условное форматирование» — «Правила выделения ячеек» — «Повторяющиеся значения»

Теперь при желании можно отфильтровать данные по цвету.

1.5. Итоговая строка – примеры подсчёта итогов

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

Внизу таблицы автоматически появится строка «Итог» с возможностью выбора.

Применение в SEO

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

2. Основные функции

2.1. Функция ВПР (англ. VLOOKUP) — как получить количество посетителей у позиций, находящихся в ТОП-10

У нас есть список запросов с позициями и данные по трафику из Яндекс.Метрики. Мы хотим знать, какие из запросов, находящихся в ТОПе, приносят нам трафик.

В этом нам может помочь специальная функция ВПР, которая ищет указанное значение в крайнем левом столбце таблицы и возвращает значение ячейки указанного столбца той же строки.

Синтаксис функции: ВПР (значение_которое_ищем; таблица_в_которой_ищем; номер_столбца_нужного_значения; [интервальный просмотр])

Применение в SEO

1. Кликаем по ячейке, в которую должны подтягиваться данные по трафику (в нашем примере это ячейка D3). Вставляем формулу = ВПР(B3;$F$2:$G$12559;2;0 ).

B3 – ключевое слово, которое необходимо найти в таблице «Трафик»;
;$F$2:$G$12559 – диапазон таблицы «Трафик». Знак «$» вставлен для закрепления диапазона, чтобы при протягивании формулы он не сдвигался;
2 – второй столбец таблицы «Трафик». Именно в этом столбце находятся данные по трафику;
0 – интервальный просмотр, может принимать значение «0» (поиск точного совпадения) или «1» (поиск приблизительного);

2. Нам нужен трафик запросов из ТОП-10. Отфильтруем данные столбца «Позиции» по условию меньше или равно 10.

В итоге получаем:

#Н/Д показывает, что нужное ключевое слово в таблице «Трафик» не было найдено — т. е. по нему нет трафика. #Н/Д можн о заменить на «0». Для этого добавим в нашу функцию ещё одну: ЕСЛИОШИБКА (на англ. IFERROR) .

Синтаксис функции: ЕСЛИОШИБКА (проверяемое_значение; значение_если_ошибка).

В нашем случае функция будет выглядеть следующим образом: ЕСЛИОШИБКА(ВПР(B3;$F$2:$G$12559;2;0);0)

2.2. Функция ЕСЛИ — проверка соответствия продвигаемой страницы и той, что находится в поиске

Функция «ЕСЛИ» проверяет выполнение условия. При его выполнении функция возвращает одно значение и другое, если условие не выполняется.

Синтаксис функции: ЕСЛИ (Условие (логическое_выражение); Значение_если_истина; Значение_если_ложь)

Применение в SEO

С помощью функции «ЕСЛИ» мы можем посмотреть, соответствует ли продвигаемая страница той, что ранжируется в поиске.

У нас есть список URL, которые ранжируются в поиске и URL и которые мы реально хотим продвигать.

В ячейке «C2» добавляем функцию =ЕСЛИ(A1=B1;1;0)

A1=B1 – проверяем, равен ли целевой URL релевантному;
1 – выводим «1», если равен;
0 – выводим «0», если не равен.

2.3. Функция СЧЁТ ЕСЛИ — подсчёт количества запросов в ТОП-5, ТОП 5-10 и ТОП-10-50

Считает количество ячеек, удовлетворяющих условию.

Синтаксис функции: СЧЕТЕСЛИ (диапазон_значений; условие)

Применение в SEO

Эта функция поможет посчитать количество запросов из ТОП-5, ТОП-5-10 и ТОП-10-50.

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

Для ТОП-5 функция будет выглядеть так: = СЧЁТЕСЛИ($C$3:$C$423;» ,

где $C$3:$C$423; — закреплённый диапазон с позициями, « =СЧЁТЕСЛИ($C$3:$C$423;»

30.05.2014

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

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

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

Добавим условное форматирование для большей наглядности:

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

И получаем:

Может быть не очень красиво. Зато наглядно и понятно. Особенно в случае, когда имеется несколько тысяч мета-тегов. По такому же принципу можно добавлять новые правила для параметров description.

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

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

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

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

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

А после растянуть формулу на весь столбец.

Вот еще наглядный пример: у вас есть столбец с 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», так как буква «п» входит в слово «оптимизация» на второй позиции:

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

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

   

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

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

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

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

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

так, что мы видим:

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

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

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

Как видим все работает. Теперь тоже проделываем и для соседнего столбца:

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

Далее нажимаем «Ок».

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

Так как массив данных у нас лежит в столбце A, в ячейку соседнего столбца вставим формулу:

Вот и все, задача выполнена. В столбец B автоматически зальётся массив уникальных строк. Формулу растягивать не нужно — всё реализовано через функцию CONTINUE.

№ 12 функция: Данные → Текст по столбцам (Data → Text to Columns)

Очень нужная и полезная функция, которая позволяет разбивать различные массивы на составляющие по отдельным столбцам. Также позволяет задать любой разделитель по Вашему выбору — слеш, точку, запятую и прочие. К примеру, можно без использования регулярных выражений и функций поиска по строке легко и быстро извлечь домены из списка различных URL. Допустим, у нас есть массив данных с разделителем вида «пайп» (вертикальная черта).

Находим во вкладке «Данные» пункт «Текст по столбцам». Кликаем по нему, предварительно выделив нужный массив данных. Появляется «Мастер распределения текстов по столбцам»:

Жмём на кнопку «Далее». Отмечаем тип разделителя «Другой» и вставляем туда символ вертикальной черты.

На следующем шаге выставляем значение в поле «Поместить в», иначе столбец с данными перезапишется (хотя в 99% случаев именно это нам собственно и нужно). 

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

Мы рассмотрели основную связку Excel + SEO и надеемся, что эта информация окажется полезной для Вас, а так же спасёт не один десяток веб-мастеров от бессмысленной траты времени на рутинные задачи.

Помощь студентам, готовые работы для Вуза

https://studwork.org/info/147162

Помощь студентам, готовые работы для Вуза

https://studwork.org/info/147162

*SEO
*CEO
*BSC
15. Процент показов, которые привели к кликам — это …
*Google Search
*GSC
*CTR
*URL
16. Google Search Console — …
*бесплатный сервис, предоставляемый Google для создания детальной статистики посетителей веб-сайтов;
*это веб-сервис от Google, который позволяет веб-мастерам проверять статус индексации и оптимизировать видимость своих веб-сайтов;
*сервис контекстной рекламы от Google;
*веб-сервис для ведения блогов, с помощью которого любой пользователь может завести свой блог, не прибегая к программированию и не заботясь об установке и настройке программного обеспечения
17. Performance-маркетинг — это…
*рекламный механизм, посредством которого онлайн-реклама направляется тем пользователям, которые уже взаимодействовали с сайтом, мобильным приложением или страницей рекламодателя в социальных сетях;
*полноценный маркетинг, а не только продвижение через различные социальные платформы;
*концепция интернет-маркетинга, цель которой — достижение конкретных, финансово измеримых бизнес-результатов в максимально короткие сроки;
*совокупность маркетинговых приемов, основанных на создании и/или распространении полезной для потребителя информации с целью завоевания доверия и привлечения потенциальных клиентов.

Поделитесь с Вашими друзьями:

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

Исходный файл (скачать CSV-файл, 1.5 КБ)

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

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

Рис. 1. Исходная таблица для работы.

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

Сортировка по любому полю

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

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

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

Выделение дублей или уникальных значений

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

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

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

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

Удаление повторяющихся значений

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

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

Выделение цветов значений в диапазоне

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

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

Далее, ячейки в заданном диапазоне будут выделены нужным цветом (зеленым), что упрощаем визуальное восприятие таблицы (Рис. 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

* Стоит отметить, что функция условного форматирования быстро работает только с небольшими и средними таблицами и плохо справляется с большими массивами данных.