2. Що робить функція VLOOKUP

VLOOKUP ([запит]; [діапазон]; [номер_стовпчика]; [тіп_поіска]) здійснює пошук по на одну колонку діапазону і повертає значення з знайденої осередки. Якщо є дві таблиці із загальними даними в одному з стовпців (наприклад, URL), але ці дані в різному форматі, функція дозволяє їх звести в одну таблицю.

Перший параметр, [запит], визначає, за яким значенням необхідно знайти збіги. У нашому прикладі це буде URL. Другий параметр, [Діапазон] - таблиця з даними, які потрібно звести до першого списку. Третій параметр [номер_стовпчика] - номер стовпця в другій таблиці, з якого необхідні значення. Важливо, щоб в цій таблиці перший стовпець містив параметри [запиту]. Якщо необхідно точний збіг, а саме це найчастіше й потрібно, в четвертому параметрі вказуємо «0» (логічне значення ЛОЖЬ).

Розглянемо функцію на прикладі. У Serpstat існує звіт по сторінках з найбільшим потенційним трафіком, тобто сторінок, оптимізація яких може принести кращий результат. Для роботи з такими сторінками важливо розуміти їх поточний рівень оптимізації, наприклад, обсяг посилальної маси, метатеги. Навіть якщо зупинитися тільки на цих параметрах, у нас вже три різних звіту. Їх необхідно привести до одного знаменника. Отже, вивантажуємо все три таблиці, використовуючи звіти Serpstat «SEO-аналіз - ТОП сторінок», «Аналіз посилань - Сторінки-лідери» і сервіс Netpeak Spider для вивантаження метатегов.

Таблицю Serpstat «SEO-аналіз - ТОП сторінок» сортуємо по стовпчику «Потенційний трафік»:

Для всіх URL даної таблиці скануємо і отримуємо метатеги:

Для зведеної таблиці нам потрібні наступні дані:

• URL;
• потенційний трафік;
• зворотні посилання (backlinks);
• посилаються домени (refdomains);
• заголовок Н1;
• Title, Description і Keywords.

Переносимо в нову таблицю всі URL і потенційним трафік, а далі налаштовуємо функцію VLOOKUP:

• в якості першого аргументу вказуємо URL (для якого шукаємо дані) з нашої нової зведеної таблиці;
• другим аргументом буде діапазон всій таблиці, дані з якої потрібні - не забуваємо закріпити її, щоб при «розтягуванні» функції дані не попливли;
• третій аргумент - номер стовпчика з обраного діапазону, в якому розташовуються дані, які розшукуються.

Проробляємо ці настройки для всіх стовпців-параметрів:

Якщо функція повертає # N / A, значить в таблиці з даними відсутня URL, за яким здійснювався пошук. Логічно, що у нас не все URL мають зворотні посилання. Аналізуємо і робимо висновки.

3. Як використовувати функцію UNIQUE

UNIQUE ([діапазон]) повертає унікальні рядки в зазначеному діапазоні, прибираючи дублікати. Рядки повертаються в тому ж порядку, в якому вони розташовуються в діапазоні.

Наприклад, є список URL, які віддають 404 помилку і для яких потрібно налаштувати редирект на нові URL. Якщо список вивантажено з одного джерела і в наявності порядку 20-50 URL, жодної проблеми не виникає. А ось якщо список з різних джерел і в наявності більше 1000 URL-адрес, велика ймовірність, що в списку присутні дублі. Вибрати унікальні адреси легко за допомогою функції UNIQUE:

4. Навіщо потрібна функція COUNTIF

COUNTIF ([діапазон]; [критерій]) підраховує кількість осередків в діапазоні, відповідних заданій умові.

Приклад практичного застосування функції - пошук потенційних донорів серед сайтів-конкурентів (які, звичайно, ранжуються краще). використовуємо Serpstat для пошуку конкурентів і вивантажуємо актуальний контрольний профіль з Ahrefs або Serpstat (звіт « Аналіз зворотних посилань ») По кожному з конкурентів:

Формуємо всі домени в єдиний стовпець (в нашому випадку G). У сусідньому стовпці H застосовуємо функцію UNIQUE. У наступному стовпці застосовуємо COUNTIF, де перший аргумент - закріплений діапазон всіх донорів всіх конкурентів, а другий - осередок з унікальним донором. Таким чином отримуємо таблицю «донор - кількість входжень серед групи конкурентів»:

Відсортувавши цю таблицю за спаданням, отримуємо робочий список з потенційними донорами для проекту. Важливо враховувати:

• аналізовані посилальні профілі конкурентів повинні бути актуальними (зняття посилань ніхто не відміняв);
• конкуренти повинні добре ранжуватися, інакше існує ймовірність зібрати список сміттєвих донорів, які в кращому випадку ніяк не вплинуть на сайт;
• серед таких майданчиків 100% будуть сайти-аналізатори, сміттєві каталоги. Їх, на жаль, доведеться чистити вручну;
• залежить від завдання, але, швидше за все, не знадобляться донори, з яких вже є посилання, тому їх слід виключити в процесі роботи (знадобиться проста функція IF).

5. У чому користь функції LEN

LEN ([ячейка_с_текстом]) обчислює довжину рядка з урахуванням пробілів. Функція буде корисна при формуванні метатегов (перевірка перевищення максимально рекомендованої довжини тега) або текстового контенту на сторінках сайту.

Отже, беремо вивантаження URL з метаданими. Поруч з кожною позначкою додаємо стовпець з функцій LEN. Застосувавши до стовпців умовне форматування, можемо виділити ті теги, де є перевищення по рекомендованої довжині.

6. Що роблять функції IMPORTHTML, IMPORTXML

IMPORTHTML ( "[URL_страніци]"; "[тег_із_которого_нужно_ізвлечь_данние]"; [порядковий_номер_тега_на_страніце]) імпортує дані з таблиці або списку на сторінці.

IMPORTXML ( "[URL_страніци]"; "xpath_запрос") - імпорт даних з джерел в форматі XML, HTML, CSV, TSV, а також RSS і ATOM XML.

Наприклад, можна вивантажити заголовки Н1 зі сторінок (xpath-запит в даному прикладі - // * / h1) або кількість переглядів (xpath запит // * / span [@ class = 'icon-views']):

7. Навіщо потрібна функція SPLIT (або Data - Text to Columns)

SPLIT ([ячейка_с_даннимі]; "[роздільник]"; TRUE) виводить текст, розділений певними символами, в різні осередки.

Вкрай корисна функція, яка дозволяє розбивати різні масиви на складові по окремих стовпців. Як роздільник можна використовувати будь-який символ (слеш, крапку, кому) або набір символів. Якщо вказати в якості третьої аргументу TRUE або 1, як роздільник буде вважатися кожен із зазначених символів. Якщо вказати FALSE або 0, роздільником буде вважається вся комбінація символів.

Припустимо, в категоріях є теги. Завдання - вивантажити все теги і перевірити їх на дублі, адекватність і упущені теги. Для цього потрібні:

• список категорій (URL), де є теги;
• path-запит, який дозволить витягнути зі сторінок URL і анкор тегів;
• сервіс для сканування (наприклад, Netpeak Spider ).

Частина підсумкової таблиці (URL і анкор сторінок тегів):

Працювати з такими осередками складно: не можна просканувати на наявність метатегов або товарів, перевірити код відповіді сторінок тегів. Для вирішення зазначених підзадач необхідні повні URL сторінок тегів.

Спочатку застосовуємо до кожної клітинки формулу SPLIT, як роздільник можна вказати символ «>», який поділить таблицю на дві частини. Потім за допомогою регулярних виразів або функцій зможемо привести обидва стовпці до потрібного вигляду.

Дану формулу можна замінити опцією таблиць Google: Data> Text to Columns (Дані> Розділити на колонки). Перед застосуванням слід виділити весь діапазон, до якого потрібно застосувати функціонал.

8. Як використовувати функції REGEXMATCH, REGEXEXTRACT, REGEXREPLACE

Функції REGEXMATCH, REGEXEXTRACT, REGEXREPLACE дозволяють використовувати регулярні виразів для перевірки, вилучення або заміни частини тексту осередку.

REGEXEXTRACT ([осередок]; "[регулярное_вираженіе]") витягує певну частину тексту, відповідну регулярному виразу.

В якості практичного прикладу скористаємося останньою таблицею. З частини коду з URL винесемо відносний URL, регулярний вираз для цього прикладу - "/.*/". Ускладнити завдання, додавши CONCATENATE і отримавши таким чином готовий абсолютний URL:

В даному прикладі завдання полегшене - все теги першого рівня, без прив'язки до категорії.

Тепер необхідно отримати «чисті» анкор даних сторінок тегів - кличемо на допомогу функцію REGEXREPLACE.

REGEXREPLACE ([осередок]; "[сімволи_которие_нужно_заменіть]"; "[сімволи_на_котторие_виполняется_замена]") замінює частину рядка на інший текст за допомогою регулярного виразу.

В даному випадку регулярний вираз не буде потрібно. Необхідно знайти символи «</ a» і видалити їх:

REGEXMATCH ([осередок]; "[регулярное_вираженіе]") перевіряє, чи відповідає текст регулярному виразу.

Перевіримо, щоб в URL-адресах не було прогалин:

9. Що таке функція SPARKLINE

10. Навіщо потрібна функція IMPORTRANGE

IMPORTRANGE ( "[Ссилка_на_електронную_табліцу]"; "[Названіе_ліста]! [Діапазон_ячеек]") - імпортує діапазон комірок з однієї електронної таблиці в іншу.

Функція корисна, якщо за проектом вивантажуються дані з різних джерел і в підсумку їх потрібно звести в одну таблицю.

Коли необхідно вивантажити на один лист кілька таблиць з різних файлів, важливо відразу резервувати місце для кожної з них, щоб вони не накладалися один на одного.

11. Як використовувати функцію TRANSPOSE

TRANSPOSE ([массів_данних]) міняє місцями рядки і стовпці в масиві осередків.

Якщо необхідно проаналізувати кілька конкурентів, їх зручно порівнювати між собою, коли домени будуть розподілені по стовпчиках, а параметри - по рядках.

При пробиванні параметрів (рекомендуємо використовувати Netpeak Checker ) Підсумкова таблиця буде зворотною: домени - по рядках, параметри по стовпцях. Якщо ви цінуєте час, вручну форматувати таблицю поганий варіант. Краще зібрати всі необхідні дані і у вільному просторі (новому аркуші) ввести формулу TRANSPOSE, аргументом якої буде вся таблиця з вихідними даними:

висновки

Функції таблиць Google можна використовувати для:

• формування файлу з неякісними доменами;
• перевірки URL і пошуку адрес, які віддають 404 помилку;
• пошуку потенційних донорів серед конкурентів;
• формування метатегов або текстового контенту;
• перевірки тегів на дублі;
• аналізу сезонності і відображення високого / низького сезону в ніші.

До речі, для чіткості в таблицях Google не забувайте використовувати додаток Crop Sheet, яке в один клік видаляє зайві (порожні) стовпці і рядки в файлі.

Функції SPARKLINE, IMPORTXML, IMPORTHTML, IMPORTRANGE працюють виключно в таблицях Google, у інших є російські аналоги, які можна використовувати в Microsoft Excel. Детальніше про ці функції можна прочитати в довідці Google .

Хто дочитав, ловите бонус - Докс з прикладами функцій .

Я розповіла тільки про деякі варіанти застосування функцій - не обмежуйте фантазію і діліться своїми прикладами в коментарях.