Excel formula: norādiet visbiežāk sastopamos skaitļus -

Vispārēja formula

(=MODE(IF(1-ISNUMBER(MATCH(data,exp_rng,0)),data)))

Kopsavilkums

Lai uzskaitītu kolonnā visbiežāk sastopamos skaitļus (ti, visizplatītāko, otro visizplatītāko, trešo visizplatītāko utt.), Jūs varat izveidot masīva formulu, kuras pamatā ir četras Excel funkcijas: IF, MODE, MATCH un ISNUMBER. Parādītajā piemērā formula D5 ir:

(=MODE(IF(1-ISNUMBER(MATCH(data,$D$4:D4,0)),data)))

kur "dati" ir nosauktais diapazons B5: B16. Pēc tam formula tiek kopēta rindās zem D5, lai izlaistu vēlamo biežāko numuru sarakstu.

Piezīme: šī ir masīva formula, un tā jāievada ar vadības taustiņu + shift + enter.

Paskaidrojums

Šīs formulas kodols ir funkcija MODE, kas atgriež diapazonā vai masīvā visbiežāk sastopamo skaitli. Pārējā formula tikai izveido filtrētu masīvu MODE lietošanai katrā rindā. Paplašinātais diapazons $ D $ 4: D4 darbojas, lai izslēgtu skaitļus, kas jau tiek ievadīti $ D $ 4: D4.

Darbs no iekšpuses uz āru:

  1. Funkcija MATCH vispirms tiek izmantota, lai pārbaudītu visus nosauktajā diapazonā esošos skaitļus "dati" salīdzinājumā ar esošajiem skaitļiem diapazonā, kas paplašinās $ D $ 4: D4
  2. ISNUMBER pārveido saskaņotās vērtības par TRUE un nesaskaņotās vērtības uz FALSE
  3. 1-NUMBER maina masīvu, un matemātikas operācija izved vienumus un nulles
  4. IF izmanto masīva izvadi no # 3, lai filtrētu sākotnējo vērtību sarakstu, izņemot skaitļus, kas jau ir $ D $ 4: D4
  5. Funkcija MODE atgriež biežāko masīva izvades numuru 4. solī

Šūnā D5 filtrēšana nenotiek, un katra iepriekš norādītā soļa izeja izskatās šādi:

(#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

Šūnā D6, kurā 93 jau atrodas D5, izeja izskatās šādi:

(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

Rīcības kļūdas

Ja režīma nav, funkcija MODE atgriezīs kļūdu # N / A. Kopējot formulu uz nākamajām rindām, iespējams, radīsies kļūda # N / A. Lai notvertu šo kļūdu un tā vietā atgrieztu tukšu virkni (""), varat izmantot IFERROR šādi:

=IFERROR(MODE(IF(1-ISNUMBER(MATCH(data,$D$4:D4,0)),data)),"")

Interesanti raksti...