Stiahnite si ukážkový zošit
Tento tutoriál ukáže, ako uviesť chýbajúce čísla v rozsahu.
Zoznam preskočených čísel uvádzajte v poradí
Na nájdenie chýbajúcich čísel v zozname môžeme použiť nasledujúci vzorec:
| 1 | = MALÉ (IF (COUNTIF ($ B $ 3: $ B $ 8, ROW ($ 1: $ 6))) = 0, ROW ($ 1: $ 6), ""), ROW (B1)) |
Poznámky:
- Toto je vzorec poľa! Pri použití Excelu 2022 a starších musíte vzorec zadať stlačením klávesov CTRL + SHIFT + ENTER. Budete vedieť, že vzorec bol zadaný správne, keď uvidíte okolo vzorca zložené zátvorky.
- Tento vzorec funguje iba s kladnými celými číslami (celými číslami).
Ako funguje vzorec?
Po prvé, funkcia ROW sa používa na generovanie radu čísel:
| 1 | {1;2;3;4;5;6} |
V skutočnosti môžete do tohto poľa čísel dokonca zadať ručne:
| 1 | = MALÝ (IF (COUNTIF ($ B $ 3: $ B $ 8, {1; 2; 3; 4; 5; 6}) = 0, {1; 2; 3; 4; 5; 6}, ""), RIADOK (B1)) |
Ďalej funkcia COUNTIF spočíta hodnoty „ak“ sa zhodujú s hodnotou nájdenou v poli.
Aby sme si to predstavili, najskôr prevedieme rozsah $ B $ 3: $ B $ 8 na jeho pole hodnôt {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 }, ""), RAD (B1)) |
Ďalej vykonáme operáciu COUNTIF:
| 1 | = MALÉ (IF ({0; 1; 1; 0; 1; 1} = 0, {1; 2; 3; 4; 5; 6}, ""), RAD (B1)) |
Ak nie je hodnota nájdená, zobrazí sa 0. Funkcia IF skontroluje, či hodnota NIE JE nájdená, a ak áno, vráti hodnotu z poľa, inak bude prázdna:
| 1 | = MALÉ ({1; ""; ""; 4; ""; ""}, RADA (B1)) |
Nakoniec funkcia SMALL vráti najmenšiu chýbajúcu hodnotu vo vybratej bunke.
| 1 | = MALÉ ({1; ""; ""; 4; ""; ""}, {1}) |
| 1 | ={1} |
Tip:
Aby ste sa vyhli #NUM! chyba, zabaľte vzorec pomocou funkcie IFERROR:
| 1 | = IFERROR (MALÉ (IF (COUNTIF (B3: B8, ROW (1: 6)) = 0, ROW (1: 6), ""), ROW (B1)), "") |
Vynechané čísla uvádzajte v zozname v Tabuľkách Google
Všetky vyššie uvedené príklady fungujú v hárkoch Google rovnako ako v programe Excel.
