Podczas swojej pracy każdy specjalista SEO odpowiedzialny jest za tworzenie strategii marketingowej, analizę danych i raportowanie działań. Jednym z najczęściej wybieranych narzędzi do pracy z danymi jest Excel lub jego odpowiednik w wersji online – Google Sheet. Aby jak najbardziej ułatwić sobie pracę z dużą ilością danych warto poznać niektóre funkcje tych arkuszy kalkulacyjnych, które znacznie przyspieszą i zautomatyzują nasze działania.

Kiedy po raz pierwszy uruchomiono Arkusze Google, były one daleko w tyle za popularnym Excelem. Teraz przepaść między Excelem a Google Sheet zmniejsza się z dnia na dzień ponieważ Google szybko nadrobiło zaległości. Poniżej prezentujemy kilka funkcji, które powinien znać każdy specjalista SEO, niezależnie od tego z którym programem częściej pracuje.

Formuły w Excel przydatne dla SEO

Excel to przede wszystkim funkcjonalność. Sprawdza się gdy pracujesz z ogromnymi bazami danych. Oto kilka niezbędnych funkcji, które przydadzą się podczas pracy z dużą ilością danych, z którymi mierzy się na co dzień pozycjoner.

Znajdź i zamień

Załóżmy, że masz bardzo dużą listę adresów URL w postaci:

Zależy Ci na wyciągnięciu z tych danych tylko i wyłącznie nazwy domeny bez protokołu http/https i www.

Wystarczy, że zaznaczysz dane (lub całą kolumnę, w której one się znajdują) i skorzystasz ze skrótu Ctrl + H. W okienku “znajdź” wpisujesz “http://”, a okienko “Zamień na” zostawiasz puste. Klikasz Zamień wszystko. Powtarzasz cały proces 2 razy, uzupełniając okienko “Znajdź” kolejno o “https://” i “www”:

W efekcie dostajesz tylko nazwy domen:

Funkcję tę można wykorzystać także wtedy, gdy posiadasz bazę adresów URL w postaci:

Jeżeli chcesz wyciągnąć z nich tylko domeny, to po znalezieniu i zastąpieniu http://, https: // i www. możesz zredukować każdy adres URL tylko do domeny korzystając z tej formuły:

* – gwiazdka jest symbolem, który usuwa wszystko po pierwszym ukośniku

W efekcie dostaniemy to:

Jeśli chcesz pozbyć się slasha to skorzystaj z formuły jeszcze jeden raz zamieniając “/” na puste pole.

Tekst jako kolumny

Bardzo przydatna funkcja gdy pobierasz dane w formacie .csv oddzielonego przecinkami, np:

Każdy element po przecinku powinien znaleźć się w osobnej komórce. Oto jak skorzystać z formuły oddzielającej od siebie dane:

Zaznaczamy całą kolumnę i wybieramy Dane → Tekst jako kolumny

W następnym kroku możesz określić dowolny znak, którym oddzielone są elementy. W naszym przykładzie będzie to przecinek. W efekcie dostaniemy uporządkowane w osobnych komórkach dane.

Warto znać ten sposób gdy pobiera się dane np. z programów Screaming Frog lub GAchecker.

Wyszukaj pionowo

Jedna z najbardziej popularnych formuł w Excel. Jest także bardzo pomocna podczas analizowania danych SEO, np. gdy chcemy porównać dwie bazy danych. Załóżmy, że posiadamy dane z dwóch różnych źródeł, np. raport pozycji fraz kluczowych z jednego narzędzia oraz raport wyświetleń i klikalności poszczególnych fraz z drugiego narzędzia.

Do Excela pobieramy dwie listy, mogą być umieszczone w jednym arkuszu lub w dwóch osobnych – gdy danych jest bardzo dużo:

naszym celem jest uzyskanie w kolumnie C informacji o liczbie kliknięć dla wybranych w kolumnie A fraz. Wpisujemy następującą formułę:

gdzie:
A2 – to komórka do której dopasowujemy dane
$E$1:$G$15 – zakres danych, które przeszukujemy. Symbol $ blokuje nam dane, dzięki czemu podczas przeciągania formuły do innych komórek mamy pewność, że dane zaciągają się zawsze z wybranego zakresu.
2 – jest to numer kolumny z danymi, które formuła przeszukuje. W naszej tabeli z przykładu mamy trzy kolumny z danymi, które formuła może przeszukać:

Chcemy uzyskać informację o liczbie kliknięć dla wybranej frazy, dlatego wpisaliśmy “2”. Gdyby zależało nam na liczbie wyświetleń to należy wpisać “3”.

0 – to inaczej zapisany FAŁSZ, dzięki takiemu zapisowi funkcja WYSZUKAJ.PIONOWO znajdzie tylko dokładne dopasowanie.

Tak wpisaną formułę należy przeciągnąć w dół, aby dopasować liczbę kliknięć do wszystkich fraz które nas interesują:

JEŻELI.BŁĄD

Może zdarzyć się tak, że niektórych danych może zabraknąć w drugiej bazie, np. nie wszystkie frazy kluczowe z pierwszej listy będą znajdować się w drugiej. Wtedy ukaże nam się wynik, który nie wygląda najlepiej:

Dlatego zalecam, przy każdym użyciu funkcji WYSZUKAJ.PIONOWO korzystać także z funkcji JEŻELI.BŁĄD:

=JEŻELI.BŁĄD(WYSZUKAJ.PIONOWO(A2;$E$1:$G$15;2;0);”brak danych”)

efekt:

w miejsce ;”brak danych”) można wpisać np. “0”, “-” lub samo “”.

X.WYSZUKAJ

Niedawno w programie Excel pojawiła się nowsza (lepsza) wersja funkcji WYSZUKAJ.PIONOWO. Umożliwia ona oddzielne podawanie szukanej i zwracanej tablicy, co jest naprawdę dużym ułatwieniem w przypadku większych baz danych (gdy mamy np. 30 kolumn). Przykład:

chcemy wyciągnąć dane z kolumny K. Korzystając ze zwykłej funkcji WYSZUKAJ.PIONOWO musielibyśmy zaznaczyć całą tabelę, a jako numer kolumny podać “7”.

Korzystając z X.WYSZUKAJ nasza formuła wyglądałaby tak:

Chodzi o to, że dla tej funkcji mamy możliwość wybrania dowolnej tablicy, którą chcemy przeszukać – zamiast E, mogłoby to być F, G.. itd i tak samo wybrać dowolną zwracaną tablicę, bez konieczności liczenia kolumn.

Kolejnym ułatwieniem jest to, że nie potrzebujemy już osobnej formuły do nadawania wartości błędu (jeżeli.błąd). W funkcji X.WYSZUKAJ odpowiada za to czwarty argument:

Liczba znaków w komórce

Bardzo prosta funkcja, która przyda się np. podczas analizowania metadanych.

Formuła DŁ oblicza liczbę znaków w określonej komórce, np:

Łącząc tę funkcję z formatowaniem warunkowym (np. zaznaczenie na czerwono wartości większych niż 60) możemy szybko zweryfikować które metadane są za długie.

Formuła pozwalająca na połączenie zawartości komórek

Ta funkcja może być przydatna również podczas analizy fraz. Załóżmy, że posiadamy arkusz z frazami kluczowymi, do których chcemy dopisać jeszcze jedno słowo, np. nazwę sklepu – Zalando:

wystarczy, że w pustej komórce wpiszemy taką formułę:

=A1&” Zalando”

i przeciągniemy ją wzdłuż fraz kluczowych (w tym przykładzie z kolumny A).

Efekt:

Przy pomocy symbolu “&” możemy łączyć dowolnie zawartości komórek, ale należy przy tym pamiętać, aby nie pominąć spacji.

Formuła USUŃ.ZBĘDNE.ODSTĘPY

O tej funkcji warto pamiętać przy raportowaniu np. zawartości meta danych. Nieraz podczas kopiowania lub pobierania danych do treści może wkraść się niepotrzebna spacja (lub kilka spacji). Zamiast ręcznie usuwać zbędne odstępy w każdej komórce, można skorzystać z funkcji: =USUŃ.ZBĘDNE.ODSTĘPY(zakres)

W komórce obok pojawi się tekst, w którym usunięty zostanie nadmiar spacji w tekście.

Formuły w Google Sheet przydatne w SEO

Początkowo Arkuszom Google sporo brakowało do funkcjonalności Excela, ale jak to zwykle bywa, w przypadku takiego giganta jakim jest Google, bardzo szybko zmniejszyła się przepaść pomiędzy obydwoma narzędziami. Google Sheet może pochwalić się nie tylko możliwością współtworzenia arkusza przez kilku użytkowników w jednym czasie, ale także funkcjami, których Excel nie posiada. Te najbardziej przydatne w pracy pozycjonera przedstawiam poniżej.

Importowanie danych z adresu URL – IMPORTXML

Funkcja IMPORTXML pozwala importować dane z witryny.

Załóżmy, że posiadasz listę adresów URL i chcesz uzyskać zestawienie znaczników <title> dla każdego. Wystarczy że w pustej komórce obok adresu URL dla którego chcesz zaciągnąć <title> umieścisz taką formułę:

=IMPORTXML(A1;”//title/text()”)

a następnie przeciągniesz ją wzdłuż całej kolumny A, dzięki czemu uzyskasz zestawienie znaczników <title> dla wszystkich adresów URL, które się w niej znajdują.

IMPORTXML nie ogranicza się tylko do pobierania <title>. Ma wiele innych zastosowań.

Oto kilka przydatnych dla SEO formuł:

Wyciąganie wszystkich linków z danej strony:

=IMPORTXML(“https://domena.pl”;”//a/@href“)

Wyciąganie meta opisu:

=IMPORTXML(“https://domena.pl”;”//meta[@name=’description’]/@content”)

Wyciąganie H1 z danej strony:

=IMPORTXML(“https://domena.pl”;“//h1”)

Tłumaczenie fraz na inne języki

Mowa tu o funkcji GOOGLETRANSLATE, która daje możliwość tłumaczenia słów z języków obcych w Google Sheet.

Obsługa tej funkcji jest bardzo prosta. Na przykładzie:

wystarczy, że wskażemy komórkę ze słowem, które chcemy przetłumaczyć, język źródłowy w naszym przypadku PL – skrót od polskiego, oraz język docelowy – EN, czyli angielski.

Importowanie danych z dowolnego arkusza Google

Niejednokrotnie podczas pracy z danymi korzystamy z kilku arkuszy. Czasem zachodzi potrzeba pobrania danych z jednego arkusza do drugiego. Google Sheet daje taką możliwość przez skorzystanie z funkcji IMPORTRANGE – dzięki niej możesz pobrać do swojego raportu dane nawet z takiego arkusza, który nie należy do Ciebie (ale masz pozwolenie na dostęp do jego zawartości).

Do pobrania danych przez funkcję IMPORTRANGE potrzebujesz adres URL arkusza kalkulacyjnego, z którego będą importowane dane oraz ciag zakresu importowanych danych w formacie “[nazwa_arkusza!]zakres” (np. “Arkusz1!A1:A2”).

Funkcja jest bardzo przydatna podczas korzystania z WYSZUKAJ.PIONOWO, gdy przeszukiwany zakres jest umieszczony w innym Arkuszu Google:

Filtrowanie widoku

Arkusze Google umożliwiają także filtrowanie danych bez wpływu na to co widzą inni użytkownicy. Gdy pracujesz z innymi osobami nad jednym arkuszem kalkulacyjnym, w którym np. znajduje się zbiorcze zestawienie Waszych projektów, niejednokrotnie może zdarzyć się tak, że będziesz chciał zobaczyć tylko swoje dane. Warto utworzyć filtr z danymi każdego użytkownika osobno, tak aby praca była łatwiejsza. Schemat tworzenia filtra jest następujący:

W Arkuszu Google wybierz komórkę z danymi.
Wybierz Dane → Widoki filtrów → Utwórz nowy widok filtra.
Z listy w nagłówku kolumny wybierz dane, które chcesz filtrować – wszystko zapisuje się automatycznie podczas wprowadzania zmian:
kliknij OK

W ten oto sposób każda osoba może wyświetlić tylko dane, na których jej zależy jednocześnie nie zmieniając widoku danych innym użytkownikom.

Zarówno Excel jak i Google Sheet to niesamowicie potężne narzędzia, które ułatwiają pracę Specjalistów na całym Świecie. Polecam zapoznać się z poszczególnymi ich funkcjami, które pozwolą na jeszcze lepsze i szybsze raportowanie danych.