Všeobecný vzorec
(=MIN(IF((rng1"")*(rng2""),rng1-rng2)))
Zhrnutie
Ak chcete vypočítať minimálny rozdiel medzi dvoma množinami hodnôt, ignorujúc prípady, keď je ktorákoľvek z týchto hodnôt prázdna, môžete použiť vzorec poľa založený na funkciách MIN a IF. V zobrazenom príklade je vzorec v F4:
(=MIN(IF((B5:B12"")*(C5:C12""),B5:B12-C5:C12)))
ktorá vráti 115, minimum predajných nákladov, ignoruje prípady, keď je ktorákoľvek hodnota prázdna.
Poznámka: toto je vzorec poľa a musí byť zadaný pomocou klávesov Control + Shift + Enter.
Vysvetlenie
V zobrazenom príklade je cieľom vypočítať minimálny rozdiel predaja mínus náklady, ale iba ak sú zadané obe hodnoty. Ak je ktorákoľvek hodnota prázdna, výsledok by sa mal ignorovať. Na potvrdenie dostupnosti oboch hodnôt je funkcia IF nakonfigurovaná na použitie logickej logiky s týmto výrazom:
(B5:B12"")*(C5:C12"")
Pretože každý rozsah obsahuje 8 buniek, výsledkom tejto operácie je pole podobné tomuto:
(1;1;1;0;1;1;0;0)
Toto pole funguje ako filter. V prípadoch, keď je hodnota 1, IF umožňuje, aby hodnoty prešli na MIN. Skutočné hodnoty rozdielov sa počítajú pomocou inej operácie s poľom:
B5:B12-C5:C12
ktorý generuje tento výsledok:
(150;255;125;1100;150;115;-890;1025)
Po vyhodnotení logického testu vyzerá pole odovzdané do funkcie MIN takto:
(150;255;125;FALSE;150;115;FALSE;FALSE)
Všimnite si, že „hodnota rozdielu“ pre riadky, kde sú buď tržby, alebo náklady prázdne, je teraz NEPRAVDA. Funkcia MIN automaticky ignoruje FALSE hodnoty a vráti minimum zvyšných čísel, 115.
Maximálny rozdiel ignorujúci medzery
Ak chcete vrátiť maximálny rozdiel ignorujúci prázdne hodnoty, môžete namiesto MAX nahradiť MIN:
(=MAX(IF((B5:B12"")*(C5:C12""),B5:B12-C5:C12)))
Tento vzorec funguje rovnako, ako je vysvetlené vyššie.
S MINIFS a pomocnou kolónou
Na vyriešenie tohto problému je možné použiť funkciu MINIFS, ktorá si však vyžaduje použitie pomocného stĺpca s týmto vzorcom:
=B5-C5
S vyššie uvedeným vzorcom v stĺpci D možno MINIFS použiť takto:
=MINIFS(D5:D12,B5:B12,"",C5:C12,"")
Toto nie je vzorec poľa a nemusí sa zadávať pomocou klávesov control + shift + enter.
