Jak používat funkci ŘÁDKY v Microsoft Excelu

Mnoho uživatelů Excelu opouští funkci ROWS, protože se zdá být technikálií, kterou lze přeskočit. K vytvoření opravdu funkčního sešitu však potřebujete vzorce, které se přizpůsobí rozměrům vašich dat, a funkce ROWS je k tomu ideální. Zde jsou čtyři způsoby, jak ji používám, aby byl můj Excel lepší.

Jak funguje funkce ŘÁDKY

Syntaxe funkce ROWS je opravdu jednoduchá:

=ROWS(array)

Jediným argumentem (polem) je rozsah buněk nebo tabulka v Excelu. Úkolem funkce je přesně vám říct, jak vysoký váš datový soubor je, bez ohledu na to, zda jsou řádky prázdné nebo vyplněné.

Všechny příklady, které vám chystám ukázat, používají formátovanou tabulku Excel s názvem T_Orders. Používání tabulek mi umožňuje používat strukturované odkazy místo přímých odkazů na buňky, což moje vzorce činí mnohem snadněji čitelnými a udržovatelnými.

Případ použití 1: Vytvoření řádkového indexu odolného proti řazení

Funkce ROWS počítá počet řádků v určitém rozsahu. Pokud použijete rozšiřující se rozsah - který začíná na pevně stanoveném bodě, ale končí u aktuálního řádku - můžete vytvořit dynamický sekvenční seznam.

Scénář

Chcete vyplnit sloupec A čísly řádků, která zůstanou v dokonalém číselném pořadí, i když tabulku přeřadíte podle jednoho z ostatních sloupců.

Řešení ROWS

Zadejte tento vzorec do prvního řádku sloupce a stiskněte Enter:

=ROWS(T_Orders[#Headers]:[@OrderID])-1

Protože používáte strukturovanou tabulku Excel, je vzorec automaticky duplikován dolů sloupcem.

Tento vzorec začíná u hlavičky tabulky (T_Orders[#Headers]) a končí u aktuálního řádku ([@OrderID]). Při posunu dolů po tabulce zůstává výchozí bod ukotvený nahoře, ale koncový bod se posouvá dolů, což umožňuje funkci ROWS počítat počet řádků v tomto rostoucím okně. Odečtení -1 na konci vylučuje hlavičku z tohoto počtu.

Proč je ROWS správnou volbou

Standardní alternativou, =ROW(), je vázána na mřížku listu. Pokud přesunete tabulku z řádku 1 na řádek 10, čísla indexů se změní na 11, 12, 13 a tak dále. Metoda ROWS je relativní k tabulce, což znamená, že se zajímá pouze o polohu uvnitř samotné tabulky.

Příklad použití 2: Výpočet „nepoddajných“ procentuálních KPI

Při výpočtu procent v Excelu potřebujete jmenovatele, který představuje celkovou kapacitu vašeho datového souboru. Funkce ROWS vrací počet rozměrů tabulky, bez ohledu na obsah buněk.

Scénář

Potřebujete přesně vypočítat, jaké procento z celkového objemu vaší objednávky dosáhlo stavu „Odesláno“.

Řešení ROWS

Do oblasti řídicího panelu v horní části pracovního listu napište tento vzorec:

=COUNTIF(T_Orders[Status],"Shipped")/ROWS(T_Orders)

Část vzorce COUNTIF počítá počet řádků, které obsahují slovo „Odesláno,“ a toto číslo se dělí funkcí ROWS(T_Orders), která vrací celkovou výšku tabulky T_Orders.

Proč je ROWS správnou volbou

Můžete být v pokušení použít funkci COUNTA jako jmenovatel v tomto scénáři, ale tato funkce počítá pouze buňky, které nejsou prázdné. Pokud někdo přidá nový řádek do tabulky, ale nechá stav prázdný, nebo pokud buňka obsahuje neviditelný „duchoví“ znak, například mezeru, funkce COUNTA vrátí nesprávný součet. Funkce ROWS měří kontejner, nikoli obsah, takže vaše KPI jsou založeny na skutečné velikosti vaší tabulky.

Případ použití 3: Audit integrity dat

Protože funkce ROWS měří fyzickou velikost oblasti, funguje jako automatický kontrolor při porovnání s funkcemi, které počítají skutečné hodnoty.

Scénář

Chcete upozornění, které se spustí vždy, když je do tabulky přidán nový řádek, ale klíčové pole Částka zůstane prázdné.

Řešení ROWS

Zde je vzorec, který můžete přidat do oblasti svého panelu, aby se to stalo:

=IF(ROWS(T_Orders)>SUMPRODUCT(--(LEN(T_Orders[Amount])>0)),"[!] Amount entry missing","All clear")

Toto porovnává celkový počet řádků v tabulce (ROWS) s počtem buněk ve sloupci Amount, které skutečně obsahují znaky. Použitím funkce LEN pro kontrolu délky znaků a SUMPRODUCT pro součet výsledků nutíte Excel počítat pouze buňky, kde je počet znaků větší než nula. Pokud celkový počet řádků v tabulce přesahuje tento počet, vzorec vrátí upozornění „Chybí zadání částky“; jinak vrátí „Vše v pořádku.“

Proč je ROWS správnou volbou

Běžné metody jako COUNTA a COUNTBLANK mohou spadnout do pasti, kdy nesprávně interpretují „duchová“ data, například náhodné mezery nebo prázdné řetězce vrácené vzorci. Kombinací funkcí ROWS s kontrolou délky vytvoříte mechanický audit, který je mnohem spolehlivější.

Případ použití 4: Vytváření flexibilních zpráv „top X“

Kombinace funkce ROWS s pořadovými funkcemi, jako je LARGE, vám umožňuje vytvářet přehledy, které se automaticky škálují tím, že se statické pořadí přemění na dynamický inkrementátor.

Scénář

Chcete zobrazit žebříček tří nejvyšších objednávek na dashboardu, ale zároveň chcete mít možnost roztáhnout seznam dolů na top pět, aniž byste museli přepisovat vzorec nebo ručně měnit pořadí u každého řádku.

Řešení ROWS

Do první buňky svého žebříčku použijte následující vzorec:

=LARGE(T_Orders[Amount],ROWS($1:1))

Pak klikněte a táhněte úchyt výplně buňky dolů o dva řádky, abyste dokončili žebříček top tří.

Funkce LARGE vyžaduje dva argumenty: datový rozsah a hodnotu k (pořadí). Místo zadávání statického čísla pro pořadí použijete ROWS($1:1), která vrací 1 v prvním řádku. Protože však první část rozsahu je absolutní ($1) a druhá relativní (1), rozsah se při tahání vzorce dolů rozšiřuje, čímž se vytvoří automatický čítač, který funkci LARGE přesně ukazuje, které pořadí má vybrat.

Proč je ROWS správnou volbou

Běžnou alternativou je použít konstantu pole ({1;2;3}) k vrácení tří nejvyšších hodnot. Tento pevně zadaný přístup však znamená, že musíte ručně upravit vzorec, pokud chcete změnit počet hodnot ve výsledku. Alternativně můžete použít funkci SEQUENCE, ale ta postrádá zpětnou kompatibilitu. Metoda ROWS je univerzální - funguje ve všech verzích Excelu a umožňuje každému rozšířit report tažením za úchyt nebo ho zmenšit vymazáním příslušných buněk.

Funkce ROWS je jen jedním ze způsobů, jak auditovat váš sešit Excelu. Můžete také podrobněji prozkoumat stav vašeho sešitu pomocí funkce Přejít na speciální, abyste našli skryté konstanty nebo nesrovnalé vzorce, a kombinace těchto nástrojů s návyky, které vaše vzorce usnadňují číst, zajistí, že vaše tabulky zůstanou dlouhodobě snadno udržovatelné jak pro vás, tak pro vaše spolupracovníky.