Jeśli arkusz programu Excel zawiera obliczenia oparte na zmieniającym się zakresie komórek, użyj razem funkcji SUMA i PRZESUNIĘCIE w formule PRZESUNIĘCIE SUMA, aby uprościć zadanie utrzymywania aktualności obliczeń.
Instrukcje zawarte w tym artykule dotyczą programów Excel dla Microsoft 365, Excel 2019, Excel 2016, Excel 2013 i Excel 2010.
Utwórz zakres dynamiczny za pomocą funkcji SUMA i PRZESUNIĘCIE
Jeżeli używasz obliczeń dla okresu, który stale się zmienia - na przykład określanie sprzedaży w danym miesiącu - użyj funkcji PRZESUNIĘCIE w programie Excel, aby skonfigurować zakres dynamiczny, który zmienia się wraz z dodawaniem danych sprzedaży każdego dnia.
Sama funkcja SUMA zwykle umożliwia wstawianie nowych komórek danych do sumowanego zakresu. Jeden wyjątek występuje, gdy dane są wstawiane do komórki, w której aktualnie znajduje się funkcja.
W poniższym przykładzie nowe dane sprzedaży dla każdego dnia są dodawane na dole listy, zmuszając sumę do ciągłego przesuwania się w dół o jedną komórkę za każdym razem, gdy nowe dane są dodawane.
Aby kontynuować ten samouczek, otwórz pusty arkusz programu Excel i wprowadź przykładowe dane. Twój arkusz roboczy nie musi być sformatowany jak w przykładzie, ale pamiętaj, aby wprowadzić dane w tych samych komórkach.
Jeśli do sumowania danych używana jest tylko funkcja SUMA, zakres komórek używany jako argument funkcji będzie musiał być modyfikowany za każdym razem, gdy dodawane są nowe dane.
Dzięki równoczesnemu użyciu funkcji SUMA i PRZESUNIĘCIE, sumowany zakres staje się dynamiczny i zmienia się w celu dostosowania do nowych komórek danych. Dodanie nowych komórek z danymi nie powoduje problemów, ponieważ zakres jest dostosowywany w miarę dodawania każdej nowej komórki.
Składnia i argumenty
W tej formule funkcja SUMA służy do sumowania zakresu danych podanych jako argument. Punkt początkowy tego zakresu jest statyczny i jest identyfikowany jako odwołanie do komórki do pierwszej liczby, która ma być zsumowana przez formułę.
Funkcja PRZESUNIĘCIE jest zagnieżdżona w funkcji SUMA i tworzy dynamiczny punkt końcowy zakresu danych zsumowanych przez formułę. Jest to osiągane przez ustawienie punktu końcowego zakresu na jedną komórkę powyżej lokalizacji formuły.
Składnia formuły to:
=SUMA(Początek zakresu:PRZESUNIĘCIE(Odniesienie, Rzędy, Kolumny))
Argumenty to:
- Początek zakresu: Punkt początkowy zakresu komórek, które zostaną zsumowane przez funkcję SUMA. W tym przykładzie punktem początkowym jest komórka B2.
- Reference: Wymagane odwołanie do komórki używane do obliczania punktu końcowego zakresu. W tym przykładzie argument Odwołanie jest odwołaniem do komórki formuły, ponieważ zakres kończy się o jedną komórkę powyżej formuły.
- Rows: Wymagana jest liczba wierszy powyżej lub poniżej argumentu Referencja użytego do obliczenia przesunięcia. Ta wartość może być dodatnia, ujemna lub ustawiona na zero. Jeśli lokalizacja przesunięcia znajduje się powyżej argumentu Reference, wartość jest ujemna. Jeśli przesunięcie jest poniżej, argument Wiersze jest dodatni. Jeśli przesunięcie znajduje się w tym samym wierszu, argumentem jest zero. W tym przykładzie przesunięcie zaczyna się o jeden wiersz powyżej argumentu Odwołanie, więc wartość argumentu jest ujemna (-1).
- Cols: Liczba kolumn po lewej lub prawej stronie argumentu Referencja użytego do obliczenia przesunięcia. Ta wartość może być dodatnia, ujemna lub ustawiona na zero. Jeśli lokalizacja przesunięcia znajduje się na lewo od argumentu Reference, ta wartość jest ujemna. Jeśli przesunięcie jest prawidłowe, argument Cols jest dodatni. W tym przykładzie sumowane dane znajdują się w tej samej kolumnie co formuła, więc wartość tego argumentu wynosi zero.
Użyj formuły PRZESUNIĘCIE SUMA do łącznych danych sprzedaży
W tym przykładzie użyto formuły PRZESUNIĘCIE SUMA w celu zwrócenia sumy dla dziennych wartości sprzedaży wymienionych w kolumnie B arkusza. Początkowo formuła została wprowadzona do komórki B6 i zsumowała dane sprzedaży z czterech dni.
Następnym krokiem jest przesunięcie formuły SUMA PRZESUNIĘCIE o jeden wiersz w dół, aby zrobić miejsce na łączną sprzedaż z piątego dnia. Osiąga się to poprzez wstawienie nowego wiersza 6, który przenosi formułę do wiersza 7.
W wyniku przeniesienia program Excel automatycznie aktualizuje argument Odwołanie do komórki B7 i dodaje komórkę B6 do zakresu zsumowanego przez formułę.
- Wybierz komórkę B6, czyli lokalizację, w której będą początkowo wyświetlane wyniki formuły.
-
Wybierz kartę Formuły na wstążce.
Image -
Wybierz Math & Trig.
Image -
Wybierz SUM.
Image - W oknie dialogowym Argumenty funkcji umieść kursor w polu tekstowym Number1.
-
W arkuszu wybierz komórkę B2, aby wprowadzić to odwołanie do komórki w oknie dialogowym. Ta lokalizacja jest statycznym punktem końcowym formuły.
Image - W oknie dialogowym Argumenty funkcji umieść kursor w polu tekstowym Number2.
-
Wprowadź PRZESUNIĘCIE(B6, -1, 0). Ta funkcja PRZESUNIĘCIA tworzy dynamiczny punkt końcowy formuły.
Image -
Wybierz OK, aby zakończyć funkcję i zamknąć okno dialogowe. Suma pojawia się w komórce B6.
Image
Dodaj dane sprzedaży następnego dnia
Aby dodać dane sprzedaży na następny dzień:
- Kliknij prawym przyciskiem myszy nagłówek wiersza 6.
-
Wybierz Wstaw, aby wstawić nowy wiersz do arkusza. Formuła PRZESUNIĘCIE SUMA przesuwa się o jeden wiersz w dół do komórki B7, a wiersz 6 jest teraz pusty.
Image - Wybierz komórkę A6 i wprowadź numer 5, aby wskazać, że wprowadzana jest suma sprzedaży za piąty dzień.
-
Wybierz komórkę B6, wprowadź $1458,25, a następnie naciśnij Enter.
Image - Aktualizacja komórki B7 do nowej sumy 7137,40 USD.
Po wybraniu komórki B7 zaktualizowana formuła pojawi się na pasku formuły.
=SUMA(B2:PRZESUNIĘCIE(B7, -1, 0))
Funkcja PRZESUNIĘCIE ma dwa opcjonalne argumenty: Wysokość i Szerokość, które nie zostały użyte w tym przykładzie. Te argumenty informują funkcję PRZESUNIĘCIE o kształcie wyniku pod względem liczby wierszy i kolumn.
Pomijając te argumenty, funkcja zamiast tego używa wysokości i szerokości argumentu Odwołanie, który w tym przykładzie ma wysokość jednego wiersza i szerokość jednej kolumny.
