☰

Die N-te Instanz einer Textzeichenfolge innerhalb eines Bereichs finden?

tag-icon • tag-icon openoffice openoffice-calc
Die N-te Instanz einer Textzeichenfolge innerhalb eines Bereichs finden?

Ich habe eine Liste mit Namen neben einer Liste mit Schichten. Ich möchte eine Liste der Personen in jeder Schicht nach Tag zusammenstellen, die automatisch und dynamisch aktualisiert wird. Ich weiß nicht, wie ich das am besten machen soll.

Im Hauptdienstplan wird es eine Spalte mit Mitarbeiternamen geben, gefolgt von 14 Spalten mit Schichten. Ich möchte dann 14 Namenslisten erstellen, basierend darauf, wer in der ersten Tabelle welche Schicht hat.

Beispiel: Der Hauptbereich hat:

        A      B   C   D   E
   ---------- --- --- --- ---
1  Joe Blogs   E   E   L   O
2  Jill Bleg   L   L   E   E
3  Geoff Ted   O   L   L   L

Ich möchte dann beispielsweise in Spalte D nachschlagen und nach der ersten Instanz von L suchen, um Joe Blogs aufzulisten, dann nach der zweiten Instanz von L suchen, um Geoff Ted aufzulisten, und so weiter.

Gibt es eine Möglichkeit, innerhalb eines Zellbereichs nach der N-ten Instanz einer Zeichenfolge zu suchen?

Antwort1

Das ist nicht so einfach, wie es scheint. Um zunächst die Zeilennummern aller Vorkommen von „L“ in Spalte D zu finden, verwenden Sie:

=IF(D1:D3="L"; ROW(D1:D3))

Speichern alseine Array-Formel: nach der Eingabe drücken Sie Strg+Umschalt+Eingabe (oder Befehl+Umschalt+Eingabe auf einem Mac). Es wird dannzeigenin geschweiften Klammern. Und es wird nicht eine einzelne Zelle als Ergebnis haben, sondern so viele Zellen wie der Bereich, an dem Sie arbeiten. Im obigen Beispiel würden Sie 3 Zellen mit den Werten 1, leer und 3 erhalten.

Nächste,verwendenSMALLum den N-ten Wert zu finden:

SMALL(numberlist; n)

Gibt die nkleinste Zahl innerhalb des (ungeordneten) Zahlenbereichs oder -arrays zurück numberlist.

Wenn Sie das Obige anwenden SMALL, erhalten Sie als Ergebnis wieder eine einzelne Zelle:

=SMALL(IF(D1:D3="L"; ROW(D1:D3)); 1)
=SMALL(IF(D1:D3="L"; ROW(D1:D3)); 2)

Obwohl das Ergebnis eine einzelne Zelle ist, muss diese dennoch mit Strg+Umschalt+Eingabe gespeichert werden.

Wenn Sie nun die Zeilennummer kennen, INDEXkönnen Sie den Namen in der ersten Spalte von finden A1:A3:

=INDEX(A1:A3; SMALL(IF(D1:D3="L"; ROW(D1:D3)); 1); 1)
=INDEX(A1:A3; SMALL(IF(D1:D3="L"; ROW(D1:D3)); 2); 1)

Auch hier müssen alle mit Strg+Umschalt+Eingabe gespeichert werden.

Eine solche Formel lässt sich allerdings nicht per Drag & Drop in andere Zellen ziehen, da die Ränge "1" und "2" dann nicht automatisch zu "3" werden würden, usw. Stattdessen berechnet man den benötigten Rang auf Basis der Zeile, in der die Formel steht:

=INDEX(A1:A3; SMALL(IF(D1:D3="L"; ROW(D1:D3)); ROW()); 1)

Nachdem dies als Array-Formel irgendwo in Zeile 1 gespeichert wurde, kann man es nach unten ziehen, um es zu Zeile 2, 3 usw. hinzuzufügen.

Alternativ können Sie, anstatt das Ergebnis zu erweitern, die Matrixformel kopieren/einfügen, um Spalten- und Zeilenreferenzen im Handumdrehen anzupassen. Im Screenshot unten habe ich Folgendes aus B7, B12 und B17 in die anderen Zellen kopiert:

=INDEX($A$1:$A$3; SMALL(IF(B$1:B$3=B$6; ROW(B$1:B$3)); ROW()-ROW(B$6)); 1)
=INDEX($A$1:$A$3; SMALL(IF(B$1:B$3=B$11; ROW(B$1:B$3)); ROW()-ROW(B$11)); 1)
=INDEX($A$1:$A$3; SMALL(IF(B$1:B$3=B$16; ROW(B$1:B$3)); ROW()-ROW(B$16)); 1)

Beachten Sie, dass die Tastenkombination für Arrayformeln nur funktioniert, nachdem Sie tatsächlich Änderungen an der Formel vorgenommen haben. Wenn Sie einfach die Eingabetaste drücken, dann erneut in die Formel gehen und dann Strg+Umschalt+Eingabe drücken, hat dies keine Auswirkung.

Sobald eine Matrixformel erweitert wurde, müssen Sie außerdem alle Ergebniszellen auswählen, um die Formel zu ändern. Andernfalls erhalten Sie„Sie können nicht nur einen Teil eines Arrays ändern“.

verwandte Informationen