需要复杂的 Excel 公式或 VBA

Question

我需要运行 2 次每日报告，这会创建一个包含学生数据的 Excel 表，可能会很大。此报告中有很多列，但我关心的是以下几列：

• A 列是唯一的学生 ID
• C 列是日期。
• G列是课时。

此报告列出了在给定日期或给定日期范围内进行考勤的时间段。但我应该确定何时没有进行考勤。

理想情况下，周一至周四的报告应该列出每个学生的第 1-6 个时段（含）。（没有第 7 个和第 8 个时段）。

周五的报告应该只有每个学生的第 7 和第 8 节课。（没有第 1-6 节课）。（报告可能针对一天或多天范围运行）。

目前，报告似乎以随机顺序重新整理数据，因此我在 Excel 中进行了嵌套排序以使其更易于接受。

Example (after sort): (ID, date, period) in each row
8124577 11/11/2020 1
8124577 11/11/2020 3
8124577 11/11/2020 4
8124577 11/11/2020 6
8124577 11/12/2020 2
8124577 11/12/2020 4
8124577 11/12/2020 5
8124577 11/12/2020 6
8124577 11/13/2020 7
567435 11/11/2020 2
567435 11/11/2020 3
567435 11/11/2020 4
567435 11/11/2020 6
567435 11/13/2020 8

因此，我那双小小的（老）眼睛注意到了以下情况：

• 学生 #8124577 缺席 2020 年 11 月 11 日的第 2 和第 5 节课
• 学生 #8124577 缺席 2020 年 11 月 12 日的第 1 节和第 3 节课
• 学生 #8124577 缺席 2020 年 11 月 13 日（星期五）的第 8 节课
• 学生 #567435 缺席了 2020 年 11 月 11 日的第 1 和第 5 节课
• 学生 #567435 缺席 2020 年 11 月 13 日（星期五）的第 7 节课

学校管理部门希望我目测 100 多名学生的每天 2 次报告数据，是否有公式或其他方法可用于此？

有人能给我指点迷津吗？谢谢。

Answer 1

我一直在研究 Power Query，（您知道他们对拿着锤子的男人有何评价）这里有一种使用它的方法：

• 对行进行排序（您可以从原始数据中执行此操作）
• 按行分组ID并Date保留All Rows
• 创建一个列表每个 ID/日期组合标记的时间段
• 将该列表与另一个列表进行测试，以确定{1..6}星期几的数字是否为1 to 4，或者{7,8}如果星期几的数字为5，则返回缺失值
• 将缺失值列表拆分为用分号分隔的字符串

M 代码

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"date", type date}, {"period", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"ID", Order.Ascending}, {"date", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"ID", "date"}, {{"Grouped", each _, type table [ID=nullable number, date=nullable date, period=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Periods Actual", each Table.Column([Grouped],"period")),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Periods Missed", 
        each if Date.DayOfWeek([date]) >= 1 and 
                Date.DayOfWeek([date]) <= 4 then 
            List.Difference({1..6}, [Periods Actual]) else 
              if Date.DayOfWeek([date]) = 5 then 
            List.Difference({7,8},[Periods Actual]) else 
                "invalid date"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Grouped", "Periods Actual"}),
    #"Extracted Values" = Table.TransformColumns(#"Removed Columns", {"Periods Missed", each Text.Combine(List.Transform(_, Text.From), "; "), type text})
in
    #"Extracted Values"