利用VBA中的和ifs函数在excel中填充二维范围

Question

基于存储和地点的利润表中的数据。希望有一个矩阵，位置在一列中，存储在一行。对于每个位置和存储，它将返回一个值，即该位置中所有商店(可能在特定位置有多个商店)的利润之和。

这是excel函数：

=SUMIFS(Sheet2!$C$2:$C$6，Sheet2!$A$2:$A$6，Sheet1!$A5，Sheet2!$B$2:$B$6，Sheet1！B！4美元)

Answer 1

试一试，

with sheets("Sheet1")
    with .range("B5:Z99")
        .formula = "=SUMIFS(Sheet2!$C$2:$C$6, Sheet2!$A$2:$A$6, Sheet1!$A5, Sheet2!$B$2:$B$6, Sheet1!B$4)"
        .value = .value
    end with 
end with

本质上，将公式写入单元格块，然后将公式的结果还原为它们的值。

动态最后一行的编辑：

dim lr as long
lr = Sheets("Sheet2").cells(rows.count, 3).end(xlup).row

那么，公式分配将是：

.formula = "=SUMIFS(Sheet2!$C$2:$C$" & lr & ", Sheet2!$A$2:$A$" & lr & ", Sheet1!$A5, Sheet2!$B$2:$B$" & lr & ", Sheet1!B$4)"

编辑:命名范围：

如果使用命名范围来定义所检查数据的范围，则使用相同的匹配公式定义不同列的限制。

在上面的SUMIFS中，您可以为Sheet2 2的A、B和C列使用命名范围。让我们将命名范围称为ws2colA、ws2colB和ws2colC。我们知道我们正在对列C进行求和，所以使用C列中最后一个数字的行来定义每个列的范围。

ws2colA - Applies to:
=Sheet2!$A$2:INDEX(Sheet2!$A:$A, MATCH(1e99, Sheet2!$C:$C))

ws2colB - Applies to:
=Sheet2!$B$2:INDEX(Sheet2!$B:$B, MATCH(1e99, Sheet2!$C:$C))

ws2colC - Applies to:
=Sheet2!$C$2:INDEX(Sheet2!$C:$C, MATCH(1e99, Sheet2!$C:$C))

公式分配如下：

.formula = "=SUMIFS(ws2colC, ws2colA, Sheet1!$A5, ws2colB, Sheet1!B$4)"

使用相同的公式来定义每个命名范围列的范围将防止SUMIFS中的范围引用不匹配。