Excel SUM i OFFSET Formula

Jeśli Twój arkusz programu Excel zawiera obliczenia oparte na zmieniającym się zakresie komórek, użycie funkcji SUMA i PRZESUNIĘCIE razem w formule SUM OFFSET upraszcza zadanie utrzymywania aktualnych obliczeń.

Utwórz zakres dynamiczny za pomocą funkcji SUMA i OFFSET

Jeśli używasz obliczeń przez pewien czas, który nieustannie się zmienia - na przykład sumując sprzedaż za miesiąc - funkcja OFFSET w programie Excel pozwala skonfigurować zakres dynamiczny, który zmienia się wraz z dodawaniem danych o sprzedaży każdego dnia.

Funkcja SUM może zwykle uwzględniać wstawianie nowych komórek danych do zsumowanego zakresu.

Jeden wyjątek występuje, gdy dane są wstawiane do komórki, w której funkcja jest aktualnie zlokalizowana.

Na powyższym obrazie przykładowym nowe dane sprzedaży dla każdego dnia są dodawane na dole listy, zmuszając sumę do ciągłego zmniejszania o jedną komórkę za każdym razem, gdy dodawane są nowe dane.

Jeśli funkcja SUMA była używana samodzielnie do sumowania danych, konieczna byłaby modyfikacja zakresu komórek używanych jako argument funkcji za każdym razem, gdy dodawano nowe dane.

Używając jednocześnie funkcji SUMA i PRZESUNIĘCIE, zakres, który jest sumowany, staje się dynamiczny. Innymi słowy, zmienia się, aby pomieścić nowe komórki danych. Dodanie nowych komórek danych nie powoduje problemów, ponieważ zakres zmienia się w miarę dodawania każdej nowej komórki.

Składnia i argumenty

Zobacz powyższy obrazek, aby postępować zgodnie z tym samouczkiem.

W tym wzorze funkcja SUM służy do sumowania zakresu dostarczanych danych jako argumentu. Punkt początkowy dla tego zakresu jest statyczny i jest identyfikowany jako odwołanie do komórki do pierwszej liczby, która ma być sumowana przez formułę.

Funkcja PRZESUNIĘCIE jest zagnieżdżona wewnątrz funkcji SUMA i służy do tworzenia dynamicznego punktu końcowego dla zakresu danych sumowanych przez formułę. Osiąga się to poprzez ustawienie punktu końcowego zakresu na jedną komórkę ponad położeniem formuły.

Składnia formuły:

= SUMA (Początek zakresu: OFFSET (odniesienie, rzędy, kole))

• Range Start: Wymagany jest punkt początkowy zakresu komórek, które będą sumowane przez funkcję SUM. W przykładowym obrazie jest to komórka B2.
• Odniesienie: Wymagane odwołanie do komórki używane do obliczania punktu końcowego zakresu znajduje się w odległości wielu wierszy i kolumn. W przykładowym obrazie argument Odwołanie jest odwołaniem do komórki dla samej formuły, ponieważ zawsze chcesz, aby zakres kończył jedną komórkę powyżej formuły.
• Wydziwianie: Wymagana jest liczba wierszy powyżej lub poniżej argumentu Reference używanego do obliczania przesunięcia. Ta wartość może być dodatnia, ujemna lub ustawiona na zero. Jeśli położenie odsunięcia jest powyżej Odniesienie argument, wartość jest ujemna. Jeśli 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 od argumentu Reference, więc wartość tego argumentu jest ujemna (-1).
• Cols: Wymagana jest liczba kolumn po lewej lub prawej stronie argumentu Odniesienie użytego do obliczenia przesunięcia. Ta wartość może być dodatnia, ujemna lub ustawiona na zero. Jeśli położenie przesunięcia znajduje się po lewej stronie argumentu Odniesienie, ta wartość jest ujemna. Jeśli po prawej stronie argument Colsa 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żywanie formuły SUM OFFSET do danych sprzedaży całkowitej

W tym przykładzie użyto formuły SUM OFFSET, aby zwrócić sumę dziennych wartości sprzedaży wymienionych w kolumnie B arkusza roboczego.

Początkowo formuła została wprowadzona do komórki B6 i podsumowała dane sprzedaży przez cztery dni.

Następnym krokiem jest przesunięcie formuły SUM OFFSET w dół w rzędzie, aby zrobić miejsce na sumę sprzedaży piątego dnia. Osiąga się to przez wstawienie nowego wiersza 6, który przenosi formułę do wiersza 7.

W wyniku przeniesienia program Excel automatycznie aktualizuje argument odwołania do komórkiB7 i dodaje komórkęB6 do zakresu zsumowanego przez formułę.

Wprowadzanie formuły SUM OFFSET

1. Kliknij komórkę B6, na której początkowo będą wyświetlane wyniki formuły.
2. Kliknij naFormuły kartę menu wstążki.
3. WybieraćMath & Trigz wstążki, aby otworzyć rozwijaną listę funkcji.
4. KliknijSUMA na liście, aby wywołać okno dialogowe funkcji.
5. W oknie dialogowym kliknij ikonęNumer 1linia.
6. Kliknij komórkę B2 aby wprowadzić odwołanie do tej komórki w oknie dialogowym. Ta lokalizacja jest statycznym punktem końcowym dla formuły.
7. W oknie dialogowym kliknij ikonęNumer 2linia.
8. Wprowadź następującą funkcję OFFSET: PRZESUNIĘCIE (B6, -1,0) aby utworzyć dynamiczny punkt końcowy dla formuły.
9. Kliknij dobrze aby ukończyć tę funkcję i zamknąć okno dialogowe.

Suma pojawia się w komórce B7.

Dodawanie danych sprzedażowych następnego dnia

Aby dodać dane dotyczące sprzedaży następnego dnia:

1. Kliknij prawym przyciskiem myszy nagłówek wiersza dla wiersza 6, aby otworzyć menu kontekstowe.
2. W menu kliknijWstawić wstawić nowy wiersz do arkusza roboczego.
3. W rezultacie formuła SUM OFFSET przesuwa się w dół do komórki B7, a wiersz 6 jest teraz pusty.
4. Kliknij komórkę A6.
5. Wprowadź numer5 aby wskazać, że suma sprzedaży piątego dnia jest wprowadzana.
6. Kliknij komórkę B6.
7. Wpisz liczbę$1458.25 i naciśnijWchodzić klawisz na klawiaturze.

Aktualizacja komórek B7 do nowej sumy$7137.40.

Po kliknięciu komórki B7 zaktualizowana formuła= SUMA (B2: PRZESUNIĘCIE (B7, -1,0)) pojawia się na pasku formuły.

Funkcja OFFSET ma dwa opcjonalne argumenty: Wysokość i szerokość , które nie były używane w tym przykładzie.

Argumenty te mogą być używane do informowania funkcji OFFSET o kształcie wyjścia pod względem tak dużej liczby wierszy i tak dużej liczby kolumn.

Pomijając te argumenty, funkcja domyślnie używa wysokości i szerokości argumentu Odwołanie, który w tym przykładzie ma jeden wiersz wysoki i jedną kolumnę.