慢性病季度反馈指标的定义及Excel实现

慢性病季度反馈指标的定义及Excel实现第1页，共47页，2023年，2月20日，星期五季度反馈报告慢性病数据下载慢性病发病监测季度反馈指标

1.估计报告年发病率

2.身份证填报比例

3.初访完成情况（初访完成率、初访及时率）

4.疑似重卡比例

5.随访完成率使用该年季度数据使用历史数据第2页，共47页，2023年，2月20日，星期五数据下载存在的问题前台数据和后台数据下载时段下载速度前台数据下载弹出对话框后要及时保存前台数据保存时，文件扩展名改为.rar第3页，共47页，2023年，2月20日，星期五糖尿病1-3季度数据下载第4页，共47页，2023年，2月20日，星期五糖尿病历史数据下载卡状态分别为可用卡、死亡卡、死卡和失访卡；审核状态为已终审；分别下载可用卡、死亡卡、死卡和失访卡第5页，共47页，2023年，2月20日，星期五估计报告年发病率（1/10万）

发病人数×12×100000

户籍人口数×months◆发病人数：某地区某时间段内区县审核通过的卡片中卡状态为可用卡、死亡卡、失访卡和死卡的数量◆months：月份，如数据为1-3月则months=3，如数据为1-6月则months=6第6页，共47页，2023年，2月20日，星期五估计报告年发病率

数据筛选自动筛选1.筛选出误诊卡、删除卡和重复卡并删除2.审核状态保留区县审核通过第7页，共47页，2023年，2月20日，星期五估计报告年发病率桐乡市1-9月糖尿病发病人数：1824人桐乡市2013年人口数：681342人糖尿病估计年发病率

1824×12÷681342÷9×100000=356.9/10万第8页，共47页，2023年，2月20日，星期五身份证填报比例

身份证号码长度大于14位的卡片数量×100

发病人数第9页，共47页，2023年，2月20日，星期五身份证填报比例插入新的一行在L2录入=IF(LEN(K2)>14,1,“”)后点击enter键拖拽L2至最后一个观测筛选L列中值为1的观测个数第10页，共47页，2023年，2月20日，星期五身份证填报比例桐乡市2014年1-9月糖尿病身份证号码长度大于14位的卡片数为1810人桐乡市1-9月糖尿病发病人数：1824人身份证填报比例：

1810÷1824×100=99.2%第11页，共47页，2023年，2月20日，星期五使用到的Excel函数LEN（）返回文本字符串中的字符数

=len(1234),返回4（1234为4个字符）

=len(a1),返回A1单元格中的字符数。（文本格式中一个空格也为一个字符）

第12页，共47页，2023年，2月20日，星期五使用到的Excel函数IF函数：

IF函数用来判断某个条件是否成立，它包含3个参数。第1个参数是要判断的条件，第2个参数在条件成立时执行，第3个参数在条件不成立时执行。例如，公式=IF(A1=B1,“相等”,”不相等”)包含1个IF函数，IF函数的第1个参数A1=B1是需要判断的条件（A1、B1单元格是否相等）；第2个参数“相等”在条件成立时（A1与B1相等时）执行；第3个参数“不相等”在条件不成立时（A1与B1不相等时）执行；整个公式的含义是：如果A1和B1单元格的值相同，则显示相等，否则显示不相等。第13页，共47页，2023年，2月20日，星期五初访完成率初访完成率（≥90%）分母：发病人数中区县审核时间小于等于本季度第二个月份的病例个数（如1-9月，区县审核日期≤2014-08-31）分子：分母中初访时间为非空的病例个数。第14页，共47页，2023年，2月20日，星期五初访完成率插入新的一列BK2处录入=DATE(LEFT(BJ2,4),MID(BJ2,6,2),RIGHT(BJ2,2))将字符型变换为日期型第15页，共47页，2023年，2月20日，星期五初访完成率筛选区县审核日期≤2014-08-31的卡片个数1533例；第16页，共47页，2023年，2月20日，星期五初访完成率继续筛选初访时间为非空的病例个数1532例。糖尿病初访完成率:1532÷1533×100=99.93%第17页，共47页，2023年，2月20日，星期五使用到的Excel函数LEFT函数RIGHT函数：

LEFT函数是取某个单元格值左面的若干位，它包含两个参数，第1个参数是数值所在的单元格，第2个参数是截取的长度，比如=LEFT(A1,1)是取A1单元格左面第一个字符(a)。而RIGHT函数是取某个单元格右面的若干位，比如=RIGHT(A1,1)是取A1单元格右面1个字符(e)。第18页，共47页，2023年，2月20日，星期五使用到的Excel函数MID函数：

MID函数用来截取某个单元格值的一部分，它有三个参数，第1个是要截取的数值所在的单元格；第2个参数代表从数值的第几位开始截取；第3个参数代表要截取的长度。举例说明如下，A1单元格中的值为“浙江省杭州市下城区”，公式=MID(A1,4,3)表示从第4个字符“杭”开始截取后面3个字符，公式执行的结果为“杭州市”。第19页，共47页，2023年，2月20日，星期五使用到的Excel函数DATE（）：返回代表特定日期的序列号。如果在输入函数前，单元格的格式为“常规”，则结果将设为日期格式。

语法：DATE(year,month,day)

Year参数可以为一到四位数字。MicrosoftExcel将根据所使用的日期系统来解释year参数。

Month代表一年中从1月到12月（一月到十二月）各月的正整数或负整数。

Day代表一月中从1日到31日各天的正整数或负整数。

第20页，共47页，2023年，2月20日，星期五初访及时率初访及时率（≥90%）分母：发病人数中初访时间为非空，录入时间大于等于本季度第一天的病例个数（如第三季度，录入时间≥2014-07-01）分子：分母中初访时间-区县审核时间≤31天的病例个数。第21页，共47页，2023年，2月20日，星期五初访及时率筛选初访时间为非空录入日期转为时间型筛选录入时间

≥2014-07-01第22页，共47页，2023年，2月20日，星期五初访及时率初访时间-区县审核时间≤31天插入新的一列，可以将日期直接相减，即录入=IF((BN6-BK6)<=31,1,"")若需要计算具体的日期间隔录入=BN6-BK6糖尿病初访及时率:639÷645×100=99.1%第23页，共47页，2023年，2月20日，星期五疑似重卡比例疑似重卡数量/发病人数×100查重条件（区县审核通过的卡片，删除重复卡、误诊卡、删除卡）：

1.姓名＋性别＋户籍区县＋出生年+出生月；

2.身份证号（长度大于14位）+户籍区县

3.心脑血管疾病两次发病日期间隔≤28天查重时间：1.糖尿病、恶性肿瘤查重数据为历史数据集；2.心脑血管疾病（脑卒中和冠心病）查重数据集为本季度数据集第24页，共47页，2023年，2月20日，星期五重复卡查询11.按照身份证号码查询重复①把身份证号中的“x”变为大写“X”第25页，共47页，2023年，2月20日，星期五重复卡查询1②按照身份证号码排序数据筛选自动筛选升序或降序第26页，共47页，2023年，2月20日，星期五重复卡查询1③筛选重复观测插入新的一列，在M2处录入=IF(OR(L2=L1,L2=L3),”重复”,””)，然后下拉拖拽第27页，共47页，2023年，2月20日，星期五重复卡查询1④筛选出身份证非空白的重复观测第28页，共47页，2023年，2月20日，星期五使用的Excel函数AND/OR函数：

AND/OR函数为逻辑判断函数,常与IF函数合用,使用的格式为AND(条件1,条件2,…,条件N),OR(条件1,条件2,…,条件N)。第29页，共47页，2023年，2月20日，星期五使用的Excel函数=IF(OR(L2=L1,L2=L3),”重复”,””)

排序后计算某一个观测的某变量与其前后观测某变量的值是否相同，相同则赋值为“重复”，不相同则赋值为空方法二：使用countif函数查重，运算效率低，不建议使用第30页，共47页，2023年，2月20日，星期五重复卡查询22.按照区县+姓名+性别+出生年+出生月查重①计算出生年和出生月第31页，共47页，2023年，2月20日，星期五重复卡查询2②连接查重变量的字符串第32页，共47页，2023年，2月20日，星期五重复卡查询2③按照新生成的字符串排序（升序或降序均可）第33页，共47页，2023年，2月20日，星期五重复卡查询2④筛选重复观测第34页，共47页，2023年，2月20日，星期五重复卡查询2⑤筛选出字符串非空白的重复观测数据——筛选——自动筛选第35页，共47页，2023年，2月20日，星期五使用的Excel函数YEAR：计算日期的年份MONTH：计算日期的月份&：严格地讲，&不是一个函数，它仅仅是一个连接符号，作用是将多个单元格的内容合并起来。例如，公式=A1&B1&C1包含了连接符号&，含义是将A1、B1、C1单元格的值合并到一个单元格里第36页，共47页，2023年，2月20日，星期五疑似重卡比例疑似重卡数量/发病人数×1001.姓名＋性别＋户籍区县＋出生年+出生月；2.身份证号（长度大于14位）+户籍区县删除条件1和条件2中的重复卡片①合并两个条件筛选出的数据集新建xls文件；拷贝条件1筛选的观测；追加拷贝条件2筛选的观测。第37页，共47页，2023年，2月20日，星期五疑似重卡比例②对报告卡编号进行排序（升序或降序均可）第38页，共47页，2023年，2月20日，星期五疑似重卡比例③筛选出报告卡编号重复的观测，对重复的第一个观测不进行标识，其余进行标识。第39页，共47页，2023年，2月20日，星期五疑似重卡比例④筛选计算出空白的观测个数，即为疑似重复卡数量第40页，共47页，2023年，2月20日，星期五随访完成率随访完成率（使用历史卡片数据）分母：

1.区县审核通过的可用卡、死卡、失访卡和死亡卡

2.删除初访时间和随访时间缺失的观测

3.删除初访时间大于等于随访率截点的观测

4.删除卡状态为死卡、失访卡和死亡卡且随访时间小于随访率截点的观测分子：分母中随访时间大于等于随访率截点的观测随访率截点：上一年本季度第一天（如2014年第三季度，随访率截点为2013-07-01）第41页，共47页，2023年，2月20日，星期五随访完成率筛选区县审核通过的可用卡、死卡、失访卡和死亡卡筛选初访时间和最后随访时间非空的观测第42页，共47页，2023年，2月20日，星期五随访完成率计算初访时间大于等于随访率截点的观测：插入一行命名为a，录入公式后拖拽第43页，共47页，2023年，2月20日，星期五筛选出报告卡状态为死卡、失访卡和死亡卡插入一行命名为b，计算并筛选随访时间小于随访率截点的观测第44页，共47页，2023年，2月20日，星期五随访完成率随访完成率分母：1.报告卡状态：筛选全部2.a：筛选13.b：自定义筛选如图第45页，共47页，2023年，2月20日