Download eksemplet på projektmappe
Denne vejledning demonstrerer, hvordan du angiver manglende numre i et område.
Liste over springede tal i rækkefølge
For at finde manglende tal på en liste kan vi bruge følgende formel:
| 1 | = LILLE (HVIS (COUNTIF ($ B $ 3: $ B $ 8, ROW ($ 1: $ 6)) = 0, ROW ($ 1: $ 6), ""), ROW (B1)) |
Bemærkninger:
- Dette er en matrixformel! Når du bruger Excel 2022 og tidligere, skal du indtaste formlen ved at trykke på CTRL + SKIFT + ENTER. Du ved, at formlen er indtastet korrekt, når du ser krøllede parenteser omkring formlen.
- Denne formel fungerer kun med positive hele tal (heltal).
Hvordan fungerer formlen?
For det første bruges ROW -funktionen til at generere en række tal:
| 1 | {1;2;3;4;5;6} |
Faktisk kan du endda indtaste i denne række tal manuelt:
| 1 | = LILLE (HVIS (COUNTIF ($ B $ 3: $ B $ 8, {1; 2; 3; 4; 5; 6}) = 0, {1; 2; 3; 4; 5; 6}, "")), RÆKKE (B1)) |
Derefter tæller COUNTIF -funktionen værdierne "hvis" de matcher en værdi, der findes i arrayet.
For at visualisere dette vil vi først konvertere intervallet $ B $ 3: $ B $ 8 til dets array af værdier {3; 2; 0; 6; 0; 5}:
| 1 | = LILLE (HVIS (ANTAL ({3; 2; 0; 6; 0; 5}, {1; 2; 3; 4; 5; 6}) = 0, {1; 2; 3; 4; 5; 6 }, ""), RÆKKE (B1)) |
Dernæst udfører vi COUNTIF -operationen:
| 1 | = LILLE (HVIS ({0; 1; 1; 0; 1; 1} = 0, {1; 2; 3; 4; 5; 6}, ""), RÆK (B1)) |
Hvis der ikke findes en værdi, udsendes 0. IF -funktionen kontrollerer, om værdien IKKE findes, og returnerer i så fald værdien fra arrayet, ellers udsendes den tom:
| 1 | = LILLE ({1; ""; ""; 4; ""; ""}, RÆK (B1)) |
Sidst returnerer SMALL -funktionen den mindste manglende værdi i den valgte celle.
| 1 | = LILLE ({1; ""; ""; 4; ""; ""}, {1}) |
| 1 | ={1} |
Tip:
For at undgå #NUM! fejl, pakk din formel ind med IFERROR -funktionen:
| 1 | = IFERROR (SMALL (IF (COUNTIF (B3: B8, ROW (1: 6)) = 0, ROW (1: 6), ""), ROW (B1)), "") |
Liste over springede numre i rækkefølge i Google Sheets
Alle eksemplerne forklaret ovenfor fungerer på samme måde i Google -ark som i Excel.
