Alternative zu einer Matrixformel

[Halbwissender]

Hallo zusammen,

mit dieser Matrixformel

Code: Alles auswählen

{=WENN(ZEILE(B1)<=ZÄHLENWENN(Daten.B$1:B$9995;$B$3);INDEX($Daten.C$1:C$9995;KKLEINSTE(WENN($Daten.$B$1:$B$9995=$B$3;ZEILE($B$1:$B$10000));ZEILE(B1)));"Daten sind nicht bekannt")}

lasse ich mir Daten aus dem Tabellenblatt "Daten" anzeigen. Das funktioniert einwandfrei. Doch nun stelle ich fest, dass durch kontinuierliche Datenerweiterung (mittlerweile knapp 2000 Zeilen - sprich Datensätze) die Performance langam, aber stetig abnimmt. Momentan mag ich mir nicht vorstellen, wie das bei 10000 und mehr Datensätzen sein wird. Wahrscheinlich kann ich gemütlich eine Tasse Kaffee trinken, bis ich mein Ergebnis habe.

Nun meine Frage. Gibt es eine matrixfreie Lösung zum Auslesen der Daten? Ein normaler SVERWEIS geht nicht, da mir dieser nur den ersten gefundenen Datensatz anzeigen würde. Da es sich bei den Daten aber um Familiennamen handelt, müssen mir alle Namen nach dem eingegebenen Suchbegriff angezeigt werden (auch der 17. Müller oder 22. Meier).

Nach Möglichkeit sollte es eine makrofreie Lösung sein - unsere IT-ler gestatten den Einsatz von Makros nicht.

[clag]

Hallo Halbwissender,

du wunderst dich doch hoffentlich nicht wirklich darüber, das diese Formeln den PC in die Knie zwingen?
Wie wäre es stattdessen mit einem persistenten spezial Filter, der liefert garantiert die Daten erheblich schneller.

[echo]

Hallo
erste Wahl ist ein Filter

wenn ich das richtig sehe, dann wird im Blatt "Daten!B1:B$9995" nach dem Inhalt nach B3 gesucht.
Angenommen das andere Blatt heißt "Anzeige" (Suchbegriff im B3)

So etwas mache ich in zwei Schritten ohne Matrix:
1. Im Blatt "Daten" erzeuge ich eine Hilfsspalte -Angenommen in Spalte Z -
In Z1 zum runter kopieren
=WENN(B1=$Anzeige.$B$3;ZEILE();"")

2. Formel in "Anzeige" zum runter kopieren
=INDEX($Daten.$B$1:$B$1000;KKLEINSTE($Daten.$Z$1:$Z$1000;ZEILE(A1));0)
incl unterdrückung von Fehler
=WENN(ZEILE(A1)>ANZAHL(Daten.Z1:Z1000);"";INDEX($Daten.$B$1:$B$1000;KKLEINSTE($Daten.$Z$1:$Z$1000;ZEILE(A1));0))

Hinweis:
......$Z$1:$Z$1000;ZEILE(A1));0) beginnt unabhängig von der Position oben immer mit A1

Gruß Holger

[Halbwissender]

Hi clag,

du wunderst dich doch hoffentlich nicht wirklich darüber, das diese Formeln den PC in die Knie zwingen?

nein, mir war von Anfang an klar, dass eine Matrixformel ressourcenfressend ist. Allerdings bin ich vor 2 Jahren davon ausgegangen, dass sich pro Jahr nicht mehr als 100 Datensätze ansammeln - das war ein Irrtum, wie sich jetzt herausgestellt hat.

Wie dem auch sei, vielen Dank für deinen Tipp, den ich mir über die Feiertage genauer ansehe.

[Halbwissender]

Hi echo,

auch dir ganz herzlichen Dank für deine Hilfe. Wie ich schon clag schrieb, werde ich über die Feiertage die Tipps umsetzen.

[F3K Total]

Hi,
ich habe mal ein Beispiel mit persistentem Spezialfilter, nach dem Vorschlag von clag, erstellt. Damit kann man momentan nach bist zu 16 Kriterien filtern, acht Nachnamen, acht Vornamen.

• Es braucht einen Datenbereich: Daten/Bereich festlegen, ich habe $Tabelle1.$A$2:$B$10002 mit NAMEN benannt.
• Der Button filtern ist eine schnellere Alternative für Daten/Bereich aktualisieren, dazu habe ich die Aktion Dokument/Webseite öffnen eingestellt und als URL den Befehl

  Code: Alles auswählen

  .uno:DataAreaRefresh

  eingetragen.

Gruß R
EDIT: Ich habe die angehängte Datei noch einmal ausgetauscht, in der ersten Version ist mir ein nicht nötiges Makro mit hineingerutscht. Funktioniert ohne Makro.
EDIT2: Ein Filter-Versuch mit 240000 Zeilen und sechs Kriterien dauert bei meinem 2.2 GHz, 3 GB Rechner nur etwa 3 Sekunden, mit 1048576 Zeilen (dann ist das Blatt voll) etwa 12 Sekunden.

[clag]

Hallo

moin @ F3K Total
zuerst dachte ich du hättest ein Problem lösen können welches mich schon länger stört,
nämlich das manuelle "refreshen" der gefilterten Ausgabe eines resistenten spezial Filters.
Aber Dein "Refresh-Button" tut es nicht wirklich, entschuldige wenn ich jetzt "rum maule"

Um die Filter-Ausgabe zu refreshen muss der Cusor im oder wenigstens am Datenbereich platziert sein,
darum funktioniert der Refesh-Button nur wenn der Cursor in Spalte A,B oder C steht änderst du in Spalte D ein Kriterium
und klickst den Button tut sich nichts mehr und wenn du eine leere Spalte einfügst zwischen B und Kriterien geht gar nichts mehr.

Das betrifft leider nicht nur deinen Button sondern jeden Refreshversuch!
Also auch über Menü > Daten > aktualisieren das ist ausgegraut wenn der Cursor nicht in Datenbereich platziert ist,
ich hatte es auch mit einem HotKey versucht, aber das tut auch nicht, solange der Cursor nicht im Datenbereich steht.

Um es wie auch immer, "ein Klick komfortabel" zu machen muss man den Cursor in den Datenbereich positionieren,
und dann kommt man wohl um etwas Makrocode nicht drumherum.
Wenn man Datenbereich, Kriterien und Ausgabe auf verschiedenen Tabellenblättern hat wird es ohne Makro echt nervig.

@ Halbwissender
du solltest dich vielleicht doch mit etwas Code anfreunden, dann flutscht das mit dem Filtern.

[F3K Total]

Hallo Clag,

ja stimmt, wusste ich auch.
Schön, dass du jetzt so genau erklärt hast, wo der Cursor stehen muss, damit die Aktualisierung des Datenbankbereiches ausgeführt wird.
Zu maulen gibt es da nichts,

Code: Alles auswählen

.uno:DataAreaRefresh

ist auf alle Fälle ein Kniff, den man zeitsparend einsetzen kann, der für die häufigsten Fälle gut nutzbar sein sollte.
Bringt man auch die Filterkriterien in dem von dir beschriebenen Bereich unter, im Bild orange, steht der Cursor nach deren Änderung von selbst richtig.
Geht gut, solange man ein Filterkriterium pro Spalte hat.

SF.png (33.94 KiB) 3845 mal betrachtet

Ansonsten bleibt wirklich nur ein Makro.
Gruß R