Rückgabe des ersten und letzten Elements in einer Teilmenge in Excel

Zu wissen, wo eine Liste beginnt und endet, kann hilfreich sein. Zum Beispiel verfolgt das einfache Blatt in der Abbildung unten die Zeit. Möglicherweise möchten Sie das erste und das letzte Datum in der Liste verwenden, um das Anfangs- und Enddatum des Zeitraums zu markieren. Da die Daten in sequenzieller Reihenfolge vorliegen, ist es einfach, das erste Datum in der Liste zu erkennen. Das letzte Datum kann etwas länger dauern, wenn die Liste lang ist. Sie müssen zum letzten Eintrag navigieren - keine so große Sache, oder?

Aber lassen Sie uns die Situation etwas komplizieren. Angenommen, Sie müssen den ersten und den letzten Eintrag für die verstrichene Zeit für jedes Datum kennen (es gibt Ihre Teilmenge). Das ändert die Dinge ziemlich - ist das kompliziert genug?

Das erste, was Sie brauchen, ist eine eindeutige Liste von Daten. Verwenden Sie zum Generieren dieser Liste einen erweiterten Filter wie folgt:

  1. Wählen Sie die Datums- und Kopfzeilenzelle in Spalte A aus - in diesem Beispiel A1: A8.
  2. Wählen Sie im Menü Daten die Option Filter | Erweiterter Filter.
  3. Klicken Sie im daraufhin angezeigten Dialogfeld Erweiterter Filter auf die Option An einen anderen Speicherort kopieren. Geben Sie dann G1 in das Steuerelement Kopieren nach ein. Stellen Sie sicher, dass die Option Nur eindeutige Datensätze aktiviert ist.
  4. Klicken Sie auf OK, und Excel kopiert eine Liste eindeutiger Werte in Spalte G.

Jetzt benötigen Sie eine Formel, die für jedes Datum den ersten Eintrag zurückgibt. Diese Formel ist einfach und Sie kennen sie vielleicht. Ich empfehle VLOOKUP () in folgender Form:

 VLOOKUP ( Lookup-Wert, Tabelle, Spaltenoffset, Exactmatch ) 

Geben Sie in Zelle H2 die Formel ein

 = VLOOKUP (G2, A2: D8, 4, FALSE) 

In diesem Fall ist der Suchwert G2 - der Wert, den Sie in der Tabelle abgleichen möchten. Wenn die Funktion eine Übereinstimmung findet, gibt sie den entsprechenden Wert in der vierten Spalte der Tabelle zurück . Das letzte Argument ist FALSE, wodurch die Funktion gezwungen wird, nur eine genaue Übereinstimmung zurückzugeben. Kopieren Sie die Formel in die Zellen H3: H5.

Leider ist diese Funktion nicht flexibel genug, um auch den letzten Artikel zurückzugeben. Tatsächlich kann keine der Such- und Referenzfunktionen dies (nicht das, von dem ich weiß).

Um das letzte Element in einer Teilmenge zu finden - den letzten Wert für die verstrichene Zeit für ein bestimmtes Datum - kombinieren Sie HLOOKUP () und MATCH (). Die Wahrheit ist, dass ich im Laufe der Jahre viele verschlungene Formeln für diese Aufgabe gesehen habe. Die Kombination dieser beiden Funktionen in der folgenden Form ist die effizienteste Lösung, die ich mir ausgedacht habe:

 HLOOKUP ( hlookupvalue, htable, MATCH ( lookupvalue, table ) +1) 

Geben Sie die folgende Formel in Zelle I2 ein und kopieren Sie sie in die Zellen I3: I5:

 = HLOOKUP ("Verstrichene Zeit", $ D $ 1: $ D $ 8, MATCH (G2, $ A $ 2: $ A $ 8) +1) 

Die Funktion MATCH () gibt die relative Position des Werts zurück, die dem Suchwert entspricht. Wenn Sie beispielsweise den Wert in G2 vom 02.06.2010 abgleichen, gibt diese Funktion 3 zurück - die relative Position innerhalb der Tabelle des letzten übereinstimmenden Werts. Die Funktion HLOOKUP () verwendet das Ergebnis der Funktion MATCH (), um die Zeile in der Tabelle anzugeben, aus der sie ihren Wert wie folgt abruft :

 HLOOKUP ("Verstrichene Zeit", $ D $ 1: $ D $ 8, MATCH (G2, $ A $ 2: $ A $ 8) +1) 
 HLOOKUP ("Verstrichene Zeit", $ D $ 1: $ D $ 8, 3 + 1) 
 HLOOKUP ("Verstrichene Zeit", $ D $ 1: $ D $ 8, 4) 

Infolgedessen gibt die Funktion HLOOKUP () den Wert in der vierten Zeile der Tabelle (D1: D8) zurück - den letzten Eintrag für das Suchdatum vom 02.06.2010.

Wenn Sie eine effizientere Methode zum Auffinden des letzten Elements in einer Teilmenge haben, teilen Sie diese bitte mit!

© Copyright 2020 | mobilegn.com