Введення в Google Sheets для SEO 

Онлайн-застосунок дуже схожий на Microsoft Excel. Він має подібний інтерфейс, засоби редагування й форматування, а також підтримку формул і скриптів. Якщо ви вже працювали в Excel, вам потрібно лише звикнути до іншого синтаксису. А початківцям, які вперше матимуть справу з електронними таблицями, варто ознайомитися з базовими поняттями:

1. Клітинка — одиниця вмісту інформації, поле, в яке вводяться дані.
2. Рядок — горизонтальний масив клітинок.
3. Стовпчик — вертикальний масив клітинок.
4. Таблиця — прямокутний масив, що містить певну кількість стовпчиків і рядків.
5. Функція — операція з даними в клітинках, покликана дати конкретний результат.
6. Ряд — частина таблиці, до якої звертається функція.
7. Формула — комбінація з функцій та рядів, на які вони посилаються. Як у Microsoft Excel, так і в Google Sheets формули починаються зі знаку рівняння «=». 

Щоб зробити ці поняття більш наочними, наведемо простий приклад:

Базові формули Google Sheets для SEO

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

1. IF для перевірки дотримання умов 

Принцип дії: формула видає одну з двох відповідей залежно від того, чи дотримано певну умову в зазначеній клітинці. 

Синтаксис: =IF(condition, value_if_true, value_if_false), де

• condition — умова, виражена в формі математичної функції;
• value_if_true — значення, якщо дані в клітинці відповідають умові;
• value_if_false — значення, якщо умову не дотримано. 

Наприклад, ми хочемо перевірити, чи перевищує місячний обсяг органічного трафіку 100 тисяч запитів. Наша SEO-формула в Google Sheets матиме такий вигляд:

=IF(C3>100000;”YES”;”NO”).

В елементі condition можуть використовуватися різні математичні оператори:

• точно дорівнює (=);
• менше (<);
• більше (>);
• менше або дорівнює (<=);
• більше або дорівнює (>=);
• не дорівнює (<>).

Зверніть також увагу на синтаксис написання елементів value_if. Щоб вивести в клітинці текст, обов’язково беріть його в лапки. Якщо не зробити цього, Google Sheets сприйматиме значення як формулу. Простий текст у такому випадку буде розпізнано як помилкову функцію.

2. LEN, щоб підрахувати кількість знаків у клітинці 

Принцип дії: формула визначає довжину тексту у зазначеній клітинці. Якщо в ній є формула, рахуватися будуть символи, які вона видає у результаті. 

Синтаксис: =LEN(insertcell), де 

• insertcell — посилання на клітинку. 

Наприклад, ми хочемо підрахувати кількість символів у пошукових запитах. Це дуже цінна метрика для SEO, яка дає змогу відсортувати ключі та перевірити їх на відповідність умовам пошукових систем або платформ контекстної реклами. Формула матиме такий вигляд: 

=LEN(B3).

Корисна порада: вам необов’язково вводити окрему формулу в кожному рядку. Після заповнення першої клітинки сервіс Google Sheets запропонує заповнити решту — достатньо просто погодитися з використанням цієї функції. 

3. SPLIT, щоб розділити інформацію на кілька клітинок 

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

Синтаксис: =SPLIT(Text, Delimiter), де

• Text — посилання на клітинку, яка містить потрібний текст;
• Delimiter — знак розділення. 

Наприклад, нам потрібно виділити окремі слова з пошукових запитів. Це можна зробити, розбиваючи текст за пробілами у фразах. Формула матиме такий вигляд:

=SPLIT(B3;” “).

Зверніть увагу: знак, за яким відбувається розділення тексту, обов’язково береться в лапки. Після виконання функції формули залишаться тільки в крайніх лівих клітинках масиву — решту буде записано текстом. Однак в разі зміни даних вони оновляться автоматично. 

4. CONCATENATE для об’єднання даних

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

Синтаксис: =CONCATENATE(range1;range2;…), де 

• range — посилання на клітинки або ряди даних. Замість нього можна додавати власний текст, беручи його в лапки. 

Наприклад, ми хочемо об’єднати пошуковий запит та обсяг органічного трафіку, розділивши їх знаком підкреслювання «_». Формула матиме такий вигляд:

=CONCATENATE(B3;”_”;C3).

Корисна порада: ця формула ідеально підходить для злиття адреси сторінки з назвою домену та префіксом HTTP або HTTPS. Вона значно спрощує побудову URL із фрагментів, вивантажених деякими SEO-сервісами. 

5. COUNTIF для підрахунку кількості клітинок, що відповідають певним умовам 

Принцип дії: на відміну від IF, перевіряє дотримання умови не в окремій клітинці, а в ряду. Видає кількість клітинок, в якій вона дотримується. 

Синтаксис: =COUNTIF(range, criteria), де

• range — посилання на ряд даних;
• criteria — умова, виражена математичною функцією. 

Наприклад, ми хочемо підрахувати кількість ключів із вартістю кліку в контекстній рекламі до 2 доларів. Формула матиме такий вигляд:

=COUNTIF(E3:E12; “<2”).

Не забувайте брати умову в лапки. Також пам’ятайте, що можна використовувати всі математичні оператори рівності або нерівності. 

6. UPPER/LOWER/PROPER для вибору потрібного регістру 

Принцип дії: змінює регістр на верхній, нижній або верхній у першій літері, нижній — в інших. 

Синтаксис: =UPPER(text), =LOWER(text), =PROPER(text), де

• text — посилання на клітинку або введений вручну фрагмент тексту.

Наприклад, нам потрібно відформатувати вивантажені автоматично пропозиції заголовків чи метатегів Description. Деякі програми за замовчуванням видають маленькі літери, деякі — великі. В нашій таблиці ми спочатку переведемо текст у нижній регістр, потім у верхній, а потім — в коректне написання. Формули матимуть такий вигляд:

=LOWER(B3),

=UPPER(G3),

=PROPER(H3).

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

7. UNIQUE для пошуку та видалення дублікатів 

Принцип дії: переглядає стовпчик — якщо в ньому є клітинки з даними, що повторюються, виводить їх тільки один раз. 

Синтаксис: =UNIQUE(range), де

• range — ряд даних, який ви переглядаєте. 

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

=UNIQUE(B3:B12).

Корисна порада: функцію UNIQUE також можна використовувати, щоб видаляти порожні клітинки. В такому разі ви можете навіть давати посилання на стовпчик. SEO-формула Google Sheets виглядатиме так: =UNIQUE(B:B).

8. SUMIF, щоб додати значення клітинок, які відповідають певним критеріям

Принцип дії: перевіряє, чи дотримується умова в цій клітинці або в іншому паралельному ряду даних. Якщо так, додає її до загальної суми, якщо ні — опускає. 

Синтаксис: =SUMIF(range, criterion, [sum_range]), де

• range — ряд, у якому перевіряється відповідність умові;
• criterion — умова, задана математичною функцією;
• [sum_range] — ряд для додавання. Необов’язковий параметр — вводиться тільки в тому випадку, якщо на відповідність умові перевіряється паралельний ряд. 

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

=SUMIF(C3:C12;”>100000″).

Якщо ж ми хочемо порахувати органічний трафік за всіма запитами, складність оптимізації яких не перевищує 50, нам знадобиться інша формула:

=SUMIF(F3:F12;”<=50″;C3:C12).

9. IFERROR, щоб замінити помилки

Принцип дії: якщо формула виконує неприпустиму дію, ця функція виявляє помилку та замінює її іншою дією — вводить текст або перемикає на альтернативу формулу. 

Синтаксис: =IFERROR(original_formula, value_if_error), де

• original_formula — формула, яку ви перевіряєте на наявність помилок;
• value_if_error — дія, яку потрібно виконати в разі виявлення помилки. 

Наприклад, CTR для деяких ключів недоступний через обмежений функціонал програми або відсутність даних у пошуковій системі. Замість цифри в цих клітинках проставлено риски. Спробувавши провести розрахунок, ми отримаємо повідомлення про помилку. Щоб замінити його на текст «NO DATA», знадобиться така формула:

=IFERROR(C3*D3; “NO DATA”).

На скриншоті в клітинці G8 можна побачити повідомлення про помилку. Формула IFERROR також може працювати з помилками типу #DIV/0! та #ERROR!

10. TODAY, щоб додати поточну дату 

Принцип дії: виводить поточну дату в форматі, встановленому хмарним сервісом Google Sheets за замовчуванням. 

Синтаксис: =TODAY(). Зверніть увагу: функція не має параметрів та не посилається на клітинки чи ряди. 

Наприклад, ви постійно забуваєте оновити дату в звіті. Замість того, щоб вводити її вручну, можна скористатися простою формулою. А за допомогою додаткових математичних операторів можна відняти від неї певну кількість днів. Припустимо, нам потрібно знати, яка дата була два тижні тому і буде на наступному тижні в той самий день. Достатньо скористатися такими формулами:

=TODAY()-14;

=TODAY()+7. 

11. SEARCH для пошуку текстового фрагменту в клітинці 

Принцип дії: перевіряє, чи міститься заданий текстовий фрагмент у певній клітинці. Якщо так, повертає номер знаку, з якого він починається. 

Синтаксис: =SEARCH(search_query, text_to_search), де

• search_query — фрагмент тексту, який потрібно знайти, або посилання на клітинку, в якій він міститься;
• text_to_search — посилання на клітинку, в якій здійснюється пошук. 

Ця функція може використовуватися для перевірки клітинок на вміст певного тексту. Наприклад, нам потрібно виявити ключову фразу, в якій міститься слово «Google». Щоб позбутися повідомлень про помилки, варто додати розглянуту вище формулу IFERROR. Кінцевий результат виглядатиме так:

=IFERROR(SEARCH(“Google”;B3);” “).

12. SORT, щоб швидко відсортувати клітинки 

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

Синтаксис: =SORT(range, sort_column, is_ascending), де

• range — ряд, який сортується;
• sort_column — ряд з ознакою, за якою відбувається сортування. Може збігатися з попереднім;
• is_ascending — спосіб сортування. Якщо воно виконується за збільшенням, вказуйте TRUE, за зменшенням — FALSE. 

Наприклад, нам потрібно відсортувати ключі за вартістю кліку в контекстній рекламі. Формула для сортування за зменшенням матиме такий вигляд:

=SORT(B3:B12;E3:E12;FALSE).

Зверніть увагу: формулу слід вводити тільки в одній клітинці. Стовпчик під нею заповнюється та оновлюється автоматично. 

SEO-формули Google Sheets для професіоналів 

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

1. IMPORTRANGE для швидкого імпорту даних з іншої таблиці 

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

Синтаксис: =IMPORTRANGE(spreadsheet_url, range_string), де

• spreadsheet_url — посилання на потрібний файл;
• range_string — адресація ряду даних, яка містить назву сторінки та масиву клітинок. 

Наприклад, ми хочемо імпортувати в свій файл таблицю з ключами, обсягом органічного трафіку та складністю оптимізації. Для складання формули потрібно знайти URL, назву сторінки та адресу масиву. Вона може мати такий вигляд:

=IMPORTRANGE(“https://docs.google.com/spreadsheets/d/AAAAAAAAAA”, “Workplace!A1:C100”).

Зверніть увагу на кілька нюансів:

• URL та адреса масиву обов’язково беруться в лапки;
• назва сторінки відокремлюється знаком оклику;
• якщо потрібно імпортувати кілька стовпчиків повністю, вказуйте тільки їхню назву без номерів. Наприклад, “Workplace!A:C”. 

2. VLOOKUP для зручного підбору даних  

Принцип дії: шукає вказане значення в ряду даних та копіює вміст відповідної клітинки в паралельному йому стовпчику. 

Синтаксис: =VLOOKUP(search_key, range, index, is_sorted), де

• search_key — посилання на клітинку, що містить потрібне значення;
• range — масив даних, в якому виконується пошук. Зверніть увагу: формула шукає значення в крайньому лівому стовпчику масиву;
• index — номер стовпчика, з якого підтягуватиметься інформація;
• is_sorted — тип перегляду. Рекомендуємо завжди користуватися точним підбором, який задається параметрами «FALSE» або «0». Якщо потрібна приблизна відповідність, можна встановити «TRUE». 

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

=VLOOKUP(G3;$B$3:$F$12;5;FALSE).

Корисна порада: ви можете автоматично заповнювати клітинки. Натисніть на клітинку, що містить формулу, та наведіть на квадрат у нижньому правому куті. Коли зображення курсора зміниться, затисніть ліву кнопку миші та розтягніть формулу на всі необхідні клітинки. 

У такому випадку всі посилання автоматично будуть скореговані. Це зручно для вибору потрібного значення. Однак масив для пошуку повинен бути незмінним. Зверніть увагу: щоб зберегти його в такому вигляді, ми позначили назви стовпчиків та номери рядків знаком долара. Щоб зробити це, наведіть на адресу масиву в формулі та натисніть кнопку F4 на клавіатурі. 

3. QUERY для інтеграції SQL-запиту 

Принцип дії: дає змогу створювати запити мовою програмування баз даних SQL. Використовується для обробки інформації у цьому документі або зовнішніх джерелах. 

Синтаксис: =QUERY(range, sql_query), де

• range — ряд, в якому здійснюється перевірка;
• sql_query — код запиту.

Наприклад, у нас є масив URL з різним типом контенту. Нас цікавлять дописи для блогу, позначені як «Blog Post». 

Щоб відібрати тільки потрібні нам URL, скористаємося такою формулою з SQL-запитом:

=QUERY(DATA!A:B,”select A where B = ‘Blog Post'”).

4. ARRAYFORMULA, щоб застосувати одну формулу до багатьох клітинок 

Принцип дії: застосовує однакову формулу для всіх клітинок, розташованих нижче в стовпчику. 

Синтаксис: =ARRAYFORMULA(array_formula), де

• array_formula — формула, яку потрібно застосувати до масиву. 

Наприклад, ми хочемо порахувати кількість кліків, знаючи обсяг органічного трафіку та CTR. Щоб зробити це для всього масиву, необов’язково повторювати одну формулу багато раз. Можна застосувати таку функцію:

=ARRAYFORMULA(C3:C12*D3:D12). 

Зверніть увагу: цього разу в формулі зазначено не клітинки, а ряди даних, які обробляються функцією. 

5. REGEXEXTRACT, щоб вибрати потрібні фрагменти тексту 

Принцип дії: застосовує регулярні вирази до масивів даних — так само, як під час автозаміни в текстовому редакторі або під час програмування. 

Синтаксис: =REGEXEXTRACT(text, regular_expression), де

• text — посилання на клітинку;
• regular_expression — вираз, який потрібно до неї застосувати. 

Наприклад, у нас є масив URL, з яких ми хочемо витягнути адресу базового домену. Для цього підходить регулярний вираз із синтаксисом «^(?:https?:\/\/)?(?:[^@\n][email protected])?(?:www\.)?([^:\/\n]+)». Щоб уникнути помилок, застосуємо функцію IFERROR. Кінцева SEO-формула для Google Sheets виглядатиме так:

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

Корисна порада: якщо ви не знайомі з регулярними виразами, скористайтеся ресурсом RegexR, де докладно описано їхній синтаксис. 

6. IMPORTXML для краулінгу вебсайтів 

Принцип дії: функція переходить за посиланнями та використовує мову запитів XPath для отримання необхідної інформації. 

Синтаксис: =IMPORTXML(url, xpath_query), де

• url — адреса клітинки, що містить посилання;
• xpath_query — запит XPath. 

Наприклад, ми хочемо використати XML-формулу Google Sheets для SEO-рангів сторінок, витягнувши з них метатеги. Щоб отримати Title, знадобиться такий запит:

=IMPORTXMLA2,”//title”).

Корисна порада: рекомендуємо підручник для вивчення синтаксису XPath. Ви також можете отримати необхідні запити в режимі розробника Google Chrome. Для цього потрібно натиснути правою кнопкою миші на потрібний вам елемент та вибрати таку дію:

Формули Google Sheets — потужний інструмент для безкоштовної SEO-аналітики 

Трохи майстерності, терпіння та практики — і ви зможете складати звіти з ефективності пошукової оптимізації не гірше, ніж дорогі платні сервіси. Використання формул Google Sheets для SEO-ранжування, керування масивами даних та збирання даних дадуть змогу заощадити купу часу та спрямувати його на дійсно важливі завдання. І це ще не все, адже електронні таблиці підтримують макроси. Але це вже тема для іншої статті. 

Поширені запитання 

Які базові SEO-формули доступні в Google Sheets? 

Дуже корисні функції — COUNTIF та SUMIF для підрахунку та додавання за умовами, IF — для перевірки дотримання умови, SPLIT — для розділення клітинок, CONCATENATE — для злиття. 

Чи можна використовувати SEO-формули Microsoft Excel в Google Sheets? 

Тільки ті, що використовують базові математичні операції — додавання, множення тощо. Більш складні функції відрізняються за синтаксисом. 

Як створити SEO-формулу в Google Sheets? 

Вибрати клітинку, ввести знак «=», потім потрібну функцію та її параметри. Для посилання на клітинки чи ряди слід ввести їхні адреси у відповідній частині формули.