Suma arkusza kalkulacyjnego Google, która zawsze kończy się na powyższej komórce
Jak utworzyć arkusz kalkulacyjny Google Sum (), który zawsze kończy się na powyższej komórce, nawet gdy dodawane są nowe komórki? Mam kilka takich obliczeń do wykonania na każdej kolumnie więc takie rozwiązania nie pomogą.
Przykład:
W kolumnie B mam kilka zakresów dynamicznych, które należy podsumować. B1..B9 należy zsumować na B10, a B11..B19 należy zsumować na B20. Mam dziesiątki takich obliczeń do wykonania. Co jakiś czas dodaję wiersze poniżej ostatniego podsumowanego wiersza i chcę, żeby zostały dodane do sumy. Dodaję nowy wiersz (nazwijmy go 9.1) przed rzędem 10, a nowy raw (nazwijmy go 19.1) przed rzędem 20. Chcę, aby B10 zawierała sumę B1 do B9. 1 i B20 zawierała sumę B11: B19.1.
W Excelu mam funkcję offsetu która robi to jak urok. Ale jak to zrobić z arkuszem kalkulacyjnym google? Próbowałem używać takich formuł:
=SUM(B1:INDIRECT(address(row()-1,column(),false))) # Formula on B10
=SUM(B11:INDIRECT(address(row()-1,column(),false))) # Formula on B20
Ale w arkuszu kalkulacyjnym Google, wszystko co daje to błąd #name.
I wasted hours próbując znaleźć rozwiązanie, może ktoś się uspokoi? Proszę o poradę
Amnon
7 answers
Prawdopodobnie szukasz formuły w stylu:
=SUM(INDIRECT("B1:"&ADDRESS(ROW()-1,COLUMN(),4)))
Google Spreadsheet INDIRECT zwraca odniesienie do komórki lub area, podczas gdy - z tego co pamiętam - Excel INDIRECT zwraca zawsze odniesienie do komórki. Biorąc pod uwagę pośrednie Google rzeczywiście ma trochę trudu, gdy próbujesz użyć go wewnątrz sumy jako odniesienia komórki, co chcesz, aby karmić SUM z całego zakresu być podsumowane w NP notacji a1: "B1: BX".
Otrzymujesz żądany adres w taki sam sposób jak w Excelu (Uwaga " 4 " tutaj dla wiersza / kolumny względne, domyślnie Google pośrednie zwraca absolutne):
ADDRESS(ROW()-1,COLUMN(),4)
I następnie użyj go do przygotowania ciągu zakresu dla funkcji SUM poprzez połączenie z komórką startową.
"B1:"&
I owiń go pośrednim, który zwróci obszar do podsumowania.
Nawiązując do poniższej odpowiedzi od Druvision (nie mogę jeszcze komentować, nie chciałem mnożyć odpowiedzi)
Zamiast czasochłonnych formuł poprawki za każdym razem, gdy wiersz jest wstawiany/usuwany, aby wszystkie wyglądały tak:
=SUM(INDIRECT(ADDRESS(ROW()-9,COLUMN(),4)&":"&ADDRESS(ROW()-1,COLUMN(),4)))
Możesz użyć jednej kolumny w oddzielnym arkuszu do przechowywania zmiennych (nazwijmy ją "def"), powiedzmy Z, do definiowania punktów początkowych np. w Z1 napisz " B1" w Z2 napisz " B11" itd. a następnie użyj go jako zmiennej w swojej sumie, używając indeksu:
SUM(INDIRECT(INDEX(def!Z:Z,1,1)&":"&ADDRESS(ROW()-1,COLUMN(),4))) - sumy od B1 do obliczonego wiersza, ponieważ w Z1 mamy " B1 " (1,1 w INDEX(...,1,1))
SUM(INDIRECT(INDEX(def!Z:Z,2,1)&":"&ADDRESS(ROW()-1,COLUMN(),4))) - sumy od B11 do obliczonego wiersza, ponieważ w Z2 mamy " B11 " (2,1 W INDEX(...,2,1))
Uwaga:
Oddzielny arkusz o nazwie 'def' - nie chcesz wstawiać/usuwać wierszy wpływających na te dane, więc trzymaj je na boku. Przydatne do dodawania list walidacyjnych, innych potrzebnych rzeczy w formułach.
"Z: Z" notacja-cała kolumna. Mówiłeś, że masz wiele takich formuł.;)
W ten sposób zachowujesz elastyczność definiowania komórki startowej dla każdej z Twoich formuł, która nie jest pod wpływem zmian arkusza kalkulacyjnego.
Przy okazji, czy nie łatwiej byłoby napisać niestandardową funkcję / skrypt podsumowujący wszystkie wiersze nad komórką? Jeśli masz ochotę na javascripting, z tego co pamiętam, arkusz google ma teraz ładny edytor skryptów. Możesz utworzyć funkcję o nazwie np. sumRowsAboveMe () i użyć jej w arkuszu jak =sumRowsAboveMe() w komórce arkusza.
Uwaga: Może być konieczne zastąpienie przecinków średnikami
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-07-27 16:51:18
Uwaga Po przetestowaniu tej odpowiedzi zadziała ona tylko wtedy, gdy suma jest w innej kolumnie z powodu okrągłego błędu zależności. W przeciwnym razie rozwiązanie jest ważne.
To trochę algebra, ale możemy skorzystać z przeciągania w prawym dolnym rogu arkuszy kalkulacyjnych.
=SUM(X:X) - SUM(X2:X)
Gdzie X jest kolumną, z którą pracujesz, a X2 jest Twoim zakończeniem punktem. Przeciągnij formułę w dół, a arkusze zwiększą X2, zmieniając w ten sposób punkt końcowy.
*wspomniałeś że masz dziesiątki takich obliczeń do wykonania. Więc aby dokładnie dopasować się do Twoich potrzeb, odejmujemy Twoje ostatnie Podsumowanie, aby uzyskać ten" środkowy " zakres, który chcieliśmy.
Np.
B1..B9 należy zsumować na B10, a B11..B19 należy zsumować na B20
Z powodu circular dependency Error wspomniano wcześniej, nie mogę go dokładnie rozwiązać i umieścić sumę w tej samej linii, ale może to działać w innych przypadkach, gdy suma musi być przechowywana w innym kolumna.
=SUM(B:B) - SUM(B9:B) //Formula on C10 (Sum of B1..B9)
=SUM(B:B) - SUM(B19:B) - B10 // Formula on C20 (Sum of B11..B19)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-03-08 09:51:05
Jest to oparte na @ PsychoFish, oto rozwiązanie:
=SUM(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&"3:"&ADDRESS(ROW()-1,COLUMN(),4)))
Po prostu zamień "3:" na sumę początkową wiersza.
@PsychoFish jest poprawny, ale nie można go przeciągnąć i skopiować, ponieważ kolumna jest dosłowna i mocno zakodowana, a @Druvision był w dobrym kierunku, ale był zły... zasadniczo skończyło się tym samym problemem konieczności ponownego wprowadzania zakresów, a następnie przesuwania formuł w kółko.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-12-01 05:10:02
Składnia Ogólna:
=SUM(INDIRECT(cell_reference_as_string1 &":"& cell_reference_as_string2)
Z na przykład:
cell_reference_as_string1 = ADDRESS(ROW(),COLUMN(),4)
cell_reference_as_string2 = ADDRESS(ROW()-1,COLUMN(),4)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2014-07-21 11:16:50
Utrudniacie to, niż musicie. Po prostu zostawiam kilka pustych wierszy powyżej przez wiersz" suma " (możesz sformatować je, aby były wypełnione kolorem lub czymś, aby nie były przypadkowo używane), a następnie po prostu dodaj nowe wiersze tuż nad tymi specjalnymi wierszami.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-12-06 06:01:13
Podoba mi się, jak @abernier opisuje ogólne rozwiązanie. Do tej pory stosuje się tylko notację A1 opartą na alfabecie (A jest pierwszą kolumną, 1 jest pierwszym wierszem). Ciągle mnie to myli, zwłaszcza gdy myślę o kolumnach pozostawionych po innych kolumnach. Bardziej podoba mi się zapis R1C1 oparty na liczbach. Aby użyć notacji R1C1 dla pośredniego , musisz przekazać FALSE w ten sposób:
=SUM(INDIRECT("R1C"&COLUMN()&":R"&(ROW()-1)&"C"&COLUMN(), FALSE))
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-07-04 00:17:09
ODPOWIEDŹ @PsychoFish doprowadziła mnie w prawidłowy sposób. Jedyna kwestia, że musiałem przepisać formułę ponownie z każdej kolumny i każdej sumy. Oto ulepszona formuła, która sumuje poprzednie 9 komórek w tej samej kolumnie, bez twardego kodowania liczb kolumn lub wierszy:
=SUM(INDIRECT(ADDRESS(ROW()-9,COLUMN(),4)&":"&ADDRESS(ROW()-1,COLUMN(),4)))
Jedynym problemem jest to, że musiałem przepisać formuły, jeśli ktoś dodaje lub usuwa wiersz. W tym przypadku powinienem zmienić 9 na 10 lub 8.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2013-09-25 07:58:28