excel - Excel 中带有公式的动态表

Question

使用 office 365 (Excel)，可以使用溢出公式创建动态表，类似于 Power Query 所做的。

动态表优于 excel 表的优点是会自动创建或删除新的行和列。

设置这些表有点麻烦。下面是我所做的。如果其他人对此进行了实验，请给我一些指示。

下面是一个没有和有 UDF 的动态表的示例：

在我的示例中，有一个现有 Excel 表的名称，其中有B1一个列标题名称B2。动态表（和公式）以A4. A4 中动态表格的公式。该表列出了现有表中的所有唯一值Table，Col并提供了一个计数。底部有一个总计行。我也有动态格式，使它看起来像一个 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

我还为我的动态表使用过滤器。我使用带有#reference 的支持溢出公式来制作动态下拉框，然后使用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
)

标签： excelexcel-formulapowerqueryexcel-tables