Формулы Excel для SEO: от простых до продвинутых

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

Функция Text to Columns
ТЕКСТРАЗД (TEXTSPLIT)
СЖПРОБЕЛЫ (TRIM)
СЧЁТЕСЛИ (COUNTIF)
СУММЕСЛИ (SUMIF)
СЦЕПИТЬ (CONCATENATE)
ВПР (VLOOKUP)
ПОДСТАВИТЬ (SUBSTITUTE)
Сводные таблицы (Pivot Charts)
Надстройка SEO Excel
Макрос для выделения цветом
Источники

Допустим, мы выгрузили из Google Search Console отчёт за 3 месяца с тысячами URL. Нам нужно выделить язык из URL для последующего анализа. Вот как это сделать:

Выделяем столбец с URL
Переходим во вкладку "Данные" → "Текст по столбцам"

Формулы Excel для SEO: от простых до продвинутых

Выбираем "Разделитель" → "Другой" и указываем символ "/"
В предпросмотре видим, что язык теперь в отдельном столбце

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

Более гибкая альтернатива — функция TEXTSPLIT. Она автоматически обновляет разделённые данные при изменении исходников. Формула может выглядеть так: =TEXTSPLIT(A2;"/")

Формула TRIM пригодится в самых базовых задачах — она:

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

Формула предельно проста: =TRIM(A2)

Функция COUNTIF помогает подсчитать, сколько ячеек соответствуют определённому значению. Например, чтобы узнать, сколько статей относятся к категории «Путешествия»: =COUNTIF(B:B;"Путешествия")

Допустим, к прошлой таблице мы добавили столбец с кликами. Хотим посчитать общее количество кликов по статьям на тему «Путешествия». Используем формулу: =SUMIF(B:B;"Путешествия";C:C)

Иногда нужно собрать строку из нескольких ячеек — например, приклеить к ключевым словам домен. Для этого и подойдёт данная формула: =CONCATENATE(A2;" ";B2)

Можно использовать и символ &, но при большом объёме данных формула даёт больше гибкости.

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

Пример: слева у нас кластеризованные запросы, справа — все запросы с урлами. Хотим автоматически прописать урлы к каждому запросу. Формула: =VLOOKUP(B2;$G:$H;2;0)

Расшифровка:

B2 — значение, которое ищем;
$G:$H — диапазон, где ищем;
2 — номер столбца, из которого берём результат;
0 — значит ищем точное совпадение (1 — неточное).

Результат — связанная таблица с нужными данными.

Функция SUBSTITUTE — мощный инструмент для работы с URL. Особенно полезна в связке с RIGHT, LEFT и FIND, если нужно точно выделить часть строки.

Несколько практических формул:

=SUBSTITUTE(RIGHT(A2;LEN(A2)-FIND(CHAR(1);SUBSTITUTE(A2;"/";CHAR(1);LEN(A2)-LEN(SUBSTITUTE(A2;"/";""))-1)));"/";"")

Дублирую на русском:

=ПОДСТАВИТЬ(ПРАВСИМВ(C2;ДЛСТР(C2)-НАЙТИ(СИМВОЛ(1);ПОДСТАВИТЬ(C2;"/";СИМВОЛ(1);ДЛСТР(C2)-ДЛСТР(ПОДСТАВИТЬ(C2;"/";""))-1)));"/";"")

Важно: в конце ссылки поставьте знак / , чтобы функция работала верно

=LEFT(A2;LEN(A2)-LEN(RIGHT(A2;LEN(A2)-FIND(CHAR(10);SUBSTITUTE(A2;"/";CHAR(10);LEN(A2)-LEN(SUBSTITUTE(A2;"/";""))-1)))))

Дублирую на русском:

=ЛЕВСИМВ(A2;ДЛСТР(A2)-ДЛСТР(ПРАВСИМВ(A2;ДЛСТР(A2)-НАЙТИ(СИМВОЛ(10);ПОДСТАВИТЬ(A2;"/";СИМВОЛ(10);ДЛСТР(A2)-ДЛСТР(ПОДСТАВИТЬ(A2;"/";""))-1)))))

Важно: в конце ссылки поставьте знак / , чтобы функция работала верно

=LEFT(A2; FIND("/";A2; 9))

Дублирую на русском:

=ЛЕВСИМВ(B3;НАЙТИ("/";B3;9))

Когда нужно быстро визуализировать данные — используем Pivot Table. Например, у нас есть таблица со статистикой по языкам. Переходим во вкладку Вставка > Сводная таблица.

В появившемся окне добавляем:

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

Так получаем понятную сводку по каждому языку.

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

Пример результата:

Код макроса:

Sub name_macros() On Error Resume Next ' массив цветов, используемых для заливки ячеек-дубликатов Colors = Array(12900829, 15849925, 14408946, 14610923, 15986394, 14281213, 14277081, _ 9944516, 14994616, 12040422, 12379352, 15921906, 14336204, 15261367, 14281213) Dim coll As New Collection, dupes As New Collection, _ cols As New Collection, ra As Range, cell As Range, n& Err.Clear: Set ra = Intersect(Selection, ActiveSheet.UsedRange) If Err Then Exit Sub ra.Interior.ColorIndex = xlColorIndexNone: Application.ScreenUpdating = False For Each cell In ra.Cells ' запонимаем значение дубликатов в коллекции dupes Err.Clear: If Len(Trim(cell)) Then coll.Add CStr(cell.Value), CStr(cell.Value) If Err Then dupes.Add CStr(cell.Value), CStr(cell.Value) Next cell For i& = 1 To dupes.Count ' заполняем коллекцию cols цветами для разных дубликатов n = n Mod (UBound(Colors) + 1): cols.Add Colors(n), dupes(i): n = n + 1 Next For Each cell In ra.Cells ' окрашиваем ячейки, если для её значения назначен цвет cell.Interior.color = cols(CStr(cell.Value)) Next cell Application.ScreenUpdating = True End Sub

Если работаете на Windows, рекомендую SEO Excel — плагин, который автоматизирует многие SEO-задачи.

Возможности:

очистка по стоп-словам;
фильтрация по частотным словарям;
дополнительные функции и визуализация.

Скачать: seo-excel.ru/skachat

Приёмы работы с SUBSTITUTE — курс Semrush
Макрос и плагин — курс Демидова
Оригинал макроса — ExcelVBA

Надеюсь, эти инструменты помогут вам оптимизировать рутинные SEO-задачи! Какими функциями Excel пользуетесь вы? Делитесь в комментариях.

Формулы Excel для SEO: от простых до продвинутых

Содержание

1. Функция Text to Columns

2. ТЕКСТРАЗД (TEXTSPLIT)

3. СЖПРОБЕЛЫ (TRIM)

4. СЧЁТЕСЛИ (COUNTIF)

5. СУММЕСЛИ (SUMIF)

6. СЦЕПИТЬ (CONCATENATE)

7. ВПР (VLOOKUP)

8. ПОДСТАВИТЬ (SUBSTITUTE)

1. Вытаскиваем последний сегмент URL:

2. Убираем последний сегмент URL, оставляя всё остальное:

3. Выделяем только домен:

9. Сводные таблицы (Pivot Charts)

10. Макрос для выделения цветом

11. Надстройка SEO Excel

12. Источники