excel使用技巧.pptx

excel使用技巧.pptx

《excel使用技巧.pptx》由会员分享，可在线阅读，更多相关《excel使用技巧.pptx（71页珍藏版）》请在冰点文库上搜索。

Office-excel软件应用,姓名：

李东明电话：

13792990519,培训目标,2,Office-excel软件应用,Excel使用技巧,Excel函数运用,数据透析表及图表,4,单元格自定义类型,在“开始”菜单栏下的“数字”区域，有设定好的一些格式，比如百分数，时间格式，分数啊，会计格式啊等等，需要使用的话，直接单击就可以了另外，在看看“自定义”设置，这就需要讲下通配符：

一般我们常用到的通配符有“0”和“#”，它代表的就是一个字符。

注意哦：

通配符要在英文半角情况下输入,5,快速给数字加上单位,有时我们需要给输入的数值加上单位（如“立方米”等），少量的我们可以直接输入而大量的如果一个一个地输入就显得太慢了。

下面的方法来实现单位的自动输入：

先将数值输入相应的单元格中（注意:

仅限于数值），然后在按住Ctrl键的同时选取需要加同一单位的单元格，单击“开始单元格-格式”命令，打开“单元格格式”对话框（），在“数字”标签中，选中“分类”下面的“自定义”选项，再在“类型”下面的方框中输入#立方米，按下确定键后，单位（立方米）即一次性加到相应数值的后面。

6,数值开头的零如何显示,在输入数值时如果开头是0如何让它显示出来？

当我们在输入数值时，如编号如果开头是0的话，往往是显示不出来的。

例如：

输入009527显示出来是9527,0是显示不出来的。

这里只要在前面加上一个单引号“”（英文格式下）就可以显示出来（如身份证号码也是相同办法）,7,在不同的单元格连续输入多个值,我们有时需要在不同单元格内连续输入多个数据，但每次输入一个数据后按回车键，活动单元格均默认下移一个单元格，非常不便。

方法:

单击鼠标选定单元格，然后按住Ctrl键再次单击鼠标选定其他单元格按回车键，光标自动停留在第一个选中单元格，再输入数据，敲回车键。

光标会自动移动到下一个选中单元格。

8,数据有效性,数据有效性在我们日常操作Excel数据时，可以借助数据有效性的功能来验证我们输入数据的有效性。

1、单击功能区“数据”，单击“数据有效性”根据我们实际需要进行设置。

如图,动手操作,9,使用数据有效性，限制单元格只能输入18位身份证号码。

文本快速换行,我们在使用Excel制作表格时经常会遇到需要在一个单元格输入一行或几行文字的情况，如果输入一行后敲回车键就会移到下一单元格，而不是换行。

简便实用的操作方法可以实现换行:

在选定单元格输入第一行内容后，在换行处按“Alt+回车键”，即可输入第二行内容，再按“Alt+回车键”输入第三行以此类推。

11,禁止复制隐藏行或列中的数据,如果你复制了包含隐藏列（或行）的一个数据区域，然后把它粘贴到一个新的工作表，那么Excel把隐藏列也粘贴过来了。

要想避免这种情况，可以选取你要复制的数据区域，然后选择“开始-编辑查找和选择-定位”命令（ctrl+G），单击“定位条件”按钮，出现“定位条件”对话框，选中“可见单元格”选项，再复制和粘贴这个选定区域就会得到你所希望的结果。

12,按笔画排序,在默认情况下，Excel对中文字的排序方式是按照“字母”顺序的，以中文姓名为例，字母顺序即按姓的拼音的首字母在26个英文字母中出现的顺序进行排列，如果同姓，则依次计算名的第二、第三字。

图中显示的表格包含了按字母顺序排列的姓名数据。

然而，在我们的习惯中，有时常常是按照“笔划”的顺序来排列姓名的。

这种排序的规则是：

按姓字的划数多少排列，同划数内的姓字按起笔顺序排列（横、竖、撇、捺、折），划数和笔形都相同的字，按字形结构排列，先左右、再上下，最后整体字。

如果姓字相同，则依次看名第二、三字，规则同姓字。

1、单击数据区域中任意单元格，如A2。

2、单击菜单“数据”“排序”，出现“排序”对话框。

3、在“排序”对话框中，选择“主要关键字”为“姓名”，排序方式为升序。

4、单击“排序”对话框中的“选项”按钮，在出现的“排序选项”对话框中，5、单击方法区域中的“笔划排序”单选按钮，如图所示，单击“确定”按钮6、单击“确定”按钮，关闭“排序”对话框。

13,在工作日历中突显周休日,很多用户喜欢使用Excel制作工作日历，如图所示的是公司人力资源部的工作日历的一部分。

如果能把所有的周休日都醒目地标识出来，就能够避免一些时间安排上的问题了。

方法如下：

1、单击A2并拖动光标到A17以选定区域A2:

A17，单击菜单“开始”“条件格式”。

在“条件格式”对话框中，单击“条件1

（1）”的下拉箭头，在列表中选择“公式”项，在右边的文本框中输入公式：

=WEEKDAY（A2,2）52、单击“格式”按钮，在“单元格格式”对话框的“字体”选项卡中选择“字形”为“加粗”，颜色为红色，单击“确定”按钮。

单击“条件格式”对话框的“确定”按钮，如图所示。

14,多个表统一页眉页脚,如果在一个Excel文件中含有多个工作表，如何将多个工作表一次设置成同样的页眉和页脚？

把鼠标移到工作表的名称处（若你没有特别设置的话，Excel自动设置的名称是“sheet1、sheet2、sheet3.”），然后点右键，在弹出的菜单中选择“选择全部工作表”的菜单项，这时你的所有操作都是针对全部选中的工作表，不管是设置页眉和页脚还是打印你的工作表。

15,自动醒目的小计,在实际工作，常常需要在表格中使用小计行或小计列来汇总某类数据，如图所示。

使用条件格式功能，能够快速地为所有小计行与小计列设置醒目的格式。

1、单击A1并拖动光标到H11以选定区域A1:

H11，单击菜单“格式”“条件格式”。

2、在“条件格式”对话框中，单击“条件1

（1）”的下拉箭头，在列表中选择“公式”项，在右边的文本框中输入公式：

=（$A1=小计）+（A$1=小计）03、单击“格式”按钮，在“单元格格式”对话框的“字体”选项卡中选择“字形”为“加粗”，颜色为蓝色，单击“确定”按钮。

4、单击“条件格式”对话框的“确定”按钮,16,快速比较不同区域的数值

（一）,Excel的多窗口特性能够帮助用户比较不同区域的数据，但是当需要比较的区域较大时，人工比较不但费时而且准确率不高。

此时如果利用条件格式功能，则能快速而又准确地完成对比工作。

在如下图所示的工作表中，源数据和校验数据分别位于A2B21和D2E21，如果希望标记出与源数据不匹配的校验数据，方法如下：

1、单击D2并拖动光标到E21以选定区域D2:

E21，单击菜单“格式”“条件格式”。

2、在“条件格式”对话框中，单击“条件1

（1）”的下拉箭头，在列表中选择“单元格数值”项，运算符选择“不等于”，在右边的文本框中输入：

=A23、单击“格式”按钮，在“单元格格式”对话框的“图案”选项卡中选择单元格底纹颜色为淡蓝，单击“确定”按钮。

4、单击“条件格式”对话框的“确定”按钮，关闭对话框。

17,快速比较不同区域的数值

（二）,上页中介绍了利用条件格式快速对比不同区域中的数值的一种方法。

然而在实际工作中，需要对比的数据可能并不是位置一一对应的。

例如，如图所示的表格中，需要把与账号二不匹配的账号一标记出来，但两列账号的排列顺序并不相同，对于这种情况，条件格式的设置要相对复杂一些。

1、单击A2并拖动光标到A11以选定区域A2:

A11，单击菜单“格式”“条件格式”。

2、在“条件格式”对话框中，单击“条件1

（1）”的下拉箭头，在列表中选择“公式”项，在右边的文本框中输入公式：

=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），会得到错误的结果。

因为当字符型数字的长度超过15位时，COUNTIF函数会把从第16位开始的数字都当作0计算。

可以按下面的方法来创建公式：

=COUNTIF（B$3:

B$12,A3&*）=0这样就可以强制COUNTIF使用文本方式进行计数。

Excel-快捷键,18,在工作表中移动到当前数据区域的边缘：

Ctrl+箭头键移动到行首：

Home移动到工作表的开头：

Ctrl+Home移动到工作表的最后一个单元格：

Ctrl+End向下移动一屏：

PageDown向上移动一屏：

PageUp向右移动一屏：

Alt+PageDown向左移动一屏：

Alt+PageUp移动到工作簿中下一个工作表：

Ctrl+PageDown移动到工作簿中前一个工作表：

Ctrl+PageUpF4键可以重复前一次操作，在很多情况下起作用，比如在工作表内加入或删除一行，然后移动插入点并按下F4键以加入或删除另一行，根本不需要使用菜单。

自己做一个EXCEL模板,19,1、包含自动的编号、姓名、身份证号码、年龄、公积金基数、工资额、大写金额等。

2、设置好打印格式确保打印到一页纸上，页脚有第*页/共*页等。

3、使用保护工作表只允许个人将身份证号码维护到该表格里面，且如果输入的不是18位身份证号码则无法输入。

Office办公软件,Excel使用技巧,Excel函数运用,数据透析表及图表,21,相对引用&绝对引用（$）,相对引用&绝对引用（$）A1相对引用$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当将公式复制到D1单元格时仍为：

=$A$1+$B$13、混合引用，复制公式时地址的部分内容跟着发生变化，如C1单元格有公式：

=$A1+B$1当将公式复制到C2单元格时变为：

=$A2+B$1当将公式复制到D1单元格时变为：

=$A1+C$1规律：

加上了绝对地址符“$”的列标和行号为绝对地址，在公式向旁边复制时不会发生变化，没有加上绝对地址符号的,22,函数的种类,按照函数的来源，Excel函数可以分为：

内置函数：

只要启动了Excel，用户就可以使用它们。

如：

sum;average扩展函数：

必须通过单击“工具加载宏”菜单命令加载，然后才能像内置函数那样使用。

当你发现你某些函数不可用时，请执行这一步骤。

如：

iseven函数的使用必须执行“工具加载宏分析工具库”方能使用,23,如何插入函数,在菜单栏公式里面插入函数，如下左图所示。

在单元格输入“=”输入函数名称插入公式，如下右图所示。

24,函数参数中的单元格引用,根据公式所在单元格的位置发生变化时，单元格引用的变化情况，我们可以引用分为相对引用（如A2）、绝对引用（$A$2）和混合引用三种类型混合引用有“绝对列和相对行”（如$A3:

$E3）；或是“绝对行和相对列”（如A$3:

E$3）两种形式。

前者不论公式怎么复制,列都不变。

后者不论公式怎么复制，行号不变。

绝对符号的添加:

用F4放在公式中的单元格引用中进行切换几种单元格引用表示法：

A2:

B7（某一单元格区域）；1:

1（第一行）；1:

5（1至5行）；F:

F（F列）；A：

N（A到N列）,25,函数参数中标志,由于Excel工作表多数带有“列标志”。

例如一张成绩统计表的首行通常带有“序号”、“姓名”、“数学”、“物理”等“列标志”（也可以称为字段），如果单击“工具选项”菜单命令，在打开的对话框中单击“重新计算”选项卡，选中“工作簿选项”选项组中的“接受公式标志”选项，公式就可以直接引用“列标志”了。

例如“B2:

B46”区域存放着学生的物理成绩，而B1单元格已经输入了“物理”字样，则求物理平均分的公式可以写成“=AVERAGE（物理）”。

需要特别说明的是，公式引用“列标志”时的限制较多，它只能在当前数据列的下方引用，不能跨越工作表引用。

从本质上讲，名称和标志都是单元格引用的一种方式。

因为它们不是文本，使用时名称和标志都不能添加引号。

函数参数中标志的使用方法：

26,EXCEL函数的结构,Excel函数即是预先定义，执行计算、分析等处理数据任务的特殊公式。

Excel函数结构:

也有一些函数是没有参数的,如ROW（）,左右括号成对出现,单一结构,嵌套结构,参数与参数之间使用半角逗号进行分隔,27,函数参数常用符号或表示方法,函数公式中的文本必须用半角引号，如：

东南汽车;而非直接输入东南汽车或“东南汽车”连接符:

A2:

B7；1:

1；1:

5；F:

F；A：

N,28,Rank函数,用途：

返回一个数值在一组数值中的排位（如果数据清单已经排过序了，则数值的排位就是它当前的位置）。

语法：

RANK（需要计算其排位的数字，存放要比较的数字的区域，排序方式）参数：

排序方式为一数字，指明排位的方式。

如果排序方式为0或省略，则按降序排列的数据清单进行排位。

如果排序方式不为零，当作按升序排列的数据清单进行排位。

注意：

函数RANK对重复数值的排位相同。

但重复数的存在将影响后续数值的排位。

如在一列整数中，若整数60出现两次，其排位为5，则61的排位为7（没有排位为6的数值）。

RANK（number,ref,order）=RANK（B7,$B$7:

$B$19,1）,29,Sum求和函数,用途：

返回某一单元格区域中所有数字之和。

语法：

SUM（number1，number2，.）。

语法格式：

Number1，number2，.为1到30个需要求和的数值（包括逻辑值及文本表达式）、区域或引用。

注意：

参数表中的数字、逻辑值及数字的文本表达式可以参与计算，其中逻辑值被转换为1、文本被转换为数字。

如果参数为数组或引用，只有其中的数字将被计算，数组或引用中的空白单元格、逻辑值、文本或错误值将被忽略。

SUM函数中的参数，即被求和的单元格或单元格区域不能超过30个。

换句话说，SUM函数括号中出现的分隔符（逗号）不能多于29个，否则Excel就会提示参数太多。

参数太多的突破方法：

巧用括号法,30,AVERAGE函数,用途：

计算所有参数的算术平均值。

语法：

AVERAGE（number1，number2，.）。

参数：

Number1、number2、.是要计算平均值的130个参数。

实例：

如果A1：

A5区域命名为分数，其中的数值分别为100、70、92、47和82，则公式“=AVERAGE（分数）”返回78.2。

31,ROUND对数值进取四舍五入,用途：

按指定位数四舍五入某个数字。

语法：

ROUND（需四舍五入的数字，保留的小数位）注意：

如果保留的小数位大于0，则四舍五入到指定的小数位；如果等于0，则四舍五入到最接近的整数；如果小于0，则在小数点左侧按指定位数四舍五入。

实例：

如果A1=65.25，则公式“=ROUND（A1，1）”返回65.3；=ROUND（82.149，2）返回82.15；=ROUND（21.5，-1）返回20；Round（A1,0）返回65。

32,INT无条件取整,用途：

将任意实数向下取整为最接近的整数。

语法：

INT（实数）参数：

Number为需要处理的任意一个实数。

实例：

如果A1=16.24、A2=-28.389，则公式“=INT（A1）”返回16，=INT（A2）返回-29。

33,TRUNC函数,用途：

将数字的小数部分依需截去（不作四舍五入），返回实数。

语法：

TRUNC（需要截去小数部分的数字，保留小数的位数）注意：

TRUNC函数可以按需要截取数字的小数部分，而INT函数则将数字向下舍入到最接近的整数。

INT和TRUNC函数在处理负数时有所不同：

TRUNC（-4.3）返回-4，而INT（-4.3）返回-5。

实例：

如果A1=78.652，则公式“=TRUNC（A1，1）”返回78.6，=TRUNC（A1，2）返回78.65，=TRUNC（-8.963，2）返回8.96。

34,比较两个字符串是否相同,用途：

测试两个字符串是否完全相同。

如果它们完全相同，则返回TRUE；否则返回FALSE。

EXACT函数能区分大小写，但忽略格式上的差异。

语法：

EXACT（text1，text2）。

参数：

Text1是待比较的第一个字符串，Text2是待比较的第二个字符串。

实例：

如果A1=得利卡、A2=富利卡、A3=戈蓝，则公式“=EXACT（A1，A2）”返回FALSE，=EXACT（A1，A3）返回FALSE，=EXACT（word，word）返回TRUE。

EXACT:

比较两个字符串是否相同,35,CONCATENATE文本合并,用途：

将若干文字串合并到一个文字串中，其功能与&运算符相同。

语法：

CONCATENATE（text1，text2，.）参数：

Text1，text2，.为1到30个将要合并成单个文本的文本项，这些文本项可以是文字串、数字或对单个单元格的引用。

实例：

如果A1=98、A2=千米，则公式“=CONCATENATE（A1，A2）”返回“98千米”，与公式“=A1&A2”等价。

CONCATENATE,36,日期函数,用途：

返回某日期的年份/月份/日期。

语法：

YEAR/MONTH/DAY（日期值）实例：

假设A2单元格存放的日期为2008-8-20公式“=YEAR（A2）返回2008”公式“=MONTH（A2）返回8”公式“=DAY（A2）返回20”,YEARMONTHDAY日期函数,37,星期函数,用途：

返回某日期的星期数。

在默认情况下，它的值为1（星期天）到7（星期六）之间的一个整数。

语法：

WEEKDAY（日期，返回类型）参数：

“返回类型”为确定返回值类型的数字，数字1或省略则1至7代表星期天到数星期六，数字2则1至7代表星期一到星期天（推荐使用），数字3则0至6代表星期一到星期天。

实例：

公式“=WEEKDAY（”2008/8/20“，2）”返回3（星期三），=WEEKDAY（“2008/8/20”，3）返回2（星期三）。

Weekday星期函数,38,VALUE文字转换成数字,用途：

将表示数字的文字串转换成数字。

语法：

VALUE（text）。

参数：

Text为带引号的文本，或对需要进行文本转换的单元格的引用。

它可以是Excel可以识别的任意常数、日期或时间格式。

如果Text不属于上述格式，则VALUE函数返回错误值#VALUE!

。

VALUE,39,VALUE演示,文本型态的数字不可计算,通过value函数转换就可以计算了,40,If:

逻辑函数,功能：

IF函数用于执行真假值判断后，根据逻辑测试的真假值返回不同的结果。

它的应用很广泛，可以使用函数IF对数值和公式进行条件检测。

语法格式：

IF（条件,A,B）。

其中“条件”表示计算结果为TRUE或FALSE的任意值或表达式，若为常量，视为True。

条件参数可使用任何比较运算符。

条件返回的结果为真的话，则执行A，否则执行B。

IF函数仅可以嵌套七层，超过将会出错。

41,IF函数的简单案例一,性别为男者,称谓为XX先生;性别为女者,称谓为XX女士,=IF（A10,负,）&IF（A11,TEXT（TRUNC（ABS（A1）,DBNum2G/通用格式）&元&IF（ROUND（A1,3）=ROUND（A1,）,整,）&IF（A1*10-FLOOR（A1,1）*10）/100.1,TEXT（RIGHT（TRUNC（A1*10）,1）,DBNum2G/通用格式）&角&IF（ROUND（A1,3）=ROUND（A1,1）,整,）&IF（A1*100-FLOOR（A1*10,1）*10）/1000.01,TEXT（RIGHT（ROUND（A1*100）,）,1）,DBNum2G/通用格式）&分整）,IF函数转化大小写,42,注意：

因IF仅可嵌套七层，所以无法计算月薪超过102000者个人所得税,假如A3为存放税前工资的单元格,个人所得税计算公式如下:

=IF（A382000,（A3-2000）*40%-10375,IF（A362000,（A3-2000）*35%-6375,IF（A342000,（A3-2000）*30%-3375,IF（A322000,（A3-2000）*25%-1375,IF（A37000,（A3-2000）*20%-375,IF（A34000,（A3-2000）*15%-125,IF（A32500,（A3-2000）*10%-25,IF（A32000,（A3-2000）*5%,0）,依下述税率表,利用if函数计算个人所得税,IF函数的简单案例二,43,Sumif:

条件求和函数,用途：

根据指定条件对若干单元格、区域或引用求和。

语法：

SUMIF（条件区域，条件，需求和的区域）参数：

条件是由数字、逻辑表达式等组成的判定条件。

44,Sumif案例,请统计人资组同仁的工资总额:

Sumif（A:

A,人资组,C:

C）,45,Countif：

条件计数函数,用途：

计算区域中满足给定条件的单元格的个数。

语法：

COUNTIF（统计区域，条件）参数：

“统计区域”为需要计算其中满足条件的单元格数目的单元格区域。

“条件”为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。

Countif：

46,Countif案例,以上述样表为据,完成以下案例:

例1：

依“师员”字段，统计师员级的人数countif（C:

C,师）；countif（C:

C,员）例2:

统计全公司年资大于等于3年的人数及大于等于3年小于5年的人数countif（I:

I,=3）；countif（I:

I,=3）-countif（I:

I,=5）例3:

检测到职编号是否有重复值countif（A:

A,A2）,Countif案例,47,Counta函数,用途：

返回参数组中非空值的数目。

利用函数COUNTA可以计算数组或单元格区域中数据项的个数。

语法：

COUNTA（单元格区域1,单元格区域2）说明：

参数的个数为130个。

Counta,48,Counta的思考案例,例1：

如果A1=6.28、A2=3.74，其余单元格为空，则公式“=COUNTA（A1:

A7）”的计算结果等于？

。

例2：

统计C列的非空白单元格的表示方法为：

？

;若整张EXCEL表的每个单元格都有数据,前述函数公式的结果为？

Counta的思考案例,49,MOD：

取余函数,用途：

返回两数相除的余数，其结果的正负号与除数相同。

语法：

MOD（被除数，除数）参数：

除数不能为零。

实例：

公式“=MOD（14，4）”返回？

2；“=MOD（5，-2）”返回？

-1。

MOD：

取余函数,50,Vlookup函数,返回表格或数组当前行中指定列处的数值。

语法：

VLOOKUP（索引值，数据区域，列序号，查找方式）参数：

索引值为需要在数据表第一列中查找的数值，它可以是数值、引用或文字串。

数据区域为需要在其中查找数据的数据表，可以使用对区域或区域名称的引用。

列序号指数据区域中待返回的匹配值的列序号。

它等于1时，返回数据区域第一列中的数值；它等于2时，返回数据区域第二列中的数值，以此类推。

查找方式为一逻辑值,指明函数VLOOKUP返回时是精确匹配还是近似匹配。

如果为1或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于索引值的最大数值；如果为0，函数VLOOKUP将返回精确匹配值。

如果找不到，则返回错误值#N/A。

Vlookup,51,截取字符串函数,用途：

根据指定的字符数返回文本串中的第一个或前几个字符。

此函数用于双字节字符。

语法：

LEFT（文本,返回的字符数）Right（文本,返回的字符数）参数：

返回的字符数必须大于或等于0