如何根据特定的标准选择前10位的值？
如何根据特定的标准选择前10位的值？
提问于 2019-05-31 08:48:51
我想根据收入来选择十大产品，三个不同的星期：
周会自动改变(例如，这周我有5/27，但下周它将自动更新到6/3)。
我的数据集每周自动更新，如下所示：
我试过使用索引匹配和大，但我不知道如何自动完成它。
我希望输出是一个公式，每次我的数据集更新时，都会通过增加额外的周收入来自动更新。
任何帮助都将不胜感激！
回答 2
Stack Overflow用户
回答已采纳
发布于 2019-05-31 19:02:12
好吧，在表被分享之前，我生成了一些随机数据，结果是上周是最好的一周：
表格数据中的原始数据：
公式：
B1
=index(Data!B1:1,COUNTA(Data!1:1)-1)D1
=index(Data!B1:1,COUNTA(Data!1:1)-2)F1
=ArrayFormula(index(Data!B1:1,match(max(mmult(transpose(row(Data!B2:index(Data!B2:B,counta(A:A)-1)))^0,Data!B2:index(Data!B2:Z,counta(A:A)-1,counta(Data!1:1)-1))),
mmult(transpose(row(Data!B2:index(Data!A2:B,counta(A:A)-1)))^0,Data!B2:index(Data!B2:Z,counta(A:A)-1,counta(Data!1:1)-1)),0)))A3
=sortn({Data!A2:A,index(Data!B2:Z,0,counta(Data!1:1)-1)},10,0,2,false)C3
=sortn({Data!A2:A,index(Data!B2:Z,0,counta(Data!1:1)-2)},10,0,2,false)E3
=sortn({Data!A2:A,index(Data!B2:Z,0,match(F1,Data!B1:1,0))},10,0,2,false)Stack Overflow用户
发布于 2019-05-31 19:16:12
B4：
={ARRAYFORMULA(INDIRECT("Dataset!"&ADDRESS(1, MATCH(WEEKNUM(TODAY(), 21),
WEEKNUM(INDIRECT("Dataset!A1:"&ADDRESS(1, COUNTA(Dataset!A1:1), 4)), 21), 0), 4))),
ARRAYFORMULA(SUM(INDIRECT("Dataset!"&ADDRESS(2, MATCH(INDIRECT("Dataset!"&
ADDRESS(1, MATCH(WEEKNUM(TODAY(), 21), WEEKNUM(INDIRECT("Dataset!A1:"&
ADDRESS(1, COUNTA(Dataset!A1:1), 4)), 21), 0), 4)),
Dataset!1:1, 0),4)&":"&ADDRESS(ROWS(Dataset!A:A), MATCH(INDIRECT("Dataset!"&
ADDRESS(1, MATCH(WEEKNUM(TODAY(), 21), WEEKNUM(INDIRECT("Dataset!A1:"&
ADDRESS(1, COUNTA(Dataset!A1:1), 4)), 21), 0), 4)), Dataset!1:1, 0),4))))}B5：
={ARRAYFORMULA(INDEX(Dataset!B1:1, MATCH(MAX(MMULT(TRANSPOSE(ROW(
Dataset!B2:INDEX(Dataset!B2:B, COUNTA(Dataset!A:A)-1)))^0,
Dataset!B2:INDEX(Dataset!B2:Z, COUNTA(Dataset!A:A)-1,
COUNTA(Dataset!1:1)-1))), MMULT(TRANSPOSE(ROW(
Dataset!B2:INDEX(Dataset!A2:B, COUNTA(Dataset!A:A)-1)))^0,
Dataset!B2:INDEX(Dataset!B2:Z, COUNTA(Dataset!A:A)-1, COUNTA(Dataset!1:1)-1)), 0))),
ARRAYFORMULA(SUM(INDIRECT("Dataset!"&ADDRESS(2, MATCH(INDEX(Dataset!B1:1,
MATCH(MAX(MMULT(TRANSPOSE(ROW(
Dataset!B2:INDEX(Dataset!B2:B, COUNTA(Dataset!A:A)-1)))^0,
Dataset!B2:INDEX(Dataset!B2:Z, COUNTA(Dataset!A:A)-1,
COUNTA(Dataset!1:1)-1))), MMULT(TRANSPOSE(ROW(
Dataset!B2:INDEX(Dataset!A2:B, COUNTA(Dataset!A:A)-1)))^0,
Dataset!B2:INDEX(Dataset!B2:Z, COUNTA(Dataset!A:A)-1, COUNTA(Dataset!1:1)-1)), 0)),
Dataset!1:1, 0), 4)&":"&ADDRESS(ROWS(Dataset!A:A), MATCH(INDEX(Dataset!B1:1,
MATCH(MAX(MMULT(TRANSPOSE(ROW(
Dataset!B2:INDEX(Dataset!B2:B, COUNTA(Dataset!A:A)-1)))^0,
Dataset!B2:INDEX(Dataset!B2:Z, COUNTA(Dataset!A:A)-1,
COUNTA(Dataset!1:1)-1))), MMULT(TRANSPOSE(ROW(
Dataset!B2:INDEX(Dataset!A2:B, COUNTA(Dataset!A:A)-1)))^0,
Dataset!B2:INDEX(Dataset!B2:Z, COUNTA(Dataset!A:A)-1,
COUNTA(Dataset!1:1)-1)), 0)), Dataset!1:1, 0), 4))))}B6：
={ARRAYFORMULA(INDIRECT("Dataset!"&ADDRESS(1, MATCH(WEEKNUM(TODAY(), 21),
WEEKNUM(INDIRECT("Dataset!A1:"&ADDRESS(1, COUNTA(Dataset!A1:1), 4)), 21), 0), 4)))-7,
ARRAYFORMULA(SUM(INDIRECT("Dataset!"&ADDRESS(2, MATCH(INDIRECT("Dataset!"&
ADDRESS(1, MATCH(WEEKNUM(TODAY(), 21), WEEKNUM(INDIRECT("Dataset!A1:"&
ADDRESS(1, COUNTA(Dataset!A1:1), 4)), 21), 0), 4))-7,
Dataset!1:1, 0),4)&":"&ADDRESS(ROWS(Dataset!A:A), MATCH(INDIRECT("Dataset!"&
ADDRESS(1, MATCH(WEEKNUM(TODAY(), 21), WEEKNUM(INDIRECT("Dataset!A1:"&
ADDRESS(1, COUNTA(Dataset!A1:1), 4)), 21), 0), 4))-7, Dataset!1:1, 0), 4))))}E5：
=ARRAYFORMULA({
QUERY({Dataset!$A$2:$A, INDIRECT("Dataset!"&
ADDRESS(2, MATCH(F$3, Dataset!$A$1:$1, 0), 4)&":"&
ADDRESS(ROWS(Dataset!$A2:$A)+1, MATCH(F$3, Dataset!$A$1:$1, 0), 4))},
"order by Col2 desc limit 10", 0),
QUERY({Dataset!$A$2:$A, INDIRECT("Dataset!"&
ADDRESS(2, MATCH(H$3, Dataset!$A$1:$1, 0), 4)&":"&
ADDRESS(ROWS(Dataset!$A2:$A)+1, MATCH(H$3, Dataset!$A$1:$1, 0), 4))},
"order by Col2 desc limit 10", 0),
QUERY({Dataset!$A$2:$A, INDIRECT("Dataset!"&
ADDRESS(2, MATCH(J$3, Dataset!$A$1:$1, 0), 4)&":"&
ADDRESS(ROWS(Dataset!$A2:$A)+1, MATCH(J$3, Dataset!$A$1:$1, 0), 4))},
"order by Col2 desc limit 10", 0)})
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/56391682
复制相关文章
相似问题
腾讯云开发者
