Excel Dashboard 中有哪些替代 Pivot Table、改善 UI 的方案？

在允许运行 VBA 的桌面版 Excel 中，可以用 UserForm + 多列 ListBox 创建借用明细窗口。以 vbModeless 方式显示后，就能拖动窗口、滚动查看记录，同时继续操作 Dashboard。

如果只需要一个可移动的明细展示区域，Camera（照相机）生成的链接图片更省事。图片会随源单元格内容更新，但无法在图片内滚动、筛选或选择记录。Excel 原生没有将 PivotTable 一键转换为可拖动交互窗格的功能。

根据交互需求选择方案

现有的 Power Query 清理流程可以继续使用，只需调整后续的明细展示方式。

先判断是否需要替换 PivotTable

如果主要问题是行标签缩进、标题重复和小计占用空间，可以先调整 PivotTable：

1. 将“报表布局”改为“以表格形式显示”。
2. 按需要启用“重复所有项目标签”。
3. 关闭明细区域不需要的分类汇总和总计。
4. 统一日期格式、列宽和行高，并隐藏 Dashboard 的网格线。

调整后，PivotTable 的布局会更接近普通明细表。Microsoft 的布局说明

PivotTable 会按字段组合分组。如果需要保留每一笔借用记录，包括字段值完全相同的多条记录，使用原始明细表或 FILTER 更直接。如果继续使用 PivotTable，应保留唯一的借用记录 ID。

用 FILTER 生成按日期筛选的明细

以下字段名和单元格位置仅用于示例，使用时需替换为实际工作簿中的名称和位置。

假设 Power Query 将结果加载为 Excel Table，表名为 tblLoans，五列依次为：

物品 | 借用人 | 数量 | 借出日期 | 归还日期

在 Dashboard 中设置筛选条件：

• B2：查询开始日期。
• B3：查询结束日期。
• B4：物品名称，留空表示全部物品。

在单独的 Detail 工作表中，将表头放在 A6:E6，在 A7 输入：

=FILTER(
    tblLoans[[物品]:[归还日期]],
    (tblLoans[借出日期]<=Dashboard!$B$3)*
    (((tblLoans[归还日期]>=Dashboard!$B$2)+
      (tblLoans[归还日期]=""))>0)*
    (((Dashboard!$B$4="")+
      (tblLoans[物品]=Dashboard!$B$4))>0),
    "无匹配记录"
)

公式会筛选出借用期间与查询期间有重叠的记录，条件是：

借出日期 <= 查询结束日期
并且
归还日期 >= 查询开始日期，或归还日期为空

这里假设日期按天记录，借用期间包含起止日期，归还日期为空表示仍在借出。如果归还当天即可再次出借，应按业务规则调整边界比较。

FILTER 适用于 Microsoft 365、Excel 2021 等支持该函数的版本，结果会自动溢出到相邻单元格。FILTER 官方说明

使用时需注意：

• 日期必须是 Excel 日期值，不能只是看起来像日期的文本。
• B2 不应晚于 B3。
• 公式应放在 Excel Table 外，结果区域不能有阻挡溢出的内容。
• 返回的空日期可能显示为 0。可将日期列设为自定义格式 yyyy-mm-dd;;，隐藏零值。
• 上述公式使用逗号分隔参数；部分区域设置需要改用分号。

只需要可移动展示区域：使用 Camera

可以将 Detail 中的明细区域或现有 PivotTable 设为 Camera 的源区域：

1. 在支持该功能的桌面版 Excel 中，将 Camera 命令加入快速访问工具栏。
2. 选中源区域，点击 Camera。
3. 切换到 Dashboard，点击放置链接图片。
4. 调整图片位置和大小。

链接图片会随源区域的内容和格式变化而更新，因此后台可以继续使用 PivotTable 或公式。Microsoft 的链接图片说明

Camera 适合展示较短的明细列表。固定的源范围不会随记录增加而自动扩展，图片也没有内部滚动条。记录较多时，UserForm 更合适。

需要可拖动、可滚动窗口：使用 UserForm

以下示例适用于允许运行 VBA 的 Windows 桌面版 Excel，数据来自前面 Detail!A7 的筛选结果。

创建窗体

按 Alt+F11 打开 VBA 编辑器，插入一个 UserForm：

• 将窗体的 (Name) 设为 frmLoans。
• 添加 ListBox，将 (Name) 设为 lstLoans。
• 添加 CommandButton，将 (Name) 设为 cmdRefresh，按钮文字设为“刷新明细”。
• 在 ListBox 上方添加五个 Label，分别作为列标题。

调整窗体和 ListBox 的大小，让五列都能完整显示。

加载明细数据

将下面代码放入 frmLoans 的代码模块：

Option Explicit

Public Sub LoadDetails()
    Dim ws As Worksheet
    Dim src As Range
    Dim values() As Variant
    Dim r As Long
    Dim c As Long

    Set ws = ThisWorkbook.Worksheets("Detail")
    Me.lstLoans.Clear
    ws.Calculate

    If IsError(ws.Range("A7").Value) Then
        MsgBox "明细公式存在错误，请检查 Detail!A7。", vbExclamation
        Exit Sub
    End If

    If CStr(ws.Range("A7").Value2) = "无匹配记录" Then
        Exit Sub
    End If

    Set src = ws.Range("A7#")
    ReDim values(0 To src.Rows.Count - 1, 0 To 4)

    For r = 1 To src.Rows.Count
        For c = 1 To 5
            values(r - 1, c - 1) = src.Cells(r, c).Text
        Next c
    Next r

    With Me.lstLoans
        .ColumnCount = 5
        .ColumnHeads = False
        .ColumnWidths = "90 pt;100 pt;50 pt;85 pt;85 pt"
        .List = values
    End With
End Sub

Private Sub cmdRefresh_Click()
    LoadDetails
End Sub

代码读取的是单元格显示的文本。应先设置 Detail 中的日期格式，并留出足够的列宽，避免将 ##### 加载到列表中。ListBox 可以通过二维数组一次加载多列数据。Microsoft 的 List 属性示例

接着插入一个标准模块，加入以下代码：

Option Explicit

Public Sub ShowLoanDetails()
    Load frmLoans
    frmLoans.LoadDetails
    frmLoans.Show vbModeless
End Sub

在 Dashboard 上放置一个形状或表单按钮，为其分配 ShowLoanDetails 宏，并将工作簿保存为 .xlsm。

使用 vbModeless 时，窗体打开后仍可操作工作表。拖动窗体标题栏即可移动窗口，记录超出列表高度时可以滚动查看。Show 方法说明

这个示例加载的是当前筛选结果的快照。修改 Dashboard 条件后，需要点击窗体内的“刷新明细”。该按钮只重新读取公式结果，不会刷新 Power Query。源数据变化后，应先完成查询刷新，再加载列表。

UserForm 是独立的浮动窗口，没有内置停靠功能。ListBox 的排序、双击查看详情等操作也需要另写代码。Excel 网页版不能运行 VBA，因此该方案不适用于纯浏览器使用场景。网页版 VBA 限制

剩余库存要与明细筛选分开计算

筛选出与查询区间重叠的所有借用记录后，不能直接将借用数量相加，再从总库存中扣除。

例如，总库存为 10 件，甲在 1 日至 2 日借走 6 件，乙在 3 日至 4 日借走 6 件。查询 1 日至 4 日时，两条记录都会显示，数量合计为 12 件，但这两次借用没有同时发生。

如果需要显示整个查询区间内的最低可用数量，在总库存固定的前提下，应计算：

区间最低可用数量
= 总库存 - 区间内最大同时借出数量

明细列表用于查看借用记录，库存指标则需要按日期或时间点计算同时占用量。两者应使用一致的归还时间规则，让 Dashboard 中的明细展示清楚，库存数量也计算准确。

备注：内容仅供参考。