Przejdź do głównej zawartości

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

Popularne funkcje: Znajdź, wyróżnij lub zidentyfikuj duplikaty  |  Usuń puste wiersze  |  Łącz kolumny lub komórki bez utraty danych  |  Okrągły bez wzoru ...
Super VLookup: Wiele kryteriów  |  Wiele wartości  |  W wielu arkuszach  |  Wyszukiwanie rozmyte...
Adw. Lista rozwijana: Łatwa lista rozwijana  |  Zależna lista rozwijana  |  Lista rozwijana wielokrotnego wyboru...
Menedżer kolumn: Dodaj określoną liczbę kolumn  |  Przesuń kolumny  |  Przełącz stan widoczności ukrytych kolumn  Porównaj kolumny z Wybierz Te same i różne komórki ...
Polecane funkcje: Fokus siatki  |  Widok projektu  |  Duży pasek formuły  |  Menedżer skoroszytów i arkuszy | Biblioteka zasobów (Automatyczny tekst)  |  Selektor dat  |  Połącz arkusze  |  Szyfruj/odszyfruj komórki  |  Wysyłaj e-maile według listy  |  Super filtr  |  Specjalny filtr (filtruj pogrubienie/kursywa/przekreślenie...) ...
15 najlepszych zestawów narzędzi12 Tekst Tools (Dodaj tekst, Usuń znaki ...)  |  50 + Wykres rodzaje (Wykres Gantta ...)  |  40+ Praktyczne Wzory (Oblicz wiek na podstawie urodzin ...)  |  19 Wprowadzenie Tools (Wstaw kod QR, Wstaw obraz ze ścieżki ...)  |  12 Konwersja Tools (Liczby na słowa, Przeliczanie walut ...)  |  7 Połącz i podziel Tools (Zaawansowane wiersze łączenia, Podziel komórki Excela ...)  |  ... i więcej

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.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations