Descărcați exemplul de registru de lucru
Acest tutorial va arăta cum să enumerați numerele lipsă dintr-un interval.
Listează numerele omise în secvență
Pentru a găsi numerele lipsă într-o listă, putem folosi următoarea formulă:
| 1 | = MIC (DACĂ (COUNTIF ($ B $ 3: $ B $ 8, ROW ($ 1: 6 $)) = 0, ROW ($ 1: 6 $), ""), ROW (B1)) |
Note:
- Aceasta este o formulă matrice! Când utilizați Excel 2022 și versiunile anterioare, trebuie să introduceți formula apăsând CTRL + SHIFT + ENTER. Veți ști că formula a fost introdusă corect atunci când vedeți paranteze cretate în jurul formulei.
- Această formulă funcționează numai cu numere întregi pozitive (numere întregi).
Cum funcționează formula?
În primul rând, funcția ROW este utilizată pentru a genera o serie de numere:
| 1 | {1;2;3;4;5;6} |
De fapt, ai putea chiar să introduci manual în această serie de numere:
| 1 | = SMALL (IF (COUNTIF ($ B $ 3: $ B $ 8, {1; 2; 3; 4; 5; 6}) = 0, {1; 2; 3; 4; 5; 6}, ""), RÂND (B1)) |
Apoi, funcția COUNTIF va număra valorile „dacă” se potrivesc cu o valoare găsită în matrice.
Pentru a vizualiza acest lucru, mai întâi vom converti intervalul $ B $ 3: $ B $ 8 în matricea de valori {3; 2; 0; 6; 0; 5}:
| 1 | = SMALL (IF (COUNTIF ({3; 2; 0; 6; 0; 5}, {1; 2; 3; 4; 5; 6}) = 0, {1; 2; 3; 4; 5; 6 }, ""), ROW (B1)) |
Apoi vom efectua operația COUNTIF:
| 1 | = MIC (IF ({0; 1; 1; 0; 1; 1} = 0, {1; 2; 3; 4; 5; 6}, ""), ROW (B1)) |
Dacă nu se găsește o valoare, se emite 0. Funcția IF verifică dacă valoarea NU este găsită și, în caz afirmativ, returnează valoarea din matrice, în caz contrar rezultă necompletat:
| 1 | = SMALL ({1; ""; ""; 4; ""; ""}, ROW (B1)) |
În cele din urmă, funcția SMALL returnează cea mai mică valoare lipsă din celula selectată.
| 1 | = SMALL ({1; ""; ""; 4; ""; ""}, {1}) |
| 1 | ={1} |
Bacsis:
Pentru a evita #NUM! eroare, înfășurați-vă formula cu funcția IFERROR:
| 1 | = IFERROR (SMALL (IF (COUNTIF (B3: B8, ROW (1: 6))) = 0, ROW (1: 6), ""), ROW (B1)), "") |
Listează numerele omise în secvență în Foi de calcul Google
Toate exemplele explicate mai sus funcționează la fel în foile Google ca în Excel.
