Kako indeksirati in razvrstiti vrednosti s pomožnim stolpcem v Excelu.

V tem članku se bomo naučili, kako indeksirati in razvrstiti vrednosti s pomožnim stolpcem v Excelu.

Scenarij:

Včasih delo z neprimernimi podatki. Krajšo različico moramo pridobiti kot zahtevane podatke iz podatkov tabele na podlagi stolpca pomočnikov. Tu bomo najprej razumeli, kako pridobiti pomožni stolpec, nato pa na podlagi podatkov tabele dobili razvrščene majhne zahtevane podatke.

Kako indeksirati vrednosti v razponu?

Za ta članek bomo morali uporabiti funkcijo COUNTIF. Zdaj bomo iz funkcije naredili formulo. Tukaj podane vrednosti besedila in številk v razponu. Indeksirati jih moramo v naraščajočem vrstnem redu najprej številčno, nato abecedno.

Splošna formula:-

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

seznam: seznam številk in besedila

f_value: prva vrednost obsega

Pojasnilo:

1. Funkcija COUNTIF šteje število vrednosti na vrnjenem seznamu
2. Funkcija COUNT prešteje število številk v razponu.
3. Funkcija ISTEXT preveri, ali je vrednost v izbrani celici besedilo ali ne. Vrne TRUE za besedilo in FALSE za številke.

Primer:

Vse to je lahko zmedeno za razumevanje. Razumejmo, kako dobiti pomožni stolpec po razloženi formuli. Tu stolpec pomočnika temelji na razvrščanju vrednosti po vrstnem redu (najprej številke in nato po abecednem redu). Za to bomo uporabili formulo, da dobimo stolpec Indeks ali rang kot pomožni stolpec za spodaj prikazane podatke o stroških.

Tukaj poiščite vrednosti v razponu. Za namene izračuna uporabljamo imenovano območje za fiksno matriko D5: D12 kot strošek.

Uporabite formulo:

= COUNTIF (stroški, "<=" & D5) + (COUNT (odhodki) * ISTEXT (D5))

Formula izgleda tako, kot je prikazano na zgornjem posnetku. Vrednost in matrika sta v formuli podana kot imenovani obseg in referenca celice.

Kot lahko vidite, je indeks prve celice v razponu 5, kar pomeni, da če stroške postavimo v vrstni red razvrščanja, Elektrika bo uvrščena na peto mesto. Zdaj kopirajte formulo v druge celice z možnostjo povleci navzdol ali s bližnjico Ctrl + D, kot je prikazano spodaj, da dobite indeks ali uvrstitev za preostale vrednosti.

Kot lahko vidite, imamo zdaj seznam indeksa ali ranga, ki je način razvrščanja stroškov. Zdaj bomo ta stolpec Rank uporabili kot pomožni stolpec, da dobimo krajšo različico tabele z enakim vrstnim redom na novem seznamu ali tabeli.

Kako razvrstiti vrednosti s pomožnim stolpcem v Excelu?

Številka razvrščanja v Excelu s pomožnim stolpcem mora vsebovati indeksne številke in zahtevani obseg za razvrščanje. Stolpec za pomoč je lahko katero koli zahtevano naročilo. Na primer, če želite seznam dobiti v zahtevanem naključnem vrstnem redu, le uvrstite rang, ki ustreza vrednosti, v stolpec indeksa ali uvrstitve, ki bo deloval kot stolpec pomočnik.

1. INDEX funkcija
2. Funkcija MATCH
3. Funkcija ROWS

Zdaj bomo naredili formulo z uporabo zgornjih funkcij. Funkcija MATCH vrne indeks najnižjega ujemanja iz območja. Funkcija INDEX vzame indeks vrstice kot argument in vrne ustrezne zahtevane rezultate. Ta funkcija najde

Splošna formula:

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

return_array: matrika, iz katere se vrne vrednost

Relativna referenca: v formuli ustvari naraščajoči vrstni red. Lahko se uporablja s katerim koli nizom

Indeks: indeksni niz tabele

0: natančno ujemanje

Pojasnilo:

1. ROWS (relative_reference) vrne vrednost glede na dolžino razširjene formule. Če ga uporabite v prvi celici, bo ena, nato druga in nadaljujte, dokler se ne razširi.
2. Funkcija MATCH se ujema z indeksom z vrednostjo vrstice, da jih dobite v naraščajočem vrstnem redu s funkcijo ROWS.
3. Funkcija INDEX vrne ujemajoči se indeks z ustrezno vrednostjo.

Primer:

Vse to je lahko zmedeno za razumevanje. Torej, razumejmo to formulo tako, da jo zaženemo na spodnjem primeru. Tu smo podatke razvrstili v naključnem vrstnem redu, da smo dobili prve tri vrednosti na podlagi stolpca Indeks. S formulo dobite prvo vrednost zahtevane kratke tabele.

Uporabite formulo:

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

Formula izgleda tako, kot je prikazano na zgornjem posnetku. Niz vrednosti je podan kot imenovani obseg in referenca celice.

Kot lahko vidite, je prva vrednost številka iz obsega, ki je "Mleko", ustrezen indeks pa je 1. Sedaj kopirajte formulo v druge celice z možnostjo povleci navzdol ali uporabite bližnjico Ctrl + D, kot je prikazano spodaj, da dobite ostale vrednosti.

Kot lahko vidite, so vrednosti indeksirane v naraščajočem vrstnem redu. Dobili smo vse vrednosti, saj tudi dokazuje, da formula dobro deluje. Številčne vrednosti lahko izvlečemo po formuli po isti formuli.

Kot lahko vidite, imamo krajše in razvrščene vrednosti iz tabele s pomožnim stolpcem, ki je ustrezno indeksiran.

Spodaj je nekaj opazovalnih opomb.

Opombe:

1. Formula deluje samo s številkami in besedilom.
2. Formula prezre vrednost besedila med primerjavo številk in prezre številke, ko se ujemajo z besedilnimi vrednostmi.
3. Pomožni stolpec je lahko katera koli vrsta zahtevanega vrstnega reda stolpcev.

Upam, da je ta članek o tem, kako indeksirati in razvrstiti vrednosti s pomožnim stolpcem v Excelu razložljiv. Raziščite več člankov o iskalnih formulah v Excelu tukaj. Če so vam bili naši blogi všeč, jih delite s prijatelji na Facebooku. Prav tako nas lahko spremljate na Twitterju in Facebooku. Radi bi slišali od vas, nam sporočite, kako lahko izboljšamo, dopolnimo ali inoviramo svoje delo in ga izboljšamo. Pišite nam na spletni strani elektronske pošte

Kako uporabljati funkcijo SUMPRODUCT v Excelu: Vrne SUM po množenju vrednosti v več matrikah v Excelu.

SUM, če je datum med : Vrne SUM vrednosti med danimi datumi ali obdobjem v Excelu.

Vsota, če je datum večji od navedenega datuma: Vrne SUM vrednosti po danem datumu ali obdobju v Excelu.

2 načina seštevanja po mesecih v Excelu: Vrne SUM vrednosti v določenem mesecu v Excelu.

Kako sešteti več stolpcev s pogojem: Vrne SUM vrednosti v več stolpcih s pogojem v excelu

Kako uporabljati nadomestne znake v Excelu : Preštejte celice, ki se ujemajo z frazami, z uporabo nadomestnih znakov v Excelu

Priljubljeni članki

50 Bližnjica v Excelu za večjo produktivnost : Hitreje opravite svojo nalogo. S temi 50 bližnjicami boste še hitreje delali v Excelu.

Kako uporabljati tFunkcija VLOOKUP v Excelu : To je ena najpogosteje uporabljenih in priljubljenih funkcij programa Excel, ki se uporablja za iskanje vrednosti iz različnih obsegov in listov.

Kako uporabljati funkcijo COUNTIF v Excelu : Štejte vrednosti s pogoji s to neverjetno funkcijo. Za štetje določenih vrednosti vam ni treba filtrirati podatkov. Funkcija Countif je bistvena za pripravo vaše armaturne plošče.

Kako uporabljati funkcijo SUMIF v Excelu : To je še ena bistvena funkcija nadzorne plošče. To vam pomaga povzeti vrednosti za posebne pogoje.