
Ich habe eine Liste mit aufeinanderfolgenden Ankunfts- und Abfahrtszeiten wie diese:
| IN/OUT | DATE/TIME |
|--------|-----------------------|
| IN | 1/2/2019 8:25:59 AM |
| OUT | 1/2/2019 11:51:59 AM |
| IN | 1/2/2019 12:48:59 PM |
| OUT | 1/2/2019 4:45:59 PM |
| IN | 1/3/2019 9:08:59 AM |
| OUT | 1/3/2019 12:38:59 PM |
| IN | 1/3/2019 3:23:59 PM |
| OUT | 1/3/2019 4:59:59 PM |
| IN | 1/4/2019 8:49:59 AM |
| OUT | 1/4/2019 5:11:59 PM |
Aus dieser Liste muss ich dieErsteUndzuletztEinträge für jeden Tag (die dann zur Berechnung der durchschnittlichen Ankunfts- und Abfahrtszeiten, Dauer usw. verwendet werden).
Aus der obigen Tabelle möchte ich beispielsweise Folgendes extrahieren:
| IN/OUT | DATE/TIME |
|--------|-----------------------|
| IN | 1/2/2019 8:25:59 AM |
| OUT | 1/2/2019 4:45:59 PM |
| IN | 1/3/2019 9:08:59 AM |
| OUT | 1/3/2019 4:59:59 PM |
| IN | 1/4/2019 8:49:59 AM |
| OUT | 1/4/2019 5:11:59 PM |
Ich weiß, dass ich mit SVERWEIS alle 'IN'- oder 'OUT'-Werte abrufen kann, aber das Problem ist, dass die meisten Tage in der Liste mehr als einen haben. Ich brauche nur den ersten und den letzten, aber ich bin nicht sicher, wie das geht.
Gibt es eine Möglichkeit, dies mit SVERWEIS zu erreichen, mit oder ohne Hilfstabelle? Oder funktioniert vielleicht eine andere Methode besser?
Antwort1
1. Denken Sie daran, zu suchen, bevor Sie fragen
Verwenden von MAX() in Excel für eine Liste von Daten unter Verwendung mehrerer Kriterien
Siehe unter „Verwandte Themen“?
2. MIN
/ MAX
CSE-Rezept
MIN
für in':
=MIN(IF($A$2:$A$99=F2,IF($B$2:$B$99=G2,$C$2:$C$99)))
Dies ist eine sogenannte Array- oder „CSE“-Formel. Geben Sie sie in die Formelleiste ein, drücken Sie dann , Ctrl Shift Enterum sie als Array-Formel einzugeben, und kopieren Sie sie anschließend nach unten.
Das ist doch intuitiv nachvollziehbar, oder? Holen Sie sich den niedrigsten Wert, bei C
dem F
Übereinstimmungen mit A
und G
Übereinstimmungen vorliegen B
.
Damit erhält man allerdings nur die minimalen IN-Werte. Durch das Tauschen MAX
in MIN
jeder zweiten Zeile erhält man die maximalen OUT-Werte. Aber wer hat schon Zeit dafür? Lassen Sie uns sie mit einem einfachen zusammenfügen IF
:
3. ENDGÜLTIGE FORMEL
=IF(F2="IN",MIN(IF($A$2:$A$99=F2,IF($B$2:$B$99=G2,$C$2:$C$99))),MAX(IF($A$2:$A$99=F2,IF($B$2:$B$99=G2,$C$2:$C$99))))
Geben Sie diese Formel erneut mit F2 ein, drücken Sie Ctrl Shift Enterund kopieren Sie sie dann nach unten.
Hinweis:
- Passen Sie die Zellbezüge nach Bedarf an
- Sie haben nicht viel explizit zur Form Ihrer Dateneingaben gesagt. Ich habe die einzelnen Argumente mithilfe der Text-in-Spalten-Funktion von Excel getrennt, sodass ich Datum und Uhrzeit unabhängig voneinander bearbeiten konnte.
- Es ist KEINE Fehlersuche enthalten und die Lösung geht davon aus, dass Sie Ihre gewünschten Testwerte bereits wie gezeigt festgelegt haben. Es sollte einfach sein, Ihre Eingabespalten IN/OUT und DATE zu kopieren und mit „Duplikate entfernen“ Ihre eindeutige Liste entsprechend zu generieren.
- Für die Spalte ist keine spezielle Formatierung vorhanden
C
. Es ist einfacher Text, wie gezeigt.Sie müssen wahrscheinlich den Format-Pinsel verwenden(aus dieser Spalte)in Ihrer endgültigen Ausgabespalte! - Ignorieren Sie meine Spalte
D
, die ich beim Zusammenstellen nur zur Fehlerprüfung verwendet und vor dem Aufnehmen der Screenshots vergessen habe zu löschen.