Я хочу узнать, как дать краткую формулу для следующей длинной формулы в таблице Excel:
= (A1*A6)+(B1*B6)+(C1*C6)...
Использовать ли мне функцию суммы или есть другая функция для этого? Я думаю, что использование двоеточия с SUMфункцией должно помочь, но я не знаю, как им пользоваться.
решение1
Вы ищетеСУММПРОИЗВЕДЕНИЕфункция.
=SUMPRODUCT(A1:C1,A6:C6)
Это вернет сумму произведений соответствующих элементов в двух (или более) диапазонах.
Как видно из документации Microsoft, ссылку на которую я привел, диапазоны не обязательно должны быть отдельными строками или отдельными столбцами (хотя они должны иметь одинаковые измерения).
SUMPRODUCT может умножать значения из 255 различных диапазонов. Например, =SUMPRODUCT(A1:C1,A6:C6,A11:C11)то же самое, что и =A1*A6*A11+B1*B6*B11+C1*C6*C11.
решение2
SUMФункция не будет работать, так как она просто добавляет элементы. Вам нужно перемножить значениядопереходя к SUM как=SUM(A1*A6, B1*B6, C1*C6, D1*D6, E1*E6)
Конечно, вы также можете использовать =A1*A6 + B1*B6 + C1*C6 + D1*D6 + E1*E6, что потребует примерно тех же усилий при наборе текста, что иSUM
Есть много лучших решений. Одно из них уже предложил Blackwood. Другой альтернативный способ — использоватьформула массива. Вы можете увидеть пример, точно такой же, как у вас, от Microsoft:
Синтаксис формулы массива
В общем, формулы массива используют стандартный синтаксис формул. Все они начинаются со знака равенства (=), и вы можете использовать большинство встроенных функций Excel в своих формулах массива. Ключевое отличие в том, что при использовании формулы массива вы нажимаете Ctrl+ Shift+ Enterдля ввода формулы. Когда вы это делаете, Excel окружает вашу формулу массива фигурными скобками — если вы введете скобки вручную, ваша формула будет преобразована в текстовую строку, и она не будет работать.
Функции массива — это действительно эффективный способ построения сложной формулы. Формула массива
=SUM(C2:C11*D2:D11)такая же:=SUM(C2*D2,C3*D3,C4*D4,C5*D5,C6*D6,C7*D7,C8*D8,C9*D9,C10*D10,C11*D11).
Чтобы использовать формулу массива в вашем случае, вы можете ввести (конечно, вам нужно будет изменить последний элемент массива соответствующим образом)
=SUM(A1:E1*A6:E6)
и затем нажмите Ctrl+ Shift+Enter
Как только вы поймете идею формулы массива, ее можно будет применять к большинству других формул, и вы даже можете забыть о существованииSUMPRODUCT
Обновлять:
Более новые версии Excel во многих случаях даже автоматически используют формулы массива.
Начиная с обновления Office 365 за сентябрь 2018 года, любая формула, которая может возвращать несколько результатов, будет автоматически переносить их либо вниз, либо в соседние ячейки. Это изменение поведения также сопровождается несколькими новыми динамическими функциями массива. Динамические формулы массива, независимо от того, используют ли они существующие функции или динамические функции массива, нужно было ввести только в одну ячейку, а затем подтвердить нажатием Enter. Ранее устаревшие формулы массива требовали сначала выбрать весь выходной диапазон, а затем подтвердить формулу с помощью Ctrl+ Shift+ Enter. Их обычно называют формулами CSE.
Формула массива — очень мощный инструмент. Однако используйте его с осторожностью. Каждый раз, когда вам нужно его редактировать, не забывайте нажимать Ctrl+ Shift+Enter
Зачем использовать формулы массива?
Если у вас есть опыт использования формул в Excel, вы знаете, что можете выполнять некоторые довольно сложные операции. Например, вы можете рассчитать общую стоимость кредита за любое заданное количество лет. Вы можете использовать формулы массива для выполнения сложных задач, таких как:
Подсчитайте количество символов, содержащихся в диапазоне ячеек.
Суммируйте только те числа, которые удовлетворяют определенным условиям, например, наименьшие значения в диапазоне или числа, которые попадают между верхней и нижней границей.
Суммируйте каждое n-ное значение в диапазоне значений.
Формулы массива также обладают следующими преимуществами:
Последовательность:Если вы нажмете на любую из ячеек от E2 и ниже, вы увидите ту же формулу. Эта согласованность может помочь обеспечить большую точность.
Безопасность:Вы не можете перезаписать компонент формулы многоячеечного массива. Например, щелкните ячейку E3 и нажмите Delete. Вам нужно либо выбрать весь диапазон ячеек (E2–E11) и изменить формулу для всего массива, либо оставить массив как есть. В качестве дополнительной меры безопасности вам нужно нажать Ctrl+ Shift+, Enterчтобы подтвердить изменение формулы.
Меньшие размеры файлов:Часто можно использовать одну формулу массива вместо нескольких промежуточных формул. Например, рабочая книга использует одну формулу массива для вычисления результатов в столбце E. Если бы вы использовали стандартные формулы (например, =C2*D2, C3*D3, C4*D4…), вам пришлось бы использовать 11 различных формул для вычисления тех же результатов.
Это также быстрее, поскольку шаблон доступа уже известен. Теперь вместо того, чтобы делать 11 различных вычислений по отдельности, это можно векторизовать и делать параллельно, используя несколько ядер и блок SIMD в ЦП
решение3
Другой подход — поместить в A7 выражение =A1*A6 и скопировать вправо до нужного места, затем суммировать строку $7$, чтобы получить окончательный ответ. Это не делается в одной ячейке, как вы хотите, но иногда наличие промежуточных продуктов удобно. Я использовал обе версии. Эта кажется мне более Excel-овской, но ваш вкус может отличаться.
решение4
Если в строках 1 и 6 нет ничего, кроме того, что вы хотите иметь SUMPRODUCT(), вы можете использовать идеюупомянуто в этом комментарии. В вашем случае, как указано в вашем вопросе, вы бы использовали=SUMPRODUCT(1:1,6:6)