excel考勤表如何转换?

发布网友 发布时间：2022-04-26 15:07

我来回答

共5个回答

热心网友 时间：2023-10-11 00:48

借 百度网友“我来也xsczd”的公式，作一些小的调整，可以得到题要求的表二如下图示：

日期的公式为“=CONCATENATE("4/"&Sheet1!$A$1)”

姓名的公式为“=OFFSET(Sheet1!$A$1,2*ROW()-5,10)&""”

上班打卡的公式为“=LEFT(OFFSET(Sheet1!$A$1,2*ROW()-4,0),5)&""”

下班打卡的公式为“=IF(LEN(OFFSET(Sheet1!$A$1,2*ROW()-4,0)),RIGHT(OFFSET(Sheet1!$A$1,2*ROW()-4,0),6)&"",RIGHT(OFFSET(Sheet1!$A$1,2*ROW()-4,0),5)&"")”

注1：表二每当以上述一组公式下拉出现姓名空白时（假定考勤表所有打卡数据的姓名不为空），则需要重新设定公式的参数值。

日期的公式需要依次调整公式“=CONCATENATE("4/"&Sheet1!$A$1)”中“!$A$1”的“A”为“B、C、......”；

姓名的公式需要依次调整公式“=OFFSET(Sheet1!$A$1,2*ROW()-5,10)&""”中“2*ROW()-5”的“5”使之与“2*ROW()”的差等于“1”；

上班打卡的公式需要依次调整公式“=LEFT(OFFSET(Sheet1!$A$1,2*ROW()-4,0),5)&""”中“2*ROW()-4,0”的“4”使之与“2*ROW()”的差等于“2”，“0”为日期与 1 的差；

下班打卡的公式需要依次调整公式“=IF(LEN(OFFSET(Sheet1!$A$1,2*ROW()-4,0)),RIGHT(OFFSET(Sheet1!$A$1,2*ROW()-4,0),6)&"",RIGHT(OFFSET(Sheet1!$A$1,2*ROW()-4,0),5)&"")”中“2*ROW()-4,0”的调整上班打卡一致。

注2：调整好后整组公式一起下拉，当出现姓名空白时，重复公式参数值的调整，直至一个月的数据全部完成。

注3：当一个月的表完成后，再将第二个月的考勤表数据粘贴在原表一的相同位置，表二就可以直接显示结果了（理论上成立，未实测）。

注4：公式中的“&" "”应该可以省去（实测可以，考虑保持原公式所以仍保留）

热心网友 时间：2023-10-11 00:48

原数据位置如下图所示，放在Sheet1工作表中。

其中，“工号......"行，作为一个字符串放在A2一个单元格中。如果工号和姓名是分开放的，则公式还可以简单些。

所做的新表数据位置如下图所示。

年月不是必需的，但有了它，可以防止日期超限

设置A列格式如图所示

在A3中输入公式：=DATE(G$1,H$1,OFFSET(Sheet1!A$1,0,INT((ROW()-ROW($3:$3))/(COUNTA(Sheet1!A:A)-1)*2)))

在B3中输入公式：=MID(SUBSTITUTE(MID(OFFSET(Sheet1!A$2,(MOD(ROW()-ROW($3:$3),(COUNTA(Sheet1!A:A)-1)/2))*2,0),45,20)," ",""),4,LEN(SUBSTITUTE(MID(OFFSET(Sheet1!A$2,(MOD(ROW()-ROW($3:$3),(COUNTA(Sheet1!A:A)-1)/2))*2,0),45,20)," ",""))-3)

在C3中输入公式：=IFERROR(LEFT(OFFSET(Sheet1!$A$3,MOD(ROW()-ROW(Sheet1!$3:$3),(COUNTA(Sheet1!A:A)-1)/2)*2,DAY(A3)-1),5)&" "&MID(OFFSET(Sheet1!$A$3,MOD(ROW()-ROW(Sheet1!$3:$3),(COUNTA(Sheet1!A:A)-1)/2)*2,DAY(A3)-1),13,5),"")

在D3中输入公式：=IFERROR(MID(OFFSET(Sheet1!$A$3,MOD(ROW()-ROW(Sheet1!$3:$3),(COUNTA(Sheet1!A:A)-1)/2)*2,DAY(A3)-1),7,5)&" "&MID(OFFSET(Sheet1!$A$3,MOD(ROW()-ROW(Sheet1!$3:$3),(COUNTA(Sheet1!A:A)-1)/2)*2,DAY(A3)-1),19,5),"")

如果不想保留工号和部门信息，可以忽略6、7

E3公式：=MID(OFFSET(Sheet1!A$2,MOD(ROW()-ROW($3:$3),(COUNTA(Sheet1!A:A)-1)/2)*2,0),4,FIND(" ",OFFSET(Sheet1!A$2,MOD(ROW()-ROW($3:$3),(COUNTA(Sheet1!A:A)-1)/2)*2,0))-4)

F3公式：=SUBSTITUTE(SUBSTITUTE(RIGHT(OFFSET(Sheet1!A$2,(MOD(ROW()-ROW($3:$3),(COUNTA(Sheet1!A:A)-1)/2))*2,0),10)," ",""),"部门：","")

选中A3:F3，下拉复制，直到A列的月份开始减少

追答那就需要你把各文字信息的单元格表示清楚才行啊。
B3的公式就是为了从一行文字中获得姓名。
下面详细解释一下这个公式：
最外层公式：MID(无空格姓名字串,从第4个字符开始,取若干个字符)

先解释一下“无空格姓名字串”是怎么计算的：
无空格姓名字串=SUBSTITUTE(带空格姓名字串," ","")，将带空格字串中的空格全部删除
带空格字串=MID(整行字串3,45,20)，从整行字串中的第45个字符开始，取20个字符
整行字串=OFFSET(Sheet1!A$2,偏移行数,0)，以Sheet1工作表的A2单元格为基准偏移若干行。
偏移行数=(Sheet2工作表姓名的行数 )*2，Sheet1工作表姓名的行数为隔行，比Sheet2工作表姓名的行数多一倍
Sheet2工作表姓名的行数
=MOD(Sheet2工作表中去除表头后的行数, Sheet1工作表中的行数/2)
MOD(被除数，除数)这个函数计算余数。用在这里，使得姓名可以被重复。
Sheet2工作表中去除表头后的行数=ROW()-ROW($3:$3)
Sheet1工作表中的行数=COUNTA(Sheet1!A:A)，计算出A列一共有多少行
再来解释一下“取若干个字符”：
=LEN(无空格姓名字串)-3，即：比“无空格姓名字串”少3个字符（“姓名：”）
你可以根据自己表格中有关姓名信息所在单元格的位置和内容进行筛选组合。
例如，在E、F、G合并列中有“姓名：张三”，并没有空格。
则公式可以是：=MID(OFFSET(Sheet1!E$2,(MOD(ROW()-ROW($3:$3),(COUNTA(Sheet1!A:A)-1)/2))*2,0),4,LEN(OFFSET(Sheet1!E$2,(MOD(ROW()-ROW($3:$3),(COUNTA(Sheet1!A:A)-1)/2))*2,0))-3)
也可以是：=RIGHT(OFFSET(Sheet1!E$2,(MOD(ROW()-ROW($3:$3),(COUNTA(Sheet1!A:A)-1)/2))*2,0),LEN(OFFSET(Sheet1!E$2,(MOD(ROW()-ROW($3:$3),(COUNTA(Sheet1!A:A)-1)/2))*2,0))-3)
部门和工号的公式也可以照此修改

热心网友 时间：2023-10-11 00:49

Excel怎样快速批量把考勤记录表重复时间删除

Excel怎样快速批量把考勤记录表转成三列表

热心网友 时间：2023-10-11 00:50

怎么又是这样的？

问题需要说清楚工作表名称啊！

原始的是表格是   Sheet1  为名称工作表吧？

则可在另外的第1个工作表的中输入公式：

A3=OFFSET(Sheet1!$A$1,2*ROW()-5,2)&""

B3=OFFSET(Sheet1!$A$1,2*ROW()-5,10)&""

C3=LEFT(OFFSET(Sheet1!$A$1,2*ROW()-4,COLUMN()-3),5)&""

A3、B3单元格公式复制并下拉，C3单元格公式复制右拉和下拉，形成一个签到时间表。

然后，再在另外的第2个工作表的中输入公式：

A3=OFFSET(Sheet1!$A$1,2*ROW()-5,2)&""

B3=OFFSET(Sheet1!$A$1,2*ROW()-5,10)&""

C3=RIGHT(OFFSET(Sheet1!$A$1,2*ROW()-4,COLUMN()-3),5)&""

A3、B3单元格公式复制并下拉，C3单元格公式复制右拉和下拉，形成一个签退时间表。

最后，你就可以根据新生成的2个时间表，进行数据核算与考核了…………

热心网友 时间：2023-10-11 00:50