Santrauka
Šis pavyzdys parodo, kaip gauti ankstyviausias ir paskutines su projektu susijusias datas. Parodytame pavyzdyje formulės H5 ir I5 yra:
=MINIFS(data(Start),data(Project),G5) // earliest =MAXIFS(data(End),data(Project),G5) // latest
kur „duomenys“ yra „Excel“ lentelė, kaip parodyta, o projekto pavadinimai G stulpelyje atitinka tuos B stulpelius.
Pastaba: MINIFS ir MAXIFS galimi tik „Excel 365“ ir „Excel 2019“. Kitose „Excel“ versijose galite naudoti paprastą masyvo formulę, kaip paaiškinta toliau.
Įvadas
Čia užduotis yra rasti ankstyviausias ir paskutines datas, susijusias su konkrečiu projektu. Ankstyviausios datos pateikiamos iš stulpelio Pradėti , o paskutinės - iš stulpelio Pabaiga .
Jums gali kilti pagunda naudoti paieškos funkciją, pvz., VLOOKUP, XLOOKUP arba INDEX ir MATCH. Tačiau kadangi kiekviename projekte yra daugiau nei vienas įrašas ir įrašai ne visada gali būti rūšiuojami pagal datą, tai tampa sudėtinga.
Geresnis būdas yra naudoti pašalinimo procesą: išmesti kitų projektų datas ir dirbti tik su likusiomis datomis.
Paaiškinimas
Funkcija MINIFS grąžina mažiausią skaitinę vertę, atitinkančią pateiktus kriterijus, o funkcija MAXIFS - didžiausią skaitinę vertę, atitinkančią pateiktus kriterijus.
Kaip ir COUNTIFS ir SUMIFS, šioms sąlygoms taikyti šiose funkcijose naudojamas diapazono / kriterijų „poros“. Abiem formulėms mums reikia tik vienos sąlygos: projekto pavadinimas turi būti lygus pavadinimui G stulpelyje:
data(Project),G5 // condition
Norėdami gauti anksčiausią pradžios datą, mes naudojame:
=MINIFS(data(Start),data(Project),G5) // earliest date
Čia MINIFS grąžina mažiausią reikšmę stulpelyje Pradėti, kur projektas yra lygus „Omega“ (iš langelio G5). Kadangi „Excel“ datos yra tik skaičiai, minimali data yra tokia pati kaip anksčiausia.
Norėdami gauti naujausią pabaigos datą, mes naudojame:
=MAXIFS(data(End),data(Project),G5) // latest date
Čia MAXIFS grąžina maksimalią reikšmę stulpelyje Pabaiga, kur projektas yra lygus „Omega“. Kaip aukščiau, maksimali vertė yra tokia pati kaip ir paskutinė data.
Masyvo formulės alternatyva
Jei neturite MINIFS ir MAXIFS, galite naudoti paprastas masyvo formules, pagrįstas MIN ir MAX funkcijomis, kad gautumėte tą patį rezultatą. Ankstyviausia pradžios data:
(=MIN(IF(data(Project)=G5,data(Start))))
Naujausia pabaigos data:
(=MAX(IF(data(Project)=G5,data(End))))
Pastaba: abi formulės yra masyvo formulės ir jas reikia įvesti „Control“ + „Shift“ + „Enter“, „Excel 2019“ ar ankstesnėse versijose. Naudodami „Excel 365“, formules galite įvesti paprastai, nes masyvo formulės yra vietinės.
Abiem atvejais „IF“ funkcija naudojama datų reikšmėms „filtruoti“ taip:
IF(data(Project)=G5,data(End)) // filter dates by project
Kai G5 yra „Omega“, IF grąžina pabaigos datą. Kitu atveju IF grąžina FALSE. Kadangi mes vienu metu bandome visus projekto pavadinimus lentelėje, rezultatas yra tokių reikšmių masyvas:
(43936;43983;43990;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Dideli serijos numeriai yra „Excel“ datos, susijusios su projektu „Omega“. Kitos vertybės yra NETIESA, nes projektas nėra „Omega“. Kadangi MIN ir MAX yra užprogramuoti ignoruoti logines reikšmes TRUE ir FALSE, jie veikia tik likusias reikšmes. MIN grąžina mažiausią (ankstyviausią) datą, o MAX - didžiausią (vėliausią) datą.
