xgl常EXCEL的技巧总结

《xgl常EXCEL的技巧总结》由会员分享，可在线阅读，更多相关《xgl常EXCEL的技巧总结（30页珍藏版）》请在装配图网上搜索。

1、常见常见Excel使用技巧使用技巧1特殊数据的输入方法特殊数据的输入方法（1）分数输入方法）分数输入方法 输入输入1/5显示显示1月月5日日 输入空格（或西文单引号）输入空格（或西文单引号）1/5显示显示1/5（2）以）以0为开头的数据输入方法为开头的数据输入方法 输入输入001显示显示1 输入西文单引号输入输入西文单引号输入001显示显示001 （3）时间的输入方法）时间的输入方法1.当今日期输入当今日期输入Ctrl+； 显示显示 2006-10-25 今天日期今天日期 =today() 例：例：2006-7-25 输入方法输入方法 7/25 7月月25日日2.当前时间当前时间 =now()

2、 例：例： 2006-7-25 21:073.三个函数：三个函数：=year() =month() =day()括号内必须是日期格式括号内必须是日期格式4.以下三个函数：以下三个函数：=hour() =minute() =second() 括号内必须是时间格括号内必须是时间格式，式，F9刷新刷新5.已知天数求日期已知天数求日期 例：例：=date(2006，0，35)= 2006-1-46. 已知分钟求时间已知分钟求时间 列：列：=Time（0，730，5）=12：10PM练习练习自动计算工龄（年，月，日）自动计算工龄（年，月，日）（1）年）年 =(today()-进公司日期）进公司日期）/3

3、65（2）月）月 =(today()-进公司日期）进公司日期）/30 (3) 日期直接相减可得相差天数日期直接相减可得相差天数注意：单元格格式必须设置为数值注意：单元格格式必须设置为数值 提醒格式提醒格式=IF(NOW()B2,提醒我！已到期！提醒我！已到期！,)2单元格的选择单元格的选择（1）选择不连续的单元格）选择不连续的单元格 使用使用Ctrl+单元格单元格（2）选择连续单元格）选择连续单元格 使用使用Shift+单元格单元格 (3)(3)选择全部区域选择全部区域 Ctrl+A练习练习: :不连续单元格填充同一数据不连续单元格填充同一数据 1使用使用Ctrl+单元格选种不连续的单元格单元

4、格选种不连续的单元格 2编辑栏输入数据编辑栏输入数据 3按按Ctrl和和Enter 4所有选中的单元格均显示同一数据所有选中的单元格均显示同一数据3EXCEL的基本用法和技巧 (1) 显示公式显示公式w 工具工具-选项选项-视图视图-公式（网格线）公式（网格线）（2）批量去掉行）批量去掉行 “数据数据”菜单，选取菜单，选取“筛选筛选”项中的项中的“自动筛选自动筛选”命令命令 ，在典型列的下拉列表框中选择，在典型列的下拉列表框中选择“空白空白 ，删除，删除Excel工作薄中的空行工作薄中的空行 w 要删除内容的单元格中的内容和它的格式和批注，要删除内容的单元格中的内容和它的格式和批注，就不能简单

5、按就不能简单按Delete键的方法了，要彻底清除单元格单键的方法了，要彻底清除单元格单击击“编辑编辑”菜单中菜单中“清除清除”项中的项中的“全部全部”命令命令（3）填充功能）填充功能 输入输入1，向上下或左右方面拉，向上下或左右方面拉w 输入输入1，2向上下或左右方面拉向上下或左右方面拉w 输入输入* -右键右键-设置单元格格式设置单元格格式 -对齐对齐-填充填充w （4）快速打开多个文件的方法是：）快速打开多个文件的方法是：w 按住按住“Ctrl”键或键或“Shift”键，选中多个键，选中多个Excel文件，文件，右键单击弹出菜单，单击右键单击弹出菜单，单击“打开打开”命令，即可同时打开命令

6、，即可同时打开选中的多个文件。选中的多个文件。快速关闭多个文件的方法是：快速关闭多个文件的方法是：w 在在Excel 2000中，按住中，按住“Shift”键，打开键，打开“文件文件”菜菜单单 ，单击，单击“关闭所有文件关闭所有文件”命令，可将当前打开的所有文命令，可将当前打开的所有文件快速关闭。件快速关闭。 w （5）对多个工作表快速编辑）对多个工作表快速编辑w 按住按住“Ctrl”键或键或“Shift”键，选中多个工作表，这键，选中多个工作表，这样在选中的工作表中样在选中的工作表中w 可进行相同的操作。一般情况下，进行的操作主要有如可进行相同的操作。一般情况下，进行的操作主要有如下几种：下

7、几种： 1、页面设置。可快速对选中工作表设置相同的页、页面设置。可快速对选中工作表设置相同的页面。面。 2、输入相同的数据。可快速在多个工作表中输入、输入相同的数据。可快速在多个工作表中输入相同的数据。相同的数据。w 3、快速操作。在多个工作表中进行一系列相同的、快速操作。在多个工作表中进行一系列相同的操作，如设置字号、字体、颜色，进行单元格的合并或操作，如设置字号、字体、颜色，进行单元格的合并或撤销合并的单元格等。撤销合并的单元格等。 4、输入公式。快速输入相同的公式，进行公式计、输入公式。快速输入相同的公式，进行公式计算。算。（6）快速应用格式）快速应用格式 要将某一单元格要将某一单元格(

8、或区域或区域)的格式的格式(字体、字号、行高、字体、字号、行高、列宽等列宽等)应用于其他区域，可将上述单元格选中，单击应用于其他区域，可将上述单元格选中，单击或双击工具栏中的或双击工具栏中的“格式刷格式刷”按钮，然后将鼠标拖过要按钮，然后将鼠标拖过要设置格式的单元格即可。说明：单击设置格式的单元格即可。说明：单击“格式刷格式刷”只能应只能应用一次，而双击用一次，而双击“格式刷格式刷”则可以多次使用，完成后再则可以多次使用，完成后再单击单击“格式刷格式刷”按钮退出操作按钮退出操作。（7）自己定制工具栏自己定制工具栏 我们可以自己动手去定制工具栏。定制方法是：单我们可以自己动手去定制工具栏。定制方

9、法是：单击击“工具工具/自定义自定义”，打开，打开“自定义自定义”对话框，选择对话框，选择“命命令令”选项，在选项，在“类别类别”中单击中单击“格式格式” 4使用隐藏功能使用隐藏功能 w（1）隐藏整个工作簿）隐藏整个工作簿w打开需要隐藏的工作簿，在打开需要隐藏的工作簿，在“窗口窗口”菜单上，单击菜单上，单击“隐藏隐藏”命令，提命令，提示框询问是否保存对隐藏工作簿的改变，单击示框询问是否保存对隐藏工作簿的改变，单击“是是”按钮，那么在下次打按钮，那么在下次打开该工作簿时，它的窗口仍然处于隐藏状态。开该工作簿时，它的窗口仍然处于隐藏状态。w 在在“窗口窗口”菜单上，单击菜单上，单击“取消隐藏取消隐

10、藏”命令。命令。w(2)隐藏工作簿中的工作表隐藏工作簿中的工作表w打开打开Excel工作簿，选定需要隐藏的一张或多张工作表。选择工作簿，选定需要隐藏的一张或多张工作表。选择“格式格式”菜单中菜单中“工作表工作表”项目下的项目下的“隐藏隐藏”命令。命令。w 在在“格式格式”菜单上，指向菜单上，指向“工作表工作表”，再单击，再单击“取消隐藏取消隐藏”命令命令w（3）隐藏工作表中行或列）隐藏工作表中行或列w从从Excel工作表中选定需要隐藏的行或列，在工作表中选定需要隐藏的行或列，在“格式格式”菜单上，指向菜单上，指向“行行”或或“列列”，再单击，再单击“隐藏隐藏”命令。命令。w 在在“格式格式”菜

11、单上，指向菜单上，指向“行行”或或“列列”，再单击，再单击“取消隐藏取消隐藏”命命令。令。 w(4)在选定单元格中隐藏零值或其它数据在选定单元格中隐藏零值或其它数据w1、选择包含零值、选择包含零值(0)或其它要隐藏数值的单元格。或其它要隐藏数值的单元格。w2、单击、单击“格式格式”菜单中的菜单中的“单元格单元格”命令，然后单击命令，然后单击“数字数字”选项选项卡。卡。w3、在、在“分类分类”选择框中，单击选择框中，单击“自定义自定义”分类。分类。w4、则在、则在“类型类型”框中键入框中键入; (三个分号三个分号)即可。经过这样设置后，被即可。经过这样设置后，被隐藏的数值将只出现在编辑栏或正编辑

12、的单元格中，这些数值将不会被打隐藏的数值将只出现在编辑栏或正编辑的单元格中，这些数值将不会被打印出来。印出来。w 5、单击、单击“工具工具”菜单中的菜单中的“选项选项”命令，然后在弹出的命令，然后在弹出的“选项选项”窗窗口中单击口中单击“视图视图”选项卡。要将含有零值的单元格显示成空白单元格时，选项卡。要将含有零值的单元格显示成空白单元格时，只需清除只需清除“零值零值”复选框即可。复选框即可。ww (5)在全部工作表中隐藏零值在全部工作表中隐藏零值w1、要将隐藏后的数值再显示出来，可先选择单元格。单击、要将隐藏后的数值再显示出来，可先选择单元格。单击“格式格式”菜单中的菜单中的“单元格单元格”

13、命令。单击命令。单击“数字数字”选项卡，在选项卡，在“分类分类”框中，单击框中，单击“常规常规”选项，这样就可应用默认的数字格式。选项，这样就可应用默认的数字格式。w 5工作表的保护w （1）加入提示信息）加入提示信息w 单击菜单单击菜单数据数据有效性有效性输入信息输入信息，在对话框中填上相，在对话框中填上相应的提示信息，然后按应的提示信息，然后按确定确定。以后，只要在工作表中选中包含。以后，只要在工作表中选中包含提示信息的单元格或单元格区域，其提示信息就会显示出来提示信息的单元格或单元格区域，其提示信息就会显示出来w （2）对单元格加以约束）对单元格加以约束 w 数据数据有效性有效性设置设置

14、，弹出对话框，弹出对话框(如图如图3)，这样，这样就限制了这部分单元格区域可接受的数据为就限制了这部分单元格区域可接受的数据为0100之间的数值。之间的数值。w （3）设置出错警告信息）设置出错警告信息 w 还是选择同样的菜单，在对话框中单击还是选择同样的菜单，在对话框中单击“出错警告出错警告”选项卡，选项卡，如在标题写上如在标题写上“出错了！出错了！”及出错信息写入及出错信息写入“小心点哦！小心点哦！”。这样，。这样，当发生错误时，就会弹出当发生错误时，就会弹出 w （4）保护特殊数据保护特殊数据w 选择所有要保护的单元格区域，然后选择菜单格式选择所有要保护的单元格区域，然后选择菜单格式单元

15、格单元格保护保护，选择所有允许修改的单元格区，选择所有允许修改的单元格区域，重复选择前一次的菜单，将域，重复选择前一次的菜单，将“锁定锁定”的复选框不选的复选框不选中，即不中，即不“锁定锁定”单位格。单位格。w （5）保护工作表）保护工作表w 选择菜单选择菜单工具工具保护保护保护工作表保护工作表，确保带圈处一定要选中。如果有必要，还可加密码，然确保带圈处一定要选中。如果有必要，还可加密码，然后单击确定。这样就既把不允许修改的数据保护起来，后单击确定。这样就既把不允许修改的数据保护起来，又不妨碍允许修改部分的数据输入了。又不妨碍允许修改部分的数据输入了。6中英文切换w 在一张工作表中，往往是既有

16、数据，又有文字，这样在输入时就在一张工作表中，往往是既有数据，又有文字，这样在输入时就需要来回在中英文之间反复切换输入法，非常麻烦。需要来回在中英文之间反复切换输入法，非常麻烦。 如果你要输入的如果你要输入的东西很有规律性，比如这一列全是单词，下一列全是汉语解释，你可东西很有规律性，比如这一列全是单词，下一列全是汉语解释，你可以用以下方法实现自动切换。以用以下方法实现自动切换。w 苹果苹果 apple apple 香蕉香蕉 banana banana 桔子桔子 orange orange函数w （1）&和和Concatenate的应用w =A &B显示显示ABw =CONCAT

17、ENATE(A1,“漂亮漂亮”,B1)显示漂亮显示漂亮w （）条件格式（）（）条件格式（） （大于）、（大于）、=（大于等于）、（大于等于）、（小于）、（小于）、=（小（小于等于）、于等于）、=（等于）、（等于）、（不等于）（不等于）w =if(一个条件，如果满足显示，如果不满足显示）一个条件，如果满足显示，如果不满足显示）w 嵌套函数最多可以嵌套嵌套函数最多可以嵌套7次次w =IF(AND(A4=60),优秀优秀,不及格不及格)w =IF(or-)或者或者w =IF(not-）符合一条件不符合另一条件）符合一条件不符合另一条件w 练习= upper( )把所有的字母转换为大写把所有的字母转换

18、为大写=IF（or(upper(G1)=“A”,upper(G1)=“B”,Upper(G1)=“C”),0, “”）=IF(D489,“优秀优秀”,IF(D479,“良良,IF(D469,一般一般,IF(D459,差差,不及不及格格) （3）排名函数）排名函数w =Rank(单元格本身，排列区域，单元格本身，排列区域，0或或1）w 0代表最大的排为代表最大的排为1w 1代表最小的排为代表最小的排为1（4）计数函数）计数函数=Countif( 计算非空单元格数目区域，以数字，表达式或文本形式定义计算非空单元格数目区域，以数字，表达式或文本形式定义的条件）的条件）w 例如：例如：w =Count

19、if（D：D，”0”)w =Countif（D：D，150) = Countif(F:F,=A)练习练习w 相同的数据禁止输入第二次：相同的数据禁止输入第二次：w 数据数据-有效性有效性-自定义自定义-=countif(B:B,B1) =1w （5）搜索函数）搜索函数w 对列进行搜索：对列进行搜索：w VLOOKUP（搜索一个相同的值，在一个范围内，显示（搜索一个相同的值，在一个范围内，显示的值在范围内在第几行，的值在范围内在第几行，0）w 对行进行搜索：对行进行搜索：w HLOOKUP（搜索一个相同的值，在一个范围内，显示（搜索一个相同的值，在一个范围内，显示的值在范围内第几列，的值在范围内

20、第几列，0）w 注意：你要搜索的值必须先在在范围内的第一行或第一注意：你要搜索的值必须先在在范围内的第一行或第一列，列，vlookup只能查到第一个数值相同的，如果要查第只能查到第一个数值相同的，如果要查第二个必须把二个必须把0改称改称1w （6）索引函数）索引函数w =row（单元格）（单元格）=3 行号行号w =column（单元格）（单元格）=5 列号列号w =index（范围，列号，行号）（范围，列号，行号）w 显示对应的单元格内容显示对应的单元格内容 指定方式下与指定数值匹配的指定方式下与指定数值匹配的数数组组中元素的相应位置中元素的相应位置 w =match(一个数值，这个值所在的

21、范围，一个数值，这个值所在的范围，0或或1或或-1）显）显示行数示行数w 如果为如果为 1，函数，函数 MATCH 查找小于或等于的最大数值。查找小于或等于的最大数值。必须按升序排列必须按升序排列w 如果为如果为 0，函数，函数 MATCH 查找等于第一个数值。可以按查找等于第一个数值。可以按任何顺序排列。任何顺序排列。w 如果为如果为 -1，函数，函数 MATCH 查找大于或等于查找大于或等于 的最小数值，的最小数值，必须按降序排列必须按降序排列练习练习种类种类价格价格种类种类价格价格花生花生1.91.9元元苹果苹果2.52.5元元香蕉香蕉2 2元元桔子桔子1.51.5元元饼干饼干3 3元元

22、花生花生1.91.9元元=HLOOKUP（B16,B19:F20,2,0）=INDEX(B3:C9, 2 ，MATCH(C1,B3:B9,0)w （7）条件求和）条件求和w =sumif(一列数范围，条件，求和范围）一列数范围，条件，求和范围）w =Sumproduct(一列数，一列数）一列数，一列数）w = Sumproduct(A2:B4, C2:D4) w （8）取值函数）取值函数w =LEFT（B2，3）取前三个字符，但后面有空格）取前三个字符，但后面有空格w （9）其他函数）其他函数w =max(一段数值）一段数值） =最大值最大值w =min(一段数值一段数值) =最小值最小值w

23、=mod(一个数值，除数）一个数值，除数）=取余数取余数 图表类型w 柱形图：柱形图：w 数据比较，由一系数据比较，由一系列垂直条组成，通常用来列垂直条组成，通常用来比较一段时间中两个或多比较一段时间中两个或多个项目的相对尺寸。例如：个项目的相对尺寸。例如：不同产品季度或年销售量不同产品季度或年销售量对比、在几个项目中不同对比、在几个项目中不同部门的经费分配情况、每部门的经费分配情况、每年各类资料的数目等。年各类资料的数目等。w 1数值柱形图数值柱形图w 2堆积柱形图堆积柱形图w 3百分比柱形图百分比柱形图w 条形图：条形图：w 数据比较，由一系列水平条组成。比如：它数据比较，由一系列水平条组

24、成。比如：它可以比较每个季度、三种产品中任意一种的销售数可以比较每个季度、三种产品中任意一种的销售数量。因为它与柱形图的行和列刚好是调过来了，所量。因为它与柱形图的行和列刚好是调过来了，所以有时可以互换使用。以有时可以互换使用。w 饼形图：饼形图：w 百分比比较，在用于对比几个数据在其形成的百分比比较，在用于对比几个数据在其形成的总和中所占百分比值时最有用。整个饼代表总和，总和中所占百分比值时最有用。整个饼代表总和，每一个数用一个楔形或薄片代表。比如：表示不同每一个数用一个楔形或薄片代表。比如：表示不同产品的销售量占总销售量的百分比，各单位的经费产品的销售量占总销售量的百分比，各单位的经费占总

25、经费的比例、收集的藏书中每一类占多少等。占总经费的比例、收集的藏书中每一类占多少等。w 柱形图比较数据间的多少关系柱形图比较数据间的多少关系;用折线图反映用折线图反映数据间的趋势关系数据间的趋势关系;用饼图表现数据间的比例分配关用饼图表现数据间的比例分配关系系。 w 面积图：面积图：w 数值随时间变化的趋势，面积图使你看见单独各部数值随时间变化的趋势，面积图使你看见单独各部分的变动，同时也看到总体的变化。分的变动，同时也看到总体的变化。 w 折线图：折线图：w 被用来显示一段时间内的趋势。比如：数据在一段被用来显示一段时间内的趋势。比如：数据在一段时间内是呈增长趋势的，另一段时间内处于下降趋势

26、，时间内是呈增长趋势的，另一段时间内处于下降趋势，我们可以通过折线图，对将来作出预测。若是其中一个我们可以通过折线图，对将来作出预测。若是其中一个数据有几种情况，折线图里就有几条不同的线，比如五数据有几种情况，折线图里就有几条不同的线，比如五名运动员在万米过程中的速度变化，就有五条折线，可名运动员在万米过程中的速度变化，就有五条折线，可以互相对比，也可以对添加趋势线对速度进行预测。以互相对比，也可以对添加趋势线对速度进行预测。 XY散点图：散点图：w 展示成对的数和它们所代表的趋势之间的关系。展示成对的数和它们所代表的趋势之间的关系。对于每一数对，一个数被绘制在对于每一数对，一个数被绘制在X轴

27、上，而另一个被绘轴上，而另一个被绘制在制在Y轴上。过两点作轴垂线，相交处在图表上有一个轴上。过两点作轴垂线，相交处在图表上有一个标记。当大量的这种数对被绘制后，出现一个图形。散标记。当大量的这种数对被绘制后，出现一个图形。散点图的重要作用是可以用来绘制函数曲线，从简单的三点图的重要作用是可以用来绘制函数曲线，从简单的三角函数、指数函数、对数函数到更复杂的混合型函数，角函数、指数函数、对数函数到更复杂的混合型函数，都可以利用它快速准确地绘制出曲线，所以在教学、科都可以利用它快速准确地绘制出曲线，所以在教学、科学计算中会经常用到。学计算中会经常用到。 w 图表的格式：图表区格式，绘图区格式，图列格图表的格式：图表区格式，绘图区格式，图列格式，数据系列格式，坐标轴格式，图表选项（网格线，式，数据系列格式，坐标轴格式，图表选项（网格线，标题），源数据，填充效果标题），源数据，填充效果按柱子右建添加趋势线