Generieke formule
(=MODE(IF(1-ISNUMBER(MATCH(data,exp_rng,0)),data)))
Samenvatting
Om de meest voorkomende getallen in een kolom op te sommen (dwz meest voorkomende, tweede meest voorkomende, derde meest voorkomende, enz.), Kunt u een matrixformule gebruiken op basis van vier Excel-functies: ALS, MODUS, VERGELIJKEN en ISGETAL. In het getoonde voorbeeld is de formule in D5:
(=MODE(IF(1-ISNUMBER(MATCH(data,$D$4:D4,0)),data)))
waarbij "data" het benoemde bereik B5: B16 is. De formule wordt vervolgens gekopieerd naar rijen onder D5 om de gewenste lijst met de meest voorkomende nummers uit te voeren.
Opmerking: dit is een matrixformule en moet worden ingevoerd met control + shift + enter.
Uitleg
De kern van deze formule is de functie MODUS, die het meest voorkomende getal in een bereik of matrix retourneert. De rest van de formule construeert gewoon een gefilterde matrix die MODUS in elke rij kan gebruiken. Het uitbreidende bereik $ D $ 4: D4 werkt om getallen uit te sluiten die al in $ D $ 4: D4 zijn uitgevoerd.
Van binnen naar buiten werken:
- De MATCH-functie wordt gebruikt, controleer eerst alle getallen in het benoemde bereik "data" met bestaande getallen in het uitbreidende bereik $ D $ 4: D4
- ISNUMBER converteert overeenkomende waarden naar TRUE en niet-overeenkomende waarden naar FALSE
- 1-NUMBER keert de array om, en de wiskundige bewerking voert enen en nullen uit
- IF gebruikt de array-uitvoer van # 3 hierboven om de originele lijst met waarden te filteren, met uitzondering van getallen die al in $ D $ 4: D4 staan
- De functie MODE retourneert het meest voorkomende getal in de array-uitvoer in stap 4
In cel D5 vindt geen filtering plaats en de uitvoer van elke bovenstaande stap ziet er als volgt uit:
(#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A) (FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE) (1;1;1;1;1;1;1;1;1;1;1;1) (93;92;93;94;95;96;97;98;99;93;97;95) 93
In cel D6, met 93 al in D5, ziet de uitvoer er als volgt uit:
(2;#N/A;2;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;2;#N/A;#N/A) (TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE) (0;1;0;1;1;1;1;1;1;0;1;1) (FALSE;92;FALSE;94;95;96;97;98;99;FALSE;97;95) 95
Omgaan met fouten
De MODE-functie retourneert de fout # N / A als er geen modus is. Terwijl u de formule naar volgende rijen kopieert, zult u waarschijnlijk de fout # N / A tegenkomen. Om deze fout op te vangen en in plaats daarvan een lege string ("") te retourneren, kunt u IFERROR als volgt gebruiken:
=IFERROR(MODE(IF(1-ISNUMBER(MATCH(data,$D$4:D4,0)),data)),"")
