EXCEL培训精简-2007.ppt

《EXCEL培训精简-2007.ppt》由会员分享，可在线阅读，更多相关《EXCEL培训精简-2007.ppt（66页珍藏版）》请在三一办公上搜索。

1、内 容,一、数据编辑二、常用技巧三、常用功能四、常用公式,2,第一部分 数据编辑,1.数据输入2.格式设置3.常用基本功能4.加密Excel文件5.打印设置6.工作表的复制与移动,3,1.数据输入输入数字或文本1、在工作表中，单击一个单元格。2、键入所需的数字或文本，然后按 Enter 或 Tab。默认情况下，按 Enter 会将向下移动一个单元格，Tab 会将向右移动一个单元格。Excel单元格文字随时换行 有时需要在一个单元格中分成几行显示文字等内容。方法一般是通过选中格式菜单中的“单元格”下“对齐”的“自动换行”复选项，单击“确定”即可。还有一种方法是：需要重起一行输入内容的时候，只要按

2、住Alt键的同时按下回车键就可以了，这种方法又快又方便。,第一部分 数据编辑,4,Excel中插入空白行如果想在某一行上面插入几行空白行，可以用鼠标拖动自此行开始选择相应的行数，然后单击右键，选择插入。如果在每一行上面均插入一空白行，按住Ctrl键，依次单击要插入新行的行标按钮，单击右键，选择插入即可。Excel中消除0值有Excel中当单元格计算结果为0时，默认会显示0，这看起来显然有点碍眼。如果你想显示0时，显示为空白，可以试试下面的方法。打开“开始EXCEL选项高级”，“在具有零值的单元格中显示零”复选项前的“”去掉，确定后，当前工作表中的值为0的单元格将全部显示成空白。,第一部分 数据

3、编辑,5,2.格式设置格式设置：选中需要设置格式的单元格，然后点击右键，选中“设置单元格格式”，在弹出的对话框中进行设置。在“数字”一栏选择你需要的类型，然后可以在其他菜单栏进行你需要的操作。,或者直接利用窗口上面的选项进行设置,第一部分 数据编辑,6,2.设置边框 快速添加普通边框可以利用工具栏上的“边框”按钮可以快速为表格添加边框：选中需要添加边框的单元格区域，单击“格式”工具栏上的“边框”按钮，在随后弹出的下拉列表中(如图)，选择一种框线样式即可。设置彩色边框选中需要添加边框的单元格区域，点击右键“设置单元格格式”命令，打开“单元格格式”对话框，切换到“边框”标签中(如图)，设置好“线条

4、”“样式”和“颜色”，再“预置”边框位置，全部设置完成后，确定返回即可。,第一部分 数据编辑,7,2.格式设置 设置行高和列宽 1、拖拉法：将鼠标移到行（列）标题的交界处，成双向拖拉箭头状时，按住左键向右（下）或向左（上）拖拉，即可调整行（列）宽（高）。2、双击法：将鼠标移到行（列）标题的交界处，双击鼠标左键，即可快速将行（列）的行高（列宽）调整为“最合适的行高（列宽）”。3、设置法：选中需要设置行高（列宽）的行（列），执行“格式行（列）行高（列宽）”命令，打开“行高（列宽）”对话框，输入一个合适的数值，确定返回即可。,第一部分 数据编辑,8,3.常用功能a.选择性粘贴 数值、公式、格式等 转

5、置：行列互换b.格式刷 双击格式刷后可连续使用,再单击格式刷可停止使用。c.快速复制公式 单元格右下角的小黑十字上用鼠标快速点击两下 d.冻结窗口e.撤消、恢复或重复操作,第一部分 数据编辑,9,f.常用函数一键完成 基本的统计信息：如求和、计数、最小（大）值等g.隐藏列或行h.查找和替换 不但文本或数字可以替换，连公式的部分内容都可以替换。有时候，在一份表格中要多次输入某个特殊符号(如“”)，每次切换软键盘，非常麻烦。我们可以用替换的方法来加快输入的速度：先在输入需要特殊符号的地方输入一个字母(如“z”)，表格整个输入完成后，按下“Ctrl+H”组合键(或执行“编辑替换”命令)，打开“查找和

6、替换”对话框(如图)，在“查找内容”中输入上述字母“z”，在“替换为”中输入特殊符号“”，按下“全部替换”按钮就行了。,第一部分 数据编辑,10,4.加密Excel文件 用Excel制作完报表，如果不想让他人查看或修改。1.Excel2007中点击左上角的选项按钮，在弹出菜单中选择“另存为”。或者按快捷键F12也是一样的效果。2.在“另存为”界面左下角有一个“工具”按钮，,第一部分 数据编辑,点击“工具”按钮右边的小三角，在弹出菜单中选择“常规选项”。设置“打开权限密码”后，他人可以用这个密码阅读这个Excel文件。设置“修改权限密码”后，他人可以用这个密码打开和修改这个Excel文件。勾选“

7、建议只读”后，他人在试图打开Excel文件的时候，会弹出建议只读的提示窗口。,11,5.打印设置 把鼠标移到工作表的名称处（若你没有特别设置的话，Excel自动设置的名称是“sheet1、sheet2、sheet3.”），然后点右键，在弹出的菜单中选择“选择全部工作表”的菜单项，这时你的所有操作都是针对全部工作表了，不管是设置页眉和页脚还是打印你工作表。,第一部分 数据编辑,让第一页以后的每一页都自动加上与第一页相同的表头：单击“文件”菜单下的“页面设置”命令，弹出“页面设置”对话框；单击“工作表”选项卡，在“打印标题”下“顶端标题行”右边文本框中单击鼠标左键，出现闪烁光标后，在Excel表用

8、鼠标左键选择表头所在行，最后选择“确定”即可。这样，Excel就会自动为第一页后面的表格加上统一表头了。“左端标题列”的设置方法同上。,12,6.Excel工作表的复制与移动技巧在使用Excel时，经常会需要在工作表间复制或移动数据，或者干脆复制整个工作表。复制一个工作表中所有单元格的内容和复制整个工作表是有区别的，下面分别介绍复制或移动的方法。1.复制与粘贴工作表中的所有单元格（复制部分单元格也可以按下列步骤）:具体操作步骤如下。(1)按Ctrl+A键选中所有单元格。也可以单击工作表左上角的行与列交点，从而选中所有单元格。(2)按Ctrl+C复制。(3)转到另一个工作表，然后单击A1单元格。

9、(4)按回车键或者是按Ctrl+v粘贴。这样就将前一工作表中的所有单元格复制到了当前工作表中了。,第一部分 数据编辑,13,6.Excel工作表的复制与移动技巧 2.复制整个工作表 复制整个工作表不只意味着要复制工作表中的所有单元格，还包括该工作表的页面设置参数，以及自定义的区域名称等。方法一(1)移动鼠标指针到工作表标签上方。(2)按住Ctrl键的同时拖动工作表到另一位置。(3)松开Ctrl键和鼠标左键，这样就完成了同一工作簿整个工作表的移动并复制。方法二(1)在工作表标签上单击右键。(2)从弹出菜单中选择“移动或复制工作表”。这时会出现如图所示的“移动或复制工作表”对话框，我们可以看到，可

10、以将选定的工作表移动到同一工作表的不同位置，也可以选择移动到其它工作簿的指定位置。如果选中对话框下方的复选框“建立副本”，就会在目标位置复制一个相同的工作表。,第一部分 数据编辑,14,第二部分 常用小技巧,1.数据输入2.数据分列3.用连字符“&”来合并文本4.其他小技巧,15,1.数据输入 Excel中多个区域一次输入同一数据 有时，需要在多个区域输入同一数据(例如，在同一列的不同单元格中输入性别男)，可以一次性输入：在按住Ctrl键的同时，分别点选需要输入同一数据的多个单元格区域，然后直接输入数据，输入完成后，按下“Ctrl+Enter”组合键确认一下就行了。注意：输入完成后，千万不能直

11、接按“Enter”键进行确认。如果采取此种方法输入公式，系统会智能化地修改公式中相对引用的单元格地址。,第二部分 常用小技巧,16,第二部分 常用小技巧,快速输入大写中文数字的简便方法 Excel具有将用户输入的小写数字转换为大写数字的功能（如它可自动将123.45转换为“壹佰贰拾叁点肆伍”），这就极大的方便了用户对表格的处理工作。实现这一功能的具体步骤为：1.将光标移至需要输入大写数字的单元格中。2.利用数字小键盘在单元格中输入相应的小写数字（如123.45）。3.右击该单元格，并从弹出的快捷菜单中执行“设置单元格格式”命令。4.从弹出的“单元格格式”对话框中选择“数字”选项卡。5.从“分类

12、”列表框中选择“特殊”选项；从“类别”列表框中选择“中文大写数字”选项。6.单击“确定”按钮，用户输入的123.45就会自动变为“壹佰贰拾叁点肆伍”，效果非常不错。,17,第二部分 常用小技巧,在多个工作表内输入相同内容的技巧 有时，我们会因为某些特殊原因而希望在同一个工作簿的不同工作表中输入相同的内容，这时我们既不必逐个进行输入，也不必利用复制、粘贴的办法，直接利用下述方法即可达到目的：1.打开相应工作簿。2.在按下Ctrl键的同时采用鼠标单击窗口需要输入相同内容的不同工作表（如Sheet1、Sheet2.），为这些工作表建立联系关系。3.在其中的任意一个工作表中输入需要所入的内容（如表格的

13、表头及表格线等），此时这些数据就会自动出现在选中的其它工作表之中。4.输入完毕之后，再次按下Ctrl键，并使用鼠标单击所选择的多个工作表，解除这些工作表之间的联系关系（否则用户输入的内容还会出现在其它工作表中）。,18,第二部分 常用小技巧,2.数据分列 一、批量转换日期格式以前在Excel中输入职工出生时间时，为了简单都输入成“yymmdd”形式，但上级部门一律要求输入成“yyyy-mm-dd”格式，那么一千多名职工出生时间肯定不能每个手工转化。最快速的方法是：先选定要转化的区域。点击“数据分列”，出现“文本分列向导”对话框。勾选“固定宽度”，连续两次点击“下一步”按钮，在步骤三对话框的“列

14、数据格式”中，选择“日期”，并选定“YMD”形式，按下“完成”按钮，以前的文本即转化成了需要的日期了。,19,第二部分 常用小技巧,2.数据分列-妙用Excel把有规律的文本数据分列,下图为文本数据,20,第二部分 常用小技巧,2.数据分列-妙用Excel把有规律的文本数据分列,在打开的“文本分列向导”对话框中，根据基本数据的特点，选择“固定宽度”。单击“下一步”，设置字段宽度。单击建立分列线，按住左键可拖动分列线的位置，双击可取消分列线。如图,21,第二部分 常用小技巧,2.数据分列-妙用Excel把有规律的文本数据分列,分列完毕后，单击“下一步”，设置每列的数据类型。如:第一列要设置为“文

15、本”，否则分列后，考号最前面的“0”将被舍去;而第三列设置为“常规”即可，Excel将按单元格中的内容自动判断并设置数据类型。如图,单击“完成”。眨眼工夫分列完毕，然后使用Excel的筛选、排序等功能就可以统计所需要的数据了,22,第二部分 常用小技巧,3.用连字符“&”来合并文本 如果想将多列的内容合并到一列中，不需要利用函数，用连字符“&”就能够轻松将它搞定。在最后合并的单元格中输入公式：=“单元格1”&“单元格2”，回车后即可合并。需要在同一单元格中既显示文本，又显示数值；例如，假设A6单元格包含数值1234，我们可以在另一个单元格(如D5)中输入以下公式：=总数:&A6 则在D5单元格

16、中就会显示出：“总数：1234”,23,第二部分 常用小技巧,4.其他小技巧 在绘图的时候按住SHIFT键，可以很简单的画出正方形、直线和等边三角形等 选中相应的单元格，按下“Ctrl+；”组合键，即可输入系统日期；按下“Ctrl+Shift+；”组合键，即可输入系统时间。注意：如果希望输入的日期或时间随系统日期或时间改变而改变，请在单元格中输入公式：=TODAY()或=NOW()。位置微移“Ctrl键”+方向键 其他诸如PPT也同样适用,24,第三部分 常用功能,1.数据透视表2.图表3.排序4.筛选5.Excel中分类设置录入数据的下拉列表6.绝对引用与相对引用,25,第三部分 常用功能,

17、1.数据透视表 建立数据透视表的目的数据透视表能帮助用户分析、组织数据。利用它可以很快地从不同角度对数据进行分类汇兑。首先应该明确的是：不是所有工作表都有建立数据透视表的必要。记录数量众多、以流水帐形式记录、结构复杂的工作表，为了，将其中的一些内在规律显现出来，可将工作表重新组合并添加算法。即，建立数据透视表。例如，有一张工作表，是一个大公司员工（姓名、性别、出生年月、所在部门、工作时间、政治面貌、学历、技术职称、任职时间、毕业院校、毕业时间等）信息一览表，不但，字段（列）多，且记录（行）数众多。为此，需要建立数据透视表，以便将一些内在规律显现出来。,26,第三部分 常用功能,创建数据透视表将

18、光标移入需建立数据透视表的工作表中，然后单击“插入”“数据透视表”。“创建数据透视表”对话框上，选择相应的要分析的数据区域。,27,第三部分 常用功能,创建数据透视表,28,第三部分 常用功能,创建数据透视表,第三部分 常用功能,创建数据透视表,第三部分 常用功能,创建数据透视表,如果要取消“酒类”汇总，直接用鼠标选中，然后点击邮件，在弹出的对话框中，讲下图中的“”取消即可。,31,第三部分 常用功能,2.图表 Excel提供了约14种标准图表类型，如面积图、柱形图、条形图、折线图、饼图、圆环图、气泡图、雷达图、股价图、曲面图、散点图、锥形图、圆柱图、棱锥图等，每种图表类型又都有几种不同的子类

19、型。此外，Excel还提供了约20种自定义图表类型，用户可根据不同的需要选用适当的图表类型。,32,第三部分 常用功能,2.图表,建议先选中数据区间,33,第三部分 常用功能,2.图表,或者先插入一个空白的图表，然后在此选择数据区间,34,第三部分 常用功能,2.图表,可以给图表添加需要的标签选项，比如，名称、值、百分比等。,35,第三部分 常用功能,3.排序 排序：行排序和列排序Excel可以对数据进行自动排序。但是，如果工作表中有拆分的单元格，则不能重新排序。工作表中某一区域重新排序选定要排序的（单元格）范围后，单击“数据”“排序”，再在“排序”对话框上选定排序规则，然后单击“确定”即可。

20、重新排序的工作表“有标题行”是默认的选项。此法通常用于工作表中部分区域重新排序。提示：如果选定某行，再排序，那么，仅仅 是该行中单元格重新排序，其他单元格不动。,36,第三部分 常用功能,4.筛选 筛选是查找和处理区域中数据子集的快捷方法。筛选区域仅显示满足条件的行，该条件由用户针对某列指定。自动筛选的操作步骤如下：在存放筛选结果的工作表中任意选取一个单元格。单击“数据”“筛选”“自动筛选命令”，在每一个字段名称旁产生一个下拉列表框按钮（右图中，红线圈定）。单击要设定筛选条件的字段名字边的下拉列表框按钮。选取筛选条件：全部、前10个、自定义、。或者单击“自定义”按钮，则会出现“自定义自动筛选对

21、话框（右图）”。在该对话框上，根据需要设定筛选条件后，单击“确定”。上述工作完成后，屏幕上就只显示符合筛选条件的数据。提示：“自定义自动筛选对话框”上，可以设定一个条件，也可以设定两个条件。设定两个条件时，须注意选定这两个条件之间的关系。即是“或”还是“与”（单选按钮确定）。,37,第三部分 常用功能,5.Excel中分类设置录入数据的下拉列表 如果在某些单元格中需要输入固定格式的数据(如“职称”等)，可以利用“数据有效性”做成一个下拉列表，让我们来选择性输入：选中需要建立下拉列表的单元格区域，执行“数据数据有效性”命令，打开“数据有效性”对话框(如图)，在“设置”标签下，按“允许”右侧的下拉

22、按钮，在随后弹出的快捷菜单中，选择“序列”选项，然后在下面“来源”方框中输入序列的各元素(如“高级工程师,工程师,助理工程师,技术员,其他职称”等)，确定返回。,选中上述区域中某个单元格，在其右侧出现一个下拉按钮，点击此按钮，在随后出现的下拉列表中，选择相应的元素(如“助理工程师”)，即可将该元素输入到相应的单元格中。,38,6.绝对引用与相对引用 相对引用、绝对引用和混合引用是指在公式中使用单元格或单元格区域的地址时，当将公式向旁边复制时，地址是如何变化的。“F4键”或者直接输入“$”美元符号相对引用 在创建公式时，单元格或单元格区域的引用通常是相对于包含公式的单元格的相对位置。在复制包含相

23、对引用的公式时，Excel 将自动调整复制公式中的引用，以便引用相对于当前公式位置的其他单元格。绝对引用 如果在复制公式时不希望 Excel 调整引用，那么请使用绝对引用。相对引用与绝对引用之间的切换 如果创建了一个公式并希望将相对引用更改为绝对引用（反之亦然），那么请先选定包含该公式的单元格。然后在编辑栏中选择要更改的引用并按 F4 键。每次按 F4 键时，Excel 会在以下组合间切换：绝对列与绝对行（例如，$C$1）；相对列与绝对行(C$1)；绝对列与相对行($C1)以及相对列与相对行(C1)。,第三部分 常用功能,39,6.绝对引用与相对引用 1、相对引用，复制公式时地址跟着发生变化，

24、如：C1单元格有公式：=A1+B1 当将公式复制到C2单元格时变为：=A2+B2 当将公式复制到D1单元格时变为：=B1+C1 2、绝对引用，复制公式时地址不会跟着发生变化，如：C1单元格有公式：=$A$1+$B$1 当将公式复制到C2单元格时仍为：=$A$1+$B$1 当将公式复制到D1单元格时仍为：=$A$1+$B$1 3、混合引用，复制公式时地址的部分内容跟着发生变化，如：C1单元格有公式：=$A1+B$1 当将公式复制到C2单元格时变为：=$A2+B$1 当将公式复制到D1单元格时变为：=$A1+C$1 随着公式的位置变化，所引用单元格位置也是在变化的是相对引用；而随着公式位置的变化所

25、引用单元格位置不变化的就是绝对引用。,第三部分 常用功能,40,在一个工作表中，在C4、C5中的数据分别是60、50。如果在D4单元格中输入“=C4”，那么将D4向下拖动到D5时，D5中的内容就变成了50，里面的公式是“=C5”，将D4向右拖动到E4，E4中的内容是60，里面的公式变成了“=D4”。,现在在D4单元格中输入“=$C4”，将D4向右拖动到E4，E4中的公式还是“=$C4”，而向下拖动到D5时，D5中的公式就成了“=$C5”。,第三部分 常用功能,41,如果在D4单元格中输入“=C$4”，那么将D4向右拖动到E4时，E4中的公式变为“=D$4”，而将D4向下拖动到D5时，D5中的公

26、式还是“=C$4”。,如果在D4单元格中输入“=$C$4”，那么不论你将D4向哪个方向拖动，自动填充的公式都是“=$C$4”。原来谁前面带上了“$”号，在进行拖动时谁就不变。如果都带上了“$”，在拖动时两个位置都不能变。,第三部分 常用功能,42,第四部分 常用公式,1.与求和有关的函数的应用 2.逻辑函数 3.文本/日期/时间函数 4.查询与引用函数 5.统计函数,43,第四部分 常用公式,1.与求和有关的函数的应用 SUM：行或列求和 区域求和 注意：SUM函数中的参数，即被求和的单元格或单元格区域不能超过30个。换句话说，SUM函数括号中出现的分隔符（逗号）不能多于29个，否则Excel

27、就会提示参数太多。SUMIF：SUMIF函数可对满足某一条件的单元格区域求和，该条件可以是数值、文本或表达式=SUMIF($C$3:$C$12,销售部,$F$3:$F$12)=SUMIF($C$3:$C$12,=100,$F$3:$F$12),44,第四部分 常用公式,2.逻辑函数 IF函数：语法为IF(logical_test,value_if_true,value_if_false)例如：=IF(D38%,a类,b类)=IF(C350000,IF(C380000,a,b),c)IF函数可以嵌套七层 COUNTIF可以用来计算给定区域内满足特定条件的单元格的数目。语法形式为COUNTIF(r

28、ange,criteria)例如：=COUNTIF(C3:C15,50000)=COUNTIF(E3:E15,a类),45,第四部分 常用公式,2.逻辑函数 IF函数应用：某班级的成绩如图所示，为了做出最终的综合评定，我们设定按照平均分判断该学生成绩是否合格的规则。如果各科平均分超过60分则认为是合格的，否则记作不合格。根据这一规则，我们在综合评定中写公式（以单元格B12为例）：=IF(B1160,合格,不合格)语法解释为，如果单元格B11的值大于60，则执行第二个参数即在单元格B12中显示合格字样，否则执行第三个参数即在单元格B12中显示不合格字样。在综合评定栏中可以看到由于C列的同学各科平

29、均分为54分，综合评定为不合格。其余均为合格。,46,第四部分 常用公式,2.逻辑函数 IF函数多层嵌套函数的应用：设定综合评定的规则为当各科平均分超过90时，评定为优秀。如图所示。根据这一规则，我们在综合评定中写公式（以单元格F12为例）：=IF(F1160,IF(F1190),优秀,合格),不合格)语法解释为，如果单元格F11的值大于60，则执行第二个参数，在这里为嵌套函数，继续判断单元格F11的值是否大于90，如果满足在单元格F12中显示优秀字样，不满足显示合格字样，如果F11的值以上条件都不满足，则执行第三个参数即在单元格F12中显示不合格字样。在综合评定栏中可以看到由于F列的同学各科

30、平均分为92分，综合评定为优秀。,47,第四部分 常用公式,2.逻辑函数-COUNTIF的应用 需要计算的是：每位学生取得优秀成绩的课程数。规则为成绩大于90分记做优秀。如图所示 根据这一规则，我们在优秀门数中写公式（以单元格B13为例）：=COUNTIF(B4:B10,90)语法解释为，计算B4到B10这个范围，即jarry的各科成绩中有多少个数值大于90的单元格。在优秀门数栏中可以看到jarry的优秀门数为两门。其他人也可以依次看到。,48,第四部分 常用公式,实例应用原始数据表所示（原始数据是以流水单形式列出的，即按订单号排列）,按销售人员汇总表,49,第四部分 常用公式,实例应用 公式

31、说明（1）订单数-用COUNTIF计算销售人员的订单数。以销售人员ANNIE的订单数公式为例。公式：=COUNTIF($C$2:$C$13,A17)语法解释为计算单元格A17(即销售人员ANNIE)在“销售人员”清单$C$2:$C$13的范围内（即图所示的原始数据表）出现的次数。这个出现的次数即可认为是该销售人员ANNIE的订单数。（2）订单总额-用SUMIF汇总每个销售人员的销售额。以销售人员ANNIE的订单总额公式为例。公式：=SUMIF($C$2:$C$13,A17,$B$2:$B$13)此公式在“销售人员”清单$C$2:$C$13中检查单元格A17 中的文本（即销售人员ANNIE），然

32、后计算“订单金额”列（$B$2:$B$13）中相应量的和。这个相应量的和就是销售人员ANNIE的订单总额。（3）销售奖金-用IF根据订单总额决定每次销售应获得的奖金。假定公司的销售奖金规则为当订单总额超过5万元时，奖励幅度为百分之十五，否则为百分之十。根据这一规则仍以销售人员ANNIE为例说明。公式为：=IF(C1750000,10%,15%)*C17如果订单总额小于 50000则奖金为 10%；如果订单总额大于等于 50000，则奖金为 15%。,50,第四部分 常用公式,2.逻辑函数-AND函数所有参数的逻辑值为真时返回 TRUE；只要一个参数的逻辑值为假即返回 FALSE。简言之，就是当

33、AND的参数全部满足某一条件时，返回结果为TRUE，否则为FALSE。语法为AND(logical1,logical2,.)，其中Logical1,logical2,.表示待检测的 1 到 30 个条件值，各条件值可能为TRUE，可能为 FALSE。参数必须是逻辑值，或者包含逻辑值的数组或引用。举例说明：1、在B2单元格中输入数字50，在C2中写公式=AND(B230,B260)。由于B2等于50的确大于30、小于60。所以两个条件值（logical）均为真，则返回结果为TRUE。2、如果 B1-B3 单元格中的值为 TRUE、FALSE、TRUE，显然三个参数并不都为真，所以 在B4单元格中

34、的公式=AND(B1:B3)等于 FALSE,51,第四部分 常用公式,2.逻辑函数 OR函数指在其参数组中，任何一个参数逻辑值为 TRUE，即返回 TRUE。它与AND函数的区别在于，AND函数要求所有函数逻辑值均为真，结果方为真。而OR函数仅需其中任何一个为真即可为真。比如，上面的示例2，如果在B4单元格中的公式写为=OR(B1:B3)则结果等于TRUE,NOT函数NOT函数用于对参数值求反。当要确保一个值不等于某一特定值时，可以使用 NOT 函数。简言之，就是当参数值为TRUE时，NOT函数返回的结果恰与之相反，结果为FALSE.比如NOT(2+2=4)，由于2+2的结果的确为4，该参数

35、结果为TRUE，由于是NOT函数，因此返回函数结果与之相反，为FALSE。,52,第四部分 常用公式,3.文本/日期/时间函数 大小写转换 LOWER-将一个文字串中的所有大写字母转换为小写字母。UPPER-将文本转换成大写形式。PROPER-将文字串的首字母及任何非字母字符之后的首字母转换成大写。将其余的字母转换成小写。这三种函数的基本语法形式均为 函数名（text）文本与数值之间的转换 VALUE：将代表数字的文字串转换成数字。语法形式为：VALUE(text)TEXT：将一数值转换为按指定数字格式表示的文本。语法形式为：TEXT(value,format_text)Format_text

36、 为“单元格格式”对话框中“数字”选项卡上“分类”框中的文本形式的数字格式。说明：Format_text 不能包含星号(*)。使用函数 TEXT 可以将数值转换为带格式的文本，而其结果将不再作为数字参与计算。,53,第四部分 常用公式,取出字符串中的部分字符LEFT函数：LEFT(text,num_chars)其中Text是包含要提取字符的文本串。Num_chars指定要由 LEFT 所提取的字符数。MID函数：MID(text,start_num,num_chars)其中Text是包含要提取字符的文本串。Start_num是文本中要提取的第一个字符的位置。RIGHT函数：RIGHT(text

37、,num_chars)其中Text是包含要提取字符的文本串。Num_chars指定希望 RIGHT 提取的字符数。字符串的合并CONCATENATE：将若干个文字项合并至一个文字项中。语法形式为：CONCATENATE(text1,text2),54,第四部分 常用公式,去除字符串的空白在字符串形态中，空白也是一个有效的字符，但是如果字符串中出现空白字符时，容易在判断或对比数据是发生错误，在Excel中可以使用Trim函数清除字符串中的空白。语法形式为：TRIM(text)其中Text为需要清除其中空格的文本。字符串的比较在数据表中经常会比对不同的字符串，此时您可以使用EXACT函数来比较两个

38、字符串是否相同。该函数测试两个字符串是否完全相同。如果它们完全相同，则返回 TRUE；否则，返回 FALSE。函数 EXACT 能区分大小写，但忽略格式上的差异。利用函数 EXACT 可以测试输入文档内的文字。语法形式为：EXACT(text1,text2)Text1为待比较的第一个字符串。Text2为待比较的第二个字符串。EXACT(China,china)=False,55,第四部分 常用公式,日期与时间函数 在数据表的处理过程中，日期与时间的函数是相当重要的处理依据。取出当前系统时间/日期信息用于取出当前系统时间/日期信息的函数主要有NOW、TODAY。语法形式均为 函数名（）取得日期/

39、时间的部分字段值如果需要单独的年份、月份、日数或小时的数据时，可以使用HOUR、DAY、MONTH、YEAR函数直接从日期/时间中取出需要的数据。比如，需要返回2001-5-30 12:30 PM的年份、月份、日数及小时数，可以分别采用相应函数实现。YEAR(E5)=2001 MONTH(E5)=5 DAY(E5)=30 HOUR(E5)=12 DATEDIF：计算两个日期之间的天数、月数或年数。语法形式为：DATEDIF(start_date,end_date,unit)Unit:“y“(年）,”m”（月）,“d”（日）,“ym”（相差的月份，忽略日期中的日和年）,“md”（相差的日，忽略日

40、期中的月和年）,56,第四部分 常用公式,4.查询与引用函数 HLOOKUP与VLOOKUPHLOOKUP用于在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值。VLOOKUP用于在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。语法形式为：HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)其中，Lookup_value表示要查找的值，它必须位于

41、自定义查找区域的最左列。Lookup_value 可以为数值、引用或文字串。Table_array查找的区域，用于查找数据的区域，上面的查找值必须位于这个区域的最左列。可以使用对区域或区域名称的引用。Row_index_num为 table_array 中待返回的匹配值的行序号。Row_index_num 为 1 时，返回 table_array 第一行的数值，row_index_num 为 2 时，返回 table_array 第二行的数值，以此类推。Col_index_num为相对列号。最左列为1，其右边一列为2，依此类推.,57,第四部分 常用公式,4.查询与引用函数 HLOOKUP与V

42、LOOKUPRange_lookup为一逻辑值，指明函数 HLOOKUP 查找时是精确匹配，还是近似匹配。（1）精确查找-根据区域最左列的值，对其它列的数据进行精确的查找=VLOOKUP($A21,$A$3:$H$12,2,FALSE)（2）近似的查找-根据定义区域最左列的值，对其它列数据进行不精确值的查找=VLOOKUP(A11,$A$4:$B$8,2,TRUE),58,第四部分 常用公式,实例应用 精确查找根据区域最左列的值，对其它列的数据进行精确的查找 根据工资表创建各个员工的工资条，此工资条为应用Vlookup函数建立。以员工Sandy（编号A001）的工资条创建为例说明。第一步，拷贝

43、标题栏第二步，在编号处（A21）写入A001第三步，在姓名（B21）创建公式=VLOOKUP($A21,$A$3:$H$12,2,FALSE)语法解释：在$A$3:$H$12范围内（即工资表中）精确找出与A21单元格相符的行，并将该行中第二列的内容计入单元格中。第四步，以此类推，在随后的单元格中写入相应的公式。,59,第四部分 常用公式,实例应用 精确查找-根据区域最左列的值，对其它列的数据进行精确的查找,60,第四部分 常用公式,5.统计函数求参数的算术平均值函数AVERAGE语法形式为AVERAGE(number1,number2,.)其中Number1,number2,.为要计算平均值的

44、 130 个参数。这些参数可以是数字，或者是涉及数字的名称、数组或引用。如果数组或单元格引用参数中有文字、逻辑值或空单元格，则忽略其值。但是，如果单元格包含零值则计算在内。如果要统计逻辑值、文字或错误值，应当使用函数 AVERAGEA 求数据集的内部平均值TRIMMEAN函数TRIMMEAN先从数据集的头部和尾部除去一定百分比的数据点，然后再求平均值。当希望在分析中剔除一部分数据的计算时，可以使用此函数。比如，我们在计算选手平均分数中常用去掉一个最高分，去掉一个最低分，XX号选手的最后得分，就可以使用该函数来计算。语法形式为TRIMMEAN(array,percent)其中Array为需要进行

45、筛选并求平均值的数组或数据区域。Percent为计算时所要除去的数据点的比例，例如，如果 percent=0.2，在 20 个数据点的集合中，就要除去 4 个数据点（20 x 0.2），头部除去 2 个，尾部除去 2 个。函数 TRIMMEAN 将除去的数据点数目向下舍为最接近的 2 的倍数。,61,第四部分 常用公式,用于求单元格个数的统计函数COUNT语法形式为COUNT(value1,value2,.)其中Value1,value2,.为包含或引用各种类型数据的参数（130个），但只有数字类型的数据才被计数。函数 COUNT 在计数时，将把数字、空值、逻辑值、日期或以文字代表的数计算进去

46、；但是错误值或其他无法转化成数字的文字则被忽略。如果参数是一个数组或引用，那么只统计数组或引用中的数字；数组中或引用的空单元格、逻辑值、文字或错误值都将忽略。如果要统计逻辑值、文字或错误值，应当使用函数 COUNTA。求数据集的最大值MAX与最小值MIN 如果要计算数组或引用中的空白单元格、逻辑值或文本将被忽略。如果逻辑值和文本不能忽略，请使用带A的函数MAXA或者MINA 来代替。,62,第四部分 常用公式,5.实例应用求选手Annie的参赛分数。在这里，我们先假定已经将该选手的分数进行了从高到底的排序,63,第四部分 常用公式,求数据集中第K个最大值LARGE与第k个最小值SMALL这两个

47、函数LARGE、SMALL与MAX、MIN非常想像，区别在于它们返回的不是极值，而是第K个值。语法形式为：函数(array,k)，其中Array为需要找到第 k 个最小值的数组或数字型数据区域。K为返回的数据在数组或数据区域里的位置（如果是LARGE为从大到小排，若为SMALL函数则从小到大排）。说到这，大家可以想得到吧。如果K=1或者K=n（假定数据集中有n个数据）的时候，是不是就可以返回数据集的最大值或者最小值了呢。求数据集中的中位数MEDIANMEDIAN函数返回给定数值集合的中位数。所谓中位数是指在一组数据中居于中间的数，换句话说，在这组数据中，有一半的数据比它大，有一半的数据比它小。

48、需要注意的是，如果参数集合中包含有偶数个数字，函数 MEDIAN 将返回位于中间的两个数的平均值。,64,第四部分 常用公式,一个数值在一组数值中的排位的函数RANK 数值的排位是与数据清单中其他数值的相对大小，当然如果数据清单已经排过序了，则数值的排位就是它当前的位置。数据清单的排序可以使用Excel提供的排序功能完成。语法形式为RANK(number,ref,order)其中Number为需要找到排位的数字；Ref 为包含一组数字的数组或引用。Order为一数字用来指明排位的方式。如果 order 为 0 或省略，则Excel 将 ref 当作按降序排列的数据清单进行排位。如果 order 不为零，Microsoft Excel 将 ref 当作按升序排列的数据清单进行排位。需要说明的是，函数 RANK 对重复数的排位相同。但重复数的存在将影响后续数值的排位。嗯，这就好像并列第几的概念啊。例如，在一列整数里，如果整数 10 出现两次，其排位为 5，则 11 的排位为 7（没有排位为 6 的数值）。,65,第四部分 常用公式,实例应用 以某单位年终奖金分配表为例说明。在示例中，将利用这些函数求解该单位年终奖金分配中的最高金额、最低金额、平均金额、中间金额、众数金额以及第二高金额等。详细的公式写法可从图中可以清楚的看出,66,结束，谢谢！,