10

Najbardziej przydatna excelowa sztuczka dla pozycjonerów

Jest taka funkcja w Excelu, bez której nie wyobrażam sobie prowadzenia jakiejkolwiek poważniejszej analizy w projektach SEO. Poważniejszej, czyli takiej, w której łączę dane z kilku źródeł. Może to być Google Analytics i narzędzie słów kluczowych Google, albo Google Analytics i raport z programu Screaming Frog, czasami dane wyeksportowane z narzędzi Google dla webmasterów i np. MajesticSEO – kombinacji jest wiele.

To, co jest najważniejsze, to konieczność połączenia tych danych po jakimś wybranym, wspólnym elemencie.

Załóżmy np., że z jednej strony mamy dane o ruchu dla kolejnych fraz, wydobyte z Google Analytics.

Z drugiej strony mamy wyniki analizy tych samych fraz z narzędzia słów kluczowych Google.

Celem jest połączenie jednego zestawienia z drugim, aby zweryfikować, w którym miejscu jest duży, niezagospodarowany potencjał.

Przy kilku czy kilkunastu frazach oczywiście można taką analizę przeprowadzić ręcznie, ale gdy fraz się robi kilkadziesiąt, kilkaset czy jeszcze więcej, sprawa staje się trudna.

Excel na odsiecz

Na szczęście Excel oferuje fenomenalną funkcją Wyszukaj.Pionowo, która idealnie ten problem rozwiązuje, pozwalając łączyć zbiory danych po wspólnym elemencie. Jestem pewien, że jest to jedna z najczęściej wykorzystywanych przez mnie funkcji podczas pracy z Excelem.

Czas na krótką demonstrację.

Funkcja wyszukaj.pionowo w Excelu w działaniu

Do Excela wkleiłem dwie listy. Jedna z nich to hipotetyczny ruch dla frazy, rejestrowany w Google Analytics.

Druga lista zawiera wyniki z narzędzia słów kluczowych Google. Naszym celem jest dopasowanie jednych danych do drugich, tak, aby obok ruchu z Google Analytics pojawiła się informacja o szacunkowym potencjale raportowanym przez Google.

Obok dodamy kolumnę, która pokazuje, jaki procent tego potencjalnego ruchu udaje się nam już zagospodarować.

Dane z Google Analytics są w kolumnach A i B, dane z narzędzia słów kluczowych Google w kolumach E i F.

W kolumnie C dopasujemy jedne wartości do drugich.

W tym celu w komórce C2 wpisuję następującą formułę:

=Wyszukaj.Pionowo(A2;$E2:$F$20;2;FAŁSZ)

Co ta formuła robi? Po kolei:

  1. Zapamiętuje wartość w komórce A2
  2. Przeszukuje zawartość pierwszej (lewej) kolumny w zakresie od E2 do F20. Zaznaczyłem obydwie kolumny, a nie tylko kolumnę E, gdyż w przeciwnym razie nie mógłbym wskazać kolumny, z której chcemy pobrać dane (krok 3). Znaki dolara dodałem, aby zakres ten nie zmienił się przy przeciąganiu formuły do innych komórek.
  3. Jeśli formuła w kolumnie E, w dowolnym wierszu, znajdzie wartość taką samą, jak w komórce A2, wówczas z z komórki w drugiej kolumnie zakresu wskazanego w kroku 2, z wiersza, w którym nastąpiło dopasowanie, pobierze znajdującą się tam wartość. Gdyby przeszukiwany zakres miał np. 5 kolumn i chcielibyśmy pobrać wartość z 5 kolumny, wówczas oczywiście byłaby tu 5 zamiast 2.
  4. Opcja ?fałsz? oznacza tyle, że szukamy dokładnych, a nie przybliżonych dopasowań.

Teraz wystarczy, że formułę z komórki C2 przeciągnę w dół, wzdłuż kolumny A i B, a Excel pięknie dopasuje do siebie wyniki.

Jeszcze tylko komórka z procentowym nasyceniem i analiza gotowa. Prezentuje się następująco:

Wynik analizy z użyciem funkcji wyszukaj.pionowo w Excelu

Tę operację można sobie dodatkowo ułatwić, unikając przeciągania formuły, dzięki wykorzystaniu mechanizmu tabel w Excelu. Ale to opowieść na inną okazję.

Podany powyżej przykład jest bardzo prosty, ale gdy pracujemy na zbiorach danych liczących tysięce wierszy (a to zdarza się bardzo często np. podczas pracy nad raportowami z linkowania), wówczas funkcja ta jest wręcz nieodzowna.

Jeśli macie swoje ulubione sztuczki w Excelu, które wykorzystujecie w działalności SEO, w PPC czy analityce, dajcie proszę znać w komentarza.

Udostępnień 0
Marcin Lejman
 

Jestem współwłaścicielem Critical.pl. Prowadzę przede wszystkim projekty związane z analityką internetową, optymalizacją konwersji i budową strategii online, a nadzoruję działania SEO i PPC prowadzone przez naszą firmę. Jeśli czujesz, że Twój biznes ma niewykorzystany potencjał i chcesz go rozwinąć, skontaktuj się ze mną - chętnie porozmawiam o możliwościach.

Click Here to Leave a Comment Below 10 comments
Rafał - 11/01/2013

Muszę podziękować za wpis, przećwiczyłem i działa. Okazuje się, że nie tylko w excelu ale również w LibreOffice.
Bardzo przydatne.

Pozdrawiam

Reply
M40 - 11/01/2013

Niby prosta sprawa ale bardzo pomocna :)

Reply
Sigmeo - 11/01/2013

Korzystam z tego od jakiegoś czasu i naprawdę przydatna rzecz ;)
Excell ogólnie jest świetnym narzędziem do SEO :)

Reply
Notatkuj - 11/01/2013

Bardzo ciekawy trik. Osobiście do projektów SEO korzystam z OpenOffice.org Calc – czy w tym oprogramowaniu również da radę zastosować taką formułę?

Reply
    Marcin Lejman - 20/01/2013

    Wydaje mi się, że jest taka możliwość, choć zapewne z drobnymi różnicami w składni – nie korzystam niestety z tego pakietu, więc więcej nie jestem w stanie pomóc.

    Reply
    Domink - 29/06/2013

    W OpenOffice też można bez zastrzeżeń stosować podstawowe formuły z klasycznego Excela – nie wiem co z bardziej zaawansowanymi formułami, bo sam ich nie stosuję.

    Reply
Semedia - 12/01/2013

Z pewnością przydatna informacja dla każdego specjalisty SEO, który chce usprawnić swój warsztat. Praca z excelem jest wciąż mocno niedowartościowana w codziennych obowiązkach.

Reply
Marcin Kościelski - 30/01/2013

Bardzo przydatna funkcja, szukałem czegoś takiego, dzięki:) Jednak zauważyłem drobny błąd (a może to różnica wersji? mam excel2013): należy zablokować dolarem także kolumnę E, w przeciwnym razie przy przeciąganiu zmienia się zakres, zatem u mnie działa taka funkcja: =Wyszukaj.Pionowo(A2;$E$2:$F$20;2;FAŁSZ)

Pozdrawiam!

Reply
Krzysztof Gromek - 06/02/2013

Też przetestuję to. Faktycznie może być bardzo przydatne. Nigdy nie myślałem, że Excel może być przydatny w SEO. Świetny artykuł!!!

Reply
Anthony Skotia - 15/01/2014

Witam,
Na pewno formuła „=Wyszukaj.Pionowo(A2;$E2:$F$20;2;FAŁSZ)” jest dobra, ja natomiast tego typu dopasowania (porządkowania danychy) na dużych zbiorach danych (kilkadziesiąt tysięcy pozycji) robię w skoroszycie excela używając np. czterech arkuszy, pierwszy jako „dane wynikowe”, kolejne dwa jako bazy danych wklejanych z zewnętrznych źródeł ostatni jako katalog tzw. indeksów. Bardzo wygodne jest w moim systemie to, że dane w arkuszach mogą być wpisane w sposób nieusystematyzowany, unikatowy musi być jedynie tzw. indeks. Od wielu lat korzystam z tego systemu przy inwentaryzacjach magazynowych – działa rewelacyjnie. Kluczem jest zdefiniowanie identycznych nagłówków kolumn we wszystkich arkuszach. No i najważniejsza jest formuła, która pozwala otrzymać uporządkowane dane – jest dość skomplikowane, ale działa rewelacyjnie:
=JEŻELI(B13>0;INDEKS(‚Ruch wg Google Analytycs’!$B$4:$D$10000; PODAJ.POZYCJĘ(B13;’Ruch wg Google Analytycs’!$B$4:$B$10000;); PODAJ.POZYCJĘ(D$11;’Ruch wg Google Analytycs’!$B$4:$D$4;));0)
i odpowiednia dla drugiej kolumny:
=JEŻELI(B13>0;INDEKS(‚Ruch wg Google’!$B$4:$D$10000; PODAJ.POZYCJĘ(B13;’Ruch wg Google’!$B$4:$B$10000;); PODAJ.POZYCJĘ(E$11;’Ruch wg Google’!$B$4:$D$4;));0)

Dla zainteresowanych chętnie udzielę szczegółowych informacji.

Reply

Leave a Reply:

0 Udostępnień