Prenesite primer delovnega zvezka
Ta vadnica bo pokazala, kako seznam manjkajočih številk v razponu.
Seznam preskočenih števil v zaporedju
Za iskanje manjkajočih številk na seznamu lahko uporabimo naslednjo formulo:
| 1 | = MALO (ČE (COUNTIF ($ B $ 3: $ B $ 8, ROW ($ 1: $ 6)) = 0, ROW ($ 1: $ 6), ""), ROW (B1)) |
Opombe:
- To je matrična formula! Ko uporabljate Excel 2022 in starejše, morate formulo vnesti s pritiskom na CTRL + SHIFT + ENTER. Ko boste videli formulo oklepanih oklepajev, boste vedeli, da je formula pravilno vnesena.
- Ta formula deluje le s pozitivnimi celimi števili (cela števila).
Kako deluje formula?
Najprej se funkcija ROW uporablja za ustvarjanje niza številk:
| 1 | {1;2;3;4;5;6} |
Pravzaprav bi lahko celo ročno vnesli ta niz številk:
| 1 | = MALO (ČE (COUNTIF ($ B $ 3: $ B $ 8, {1; 2; 3; 4; 5; 6}) = 0, {1; 2; 3; 4; 5; 6}, ""), RED (B1)) |
Nato funkcija COUNTIF šteje vrednosti "če" se ujemajo z vrednostjo v matriki.
Za vizualizacijo tega bomo najprej pretvorili obseg $ B $ 3: $ B $ 8 v niz vrednosti {3; 2; 0; 6; 0; 5}:
| 1 | = MALO (ČE (COUNTIF ({3; 2; 0; 6; 0; 5}, {1; 2; 3; 4; 5; 6}) = 0, {1; 2; 3; 4; 5; 6 }, ""), ROW (B1)) |
Nato bomo izvedli operacijo COUNTIF:
| 1 | = MALO (ČE ({0; 1; 1; 0; 1; 1} = 0, {1; 2; 3; 4; 5; 6}, ""), ROW (B1)) |
Če vrednost ni najdena, se prikaže 0. Funkcija IF preveri, če vrednost NI Najdena, in če je tako, vrne vrednost iz matrike, sicer pa izpiše prazno:
| 1 | = MALO ({1; ""; ""; 4; ""; ""}, ROW (B1)) |
Nazadnje, funkcija SMALL vrne najmanjšo manjkajočo vrednost v izbrani celici.
| 1 | = MALO ({1; ""; ""; 4; ""; ""}, {1}) |
| 1 | ={1} |
Nasvet:
Da bi se izognili številki #NUM! napaka, zavijte formulo s funkcijo IFERROR:
| 1 | = NAPAKA (MALO (ČE (COUNTIF (B3: B8, ROW (1: 6)) = 0, ROW (1: 6), ""), ROW (B1)), "") |
Seznam preskočenih števil v zaporedju v Google Preglednicah
Vsi zgoraj opisani primeri delujejo enako v Googlovih listih kot v Excelu.
