Это глава из книги Билла Джелена Гуру Excel расширяют горизонты: делайте невозможное с Microsoft Excel.
Задача: в то время как функция ЕСЛИ и большинство подобных функцией легко конвертируют логические значения ИСТИНА и ЛОЖЬ в 1 и 0, СУММПРОИЗВ не делает этого (рис. 1).
Рис. 1. Странно, но внутри функции СУММПРОИЗВ Булевы значения не конвертируются в числа
Скачать заметку в формате Word или pdf, примеры в формате Excel
Здесь функция СУММПРОИЗВ используется для расчета совокупного 2%-ного бонуса за выручку, превышающую 20 000 при рентабельности выше 50%. Однако формула в ячейке С16 =СУММПРОИЗВ((C4:C14>20000);(D4:D14>0,5);C4:C14)*0,02 не работает.
Для проверки постройте простой массив (рис. 2) и убедитесь, что формула =СУММПРОИЗВ(A1:A2;B1:B2;C1:C2) возвращает 0. Причем, дело не в массиве. Даже формула =СУММПРОИЗВ(ИСТИНА;ИСТИНА;21000) не работает и возвращает ошибку #ЗНАЧ!
Рис. 2. Убеждаемся, что не работает конвертация значения ИСТИНА в 1
На рис. 3 первое слагаемое функции СУММПРОИЗВ было рассчитано в окне Вычисление формулы. Вы видите массив {ИСТИНА:ИСТИНА: … }. Если вы продолжите пошаговые вычисления, то увидите, что произведение трех массивов дает 0, даже несмотря на то, что отдельные слагаемые имеют вид ИСТИНА*ИСТИНА*Число.
Рис. 3. Окно Вычисление формулы
Решение: вам нужно как-то преобразовать логические значения ИСТИНА/ЛОЖЬ в числа 1/0. Гуру Excel используют для этого операцию минус минус: —(С4:С14>20000). А вся формула принимает вид: =СУММПРОИЗВ(—(C4:C14>20000);—(D4:D14>0,5);C4:C14)*0,02. Теперь она работает (рис. 4).
Рис. 4. Используя операцию минус минус, вы конвертируете логические значения ИСТИНА/ЛОЖЬ в числа 1/0, после чего формула выполняет свою работу
Альтернативные стратегии: все, приведенные ниже функции и операции также преобразуют массив логических значений ИСТИНА/ЛОЖЬ в массив чисел 1/0:
Ч(С4:С14>20000)
1*(С4:С14>20000)
(С4:С14>20000)+0
В последнем случае работающая формула принимает вид: =СУММПРОИЗВ((C4:C14>20000)+0;(D4:D14>0,5)+0;C4:C14)*0,02. Подробнее о функции Ч см. Excel. Добавление примечания в формулу с использованием функции Ч
Еще одна альтернатива – собрать все критерии в одном аргументе функции СУММПРОИЗВ. Для этого перемножьте их. Формула для расчета бонуса примет вид: =СУММПРОИЗВ((C4:C14>20000)*(D4:D14>0,5);C4:C14)*0,02 (рис. 5). Обратите внимание, что Массив 1, равный произведению двух логических массив, справился с задачей, и вернул значения 1 и 0 (на рисунке подчеркнуто красной чертой).
Рис. 5. Перемножение двух логических массивов внутри первого аргумента функции СУММПРОИЗВ исправляет ситуацию
Этот синтаксис позволяет вам комбинировать логические И и ИЛИ. Например, задать выплату бонуса, если выручка более 20 000 или рентабельность более 50%, можно с помощью формулы =СУММПРОИЗВ((C4:C14>20000)+(D4:D14>0,5);C4:C14)*0,02.
Резюме: использовать массивы с логическими значениями можно и внутри СУММПРОИЗВ. Чтобы справиться с проблемами используйте операцию минус минус, или поместите все критерии в один аргумент, используя умножение (*) для Булевого критерия И и сложение (+) – для ИЛИ.
Источники: http://www.mrexcel.com/forum/excel-questions/221125-up-all-night-better-not-easy-answer.html, http://www.mrexcel.com/forum/excel-questions/128907-explanation-dashes.html.
Добрый день!
Можете все фишки по екселю скинуть в один тейкстовый файл?
пожалуйста!