Atsisiųskite darbaknygės pavyzdį
Ši pamoka parodys, kaip išvardyti trūkstamus skaičius diapazone.
Išvardykite praleistus skaičius iš eilės
Norėdami rasti trūkstamus skaičius sąraše, galime naudoti šią formulę:
1 | = SMALL (IF (COUNTIF ($ B $ 3: $ B $ 8, ROW ($ 1: $ 6))) = 0, ROW ($ 1: $ 6), ""), ROW (B1)) |
Pastabos:
- Tai masyvo formulė! Kai naudojate „Excel 2022“ ir ankstesnę versiją, turite įvesti formulę paspausdami CTRL + SHIFT + ENTER. Jūs žinosite, kad formulė buvo įvesta tinkamai, kai aplink formulę matote garbanotus skliaustus.
- Ši formulė veikia tik su teigiamais sveikais skaičiais (sveikais skaičiais).
Kaip veikia formulė?
Pirma, funkcija ROW naudojama skaičių masyvui generuoti:
1 | {1;2;3;4;5;6} |
Tiesą sakant, jūs netgi galite įvesti šį skaičių masyvą rankiniu būdu:
1 | = SMALL (IF (COUNTIF ($ B $ 3: $ B $ 8, {1; 2; 3; 4; 5; 6})) = 0, {1; 2; 3; 4; 5; 6}, ""), EILUTĖ (B1)) |
Tada funkcija COUNTIF suskaičiuos reikšmes „jei“ jos atitinka masyve esančią vertę.
Norėdami tai vizualizuoti, pirmiausia konvertuosime diapazoną $ B $ 3: $ B $ 8 į jo verčių masyvą {3; 2; 0; 6; 0; 5}:
1 | = MAŽAS (JEI (COUNTIF ({3; 2; 0; 6; 0; 5}, {1; 2; 3; 4; 5; 6}) = 0, {1; 2; 3; 4; 5; 6 }, ""), ROW (B1)) |
Toliau atliksime operaciją COUNTIF:
1 | = SMALL (IF ({0; 1; 1; 0; 1; 1} = 0, {1; 2; 3; 4; 5; 6}, ""), ROW (B1)) |
Jei reikšmė nerasta, išvedamas 0. Funkcija IF patikrina, ar reikšmė NErasta, ir jei taip, grąžina reikšmę iš masyvo, kitaip ji pateikia tuščią:
1 | = SMALL ({1; ""; ""; 4; ""; ""}, ROW (B1)) |
Galiausiai funkcija SMALL grąžina mažiausią trūkstamą reikšmę pasirinktame langelyje.
1 | = MAŽAS ({1; ""; ""; 4; ""; ""}, {1}) |
1 | ={1} |
Patarimas:
Kad išvengtumėte #NUM! klaida, apvyniokite formulę naudodami funkciją IFERROR:
1 | = IFERROR (SMALL (IF (COUNTIF (B3: B8, ROW (1: 6))) = 0, ROW (1: 6), ""), ROW (B1)), "") |
Išvardykite praleistus skaičius iš eilės „Google“ skaičiuoklėse
Visi aukščiau aprašyti pavyzdžiai „Google“ lapuose veikia taip pat, kaip ir „Excel“.