Excel高级功能应用课件

OFFICE系列培训之

Excel高级应用OFFICE系列培训之Excel高级应用1第2页序言Excel用户水平的5个层次刚刚开始接触基本掌握常用功能熟练使用常用功能+数据透视表+公式…+数组公式+VBA简单编程Excel技能+专业知识+行业经验新手

初级用户

中级用户

高级用户

专家第2页序言Excel用户水平的5个层次刚刚开始接触基本掌握2第3页知识结构Excel数据处理与操作Excel函数与公式Excel图表与图形ExcelVBA与宏Excel数据分析第3页知识结构Excel数据处理与操作Excel函数与公式E3第4页提纲一、高级功能应用二、应用技巧及快捷键三、枢纽分析表（数据透视表）第4页提纲一、高级功能应用4第5页目录冻结窗格

移动工作表

群组小计/分类汇总筛选

保护自定义序列验证/条件条件格式标题选择性粘贴自动格式设定

第5页目录冻结窗格移动工作表群组小计/分类汇总筛选保护5高级功能应用冻结窗格-當有一个表很多行（项次）时，你想在表的后面也想看到标题.第6页实例1高级功能应用冻结窗格第6页实例16高级功能应用移动工作表-當有一个或多个工作表想移到另外的工作簿（Excel文件），你会怎样处理？1.全部复制->在目的文件新建工作表->粘贴2.使用“移动或复制”工作表功能第7页实例1高级功能应用移动工作表第7页实例17高级功能应用你想按一定的熟性或类型对资料进行分组并可收缩和展开，你会怎样处理？群组（资料->群组及大纲->群组）小计/分类汇总(资料->小计）第8页实例1高级功能应用你想按一定的熟性或类型对资料进行分组并可收缩和展8高级功能应用筛选自动筛选（资料->筛选->自动筛选）自定义筛选第9页实例1高级功能应用筛选第9页实例19高级功能应用你有一个文件希望共享给别人看，但不希望别人修改里面的内容，你会怎样处理？保护功能文件保护（另存->工具->一般选项->设置密码）活页保护（工具->保护->保护活页簿）工作表保护（工具->保护->保护工作表）单元格保护（在单元格格式的“保护”卡中设定是否锁定，再使用工作表保护功能，则保护锁定的单元格）第10页实例1高级功能应用你有一个文件希望共享给别人看，但不希望别人修改里10高级功能应用如有一个人名，很生僻，每次都很难输入，但此人名又经常用到，你会怎样处理？自定义序列（工具->选项->自订清单->新增），豆号分隔

（如可通过“张三”的名字把“李四”名字找出来）第11页高级功能应用如有一个人名，很生僻，每次都很难输入，但此人名又11高级功能应用如你希望某些单元格控制输入数据的范围或只可选择预定的值，你会怎样处理验证/条件（资料->验证）整数实数清单日期时间文字长度第12页实例2高级功能应用如你希望某些单元格控制输入数据的范围或只可选择预12高级功能应用如你希望某些单元格在输入不同范围的数据时显示不同的格式（职工的年龄在16岁以下显示红色,以上则不显示颜色）条件格式(格式->设定格式化的条件）第13页实例3高级功能应用如你希望某些单元格在输入不同范围的数据时显示不同13高级功能应用如你有一张很多行的表需打印（预计有很多页），希望每一页都自动显示标题指定标题（档案->版面设定->页首/页尾）指定标题列（档案->版面设定->工作表->标题列）第14页实例4高级功能应用如你有一张很多行的表需打印（预计有很多页），希望14高级功能应用如你有一组数据，希望去除格式，只需要内容。或希望从垂直转为横向，你会怎样处理？选择性粘贴(编辑->选择性粘贴/右键->选择性粘贴）只粘贴值、公式、格式等与目的单元格运算转置第15页实例5高级功能应用如你有一组数据，希望去除格式，只需要内容。或希望15高级功能应用自动格式设定（格式->自动格式设定）第16页高级功能应用自动格式设定（格式->自动格式设定）第16页16第17页提纲一、高级功能应用二、应用技巧及快捷键三、枢纽分析表（数据透视表）第17页提纲一、高级功能应用17应用技巧及快捷键快速输入数据自动填充等差填充快速自动填充批量输入选中批量输入单元格输入资料Ctrl+Enter（回车）重复刚输入的值Ctrl+D（往下重复）Ctrl+R（往右重复）Ctrl+'（为英文状态下的单引号）快速复制一个单元格的资料至另一单元格（Ctrl+用鼠标点原单元格资料并拖至目标单元格）第18页实例1应用技巧及快捷键快速输入数据第18页实例118应用技巧及快捷键换行自动换行（格式/右键->存储格式->对齐方式->自动换列）单元格换行（Alt+Enter）第19页实例1应用技巧及快捷键换行第19页实例119应用技巧及快捷键插入“a”或“r”（在字体下拉列表中选择“Marlett”字体，输入a或b为“a”输入r为“r”

快速恢隐藏的列（将鼠标指针放置在被隐藏列标的分割线上，并拖动）自动适配行高或列宽（将鼠标指针放在列标或行标的分割线上，并双击）在同一单元格中输入多个测试值（工具->选项->编辑->按Enter后，移动选取范围）第20页实例1应用技巧及快捷键插入“a”或“r”第20页实例120应用技巧及快捷键快速选择单元格快速选择整个工作表的单元格（鼠标点击左上角的方块）快速选择整列或整行（鼠标点击列的方块或行的方块）快速选择整列或整行的资料快速选择所连续单元格的资料（Shitf+/）快速选择所有资料（Shitf+Alt++）选择不连续列或行或单元格资料（Ctrl+鼠标点击对应的列或行或单元格）快速跳至单行首、列首、行尾、列尾（Ctrl+箭头）选定整个工作表：CTRL+A第21页实例5应用技巧及快捷键快速选择单元格第21页实例521应用技巧及快捷键速选择单元格移动到工作表的开头：CTRL+HOME移动到工作表的最后一个单元格。：CTRL+END移动到工作簿中下一个工作表：CTRL+PAGEDOWN移动到工作簿中前一个工作表：CTRL+PAGEUP第22页实例5应用技巧及快捷键速选择单元格第22页实例522应用技巧及快捷键常用功能键应用帮助F1编辑单元格F2重复上一操作F4固定选择初始单元格F8光标跳到菜单栏F10第23页实例5应用技巧及快捷键常用功能键应用第23页实例523应用技巧及快捷键快捷键插入新工作表：SHIFT+F11重复上一次操作：F4或CTRL+Y完成单元格输入并在选定区域中上移：SHIFT+ENTER完成单元格输入并在选定区域中右移：TAB完成单元格输入并在选定区域中左移：SHIFT+TAB显示“单元格格式”对话框：CTRL+1第24页实例5应用技巧及快捷键快捷键第24页实例524应用技巧及快捷键快捷键应用外边框：CTRL+SHIFT+&删除外边框：CTRL+SHIFT+_应用或取消字体加粗格式：CTRL+B应用或取消字体倾斜格式：CTRL+I应用或取消下划线格式：CTRL+U应用或取消删除线格式：CTRL+5隐藏行：CTRL+9取消隐藏行：CTRL+SHIFT+(左括号

隐藏列：CTRL+0（零）

取消隐藏列：CTRL+SHIFT+)右括号第25页实例5应用技巧及快捷键快捷键第25页实例525应用技巧及快捷键快捷键复制选定区域：CTRL+C剪切选定区域：CTRL+X粘贴选定区域：CTRL+V保存：CTRL+S撤消最后一次操作：CTRL+Z查找：CTRL+F新建工作簿:CTRL+N关闭当前文档：CTRL+W

第26页实例5应用技巧及快捷键快捷键第26页实例526应用技巧及快捷键快捷键打印：CTRL+P打开：CTRL+O

插入一个含SUM函数的自动求和公式：Alt+=

插入日期：CTRL+;输入时间：Ctrl+Shift+：

在公式中，显示“插入函数”对话框：Shift+F3

删除选中的单元格：Ctrl+-

插入空白单元格：Ctrl+Shift++

编辑单元格批注：Shift+F2

第27页实例5应用技巧及快捷键快捷键第27页实例527应用技巧及快捷键快捷键应用“常规”数字格式：Ctrl+Shift+~

应用“货币”格式：Ctrl+Shift+$

应用“百分比”格式：Ctrl+Shift+%

第28页实例5应用技巧及快捷键快捷键第28页实例528第29页提纲一、高级功能应用二、应用技巧及快捷键三、枢纽分析表（数据透视表）第29页提纲一、高级功能应用29了解枢纽分析表你的工作表有大量数据，你怎样了解这些数据的含义？这些数据能怎样解答你的问题？你怎样从不同的角度看这些数据？让我们一起了学习怎样解决这些疑问。第30页了解枢纽分析表你的工作表有大量数据，你怎样了解这些数据的含义30什么是枢纽分析表？数据透视表是Excel提供的一种交互式报表，可以根据不同的分析目的进行汇总、分析、浏览数据，得到想要的分析结果，它是一种动态数据分析工具。在数据透视表中可以交换行和列来查看原数据的不同汇总结果。第31页什么是枢纽分析表？数据透视表是Excel提供的一种交互式报表31枢纽分析表例子查看此资料，怎样能达到以下的分析目的？不同产品组的采购收货金额是多少？不同产品组各币别分别的金额是多少？各币别各产品组的金额是多少？各产品组在各月份的金额是多少？各币别在各月份的金额是多少？各月分的收货项数？…?第32页实例5枢纽分析表例子查看此资料，怎样能达到以下的分析目的？第32页32准备工作你想得到什么样的结果？你想从什么样的角度分析这些数据？原始数据是否有你想得到的数据？第33页准备工作你想得到什么样的结果？第33页33枢纽分析表向导把光标方到数据区域：资料枢纽分析表及图表精灵第34页选择表或图枢纽分析表向导把光标方到数据区域：资料枢纽分析表及图表精灵34枢纽分析表向导如使用精灵之前光标已放到数据区域，则自动选择第35页选择数据区域枢纽分析表向导如使用精灵之前光标已放到数据区域，则自动选择第35枢纽分析表向导选项第36页指定新生成的分析表或图表位置枢纽分析表向导选项第36页指定新生成的36枢纽分析表向导选择你要选择的汇总方式第37页把对应字段拖到对应的位置枢纽分析表向导选择你要选择的汇总方式第37页把对应字段拖到37枢纽分析表向导

设置计算方式，如加总，平均，最大，最小…第38页点击设置计算方式枢纽分析表向导设置计算方式，如加总，平均，最大，最小…第338枢纽分析表向导选择计算方式第39页选择你想要的计算方式枢纽分析表向导选择计算方式第39页选择你想要39枢纽分析表向导按产品组汇总的金额分析表第40页如想知道4-PASS产品组的详细数数据？枢纽分析表向导按产品组汇总的金额分析表第40页如想知道4-P40枢纽分析表向导如现在想看各产品组的有多少项？第41页点击这里更改计算方式枢纽分析表向导如现在想看各产品组的有多少项？第41页点击这里41枢纽分析表向导在分析表区域右键可选择修改相关选项第42页枢纽分析表向导在分析表区域右键可选择修改相关选项第42页42枢纽分析表向导在分析表区域右键可选择修改相关选项第43页枢纽分析表向导在分析表区域右键可选择修改相关选项第43页43枢纽分析表向导练习：请使用“樞紐分析練習”分析，各币别的的汇总金额第44页枢纽分析表向导练习：请使用“樞紐分析練習”分析，各币别的的汇44

第45页ThankYou!第45页ThankYou!45OFFICE系列培训之

Excel高级应用OFFICE系列培训之Excel高级应用46第47页序言Excel用户水平的5个层次刚刚开始接触基本掌握常用功能熟练使用常用功能+数据透视表+公式…+数组公式+VBA简单编程Excel技能+专业知识+行业经验新手

初级用户

中级用户

高级用户

专家第2页序言Excel用户水平的5个层次刚刚开始接触基本掌握47第48页知识结构Excel数据处理与操作Excel函数与公式Excel图表与图形ExcelVBA与宏Excel数据分析第3页知识结构Excel数据处理与操作Excel函数与公式E48第49页提纲一、高级功能应用二、应用技巧及快捷键三、枢纽分析表（数据透视表）第4页提纲一、高级功能应用49第50页目录冻结窗格

移动工作表

群组小计/分类汇总筛选

保护自定义序列验证/条件条件格式标题选择性粘贴自动格式设定

第5页目录冻结窗格移动工作表群组小计/分类汇总筛选保护50高级功能应用冻结窗格-當有一个表很多行（项次）时，你想在表的后面也想看到标题.第51页实例1高级功能应用冻结窗格第6页实例151高级功能应用移动工作表-當有一个或多个工作表想移到另外的工作簿（Excel文件），你会怎样处理？1.全部复制->在目的文件新建工作表->粘贴2.使用“移动或复制”工作表功能第52页实例1高级功能应用移动工作表第7页实例152高级功能应用你想按一定的熟性或类型对资料进行分组并可收缩和展开，你会怎样处理？群组（资料->群组及大纲->群组）小计/分类汇总(资料->小计）第53页实例1高级功能应用你想按一定的熟性或类型对资料进行分组并可收缩和展53高级功能应用筛选自动筛选（资料->筛选->自动筛选）自定义筛选第54页实例1高级功能应用筛选第9页实例154高级功能应用你有一个文件希望共享给别人看，但不希望别人修改里面的内容，你会怎样处理？保护功能文件保护（另存->工具->一般选项->设置密码）活页保护（工具->保护->保护活页簿）工作表保护（工具->保护->保护工作表）单元格保护（在单元格格式的“保护”卡中设定是否锁定，再使用工作表保护功能，则保护锁定的单元格）第55页实例1高级功能应用你有一个文件希望共享给别人看，但不希望别人修改里55高级功能应用如有一个人名，很生僻，每次都很难输入，但此人名又经常用到，你会怎样处理？自定义序列（工具->选项->自订清单->新增），豆号分隔

（如可通过“张三”的名字把“李四”名字找出来）第56页高级功能应用如有一个人名，很生僻，每次都很难输入，但此人名又56高级功能应用如你希望某些单元格控制输入数据的范围或只可选择预定的值，你会怎样处理验证/条件（资料->验证）整数实数清单日期时间文字长度第57页实例2高级功能应用如你希望某些单元格控制输入数据的范围或只可选择预57高级功能应用如你希望某些单元格在输入不同范围的数据时显示不同的格式（职工的年龄在16岁以下显示红色,以上则不显示颜色）条件格式(格式->设定格式化的条件）第58页实例3高级功能应用如你希望某些单元格在输入不同范围的数据时显示不同58高级功能应用如你有一张很多行的表需打印（预计有很多页），希望每一页都自动显示标题指定标题（档案->版面设定->页首/页尾）指定标题列（档案->版面设定->工作表->标题列）第59页实例4高级功能应用如你有一张很多行的表需打印（预计有很多页），希望59高级功能应用如你有一组数据，希望去除格式，只需要内容。或希望从垂直转为横向，你会怎样处理？选择性粘贴(编辑->选择性粘贴/右键->选择性粘贴）只粘贴值、公式、格式等与目的单元格运算转置第60页实例5高级功能应用如你有一组数据，希望去除格式，只需要内容。或希望60高级功能应用自动格式设定（格式->自动格式设定）第61页高级功能应用自动格式设定（格式->自动格式设定）第16页61第62页提纲一、高级功能应用二、应用技巧及快捷键三、枢纽分析表（数据透视表）第17页提纲一、高级功能应用62应用技巧及快捷键快速输入数据自动填充等差填充快速自动填充批量输入选中批量输入单元格输入资料Ctrl+Enter（回车）重复刚输入的值Ctrl+D（往下重复）Ctrl+R（往右重复）Ctrl+'（为英文状态下的单引号）快速复制一个单元格的资料至另一单元格（Ctrl+用鼠标点原单元格资料并拖至目标单元格）第63页实例1应用技巧及快捷键快速输入数据第18页实例163应用技巧及快捷键换行自动换行（格式/右键->存储格式->对齐方式->自动换列）单元格换行（Alt+Enter）第64页实例1应用技巧及快捷键换行第19页实例164应用技巧及快捷键插入“a”或“r”（在字体下拉列表中选择“Marlett”字体，输入a或b为“a”输入r为“r”

快速恢隐藏的列（将鼠标指针放置在被隐藏列标的分割线上，并拖动）自动适配行高或列宽（将鼠标指针放在列标或行标的分割线上，并双击）在同一单元格中输入多个测试值（工具->选项->编辑->按Enter后，移动选取范围）第65页实例1应用技巧及快捷键插入“a”或“r”第20页实例165应用技巧及快捷键快速选择单元格快速选择整个工作表的单元格（鼠标点击左上角的方块）快速选择整列或整行（鼠标点击列的方块或行的方块）快速选择整列或整行的资料快速选择所连续单元格的资料（Shitf+/）快速选择所有资料（Shitf+Alt++）选择不连续列或行或单元格资料（Ctrl+鼠标点击对应的列或行或单元格）快速跳至单行首、列首、行尾、列尾（Ctrl+箭头）选定整个工作表：CTRL+A第66页实例5应用技巧及快捷键快速选择单元格第21页实例566应用技巧及快捷键速选择单元格移动到工作表的开头：CTRL+HOME移动到工作表的最后一个单元格。：CTRL+END移动到工作簿中下一个工作表：CTRL+PAGEDOWN移动到工作簿中前一个工作表：CTRL+PAGEUP第67页实例5应用技巧及快捷键速选择单元格第22页实例567应用技巧及快捷键常用功能键应用帮助F1编辑单元格F2重复上一操作F4固定选择初始单元格F8光标跳到菜单栏F10第68页实例5应用技巧及快捷键常用功能键应用第23页实例568应用技巧及快捷键快捷键插入新工作表：SHIFT+F11重复上一次操作：F4或CTRL+Y完成单元格输入并在选定区域中上移：SHIFT+ENTER完成单元格输入并在选定区域中右移：TAB完成单元格输入并在选定区域中左移：SHIFT+TAB显示“单元格格式”对话框：CTRL+1第69页实例5应用技巧及快捷键快捷键第24页实例569应用技巧及快捷键快捷键应用外边框：CTRL+SHIFT+&删除外边框：CTRL+SHIFT+_应用或取消字体加粗格式：CTRL+B应用或取消字体倾斜格式：CTRL+I应用或取消下划线格式：CTRL+U应用或取消删除线格式：CTRL+5隐藏行：CTRL+9取消隐藏行：CTRL+SHIFT+(左括号

隐藏列：CTRL+0（零）

取消隐藏列：CTRL+SHIFT+)右括号第70页实例5应用技巧及快捷键快捷键第25页实例570应用技巧及快捷键快捷键复制选定区域：CTRL+C剪切选定区域：CTRL+X粘贴选定区域：CTRL+V保存：CTRL+S撤消最后一次操作：CTRL+Z查找：CTRL+F新建工作簿:CTRL+N关闭当前文档：CTRL+W

第71页实例5应用技巧及快捷键快捷键第26页实例571应用技巧及快捷键快捷键打印：CTRL+P打开：CTRL+O

插入一个含SUM函数的自动求和公式：Alt+=

插入日期：CTRL+;输入时间：Ctrl+Shift+：

在公式中，显示“插入函数”对话框：Shift+F3

删除选中的单元格：Ctrl+-

插入空白单元格：Ctrl+Shift++

编辑单元格批注：Shift+F2

第72页实例5应用技巧及快捷键快捷键第27页实例572应用技巧及快捷键快捷键应用“常规”数字格式：Ctrl+Shift+~

应用“货币”格式：Ctrl+Shift+$

应用“百分比”格式：Ctrl+Shift+%

第73页实例5应用技巧及快捷键快捷键第28页实例573第74页提纲一、高级功能应用二、应用技巧及快捷键三、枢纽分析表（数据透视表）第29页提纲一、高级功能应用74了解枢纽分析表你的工作表有大量数据，你怎样了解这些数据的含义？这些数据能怎样解答你的问题？你怎样从不同的角度看这些数据？让我们一起了学习怎样解决这些疑问。第75页了解枢纽分析表你的工作表有大量数据，你怎样了解这些数据的含义75什么是枢纽分析表？数据透视表是Excel提供的一种交互式报表，可以根据不同的分析目的进行汇总、分析、浏览数据，得到想要的分析结果，它是一种动态数据分析工具。在数据透视表中可以交换行和列来查看原数据的不同汇总结果。第76页什么是枢纽分析表？数据透视表是Exc