google-sheets - 如何根据特定标准选择前 10 个值？

Question

我想根据收入选择前十名产品，为期三周：

星期自动更改（例如，本周我有 5/27，但下周会自动更新为 6/3）。

我的数据集每周自动更新，如下所示：

我试过使用 INDEX MATCH 和 LARGE，但我不知道如何自动完成。

我希望输出是一个公式，每次我的数据集更新时都会通过增加额外的几周收入来自动更新。

任何帮助将不胜感激！

标签： google-sheetsgoogle-sheets-formulaarray-formulas

Answer 1

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)})