Clever-Excel-Forum

Moin zusammen,

ich dachte immer, dass ich mit Excel zumindest schon fortgeschritten bin.

Doch meine aktuelle Arbeitsmappe bringt mich zur Verzweiflung.

Ich arbeite als SEO und möchte riesige Keyword-Sets automatisiert auswerten. Einen Großteil der Mappe konnte ich auch fertigstellen, aber eine scheinbar simple Formel bringt mich gerade zum verzweifeln.

Es geht darum, dass ich gerne eine KGRÖSSTE-Auswertung machen möchte, aber nur unter bestimmten Voraussetzungen.

Und zwar möchte ich für das Kriterium Seite 1, Seite 2, Seite 3, Seite 4, Seite 5, Seite 6, Seite 7, Seite 8, Seite 9 und Seite 10 jeweils das Keyword mit dem größten monatlichen Suchvolumen ermitteln und dieses dann via INDEX anzeigen lassen. Die Verbindung der INDEX, WENN und KGRÖSSTE habe ich hinbekommen, sodass mir jeweils das Keyword mit dem höchsten Suchvolumen angezeigt wird und zwar jeweils für Seite 1, Seite 2 usw. (Sinn der Funktion ist, dass ich später nur durch den simplen Import von Keyword und Position eines Kunden (also einer Domain / URL) sofort die wichtigsten Begriffe erkennen kann, zu denen diese URL oder Domain derzeit auf Seite 1, Seite 2, Seite 3 usw. rankt). 

Jedoch steckt der Teufel hier scheinbar wieder im Detail:

=INDEX(A:A;VERGLEICH(KGRÖSSTE(WENN(I:I="Seite 1";C:C);1);C:C;0);1)

Mit dieser Formel funktioniert es, dass die Tabelle nach der Größe des Suchvolumens durchsucht wird und mir statt des Wertes "Suchvolumen", der in Spalte C hinterlegt ist, das dazugehörige Keyword angezeigt wird, welches in Spalte A hinterlegt ist. Zudem werden nur die Keywords berücksichtigt, die in Spalte I die Kategorie "Seite 1" enthalten. 

Soweit so gut. 

Möchte ich dasselbe nun aber für "Seite 2" Keywords machen, kommt es zu einem seltsamen Fehler. Und zwar werden nun plötzlich trotzdem Keywords der Kategorie "Seite 1" hinzugefügt. Datenfehler habe ich bereits ausschließen können. 

Vermutlich ist das auf den häufigen Umstand bei Problemen mit KGRÖSSTE zurückzuführen, dass mehrere Werte in Spalte C die gleiche Größe enthalten. Nun hätte ich hier aber durch die zusätzliche Kategorisierung mit "Seite 2", "Seite 3" usw. erhofft, dass dieses Problem vermieden werden kann. 

Zudem weiß ich nicht, wie ich diesen Zusatz +ZEILE etc. einbinde, der den doppelten Werten vorbeugt, indem die jeweilige Zeile dem Wert für die KGRÖSSTE-Sortierung hinzugefügt wird. Habe es mal wie folgt versucht:

=KGRÖSSTE(WENN(H:H="Seite 1";C:C+ZEILE(C:C)/10^6);ZEILE(A1))

Aber das funktioniert auch nicht. Zwar wird mir ein Wert ausgegeben, aber hier bekomme ich es nicht hin, den INDEX(VERGLEICH richtig hinzuzufügen. Sprich: Es wird mir zwar Suchvolumen aus Spalte C ausgegeben, aber ich kann nicht überprüfen, zu welchem Keyword dieses gehört, da ich beim Einfügen der =INDEX(A:A;VERGLEICH Formel Fehlermeldungen bekomme. Ich hätte gedacht, dass es wie folgt funktioniert:

=INDEX(A:A;VERGLEICH(KGRÖSSTE(WENN(H:H="Seite 1";C:C+ZEILE(C:C)/10^6);ZEILE(A1))

Aber das war leider nicht von Erfolg gekrönt. Falls jemand eine Idee hat, was ich falsch mache, oder ob ich zu kompliziert denke und es einen viel einfacheren Weg gibt, dann meldet euch gerne. 

Das Ziel ist letztendlich, dass ich automatisiert jeweils das Keyword mit dem größten Suchvolumen auf Seite 1, Seite 2, Seite 3, Seite 4 usw. erhalte und dieses bei Bedarf weiterspinnen kann, also z.B. jeweils die fünf Keywords mit dem größten Suchvolumen pro Seite usw.

Die Kategorien "Seite 1", "Seite 2" usw. in Spalte I sind übrigens bereits ein Hilfswert. Eleganter wäre es, mit weiteren WENN-Funktionen zu arbeiten.

Position 1-10 = Seite 1 (ergo >0 <11=Seite 1)
Position 11 - 20 = Seite 2
Position 21 - 30 = Seite 3 
Position 31 - 40 = Seite 4
usw. 

Aber das auch noch zu verschachteln fällt mir sehr schwer. 

Ich habe die Datei einfach mal angehängt. Wenn ihr Fragen habt oder euch weitere Infos fehlen, um mir weiterzuhelfen, dann schreibt hier einfach rein. Ich melde mich dann dazu. 

Hoffe, dass mir jemand helfen kann. 

Grüße

Hallo,

da ich nicht weiß, was Du bezweckst, hast Du schon mal daran gedacht, die Tabelle entsprechend zu sortieren, sodass die Werte mit den größten Abfragen oben stehen?
[attachment=43121]

Moin,

nett gemeint ;)

Klar, kann ich die Werte mit Filtern rausbekommen.

Das Problem ist, dass es teilweise Listen mit mehreren zehntausend Worten sind.

Und ich möchte eventuell auch Listen für mehrere Kunden in einer Arbeitsmappe speichern und dann auf einer Art Dashboard automatisiert die Top Keywords für verschiedene Kriterien sammeln, ohne hin und her filtern und per Copy + Paste wieder neue Listen anlegen zu müssen.

Also ja, es ist richtig, dass ich die Werte durch Filtern ebenso herausbekommen kann.

Aber angesichts der Nutzbarkeit im Alltag wäre es super, wenn eine Formel diese Aufgabe übernimmt und z.B. über ein Diagramm dann automatisch und übersichtlich in einem separaten Arbeitsblatt auflistet. Das wird in der Beispieldatei nicht ganz klar, weil die Auflistung dort zu Übungszwecken direkt neben der eigentlichen Tabelle stattfindet. Das wird später etwas anders aussehen und funktionieren. Deshalb mag es so aussehen, als wäre es unnötig kompliziert für einen Wert, den ich auch über Sortieren oder Filtern ermitteln kann. 

Ich möchte die Formel dann später eben auch auf andere Kriterien anwenden, also z.B. nicht "Seite 1", "Seite 2" etc., sondern vllt. zusätzlich kombiniert mit der Suchintention (Do, Know, Go usw.) oder mit einem manuell den Begriffen zugeordneten Überthema.

Hallo,

versuche es mal mit dieser Formel...

Moin

Ein (reduziertes) Ergebnis geht mit Pivot.

Arbeitsblatt mit dem Namen 'Tabelle3'
	A	B
3	Keyword	Summe von Suchvolumen
4	parkinson symptome	20200
5	epileptische anfälle	18100
6	morbus parkinson	7400
7	epileptiker symptome	5400
8	epilepsie symptome	5150
9	rolando-epilepsie	4400
10	orfiril	3850
11	atosil tropfen	3650
12	rolando epilepsie	3200
13	neurocil	3150
14	symptome parkinson	2950
15	parkinson-symptome	2950

Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016

Diese Tabelle wurde mit Tab2Html (v2.6.2) erstellt. ©Gerd alias Bamberg

Alternativ geht auch bestimmt was mit PQ.

Hi,

so geht´s:

[attachment=43123]

Ich würde mal die Grundlagen bewerten

z.B.
In E2, Statt:


Geht auch:


In H1:
Statt

reicht schon

In I10:

Moin Leute,

Danke euch für die vielen Antworten.

Ich werde jetzt mal fleißig testen :) und habe auch schon durch die Antworten einiges gelernt.

Cool, dass ihr auch einem Neuling so fix weiterhelft.

Ich melde mich nochmal mit dem fertigen Ergebnis.

Grüße

Moin,

ich habe jetzt einige Zeit gut mit der Formel arbeiten können, die mir hier von @BoskoBiati am 01.05.2022 vorgeschlagen wurde, im angehängten Dokument Desitin Testing_VZ.xlsx.

Zur Erinnerung, es handelt sich um diese Formel.

=INDEX($A:$A;RUNDEN(REST(AGGREGAT(14;6;($C:$C+ZEILE($A:$A)%%)/($H:$H="Seite 1");ZEILE($A1));1)*10^4;0))

Mit ihr stoße ich allerdings an eine Grenze, an der ich seit einer Woche herum bastele und nicht auf die Lösung komme. Wenn die auszuwertende Tabelle mehr als 10.000 Zeilen hat, funktioniert die Formel plötzlich nicht mehr richtig. Ich habe ein entsprechendes Dokument mal angehängt.

Ab Zeile 72 Spalte I funktioniert die Auswertung plötzlich nicht mehr, obwohl die Formel korrekt übertragen wurde. Aber weder werden die Begriffe nun noch absteigend nach Suchvolumen über die Formel ausgewählt, noch stimmt die Zuordnung der Position. Obwohl also ausschließlich Keywords mit "Seite 3" in Spalte H berücksichtigt werden sollen, tauchen plötzlich einige mit "Seite 1" oder "Seite 2" auf, außerdem werden Begriffe doppelt und dreifach durch die Formel ausgespuckt und auch nicht mehr nach Suchvolumen sortiert.

Ich habe bereits rausgefunden, dass die Formel wieder funktioniert, sobald weniger als 10.000 Keywords, also weniger als 10.000 Zeilen ausgewertet werden müssen. Nur ist das eben im Praxisalltag nicht der Fall. Oft sind die Listen deutlich länger. Was muss ich tun, um die Formel oben entsprechend anzupassen, sodass auch mehr als 10.000 Zeilen ausgewertet werden können? Habe die Datei mal angehängt. 

Die Auswertung der Begriffe auf Seite 1 (Position 1-10) und Seite 2 (Position 11-20) funktioniert noch korrekt, doch ab Seite 3 (Position 21-30) entsteht nur noch Chaos :/

Bin wirklich dankbar für eure Hilfe.

Grüße

Arne

Hallo Arne,

da ich nicht weis ob du beide Themen verfolgst.

=INDEX($A:$A;RUNDEN(REST(AGGREGAT(14;6;($C:$C+ZEILE($A:$A)/10^6)/($H:$H="Seite 1");ZEILE($A1));1)*10^6;0))