Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1512to1516
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Unike Werte aus gefilterter Liste extrahieren

Unike Werte aus gefilterter Liste extrahieren
08.09.2016 10:19:45
Marie
Hallo zusammen,
kann mir bei Euch jemand helfen? Ich habe es bereits versucht, eine Antwort zu finden, aber bisher noch keinen hilfreichen Input erhalten...
Und zwar möchte ich aus einer gefilterten Liste über eine Formel (nicht VBA, nicht weiterer Filter, keine Pivot, ...) alle in einer bestimmten Spalte vorkommenden Einträge ohne Duplikate oder Leerzellen wiedergeben. Die Datei hat ca. 50.000 Datenzeilen, einige Berechnungen und Diagramme, die sich dynamisch anpassen sollen.
Die Formel zur Bildung der Liste, die die Filterung bisland NICHT händeln kann, lautet:
{=INDEX($K$22:$K$266,MATCH(0,COUNTIF($K$1:K1,$K$22:$K$266),0),1)} .
Die funktioniert insgesamt, aber ich weiß nicht, wie ich den Filter berücksichtigt bekomme.
Vielen Dank für Eure Hilfe!
Marie
PS: Diese habe ich auch probiert, aber sie funktioniert nicht:
{=INDEX($K$22:$K$265,MATCH(0,INDEX(-ISBLANK($K$22:$K$265),0),0))}

9
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: nachgefragt ...
08.09.2016 11:26:41
...
Hallo Marie,
... Du meinst mit gefilterter Liste eine Datenliste in Spalte K die mit dem Autofilter nach einem anderen Kriterium in einer anderen Spalte temp. entsteht oder?
Du schreibst dass die Excelversion ohne Relevanz sein soll. Also soll die Auswertung auch für ältere Excelversionen funktionieren? Oder kannst Du die älteste Version genauer spezifizieren?
Die Auswertung soll gemäß Deiner Formel aber nur für einen beschränkten Bereich vorgenommen werden? Oder für 50.000 Datensätze?
Gruß Werner
.. , - ...
AW: nachgefragt ...
08.09.2016 11:46:29
Marie
Hallo Werner,
danke für Deine Nachfragen - hier die Konkretisierung:
- Mit gefilterter Liste meine ich, dass ich einen Gesamtdatensatz habe (ca. 50.000 Zeilen x 20 Spalten), mit dem verschiedene Nutzer arbeiten. Da jeder nur an den für ihn persönlich relevanten Daten interessiert ist, schneidet jeder Nutzer sich den Datensatz über die Anwendung verschiedener Filter in den 20 Spalten zusammen (ca. 5-8 Filter). Dieser gefilterte Datensatz hat in der Spalte K dann unterschiedliche Texteinträge, die ich einer dynamischen Liste "uniker Werte" benötige, um auf der Basis dynamische Diagramme erstellen zu können.(Nicht sicher, ob Du das gemeint hast.)
- Für die Entwicklung habe ich meinen Testdatensatz klein gehalten, daher geht der beschränkte Bereich hier nur bis Zeile 266. Real werden ca. 50.000 Zeilen enthalten sein. Diese Auswertung läuft auf eine Spalte.
- Ich nutze Excel 2016 (war als Auswahl nicht verfügbar). Wenn es in 2010 auch liefe, wäre es gut.
Ich hoffe, jetzt ist es klarer geworden.
Danke für Deine Mühe!
Marie
Anzeige
AW: mit AGGREGAT() und einer Hilfspalte und ...
08.09.2016 12:48:07
...
Hallo Marie,
... einen "Rechen-Schalter", könntest Du Dich mal heranranpirschen, denn Dein PC wird wohl etwas ins schwitzen kommen, aber nicht wegen der heute noch zu erwartenden Hitze draußen ;-)
Zunächst in einer freien Spalte (ich hab mal Spalte H angenommen) schreibe folge einfache Formel in H22: =WENN(H$1="List"; TEILERGEBNIS(103;K22);"") und kopiere diese bis zu Deinem letzten auszuwertenden Datensatz.
In Deinem Ergebnisspaltenbereich (am sinnvollsten wohl in einem anderen Tabellenblatt wenn mehr als 20 Ergebnisdaten zu erwarten sind, oder?)
Zumindest muss Ergebnisspaltenbereich außerhalb des gefilterten Bereichs liegen, was ich der Einfachheit nachfolgend mal angenommen habe für I1:I21 und schreibe folgende Formel in I2(!) wie folgt:
=WENN(H$1="List";WENNFEHLER(INDEX(K:K;AGGREGAT(15;6;ZEILE(H$22:K$500)/(ZÄHLENWENN(I$1:I1;K$22:K$500)=0)/(H$22:H$500);1));"");"") und kopierst diese Formel weit genug aber - nur soweit als notwendig nach unten.
Achtung: I1 muss leer sein oder einen Text enthalten, der in K2:K50000 nicht vorkommt.
Soweit so gut, bis hierher tut sich noch gar nichts, weil der "Rechenschalter" noch "ausgeknipst" ist.
Erst wenn Du in H1 List schreibst, beginnt die Auswertung.
In der Formel habe ich den Auswertungsbereich bewusst zunächst auf 500 begrenzt. Da dürfte Dein PC noch müde lächeln. Munter, wenn nicht gar verschreckt ;-) wird er aber sicherlich, wenn Du den Bereich in der Formel auf die erforderliche 50000 erhöhst. Geh es aber vielleicht nicht sofort an.
Teste zunächst mal den Zwischenschritt 5000 ... 25000 und dann ...
Bin selbst gespannt, ob und wie lange die Auswertung derartig vieler Datensätze mit Formel dauert.
In H1 kannst Du ja eine Dropdownzelle mit "List" einrichten und den Wert einfach wieder löschen wenn Du Deinem PC wieder mehr "Luft" geben willst.
Gruß Werner
.. , - ...
Anzeige
AW: mit AGGREGAT() und einer Hilfspalte und ...
08.09.2016 15:27:24
Marie
Werner,
sauber, es läuft!
Habs zwar auch erst nur im kleinen Datenset, aber immerhin :)
Ganz vielen Dank für Deine Mühe!!
Marie
AW: mit AGGREGAT() und einer Hilfspalte und ...
08.09.2016 16:00:15
Marie
Werner,
noch eine Frage: Muss die Rechnung über "K:K" laufen oder könnte durch Einschränkung auf gewisse Zeilen - falls möglich - an Performance gewonnen werden?
Danke
AW: auf jeden Fall den Bereich einschränken ...
08.09.2016 17:19:55
...
Hallo Marie,
... das spart sicherlich nochmal enorm an Rechenzeit. Denn K:K sind etwas über 1 Mio Zellen und damit 20x mehr als 50.000.
Bitte schreibe mir, wie lange der PC gerechnet hat für wie viele Unikate.
Gruß Werner
.. , - ...
AW: auf jeden Fall den Bereich einschränken ...
13.09.2016 10:14:12
Marie
Hallo Werner,
sorry, dass ich mich jetzt erst melde. Musste wg. eines traurigen familiären Falls übers Wochenende + Montag weg vom Rechner, aber heute gehts weiter. Die Daten befinden sich z.Z. allerdings auf einem Server, so dass ich im Moment noch eine keine Info wg. der Rechenzeit bei lokaler Verarbeitung nennen kann. Vielleicht passen wir das heute noch an.
Die Anpassung bzgl. K:K hatte ich letzte Woche bis zu meinem abrupten Aufbruch noch angefangen, das lief aber nicht. Gibts da eine Besonderheit wg. der Zeilenangaben o.ä. zu beachten?
Danke!
Anzeige
AW: dazu nachgefragt ...
13.09.2016 11:46:02
...
Hallo Marie,
... welcher Bereich soll denn jetzt ausgewertet werden? Wie sieht denn Deine angepasste Formel jetzt aus? Was genau meinst Du mit "das lief aber nicht"?
Gruß Werner
.. , - ...
AW: dazu nachgefragt ...
15.09.2016 18:13:19
Marie
Oh man,
was eine Zeit... So, bin nun wieder an dem Rechner, mit dem ich auf die Daten zugreifen kann.
Meine Formel lautet:
=IF(K$1="Start",IFERROR(INDEX(J:J,AGGREGATE(15,6,ROW(R$22:R$266)/(COUNTIF(J$1:J1,J$22:J$266)=0) /(R$22:R$266),1)),""),"")
und ich bin nicht sicher, ob "J:J" auf die Zeilen 1-20 oder 22-266 oder 1-266 anzupassen ist.
(Erklärung:
in 1-20 steht die gewünscht Kurzliste,
in 22-266 ein kleiner Datenausschnitt, der später 22-40000 lauten wird.)
Die Rechenzeit gebe ich hier preis, sobald ich Access auf den kompletten Datensatz habe.
Beste Grüße & Danke!
Anzeige

313 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige