如何在Google Sheets中实现基于日期/百分比的通知及房间级任务进度提示？

一、房间级汇总Sparkline实现 #

要实现房间级的完成度Sparkline，核心是精准抓取对应房间组内的任务数据，结合Excel的SUBTOTAL函数（自动识别折叠分组范围）和SPARKLINE参数配置即可：

假设你的表格结构为：

• A列：房间名称（作为折叠分组的标题行）
• B列：具体维护任务
• C列：完成状态（用1标记完成，0标记未完成）

在房间标题行的空白单元格（比如D2）输入以下公式：

=SPARKLINE(
  SUBTOTAL(9, C:C), 
  {
    "charttype", "bar";
    "color1", "#27ae60";
    "color2", "#e74c3c";
    "max", SUBTOTAL(103, C:C)
  }
)

参数说明：

• SUBTOTAL(9, C:C)：计算当前折叠组内的完成任务总数（忽略隐藏行）
• SUBTOTAL(103, C:C)：计算当前折叠组内的总任务数（忽略隐藏行）
• "charttype", "bar"：生成横向进度条样式
• "color1"/"color2"：分别对应完成和未完成部分的颜色

将公式向下填充到所有房间标题行，就能自动生成每个房间的任务完成进度条。

二、房间标题变色提示（条件格式） #

要让有未完成任务的房间标题变红，用条件格式+分组识别公式即可：

1. 选中所有房间标题所在的单元格（比如A2、A7、A12等）
2. 打开「条件格式」→「新建规则」→「使用公式确定要设置格式的单元格」
3. 输入公式：

   =SUBTOTAL(103, C:C) - SUBTOTAL(9, C:C) > 0
   

4. 设置格式为「字体颜色：红色」

原理：用总任务数减去已完成任务数，结果大于0说明存在未完成任务，触发颜色提示。

三、通知弹窗实现（VBA宏） #

如果需要主动弹窗提醒未完成任务的房间，可以用VBA遍历分组并检查：

1. 打开Excel的「开发工具」→「Visual Basic」（或按Alt+F11）
2. 插入模块，粘贴以下代码：

   Sub CheckMaintenanceTasks()
       Dim ws As Worksheet
       Dim roomRow As Range
       Dim msgContent As String
       Dim completedTasks As Integer, totalTasks As Integer
       
       '替换为你的工作表名称
       Set ws = ThisWorkbook.Worksheets("房屋维护表")
       msgContent = "需处理的房间任务：" & vbCrLf
       
       '遍历A列所有房间标题行（假设标题行是分组的顶级行，OutlineLevel=1）
       For Each roomRow In ws.UsedRange.Rows
           If roomRow.OutlineLevel = 1 Then
               '获取当前房间组内的任务范围
               Dim taskRange As Range
               Set taskRange = ws.Range("C" & roomRow.Row + 1 & ":C" & roomRow.End(xlDown).Row - 1)
               
               completedTasks = Application.Subtotal(9, taskRange)
               totalTasks = Application.Subtotal(103, taskRange)
               
               If completedTasks < totalTasks Then
                   msgContent = msgContent & "- " & roomRow.Cells(1, 1).Value & vbCrLf
               End If
           End If
       Next roomRow
       
       '弹窗提示
       If msgContent <> "需处理的房间任务：" & vbCrLf Then
           MsgBox msgContent, vbExclamation, "维护任务提醒"
       Else
           MsgBox "所有房间任务已完成！", vbInformation, "维护任务提醒"
       End If
   End Sub
   

3. 返回Excel，添加一个按钮（「开发工具」→「插入」→「按钮」），关联这个宏即可。你也可以设置宏在工作簿打开时自动运行，只需将代码放到ThisWorkbook的Workbook_Open事件中。

注意事项 #

• 确保你的表格已经按房间完成分组折叠（选中任务行→「数据」→「分组」），这样SUBTOTAL才能正确识别组内数据
• 完成状态尽量用数字1/0或布尔值TRUE/FALSE，避免文本导致计算错误

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