Суммпроизв в excel

Как производится расчет, если налогоплательщику принадлежит только доля в недвижимости

Подобный расчет будет выполнять следующим образом:

1. Для начала происходит расчет общей площади квартиры, которая облагается налогом. Для этого из данной площади требуется вычесть налоговый вычет.
2. Следующий шаг – определить кадастровую стоимость данного объекта недвижимости, а точнее, стоимость одного квадратного метра. Для этого общую сумму требуется поделить на существующий метраж помещения.
3. Далее нужно определить налоговую базу – стоимость одно метра умножить на весь метраж, облагаемый налогами.
4. Следующий шаг – умножить свою долю на данный метраж.
5. Заключение – рассчитать свою сумму налога при помощи понижающего коэффициента.

Поиск в массиве при помощи ИНДЕКС ПОИСКПОЗ

Объединяем несколько условий.

Добавление условий к периоду

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

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

1. Активируйте первую ячейку из вспомогательного столбца с функцией AND и добавьте к ней еще одно условие, которое выглядит как B2=$F$1. B2 – первая ячейка с видом деятельности, а F1 – то самое условие, которое мы выбираем при расчетах. Его нужно закрепить, чтобы далее ячейка не съехала.

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

3. В функции =СУММЕСЛИ изменения произойдут автоматически, поскольку она берет данные из ИСТИНА и ЛОЖЬ. Теперь вы видите, что формула считает по-новому, с учетом введенного условия. Таких условий может быть практически неограниченное количество.

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

=AND(A2>=$D$1;A2<=$E$1;B2=$F$1)

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

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

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

=СУММПРОИЗВ((A2:A15>=$D$1)*(A2:A15<=$E$1)*(B2:B15=$F$1)*(C2:C15))

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

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

Пусть имеется 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 ), но и использовать другие функции и даже применить к массивам операции сравнения, т.е. использовать ее для сложения чисел, удовлетворяющих определенным условиям.

10 комментариев

А вот вопросик такой, второй вариант который вы описали повторяет формулу СУММЕСЛИМН, так вот не сталкивались ли вы какая будет работать быстрее? и где можно подробнее почитать про использование «- -» никогда раньше с этим не сталкивался?

Сергей, однозначно, формула СУММЕСЛИМН будет работать быстрее, так как встроенные формулы шустрее, чем пользовательские. Данный пример будет полезен читателям, которые используют старые версии Excel (до 2007), в которых данная функция не реализована. По поводу второго вопроса на англоязычных ресурсах об этом много написано, в любом поисковике введите «double dash excel», думаю, вы найдете ответ на свой вопрос.

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

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

Добрый день. Одно из преимуществ функции СУММПРОИЗ в отличии от СУММЕСЛИ — это работа с закрытым файлом, т.е. СУММПРОИЗ рассчитывает результат, используя данные из другой рабочей книги, даже если она закрыта. Все было хорошо, пока длина диапазона данных (список1; список2 …) не стала переменной. Решила стандартное написание диапазона заменить на название колонки таблицы, например, (- -(Табл1=»Агент Смит»)….), где Табл1 — таблица в другом файле. Пока этот другой файл открыт, все работает. Но при закрытом файле любой Update приводит СУММПРОИЗ к ошибке (Ref). Или я что-то делаю неправильно?

Добрый день, Лилия К сожалению, это слабое место Excel. Он не способен вытягивать динамические диапазоны с закрытых книг. Есть предложение производить все вычисления на стороне закрытой книги, а в книге, где это будет использоваться, помещать только ссылку на ячейку закрытой книги

Спасибо Вам огромное. Побольше вам времени и позитивных эмоций и дальше вести свой сайт!

Допустим у нас в компании сидят «грамотеи» и пишут слова с ошибками: Агент Смит, Агент Смид, Запад, Запат и т.д. Вопрос: Как используются подстановочные знаки «*», «?» в функции «СУММПРОИЗВ»? Когда пишу =СУММПРОИЗВ(- -(A2:A21=»Агент*»);- -(B2:B21=»Запа?»);C2:C21), результат=0 Заранее спасибо!

Доброго времени суток. При помощи функции суммпроизводства пытаюсь посчитать, столбце с датами. Пишу в условии следующее. =Сегодня()-4 Ошубку не выдает но не считает результат. А если пишу

Здравствуйте! Подскажите, как задать условие — если значение в ячейке $H$2=1, то ссылаться на столбец MATYEAR и возвращать 1, если MAT 17, если $H$2 равно не один, тогда ссылаться на соседний столбец. Писала условие через ЕСЛИ, но почему-то не работает.

Формула MAX IF с несколькими критериями

Функция СУММПРОИЗВ — как использовать формулу СУММПРОИЗВ в 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 с примером использования в формуле

Условная функция ЕСЛИ()

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

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

Пусть имеется 2 диапазона чисел A3:A6 и B3:B6, содержащие соответственно 2 массива чисел: и . Записав формулу =СУММПРОИЗВ(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 ), но и использовать другие функции и даже применить к массивам операции сравнения, т.е. использовать ее для сложения чисел, удовлетворяющих определенным условиям.

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

Excel СУММПРОИЗВ — примеры расширенных формул

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

Вот как мы это уже делали в более ранних публикациях на нашем сайте:

•  — как выполнять поиск по нескольким критериям и возвращать совпадающие числа. 
• – извлекаем из столбца слева от столбца поиска числовые значения и суммируем их.
• Как подсчитать символы в Excel — формулы для подсчета всего или только определенных символов в диапазоне.
• СУММПРОИЗВ для подсчета слов в Excel — формулы для получения общего количества слов в определенном диапазоне или подсчета только определенных слов.
•  — узнайте, сколько разных значений появляется в диапазоне (уникальных плюс первые повторяющиеся вхождения).
• Подсчет дубликатов и уникальных значений — формула СУММПРОИЗВ / СЧЁТЕСЛИ для подсчета дубликатов и уникальных значений в столбце или между двумя столбцами.
•  — СУММПРОИЗВ в сочетании с 7 другими функциями извлекает все числа из строки число-текст-число.

Как не забивать гвозди микроскопом с функцией СУММПРОИЗВ

Стандартное использование

Базовый синтаксис нашей функции прост:

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

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

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

= B2*C2 + B3*C3 + B4*C4 + B5*C5

Технически, перемножаемых массивов (диапазонов) может быть не два, а три или больше (до 255). Главное, чтобы они были одного размера. Удобно, но ничего особенно. Однако, использовать СУММПРОИЗВ только так — забивать гвозди микроскопом, ибо, на самом деле, она умеет гораздо больше.

Работа с массивами без Ctrl+Shift+Enter

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

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

Подсчет количества выполненных условий

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

Умножение на 1, в данном случае, нужно, чтобы преобразовать результаты сравнения плана и факта — логическую ИСТИНУ и ЛОЖЬ в 1 и 0, соответственно.

Проверка нескольких условий

Если нужно проверять больше одного условия, то формулу из предыдущего примера нужно будет дополнить еще одним (или несколькими) множителями. И если нужно подсчитывать не количество, а сумму, то умножать можно не на 1, а на диапазон с суммируемыми данными:

Фактически, получается что-то весьма похожее на математическую функцию выборочного подсчета СУММЕСЛИМН (SUMIFS) , которая также умеет проверять несколько условий (до 127) и суммировать по ним значения из заданного диапазона.

Логические связки И и ИЛИ (AND и OR)

Если нужно связывать условия не логическим «И», как в примере выше (Факт>=План) И (Регион=Восток) , а логическим ИЛИ, то конструкция немного изменится — знак умножения заменяется на плюс:

Подсчет по данным из закрытого(!) файла

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

. то вторая формула с классической функцией СЧЁТЕСЛИМН (COUNTIFS) будет работать только до тех пор, пока исходный файл открыт. Если его закрыть, то появляется ошибка #ЗНАЧ! Наша же функция СУММПРОИЗВ (SUMPRODUCT) спокойно считает по данным даже из неоткрытой книги!

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

Подобрать значения для нужного результата

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

Для этого на вкладке «Данные» надо выбрать «Анализ „Что если“», с помощью функции «Подбор параметра» задать целевое значение и выбрать ячейку, которую нужно изменить для получения желаемой цифры.

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

«Подбор параметра» подойдет и для составления бизнес-плана. Введите желаемую прибыль и посчитайте, сколько единиц товара и с какой накруткой нужно продавать

Как использовать функцию ЕСЛИ с И, ИЛИ и НЕ в Excel?

Использование функции СУММЕСЛИМН

Исходные данные и подготовительные работы

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

1. В одном столбце у нас есть последовательные дни, которые и будут являться периодом в будущем. 

2. Следующий столбец показывает сферу деятельности, которой пользователь занимался в каждый из этих дней.

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

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

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

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

1. Для начала разберемся с начальной датой периода. Выделим эту самую ячейку и развернем меню «Данные».

2. Из него выберите пункт «Настроить проверку данных».

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

4. Теперь рядом с числом появится кнопка со стрелкой вниз.

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

6. Абсолютно то же самое проверните и с ячейкой конечной даты, чтобы точно так же переключаться при необходимости.

7. С дополнительными условиями все примерно так же. Выделите пустую ячейку, где хотите сформировать список, запомните номер столбца и переходите в меню «Данные».

8. Снова вызовите функцию «Настроить проверку данных» и в новом окне задайте подходящий диапазон точно так же, как делали это ранее.

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

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

Комьюнити теперь в Телеграм

Подпишитесь и будьте в курсе последних IT-новостей

Подписаться

Как пересчитать, суммировать или найти среднее по нескольким условиям

Суммирование в Excel, используя функции СУММ и СУММЕСЛИ

​ перерыва (всего 8,5​ число, хотя должно​ при вставке или​ и получить абсолютно​2250​Функция​ следующий пример:​ все требуемые параметры.​ не был перегружен​​Есть таблица, в заголовке​​Не по теме:​​ нужен столбец, C1​​(Факт>=План) И (Регион=Восток)​ заменить несколько столбцов​Если одного условия Вам​ попытаться сложить оператором​СУММЕСЛИ​

Функция СУММ – простое суммирование ячеек в Excel

​ часов).​​ отображать десятичное.​​ удалении строк в​ точные результаты, однако​=ПРОИЗВЕД(A2:A4; 2)​ПРОИЗВЕД​Заполним ячейки в таблице​В случае, если название​ длинными формулами и​ идут дни месяца,​быстро каждому по спасибу​ = A1*B1 -​

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

​Если вам нужно просто​​Проверьте, выбрано ли отображение​​ диапазоне, на который​ этот метод ненадежен​Перемножает числа из ячеек​перемножает все числа,​ Excel как показано​ функции заведомо известно,​

​ вычислениями. Ознакомиться с​​ ниже идут данные​​ и 500 р.​ и перетянуть на​ИЛИ​ ручного труда. Но​ Вы всегда можете​ вернет ошибку:​​ их функционала достаточно,​​ добавить часы и​ десятичных чисел в​ указывает ссылка по​ по ряду причин.​ A2–A4, а затем​

​ переданные как аргументы,​​ на рисунке, приведенном​​ и пользователю не​ полным перечнем встроенных​ закупок за день​

​ :D​ все строки.​, то конструкция немного​​ у формул массива​​ воспользоваться функцией​Функция​ чтобы решать практически​ минуты, вы можете​ формате ячейки. Выберите​ тем же причинам.​

​Опечатки​​ умножает полученный результат​​ и возвращает произведение.​ ниже.​ нужны дополнительные подсказки​ функций в Excel​ в единицах и​

​шутки в сторону​Анатолий головатенко​ изменится -​​ есть и минусы.​​СУММЕСЛИМН​СУММ​ все возможные вопросы​

​ просто вычислить сумму,​ соответствующую ячейку или​ Намного лучше использовать​. Допустим, вы пытаетесь​

​ на 2.​​ Например, если в​​В строке формул запишем​ по ее использованию,​ можно, используя справку​ цене за единицу.​ — какой фашист​: В первой строке​знак умножения заменяется на​ Главные — это​, которая позволяет осуществлять​

​достаточно универсальна и​ суммирования в Excel.​ не умножая ее​ соответствующий диапазон и​

СУММЕСЛИ — условная сумма в Excel

​ отдельные диапазоны, такие​​ ввести много больших​​4500​ ячейках A1 и​ «СУММПРОИЗВ(A2:B5;C2:D5)». Для того,​ можно просто поставить​ Microsoft Office.​​ Надо посчитать общую​​ вам цифры такие​ третьего столбика поставь​

​ плюс​ относительная сложность понимания,​ условное суммирование в​ позволяет использовать в​Функция​ на 24. Во​

​ нажмите клавиши​ как:​ значений такого вида:​=A2*A3*A4​ A2 содержатся числа,​

​ чтобы начать вводить​ в строке формул​

​Одна из множества полезных​ сумму за месяц,​ дал или ты​ курсор на ячейку,​:​ замедление пересчета книги​

​ Excel на основе​ качестве своих аргументов​СУММ​ втором примере используется​Ctrl + 1​=СУММ(A1:A3;B1:B3)​=14598,93+65437,90+78496,23​Перемножает числа из ячеек​ формула​ в ячейку какую-либо​

​ знак равно (=)​​ функций, которые помогают​​ т. е. сумму​ сам их придумал​ затем поставь знак​Кроме всего вышеперечисленного, у​ и необходимость вводить​ нескольких критериев.​ не только ссылки​вычисляет сумму всех​

​ формула​, чтобы открыть диалоговое​Такая формула будет обновляться​Попробуйте проверить правильность записей.​ A2–A4 с помощью​

​=ПРОИЗВЕД(A1;A2)​​ функцию, следует поставить​​ и ввести название​

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

​=СУММ(A6:C6)​ окно​ при добавлении и​ Намного проще установить​ математических операторов, а​​возвращает произведение этих​​ в ней знак​​ встроенной функции. При​​ массивами данных и​ день единиц и​смотри файл​ адрес ячейки «Количество»​есть еще одно​Ctrl+Shift+Enter​ из основных действий,​ диапазоны, но и​ является наиболее часто​, так как здесь​Формат ячеек​ удалении строк.​ следующие значения в​ не функции​

​ чисел. Эту операцию​

office-guru.ru>

Формула Excel МАКС ЕСЛИ

До недавнего времени в Microsoft Excel не было встроенной функции МАКС. ЕСЛИ для получения максимального значения в зависимости от условий. С введением MAXIFS в Excel 2019 мы можем легко выполнять условное максимальное значение.

В Excel 2016 и более ранних версиях вам все равно придется создавать собственную формулу массива, комбинируя функцию MAX с оператором IF:

{=МАКС(ЕСЛИ(критерии_диапазонзнак равнокритерии, максимальный_диапазон))}

Чтобы увидеть, как эта общая формула MAX IF работает с реальными данными, рассмотрим следующий пример. Предположим, у вас есть таблица с результатами прыжков в длину нескольких учеников. В таблицу включены данные по трем раундам, и вы ищете лучший результат конкретного спортсмена, скажем Якова. С именами учащихся в A2:A10 и расстояниями в C2:C10 формула принимает следующий вид:

=МАКС(ЕСЛИ(A2:A10=»Иаков», C2:C10))

Помните, что формулу массива всегда нужно вводить, одновременно нажимая клавиши Ctrl + Shift + Enter. В результате он автоматически обрамляется фигурными скобками, как показано на скриншоте ниже (набор фигурных скобок вручную не работает!).

В реальных рабочих листах критерий удобнее вводить в какую-то ячейку, чтобы можно было легко изменить условие, не меняя формулу. Итак, набираем нужное имя в F1 и получаем следующий результат:

=МАКС(ЕСЛИ(A2:A10=F1, C2:C10))

Как работает эта формула

В логическом тесте функции ЕСЛИ мы сравниваем список имен (A2:A10) с целевым именем (F1). Результатом этой операции является массив значений ИСТИНА и ЛОЖЬ, где значения ИСТИНА представляют имена, совпадающие с целевым именем (Джейкоб):

{ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ИСТИНА; ИСТИНА; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ}

Для значение_ если_истина аргумент, мы предоставляем результаты длинного перехода (C2:C10), поэтому, если логический тест оценивается как TRUE, возвращается соответствующее число из столбца C. значение_ если_ложь аргумент опущен, то есть будет иметь значение FALSE, если условие не выполняется:

{ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;5.48;5.42;5.57;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ}

Этот массив передается функции MAX, которая возвращает максимальное число, игнорируя значения FALSE.

Кончик. Чтобы просмотреть внутренние массивы, описанные выше, выберите соответствующую часть формулы на листе и нажмите клавишу F9. Чтобы выйти из режима оценки формулы, нажмите клавишу Esc.

Как сравнить две ячейки в Excel.

Функция СУММПРОИЗВ — как использовать формулу СУММПРОИЗВ в 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 – просто возвращает массив с продажами

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

Послесловие

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

Суммирование значений по заданным аргументам при помощи функций ВПР и СУММ

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

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

Таблица данных — ежемесячные продажи:

Эта задача решается через использование в функции ВПР() массива в третьем аргументе (номер_столбца).

=СУММ (ВПР (искомое значение, интервальный просмотр, {2,3,4}, ЛОЖЬ)).

Исходя из примера выше, в массиве {2, 3, 4} рассматриваемого аргумента для суммирования чисел из столбцов 2,3 и 4 используются функции ВПР().

Для получения искомого числа из столбцов от B до M, сочетание функций ВПР() и СУММ() выглядит так:

=СУММ (ВПР (B2, ‘Monthly sales’! $A$2:$M$9, {2,3,4,5,6,7,8,9,10,11,12,13}, ЛОЖЬ))

{=СУММ (ВПР(B2, ‘Monthly sales’!$A$2:$M$9, {2,3,4,5,6,7,8,9,10,11,12,13}, ЛОЖЬ))}

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

СОВЕТ. Искомое значение может указываться при помощи ссылки на столбец , как показано на рисунке 2. Поскольку рассматриваемая в примере таблица создана посредством инструмента «Вставка” → «Таблица», она стала полнофункциональной. В такой таблице внесенные изменения по умолчанию дублируются на выбранный столбец в вышестоящую ячейку.Исходя из указанной информации, можно сделать вывод о том, что совмещать функции ВПР() и СУММ() в работе достаточно просто. Хотя предложенный способ – не единственно верный путь, в частности, при работе с таблицами с большим количеством данных. В связи с тем, что каждый аргумент делает запрос к функции ВПР(), применение большого количества формул массива отрицательно сказывается на скорости работы книги Excel. Отсюда вывод: большое количество формул в книге и аргументов в массиве может существенно замедлить работу Excel.

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

Использование функции “ЕСЛИ” на примере

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

У нас есть таблица с наименованиями спортивной обуви. Представим, что скоро у нас будет распродажа, и всей женской обуви нужно проставить скидку 25%. В одном из столбцов в таблице как раз прописан пол для каждого наименования.

Наша задача — в столбце “Скидка” вывести значение «25%» для всех строк с наименованиями для женского пола. И соответственно, значение «0», если в столбце «Пол» стоит значение «мужской»

Заполнение данных вручную займет немало времени, и есть высокая доля вероятности где-то ошибиться, особенно, если список длинный. Гораздо проще в этом случае будет автоматизировать процесс при помощи оператора «ЕСЛИ».

Для выполнения этой задачи потребуется составить следующую формулу ниже:

=ЕСЛИ(B2=”женский”;25%;0)

• Логическое выражение: B2=”женский”
• Значение в случае, условие выполняется (истина) – 25%
• Значение, если условие не выполняется (ложь) – 0.

Данную формулу пишем в самой верхней ячейке столбца “Скидка” и нажимаем Enter. Не забываем ставить знак равно (=) перед формулой.

После этого для данной ячейки будет отображен результат согласно нашему логическому условию (на забываем настроить формат ячеек – процентный). Если проверка выявит, что пол «женский» – будет выведено значение 25%. В противном случае значение ячейки будет равно 0. Собственно говоря, что нам и требовалось.

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

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