《Excel函数名师优质课获奖市赛课一等奖课件.ppt》由会员分享，可在线阅读，更多相关《Excel函数名师优质课获奖市赛课一等奖课件.ppt（55页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、日常EXCEL函数应用第1页EXCEL操作界面及设置函数与公式通用技巧惯用函数第2页Where is itWhere is it？EXCELEXCEL操作界面及设置操作界面及设置操作界面及设置操作界面及设置在“视图”选项卡中，经过勾选或取消勾选复选框来设置界面中显示或者隐藏编辑栏、行号列标、工作表标签等。“公式”选项卡中，有“公式审核”、“保护”等惯用函数公式。第3页Where is itWhere is it？右键单击工具栏“自定义快速访问工具栏”弹出自定义快速访问工具栏选项卡，能够设置自定义访问工具栏。EXCELEXCEL操作界面及设置操作界面及设置操作界面及设置操作界面及设置第4页EXC
2、EL操作界面及设置函数与公式通用技巧惯用函数第5页在工作表或工作簿中移在工作表或工作簿中移动和和滚动快捷快捷键 按下按下 完完成成l箭箭头键向上、下、左或右移向上、下、左或右移动单元格元格lCTRL+箭箭头键移移动到当前数据区域到当前数据区域边缘lCTRL+HOME移移动到工作表开到工作表开头lCTRL+END移移动到工作表最到工作表最终一个一个单元格（右下角）元格（右下角）lCTRL+PAGE DOWN移移动到工作簿中下一个工作表到工作簿中下一个工作表lCTRL+PAGE UP移移动到工作簿中前一个工作表到工作簿中前一个工作表lCTRL+F6 或或 CTRL+TAB移移动到下一工作簿或窗口到
3、下一工作簿或窗口lF5或或 CTRL+G显示示“定位定位”对话框框lSHIFT+F5或或CTRL+F显示示“查找找”对话框框lSHIFT+F4重复重复上一次上一次“查找找”操作（等同于操作（等同于“查找找下下一一个个”）lTAB在在保保护工作表中非工作表中非锁定定单元格之元格之间移移动函数与公式通用技巧函数与公式通用技巧函数与公式通用技巧函数与公式通用技巧第6页选择单元格、行或列快捷元格、行或列快捷键 请 按 假如要SHIFT+箭箭头键将将选定区域定区域扩展一个展一个单元格元格宽度度CTRL+SHIFT+箭箭头键选定定区域区域扩展到活展到活动单元格同一行或同元格同一行或同一列最一列最终一个非空
4、白一个非空白单元格元格SHIFT+HOME将将选定区域定区域扩展到行首展到行首CTRL+SHIFT+HOME将将选定区域定区域扩展到工作表开始展到工作表开始CTRL+SHIFT+END 将将选定区域定区域扩展到表最展到表最终一个使用一个使用单元元格格CTRL+A选定整个工作定整个工作表表SHIFT+PAGE DOWN将将选定区域向下定区域向下扩展一屏展一屏SHIFT+PAGE UP将将选定区域向上定区域向上扩展一屏展一屏函数与公式通用技巧函数与公式通用技巧函数与公式通用技巧函数与公式通用技巧第7页插入工作表、插入工作表、图表、宏快捷表、宏快捷键 请请 按按假如假如要要SHIFT+F11 或或
5、ALT+SHIFT+F1 插入插入新工作表新工作表F11 或或 ALT+F1创建建使用当前区域使用当前区域图表表ALT+F8显示示“宏宏”对话框框ALT+F11显示示“Visual Basic 编辑器器”函数与公式通用技巧函数与公式通用技巧函数与公式通用技巧函数与公式通用技巧第8页符号说明实例优先次序：_(空格)，引用运算符=SUM(A1:B10)区域运算符=SUM(A1:B5 A4:D9)交叉运算符，相当于=SUM(A4:B5)=RANK(A1,(A1:A10,C1:C10)联合运算符1+-*/算术运算符=5+7-9/3*42&文本运算符=“Excel”&”home”返回”Excelhome
6、”3=比较运算符=(A1=A2)判断A1与A2相等=(B1”ABC”)判断B1”ABC”=(C1=5)判断C154括号能够改变运算优先级别，多组括号进行嵌套是，计算次序是由最内层括号逐层向外进行运算。实实 例例函数与公式通用技巧函数与公式通用技巧函数与公式通用技巧函数与公式通用技巧第9页符号说明实例*通配符，表示任何字符=COUNTIF(A:A，“北京*有限企业”)?通配符，表示任何单个字符=COUNTIF(A:A，“北京*有限?企业”)通配符，用于解除字符通配性=COUNTIF(A:A，”北京*有限企业”)函数与公式通用技巧函数与公式通用技巧函数与公式通用技巧函数与公式通用技巧第10页限制：
7、限制：突破：突破：使用定义名称。使用定义名称。当参数为单元格区域引用时，可当参数为单元格区域引用时，可使用一对括号来包含多个单元格使用一对括号来包含多个单元格区域引用部分形成合并区域引用区域引用部分形成合并区域引用作为函数参数。作为函数参数。1 1、提前将单元格格式设置为文、提前将单元格格式设置为文本后再输入数字本后再输入数字2 2、在输入数字时用半角单引号、在输入数字时用半角单引号开头。开头。公式内容长度不能超出公式内容长度不能超出10241024个字个字符；符；公式中函数嵌套不能超出公式中函数嵌套不能超出7 7层；层；公式中函数参数不能超出公式中函数参数不能超出3030个；个；数字计算精度
8、为数字计算精度为1515位。位。函数与公式通用技巧函数与公式通用技巧函数与公式通用技巧函数与公式通用技巧第11页改变工作表计算精度改变工作表计算精度改变工作表计算精度改变工作表计算精度 在实际工作中，许多用户在计算数在实际工作中，许多用户在计算数字时会设置单元格格式为字时会设置单元格格式为2 2为小数数值、为小数数值、货币格式或者是整数值。但在使用公式货币格式或者是整数值。但在使用公式进行数据处理过程中，经常会发觉几个进行数据处理过程中，经常会发觉几个单元格之和与累计单元格显示数据之和单元格之和与累计单元格显示数据之和不相等。不相等。因为因为excelexcel将数值按照将数值按照1515位计
9、算精位计算精度带入计算所致。如图，在度带入计算所致。如图，在excelexcel选项选项中勾选中勾选“将精度设为所显示精度将精度设为所显示精度”，能，能够处理上述情况发生，但这么也降低了够处理上述情况发生，但这么也降低了工作薄中全部工作表计算精度，但不影工作薄中全部工作表计算精度，但不影响其它工作薄。响其它工作薄。另外，也能够用另外，也能够用ROUNDROUND函数对数值函数对数值进行修约后再求和。进行修约后再求和。函数与公式通用技巧函数与公式通用技巧函数与公式通用技巧函数与公式通用技巧第12页分步查看公式计算结果分步查看公式计算结果分步查看公式计算结果分步查看公式计算结果方法一：方法一：选中
10、包含公式单元格，单击选中包含公式单元格，单击“公式公式”选选项卡中项卡中“公式求值公式求值”按钮，弹出按钮，弹出“公式求值公式求值”对话框，然后屡次单击对话框，然后屡次单击“求值求值”按钮，按钮，“求值求值”域中将按照公式计算次序逐步地显示域中将按照公式计算次序逐步地显示公式计算过程。公式计算过程。方法二：方法二：功效键除了能够重算工作薄外，功效键除了能够重算工作薄外，还能够用于查看公式运算结果。还能够用于查看公式运算结果。当一个函数公式比较长时，能够用当一个函数公式比较长时，能够用键对各个部分进行分段计算，方便键对各个部分进行分段计算，方便公式差错和更正。在分段选择时，注意公式差错和更正。在
11、分段选择时，注意要包含一个完整运算对象。要包含一个完整运算对象。按按功效键切换功效键切换函数与公式通用技巧函数与公式通用技巧函数与公式通用技巧函数与公式通用技巧第13页EXCEL操作界面及设置函数与公式通用技巧惯用函数第14页 函数函数语法法 由由函数名函数名+括号括号+参数参数组成成 例：例：求和函数求和函数:SUM(A1,B2,)参数与参数之参数与参数之间用逗号用逗号“,”表示表示惯用函数惯用函数惯用函数惯用函数第15页1.1.条件函数条件函数条件函数条件函数IFIF、IFERRORIFERROR依据对指定条件计算结果为 TRUE 或 FALSE，返回不一样结果。能够使用 IF 对数值和公
12、式执行条件检测。Microsoft Excel 还提供了其它一些函数，它们可依据条件来分析数据。比如，假如要计算某单元格区域内某个文本字符串或数字出现次数，则可使用 COUNTIF 和 COUNTIFS 电子表格函数。若要计算基于某区域内一个文本字符串或一个数值总和，可使用 SUMIF 和 SUMIFS 工作表函数。惯用函数惯用函数惯用函数惯用函数第16页 ISERROR()括号中括号中为：#N/A、#VALUE、#REF、#DIV/0、#NUM、#NAME？或？或#NULL时为TRUE ISNA()括号中括号中为：#N/A时为TRUE 常与常与IF嵌套使用嵌套使用条件函数条件函数条件函数条件
13、函数IFIF、IFERRORIFERROR惯用函数惯用函数惯用函数惯用函数第17页 =IFERROR=IFERROR(valuevalue,value_if_errorvalue_if_error)假如公式计算犯错误则返回您指定值；不然返回公式结果。使用 IFERROR 函数来捕捉和处理公式中错误。Value 是需要检验是否存在错误参数。Value_if_error 是公式计算犯错误时要返回值。计算得到错误类型有：#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME?或#NULL!。条件函数条件函数条件函数条件函数IFIF、IFERRORIFERROR惯用函数惯用函数惯
14、用函数惯用函数第18页2.2.逻辑函数逻辑函数逻辑函数逻辑函数ANDAND、OROR全部参数计算结果为 TRUE 时，返回 TRUE；只要有一个参数计算结果为 FALSE，即返回 FALSE。经过 AND 函数能够检验多个不一样条件，而不但仅是一个条件。=AND(=AND(logical1logical1,logical2logical2,.)=OR(=OR(logical1logical1,logical2logical2,.)在其参数组中，任何一个参数逻辑值为 TRUE，即返回 TRUE；任何一个参数逻辑值为 FALSE，即返回 FALSE。注意：注意：u参数计算结果必须是逻辑值（如参数计
15、算结果必须是逻辑值（如 TRUE TRUE 或或 FALSEFALSE），而参数必须是包含逻辑值数组），而参数必须是包含逻辑值数组u假如数组或引用参数中包含文本或空白单元格，则这些值将被忽略。假如数组或引用参数中包含文本或空白单元格，则这些值将被忽略。u假如指定单元格区域未包含逻辑值，则假如指定单元格区域未包含逻辑值，则 函数将返回错误值函数将返回错误值#VALUE!#VALUE!。惯用函数惯用函数惯用函数惯用函数第19页逻辑函数函数 AND(条件条件)括号中条件括号中条件值均均为TRUE，则为TRUE；假如；假如任一个任一个为FALSE，则为FALSE OR(条件条件)括号中条件括号中条件值
16、均均为FALSE，则为FALSE；假如；假如任一个任一个为TRUE，则为TRUE NOT(条件条件)括号中条件括号中条件值为FALSE，则为TRUE；假如；假如为TRUE，则为FALSE惯用函数惯用函数惯用函数惯用函数第20页3.3.统计函数统计函数统计函数统计函数LARGELARGE、SMALLSMALL=LARGE=LARGE(arrayarray,k k)返回数据集中第 k 个最大值。使用此函数能够依据相对标准来选择数值。Array 为需要从中选择第 k 个最大值数组或数据区域。K 为返回值在数组或数据单元格区域中位置（从大到小排）。注解注解u假如数组为空，函数假如数组为空，函数 LAR
17、GE LARGE 返回错误值返回错误值#NUM!#NUM!。u假如假如 k 0 k 0 或或 k k 大于数据点个数，函数大于数据点个数，函数 LARGE LARGE 返回错误值返回错误值#NUM!#NUM!。u假如区域中数据点个数为假如区域中数据点个数为 n n，则函数，则函数 LARGE(array,1)LARGE(array,1)返回最大值，函数返回最大值，函数 LARGE(array,n)LARGE(array,n)返回最小值。返回最小值。=SMALL(=SMALL(arrayarray,k k)返回数据集中第 k 个最小值。惯用函数惯用函数惯用函数惯用函数第21页4.4.统计函数统计
18、函数统计函数统计函数MAXMAX、MINMIN=MAX(=MAX(number1number1,number2number2,.)返回一组值中最大值。注解u参数能够是数字或者是包含数字名称、数组或引用。u逻辑值和直接键入到参数列表中代表数字文本被计算在内。u假如参数为数组或引用，则只使用该数组或引用中数字。数组或引用中空白单元格、逻辑值或文本将被忽略。u假如参数不包含数字，函数 MAX 返回 0（零）。u假如参数为错误值或为不能转换为数字文本，将会造成错误。u假如要使计算包含引用中逻辑值和代表数字文本，请使用 MAXA 函数。=MIN(=MIN(number1number1,number2nu
19、mber2,.)返回一组值中最小值。=MEDIAN(=MEDIAN(number1number1,number2number2,.)返回给定数值中值。中值是在一组数值中居于中间数值。=MODE(=MODE(number1number1,number2number2,.)返回在某一数组或数据区域中出现频率最多（众数）数值。惯用函数惯用函数惯用函数惯用函数第22页5.5.统计函数统计函数统计函数统计函数AVERAGEAVERAGE=TRIMMEAN(=TRIMMEAN(arrayarray,percentpercent)返回数据集内部平均值。函数 TRIMMEAN 先从数据集头部和尾部除去一定百分
20、比数据点，然后再求平均值。当希望在分析中剔除一部分数据计算时，能够使用此函数。=AVERAGE(=AVERAGE(number1number1,number2number2,.)返回参数平均值（算术平均值）。注释：u参数能够是数字或者是包含数字名称、单元格区域或单元格引用。u逻辑值和直接键入到参数列表中代表数字文本被计逻辑值和直接键入到参数列表中代表数字文本被计算在内。算在内。u假如区域或单元格引用参数包含文本、逻辑值或空单假如区域或单元格引用参数包含文本、逻辑值或空单元格，则这些值将被忽略；但包含零值单元格将被计算在内。元格，则这些值将被忽略；但包含零值单元格将被计算在内。u假如参数为错误值
21、或为不能转换为数字文本，将会造成错误。u若要在计算中包含引用中逻辑值和代表数字文本，请使用 AVERAGEA 函数。u若要只对符合一些条件值计算平均值，请使用 AVERAGEIF 函数或 AVERAGEIFS 函数。惯用函数惯用函数惯用函数惯用函数第23页 AVERAGE(数数值1,数数值2,)A B C 1 100 20 1000 2 200 40 3 300 60 3000 =AVERAGE（A1：A3）=200 =AVERAGE（A1：A3，B1）=105统计函数统计函数统计函数统计函数AVERAGEAVERAGE惯用函数惯用函数惯用函数惯用函数第24页6.6.统计函数统计函数统计函数统
22、计函数AVERAGEIFAVERAGEIF=AVERAGEIF(=AVERAGEIF(rangerange,criteriacriteria,average_rangeaverage_range)注解：u忽略区域中包含 TRUE 或 FALSE 单元格。u假如 average_range 中单元格为空单元格，AVERAGEIF 将忽略它。u假如 range 为空值或文本值，则 AVERAGEIF 会返回#DIV0!错误值。u假如条件中单元格为空单元格，AVERAGEIF 就会将其视为 0 值。u假如区域中没有满足条件单元格，则 AVERAGEIF 会返回#DIV/0!错误值。u您能够在条件中使
23、用通配符，即问号(?)和星号(*)。uAverage_range Average_range 无须与无须与 range range 大小和形状相同。求平均值实际单元格是经过使用大小和形状相同。求平均值实际单元格是经过使用 average_range average_range 中左上方单元格作为起中左上方单元格作为起始单元格，然后加入与始单元格，然后加入与 range range 大小和形状相对应单元格确定。大小和形状相对应单元格确定。range是要计算平均值一个或多个单元格，其中包含数字或包含数字名称、数组或引用。criteria是数字、表示式、单元格引用或文本形式条件，用于定义要对哪些单元
24、格计算平均值。比如，条件能够表示为 32、32、32、苹果 或 B4。average_range是要计算平均值实际单元格集。假如忽略，则使用 range。惯用函数惯用函数惯用函数惯用函数第25页求和求和:SUM SUM(数数值1,数数值2,)A1=6 A2=7 A3=8 =SUM(6,8)=14 =SUM(A1,A3)=6+8=14 =SUM(A1:A3)=6+7+8=147.7.数学函数数学函数数学函数数学函数SUMSUM惯用函数惯用函数惯用函数惯用函数第26页数学函数数学函数数学函数数学函数SUMSUM=SUM(=SUM(number1number1,number2number2,.)说明
25、u直接键入到参数表中数字、逻辑值及数字文本表示式将被计算，请参阅下面示例一和示例二。u假如参数是一个数组或引用，则只计算其中数字。数组或引用中空白单元格、逻辑值或文本将被忽略。请参阅下面第三个示例。u假如参数为错误值或为不能转换为数字文本，将会造成错误。惯用函数惯用函数惯用函数惯用函数第27页条件求和条件求和:SUMIF SUMIF(范范围,条件条件,要求和范要求和范围)A B C 1 100 20 1000 2 200 40 3 300 60 3000=SUMIF(A1:A3,“=200”,B1:B3)=100=SUMIF(A1:A3,“=200”,C1:C3)=50008.8.数学函数数学
26、函数数学函数数学函数SUMIFSUMIF惯用函数惯用函数惯用函数惯用函数第28页9.9.统计函数统计函数统计函数统计函数COUNTCOUNT=COUNT(=COUNT(value1value1,value2value2,.)注解u假如参数为数字、日期或者代表数字文本（比如，用引号引发数字，如 1），则将被计算在内。u逻辑值和直接键入到参数列表中代表数字文本被计算在内。u假如参数为错误值或不能转换为数字文本，则不会被计算在内。u假如参数为数组或引用，则只计算数组或引用中数字个数。不会计算数组或引用中空单元格、逻辑值、文本或错误值。u若要计算逻辑值、文本值或错误值个数，请使用 COUNTA 函数。
27、u若要只计算符合某一条件数字个数，请使用 COUNTIF 函数或 COUNTIFS 函数。惯用函数惯用函数惯用函数惯用函数第29页求个数求个数:COUNT COUNT(数数值1,数数值2,)A B C 1 100 20 1000 2 200 40 3 300 60 3000 =COUNT(A1:A3)=3 =COUNT(B1:B3)=3=COUNTIF(=COUNTIF(rangerange,criteriacriteria)计算某个区域中满足给定条件单元格数。比如：=COUNTIF(A2:A7,=200”）=2 =COUNTIF(A1:A3,“200”）=110.10.统计函数统计函数统计函
28、数统计函数COUNTIFCOUNTIF惯用函数惯用函数惯用函数惯用函数第31页11.11.文本函数文本函数文本函数文本函数LEFTLEFT、MIDMID、RIGHTRIGHT=LEFT(=LEFT(texttext,num_charsnum_chars)=MID(=MID(texttext,start_numstart_num,num_charsnum_chars)=RIGHT(=RIGHT(texttext,num_charsnum_chars)Text 是包含要提取字符文本字符串。Start_num 是文本中要提取第一个字符位置。文本中第一个字符 start_num 为 1，以这类推。Nu
29、m_chars 指定希望函数从文本中返回字符个数。依据所指定字符数，LEFT 返回文本字符串中第一个字符或前几个字符。MID 返回文本字符串中从指定位置开始特定数目标字符，该数目由用户指定。RIGHT 依据所指定字符数返回文本字符串中最终一个或多个字符。惯用函数惯用函数惯用函数惯用函数第32页文本函数截取函数截取函数LEFT RIGHT MID LEFT(文本文本,数数值)从左从左边截取截取 RIGHT(文本文本,数数值)从右从右边截取截取 MID(文本文本,开始位，数开始位，数值)从中从中间截取截取 =LEFT（“abcdef”,2）=ab =RIGHT（“abcdef”,2）=ef =MI
30、D（“abcdef”,2,3）=bcd惯用函数惯用函数惯用函数惯用函数第33页12.12.文本函数文本函数文本函数文本函数CONCATENATECONCATENATE=CONCATENATE(=CONCATENATE(text1text1,text2text2,.)将两个或多个文本字符串合并为一个文本字符串。注解u您也可使用连接符号(&)计算运算符代替 CONCATENATE 函数来连接文本项。比如=A1&B1 与=CONCATENATE(A1,B1)返回相同值。惯用函数惯用函数惯用函数惯用函数第34页计算字符算字符长度度LEN LEN(文本文本)计算字符串算字符串长度度 =LEN（“abcd
31、ef”）=6合并字符函数合并字符函数CONCATENATE 或或&CONCATENATE(文本文本1，)合并字符串合并字符串 =CONCATENATE（“a”,“ef”）=aef惯用函数惯用函数惯用函数惯用函数第35页将数将数值转化化为文本文本TEXT TEXT(数数值,参数参数)将数将数将数将数值转值转化化化化为为文本文本文本文本,参数普通参数普通参数普通参数普通为为0 0.=TEXT（1234,0）=1234还可用于可用于补0惯用函数惯用函数惯用函数惯用函数第36页在字符串中在字符串中查找特定字符找特定字符FIND FIND(文本文本,范范围,数数值)查找一个字符在另一个字符串中位置找一个
32、字符在另一个字符串中位置 数数值表示表示查找第几个找第几个.=FIND（“a”,“abcaef”,1）=1 =FIND（“a”,“abcaef”,2）=4惯用函数惯用函数惯用函数惯用函数第37页比比较两个字符是否完全相符两个字符是否完全相符EXACT EXACT(文本文本1,文本文本2)比比较两个字符是否完全相符两个字符是否完全相符,是是则为TRUE,不然不然为FALSE.=EXACT（“a”,“a”）=TRUE =EXACT（“a”,“ab”）=FALSE =EXACT（“a”,“A”）=FALSE惯用函数惯用函数惯用函数惯用函数第38页文本函数文本函数文本函数文本函数SUBSTITUTES
33、UBSTITUTE=SUBSTITUTE(=SUBSTITUTE(texttext,old_textold_text,new_textnew_text,instance_numinstance_num)Text 为需要替换其中字符文本，或对含有文本单元格引用。Old_text 为需要替换旧文本。New_text 用于替换 old_text 文本。Instance_num 为一数值，用来指定以 new_text 替换第几次出现 old_text。假如指定了 instance_num，则只有满足要求 old_text 被替换；不然将用 new_text 替换 Text 中出现全部 old_text
34、。在文本字符串中用 new_text 替换 old_text。惯用函数惯用函数惯用函数惯用函数第39页数学函数数学函数数学函数数学函数RANDRAND、RANDBETWEENRANDBETWEEN=RAND()=RAND()注解u若要生成 a 与 b 之间随机实数，请使用：RAND()*(b-a)+au假如要使用函数 RAND 生成一随机数，而且使之不随单元格计算而改变，能够在编辑栏中输入“=RAND()”，保持编辑状态，然后按 F9，将公式永久性地改为随机数。返回大于等于 0 及小于 1 均匀分布随机实数实数，每次计算工作表时都将返回一个新随机实数。=RANDBETWEEN(=RANDBET
35、WEEN(bottombottom,toptop)返回位于指定两个数之间一个随机整数。每次计算工作表时都将返回一个新随机整数整数。Bottom函数 RANDBETWEEN 将返回最小整数。Top函数 RANDBETWEEN 将返回最大整数。惯用函数惯用函数惯用函数惯用函数第40页统计函数统计函数统计函数统计函数RANKRANK=RANK(=RANK(numbernumber,refref,orderorder)Number 为需要找到排位数字。Ref 为数字列表数组或对数字列表引用。Ref 中非数值型参数将被忽略。Order 为一数字，指明排位方式。假如 order 为 0（零）或省略，Mic
36、rosoft Excel 对数字排位是基于 ref 为按照降序排列列表。假如 order 不为零，Microsoft Excel 对数字排位是基于 ref 为按照升序排列列表。注解u函数 RANK 对重复数排位相同。但重复数存在将影响后续数值排位。比如，在一列按升序排列整数中，假如整数 10 出现两次，其排位为 5，则 11 排位为 7（没有排位为 6 数值）。返回一个数字在数字列表中排位。惯用函数惯用函数惯用函数惯用函数第41页排位排位:RANKRANK(数数值,范范围,数数值)1-升序升序,0-降序降序 A B C 1 100 20 1000 2 200 40 3 300 60 3000=
37、RANK(A1,A1:A3,1)=1=RANK(A1,A1:A3,0)=3惯用函数惯用函数惯用函数惯用函数第42页数学函数数学函数数学函数数学函数ROUNDROUND=ROUND(=ROUND(numbernumber,num_digitsnum_digits)ROUND 函数可将某个数字四舍五入为指定位数。说明u假如 num_digits 大于 0（零），则将数字四舍五入到指定小数位。u假如 num_digits 等于 0，则将数字四舍五入到最靠近整数。u假如 num_digits 小于 0，则在小数点左侧进行四舍五入。u若要一直进行向上舍入（远离 0），请使用 ROUNDUP 函数。u若要
38、一直进行向下舍入（朝向 0），请使用 ROUNDDOWN 函数。u若要将某个数字四舍五入为指定倍数（比如，四舍五入为最靠近 0.5 倍），请使用 MROUND 函数。惯用函数惯用函数惯用函数惯用函数第43页四舍五入函数四舍五入函数:ROUND ROUND(数数值,数位数位(+/-)A B C 1 150.45 2899 0.2345 =ROUND(C1,2)=0.23 =ROUNDUP(C1,2)=0.24 =ROUNDDOWN(A1,1)=150.4惯用函数惯用函数惯用函数惯用函数第44页引用函数引用函数引用函数引用函数ROWROW、COLUMNCOLUMN说明：u假如省略 referenc
39、e，则假定是对函数所在单元格引用。u假如 reference 为一个单元格区域，而且函数作为数组输入，则函数 将以数组形式返回 reference 行号或列号。u假如参数 reference 为一个单元格区域，而且函数不是以数组公式形式输入，则 COLUMN 函数将返回最上侧行号或最左侧列列号。uReference 不能引用多个区域。=ROW(=ROW(referencereference)=ROWS(=ROWS(arrayarray)返回引用或行数。=COLUMNS(COLUMNS(arrayarray)返回数组或引用列数。=COLUMN(=COLUMN(referencereference
40、)惯用函数惯用函数惯用函数惯用函数第45页查找函数查找函数查找函数查找函数MATCHMATCH=MATCH(=MATCH(lookup_valuelookup_value,lookup_arraylookup_array,match_typematch_type)MATCH 函数可在单元格区域（区域:工作表上两个或多个单元格。区域中单元格能够相邻或不相邻。）中搜索指定项，然后返回该项在单元格区域中相对位置。Match_typeMatch_type行为行为1 或被省略MATCH 函数会查找小于或等于 lookup_value 最大值。lookup_array 参数中值必须按升序排列，比如：.-2
41、,-1,0,1,2,.,A-Z,FALSE,TRUE。0MATCH 函数会查找等于 lookup_value 第一个值。lookup_array 参数中值能够按任何次序排列。-1MATCH 函数会查找大于或等于 lookup_value 最小值。lookup_array 参数中值必须按降序排列，比如：TRUE,FALSE,Z-A,.2,1,0,-1,-2,.等等。惯用函数惯用函数惯用函数惯用函数第46页引用函数引用函数引用函数引用函数INDEXINDEX数组形式数组形式:=INDEX(:=INDEX(arrayarray,row_numrow_num,column_numcolumn_num)
42、引用形式引用形式:=INDEX(:=INDEX(referencereference,row_numrow_num,column_numcolumn_num,area_numarea_num)Array 为单元格区域或数组常量。Reference 对一个或多个单元格区域引用。假如为引用输入一个不连续区域，必须将其用括号括起来。假如引用中每个区域只包含一行或一列，则对应参数 row_num 或 column_num 分别为可选项。Row_num 引用中某行行号，函数从该行返回一个引用。Column_num 引用中某列列标，函数从该列返回一个引用。Area_num 选择引用中一个区域，返回该区域中
43、 row_num 和 column_num 交叉区域。选中或输入第一个区域序号为 1，第二个为 2，以这类推。假如省略 area_num，则函数 INDEX 使用区域 1。惯用函数惯用函数惯用函数惯用函数第47页引用函数引用函数引用函数引用函数INDEXINDEX惯用函数惯用函数惯用函数惯用函数第48页查找函数查找函数查找函数查找函数OFFSETOFFSET=OFFSET(=OFFSET(referencereference,rowsrows,colscols,heightheight,widthwidth)以指定引用为参考系，经过给定偏移量得到新引用。返回引用能够为一个单元格或单元格区域。并
44、能够指定返回行数或列数。Reference 作为偏移量参考系引用区域。Rows 相对于偏移量参考系左上角单元格，上（下）偏移行数。行数可为正数或负数。Cols 相对于偏移量参考系左上角单元格，左（右）偏移列数。列数可为正数或负数。Height 高度，即所要返回引用区域行数。Height 必须为正数。Width 宽度，即所要返回引用区域列数。Width 必须为正数。惯用函数惯用函数惯用函数惯用函数第49页查找函数查找函数查找函数查找函数VLOOKUPVLOOKUP、HLOOKUPHLOOKUP=VLOOKUP(=VLOOKUP(lookup_valuelookup_value,table_arr
45、aytable_array,col_index_numcol_index_num,range_lookuprange_lookup)Lookup_value为需要在表格数组第一列中查找数值。Lookup_value 能够为数值或引用。Table_array为两列或多列数据。使用对区域或区域名称引用。table_array table_array 第一列中值是由第一列中值是由 lookup_value lookup_value 搜索值。搜索值。这些值能够是文本、数字或逻辑值。文本不区分大小写。Col_index_num为 table_array 中待返回匹配值列序号。Col_index_num
46、为 1 时，返回 table_array 第一列中数值；col_index_num 为 2，返回 table_array 第二列中数值，以这类推。Range_lookup为逻辑值，指定希望 VLOOKUP 查找准确匹配值还是近似匹配值：假如为 TRUE 或省略，则返回准确匹配值或近似匹配值。也就是说，假如找不到准确匹配值，则返回小于 lookup_value 最大数值。table_array 第一列中值必须以升序排序；不然 VLOOKUP 可能无法返回正确值。假如为 FALSE，VLOOKUP 将只寻找准确匹配值。在此情况下，table_array 第一列值不需要排序。假如 table_arr
47、ay 第一列中有两个或多个值与 lookup_value 匹配，则使用第一个找到值。假如找不到准确匹配值，则返回错误值#N/A。惯用函数惯用函数惯用函数惯用函数第50页查找与引用函数找与引用函数:查找表格中找表格中值以列方式以列方式VLOOKUP Vlookup(文本文本,范范围,列列,FALSE)文本文本-条件条件 范范围-条件所在列条件所在列 列列-范范围中中对应列用数列用数值表示表示 FALSE-准确准确查找找另：另：HLOOKUP纵向向查找找惯用函数惯用函数惯用函数惯用函数第51页查找函数查找函数查找函数查找函数VLOOKUPVLOOKUP惯用函数惯用函数惯用函数惯用函数第52页查找函
48、数查找函数查找函数查找函数HLOOKUPHLOOKUP=HLOOKUP(=HLOOKUP(lookup_valuelookup_value,table_arraytable_array,row_index_numrow_index_num,range_lookuprange_lookup)惯用函数惯用函数惯用函数惯用函数第53页日期函数日期函数返回日期序列年份返回日期序列年份YEAR返回日期序列月份返回日期序列月份MONTH返回日期序列特定天返回日期序列特定天DATE返回系返回系统今天日期今天日期TODAY返回系返回系统今天日期与今天日期与时间NOW返回返回对应日期星期数日期星期数WEEKDAY惯用函数惯用函数惯用函数惯用函数第54页条件格式条件格式能能够输入公式或数入公式或数值最多能最多能够设三种条件格式，加无条件三种条件格式，加无条件时格格式，即依据一个式，即依据一个单元格内容最多可自元格内容最多可自动设置四种格式置四种格式惯用函数惯用函数惯用函数惯用函数第55页