Sumuj wartości według roku w Excelu
Przypuśćmy, że masz zakres danych, który zawiera listę dat i kwot sprzedaży, teraz chcesz uzyskać całkowitą kwotę na podstawie danych lat, jak pokazano na zrzucie ekranu. W programie Excel normalne funkcje SUMIFS i SUMPRODUCT mogą pomóc w szybkim i łatwym rozwiązaniu tego zadania. Ten samouczek pokaże szczegółowo, jak korzystać z tych dwóch funkcji.
- Sumuj wartości według roku w programie Excel z funkcjami SUMAFS i DATE
- Sumuj wartości według roku w Excelu z funkcją SUMPRODUCT
Sumuj wartości według roku w programie Excel z funkcjami SUMAFS i DATE
Aby zsumować łączną wartość na podstawie danego roku, należy określić pierwszą i ostatnią datę roku za pomocą funkcji DATE, a następnie użyć funkcji SUMA.FS, aby zsumować wszystkie wartości w określonych datach, ogólna składnia to:
- sum_range: zakres wartości do zsumowania;
- date_range: zakres dat do przetestowania dla określonego roku;
- year: Konkretny rok, na podstawie którego chcesz zsumować wartości. (rok, 1,1 wskazuje pierwszy dzień roku i rok,12,31 wskazuje ostatni dzień roku.)
Aby rozwiązać to zadanie w programie Excel, skopiuj lub wprowadź poniższą formułę do pustej komórki, a następnie przeciągnij formułę do innych komórek, aby uzyskać wynik:
Wyjaśnienie tego wzoru:
=SUMIFS($C$2:$C$12,$B$2:$B$12,">="&DATE(E2,1,1),$B$2:$B$12,"<="&DATE(E2,12,31))
- 2 C $: 12 C $: to „zakres_sum” zawierający wartości, które chcemy zsumować.
- $ B$2: $B$12,">="&DATA(E2,1,1): sprawdza daty z zakresu B2:B12, które są większe lub równe 01 – pierwszy dzień roku w komórce E01.
- $B2:$B$12,"<="&DATA(E2,12,31): sprawdza daty z zakresu B2:B12, które są mniejsze lub równe 12 – ostatni dzień roku w komórce E31.
- SUMIFS($C$2:$C$12,$B$2:$B$12,">="&DATE(E2,1,1),$B$2:$B$12,"<="&DATE(E2,12,31)):Po spełnieniu kryteriów zostaną znalezione i zsumowane wartości z zakresu C2:C12.
Sumuj wartości według roku w Excelu z funkcją SUMPRODUCT
W tym przypadku pomocna może być również inna prosta formuła utworzona przez funkcję SUMPRODUCT, ogólna składnia to:
- date_range: zakres dat do przetestowania dla określonego roku;
- year: Konkretny rok, na podstawie którego chcesz zsumować wartości;
- sum_range: Zakres wartości do zsumowania.
Skopiuj lub wprowadź następującą formułę do pustej komórki, a następnie przeciągnij formułę w dół do innych komórek, aby uzyskać wynik:
Wyjaśnienie tego wzoru:
=SUMPRODUCT((YEAR($B$2:$B$12)=E2)*$C$2:$C$12)
- ROK ($ B$2:$B$12)=E2:Funkcja ROK zwraca numer roku z każdej komórki daty w zakresie B2:B12. To wyrażenie sprawdza, czy rok w E2 jest równy rokowi z zakresu B2:B12. Otrzymasz tablicę taką jak ta: {PRAWDA;FAŁSZ;PRAWDA;PRAWDA;FAŁSZ;PRAWDA;PRAWDA;FAŁSZ;FAŁSZ;PRAWDA;FAŁSZ}.
- (YEAR($B$2:$B$12)=E2)*$C$2:$C$12={TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE}*$C$2:$C$12: W tym wyrażeniu wartości TRUE i FALSE zostaną ustawione na 1 i 0, a ta tablica jest mnożona przez każdą wartość z zakresu C2:C12, a otrzymasz nową tablicę w następujący sposób: {347;0;398;430; 0;379;412;0;0;420;0} .
- SUMPRODUCT((YEAR($B$2:$B$12)=E2)*$C$2:$C$12)= SUMPRODUCT({347;0;398;430;0;379;412;0;0;420;0}): Funkcja SUMPRODUCT w końcu sumuje wszystkie liczby w tej tablicy i zwraca wynik jako 2386.
Zastosowana funkcja względna:
- SUMA:
- Funkcja SUMIFS w programie Excel może pomóc w sumowaniu wartości komórek na podstawie wielu kryteriów.
- SUMPRODUCT:
- Funkcja SUMPRODUCT może służyć do mnożenia dwóch lub więcej kolumn lub tablic, a następnie do uzyskania sumy iloczynów.
- DATA:
- Funkcja DATA może nam pomóc w połączeniu numerów roku, miesiąca i dnia z oddzielnych komórek z prawidłową datą.
- ROK:
- Funkcja ROK zwraca rok na podstawie podanej daty w 4-cyfrowym formacie numeru seryjnego.
Więcej artykułów:
- Suma wartości według miesiąca (z rokiem lub bez)
- W tym samouczku szczegółowo przedstawiono dwie formuły, które ułatwiają sumowanie wartości według określonego miesiąca i roku lub sumowanie wartości według określonego miesiąca w programie Excel.
- Sumy wartości według tygodnia podanej daty
- Aby zsumować wartości według tygodnia, w którym przypada dana data, możesz zastosować formułę opartą na funkcji SUMA.
- Suma wartości według numeru tygodnia lub dnia tygodnia
- W tym samouczku przedstawiono krok po kroku formuły ułatwiające sumowanie wartości w określonym zakresie na podstawie określonego numeru tygodnia lub dnia tygodnia w programie Excel.
Najlepsze narzędzia biurowe
Kutools dla programu Excel - pomaga wyróżnić się z tłumu
Kutools dla programu Excel oferuje ponad 300 funkcji, Pewność, że to, czego potrzebujesz, jest w zasięgu jednego kliknięcia...
Karta Office - Włącz czytanie i edycję na kartach w Microsoft Office (w tym Excel)
- Jedna sekunda, aby przełączać się między dziesiątkami otwartych dokumentów!
- Zmniejsz liczbę kliknięć myszą każdego dnia, pożegnaj się z dłonią myszy.
- Zwiększa produktywność o 50% podczas przeglądania i edytowania wielu dokumentów.
- Wprowadza wydajne karty do pakietu Office (w tym programu Excel), podobnie jak przeglądarki Chrome, Edge i Firefox.
