Excel操作与数据分析技巧精选课件.ppt

1、培训科培训科 张其建张其建2019-6-242019-6-24EXCEL实战技巧1EXCEL简介2EXCEL常用操作及功能3EXCEL函数使用技巧4数据透视表5公式及函数6数组的运用报告目录宏与简单VBA中级高级专家级初级l功能区：功能区：新的控制中心，汇集了一些基本的命令并使这些命令非常直观。1、EXCEL简介你用2019？不一样？没有关系，我这里为大家提供一个03-07对照工具Excel2019Excel2019中有许多更改。其中以窗口顶部的更改最为明显。中有许多更改。其中以窗口顶部的更改最为明显。功能区的三个组成部分是选项卡、组和命令。选项卡：选项卡呈现了可在 Excel 中执行的核心任

2、务。组：组是指显示在选项卡上的相关命令集。命令：命令是指按钮、菜单或信息输入框。1、EXCEL简介隐藏的命令功能区上的命令是最常用的命令。lExcel 2019 并不总是显示每一项命令，只有在可能需要使用某些命令时，才会显示这些命令，以响应所执行的操作。1、EXCEL简介更多的选项l单击对话框启动器单击对话框启动器，您就会看到一个对话框或任务窗格。，您就会看到一个对话框或任务窗格。1、EXCEL简介对话框启动器l在首次启动 Excel 2019 时，快速访问工具栏位于功能区上方。在快速访问工具栏中，命令始终可见.l我们可以把一些常用的命令列入快速工具栏中方便应用。快速访问工具栏1、EXCEL简

3、介键盘快捷方式l功能区上的每一个按钮都有快捷方式。功能区上的每一个按钮都有快捷方式。l新快捷方式都有提示。新快捷方式都有提示。l以以 Ctrl 开头的旧键盘快捷方式没有变化。开头的旧键盘快捷方式没有变化。1、EXCEL简介新视图l在工作表直接看到：行在工作表直接看到：行/列标题、页边距标尺。列标题、页边距标尺。l在打印之前无需使用在打印之前无需使用“打印预览打印预览”来发现问题。来发现问题。l添加页眉和页脚比以前更加简捷。添加页眉和页脚比以前更加简捷。1、EXCEL简介兼容性1、EXCEL简介类型97-20192019不带宏的工作簿.xls.xlsx带宏的工作簿.xls.xlsm文件格式为方便

4、在使用新版excle时，可以设置中默认的文件保存格式强烈推荐所有正在使用Excel2019的同事们，使用EXCEL2019或20191EXCEL简介2EXCEL常用操作及功能3EXCEL函数使用技巧4数据透视表5公式及函数6数组的运用报告目录宏与简单VBA2、EXCEL常用操作及功能数据录入TAB与ENTER数据录入时检查错误：让EXCEL读出你的输入内容一般情况下：TAB-向右；ENTER-向下可调整ENTER属性选定需要输入区域一直按ENTER可顺序输入在选项栏中选择：按ENTER键开始朗读单元格相对位置:lA2 F13 C7绝对位置:l列绝对：$A2、$F13、$C7l行绝对：A$2、F

5、$13、C$7l绝对位置：$A$2、$F$13、$C$7引用引用“$”表示对后面所表达的列或行的锁定。注意：注意：引用在我们拖动公式或填充时有重大的影响只有理解了应用，你才能更好的使用公式2、EXCEL常用操作及功能引用名称命名与引用我们可以对某些单元格定义名称后直接使用名称，以简化公式内容。2、EXCEL常用操作及功能填充l当数据量少的时候：直接采用鼠标拖拉即可。l填充时尤其要注意引用方式l右键也是可以填充的当数据量多的时候：需要采用键盘来操作（如果采取鼠标填充的方式，就会拖到”地久天长”)l具体快捷键：快速移动：ctrl+方向键（）快速选中：ctrl+shift+方向键（）快速填充：ctr

6、l+D（向下）ctrl+R（向右）2、EXCEL常用操作及功能左键填充右键填充复制复制公式、复制数据、复制格式行列互换不同的复制方式能起到神奇的工效。2、EXCEL常用操作及功能选定遇到数据中太多的空格时，有一种抓狂的感觉F5：定位条件可以为你带来很多快乐2、EXCEL常用操作及功能数据有效性可以帮助我们设置可选的菜单，避免五花八门的描述产生。2、EXCEL常用操作及功能排序行也是可以排序的汉字可以按笔划排序2、EXCEL常用操作及功能排序自定义序列OFFICE按钮EXCEL 选项常用编辑自定义列表设定按照自己顺序的排列2、EXCEL常用操作及功能筛选自动筛选和高级筛选2、EXCEL常用操作及

7、功能分列快速的将一些有规律的数据分成不同类别。2、EXCEL常用操作及功能分类汇总2、EXCEL常用操作及功能单元格格式快捷键：Ctrl+12、EXCEL常用操作及功能条件格式开始样式条件格式条件格式是2019里面新增加的一项功能，按要求对数据进行分类显示2、EXCEL常用操作及功能冻结和拆分这是查看大量数据显示表头的利器2、EXCEL常用操作及功能2、EXCEL常用操作及功能其他简便功能A、删除重复数据照相机-把数据变为图片，防止泄露其他数据对象选择工具-选择EXCEL中的图形对象B、隐藏着的快捷工具按拼音排序1EXCEL简介2EXCEL常用操作及功能3EXCEL函数使用技巧4数据透视表5公

8、式及函数6数组的运用报告目录宏与简单VBA源数据-一定是要规则清晰，无空格断行的数据。l源数据的每列都将成为一个字段并且可在报表中使用。l报表字段的的名称来自源数据中的列标题，标题下的其余各行应该包含同一列中的类似项。l删除用于数据块相互隔开的空白行。数据透视表3、数据透视表一种可以动态地改变版面布置，以便按照不同方式分析数据的动态表格。创建数据透视表1、插入选项卡2、选择数据透视表3、选项框中选择确定3、数据透视表数据透视表的使用把你想透视的内容拖到对应区域3、数据透视表3、数据透视表数据透视表的使用现在对应的内容就是：谁在哪台机器上的产量是多少。行和列标签的自由拖动可以创造很多不同的数据表

9、达方式。3、数据透视表数据透视表的使用在图表区域中点击右键，还可以选择其他的内容：例如：汇总类型（求和，计数等），显示方式(百分比等等数据透视图的使用方法和透视表一致，只是多了一个可以筛选的图表1EXCEL简介2EXCEL常用操作及功能3EXCEL函数使用技巧4数据透视表5公式及函数6数组的运用报告目录宏与简单VBA1、公式简介EXCEL中公式分为了很多类四、公式及函数我们学习函数最大的帮助工具是EXCEl自带的帮助编写公式时你应该具备基础的程序逻辑思考技巧。四、公式及函数2、函数与公式的基本概念EXCEL公式以“=“为引导，后面接上函数，以及函数所使用的参数=IF(logical_test,

10、value_if_true,value_if_false)参数数据类型在EXCEL中可运算的数据类型分为：文本，数值、日期、逻辑值、错误值运算符号符号类型符号案例算术运算+；-；*；/；=62=3+2*5比较运算;=;；=；=；=51,SUM(B1:G1),”)在这个公式中，sum函数是2级函数，它是if函数的参数Excel2019允许64层的嵌套，2019只能7层。=IF(MOD(ROW(),3)=1MOD(ROW(),3)=1,源数据源数据!A$1!A$1,IF(MOD(ROW(),3)=2,INDEX(源数据!$A$1:$G$349,(ROW()+4)/3,COLUMN(),)第1个IF

11、的条件参数当条件为真时返回该值当条件为假时进入下一个IF第2个IF的条件参数当条件为真时进入Index函数当条件为假时返回空值5、如何解读函数=IF(MOD(ROW(),3)=1,源数据!A$1,IF(MOD(ROW(),3)=2,INDEX(源数据!$A$1:$G$349,(ROW()+4)/3,COLUMN(),)复杂案例复杂案例A、设计目的：按规律切割工资条B、设计思路：结果的第一行：永远是原数据中的第一行。结果的第二行：结果第2行对应原数据第2行，结果第5行对应原数据第3行，第8对应第4，第11对应第5。结果第三行：永远为空。这里面第二个最难算，我们看看他们之间有什么规律。案例:工资打

12、印切割公式=IF(MOD(ROW(),3)=1,源数据!A$1,IF(MOD(ROW(),3)=2,INDEX(源数据!$A$1:$G$349,(ROW()+4)/3,COLUMN(),)四、公式及函数6、函数是怎么编出来的案例4.25、公式的查错错误值含义#列宽不够显示，或使用了负的日期，和时间。#VALUE!参数或数值类型错误#DIV/0!被零除错误#NAME？无法识别的公式#N/A数值对函数或公式不可用#REF!单元格引用无效#NUM!无效的数字值#NULL!计算为空值时，无法得出计算结果；例如：公式差错的工具：1、键盘上的F92、公式选项卡中的公式查错时的要诀：1、读懂公式2、看看公式

13、在拖动过程中的引用方式是否正确3、当嵌套太多太复杂时，把公式分层粘到单元格中四、公式及函数1EXCEL简介2EXCEL常用操作及功能3EXCEL函数使用技巧4数据透视表5公式及函数6数组的运用报告目录宏与简单VBA数组的概念五、数组的运用数组是一个或多个元素按照行业排列方式组成的组合。在EXCEl中使用表达数组=1;2;3;4;51、在一个单元格中输入数组2、选中5个格子，按键盘上的F2。3、CTRL+SHIFT+ENTER按键：CTRL+SHIFT+ENTER数组的输入数组的使用五、数组的运用1、频次统计2、不确定结果的查询案例=FREQUENCY(C2:C6,E2:E5)=IF(ROW(1

14、:1)COUNTIF(A:A,$C$1),INDEX(B:B,SMALL(IF(A$2:A$10=$C$1,ROW($2:$10),ROW(1:1)1EXCEL简介2EXCEL常用操作及功能3EXCEL函数使用技巧4数据透视表5公式及函数6数组的运用报告目录宏与简单VBA定义与用途六、宏与简单VBAVBA是专为OFFICE系列开发的操作程序，使用VBA必须要具备计算机的编程知识。可以这么说，在我们工作中99.9%的情况下下用不到VBA。而且，在实际工作中能不用就不用，很多工作用函数也能解决问题。VBA运用的演示在此不再细讲谢谢！结束语EXCELHOME：excelhome/推荐书籍：Excel 2019实战技巧精粹