如图,黄色标记部分为需要统计的数值,即统计标题1-6中有某内容出现的行数及对应行中H列数值的求和。其中出现次数我使用了COUNTIF,但无法解决同一行内多次出现同内容的情况,即比如序号7中A出现了3次,实际只需要统计一次。数值求和部分实在没有想到太好的办法。求大神们指导!
是这样吧?您截图中B的求和算错了?
我是用powerquery实现的:
let
源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
更改的类型 = Table.TransformColumnTypes(源,{{"序号", Int64.Type}, {"标题1", type text}, {"标题2", type text}, {"标题3", type text}, {"标题4", type text}, {"标题5", type text}, {"标题6", type text}, {"数值", Int64.Type}}),
逆透视的列 = Table.UnpivotOtherColumns(更改的类型, {"序号"}, "属性", "值"),
复制的列 = Table.DuplicateColumn(逆透视的列, "值", "值 - 复制"),
更改的类型1 = Table.TransformColumnTypes(复制的列,{{"值 - 复制", Int64.Type}}),
已替换的错误 = Table.ReplaceErrorValues(更改的类型1, {{"值 - 复制", null}}),
填充 = Table.FillUp(已替换的错误,{"值 - 复制"}),
筛选的行 = Table.SelectRows(填充, each ([属性] <> "数值")),
重命名的列 = Table.RenameColumns(筛选的行,{{"值 - 复制", "数值"}}),
分组的行 = Table.Group(重命名的列, {"值"}, {{"计数", each Table.RowCount(_), type number}, {"data", each _, type table [序号=number, 属性=text, 值=text, 数值=number]}}),
#"展开的“data”" = Table.ExpandTableColumn(分组的行, "data", {"序号", "属性", "数值"}, {"序号", "属性", "数值"}),
删除的列 = Table.RemoveColumns(#"展开的“data”",{"属性"}),
分组的行1 = Table.Group(删除的列, {"序号"}, {{"计数", each Table.RowCount(_), type number}, {"data", each _, type table [值=text, 计数=number, 序号=number, 属性=text, 数值=number]}}),
自定义1 = Table.TransformColumns(分组的行1,{"data",each Table.Distinct(_)}),
删除的其他列 = Table.SelectColumns(自定义1,{"计数", "data"}),
删除的列1 = Table.RemoveColumns(删除的其他列,{"计数"}),
#"展开的“data”1" = Table.ExpandTableColumn(删除的列1, "data", {"值", "计数", "序号", "数值"}, {"值", "计数", "序号", "数值"}),
删除的列2 = Table.RemoveColumns(#"展开的“data”1",{"序号"}),
分组的行2 = Table.Group(删除的列2, {"值", "计数"}, {{"求和", each List.Sum([数值]), type number}})
in
分组的行2
用了unpivot之后,多次用了分组。
提供一个示例文件供参考:链接: https://pan.baidu.com/s/12zoQ8y0Pa_VpMLef-RB9uA
用法:用excel2016及以上版本打开,替换原始数据区域,然后点击【数据】选项卡,点击”全部刷新“,新结果(在sheet2上)就出来了。如果是低版本,需要自行百度安装启用powerquery插件。
出现次数(也许称为出现的行数更确切)可用公式,如图K列:
K2=COUNT(0/MMULT(N(B$2:G$8=J2),ROW($1:$6)^0))
也可用公式,如图M列:
M2=COUNT(0/COUNTIF(OFFSET(B$1:G$1,ROW($1:$7),,),J2))
同时按Ctrl+Shift+Enter三键输入数组公式
或用公式,如N列:
N2=SUMPRODUCT((COUNTIF(OFFSET(B$1:G$1,ROW($1:$7),,),J2)>0)*1)
个人偏好公式1,用矩阵乘法的方法
数值求和公式:
L2=SUMPRODUCT((COUNTIF(OFFSET(B$1:G$1,ROW($1:$7),,),J2)>0)*H$2:H$8)
求和列,L2输入
=(COUNTIF($B$2:$G$2,J2)>0)*$H$2+(COUNTIF($B$3:$G$3,J2)>0)*$H$3+(COUNTIF($B$4:$G$4,J2)>0)*$H$4+(COUNTIF($B$5:$G$5,J2)>0)*$H$5+(COUNTIF($B$6:$G$6,J2)>0)*$H$6+(COUNTIF($B$7:$G$7,J2)>0)*$H$7+(COUNTIF($B$8:$G$8,J2)>0)*$H$8
公式下拉
追问首先感谢大佬辛苦回答!
不过这个的话有个问题……我现在是为了说明问题内容所以只用了七行数据,假如数据的行数有成千上万行,总不能一直把函数往下加长……所以这样的话您有解决的思路吗?
只能用VBA了了,公式解决不了
感谢大佬解决第一个问题……关于数值的那一项有没有比较好的解决办法呢?
追答数值求和,没看明白计算规律。你能举例说明一下吗?
追问就是假设内容A在第1/2/3/4/6/7行中出现,那么就把1/2/3/4/6/7行中对应的H列数值求和的意思。 即 出现次数和数值求和分别是 :A在1-7行中共几行出现过,出现过的行所对应的H列数值求和是多少。
另外昨天因为比较急,疏漏了一个问题,就是这个函数公式假设内容不仅仅是7行而是成千上万行的话,是否能适用、、
再次感谢!!!
简化了一下公式。