Pobierz przykładowy skoroszyt
Ten samouczek pokaże, jak wyświetlić listę brakujących liczb w zakresie.
Lista pominiętych liczb w kolejności
Aby znaleźć brakujące liczby na liście, możemy użyć następującego wzoru:
| 1 | =MAŁY(JEŻELI(LICZ.JEŻELI($B3:$B8;WIERSZ(1$:6)$)=0;WIERSZ(1$:6$;"");WIERSZ(B1)) |
Uwagi:
- To jest formuła tablicowa! W przypadku korzystania z programu Excel 2022 lub starszego należy wprowadzić formułę, naciskając klawisze CTRL + SHIFT + ENTER. Będziesz wiedział, że formuła została wpisana poprawnie, gdy zobaczysz nawiasy klamrowe wokół formuły.
- Ta formuła działa tylko z dodatnimi liczbami całkowitymi (liczbami całkowitymi).
Jak działa formuła?
Po pierwsze, funkcja ROW służy do generowania tablicy liczb:
| 1 | {1;2;3;4;5;6} |
W rzeczywistości możesz nawet wprowadzić tę tablicę liczb ręcznie:
| 1 | =MAŁY(JEŻELI(LICZ.JEŻELI($B$3:$B$8;{1;2;3;4;5;6}))=0;{1;2;3;4;5;6};""), WIERSZ(B1)) |
Następnie funkcja LICZ.JEŻELI zliczy wartości „jeśli” pasują do wartości znalezionej w tablicy.
Aby to zwizualizować, najpierw przekonwertujemy zakres $B3:$B8 na jego tablicę wartości {3;2;0;6;0;5}:
| 1 | =MAŁA(JEŻELI(LICZ.JEŻELI({3;2;0;6;0;5};{1;2;3;4;5;6}))=0,{1;2;3;4;5;6 },""),WIERSZ(B1)) |
Następnie wykonamy operację LICZ.JEŻELI:
| 1 | =MAŁY(JEŻELI({0;1;1;0;1;1}=0;{1;2;3;4;5;6};"");WIERSZ(B1)) |
Jeśli wartość nie zostanie znaleziona, zostanie wyprowadzone 0. Funkcja IF sprawdza, czy wartość NIE została znaleziona, a jeśli tak, zwraca wartość z tablicy, w przeciwnym razie wyprowadza puste:
| 1 | =MAŁY({1;"";"";4;"";""};WIERSZ(B1)) |
Na koniec funkcja MAŁA zwraca najmniejszą brakującą wartość w wybranej komórce.
| 1 | =MAŁY({1;"";"";4;"";""},{1}) |
| 1 | ={1} |
Wskazówka:
Aby uniknąć #NUM! błąd, otocz formułę funkcją IFERROR:
| 1 | =JEŻELI.BŁĄD(MAŁY(JEŻELI(LICZ.JEŻELI(B3:B8;WIERSZ(1:6))=0;WIERSZ(1:6);"");WIERSZ(B1));"") |
Lista pominiętych numerów w kolejności w Arkuszach Google
Wszystkie powyższe przykłady działają tak samo w arkuszach Google, jak w programie Excel.
