Bendroji formulė
(=MIN(IF((rng1"")*(rng2""),rng1-rng2)))
Santrauka
Norėdami apskaičiuoti mažiausią skirtumą tarp dviejų verčių rinkinių, nepaisydami atvejų, kai kuri nors reikšmė yra tuščia, galite naudoti masyvo formulę, pagrįstą MIN ir IF funkcijomis. Parodytame pavyzdyje formulė F4 yra:
(=MIN(IF((B5:B12"")*(C5:C12""),B5:B12-C5:C12)))
kuris grąžina 115, mažiausią pardavimo kainą, neatsižvelgiant į atvejus, kai kuri nors vertė yra tuščia.
Pastaba: tai yra masyvo formulė, kurią reikia įvesti paspaudus „Control“ + „Shift“ + „Enter“.
Paaiškinimas
Parodytame pavyzdyje tikslas yra apskaičiuoti mažiausią pardavimo skirtumą atėmus išlaidas, tačiau tik tada, kai įvestos abi vertės. Jei kuri nors reikšmė yra tuščia, rezultato reikia nepaisyti. Norėdami patvirtinti, kad yra abi reikšmės, IF funkcija sukonfigūruota naudoti loginę logiką su šia išraiška:
(B5:B12"")*(C5:C12"")
Kadangi kiekviename diapazone yra 8 langeliai, šios operacijos rezultatas yra toks masyvas:
(1;1;1;0;1;1;0;0)
Šis masyvas veikia kaip filtras. Tais atvejais, kai vertė yra 1, IF leidžia vertėms perduoti MIN. Faktinės skirtumo vertės apskaičiuojamos naudojant kitą masyvo operaciją:
B5:B12-C5:C12
kuris generuoja šį rezultatą:
(150;255;125;1100;150;115;-890;1025)
Įvertinus loginį testą, masyvas, perduotas MIN funkcijai, atrodo taip:
(150;255;125;FALSE;150;115;FALSE;FALSE)
Atkreipkite dėmesį, kad „skirtumo vertė“ eilutėse, kuriose pardavimo ar sąnaudų laukai tušti, dabar yra FALSE. MIN funkcija automatiškai ignoruoja FALSE reikšmes ir grąžina mažiausią likusių skaičių 115.
Didžiausias skirtumas nepaisant ruošinių
Jei norite grąžinti didžiausią skirtumą, nepaisydami tuščių reikšmių, galite pakeisti MIN: MAX:
(=MAX(IF((B5:B12"")*(C5:C12""),B5:B12-C5:C12)))
Ši formulė veikia taip pat, kaip paaiškinta aukščiau.
Su MINIFS ir pagalbiniu stulpeliu
Funkciją MINIFS galima naudoti šiai problemai išspręsti, tačiau jai reikia naudoti pagalbinį stulpelį, kurio formulė yra tokia:
=B5-C5
Pagal D stulpelyje pateiktą formulę MINIFS galima naudoti taip:
=MINIFS(D5:D12,B5:B12,"",C5:C12,"")
Tai nėra masyvo formulė ir jos nereikia įvesti paspaudus „Control + Shift“ + „Enter“.
