Przydatne funkcje w Excelu i Google Sheets dla specjalistów SEO

Zgodzisz się, że powtarzalne zadania zajmują specjalistom SEO za dużo czasu? Na szczęście jest na to prosty sposób. Poznaj podstawowe i zaawansowane funkcje w Excelu i Google Sheets. Miej te formuły zawsze pod ręką, aby zredukować czasochłonność przygotowywania raportów i analizowania danych.

Kiedy funkcje w Excelu i Google Sheets przydają się SEOwcom?

Aby specjalista SEO mógł stworzyć skuteczną strategię pozycjonowania strony internetowej, musi nie tylko śledzić działania konkurencji, ale także monitorować zmiany zachodzące w widoczności domeny w Google. Celny dobór słów kluczowych ma ogromne znaczenie dla budowy profilu linków, optymalizacji technicznej serwisu i doskonalenia tekstów pod kątem SEO. Do realizacji tych wszystkich zadań niezbędna jest praca z arkuszami kalkulacyjnymi, które wspierają SEOwca w obróbce danych. 

Jeżeli pobierasz raporty z tak popularnych narzędzi, jak Senuto, SEMSTORM czy SurferSEO, doskonale wiesz, że sortowanie i filtrowanie danych zgromadzonych w setkach wierszy zajmuje sporo czasu. Ręczne ujednolicanie danych w komórkach? Zapomnij o tym. Niezależnie od tego, czy używasz programu Microsoft Excel, czy korzystasz z arkuszy Google Sheets, możesz poznać liczne formuły, które przyspieszą czasochłonne zadania.

Przydatne funkcje w Excelu i Google Sheets dla SEOwców mają zastosowanie m.in. w:

• uporządkowaniu informacji pobranych w pliku CSV,
• filtrowaniu i sortowaniu wartości,
• tworzeniu tabel,
• wyszukiwaniu i zamienianiu fraz na inne,
• kolorystycznym wyróżnianiu danych,
• usuwaniu niepożądanych odstępów,
• uzyskiwaniu meta tytułów i meta opisów na podstawie linków,
• gromadzeniu linków wychodzących z danej strony,
• pobieraniu danych z innych arkuszy,
• tłumaczeniach fraz, 
• obliczaniu liczby znaków w komórkach.

Podstawowe funkcje w Excelu dla specjalistów SEO

Znajomość podstawowych funkcji w Excelu przyda Ci się szczególnie do analizy widoczności strony internetowej. Im obszerniejszy raport generujesz, tym trudniejsze staje się wydzielenie poszczególnych danych. 

Aby nauka była efektywna, postaraj się przygotować dowolny raport do testowania poniżej opisanych funkcji. Jeżeli chcesz korzystać z naszego pliku, aby mieć identyczny widok, kliknij tutaj.

Tekst jako kolumny

Narzędzia SEO pozwalają na wygenerowanie raportów przyporządkowujących liczne dane do domen, subdomen czy słów kluczowych. Wyeksportowany plik może mieć format CSV, aczkolwiek popularne narzędzia, np. SEMSTORM czy Senuto, pozwalają na pobranie pliku XLS. 

Przyjmijmy jednak, że jedyną dostępną opcją jest CSV – taki plik po otworzeniu wygląda mało przyjaźnie. Dla uproszczenia weźmy przykład spoza obszernego raportu SEO. Załóżmy, że arkusz kalkulacyjny powinien zawierać 3 kolumny: imię i nazwisko autora, liczbę książek i cenę. Niestety format CSV prezentuje dane w jednej kolumnie, oddzielając je średnikami.

W takim przypadku zaznacz całą kolumnę i udaj się do zakładki „Dane”, a następnie kliknij „Tekst jako kolumny”.

Po wyświetleniu się okienka zaznacz opcję „Rozdzielany”.

Następnie wskaż, że ogranicznikiem jest średnik.

Na tym etapie możesz sfinalizować dzielenie tekstu na kolumny i kliknąć „Zakończ”.

Filtrowanie i sortowanie bez tabeli

Do jednych z prostych i ważnych funkcji w Excelu należy filtrowanie i sortowanie danych. 

Kiedy spojrzysz na poniższy screen, zobaczysz fragment raportu z SEMSTORM. Plik zawiera listę słów kluczowych, na które jest widoczny Onet.pl. Kiedy podzielisz wiersze na komórki za pomocą wcześniejszej funkcji, otrzymasz czytelniejszy efekt, lecz wciąż niesprzyjający wygodnej analizie danych.

Excel pozwala na dwojakie filtrowanie i sortowanie danych – z utworzeniem tabeli lub bez. Teraz skupimy się na drugim wariancie. W tym celu należy zaznaczyć komórkę z nazwą dowolnej kolumny, np. URL, a następnie przejść do zakładki „Dane” i kliknąć „Filtruj”. Tym prostym sposobem każda komórka z nagłówkiem kolumny została wzbogacona o przycisk ze strzałką.

Kiedy klikniesz niewielki kwadracik ze strzałką w komórce, pojawi się okno, w którym możesz określić, czy chcesz uszeregować dane w pewnej kolejności, czy wyfiltrować konkretne komórki. 

Jeżeli wpiszesz „biznes.onet.pl” i klikniesz „OK”, widoczne wyniki zostaną ograniczone dla tej subdomeny.

Uwaga – wpisanie słowa „biznes” wiązałoby się z uwzględnieniem innych subdomen, które zawierają w adresie URL tę frazę.

Tworzenie tabeli

Wróćmy do momentu, w którym plik był pozbawiony filtrów. Aby utworzyć tabelę z funkcją filtrowania i sortowania w Excelu, należy zaznaczyć wszystkie dane. Ręcznie? W przypadku setek, a nawet tysięcy rekordów byłoby to dość czasochłonne. 

1. Aby błyskawicznie zaznaczyć wszystkie dane w raporcie SEO w Excelu, wystarczy zaznaczenie komórki A1 i przytrzymanie klawiszy Ctrl + Shift+ End.
2. Następnie przejdź do zakładki „Wstawianie” i wybierz „Tabelę”. Ewentualnie – przytrzymaj Ctrl + T.
3. Nowe okienko pokaże zakres danych uwzględnionych w tabeli i opcję „Moja tabela ma nagłówki”. Pole obok powinno zostać zaznaczone.

      4. Po kliknięciu „OK” otrzymasz tabelę z funkcją filtrowania i sortowania danych. Atutem tego rozwiązania jest wyróżnienie kolorystyczne wierszy.

Blokowanie wierszy i kolumn

Załóżmy, że preferujesz widok tabeli. Niestety po kilkukrotnym przescrollowaniu okazuje się, że pierwszy wiersz z czytelnie wyróżnionymi tytułami kolumn znika. Analogicznie przesunięcie tabeli w bok spowodowałoby schowanie się pierwszej kolumny. Na szczęście istnieje kolejna prosta funkcja w Excelu, którą warto znać, jeśli chcesz mieć widok na wiersz lub kolumnę podczas podróżowania po tabeli. 

Przejdź do zakładki „Widok” i kliknij „Zablokuj okienka”. Wyświetlą Ci się 3 opcje:

• zablokowanie komórek na podstawie zaznaczenia użytkownika,
• zablokowanie pierwszego wiersza w polu widzenia,
• zablokowanie pierwszej kolumny w polu widzenia.

Funkcja zablokowania pierwszego wiersza w Excelu sprawia, że nagłówki pozostają widoczne, niezależnie od tego, do której pozycji sięgniesz. O ile kolumny w przykładzie są dość intuicyjne i raczej trudno o pomyłkę, o tyle „przyklejenie” głównego wiersza jest pomocne w porównywaniu danych z bogatszych raportów.

Możesz jednocześnie zablokować pierwszy wiersz i pierwszą kolumnę. W poniższym przykładzie przesunięcie tabeli spowodowało ukrycie drugiej kolumny z miesięczną liczbą wyszukiwań.

Warto dodać, że wcześniej wskazane okienko wzbogaci się o nową opcję po zablokowaniu komórek – odblokowania wszystkich wierszy i kolumn do przewijania całego arkusza.

Wyróżnienie danych kolorami

O ile przykładowa tabela zawiera aż 3 odcienie niebieskiego, które zapobiegają przypadkowemu ześlizgnięciu się spojrzenia do niższego lub wyższego wiersza podczas odczytywania danych, o tyle nie jest to wystarczające urozmaicenie do wygodnej analizy. Wśród funkcji w Excelu dla SEOwców warto wymienić formatowanie warunkowe. Ta opcja pozwala na automatyczne wyróżnienie kolorem komórek z danymi zbieżnymi z określonym zakresem.

Załóżmy, że chcesz zróżnicować wypełnienie komórek w kolumnie z pozycjami w Google. Pozycje od 1 do 10 chcesz pokolorować na zielono, pozycje od 11 do 20 na pomarańczowo, pozycje od 21 do 30 na żółto, a pozycje poniżej 30. miejsca – na czerwono.

W tym celu:

1. Zaznacz kolumnę.
2. Przejdź do zakładki „Narzędzia główne”.
3. Wybierz „Formatowanie warunkowe”, a następnie „Między”. 
4. W pierwszym polu wpisz „1”, w drugim polu – „10” i wybierz „Zielone wypełnienie z ciemnozielonym tekstem” lub „Format niestandardowy”, aby wyświetlić okno „Formatowanie komórek”. W zakładce „Wypełnienie” możesz wybrać dowolny odcień zielonego. 

  5. Postąp analogicznie dla pozycji od 11 do 30.

 6. Dla pozycji poniżej 30. miejsca należy wybrać opcję „Większe niż”.

Myślisz, że wypełnianie komórek kolorem ma wyłącznie zaletę wizualną? Otóż nie. Po kliknięciu strzałki w nagłówku zaznaczonej kolumny pojawią się nowe opcje – sortowanie według kolorów komórek i według kolorów czcionek, jeśli zostały ustawione także kolory tekstu. Ta funkcja w Excelu przydaje się zarówno podczas analizowania danych, jak i do wykonania atrakcyjnego raportu dla klienta, któremu łatwiej będzie odnaleźć pożądane informacje.

Znajdź i zamień

To jedna z najpopularniejszych funkcji w Excelu. Aby wyszukać i zamienić dane kilkoma kliknięciami, należy:

• zaznaczyć obszar, np. kolumnę URL,
• użyć skrótu Ctrl + H,
• uzupełnić jedno lub oba pola w zależności od pożądanych efektów,
• kliknąć „Zamień wszystko”.

Używając funkcji „Znajdź i zamień” możesz skracać zawartość komórek. Pokażemy to na przykładzie skracania subdomen. W tym celu należy wpisać wiadomosci.onet.pl/* w pierwszym polu, w drugim – wiadomosci.onet.pl. Gwiazdka sygnalizuje, że po ukośniku następuje ciąg znaków. Jej brak spowodowałby wyłącznie usunięcie ukośnika.

Tak prezentuje się arkusz po użyciu funkcji znajdywania i zamieniania.

Jeżeli pierwsze pole będzie zawierać frazę lub liczbę, a drugie pole pozostanie puste, komórki z zawartością wpisaną w pierwszym polu zostaną wyczyszczone.

Zaawansowane funkcje w Excelu dla specjalistów SEO

Zaawansowane funkcje w arkuszach kalkulacyjnych dla SEOwców umożliwiają m.in. generowanie prostych tabel podsumowujących tysiące informacji i porównywanie raportów dla tych samych słów kluczowych, lecz z przyporządkowanymi innymi danymi.

W tej części przydadzą Ci się pliki z frazami – wygeneruj je z dwóch różnych narzędzi. Pierwszy może zawierać comiesięczną liczbę wyszukiwań, a drugi – CPC.

Tworzenie tabeli przestawnej

Na początek wróćmy do znanego już pliku. Aby stworzyć tabelę przestawną w Excelu, należy zaznaczyć wszystkie dane (Ctrl + Shift + End), a następnie przejść do zakładki „Projekt tabeli” i kliknąć opcję „Podsumuj w tabeli przestawnej”. Kiedy pojawi się okienko, kliknij „OK”.

Spowodujesz przejście do nowego arkusza w tym samym pliku. Początkowo będzie świecił pustkami. Aby stworzyć tabelę, musisz wybrać pola wyświetlone po prawej stronie. Selekcja odbywa się na podstawie drag & drop, czyli przeciągania i upuszczania pól do wyszczególnionych obszarów.

Aby sprawdzić, na ile słów kluczowych wyświetlają się poszczególne adresy URL, należy:

• przenieść pole „URL” do obszaru „Wiersze”,
• przenieść pole „Słowo kluczowe” do obszarów „Filtry i Wartości”,
• w obszarze „Wartości” należy kliknąć strzałkę, następnie „Ustawienia pola wartości” i wybrać „Liczba”.

 

Wyszukaj pionowo

Teraz przejdziemy do nowych raportów. Formuła „Wyszukaj pionowo” przydaje się szczególnie do analizowania i porównywania danych z różnych źródeł. Najłatwiej ją wyjaśnić na przykładzie pliku z dwoma arkuszami, które zawierają dane związane ze słowem kluczowym, np. „bluza”. 

Pierwszy arkusz zawiera listę fraz z comiesięczną liczbą wyszukiwań. 

Drugi arkusz – podobną listę słów kluczowych z CPC. 

Twoim zadaniem jest sprawdzenie CPC dla fraz z pierwszego arkusza – dane mają się pojawić w kolumnie C. Aby tego dokonać, należy użyć formuły =WYSZUKAJ.PIONOWO(A2;Arkusz2!$D$1:$E$190;2;0).

• A2 – wskazuje komórkę, dla której należy znaleźć dane.
• Arkusz2 – wskazuje nazwę arkusza, jeżeli dane są ulokowane oddzielnie.
• !$D$1:$E$190 – wskazuje przeszukiwany zakres komórek w arkuszu. Symbol $ zapobiega przypadkowemu pobieraniu danych z innego zakresu podczas przeciągania formuły do reszty komórek.
• 2 – wskazuje numer kolumny. W naszym przykładzie kolumna E jest drugą kolumną z danymi w arkuszu. 
• 0 – wymusza wyszukiwanie dokładnego dopasowania.

#N/D oznacza brak danych dla danej frazy.

JEŻELI.BŁĄD

Kolejna funkcja w Excelu dla SEOwców jest powiązana z poprzednim przykładem. O ile znasz znaczenie #N/D, być może uważasz, że raport byłby czytelniejszy, jeśli komórki wyświetlałyby informację o braku danych. Jest na to sposób – formuła JEŻELI.BŁĄD w Excelu. Jak ją zastosować? Wystarczy w prawidłowy sposób rozszerzyć już poznaną formułę, czyli =JEŻELI.BŁĄD(WYSZUKAJ.PIONOWO(A2;Arkusz2!$D$1:$E$190;2;0);”nieznaleziono”).

X.WYSZUKAJ

Możesz nieco uprościć powyższe formuły, korzystając z nowszej funkcji X.WYSZUKAJ. Jej zastosowanie nie wymaga wskazywania określania numeru kolumny. Używając X.WYSZUKAJ, poradzisz sobie także bez formuły JEŻELI.BŁĄD. Jak to możliwe? 

Formuła =JEŻELI.BŁĄD(WYSZUKAJ.PIONOWO(A2;Arkusz2!$D$1:$E$190;2;0);”nieznaleziono”) zostałaby skrócona do =X.WYSZUKAJ(A2;Arkusz2!D:D;Arkusz2!E:E;”nieznaleziono”). To rozwiązanie pozwala na precyzyjne wskazanie pożądanych kolumn w drugim arkuszu. Dodatkowy czwarty argument zastępuje funkcję JEŻELI.BŁĄD w Excelu.

Funkcje w Google Sheets dla specjalistów SEO

Wyobraź sobie, że tylko kilka kliknięć dzieli Cię od poznania wartościowych nagłówków na landingu bądź zdobycia pełnej listy linków z danej podstrony. To możliwe dzięki wcale nie tak trudnym formułom, które nie są znane wszystkim specjalistom SEO. Poznaj przewagi, dla których warto używać funkcji w Google Sheets, nawet jeśli masz dostęp do płatnego Excela. 

Przygotuj dowolną listę linków, aby od razu przetestować poznane formuły. Uwaga – powinny uwzględniać protokół HTTPS, czyli np. https://domena.pl. 

Importowanie danych z adresu URL

Masz długą listę adresów URL, dla których chcesz wykonać zestawienie meta tytułów. Wystarczy zapamiętanie jednej prostej formuły: =IMPORTXML(A1;”//title/text()”). Należy ją wpisać w komórce obok adresu URL. Pamiętaj o uwzględnieniu poprawnej komórki, np. A2 czy C5.

Jeżeli chcesz modyfikować meta tytuł lub sprawdzić, czy aktualny nie zawiera zbędnych spacji, użyj formuły =USUŃ.ZBĘDNE.ODSTĘPY(numerkomórki) lub =TRIM(numerkomórki).

Funkcja IMPORTXML w Google Sheets służy także do innych zadań. 

Analogicznym sposobem możesz przygotować zestawienie meta opisów i nagłówków H1. W przypadku meta opisów należy użyć funkcji =IMPORTXML(„https://domena.pl”;”//meta[@name=’description’]/@content”), np. =IMPORTXML(„https://www.money.pl/wiadomosci/biznes.html”;”//meta[@name=’description’]/@content”). 

Zobacz, jak wygląda formuła w Google Sheets, kiedy wskazuje się komórki z adresami URL.

#N/A oznacza, że meta opis nie został stworzony. 

Przy okazji warto wspomnieć o prostej funkcji, która ma zastosowanie także w Excelu. Wpisując formułę =DŁ(numerkomórki) lub =LEN(numerkomórki), możesz poznać długość meta title czy meta description.

Jeżeli chcesz poznać nagłówki H1 zamieszczone na danej podstronie, wpisz =IMPORTXML(„https://domena.pl”;”//h1″), np. =IMPORTXML(„https://www.money.pl/wiadomosci/biznes.html”;”//h1″). Jeśli wskazujesz konkretne komórki w Google Sheets, formuła wygląda następująco.

Warto znać jeszcze formułę umożliwiającą stworzenie listy wszystkich linków na danej stronie, czyli =IMPORTXML(„https://domena.pl”;”//a/@href”). Wynik jest dostępny w ciągu kilku sekund.

Importowanie danych z innego arkusza

Importowanie danych z innego arkusza w Excelu polega na zaznaczeniu obszaru, z którego mają zostać pobrane dane, ale Google Sheets wymaga oddzielnej formuły =IMPORTRANGE(„https://docs.google.com/spreadsheets/d/abcdefghijklmnabcdefgh#gid=0″;”Arkusz1!A1:B2”). Oczywiście koniec formuły należy uzupełnić odpowiednią nazwą arkusza i zakresem.

Tłumaczenie fraz

Automatyczne tłumaczenie fraz w Google Sheets jest funkcją zaawansowaną, ale łatwą w użyciu. Aby przetłumaczyć polskie frazy na inny język bądź przetłumaczyć nieznane słowa na język polski, wystarczy użyć formuły =GOOGLETRANSLATE(tekst; [język_źródłowy];[język_docelowy]). Przykład pokazuje tłumaczenie fraz związanych z bluzami na język szwedzki. 

Aby poznać skróty poszczególnych języków, wyszukaj listę kodów ISO 639-1. Pamiętaj jednak, że tłumaczenie nie musi odpowiadać najczęściej wyszukiwanym frazom przez dany naród. Jeżeli chcesz poznać listę fraz z długiego ogona w innym języku, użyj narzędzi SEO, które podpowiedzą Ci słowa kluczowe używane przez native’ów.

Nawet podstawowa znajomość funkcji w Excelu i Google Sheets wspiera specjalistę SEO. Dzięki stosowaniu formuł w arkuszach kalkulacyjnych możesz zaoszczędzić czas i uniknąć błędów podczas analizy danych, szczególnie jeśli raport liczy setki, a nawet tysiące wierszy. Nawet jeżeli nie dysponujesz płatnym programem Microsoft Excel, warto używać funkcji dla SEOwców w Google Sheets. 

Podziel się przydatnymi funkcjami z działem SEO i innymi współpracownikami, aby usprawnić pracę całego zespołu. Daj znać w komentarzu, którą funkcję w Excelu lub Google Sheets uznajesz za najbardziej pomocną!