主页 / coding / 问题 / 78951732
Accepted
Gen
Asked: 2024-09-05 15:11:48 +0800 CST

SUMPRODUCT 与 TEXTSPLIT 和 MATCH 公式返回 #VALUE！在 Excel 中对多个交集求和时出现错误

  • 772

我正在尝试对表中的值求和，其中行和列引用都是从单独单元格中的连接字符串动态生成的。具体来说，我需要：

  1. 将包含连接行引用（例如，“数据转换验证模拟 1、切换计划验证”）的一个单元格 (BO28) 中的值拆分并匹配到表中的行。
  2. 将包含连接列标题（例如“Jane Smith、Emily Force”）的另一个单元格 (BP28) 中的值拆分并匹配到表中的列标题。
  3. 对行和列匹配的每个组合的相交值求和。

问题截图

在我的公式中，我想对以下交点求和：

“数据转换验证模拟 1”与“Jane Smith” “数据转换验证模拟 1”与“Emily Force” “切换计划验证”与“Jane Smith” “切换计划验证”与“Emily Force”

因此，预期结果应该是80 + 40 + 80 + 40 = 240。但是，当我尝试应用此公式时，出现了 #VALUE! 错误：

=SUMPRODUCT(
    ISNUMBER(MATCH(BN2:BN10, TEXTSPLIT(BN25, ", "), 0)) *
    ISNUMBER(MATCH(BN1:BU1, TEXTSPLIT(BN26, ", "), 0)) *
    BN2:BU10
)

我使用 TEXTSPLIT 将 BO28 和 BP28 中的连接字符串拆分为单独的值。我应用 MATCH 查找相应的行和列，使用 INDEX 返回相交的值。将其全部包装在 SUMPRODUCT 中以处理数组逻辑。

尽管遵循了这种方法，我仍然不断收到 #VALUE! 错误，我不知道问题出在哪里。（如果需要提供 excel 文件，请告诉我）。谢谢

excel

4 个回答

  1. Best Answer

    这是一个稍微不同的方法：

        =SUM(
CHOOSECOLS(
CHOOSEROWS(BO2:BQ5,XMATCH(TEXTSPLIT(BN10,", ");BN2:BN5)),
XMATCH(TEXTSPLIT(BN9,", "),BO1:BQ1))
)

    例子

    • 2

  2. 您需要比较数据的垂直和水平标题。然后使用SUMPRODUCT()与列标题和行标题匹配的相应值求和。尝试-

    =SUMPRODUCT(MAP(BO2:BU10,LAMBDA(x,
ISNUMBER(SEARCH(INDEX(BN2:BN10,ROW(x)-ROW(BO1)),BN25))*
(ISNUMBER(SEARCH(INDEX(BO1:BU1,1,COLUMN(x)-COLUMN(BN2)),BN26)))))*
(BO2:BU10))

    在此处输入图片描述

    • 1

  3. 另一种方式

    在此处输入图片描述

    =XMATCH(TRIM(TEXTSPLIT(A12,",")),$A$2:$A$6)查找课程名称。
    =XMATCH(TRIM(TEXTSPLIT(A13,",")),$B$1:$E$1)查找用户名。
    我添加了 TRIM，以防后面有空格,。

    =INDEX($B$2:$E$6,<course names formula>,<user names formula>)返回交叉点。

    将其全部粘贴在一起并放入 SUM 公式中：

    =SUM(INDEX($B$2:$E$6,XMATCH(TRIM(TEXTSPLIT(A12,",")),$A$2:$A$6),XMATCH(TRIM(TEXTSPLIT(A13,",")),$B$1:$E$1)))
    • 1

  4. 有INDEX且MATCH不需要处理标题。TRIM 删除了误导功能的不必要的空格。

    =SUMPRODUCT(INDEX(A1:G8,MATCH(TRANSPOSE(TRIM(TEXTSPLIT(A12,","))),A1:A8,0),MATCH(VALUE(TRIM(TEXTSPLIT(A13,","))),A1:G1,0)))

    在此处输入图片描述

    • 0

相关问题