I den här artikeln lär vi oss hur man indexerar och sorterar värden med hjälpkolumnen i Excel.
Scenario:
Ibland arbetar du med omänliga data. Vi måste få den kortare versionen som nödvändig data från tabelldata baserat på hjälpkolumnen. Här först kommer vi att förstå hur man får hjälpkolumnen och sedan få de sorterade små erforderliga data baserat på tabelldata.
Hur indexerar man värden inom intervallet?
För den här artikeln kommer vi att behöva använda COUNTIF -funktionen. Nu ska vi göra en formel av funktionen. Här ges text och nummer blandade värden i ett intervall. Vi måste indexera dem i stigande ordning först numeriskt och sedan alfabetiskt.
Generisk formel:-
| = COUNTIF (lista, "<=" & f_värde) + (COUNT (lista) * ISTEXT (f_value)) |
lista: lista med siffror och text
f_value: första värdet i intervallet
Förklaring:
- COUNTIF -funktionen räknar antalet värden i den returnerade listan
- COUNT -funktionen får antalet nummer i intervallet.
- ISTEXT -funktionen kontrollerar om värdet i den markerade cellen är text eller inte. Returnerar SANT för text och FALSKT för tal.
Exempel:
Allt detta kan vara förvirrande att förstå. Låt oss förstå hur man får hjälpkolumnen med hjälp av den förklarade formeln. Här ska hjälpkolumnen baseras på sorteringsvärdena i ordning (först siffror och sedan alfabatiskt). För detta kommer vi att använda formeln för att få index- eller rangkolumnen som hjälpkolumn för utgiftsdata som visas nedan.
Här hittar du värdena inom intervallet. För beräkningsändamål använder vi det angivna intervallet för den fasta matrisen D5: D12 som kostnad.
Använd formeln:
| = RÄKNING (utgift, "<=" & D5) + (RÄKNING (utgift) * ISTEXT (D5)) |
Formeln ser ut som den visas i ögonblicksbilden ovan. Värdet och matrisen ges som namngivet område och cellreferens i formeln.
Som du kan se kommer indexet för den första cellen inom intervallet att vara 5 vilket betyder att om vi placerar utgifterna i sorteringsordning, Elektricitet kommer att placeras på 5: e plats. Kopiera nu formeln i andra celler med hjälp av dra -ned -alternativet eller med genvägstangenten Ctrl + D som visas nedan för att få index eller rang för resten av värdena.
Som du kan se har vi nu en lista över index eller rangordningar som sorterar utgifterna. Nu kommer vi att använda denna Rank -kolumn som hjälpkolumn för att få den kortare versionen av tabellen med samma ordning i den nya listan eller tabellen.
Hur sorterar jag värden med hjälpkolumn i Excel?
Sorteringsnummer i excel med hjälpkolumnen måste ha indexnummer och nödvändigt område för att sortera. Hjälpkolumn kan vara vilken ordning som helst. Till exempel, om du behöver få listan i önskad slumpmässig ordning, placerar du bara den rang som motsvarar värdet i index- eller rangkolumnen som fungerar som hjälpkolumn.
- INDEX -funktion
- MATCH -funktion
- ROWS -funktion
Nu kommer vi att göra en formel med hjälp av ovanstående funktioner. MATCH -funktionen returnerar indexet för den lägsta matchningen från intervallet. INDEX -funktionen tar radindexet som ett argument och returnerar motsvarande erforderliga resultat. Denna funktion hittar
Generisk formel:
| = INDEX (retur_array, MATCH (RADER (relativ_referens), index, 0)) |
return_array: array att returnera värde från
Relativ referens: genererar en stigande ordning i formeln. Kan användas med vilken array som helst
Index: Tabellens indexmatris
0: exakt matchning
Förklaring:
- RADER (relativ_referens) returnerar ett värde enligt längden på den utökade formeln. Om den appliceras i den första cellen blir den en, sedan andra och fortsätter tills den sträcker sig.
- MATCH -funktionen matchar indexet med radvärdet för att få dem i stigande ordning med hjälp av ROWS -funktionen.
- INDEX -funktionen returnerar det matchade indexet med motsvarande värde.
Exempel:
Allt detta kan vara förvirrande att förstå. Så, låt oss förstå denna formel genom att köra den på exemplet som visas nedan. Här rankade vi uppgifterna i slumpmässig ordning för att få de tre första värdena baserat på indexkolumn. Använd formeln för att få det första värdet för den nödvändiga korta tabellen.
Använd formeln:
| = INDEX (B5: B13, MATCH (RADER (D5: D5), D5: D13,0)) |
Formeln ser ut som den visas i ögonblicksbilden ovan. Värdmatrisen ges som namngivet intervall och cellreferens.
Som du kan se är det första värdet att numret från intervallet visar sig vara "Mjölk" motsvarande index är 1. Kopiera nu formeln i andra celler med hjälp av alternativet dra ner eller med genvägstangenten Ctrl + D som visas nedan för att få resten av värdena.
Som du kan se värden indexeras i stigande ordning. Vi fick alla värden eftersom det också visar att formeln fungerar bra. Vi kan extrahera talvärdena med formeln samma formel.
Som du kan se har vi de kortare och sorterade värdena från tabellen med hjälpkolumnen som indexeras därefter.
Här är några observationsanteckningar som visas nedan.
Anmärkningar:
- Formeln fungerar bara med siffror och text båda.
- Formeln ignorerar textvärde medan tal jämförs och ignorerar siffror när de matchar textvärden.
- Hjälpkolumn kan vara vilken typ av önskad kolumnordning som helst.
Hoppas att den här artikeln om hur man indexerar och sorterar värden med hjälparkolumn i Excel är förklarande. Utforska fler artiklar om uppslagsformler i Excel här. Om du gillade våra bloggar, dela den med dina vänner på Facebook. Och du kan också följa oss på Twitter och Facebook. Vi vill gärna höra från dig, låt oss veta hur vi kan förbättra, komplettera eller förnya vårt arbete och göra det bättre för dig. Skriv till oss på e -postwebbplatsen
Så här använder du SUMPRODUCT -funktionen i Excel: Returnerar SUMMEN efter multiplikation av värden i flera matriser i excel.
SUMMA om datumet är mellan : Returnerar summan av värden mellan givna datum eller period i excel.
Summa om datum är större än angivet datum: Returnerar summan av värden efter det angivna datumet eller perioden i excel.
2 sätt att summera per månad i Excel: Returnerar summan av värden inom en viss specifik månad i excel.
Hur man summerar flera kolumner med villkor: Returnerar SUMMAN av värden över flera kolumner med villkor i excel
Hur man använder jokertecken i excel : Räkna celler som matchar fraser med jokertecken i excel
Populära artiklar
50 Excel -genväg för att öka din produktivitet : Bli snabbare på din uppgift. Dessa 50 genvägar får dig att arbeta ännu snabbare med Excel.
Hur man använder than VLOOKUP -funktion i Excel : Detta är en av de mest använda och populära Excel -funktionerna som används för att leta upp värde från olika intervall och ark.
Så här använder du funktionen COUNTIF i Excel : Räkna värden med villkor med denna fantastiska funktion. Du behöver inte filtrera dina data för att räkna specifika värden. Countif -funktionen är avgörande för att förbereda din instrumentpanel.
Hur man använder SUMIF -funktionen i Excel : Detta är en annan viktig instrumentpanel. Detta hjälper dig att summera värden på specifika förhållanden.