Excel数据透视表中识别每日同时存在0和1值的公式需求

方法1：数组公式快速统计 #

核心逻辑是按日期分组，验证每个日期是否同时包含'0'（进入）和'1'（离开）状态，直接计算有效睡眠事件总数：

=SUM(--(FREQUENCY(IF((A:A<>"")*(B:B="0")*(COUNTIFS(A:A,A:A,B:B,"1")>0),MATCH(A:A,A:A,0)),ROW(A:A)-ROW(A1)+1)>0))

• 注意：旧版Excel需按Ctrl+Shift+Enter确认数组公式，新版Excel直接回车即可。
• 若要逐日期查看事件数，可在C2单元格输入公式下拉填充，再对C列求和：

=IF(COUNTIFS($A:$A,A2,$B:$B,"0")>0,COUNTIFS($A:$A,A2,$B:$B,"1"),0)

方法2：Power Query自动化处理 #

适合需要重复处理或数据量较大的场景：

• 选中数据区域，点击「数据」→「从表格/区域」进入Power Query编辑器
• 按日期列分组，操作选择「所有行」，将状态列打包为集合
• 添加自定义列，公式为：=List.Contains([Grouped],"0") and List.Contains([Grouped],"1")，命名为「有效睡眠事件」
• 筛选「有效睡眠事件」为True的行，统计行数即为总事件数，也可导出回Excel得到每日的有效记录

方法3：VBA宏批量统计 #

适合频繁处理同类数据的场景，一键完成统计：

Sub CountSleepEvents()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim dateDict As Object
    Dim i As Long
    Dim currentDate As Date
    Dim statusVal As String
    
    Set ws = ActiveSheet
    Set dateDict = CreateObject("Scripting.Dictionary")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    '遍历数据，记录每个日期的唯一状态集合
    For i = 2 To lastRow '假设第一行为表头
        currentDate = ws.Cells(i, "A").Value
        statusVal = ws.Cells(i, "B").Value
        
        If Not dateDict.Exists(currentDate) Then
            dateDict(currentDate) = New Collection
        End If
        
        '避免重复添加同一状态
        On Error Resume Next
        dateDict(currentDate).Add statusVal, Key:=CStr(statusVal)
        On Error GoTo 0
    Next i
    
    '统计同时包含0和1的日期数
    Dim eventCount As Integer
    eventCount = 0
    For Each key In dateDict.Keys
        If dateDict(key).Count = 2 Then '确保同时存在进入和离开状态
            eventCount = eventCount + 1
        End If
    Next key
    
    '输出结果到C列
    ws.Cells(1, "C").Value = "总睡眠事件数"
    ws.Cells(2, "C").Value = eventCount
End Sub

• 使用方式：按Alt+F11打开VBA编辑器，插入模块粘贴代码，运行宏即可。结果会输出到当前工作表的C2单元格。

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