Ist es möglich, ein Array nach einem Teilstring zu filtern und mehrere Einträge mit einer einzigen Formel (ohne Autofilter, VBA oder zusätzliche Spalten) nur einmal aufzulisten? Ich habe beispielsweise die folgende Tabelle:
A | B | C | D | F
-----------------------------------
01| ID | Class | Value | Filter
02| 1 | A/as | V1 |
03| 1 | B/as | V2 |
04| 2 | A/ab | V3 |
05| 3 | B/ab | V4 |
06| 3 | B/as | V5 |
07| 2 | B/ab | V5 |
08| 1 | A/as | V5 |
09| 3 | A/ab | V5 |
Wenn ich die Spalte B filtere nachKlasse"A/*", aber der Wert wird nur einmal angezeigt. Das Ergebnis sollte sein:
A | B | C | D | F
-----------------------------------
01| ID | Class | Value | Filter
02| 1 | A/as | V1 | V1
03| 1 | B/as | V2 | V3
04| 2 | A/ab | V3 | V2
05| 3 | A/ab | V1 |
06| 3 | B/as | V4 |
07| 2 | B/ab | V5 |
08| 1 | A/as | V2 |
09| 3 | A/ab | V2 |
anstatt
A | B | C | D | F
-----------------------------------
01| ID | Class | Value | Filter
02| 1 | A/as | V1 | V1
03| 1 | B/as | V2 | V3
04| 2 | A/ab | V3 | V1
05| 3 | A/ab | V1 | V2
06| 3 | B/as | V4 | V2
07| 2 | B/ab | V5 |
08| 1 | A/as | V2 |
09| 3 | A/ab | V6 |
Filtern Sie die Spalte nach den Teilzeichenfolgen (angepasstDasBeschreibung) bis ungefähr so:
...
IF(
ISNUMBER(Search("A/*"; $B$2:$B$9))
...
das Ergebnis ist ein Array mit den Indizes der Liste $B$2:$B$9:
ISNUMBER(Search("A/*"; $B$2:$B$9): {1;0;1;1;0;0;1;1}
und so
IF(IS...): {1;3;4;8;9}
Bisher habe ich keine Möglichkeit gefunden, dies mit dem Ansatz „eindeutige Namensliste“ zu kombinieren
`MATCH(0;INDEX(COUNTIF(`
wie beschriebenHier
Das bisschen, das ich habe, ist, dass es aber nicht so gut funktioniert und eine ziemliche CPU-Last verursacht, z. B. Zelle C8
{=IFERROR(INDEX(
INDEX($D$2:$D$9;
IF(ISNUMBER(SEARCH("A/*";$C$2:$C$9));
ROW($D$2:$D$9)-ROW($D$2)+1)));
MATCH(0;
INDEX(COUNTIF($C$2:C7;
INDEX($D$2:$D$9;
IF(ISNUMBER(SEARCH("A/*";$C$2:$C$9));
ROW($D$2:$D$9)-ROW($D$2)+1)));
0;0);0));
"error")
Antwort1
Ich habe es gelöst, nicht perfekt (es braucht 3 Spalten), aber es funktioniert wunderbar.
A | B | C | D | E | F | G
-------------------------------------------------------
01| Array |Array | Text | search | search | ordered
02| Source|Source | sought| results | results |
03| #1 | #2 | *a* | #1 | #2 |
04| aa | c12 | | c12 | c12 | c02
05| ca | c13 | | c13 | c13 | c06
06| ad | c06 | | c06 | c06 | c12
07| ee | c11 | | c02 | c02 | c13
08| fa | c02 | | c06 | c25 | c25
09| gg | c12 | | c13 | |
10| ba | c06 | | c06 | |
11| aa | c13 | | c25 | |
12| ad | c06 | | #NUM! | |
13| gt | c12 | | #NUM! | |
14| aa | c25 | | #NUM! | |
Spalte EListet alle Elemente der Spalte C auf, wenn die entsprechende Zelle in Spalte B den Ausdruck D3 enthält. Formel in Zelle E4, die in E5-E14 kopiert wird:
{=INDEX(C:C;
SMALL(IF(ISNUMBER(SEARCH($D$3&"/*";
$B$4:$B$14));
ROW($B$4:$B$14));
ROWS($E$4:E4)))}
Sie müssen Strg-Umschalt-Eingabe drücken, um die Formel als Array einzugeben. Seien Sie jedoch vorsichtig, es kann ziemlich lange dauern, wenn eine große Tabelle durchsucht wird. Ich habe 1300 Zellen, das hat mehr als eine Minute gedauert, aber nur, um die Formel einzugeben. Das Kopieren in die anderen Zellen erfolgt ohne Verzögerung.
Hier kommt, was es tut:
- INDEX (arg1,arg2)gibt den Wert von Element/Zelle n aus (arg2) der Spalte C (arg1). N wird berechnet inKlein.
- KLEIN(arg1,arg2)soll den k-ten Wert zurückgeben (arg2) kleinster Wert in einem Datensatz (arg1).
Diese Funktion gibt Werte mit einer bestimmten relativen Position in einem Datensatz zurück. Das ist genau das, was für die korrekte Verwendung vonWENNUndREIHEN, tiefer verschachtelt. WENN (Logik Test,Wert, wenn wahr)ist der Hauptteil des Tricks: Es erstellt ein Array der Zeilennummern, bei denen die WENN-Bedingung erfüllt ist (beachten Sie, dass dieWENNhat kein 'anders' Wert, es wird nurFALSCHwenn die Bedingung nicht erfüllt ist)
- Logik Test:ISTZAHL(SUCHE($D$3&"/*"; $B$4:$B$14))Gibt ein Array von True und False zurück, je nachdem, ob SEARCH für jede Zelle im Bereich $B$4:$B$15 einen numerischen Wert ergibt oder nicht.
Das Ergebnis für das obere Beispiel lautet:- SUCHEN:1, 2, 1, #WERT, 2, #WERT, 2, 1, 1, #WERT, 1
- IST NUMMER:WAHR, WAHR, WAHR, FALSCH, WAHR, FALSCH, WAHR, WAHR, WAHR, FALSCH, WAHR
- Wert wenn wahr: ROW($B$4:$B$14)) gibt ein Array zurück, das mit den Zeilennummern des Arrays $B$4:$B$14 gefüllt ist.
Das Ergebnis für das obere Beispiel lautet: 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14
WENNwerden kombinierenLogik Test#1 undWert, wenn wahr#2. Alle Werte in #1 werden ignoriert, wenn #2 an derselben Position innerhalb des Arrays #2 FALSE anzeigt. Schließlich haben wir ein Array, das angibt, in welcher Zeile der Spalte B innerhalb des in angegebenen BereichsREIHE(...)der Ausdruck von D3 wird gefunden. Das Ergebnis für das obere Beispiel lautet: 4,5,6,8,10,11,12,14
- Logik Test:ISTZAHL(SUCHE($D$3&"/*"; $B$4:$B$14))Gibt ein Array von True und False zurück, je nachdem, ob SEARCH für jede Zelle im Bereich $B$4:$B$15 einen numerischen Wert ergibt oder nicht.
ZEILEN($E$4:E4)ist nur ein Trick, der Ihnen eine inkrementelle Zahl gibt (also 1 in F2, 2 in F3...). Was wird verwendet inKLEINalsarg2Das Ergebnis in der ersten Zelle (REIHEN(...)=1) ergibt 4 (niedrigster Wert), in der zweiten 5 usw. Am Ende zeigt jede folgende Zelle die Zeilennummer/Position in Spalte B, wo der Ausdruck von D3 gefunden wird.
Spalte Ffiltert Duplikate, was der schwierigste Teil war. Spalte F listet alle in Spalte E aufgeführten Elemente „nur“ einmal auf.
Das ist die Formel, die in F5 eingegeben wird! (F4 ist dasselbe wie in E4) mit Strg-Umschalt-Eingabe:
{=IFERROR(INDEX($C$2:$C$14;
MATCH(0;
COUNTIF($E$4:E4;
$C$2:$C$14);
0));
"")}
Hier kommt, was es tut:
- ZÄHLENWENN(arg1,arg2)ergibt ein Array der Länge des Bereichsarg1, wobei durch 1 angegeben wird, wobei eine Übereinstimmung mit den Einträgen inarg2.
Das Ergebnis für das obere Beispiel ist: 1, 1, 0, 0, 0, 1, 0, 0, 0, 0, 0 - Übereinstimmen(Lookup-Wert,Anordnung,Übereinstimmungstyp)sucht nach dem ersten Vorkommen von 0 im resultierenden Array vonZÄHLENWENN(...)was ist die 3. Position im oberen Beispiel
- Lookup-Wert:0, erster neuer Wert/kein Duplikat
- Anordnung:resultierendes Array vonZÄHLENWENN(...)
- Übereinstimmungstyp:0 = genau
- INDEX(arg2von ZÄHLENWENN, Match(...))zeigt schließlich den neuen/nicht duplizierten Wert an, der sich an der 3. Stelle des Bereichs befindetarg2, was ist c06 im oberen Beispiel.
Spalte GAbschließend werden alle Einträge alphabetisch sortiert. Dies ist die Formel, die in G4 mit Strg-Umschalt-Eingabe eingegeben wird:
{=IFERROR(INDEX($F$4:$F$14;
MATCH(ROWS($G$4:$G4);
COUNTIF($F$4:$F$14;
"<="&$F$4:$F$14);
0));
"")}
Hier kommt, was es tut:
- ZÄHLENWENN(arg1,arg2)ist der Hauptteil des Tricks: Er vergleicht die Textwerte inarg2mit allen anderen Textwerten inarg1und gibt seinen relativen Rang (alphabetische Reihenfolge) zurück.
Das Ergebnis für das obere Beispiel ist:
3, 4, 2, 1, 5 - ZEILEN($E$2:E2)ist nur ein Trick, der Ihnen eine inkrementelle Zahl gibt (also 1 in G2, 2 in G3...). Was wird verwendet inÜbereinstimmenalsLookup-Wert.
- Übereinstimmen(Lookup-Wert,Anordnung,Übereinstimmungstyp)sucht nach dem ersten Vorkommen vonREIHEN(...)im resultierenden Array vonZÄHLENWENN(...)
Das Ergebnis für das obere Beispiel lautet: 4
, 3, 1, 2, 5- Zelle:REIHEN(...)=1 => 4
- Zelle:REIHEN(...)=2 => 3
- Zelle:REIHEN(...)=3 => 1
- Zelle:REIHEN(...)=4 => 2
- Zelle:REIHEN(...)=5 => 5
- INDEX(arg1 von ZÄHLENWENN, Match(...))zeigt schließlich den sortierten Eintrag entsprechend seinerREIHEN(...)Ergebnis. Das Ergebnis für das obere Beispiel ist:
- Zelle:REIHEN(...)=1 => 4 inZÄHLENWENNArray => c02
- Zelle:REIHEN(...)=2 => 3 inZÄHLENWENNArray => c06
- Zelle:REIHEN(...)=3 => 1 inZÄHLENWENNArray => c12
- Zelle:REIHEN(...)=4 => 2 inZÄHLENWENNArray => c13
- Zelle:REIHEN(...)=5 => 5 inZÄHLENWENNArray => c25
So weit, so gut. Der letzte Schritt wäre, alles in einer Spalte zu kombinieren. Zumindest habe ich einige gefundenhelfenZusammenführen der Spalten F und G (aber ich werde es heute nicht tun).