Ladda ner exempelarbetsboken
Denna handledning visar hur du listar saknade nummer i ett intervall.
Lista hoppade nummer i sekvens
För att hitta saknade nummer i en lista kan vi använda följande formel:
| 1 | = LILLA (OM (RÄTTA ($ B $ 3: $ B $ 8, RAD ($ 1: $ 6)) = 0, RAD ($ 1: $ 6), ""), RAD (B1)) |
Anmärkningar:
- Detta är en matrisformel! När du använder Excel 2022 och tidigare måste du ange formeln genom att trycka på CTRL + SKIFT + ENTER. Du vet att formeln har angetts korrekt när du ser lockiga parenteser runt formeln.
- Denna formel fungerar bara med positiva heltal (heltal).
Hur fungerar formeln?
Först används ROW -funktionen för att generera en rad nummer:
| 1 | {1;2;3;4;5;6} |
Faktum är att du till och med kan ange den här siffran manuellt:
| 1 | = LILLA (OM (RÄTTA ($ B $ 3: $ B $ 8, {1; 2; 3; 4; 5; 6}) = 0, {1; 2; 3; 4; 5; 6}, "")), RAD (B1)) |
Därefter räknar COUNTIF -funktionen värdena "om" de matchar ett värde som finns i matrisen.
För att visualisera detta kommer vi först att konvertera intervallet $ B $ 3: $ B $ 8 till dess uppsättning värden {3; 2; 0; 6; 0; 5}:
| 1 | = LILLA (OM (RÄTTA ({3; 2; 0; 6; 0; 5}, {1; 2; 3; 4; 5; 6}) = 0, {1; 2; 3; 4; 5; 6 }, ""), RAD (B1)) |
Därefter utför vi COUNTIF -operationen:
| 1 | = LITEN (OM ({0; 1; 1; 0; 1; 1} = 0, {1; 2; 3; 4; 5; 6}, ""), RAD (B1)) |
Om ett värde inte hittas matas 0 ut. IF -funktionen kontrollerar om värdet INTE hittas och returnerar i så fall värdet från matrisen, annars matas det ut tomt:
| 1 | = LILLA ({1; ""; ""; 4; ""; ""}, RAD (B1)) |
Sist returnerar SMALL -funktionen det minsta saknade värdet i den markerade cellen.
| 1 | = LILLA ({1; ""; ""; 4; ""; ""}, {1}) |
| 1 | ={1} |
Dricks:
För att undvika #NUM! fel, linda din formel med IFERROR -funktionen:
| 1 | = IFERROR (Liten (OM (RÄKNING (B3: B8, RAD (1: 6)) = 0, RAD (1: 6), ""), RAD (B1)), "") |
Lista hoppade nummer i sekvens i Google Kalkylark
Alla exemplen som förklaras ovan fungerar på samma sätt i Google -blad som i Excel.
