在数组上使用OR逻辑作为Sumproduct中的参数 [英] Using OR logic on an array as argument in Sumproduct

问题描述

我有一个相当大的数据集，需要将多个条目合并为一个值.我的数据集包含有关两个数据集的组合的数据，每个数据集都使用自己的ID和密钥.

I'm having a fairly large dataset where I need to combine multiple entries into a single value. My dataset contains data on the combination of two datasets, each using their own ID's and keys.

我想到使用这样的Sumproduct()函数:

I thought of using a Sumproduct() function like this:

=SUMPRODUCT(--('Raw data'!C:C=Landgebruik!A2);--('Raw data'!O:O={20;21;22;23;40});'Raw data'!S:S)

Landgebruik!A2拥有第一个数据集的ID，我需要将第二个数据集聚合到该ID.

With Landgebruik!A2 holding an ID for the first dataset, which I need to aggregate the second dataset to.

'Raw data'!O:O包含第二个数据集中的ID.在上述情况下，当第二个ID的值是以下任何一个值时，我需要求和('Raw data'!S:S中的面积):{20;21;22;23;40}. (或逻辑)该列仅包含整数值.

'Raw data'!O:O contains the ID's from the second dataset. In the case above I need to sum the area (in 'Raw data'!S:S) when the value of the second ID is any of these values: {20;21;22;23;40}. (OR logic) The column only contains integer values.

还有其他解决方法，然后对数组中的所有值复制--('Raw data'!O:O=20)吗?

Is there any other way of fixing this then duplicating --('Raw data'!O:O=20) for all values in the array?

我现在使用的变通方法是:=SUMPRODUCT(--('Raw data'!C:C=Landgebruik!A2);--('Raw data'!O:O=20)+('Raw data'!O:O=20)+('Raw data'!O:O=21)+('Raw data'!O:O=22)+('Raw data'!O:O=23)+('Raw data'!O:O=40);'Raw data'!S:S).但是我觉得应该有一种更优雅的方法.

I went with the work-around for now, which was: =SUMPRODUCT(--('Raw data'!C:C=Landgebruik!A2);--('Raw data'!O:O=20)+('Raw data'!O:O=20)+('Raw data'!O:O=21)+('Raw data'!O:O=22)+('Raw data'!O:O=23)+('Raw data'!O:O=40);'Raw data'!S:S). But I feel that there should be a more elegant way of doing this.

推荐答案

您可以对当前公式进行一些小的更改；将;更改为*(在特定情况下也不需要--):

You could make a small change to your current formula; change the ; to * (-- are also unneeded in that particular case):

=SUMPRODUCT(('Raw data'!C:C=Landgebruik!A2)*('Raw data'!O:O={20;21;22;23;40})*'Raw data'!S:S)

那应该可行.

当您将单独的参数提供给SUMPRODUCT时，每个参数的大小必须相同.但是，当您像这样乘以它们时，它将强制求值，并且数组会扩展.

When you feed separate parameters to SUMPRODUCT, each parameter has to be of the same size. But when you multiply them like this, it forces evaluation and the arrays expand.

例如，如果采用两个数组5x1和1x5，则会得到一个5x5的结果数组:

For example, if you take two arrays, 5x1 and 1x5, you get a 5x5 resulting array:

这篇关于在数组上使用OR逻辑作为Sumproduct中的参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！