1、Microsoft Office Excel 培训,2010.1.21,MS Excel 简介,什么是MS Excel?MS Excel是由微软公司开发的电子制表软件。提供输入数字或数据在一个由行和列组成的电子表格内，来进行对于数据的计算，统计，分析和制表的工具。为什么需要使用Excel电子表格软件(如MS Excel或 Lotus 1-2-3)能够非常快捷和方便的进行数据的处理和计算等功能，同时能够快速的创建图表来进行数据分析。,添加自定义工具栏,问题描述用户在制作不同的工作簿时，可以根据需要定义不同的工具栏。这样可以加快操作的速度，提高工作效率。但下次再打开工作簿时，工具栏往往就会变样了。
2、解决方案要解决这个问题，可以将该工作簿的工具栏附加到这个工作簿上去，具体操作方法如下。1打开需要设置的工作簿，单击“工具”“自定义”命令。弹出“自定义”对话框，选择“工具栏”选项卡，,添加自定义工具栏,添加自定义工具栏,2单击“新建”按钮，弹出“工具栏名称”对话框。输入新建工具栏的名称，单击“确定”按钮， 3选择“命令”选项卡，进行添加按钮或菜单的操作。添加完所需的全部按钮和菜单后，单击“关闭”按钮。4单击“工具”“自定义”命令，选择“工具栏”选项卡。单击“附加”按钮，弹出“附加工具栏”对话框中。选择要附加的自定义工具栏，单击“复制”按钮。单击“确定”按钮完成操作，,添加自定义工具栏,用文字显
3、示工具按钮,问题描述 用户在工作中经常想到这样的问题，就是工具栏中按钮都是用图表或符号来表示的。必须用鼠标指到的时候才会文字出现，不会直接显示。用户对这些图标并不熟悉，给操作带来了不便。 解决方案 可以通过设置让它直接显示文字，具体操作如下。 1单击“工具”“自定义”命令，弹出“自定义”对话框，不用去管它。右击系统工具栏上的工具按钮，单击菜单中的“总是只用文字”或者“图像和文本”命令。 2经过这样的设置后按钮就会显示文字提示了。不过并不是每个工具按钮都有文字提示的，有的工具按钮该命令项是不可用的。,用文字显示工具按钮,更改Excel程序的背景,问题描述 用户在工作中每天都要使用Excel程序，
4、总是盯着一种界面太过于单调，容易使人感到疲惫。可以通过更改界面背景来进行一下调节。 解决方案 单击“格式”“工作表”“背景”命令，弹出“工作表背景”对话框，选择一张喜欢的图片，单击“确定”按钮完成操作。,同时显示多张工作表,问题描述 用户在编辑工作簿的过程中，有时需要查看或引用多张工作表上的数据。这就必须在各个窗口中来回的切换，比较的麻烦，降低了工作效率。 解决方案 可以设置窗口中显示多张工作表，这样可以帮助用户进行编辑，具体操作如下。 1单击“窗口”“新建窗口”命令，切换至新的窗口。选择需要显示的工作表。对每张需要显示的工作表重复这项操作。 2单击“窗口”“重排窗口”命令，弹出“重排窗口”对
5、话框。在“排列方式”栏下，选择合适的选项，单击“确定”,避免数据意外丢失,问题描述用户在工作中有时会遇到断电，死机等意外情况发生。由于没有及时保存而造成了数据丢失，浪费了用户的时间和精力。解决方案其实可以启用Excel的“自动保存”功能来避免因此而赞成的数据丢失，具体操作如下。1单击“工具”“选项”命令，弹出“选项”对话框。选择“保存”选项卡2选择“保存自动恢复信息”复选框，在后面的时间框中，可以设置自动保存的时间间隔。单击“确定”按钮完成操作，经过这样的设置后系统就会每隔一定的时间就会自动保存一下文件。,避免数据意外丢失,让系统自动选择输入法,问题描述用户在工作表中输入数据时，有的文字说明，
6、有的是具体数据。需要频繁的切换输入法，非常的麻烦，降低了用户的工作效率。解决方案可以利用“有效性”功能来设置使其自动切换输入法，具体操作如下。1选择需要进行设置的单元格区域，将要设置的输入法激活。单击“数据”“有效性”命令，弹出“有效性检查”对话框。2选择“输入法模式”选项卡，在“模式”下拉列表中选择“打开”选项，单击“确定”按钮完成操作,让系统自动选择输入法,查看不同页面,问题描述用户在Excel中编辑多个页面的表格时，如果需要在一个页面中查看另一个页面。得在不同的页面之间切换，比较的麻烦。解决方案可以利用Excel的“照相机”功能来帮助用户在不同页面上编辑表格，具体操作如下。1单击“工具”
7、“自定义”命令，弹出“自定义”对话框，选择“命令”选项卡。2在“类别”栏中选择“工具”选项，在右边“命令”栏中选择“照相机”选项。将其拖到工具栏上，单击“关闭”按钮,查看不同页面,查看不同页面,3如果要在编辑“Sheet2”工作表时查看“Sheet1”工作表。在“Sheet1”工作表中拖动鼠标选择需要查看的内容，单击工具栏上“照相机”按钮，选定区域就被拍了下来。4打开“Sheet2”工作表中，在需要显示这些数据的位置上右击，被“拍摄”的数据会立即粘贴过来了。注意：粘贴到“Sheet2”工作表中的“照片”的内容将会随着“Sheet1”工作表中的改变同步发生变化,查看不同页面,自动修改建立备份,问
8、题描述用户在编辑工作表时可能会遇到这样的情况。就是在对工作表数据进行修改后，觉不很满意，需要重新再改。但现在很难恢复原先的数据了。解决方案用户可以设置程序建立备份文件，这样就可以恢复原先数据了，具体操作如下。1打开工作表，单击“文件”“另存为”命令，弹出“另存为”对话框。单击“工具”按钮，如图所示。,自动修改建立备份,自动修改建立备份,2单击下拉列表中的“常规选项”命令，弹出“保存选项”对话框。选择“生成备份文件”复选框。3同时还可以设置该文件的“打开的权限密码”和“修改权限密码”选项。该密码对备份的文件无效， 4单击“确定”按钮返回“另存为”对话框，输入保存的文件名，在文件类型中选择适当的选
9、项作为文件类型.5设置完成后，单击“保存”按钮进行备份。经过这样的设置后，对于修改后的文档执行保存时。系统会自动在同一目录下生成一个备份文件，用于保存修改前的数据。,将工作表内容以图片格式插入Word文档,问题描述用户在实际工作中有时候需要将工作表中的内容，以图片形式插入到Word文件中。解决方案用户可以利用Excel的“复制图片”功能进行操作，具体如下。1选择要转换成图片的单元格，按下【Shift】键时，单击“编辑”“复制图片”命令，弹出“复制图片”对话框。2在对话框中选择“如屏幕所示”和“图片”功能项，单击“确定”按钮.,以0为头的数据输入,问题描述用户在使用Excel制作工作表过程中，常
10、会遇到这样的情况，就是输入一个以“0”开头的数据，系统会自动把“0”消除。解决方案可以实现以“0”开头的数据，具体操作如下：在输入数据前先输入一个“”（单引号），这样跟在后面的以“0”开头的数字的“0”就不会被系统自动消除。,自动输入时间,问题描述用户在使用Excel编辑工作表时，有时需要输入日期和时间，日期和时间的输入形式非常麻烦，而且还不容易记住。解决方案如果想要快速的输入日期和时间，可以通过快捷键帮忙，具体操作如下。选中需要输入日期和时间的单元格，按下【Ctrl】+【；】组合键可输入当前日期。如果按下【Ctrl】+【Shift】+【；】组合键即可输入当前时间；如果希望日期、时间随当前系统
11、的日期、时间自动更新，可以利用函数来实现。在单元格中输入“=today()”可以得到当前系统日期，输入“=now()”可以得到当前系统时间和日期。,实现复杂数据的排序,问题描述用户在使用Excel编辑工作表过程中，有时会遇到这样的麻烦事，就是对一些复杂的数据进行排序，如：型号140025第107台，这样的排序就比较麻烦了。解决方案其实可以利用“分列”功能进行设置的，具体操作如下。1选择需要进行排序的列，单击“数据”“分列”命令。弹出“文本分列向导”对话框。在这里选择“分隔符号”选项。2单击“下一步”按钮，弹出向导下一个界面，在“分隔符”选项框中选择一种符号做为分隔符，在这里选择“其它”选项，在
12、后面的文本框中输入想要的分隔符3单击“下一步”按钮，弹出向导完成界面，在“数据”预览框中可以看到分列后的安排，单击“完成”按钮。分完列后就可以对它们进行分别的排序了。,在Excel中换行,问题描述用户工作表中输入的文本或数据时，由于数据很长需要在单元格中换行，但是系统默认不能直接换行。解决方案如果想在工作表中进行换行操作，有两种方法可以实现，具体操作如下。方法一：自动换行1选择要进行设置的单元格范围。单击“格式”“单元格”命令，弹出“单元格格式”对话框。2选择“对齐”选项卡，选择“自动换行”选项。单击“确定”按钮完成操作。方法二：强行换行将光标移到需要换行的位置上，按下【Alt】+【Enter
13、】键即可实现换行操作。,在常规格式下输入分数,问题描述用户在编辑工作表时，有时需要在单元格中输入如“2/5、6/7”形式的分数。但这时系统都会自动将其转换为日期格式，可以单元格格式设置为“分数”形式再进行输入，但这样比较的麻烦。解决方案其实在“常规”模式下也可以实现分数输入的，具体操作如下。在输入分数前，先输入“0空格符”。再在后面输入分数即可。如输入“2/3”，得输入“0空格2/3”。注意：利用此方法输入的分数的分母不能超过99，如果输入“2/101”，系统会将其转换为近似值“1/50”,筛选数据,问题描述用户在编辑工作表时，有时想在已经输入的数据中筛选出需要的一些数据来。解决方法Excel
14、程序中就有这个功能，但是一次只能对工作表中的一个区域进行筛选，具体操作如下。1选择要进行筛选区域单元格，单击“数据”“筛选”“自动筛选”命令。这时在选择的区域中会以列为单位多出一些小的箭头来。2单击包含数字列中的箭头，单击菜单中的“（“自定义”）“命令。弹出“自定义筛选方式”对话框。,筛选数据,筛选数据,3选择筛选的条件，如“大于”、“小于”、“大于或等于”或“小于或等于”等。在右边的框中选择或输入数字。4若要添加另一个条件，选择“与”或“或”选项，进行下一个的条件设置。另外也进行文本的筛选操作。5条件设置完成后，单击“确定”按钮完成操作,如何取消数据筛选,问题描述用户在操作过程中如果对筛选结
15、果不满意，可以取消筛选。解决方案如果要取消对某一列进行的筛选，单击该列首单元格右端的下拉箭头，在弹出的菜单中选择“全部”选项即可完成。如果要取消对所有列进行的筛选，单击“数据”“筛选”“全部显示”命令即可。如果想删除区域或列表中的筛选箭头，再次单击“数据”“筛选”“自动筛选”命令取消掉它前面的对勾即可。,删除表格中的空行,问题描述用户在数据统计过程中，由于种种原因，会在表格中出现一些空行。影响了数据分析的结果，删除时如果这些空行不连续的时候，比较的麻烦。解决方案可以利用Excel中提供的“定位”功能，一次性定位表格中所有空行，然后将其删除，具体操作如下。1单击“编辑”“定位”命令，弹出“定位”
16、对话框，单击“定位条件”按钮，如图所示。,删除表格中的空行,删除表格中的空行,2弹出“定位条件”对话框中选择“空值”选项，单击“确定”按钮，如图2.60所示。这时表格中所有的空行全部选中。右击选中区域，单击菜单中的“删除”“整行”命令完成操作,如何让表格行列转置,问题描述用户在使用Excel编辑完成一张表格后，想把表格的行列进行互换，这样就方便打印或其它操作。解决方案可以通过“选择性粘贴”功能进行设置，具体操作如下。1选中需要进行行列转换的单元格区域，单击“编辑”“复制”命令。2选择要放置该表的位置，单击“编辑”“选择性粘贴”命令，弹出“选择性粘贴”对话框。选中“转置”选项，单击“确定”按钮完
17、成操作。,隐藏行或列,问题描述在工作中为了保护数据的安全，可以利用行列的隐藏来实.解决方案要实现行或者列的隐藏有很多方法，一般都是利用“隐藏”命令，除了这个命令外还有几种方法可以实现隐藏，具体操作如下。方法一：鼠标拖动移动鼠标指针至要隐藏的行或者列的最下边或最右边，当鼠标指针变成了一个黑色的带有上下箭头的十字状时，按下鼠标左键，拖动鼠标使其不可以见即可。方法二：使用快捷键按下组合键【Ctrl】+【9】隐藏行；按下组合键【Ctrl】+【0】隐藏列。如果要取消隐藏，只需同时按下【Ctrl】+【Shift】+【9】与【Ctrl】+【Shift】+【0】组合键,恢复被隐藏行或列,问题描述在工作了设置了
18、行列的隐藏，如果要进行取消隐藏，除了使用“取消隐藏”命令外，还有一些快捷的方法使用。解决方案假如要取消的是行的隐藏，可做如下操作。将鼠标指针移动到已经隐藏了行的最前位置，这时指针变成横向为号，纵向为黑色实心双箭头的十字形状，双击鼠标行就会显示出来了,设置单元格下拉选择输入,问题描述用户在制作工作表时由于特殊要求，想让用户在输入数据时，选择已经设置好的内容，不要求自行输入。解决方案可以为单元格设置一个供选择的下拉列表，通过“数据”“有效性”命令来实现的，具体操作如下。1选择需要设置自动选择列表的单元格，单击“数据”“有效性”命令，弹出“有效性”对话框，选择“设置”选项卡，在“允许”下拉列表中选“
19、序列”选项2这时对话框会增加“来源”选项，在其下面的输入框中，输入供用户选择的序列。不同的选择项用半角“,”号分开， 3单击“确定”按钮完成操作。经过这样设置后，当选择该单元格时，就会在单元格右边会出现一个向下的黑色箭头，单击该箭头就会弹出一个选择输入列表了,用颜色给数据进行分类,问题描述为了查找和操作的方便，用户可以对工作表的数据进行分类，分类后可以使用不同的颜色进行区别。解决方案1选择需要进行设置的数据，单击“格式”“单元格”命令，弹出“单元格”对话框，选择“数字”选项卡。2在“分类”列表框中选择“自定义”选项，在“类型”框中输入“黑色=60#0.00;”，这段代码的表示意思是：如果数值大
20、于或等于60就用红色表示，小于60用黑色表示。单击“确定”按钮完成操作。,用颜色给数据进行分类,在单元格中引用函数,问题描述用户用Excel制作表格的时候，经常需要对数值进行复杂的运算。解决方案这时就要使用到函数，这样速度快而且准确，还可以方便日后对表格的修改。利用Excel中的函数向导功能可以快速完成函数的填加，具体操作如下。1把光标定位在需要使用函数的单元格中，单击“编辑栏”左侧的插入函数按钮 。这时在目标单元格中就会出现一个“=”号，并同时弹出“插入函数”对话框。2在对话框的顶部，可以查找相关的函数。在“或选择类别”栏中可以选择需要的种类， 3选择好函数后，在下面的“选择函数”框中，就列
21、出了具体的函数名称。选中一个函数，会自动出现该函数的功能解释，单击“确定”按钮。4弹出“函数参数”对话框，单击“Number”文本框右侧的按钮。再用鼠标选择数据源单元格，该单元格的地址就自动添加到“Number”文本框中了， 5单击“确定”按钮完成操作，这时运算结果就被添加到目标单元格中了。,Vlookup函数使用说明,Vlookup:在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。当比较值位于数据表首列时，可以使用函数 VLOOKUP 代替函数 HLOOKUP。 在 VLOOKUP 中的 V 代表垂直。 语法 VLOOKUP(lookup_value,tab
22、le_array,col_index_num,range_lookup) Lookup_value 为需要在数组第一列中查找的数值。Lookup_value 可以为数值、引用或文本字符串。 Table_array 为需要在其中查找数据的数据表。可以使用对区域或区域名称的引用，例如数据库或数据清单。 如果 range_lookup 为 TRUE，则 table_array 的第一列中的数值必须按升序排列：、-2、-1、0、1、2、-Z、FALSE、TRUE；否则，函数 VLOOKUP 不能返回正确的数值。如果 range_lookup 为 FALSE，table_array 不必进行排序。,Vl
23、ookup函数使用说明,通过在“数据”菜单中的“排序”中选择“升序”，可将数值按升序排列。 Table_array 的第一列中的数值可以为文本、数字或逻辑值。文本不区分大小写。 Col_index_num 为 table_array 中待返回的匹配值的列序号。Col_index_num 为 1 时，返回 table_array 第一列中的数值；col_index_num 为 2，返回 table_array 第二列中的数值，以此类推。如果 col_index_num 小于 1，函数 VLOOKUP 返回错误值值 #VALUE!；如果 col_index_num 大于 table_array 的
24、列数，函数 VLOOKUP 返回错误值 #REF!。 Range_lookup 为一逻辑值，指明函数 VLOOKUP 返回时是精确匹配还是近似匹配。如果为 TRUE 或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于 lookup_value 的最大数值；如果 range_value 为 FALSE，函数 VLOOKUP 将返回精确匹配值。如果找不到，则返回错误值 #N/A。,Vlookup函数使用说明,说明 如果函数 VLOOKUP 找不到 lookup_value，且 range_lookup 为 TRUE，则使用小于等于 lookup_value 的最大值。 如果 lo
25、okup_value 小于 table_array 第一列中的最小数值，函数 VLOOKUP 返回错误值 #N/A。 如果函数 VLOOKUP 找不到 lookup_value 且 range_lookup 为 FALSE，函数 VLOOKUP 返回错误值 #N/A,查找函数错误,问题描述Excel系统中提供的函数是十分丰富的，但是在使用一些比较复制的嵌套函数时，一旦出现错误，要找到错误原因是比较困难的。解决方案可以使用“公式求值”功能来检测函数，找出错误。“公式求值”不是默认的按钮，将其找出来，具体操作如下。1单击“工具”“自定义”命令，弹出“自定义”对话框，选择“命令”选项卡。2在“类别”
26、列表中选择“工具”选项，在“命令”列表中选择“公式求值”选项，将其拖到工具栏中适当的位置，如图所示。,查找函数错误,查找函数错误,3选择包含函数的单元格，单击“公式求值”按钮。在弹出的对话框中，会用下划线表示公式中的执行步骤。单击“求值”按钮，可以执行公式，同时观察下划线表达式的运算结果是否正确，从而找出公式的错误之处，如图所示。,设置打印图表,问题描述用户在工作中做好了的工作表需要进行打印，里面包含的图表也要打印出来。解决方案如果要进行图表的打印，具体操作如下。右击该图表，单击菜单中的“图表区格式”命令，弹出“图表区格式”对话框，在弹出的对话框中选择“属性”选项卡，单击选择“打印对象”复选框
27、，单击“确定”按钮完成操作,Excel 宏与 Personal.xls,大家在日常工作中用excel，少不了要自定义一些宏，这些宏一般都是存在一个叫“personal.xls“的文件中，这样下次你需要这些宏功能的时候就不用再次录制了，那么这个PERSONAL.XLS到底是何方神圣？它又存在于什么地方？为什么有的时候我会找不到存在于PERSONAL.XLS的宏呢？其实当你刚刚按装好了新的OFFICE，在你录制第一个宏以前，PERSONAL.XLS是不存在的，当你在EXCEL里面录制好了第一个宏并且保存在PERSONAL MACRO WORKBOOK的时候，Office会自动创建这个文件，当你关闭
28、EXCEL时，EXCEL会提示你要不要保存PERSONAL.XLS，点击确认按钮，这时PERSONAL.XLS就已经存在于“C:Documents and Settings你的用户名Application DataMicrosoftExcelXLSTART下边了，并且你刚刚录制的宏以后也可以重复使用.,Excel 宏与 Personal.xls,默认情况下PERSONAL.XLS是隐藏的，其实在我们每次打开EXCEL的时候，EXCEL都会自动把PERSONAL.XLS打开并隐藏于后台，所以对于我们用户来说它是透明的，我们用户感觉不到PERSONAL.XLS的存在，而当我们需要用到存放于PERS
29、ONAL.XLS里面的宏的时候，EXCEL会自动调用，你也可以用WindowUnHide命令把PERSONAL.XLS显示出来，不过最好在你退出EXCEL之前再把它隐藏回去或者在退出EXCEL时不要保存，否则下次你打EXCEL的时候，EXCEL就不是默认创建一个新文档了，而是自动把PERSONAL.XLS显示在你面前，这样就很容易不小心破坏掉你千辛万苦才录制好的宏喽.当你精心录制好宏之后，记得要去备份一下吆，否则一但你的系统或是PERSONAL.XLS出了问题，那可就损失惨重喽，如果你有备份PERSONAL.XLS的话，当你系统和office都恢复后，只要把PERSONAL.XLS再copy回
30、C:Documents and Settings你的用户名Application DataMicrosoftExcelXLSTART下边就可以了。 还有，如果你的EXCEL不会自动把PERSONAL.XLS打开，而是每次要你先手动打开它才可以调用里面的宏的话，那一定是你把PERSONAL.XLS放错位置了，他细检查一下是不是放对了目录。,Excel 宏与 Personal.xls,EXCEL同样也可以自动加载别的目录下的文件以便共享那个文件里面的宏，比如说我不喜欢把我的宏放到C盘下面，因为每次电脑出了问题，在我重新恢复OS之前我都要记得去备份它，在恢复之后还要再考备回去，麻烦，并且放PERSO
31、NAL.XLS的这个目录可不是很好找的，是隐藏的，所以我在我的D盘建了一个我自己专用来放宏文件的目录-MyMacro, 我把我的宏都放在一个叫MyMacro.xls的文件里放到D盘的MyMacro目录下，然后在EXCEL的ToolsOptionsGeneral“下有一个叫At startup, Open all files in:里指定我自己的 Excel 启动宏文件的文件夹-D:MyMacro，这样每次EXCEL启动都会自动加载我的宏文件，并且我也不需要在重装WINDOWS的时候动备份什么PERSONAL 了，如果用这种方法，记得用EXCEL把你的宏文件也设置成隐藏的(WindowHide)
32、, 在退出excel的时候保存一下就行了，这样下一次你再启动EXCEL的时候，你的这个宏启动文件就会和PERSONAL.XLS一样，被自动隐藏在后台了。,FAQ,公式无法自动更新/公式计算很慢如果公式内容在更新后没有更新结果，请检查系统的自动计算功能有没打开选择 Tools- Options,FAQ,FAQ,在设置窗口中选择Calculation. 下图方框中的设置就是自动计算的选项，如果现在Manual的话系统就不会自动更新结果。如果您的表格中有大量的公式而影响到系统速度的话可以使用manual来加快系统速度，通过按F9来手动计算。一般情况下选自动就可以了.,表格中的宏无法运行,如果打开一个
33、存储了宏的表格时，或者运行已经录制好的宏时出现以下报错而且无法运行宏时,表格中的宏无法运行,请检查Excel的安全级别是否设置太高，同样打开Tools-Options,表格中的宏无法运行,选择Security页面，下面如图点击Macro Security,表格中的宏无法运行,出现以下窗口，一般我们选择Medium, 这样打开包含宏的文件时会有对话框问你是否允许运行宏，选择Enable后就可以运行了。如果觉的麻烦也可以选择Low来默认可以运行。但是会有一定的潜在威胁，不建议使用。,Excel表格的纵横都以数字形式显示了,这是Excel的另外一种表达形式叫做R1C1，他是纵横都以数字显示如下图，然
34、后通过R和C分别标识Row和Column,Excel表格的纵横都以数字形式显示了,可以在设置中进行切换,Excel表格的纵横都以数字形式显示了,在General页面中，R1C1 reference style就是进行切换的。一般我们取消选择就可以了,Networkdays公式无法使用,networkdays公式是一个用来计算两个日期间的工作日的公式，它会扣除日期间的周末。而这个公式在默认情况下是没有激活的。需要打开Excel的附加公式组。选择Tools-Add-Ins,Networkdays公式无法使用,在以下窗口中选择第一项Analysis ToolPak,Networkdays公式无法使用,下面窗口将会出现来安装此插件。,Networkdays公式无法使用,安装完成后公式就可以使用了,
