Preuzmite primjer radne knjige
Ovaj će vodič pokazati kako popisati brojeve koji nedostaju u rasponu.
Popis preskočenih brojeva u nizu
Za pronalaženje brojeva koji nedostaju na popisu možemo upotrijebiti sljedeću formulu:
| 1 | = MALO (AKO (COUNTIF ($ B $ 3: $ B $ 8, ROW ($ 1: $ 6)) = 0, ROW ($ 1: $ 6), ""), ROW (B1)) |
Bilješke:
- Ovo je formula niza! Kada koristite Excel 2022 i starije verzije, formulu morate unijeti pritiskom na CTRL + SHIFT + ENTER. Znat ćete da je formula pravilno unesena kad vidite uvijene zagrade oko formule.
- Ova formula radi samo s pozitivnim cijelim brojevima (cijelim brojevima).
Kako funkcionira formula?
Prvo, funkcija ROW se koristi za generiranje niza brojeva:
| 1 | {1;2;3;4;5;6} |
Zapravo, čak možete ručno unijeti ovaj niz brojeva:
| 1 | = MALO (AKO (COUNTIF ($ B $ 3: $ B $ 8, {1; 2; 3; 4; 5; 6}) = 0, {1; 2; 3; 4; 5; 6}, ""), RED (B1)) |
Zatim će funkcija COUNTIF brojati vrijednosti "ako" odgovaraju vrijednosti koja se nalazi u nizu.
Da bismo to vizualizirali, prvo ćemo pretvoriti raspon $ B $ 3: $ B $ 8 u njegov niz vrijednosti {3; 2; 0; 6; 0; 5}:
| 1 | = MALO (AKO (COUNTIF ({3; 2; 0; 6; 0; 5}, {1; 2; 3; 4; 5; 6}) = 0, {1; 2; 3; 4; 5; 6 }, ""), ROW (B1)) |
Zatim ćemo izvršiti operaciju COUNTIF:
| 1 | = MALO (AKO ({0; 1; 1; 0; 1; 1} = 0, {1; 2; 3; 4; 5; 6}, ""), ROW (B1)) |
Ako vrijednost nije pronađena, izlazi 0. IF funkcija provjerava je li vrijednost NIJE pronađena i ako je tako, vraća vrijednost iz niza, u protivnom ispisuje prazno:
| 1 | = MALO ({1; ""; ""; 4; ""; ""}, ROW (B1)) |
Na kraju, SMALL funkcija vraća najmanju vrijednost koja nedostaje u odabranoj ćeliji.
| 1 | = MALO ({1; ""; ""; 4; ""; ""}, {1}) |
| 1 | ={1} |
Savjet:
Da biste izbjegli #NUM! pogreška, omotajte formulu funkcijom IFERROR:
| 1 | = IFERROR (MALI (IF (COUNTIF (B3: B8, ROW (1: 6)) = 0, ROW (1: 6), ""), ROW (B1)), "") |
Popis preskočenih brojeva u nizu u Google tablicama
Svi gore opisani primjeri rade isto na Google tablicama kao i u Excelu.
