Превращение логических значений ИСТИНА/ЛОЖЬ в числа с помощью операции минус минус

Рубрика: 7. Полезняшки Excel

Это глава из книги Билла Джелена Гуру Excel расширяют горизонты: делайте невозможное с Microsoft Excel.

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

Рис. 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

Рис. 2. Убеждаемся, что не работает конвертация значения ИСТИНА в 1

На рис. 3 первое слагаемое функции СУММПРОИЗВ было рассчитано в окне Вычисление формулы. Вы видите массив {ИСТИНА:ИСТИНА: … }. Если вы продолжите пошаговые вычисления, то увидите, что произведение трех массивов дает 0, даже несмотря на то, что отдельные слагаемые имеют вид ИСТИНА*ИСТИНА*Число.

Рис. 3. Окно Вычисление формулы

Рис. 3. Окно Вычисление формулы

Решение: вам нужно как-то преобразовать логические значения ИСТИНА/ЛОЖЬ в числа 1/0. Гуру Excel используют для этого операцию минус минус: —(С4:С14>20000). А вся формула принимает вид: =СУММПРОИЗВ(—(C4:C14>20000);—(D4:D14>0,5);C4:C14)*0,02. Теперь она работает (рис. 4).

Рис. 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. Перемножение двух логических массивов внутри первого аргумента функции СУММПРОИЗВ исправляет ситуацию

Рис. 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.

 

Комментарии: (1)

Добрый день!
Можете все фишки по екселю скинуть в один тейкстовый файл?
пожалуйста!


Прокомментировать

0011
0022