Stáhněte si ukázkový sešit
Tento tutoriál předvede, jak vypsat chybějící čísla v rozsahu.
Seznam přeskočených čísel v pořadí
K nalezení chybějících čísel v seznamu můžeme použít následující vzorec:
| 1 | = MALÝ (IF (COUNTIF ($ B $ 3: $ B $ 8, ROW ($ 1: $ 6))) = 0, ROW ($ 1: $ 6), ""), ROW (B1)) |
Poznámky:
- Toto je vzorec pole! Pokud používáte Excel 2022 a starší, musíte vzorec zadat stisknutím kláves CTRL + SHIFT + ENTER. Až vzorec uvidíte správně, poznáte, že byl vzorec zadán správně.
- Tento vzorec funguje pouze s kladnými celými čísly (celými čísly).
Jak funguje vzorec?
Za prvé, funkce ROW se používá ke generování pole čísel:
| 1 | {1;2;3;4;5;6} |
Ve skutečnosti můžete do této řady čísel dokonce zadat ručně:
| 1 | = MALÝ (IF (COUNTIF ($ B $ 3: $ B $ 8, {1; 2; 3; 4; 5; 6}) = 0, {1; 2; 3; 4; 5; 6}, ""), ŘADA (B1)) |
Dále bude funkce COUNTIF počítat hodnoty „pokud“ odpovídají hodnotě nalezené v poli.
Abychom si to mohli představit, nejprve převedeme rozsah $ B $ 3: $ B $ 8 na jeho pole hodnot {3; 2; 0; 6; 0; 5}:
| 1 | = MALÝ (IF (COUNTIF ({3; 2; 0; 6; 0; 5}, {1; 2; 3; 4; 5; 6}) = 0, {1; 2; 3; 4; 5; 6 }, ""), ŘÁDEK (B1)) |
Dále provedeme operaci COUNTIF:
| 1 | = MALÝ (IF ({0; 1; 1; 0; 1; 1} = 0, {1; 2; 3; 4; 5; 6}, ""), ŘADA (B1)) |
Není -li hodnota nalezena, zobrazí se 0. Funkce IF kontroluje, zda hodnota NENÍ nalezena, a pokud ano, vrátí hodnotu z pole, v opačném případě bude prázdná:
| 1 | = MALÉ ({1; ""; ""; 4; ""; ""}, ŘADA (B1)) |
Nakonec funkce SMALL vrátí nejmenší chybějící hodnotu ve vybrané buňce.
| 1 | = MALÉ ({1; ""; ""; 4; ""; ""}, {1}) |
| 1 | ={1} |
Spropitné:
Abyste se vyhnuli #NUM! chyba, zabalte vzorec funkcí IFERROR:
| 1 | = IFERROR (SMALL (IF (COUNTIF (B3: B8, ROW (1: 6)) = 0, ROW (1: 6), ""), ROW (B1)), "") |
Seznam přeskočených čísel za sebou v Tabulkách Google
Všechny výše vysvětlené příklady fungují v listech Google stejně jako v Excelu.
