Сумма произведений в excel с условием

Содержание:

Пример 1: умножаем число на число

Самый простой пример – это произведение двух конкретных чисел. В данном случае Эксель выступает в роли обычного калькулятора.

Встаем в любую свободную ячейку, затем:
- ставим в ячейке (или в строке формул) знак “=”;
- пишем первое число (множимое;
- ставим знак умножения – “*“;
- указываем второе число (множитель).
После нажатия клавиши Enter получаем результат в ячейке с формулой.

Примечание: при работе с вычислениями нужно помнить, что в Excel действуют такие же законы приоритета, что и в обычной арифметике, т.е. сперва выполняется умножение или деление, и только потом – сложение или вычитание.

В отличие от того, как записываются выражения со скобками на бумаге, в программе всегда ставится знак умножения (и до, и после скобок). Допустим, у нас есть вот такое выражение: 32+28(5+7) .

В ячейке нам нужно написать следующую формулу: =32+28*(5+7) .

Нажав Enter получаем ответ.

Задача3 (2 критерия Дата)

Сумма прописью онлайн

Другой задачей может быть нахождение суммарных продаж за период (см. файл примера Лист “2 Даты” ). Используем другую исходную таблицу со столбцами Дата продажи и Объем продаж .

Формулы строятся аналогично задаче 2: = СУММЕСЛИМН(B6:B17;A6:A17;”>=”&D6;A6:A17;”

Примечание . Даты могут быть представлены в числовой форме см. статью Как Excel хранит дату и время

При необходимости даты могут быть введены непосредственно в формулу = СУММЕСЛИМН(B6:B17;A6:A17;”>=15.01.2010″;A6:A17;”

Чтобы вывести условия отбора в текстовой строке используейте формулу =”Объем продаж за период с “&ТЕКСТ(D6;”дд.ММ.гг”)&” по “&ТЕКСТ(E6;”дд.ММ.гг”)

В последней формуле использован Пользовательский формат .

Другие виды задач с использованием функции ВПР в Excel

Сумма прописью формула эксель

С использованием функции ВПР(), кроме выполнения математических вычислений из нескольких столбцов с использованием извлеченных по аргументам данных, аналогичным образом можно решить другие математические задачи совместно с другими дополнительными функциями:

Операция	Пример	Описание
Расчет среднего значения	{=СРЗНАЧ(ВПР(A2, ‘Lookup Table’$A$2:$D$10, {2, 3, 4}, ЛОЖЬ))}	Формула находит среднее значение из столбцов B, C и D таблицы ‘Lookup table’ в той же строке для ячейки A2.
Поиск максимального значения	{=МАКС(ВПР(A2, ‘Lookup Table’$A$2:$D$10, {2,3,4}, ЛОЖЬ}	По значению ячейки A2 формула находит максимальное число в столбцах B, C и D таблицы ‘Lookup Table’ в этой же строке.
Поиск минимального значения	{=МИН(ВПР(A2, ‘Lookup Table’$A$2:$D$10, {2,3,4}, ЛОЖЬ))}	Для ячейки A2 формула находит минимальное число в столбцах B, C и D этой же строки таблицы ‘Lookup Table’.
Расчет процента от суммы	{=0.3СУММ(ВПР(A2, ‘Lookup Table’$A$2:$D$10, {2,3,4}, ЛОЖЬ))}*	Формула подбирает значение, равное указанному в ячейке A2 из таблицы ‘Lookup Table’, далее суммирует значения в столбцах B, C и D в этой же строке, далее высчитывает из полученного числа 30%.

Если рассматривать вышеуказанные формулы по образцу таблицы из первого примера, полученные результаты будут выглядеть вот так:

Дополнительные формулы сокращенного умножения

Функция счетесли в excel

К таблице основных ФСУ следует добавить еще несколько важных тождеств, которые пригодятся для решения задач.

Бином Ньютона

Формула для разложения на отдельные слагаемые целой неотрицательной степени суммы двух переменных. Записывается вот так:

Пример вычисления биномиальных коэффициентов, которые стоят в строке под номером n в треугольнике Паскаля:

ФСУ для квадрата и куба суммы и разности — являются частными случаями формулы бинома Ньютона при n = 2 и n = 3.

Формула возведения в квадрат суммы трех, четырех и более слагаемых

Пригодится, если слагаемых в сумме, которую нужно возвести в степень, больше, чем два.

(a₁+a₂+…+a_n)2 = a₁2 + a₂2 + … + a_n-12 + a_n2 + 2 * a₁ * a₂ + 2 * a₁ * a₃ + 2 * a₁ * a₄ + … +

+ 2 * a₁ * a_n-1 + 2 * a₁ * a_n + 2 * a₂ * a₃ + 2 * a₂ * a₄ + … + 2 * a₂ * a_n-1 + 2 * a₂ * a_n +…+

+ 2 * a_n-1 * a_n

Читается так: квадрат суммы n слагаемых равен сумме квадратов всех этих слагаемых и удвоенных произведений всех возможных пар этих слагаемых.

Формула разности n-ых степеней двух слагаемых

an − bn = (a − b) * (an-1 + an-2 * b + an-3 * b2 + … + a * bn-2 + bn-1).

Для четных показателей можно записать так:

a2*m − b2*m = (a2 − b2) *(a2*m−2 + a2*m−4 * b2 + a2*m−6 * b4 + … + b2*m−2).

Для нечетных показателей:

a2*m+1 − b2*·m+1 = (a − b) * (a2*m + a2*m−1 * b + a2*m−2 * b2 + … + b2*m).

Частными случаями являются формулы разности квадратов и кубов при n = 2 и n = 3. Для разности кубов b можно также заменить на −b.

Синтаксис функции и принцип работы

О возможностях сложения известно каждому пользователю, хоть раз запускавшего Excel. СУММЕСЛИ – логичное продолжение базовой СУММ, отличие которой заключается в суммировании по условию.

Синтаксис СУММЕСЛИ выглядит следующим образом:

=СУММЕСЛИ(диапазон; критерий; ), где:

диапазон – список значений, к которым будет применено ограничение;
критерий – конкретное условие;
диапазон суммирования – список значений, которые будут суммироваться.

Итак, имеем функцию с тремя компонентами. Стоит отметить, что последний аргумент можно не использовать – СУММЕСЛИ способен работать без диапазона суммирования.

Постановка задачи

Имеем таблицу по продажам, например, следующего вида:

Задача: просуммировать все заказы, которые менеджер Григорьев реализовал для магазина “Копейка”.

Способ 1. Функция СУММЕСЛИ, когда одно условие

Если бы в нашей задаче было только одно условие (все заказы Петрова или все заказы в “Копейку”, например), то задача решалась бы достаточно легко при помощи встроенной функции Excel СУММЕСЛИ (SUMIF) из категории Математические (Math&Trig). Выделяем пустую ячейку для результата, жмем кнопку fx в строке формул, находим функцию СУММЕСЛИ в списке:

Жмем ОК и вводим ее аргументы:

Диапазон – это те ячейки, которые мы проверяем на выполнение Критерия. В нашем случае – это диапазон с фамилиями менеджеров продаж.
Критерий – это то, что мы ищем в предыдущем указанном диапазоне. Разрешается использовать символы * (звездочка) и ? (вопросительный знак) как маски или символы подстановки. Звездочка подменяет собой любое количество любых символов, вопросительный знак – один любой символ. Так, например, чтобы найти все продажи у менеджеров с фамилией из пяти букв, можно использовать критерий ?????. А чтобы найти все продажи менеджеров, у которых фамилия начинается на букву “П”, а заканчивается на “В” – критерий П*В. Строчные и прописные буквы не различаются.
Диапазон_суммирования – это те ячейки, значения которых мы хотим сложить, т.е. нашем случае – стоимости заказов.

Способ 2. Функция СУММЕСЛИМН, когда условий много

Если условий больше одного (например, нужно найти сумму всех заказов Григорьева для “Копейки”), то функция СУММЕСЛИ (SUMIF) не поможет, т.к. не умеет проверять больше одного критерия. Поэтому начиная с версии Excel 2007 в набор функций была добавлена функция СУММЕСЛИМН (SUMIFS) – в ней количество условий проверки увеличено аж до 127! Функция находится в той же категории Математические и работает похожим образом, но имеет больше аргументов:

При помощи полосы прокрутки в правой части окна можно задать и третью пару (Диапазон_условия3–Условие3), и четвертую, и т.д. – при необходимости.

Если же у вас пока еще старая версия Excel 2003, но задачу с несколькими условиями решить нужно, то придется извращаться – см. следующие способы.

Функция СУММПРОИЗВ – подробное описание

Несмотря на кажущуюся с первого взгляда простоту, эта функция не такая уж и простая. Точно так же, как и спектр ее применения. Все потому, что она выполняет сразу несколько действий. Так, с ее помощью можно суммировать значения, опираясь на определенные критерии. Кроме этого, она позволяет избавиться от необходимости использовать формулы массива в работе, которые очень часто приходится применять для стандартной функции СУММ.

Сами способы следующие:

Определение суммы произведений массива.
Проверка значения на предмет соответствия определенным критериям, получение их произведений и суммирования.
Использование в качестве замены формулы массива.

На самом деле, возможных вариаций методов использования функции СУММПРОИЗВ значительно больше. Опытный пользователь Excel с легкостью сможет комбинировать несколько функций для того, чтобы заставить электронную таблицу работать так, как надо именно ему.

Синтаксис функции СУММПРОИЗВ

В качестве аргументов для этой функции используется набор диапазонов, которые сначала множатся между собой, а потом получившиеся результаты суммируются. Разделение аргументов осуществляется с помощью точки с запятой

Важно учитывать, что массивы не могут быть разного типа. Простыми словами, можно использовать либо только вертикальные, либо только горизонтальные диапазоны

Давайте приведем наиболее простой для понимания пример применения этой функции. А потом будем постепенно усложнять задачу, демонстрируя все более новые особенности ее использования.

Итак, у нас есть таблица, в которой заранее известны значения длины и ширины разных прямоугольников. Перед нами была поставлена задача определить сумму их площадей. Можно обойтись и без функции СУММПРОИЗВ, рассчитав площадь путем умножения длины на ширины вручную, после чего просуммировать получившиеся значения. В результате, получится такая громоздкая таблица.

Но можно значительно упростить задачу, использовав функцию СУММПРОИЗВ, использовав два аргумента: диапазон всех значений длины и диапазон всех значений ширины. После этого программа сама выполнит все необходимые действия.

Видим, что результат использования функции СУММПРОИЗВ получился идентичным.

Функция СУММПРОИЗВ с условием

В чистом виде функция СУММПРОИЗВ используется довольно редко. Особенно в бухгалтерском учете. Тяжело представить ситуацию, что она оказывается непосредственно полезной каждый день для того, чтобы рассчитывать сумму произведений. Впрочем, такие ситуации тоже бывают. Например, если поставлена задача перемножить между собой курс и сумму в долларах, а потом получить общий финансовый показатель, основанный на стоимости каждой продукции в национальной валюте. Но такая задача все равно случается довольно редко.

При этом данная формула очень часто используется для того, чтобы выводить значения, основываясь на определенных условиях.

Значительно проще рассмотреть это на реальном примере. Предположим, у нас есть таблица расходов крошечной организации за один месяц. Перед нами стоит задача определить, сколько денег было потрачено в итоге за два первых месяца года суммарно по всем статьям расходов.

Чтобы выполнить эту задачу с помощью этой функции, необходимо ее использовать и записать два условия в самом начале. Каждое из условий заключается в скобках. Поскольку в нашем случае должно быть соответствие сразу двум критериям, то мы использовали символ звездочки (*). Синтаксис виден на этом скриншоте. Вам достаточно просто подставить соответствующие вашему случаю значения в необходимые места. И дело сделано!

Давайте рассмотрим аргументы более подробно:

Первое условие записывается в первых скобках.
Второе условие записывается во вторых скобках.
Третий аргумент – это массив, сумму из которого необходимо извлечь.

Итоговый результат – 3700 рублей. Если распространить эту формулу на остальные строки и заменить условия в каждой из них, можно получить значения, характерные для другого отчетного периода или иной статьи расходов. К сожалению, лучше автоматизировать этот процесс не получится. Придется некоторые показатели править вручную. Но такова жизнь. Правда, если научиться писать макросы, можно добиться абсолютно любых уровней автоматизации при работе с электронными таблицами.

Оператор ПРОИЗВЕД

В Эксель, помимо использования формулы, также предусмотрена специальная функция для выполнения умножения под названием – ПРОИЗВЕД. Работа с функцией предполагает следующие шаги:

Выбираем ячейку, в которой планируем выполнить расчеты и нажимаем кнопку “Вставить функцию” рядом со строкой формул.
В открывшемся окне мастера функций выбираем категорию “Математические”, находим оператор “ПРОИЗВЕД”, отмечаем его и жмем OK.
Откроется окно с аргументами функции (максимум, до 255), в которых могут быть указаны:
- обычные числа;
- ссылки на ячейки (относительные или абсолютные), которые пишутся либо вручную, либо просто выбираются путем клика на листе;
- комбинированные аргументы, т.е. и конкретные числа, и ссылки на ячейки.
Когда все аргументы заполнены, жмем кнопку OK и получаем результат в ячейке с функцией (в нашем случае – произведение ячеек).

Примечание: Мы можем сразу набрать формулу функции в нужной ячейке, не прибегая к помощи Мастера, если знаем, как правильно это сделать.

Sumproduct в русском excel

Функция СУММПРОИЗВ – секретное оружие Excel

Функция СУММПРОИЗВ (SUMPRODUCT) из категории Математические – это одна из тех незаслуженно обделенных вниманием функций в Excel, о настоящих возможностях которой очень сложно найти информацию в книгах или интернете. Справка Excel по этой функции говорит о том, что она может перемножить несколько массивов ячеек и сложить результаты – т.е., например, перемножить столбцы с ценами и количеством товаров, сложить получившиеся суммы и выдать общую выручку

Звучит весьма банально, согласитесь?

На самом деле решение таких задач – это, наверное, последнее, для чего я бы использовал функцию СУММПРОИЗВ. На самом деле она может гораздо больше! Посмотрите-ка, вот на эту таблицу отгрузок товара:

Задача – сформировать по ней отчет, из которого было бы видно суммарную выручку по каждому наименованию и заказчику. Посмотрите как красиво эта задача решается с помощью одной формулы(!) :

Т.е. все что надо сделать – это ввести названия товаров в ячейках E3:E8 и названия заказчиков в ячейки F2:H2, а потом ввести в ячейку F3 формулу с нашей функцией:

=СУММПРОИЗВ( ($A$2:$A$20=$E3) * ($B$2:$B$20=F$2) * ($C$2:$C$20) )

Говоря простым языком, эта функция суммирует значения стоимости из диапазона $C$2:$C$20, если значения диапазона $A$2:$A$20 (наименования) равны $E3 (грейпфрут), а значения диапазона $B$2:$B$20 (заказчики) равны F$2 (Ланит). Все, что осталось сделать – это скопировать эту формулу на все ячейки отчета (F2:H8).

Другими словами синтаксис этой функции можно представить так:

=СУММПРОИЗВ( (условие1) * (условие2) * (что_суммировать) )

Причем, никто не мешает Вам добавить третье, четвертое и т.д. условия, расширяя свой отчет. Например, если в исходную таблицу добавить столбец с номером склада, с которого происходила отгрузка товара, то можно отразить это в отчете, добавив третье условие в функцию СУММПРОИЗВ:

Умные люди подсказали одну совсем не очевидную фишку. При использовании функции СУММПРОИЗВ вы можете давать ссылки на диапазоны не только из той же книги, где идет расчет, но и из других файлов. Например, если мы хотим подсчитать сумму всех ячеек, значение которых больших десяти из диапазона А1:А10 с листа Затраты из книги Бюджет.xls , то формула могла бы выглядеть примерно так:

Проблема в том, что если файл Бюджет.xls в данный момент не открыт, то формула при пересчете листа выдает ошибку #ЗНАЧ. Но, если добавить в формулу двойное бинарное отрицание (два знака минус подряд) перед аргументами, и немного ее изменить, то она будет работать даже при закрытом файле Бюджет.xls . Формула должна иметь следующий вид:

Вот уж, как говорится, ни в жизнь бы сам до такого не додумался.

НОВОСТИ ФОРУМАРыцари теории эфира

01.10.2019 – 05:20: ВОСПИТАНИЕ, ПРОСВЕЩЕНИЕ, ОБРАЗОВАНИЕ – Upbringing, Inlightening, Education -> 69vJGqDENq414:36Osievskii Global News29 сент. Отправлено 05:20, 01.10.2019 г.’ target=_top>Просвещение от Вячеслава Осиевского – Карим_Хайдаров.30.09.2019 – 12:51: ВОСПИТАНИЕ, ПРОСВЕЩЕНИЕ, ОБРАЗОВАНИЕ – Upbringing, Inlightening, Education -> 37630907011:03 Отправлено 12:51, 30.09.2019 г.’ target=_top>Просвещение от Дэйвида Дюка – Карим_Хайдаров.30.09.2019 – 11:53: ВОСПИТАНИЕ, ПРОСВЕЩЕНИЕ, ОБРАЗОВАНИЕ – Upbringing, Inlightening, Education -> VVQv1EzDTtY10:43

интервью Раввина Борода https://cursorinfo.co.il/all-news/rav. мой телеграмм https://t.me/peshekhonovandreiмой твиттер https://twitter.com/Andrey54708595мой инстаграм https://www.instagram.com/andreipeshekhonow/

Доминико Риккарди: Россию ждёт страшное будущее (хотелки ЦРУ):https://tainy.net/22686-predskazaniya-dominika-rikardi-o-budushhem-rossii-sdelannye-v-2000-godu.html

Завещание Алена Даллеса / Разработка ЦРУ (запрещено к ознакомлению Роскомнадзором = Жид-над-рус-надзором)http://av-inf.blogspot.com/2013/12/dalles.html

Примеры

Пример #1. Можно ли утвержадать, что число 6 — делитель числа 55?

Решение:

По определению делителем натурального числа a называют натуральное число, на которое число a делится без остатка.

Значит, чтобы число 6 было делителем числа 55, нужно, чтобы число 55 делилось на число 6 без остатка.

В данно случае деление получается с остатком, т. к. число 55 не делится нацело на число 6, т.е. число 6 не является делителем числа 55.

Ответ: нет.

Пример #2. Назови все двузначные числа, кратные числу 48.Решение: По определению кратным натуральному числу a называют натуральное число, которое делится без остатка на число a.

Значит, чтобы двузначное число было бы кратным числу 48, оно должно делиться на число 48 без остатка.

Таких двузначных чисел, делящихся на число 48 без остатка, два: 48; 96.

Ответ: 48;96.

Пример #3. Не выполняя вычислений, определи, какому числу из предложенных в ответе (3, 2 или 7) кратно данное произведение 29 ⋅ 27.Решение: Известно, что кратным натуральному числу a называют натуральное число, которое делится без остатка на число a.

Также знаем, что если хотя бы один из множителей делится на некоторое число, то и произведение делится на это число.

В данном произведении 29 ⋅ 27 множитель 27 делится без остатка на число 3, значит, и произведение 29 ⋅ 27 делится без остатка на число 3, т.е. кратно числу 3.

Ответ: 3.

Пример #4. В каждой коробке лежат 8 чайных ложек. Возможно ли, взять определённое количество коробок, чтобы в них лежало ровно 13 ложки(-ек)? Решение: Анализируя условие задачи, можно сделать вывод, что утвердительный ответ возможен, если число ложек, которое мы хотим взять, кратно числу ложек, находящихся в каждой коробке.

Это следует из определения: кратным натуральному числу a называют натуральное число, которое делится без остатка на число a. Значит, нельзя, не вскрывая коробок, взять 13 шт. ложек, т. к. число 13 не делится на число 8 без остатка, т. е. 13 : 8 ≠ целому числу.

Ответ: нет.

Пример #5. Сократи дробь:

Решение:

Для сокращения дроби заметим, что один из множителей в числителе дроби и один из множителей в знаменателе дроби делится на число 5, значит, и произведения в числителе и знаменателе делятся на число 5.

Поэтому, если сократим эти множители на 5, останется дробь с такими множителями в числителе и знаменателе.

Ответ:

Пример #6. В одном букете было 16 роз, а в другом — 49. Можно ли эти розы поставить в 6 ваз так, чтобы в каждой вазе цветов было поровну?Решение: 1. Определим общее количество роз в двух букетах вместе: 16 + 49 = 65 шт.

2. Можно ли эти розы поставить в 6 ваз так, чтобы в каждой вазе цветов было поровну? Для ответа на этот вопрос нужно проверить делимость полученной суммы на число 6.

Получим, что розы поставить в 6 ваз так, чтобы в каждой вазе цветов было поровну нельзя, потому что числа не делятся нацело, т. к. 65 : 6 ≠ целому числу.

Ответ: разделить поровну нельзя, потому что числа не делятся нацело.

Пример #7. Укажи натуральное значение x, чтобы сумма 42 + x не делилась на 7. Выбери из следующих вариантов: 5, 21, 7.

Решение: Для того, чтобы сумма 42 + x не делилась на 7, выбираем значение x = 5, т.к. известно, что если каждое слагаемое делится на некоторое число, то и сумма делится на это число.

Имеем, что число 42 делится на 7, значит, число x не должно делиться на 7, чтобы сумма не делилась на 7.

Значение x = 5 не делится на 7.

Ответ: 5.

Пример #8. Известно, что c и d — натуральные числа и 5c + d = 42. Каким может быть число c?

Решение: Известно, что c и d — натуральные числа и 5c + d = 42. Выражение 5c = 42 − d должно быть кратно числу 5.

Поэтому число c может быть равно 1;2;3;4;5;6;7;8, тогда 5c = 5; 10; 15; 20; 25; 30; 35; 40, а d = 37; 32; 27; 22; 17; 12; 7; 2, т. е. сумма будет равна 5c + d = 42.Ответ: 1;2;3;4;5;6;7;8.

Пример #9. Определи натуральные значения, которые может принимать выражение

если 0 < x < 40, x — натуральное число.

Решение: Для того, чтобы выражение приняло натуральные значения, необходимо вместо переменной x подставить числа, кратные числу 8, принадлежащие промежутку 0 < x < 40. Это будут числа 8; 16; 24; 32.

Разделив эти числа на число 8 и умножив полученные частные на 3, имеем в результате 3;6;9;12.

Ответ: 3;6;9;12.

Пример #10. Выполни деление: (39b + 24) : 3.

Решение: Известно, что если каждое слагаемое делится на некоторое число, то и вся сумма делится на это число.

Выполняя действие деления суммы на число 3, необходимо разделить на 3 как первое слагаемое, так и второе слагаемое.

Получим: (39b + 24) : 3 = 39b : 3 + 24 : 3 = 13b + 8.Ответ: 13b + 8.

Пример #11. Выполни деление: 6xy : 3x.

Решение: Выполняя деление (6xy):(3x), разделим сначала числовые множители, затем одинаковые буквенные множители, полученные результаты перемножим.

Получим: 6xy : 3x = 6:3 ⋅ (x:x) ⋅ y = 2 ⋅ 1 ⋅ y = 2y.

В результате деления получаем ответ: 2y.Ответ: 2y.

Функция СУММПРОИЗВ — как использовать формулу СУММПРОИЗВ в Excel

В сегодняшней статье мы рассмотрим одну из самых популярных и мощных функций Excel – СУММПРОИЗВ. Изначально функция может показаться вам не такой полезной, как многие описывают. Но стоит начать работать со списками или массивами данных, польза СУММПРОИЗВ становится очевидной.

СУММПРОИЗВ – синтаксис формулы

Синтаксис формулы Excel суммы произведений довольно прост. Функция берет в качестве аргументов один или более массивов данных и возвращает сумму произведений соответствующих значений.

Синтаксис функции выглядит следующим образом: =СУММПРОИЗВ(список1; список2 …)

То есть, если у вас есть массив данных в одной колонке и – в другой, и вы воспользовались функцией СУММПРОИЗВ, вы получите результат 120 (потому что 2*5+3*10+4*20=120).

СУММПРОИЗВ и массивы

Давайте предположим, что у вас имеется таблица продаж с колонками Имя продавца, Регион и Сумма продаж. И вам необходимо узнать, на какую сумму сделал продаж тот или иной агент. Это просто, можно воспользоваться функцией СУММЕСЛИ и указать в качестве критерия суммирования, необходимое нам имя агента.

Вопрос довольно просто решаем с одним условием, но что если количество условий возрастает и нам необходимо узнать сумму продаж конкретного агента в конкретном регионе. У нас есть три пути:

На самом деле у нас есть еще один, скрытый путь, воспользоваться СУММПРОИЗВ.

Использование СУММПРОИЗВ в формулах массива

Если предположить, что данные находятся в диапазоне A2:C21, с именами агентов в колонке A, регионами – в колонке B и продажами – в колонке C, то формула СУММПРОИЗВ будет выглядеть следующим образом:

=СУММПРОИЗВ(- -(A2:A21=»Агент Смит»);- -(B2:B21=»Запад»);C2:C21)

Давайте разберемся, как работает формула:

Часть формулы (- -(A2:A21=»Агент Смит» ищет Агента Смита в диапазоне A2:A21 и возвращает массив с единицами и нулями (единица, если ячейка содержит Агента Смита, и ноль – если нет).
Часть — -(B2:B21=»Запад») делает тоже самое, только возвращает единицу, если ячейка содержит Запад.
C2:C21 – просто возвращает массив с продажами

Если вы перемножите все три массива и затем просуммируете произведения, получиться искомый результат.

Послесловие

Функция СУММПРОИЗВ сможет гораздо больше, когда вы поймете, как она работает. Данная статья только приоткрывает завесу тайны к данной формуле.

Как умножать в экселе

Как умножать в экселе
Как в excel умножить столбцы
Как считать в Экселе (Excel)

Табличный редактор Excel 2007

Если требуется разовое действие — перемножить два числа – то последовательность действий должна быть такой:

— перейдите в пустую ячейку таблицы Excel (можно с помощью навигационных клавиш-стрелок, а можно щелкнув нужную ячейку мышью);

— нажмите клавишу «=». Excel интерпретирует это действие как начало ввода формулы;

— теперь печатайте нужное вам математическое действие, используя в качестве знака умножения звездочку (*). Знаки математических действий принято назвать «операторами». Например, если надо перемножить 2 на 3, то в ячейке вам нужно напечатать «=2*3» — здесь знак равенства остался от предыдущего шага, повторно печатать его не нужно. Если требуется перемножить не два, а больше чисел — ничего не меняется в правилах, печатайте больше. Например, =2*3*4*7*12;

— по завершению ввода нажмите Enter. Excel рассчитает результат и покажет его в этой же ячейке.

Вместо обычной записи математического действия с использованием оператора умножения (*), можно использовать функцию с именем ПРОИЗВЕД. В этом случае содержимое ячейки таблицы, осуществляющей перемножение пяти чисел, будет выглядеть так: =ПРОИЗВЕД(2;3;4;7;12).

Если надо организовать постоянную форму из нескольких ячеек, чтобы в одной из них напечатать множимое, в другой — множитель, а в третьей увидеть результат умножения:

— в первой свободной ячейке напечатайте число (множимое) и нажмите Enter;

— во второй свободной ячейке напечатайте второе число (множитель) и нажмите Enter;

— в третьей ячейке нажмите клавишу «=» и с помощью клавиш навигации (стрелок) переместитесь в первую ячейку (содержащую множимое). Можно вместо клавиш просто щелкнуть ячейку курсором мыши. Сделав это, нажмите звездочку (оператор умножения). При этом курсор вернется обратно в третью ячейку, а вам нужно с помощью тех же клавиш навигации или курсора мыши перейти во вторую ячейку, содержащую множитель. После этого содержимое ячейки с формулой должно выглядеть, например, так: =A1*A2. Сделав все это, нажмите Enter для завершения ввода формулы и в третьей ячейке увидите результат умножения.

Вы построили мини-калькулятор — теперь можете менять числа множимого и множителя, а Excel будет в третьей ячейке показывать их произведение.

И здесь умножаемых чисел не обязательно должно быть только два, действуя точно так же можно организовать нужное вам количество перемножаемых ячеек с числами. Но в этом случае удобнее будет пользоваться не оператором *, а функцией ПРОИЗВЕД. Тогда вам не надо будет указывать каждую ячейку с числом по отдельности, а можно указать целый диапазон ячеек. Например, если вы поместили перемножаемые числа в ячейки начиная с A1 и заканчивая A8, то содержимое ячейки, показывающей результат перемножения их всех, должно быть таким: =ПРОИЗВЕД(A1:A8). Диапазон ячеек можно вписать в функцию «вручную», а можно выделить мышью и Excel впишет нужные значения сам.

Если надо разово умножить значение каждой ячейки в столбце (или строке) таблицы на какой-то коэффициент:

— в пустой ячейке напечатайте это число-коэффициент;

— затем выделите эту ячейку, на вкладке «Главная» в самой первой группе («Буфер обмена») нажмите кнопку «Копировать»;

— теперь выделите диапазон ячеек, которые требуется умножить на коэффициент. Это можно сделать как мышкой, так и с помощью стрелок при нажатой клавише CTRL;

— в той же группе «Буфер обмена», ниже команды «Вставить», есть стрелка, которая открывает дополнительные варианты вставки — щелкните ее и выберите в списке пункт «Специальная вставка»;

— в группе переключателей «Операция» выберите «Умножить»;

— нажмите кнопку «ОК» и Excel перемножит каждую выделенную ячейку на значение коэффициента, скопированного вами в буфер обмена.

Правила использования функции ПРОИЗВЕД в Excel

Функция имеет следующий синтаксис:

=ПРОИЗВЕД( число1; ;…)

число1 – обязательный для заполнения, принимает числовое значение, ссылку на ячейку или диапазон ячеек с числовыми данными, которые необходимо перемножить;
;… — второй и последующие необязательные аргументы, которые принимают числовые значения или ссылки на ячейки с числами, выступающими в роли второго и последующим множителей. Если явно не указан, принимается значение, равное 1. Например, =ПРОИЗВЕД(5) эквивалентно =5*1 и возвращает число 5. Максимальное число аргументов – 255.

Функция выполняет промежуточные преобразования данных там, где это возможно, например, текстовые представления чисел, логические ИСТИНА и ЛОЖЬ. Например, =ПРОИЗВЕД(“3”;”6”) вернет значение 18, =ПРОИЗВЕД(3;ЛОЖЬ) вернет значение 0 (так как логическое ложь соответствует числовому 0).
Если аргументы рассматриваемой функции указаны в виде ссылок на диапазоны ячеек, в некоторых из которых содержатся не преобразуемые к числам данные, функция исключит эти данные из расчетов. Например, результатом выполнения =ПРОИЗВЕД(A1:A4;B1:B4) будет число 144 ( в ячейках A1:A4 содержатся данные: 1, 2, 3, “текст”, в ячейках B1:B4 содержатся числа: 1, 2, 3, 4 соответственно).

Как читать формулы сокращенного умножения

Учимся проговаривать формулы сокращенного выражения:

Разность квадратов двух выражений равна произведению их разности и их суммы.
Квадрат суммы двух выражений равен квадрату первого плюс удвоенное произведение первого на второе плюс квадрат второго.
Квадрат разности двух выражений равен квадрату первого минус удвоенное произведение первого на второе плюс квадрат второго.
Сумма кубов двух выражений равна произведению суммы первого и второго на неполный квадрат их разности.
Разность кубов двух выражений равна произведению разности первого и второго на неполный квадрат их суммы.
Куб суммы двух выражений равен кубу первого плюс утроенное произведение квадрата первого на второе плюс утроенное произведение первого на квадрат второго плюс куб второго.
Куб разности двух выражений равен кубу первого минус утроенное произведение квадрата первого на второе плюс утроенное произведение первого на квадрат второго минус куб второго.

Синтаксис СУММПРОИЗВ

Аргументами функции СУММПРОИЗВ являются массивы, т.е. заданные диапазоны. Их может быть сколько угодно. Перечисляя их через точку с запятой, мы задаем количество массивов, которые надо сначала перемножить, а затем просуммировать. Единственное условие: массивы должны быть равными по длине и однотипными (т.е. либо все горизонтальные, либо все вертикальные).

Простейший пример использования функции

Чтобы стало понятно, как и что считает команда, рассмотрим простой пример. Имеем таблицу с указанными длинами и ширинами прямоугольников. Нам нужно сосчитать сумму площадей всех прямоугольников. Если не пользоваться данной функцией, придется произвести промежуточные действия и сосчитать площадь каждого прямоугольника, а только потом сумму. Как мы и сделали.

Обратите внимание, что нам не понадобился массив с промежуточными итогами. В аргументах функции мы использовали только массивы с длиной и шириной, а функция их автоматически перемножила и просуммировала, выдав тот же результат = 70

Часто задаваемые вопросы и проблемы.

Я надеюсь, что эти примеры помогли вам почувствовать функцию Excel СЧЕТЕСЛИ. Если вы попробовали какую-либо из приведенных выше формул в своих данных и не смогли заставить их работать или у вас возникла проблема, взгляните на следующие 5 наиболее распространенных проблем. Есть большая вероятность, что вы найдете там ответ или же полезный совет.

Возможен ли подсчет в несмежном диапазоне клеток?

Вопрос: Как я могу использовать СЧЕТЕСЛИ для несмежного диапазона или ячеек?

Ответ: Она не работает с несмежными диапазонами, синтаксис не позволяет указывать несколько отдельных ячеек в качестве первого параметра. Вместо этого вы можете использовать комбинацию нескольких функций СЧЕТЕСЛИ:

Неправильно: =СЧЕТЕСЛИ(A2;B3;C4;”>0″)

Правильно: = СЧЕТЕСЛИ (A2;”>0″) + СЧЕТЕСЛИ (B3;”>0″) + СЧЕТЕСЛИ (C4;”>0″)

Альтернативный способ – использовать функцию ДВССЫЛ (INDIRECT) для создания массива из несмежных клеток. Например, оба приведенных ниже варианта дают одинаковый результат, который вы видите на картинке:

=СУММ(СЧЁТЕСЛИ(ДВССЫЛ({“B2:B11″;”D2:D11″});”=0”))

Или же

=СЧЕТЕСЛИ($B2:$B11;0) + СЧЕТЕСЛИ($D2:$D11;0)

Амперсанд и кавычки в формулах СЧЕТЕСЛИ

Вопрос: когда мне нужно использовать амперсанд?

Ответ: Это, пожалуй, самая сложная часть функции СЧЕТЕСЛИ, что лично меня тоже смущает. Хотя, если вы подумаете об этом, вы увидите – амперсанд и кавычки необходимы для построения текстовой строки для аргумента.

Итак, вы можете придерживаться этих правил:

Если вы используете число или ссылку на ячейку в критериях точного соответствия, вам не нужны ни амперсанд, ни кавычки. Например:

= СЧЕТЕСЛИ(A1:A10;10) или = СЧЕТЕСЛИ(A1:A10;C1)

Если ваши условия содержат текст, подстановочный знак или логический оператор с числом, заключите его в кавычки. Например:

= СЧЕТЕСЛИ(A2:A10;”яблоко”) или = СЧЕТЕСЛИ(A2:A10;”*”) или = СЧЕТЕСЛИ(A2:A10;”>5″)

Если ваши критерии – это выражение со ссылкой или же какая-то другая функция Excel, вы должны использовать кавычки (“”) для начала текстовой строки и амперсанд (&) для конкатенации (объединения) и завершения строки. Например:

= СЧЕТЕСЛИ(A2:A10;”>”&D2) или = СЧЕТЕСЛИ(A2:A10;”<=”&СЕГОДНЯ())

Если вы сомневаетесь, нужен ли амперсанд или нет, попробуйте оба способа. В большинстве случаев амперсанд работает просто отлично.

Например, = СЧЕТЕСЛИ(C2: C8;”<=5″) и = СЧЕТЕСЛИ(C2: C8;”<=”&5) работают одинаково хорошо.

Как сосчитать ячейки по цвету?

Вопрос: Как подсчитать клетки по цвету заливки или шрифта, а не по значениям?

Ответ: К сожалению, синтаксис функции не позволяет использовать форматы в качестве условия. Единственный возможный способ суммирования ячеек на основе их цвета – использование макроса или, точнее, пользовательской функции Excel VBA.

Ошибка #ИМЯ?

Проблема: все время получаю ошибку #ИМЯ? Как я могу это исправить?

Ответ: Скорее всего, вы указали неверный диапазон. Пожалуйста, проверьте пункт 1 выше.

Формула не работает

Проблема: моя формула не работает! Что я сделал не так?

Ответ: Если вы написали формулу, которая на первый взгляд верна, но она не работает или дает неправильный результат, начните с проверки наиболее очевидных вещей, таких как диапазон, условия, ссылки, использование амперсанда и кавычек.

Будьте очень осторожны с использованием пробелов. При создании одной из формул для этой статьи я был уже готов рвать волосы, потому что правильная конструкция (я точно знал, что это правильно!) не срабатывала. Как оказалось, проблема была на самом виду… Например, посмотрите на это: =СЧЁТЕСЛИ(A4:A13;” Лимонад”). На первый взгляд, нет ничего плохого, кроме дополнительного пробела после открывающей кавычки. Программа отлично проглотит всё без сообщения об ошибке, предупреждения или каких-либо других указаний. Но если вы действительно хотите посчитать товары, содержащие слово «Лимонад» и начальный пробел, то будете очень разочарованы….

Если вы используете функцию с несколькими критериями, разделите формулу на несколько частей и проверьте каждую из них отдельно.

И это все на сегодня. В следующей статье мы рассмотрим несколько способов подсчитывания ячеек в Excel с несколькими условиями.

Ещё примеры расчета суммы: