Непоследовательное возвращаемое значение SUMPRODUCT

Что, черт возьми, происходит здесь? Это ошибка?

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

=SUMPRODUCT(--(B4=IF(NOT($C$4:$C$11),$B$4:$B$11))) 

Предполагается вернуть 1. Почему он возвращает 2?

введите описание изображения здесь

Когда я вручную оцениваю массив внутри SUMPRODUCT с помощью F9 , я вижу, что там только один; когда я затем нажимаю Enter , он возвращает 1, что и ожидалось с самого начала.

введите описание изображения здесь

введите описание изображения здесь

Наилучшая часть: если я скопирую ту же самую оригинальную формулу в другие ячейки (не перетаскивается – она ​​имеет одинаковые ссылки на ячейки), тогда она возвращает 0 или #VALUE! ошибка. Почему результат изменится так?

введите описание изображения здесь

Тестовые данные для вашего удовольствия от копирования:

 Fruit Made of plastic apple FALSE banana FALSE plum FALSE pear TRUE apple TRUE banana FALSE plum TRUE pear TRUE 

на самом деле все в порядке. Вам просто нужно нажать ctrl + shift + enter, чтобы вычислить формулу. Когда вы нажимаете кнопку « ТОЛЬКО», он не принимает во внимание, что формула основана на массиве.

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

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

Та же история, когда вы копируете формулу в другую ячейку. Также вам нужно нажать ctrl + shift + enter, чтобы вычислить формулу массива.

У меня есть теория – и это не более того (но не вписывается в комментарий), – что основной причиной является короткозамкнутый характер IF.

Возьмите первое apple – оно связано с FALSE, поэтому подсчитайте 1. Есть ли еще один ЛОЖЬ в списке? Да, так есть еще одно apple в списке? Также да, так что добавьте 1, результат 2 .
Возьмите первый banana – как указано выше, результат 2 .
Возьмите первую plum – как указано выше, результат 2.
Возьмите первую pear – она ​​связана с TRUE, поэтому подсчитайте 0 – и остановите подсчет.
Возьмите второе apple – оно связано с TRUE, поэтому подсчитайте 0 – и остановите подсчет.
Возьмите второй banana – он связан с FALSE, поэтому подсчитайте 1. Есть ли еще ЛОЖЬ в списке? Да, так есть еще один banana в списке? Также да, так что добавьте 1, результат 2 .
Возьмите вторую plum – она ​​связана с TRUE, поэтому подсчитайте 0 – и остановите подсчет.
Возьмите вторую pear – она ​​связана с TRUE, поэтому подсчитайте 0 – и остановите подсчет.

Но это в сочетании с подразумеваемым перехватом – оно работает только для B4, когда в Row4.

Это ошибка?

Я думаю, что можно с уверенностью сказать, а не ошибка.

Почему он возвращает 2?

Возможно, это объяснение (я его не проверял!)

Что, черт возьми, происходит здесь?

Возможно, вы не можете получить подробные сведения без доступа к коду Microsoft?

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

 =COUNTIFS(B:B, B4, C:C, False) 

Это работает:

 =SUMPRODUCT(($B$4:$B$11=B4)*NOT($C$4:$C$11)) 

Его не нужно вводить в виде формулы массива.

  • Расчет PI () и 3.14159265358979 в excel
  • Excel Ссылка на другой лист из функции, определенной ячейкой
  • определить ячейку, держащую самую длинную строку с помощью excel
  • Объявление переменных в ячейках Excel
  • SUMIFS на основе динамического Sum_Range
  • Сравнение дубликатов Excel по нескольким столбцам с неорганизованными данными
  • Как найти последнее непустое ненулевое положительное значение в столбце / диапазоне?
  • Как написать сложную формулу в Excel?
  • Генерировать или заполнять данные ячейки на основе другого набора данных excel
  • Формат даты Excel - квартал и год
  • Не удалось восстановить эту формулу в Excel
  • Давайте будем гением компьютера.