代码之家 › 专栏 › 技术社区 › Daniel

如何使用LET和其他动态数组函数创建excel公式,给出每列的计算输出?

excel-tables array-formulas excel-formula excel

-5

Daniel · 技术社区 · 7 月前

我有一张这样的桌子,上面存放着不同科目学生的分数

姓名	等级	身份证件	数学	英语	印地语
托尼	5.	1.	39	30	30
安得烈	5.	2.	40	20	20
作记号	5.	3.	40	10	30

还有一个像这样的表格,它设置了主体的最大标记

主题:	最大标记
数学	40
英语	30
印地语	30

我想创建一个excel公式,通过计算每个科目获得的分数百分比来返回这个值,并相应地对其进行评分,然后将数据排列到这个位置

并且可以使用AI生成excel函数 =LET( Data, Table1, Headers, {"Name","Class","ID","Math","Math Grade","English","English Grade","Hindi","Hindi Grade"}, GradeThresholds, Table2, GetMaxMarks, LAMBDA(Subject, IFERROR(INDEX(Table2[Max Marks], MATCH(Subject, Table2[Subject], 0)), "Subject Not Found") ), Grades, LAMBDA(Score,MaxMarks, IFS( Score / MaxMarks >= 0.9, "A", Score / MaxMarks >= 0.75, "B", Score / MaxMarks >= 0.6, "C", Score / MaxMarks >= 0.5, "D", TRUE, "F" ) ), MathMaxMarks, GetMaxMarks("Math"), EnglishMaxMarks, GetMaxMarks("English"), HindiMaxMarks, GetMaxMarks("Hindi"), MathGrades, Grades(INDEX(Data,,MATCH("Math",Table1[#Headers],0)), MathMaxMarks), EnglishGrades, Grades(INDEX(Data,,MATCH("English",Table1[#Headers],0)), EnglishMaxMarks), HindiGrades, Grades(INDEX(Data,,MATCH("Hindi",Table1[#Headers],0)), HindiMaxMarks), ExpandedTable, HSTACK( INDEX(Data,,MATCH("Name",Table1[#Headers],0)), INDEX(Data,,MATCH("Class",Table1[#Headers],0)), INDEX(Data,,MATCH("ID",Table1[#Headers],0)), INDEX(Data,,MATCH("Math",Table1[#Headers],0)), MathGrades, INDEX(Data,,MATCH("English",Table1[#Headers],0)), EnglishGrades, INDEX(Data,,MATCH("Hindi",Table1[#Headers],0)), HindiGrades ), VSTACK(Headers, ExpandedTable) )

这工作得很好,但我希望公式是动态的,并根据需要进行调整。我将向表中添加新的主题,不想每次添加新主题时都将主题名称硬编码到公式中。

1 回复 | 直到 7 月前

rotabor 7 月前

我为你创建了这个解决方案:

[A14]=LET(
  marks,LAMBDA(sc,subj,
    LET(r,sc/XLOOKUP(subj,Table2[[#All],[Subject]],Table2[[#All],[Max Marks]]),
      IFS(r>=0,9,"A",r>=0,75,"B",r>=0,6,"C",r>=0,5,"D",TRUE,"F")
    )
  ),
  REDUCE(
    Table1[[#All],[Name]:[ID]],
    SEQUENCE(1,COLUMNS(Table1[#All])-3,4),
    LAMBDA(a,v,
      HSTACK(a,
        CHOOSECOLS(Table1[#All],v),
        VSTACK(INDEX(Table1[#All],1,v)&" Grade",
          marks(CHOOSECOLS(Table1,v),INDEX(Table1[#All],1,v))
        )
      )
    )
  )
)

其中表1是第一张表,表2是第二张表。您可以轻松地在表1中添加新行和列,在表2中添加新行,而无需修改公式。

P.b 7 月前

跳过最高分数表的另一个选项:

=REDUCE(A1:C4,{1,2,3},
 LAMBDA(x,y,
        LET(i,CHOOSECOLS(D1:F4,y),
            n,DROP(i,1),
HSTACK(x,
       i,
       VSTACK(TAKE(i,1)&" Grade",
              XLOOKUP(n/MAX(n),{0.9,0.75,0.6,0.5},{"A","B","C","D"},"F",-1))))))

表参考:

=REDUCE(Table1[[#All],[Name]:[ID]],{1,2,3},
 LAMBDA(x,y,
        LET(i,CHOOSECOLS(Table1[[#All],[Math]:[Hindi]],y),
        n,DROP(i,1),
HSTACK(x,
       i,
       VSTACK(TAKE(i,1)&" Grade",
              XLOOKUP(n/MAX(n),{0.9,0.75,0.6,0.5},{"A","B","C","D"},"F",-1))))))

完全动态:

=LET(table,Table1[#All],
     subjects,DROP(table,,3),
REDUCE(TAKE(table,,3),SEQUENCE(,COLUMNS(subjects)),
LAMBDA(x,y,
   LET(i,CHOOSECOLS(subjects,y),
       n,DROP(i,1),
HSTACK(x,
       i,
       VSTACK(TAKE(i,1)&" Grade",
              XLOOKUP(n/MAX(n),{0.9,0.75,0.6,0.5},{"A","B","C","D"},"F",-1)))))))