Jak indexovat a třídit hodnoty pomocí pomocného sloupce v aplikaci Excel.

Anonim

V tomto článku se naučíme Jak indexovat a třídit hodnoty pomocí pomocného sloupce v Excelu.

Scénář:

Někdy se pracuje s nehanebnými daty. Kratší verzi potřebujeme získat jako požadovaná data z dat tabulky na základě pomocného sloupce. Zde nejprve porozumíme tomu, jak získat pomocný sloupec, a poté získat seřazená malá požadovaná data na základě dat tabulky.

Jak indexovat hodnoty v rozsahu?

V tomto článku budeme muset použít funkci COUNTIF. Nyní z funkce vytvoříme vzorec. Zde daný text a číslo smíšené hodnoty v rozsahu. Musíme je indexovat ve vzestupném pořadí nejprve číselně, pak podle abecedy.

Obecný vzorec:-

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

seznam: seznam čísel a textu

f_value: první hodnota rozsahu

Vysvětlení:

  1. Funkce COUNTIF počítá počet hodnot ve vráceném seznamu
  2. Funkce COUNT získá počet čísel v rozsahu.
  3. Funkce ISTEXT kontroluje, zda je hodnota ve vybrané buňce textová nebo ne. Vrátí hodnotu TRUE pro text a FALSE pro čísla.

Příklad:

Pochopit to všechno může být matoucí. Pojďme pochopit, jak získat pomocný sloupec pomocí vysvětleného vzorce. Zde pomocný sloupec vychází z hodnot řazení v pořadí (první čísla a poté podle abecedy). K tomu použijeme vzorec pro získání sloupce Index nebo pořadí jako pomocného sloupce pro níže uvedená data výdajů.

Zde najdete hodnoty v rozsahu. Pro účely výpočtu používáme jako výdaj pojmenovaný rozsah pro pevné pole D5: D12.

Použijte vzorec:

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


Vzorec vypadá jako na výše uvedeném snímku. Hodnota a pole jsou ve vzorci uvedeny jako pojmenovaný rozsah a odkaz na buňku.


Jak vidíte, index první buňky v rozsahu je 5, což znamená, že pokud umístíme výdaje v pořadí řazení, Elektřina se umístí na 5. místě. Nyní zkopírujte vzorec v jiných buňkách pomocí možnosti přetažení dolů nebo pomocí klávesové zkratky Ctrl + D, jak je uvedeno níže, abyste získali index nebo pořadí pro ostatní hodnoty.

Jak vidíte, nyní máme seznam indexů nebo hodností, které třídí způsob výdajů. Nyní tento sloupec Rank použijeme jako pomocný sloupec, abychom získali kratší verzi tabulky se stejným pořadím v novém seznamu nebo tabulce.

Jak seřadit hodnoty pomocí pomocného sloupce v Excelu?

Řazení čísel v Excelu pomocí pomocného sloupce musí mít čísla indexů a požadovaný rozsah pro řazení. Sloupcem pomocníka může být libovolná požadovaná objednávka. Pokud například potřebujete získat seznam v požadovaném náhodném pořadí, stačí umístit pořadí odpovídající hodnotě do sloupce index nebo pořadí, který bude fungovat jako pomocný sloupec.

  1. Funkce INDEX
  2. Funkce MATCH
  3. Funkce ŘADY

Nyní vytvoříme vzorec pomocí výše uvedených funkcí. Funkce MATCH vrátí index nejnižší shody z rozsahu. Funkce INDEX bere index řádku jako argument a vrací odpovídající požadované výsledky. Tato funkce najde

Obecný vzorec:

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

return_array: pole pro návrat hodnoty

Relativní reference: generuje vzorec ve vzestupném pořadí. Lze použít s jakýmkoli polem

Index: Indexové pole tabulky

0: přesná shoda

Vysvětlení:

  1. ROWS (relative_reference) vrací hodnotu podle délky rozšířeného vzorce. Pokud je aplikován v první buňce, bude to jedna, pak druhá a bude pokračovat, dokud se neroztáhne.
  2. Funkce MATCH odpovídá indexu s hodnotou řádku, aby je pomocí funkce ROWS získala vzestupně.
  3. Funkce INDEX vrací odpovídající index s odpovídající hodnotou.

Příklad:

Pochopit to všechno může být matoucí. Pojďme tedy pochopit tento vzorec spuštěním na níže uvedeném příkladu. Zde jsme data seřadili v náhodném pořadí, abychom získali první tři hodnoty na základě sloupce Index. Pomocí vzorce získáte první hodnotu požadované krátké tabulky.

Použijte vzorec:

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

Vzorec vypadá jako na výše uvedeném snímku. Pole hodnot je uvedeno jako pojmenovaný rozsah a odkaz na buňku.

Jak vidíte, první hodnota je číslo z rozsahu, které vychází jako „Mléko“, odpovídající index je 1. Nyní zkopírujte vzorec v jiných buňkách pomocí možnosti přetažení dolů nebo pomocí klávesové zkratky Ctrl + D, jak je uvedeno níže, abyste získali zbytek hodnot.

Jak vidíte, hodnoty jsou indexovány vzestupně. Získali jsme všechny hodnoty, protože to také dokazuje, že vzorec funguje dobře. Číselné hodnoty můžeme extrahovat pomocí vzorce stejného vzorce.

Jak vidíte, máme kratší a seřazené hodnoty z tabulky pomocí pomocného sloupce, který je odpovídajícím způsobem indexován.

Zde je několik pozorovacích poznámek uvedených níže.

Poznámky:

  1. Vzorec funguje pouze s čísly a textem.
  2. Vzorec ignoruje textovou hodnotu při porovnávání čísel a ignoruje čísla při shodě s textovými hodnotami.
  3. Sloupec pomocníka může být jakýkoli typ požadovaného pořadí sloupců.

Doufám, že tento článek o tom, jak indexovat a třídit hodnoty pomocí pomocného sloupce v aplikaci Excel, je vysvětlující. Zde najdete další články o vzorcích pro vyhledávání v Excelu. Pokud se vám naše blogy líbily, sdílejte je se svými přáteli na Facebooku. A také nás můžete sledovat na Twitteru a Facebooku. Rádi bychom od vás slyšeli, dejte nám vědět, jak můžeme zlepšit, doplnit nebo inovovat naši práci a zlepšit ji pro vás. Napište nám na e -mail

Jak používat funkci SUMPRODUCT v Excelu: Vrací SUMU po vynásobení hodnot ve více polích v Excelu.

SUM, pokud je datum mezi : Vrátí SUMU hodnot mezi danými daty nebo obdobím v Excelu.

Součet, pokud je datum větší než dané datum: Vrátí SUMU hodnot po daném datu nebo období v aplikaci Excel.

2 způsoby součtu podle měsíce v Excelu: Vrátí SUMU hodnot v rámci daného konkrétního měsíce v aplikaci Excel.

Jak sečíst více sloupců s podmínkou: Vrátí SUMU hodnot napříč více sloupci s podmínkou v Excelu

Jak používat zástupné znaky v aplikaci Excel : Počítejte buňky odpovídající frázím pomocí zástupných znaků v aplikaci Excel

Populární články

50 Zkratka aplikace Excel pro zvýšení produktivity : Získejte rychleji svůj úkol. Těchto 50 klávesových zkratek vám umožní pracovat v Excelu ještě rychleji.

Jak používat tFunkce VLOOKUP v Excelu : Toto je jedna z nejpoužívanějších a nejoblíbenějších funkcí Excelu, která se používá k vyhledávání hodnot z různých rozsahů a listů.

Jak používat funkci COUNTIF v aplikaci Excel : Pomocí této úžasné funkce spočítejte hodnoty s podmínkami. K počítání konkrétních hodnot nemusíte data filtrovat. Funkce Countif je nezbytná pro přípravu řídicího panelu.

Jak používat funkci SUMIF v aplikaci Excel : Toto je další základní funkce řídicího panelu. To vám pomůže shrnout hodnoty za konkrétních podmínek.