Vender tilbage til det første og sidste punkt i en delmængde i Excel

Det kan være nyttigt at vide, hvor en liste begynder og slutter. F.eks. Sporer det enkle ark i figuren nedenfor tid. Du ønsker måske at bruge de første og sidste datoer på listen til at markere perioden start- og slutdatoer. Da datoerne er i rækkefølge, er det let at skelne den første dato på listen. Den sidste dato kan tage lidt mere indsats, hvis listen er lang. Du er nødt til at gennemse til den sidste post - ikke sådan en stor ting, ikke?

Men lad os komplicere situationen lidt. Antag, at du har brug for at kende den første og den sidste forløbne tidsindtastning for hver dato (der er din undergruppe). Det ændrer tingene ganske lidt - er det kompliceret nok?

Den første ting, du har brug for, er en unik liste over datoer. For at generere denne liste skal du bruge et avanceret filter som følger:

  1. Vælg datoer og headercelle i kolonne A - det er A1: A8 til dette eksempel.
  2. Vælg Filter | i menuen Data Avanceret filter.
  3. I den resulterende dialogboksen Avanceret filter skal du klikke på indstillingen Kopier til en anden placering. Indtast derefter G1 i kopien til kontrol. Sørg for at kontrollere indstillingen Kun unikke poster.
  4. Klik på OK, og Excel kopierer en liste med unikke værdier til kolonne G.

Nu har du brug for en formel, der returnerer det første element for hver dato. Denne formel er enkel, og du er måske bekendt med den. Jeg anbefaler VLOOKUP () i følgende form:

 VLOOKUP ( lookupvalue, tabel, columnoffset, exactmatch ) 

Indtast formlen i celle H2

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

I dette tilfælde er lookupvalue G2 - den værdi, du vil matche i tabellen . Når funktionen finder et match, returnerer den den tilsvarende værdi i tabellens fjerde kolonne. Det sidste argument er FALSE, der tvinger funktionen til kun at returnere et nøjagtigt match. Kopier formlen til celler H3: H5.

Desværre er denne funktion ikke fleksibel nok til også at returnere den sidste vare. Faktisk kan ingen af ​​opslags- og referencefunktionerne gøre det (ikke det, jeg ved om).

For at finde det sidste punkt i en undergruppe - den sidste værdi for forløbet tid for en bestemt dato - kombiner HLOOKUP () og MATCH (). Sandheden er, at jeg gennem årene har set mange indviklede formler til denne opgave. At kombinere disse to funktioner i følgende form er den mest effektive løsning, som jeg har fundet ud af:

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

Indtast følgende formel i celle I2 og kopier den til celler I3: I5:

 = HLOOKUP ("Forløbet tid", $ D $ 1: $ D $ 8, MATCH (G2, $ A $ 2: $ A $ 8) +1) 

Funktionen MATCH () returnerer den relative position for den værdi, der matcher opslagværdien. Når du for eksempel matcher værdien i G2, 6/2/2010, returnerer denne funktion 3 — den relative position inden for tabellen over den sidste matchende værdi. Funktionen HLOOKUP () bruger resultatet af funktionen MATCH () til at specificere rækken i htable, hvorfra den griber sin værdi, som følger:

 HLOOKUP ("Forløbet tid", $ D $ 1: $ D $ 8, MATCH (G2, $ A $ 2: $ A $ 8) +1) 
 HLOOKUP ("Forløbet tid", $ D $ 1: $ D $ 8, 3 + 1) 
 HLOOKUP ("Forløbet tid", $ D $ 1: $ D $ 8, 4) 

Som et resultat returnerer HLOOKUP () -funktionen værdien i den fjerde række af htable (D1: D8) - den sidste post for opslagningsdatoen 6/2/2010.

Hvis du har en mere effektiv metode til at finde den sidste vare i en undergruppe, skal du dele den!

© Copyright 2021 | pepebotifarra.com