|
Функция СУММПРОИЗВ - секретное оружие 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)*(что_суммировать))
Причем, никто не мешает Вам добавить третье, четвертое и т.д. условия, расширяя свой отчет. Например, если в исходную таблицу добавить столбец с номером склада, с которого происходила отгрузка товара, то можно отразить это в отчете, добавив третье условие в функцию СУММПРОИЗВ:
P.S.
Умные люди подсказали одну совсем не очевидную фишку. При использовании функции СУММПРОИЗВ вы можете давать ссылки на диапазоны не только из той же книги, где идет расчет, но и из других файлов. Например, если мы хотим подсчитать сумму всех ячеек, значение которых больших десяти из диапазона А1:А10 с листа Затраты из книги Бюджет.xls, то формула могла бы выглядеть примерно так:
=СУММПРОИЗВ('[Бюджет.xls]Затраты'!$A$1:$A$10;">10")
Проблема в том, что если файл Бюджет.xls в данный момент не открыт, то формула при пересчете листа выдает ошибку #ЗНАЧ. Но, если добавить в формулу двойное бинарное отрицание (два знака минус подряд) перед аргументами, и немного ее изменить, то она будет работать даже при закрытом файле Бюджет.xls. Формула должна иметь следующий вид:
=СУММПРОИЗВ(--('[Бюджет.xls]Затраты'!$A$1:$A$10>10));--('[Бюджет.xls]Затраты'!$A$1:$A$10))
Вот уж, как говорится, ни в жизнь бы сам до такого не додумался... :)
Дело в том, что в его постановке и выводах произведена подмена, аналогичная подмене в школьной шуточной задачке на сообразительность, в которой спрашивается:
- Cколько яблок на березе, если на одной ветке их 5, на другой ветке - 10 и так далее
При этом внимание учеников намеренно отвлекается от того основополагающего факта, что на березе яблоки не растут, в принципе.
В эксперименте Майкельсона ставится вопрос о движении эфира относительно покоящегося в лабораторной системе интерферометра. Однако, если мы ищем эфир, как базовую материю, из которой состоит всё вещество интерферометра, лаборатории, да и Земли в целом, то, естественно, эфир тоже будет неподвижен, так как земное вещество есть всего навсего определенным образом структурированный эфир, и никак не может двигаться относительно самого себя.
Удивительно, что этот цирковой трюк овладел на 120 лет умами физиков на полном серьезе, хотя его прототипы есть в сказках-небылицах всех народов всех времен, включая барона Мюнхаузена, вытащившего себя за волосы из болота, и призванных показать детям возможные жульничества и тем защитить их во взрослой жизни. Подробнее читайте в FAQ по эфирной физике.
|
|
 |
