Das Problem, das ich im Wesentlichen zu lösen versuche, ist eine SVERWEIS-Funktion, die die Spalten A:E auf einen Wert überprüft und den in Spalte F enthaltenen Wert zurückgibt, wenn er in einer dieser Spalten gefunden wird.
Da SVERWEIS dieser Aufgabe nicht gewachsen ist, habe ich mir die INDEX-MATCH-Syntax angesehen, aber ich habe Schwierigkeiten, herauszufinden, wie ich dies für ein Array von Werten im Gegensatz zu einer einzelnen Spalte durchführen kann. Ich habe unten einen Beispieldatensatz erstellt, um dies zu erklären:
A------B------C------D------E------F
1------2------3------4------5------Apple
12-----13--------------------------Banana
14---------------------------------Carrot
Wenn die zu prüfende Zelle die Zahlen 1, 2, 3, 4 oder 5 enthält, sollte das Ergebnis der Formel Apfel sein. Wenn die Zelle 12 oder 13 enthält, sollte Banane zurückgegeben werden und wenn die Zelle 14 enthält, sollte Karotte zurückgegeben werden.
Die zweite Hälfte davon ergibt sich aus der Tatsache, dass die referenzierte Zelle kein einzelner Wert ist, sondern selbst eine vollständige Tabelle. Daher wird diese Suche je nach unterschiedlichen Werten sehr oft durchgeführt.
Zur Veranschaulichung gibt es eine andere Tabelle (siehe unten), die diese Werte enthält. Ich versuche, das System dazu zu bringen, zu erkennen, welche Zeile und damit welcher der Werte „Apfel, Banane, Karotte“ mit welcher Spalte verknüpft werden soll. Die Tabelle würde wie folgt aussehen:
HALLO------------
1------(Apfel)----
2------(Apfel)----
12-----(Banane)-
usw.-----------------
Die Werte in Klammern geben die Werte an, für deren Berechnung die Formel zuständig ist.
Antwort1
Es gibt mehrere verschiedene Fälle. Betrachten wir einen Fall:
Irgendwo in SpaltenAdurchEEs gibt nur eine Zelle mit dem Wert 13. Gib den Inhalt der Zelle in der Spalte zurück.Fin der gleichen Zeile.
Wir werden eine "Hilfsspalte" verwenden. InG1eingeben:
=COUNTIF(A1:E1,13)
und nach unten kopieren. Dadurch können wir die Zeile identifizieren:
Jetzt können wir verwendenMATCH()/INDEX():
Wählen Sie eine Zelle aus und geben Sie ein:
=INDEX(F:F,MATCH(1,G:G,0))
Wenn sich die „Regeln“ ändern und es mehr als eine 13 in einer Reihe oder mehrere Reihen mit der Zahl 13 geben könnte, würden wir die Hilfsspalte ändern.
BEARBEITEN #1:
Basierend auf Ihrem Update wäre der erste Schritt, die hartcodierten13aus den Formeln in der Spalte "Hilfs" und füge sie in eine eigene Zelle ein,(sagenH1). Dann können Sie verschiedene Fälle ausführen, indem Sie einfach eine einzelne Zelle ändern.
Wenn Sie eine große Anzahl von Fällen in einer Tabelle haben, können Sie ein Makro erstellen, um jeden Fall einzurichten(aktualisierenH1)und notieren Sie die Ergebnisse.
Antwort2
Basierend auf meinen eigenen Recherchen und Diskussionen mit @Gary'sStudent bestand meine Lösung darin, eine MATCH-Formel für alle möglichen Spalten zu erstellen, die den Wert enthalten könnten, zusammen mit einer „IFERROR“-Anweisung zum Auffangen von Leerzeichen.
I1 =IFERROR(MATCH($H1,A$1:A$3,0),"")
J1 =IFERROR(MATCH($H1,B$1:B$3,0),"")
K1 =IFERROR(MATCH($H1,C$1:C$3,0),"")
L1 =IFERROR(MATCH($H1,D$1:D$3,0),"")
M1 =IFERROR(MATCH($H1,E$1:E$3,0),"")
etc.
Diese Spalten können jetzt ausgeblendet werden, um Verwirrung/Interaktionen des Benutzers zu vermeiden.
Ich habe dann einen Index erstellt, der diese zu einem einzigen Wert zusammenfasst, der mit der betreffenden Zeile übereinstimmen sollte. Auch hier gibt es eine Prüfung (erste SUMME), um dies als leeren Wert einzugeben, wenn der Wert nicht in der Tabelle gefunden wird.
N1 =IF(SUM(I1:M1)=0,"",INDEX($A$1:$F$3,SUM(I1:M1),6))
Abschließend habe ich einige Formeln zur bedingten Formatierung eingegeben, um sicherzustellen, dass der Benutzer doppelte Daten erkennt und ersetzt/entfernt.
A1:E3 Cell contains a blank value [Formatting None Set, Stop if True]
A1:E3 =COUNTIF($A$1:$E$3,A1)>1 [Formatting Text:White, Background:Red]
H1:N1 =COUNTIF($A$1:$E$3,H1)>1 [Formatting Text:Red, Background:Red]
Dies ist lediglich ein Hinweis an den Benutzer, diese doppelten Daten zu entfernen.
Antwort3
Für eine einzelne Formel in H1:
=INDEX($F$1:INDEX(F:F,MATCH("ZZZ",F:F)),AGGREGATE(15,6,ROW($A$1:INDEX(E:E,MATCH("ZZZ",F:F)))/($A$1:INDEX(E:E,MATCH("ZZZ",F:F))=H1),1))
Dies ist eine Array-Formel, daher müssen wir die Verweise auf die Größe des Datensatzes beschränken. INDEX(E:E,MATCH("ZZZ",F:F))Das tun alle. Dadurch wird die letzte Zeile in Spalte F zurückgegeben, die Text enthält. Diese wird dann als letzte Zeile für die Iteration festgelegt.
Die Methode @Gary'sStudent vermeidet Array-Formeln und ist möglicherweise die benötigte Methode. Mit zunehmendem Datensatz und Anzahl der Formeln erhöht sich auch die Zeit für die Berechnungen. An einem gewissen Punkt kann es sogar zum Absturz von Excel kommen. Normalerweise dauert dies einige Tausend, aber ich möchte Sie warnen.
BEARBEITEN
So vermeiden Sie die Verwendung von Array-Formeln und haben dennoch eine einzige Formel:
=IFERROR(INDEX(F:F,MIN(IFERROR(MATCH($H1,A:A,0),1050000),IFERROR(MATCH($H1,B:B,0),1050000),IFERROR(MATCH($H1,C:C,0),1050000),IFERROR(MATCH($H1,D:D,0),1050000),IFERROR(MATCH($H1,E:E,0),1050000))),"")
Dies basiert auf der Antwort des OP, lediglich diese Methode wurde in einer Formel kombiniert.
Diese Formel ignoriert doppelte Einträge und gibt die erste Zeile zurück, in der die Nummer gefunden wird.
Und da es sich nicht um ein Array handelt, wirken sich Vollspaltenverweise nicht nachteilig auf die Berechnungszeiten aus.
Antwort4
Eine andere Methode würde auf einer Hilfstabelle basieren, die darstellt, wie dies von Anfang an „hätte“ strukturiert sein sollen. Dadurch würden die Monstergleichungen vermieden, deren Debuggen und anschließendes Ändern lästig ist, und es ist in der Lage, eine variierende Anzahl von Spalten sauber zu lösen, im Gegensatz zur Idee mit 5 Nachschlagespalten.
Wenn das Obige in Blatt1 steht, fügen Sie ein Blatt2 hinzu. Platzieren Sie dort vier Spalten: Zeile, Spalte, ID, Name
Die Formel Rowsollte lauten (im Pseudocode bedeutet „Letzte“ „für die Zeile darüber in Blatt2“).
=IF(Column = 1, Last row + 1 , Last row)
Formel in Column:
=IF(OR(Last Column = 5; INDEX(StartTable, last row, last column + 1) = ""), 1, Last column+1)
Formel in IDund Name:
=INDEX(StartTable, Row, Column)
=INDEX(NameColumn, Row, 1)
Dann füllen Sie dies nach unten aus (im Grunde bis row>Anzahl der Zeilen in der Originaltabelle).
Schließlich verwenden Sie die neue Tabelle mit einem gewöhnlichen VLookup oder Index/Match.
PRO: Viel einfachere Formeln, leichter zu verwenden und zu verstehen.
NACHTEILE: Zusätzliche Tabelle erforderlich, die Länge der Tabelle muss beibehalten werden. Leistungstechnisch besteht ein Risiko, da für die gesamte Wertezeichenfolge praktisch ein einziger Thread erforderlich ist.
Wenn auch ein paar Fehlerzeilen in Ordnung sind, kann der Code etwas einfacher und möglicherweise leistungsfähiger sein. Wir können dann davon ausgehen, dass die Anzahl der Spalten immer 5 ist, was sowohl Zeile als auch Spalte ergibt.