☰

Excel: Suchen Sie die frühesten/neuesten Werte für jedes Datum in einer Liste mit Datums- und Uhrzeitangaben

tag-icon • tag-icon microsoft-excel microsoft-excel-2016
Excel: Suchen Sie die frühesten/neuesten Werte für jedes Datum in einer Liste mit Datums- und Uhrzeitangaben

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“?

VERWANDTE ANFRAGE

2. MIN/ MAXCSE-Rezept

BEISPIELBILD

MINfü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 Cdem FÜbereinstimmungen mit Aund GÜbereinstimmungen vorliegen B.

Damit erhält man allerdings nur die minimalen IN-Werte. Durch das Tauschen MAXin MINjeder 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

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:

  1. Passen Sie die Zellbezüge nach Bedarf an
  2. 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.
  3. 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.
  4. 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!
  5. 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.

verwandte Informationen