Es gibt einige Excel Funktionen, die man im Alltag immer wieder braucht und da ich so langsam das ständige Suchen leid bin, nutze ich die Chance mal und werde einige (für mich und vielleicht auch für euch) hilfreiche Excel Funktionen und Formeln beschreiben.
Immer wieder möchte man in Excel die Anzahl bestimmter Zeichen innerhalb einer Zelle oder Spalte zählen, um weitere Erkenntnisse bei der Daten-Analyse zu gewinnen. Beispiele für solche Anwendungsfälle im Bereich SEO wären:
- Anzahl der Wortlängen innerhalb von Keyword-Reports bestimmen
- Anzahl der Verzeichnisse (Backslashes) URLs bestimmen
Genau diese zwei Anwendungsfälle möchte ich nachfolgend kurz ein wenig detaillierter beleuchten.
Anzahl der Wortlänge über das Zählen von Leerzeichen in Zellen bestimmen
Immer wieder möchte man bei der Analyse von Keyword-Sets die Wortlänge genauer betrachten, um ein Gefühl für die Wichtigkeit des Short- und des Longtail bei der zu analysierenden Seite zu bekommen. Mit Hilfe einer zusätzlichen Spalte in Excel und der folgenden Formel können auch sehr umfangreiche Keyword-Listen schnell hinsichtlich der Wortlängen untersucht werden.
=LÄNGE(A2)-LÄNGE(WECHSELN(KLEIN(A2);“ „;))+1
Nachfolgend möchte ich die Vorgehensweise kurz an einem Google Webmaster Tools Export der Suchanfragen aufzeigen:
- Export der Google Webmaster Tool Suchanfragen
- Einfügen einer neuen Spalte (bspw. B)
- Einfügen der Formel in die neuen Spalte (die Spalten-Referenz für den Suchbegriffs sollte, wenn diese nicht in Spalte A aufgeführt werden entsprechend angepasst werden)
- Bezug der Formel in Spalte B auf alle Datensätze durch kopieren herstellen.
- Nun wird in Spalte B zu jeden Suchbegriff in Spalte A die Wortlänge ausgegeben.
Soweit so gut. Um jetzt einen schnellen Überblick über die Wortlängen über das gesamte Keyword-Set zu erlangen, kann man die gesamte Tabelle markieren und über die Funktion „Pivot Chart“ ein Säulendiagramm erstellen, welche die Anzahl der unterschiedlichen Wortlängen innerhalb der Suchbegriffe gut visualisiert.
Somit wird sofort die Zusammensetzung des Keyword-Sets hinsichtlich der Wortlängen sehr schnell ersichtlich.
Wie erstellt man einen solchen Chart?
Haben wir die Formel zur Bestimmung der Wortlängen bspw. in Spalte B eingefügt, markieren wir die gesamte Tabelle durch das Klicken in den Datensatz und die Tastenkombination Strg & a. Anschließend wählt man im Funktionsband „Einfügen“ die Funktion „Pivot Chart“ aus.
Nun erstellt man ein Pivot Chart in einem neuen Tabellenblatt und kann zwischen folgenden Spalten wählen:
Wir geben die errechnete „Wortlänge“ als „Achsenfelder“ & „Werte“ an und bekommen als Ergebnis alle verfügbaren Wortlängen sowie deren Häufigkeit innerhalb des Datensatzes in einer Tabelle & als Säulendiagramm angezeigt.
Analyse der Verzeichnistiefe über das Zählen von Backslashs innerhalb einer URL & einer Zelle in Excel bestimmen
Bei der Analyse von Webseiten kann es durchaus interessant sein die Verzeichnis-Tiefe einer Webseite, also besser gesagt die Verzeichnis-Tiefen einer URL Liste genauer zu betrachten. Die Verknüpfung mit anderen Datensätzen kann dabei einige wichtige Erkenntnisse zu Tage fördern.
Ein möglicher Anwendungsfall wäre: Es steht zur Diskussion ob man die Verzeichnis-Tiefen eines Portals für eine bessere Crawlability zu reduzieren. Nun ist bekannt, dass eine Vielzahl der Links Deep-Links sind, d.h. tief in das Webangebot verweisen. Jetzt wäre es interessant zu wissen, welche Verzeichnis-Ebene wie viele Links führt, um die Effekte einer möglichen Reduktion der Verzeichnis-Tiefen auf das Backlink Profil besser einschätzen zu können. Wir wollen also versuchen über die Datenaufbereitung in Excel nachzuvollziehen, wie viele Links welche Verzeichniseben führt.
Als Basis für die nachfolgende Analyse wurde der Sistrix Linkreport (Expertenmodus) genutzt. Es ist aber auch denkbar Linkreports anderer Tools zu verwenden, um identische Analysen durchzuführen.
Nachdem wir den Export der Links vorgenommen haben, fügen wir, wie auch im vorherigen Beispiel eine zusätzliche Spalte (blau hinterlegt) ein, in der wir mit Hilfe der folgenden Funktion die Anzahl der Backslashes innerhalb der URLs zählen:
=WENN(LÄNGE(D2)-LÄNGE(WECHSELN(KLEIN(D2);“/“;))=0;LÄNGE(D2)-LÄNGE(WECHSELN(KLEIN(D2);“/“;));LÄNGE(D2)-LÄNGE(WECHSELN(KLEIN(D2);“/“;))-2)
Zu beachten ist, dass es sich bei Verzeichnis-Tiefen 0 und 1 um identische URLs handeln kann, die entweder mit oder ohne abschließenden Backslash angegeben werden.
Beispielsweise:
http://www.beispiel.de: Verzeichnis-Tiefe 0
http://www.beispiel.de/: Verzeichnis-Tiefe 1
In Fall der Analyse ist dies allerdings zu vernachlässigen, da wir ja den Anteil der Links auf tiefe Verzeichnis-Ebenen untersuchen wollen, um besser verstehen zu können, wie viele Links auf tiefe Verzeichnis-Ebenen (also bspw. >5) zeigen.
Haben wir die Verzeichnis-Tiefe aller in der Excel-Liste befindlichen URLs ermittelt, markieren wir wieder alle Inhalte mit einem Klick in die Datentabelle und die Tastenkombination „Strg & a“. Auch aus dieser Basis-Tabelle erstellen wir nun einen Pivot Chart, indem wir die Verzeichnis-Tiefe als „Zeilenbeschriftung“ und „to_url“ als „Werte“ angeben.
Als Ergebnis bekommen wir eine Tabelle und einen Chart, der die Anzahl der Links pro Verzeichnis-Ebene aufschlüsselt:
Um die Verhältnisse innerhalb der Analyse besser darzustellen, wurde noch eine Spalte eingezogen, in welcher der prozentuale Anteil der Links / Verzeichnis errechnet wird. Es lässt sich somit sehr gut nachvollziehen, dass nur knapp 3,40 % aller Links auf Verzeichnisse zeigen, die eine Verzeichnis-Tiefe >5 haben. Mit ergänzenden und detaillierteren Analysen könnte man die Qualität der Links auf tiefe Verzeichnis-Ebenen weiter untersuchen, um noch besser einschätzen zu können, welche Effekte bei einer Reduktion hoher Verzeichnis-Tiefen hinsichtlich des Backlink Profils zu erwarten sind.
Wie findet ihr die oben beschriebenen Analysen oder nutzt ihr noch weitere Analyse-Ansätze in Excel?! Ich freue mich über einen Kommentar von euch…
Weitere hilfreiche Hinweise zur Formel findet ihr hier: http://www.excelformeln.de/formeln.html?welcher=96