Excel

Získajte najskoršie a najnovšie termíny projektov

Get Earliest Latest Project Dates

Vzorec programu Excel: Získajte najskoršie a najnovšie dátumy projektovZhrnutie

Tento príklad ukazuje, ako načítať najskoršie a najnovšie dátumy súvisiace s projektom. V uvedenom príklade vzorce v H5 a I5 sú:





 
= MINIFS (data[Start],data[Project],G5) // earliest = MAXIFS (data[End],data[Project],G5) // latest

kde „údaje“ sú Excelová tabuľka ako je znázornené, a názvy projektov v stĺpci G sa zhodujú s názvami stĺpcov B.

Poznámka: MINIFS a MAXIFS sú k dispozícii iba v Excel 365 a Excel 2019. V iných verziách Excelu môžete použiť jednoduchý vzorec poľa, ako je vysvetlené nižšie.





Úvod

Úlohou tu je nájsť najskoršie a najnovšie dátumy súvisiace s daným projektom. Najskoršie dátumy pochádzajú z Začnite stĺpci a najnovšie dátumy pochádzajú z Koniec stĺpci.

Môžete byť v pokušení použiť funkciu vyhľadávania ako VLOOKUP , XLOOKUP , alebo INDEX a ZHODA. Keďže však každý projekt má viac ako jeden záznam a položky nemusia byť vždy zoradené podľa dátumu, je to náročné.



Lepším prístupom je použiť postup eliminácie: zahodiť dátumy pre iné projekty a pracovať iba s dátumami, ktoré zostávajú.

Vysvetlenie

The Funkcia MINIFS vráti najmenší číselnú hodnotu, ktorá spĺňa dodané kritériá, a Funkcia MAXIFS vráti najväčší číselná hodnota, ktorá spĺňa dodané kritériá.

Rovnako ako COUNTIFS a SUMIFS, tieto funkcie používajú na aplikovanie podmienok „páry“ rozsahu/kritéria. Pre oba vzorce potrebujeme iba jednu podmienku: názov projektu sa musí zhodovať s názvom v stĺpci G:

 
data[Project],G5 // condition

Ak chcete získať najskôr dátum začiatku, používame:

 
= MINIFS (data[Start],data[Project],G5) // earliest date

Tu MINIFS vráti súbor minimum hodnota v Začnite stĺpec, kde sa projekt rovná „Omega“ (z bunky G5). Od Dátumy Excelu sú iba čísla , minimálny dátum je rovnaký ako najskorší dátum.

ako odčítať mesiace v exceli

Ak chcete získať najnovšie dátum ukončenia, používame:

 
= MAXIFS (data[End],data[Project],G5) // latest date

Tu MAXIFS vracia maximum hodnota v Koniec stĺpci, kde sa projekt rovná „Omega“. Ako je uvedené vyššie, maximálna hodnota je rovnaká ako posledný dátum.

Alternatíva vzorca poľa

Ak nemáte MINIFS a MAXIFS, môžete použiť jednoduché vzorce poľa založené na MIN a MAX funkcií, aby ste dosiahli rovnaký výsledok. Najskorší dátum začiatku:

 
{= MIN ( IF (data[Project]=G5,data[Start]))}

Najnovší dátum ukončenia:

 
{= MAX ( IF (data[Project]=G5,data[End]))}

Poznámka: oba vzorce sú maticové vzorce a je potrebné ho zadať kombináciou klávesov Ctrl + Shift + Enter v programe Excel 2019 alebo staršom. V programe Excel 365 môžete vzorce zadávať normálne, pretože maticové vzorce sú natívne .

V oboch prípadoch platí IF funkcia používa sa na „filtrovanie“ hodnôt dátumu takto:

 
 IF (data[Project]=G5,data[End]) // filter dates by project

Keď je G5 „Omega“, IF vráti dátum ukončenia. V opačnom prípade IF vráti FALSE. Pretože testujeme všetky názvy projektov v tabuľke súčasne, výsledkom je pole hodnôt takto:

 
{439364398343990FALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSE}

Veľké sériové čísla sú dátumy programu Excel spojené s projektom Omega. Ostatné hodnoty sú NEPRAVDIVÉ, pretože projekt nie je Omega. Pretože MIN a MAX sú naprogramované tak, aby ignorovali logické hodnoty PRAVDA a NEPRAVDA, fungujú iba so zvyšnými hodnotami. MIN vráti najmenší (najskorší) dátum a MAX vráti najväčší (najnovší) dátum.

Prílohy Súbor získať najskoršie a najnovšie dátumy projektu.xlsx Autor Dave Bruns


^