В інтернеті є безліч сервісів для SEO оптимізаторів, які пропонують різні платні послуги. Частина цих платних функцій перетинається з можливостями всім доступного Excel, а тому ми можемо нікому не платити, а безкоштовно використовувати Excel для SEO, отримуючи при цьому ті ж самі результати.
Ми часто зустрічаємося з необхідністю чистити безліч даних або, наприклад, фільтрувати базу e-mail адрес, посилання та інше. Причому, як це часто буває, потрібно фільтрувати величезну кількість посилань.
І всі ці речі можна робити за допомогою потужного і гнучкого інструменту - Excel. Якщо ви - SEO оптимізатор, то вміння правильно використовувати Excel для потреб SEO зробить вашу роботу більш продуктивною.
Зрозуміло, повністю всі фішки, які тільки є в природі по Excel ми не розповімо, але кілька корисних функцій в Excel сео оптимізатору однозначно знадобляться.
1. Видаляємо всі посилання з Excel
Ця функція стане в нагоді, коли ми експортуємо якісь дані в Excel, де будуть міститися посилання. І нам потрібно ці посилання видалити.
Excel влаштований так, що за замовчуванням він розпізнає текст з http:// як гіперпосилання. А це означає, що Excel на автоматі зафарбовує такий текст синім кольором і підкреслює його. А якщо ви клікнете на такі елементи, то ці урл-адреси відкриються в браузері.
І якщо аналізувати великий масив даних, то ці гіперпосилання не завжди будуть доречні. Тому простіше перетворити їх в звичайний текст.
Ви можете конвертувати всі гіперпосилання в звичайний текст наступним чином. Спочатку натискаємо одночасно клавіші Ctrl+A. Так ми виділимо всі осередки на аркуші. Після цього натискаємо праву кнопку миші і вибираємо пункт «Видалити гіперпосилання».
2. Як красиво вставити довгі тексти в Excel
Іноді вміст клітинки занадто великий і текст, який в ній розміщений, не дуже зручно аналізувати. Коли копіюєш текст звідки-небудь відразу в клітинку, то клітинка автоматично розтягується по вертикалі. І в підсумку це псує вигляд нашої сторінки. Тому можна надати осередку більш привабливий вигляд. Для цього після того, як ви скопіювали дані в клітинку, клікніть по стовпчику, де містяться потрібні дані (А, В, С і т.д.). Далі клікніть на кнопку «Перенести текст» на вкладці «Головна».
до
після
Тепер, якщо потрібно буде щось записати в клітинку, яка розташована наступною за нашою з текстом (в даному випадку це клітинка в стовпці F, то текст в клітині стовпця E буде приховано і буде більш зручно аналізувати дані.
3. Як закріпити область в Excel
Часто при аналізі великого масиву даних потрібно скролити сторінку вниз, а якщо у нас при цьому є заголовки, то буде незручно аналізувати дані. Цю проблему можна вирішити.
Прокручуємо нашу сторінку в Excel до самого верху, далі йдемо на вкладку «Вид» і натискаємо на кнопку «Закріпити області» і далі на кнопку «Закріпити верхній рядок». Готово! У нас зараз закріплені заголовки
Тепер ми можемо прокручувати сторінку вниз і бачимо, що наші заголовки відображаються незалежно від того, як глибоко вниз ми прокрутили сторінку. І відтепер вам не доведеться кожного разу прокручувати сторінку вгору, щоб подивитися заголовок стовпця, клітинку з якого ви аналізуєте. Якщо ви займаєтеся SEO, ви рано чи пізно зіткнетеся з необхідністю закріплення областей.
4. Як вирізати дані з таблиці, які не потрібні
Фільтри - це одні з найбільш часто використовуваних функцій в Excel. Вони прості у використанні і можна швидко отримати потрібну інформацію. SEO спеціаліст обов'язково повинен вміти користуватися цими фільтрами.
Перш за все, вибираємо якусь клітинку. Далі йдемо на вкладку «Дані» і натискаємо на кнопку «Фільтр». Після цього біля кожного нашого заголовка з'явиться випадаючий фільтр.
Після цього можна натискати на кожен випадаючий фільтр в заголовках, щоб фільтрувати дані в Excel за потрібними нам значеннями. У випадаючому фільтрі можна прибрати позначку з чекбокса, щоб не відображати якусь інформацію.
Цими даними тут можна взагалі вертіти як заманеться. Можна ставити відображення тільки потрібних рядків, що містять, наприклад, значення 11 (як показано на зображенні вище) і т.д. і т.п.
5. Знаходимо і замінюємо масово елементи в таблиці
Функція «Знайти і замінити» не так часто використовується в Excel. Багато користувачів Excel навіть не уявляють собі, як використовувати цю функцію для очищення безлічі даних в текстових рядках за один раз.
Наприклад, в рядках містяться url-адреси з префіксом www. і нам потрібно цей префікс централізовано почистити. Вірніше, видалити з усіх url-адрес в таблиці префікс www. Щоб це зробити, виділіть стовпець шляхом натискання клавіші миші на його заголовок, а потім введіть поєднання клавіш «Ctrl+H», щоб вивести діалогове вікно «Знайти і замінити». Далі тут в поле «Знайти» вводимо значення «www.» і більше нічого ніде не вводимо. Натискаємо кнопку «Замінити все».
Готово! Тепер ми видалили всі url-адреси, які містять префікс «www.» в цьому стовпці. При необхідності аналогічну процедуру можна виконати для інших стовпців. Це найшвидший спосіб чистки даних в Excel.
6. Умовне форматування, виявляємо дублікати
Умовне форматування використовується для фарбування клітинок в листі, якщо виконуються певні умови. Наприклад, нам потрібно знайти всі дублі в певному стовпці Excel.
Ми натискаємо на заголовок стовпчика, після чого на вкладці «Головна» натискаємо на кнопку «Умовне форматування» - «Правила виділення клітинок» - «Повторювані значення...». Після цього натискаємо у спливаючому вікні ОК і у нас в таблиці будуть виведені всі повторювані значення, які були знайдені в виділеному стовпці. Ці повторювані значення будуть підсвічені.
7. Імпорт з CSV. файлу
Кожен SEO спеціаліст використовує в роботі такі сервіси як Ahrefs, Semrush, Google Analytics і т.д. У всіх цих SEO сервісах є можливість експортувати отримані дані в CSV.файл. Коли ви відкриваєте цей CSV.файл, ви можете працювати з ним як завгодно. Але якщо ви працювали з цим типом файлів, то повинні знати, що все-таки це не завжди зручно і простіше працювати з даними в Excel.
Тому проблема вирішується таким чином. Відкриваємо Excel без будь-яких даних, далі переходимо на вкладку «Дані» і натискаємо на кнопку «Отримання зовнішніх даних» у спливаючому вікні вибираємо «З тексту».
Далі знаходимо через провідник наш CSV.файл і натискаємо «Імпорт». Далі у нас з'явиться спливаюче вікно майстра імпорту, де в трьох кроках потрібно буде вибрати відповідні налаштування. На першому етапі необхідно вибрати формат файлу, щоб дані правильно відображалися.
На другому кроці потрібно вибрати роздільник, а на третьому - формат даних стовпця. Ну і в кінці потрібно буде вибрати в який лист (поточний або новий) вставити імпортовані дані. Готово! Тепер у нас імпортований CSV. файл.
8. Підрахунок клітинок, які відповідають певним критеріям
Результатом нашої роботи будуть якісь узагальнені дані. SEO - не виняток.
Функція РАХУНОКЯКЩО (COUNIF) досить проста і складається з двох складових:
- Де ви хочете порахувати
- Що ви хочете порахувати
В даному випадку можна змінювати стовпці і значення в залежності від того, що нам потрібно. Тільки не забудьте поставити тут текстовий критерій в лапки.
Таким ось чином можна порахувати, наприклад, скільки разів зустрічається те чи інше слово або число в масиві даних.
Сама по собі функція РАХУНОКЯКЩО (COUNIF) набагато ширша за своїми можливостями, ніж сказано вище, тому детальніше можете подивитися у відео.
9. Розширити клітинку до потрібної ширини
Для цієї функції у нас найкоротший опис. Тут все просто. Іноді у нас клітинки виглядають ось так.
Тобто, до кінця не видно, який текст в клітинці А1. Щоб це виправити, наводимо курсор миші на роздільник між стовпчиками, і клікаєм по ньому двічі. Після цього клітинка розтягнеться до ширини, необхідної для повного перегляду вмісту.
10. Функція ВПР (VLOOKUP)
Можливо, раніше ви вже чули про знамениту функції ВПР (VLOOKUP). Коли ми тільки дізналися про неї, довго не могли зрозуміти, що до чого. Тому не дивуйтеся, якщо ви нагуглите щось про функцію ВПР в Excel і нічого відразу не зрозумієте. Але ми все одно спробуємо пояснити, тому що іноді ця функція буває дуже і дуже потрібна.
Функція ВПР в Excel потрібна, щоб зістикувати між собою значення з різних таблиць в одній на основі аналізу даних в клітинках. Ось найпростіший приклад.
Припустимо, ми напарсили дані по сайтах і в одній таблиці у нас є два стовпчики - в одному урли сайтів, у другому - анкор, або позиції або взагалі все що завгодно ще. А в другій таблиці для тих же самих сайтів спарсили дані по ТИЦ (тепер це вже ІКС - індекс якості сайтів), DA, Alexa Rank. І порядок, в якому розміщені домени в двох таблицях, різняться. Тобто, ми вручну не зможемо скопіювати і вставити дані з однієї таблиці в іншу, щоб вони співпадали.
На зображенні вище ви можете бачити, що сайти одні й ті ж, але їх порядок в першому і другому випадку різний. Тобто, щоб у нас в один рядок були всі наявні дані по site1.ua, потрібно ці дані перенести вручну. А це дуже незручно. Ну і для того, щоб це зробити на автоматі для великої кількості даних, нам якраз і стане в нагоді функція ВПР.
Використовуємо таку формулу
=VLOOKUP("A2;$E$2:$G$11;2;FALSE")
де:
- Параметр А2 - це перший параметр, який визначає по якому значенню ми будемо шукати збіги. Тобто, в даному конкретному випадку нам потрібно зістикувати дві таблиці по доменах
- Параметр E2 і G11 - це стобці, з яких ми будемо брати значення
- 2 - це номер стовпця в цих всіх стовпцях, в яких ми шукаємо збіги. В даному конкретному випадком ми шукаємо в трьох стовпчиках - Е, F і G. Рахується зліва-направо. Ми хочемо пристикувати до нашихх доменів з першої таблиці дані по ІКС (індексу якості сайтів). А стовпець з цими даними йде другим за рахунком (зліва-направо) в шуканих даних
- Ну і останній параметр в цій формулі - це «БРЕХНЯ». Це означає, що ми шукаємо точні співпадіння. Якщо використовувати значення «ІСТИНА», то у нас при відсутності точних збігів будуть використовуватися найближчі значення по спадаючій. І так, якщо будете використовувати значення «ІСТИНА», то робіть сортування стовпця по зростанню, тому що можуть бути некоректні результати.
Далі ми виділяємо клітинку, з якої бажаємо почати, в даному випадку С2, і вводимо формулу.
Ввели формулу, натиснули на галочку і отримуємо наступний результат.
Як бачимо, клітинка С2 прийняла значення 1500, що відповідає site1.ua. Тепер якщо ми потягнемо за нижній правий куточок цієї клітинки вниз і протягнемо до клітинка С11, то у нас автоматично заповняться всі значення для інших клітинок.
При роботі з різними джерелами і з великою кількістю даних функція ВПР (VLOOKUP) в Excel є просто незамінною для SEO фахівця.
11. Функція РАХУНОКЯКЩО (COUNTIF)
Функція РАХУНОКЯКЩО добре застосовна для аналізу посилального профілю. Наприклад, ми вивантажили з Ahrefs контрольний профіль нашого сайту. Тобто у нас буде таблиця CSV. або Excel з купою анкорів. У світлі нинішніх репресій з боку пошукових систем по відношенню до посилань і їх якості, потрібно ретельно контролювати, щоб частка природних анкорів типу «тут», «тут», або у вигляді адреси сайту «site.ua» була досить високою.
Тому наше завдання - проаналізувати контрольний профіль і вичленувати, скільки в процентному співвідношенні зустрічається те чи інше слово. Припустимо, у нас є Excel таблиця зі списком анкорів.
У нас тут зустрічається анкор у вигляді url-адреси сайту - site1.ua. Ми хочемо перевірити, скільки буде у відсотках вміст анкорів, що включають в себе «site1.ua». Врахуйте, що в даному випадку не точне входження за назвою домену, а саме анкори, які містять в собі крім усього іншого «site1.ua». Для цього використовуємо формулу
=COUNTIF(B1:B12;"*site1.ua*")/(COUNT3(B1:B12)+РАХУВАТИПУСТОТИ(B1:B12))
Отримуємо наступний результат.
Клітинку для виведення результату можна вибрати будь-яку. До речі, як бачите, у нас результат вийшов у вигляді числа, а нам потрібно в процентах. Для цього в Excel достатньо натиснути на клавіші Ctrl+Shift+5 і тоді наше число відобразиться у вигляді відсотків.
12. Функція ЗБІГ
Функція ЯКЩО - ще одна корисна в Excel. Якщо хтось парсить свої сайти, а потім експортує отримані дані в Excel, то ви повинні знати, що є стовпець з релевантними URL вашого сайту, а є стовпець з URL, які знаходяться у видачі по пошуковому запиту. Але так іноді буває, що для одного і того ж пошукового запиту релевантний URL і URL, що знаходиться у видачі, можуть бути різними.
Вручну шукати такі розбіжності досить складно, тому в даному випадку на допомогу прийде функція ЗБІГ.
Тепер уявімо, що у нас є таблиця Excel. В одному стовпчику у нас релевантні сторінки в Google для пошукових запитів, у другому - сторінки, які знаходяться в пошуковій видачі за тими ж запитами.
Нам потрібно знайти рядки, в яких релевантна сторінка відрізняється від сторінки, яка знаходиться у видачі по пошуковому запиту. Для цієї мети ми використовуємо формулу.
=ЗБІГ(N2;O2)
Тут вказані клітинки, в яких проводиться пошук. Після використання формули отримуємо результат.
У стовпці P в кожному рядку праворуч від наших URL, тепер зазначено «БРЕХНЯ» або «ІСТИНА». ІСТИНА - це значить, що дані двох клітинок збігаються. БРЕХНЯ - не збігаються. Далі можна відфільтрувати дані за цими двома параметрами і легко знайти пошукові запити, для яких у видачі одна сторінка, а релевантна сторінка - інша.
13. Функція Текст за стовпцями (Text to Columns)
Ще одна корисна функція Excel для кожного SEO фахівця. Працює наступним чином. Припустимо, ми пішли в Google Search Console і експортували звідти дані за нашими ключовими словами. Експортуються звідти дані в CSV. формат, і якщо відкрити цей файл, то зазвичай бачимо наступне.
Щоб правильно відобразити дані в Excel, нам необхідно спочатку відкрити порожній Excel так, як показано в пункті 7 цієї статті (імпорт з CSV. Файлу). І вже після імпорту цим способом у нас дані будуть відображатися ось в такому вигляді.
Тобто, за фактом ми маємо масив даних з роздільниками у вигляді коми. І нам потрібно розбити всі ці дані по стовпцях. Для цього виділяємо потрібні нам клітинки в стовпці А, після чого на вкладці «Дані» натискаємо на кнопку «Текст за стовпцями».
Далі у нас з'являється спливаюче вікно. На першому кроці ми залишаємо «з роздільниками». На другому кроці в якості символу-роздільника ставимо «кома» (в вашому випадку роздільник може бути інший).
На третьому кроці вибираємо формат даних стовпця як «текстовий» і натискаємо «Готово». Тепер таблиця у нас прийме ось такий вигляд.
Погодьтеся, SEO фахівцям буде значно простіше аналізувати дані в Excel в такому вигляді.
14. Візуалізація звітів
Візуальні звіти в Excel завжди простіше сприймаються і доносять інформацію до користувача значно швидше, ніж просто цифри. Тому можна використовувати функцію РАХУНОКЯКЩО (COUNTIF), щоб отримані числові результати представити у вигляді графіків.
Якщо ви зробили якісь підрахунки, використовуючи функцію РАХУНОКЯКЩО, давайте спробуємо уявити ці дані у вигляді кругової діаграми. Уявімо, що у нас є деякі дані.
Щоб представити ці дані в Excel за допомогою кругової діаграми, спочатку виділяємо весь діапазон клітинок, які потрібно відобразити в діаграмі, потім переходимо на вкладку «Вставка», і далі вибираємо будь-яку діаграму на смак.
Далі можна погратися з даними в «Додати елемент діаграми». Тут є такі елементи як «підписи даних». В результаті у нас може вийти наступне.
Ця функція цілком може стати комусь в нагоді.
Обробка: Vinci