《Excel数据处理与可视化》（第二版） 课件 韩春玲 第1部分 数据采集整

第1部分 数据采集整理----1.1数据录入与基本设置1.1 数据录入与基本设置1.1.1使用特殊符号自定义单元格格式1．“0”数字占位符数字“0”代表占位符，如果单元格的内容大于指定占位符，则显示实际数字；如果小于占位符的数量，则用0补足。1.1 数据录入与基本设置1.1.1使用特殊符号自定义单元格格式2．“#”数字占位符“#”数字占位符，只显有意义的零，不显示无意义的零。小数点后数字如大于“#”的数量,则按“#”的位数四舍五入。1.1 数据录入与基本设置1.1.1使用特殊符号自定义单元格格式3．“?”数字占位符“?”数字占位符，在小数点两边为无意义的零添加空格，以实现按小数点对齐。1.1 数据录入与基本设置1.1.1使用特殊符号自定义单元格格式4．“,”千分位分隔符“,”千分位分隔符。在数字中，每隔三位数加进一个逗号，也就是千位分隔符，以免数字太多不好读取，1.1 数据录入与基本设置1.1.1使用特殊符号自定义单元格格式5.“@”文本占位符使用单个@，作用是引用固定文本。使用多个@，则可以重复文本，1.1 数据录入与基本设置1.1.1使用特殊符号自定义单元格格式6．“!”原样显示后面的符号在自定义格式中，“”、#、？等都是有特殊意义的字符，如果想在单元格中显示这些字符，需要在前符号前加“!”，，1.1 数据录入与基本设置1.1.1使用特殊符号自定义单元格格式7．“*”重复后面的字符重复显示*后面的字符，直到充满整个列宽。1.1 数据录入与基本设置1.1.1使用特殊符号自定义单元格格式8．颜色显示符代码：[红色];[蓝色];[黑色];[绿色]，显示结果为正数为红色，负数显示蓝色，零显示黑色1.1 数据录入与基本设置1.1.2设置数字以小数点对齐，使数值大小一目了然第1步：设置数据右对齐第2步：设置单元格格式，自定义单元格格式为：#.0?????，1.1 数据录入与基本设置1.1.3单元格数据换行第1种方法：自动换行。第2种方法：Alt+Enter。1.1 数据录入与基本设置1.1.4设置不能隔行隔列填写数据1．打开“数据验证”2．设置验证条件3、设置出错警告1.1 数据录入与基本设置1.1.5设置倾斜列标签单击“开始”菜单的“对齐方式”功能区中的“方向”按钮，可以沿对角或垂直方向旋转文字，这是标记窄列的好方式，1.1 数据录入与基本设置1.1.6单元格数据太多，加滚动条显示第1步：插入文本控件第3步：关闭设计模式，输入文字第2步：设置文本框属性1.1 数据录入与基本设置1.1.7冻结拆分窗格，轻松查看行列数据1．冻结行2．冻结列3．冻结行和列1.1 数据录入与基本设置1.1.8轻松绘制单斜线、双斜线表头1．单斜线表头。2．双斜线表头。1.1 数据录入与基本设置1.1.9如何让数字以“万”为计数单位来显示第1种方法：设置单元格格式第2种方法：选择性粘贴除以10000。1.1 数据录入与基本设置1.1.10设置仅能修改部分单元格数据第1步：解除“锁定”。第2步：设置保护1.1 数据录入与基本设置1.1.11隐藏工作表第1步：打开VBA对话框。第2步：设置要隐藏的工作表属性。第1部分 数据采集整理----1.2合并单元格1.2 合并单元格1.2.1批量合并单元格第1步：“数据”→“分类汇总”第2步：“定位”→“定位条件”第3步：“开始”→“合并后居中”1.2 合并单元格1.2.2批量拆分合并单元格第1步：取消“合并后居中”第2步：“定位”→“空值”第3步：输入“=A2”,按Ctrl+Enter组合键执行计算1.2 合并单元格1.2.3合并单元格填充序号选中整个合并单元格区域，输入公式“=MAX($A$1:A1)+1”，按“Ctrl+Enter”组合键执行计算。1.2 合并单元格1.2.4合并单元格计算1.合并单元格求和3.合并单元格平均值2.合并单元格计数1.2 合并单元格1.2.5合并单元格筛选1. 选择合并单元格，复制到另一列，备用。3.“定位”→“定位条件”→“空值”2.“开始”→“合并单元格”→“取消合并单元格”4.输入公式=A2，按“Ctrl+Enter”键结束5.备用的合并单元格区域，单击“格式刷”1.2 合并单元格1.2.6合并单元格数据查询公式：=VLOOKUP(F2,OFFSET(B1:C1,MATCH(E2,A2:A10,0),,3),2,)第1部分 数据采集整理----1.3数据规范1.3 数据规范1.3.1利用数据验证（数据有效性）规范数据输入1．规范性别输入1.3 数据规范1.3.1利用数据验证（数据有效性）规范数据输入2．限定输入内容1.3 数据规范1.3.1利用数据验证（数据有效性）规范数据输入3．限定数字范围1.3 数据规范1.3.1利用数据验证（数据有效性）规范数据输入4．限定文本长度1.3 数据规范1.3.1利用数据验证（数据有效性）规范数据输入5．限制输入重复信息1.3 数据规范1.3.1利用数据验证（数据有效性）规范数据输入6．限定身份证号码1.3 数据规范1.3.2设置只能输入规范的日期第1步：设置日期格式。第2步：数据验证规范日期区间。1.3 数据规范1.3.3巧用数据有效性规范报到时间1.公式“=NOW()”2.“数据”→“数据验证”→“序列”，“来源”设为“=$E$2”，3.“报到时间”一列单元格格式4.隐藏E列。1.3 数据规范1.3.4数量单位，怎么计算平均值{=ROUND(AVERAGE(--SUBSTITUTE(B2:B10,"分",)),2)}，1.3 数据规范1.3.5一键添加“能计算”的数量单位“设置单元格格式”对话框，在“分类”中选择“自定义”，直接在“类型”文本框的“G/通用格式”后输入数量单位，1.3 数据规范1.3.6多级联动菜单，规范数据输入第1步：分级数据整理。第2步：自定义名称。第3步：建立各级菜单。1.3 数据规范1.3.7”数值”不能计算，怎么办第1种方法：选择性粘贴。第2种方法：数据分列。第3种方法：VALUE函数。1.3 数据规范1.3.8规范全角半角数据公式“=ASC(D3)”公式“=WIDECHAR(D3)”1.3 数据规范1.3.9数字与文本分离的三种方法第1种方法：函数法。第2种方法：分列。第3种方法：快速填充。1.3 数据规范1.3.10用LOOKUP和FIND函数规范标准名称第1步：建立关键字与标准名称对应表。第2步：函数实现公式“=LOOKUP(1,0/FIND($D$2:$D$7,A2),$E$2:$E$7)”1.3 数据规范1.3.11给同一单元格中姓名和电话号码中间加分隔符号公式“=REPLACEB(A2,SEARCHB("?",A2),0,"：")第1部分 数据采集整理----1.4行列设置1.4 行列设置1.4.1快速删除空白行1．纯空白行，无其它空白单元格2．既有空白行，又有空白单元格1.4 行列设置1.4.2不管插入行还是删除行，序号都可自动填写方法1：ROW函数。方法2：ROW函数+表格。方法3：SUBTOTAL函数。1.4 行列设置1.4.3数据的转置与跳过单元格复制１．选择性粘贴，数据转置的使用。2．跳过单元格复制。1.4 行列设置1.4.4最快捷的一列变多列方式1．将数据复制到C列。2．在D1单元格中输入公式“=C6”，按Enter键执行计算。因为从C6开始名字另起一列显示。3．公式向下填充，再向右填充，在C1：G5区域会囊括原来的一整列分布的数据。4．选中D1：G5区域，复制，再进行选择性粘贴，用数值覆盖原有的公式。5．删除C1：G5区域以外的数据。1.4 行列设置1.4.5聚光灯效果（阅读模式）改变当前行和列的颜色1．条件格式设计颜色。2．颜色随单元格改变而移动1.4 行列设置1.4.6同一部门员工合并到同一单元格1.4 行列设置1.4.7同一部门员工，由同一单元格变分行显示1．打开查询编辑器。2．姓名的分列显示3．逆透视列。4．关闭并上载第1部分 数据采集整理----1.5数据维度转换1.5 数据维度转换1.5.1为工作表建目录1．“公式”菜单中“定义名称”公式“=INDEX(GET.WORKBOOK(1),ROW(A1))&T(NOW())”公式“=IFERROR(HYPERLINK(目录&"!A1",MID(目录,FIND("]",目录)+1,99)),"")”2．公式1.5 数据维度转换1.5.2旁门左道创建目录第1步：选定所有工作表。第2步：输入公式。第3步：自动生成“兼容性报表”。第4步：复制目录到“目录”工作表。1.5 数据维度转换1.5.3链接到另一张表的四种姿势第1种方法：文字。1.5 数据维度转换1.5.3链接到另一张表的四种姿势第2种方法：形状。1.5 数据维度转换1.5.3链接到另一张表的四种姿势第3种方法：图标。1.5 数据维度转换1.5.3链接到另一张表的四种姿势第4种方法：ActiveX控件。1.5 数据维度转换1.5.4单击订单名称即可跳到订单详情工作表第1部分 数据采集整理----1.6数据格式转换1.6 数据格式转换1.6.1数值取整的9种方式1．INT取整。1.6 数据格式转换1.6.1数值取整的9种方式2．TRUNC取整。1.6 数据格式转换1.6.1数值取整的9种方式3．ROUND小数取整。1.6 数据格式转换1.6.1数值取整的9种方式4．ROUND整数取整。1.6 数据格式转换1.6.1数值取整的9种方式5．ROUNDUP向上舍入函数。1.6 数据格式转换1.6.1数值取整的9种方式6．ROUNDDOWN向下舍入函数。1.6 数据格式转换1.6.1数值取整的9种方式7．MROUND函数。1.6 数据格式转换1.6.1数值取整的9种方式8．CEILING函数1.6 数据格式转换1.6.1数值取整的9种方式9．FLOOR函数1.6 数据格式转换1.6.2数值的特殊舍入方式：舍入到偶数或奇数1．舍入到偶数MROUND四舍五入到偶数CEILING函数FLOOR函数EVEN函数1.6 数据格式转换1.6.2数值的特殊舍入方式：舍入到偶数或奇数2．舍入到奇数1.6 数据格式转换1.6.3NUMBERSTRING和TEXT函数：阿拉伯数字和中文数字转换1．阿拉伯数字转中文数字2．中文数字转阿拉伯数字1.6 数据格式转换1.6.4一串串长短不一的文本算式，怎么算结果第1步：选项设置。第2步：数据分列。第3步：选项设置。1.6 数据格式转换1.6.5人民币阿拉伯数字转为中文大写格式公式“=SUBSTITUTE(SUBSTITUTE(IF(-RMB(A2,2),TEXT(A2,";负")&TEXT(INT(ABS(A2)+0.5%),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(RMB(A2,2),2),"[dbnum2]0角0分;;整"),"零元整"),"零角",IF(A2^2<1,,"零")),"零分","整")”第1部分 数据采集整理----1.7数据筛选1.7 数据筛选1.7.1数值筛选基础1．筛选指定某值。1.7 数据筛选1.7.1数值筛选基础2．排除指定值筛选。1.7 数据筛选1.7.1数值筛选基础3．高于指定值筛选。1.7 数据筛选1.7.1数值筛选基础4．筛选指定范围数值。1.7 数据筛选1.7.1数值筛选基础5．筛选前几位数值。1.7 数据筛选1.7.1数值筛选基础6．高于平均值筛选。1.7 数据筛选1.7.2高级筛选高级筛选的关键点：必须按照筛选要求自己写一个条件区域。高级筛选的原数据区域第一行（列标签）一定要包含条件区域的第一行（列标签）。高级筛选条件区域，一定要注意各条件之间的关系。1.7 数据筛选1.7.3筛选符合条件人员的两种方法1.IF+COUNTIF、2.高级筛选1.7 数据筛选1.7.4不用公式的跨表查询：查询指定顾客的购买记录第1步：写条件。第2步：高级筛选。1.7 数据筛选1.7.5SUMIF+CELL使隐藏列不参与汇总第1步：建立辅助行第2步：函数实现。1.7 数据筛选1.7.6序号经过筛选后仍然不乱第1部分 数据采集整理----1.8排序和排名1.8 排序和排名1.8.1多关键字排序，多少个排序条件都可以一起来“数据”→“排序”“添加条件”1.8 排序和排名1.8.2数据也可按行排序第1步：选定除了行标题（首列）以外的所有数据，单击“数据”→“排序”第2步：选择“主要关键字”为消费记录所在“行2”，“次序”为“降序”；单击“添加条件”按钮，添加“次要关键字”，次要关键字选择消费方式所在的“行4”，根据要求选择升序或者降序，1.8 排序和排名1.8.3按自定义序列排序，谁在前谁在后由你定1.8 排序和排名1.8.4剔除0值排名次，升序降序由你来定1．剔除0值降序排。2．剔除0值升序排名。1.8 排序和排名1.8.5只给有销量的产品添加序号公式“=IF(C2=0,"",(C2<>0)*(COUNT($C$2:C2)-COUNTIF($C$2:C2,"0")))”1.8 排序和排名1.8.6RANK.EQ引用合并区域，实现多列数据排名公式“=RANK.EQ(B3,($B$3:$B$11,$E$3:$E$11,$H$3:$H$11,$K$3:$K$11))”1.8 排序和排名1.8.7SUMPRODUCT实现中式排名公式“=SUMPRODUCT(($B$2:$B$7>=B2)/COUNTIF($B$2:$B$7,$B$2:$B$7))”1.8 排序和排名1.8.8SUMPRODUCT分组排名公式公式“=SUMPRODUCT(($C$2:$C$14=C2)*($D$2:$D$14>=D2)/COUNTIFS($C$2:$C$14,$C$2:$C$14,$D$2:$D$14,$D$2:$D$14))”1.8 排序和排名1.8.9利用公式自动生成字母序列公式“=CHAR(COLUMN(A1)+64)”公式“=CHAR(ROW(A1)+64)”公式“=CHAR(COLUMN()+96)”公式“=CHAR(ROW(B1)+96)”1.8 排序和排名1.8.10数据透视表实现排名（1）建立数据透视表。（2）添加字段到“行”（3）更改“值显示方式”第1部分 数据采集整理----1.9数据去重复1.9数据去重复1.9.1删除重复项，也可以随你所欲1．只有一列数据。2．数据有多列。1.9数据去重复1.9.2每人报名项目多少不一，计算有多少人报名公式“=SUMPRODUCT(1/COUNTIF($A$2:$A$16,$A$2:$A$16))”1.9数据去重复1.9.3COUNT+MATCH，统计两列有多少重复值公式“=COUNT(MATCH(A2:A11,B2:B11,0))”，按Ctrl+Shift+Enter组合键1.9数据去重复1.9.4利用EXACT函数设置条件格式，标记两组数据的不同在“新增格式规则”对话框中，“选择规则类型”为“使用公式确定要设置格式的单元格”，并在“为符合此公式的值设计格式”中输入公式“=OR(EXACT(A2,$B$2:$B$21))=FALSE”，第1部分 数据采集整理----1.10多工作簿、工作表合并、汇总与拆分1.10 多工作簿、工作表合并、汇总与拆分1.10.1数据查询功能实现多工作表合并第1步：打开查询编辑器。第2步：追加查询。第3步：关闭并上载1.10 多工作簿、工作表合并、汇总与拆分1.10.2PowerQuery编辑器实现多工作簿合并与刷新第1步：将需要合并的工作簿存入一个文件夹中。第2步：新建“合并”工作簿。新建的工作簿不要和需要合并的工作簿放在同一文件夹。第3步：新建查询。第4步：删除多余数据列。第5步：添加列选项。第6步：数据加工。第7步：数据加载到表格。第8步：数据刷新。1.10 多工作簿、工作表合并、汇总与拆分1.10.3多工作表数据汇总第1种情况：所有表“姓名”排序一致。公式“=SUM('*'!C2)”1.10 多工作簿、工作表合并、汇总与拆分1.10.3