W tym artykule dowiemy się, jak liczyć daty danego roku w zakresie dat.
W prostych słowach, pracując z datami, czasami musimy policzyć komórki, w których są daty z danego roku.
Jak rozwiązać problem?
W tym artykule będziemy musieli użyć funkcji SUMPRODUCT. Jeśli podano nam daty z zakresu danych i konkretną wartość roku, musimy policzyć wartości dat, w których data należy do konkretnego roku.
Wzór ogólny:
= PRODUKT SUMA ( -- ( ROK(zakres) = rok ) )
zakres : wartości dat podane jako zakres
rok : wartość roku podana jako odniesienie do komórki.
Przykład:
Przetestujmy więc tę formułę, uruchamiając ją na poniższym przykładzie.
Tutaj mamy rekordy danych i musimy znaleźć wartości dat leżące w określonym roku lub zwrócić wiersze liczników, w których wartość daty leży w danym roku.
Po pierwsze, musimy zdefiniować nazwany zakres dla zakresu dat jako zakres. Po prostu wybierz wartości dat i wpisz nazwę (zakres) zakresu w lewym górnym rogu.
Teraz użyjemy następującego wzoru, aby obliczyć daty przypadające na rok 2016.
Użyj formuły:
= SUMPRODUCT ( -- ( ROK ( zakres ) = F4 ))
zasięg: nazwany zakres używany dla wartości dat D3:D11.
F4: wartość roku podana jako odwołanie do komórki.
Wyjaśnienie:
- Funkcja YEAR ( zakres ) wyodrębnia wartość roku z daty i dopasowuje ją do podanego roku i operatora.
= PRODUKT SUMA ( -- ( { 2019; 2018 ; 2019 ; 2016 ; 2020 ; 2019 ; 2017 ; 2016 ; 2020 } = 2016 ))
- operator = dopasowuje go do wartości roku 2019 i zwraca tablicę wartości TRUE i FALSE w zależności od operacji.
= PRODUKT SUMA ( -- ( { FAŁSZ ; FAŁSZ ; FAŁSZ ; PRAWDA ; FAŁSZ ; FAŁSZ ; FAŁSZ ; FAŁSZ } ) )
- -- operator używany do konwersji wartości TRUE na 1 i wartości Fałsz na 0.
= PRODUKT SUMA ( { 0 ; 0 ; 0 ; 1 ; 0 ; 0 ; 0 ; 1 ; 0 } )
- Funkcja SUMPRODUCT pobiera sumę 1s w zwróconej tablicy, która będzie liczbą wymaganych dat lub wierszy.
Tutaj wartość roku jest podana jako odwołanie do komórki, a zakres jako odwołanie do komórki. Naciśnij Enter, aby uzyskać liczbę.
Jak widać, całkowita data pasuje do roku 2016 wyszła na 2. Oznacza to, że istnieją 2 wartości dat lub 2 wiersze, w których wartość roku jest równa 2016.
Teraz skopiuj i wklej formułę do innych komórek za pomocą CTRL + D lub przeciągnij w dół opcję programu Excel.
Jak widać na powyższym zrzucie, wszystkie wartości danych, które leżą w określonym roku, uzyskujemy za pomocą formuły Excela. Możesz również uzyskać czeki na to samo, korzystając z opcji filtra programu Excel. Zastosuj filtr do nagłówka Daty i usuń zaznaczenie opcji zaznacz wszystko i wybierz opcję roku, którą chcesz filtrować.
Opcja filtrowania zwraca w rezultacie 3 wartości. Wykonaj to samo z innymi wartościami dat, aby uzyskać liczbę dat lub wierszy spełniających kryteria podane w określonym roku.
Oto kilka uwag obserwacyjnych pokazanych poniżej.
Uwagi:
- Formuła działa tylko z liczbami.
- Formuła działa tylko wtedy, gdy w tabeli przeglądowej nie ma duplikatów.
- Funkcja SUMPRODUCT traktuje wartości nieliczbowe jako 0s.
- Funkcja SUMPRODUCT traktuje wartość logiczną TRUE jako 1 i False jako 0.
- Tablica argumentów musi mieć taką samą długość jak funkcja.
Mam nadzieję, że ten artykuł o tym, jak zliczyć liczbę zwrotów, jeśli z SUMPRODUCT w programie Excel jest objaśniający. Więcej artykułów na temat funkcji SUMPRODUCT znajdziesz tutaj. Podziel się swoim zapytaniem poniżej w polu komentarza. Pomożemy Ci.
Jak korzystać z funkcji SUMA PRODUKT w programie Excel: Zwraca SUMA po pomnożeniu wartości w wielu tablicach w programie Excel.
LICZ.IFS z zakresem kryteriów dynamicznych : Policz komórki w zależności od innych wartości komórek w programie Excel.
LICZNIKI Dopasowanie dwóch kryteriów : Policz komórki spełniające dwa różne kryteria na liście w programie Excel.
LICZNIKI Z LUB dla wielu kryteriów : Policz komórki spełniające wiele kryteriów za pomocą funkcji LUB.
Funkcja LICZ.WARUNKI w programie Excel : Policz komórki w zależności od innych wartości komórek.
Jak korzystać z Countif w VBA w programie Microsoft Excel : Policz komórki przy użyciu kodu Visual Basic for Applications.
Jak używać symboli wieloznacznych w programie Excel : Policz komórki pasujące do fraz za pomocą symboli wieloznacznych w programie Excel
popularne artykuły
50 skrótów Excela, aby zwiększyć produktywność
Edytuj listę rozwijaną
Absolutne odniesienie w Excelu
Jeśli z formatowaniem warunkowym
e rok 2019, wybierz rok 2019 i kliknij OK.