数据管理和分析课件及相关文件

基于Excel的

医学数据

管理与分析

数据管理的基本原则

相关函数的应用常用的数据分析方法统计图表的制作Excel的医学应用范围数据管理数据编辑数据交换数据筛选统计分析统计函数透视分析数据汇总图表制作自定义功能宏VBAExcel强大之处工作簿（活頁簿，Workbook）用于存储处理数据的文件，一个Excel文件就是一个工作簿基于97-2003版本的扩展名（副檔名）为xls2007版本之后新类型工作簿的扩展名为xlsx兼容性问题早期版本Excel安装兼容包才能打开新版本的工作簿新版本另存为兼容97-2003的工作簿，可能会损失数据和函数工作表（Worksheet）

显示在工作簿窗口中的表格一个工作簿最多有255个工作表单元格（儲存格,Cell）存储数据的基本单元列（欄，column）用字母按顺序表示（16,384列，A-XFD，03版本共256列，A-Ⅳ）行（列，row）用数字按顺序表示（1,048,576行，03版本有65,536行）单元地址——单元格所处位置的列号和行号的组合符号。例：A1，B10活动单元格——黑框标注的单元格。也称为“当前单元格”。任何时候只有一个活动单元格单元格区域多个单元格组成的矩形区域单元格区域的标识方法：单元格区域左上角和右下角的单元地址组合，中间用英文冒号（:）分隔如A1:C5多个单元格区域则用英文逗号连接如A1:C5,E1:F5常用于公式中的单元格引用原始数据表整理加工数据源表需要妥善保管计算分析表工作底稿结果报告表数据整理基本流程理想的数据源表一个工作表有满足分析需要的字段单层表头没有空行空列没有合并单元格一格一属性数据和数据格式正确数据类型数值型数据用于计算的数据，如住院费，身高等数据用于标识的数据，如住院号，不用于计算用于分类的数据，如用1表示男性，用于汇总、排序文本型数据用于分类的数据，如男/女，科室过长的数值型数据，如身份证号日期型数据可用于计算和筛选逻辑型数据用于判断数值型数据数据在单元格中以右对齐形式显示（默认格式）数据太大或太小时，Excel在单元格中将以科学计数法显示。当列宽太小时，可能单元格会显示“###”，则表明没有足够的宽度“设置单元格格式（儲存格格式）”可以设置各种数值格式和小数位数（与保留小数位数的函数不同。函数是改变了数据）字符型数据字符在单元格中以左对齐形式显示（默认方式）当输入数字组成的字符串时，可在数字字符串前加前导符单引号‘

或将字符串用双引号括起并以等号作前导符。如：输入字符串“025”，应键入’025或=“025”单元格内换行Alt+Enter（很多软件的换行快捷键是Crtl+Enter）日期型数据特殊的数值型数据与系统的“区域（地區）”设置中日期格式设置相关简体中文系统通常是：年/月/日或年-月-日1900年1月1日（或者1904-1-2(Mac)）相当于数字1当两个日期型数据相减时可得出相隔天数一个日期型数据与一个数据相加时可得出另一个日期型数据。时间型数据格式是时:分:秒。快捷输入方法Ctrl+;输入当前日期Ctrl+Shift+;输入当前时间逻辑型数据描述关系成立与否，常作为公式的中间层数据，如IF函数。很少会直接出现在最终结果中TRUE、非零（真）如一个单元格的内容为=5>3，则返回TRUEFALSE、零（假）如一个单元格的内容为=5<3，则返回FALSE填充相同数据选择区域，输入数据，按Ctrl+Enter填充（填滿）命令向下填充 Ctrl+D向右填充 Ctrl+R填充柄“”拖动当鼠标移动到单元格的右下角时出现可用于各种复制，是最有用的技术之一双击填充柄，可快捷复制数据到下方的单元格填充有规律的数据填充柄左键拖动填充填充柄右键拖动填充填充（填滿）命令自定义序列自訂清單同时按下Ctrl键选中可体现规律的多个单元格填充规则初始值为纯字符或者纯数字——复制数据等差或等比数列——可按序列填充初始值为字符数字混合体——字符不变，数字变化初始值为已定义的自动填充序列中一员——填充自定义序列（自訂清單）数据有效性（数据验证，資料驗證）可以设置允许输入的数据类型、范围将数据限制为列表中的预定义项将数字限制在指定范围之内将日期时间限制在某一范围之内限制文本字符数根据其他单元格中的公式或值验证数据有效性可结合“保护工作表”实现可控数据输入选定数据区域如果工作表包含数据，则按Ctrl+A将选择当前区域。再次按Ctrl+A将选择整个工作表选定整个工作表：单击工作表左上角的“全选”按钮选定一个矩形区域：矩形区域范围较大时：按Shift键，选定区域选定不相邻的矩形区域选取第一个区域，方法同前按住Ctrl键同时进行第二个区域的选取重复第二步，将所需的不相邻区域全部选定，再松开Ctrl键。在快速构建图形的时候非常有用选定数据区域按Ctrl+End可移至工作表最右下角的那个单元格前提是有数据或者曾经有过数据按Ctrl+Shift+箭头键可将单元格的选定范围扩展到活动单元格所在列（欄）或行（列）中的最后一个非空单元格可多次按动，再次按动则将选定范围扩展到下一个非空单元格数据的浏览方法或者叫分割可用于分别浏览工作表的不同位置的数据用于固定部分内容主要用于大量数据浏览时，可查看所需的行列（列欄）标题查找和替换（尋找及取代）与Word对应功能类似，也可以查找特定格式的数据特色功能可选择区域以指定范围查找全部（全部尋找，可看到全部查找结果）适用于快捷、批量转换数据（如将字符型数据与数值型数据的互换）添加/去除某些特定字符（如数据中的文字）查找异常数据圈施无效数据（圈選錯誤資料）可用于输入后进行审核，但需要先设置数据有效性（資料驗證）可用于发现异常数据，但仅能标识前255个条件格式（設定格式化的條件）快速浏览便可识别数值中存在的差异突显目标数据（醒目提示儲存格規則）公式及常见函数应用公式常数函数单元格引用运算符公式是对工作表中数据进行分析计算的表达式以英文“=”开始运算符算术运算符+-*/%（百分比）^（乘方）比较运算符=><>=<=<>文本运算符&注意优先级和括号！！！公式的单元格引用——引用地址类型相对地址用列（欄）号、行（列）号作为单元格标识。例：B6，A4，C5:F8绝对地址在列（欄）号行（列）号前都冠以“$”符号的单元格标识。例：$B$6,$A$4:$C$10混合地址用列（欄）号$行（列）号或$列（欄）号行（列）号标识单元格。例：A$1，$B6，C$1:E$5公式的单元格引用相对引用默认的单元格引用方式，如=INT(B2)。实际上是记录了公式所在单元格与被引用单元格之间的偏移量特点是公式复制、移动时，被引用单元格的地址自行调整，但偏移量不发生变化公式引用了特定位置的单元格，如=INT($B$2)特点是在公式复制、移动时，被引用单元格的地址不会改变。适用于引用计算过程中固定不变的单元格/单元格区域绝对引用定义名称可将某个单元格或者单元格区域定义为特定的名称方便引用：相当于绝对引用；无需理解具体的单元格位置；并可方便定位便于理解：可定义有意义的名称便利公式的修改：一旦在公式中引用了名称，通过修改名称对应的单元格，便可完成所有引用了名称的公式的修改工作方法：如定义某区域为住院天数，便可如此引用：=max(住院天数)便可求得最长住院天数公式或数据的复制公式的复制或移动，所引用的单元格会随引用地址类型的不同，相应变化或固定不变。复制后的公式，会因新位置的缘故，或出现错误选择性粘贴（選擇性貼上）选择值或者公式可保留格式可叠加运算可进行转置（即行列（列欄）互换）公式的设计方法由于公式是可以嵌套和叠加的。因此，在解决一个问题时，可以利用公式分解的思路。

通过多个过渡的单元格，得到最终的结果。确保正确后，

再合并公式

如已知出生日期在A1单元格，如何求年龄可在A2先求当前的年，=year(now())在A3求出生的年份，=year(A1)在A4获得大概的年龄=A2-A3

也可以在A4直接写=year(now())-year(A1)公式的分析方法追踪单元格（追蹤參照）可了解引用单元格的来源和发展引用（前導）和从属（從屬）单元格公式求值（評估值公式）可调试公式，了解公式每一步的运作方式和计算结果Excel函数预定义的内置公式数学与三角函数日期与时间统计工程财务信息逻辑查找和引用文本数据库常见数学函数INT(数值)——向下取整ROUND(数值,执行四舍五入的位数)——四舍五入SUM(数值,数值……)——求所有数据之和RAND()生成0-1范围上的随机数，无参数可用于生成随机分组的数据，用于医学实验设计先用RAND函数生成随机数再复制值，并对值数据排序根据分组数目选择相应的数据常见文本函数LEN(文本)——返回文本字符数LEFT/RIGHT(文本,字符数)MID(文本,起始位置,字符数)——返回从指定位置开始的特定数目的字符常见日期/时间函数TODAY()，NOW()返回当前日期/时间，无参数DATE(year,month,day)在年、月、日为变数的公式中非常有用datedif(早的日期,晚的日期,”日期参数”)求两个日期数据之间的差异有用的早期函数，现在可以用，但没有公开参数：y表示年，m表示月，d表示日常见逻辑函数AND、OR、NOTIF用于逻辑判断，还可以嵌套多个IF函数用途：序列文本的数值转换，特殊标记（如筛选标记）的生成IF(逻辑判断式，当逻辑判断为真时执行的命令，当逻辑判断为假时执行的命令）IF(A1=1，10，5）当A1单元格的值为1时，结果为：10当A1单元格的值不为1时，结果为：5常用的统计函数COUNT计算日期和数值型单元格的个数COUNTA计算非空单元格的个数字符型单元格也会计入AVERAGE计算数值数据的平均值常用于对称分布的资料MEDIAN计算数据的中位数适用于描述非对称分布的资料描述资料离散趋势的函数MAX/MIN(array)array：整个数据区域计算数值的最大/最小值PERCENTILE(array,k)计算百分位数k位于0到1之间RANK(待排名的数据,整个数据,排序方法)计算位置(排位)，也可用于统计学中的秩和检验描述资料离散趋势的函数用于反映数据的变异程度VARP/VAR

计算总体/样本方差（variance）VAR.P/VAR.S是新版本的函数STDEVP/STDEV

计算总体/样本标准偏差（标准差，standarddeviation）STDEV.P/STDEV.S是新版本的函数P：Population，S：Sample查找函数职场必须了解的函数VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])垂直方向查找某个数据，并返回指定的数据参数含义：查找值，查找范围，查找列（欄）数，精确匹配false/0或者近似匹配true/1根据查找值参数，在查找范围的第一列（欄）搜索查找值，找到该值后，则返回值为：以第一列（欄）为准，往后推数查找列数值的这一列（欄）所对应的值HLOOKUP水平方向查找某个数据，并返回指定的数据常用的数据分析方法及工具数据分析的基本注意事项建立一个符合要求的数据列表切勿对数据列表中的某一部分单独操作，以免破坏数据分析时无须选中整个数据列表，仅需要选中某一个单元格即可。如果不能识别整个区域，检查数据列表是否不合要求最好有一列（欄）顺序编号的字段，如ID字段，以便于标识各条数据，以及恢复原有状态某些时候，可利用现有数据构建辅助列（欄）来帮助完成分析，通常可使用公式实现数据排序排序的目的是让数据按照某种规则有顺序、有规律的排列升序（A-Z）：从小到大排列降序（Z-A）：与升序相反。按从大到小排列主要关键字（排序方式）：选定的第一个排序字段，按其升序或降序排列，这个字段称为主关键字次要关键字（次要排序方式）：当某些数据要按一列或一行中的相同值进行分组时，可按已有关键字排序的前提下，再次排序所依据的字段。如一个足球联赛的积分榜升序排序原则数值型按数值从小到大字符型英文字段按ASCII码，Ａ－Ｚ中文字段字母——拼音笔画——笔画日期型按日期先后逻辑型数据：“FALSE”，“TRUE”还可以按照单元格颜色，字体颜色或图标排序可以使用自定义序列（自訂清單）排序注意事项一般是对列（欄）排序，通过设置也可以对行（列）进行排序要按部分值进行排序，例如，部件号代码(789-WDG-34)，可拆分数据，再用拆分列作为排序依据可使用文本函数，或者分列（資料剖析）切记一般情况下不要对数据列表中的某一列（欄）单独排序数据筛选原始数据往往是没甄选过的全面数据，也就意味着不会完全符合分析的要求。因此，需要进行筛选分析，以挑选出符合要求的数据，从而实现缩小分析范围，剔除无效数据的目的两种方法：筛选，高级筛选（進階篩選）有时候需要构建辅助列以帮助筛选数据筛选——快速选出所需的数据条件限制数值的各种比较条件数据的某个范围文本的各种包括或排除条件该字段的某一值自定义：根据用户自己的条件筛选多个列（欄）的条件之间是“与（AND）”的关系，也就是所有条件都要满足高级筛选（進階篩選）高级筛选按用户自己在选定区域内定义的复杂条件进行数据筛选操作,适合于有多个数据或者多种条件，如从备选基因列表中筛选目标基因条件区域（準則範圍）条件标记条件性别总费用性别总费用男>12000女<12000条件区域（準則範圍）的约定两条件在同一行，表示“与（AND）”关系，也就是都需要满足两条件在不同行，表示“或（OR）”关系，也就是满足其中之一即可空白为无条件限制多条件的运算顺序：与→或特别注意：条件区中的字段名格式必须与原数据区的字段名一致，可以使用“复制/粘贴”以确保一致性别总费用性别总费用男>12000女<12000既有和也有或条件区域（準則範圍）或（OR）与（AND）数据汇总所谓汇总，顾名思义，就是将数据汇集在一起，按照其中的类别进行统计分析汇总好的数据是很多统计图表的基础两种方法分类汇总（小計）数据透视表（樞紐分析表，更加方便好用）汇总的准备一个或多个用于分类的字段（分类变量）有序变量，如疾病严重程度，文化程度名义变量，如职业、分娩方式如果是身高体重血压之类的数据（数值变量），则需要进行分组转换为有限的分类变量可使用IF函数，生成对应的分类变量也可以使用工具自带的分组工具分类汇总（小計）用途及优点可根据某一分类字段对其他数据进行统计汇总多种汇总方式可对工作表进行分级显示前提：某一分类变量字段，如性别。需要对该字段进行排序，升降序皆可。否则结果无意义数据透视表（樞紐分析表）强大方便的分类统计、汇总数据的交互式表格可发现和处理缺失数据和异常数据字段处理原则一个列标签（欄標籤），常用于放置因变量（结局）多个行标签（列標籤），常放置放置自变量（影响因素）可对数值变量进行分组复制透视表结果应使用选择性粘贴（選擇性貼上）图表制