门禁系统迟到早退数据统计在Excel中如何做？

橒♂蝣 · 发表于 2024-1-4 15:37

JasonTigerHu 发表于 2024-1-4 14:32
厉害厉害!
初步测试了一下，应该是对的。这是做了多久做出来的？我要花时间理解下公式的逻辑 ...

有的问题就是unique数据过多的时候就不显示啦，不知道是电脑性能（平板）的原因还是这个函数自身有限制

JasonTigerHu · 发表于 2024-1-5 14:04

橒♂蝣发表于 2024-1-4 15:37
有的问题就是unique数据过多的时候就不显示啦，不知道是电脑性能（平板）的原因还是这个函数自身有限制

公式可以用的，就是中间有一个笔误吧，
...UNIQUE(A2:A1000)...
改为
...UNIQUE(A2:A10000)...
就ok了
公式研究了2天，刷新了我对Excel这些新公式的认知！
感谢！！

Sean_zhang810 · 发表于 2024-1-5 15:48

真心厉害，这个公式是我逛论坛以来，看过的最牛逼的公式了。根本看不懂逻辑。

橒♂蝣 · 发表于 2024-1-5 17:02

JasonTigerHu 发表于 2024-1-5 14:04
公式可以用的，就是中间有一个笔误吧，
...UNIQUE(A2:A1000)...
改为

你这改成A10000后可以运行吗？

JasonTigerHu · 发表于 2024-1-7 10:26

橒♂蝣发表于 2024-1-5 17:02
你这改成A10000后可以运行吗？

我稍微修改了一下，缩小了一下数据范围，运行速度还蛮快的。另外排版了一下公式格式，方便理清逻辑
=REDUCE({"工号","日期","上班","下班"},
               UNIQUE(A2:A8401),
               LAMBDA(i,j,
                              LET(x,FILTER(B2:B8401,
                                                   (A2:A8401=j)*(ISNUMBER(XMATCH(WEEKDAY(B2:B8401),
                                                                                                                  {2,3,4,5,6}
                                                                                                                  )
                                                                                                )
                                                                              )
                                                   ),
                                    y,UNIQUE(DATE(YEAR(x),MONTH(x),DAY(x))),
                                    VSTACK(i,
                                                   IFERROR(HSTACK(j,
                                                                                 y,
                                                                                 DROP(REDUCE({"",""},
                                                                                                            y,
                                                                                                            LAMBDA(m,n,
                                                                                                                           VSTACK(m,
                                                                                                                                          LET(a,FILTER(x,DAY(x)=DAY(n)),
                                                                                                                                                b,IF(MIN(a)>INT(MIN(a))+8/24+5/(24*60), "迟到","正常"),
                                                                                                                                                c,IF(MAX(a)<INT(MAX(a))+18/24,"早退","正常"),
                                                                                                                                                HSTACK(b,c)
                                                                                                                                                )
                                                                                                                                          )
                                                                                                                           )
                                                                                                            ),
                                                                                                1
                                                                                          )
                                                                              ),
                                                                  HSTACK(j,"","","")
                                                                  )
                                                   )
                                 )
                        )
            )

JasonTigerHu · 发表于 2024-1-7 10:29

Sean_zhang810 发表于 2024-1-5 15:48
真心厉害，这个公式是我逛论坛以来，看过的最牛逼的公式了。根本看不懂逻辑。

分解出来一个一个公式拆分来看，结合任务目标，还是能看懂的，就是比较绕吧。不过看懂是一回事，能写出来，说明平时对公式没少花时间研究

JasonTigerHu · 发表于 2024-1-10 10:18

橒♂蝣发表于 2024-1-5 17:02
你这改成A10000后可以运行吗？

如果我要将工号替换成员工姓名，再按照部门分组统计，可以做到吗？（我是指通过一步来完成）

早做完，不加班 · 发表于 2024-1-10 11:29

用power query处理考勤数据 let 源 = Table.ExpandTableColumn( Table.NestedJoin( Table.AddColumn( Table.AddColumn( Table.TransformColumnTypes( Excel.CurrentWorkbook(){[Name = "考勤数据"]}[Content], {{"日期时间", type datetime}} ), "日期", each DateTime.Date([日期时间]), type date ), "时间", each DateTime.Time([日期时间]), type time ), {"工号"}, 人员名单, {"工号"}, "人员名单", JoinKind.LeftOuter ), "人员名单", {"姓名"}, {"姓名"} ) in 源上班打卡 let 源 = Table.Group(考勤数据, {"工号", "日期"}, {{"上班打卡时间", each List.Min([时间]), type time}}) in 源下班打卡迟到早退判断 let 计算 = Table.AddColumn( Table.AddColumn( Table.ExpandTableColumn( Table.NestedJoin(上班打卡, {"工号", "日期"}, 下班打卡, {"工号", "日期"}, "下班", JoinKind.LeftOuter), "下班", {"下班打卡时间"}, {"下班打卡时间"} ), "迟到判断", each if [上班打卡时间] <= #time(8, 0, 0) then "正常" else "迟到" ), "早退判断", each if [下班打卡时间] <= #time(18, 0, 0) then "早退" else "正常" ) in 计算

早做完，不加班 · 发表于 2024-1-10 11:30

用power query处理

考勤数据
let
  源 = Table.ExpandTableColumn(
Table.NestedJoin(
   Table.AddColumn(
      Table.AddColumn(
      Table.TransformColumnTypes(
         Excel.CurrentWorkbook(){[Name = "考勤数据"]}[Content],
         {{"日期时间", type datetime}}
      ),
      "日期",
      each DateTime.Date([日期时间]),
      type date
      ),
      "时间",
      each DateTime.Time([日期时间]),
      type time
   ),
   {"工号"},
   人员名单,
   {"工号"},
   "人员名单",
   JoinKind.LeftOuter
),
"人员名单",
{"姓名"},
{"姓名"}
  )
in
  源

上班打卡
let
源 = Table.Group(考勤数据, {"工号", "日期"}, {{"上班打卡时间", each List.Min([时间]), type time}})
in
源
下班打卡

迟到早退判断
let
计算 =
  Table.AddColumn(
Table.AddColumn(
   Table.ExpandTableColumn(
      Table.NestedJoin(上班打卡, {"工号", "日期"}, 下班打卡, {"工号", "日期"}, "下班", JoinKind.LeftOuter),
      "下班",
      {"下班打卡时间"},
      {"下班打卡时间"}
   ),
   "迟到判断",
   each if [上班打卡时间] <= #time(8, 0, 0) then "正常" else "迟到"
),
"早退判断",
each if [下班打卡时间] <= #time(18, 0, 0) then "早退" else "正常"
  )
in
计算

早做完，不加班 · 发表于 2024-1-10 11:31

用power query处理

考勤数据
let
  源 = Table.ExpandTableColumn(
Table.NestedJoin(
   Table.AddColumn(
      Table.AddColumn(
      Table.TransformColumnTypes(
         Excel.CurrentWorkbook(){[Name = "考勤数据"]}[Content],
         {{"日期时间", type datetime}}
      ),
      "日期",
      each DateTime.Date([日期时间]),
      type date
      ),
      "时间",
      each DateTime.Time([日期时间]),
      type time
   ),
   {"工号"},
   人员名单,
   {"工号"},
   "人员名单",
   JoinKind.LeftOuter
),
"人员名单",
{"姓名"},
{"姓名"}
  )
in
  源

上班打卡
let
源 = Table.Group(考勤数据, {"工号", "日期"}, {{"上班打卡时间", each List.Min([时间]), type time}})
in
源
下班打卡

迟到早退判断
let
计算 =
  Table.AddColumn(
Table.AddColumn(
   Table.ExpandTableColumn(
      Table.NestedJoin(上班打卡, {"工号", "日期"}, 下班打卡, {"工号", "日期"}, "下班", JoinKind.LeftOuter),
      "下班",
      {"下班打卡时间"},
      {"下班打卡时间"}
   ),
   "迟到判断",
   each if [上班打卡时间] <= #time(8, 0, 0) then "正常" else "迟到"
),
"早退判断",
each if [下班打卡时间] <= #time(18, 0, 0) then "早退" else "正常"
  )
in
计算

		自动登录	找回密码
密码			免费注册

[求助] 门禁系统迟到早退数据统计在Excel中如何做？