☰

计算二维数组中除零之外的不同列表的数量

tag-icon • tag-icon microsoft-excel microsoft-excel-2016
计算二维数组中除零之外的不同列表的数量

在 Excel 2019 中，我在一个单元格中有一个公式，当在公式栏中使用 F9 进行计算时，将返回以下二维数组（矩阵）：

{0,0; 0,0; 0,0; 0,0; 0,0; 0,0; 44846,1; 44846,1; 44846,1}

我想将这个结果嵌套在另一个函数中，该函数将计算不同列表（值对或行）的数量，而不计算零的数量。不同的值表示唯一值+重复值的首次出现。

因此，对于前一个二维数组，公式应该返回 1，因为只有一个唯一对（44846,1），不计算零。

对于以下二维数组，公式应返回 2，因为只有两个唯一对（ 和44843,1）44844,1，不计算零：

{44843,1; 44844,1; 0,0; 0,0; 0,0; 0,0; 0,0; 0,0; 0,0}

对于以下二维数组，公式应返回 4，因为只有四个唯一对（ 、44843,1、44843,2和44845,3）44845,4，不计算零：

{0,0; 0,0; 44843,1; 44843,2; 44845,3; 44845,4; 0,0; 0,0; 0,0}

我希望这样的公式不需要 Excel 365、VBA，并且最好不是数组公式（IE需要Ctrl++ Shift）Enter。

如果您需要更多背景信息，请随时询问。

答案1

不能确定这是否需要 CSE，但它应该适用于所有 Excel 版本：

=SUM(
    IF(FREQUENCY(
        IF(MMULT(N(ζ<>0),{1;1}),
            MMULT(
                N(INDEX(ζ,,1)&"|"&INDEX(ζ,,2)=TRANSPOSE(INDEX(ζ,,1)&"|"&INDEX(ζ,,2))),
                ROW(INDIRECT("1:"&ROWS(ζ)))
            )
        ),
        ROW(INDIRECT("1:"&ROWS(ζ)))),1
    )
)

将 ζ 替换为您的数组。

答案2

你可以尝试这个：

如果在行中：

=SUM(--(FREQUENCY(IF(A1:C3<>0,MMULT(A1:C3,{1;10^4;10^8})),IF(A1:C3<>0,MMULT(A1:C3,{1;10^4;10^8})))>0))

如果在列中：

=SUM(--(FREQUENCY(IF(A1:A10<>0, MMULT((B1:C10<>"")*B1:C10,{1;10^4;10^8})), IF(A1:A10<>0, MMULT((B1:C10<>"")*B1:C10,{1;10^4;10^8})))>0))
  • 根据需要调整单元格引用。

相关内容