Suchen Sie nach dem zweiten oder dritten Vorkommen eines Werts in einer bestimmten Zeile - Excel

Question

Ich versuche, die Spaltenposition eines Datums zu ermitteln, das mehr als einmal in einer Zeile vorkommt.

Beispieldaten:
.

Die folgende Formel identifiziert die meisten Spaltenpositionen korrekt, wenn jedoch ein Duplikat vorhanden ist, wird immer die Position des ersten Vorkommens zurückgegeben.

Die Formel :

Vielen Dank im Voraus.

Answer 1

Die folgende Formel wählt die n-thÜbereinstimmung in einer Reihe übereinstimmender und nicht übereinstimmender Zellen aus. Sie kann an verschiedene Quelldatenlayouts angepasst werden, dazu später mehr. Sie ist insofern sehr simpel, als sie keinerlei Fehlerprüfung bietet, nicht einmal Ihren IF(FL2<>0Teil, da sie für eine breite Palette von Anwendungen gedacht ist, aber Sie können eine solche Prüfung ganz einfach hinzufügen, um sie genau Ihren Umständen anzupassen:

=FILTERXML("<Outer><Inner>"&SUBSTITUTE(TEXTJOIN("¢",FALSE,FILTER(SUBSTITUTE(ADDRESS(ROW(),COLUMN(AW2:CW2),4),ROW(),""),AW2:CW2=B2),ROW(),""),"¢","</Inner><Inner>")&"</Inner></Outer>","/Outer/Inner["&C2&"]")

Es ist so eingerichtet, dass es davon ausgeht, dass es in Spalte A einer Zeile mit dem passenden Wert in Spalte B und dem n-thWert in Spalte C passt. Natürlich können Sie diese anpassen.

Eine Version, LET()bei der alle Variablen an einer leicht zu bearbeitenden Stelle abgelegt werden, ist:

=LET(
 RangeToExamine, AW2:CW2,  Delimiter, "¢",  ItemToMatch, B2,  InstanceToMatch,  C2,
 FILTERXML("<Outer><Inner>"
 &SUBSTITUTE(TEXTJOIN(Delimiter,FALSE,
 FILTER(SUBSTITUTE(ADDRESS(ROW(),COLUMN(RangeToExamine),4),ROW(),""),
 RangeToExamine=ItemToMatch),
 ROW(),""),  Delimiter,"</Inner><Inner>") & "</Inner></Outer>",
 "/Outer/Inner["&InstanceToMatch&"]"))

Das LET()ist alles, was dazu dient, die Formel leichter bearbeiten zu können.

Also, was es tut. Erstens, obwohl Ihr Layout eine einzelne Zeile sein könnte, war ich mir nicht sicher, also ließ ich es so angeordnet, dass es mit jeder Zeile funktioniert. Dies wird dadurch behoben, dass die ROW()Funktion innerhalb der ADDRESS()Funktion und später in einer SUBSTITUTE()Funktion verwendet wird, die die Zeilennummer entfernt (so dass Sie nur eine Spalte haben, was Ihr gewünschtes Ergebnis ist). Sie könnten bei Bedarf eine Ziffer „1“ an beiden Stellen platzieren, um sicherzustellen, dass keine zeilenweise Aktivität stattfindet. Sie könnten sogar eine Klausel hinzufügen, LET()wenn Sie sie hier und da ändern möchten. Ich habe das hier nicht getan, weil es die LET()Zeile hier auf zwei Zeilen kürzt und das würde das Ganze unübersichtlich machen. Übrigens, so konvertieren Sie eine Spaltennummer in eine Spaltenbuchstabe, nicht mit all diesen lustigen Formeln mit MOD()(und Schlimmerem). Keine UDFs erforderlich.

ADDRESS()Verwendet also Spilleine Funktion, um ein Array aller Zelladressen im untersuchten Bereich zu erstellen. FILTER()Untersucht dann den Zielbereich auf Übereinstimmungen und listet die entsprechenden Zelladressen auf. (Auch hier gilt: nicht auf Fehler ausgelegt!) SUBSTITUTE()Dann wird die Zeilennummer entfernt (oder die Konstante, wenn Sie diese Änderung vorgenommen haben), sodass Excel nun ein Array nur mit den Spaltenbuchstabenbeschriftungen für die Spalten hat, in denen Ihre Übereinstimmungen waren.

Ich sollte kurz erwähnen, dass das Trennzeichen, das ich hier verwendet habe, nicht unbedingt immer gut ist – in anderen Fällen. Da es aber nicht mit den zugrunde liegenden Daten, sondern mit den Adressdaten verbunden ist, muss es nur etwas sein, das in einer von zurückgegebenen Adresse nie verwendet wurde, ADDRESS()sodass es hier nicht fehlschlagen kann. Wenn Sie jedoch anderswo den Ansatz „selten verwendetes Zeichen“ verwenden, müssen Sie möglicherweise etwas Seltsames auswählen, das Sie in der Zeichentabelle finden.

Als nächstes TEXTJOIN()wird das Array von Adressen in einen String umgewandelt und SUBSTITUTE()dieses Trennzeichen durch den </Inner><Inner>String ersetzt, während Sie davor und danach ebenfalls einen String hinzufügen, um das bisherige Ergebnis in akzeptables XML umzuwandeln. Zur Strukturierung des XML kann fast alles verwendet werden, solange es mindestens eine höhere Ebene gibt, die Dinge umschließt, und mindestens eine niedrigste Ebene, die jeden Teil des TEXTJOIN()Strings umschließt. Ich verwende gerne <Outer>für die höhere Ebene, die alles umschließt, und <Inner>für die Arbeitsebene.

Ich habe das zum ersten Mal irgendwo gesehen, mit einer sehr schlecht formulierten Erklärung, aber dann ging ich zuChandoo.orgum zu sehen, ob er einen Tipp dazu hat, da seine Erklärungen (fast) immer ziemlich klar und nützlich sind. Es war sehr praktisch, also muss ich Anerkennung und Dank aussprechen!

Zum Schluss FILTERXML()noch eine letzte Sache. Sie können auswählen, welche Instanz der Übereinstimmungen Sie zurückgeben möchten. In Chandoos Tipp zerlegt er einen Satz auf diese Weise, aber wie Sie sehen, kann er alles zerlegen, was Sie erhalten, oder in eine Zeichenfolge umwandeln, einschließlich Spaltendaten. In diesem Fall würden Sie, wenn Sie eine dritte Instanz wünschen, [3]Dinge hinzufügen.

Excel ist hier jedoch noch einen Schritt weiter. Erstens können Sie die numerische Instanz verwenden, wie [3] für die dritte Instanz, aber Sie können auch verwenden, [last()]um die letzte Instanz zu finden, ohne auch ihre Kardinalzahl finden zu müssen. Aber wo die Funktion wirklich glänzt, ist überraschenderweise (da sie nicht auf diese Art von Dingen ausgerichtet zu sein scheint), dass Sie diese Klausel „erstellen“ können, oder anders gesagt, Sie können eine Zeichenfolge erstellen, die variable Daten als Teil enthält, die Ihre Formel dann reaktionsfähig macht, anstatt sie eingeben und bei Bedarf innerhalb der Formel ändern zu müssen. Das bedeutet, dass Ihre Benutzer diese Funktionalität durch Einträge in Zellen erhalten können, anstatt Formeln zu bearbeiten. Oder dass Sie sie bei Bedarf das Ergebnis einer anderen Formel widerspiegeln lassen können.

Dieser letzte Teil erfolgt in der letzten Zeile der formatierten Version der Formel. Sie geben den „XML-Pfad“ (in diesem Fall „/Outer/Inner“) ein, unmittelbar gefolgt von einem Satz eckiger Klammern („[ ]“) mit der Instanznummer oder dem Ausdruck darin.

Im Allgemeinen empfehle ich, bei der Fehlerprüfung Folgendes zu beachten:

1. Ihr passender Artikel liegt nicht im untersuchten Bereich
2. Die gewünschte Instanznummer ist höher als die Gesamtzahl der Übereinstimmungen: Sie möchten Nr. 6, aber es sind nur vier vorhanden ...
3. Wenn Sie die n-te Instanz ab der letzten Instanz suchen möchten, muss diese ebenfalls vorhanden sein. Wenn Sie ab „last()“ die sechste Instanz suchen möchten, muss es mindestens sechs weitere geben …

und dass Sie bei der Wahl des Trennzeichens vorsichtig sein sollten, wenn die Daten mehr als nur Buchstaben und Zahlen enthalten.

Wenn Sie eine Zellenadresse statt nur einer Spalte möchten, entfernen Sie das, SUBSTITUTE()wodurch die Zeile von der vollständigen Adresse befreit wird.

Nun, es tut mir in gewisser Weise leid: Es IST eine allgemeine Lösung und nicht vollständig auf Ihren Beitrag zugeschnitten, aber ich war mir nicht sicher, welchen genauen Verwendungszweck Sie haben, und daher könnte das auch hilfreich sein, und etwaige Änderungen sind ziemlich leicht zu erkennen. Die Erfahrung zeigt, dass in Beiträgen sehr oft nützliche Informationen ausgelassen werden und ein Verfasser dann mindestens eine weitere Frage stellen muss, um die gewünschte Antwort zu erhalten. Ich versuche, die Notwendigkeit für Sie, diesen Prozess zu durchlaufen, zu umgehen.

Answer 2

Suchen Sie nach der ZÄHLENWENN-Formel? =WENN(A2<>0,ZÄHLENWENN($A$2:A2,A2),0)