Sådan indekseres og sorteres værdier ved hjælp af hjælperkolonnen i Excel.

Anonim

I denne artikel lærer vi, hvordan du indekserer og sorterer værdier ved hjælp af hjælperkolonnen i Excel.

Scenarie:

Nogle gange arbejder man med umenneskelige data. Vi skal hente den kortere version som krævede data fra tabeldataene baseret på hjælperkolonnen. Her først vil vi forstå, hvordan man får hjælperkolonnen og derefter få de sorterede små nødvendige data baseret på tabeldataene.

Hvordan indekseres værdier inden for rækkevidde?

Til denne artikel bliver vi forpligtet til at bruge funktionen TÆLLE. Nu laver vi en formel ud af funktionen. Her givet tekst og tal blandede værdier i et område. Vi skal indeksere dem i stigende rækkefølge først numerisk og derefter alfabetisk.

Generisk formel:-

= COUNTIF (liste, "<=" & f_værdi) + (COUNT (liste) * ISTEXT (f_værdi))

liste: liste over tal og tekst

f_value: første værdi af intervallet

Forklaring:

  1. COUNTIF funktion tæller antallet af værdier i returneret liste
  2. COUNT funktion får antallet af tal i området.
  3. ISTEXT -funktionen kontrollerer, om værdien i den markerede celle er tekst eller ej. Returnerer SAND for tekst og FALSK for tal.

Eksempel:

Alt dette kan være forvirrende at forstå. Lad os forstå, hvordan du får hjælperkolonnen ved hjælp af den forklarede formel. Her skal hjælperkolonnen være baseret på sorteringsværdierne i rækkefølge (først tal og derefter alfabatisk). Til dette vil vi bruge formlen til at få indekset eller rangkolonnen som hjælperkolonne for udgiftsdataene vist nedenfor.

Her finder du værdierne inden for rækkevidde. Til beregningsformål bruger vi det navngivne område til det faste array D5: D12 som omkostning.

Brug formlen:

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


Formlen ser ud som vist i ovenstående snapshot. Værdien og array er angivet som navngivet område og cellereference i formlen.


Som du kan se, kommer indekset for den første celle i området til at være 5, hvilket betyder, at hvis vi placerer udgifterne i sorteringsrækkefølge, Elektricitet vil blive placeret på 5. position. Kopier nu formlen i andre celler ved hjælp af træk -ned -indstillingen eller ved hjælp af genvejstasten Ctrl + D som vist nedenfor for at få indekset eller rangeringen for resten af ​​værdierne.

Som du kan se, har vi nu en liste over indeks eller rang, som sorterer udgifternes måde. Nu vil vi bruge denne Rang -kolonne som hjælperkolonne for at få den kortere version af tabellen med den samme rækkefølge i den nye liste eller tabel.

Sådan sorteres værdier ved hjælp af hjælperkolonne i Excel?

Sorter nummer i excel ved hjælp af hjælperkolonnen skal have indeksnumre og det nødvendige område for at sortere. Hjælperkolonne kan være enhver påkrævet rækkefølge. For eksempel, hvis du har brug for at få listen i den krævede tilfældige rækkefølge, skal du blot placere rangen, der svarer til værdien i indekset eller rangkolonnen, som fungerer som hjælperkolonne.

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

Nu laver vi en formel ved hjælp af ovenstående funktioner. MATCH -funktionen returnerer indekset for den laveste match fra intervallet. INDEX -funktionen tager rækkeindekset som et argument og returnerer de tilsvarende krævede resultater. Denne funktion finder

Generisk formel:

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

return_array: array at returnere værdi fra

Relativ reference: genererer en stigende rækkefølge i formlen. Kan bruges med enhver matrix

Indeks: Indeksmatrix af tabellen

0: eksakt match

Forklaring:

  1. RÆKER (relativ_reference) returnerer en værdi i henhold til længden af ​​den udvidede formel. Hvis den anvendes i den første celle, vil den være en, derefter anden og fortsætte, indtil den strækker sig.
  2. MATCH -funktion matcher indekset med rækkeværdi for at få dem i stigende rækkefølge ved hjælp af ROWS -funktionen.
  3. INDEX -funktionen returnerer det matchede indeks med tilsvarende værdi.

Eksempel:

Alt dette kan være forvirrende at forstå. Så lad os forstå denne formel ved at køre den på eksemplet nedenfor. Her rangerede vi dataene i tilfældig rækkefølge for at få de første tre værdier baseret på Indeks -kolonne. Brug formlen til at få den første værdi af den nødvendige korte tabel.

Brug formlen:

= INDEKS (B5: B13, MATCH (RÆKKER (D5: D5), D5: D13,0))

Formlen ser ud som vist i ovenstående snapshot. Værdiarrayet er givet som navngivet område og cellereference.

Som du kan se, er den første værdi, at tallet fra intervallet viser sig at være "Mælk", tilsvarende indeks er 1. Kopier nu formlen i andre celler ved hjælp af træk -ned -indstillingen eller brug genvejstasten Ctrl + D som vist nedenfor for at få resten af ​​værdierne.

Som du kan se, er værdier indekseret i stigende rækkefølge. Vi opnåede alle værdierne, da det også viser, at formlen fungerer fint. Vi kan udtrække talværdierne ved hjælp af formlen den samme formel.

Som du kan se, har vi de kortere og sorterede værdier fra tabellen ved hjælp af hjælperkolonnen, som indekseres i overensstemmelse hermed.

Her er nogle observationsnoter vist nedenfor.

Bemærkninger:

  1. Formlen fungerer kun med tal og tekst begge.
  2. Formlen ignorerer tekstværdi, mens tal sammenlignes, og tal ignoreres, når tekstværdier matcher.
  3. Hjælperkolonne kan være enhver type krævet rækkefølge af kolonne.

Håber denne artikel om Sådan indekseres og sorteres værdier med hjælperkolonne i Excel er forklarende. Udforsk flere artikler om opslagsformler i Excel her. Hvis du kunne lide vores blogs, kan du dele den med dine venner på Facebook. Og også du kan følge os på Twitter og Facebook. Vi vil meget gerne høre fra dig, lad os vide, hvordan vi kan forbedre, supplere eller innovere vores arbejde og gøre det bedre for dig. Skriv til os på e -mail -stedet

Sådan bruges funktionen SUMPRODUCT i Excel: Returnerer SUMMEN efter multiplikation af værdier i flere arrays i excel.

SUM, hvis datoen er mellem : Returnerer SUM af værdier mellem givne datoer eller periode i excel.

Sum, hvis datoen er større end den givne dato: Returnerer SUM af værdier efter den givne dato eller periode i excel.

2 måder at summe efter måned i Excel: Returnerer SUM af værdier inden for en given specifik måned i excel.

Sådan summeres flere kolonner med betingelse: Returnerer SUM af værdier på tværs af flere kolonner med betingelse i excel

Sådan bruges jokertegn i excel : Tæl celler, der matcher sætninger ved hjælp af jokertegnene i excel

Populære artikler

50 Excel -genvej til at øge din produktivitet : Bliv hurtigere til din opgave. Disse 50 genveje får dig til at arbejde endnu hurtigere i Excel.

Sådan bruges than VLOOKUP -funktion i Excel : Dette er en af ​​de mest anvendte og populære funktioner i excel, der bruges til at slå værdi op fra forskellige områder og ark.

Sådan bruges funktionen COUNTIF i Excel : Tæl værdier med betingelser ved hjælp af denne fantastiske funktion. Du behøver ikke at filtrere dine data for at tælle bestemte værdier. Countif -funktion er afgørende for at forberede dit dashboard.

Sådan bruges SUMIF -funktionen i Excel : Dette er en anden vigtig instrumentbrætfunktion. Dette hjælper dig med at opsummere værdier på bestemte betingelser.