EXCEL培训(操作)

1、EXCELEXCEL常用功能讲解常用功能讲解蒋芳蒋芳 2011-10-252011-10-25EXCELEXCEL水平测试水平测试1010个个EXCELEXCEL问题问题（每个（每个2 2分）分） 1 1、您懂得使用数据透视功能进行灵活、快速的数据汇总您懂得使用数据透视功能进行灵活、快速的数据汇总2 2、您很清楚您很清楚EXCEL2003EXCEL2003表格具有表格具有256256列的数据能力列的数据能力3 3、您可以区分柱状图与饼形图的数据表述区别您可以区分柱状图与饼形图的数据表述区别4 4、您可以区分普通图柱状图和百分比堆积柱状图数据表述区别您可以区分普通图柱状图和百分比堆积柱状图数据表

2、述区别5 5、您经常使用您经常使用CRTL+1CRTL+1这个快捷键这个快捷键6 6、您善于使用您善于使用IFIF函数进行数据条件处理函数进行数据条件处理7 7、您善于使用您善于使用VLOOKUPVLOOKUP函数进行数据处理函数进行数据处理8 8、您懂得通过条件格式进行录入字符的控制您懂得通过条件格式进行录入字符的控制9 9、您懂得通过保护工作表控制单元格录入限制您懂得通过保护工作表控制单元格录入限制1010、您清楚您清楚EXCEL2003EXCEL2003文件格式为文件格式为“.xls”,“.xls”,而而EXCEL2007EXCEL2007为为“.xlsx”“.xlsx”EXCELEXC

3、EL水平测试结果水平测试结果测试评定测试评定A A类：类：0-30-3分分您可以转头走了，就当没参加过这个测试吧。如果您真想在您可以转头走了，就当没参加过这个测试吧。如果您真想在EXCELEXCEL实战实战方面有所提高，或者必须面对方面有所提高，或者必须面对EXCELEXCEL工作，那您可真需要下大功夫了。工作，那您可真需要下大功夫了。 B B类：类：4-84-8分分您应该具有一些您应该具有一些EXCELEXCEL实战能力，不过经验尚有不足，当您面对稍有艰实战能力，不过经验尚有不足，当您面对稍有艰巨的巨的EXCELEXCEL业务数据处理或业务数据处理或PPTPPT商务呈现任务的时候可能会稍许感

4、觉头疼，商务呈现任务的时候可能会稍许感觉头疼，胸闷，无从下手，一般得借助别人支持或援助才可渡过难关。胸闷，无从下手，一般得借助别人支持或援助才可渡过难关。 C C类：类：9-129-12分分您应该具备了一定的您应该具备了一定的EXCELEXCEL实战经验，或者参加过专业培训学习，具有实战经验，或者参加过专业培训学习，具有一的一的EXCELEXCEL商务办公实战能力，可以应对日常商务商务办公实战能力，可以应对日常商务OFFICEOFFICE工作。工作。 D D类：类：13-1613-16分分您应该是企业中的您应该是企业中的OFFICEOFFICE高手，同事中的榜样，您可以较为自如的处理高手，同事

5、中的榜样，您可以较为自如的处理EXCELEXCEL日常工作问题，或找到解决建议。经过深入训练，应该可以达到日常工作问题，或找到解决建议。经过深入训练，应该可以达到专业级专业级EXCELEXCEL水平。水平。 E E类：类：17-2017-20分分E E类：高手，绝对的类：高手，绝对的OFFICEOFFICE高手！高手！EXCELEXCEL高手的必修课高手的必修课工具栏窗口标题窗口标题( (显示程序名及文显示程序名及文件件名名) )列号状态栏行号标签栏编辑栏(显示当前单元格中的数据或公式,单击左边“fx”可输入公式） 工作区菜单栏左右视窗分隔条上下视窗分隔条全选按钮储存格名称栏由列标和行标组成当

6、前活动储存格地址?EXCEL2003EXCEL20031234特殊输入及字符处理特殊输入及字符处理提高工作效率的快捷方式提高工作效率的快捷方式提高工作效率的快捷键提高工作效率的快捷键操作操作EXCELEXCEL大礼包：大礼包：ExcelExcel提高实例提高实例快速输入技巧快速输入技巧5数据处理数据处理1 1、直接输入字符串的方法，先输入单引号、直接输入字符串的方法，先输入单引号2 2、输入分数，、输入分数，0 0 加空格；分式运算加空格；分式运算3 3、巧用字符连接运算符、巧用字符连接运算符 & &（1 1）给每个数字（字符）前添加相同字符串）给每个数字（字符）前添加相同字符

7、串 abcabc =abc &A2 =abc &A2（2 2）把每个数字转换成）把每个数字转换成9 9位字符，不足者前面添位字符，不足者前面添0 0 =REPT(0,9-LEN(A2) &A2=REPT(0,9-LEN(A2) &A24 4、在相对引用、绝对引用和混合引用间切换、在相对引用、绝对引用和混合引用间切换 （ $ $ ）。$A$1$A$1绝对列和绝对行绝对列和绝对行 A$1A$1相对列和绝对行相对列和绝对行$A1$A1绝对列和相对行绝对列和相对行 A1A1相对列和相对行相对列和相对行一、一、特殊输入及字符处理特殊输入及字符处理注意：输入字符串时，请确保

8、是否在英文的半角状态下输入。注意：输入字符串时，请确保是否在英文的半角状态下输入。5 5、填充，例如：、填充，例如：（1 1）如果您希望使用序列）如果您希望使用序列 1 1、 2 2、 3 3、 44，请在前两个单元格中键入，请在前两个单元格中键入 1 1 和和 2 2；（2 2）如果您希望使用序列）如果您希望使用序列 2 2、 4 4、 6 6、 88，请在前两个单元格中键入，请在前两个单元格中键入 2 2 和和 4 4；（3 3）如果您希望使用序列）如果您希望使用序列 1 1、1 1、1 1、1 1 ，请在前两个单元格中键入，请在前两个单元格中键入 1 1 和和 1 1；（4 4）如果您希

9、望在一个区域内全部输入）如果您希望在一个区域内全部输入1 1，请选中该区域，输入，请选中该区域，输入1 1，, ,按按 Ctrl+Enter Ctrl+Enter 键键, , 此法可对多工作表多区域同时输入此法可对多工作表多区域同时输入一、一、特殊输入及字符处理特殊输入及字符处理1 1、高效选择区域、高效选择区域（1 1）当区域较小时，可直接用鼠标拖动选择）当区域较小时，可直接用鼠标拖动选择（2 2）区域稍大，但在屏幕范围内，用鼠标点击左上角单元格，按住）区域稍大，但在屏幕范围内，用鼠标点击左上角单元格，按住ShiftShift键点击右下角单元格键点击右下角单元格（3 3）不连续区域选择：按住

10、）不连续区域选择：按住 CtrlCtrl键用鼠标选择或在名称框输入各区域名键用鼠标选择或在名称框输入各区域名称，区域名称间用逗号分隔如：称，区域名称间用逗号分隔如： A3:B7,A10:D17A3:B7,A10:D17（4 4）选定从工作表开始到结尾的方法：选中开始单元格（如）选定从工作表开始到结尾的方法：选中开始单元格（如A21A21），同时），同时按下按下ctrlctrl+shift+shift+end.+end. ctrlctrl+shift+shift+home+home从工作表结尾到开始从工作表结尾到开始. .（5 5）ctrlctrl+shift+shift+ +（向左小箭头），（

11、向左小箭头）， ctrlctrl+shift+shift+ +（向右小箭头），（向右小箭头）， ctrlctrl+shif+shift+t+（向上小箭头），（向上小箭头）， ctrlctrl+shift+shift+ +（向下小箭头）（向下小箭头）二、提高工作效率的快捷方式二、提高工作效率的快捷方式请记住：请记住：（1 1）CLTR+CLTR+箭头箭头表示表示快速到达快速到达；CHTRL+SHIFT+CHTRL+SHIFT+箭头箭头表示快速选定表示快速选定。（2 2）用）用ShiftShift配合鼠标配合鼠标选择连续区域或连续的工作表选择连续区域或连续的工作表，用，用CtrlCtrl键配合鼠键

12、配合鼠标标选择不连续区域或不连续的工作表选择不连续区域或不连续的工作表2 2、录入与公式填充、录入与公式填充键盘：键盘：Ctrl+D Ctrl+D 复制上一单元格的公式或内容；填充纵向区域（与双击黑复制上一单元格的公式或内容；填充纵向区域（与双击黑十字功能相仿）十字功能相仿）键盘：键盘：Ctrl+R;Ctrl+R;复制左边单元个的公式或内容；填充横向区域复制左边单元个的公式或内容；填充横向区域3 3、批量处理数据、批量处理数据（1 1）给区域每个单元格的值均输入）给区域每个单元格的值均输入100000100000（2 2）将区域的数字折成千元）将区域的数字折成千元在区域外的任意单元格（如：在区

13、域外的任意单元格（如：A22A22）输入）输入1000,1000,并复制该单元格；选中区域，并复制该单元格；选中区域，选择性粘贴选择性粘贴-除，确定除，确定4 4、工作表、工作表（1 1）按住）按住Ctrl,Ctrl,用鼠标左键拖动屏幕下边的工作表标签，可复制一个相同用鼠标左键拖动屏幕下边的工作表标签，可复制一个相同的表格；的表格；（2 2）选中几个工作表再进行画表等操作，可对多个工作表进行操作；）选中几个工作表再进行画表等操作，可对多个工作表进行操作；（3 3）选中多个工作表设置页脚页码并打印预览，工作簿页码连续；）选中多个工作表设置页脚页码并打印预览，工作簿页码连续；二、提高工作效率的快捷

14、方式5 5、如何使空白单元格等于上一个单元格、如何使空白单元格等于上一个单元格 （1 1）选中数据区域）选中数据区域（2 2）CTRL+GCTRL+G打开定位窗口，定位条件，空值打开定位窗口，定位条件，空值（3 3）输入）输入“+ +”。（4 4）选中上一单元格。）选中上一单元格。 即：即：=+=+要等于的单元格要等于的单元格二、提高工作效率的快捷方式1 1、ALTALT系列快捷键系列快捷键（1 1）AltAlt+enter+enter拆分同一单元格内多行文字拆分同一单元格内多行文字（2 2）Alt+=Alt+=，自动求和，自动求和（3 3）Alt+0165 = Alt+0165 = （4 4

15、）ALT+0178 =ALT+0178 =（5 5）ALT+0179 =ALT+0179 =（6 6）ALT+0177 =ALT+0177 =（7 7）ALT+0137 =ALT+0137 =2 2、SHIFTSHIFT系列快捷键系列快捷键（1 1）按住）按住shiftshift拖动单元格拖动单元格= =剪切剪切+ +插入单元格插入单元格+ +粘贴粘贴（2 2）选中一个单元格，按）选中一个单元格，按shift+F2shift+F2 键，可以插入批注。键，可以插入批注。三、提高工作效率的快捷键三、提高工作效率的快捷键3 3、CTRLCTRL系列快捷键系列快捷键（1 1）按住）按住ctrl+shi

16、ftctrl+shift拖动单元格拖动单元格= =复制复制+ +插入单元格插入单元格+ +粘贴粘贴（2 2）Ctrl +9Ctrl +9隐藏一行单元格隐藏一行单元格 Ctrl+Shift+9Ctrl+Shift+9取消隐藏行取消隐藏行（3 3）Ctrl+0Ctrl+0隐藏一列单元格隐藏一列单元格 Ctrl+Shift+0Ctrl+Shift+0取消隐藏列取消隐藏列（4 4）Ctrl+;Ctrl+;输入今天的日期输入今天的日期 Ctrl+Shift+;Ctrl+Shift+;输入当前的时间输入当前的时间（5 5）Ctrl+“-”Ctrl+“-”（减号）删除单元格（减号）删除单元格/ /行行/ /

17、列列 Ctrl+Shift +“+”Ctrl+Shift +“+”（加号）插入单元格（加号）插入单元格/ /行行/ /列列（6 6） Ctrl+1 Ctrl+1 快速打开格式对话框快速打开格式对话框（7 7）+可以将当前工作表中的公式显示出来可以将当前工作表中的公式显示出来, ,再按再按+则则恢复恢复. .（8 8）Ctrl+f9Ctrl+f9最小化窗口最小化窗口（9 9）Ctrl+f10Ctrl+f10最大化和还原窗口最大化和还原窗口 (10) Ctrl+Shift+_ (10) Ctrl+Shift+_删除选定单元格区域的边框删除选定单元格区域的边框三、提高工作效率的快捷键三、提高工作效率

18、的快捷键3 3、CTRLCTRL系列快捷键系列快捷键（1 1）Ctel+Tab Ctel+Tab 可在程序与程序，表簿与表簿之间转换打开。可在程序与程序，表簿与表簿之间转换打开。（2)2)使用使用ctrl+PageDownctrl+PageDown和和ctrl+PageUpctrl+PageUp可以在工作表之间向前向后切换可以在工作表之间向前向后切换（3)3)如果你用的是带滚轮的鼠标，那么先按住如果你用的是带滚轮的鼠标，那么先按住“Ctrl”Ctrl”键，再拨动滚轮吧！键，再拨动滚轮吧！可以快速改变显示比例哟！可以快速改变显示比例哟！注意注意: 有时在中文输入状态下会有所冲突有时在中文输入状态

19、下会有所冲突, ,将输入法转换为英文即可将输入法转换为英文即可. .三、提高工作效率的快捷键三、提高工作效率的快捷键4 4、功能键系列快捷键功能键系列快捷键 F2F2用来修改单元格内容，像改文件名一样简单用来修改单元格内容，像改文件名一样简单 F3 F3用来打开粘贴名称对话框用来打开粘贴名称对话框 F4 F4重复上一步操作重复上一步操作. 这招在隔行删除、多点复制等方面用起来很爽这招在隔行删除、多点复制等方面用起来很爽 F5 F5用来打开定位对话框用来打开定位对话框5 5、工具、工具选项使用技巧选项使用技巧（1 1）工具）工具- -选项选项- -视图中视图中, ,在在“窗口选项窗口选项”下去掉

20、下去掉“零值零值”后后, ,余额为余额为0 0就不就不显示了。显示了。（2 2）工具选项视图网格线前面的对勾去掉可以不显示网格线）工具选项视图网格线前面的对勾去掉可以不显示网格线（3 3）工具选项批注）工具选项批注.三、提高工作效率的快捷键三、提高工作效率的快捷键1 1、快速输入大量含小数点的数字快速输入大量含小数点的数字 单击“工具”/“选项”/“编辑”标签，在弹出的对话框中选中“自动设置小数点”复选框，然后在“位数”微调编辑框中键入需要显示在小数点右面的位数就可以了。以后我们再输入带有小数点的数字时，直接输入数字，而小数点将在回车键后自动进行定位2 2、快速输入大量相同数据、快速输入大量相

21、同数据 首先同时选中需要填充数据的单元格。若某些单元格不相邻，可在按住Ctrl键的同时，点击鼠标左键，逐个选中；其次输入要填充的某个数据，按住Ctrl键的同时，按回车键，则刚才选中的所有单元格同时填入该数据3 3、快速删除工作表中空行快速删除工作表中空行 4 4、快速输入多个重复数据快速输入多个重复数据 （录制宏）（录制宏）5 5、快速处理多个工作表快速处理多个工作表 四、快速输入技巧四、快速输入技巧五、数据处理五、数据处理1、自动填充、自动填充2、定义名称、定义名称3、数据有效性、数据有效性4、自定义格式、自定义格式5、条件格式、条件格式6、分类汇总、分类汇总7、合并计算、合并计算8、数据透

22、视表、数据透视表五、数据处理五、数据处理1、自动填充、自动填充 等差填充等差填充 日期填充日期填充 文字填充文字填充 特定填充特定填充五、数据处理五、数据处理2、定义名称、定义名称1、插入名称。 “插入”-“名称”-“定义”2、使用名称框命名、使用名称框命名3、指定名称。 “插入”-“名称”-“指定”五、数据处理五、数据处理2、定义名称、定义名称全局定义全局定义 与与 局部定义：局部定义：1、默认情况下，为全局定义2、局部定义命名方法：工作表名称+半角感叹号+名称命名规则：命名规则：1、可用数字与字母组合，但不能以数字开头；2、不能以R、C、r、c作为名称，如R、C在R1C1引用样式中表示行、

23、列；3、名称中不能包含空格，可用下划线或点号代替；4、不能使用除了下换线、点号和反斜杠（）意外的其他符号。允许使用问号，但不能作为名称的开头；5、名称字符不能超过255个；6、字母不区分大小写。五、数据处理五、数据处理3、数据有效性、数据有效性五、数据处理五、数据处理3、数据有效性、数据有效性停止不能输入不符合条件的数据，点击“重试”可重新输入，点击“取消”则取消输入。警告可以选择是否继续输入数据，点击“是”可以强行输入不符合条件数据，点击“否”可以重新输入数据，点击“取消”则取消输入。信息可选择是否输入数据，点击“确定”可以直接输入数据，点击“取消”可以取消输入。实战练习：实战练习：选择输入

24、企业类型，并根据不同的企业类型，选择不同的企业名称。选择输入企业类型，并根据不同的企业类型，选择不同的企业名称。建立分类下拉列表填充项。建立分类下拉列表填充项。“数据数据有效性有效性”(1)(1)、在、在Sheet2Sheet2中，将企业建立一个企业名称数据库。中，将企业建立一个企业名称数据库。(2)(2)、选中、选中A A列列(“(“工业企业名称按类别工业企业名称按类别( (如如“工业企业工业企业”、“商业企业商业企业”、“个体企业个体企业”等等) )分别输入分别输入不同列中，不同列中，”名称所在列名称所在列) )，在，在“名称名称”栏内，输入栏内，输入“工业企业工业企业”字符后，按字符后，

25、按“回车回车”键进行确键进行确认。认。 仿照上面的操作，将仿照上面的操作，将B B、CC列分别命名为列分别命名为“商业企业商业企业”、“个体企业个体企业”(3)(3)、切换到、切换到Sheet1Sheet1中，选中需要输入中，选中需要输入“企业类别企业类别”的列的列( (如如C C列列) )，执行，执行“数据数据有效性有效性”命令，打开命令，打开“数据有效性数据有效性” ” 对话框。在对话框。在“设置设置”标签中，单击标签中，单击“允许允许”右侧的下拉按钮，选中右侧的下拉按钮，选中“序列序列”选选项，在下面的项，在下面的“来源来源”方框中，输入方框中，输入“工业企业工业企业”，“商业企业商业企

26、业”，“个体企业个体企业”序列序列( (各各元素之间用英文逗号隔开元素之间用英文逗号隔开) )，确定退出。，确定退出。 再选中需要输入企业名称的列再选中需要输入企业名称的列( (如如D D列列) )，再打开，再打开“数据有效性数据有效性”对对 话框，选中话框，选中“序列序列”选项后，选项后，在在“来源来源”方框中输入公式：方框中输入公式：=INDIRECT(C1)=INDIRECT(C1)，确定退出。，确定退出。(4)(4)、选中、选中C C列任意单元格列任意单元格( (如如C4)C4)，单击右侧下拉按钮，选择相应的，单击右侧下拉按钮，选择相应的“企业类别企业类别”填入单元格中。然后填入单元格

27、中。然后选中该单元格对应的选中该单元格对应的D D列单元格列单元格( (如如D4)D4)，单击下拉按钮，即可从相应类别的企业名称列表中选择，单击下拉按钮，即可从相应类别的企业名称列表中选择需要的企业名称填入该单元格中。需要的企业名称填入该单元格中。 提示：在以后打印报表时，如果不需要打印提示：在以后打印报表时，如果不需要打印“企业类别企业类别”列，可以选中该列，右击鼠标，选列，可以选中该列，右击鼠标，选“隐藏隐藏”选项，将该列隐藏起来即可。选项，将该列隐藏起来即可。五、数据处理五、数据处理五、数据处理五、数据处理4、自定义格式、自定义格式 可为可为4种类型的数值指定不同的种类型的数值指定不同的

28、格式：正数、负数、零值和文本。格式：正数、负数、零值和文本。“大于条件值”格式；“小于条件值”格式；“等于条件值”格式；文本格式 格式代码组成结构：格式代码组成结构：注：没有特别指定条件值的时候，注：没有特别指定条件值的时候，默认条件值为默认条件值为0 节代码与节节代码与节代码之间用代码之间用英文状态下英文状态下的分号的分号“;”隔开。隔开。快捷键 Ctrl+1实战练习：编制设备编号，不足实战练习：编制设备编号，不足10位，编号前补位，编号前补0，并在，并在10位位编号前统一加编号前统一加“No：”。五、数据处理五、数据处理5、条件格式条件格式格式格式条件格式条件格式五、数据处理五、数据处理6

29、、分类汇总、分类汇总可手动创建分级显示和自动生成分级显示。可手动创建分级显示和自动生成分级显示。 注意：注意：1、一个工作表内在同行方向、一个工作表内在同行方向上或列方向上只能创建一上或列方向上只能创建一个分级显示；个分级显示；2、在同一个分级显示中，最、在同一个分级显示中，最多允许有多允许有8个层次关系个层次关系 五、数据处理五、数据处理7、合并计算合并计算例：年销售产品的统计计算一、数据透视表一、数据透视表想看各种数据之间的交叉、组合想看各种数据之间的交叉、组合关系关系BOSSBOSS随时要求你提供某一数据汇总随时要求你提供某一数据汇总每周、每月要编制数据报告每周、每月要编制数据报告每天上

30、班第一件事，打开每天上班第一件事，打开ExcelExcel，输入数据输入数据1 1、ERP?ERP?2 2、OAOA？3 3、专用统计软件？、专用统计软件？4 4、ExcelExcel筛选、运算、筛选、运算、摘录？摘录？数据透视表数据透视表 Excel自带自带 独立于原始数据独立于原始数据 快速汇总快速汇总 想看啥就看啥想看啥就看啥五、数据处理五、数据处理8、数据透视表、数据透视表为数据透视表报表准备数据为数据透视表报表准备数据确保数据以表格形式布局确保数据以表格形式布局避免在分节标题中存储数据避免在分节标题中存储数据消除数据源中的空白和空单元格消除数据源中的空白和空单元格有效的数据源设计要点有效的数据源设计要点数据源的第一行由字段标签或者各列信息的标题组成数据源的第一行由字段标签或者各列信息的标题组成数据源中的每一列表示惟一一类数据数据源中的每一列表示惟一一类数据数据源中的每一行表示各列中的单独一项数据源中的每一行表示各列中的单独一项五、数据处理五、数据处理创建数据透视表创建数据透视表1、选中数据源（默认数据源区域为已选中的整个列表）选中数据源（默认数据源区域为已选中的整个列表）2、菜单选择及步骤说明（数据菜单选择及步骤说明（数据数据透视表和数据透视图）数据透视表和数据透视图）3、框架及工具栏说明框架及工具栏说明4、内容拖放内容拖放5、格式设