Clever-Excel-Forum

Hallo zusammen,

mal abseits der Sinnhaftigkeit der Tabelle habe ich ein echt komisches Problem mit der =(INDEX Formel und komme nicht auf die Lösung.

In einer Tabelle funktioniert die Formel problemlos, in einer anderen spuckt sie aus irgendeinem Grund falsche Ergebnisse aus.

Es geht mir darum, dass eine lange Liste von Keywords, die mit Suchvolumen pro Monat, Position und entsprechendem Traffic-Potenzial eingeordnet werden, automatisch auf Basis ihres Suchvolumens und der Position des jeweiligen Keywords bei Google analysiert wird. So sollen automatisiert Listen von Begriffen erstellt werden, nach absteigendem Suchvolumen sortiert, um sozusagen die wichtigsten Keywords auf Position 1-10, 11-20, 21-30, 31-40 usw. aufzulisten, indem ich einfach nur eine beliebig lange Keywordliste importiere. 

Mir ist klar, dass das Ganze auch manuell mit Filtern geht, aber gerade bei sehr langen Keywordlisten (20k< Begriffe) geht das so sehr viel schneller, wenn die Formel einmal funktioniert. 

In den angehängten Excel-Dateien funktioniert die Variante 1 so wie sie soll und Variante 2 nicht.

Im ersten Datenblatt Spalte A:H werden die Daten eingetragen und ab Spalte I erfolgt die Auswertung. 

Kann jemand das Problem lösen?

Grüße

---

Bin dem Problem schon etwas auf die Spur gekommen.

Es liegt an der Anzahl der Zeilen. Habe bis jetzt keine Liste importiert, die mehr als 10k Keywords hatte.

Sobald man eine Zeilenzahl von <10k erreicht, funktioniert die Formel wie gewünscht.

Könnt ihr mir sagen wie ich die Formel anpassen muss, damit es auch mit mehr als 10k Zeilen funktioniert?

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

Grüße

Hi,

einfach mal ins Blaue geraten: das %% in deiner Formel Ist im Grunde das Gleiche wie /100/100 oder anders gesagt /10.000. Wenn du ein weiteres % anhängst, dann sollte die Formel bis 1.000.000 Zeilen funktionieren.

Hi,

auch ins Blaue:


ist bei 10000 auch verdächtig.

CU
Oberon

Hi,

für H würde auch das reichen:


Außerdem fände ich das für Deine INDEX-Formel besser:

Moin zusammen,

erstmal Danke für die Antworten. 

Hat leider bisher nicht ganz funktioniert.

Wenn ich ein weiteres "%" anhänge, dann kriege ich immer die Fehlermeldung "ÜBERLAUF".

Und bei der neu vorgeschlagenen Indexformel 




Wird das Problem leider auch nicht gelöst. Die ersten drei Ergebnisse sind richtig, aber dann beginnt der Fehler erneut und es werden nicht mehr nur Keywords der angegebenen Seitenzahl in Spalte H berücksichtigt, z.B. "Seite 3", sondern es tauchen auch wieder Seite 1, 2 usw. auf. Habe die Excel mal mit jeweils einer angepassten Variante nochmal hochgeladen.

• In Spalte I, von Zeile 8 bis Zeile 66 findet ihr meine ursprüngliche Formel, die bei einer Auswertung bis 9.999 Zeilen gut funktioniert.
  

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

• Ab Spalte I, von Zeile 70 bis Zeile 111 findet ihr den alternativen Indexformel-Vorschlag von BokosBiati
  

• In Spalte I, von Zeile 115 bis 156 findet ihr den Indexformel-Vorschlag mit dem zusätzlichen "%" von HKindler.
  

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


Habt ihr noch irgendeine Idee? Treibt mich langsam in den Wahnsinn diese Tabelle^^

Aber Danke euch schonmal für eure bisherige Mühe :)

Grüße

Arne

Hallo Arne,

wenn du mit drei % die Zeilenzahl durch 100^3 teilst, must du den Rest auch mit 100^3 = 10^6 multiplizieren ( anstatt 10^4). Ansonsten ist der Parameter Zeile in der Index-Funktion nicht ganzzahlig (und auch mal kleiner als 1 was zum Überlauf führt).

ps.
Wenn dich eine zusätzliche Spalte vor deiner Auswertung nicht stört, würde ich dort die Zeilennummer eintragen (alte Formel ohne Index).
Dann kanst du deine gesuchten Informationen direkt über Index ermitteln und Excel muss nicht über Sverweis  im Mittel tausende von Zellen lesen.

pps.
Die Formel ist jetzt nur noch für Suchvolumen bis 10^9 möglich, da die Zahlen in Excel nur 15 signifikante Stellen haben.

Hi,

was wäre denn z.B. für Zelle I117 das gewünschte Ergebnis?

CU
Oberon

Moin

Ich habe im anderen Thread bereits auf Pivot und PQ hingewiesen. Keine Ahnung warum man nach Monaten immer noch mit irgendwelchen Formeln wurschtelt.