Kleine Helfer: SVERWEIS mit mehreren Bedingungen

Für ein aktuelles Rechercheprojekt hatte ich eine umfangreiche CSV–Datei mit Keywords zur Analyse vorliegen.
Für jede Suchmaschinenoptimierung steht zu Beginn ein Keyword–Überblick. Selbst bei großen, nicht auf einzelne Keywords fokussierten Projekten ist es trotzdem extrem schwierig Verbesserungen im Projekt durchzuführen, wenn man keinen Überblick über das Keyword–Set hat. Außerdem ist es häufig ein Eye–opener im Marktforschungsbereich für den Kunden: „Ach soooo. So sprechen meine Kunden“. Keywordanalyse ist also Pflicht. Bei großen Projekten, oder Keywordsets mit einer breiten Streuung, oder Themengebieten, in denen ich mich bisher nicht so auskenne, verschaffe ich mir gern einen ersten Überblick.
Bei der Keywordrecherche gilt: Es ist nicht schwer viele Keywords zu finden. Man kann schnell Listen mit 500.000 Keywords und mehr zu bestimmten Themenbereichen erstellen, oder kriegt sie aus Tracking–Systemen, Adwords–Tools und ein paar Skripten schnell zusammen. Die Schwierigkeit ist die für das aktuelle Projekt relevanten Keywords zu finden.
In dem aktuellen Projekt hatten wir eine Liste mit 200.000+ Keywords vorliegen, die nach bestimmten Kriterien in ca. 100 Kategorien unterteilt waren. Wir wollten wissen, auf welchen Kategorien der Fokus liegt. Dafür brauchten wir je Kategorie die Top 3 Keywords nach Suchvolumen, um den Kunden entsprechend gewichten zu lassen (hohe Marge, wichtig für den Chef, Fokusbereich der Konkurrenz).
Wie kriegen wir also aus 200.000+ Keywords mit 100 Kategorien eine Datei mit den Top 3 Keywords in einem dem Kunden vertrauten Format?

Die Keyword–Ausgangsdaten

Die Ausgangstabelle lag so vor:

Wie gesagt: Ein paar Zeilen mehr, als in der Skizze.

Um einen Überblick zu gewinnen sollten die Keywords nach ihrem Suchvolumen bewertet werden. Das Problem dabei: Für jede Kategorie sind andere Keywords im Gesamtset. Die Schritte waren also:

• Schau welche Keywords alle zu einer Kategorie gehören,
• sortiere innerhalb dieser Kategorie nach Suchvolumen
• und merk Dir die drei Keywords, die das höchste Suchvolumen haben.

Das Zielformat der Datei

Das sollte dann ungefähr so aussehen:

Das ganze kann man in mehreren Schritten machen, oder per Hand, aber ich mag das nicht. Ich brauchte das wiederverwertbar und weil die Datei prinzipiell auf Basis anderer Daten auch für einen Kunden weiterverwertbar sein sollte war auch Datenbank leider keine Option. Und wenn wir davon ausgehen, dass die Datenbasis sich ändern kann, dann funktioniert auch vorher sortieren nicht.

Wie funktioniert also unsere Datei?

Hier also unsere kleine Formel für E2 (Davon ausgehend, dass wir in D2 unsere Kategorie stehen haben):
=INDEX(A:C;VERGLEICH(KGRÖSSTE(WENN(B:B=D2;C:C);1);C:C;0);1)

• KGRÖSSTE(WENN(A:A=D2;C:C);1) — Immer dann, wenn in A:A unser Keyword steht, dann übernimm die Werte aus C:C und ermittle mit KGRÖSSTE den höchsten Wert. Die 1 am Ende sagt KGRÖSSTE, dass wir den höchsten Wert haben wollen. 2 wäre er zweithöchste, 3 der dritthöchste, usw.
• Vergleich(KGRÖSSTE;C:C;0) — Gibt die relative Position des Werts innerhalb unserer Auswahlmatrix zurück
• Index(A:C;Vergleich;1) — Gibt den Wert zurück, der innerhalb der Matrix A:C in der Zeile, die wir mit Vergleich ermittelt haben und in Spalte 1 steht.

Wichtig: Wir spielen hier mit Matrix-Formeln. Daher müssen wir die Formeln mit STRG+SHIFT+ENTER bestätigen.

Ein Excel–Download

Das Ergebnis sollte dann so aussehen:
Download: Beispiel Keywordliste