Ich suche nach einer Formel, die einen Bericht über den ablaufenden Zugriff auf bestimmte Schulungen erstellt.
Momentan sieht meine Tabelle so aus:
| Name | Schulung1 | Schulung2 | Schulung3 |
|---|---|---|---|
| John | 01.08.2023 | 01.08.2023 | 01.08.2023 |
| Markieren | 01.08.2023 | 01.06.2023 | 01.10.2023 |
| Steve | 01.06.2023 | 01.06.2023 | 01.06.2023 |
Ich möchte einen Mechanismus erstellen, der mir Schulungen anzeigt, die in den nächsten 45 Tagen ablaufen und abgelaufen sind, mit einer Liste von Namen – Modulen und Datum, etwa so:
Läuft innerhalb von 45 Tagen ab:
| Name | Modul | Läuft ab am |
|---|---|---|
| John | Schulung1 | 01.08.2023 |
| John | Schulung2 | 01.08.2023 |
| John | Schulung3 | 01.08.2023 |
| Markieren | Schulung1 | 01.08.2023 |
Abgelaufen:
| Name | Modul | Läuft ab am |
|---|---|---|
| Markieren | Schulung2 | 01.06.2023 |
| Steve | Schulung1 | 01.06.2023 |
| Steve | Schulung2 | 01.06.2023 |
| Steve | Schulung3 | 01.06.2023 |
Ich habe es geschafft, eine Formel zum Auflisten von Namen zu erhalten, aber ich kann nicht herausfinden, wie ich „Training“ in der Kopfzeile oder im Datum abgleichen kann.
=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(B:D)/(B:D<TODAY()+45), ROW(1:1))),"") / expiring within 45 days
=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(B:D)/(B:D<TODAY()), ROW(1:1))),"") / expired
Wie kann die Formel so umgestaltet werden, dass sie der Trainingsüberschrift und dem Datum jeder Person entspricht?
Antwort1
Ein Ansatz, dies zu erreichen, ist die Verwendung einer LET()Anweisung, die es Ihnen ermöglicht, Zwischenergebnisse zu speichern. Auf diese Weise können Sie Ihre Daten in die richtige Form bringen und dann basierend auf Ihren Bedingungen filtern, z. B. Datum < heute für die abgelaufenen Trainings.
Ich habe Ihr Beispiel leicht erweitert, um eine andere Anzahl von Namen und Modulen zu haben, damit das Verfahren leichter verdaulich wird. Die Daten werden im Bereich gespeichert A1:D5:
| Name | Schulung1 | Schulung2 | Schulung3 |
|---|---|---|---|
| John | 01.08.2023 | 01.08.2023 | 01.08.2023 |
| Markieren | 01.08.2023 | 01.06.2023 | 01.10.2023 |
| Steve | 01.06.2023 | 01.06.2023 | 01.06.2023 |
| Rechnung | 01.06.2023 | 01.08.2023 | 01.10.2023 |
Die LET()Anweisung sieht wie folgt aus:
=LET(
data,$A$1:$D$5,
nMod,COLUMNS(INDEX(data,1,))-1,
nRow,ROWS(INDEX(data,,1))-1,
tmpM,CHOOSEROWS(TRANSPOSE(INDEX(data,1,)),SEQUENCE(nMod,,2)),
modules,SORT(INDEX(tmpM,MOD(SEQUENCE(nMod*nRow)-1,nMod)+1)),
tmpN,CHOOSEROWS(INDEX(data,,1),SEQUENCE(nRow,,2)),
names,INDEX(tmpN,MOD(SEQUENCE(nMod*nRow)-1,nRow)+1),
dates,TOCOL(CHOOSEROWS(CHOOSECOLS(data,SEQUENCE(nMod,,2)),SEQUENCE(nRow,,2)),0,TRUE),
combinedTable, HSTACK(names,modules,dates),
filteredData,SORT(FILTER(combinedTable, INDEX(combinedTable,,3)<TODAY()),1),
result, VSTACK(HSTACK("Name", "Module", "Expiring on"), filteredData),
result)
Sie beginnen damit, den Bereich Ihrer Daten anzugeben, z. B. A1:D5in diesem Fall. Dies könnte möglicherweise noch weiter automatisiert werden, z. B. durch eine COUNTA()Anweisung oder Ähnliches. Da der Bereich jedoch nur einmal angegeben werden muss, ist dies in den meisten Fällen ausreichend. Alles andere wird automatisch berechnet.
Als nächstes berechnen wir zunächst die Anzahl der Module und Zeilen als Anzahl der Spalten/Zeilen in „Daten“ - 1, vorausgesetzt, dass „Name“ und „Header“ kein relevantes Modul oder keine relevante Zeile sind. Im nächsten Schritt extrahieren wir die jeweiligen Namen der Module (z. B. Training1-Training3) als tmpM. Die Module müssen basierend auf der Anzahl der relevanten Module und Zeilen x-mal wiederholt werden. Um dies zu erreichen, kombinieren wir INDEX()mit MOD()und SEQUENCE(). Das gleiche Verfahren wird wiederholt, um die jeweilige Namenssequenz zu erstellen, die den Namen basierend auf den Modulen so oft wie nötig wiederholt. Schließlich müssen wir alle Daten vertikal in einer Spalte stapeln, anstatt sie als Matrix zu haben. Dafür verwenden wir hauptsächlich die TOCOL()Funktion. Darüber hinaus wählen wir auch nur die Daten aus, d. h. keine Namen und Header, indem wir zusätzlich CHOOSEROWSund verwenden CHOOSECOLS. Sobald dies erledigt ist, stapeln wir die „Namen“, „Module“ und „Daten“ horizontal zusammen, indem wir verwenden HSTACK(). Die resultierende Tabelle sieht wie folgt aus:
| A | B | C |
|---|---|---|
| John | Schulung1 | 01.08.2023 |
| Markieren | Schulung1 | 01.08.2023 |
| Steve | Schulung1 | 01.06.2023 |
| Rechnung | Schulung1 | 01.06.2023 |
| John | Schulung2 | 01.08.2023 |
| Markieren | Schulung2 | 01.06.2023 |
| Steve | Schulung2 | 01.06.2023 |
| Rechnung | Schulung2 | 01.08.2023 |
| John | Schulung3 | 01.08.2023 |
| Markieren | Schulung3 | 01.10.2023 |
| Steve | Schulung3 | 01.06.2023 |
| Rechnung | Schulung3 | 01.10.2023 |
Der nächste Schritt ist dann eine einfache FILTER()Anweisung, die die Daten filtert, deren Datum < ist TODAY(). Dazu wollen wir die „combinedTable“ anhand der dritten Spalte, also dem Datum, filtern. Um diese Information in der Filteranweisung zu verwenden, verwenden wir die INDEX()Funktion.
Wenn Sie Daten filtern möchten, die innerhalb der nächsten 45 Tage liegen, müssen Sie die FILTER()Anweisung wie folgt anpassen, sodass Sie nur die Fälle filtern, deren Datum > TODAY()und Datum <= ist TODAY()+45:
filteredData,SORT(FILTER(combinedTable,
(INDEX(combinedTable,,3)>TODAY())*(INDEX(combinedTable,,3)<=TODAY()+45)),1),
Sobald die Daten gefiltert sind, sammeln wir die Ergebnisse, d. h. wir schließen die relevanten Überschriften ein und verwenden eine VSTACK()Anweisung, um die Überschriften mit den gefilterten Daten zu kombinieren. Die Ausgabe ist ein einzelnes Spilling-Array, das alle relevanten Informationen enthält und nur eine Formel in einer einzelnen Zelle benötigt. Die endgültige Ausgabe sieht wie folgt aus:
Abgelaufen:
| Name | Modul | Läuft ab am |
|---|---|---|
| Rechnung | Schulung1 | 01.06.2023 |
| Markieren | Schulung2 | 01.06.2023 |
| Steve | Schulung1 | 01.06.2023 |
| Steve | Schulung2 | 01.06.2023 |
| Steve | Schulung3 | 01.06.2023 |
Läuft innerhalb von 45 Tagen ab:
| Name | Modul | Läuft ab am |
|---|---|---|
| Rechnung | Schulung2 | 01.08.2023 |
| John | Schulung1 | 01.08.2023 |
| John | Schulung2 | 01.08.2023 |
| John | Schulung3 | 01.08.2023 |
| Markieren | Schulung1 | 01.08.2023 |
Wenn Sie Zwischenschritte anzeigen möchten, können Sie das letzte „Ergebnis“ in der Formel einfach durch einen anderen definierten Namen ersetzen, z. B. „kombinierteTabelle“, „Daten“ usw.