Ich habe versucht, verschiedene Funktionskombinationen zu verwenden, aber es scheint, dass es keine spezielle Funktion gibt, die nicht gesuchte/übereinstimmende Werte zurückgeben kann. Es manuell zu machen, dauert Tage, da ich eine große Datenmenge durchsehen muss.
Ich möchte, dass MS Excel 2003 den Rest von Liste A basierend auf Liste B extrahiert.
*Liste A umfasst 2000 Elemente, Liste B nur maximal 10-30
Liste A
Nr.1----1 2 3 4 5 6 (jede Ziffer steht in 1 Zelle, immer 6 Ziffern)
Nr.2----1 1 2 3 4 5 (jede Ziffer steht in 1 Zelle, immer 6 Ziffern)
Nr.3----1 3 4 5 6 7 (jede Ziffer steht in 1 Zelle, immer 6 Ziffern)
Liste B
Nr. 1 – 1 2 3 (jede Ziffer steht in einer Zelle, immer 3 Ziffern)
Nr. 2 – 1 1 4 (jede Ziffer steht in einer Zelle, immer 3 Ziffern)
Nr. 3 – 2 3 5 (jede Ziffer steht in einer Zelle, immer 3 Ziffern)
Zum Beispiel:
Suchen Sie in Liste A nach Übereinstimmungen (sofern vorhanden) basierend auf den Eingaben aus Liste B und geben Sie den Rest als Ausgabe zurück. Wenn keine Übereinstimmung gefunden wird, ist keine Ausgabe erforderlich.
Liste A
Nr. 1 – 1 2 3 4 5 6 (jede Ziffer steht in einer Zelle, immer 6 Ziffern)
Basierend auf Liste B
Nr.1----1 2 3 (Übereinstimmung gefunden (1&2&3 ist vorhanden), dann wähle ich den Rest manuell aus # # # 4 5 6 oder = 456)
Nr.2----1 1 4 (keine Übereinstimmung gefunden (1&1&4 ist nicht vorhanden), keine Ausgabe)
Nr.3----2 3 5 (Übereinstimmung gefunden (2&3&5 ist vorhanden), dann wähle ich 1 # # 4 # 6 oder gebe aus= 146)
Ich habe mir gedacht, dass ich die Funktion COUNT verwende, um die Häufigkeit jeder Ziffer von 0 bis 9 in jedem Element in Liste A zu zählen und dann die Funktion IF & AND verwende (die angibt, welche und wie viele jeder Ziffer erforderlich sind, um als Übereinstimmung zu gelten), um mir mitzuteilen, welche Elemente in Liste B mit Liste A übereinstimmen.
Für jedes Element in Liste A muss Excel also die gesamte Liste B durchgehen. Die Ausgabe kann von keiner Ausgabe bis zu maximal 3 Ausgaben reichen.
Ich möchte außerdem die Möglichkeit haben, die Werte in Liste B zu ändern, ohne die für die Suche verwendete Formel zu verändern, sodass ich dasselbe Arbeitsblatt problemlos wiederholt verwenden kann.
Bisher haben alle meine Versuche, andere Funktionen zu verwenden, nicht dazu geführt, dass der Rest wie gewünscht extrahiert werden konnte. Wenn Sie Vorschläge haben, bringen Sie es mir bitte bei.
Antwort1
OK, hier ist eine Lösung, die funktioniert, aber bei der Einrichtung zu Hirnschäden führen kann. Ich habe sie Schritt für Schritt aufgebaut und einen Satz von Dingen berechnet, die dann von den nächsten Berechnungen verwendet wurden. Als ich ein funktionierendes Modell hatte, arbeitete ich rückwärts und ersetzte die Zellreferenzen durch die eigentlichen Formeln, sodass alle Formeln nur auf Ihre eigentlichen Listen und nicht auf Zwischenberechnungen verwiesen. Die Formeln schossen wie Pilze aus dem Boden. Tatsächlich führte der erste Versuch zu Formeln, die die Zellkapazität überstiegen. Ich teilte sie in zwei Tabellen auf, wobei die erste die zweite speist. Die Tabellen sind sehr groß und Sie wären völlig durchgedreht, wenn Sie versucht hätten, alle Zellreferenzen auf die richtigen Stellen zu bringen, um die Formeln in zwei Richtungen in den Tabellen zu füllen. Also fügte ich einige indirekte Referenzen hinzu, sodass die Formeln einfach kopiert und eingefügt werden können und ohne manuelle Bereinigung funktionieren. Leider führte dies zu einigen ziemlich großen Formeln.
Ich erkläre dies anhand eines Beispiels, das an bestimmten Stellen in einer Tabelle platziert ist. Wenn Sie die Teile woanders platzieren müssen, bearbeiten Sie alle Zeilen- und Spaltenreferenzen in der ersten Zelle und kopieren und fügen Sie sie dann ein, um die Tabellen zu füllen. Richten Sie zu Ihrer eigenen Sicherheit einige bekannte Beispiele ein, damit Sie überprüfen können, ob die ersten paar Zeilen und Spalten in jeder Tabelle funktionieren, bevor Sie das Ganze füllen. Nehmen Sie vorbeugend ein paar Aspirin und wir legen los.
Dies basiert auf Ihrer Liste A in den Spalten A bis F mit Daten, die in Zeile 1 beginnen (2.000 Zeilen). Liste B befindet sich in den Spalten H bis J mit Daten, die in Zeile 1 beginnen (30 Zeilen).
Die erste Tabelle beginnt in L1. Diese Tabelle erstellt eine Liste der Positionen der Einträge der Liste B in den Datensätzen der Liste A. Beispiel:
Position: 1 2 3 4 5 6
So if a List A record contains: 1 3 3 5 7 9
and a List B record contains: 1 3 7
the entry in this table will be: 1 2 5 (stored as a single number: 125)
Wenn der Datensatz aus Liste B nicht mit dem Datensatz aus Liste A übereinstimmt, wird in der Zelle ein #N/A angezeigt. Das Layout dieser Tabelle sieht folgendermaßen aus:
[L] [M] [N] [O]
[1] <=======List B Row========>
[2] List A Row 1 2 3 ...
[3] 1
[4] 2
[5] 3
...
Sie müssen die Zeilennummern tatsächlich als Spaltenüberschriften in Zeile 2 der Spalten M bis AP und als Zeilenbeschriftungen in Spalte L eingeben. Diese werden von den Formeln als Zeiger verwendet. Es gibt 30 Datenspalten, eine für jede Zeile mit Einträgen in Liste B, und Sie erhalten 2.000 Zeilen, die die Einträge in Liste A darstellen, beginnend in Zeile 3. Jede Zelle der Tabelle spiegelt einen Eintrag in Liste B gegenüber einem Eintrag in Liste A wider. Dies ist die Formel für M3:
=MATCH(INDIRECT("H"&M$2),$A1:$F1,0)&MATCH(INDIRECT("H"&M$2),$A1:$F1,0)
+MATCH(INDIRECT("I"&M$2),INDIRECT(ADDRESS($L3,MATCH(INDIRECT("H"&M$2),$A1:$F1,0)+1, , )&":$F"&$L3),0)&MATCH(INDIRECT("H"&M$2),$A1:$F1,0)
+MATCH(INDIRECT("I"&M$2),INDIRECT(ADDRESS($L3,MATCH(INDIRECT("H"&M$2),$A1:$F1,0)+1, , )&":$F"&$L3),0)
+MATCH(INDIRECT("J"&M$2),INDIRECT(ADDRESS($L3,MATCH(INDIRECT("H"&M$2),$A1:$F1,0)
+MATCH(INDIRECT("I"&M$2),INDIRECT(ADDRESS($L3,MATCH(INDIRECT("H"&M$2),$A1:$F1,0)+1, , )&":$F"&$L3),0)+1, , )&":$F"&$L3),0)
Ich habe die Formel hier aufgeteilt, um sie lesbarer zu machen, aber es ist alles eine Formel. Überprüfen Sie, ob sie in M3 bis N4 mit einigen Beispieldaten funktioniert, und kopieren und fügen Sie sie dann ein, um die Tabelle zu füllen.
Die zweite Tabelle beginnt in AR1. Diese Tabelle ist genauso aufgebaut:
[AR] [AS] [AT] [AU]
[1] <=======List B Row========>
[2] List A Row 1 2 3 ...
[3] 1
[4] 2
[5] 3
...
Diese Tabelle funktioniert ähnlich wie die erste – jede Zelle stellt die Ergebnisse eines Datensatzes aus Liste B gegenüber einem Datensatz aus Liste A dar. Diese Tabelle enthält Ihren Rest. In dem Beispiel, das ich für die erste Tabelle angegeben habe, wäre der Rest also 359:
So if a List A record contains: 1 3 3 5 7 9
and a List B record contains: 1 3 7
the remainder is: 3 5 9
Die Formel für Zelle AS3 lautet:
=IF(ISNA(M3),"",IF(ISERROR(FIND(COLUMN(INDIRECT("a"&$AR3)),M3)),INDIRECT("a"&$AR3),"")&
IF(ISERROR(FIND(COLUMN(INDIRECT("b"&$AR3)),M3)),INDIRECT("b"&$AR3),"")&
IF(ISERROR(FIND(COLUMN(INDIRECT("c"&$AR3)),M3)),INDIRECT("c"&$AR3),"")&
IF(ISERROR(FIND(COLUMN(INDIRECT("d"&$AR3)),M3)),INDIRECT("d"&$AR3),"")&
IF(ISERROR(FIND(COLUMN(INDIRECT("e"&$AR3)),M3)),INDIRECT("e"&$AR3),"")&
IF(ISERROR(FIND(COLUMN(INDIRECT("f"&$AR3)),M3)),INDIRECT("f"&$AR3),""))
Jede Zelle in dieser Tabelle enthält entweder den Rest oder ein Nullzeichen, wenn keine Übereinstimmung vorliegt.
Sie wollten eine Zusammenfassung der Ergebnisse für jeden Datensatz der Liste A erhalten. Da jede Zeile der Tabelle einen Datensatz der Liste A darstellt, kann die Zusammenfassung am Ende jeder Zeile der Tabelle stehen. Die 30 Spalten der Tabelle enden in Spalte BV, die Ergebnisse stehen also in Spalte BW. Die Formel für BW3 lautet:
=AS3&IF(ISBLANK(AS3),""," ")&AT3&IF(ISBLANK(AT3),""," ")& ... &BV3&IF(ISBLANK(BV3),""," ")
Anstatt alle 30 Begriffe hier anzuzeigen, werden nur die ersten beiden und der letzte angezeigt. Folgen Sie demselben Muster, um den Rest hinzuzufügen. Die Ergebniszeichenfolge wird erstellt, indem die Ergebnisse jeder Übereinstimmung verkettet werden. Wenn eine Zelle einen Wert enthält, wird vor dem nächsten Wert ein Leerzeichen eingefügt. Wenn Sie ein anderes Trennzeichen wünschen, ändern Sie das Leerzeichen in etwas anderes, z. B. ein Komma. Kopieren Sie diese Formel für alle Zeilen in die Spalte BW.
Dies ist wahrscheinlich nicht der nützlichste Ort für die Ergebnisse. Sobald alles funktioniert, können Sie die Dinge verschieben. Tatsächlich kann es sein, dass Sie beim Verschieben eine umfassende Bereinigung der Zellreferenzen durchführen müssen. Es wäre sinnvoller, die gewünschte Ausgabe einfach an einem anderen Ort zu erstellen und Zellreferenzen zu verwenden, um auf das zu verweisen, was bereits eingerichtet ist.