Returnerer det første og siste elementet i en undergruppe i Excel

Det kan være nyttig å vite hvor en liste begynner og slutter. For eksempel sporer det enkle arket i figuren nedenfor tid. Det kan være lurt å bruke de første og siste datoene i listen til å markere perioden begynnelses- og sluttdatoer. Siden datoene er i rekkefølge, er det lett å skille den første datoen i listen. Den siste datoen kan ta litt mer krefter hvis listen er lang. Du må bla til den siste oppføringen - ikke så stor sak, ikke sant?

Men la oss komplisere situasjonen litt. Anta at du trenger å kjenne til den første og den siste forløpte tidsoppføringen for hver dato (det er ditt underett). Det endrer ting ganske mye - er det komplisert nok?

Det første du trenger er en unik liste over datoer. Hvis du vil generere denne listen, bruker du et avansert filter på følgende måte:

  1. Velg datoer og toppcelle i kolonne A - det er A1: A8 for dette eksempelet.
  2. Fra Filter-menyen velger du Filter | Avansert filter.
  3. I dialogboksen Avansert filter klikker du alternativet Kopier til et annet sted. Deretter skriver du inn G1 i Copy To-kontrollen. Sørg for å merke av for Unique Records Only-alternativet.
  4. Klikk OK, og Excel kopierer en liste over unike verdier til kolonne G.

Nå trenger du en formel som returnerer det første elementet for hver dato. Denne formelen er enkel, og du er kanskje kjent med den. Jeg anbefaler VLOOKUP (), i følgende skjema:

 VLOOKUP ( lookupvalue, table, columnoffset, exactmatch ) 

Skriv inn formelen i celle H2

 = FINN.RAD (G2, A2: D8, 4, FALSE) 

I dette tilfellet er oppslagsverdien G2 - verdien du vil matche i tabellen . Når funksjonen finner en kamp, ​​returnerer den tilsvarende verdi i tabellens fjerde kolonne. Det siste argumentet er FALSE, som tvinger funksjonen til å returnere bare en eksakt samsvar. Kopier formelen til cellene H3: H5.

Dessverre er denne funksjonen ikke fleksibel nok til også å returnere den siste varen. Faktisk kan ingen av oppslag- og referansefunksjonene gjøre det (ikke det jeg vet om).

For å finne det siste elementet i et delsett - den siste forløpne tidsverdien for en spesifikk dato - kombiner HLOOKUP () og MATCH (). Sannheten er at jeg gjennom årene har sett mange sammensveisede formler for denne oppgaven. Å kombinere disse to funksjonene i følgende form er den mest effektive løsningen som jeg har kommet frem til:

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

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

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

MATCH () -funksjonen returnerer den relative posisjonen til verdien som samsvarer med oppslagverdien. Når du for eksempel samsvarer med verdien i G2, 6/2/2010, returnerer denne funksjonen 3 — den relative posisjonen i tabellen for den siste samsvarende verdien. HLOOKUP () -funksjonen bruker resultatet av MATCH () -funksjonen for å spesifisere raden i htable, hvorfra den henter verdien, som følger:

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

Som et resultat returnerer HLOOKUP () -funksjonen verdien i den fjerde raden med htable (D1: D8) - den siste oppføringen for oppslagdatoen 6/2/2010 .

Hvis du har en mer effektiv metode for å finne den siste varen i en undergruppe, kan du dele den!

© Copyright 2020 | mobilegn.com