基于Excel颜色标记的Power BI考勤报表工具搭建及数据同步咨询

一、先统一Excel颜色规则 #

先把颜色和考勤状态的对应关系固定死，避免后续混乱：

• 红色单元格 → 休假
• 蓝色单元格 → 远程办公
• 绿色单元格 → 现场办公
  确保在线Excel考勤表的结构是：第一行是日期列，第一列是姓名，交叉单元格用颜色标记状态。

二、方案一：Power Query自定义函数读取颜色（无需Excel宏） #

这是纯Power BI端的实现，适合不想碰VBA的场景：

1. 导入考勤表到Power Query #

在Power BI中点击「获取数据」→「Excel工作簿」，选择在线Excel的路径（OneDrive/SharePoint链接需确保有权限），导入考勤表工作表到Power Query编辑器。

2. 创建读取单元格颜色的自定义函数 #

• 新建空白查询，重命名为GetCellColor
• 打开高级编辑器，替换为以下M代码（注意替换颜色的十进制值，需自行从Excel中获取）：

  (FilePath as text, SheetName as text, RowNumber as number, ColumnNumber as number) as text =>
  let
      ExcelApp = CreateObject("Excel.Application"),
      ExcelWorkbook = ExcelApp.Workbooks.Open(FilePath),
      ExcelSheet = ExcelWorkbook.Sheets(SheetName),
      CellColor = ExcelSheet.Cells(RowNumber, ColumnNumber).Interior.Color,
      // 替换为你实际的颜色-状态映射
      Status = if CellColor = 255 then "休假"
               else if CellColor = 16711680 then "远程办公"
               else if CellColor = 65280 then "现场办公"
               else "未标记",
      Cleanup = ExcelWorkbook.Close(false) & ExcelApp.Quit()
  in
      Status
  

  颜色十进制值获取方法：在Excel中选中对应颜色的单元格，打开VBA编辑器输入?Range("单元格地址").Interior.Color，回车得到数值。

3. 批量转换考勤表为状态数据 #

• 回到考勤表查询，将表头转成标题，然后选择所有日期列，点击「转换」→「逆透视列」，得到三列：姓名、属性（日期）、值（值列无用）。
• 添加索引列（从0开始），然后添加自定义列状态，调用GetCellColor函数，示例公式：

  = GetCellColor("C:\Users\XXX\OneDrive\考勤表.xlsx", "考勤表", [索引] + 2, List.PositionOf(Table.ColumnNames(考勤表), [属性]) + 1)
  

  说明：[索引]+2是因为数据从Excel第2行开始（假设表头占1行），List.PositionOf(...)用来映射日期列对应的Excel列号。

4. 同步到关联表结构 #

将得到的「姓名、日期、状态」扁平表，通过Power Query的合并、匹配操作，关联到你已有的类数据库架构表格（比如匹配姓名到员工ID、日期到日期ID、状态到状态ID），生成符合要求的关联数据后加载到Power BI。

三、方案二：Excel VBA生成辅助表（更快更稳定） #

如果Power Query函数运行速度慢，可在Excel端用VBA自动生成状态数据辅助表，Power BI直接读取：

1. 创建辅助表 #

在在线Excel中插入新工作表，命名为考勤状态数据，表头设为姓名、日期、状态。

2. 编写VBA自动同步代码 #

打开Excel VBA编辑器，在考勤表的代码窗口粘贴以下代码：

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim wsSource As Worksheet, wsTarget As Worksheet
    Dim lastRow As Long, lastCol As Long
    Dim i As Long, j As Long
    Dim cellColor As Long
    Dim targetRow As Long
    
    Set wsSource = ThisWorkbook.Sheets("考勤表")
    Set wsTarget = ThisWorkbook.Sheets("考勤状态数据")
    
    ' 清空旧数据（保留表头）
    wsTarget.Range("A2:C" & wsTarget.Cells(wsTarget.Rows.Count, "A").End(xlUp).Row).ClearContents
    targetRow = 2
    
    lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row
    lastCol = wsSource.Cells(1, wsSource.Columns.Count).End(xlToLeft).Column
    
    ' 遍历单元格生成状态数据
    For i = 2 To lastRow
        For j = 2 To lastCol
            cellColor = wsSource.Cells(i, j).Interior.Color
            wsTarget.Cells(targetRow, "A").Value = wsSource.Cells(i, 1).Value
            wsTarget.Cells(targetRow, "B").Value = wsSource.Cells(1, j).Value
            Select Case cellColor
                Case 255: wsTarget.Cells(targetRow, "C").Value = "休假"
                Case 16711680: wsTarget.Cells(targetRow, "C").Value = "远程办公"
                Case 65280: wsTarget.Cells(targetRow, "C").Value = "现场办公"
                Case Else: wsTarget.Cells(targetRow, "C").Value = "未标记"
            End Select
            targetRow = targetRow + 1
        Next j
    Next i
End Sub

保存为启用宏的工作簿（.xlsm格式），确保在线文档所在平台（OneDrive/SharePoint）支持宏运行。

3. Power BI读取辅助表 #

在Power BI中直接导入考勤状态数据工作表，再同步到你的关联表结构即可。

四、设置实时刷新 #

• 对于OneDrive/SharePoint的在线Excel，在Power BI中点击「刷新」→「设置刷新频率」，设置为15分钟或更短间隔，确保能实时获取Excel的颜色变更。
• 若用VBA方案，需确保Excel宏在打开时自动启用，可在Excel信任中心设置信任该文档。

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