Hvordan indeksere og sortere verdier ved hjelp av hjelperkolonnen i Excel.

Anonim

I denne artikkelen lærer vi hvordan du indekserer og sorterer verdier ved hjelp av hjelperkolonnen i Excel.

Scenario:

Noen ganger jobber vi med umenneskelige data. Vi må få den kortere versjonen som nødvendig data fra tabelldataene basert på hjelperkolonnen. Her først vil vi forstå hvordan du får hjelpekolonnen og deretter få de sorterte små nødvendige dataene basert på tabelldataene.

Hvordan indeksere verdier i området?

For denne artikkelen må vi bruke COUNTIF -funksjonen. Nå skal vi lage en formel av funksjonen. Her gis tekst og tall blandede verdier i et område. Vi må indeksere dem i stigende rekkefølge først numerisk og deretter alfabetisk.

Generisk formel:-

= COUNTIF (liste, "<=" & f_verdi) + (COUNT (liste) * ISTEXT (f_value))

liste: liste over tall og tekst

f_value: første verdi i området

Forklaring:

  1. COUNTIF -funksjonen teller antall verdier i returnert liste
  2. COUNT -funksjonen får antallet tall i området.
  3. ISTEXT -funksjonen sjekker om verdien i den valgte cellen er tekst eller ikke. Returnerer TRUE for tekst og FALSE for tall.

Eksempel:

Alt dette kan være forvirrende å forstå. La oss forstå hvordan du får hjelpekolonnen ved hjelp av den forklarte formelen. Her skal hjelpekolonnen baseres på sorteringsverdiene i rekkefølge (først tall og deretter alfabatisk). For dette vil vi bruke formelen for å få indeksen eller rangeringskolonnen som hjelperkolonne for utgiftsdataene vist nedenfor.

Her finner du verdiene i området. For beregningsformål bruker vi det navngitte området for den faste matrisen D5: D12 som kostnad.

Bruk formelen:

= COUNTIF (utgift, "<=" & D5) + (COUNT (utgift) * ISTEXT (D5))


Formelen ser ut som vist i øyeblikksbildet ovenfor. Verdien og matrisen er gitt som navngitt område og cellereferanse i formelen.


Som du kan se, kommer indeksen for den første cellen i området til å være 5, noe som betyr at hvis vi plasserer utgiftene i sorteringsrekkefølge, Elektrisitet blir plassert på 5. plass. Kopier nå formelen i andre celler ved å dra ned -alternativet eller bruke hurtigtasten Ctrl + D som vist nedenfor for å få indeksen eller rangeringen for resten av verdiene.

Som du kan se, har vi nå en liste over indeks eller rangering som sorterer utgiftene. Nå vil vi bruke denne Rang -kolonnen som hjelperkolonne for å få den kortere versjonen av tabellen som har samme rekkefølge i den nye listen eller tabellen.

Hvordan sorterer du verdier ved hjelp av hjelperkolonne i Excel?

Sorter tall i excel ved hjelp av hjelperkolonnen må ha indeksnummer og nødvendig område for å sortere. Hjelpekolonne kan være hvilken som helst nødvendig rekkefølge. For eksempel, hvis du trenger å få listen i nødvendig tilfeldig rekkefølge, må du bare plassere rangeringen som tilsvarer verdien i indeksen eller rangekolonnen som vil fungere som hjelperkolonne.

  1. INDEX -funksjon
  2. MATCH -funksjon
  3. ROWS -funksjon

Nå skal vi lage en formel ved å bruke funksjonene ovenfor. MATCH -funksjonen returnerer indeksen for den laveste matchen fra området. INDEKS -funksjonen tar radindeksen som et argument og returnerer de tilsvarende nødvendige resultatene. Denne funksjonen finner

Generisk formel:

= INDEX (return_array, MATCH (ROWS (relative_reference), Index, 0))

return_array: array å returnere verdi fra

Relativ referanse: genererer en stigende rekkefølge i formelen. Kan brukes med alle matriser

Indeks: Indeksmatrise av tabellen

0: eksakt match

Forklaring:

  1. RADER (relativ_referanse) returnerer en verdi i henhold til lengden på formelen som er utvidet. Hvis den brukes i den første cellen, vil den være én, så andre og fortsette til den strekker seg.
  2. MATCH -funksjonen matcher indeksen med radverdi for å få dem i stigende rekkefølge ved hjelp av ROWS -funksjonen.
  3. INDEX -funksjonen returnerer den samsvarende indeksen med tilsvarende verdi.

Eksempel:

Alt dette kan være forvirrende å forstå. Så la oss forstå denne formelen ved å kjøre den på eksemplet vist nedenfor. Her rangerte vi dataene i tilfeldig rekkefølge for å få de tre første verdiene basert på indeks -kolonnen. Bruk formelen for å få den første verdien av den nødvendige korte tabellen.

Bruk formelen:

= INDEKS (B5: B13, MATCH (RADER (D5: D5), D5: D13,0))

Formelen ser ut som vist i øyeblikksbildet ovenfor. Verdimatasjen er gitt som navngitt område og cellereferanse.

Som du kan se, er den første verdien tallet fra området "Milk", tilsvarende indeks er 1. Kopier nå formelen i andre celler ved å dra ned -alternativet eller bruk hurtigtasten Ctrl + D som vist nedenfor for å få resten av verdiene.

Som du kan se er verdier indeksert i stigende rekkefølge. Vi oppnådde alle verdiene, da det også viser at formelen fungerer bra. Vi kan trekke ut tallverdiene ved å bruke formelen den samme formelen.

Som du kan se har vi de kortere og sorterte verdiene fra tabellen ved hjelp av hjelperkolonnen som blir indeksert tilsvarende.

Her er noen observasjonsnotater vist nedenfor.

Merknader:

  1. Formelen fungerer bare med tall og tekst begge deler.
  2. Formelen ignorerer tekstverdi mens tall sammenlignes og ignorerer tall når tekstverdier samsvarer.
  3. Hjelpekolonne kan være hvilken som helst type nødvendig kolonnrekkefølge.

Håper denne artikkelen om Hvordan indeksere og sortere verdier med hjelperkolonne i Excel er forklarende. Utforsk flere artikler om oppslagsformler i Excel her. Hvis du likte bloggene våre, del den med vennene dine på Facebook. Og du kan også følge oss på Twitter og Facebook. Vi vil gjerne høre fra deg, gi oss beskjed om hvordan vi kan forbedre, utfylle eller innovere arbeidet vårt og gjøre det bedre for deg. Skriv til oss på e -post

Slik bruker du SUMPRODUCT -funksjonen i Excel: Returnerer SUMMEN etter multiplikasjon av verdier i flere matriser i excel.

SUM hvis datoen er mellom : Returnerer SUMMEN av verdier mellom gitte datoer eller periode i excel.

Sum hvis datoen er større enn gitt dato: Returnerer SUMMEN av verdier etter gitt dato eller periode i excel.

2 måter å summere etter måned i Excel: Returnerer SUMMEN av verdier i en gitt spesifikk måned i excel.

Slik summerer du flere kolonner med betingelse: Returnerer SUM av verdier på tvers av flere kolonner som har betingelse i excel

Hvordan bruke jokertegn i excel : Tell celler som matcher setninger ved hjelp av jokertegnene i excel

Populære artikler

50 Excel -snarvei for å øke produktiviteten : Bli raskere på oppgaven din. Disse 50 snarveiene får deg til å jobbe enda raskere med Excel.

Slik bruker du tVLOOKUP -funksjonen i Excel : Dette er en av de mest brukte og populære funksjonene i excel som brukes til å slå opp verdi fra forskjellige områder og ark.

Slik bruker du COUNTIF -funksjonen i Excel : Tell verdier med betingelser ved hjelp av denne fantastiske funksjonen. Du trenger ikke å filtrere dataene dine for å telle spesifikke verdier. Countif -funksjonen er avgjørende for å forberede dashbordet.

Slik bruker du SUMIF -funksjonen i Excel : Dette er en annen viktig funksjon i instrumentbordet. Dette hjelper deg med å oppsummere verdier på spesifikke forhold.