☰

Für jede Zeile in einer Gruppe eine Spalte aus einer anderen Spalte auffüllen

tag-icon • tag-icon microsoft-excel
Für jede Zeile in einer Gruppe eine Spalte aus einer anderen Spalte auffüllen

Ich bin gerade dabei, Daten zu migrieren und habe ein Datenblatt mit über 100.000 Zeilen. Daher brauche ich eine Lösung, die über eine manuelle Aktualisierung hinausgeht.

Für diesen Beitrag werde ich das Problem vereinfachen: Ich habe ein Blatt mit drei Spalten (A, B und C) mit Überschriften [Datentyp] wie folgt: Name [Text], ID [Nummer], CreateDate [Datum]. Die Daten wurden nach Spalte A sortiert, um die doppelten Werte in dieser Spalte bewusst in den Fokus zu rücken. Die IDs sind eindeutig; die Daten sindnichteinzigartig.

Im Beispiel gibt es neun Zeilen, die aufgrund doppelter Werte in Spalte A in drei Gruppen „gruppiert“ werden können. Für die Zeilen 2 und 3 ist der Wert in Spalte A also abc, für die Zeilen 4, 5 und 6 ist der Wert in Spalte A defund für die Zeilen 7-10 ist er ghi.

Die erste Aufgabe besteht darin, das letzte Erstellungsdatum in jeder der „Zeilengruppen“ zu bestimmen. In diesem Beispiel wäre das also der 11.05.1999 für die Zeilen 2 und 3, der 12.03.2001 für die Zeilen 4 bis 6 und der 11.05.1999 für die Zeilen 7 bis 10. Ich mache das, indem ich die Array-Formel {=MAX(IF(A2=$A:$A,$C:$C))} in Spalte D verwende.

Die nächste Aufgabe erweist sich als schwierig. Nachdem ich nun das neueste Datum für jede Zeilengruppe ermittelt habe, möchte ich die ID, die diesem LatestDate entspricht, in Spalte E (in jeder Zeile der Zeilengruppe) einfügen, sodass das Ergebnis wie Spalte E im folgenden Beispiel aussieht. Aber ich muss dies mit einer Formel/Funktion tun, nicht manuell. Dies ist für ein Migrationsprojekt, also muss es nicht schön sein.

Die passende ID in Spalte E in derselben Zeile einzutragen ist einfach (eine IF-Anweisung) – siehe Zeilen 3, 6 und 9 – aber ich behaupte nicht, dass das die Lösung ist. Aber ich kann nicht herausfinden, wie ich die anderen Zeilen in jeder Gruppe mit derselben ID füllen kann – in meinem beigefügten Beispiel wären das die Zeilen 2, 4 & 5, 7 & 8 und 10. Ich kann nicht einfach in Spalte C nachschlagen, weil diese Werte nicht eindeutig sind. Nach vergeblichen Versuchen mit verschiedenen Formeln bin ich ratlos. Ich vermute, dass es auf Spalte C (die passenden Werte) und Funktionen wie INDEXund  MATCH... oder vielleicht VBA angewiesen ist.

     +------+------+------------+------------+-----------------+
     |  A   |   B  |     C      |     D      |        E        |
+----+------+------+------------+------------+-----------------+
|  1 | Name |  ID  | CreateDate | LatestDate | CorrespondingID |
|  2 | abc  |   1  | 4/12/1998  | 5/11/1999  |         2       |
|  3 | abc  |   2  | 5/11/1999  | 5/11/1999  |         2       |
|  4 | def  |   3  | 1/12/1999  | 3/12/2001  |         5       |
|  5 | def  |   4  | 5/11/1999  | 3/12/2001  |         5       |
|  6 | def  |   5  | 3/12/2001  | 3/12/2001  |         5       |
|  7 | ghi  |  17  | 1/17/1999  | 5/11/1999  |        55       |
|  8 | ghi  |  42  | 2/4/1999   | 5/11/1999  |        55       |
|  9 | ghi  |  55  | 5/11/1999  | 5/11/1999  |        55       |
| 10 | ghi  |  83  | 3/28/1999  | 5/11/1999  |        55       |
+----+------+------+------------+------------+-----------------+

(Ein kürzeres Beispiel steht als Bild zur Verfügung.)

Antwort1

Meine Referenz istHier. TLDR: Verwenden Sie die Nicht-Array-Version der Index-Match-Formel.

Geben Sie in E2 Folgendes ein:

=INDEX(B:B,MATCH(1,INDEX((A2=A:A)*(D2=C:C),0,1),0))

Idee: Der interne Index () generiert eine Liste mit 0 und 1, die sowohl dem Namens- als auch dem Datumskriterium entspricht. Anschließend wird der externe Index () verwendet, um den Namen aus A:A zu „laden“.

Antwort2

Diese Lösung verwendet die Offset-Match-Methode. Dabei wird ein Match verwendet, um das Datum zu finden, und dann basierend auf dem Datum ein Offset, um die entsprechende ID zu finden:

=OFFSET(C2,VERGLEICH(D2,C2:C,0)-1,-1)

Legen Sie dies in der Zelle E2 ab und ziehen Sie es dann nach unten über E

Antwort3

Für eine bestimmte Zeile (z. B. Zeile 2) möchten Sie die ID (Spalte  B) aus einer Zeile  findenN(d. h. Zelle  ), wobei der Name ( ) dem Namen aus der aktuellen Zeile ( ) und das Erstellungsdatum ( ) dem neuesten Datum aus der aktuellen Zeile ( ) entspricht. Zeile BnAnA2CnD2Nist eindeutig, weil die IDs eindeutig sind, und daher „Die” ist die maximale Zeile. Eine logische Antwort wäre eine geringfügige Erweiterung Ihrer Formel für Spalte  D:

=MAX(IF(AND(A2=$A:$A,D2=$C:$C), $B:$B))

Leider ANDscheint das in Array-Formeln nicht zu funktionieren. Wir verwenden also einen Standardtrick: TRUE = 1 (oder irgendetwas anderes als Null) und  = 0, damit wir mit Multiplikation FALSE simulieren können (  =  genau wie 1 × 1 = 1,  =  genau wie 0 × ANDAND(TRUE,TRUE)TRUEAND(FALSE,anything)FALSEirgendetwas = 0). Also ändern wir das obige zu

=MAX(IF((A2=$A:$A)*(D2=$C:$C), $B:$B))

(eingegeben als Matrixformel, natürlich mit Ctrl+ Shift+ ):Enter

Beachten Sie, dass dies mit nicht eindeutigen Erstellungsdaten funktioniert. Es funktioniert sogar, wenn die Erstellungsdaten nicht in derselben Reihenfolge wie die IDs sind, wie im obigen Beispiel (wo die Daten nach Spalten  Aund  sortiert sind B).

verwandte Informationen