Laadige alla töövihiku näide
See õpetus näitab, kuidas loetleda vahemikus puuduvaid numbreid.
Loetlege vahelejäänud numbrid järjest
Loendist puuduvate numbrite leidmiseks saame kasutada järgmist valemit:
| 1 | = SMALL (IF (COUNTIF ($ B $ 3: $ B $ 8, ROW ($ 1: $ 6))) = 0, ROW ($ 1: $ 6), ""), ROW (B1)) |
Märkused:
- See on massiivivalem! Kui kasutate Excel 2022 ja varasemaid versioone, peate sisestama valemi, vajutades klahvikombinatsiooni CTRL + SHIFT + ENTER. Teate, et valem on õigesti sisestatud, kui näete valemi ümber lokkis sulgusid.
- See valem töötab ainult positiivsete täisarvudega (täisarvudega).
Kuidas valem töötab?
Esiteks kasutatakse funktsiooni ROW numbrimassiivi genereerimiseks:
| 1 | {1;2;3;4;5;6} |
Tegelikult võite selle numbrimassiivi isegi käsitsi sisestada:
| 1 | = VÄIKE (IF (COUNTIF ($ B $ 3: $ B $ 8, {1; 2; 3; 4; 5; 6})) = 0, {1; 2; 3; 4; 5; 6}, ""), RIDA (B1)) |
Seejärel loendab funktsioon COUNTIF väärtused "kui" need vastavad massiivist leitud väärtustele.
Selle visualiseerimiseks teisendame kõigepealt vahemiku $ B $ 3: $ B $ 8 selle väärtuste massiiviks {3; 2; 0; 6; 0; 5}:
| 1 | = VÄIKE (IF (COUNTIF ({3; 2; 0; 6; 0; 5}, {1; 2; 3; 4; 5; 6}) = 0, {1; 2; 3; 4; 5; 6 }, ""), ROW (B1)) |
Järgmisena teeme COUNTIF toimingu:
| 1 | = VÄIKE (KUI ({0; 1; 1; 0; 1; 1} = 0, {1; 2; 3; 4; 5; 6}, ""), RIDA (B1)) |
Kui väärtust ei leita, väljastatakse 0. Funktsioon IF kontrollib, kas väärtust EI OLE leitud ja tagastab massiivist saadud väärtuse, vastasel juhul väljastab see tühja:
| 1 | = VÄIKE ({1; ""; ""; 4; ""; ""}, RIDA (B1)) |
Lõpuks tagastab funktsioon SMALL valitud lahtris väikseima puuduva väärtuse.
| 1 | = VÄIKE ({1; ""; ""; 4; ""; ""}, {1}) |
| 1 | ={1} |
Näpunäide:
Et vältida #NUM! viga, sisestage valem IFERROR -funktsiooniga:
| 1 | = IFERROR (SMALL (IF (COUNTIF (B3: B8, ROW (1: 6)) = 0, ROW (1: 6), ""), ROW (B1)), "") |
Loetlege vahelehtedest numbrid järjest Google’i arvutustabelites
Kõik ülaltoodud näited töötavad Google'i lehtedel samamoodi nagu Excelis.
