EXCEL使用技巧

-0-主讲：王娟敏Excel使用技巧-1-Excel可以做什么？Excel—MicrosoftOffice组件之一功能：对数据进行记录、计算与分析应用：管理/财经/金融/统计等领域—完成商业和工业任务个人事务处理专业科学运算、统计分析熟练掌握Excel的使用技巧→提高学习和工作效率12019/12/10-2-124目录Excel概述基本操作技巧数据处理与分析技巧6公式与函数数据图表化数据透视表35-3-第一部分Excel概述PART01Excel基本概念Excel界面-4-1.Excel概述1.1基本概念Excel创建的文件(Book1/2/3…)工作簿的组成部分，存储和处理数据的主要文档数据工作表(Sheet1/2/3…)图表工作表(Chart1/2/3…)工作表存储数据的最基本单元行列交叉点(列标A/B/C…+行标1/2/3…→A1/B2/C3…)多个单元格组成的矩形区域左上角单元格:右下角单元格(A1:F9)42019/12/10-5-1.Excel概述1.1基本概念工作簿、工作表、单元格之间的关系52019/12/10单元格工作表工作簿-6-1.Excel概述1.2Excel界面62019/12/10名称框编辑栏全选标题栏(工作簿名称)快速访问工具栏文件状态栏活动单元格工作表标签行标题1列标题↓1,048,576A→XFD(16,384)功能区-7-1.Excel概述1.2Excel界面功能区(↔Excel2016菜单栏)选项卡(集中某一项核心任务)组(相关项的组合)命令(执行某一具体任务)更多地显示出以前隐藏在菜单和工具栏中的命令和功能72019/12/10快速访问工具栏(↔Excel2010工具栏)—固定显示最常用的命令对话框启动器—点击获取更多选项，来做更细部的设定-8-1.Excel概述1.2Excel界面文件选项卡Excel选项↔(Excel工具菜单→选项)82019/12/10-9-1.Excel概述1.2Excel界面名称框+编辑栏92019/12/10编辑栏—显示活动单元格中的数据输入/编辑数据（值/公式）名称框—显示活动单元格的地址/名称定义名称/快速定位单元格或区域取消确认插入函数向导-10-1.Excel概述1.2Excel界面状态栏单元格模式就绪/输入/编辑自动计算结果平均值/计数/最值/求和102019/12/10-11-1.Excel概述1.2Excel界面活动单元格（当前选中的单元格）黑色边框右下角—填充柄（扩展/填充序列）选中的单元格区域黑色边框+高亮活动单元格反色填充柄112019/12/10-12-第二部分基本操作技巧PART02工作簿工作表快捷操作及美化单元格-13-2.基本操作2.1工作簿创建空白工作簿基于模板基于现有工作簿保存打开关闭132019/12/10XML文件类型扩展名工作簿.xlsx启用了宏的工作簿.xlsm模板.xltx启用了宏的模板.xltm非XML二进制工作簿.xlsb启用了宏的加载项.xlam-14-2.基本操作2.2工作表—冻结窗格例题1视图—窗口—冻结窗口—冻结拆分窗格142019/12/10-15-2.基本操作2.2工作表—拆分窗格视图—窗口—拆分。以选定单元格左上角位置进行拆分152019/12/10-16-2.基本操作2.2工作表—并排查看视图—窗口—新建窗口-全部重排-垂直并排/同步滚轮162019/12/10-17-2.基本操作2.2工作表—快速美化快速美化—套用表格格式—两种方法172019/12/10单击数据表，开始-套用表格格式Ctrl+T组合键【技巧】将表格转换为普通数据区域：淡季表格中任意一个单元格，表格工具/设计选项卡—转换为区域按钮单击数据表，按Ctrl+T快捷键-18-2.基本操作2.2工作表—快速美化例题2快速美化—使用格式刷、单元格样式选中设置好的表头区域，单击开始—样式组—单元格样式下拉箭头—新建单元格样式，创建自定义样式，快速美化其他工作表或工作簿182019/12/10-19-2.基本操作2.2工作表—设置格式与高级打印单击页面布局—页面设置-打印标题192019/12/10设置打印标题后，打印长表格时每页都有标题行-20-2.基本操作2.2工作表—设置格式与高级打印例1分页打印202019/12/10选择要分页处左上角的单元格，页面布局-分隔符-插入分页符。插入分页符后，Excel自动按选定的单元格的左边框和上边框，将工作表划分为多个打印区域。-21-2.基本操作2.2工作表—打印工作簿单击文件-打印-在打印设置中设置“打印整个工作簿”212019/12/10-22-2.基本操作2.2工作表—工作表的移动与复制选择:Shift[连续]/Ctrl[不连续]+单击工作表标签命令：插入/删除、重命名、移动/复制222019/12/10右键-23-2.基本操作2.3单元格—定位单元格的技巧232019/12/10定位单元格：鼠标左键单击1、Ctrl+Home快速定位A1单元格2、Ctrl+方向键快速定位水平或垂直方向的的始末单元格3、Ctrl+End快速定位已使用区域右下角单元格4、名称框定位当要定位的单元格不在当前屏幕内时：-24-2.基本操作2.3单元格—选取单元格的高招例3242019/12/10快速准确的选取单元格区域1、Shift+对角单元格：快速选中任意目标区域2、Ctrl+Shift+Home：快速从活动单元格到A1区域3、Ctrl+Shift+方向键：快速选中当前行、列数据区域4、Ctrl+A：快速选中当前数据区域（全选）5、Ctrl+单击工作表标签：多表批量操作-25-2.基本操作2.3单元格—输入数据手动输入数据多个单元格/区域同时输入相同数据→Ctrl+Enter单元格内数据多行显示→自动换行/Alt+Enter自动输入数据填充柄→自动填充向外拖动→扩展填充向内拖动→清除252019/12/10填充柄-26-第三部分数据处理与分析技巧PART03粘贴与分列查找定位数据有效性条件格式数据排序筛选技巧分类汇总合并计算-27-3.数据处理与分析技巧3.1粘贴选择性粘贴-转置—实现行列内容快速转换让原始区域在粘贴后行列互换，并能自动调整所有公式转置后正常计算272019/12/10年度1月2月3月4月2014年249322375533079323492015年340162995534174172912016年457532971233726298922017年33471352345585232093年度2014年2015年2016年2017年1月249323401645753334712月237552995529712352343月330793417433726558524月32349172912989232093-28-3.数据处理与分析技巧3.1粘贴例3选择性粘贴-转换为数值选择性粘贴-运算（结构相同）282019/12/10-29-3.数据处理与分析技巧3.1分列根据分隔符号、字符个数将目标拆分成多列。1、确定数据拆分方式2、选择分隔符号或设置分列线3、为拆分后的各列设置数据格式292019/12/10-30-3.数据处理与分析技巧3.1分列例4根据固定宽度方式拆分单元格。从身份证号码中提取“地区代码”和“出生日期”302019/12/10姓名身份证号码地区代码出生日期李易峰11010619740104405711010619740104杨幂21140119600307531121140119600307赵丽颖34182319580202049334182319580202张杰13020719540926885213020719540926唐嫣54233819750717121854233819750717郑爽33022219800227123433022219800227刘诗诗51072519750506339851072519750506言承旭22000019701001271122000019701001张翰33022219800227123433022219800227冯绍峰13068419800511557513068419800511马天宇21030419670915455921030419670915姚笛43052719780822655543052719780822李小璐43052719780822655543052719780822-31-3.数据处理与分析技巧3.2查找与替换例5查找与替换基础应用开始—编辑—查找和选择—查找（Ctrl+F）/替换（Ctrl+H）312019/12/10-32-3.数据处理与分析技巧3.2查找与替换按格式查找替换单元格内容322019/12/10-33-3.数据处理与分析技巧3.2查找与替换合并单元格填入数据选中合并单元格—取消合并—定位空值—“=+方向上键”—Ctrl+Enter批量录入332019/12/10品名规格1月2月3月4月品名规格1月2月3月4月30粒50607080通心络胶囊30粒5060708040粒60708090通心络胶囊40粒6070809090粒708090100通心络胶囊90粒70809010024粒8090100110连花清瘟胶囊24粒809010011036粒90100110120连花清瘟胶囊36粒9010011012048粒100110120130连花清瘟胶囊48粒10011012013072粒110120130140连花清瘟胶囊72粒11012013014024片120130140150连花清瘟片24片12013014015036片130140150160连花清瘟片36片13014015016010袋140150160170连花清瘟颗粒10袋14015016017010袋150160170180连花清瘟颗粒10袋15016017018036粒160170180190芪苈强心胶囊36粒160170180190180粒170180190200芪苈强心胶囊180粒170180190200180粒180190200210芪苈强心胶囊180粒180190200210芪苈强心胶囊通心络胶囊连花清瘟胶囊连花清瘟片连花清瘟颗粒-34-3.数据处理与分析技巧3.3数据有效性342019/12/10图标类型停止—禁止输入无效数据警告—不禁止输入无效数据是/否/取消→接受/编辑/删除信息—不禁止输入无效数据确定/取消→接受/拒绝限制数据类型数值范围数据范围-35-3.数据处理与分析技巧3.3数据有效性例6圈释无效数据—找出不符合要求数据选择数据区域，为数据添加有效性，使用圈释无效数据功能352019/12/10-36-3.数据处理与分析技巧3.4条件格式例7条件格式—基于条件更改单元格区域的外观362019/12/10-37-3.数据处理与分析技巧3.5数据排序例8普通排序—内置序列自定义排序—自定义序列单列/行排序/多列/行排序372019/12/10-38-3.数据处理与分析技巧3.6数据筛选自动筛选/自定义筛选—仅显示满足条件的数据，隐藏不满足条件的数据382019/12/10删除重复项-快速删除工作表重复栏目-39-3.数据处理与分析技巧3.6数据筛选例10高级筛选—根据多个条件进行筛选列出条件（文本/表达式）—高级筛选中选择列表与条件区域392019/12/10姓名籍贯职称入职日期年终奖金高级工程师2012/1/115000中级工程师2012/1/113000姓名籍贯职称入职日期年终奖金张杰上海高级工程师2010/2/218000唐嫣广州工程师2012/7/1018000郑爽广州高级工程师2013/3/317000刘诗诗重庆中级工程师2014/2/1215000言承旭上海高级工程师2013/3/316000张翰天津中级工程师2012/7/1018000冯绍峰