Lataa esimerkkityökirja
Tämä opetusohjelma näyttää kuinka luetteloida puuttuvat numerot alueelta.
Lista ohitetut numerot järjestyksessä
Löydämme puuttuvia numeroita luettelosta käyttämällä seuraavaa kaavaa:
| 1 | = PIENI (JOS (COUNTIF ($ B $ 3: $ B $ 8, ROW ($ 1: $ 6))) = 0, ROW ($ 1: $ 6), ""), ROW (B1)) |
Huomautuksia:
- Tämä on matriisikaava! Kun käytät Excel 2022 ja sitä vanhempia versioita, sinun on annettava kaava painamalla CTRL + VAIHTO + ENTER. Tiedät, että kaava on syötetty oikein, kun näet kaarevat hakasulkeet kaavan ympärillä.
- Tämä kaava toimii vain positiivisten kokonaislukujen (kokonaislukujen) kanssa.
Miten kaava toimii?
Ensinnäkin ROW -funktiota käytetään numeroryhmän luomiseen:
| 1 | {1;2;3;4;5;6} |
Itse asiassa voit jopa syöttää tämän numeroryhmän manuaalisesti:
| 1 | = PIENI (JOS (COUNTIF ($ B $ 3: $ B $ 8, {1; 2; 3; 4; 5; 6}) = 0, {1; 2; 3; 4; 5; 6}, ""), RIVI (B1)) |
Seuraavaksi COUNTIF -toiminto laskee arvot "jos", jotka vastaavat taulukosta löytyvää arvoa.
Tämän visualisoimiseksi muunnamme ensin alueen $ B $ 3: $ B $ 8 sen arvojoukkoksi {3; 2; 0; 6; 0; 5}:
| 1 | = PIENI (JOS (LASKE ({3; 2; 0; 6; 0; 5}, {1; 2; 3; 4; 5; 6}) = 0, {1; 2; 3; 4; 5; 6 }, ""), RIVI (B1)) |
Seuraavaksi suoritamme COUNTIF -operaation:
| 1 | = PIENI (JOS ({0; 1; 1; 0; 1; 1} = 0, {1; 2; 3; 4; 5; 6}, ""), RIVI (B1)) |
Jos arvoa ei löydy, tulostuu 0. IF -toiminto tarkistaa, onko arvoa EI löydetty, ja jos on, palauttaa arvon taulukosta, muuten se antaa tyhjän:
| 1 | = PIENI ({1; ""; ""; 4; ""; ""}, RIVI (B1)) |
Lopuksi SMALL -funktio palauttaa pienimmän puuttuvan arvon valitusta solusta.
| 1 | = PIENI ({1; ""; ""; 4; ""; ""}, {1}) |
| 1 | ={1} |
Kärki:
Välttääksesi #NUM! virhe, kääri kaava IFERROR -funktiolla:
| 1 | = VIRHE (PIENI (JOS (LASKE (B3: B8, RIVI (1: 6)) = 0, RIVI (1: 6), ""), RIVI (B1)), "") |
Luettele ohitetut numerot järjestyksessä Google Sheetsissä
Kaikki yllä selitetyt esimerkit toimivat samalla tavalla Google -arkeissa kuin Excelissä.
