求个汇总公式

evluan01 · 发表于 2024-3-4 09:39

求个汇总公式

hcm19522 · 发表于 2024-3-4 11:26

直接取出前3名{=INDEX(A:A,RIGHT(LARGE(IF(MATCH($A$2:$A$31,$A$2:$A$31,)=ROW($1:$30),MMULT(N($A$2:$A$31=TRANSPOSE($A$2:$A$31)),(MMULT($C$2:$E$31,{1;1;1})>=18)*MMULT($C$2:$E$31,{1;1;1}))/1%+ROW($2:$31)),ROW(A1)),2))

G16:G22填入A1:A7 H16:H22統計各酒店符合條件加總 =SUMPRODUCT(($A$2:$A$31=G16)*(MMULT($C$2:$E$31,{1;1;1})>=18)*MMULT($C$2:$E$31,{1;1;1}))

橒♂蝣 · 发表于 2024-3-4 16:18

=LET(h,MMULT(C2:E31,SEQUENCE(3)^0),
REDUCE(A1:E1,UNIQUE(FILTER(A2:A31,ISERROR(XMATCH(A2:A31,FILTER(A2:A31,h<18))))),LAMBDA(x,y,LET(
i,FILTER(HSTACK(A2:E31,h),(h>=18)*(A2:A31=y)),
j,TEXTJOIN(",",1,FILTER(INDEX(i,,2),ISNUMBER(XMATCH(INDEX(i,,6),LARGE(INDEX(i,,6),SEQUENCE(3)))))),
k,FILTER(INDEX(i,SEQUENCE(ROWS(i)),{3,4,5}),ISNUMBER(XMATCH(INDEX(i,,6),LARGE(INDEX(i,,6),SEQUENCE(3))))),
VSTACK(x,IFERROR(HSTACK(y,j,MMULT(SEQUENCE(,ROWS(k))^0,k)/ROWS(k)),""))
))))

Holiday2022 · 发表于 2024-3-4 19:22

啥是“不可以用N-S写公式”

Holiday2022 · 发表于 2024-3-4 19:30

还挺难的！

Holiday2022 · 发表于 2024-3-4 21:29

想要计算各酒店总收入，却发现sumifs似乎不支持这样的数组。卡这里半天了，容我去开个帖子请教一下论坛的老师们。

=LET(酒店名称,UNIQUE(A2:A31),
酒店和相应技师,A2:B31,
科目得分,C2:E31,
各技师三项收入,BYROW(科目得分,LAMBDA(x,SUM(x))),
构建新表,SORT(HSTACK(酒店和相应技师,各技师三项收入),1),
各酒店总收入1,SUMIFS(TAKE(构建新表,,-1),TAKE(构建新表,,1),酒店名称),
各酒店总收入1)

复制代码

cnmlgb9998 · 发表于 2024-3-4 22:37

let
源 = Excel.CurrentWorkbook(){[Name = "表1"]}[Content],
更改的类型 = Table.TransformColumnTypes(
源,
{
{"酒店", type text},
{"技师姓名", type text},
{"科目A", Int64.Type},
{"科目B", Int64.Type},
{"科目C", Int64.Type}
}
),
分组的行 = Table.Group(
更改的类型,
{"酒店"},
{
{
"data",
each Table.AddColumn(
_,
"小计",
(x) => if List.Sum(List.Skip(Record.ToList(x), 2)) >= 18 then "1" else "0"
)
}
}
),
自定义1 = Table.SelectRows(分组的行, each not List.Contains([data][小计], "0")),
自定义2 = Table.TransformColumns(
自定义1,
{
{
"data",
each Table.FromRows(
{
[
a = Table.ToColumns(_),
b = Text.Combine(a{1}, ","),
c = List.Accumulate(
{2 .. 4},
{},
(x, y) => x & {Number.Round(List.Sum(a{y}) / List.Count(a{y}), 2)}
),
d = {b} & c
][d]
},
{"技师", "科目A", "科目B", "科目C"}
)
}
}
),
#"展开的“data”" = Table.ExpandTableColumn(
自定义2,
"data",
{"技师", "科目A", "科目B", "科目C"},
{"技师", "科目A", "科目B", "科目C"}
)
in
#"展开的“data”"

复制代码

cnmlgb9998 · 发表于 2024-3-4 22:59

//Unnamed
let
源 = Excel.CurrentWorkbook(){[Name = "表1"]}[Content],
更改的类型 = Table.TransformColumnTypes(
源,
{
{"酒店", type text},
{"技师姓名", type text},
{"科目A", Int64.Type},
{"科目B", Int64.Type},
{"科目C", Int64.Type}
}
),
分组的行 = Table.Group(
更改的类型,
{"酒店"},
{{"data", each Table.AddColumn(_, "小计", (x) => List.Sum(List.Skip(Record.ToList(x), 2)))}}
),
自定义1 = Table.TransformColumns(
Table.SelectRows(分组的行, each not List.Contains([data][小计], 18, (x, y) => x < y)),
{
{
"data",
each Table.SelectRows(
Table.AddRankColumn(_, "rank", {{"小计", 1}}, [RankKind = 1]),
each [rank] <= 3
)
}
}
),
自定义2 = Table.TransformColumns(
自定义1,
{
{
"data",
each Table.FromRows(
{
[
a = Table.ToColumns(_),
b = Text.Combine(a{1}, ","),
c = List.Accumulate(
{2 .. 4},
{},
(x, y) => x & {Number.Round(List.Sum(a{y}) / List.Count(a{y}), 2)}
),
d = {b} & c
][d]
},
{"技师", "科目A", "科目B", "科目C"}
)
}
}
),
#"展开的“data”" = Table.ExpandTableColumn(
自定义2,
"data",
{"技师", "科目A", "科目B", "科目C"},
{"技师", "科目A", "科目B", "科目C"}
)
in
#"展开的“data”"

复制代码

cnmlgb9998 · 发表于 2024-3-4 23:01

PQ能完美解决。见代码。

cnmlgb9998 · 发表于 2024-3-4 23:02

//Unnamed
let
源 = Excel.CurrentWorkbook(){[Name = "表1"]}[Content],
更改的类型 = Table.TransformColumnTypes(
源,
{
{"酒店", type text},
{"技师姓名", type text},
{"科目A", Int64.Type},
{"科目B", Int64.Type},
{"科目C", Int64.Type}
}
),
分组的行 = Table.Group(
更改的类型,
{"酒店"},
{{"data", each Table.AddColumn(_, "小计", (x) => List.Sum(List.Skip(Record.ToList(x), 2)))}}
),
自定义1 = Table.TransformColumns(
Table.SelectRows(分组的行, each not List.Contains([data][小计], 18, (x, y) => x < y)),
{
{
"data",
each Table.SelectRows(
Table.AddRankColumn(_, "rank", {{"小计", 1}}, [RankKind = 1]),
each [rank] <= 3
)
}
}
),
自定义2 = Table.TransformColumns(
自定义1,
{
{
"data",
each Table.FromRows(
{
[
a = Table.ToColumns(_),
b = Text.Combine(a{1}, ","),
c = List.Accumulate(
{2 .. 4},
{},
(x, y) => x & {Number.Round(List.Sum(a{y}) / List.Count(a{y}), 2)}
),
d = {b} & c
][d]
},
{"技师", "科目A", "科目B", "科目C"}
)
}
}
),
#"展开的“data”" = Table.ExpandTableColumn(
自定义2,
"data",
{"技师", "科目A", "科目B", "科目C"},
{"技师", "科目A", "科目B", "科目C"}
)
in
#"展开的“data”"

复制代码

		自动登录	找回密码
密码			免费注册

[求助] 求个汇总公式

评分

评分

评分