如何在Excel 365中借助溢出公式实现类似Power Query的动态表格？

我在使用Office 365版本的Excel时，发现可通过溢出公式创建类似Power Query效果的动态表格，这类动态表格相较普通Excel表的优势在于可自动增删行列。不过这类表格的配置比较繁琐，以下是我目前的实现方案，也想请教有相关实践经验的朋友分享优化建议。

以下分别是不使用UDF和使用UDF的动态表格示例：

我的示例中，B1单元格存储现有Excel表的名称，B2单元格存储列标题名称，动态表格及公式从A4单元格开始，功能是提取指定现有表指定列的所有唯一值并统计出现次数，底部带合计行，同时配置了动态格式让其外观和普通Excel表一致。

核心实现公式 #

=LET(
    Table, B1,
    Col, B2,
    EmptyList, "-",
    Header, CHOOSE({1,2},"Name","Count"),

    MyColumn, INDIRECT(Table & "[" & Col & "]"),
    Uniques, UNIQUE(MyColumn),
    List, SORT(FILTER(Uniques,LEN(Uniques)>0,EmptyList),1,1),
    Counts, COUNTIF(MyColumn,"="&List),
    ReturnArray, IFERROR(CHOOSE({1,2},List, Counts),CHOOSE({1,2},EmptyList, EmptyList)),

    Totals, CHOOSE({1,2},"Total",IFERROR(SUM(Counts),EmptyList)),

    Range1,Header,
    Range2,ReturnArray,
    Range3,Totals,
    Rows1,ROWS(Range1),Rows2,ROWS(Range2),Rows3,ROWS(Range3),Cols1,COLUMNS(Range1),
    RowIndex,SEQUENCE(Rows1+Rows2+Rows3),ColIndex,SEQUENCE(1,Cols1),
    Range123,IF(RowIndex<=Rows1,INDEX(Range1,RowIndex,ColIndex),IF(RowIndex<=Rows1+Rows2,INDEX(Range2,RowIndex-Rows1,ColIndex),INDEX(Range3,RowIndex-Rows1-Rows2,ColIndex))),

    Return, Range123,
    Return
)

动态格式规则 #

格式通过应用到整个工作表的3条条件格式规则实现：

• 表头格式（字体加粗、上下边框）：=AND(OR(ISERR(OFFSET(A1,-1,0)),ISBLANK(OFFSET(A1,-1,0)))=TRUE,ISBLANK(A1)=FALSE)
• 条纹格式（偶数行填充灰色）：=AND(CELL("row",A1)=EVEN(CELL("row",A1)),ISBLANK(A1)=FALSE)
• 合计行格式（字体加粗、上下边框）：=AND(ISBLANK(A1)=FALSE,ISBLANK(A2)=TRUE)

如果使用的是启用宏的工作簿，可借助以下UDF在条件格式中区分动态表格和其他单元格：

Function ListTables() As Variant
    
    Dim oSheet As Worksheet
    Dim loTable As ListObject
    Dim aTables As Variant
    Dim lFound As Long
    
    lFound = 0
    ReDim aTables(1 To 1)
    
    For Each oSheet In ThisWorkbook.Worksheets
        For Each loTable In oSheet.ListObjects
            If Not loTable.HeaderRowRange Is Nothing Then
                ' This is a table
                lFound = lFound + 1
                ReDim Preserve aTables(1 To lFound)
                aTables(lFound) = loTable.Name
            End If
        Next loTable
    Next oSheet
    
    ListTables = Application.WorksheetFunction.Transpose(aTables)
End Function

扩展筛选功能 #

我还给这类动态表格配置了筛选功能，借助带#引用的溢出公式制作动态下拉框，组合使用FILTER、UNIQUE、SORT函数实现，效果示例如下：

不带表头和合计行的筛选版实现公式如下：

=LET(
    ColumnReturn,  Table_1[Column 1],
    TableFilter, E1,
    TableHeaderFilter, E2,
    ColumnFilter, INDIRECT(TableFilter & "[" & TableHeaderFilter & "]"),
    CriteriaExclude, E3,
    CriteriaOperator,E4,

    FilterEqual, FILTER(ColumnReturn,ColumnFilter = CriteriaExclude,"- None -"),
    FilterGreater, FILTER(ColumnReturn,ColumnFilter > CriteriaExclude,"- None -"),
    FilterLess,FILTER(ColumnReturn,ColumnFilter < CriteriaExclude,"- None -"),
    FilterNotEqual, FILTER(ColumnReturn,ColumnFilter<> CriteriaExclude,"- None -"),
    ListReturn, IF(CriteriaOperator = "=", FilterEqual, IF(CriteriaOperator = ">", FilterGreater, IF(CriteriaOperator = "<", FilterLess, IF(CriteriaOperator = "<>", FilterNotEqual, "")))),

    Return, IF(OR(ISBLANK(TableFilter),ISBLANK(TableHeaderFilter),ISBLANK(CriteriaExclude),ISBLANK(CriteriaOperator)),ColumnReturn,ListReturn),
    Return
)

优化咨询 #

想请教各位有没有更简便的实现方案，或是相关优化技巧？

内容的提问来源于stack exchange，提问作者Dave Thunes