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» містять назви розділів. Якщо ми скопіюємо ці колонки в нашу таблицю з 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

Еще по теме:

• 20 можливостей Google Sheets, які дозволять заощадити час SEO-оптимізатору: функції, плагіни, макроси
• Огляд ТОП-6 парсерів сайтів
• Відстеження кліків у Google Analytics за допомогою Google Tag Manager