Download het voorbeeldwerkboek
Deze zelfstudie laat zien hoe u ontbrekende nummers in een bereik kunt weergeven.
Overgeslagen nummers in volgorde weergeven
Om ontbrekende nummers in een lijst te vinden, kunnen we de volgende formule gebruiken:
| 1 | =KLEIN(ALS(AANTAL.ALS($B$3:$B$8,RIJ($1:$6))=0,RIJ($1:$6),""),RIJ(B1)) |
Opmerkingen:
- Dit is een matrixformule! Wanneer u Excel 2022 en eerder gebruikt, moet u de formule invoeren door op CTRL + SHIFT + ENTER te drukken. U weet dat de formule correct is ingevoerd als u accolades rond de formule ziet.
- Deze formule werkt alleen met positieve gehele getallen (gehele getallen).
Hoe werkt de formule?
Eerst wordt de ROW-functie gebruikt om een reeks getallen te genereren:
| 1 | {1;2;3;4;5;6} |
U kunt deze reeks getallen zelfs handmatig invoeren:
| 1 | =KLEIN(ALS(AANTAL.ALS($B$3:$B$8,{1;2;3;4;5;6})=0,{1;2;3;4;5;6},""), RIJ (B1)) |
Vervolgens telt de AANTAL.ALS-functie de waarden "als" ze overeenkomen met een waarde die in de array wordt gevonden.
Om dit te visualiseren, zullen we eerst het bereik $B$3:$B$8 converteren naar zijn reeks waarden {3;2;0;6;0;5}:
| 1 | =KLEIN(ALS(AANTAL.ALS({3;2;0;6;0;5},{1;2;3;4;5;6})=0,{1;2;3;4;5;6 },""),RIJ(B1)) |
Vervolgens zullen we de AANTAL.ALS-bewerking uitvoeren:
| 1 | =KLEIN(ALS({0;1;1;0;1;1}=0,{1;2;3;4;5;6},""),RIJ(B1)) |
Als er geen waarde wordt gevonden, wordt 0 uitgevoerd. De IF-functie controleert of de waarde NIET gevonden is en zo ja, retourneert de waarde uit de array, anders wordt leeg weergegeven:
| 1 | =KLEIN({1;"";"";4;"";""},RIJ(B1)) |
Als laatste retourneert de SMALL-functie de kleinste ontbrekende waarde in de geselecteerde cel.
| 1 | =KLEIN({1;"";"";4;"";""},{1}) |
| 1 | ={1} |
Tip:
Om de #NUM! fout, wikkel uw formule in met de IFERROR-functie:
| 1 | =ALS.ALS(AANTAL.ALS(B3:B8,RIJ(1:6))=0,RIJ(1:6),""),RIJ(B1)),"") |
Lijst overgeslagen nummers op volgorde in Google Spreadsheets
Alle hierboven uitgelegde voorbeelden werken hetzelfde in Google-bladen als in Excel.
