前言

在群里经常遇到有人发布通知后,会在后面加一句“收到请回复“,然后大家就各种“收到”“OK”回复一大串。发通知的人需要核对,看哪些人回复了,哪些没回复。当涉及的人比较多时,根据回复信息逐个核对,就比较麻烦。其实用excel可以一键核对,快速找出那些没有回复的人,一起来看看。

案例

超实用你不能不会的10个excel技巧(收到请回复一个你很可能会用到的excel实用干货)(1)

群里通知要求截图中B列单位收到后回复,群里备注名是规定了的,项目名称后面接真实姓名,当然也有个别不按规定备注名称的。表中C、D列准备填入已回复项目的确认信息和确认人,G列设置了对回复情况的汇总统计,未回复名单从G3单元格开始往下排。

下图是群里收到的部分回复信息:

超实用你不能不会的10个excel技巧(收到请回复一个你很可能会用到的excel实用干货)(2)

总共21个项目,回复了的已有十好几个,但到底哪几个还没回复,一个个的核对还真有点麻烦。我们通过excel函数和VBA对表格进行编辑设置后,得到快速查找方法为:一次性选定群内所有回复消息,中间夹杂着其他消息也不要紧,右键复制消息,在表格中单击C1单元格,结果见下图:

超实用你不能不会的10个excel技巧(收到请回复一个你很可能会用到的excel实用干货)(3)

单击后表格中哪些项目已回复,何时何人回复的何内容,都一清二楚,G列也统计出了未确认的项目。表格通用性挺好,只需修改B列名称即可运用到别的群。来看看实现过程。

VBA代码实现回复信息自动填入表中的C、D列

分两步实现:先对剪贴板里的内容先进行处理形成数组,然后根据数组内容和B列信息进行匹配填写。

1、数据处理代码将从群里复制的信息进行处理后,放到数组中。

a.GetFromClipboard:获得剪贴板内容,

Set re = CreateObject("VBScript.Regexp")

re.Global = True: re.Pattern = "[\r\n] "

s = re.Replace(a.GetText, vbCrLf):用正则表达式将获取的剪贴板的内容中附带的一些换行、回车符号统一替换成VBA识别的换行符。

b = Split(s, vbCrLf):以换行符为分隔符,将获取的剪贴板内容分割成数组放到数组变量b中。此时,数组b中的内容就是:{回复人1回复日期;回复信息;回复人2回复日期;回复信息;......回复人n回复日期;回复信息;}

2、写入C、D列的代码根据数组b中的关键字与表中B列名称进行匹配,对应后就将相应信息填入C、D列。

For k = 2 To [b30000].End(3).Row

For j = 1 To UBound(b)

If b(j) = "收到" Or b(j) = "/OK" Then:设置当回复信息为收到或OK手势图时,才写到表格中,是其他无关信息时不写入。

If InStr(b(j - 1), Cells(k, 2)) Or InStr(b(j - 1), Cells(k, 5)) And Cells(k, 5) <> "" ThenCells(k, 2).Font.Color = RGB(255, 0, 0):当匹配吻合时,设置已回复信息的B列文字变为红色

Cells(k, 3) = b(j) :将回复的信息写入C列

Cells(k, 4) = Replace(b(j - 1), Cells(k, 2), ""):将回复人和时间写入D列

GoTo 30

End If

End If

Next

30 Next

函数公式实现G列汇总统计

G1单元格公式:=COUNTA(D2:D22):统计D列第2到22行间所有不为空的单元格数量。得到已经回复的数量。

G2单元格公式:=COUNTA(A2:A22)-G1:得到未回复的数量。

G3单元格公式:

=IFERROR(INDEX($B$2:$B$22,SMALL(IF($D$2:$D$22<>"",4^9,ROW($D$2:$D$22)-1),ROW(A1))),""),数组公式,CTRL SHIFT ENDTER同时按,下拉填充。

总结

本文主要用到了对剪贴板的操作,群里复制的数据有些附带信息的删除处理比较麻烦,用到了正则表达式。G3单元格的项目重列公式有点难度。另外功能实现的前提是群员的备注名按固定的规律起名,假如某个没有修改备注名的也发个“收到”就不会被纳入进来,不过这种情况就是人工处理一般也不知道该算在哪。觉得有用的,欢迎收藏转发!收到请回复哦!

,