Czy istnieje zwięzła formuła programu Excel do obliczenia (A1 * A6) + (B1 * B6) + (C1 * C6)…?


45

Chcę wiedzieć, jak podać zwięzłą formułę dla następującej długiej formuły w arkuszu Excel:

= (A1*A6)+(B1*B6)+(C1*C6)...

Czy używam funkcji sumowania, czy jest do tego inna funkcja? Myślę, że użycie dwukropka z SUMfunkcją powinno pomóc, ale nie wiem, jak go używać.




7
Matematycznie jest to znane jako iloczyn skalarny lub iloczyn skalarny. Przydatne warunki, jeśli kiedykolwiek będziesz potrzebować swojej ulubionej wyszukiwarki.
Philip Kendall,

produkt wewnętrzny: Zazwyczaj transponowalibyśmy ten problem, aby to zrobić (A1 * B1 + A2 * B2 + C3 * B3 ....) .... Oczywiście to tylko notacja ...
JosephDoggie

Odpowiedzi:


110

Szukasz funkcji SUMPRODUCT .

=SUMPRODUCT(A1:C1,A6:C6)

Zwróci to sumę produktów odpowiadających produktów w dwóch (lub więcej) zakresach.

wprowadź opis zdjęcia tutaj

Jak widać z dokumentacji Microsoft, z którą się połączyłem, zakresy nie muszą być pojedynczymi wierszami ani pojedynczymi kolumnami (chociaż muszą mieć takie same wymiary).

SUMPRODUCT może pomnożyć wartości z maksymalnie 255 różnych zakresów. Na przykład =SUMPRODUCT(A1:C1,A6:C6,A11:C11)jest taki sam jak =A1*A6*A11+B1*B6*B11+C1*C6*C11.


19

SUMfunkcja nie będzie działać, ponieważ po prostu dodaje elementy. Musisz pomnożyć wartości przed przejściem do SUM like=SUM(A1*A6, B1*B6, C1*C6, D1*D6, E1*E6)

Oczywiście możesz także użyć tego, =A1*A6 + B1*B6 + C1*C6 + D1*D6 + E1*E6co powoduje prawie taki sam wysiłek pisania, jakSUM

Istnieje wiele lepszych rozwiązań. Jednym ze sposobów jest użycie formuły tablicowej . Tam możesz zobaczyć dokładny przykład taki jak twój:

Składnia formuły tablicowej

Ogólnie formuły tablicowe używają standardowej składni formuł. Wszystkie zaczynają się od znaku równości (=) i możesz używać większości wbudowanych funkcji Excela w formułach tablicowych. Kluczową różnicą jest to, że używając formuły tablicowej, naciśnij Ctrl+ Shift+, Enteraby wprowadzić formułę. Po wykonaniu tej czynności program Excel otacza formułę tablicową nawiasami klamrowymi - jeśli wpiszesz nawiasy klamrowe, formuła zostanie przekonwertowana na ciąg tekstowy i nie będzie działać.

Funkcje tablic to naprawdę skuteczny sposób na zbudowanie złożonej formuły. Formuła tablicowa =SUM(C2:C11*D2:D11)jest taka sama:

=SUM(C2*D2,C3*D3,C4*D4,C5*D5,C6*D6,C7*D7,C8*D8,C9*D9,C10*D10,C11*D11).

Aby użyć formuły tablicowej w twoim przypadku, możesz wpisać (oczywiście musisz odpowiednio zmienić ostatni element tablicy)

=SUM(A1:E1*A6:E6)

a następnie naciśnij Ctrl+ Shift+Enter


Formuła tablicowa jest bardzo potężnym narzędziem. Używaj go jednak ostrożnie. Za każdym razem, gdy musisz go edytować, nie możesz zapomnieć o naciśnięciu Ctrl+ Shift+Enter

Dlaczego warto korzystać z formuł tablicowych?

Jeśli masz doświadczenie w używaniu formuł w programie Excel, wiesz, że możesz wykonywać dość skomplikowane operacje. Na przykład można obliczyć całkowity koszt pożyczki w dowolnej liczbie lat. Za pomocą formuł tablicowych można wykonywać złożone zadania, takie jak:

  • Policz liczbę znaków zawartych w zakresie komórek.

  • Zsumuj tylko liczby spełniające określone warunki, takie jak najniższe wartości w zakresie lub liczby mieszczące się między górną i dolną granicą.

  • Sumuj każdą n-tą wartość z zakresu wartości.

Formuły tablicowe oferują również następujące zalety:

  • Spójność: jeśli klikniesz dowolną komórkę od E2 w dół, zobaczysz tę samą formułę. Ta spójność może pomóc zapewnić większą dokładność.

  • Bezpieczeństwo: Nie można zastąpić składnika formuły macierzy wielokomórkowej. Na przykład kliknij komórkę E3 i naciśnij klawisz Delete. Musisz albo wybrać cały zakres komórek (od E2 do E11) i zmienić formułę dla całej tablicy lub pozostawić tablicę bez zmian. Jako dodatkowy środek bezpieczeństwa musisz nacisnąć Ctrl+ Shift+, Enteraby potwierdzić zmianę formuły.

  • Mniejsze rozmiary plików: często można użyć formuły z jedną tablicą zamiast kilku formuł pośrednich. Na przykład skoroszyt używa jednej formuły tablicowej do obliczania wyników w kolumnie E. Gdybyś użył standardowych formuł (takich jak = C2 * D2, C3 * D3, C4 * D4…), użyłbyś 11 różnych formuł do obliczenia te same wyniki.

Jest także szybszy, ponieważ wzorzec dostępu jest już znany. Teraz zamiast wykonywania 11 różnych obliczeń osobno, można go wektoryzować i wykonać równolegle, wykorzystując wiele rdzeni i jednostkę SIMD w CPU


7
Należy jednak pamiętać, że funkcje tablicowe są bardzo delikatne. Na przykład, jeśli je skopiujesz i wkleisz, rzeczy mogą się bardzo zepsuć. Często nie będą one poprawnie aktualizowane bez wymuszonej aktualizacji, jeśli są bardzo duże. Świetna, potężna funkcja, ale persnickety w porównaniu do zwykłych formuł.
JKreft

3
@StianYttervik: Myślę, że odpowiedź jest przydatna. Co jeśli ktoś otworzy arkusz z taką formułą i nigdy go nie widział? Ta odpowiedź byłaby pomocna dla tych użytkowników.
Bad_Bishop

3
Formuła tablicowa @StianYttervik to potężne narzędzie we właściwej ręce. Nie jest wcale wolny , ponieważ można go łatwiej zoptymalizować i wektoryzować. I oszczędza pamięć. Obsługują go nawet dokumenty Google. I nie odpowiadaj na to. Nie będę więcej odpowiadał
phuclv,

2
@StianYttervik Niektóre funkcje (np. MOD) Spowodują uszkodzenie SUMPRODUCT, a następnie musisz zastąpić je formułą Array. Nie wspominając już o tym, że każda wydana wersja pakietu Office ma jeszcze częściej używane formuły tablicowe dodane jako oficjalne funkcje (np. MAXIFSW 365). Jeśli twoja formuła tablicowa jest powolna, niezgrabna i łamie się, prawdopodobnie musisz zoptymalizować swój kod. I oczywiście - ponieważ PowerQuery jest dodatkowym rozszerzeniem rozszerzenia, wiele firm (z różnych powodów) nie uwzględni go w pakiecie kompilacji korporacyjnej, co czyni go gorszym rozwiązaniem dla rozproszonych celów biznesowych.
Chronocidal

1
@Chronocidal Tak, są przypadki, ale jest ich niewiele ... A potem zapytania o moc są rozwiązaniem. Lub model danych. Lub funkcje kostki. Lub wszystkie powyższe. Tablice i obliczenia / wykonanie rząd po rzędzie po prostu nie mogą mierzyć.
Stian Yttervik

7

Innym podejściem jest wstawienie w A7 wyrażenia = A1 * A6 i skopiowanie w prawo tak daleko, jak chcesz, a następnie zsumowanie wiersza 7 $, aby uzyskać ostateczną odpowiedź. Nie robi tego w jednej komórce, jak chcesz, ale czasami posiadanie produktów pośrednich jest przydatne. Użyłem obu wersji. Ten wydaje mi się bardziej podobny do Excela, ale twój gust może się różnić.


1

jeśli umieścisz listę liczb w kolumnach zamiast wierszy (powiedzmy dwie kolumny A i B), możesz użyć funkcji = Sumproduct (A: A, B: B) w ten sposób. da ci iloczyn tylu liczb, ile jest w kolumnach A i B.

Możesz użyć tyle kolumn w funkcji Sumproduct, ile potrzebujesz


2
Sumproduct (1: 1,2: 2) działa również w ten sam sposób dla wierszy.
Máté Juhász,

0

Jeśli w wierszach 1 i 6 nie ma nic innego niż to, co chcesz mieć SUMPRODUCT(), możesz skorzystać z pomysłu wymienionego w tym komentarzu . W twoim przypadku, jak opisano w pytaniu, użyłbyś=SUMPRODUCT(1:1,6:6)


4
To tak naprawdę nie daje dodatkowych informacji na temat tego, co już wpisano w odpowiedziach i komentarzach.
Máté Juhász

1
@ MátéJuhász - Jaka odpowiedź zawiera moją odpowiedź? Twój komentarz do odpowiedzi udzielonej przez @AjayC daje, SUMPRODUCT(1:1,2:2)a ja odpowiedziałem na podstawie tego komentarza, wyjaśniając, w jaki sposób można go wykorzystać na podstawie pierwotnego pytania i jakie byłoby to ograniczenie.
Chris Rogers,
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.