excel使用技巧PPT资料.pptx

1、这里只要在前面加上一个单引号“”（英文格式下）就可以显示出来（如身份证号码也是相同办法）,7,在不同的单元格连续输入多个值,我们有时需要在不同单元格内连续输入多个数据，但每次输入一个数据后按回车键，活动单元格均默认下移一个单元格，非常不便。方法:单击鼠标选定单元格，然后按住Ctrl键再次单击鼠标选定其他单元格按回车键，光标自动停留在第一个选中单元格，再输入数据，敲回车键。光标会自动移动到下一个选中单元格。,8,数据有效性,数据有效性在我们日常操作Excel数据时，可以借助数据有效性的功能来验证我们输入数据 的有效性。1、单击功能区“数据”，单击“数据有效性”根据我们实际需要进行设置。如图,动手

2、操作,9,使用数据有效性，限制单元格只能输入18位身份证号码。,文本快速换行,我们在使用Excel制作表格时经常会遇到需要在一个单元格输入一行或几行文字的情况，如果输入一行后敲回车键就会移到下一单元格，而不是换行。简便实用的操作方法可以实现换行:在选定单元格输入第一行内容后，在换行处按“Alt+回车键”，即可输入第二行内容，再按“Alt+回车键”输入第三行以此类推。,11,禁止复制隐藏行或列中的数据,如果你复制了包含隐藏列（或行）的一个数据区域，然后把它粘贴到一个新的工作表，那么Excel把隐藏列也粘贴过来了。要想避免这种情况，可以选取你要复制的数据区域，然后选择“开始-编辑查找和选择-定位”

3、命令（ctrl+G），单击“定位条件”按钮，出现“定位条件”对话框，选中“可见单元格”选项，再复制和粘贴这个选定区域就会得到你所希望的结果。,12,按笔画排序,在默认情况下，Excel对中文字的排序方式是按照“字母”顺序的，以中文姓名为例，字母顺序即按姓的拼音的首字母在26个英文字母中出现的顺序进行排列，如果同姓，则依次计算名的第二、第三字。图中显示的表格包含了按字母顺序排列的姓名数据。然而，在我们的习惯中，有时常常是按照“笔划”的顺序来排列姓名的。这种排序的规则是：按姓字的划数多少排列，同划数内的姓字按起笔顺序排列（横、竖、撇、捺、折），划数和笔形都相同的字，按字形结构排列，先左右、再上下，

4、最后整体字。如果姓字相同，则依次看名第二、三字，规则同姓字。1、单击数据区域中任意单元格，如A2。2、单击菜单“数据”“排序”，出现“排序”对话框。3、在“排序”对话框中，选择“主要关键字”为“姓名”，排序方式为升序。4、单击“排序”对话框中的“选项”按钮，在出现的“排序选项”对话框中，5、单击方法区域中的“笔划排序”单选按钮，如图所示，单击“确定”按钮6、单击“确定”按钮，关闭“排序”对话框。,13,在工作日历中突显周休日,很多用户喜欢使用Excel制作工作日历，如图所示的是公司人力资源部的工作日历的一部分。如果能把所有的周休日都醒目地标识出来，就能够避免一些时间安排上的问题了。方法如下：1

5、、单击A2并拖动光标到A17以选定区域A2:A17，单击菜单“开始”“条件格式”。在“条件格式”对话框中，单击“条件1（1）”的下拉箭头，在列表中选择“公式”项，在右边的文本框中输入公式：=WEEKDAY（A2,2）52、单击“格式”按钮，在“单元格格式”对话框的“字体”选项卡中选择“字形”为“加粗”，颜色为红色，单击“确定”按钮。单击“条件格式”对话框的“确定”按钮，如图所示。,14,多个表统一页眉页脚,如果在一个Excel文件中含有多个工作表，如何将多个工作表一次设置成同样的页眉和页脚？把鼠标移到工作表的名称处（若你没有特别设置的话，Excel自动设置的名称是“sheet1、sheet2、

6、sheet3.”），然后点右键，在弹出的菜单中选择“选择全部工作表”的菜单项，这时你的所有操作都是针对全部选中的工作表，不管是设置页眉和页脚还是打印你的工作表。,15,自动醒目的小计,在实际工作，常常需要在表格中使用小计行或小计列来汇总某类数据，如图所示。使用条件格式功能，能够快速地为所有小计行与小计列设置醒目的格式。1、单击A1并拖动光标到H11以选定区域A1:H11，单击菜单“格式”“条件格式”。2、在“条件格式”对话框中，单击“条件1（1）”的下拉箭头，在列表中选择“公式”项，在右边的文本框中输入公式：=（$A1=小计）+（A$1=小计）03、单击“格式”按钮，在“单元格格式”对话框的“

7、字体”选项卡中选择“字形”为“加粗”，颜色为蓝色，单击“确定”按钮。4、单击“条件格式”对话框的“确定”按钮,16,快速比较不同区域的数值（一）,Excel的多窗口特性能够帮助用户比较不同区域的数据，但是当需要比较的区域较大时，人工比较不但费时而且准确率不高。此时如果利用条件格式功能，则能快速而又准确地完成对比工作。在如下图所示的工作表中，源数据和校验数据分别位于A2B21和D2E21，如果希望标记出与源数据不匹配的校验数据，方法如下：1、单击D2并拖动光标到E21以选定区域D2:E21，单击菜单“格式”“条件格式”。2、在“条件格式”对话框中，单击“条件1（1）”的下拉箭头，在列表中选择“单

8、元格数值”项，运算符选择“不等于”，在右边的文本框中输入：=A23、单击“格式”按钮，在“单元格格式”对话框的“图案”选项卡中选择单元格底纹颜色为淡蓝，单击“确定”按钮。4、单击“条件格式”对话框的“确定”按钮，关闭对话框。,17,快速比较不同区域的数值（二）,上页中介绍了利用条件格式快速对比不同区域中的数值的一种方法。然而在实际工作中，需要对比的数据可能并不是位置一一对应的。例如，如图所示的表格中，需要把与账号二不匹配的账号一标记出来，但两列账号的排列顺序并不相同，对于这种情况，条件格式的设置要相对复杂一些。1、单击A2并拖动光标到A11以选定区域A2:A11，单击菜单“格式”“条件格式”。

9、=OR（EXACT（A2,B$2:B$11）=FALSE3、单击“格式”按钮，在“单元格格式”对话框的“图案”选项卡中选择单元格底纹颜色为淡蓝，单击“确定”按钮。4、单击“条件格式”对话框的“确定”按钮。,在上述公式中，EXACT函数用于比较两个文本字符串是否完全相同，如完全相同则返回TRUE，否则返回FALSE。使用以下3个公式，也能够实现同样的功能：=ISNA（MATCH（A2,B$2:B$11,）=ISNA（VLOOKUP（A2,B$2:B$11,1,）=NOT（OR（A2=B$2:B$11）如果使用公式=COUNTIF（B$3:B$12,A3），会得到错误的结果。因为当字符型数字的长度

10、超过15位时，COUNTIF函数会把从第16位开始的数字都当作0计算。可以按下面的方法来创建公式：=COUNTIF（B$3:B$12,A3&*）=0这样就可以强制COUNTIF使用文本方式进行计数。,Excel-快捷键,18,在工作表中移动到当前数据区域的边缘：Ctrl+箭头键移动到行首：Home移动到工作表的开头：Ctrl+Home移动到工作表的最后一个单元格：Ctrl+End向下移动一屏：Page Down 向上移动一屏：Page Up向右移动一屏：Alt+Page Down 向左移动一屏：Alt+Page Up 移动到工作簿中下一个工作表：Ctrl+Page Down 移动到工作簿中前一

11、个工作表：Ctrl+Page UpF4键可以重复前一次操作，在很多情况下起作用，比如在工作表内加入或删除一行，然后移动插入点并按下F4键以加入或删除另一行，根本不需要使用菜单。,自己做一个EXCEL模板,19,1、包含自动的编号、姓名、身份证号码、年龄、公积金基数、工资额、大写金额等。2、设置好打印格式确保打印到一页纸上，页脚有第*页/共*页等。3、使用保护工作表只允许个人将身份证号码维护到该表格里面，且如果输入的不是18位身份证号码则无法输入。,Office办公软件,Excel使用技巧,Excel函数运用,数据透析表及图表,21,相对引用&绝对引用（$）,相对引用&绝对引用（$）A1相对引用

12、$A1绝对引用列A$1绝对引用行$A$1绝对引用行和列$在谁的前面就绝对引用谁F4是在四种引用间相互转换的快捷键（在编辑栏输入公式时按下F4功能键可进行切换）相对引用、绝对引用和混合引用是指在公式中使用单元格或单元格区域的地址时，当将公式向旁边复制时，地址是如何变化的。具体情况举例说明：1、相对引用，复制公式时地址跟着发生变化，如C1单元格有公式：=A1+B1当将公式复制到C2单元格时变为：=A2+B2当将公式复制到D1单元格时变为：=B1+C12、绝对引用，复制公式时地址不会跟着发生变化，如C1单元格有公式：=$A$1+$B$1当将公式复制到C2单元格时仍为：=$A$1+$B$1当将公式复制

13、到D1单元格时仍为：=$A$1+$B$13、混合引用，复制公式时地址的部分内容跟着发生变化，如C1单元格有公式：=$A1+B$1当将公式复制到C2单元格时变为：=$A2+B$1当将公式复制到D1单元格时变为：=$A1+C$1规律：加上了绝对地址符“$”的列标和行号为绝对地址，在公式向旁边复制时不会发生变化，没有加上绝对地址符号的,22,函数的种类,按照函数的来源，Excel 函数可以分为：内置函数：只要启动了Excel，用户就可以使用它们。如：sum;average扩展函数：必须通过单击“工具加载宏”菜单命令加载，然后才能像内置函数那样使用。当你发现你某些函数不可用时，请执行这一步骤。isev

14、en函数的使用必须执行“工具加载宏分析工具库”方能使用,23,如何插入函数,在菜单栏公式里面插入函数，如下左图所示。在单元格输入“=”输入函数名称插入公式，如下右图所示。,24,函数参数中的单元格引用,根据公式所在单元格的位置发生变化时，单元格引用的变化情况，我们可以引用分为相对引用（如A2）、绝对引用（$A$2）和混合引用三种类型混合引用有“绝对列和相对行”（如$A3:$E3）；或是“绝对行和相对列”（如A$3:E$3）两种形式。前者不论公式怎么复制,列都不变。后者不论公式怎么复制，行号不变。绝对符号的添加:用F4放在公式中的单元格引用中进行切换几种单元格引用表示法：A2:B7（某一单元格区域）；1:1（第一行）；5（1至5行）；F:F（F列）；A：N（A到N列）,25,函数参数中标志,由于Excel 工作表多数带有“列标志”。例如一张成绩统计表的首行通常带有“序号”、“姓名”、“数学”、“物理”等“列标志”（也可以称为字段），如果单击“工具选项”菜单命令，在打开的对话框中单击“重新计算”选项卡，选中“工作簿选项