☰

请参阅下文以了解重写情况。

tag-icon • tag-icon microsoft-excel worksheet-function duplicate array
请参阅下文以了解重写情况。

请参阅下文以了解重写情况。

请参考以下数据回答问题。最顶部的 ABC 是列，最左边的数字 1 到 15 是行。其余的是单元格。

           A       B        C
      1    1       A       100
      2    1       B       200
      3    1       C       300
      4    1       D       400
      5    1       E       500
      6    2       Z       100
      7    2       B       200
      8    2       C       300
      9    2       D       400
     10    2       Y       500
     11    3       K       100
     12    3       B       200
     13    3       C       300
     14    3       D       400
     15    3       J       500

我想使用 A 列和 B 列作为标准对 C 列上的 #s 求和：仅当 A 列为 1 且 A 列 = 1 的 B 列分量（A、B、C、D、E）也位于 A 列 = 2 和 A 列 = 3 上时，才对 C 列数字求和（以粗体突出显示；B、C、D）。

例如：

A 列 = 1 的期望输出应为 900 (B+C+D)，因为 A 列 = 2 和 A 列 = 3 也仅包含 B、C、D

列 A = 1 的 A（单元格 B1）、E（单元格 B5）被排除，因为它们与列 A = 2 和列 A = 3 不重复”

我考虑使用数组公式（CSE）和 SUMPRODUCT 来创建一种 COUNTIF 规则，但却迷失了形成正确逻辑的方法。

建议重写：

请参阅以下数据以了解问题。最顶部的A、B、C和Desired Result只是列名，最左边的数字 1 到 24 是行号。其余的是包含数据的单元格。数据实际上没有格式化（粗体和斜体）；下面的格式只是为了突出显示文本中讨论的单元格。
注意：列中的值 B对于其首字母是唯一的；没有Aardvark、Butterfly或Caterpillar条目。

       ABC          德期望结果
 1 17 羚羊 101 502
 2 17    獾     201
 3 17    美洲狮     301
 4 17    澳洲野狗      401
 5 17 大象 501
 6 42 斑马 102 504
 7 42    獾     202
 8 42    美洲狮     302
 9 42    澳洲野狗      402
10 42 牦牛 502
11 83 考拉 103 506
12 83    獾     203
13 83    美洲狮     303
14 83    狐狸        403
15 83 捷豹 503
16 83 骆驼 603
17 83 驼鹿 703
18 83 山猫 803
19 83 豹 903
20 95 老虎 104 708
21 95    澳洲野狗      204
22 95    美洲狮     304
23 95    獾     404
24 95 黄鼠狼 504

这只是一个更大的电子表格的开始。 Column A包含非唯一值（数字）列表，工作表按 Column 排序 A（或至少分组），因此所有17s 都在连续的行中，所有42s 都在一起，等等。在实际文件中， Column 中的不同值远不止四个 A。

Column 中不同值的组A是这个问题的关键。一种看待这个问题的方式是，Column 中的一些值B存在于每一个列值组 A。它们在上面的数据中以（非斜体）粗体突出显示：（ Badger在单元格B2、B7、B12和 中B23）和 Cougar（在单元格B3、B8、B13和 中B22）。 

现在，对于每个唯一的列 值，我想要位于同一行且 A列值以粗体显示的值的总和 ；即在每个组中出现的值。CBB

例如：

第一组（其中列A= 17）的期望输出为 502（显示在单元格中D1），因为第 2 行和第 3 行是“第 17 组”中包含Badger和 的行Cougar，并且C2+C3为 502。同样，D6= C7+ C8、D11= C12+C13和D20= C23+ C22。

如果将单元格 从B14改为，则它将与、和相同 （目前均以斜体显示）。因此，它们将变为粗体，因此、、和将进入总和，从而得到列 值为 903、906、909 和 912。FoxDingoB4B9B21C4C9C14C21D

我想到使用数组公式（CSE）并SUMPRODUCT创建一种COUNTIF规则，但却迷失了自己以形成正确的逻辑。

答案1

假设 B 列值仅需要出现在另一组中，则以下内容将起作用：

这是作为数组公式 (CSE) 的最终单行公式。将其放在任何单元格中，并不要忘记按 Ctrl + Shift + Enter 对其进行数组化：

=SUM(IF(IFERROR(MATCH(IF(A1:A15=1,B1:B15,"X~X"),IF(A1:A15<>1,B1:B15,""),0),0)>0,C1:C15))

让我来分解一下：

[1] IF(A1:A15=1,B1:B15,"X~X") 仅返回 A = 1 行的 B 值数组，否则返回垃圾（将解释）。

[2] IF(A1:A15<>1,B1:B15,"") 仅返回 A <> 1 行的 B 值数组，否则返回空白。

[3] 这些位于 MATCH([1], [2], FALSE) 内。对于 [1] 行中的每一行，此操作都会在 [2] 中查找该值，如果 [1] 返回空白而不是垃圾，那么我们最终也会匹配空白，而我们不希望出现这种情况。

[4] 然后将其嵌套在 IFERROR() 内部以清除错误。

[5] 在 IF() 内部，返回 [4] 中没有错误的 C 值数组。

[6] 然后 SUM 将所有这些加在一起，就完成了！

答案2

我自己还没能想出一个一行公式。不过，我想出了一个两步流程，可能会激发出一行公式，所以我会发布它。

  • 填充单元格 D2 及以下 =AND(A1=1,AND(COUNTIFS($A$1:$A$15,1,$B$1:$B$15,B1)>=1,COUNTIFS($A$1:$A$15,2,$B$1:$B$15,B1)>=1,COUNTIFS($A$1:$A$15,3,$B$1:$B$15,B1)>=1))
  • 填充单元格 D16（或任何空单元格） =SUMPRODUCT(C1:C15*D1:D15)

希望这可以帮助。

答案3

唯一可行的方法（如果我完全理解这个问题）是在 VBA 中编写一个用户定义函数。

原因是您需要确定组数并检查每个组中是否存在某些内容。如果 Excel 函数返回某个范围内的唯一值，那么您可能没问题。但由于没有，您无法确定所有组之间的任何共同点。

请注意，您可以计算一列中唯一值的数量，但这仅当 B 列值每个组仅出现一次时才有用。

以下是一些可帮助你开始使用 VBA 中的自定义函数的资源：

https://support.office.com/en-us/article/Create-Custom-Functions-in-Excel-2007-2f06c10b-3622-40d6-a1b2-b6748ae8231f

http://www.ozgrid.com/VBA/Functions.htm

答案4

@Snoopen

谢谢！成功了！！

该网站一直说我需要更多的声誉才能直接在你的帖子中添加评论，但你的终极一线公式有效。非常感谢！

@Clif

感谢您分享您的发现，这确实很有帮助！

非常感谢大家的帮助！

相关内容