Splošna formula
(=MIN(IF((rng1"")*(rng2""),rng1-rng2)))
Povzetek
Če želite izračunati najmanjšo razliko med dvema nizoma vrednosti, če ne upoštevate primerov, ko je katera koli vrednost prazna, lahko uporabite matrično formulo, ki temelji na funkcijah MIN in IF. V prikazanem primeru je formula v F4:
(=MIN(IF((B5:B12"")*(C5:C12""),B5:B12-C5:C12)))
ki vrne 115, najmanjši prodajni strošek, pri čemer se ne upoštevajo primeri, ko je katera koli vrednost prazna.
Opomba: to je matrična formula, ki jo je treba vnesti s tipkama Control + Shift + Enter.
Pojasnilo
V prikazanem primeru je cilj izračunati najmanjšo razliko v prodaji minus stroški, vendar le, če sta vneseni obe vrednosti. Če je katera koli vrednost prazna, je treba rezultat prezreti. Za potrditev, da sta na voljo obe vrednosti, je funkcija IF konfigurirana za uporabo logične logike s tem izrazom:
(B5:B12"")*(C5:C12"")
Ker vsak obseg vsebuje 8 celic, je rezultat te operacije matrika, kot je ta:
(1;1;1;0;1;1;0;0)
Ta matrika deluje kot filter. V primerih, ko je vrednost 1, IF dovoli, da vrednosti prehajajo na MIN. Dejanske vrednosti razlike se izračunajo z drugo operacijo matrike:
B5:B12-C5:C12
ki ustvari ta rezultat:
(150;255;125;1100;150;115;-890;1025)
Po ovrednotenju logičnega testa je matrika, prenesena v funkcijo MIN, videti takole:
(150;255;125;FALSE;150;115;FALSE;FALSE)
Upoštevajte, da je "vrednost razlike" za vrstice, kjer so prazne bodisi Prodaja bodisi Stroški, zdaj FALSE. Funkcija MIN samodejno prezre vrednosti FALSE in vrne najmanj preostalih številk, 115.
Največja razlika ob neupoštevanju praznih prostorov
Če želite vrniti največjo razliko, če ne upoštevate praznih vrednosti, lahko nadomestite MAX za MIN:
(=MAX(IF((B5:B12"")*(C5:C12""),B5:B12-C5:C12)))
Ta formula deluje na enak način, kot je razloženo zgoraj.
Z MINIFI in pomožnim stolpcem
Funkcijo MINIFS lahko uporabimo za reševanje te težave, vendar zahteva uporabo pomožnega stolpca s formulo, kot je ta:
=B5-C5
Z zgornjo formulo v stolpcu D lahko MINIFS uporabimo tako:
=MINIFS(D5:D12,B5:B12,"",C5:C12,"")
To ni formula matrike in je ni treba vnašati s tipko control + shift + enter.
