Podsumowanie
Ten przykład pokazuje, jak pobrać najwcześniejszą i najpóźniejszą datę skojarzoną z projektem. W pokazanym przykładzie formuły w H5 i I5 to:
=MINIFS(data(Start),data(Project),G5) // earliest =MAXIFS(data(End),data(Project),G5) // latest
gdzie „dane” to tabela programu Excel, jak pokazano, a nazwy projektów w kolumnie G są zgodne z tymi z kolumny B.
Uwaga: MINIFS i MAXIFS są dostępne tylko w programach Excel 365 i Excel 2019. W innych wersjach programu Excel można użyć prostej formuły tablicowej, jak wyjaśniono poniżej.
Wprowadzenie
Zadaniem tutaj jest znalezienie najwcześniejszych i najpóźniejszych dat związanych z danym projektem. Najwcześniejsze daty pochodzą z kolumny Początek , a najpóźniejsze z kolumny Koniec .
Możesz ulec pokusie skorzystania z funkcji wyszukiwania, takiej jak WYSZUKAJ.PIONOWO, XLOOKUP lub INDEKS i PODAJ. Ponieważ jednak każdy projekt ma więcej niż jeden wpis, a wpisy nie zawsze mogą być sortowane według daty, staje się to trudne.
Lepszym podejściem jest użycie procesu eliminacji: odrzucanie dat dla innych projektów i praca tylko z pozostałymi datami.
Wyjaśnienie
Funkcja MINIFS zwraca najmniejszą wartość liczbową spełniającą podane kryteria, a funkcja MAXIFS zwraca największą wartość liczbową spełniającą podane kryteria.
Podobnie jak LICZ.WARUNKI i SUMA.WARUNKÓW, funkcje te używają „par” zakres / kryteria do stosowania warunków. W przypadku obu formuł potrzebujemy tylko jednego warunku: nazwa projektu musi odpowiadać nazwie w kolumnie G:
data(Project),G5 // condition
Aby uzyskać najwcześniejszą datę rozpoczęcia, używamy:
=MINIFS(data(Start),data(Project),G5) // earliest date
W tym przypadku funkcja MINIFS zwraca minimalną wartość w kolumnie Start, w której projekt jest równy „Omega” (z komórki G5). Ponieważ daty programu Excel to tylko liczby, minimalna data jest taka sama jak najwcześniejsza data.
Aby uzyskać najnowszą datę zakończenia, używamy:
=MAXIFS(data(End),data(Project),G5) // latest date
Tutaj MAXIFS zwraca maksymalną wartość w kolumnie End, w której projekt jest równy „Omega”. Jak wyżej, maksymalna wartość jest taka sama jak ostatnia data.
Alternatywna formuła tablicowa
Jeśli nie masz opcji MINIFS i MAXIFS, możesz użyć prostych formuł tablicowych opartych na funkcjach MIN i MAX, aby uzyskać ten sam wynik. Najwcześniejsza data rozpoczęcia:
(=MIN(IF(data(Project)=G5,data(Start))))
Najpóźniejsza data zakończenia:
(=MAX(IF(data(Project)=G5,data(End))))
Uwaga: obie formuły są formułami tablicowymi i należy je wprowadzić za pomocą Ctrl + Shift + Enter w programie Excel 2019 lub starszym. W programie Excel 365 możesz normalnie wprowadzać formuły, ponieważ formuły tablicowe są natywne.
W obu przypadkach funkcja JEŻELI służy do „filtrowania” wartości dat w następujący sposób:
IF(data(Project)=G5,data(End)) // filter dates by project
Gdy G5 to „Omega”, funkcja JEŻELI zwraca datę końcową. W przeciwnym razie funkcja JEŻELI zwraca FAŁSZ. Ponieważ testujemy wszystkie nazwy projektów w tabeli w tym samym czasie, wynikiem jest tablica wartości, takich jak ta:
(43936;43983;43990;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Duże numery seryjne to daty programu Excel związane z projektem Omega. Pozostałe wartości to FAŁSZ, ponieważ projekt nie jest Omegą. Ponieważ MIN i MAX są zaprogramowane tak, aby ignorować wartości logiczne PRAWDA i FAŁSZ, działają one tylko na pozostałych wartościach. MIN zwraca najmniejszą (najwcześniejszą) datę, a MAX zwraca największą (najpóźniejszą) datę.
