Datum des dritten vorherigen Ereignisses suchen

Question

Grundsätzlich muss die Formel die drei aktuellsten Abschlüsse einer Aufgabe finden und dann das Datum des ältesten Abschlusses dieser drei Aufgaben ausgeben. Das heißt, das Datum des drittaktuellsten Abschlusses jeder Aufgabe.

Hier ist ein Beispiel, falls meine Erklärung nicht klar ist:

Date    | Task   | Count of Task
--------------------------
6/30/18 | Task A | 2
6/30/18 | Task B | 3
7/01/18 | Task A | 2
7/02/18 | Task B | 1
7/03/18 | Task B | 1
7/03/18 | Task B | 1
7/05/18 | Task A | 2
7/09/18 | Task C | 7

Die Formel muss Folgendes zurückgeben:

Task A: 7/1/18
Task B: 7/2/18
Task C: 7/9/18

Aufgabe A: Es gab 2 Abschlüsse am 5.7. (der letzte), also ist der drittletzte vom 1.7.
Aufgabe B: Es gab 2 Abschlüsse am 3.7. (der letzte), also ist der drittletzte vom 2.7.
Aufgabe C: Alle waren am selben Datum, also ist der drittletzte vom 9.7.

Die Formel muss in der Lage sein, zwei separate Zeilen mit demselben Datum und derselben Aufgabe zu verarbeiten, da der Datensatz solche Einträge enthält.

Ich denke, die Lösung wird eine Kombination aus Vlookup und Sumif beinhalten, aber das übersteigt meine derzeitigen Fähigkeiten.

Answer 1

C10Schreiben Sie diese Array-Formel in die Zelle , schließen Sie mit Ctrl+Shift+Enter„& nach unten füllen“ ab.

{=MIN(IF($B$3:$B$8=A10,$A$3:$A$8))}

Answer 2

1. Erstellen Sie die PivotTable.

   • Markieren Sie die Daten.
   • Gehen Sie zu Einfügen > Tabellen > PivotTable.
   • Wählen Sie, wo der Tisch platziert werden soll.
   • Überprüfen Sie Datum, Aufgabe und Anzahl.
   • Ziehen Sie „Datum“ in die Zeilen, „Aufgabe“ in die Spalten und „Anzahl“ in die Werte. Und wählen Sie „Summe der Anzahl“ (falls nicht bereits geschehen).

2. Sortieren Sie die Daten (Zeilenbeschriftungen) absteigend.

3. Kumulierte Summen bilden: In F3 die Formel eintippen =SUM(B$3:B3). Und bis H8 erweitern.
4. Erstellen Sie Boolesche Werte, um anzugeben, wann die Summe mindestens 3 beträgt: Geben Sie in I3 die Formel ein =F3>=3. Und erweitern Sie sie auf K8.
5. Wiederholen Sie die Daten, da die Suche bei SVERWEIS nach rechts erfolgen muss: Geben Sie in L3 die Formel ein =$A3. Und erweitern Sie sie bis N8.
6. Erstellen Sie die SVERWEISE. Geben Sie in I9 die Formel ein =VLOOKUP(TRUE, I3:L8,4,FALSE). Erweitern auf K9

Die Antwort liegt in I9 bis K9.

Diese Lösung nimmt viele Zellen in Anspruch, ist aber einfach einzurichten. Beachten Sie, dass Sie, anstatt die Formel in Schritt 5 auf N8 zu erweitern, stattdessen einfach die SVERWEIS-Funktion in J9 so ändern können, dass sie auf die 3. Spalte verweist, und die SVERWEIS-Funktion in K9 so ändern können, dass sie auf die 2. Spalte verweist.

Tabelle mit Werten:

Tabellenkalkulation mit Formeln:

Answer 3

Eine Kombination aus meiner älteren Antwort und der Antwort von Rajesh S.

1. Erstellen Sie eine kumulative Summe für jede Aufgabe: D2Schreiben Sie in „Zelle“ =SUMIF($B2:B$9, B2, $C2:C$9) >= 3und füllen Sie sie bis zur Zelle aus C9.

2. Suchen Sie das maximale Datum für jede Aufgabe, die wie TRUEin Schritt 1 markiert wurde: D11Schreiben Sie in „Zelle“ die folgende Array-Formel (und drücken Sie Ctrl+Shift+Enter):

   =MAX(WENN(($B$2:$B$9=B11)*($D$2:$D$9),$A$2:$A$9))

3. Füllen Sie es bis D13 auf.

Hinweis: Damit die Lösung funktioniert, müssen die Aufgaben in aufsteigender Reihenfolge nach Datum vorliegen.