如何从表中提取数据并根据特定标准汇总为文本字符串

如何从表中提取数据并根据特定标准汇总为文本字符串

我认为我可以用一张图片来最好地解释我的问题: 我的例子和问题

基本上,我需要一个公式,将“客户”行连接起来,然后将该客户的百分比和每月日期连接起来,以获得“Name1”在表中出现的频率。

有人可以帮帮我吗?

答案1

对……虽然棘手,但可以做到!

数据表

首先,熟悉 Excel 的“数据表”功能,该功能位于“数据”功能区 >“假设分析”下。数据表允许您创建一个 2D 变量表,其中的标题列 (x) 和标题行 (y) 被替换为公式,例如,像这样计算圆的半径:

在此处输入图片描述

计划

我们将创建一个数据表,将名称作为列输入值,将日期作为行输入值。这些值将代入计算摘要文本的公式中,从数学上讲,F(x,y) = SUMMARY(名称,日期)。

我们将在您的原始数据中添加两列,分别称为“选定日期”和“选定名称的摘要”。同时将旁边的单元格命名为“输入”日期姓名. 为这些单元格提供任何当前有效的值(例如 name1 和 21-Feb)。数据表将用值替换这些单元格并将计算出的摘要文本“复制”到表中。

在此处输入图片描述

总结公式

现在执行以下公式:

在此处输入图片描述

在 F2 中拥有空白的“=”单元格很重要,因为它是逐行传播的摘要文本的起点。

选定日期的 E 列仅复制选定日期的 % 数据日期. HLOOKUP 通过以下方式查找列日期在标题行并使用 ROW() 返回当前行的值。

=HLOOKUP(date, $C$1:$D$5, ROW(), TRUE)

摘要列 F 使用 IF(...) 来确定所选的姓名匹配当前行,然后将客户姓名和 % 值(来自 E 列)连接到其上方的摘要字符串。如果姓名不匹配,则只需将当前摘要字符串从上方移下即可。

=IF($A3=name, F2 & $B3 & "(" & E3*100 & "%), ", F2)

最后,使用 F6 中的 SUMIF 将所选人员的总数连接到摘要文本(仅当其属于当前选定的人员时才添加 % 数据):

=F5 & "总计 (" & SUMIF($A$3:$A$5, 名称, C3:C5)*100 & "%)"

最后,也是最重要的一点!数据表 A8 的角落单元格必须引用我们的摘要单元格 =F6。在数据表 A9:A10 中键入唯一名称列表,并在第 8 行中复制日期。选择整个数据表区域 A8:C10,单击数据 > 假设分析 > 数据表,然后通过选择名为的单元格来设置行输入数据并通过选择名为姓名

答案2

在 中J3,数组公式(CSE)将右侧复制到 ,K3并将所有内容向下复制:

=TEXTJOIN(", ",1,IF(($C$4:$C$6=$I3)*(INDEX($E$4:$F$6,,MATCH(J$2,$E$3:$F$3,0))<>0),$D$4:$D$6&TEXT(INDEX($E$4:$F$6,,MATCH(J$2,$E$3:$F$3,0))," (0%);;;"),""),"Sum"&TEXT(SUMIFS(INDEX($E$4:$F$6,,MATCH(J$2,$E$3:$F$3,0)),$C$4:$C$6,$I3)," (0%);;;"))

在此处输入图片描述

答案3

哦,我当然假设你真的确实需要文本摘要,否则数据透视表就是你最好的朋友:

在此处输入图片描述

相关内容