Вопрос по excel-2003, excel-formula, excel, excel-2007 – Функция ROW () ведет себя по-разному внутри SUM () и SUMPRODUCT ()

7

Problem definition:

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

=SUM(INDIRECT("A"&ROW()))

а также

=SUMPRODUCT(INDIRECT("A"&ROW()))

Первая формула оценивает, вторая дает ошибку #VALUE. Это вызваноROW() функция ведет себя по-разному внутриSUMPRODUCT().

В первой формулеROW() возвращается1, Во второй формуле возвращается строка{1} (массив одной длины), хотя формула не была введена как формула CSE.

Почему это происходит?

Background

Мне нужно оценить формулу типа

=SUMPRODUCT(INDIRECT(*range formed by concatenation and using ROW()*)>1)

Это работает с ошибкой. В качестве обходного пути к этой проблеме, я теперь вычисляюROW() в другой камере (в том же ряду, очевидно) и объединить это в моемINDIRECT(), С другой стороны, я также попытался инкапсулировать его в функцию суммы, какSUM(ROW())и это тоже работает.

Я был бы признателен, если бы кто-то мог объяснить (или указать мне на ресурс, который может объяснить), почемуROW() возвращает массив внутриSUMPRODUCT() не входя в CSE.

Ваш Ответ

3   ответа
1

что ROW () ведет себя по-разному, он возвращает массив в обоих случаях. Я предполагаю, что SUM и SUMPRODUCT обрабатывают этот массив по-разному - не знаю почему.

Многие функции или их комбинации возвращают массивы - для этого вам не нужно сочетание клавиш CTRL + SHIFT + ENTER, во многих случаях требуется только CSE для обработки созданных массивов.

Я бы просто использовал INDEX вместо INDIRECT (который также приносит пользу вам, избегая изменчивой функции), т.е.

=SUMPRODUCT(INDEX(A:A,ROW()))

.... расширяя его до вашего диапазона, эта формула будет подсчитывать количество значений & gt; 1 в диапазоне в столбце A, где x определяет начальную строку, а y - конечную строку

=COUNTIF(INDEX(A:A,x):INDEX(A:A,y),">1")

х и у можно рассчитать по формулам

вы можете использовать SUMPRODUCT или COUNTIFS аналогичным образом, если есть дополнительные условия для добавления

Спасибо за ответ. Я использовалSUM() функционировать вокругROW() функция и обошла проблему. Кроме того, для моей конкретной проблемы мне пришлось использоватьINDIRECT() функция, потому что ссылки на ячейки рассчитывались еще где !. Похоже, вы поняли это правильно, когда говорите, что CSE просто меняет обработку массивов. У @lori_m потрясающий ответ на вопрос, почемуSUM() а такжеSUMPRODUCT() относиться к массивам по-разному. playercharlie
2

ROW() возвращает массив, используйтеINDEX чтобы получить 1-й элемент.

Ваш пример тогда становится:=SUMPRODUCT(INDIRECT("A"&INDEX(ROW(),1)))

Спасибо за ответ. Я использовалSUM() функция вместоINDEX() функция и обошла проблему. Однако я искал объяснение, почему я должен был это сделать. playercharlie
sumproduct не летуч. Это волатильные функции:msdn.microsoft.com/en-us/library/office/bb687891.aspx#Anchor_2
Это лучший ответ для меня. Использование INDEX () для массива, полученного с помощью ROW (), используемого внутри SUMPRODUCT (), прекрасно! Если волатильность связана с пересчетом, она меня не очень беспокоит, потому что компьютер теперь намного быстрее. В моих книгах используется много косвенных и смещенных данных, и я в порядке с задержкой пересчета до 1 секунды. Поэтому, если мне когда-либо понадобится использовать ROW () внутри SUMPRODUCT (), то INDEX () - обязательная вспомогательная функция! Большое спасибо SeanC
О, и спасибо за трюк SUM (), ответили OP. Это сокращает формулу, и я предпочитаю более короткую. Интересно, один из них быстрее? Возможно, SUM () быстрее, потому что он энергонезависимый?
6

которые я не видел задокументированными.

Похоже на тоINDIRECT("A"&ROW()) возвращает массив, состоящий из одного элемента, который является ссылкой на ячейку, а не значением в этой ячейке. Многие функции не могут правильно обрабатывать данные этого типа, но некоторые функции, такие как N и T, могут «разыменовывать». массив и вернуть базовое значение.

Возьмите этот случай, когда в массиве есть два элемента:

=SUM(N(INDIRECT("A"&ROW(1:2))))

Возвращает A1 + A2 при вводе массива, но возвращает A1 только при обычном вводе. Однако изменение ROW (1: 2) на {1; 2} в этой формуле возвращает правильный результат при обычном вводе. Эквивалентная формула SUMPRODUCT возвращает A1 + A2 независимо от того, введен массив или нет.

Это может быть связано с тем, как аргументы регистрируются в функции. В соответствии сhttp://msdn.microsoft.com/en-us/library/bb687900.aspx Существуют два метода регистрации аргументов функций для обработки типов данных Excel:

Введите R / U: & quot; Значения, массивы и ссылки на диапазон. & Quot;

Введите P / Q: & quot; Excel преобразует ссылки на одну ячейку в простые значения и ссылки на несколько ячеек в массивы при подготовке этих аргументов. & Quot;

Аргументы SUM, похоже, соответствуют типу R / U, а аргументы SUMPRODUCT ведут себя как тип P / Q. Ввод массива в приведенной выше формуле SUM заставляет ссылочный аргумент диапазона в ROW быть оцененным как массив, тогда как это происходит автоматически с SUMPRODUCT.

Update

После небольшого дополнительного исследования приведем еще одно доказательство, подтверждающее эту теорию. На основании ссылки в комментарии формула = SUM ((A1, A2)) дает те же значения, что и:

?executeexcel4macro("CALL(""Xlcall32"",""Excel4"",""2JRJR"",4,,1,(!R1C1,!R2C1))")

Регистрация последнего аргумента как типа P путем изменения2JRJR в2JRJP дает ошибку в этом случае, но допускает диапазоны одной области, такие как!R1C1:!R2C1, С другой стороны, изменение 4 (xlfsum) на 228 (xlfsumproduct) допускает только ссылки на одну область в любом случае, так как она называется так же, как SUMPRODUCT.

+1 хорошее исследование!
+1 Это именно то, что мне было нужно. «Разыменование»; способностьN() а такжеT() функции меня удивляют. На самом деле в формуле=SUMPRODUCT(N(INDIRECT("A"&ROW()))) ,N() функция на самом деле разрешает ошибку #VALUE (как видно в формуле оценки, Excel 2003). Это очень приятно знать. Также информация о типах аргументов R / U и P / Q кажется хорошим объяснением. ОбработкаxlTypeNil элементы для типов P / Q, кажется, совпадают с поведениемSUMPRODUCT(), Спасибо за блестящий ответ. Никогда бы не знал, для чего Google. Заслуживает +10 !! playercharlie
Рад, что это помогло - Лоран Лонгре первоначально выяснил это поведение и показал, как можно использовать функцию CALL со ссылкой на xlcall.h для функций листа:cpearson.com/excel/Call.htm, В VBA это все еще можно получить через:ExecuteExcel4Macro.

Похожие вопросы