大家好啊,好久不见!今天咱们分享一个数据清洗的示例。
每天看病例通报,却又看不到各区的病例数,索性自己来一波简单的统计。
效果如图:
怎么统计的呢?肯定不是一个一个数的。
思考2分钟。。。
以下方法所用版本为WPS表格,操作步骤如下:
【1】选中病例内容,复制粘贴到WPS表格中。
【2】在K列输入区县对照表。
在B3单元格中输入以下公式,来提取A列内容中的区县名称:
=IFERROR(LOOKUP(1,-FIND($K$3:$K$11,A3),$K$3:$K$11),"")
这是一个按关键字查询的模式化公式。
如果包含对照表中的区县名称,FIND函数返回表示位置的数字,否则返回错误值。再将FIND函数的结果加上负号,将数字部分变成负值,得到由负数和错误值组成的内存数组。
最后用1作为查询值,在内存数组中查询到最后一个负数所处的位置,并返回K3:K11单元格区域中对应位置的内容。
【3】在C3单元格输入以下公式,根据A2单元格中的内容提取“病例”后的数字部分。
=IF(B3<>"",MID(A2,3,10),"")
公式从A2单元格的第三位开始,提取出10个字符,这里的10,可以是一个比较大的任意数字。
【4】在D3单元格输入以下公式,根据C列的提取结果,计算出病例实际数。
=IF(C3="","",IF(COUNTIF(C3,"*-*"),ABS(EVALUATE(C3))+1,1))
先使用IF函数判断C列是否为空,如果C列为空时,公式返回空文本。
再用COUNTIF函数统计C3单元格中“-”的个数,如果包含间隔符号“-”,COUNTIF函数返回1,否则返回0。结果作为IF函数的第一参数。
当IF函数的第一参数为不等于0的数值时,相当于逻辑值TRUE。此时执行EVALUATE函数部分,来计算C3单元格中的文本算式“1-7”,再用ABS函数取绝对值,最后加上1进行修正。
【5】求和统计
在L3单元格输入以下公式,计算各个县区的人数:
=SUMIF(B:B,K3,D:D)
提示:EVALUATE函数专门用于计算文本算式,在WPS表格中可以直接在单元格输入,如果你使用的是Excel,需要先将公式定义成名称再使用。
如果你知道具体的步骤和注意事项,可以在留言区分享给小伙伴们。
作者:赵中山
编辑:老祝