7 функций excel для экономиста: простые примеры для сложных задач

Тригонометрические функции

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

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

Первое, на что следует обратить внимание, что подавляющее большинство функций не переводится, а исполь­зуется их написание латиницей.

Следующим немаловажным моментом является неспособность программы вывести на экран значение угла с минутами и секундами (10°5’10»).
Единственным вариантом решения остается написание пользовательской функции на VBA.
Она настолько востребована, что Microsoft приводит вариант на своем сайте в качестве примера.
Но его существенным недостатком является некорректная обработка десятичной доли секунд.
Сам текст описания изобилует ошибками.

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

ПИ (PI)

Возвращает число 3,14159265358979 — математическую константу π (пи) с точностью до 15 цифр.

Здесь она приведена, так как является основой работы с радианами — базовой угловой единицей в Excel.
Пи радиан соответствует угловой величине в 180°.

ГРАДУСЫ (DEGREES)

Преобразует угловой размер, выраженный в радианах, в угловые градусы.

В качестве аргумента передается значения угла в радианах!

Обратной функцией является РАДИАНЫ (RADIANS), востребованная для перевода «нормального» значения в систему измерений, понятную программе.

Альтернативно можно использовать формулу Угол°*ПИ()/180, результатом которой также будет значение в радианах.
То есть, например, TAN(45*ПИ()/180) полностью эквивалентно TAN(РАДИАНЫ(45)), но будет вычисляться чуть дольше.

Дополнительно.
Градусная мера для использования в программе может быть выражена только десятичным числом!

SIN

На примере данной функции будет разобран весь основной массив.

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

Таким образом необходимо использовать синтаксис SIN(<угол в радианах>), что далеко не всегда удобно.

Чтобы воспользоваться стандартными угловыми градусами, придется дополнять формулу:

SIN(РАДИАНЫ(<угол в градусах>)).

Arc-функции

В программе используется ряд обратных функций, извлекающих из значения тригонометрической функции угол (в радианах!), которому они соответствуют.
Все они начинаются с буквы «A»: ACOS, ACOSH, ACOT, ACOTH, ASIN, ASINH, ATAN, ATAN2, ATANH.

Таким образом, например, зная, что тангенс угла равен 0,5, можно вычислить значение угла:

=ГРАДУСЫ(ATAN(0,5)).

Результатом будет 26,56505118 (угловых градусов).

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

См. также Перечень некоторых функций.

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

• Как умножать в экселе
• Как в 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 перемножит каждую выделенную ячейку на значение коэффициента, скопированного вами в буфер обмена.

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

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

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

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

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

Простейшие функции

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

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

Диапазон в формуле выделен.
Это означает, что если он определен неправильно, то его можно исправить, выделив нужный мышкой или введя его вручную.
Если проще откорректировать единичные символы в координатах, то это следует делать, перейдя в режим редактирования (клавишей F2).

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

Другой способ.
Выделить нужный диапазон и нажать кнопку суммирования.
Excel сам найдет свободную ячейку (справа или внизу, в зависимости от выделения) и вставит в неё сумму.

Если вам нужна не сумма, а другая функция, нажмите на стрелку кнопки .
Появится список

,

из которого можно выбрать необходимое действие.

Перечисленное в списке осуществляется описанными ниже функциями.
Аргументы совершенно одинаковые: «число1, число2… | диапазон».
Каждая из приведенных функций может обработать до 30 числовых аргументов.
(В скобках указаны английские эквиваленты.)

• СУММ(аргументы) (SUM) — складывает аргументы и возвращает сумму.
  Единственная функция, использование которой просто с числами нелепо, так как формула СУММ(1,2,3) абсолютно идентична формуле =1+2+3.
• СРЗНАЧ(аргументы) (AVERAGE) — находит среднее арифметическое аргументов.
• СЧЁТ(аргументы) (COUNT) — подсчитывает количество чисел среди аргументов.
• МАКС(аргументы) (MAX) — находит наибольшее число среди аргументов.
• МИН(аргументы) (MIN) — находит наименьшее число среди аргументов.

СУММПРОИЗВ (SUMPRODUCT)

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

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

Доступ к этим материалам предоставляется только зарегистри­рован­ным пользователям!

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

Синтаксис чрезвычайно примитивен (как и Справка о функции):

=СУММПРОИВ(массив1;;;…),

но вряд ли у вас получится сделать то, что требуется нам.

Отметьте первое главное условие: обрабатываются несколько массивов идентичного размера, например, A:A и B:B, то есть два столбца целиком; либо A1:A5 и B1:B5, то есть по 5 ячеек.

Все остальные обстоятельства будут соответствовать приведенному выше примеру.

Доступ к этим материалам предоставляется только зарегистри­рован­ным пользователям!

Выборочные вычисления по одному или нескольким критериям

​ F9, то мы​ в ячейке G2.​

​ десятков, то такой​

​ сталкивались с такой​СУММЕСЛИ (SUMIF)​ все заказы в​

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

​ «*» (любое количество​ в году соответственно​»​Фрукты​ листа Excel. Чтобы​можно использовать подстановочные​суммирует только те​ окно заполняем, как​ написали бы критерий​ на замечание​​ (слово «​

​ увидим массив результатов​​ «Читать» эту формулу​​ способ начинает нагонять​ замечательной возможностью Excel​не поможет, т.к.​​ «Копейку», например), то​​ символов) или вопросительным​ составим следующую таблицу:​​B3:B22 – диапазон ячеек,​​Апельсины​

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

• ​ в предыдущей формуле,​​ – «выпущено».​ant6729​Условие​​ работы функции​​ лучше из середины​ тоску, да и​ как формулы массива,​ не умеет проверять​
• ​ задача решалась бы​​ знаком «?» (один​Для расчетов используем следующую​ содержащих значения членов​8 000 ₽​ выделите их и​ (​ C2:C5, для которых​ только «критерий» пишем​В разделе «Критерий»​: Там, где 1​» в E1), лишь​ОСТАТ​ наружу:​ ошибиться при вводе​ то советую почитать​ больше одного критерия.​ достаточно легко при​ любой символ). В​​ формулу:​​ прогрессии.​Масло​ нажмите клавишу F2,​?​ соответствующие значения из​ «%» — это​​ можно писать некоторые​​ — вставляете количество​ бы он не​
• ​, т.е. остатки от​​Функция​ формулы можно запросто.​ предварительно про них​ Поэтому начиная с​ помощи встроенной функции​

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

​ качестве примеров могут​В результате получим:​Полученный результат:​4 000 ₽​ а затем — клавишу​​) и звездочку (​​ диапазона B2:B5 равны​ в нашей таблице​ символы.​ ячеек для суммирования​ совпадал с заголовками​ деления номеров строк​СТРОКА (ROW)​​ Как же поступить?​​ много хорошего здесь.​ версии Excel 2007​ Excel​ быть критерии «ст?л»​Функция СУММЕСЛИ имеет следующий​Доля в процентах первых​​Овощи​​ ВВОД. При необходимости​*​ «Иван».​

​ так написано. Получилась​* (звездочка) означает​ (количество первых ячеек​ из таблицы с​ на 2:​​выдает номер строки​​ Есть способы сделать​​ Ну, а в​​ в набор функций​СУММЕСЛИ (SUMIF)​ (стол либо стул​

​ синтаксис:​ 15-ти значений (75%​Морковь​ измените ширину столбцов,​). Вопросительный знак соответствует​Если необходимо выполнить суммирование​ такая формула.​ любое количество символов​

Способ 3. Столбец-индикатор

​ диапазона)​ данными. После ввода​И, наконец, если выделить​ для каждой по​ это изящнее.​ нашем случае задача​ была добавлена функция​ из категории​ при поиске наименований​=СУММЕСЛИ(диапазон; условие; )​ — от количества​42 000 ₽​ чтобы видеть все​

​ одному любому символу,​

​ ячеек в соответствии​=СРЗНАЧЕСЛИ(B2:B10;»%»;C2:C10)​ в слове. Например,​Там, где 2​ условия в ячейку​ фрагмент​ очереди ячейки из​Если в таблице есть​ решается одной формулой:​СУММЕСЛИМН (SUMIFS)​Математические (Math&Trig)​ мебели), «Ива*» (фамилии​Описание аргументов:​ всех 20-ти значений)​Фрукты​ данные.​

Способ 4. Волшебная формула массива

​ а звездочка — любой​ с несколькими условиями,​Таблица посчитала так.​ «мор*» — будет​ — суммируется​ E2 появится слово​ЕСЛИ(ОСТАТ(СТРОКА(B2:B15);2)=0;B2:B15)​ диапазона B2:B15.​ столбец с признаком,​=СУММ((A2:A26=»Копейка»)*(B2:B26=»Григорьев»)*D2:D26)​

​- в ней​

​. Выделяем пустую ячейку​ Иванов, Иваненко, Иванищев​диапазон – обязательный для​ данной геометрической прогрессии​​Яблоки​Стоимость имущества​

​ последовательности символов. Если​ используйте функцию СУММЕСЛИМН.​Как посчитать наценку, скидку,​ искать слова на​Как-то так​ИСТИНА (TRUE)​и нажать на​Функция​ по которому можно​После ввода этой формулы​ количество условий проверки​ для результата, жмем​ и другие, которые​

Способ 4. Функция баз данных БДСУММ

​ заполнения аргумент, принимающий​​ составляет всего лишь​

​12 000 ₽​​Комиссионные​ ​ требуется найти непосредственно​СУММЕСЛИ(диапазон; условие; )​ умножив столбец на​ «мор» и любое​ant6729​или​F9​ОСТАТ (MOD)​ произвести выборочное суммирование​ необходимо нажать не​ увеличено аж до​ кнопку​

​ начинаются на «Ива»).​

planetaexcel.ru>

Как работает СУММЕСЛИМН?

При помощи СУММЕСЛИМН можно найти сумму величин, для которых есть много условий. Она появилась впервые в MS Excel 2007, поэтому вы можете использовать ее во всех современных версиях программы.

По сравнению с СУММЕСЛИ, синтаксис СУММЕСЛИМН немного сложнее:

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

диапазон_суммирования — требуется одна или более ячеек для суммирования. Это может быть отдельная клеточка, область или именованный диапазон. Суммируются только ячейки с чисами; пустые и текстовые значения игнорируются.

диапазон_условия1 — обязательный первый диапазон, который должен быть оценен по соответствующим критериям.

условие1- обязательное первое условие, которое должно быть выполнено. Вы можете предоставить его в виде числа, логического выражения, ссылки, текста или другой функции Excel. Например, вы можете использовать такие критерии, как 10, «> = 10», A1, «яблоко» или СЕГОДНЯ().

Все, что следует далее — это дополнительные диапазоны и связанные с ними критерии. Они не являются обязательными, но если у вас только одно ограничение, то зачем вам эта функция? Просто используйте СУММЕСЛИ. Тем не менее, вы можете использовать до 127 пар диапазон/условие.

Важно! Функция СУММЕСЛИМН работает с логикой «И». Это означает, что число в диапазоне суммирования учитывается, только если оно удовлетворяет всем указанным критериям (все требования соблюдаются для этой ячейки)

СУММПРОИЗВ (функция СУММПРОИЗВ)

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

В этом примере мы будем использовать СУММПРОИЗВ, чтобы вернуть общие продажи для определенного элемента и размера:

СУММПРОИЗВ соответствует всем экземплярам элемента Y/size M и суммирует их, поэтому для этого примера 21 плюс 41 равняется 62.

Чтобы использовать операцию по умолчанию (умножение), выполните указанные ниже действия.

= СУММПРОИЗВ (массив1; ; ;. )

Аргументы функции СУММПРОИЗВ описаны ниже.

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

От 2 до 255 массивов, компоненты которых нужно перемножить, а затем сложить результаты.

Выполнение других арифметических операций

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

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

Нахождение суммы произведений элементов массивов

В этом разделе показан синтаксис функции СУММПРОИЗВ() и раскрыт ее потенциал для других применений.

Пусть имеется 2 диапазона чисел A3:A6 и B3:B6 , содержащие соответственно 2 массива чисел : {4:8:6:1} и {7:6:7:5}. Записав формулу =СУММПРОИЗВ(A3:A6;B3:B6) , получим 123. Результат получен поэлементным перемножением всех элементов двух массивов, а затем сложением полученных произведений. То есть были выполнены следующие арифметические действия: 4*7 + 8*6 + 6*7 + 1*5= 123

Таким образом, можно найти сумму произведений 3-х, 4-х и т.д. массивов.

Аргументы, которые являются массивами, должны иметь одинаковые размерности (в нашем случае это массивы по 4 элемента). В противном случае функция СУММПРОИЗВ() возвращает значение ошибки #ЗНАЧ!.

В формуле =СУММПРОИЗВ(A3:A6;B3:B6) функция СУММПРОИЗВ() трактует нечисловые элементы массивов как нулевые. Однако, как показано ниже, функцию можно использовать для подсчета текстовых значений.

Что произойдет если указать только 1 массив, т.е. =СУММПРОИЗВ(A3:A6) ? Тогда функция СУММПРОИЗВ() вернет сумму элементов, т.е. будет эквивалентна функции СУММ() : =СУММ(A3:A6) .

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

• =СУММПРОИЗВ(A3:A6*2) , получим сумму произведений =38 (каждый элемент массива из A3:A6 был умножен на 2, затем все произведения просуммированы);
• =СУММПРОИЗВ(A3:A6*B3:B6) , получим результат суммы произведений – 123 (все элементы массивов были попарно перемножены, а затем сложены, т.е. A3*B3+ A4*B4+ A5*B5+ A6*B6), т.е. эта запись эквивалента формула =СУММПРОИЗВ(A3:A6;B3:B6) ;
• =СУММПРОИЗВ(A3:A6+B3:B6) , получим сумму элементов из двух диапазонов;
• =СУММПРОИЗВ(A3:A6/B3:B6 ), получим сумму попарных отношений всех элементов,  т.е. 4/7 + 8/6 + 6/7 + 1/5= 2,9619

Аналогичные вычисления можно выполнить и с функцией СУММ() , только для этого нужно ее ввести как формулу массива , т.е. после ввода функции в ячейку вместо ENTER нажать CTRL+SHIFT+ENTER : =СУММ(A3:A6/B3:B6)

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

Оказывается, что в качестве аргумента этой функции можно указать не только произведение массивов ( A3:A6*B3:B6 ), но и использовать другие функции и даже применить к массивам операции сравнения, т.е. использовать ее для сложения чисел, удовлетворяющих определенным условиям.

Пример 6: умножаем столбец на ячейку

Предположим, нам нужно посчитать скидку по перечню товаров, представленному в одном столбце (B). Размер скидки указан в отдельной ячейке (E2).

Алгоритм действий следующий:

1. Для начала пишем в самой верхней ячейке столбца C (не считая шапки) формулу умножения ячейки B2 на E2.
2. Не спешим нажимать клавишу Enter. Дело в том, что сейчас в формуле используются относительные ссылки, а это значит, что при ее копировании в другие ячейки произойдет смещение адресов (т.е. B3 будет умножаться на E3). Нам же нужно зафиксировать адрес E2, т.е. сделать ссылку на эту ячейку абсолютной. Для этого находясь курсором до, после, или внутри адреса (между буквой и цифрой) нажимаем клавишу F4.
3. Перед обозначением столбца и номером строки появятся символы “$”. Теперь можно нажимать Enter.
4. Растягиваем формулу на другие ячейки с помощью маркера заполнения.
5. Как мы можем убедиться на примере формулы в ячейке C9, в ней по-прежнему участвует ячейка E2, а это значит, что мы все сделали правильно.

Сложные формулы

Писать сложные формулы в Excel нужно точно такими же как и в математике. Например у вас заполнены ячейки данными и нужно чтобы в ячейке A4 отобразился результат исходя из формулы =A1+A2/(A3-A2)*A1.

Можно как писать формулы в Excel или вставлять уже готовые

Первым делом копируете эту формулу в буфер обмена чтобы потом вставить формулу в эксель. Наводите курсор на ячейку A4 и нажимаете левую кнопку мыши. Наводите курсор на строку формул в Excel и нажимаете левую кнопку мыши. Затем нужно в эту строку в Excel вставить формулу из буфера обмена, а для этого нажмите на клавиатуре клавишу Ctrl и удерживая её нажмите клавишу V. Формула пропишется в строке формул, а нажав на клавишу Enter сразу отобразится результат вычисления по этой формуле. Чтобы удалить формулу из ячейки в excel достаточно навести курсор на ячейку, нажать левую кнопку мыши и удалить формулу в строке формул.

Примечания

Аргументы, которые являются массивами, должны иметь одинаковые размерности. В противном случае функция СУММПРОИЗВ возвращает значение ошибки #ЗНАЧ!. Например, если = СУММПРОИЗВ (C2: C10; D2: D5), будет возвращена ошибка, так как диапазоны имеют одинаковый размер.

СУММПРОИЗВ обрабатывает нечисловые элементы массива так, как если бы они были нулевыми.

Чтобы создать формулу с помощью нашего примера списка выше, введите = СУММПРОИЗВ (C2: C5; D2: D5) и нажмите клавишу Ввод. Каждая ячейка в столбце C умножается на соответствующую ячейку в той же строке в столбце D, и результаты будут добавлены в нее. Общая сумма для сходить составляет $78,97.

Чтобы ввести более длинную формулу, которая приводит к тому же результату, введите = C2 * D2 + C3 * D3 + C4 * D4 + C5 * D5 и нажмите клавишу Ввод. После нажатия клавиши Ввод результат будет таким же: $78,97. Ячейка C2 умножается на D2, и ее результат добавляется в результат ячейки С3 и т. д.

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

Формула: = СУММПРОИЗВ (((Таблица1 ) + (Таблица1 )) * (Table1 = B8)) и возвращает сумму всех продаж и расходов для агента, указанного в ячейке B8.

В этом примере мы хотели бы вернуть сумму определенного элемента, проданного в определенном регионе. В этом случае количество вишний в регионе «Восток» продавалось?

Формула: = СУММПРОИЗВ ((B2: B9 = B12) * (C2: C9 = C12) * D2: D9). Сначала число вхождений Востока умножается на число совпадающих вхождений вишни. Наконец, она суммирует значения соответствующих строк в столбце Sales. Чтобы увидеть, как Excel вычисляет это, выделите ячейку с формулой, а затем перейдите к формулам > Вычислить формулу > оценить.

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

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

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

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

Суммирование и подсчет значений отвечающих критериям

Если мы напишем такую ​​формулу, результат будет равен нулю: = СУММПРОИЗВ (A3: A6> 2). Если вы перейдете к строке формулы и выберете там часть, написанную в скобках, затем нажмите F9, вы получите матрицу: ИСТИНА: ИСТИНА и ИСТИНА: ЛОЖЬ.

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

Здесь необходимо учитывать один нюанс: если значение ИСТИНА равно единице, а ЛОЖЬ равно нулю, это не означает, что с этими числами можно выполнять какие-либо математические операции. Для этого нужно поставить два знака минус рядом друг с другом. В этом случае массив будет преобразован в числовую форму, и вы сможете выполнять с ним математические операции.

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

= SUMPRODOTTO (- (B3: B6 = «яблоки»))

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

Сумма, если ячейки не пустые

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

В показанном примере, ячейка G4 содержит следующую формулу:

Эта формула суммирует суммы в колонке D только тогда, когда значение в столбце С не пусто

Функция СУММЕСЛИ поддерживает все стандартные операторы Excel, в том числе не равно к, который вводится в <>.

При использовании оператора в критериях функции как СУММЕСЛИ, необходимо заключить его в двойные кавычки ( «»). При использовании только «<>» в качестве критерия, вы можете думать о значении как «не равно пустой», или «не пусто».

Альтернатива с СУММЕСЛИМН

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

С СУММЕСЛИМН диапазон сумма всегда стоит первым.

Функция СУММПРОИЗВ в VBA — помогите поправить

​R Dmitry​​ были переданы ссылки​ проверки разряда каждого​​ поставщиках, видах товаров​ продавцов, C1:C100 –​ просто выполнит деление​​ него. Например,​/​ правильно и не​(AVERAGE). Но что​В ряде случаев (когда​ например, применить операцию​​ ввода функции в​ массивов.​ и думает долго»)​ выбора только необходимых​ найти более шустрый​​: Cells(1, 3) =​ на 2 и​ сотрудника. Умножение на​ и суммарной стоимости.​​ диапазон ячеек с​

​ на большее значение,​​Задание 2​(прямой слеш), а​ зависеть от суммы,​ делать, если некоторые​​ нужно подсчитать или​

​ двойного отрицания (—),​​ ячейку вместо​Аргументы, которые являются массивами,​​buchlotnik​

​ значений. Заранее спасибо​​ способ расчёта.​ WorksheetFunction.SumProduct(Range(«a1:a3»), Range(«b1:b3»))​​ более диапазона ячеек,​ единицу выполняется для​​ Определить общую сумму​ числовыми значениями стоимости​ вновь приводя к​​посчитано 5 раз,​

​ далее записываем функцию​​ в которую складываются​ значения имеют больший​

​ сложить значения, удовлетворяющие​​ что позволит привести​ENTER​​ должны иметь одинаковые​: Виктор, а вот​​DV​Начал свои поиски​это пример​​ эти диапазоны должны​​ преобразования логических данных​ закупок хлеба от​ проданных товаров. Символы​​ правильному ответу. Или​ а​СУММ​​ веса.​ вес, чем другие?​​ определенным критериям) можно​ массив в числовую​

​нажать​​ размерности (в нашем​ так не быстрее​: H8:​ с WorksheetFunction.SumProduct(), предположив,​ПАХОМ​ содержать одинаковое количество​ к числам.​​ первого поставщика без​​ «—» — двойное​ же мы можем​

​Итоговый экзамен​​(SUM):​​Теперь, когда наша таблица​ Например, на многих​ заменить использование формул​ форму {1:1:1:0}.​​CTRL+SHIFT+ENTER​ случае это массивы​ будет?​=СУММПРОИЗВ((C$23:C$34=G8)*D$23:D$34;(C$8:C$19=G8)*D$8:D$19)+СУММПРОИЗВ((C$8:C$19=G8)*E$8:E$19;(C$23:C$34=G8)*E$23:E$34)​ что функция прописанная​: Доброй ночи, R​​ ячеек. При несоблюдении​В результате вычислений получим​ использования функции СУММЕСЛИМН.​ отрицание, используемое для​ сделать веса намного​​– 45 раз.​=СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(​ готова, мы добавляем​ курсах тесты имеют​ массива функцией СУММПРОИЗВ(),​Итак, задача подсчета значений​: =СУММ(A3:A6/B3:B6)​ по 4 элемента).​=-СУММПРОИЗВ(-(B2:B16=F2);СЧЁТЕСЛИ(Город;A2:A16);C2:C16)​Grand68​​ в VBA будет​

​ Dmitry!​​ указанных условий функция​​ следующее число не​Вид таблицы данных:​ прямого преобразования логических​ меньше, например, указать​ Вот почему​=SUMPRODUCT(B2:B9, C2:C9)/SUM(​​ формулу в ячейку​

​ больший вес, чем​​ например:​ больше 2 решается​Прелесть функции СУММПРОИЗВ() в​ В противном случае​vikttur​: Огромное спасибо -​ быстрее чем в​​Да, с листом​​ СУМППРОИЗВ вернет код​ выполнивших норму сотрудников:​Для расчета используем формулу:​ ИСТИНА и ЛОЖЬ​ такие значения как​Итоговый экзамен​Для функции​B10​ задания. Для таких​=СУММПРОИЗВ(—ЕПУСТО(D2:D23)) подсчет пустых ячеек​

​ следующим образом: =СУММПРОИЗВ(—(A3:A6>2))​​ том, что после​​ функция СУММПРОИЗВ() возвращает​: Вряд ли. Двойное​ то что нужно!!!​​ книге Excel. Но​ неувязочка вышла.​ ошибки #ЗНАЧ!.​​Функция имеет следующий синтаксис:​

planetaexcel.ru>

Сравнение в СУММПРОИЗВ

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

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

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

Формула СУММПРОИЗВ для средневзвешенного значения

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

Общая формула СУММПРОИЗВ для средневзвешенного значения выглядит следующим образом:

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

Думаю, на этом этапе у вас не возникнет трудностей с пониманием логики формулы. Находим сумму произведений процента выполнения на коэффициент важности и делим на сумму коэффициентов важности