Excel ma już funkcje takie jak SUMIF i SUMIFS do sumowania danych według grup. Jednak; nie mogą one działać, jeśli masz połączone wartości czasu daty. Ten artykuł pokazuje Jak sumować według daty w Excelu bez czasu za pomocą funkcji SUMPRODUCT i INT.
Syntaktyka
=SUMPRODUCT( zakres wartości * ( INT( zakres daty-czasu ) = komórka daty )
Kroki
- Zacznij od =SUMPRODUCT( funkcja
- Typ wybierz zakres, który zawiera wartości $C$3:$C$16
- Użyj gwiazdki do iloczynu wartości zakresu z zakresem warunku i otwórz nawias *(
- Kontynuuj z INT($B$3:$B$16), aby usunąć część czasową
- Wprowadź warunek ze znakiem równości i zakresem kryteriów =E3
- Typ )), aby zamknąć funkcję i zakończyć formułę
Jak
Excel zachowuje wartości daty i czasu jako liczby. Excel zakłada, że historia zaczyna się od 1 stycznia 1900 r. i przyjmuje tę datę jako 1. Podczas gdy liczby całkowite reprezentują dni, dziesiętne reprezentują czas. Na przykład; 1/1/2018 jest równe 43101, 12:00 jest równe 0,5.
Możemy użyć funkcji INT, aby pozbyć się wartości czasu lub dziesiętnej części liczb. Funkcja INT po prostu zwraca całą część liczbową z wartości numerycznej. W naszym przypadku;
INT($B$3:$B$16) zwraca {43292;43292;43292;43292;43293;43293;43293;43293;43293;43293;43293;43293;43294;43294}
Jednakże; aby funkcja INT działała na tablicy, musimy użyć funkcji SUMPRODUCT. Zdolność funkcji SUMPRODUCT do obsługi tablic bez użycia formuł tablicowych zapewnia przewagę nad ograniczeniami funkcji SUMIF i SUMIFS. SUMPRODUCT może oceniać i zwracać tablice z warunków zakresu – wartości. Wykorzystujemy tę funkcjonalność do rozwiązywania par zakres – kryteria.
(INT($B$3:$B$16)=E3) zwraca {TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}
Funkcja SUMPRODUCT sumuje również wartości w swojej tablicy argumentów, jeśli jest tylko jeden argument. Jednak równania warunkowe zwracają wartości logiczne. Muszą więc zostać przekonwertowane na liczby, które są znaczące dla funkcji SUMPRODUCT. Na szczęście robi to operator iloczynu (*).
Mnożąc tablicę wartości z tablicą warunków, otrzymujemy wartości tablicowe wygenerowane zgodnie z naszymi potrzebami.
$C$3:$C$16*(INT($B$3:$B$16)=E3) zwraca {1;2;3;4;0;0;0;0;0;0;0;0}
W końcu SUMPRODUCT zwraca wyniki jako sumę według daty w Excelu, czyli to, czego szukamy.
=SUMPRODUCT($C$3:$C$16*(INT($B$3:$B$16)=E3))) zwraca 10 dla daty 7/11/2018.
Alternatywna opcja
Alternatywnym sposobem jest użycie tabeli przestawnej, która może dać Ci więcej opcji niż sumowanie wartości. Szczegółowe informacje można znaleźć w artykule Jak grupować dni bez czasu za pomocą tabeli przestawnej.
Zobacz również powiązane artykuły, jak obliczyć średnią ważoną za pomocą SUMPRODUCT, jak zsumować 2d zakresów za pomocą SUMPRODUCT oraz jak zsumować wartości z operatorem OR za pomocą SUMPRODUCT z wieloma kryteriami.