Töltse le a példa munkafüzetet
Ez az oktatóanyag bemutatja, hogyan kell felsorolni a hiányzó számokat egy tartományban.
Sorolja fel az átugrott számokat
A listában hiányzó számok megkereséséhez a következő képletet használhatjuk:
1 | = KIS (HA (COUNTIF ($ B $ 3: $ B $ 8, ROW ($ 1: $ 6))) = 0, SOR ($ 1: $ 6), ""), ROW (B1)) |
Megjegyzések:
- Ez egy tömbképlet! Excel 2022 és korábbi verziók használatakor a képletet a CTRL + SHIFT + ENTER billentyűkombinációval kell megadnia. Tudni fogja, hogy a képletet helyesen adta meg, ha göndör zárójeleket lát a képlet körül.
- Ez a képlet csak pozitív egész számokkal (egész számokkal) működik.
Hogyan működik a képlet?
Először is a ROW függvényt használjuk egy számtömb előállítására:
1 | {1;2;3;4;5;6} |
Valójában manuálisan is beírhatja ezt a számtömböt:
1 | = KIS (HA (COUNTIF ($ B $ 3: $ B $ 8, {1; 2; 3; 4; 5; 6}) = 0, {1; 2; 3; 4; 5; 6}, ""), Sor (B1)) |
Ezután a COUNTIF függvény megszámolja az értékeket, „ha” megegyeznek a tömbben található értékkel.
Ennek vizualizálásához először a $ B $ 3: $ B $ 8 tartományt alakítjuk át annak értékrendjévé {3; 2; 0; 6; 0; 5}:
1 | = KIS (HA (COUNTIF ({3; 2; 0; 6; 0; 5}, {1; 2; 3; 4; 5; 6}) = 0, {1; 2; 3; 4; 5; 6 }, ""), ROW (B1)) |
Ezután a COUNTIF műveletet hajtjuk végre:
1 | = KIS (HA ({0; 1; 1; 0; 1; 1} = 0, {1; 2; 3; 4; 5; 6}, ""), SOR (B1)) |
Ha az érték nem található, akkor a kimenet 0 lesz. Az IF függvény ellenőrzi, hogy az érték NEM található -e, és ha igen, akkor visszaadja az értéket a tömbből, különben üresen adja ki:
1 | = KIS ({1; ""; ""; 4; ""; ""}, SOR (B1)) |
Végül a SMALL függvény a kiválasztott cellában a legkisebb hiányzó értéket adja vissza.
1 | = KIS ({1; ""; ""; 4; ""; ""}, {1}) |
1 | ={1} |
Tipp:
A #SZÁM elkerülése érdekében! hiba, csomagolja be a képletet az IFERROR függvénnyel:
1 | = IFERROR (SMALL (IF (COUNTIF (B3: B8, SOR (1: 6)) = 0, SOR (1: 6), ""), ROW (B1)), "") |
Sorolja fel az átugrott számokat a Google Táblázatokban
A fent ismertetett példák ugyanúgy működnek a Google -lapokban, mint az Excelben.