Pobierz przykładowy skoroszyt
Ten samouczek pokaże, jak sumować dane odpowiadające konkretnym numerom tygodni w Excelu i Arkuszach Google.
Suma jeśli według numeru tygodnia
Aby „zsumować jeśli” według numeru tygodnia, użyjemy funkcji SUMIFS. Ale najpierw musimy dodać kolumnę pomocniczą zawierającą funkcję WEEKNUM.
ten Numer tygodnia Kolumna pomocnicza jest obliczana za pomocą funkcji WEEKNUM:
| 1 | =NUM.TYG(B3;1) |
Następnie użyjemy funkcji SUMIFS, aby zsumować wszystko Sprzedaż które mają miejsce w konkretnym Numer tygodnia.
| 1 | =SUMA.WARUNKI(D3:D9;C3:C9;F3) |
Suma jeśli według numeru tygodnia - bez kolumny pomocniczej
Metoda kolumny pomocniczej jest łatwa do naśladowania, ale można również zreplikować obliczenia w jednej formule, używając funkcji SUMPRODUCT w połączeniu z funkcją WEEKNUM, aby zsumować Całkowita liczba sprzedaży za pomocą Numer tygodnia.
| 1 | =PROD.SUMA(--(NUM.TYG(B3:B9+0,1)=E3);C3:C9) |
W tym przykładzie możemy użyć funkcji SUMPRODUCT do wykonania skomplikowanych obliczeń „suma jeśli”. Przejdźmy przez powyższy przykład.
Oto nasza ostateczna formuła:
| 1 | =PROD.SUMA(--(NUM.TYG(B3:B9+0,1)=E3);C3:C9) |
Najpierw funkcja SUMPRODUCT wyświetla tablicę wartości z zakresów komórek:
| 1 | =(--(({"1/3/2020"; "1/6/2020"; "1/9/2020"; "1/12/2020"; "1/15/2020"; "1/18/2020"; "1/21/2020"}+0,1)=1), {4; 9; 1; 7; 6; 2; 5}) |
Następnie funkcja WEEKNUM oblicza Numer tygodnia każdego z Daty sprzedaży.
Funkcja WEEKNUM nie jest przeznaczona do pracy z wartościami tablicowymi, więc musimy dodać zero („+0”), aby WEEKNUM prawidłowo przetwarzał wartości.
| 1 | =PROD.SUMA(--({1; 2; 2; 3; 3; 3; 4}=1), {4; 9; 1; 7; 6; 2; 5}) |
Numer tygodnia wartości równe 1 są zmieniane na wartości TRUE.
| 1 | =PRODUKCJASUMA(--({PRAWDA; FAŁSZ; FAŁSZ; FAŁSZ; FAŁSZ; FAŁSZ; FAŁSZ}), {4; 9; 1; 7; 6; 2; 5}) |
Następnie podwójne myślniki (-) zamieniają wartości TRUE i FALSE na 1 i 0:
| 1 | =PROD.SUMA({1; 0; 0; 0; 0; 0; 0}; {4; 9; 1; 7; 6; 2; 5}) |
Funkcja SUMPRODUCT następnie mnoży każdą parę wpisów w tablicach, aby utworzyć tablicę Liczba sprzedaży które mają Numer tygodnia z 1:
| 1 | =PROD.SUMA({4; 0; 0; 0; 0; 0; 0}) |
Na koniec liczby w tablicy są sumowane:
| 1 | =4 |
Ten wzór jest następnie powtarzany dla innych możliwych wartości Numer tygodnia.
Więcej szczegółów na temat używania instrukcji logicznych i polecenia „-” w funkcji SUMPRODUCT można znaleźć tutaj.
Blokowanie odwołań do komórek
Aby ułatwić czytanie naszych formuł, pokazaliśmy formuły bez zablokowanych odwołań do komórek:
| 1 | =PROD.SUMA(--(NUM.TYG(B3:B9+0,1)=E3);C3:C9) |
Ale te formuły nie będą działać poprawnie po skopiowaniu i wklejeniu w innym miejscu pliku. Zamiast tego powinieneś używać zablokowanych odwołań do komórek w następujący sposób:
| 1 | =PRODUKTSUMA(--(NUM.TYGODNIA($B$3:$B$9+0,1)=E3);$C$3:$C$9) |
Przeczytaj nasz artykuł na temat blokowania odwołań do komórek, aby dowiedzieć się więcej.
Suma jeśli według numeru tygodnia w Arkuszach Google
Te formuły działają dokładnie tak samo w Arkuszach Google, jak w programie Excel.
Jednak funkcja WEEKNUM jest bardziej elastyczna w Arkuszach Google niż w programie Excel i akceptuje dane wejściowe i wyjściowe tablic. Dlatego operacja {Tablica}+0 w formule WEEKNUM(B3:B9+0,1) nie jest wymagana.
Pełną formułę SUMPRODUCT można zapisać w Arkuszach Google jako:
| 1 | =SUMA PRODUKT(--(WEEKNUM($ 3 $: $ 9 $+0,1)=E3),$C$3:$C$9) |
