Ako indexovať a triediť hodnoty pomocou pomocného stĺpca v programe Excel.

Anonim

V tomto článku sa naučíme Ako indexovať a triediť hodnoty pomocou pomocného stĺpca v programe Excel.

Scenár:

Niekedy sa pracuje s nehanebnými údajmi. Kratšiu verziu ako požadované údaje musíme získať z údajov tabuľky na základe pomocného stĺpca. Tu najskôr pochopíme, ako získať pomocný stĺpec, a potom získať zoradené malé požadované údaje na základe údajov z tabuľky.

Ako indexovať hodnoty v rozsahu?

V tomto článku budeme musieť používať funkciu COUNTIF. Teraz z funkcie vytvoríme vzorec. Tu je daný text a číslo zmiešané hodnoty v rozsahu. Musíme ich indexovať vo vzostupnom poradí najskôr číselne a potom podľa abecedy.

Všeobecný vzorec:-

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

zoznam: zoznam čísiel a textu

f_value: prvá hodnota rozsahu

Vysvetlenie:

  1. Funkcia COUNTIF počíta počet hodnôt vo vrátenom zozname
  2. Funkcia COUNT zisťuje počet čísel v rozsahu.
  3. Funkcia ISTEXT kontroluje, či je hodnota vo vybratej bunke textová alebo nie. Vráti hodnotu TRUE pre text a FALSE pre čísla.

Príklad:

Toto všetko môže byť mätúce na pochopenie. Poďme pochopiť, ako získať pomocný stĺpec pomocou vysvetleného vzorca. Tu je pomocný stĺpec založený na hodnotách zoradenia v poradí (prvé čísla a potom podľa abecedy). Na tento účel použijeme vzorec na získanie stĺpca Index alebo poradia ako pomocného stĺpca pre údaje o výdavkoch uvedené nižšie.

Tu nájdete hodnoty v rozsahu. Na účely výpočtu používame ako výdavok pomenovaný rozsah pre pevné pole D5: D12.

Použite vzorec:

= COUNTIF (výdavok, "<=" & D5) + (COUNT (výdavok) * ISTEXT (D5))


Vzorec vyzerá ako na obrázku vyššie. Hodnota a pole sú vo vzorci uvedené ako pomenovaný rozsah a odkaz na bunku.


Ako vidíte, index prvej bunky v rozsahu je 5, čo znamená, že ak umiestnime výdavky do poradia zoradenia, Elektrina sa umiestnia na 5. mieste. Teraz skopírujte vzorec do iných buniek pomocou možnosti potiahnutia nadol alebo pomocou klávesovej skratky Ctrl + D, ako je uvedené nižšie, aby ste získali index alebo poradie pre ostatné hodnoty.

Ako vidíte, teraz máme zoznam indexov alebo poradí, ktoré triedia náklady. Teraz použijeme tento stĺpec Rank ako pomocný stĺpec, aby sme v novom zozname alebo tabuľke získali kratšiu verziu tabuľky s rovnakým poradím.

Ako zoradiť hodnoty pomocou pomocného stĺpca v programe Excel?

Zoradiť číslo v programe Excel pomocou pomocného stĺpca musí mať čísla indexov a požadovaný rozsah na zoradenie. Pomocným stĺpcom môže byť ľubovoľná požadovaná objednávka. Ak napríklad potrebujete získať zoznam v požadovanom náhodnom poradí, umiestnite poradie zodpovedajúce hodnote do stĺpca index alebo poradie, ktorý bude fungovať ako pomocný stĺpec.

  1. Funkcia INDEX
  2. Funkcia MATCH
  3. Funkcia ROWS

Teraz vytvoríme vzorec pomocou vyššie uvedených funkcií. Funkcia MATCH vráti index najnižšej zhody z rozsahu. Funkcia INDEX berie argument argumentov ako riadok a vracia zodpovedajúce požadované výsledky. Táto funkcia nájde

Generický vzorec:

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

return_array: pole, z ktorého sa má vrátiť hodnota

Relatívna referencia: generuje vzorec vzostupne. Môže byť použitý s akýmkoľvek poľom

Index: Indexové pole tabuľky

0: presná zhoda

Vysvetlenie:

  1. ROWS (relative_reference) vráti hodnotu podľa dĺžky predĺženého vzorca. Ak sa použije v prvej bunke, bude to jedna, potom druhá a pokračuje, kým sa neroztiahne.
  2. Funkcia MATCH porovnáva index s hodnotou riadka, aby ich získal vo vzostupnom poradí pomocou funkcie ROWS.
  3. Funkcia INDEX vráti zodpovedajúci index so zodpovedajúcou hodnotou.

Príklad:

Toto všetko môže byť mätúce na pochopenie. Poďme teda porozumieť tomuto vzorcu spustením na príklade uvedenom nižšie. Tu sme zoradili údaje v náhodnom poradí, aby sme získali prvé tri hodnoty na základe stĺpca Index. Pomocou vzorca získate prvú hodnotu požadovanej krátkej tabuľky.

Použite vzorec:

= INDEX (B5: B13, MATCH (RADY (D5: D5), D5: D13,0))

Vzorec vyzerá ako na obrázku vyššie. Pole hodnôt je uvedené ako pomenovaný rozsah a odkaz na bunku.

Ako vidíte, prvá hodnota je číslo z rozsahu, ktoré vychádza ako „Mlieko“, zodpovedajúci index je 1. Teraz skopírujte vzorec v iných bunkách pomocou možnosti potiahnutia nadol alebo pomocou klávesovej skratky Ctrl + D, ako je uvedené nižšie, aby ste získali ostatné hodnoty.

Ako vidíte, hodnoty sú indexované vzostupne. Získali sme všetky hodnoty, pretože to tiež dokazuje, že vzorec funguje dobre. Číselné hodnoty môžeme extrahovať pomocou vzorca rovnakého vzorca.

Ako vidíte, máme kratšie a zoradené hodnoty z tabuľky pomocou pomocného stĺpca, ktorý je podľa toho indexovaný.

Tu je niekoľko pozorovacích poznámok uvedených nižšie.

Poznámky:

  1. Vzorec funguje iba s číslami a textom.
  2. Vzorec ignoruje textovú hodnotu pri porovnávaní čísel a ignoruje čísla pri zhode s textovými hodnotami.
  3. Pomocným stĺpcom môže byť ľubovoľný typ požadovaného poradia stĺpcov.

Dúfam, že tento článok o tom, ako indexovať a triediť hodnoty pomocou pomocného stĺpca v programe Excel, je vysvetľujúci. Tu nájdete ďalšie články o vzorcoch vyhľadávania v programe Excel. Ak sa vám páčili naše blogy, zdieľajte ich so svojimi priateľmi na Facebooku. A tiež nás môžete sledovať na Twitteri a Facebooku. Radi by sme vás počuli, dajte nám vedieť, ako môžeme zlepšiť, doplniť alebo inovovať našu prácu a zlepšiť ju pre vás. Napíšte nám na emailovú stránku

Ako používať funkciu SUMPRODUCT v programe Excel: Vráti SUMU po vynásobení hodnôt vo viacerých poliach v programe Excel.

SUM, ak je dátum medzi : Vráti SÚčet hodnôt medzi danými dátumami alebo obdobím v programe Excel.

Suma, ak je dátum väčší ako daný dátum: Vráti SÚčet hodnôt po danom dátume alebo období v programe Excel.

2 spôsoby súčtu podľa mesiacov v programe Excel: Vráti SÚčet hodnôt v rámci daného konkrétneho mesiaca v programe Excel.

Ako sčítať viac stĺpcov s podmienkou: Vráti SÚčet hodnôt vo viacerých stĺpcoch s podmienkou v programe Excel

Ako používať zástupné znaky v programe Excel : Spočítajte bunky zodpovedajúce frázam pomocou zástupných znakov v programe Excel

Populárne články

50 Skratka pre Excel, ktorá zvýši vašu produktivitu : Zrýchlite sa vo svojej úlohe. Týchto 50 skratiek vám umožní pracovať ešte rýchlejšie v Exceli.

Ako používať tFunkcia VLOOKUP v programe Excel : Toto je jedna z najpoužívanejších a najobľúbenejších funkcií programu Excel, ktorá sa používa na vyhľadávanie hodnôt z rôznych rozsahov a hárkov.

Ako používať funkciu COUNTIF v programe Excel : Počítajte hodnoty s podmienkami pomocou tejto úžasnej funkcie. Na počítanie konkrétnych hodnôt nie je potrebné filtrovať údaje. Funkcia Countif je potrebná na prípravu palubnej dosky.

Ako používať funkciu SUMIF v programe Excel : Toto je ďalšia zásadná funkcia palubnej dosky. To vám pomôže zhrnúť hodnoty za konkrétnych podmienok.