Note: The other languages of the website are Google-translated. Back to English

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

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:

=SUMIFS(sum_range,date_range,">="&DATE(year,1,1),date_range,"<="&DATE(year,12,31)
  • 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:

=SUMIFS($C$2:$C$12,$B$2:$B$12,">="&DATE(E2,1,1),$B$2:$B$12,"<="&DATE(E2,12,31))


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:

=SUMPRODUCT((YEAR(date_range)=year)*sum_range)
  • 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.

Najlepsze narzędzia biurowe

Kutools dla programu Excel - pomaga wyróżnić się z tłumu

Chcesz szybko i perfekcyjnie zakończyć swoją codzienną pracę? Kutools dla programu Excel oferuje 300 zaawansowanych zaawansowanych funkcji (Łącz skoroszyty, sumuj według koloru, dziel zawartość komórek, konwertuj daty itd.) i oszczędzaj 80% czasu.

  • Zaprojektowany dla 1500 scenariuszy pracy, pomaga rozwiązać 80% problemów z Excelem.
  • Zmniejsz tysiące kliknięć klawiatury i myszy każdego dnia, odciąż zmęczone oczy i dłonie.
  • Zostań ekspertem Excela w 3 minuty. Nie musisz już pamiętać żadnych bolesnych formuł i kodów VBA.
  • 30-dniowy nieograniczony bezpłatny okres próbny. 60-dniowa gwarancja zwrotu pieniędzy. Bezpłatna aktualizacja i wsparcie przez 2 lata.
Wstążka programu Excel (z zainstalowanym Kutools dla programu Excel)

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 zakładki do pakietu Office (w tym Excel), podobnie jak Chrome, Firefox i nowy Internet Explorer.
Zrzut ekranu programu Excel (z zainstalowaną kartą Office)
Sortuj komentarze według
Komentarze (0)
Brak ocen. Oceń jako pierwszy!
Nie ma tu jeszcze żadnych komentarzy
Zostaw swój komentarz
Publikowanie jako gość
×
Oceń ten post:
0   Postacie
Sugerowane lokalizacje

Bądż na bieżąco

Prawa autorskie © 2009 - www.extendoffice.com. | Wszelkie prawa zastrzeżone. Zasilany przez ExtendOffice, | Mapa strony
Microsoft i logo Office są znakami towarowymi lub zastrzeżonymi znakami towarowymi Microsoft Corporation w Stanach Zjednoczonych i / lub innych krajach.
Chronione przez Sectigo SSL