日常EXCEL函数应用实例
合集下载
相关主题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
MID 返回文本字符串中从指定位置开始的特定数目的字符，该数 目由用户指定。
RIGHT 根据所指定的字符数返回文本字符串中最后一个或多个字 符。
Text 是包含要提取字符的文本字 符串。
Start_num 是文本中要提取的第 一个字符的位置。文本中第一个字符的 start_num 为 1，以此类推。
+*/
算术运算符 =5+7-9/3*4
2
&
文本运算符 =“Excel”&”home” 返回”Excelhome”
3
= <>
=(A1=A2) 判断A1与A2相等
> >= 比较运算符 =(B1<>”ABC”) 判断B1≠”ABC”
4
< <=
=(C1>=5) 判断C1≥5
括号可以改变运算的优先级别，多组括号进行嵌套是，计算顺序是由最内层的括号逐级向外进行 运算。
=TRIMMEAN(array,percent)返回数据集的内部平均值。函数 TRIMMEAN 先从数据集的头部和尾部除去一定百
分比的数据点，然后再求平均值。当希望在分析中剔除一部分数据的计算时，可以使用此函数。
=AVERAGEIF(range,criteria,average_range)
range 是要计算平均值的一个或多个单元 格，其中包括数字或包含数字的名称、数组或引 用。
选项卡中的“公式求值”按钮，弹出“公式 求值”对话框，然后多次单击“求值”按钮， “求值”域中将按照公式计算的顺序逐步地 显示公式的计算过程。
按<F9>功能键切换
方法二： <F9>功能键除了可以重算工作薄外，
还可以用于查看公式的运算结果。 当一个函数公式比较长时，可以用
<F9>键对各个部分进行分段计算，以便 公式差错和更正。在分段选择时，注意 要包含一个完整的运算对象。
注意： 参数的计算结果必须是逻辑值（如 TRUE 或 FALSE），而参数必须是包含逻辑值的数组 如果数组或引用参数中包含文本或空白单元格，则这些值将被忽略。 如果指定的单元格区域未包含逻辑值，则 函数将返回错误值 #VALUE!。
=LARGE(array,k)
返回数据集中第 k 个最大值。使用此函数可以根 据相对标准来选择数值。
=COUNT(value1, [value2],...)
注解 如果参数为数字、日期或者代表数字的文本 （例如，用引号引起的数字，如 "1"），则将被 计算在内。 逻辑值和直接键入到参数列表中代表数字的 文本被计算在内。 如果参数为错误值或不能转换为数字的文本， 则不会被计算在内。 如果参数为数组或引用，则只计算数组或引 用中数字的个数。不会计算数组或引用中的空单 元格、逻辑值、文本或错误值。 若要计算逻辑值、文本值或错误值的个数， 请使用 COUNTA 函数。 若要只计算符合某一条件的数字的个数，请 使用 COUNTIF 函数或 COUNTIFS 函数。
数字计算精度为15位。
突破：
使用定义名称。
当参数为单元格区域引用时， 可使用一对括号来包含多个单 元格区域的引用部分形成合并 区域引用作为函数参数。
1、提前将单元格格式设置为文 本后再输入数字 2、在输入数字时用半角单引号 开头。
在实际工作中，许多用户在计算数 字时会设置单元格格式为2为小数的数 值、货币格式或者是整数值。但在使用 公式进行数据处理的过程中，经常会发 现几个单元格之和与合计单元格显示的 数据之和不相等。
Num_chars 指定希望函数从文本 中返回字符的个数。
=CONCATENATE (text1,text2,...)
将两个或多个文本字符串合并为一个文本字符串。
注解 您也可使用连接符号 (&) 计算运算符代替 CONCATENATE 函数来连接文本项。例如 =A1&B1 与 =CONCATENATE(A1,B1) 返 回相同的值。
因为excel将数值按照15位计算精 度带入计算所致。如图，在excel选项 中勾选“将精度设为所显示的精度”， 可以解决上述情况的发生，但这样也降 低了工作薄中所有工作表的计算精度， 但不影响其他工作薄。
此外，也可以用ROUND函数对数值 进行修约后再求和。
方法一： 选中包含公式的单元格，单击“公式”
=RAND( )
返回大于等于 0 及小于 1 的均匀分布随机实数， 每次计算工作表时都将返回一个新的随机实数。
=RANDBETWEEN(bottom,top)
返回位于指定的两个数之间的一个随机整数。每 次计算工作表时都将返回一个新的随机整数。
=AND(logical1, [logical2], ...)
所有参数的计算结果为 TRUE 时，返回 TRUE；只 要有一个参数的计算结果为 FALSE，即返回 FALSE。
通过 AND 函数可以检验多个不同的条件，而不仅 仅是一个条件。
=OR(logical1,[logical2],...)
在其参数组中，任何一个参数逻辑值为 TRUE， 即返回 TRUE；任何一个参数的逻辑值为 FALSE，即 返回 FALSE。
1 EXCEL操作界面及设置 2 函数与公式通用技巧 3 常用函数
“公式”选项卡中，有“公式审核”、 “保护”等常用函数公式。
在“视图”选项卡中，通过勾选或取消勾选复选框来设置 界面中显示或者隐藏编辑栏、行号列标、工作表标签等。
Where is it？
右键单击工具栏→“自定义 快速访问工具栏”→弹出自 定义快速访问工具栏选项卡， 可以设置自定义访问工具栏。
=COUNTIF(range,criteria) 计算某个区域中满足给定条件的单元格数。例如：=COUNTIF(A2:A7,"<=20")
=LEFT(text,num_chars) =MID(text,start_num,num_chars) =RIGHT(text,num_chars)
根据所指定的字符数，LEFT 返回文本字符串中第一个字符或前 几个字符。
Array 为需要从中选择第 k 个最大值的数组或 数据区域。
K 为返回值在数组或数据单元格区域中的位置 （从大到小排）。
注解 如果数组为空，函数 LARGE 返回错误值 #NUM!。 如果 k ≤ 0 或 k 大于数据点的个数，函数 LARGE 返回错误值 #NUM!。 如果区域中数据点的个数为 n，则函数 LARGE(array,1) 返回最大值，函数 LARGE(array,n) 返回最小值。
=SMALL(array,k)
返回数据集中第 k 个最小值。
=MAX(number1,number2,...)
返回一组值中的最大值。 注解 参数可以是数字或者是包含数字的名称、数组或引用。 逻辑值和直接键入到参数列表中代表数字的文本被计算在内。 如果参数为数组或引用，则只使用该数组或引用中的数字。数组
①Baidu Nhomakorabea
②
=IFERROR(value,value_if_error)
如果公式计算出错误则返回您指定的值；否则返回公式结果。使用 IFERROR 函数来捕获和处理公式中的错误。 Value 是需要检查是否存在错误的参数。 Value_if_error 是公式计算出错误时要返回的值。计算得到的错误类型有：#N/A、#VALUE!、#REF!、#DIV/0!、 #NUM!、#NAME? 或 #NULL!。
算在内。 如果区域或单元格引用参数包含文本、逻辑值或空单
元格，则这些值将被忽略；但包含零值的单元格将被计算在内。 如果参数为错误值或为不能转换为数字的文本，将会导致错误。 若要在计算中包含引用中的逻辑值和代表数字的文本，请使用 AVERAGEA 函数。 若要只对符合某些条件的值计算平均值，请使用 AVERAGEIF 函数或 AVERAGEIFS 函数。
=SUBSTITUTE(text,old_text,new_text,instance_num)
在文本字符串中用 new_text 替代 old_text。
Text 为需要替换其中字符的文本，或对含有文本的单元格的引用。 Old_text 为需要替换的旧文本。 New_text 用于替换 old_text 的文本。 Instance_num 为一数值，用来指定以 new_text 替换第几次出现的 old_text。如果指定了 instance_num，则只 有满足要求的 old_text 被替换；否则将用 new_text 替换 Text 中出现的所有 old_text。
或引用中的空白单元格、逻辑值或文本将被忽略。 如果参数不包含数字，函数 MAX 返回 0（零）。 如果参数为错误值或为不能转换为数字的文本，将会导致错误。 如果要使计算包括引用中的逻辑值和代表数字的文本，请使用 MAXA 函数。
=MIN(number1,number2,...) =MEDIAN(number1,number2,...) =MODE(number1,number2,...)
=SUM(number1,number2, ...)
说明 直接键入到参数表中的数字、逻辑值及数字的文本表达式将被计算，请参阅下面的示例一和示例二。 如果参数是一个数组或引用，则只计算其中的数字。数组或引用中的空白单元格、逻辑值或文本将被忽略。请参阅 下面的第三个示例。 如果参数为错误值或为不能转换为数字的文本，将会导致错误。
criteria 是数字、表达式、单元格引用或 文本形式的条件，用于定义要对哪些单元格计算 平均值。例如，条件可以表示为 32、"32"、 ">32"、"苹果" 或 B4。
average_range 是要计算平均值的实际单 元格集。如果忽略，则使用 range。
注解： 忽略区域中包含 TRUE 或 FALSE 的单元格。 如果 average_range 中的单元格为空单元格，AVERAGEIF 将忽略它。 如果 range 为空值或文本值，则 AVERAGEIF 会返回 #DIV0! 错误值。 如果条件中的单元格为空单元格，AVERAGEIF 就会将其视为 0 值。 如果区域中没有满足条件的单元格，则 AVERAGEIF 会返回 #DIV/0! 错误值。 您可以在条件中使用通配符，即问号 (?) 和星号 (*)。 Average_range 不必与 range 的大小和形状相同。求平均值的实际单元格是通过使用 average_range 中左上方的单元格 作为起始单元格，然后加入与 range 的大小和形状相对应的单元格确定的。
符号 * ? ~
说明
实例
通配符，表示任何字符
=COUNTIF(A:A，“北京*有限公司”)
通配符，表示任何单个字符
=COUNTIF(A:A，“北京*有限??公司”)
通配符，用于解除字符的通配性 =COUNTIF(A:A，”北京~*有限公司”)
限制：
公式内容的长度不能超过1024个 字符； 公式中函数的嵌套不能超过7层； 公式中函数的参数不能超过30个；
1 EXCEL操作界面及设置 2 函数与公式通用技巧 3 常用函数
根据对指定的条件计算结果为 TRUE 或 FALSE，返回不同的结果。可以使用 IF 对数值和公式执行条件检测。 Microsoft Excel 还提供了其他一些函数，它们可根据条件来分析数据。例如，如果要计算某单元格区域内某个文本字符串 或数字出现的次数，则可使用 COUNTIF 和 COUNTIFS 电子表格函数。若要计算基于某区域内一个文本字符串或一个数值的总和， 可使用 SUMIF 和 SUMIFS 工作表函数。
Where is it？
1 EXCEL操作界面及设置 2 函数与公式通用技巧 3 常用函数
符号
说明
实例
优先顺序
：
=SUM(A1:B10) 区域运算符
_(空格) 引用运算符 =SUM(A1:B5 A4:D9) 交叉运算符，相当于=SUM(A4:B5)
1
，
=RANK(A1,(A1:A10,C1:C10)) 联合运算符
返回一组值中的最小值。 返回给定数值的中值。中值是在一组数值中居于中间的数值。 返回在某一数组或数据区域中出现频率最多（众数）的数值。
=AVERAGE(number1, [number2],...)
返回参数的平均值（算术平均值）。
注释： 参数可以是数字或者是包含数字的名称、单元格区域
或单元格引用。 逻辑值和直接键入到参数列表中代表数字的文本被计
RIGHT 根据所指定的字符数返回文本字符串中最后一个或多个字 符。
Text 是包含要提取字符的文本字 符串。
Start_num 是文本中要提取的第 一个字符的位置。文本中第一个字符的 start_num 为 1，以此类推。
+*/
算术运算符 =5+7-9/3*4
2
&
文本运算符 =“Excel”&”home” 返回”Excelhome”
3
= <>
=(A1=A2) 判断A1与A2相等
> >= 比较运算符 =(B1<>”ABC”) 判断B1≠”ABC”
4
< <=
=(C1>=5) 判断C1≥5
括号可以改变运算的优先级别，多组括号进行嵌套是，计算顺序是由最内层的括号逐级向外进行 运算。
=TRIMMEAN(array,percent)返回数据集的内部平均值。函数 TRIMMEAN 先从数据集的头部和尾部除去一定百
分比的数据点，然后再求平均值。当希望在分析中剔除一部分数据的计算时，可以使用此函数。
=AVERAGEIF(range,criteria,average_range)
range 是要计算平均值的一个或多个单元 格，其中包括数字或包含数字的名称、数组或引 用。
选项卡中的“公式求值”按钮，弹出“公式 求值”对话框，然后多次单击“求值”按钮， “求值”域中将按照公式计算的顺序逐步地 显示公式的计算过程。
按<F9>功能键切换
方法二： <F9>功能键除了可以重算工作薄外，
还可以用于查看公式的运算结果。 当一个函数公式比较长时，可以用
<F9>键对各个部分进行分段计算，以便 公式差错和更正。在分段选择时，注意 要包含一个完整的运算对象。
注意： 参数的计算结果必须是逻辑值（如 TRUE 或 FALSE），而参数必须是包含逻辑值的数组 如果数组或引用参数中包含文本或空白单元格，则这些值将被忽略。 如果指定的单元格区域未包含逻辑值，则 函数将返回错误值 #VALUE!。
=LARGE(array,k)
返回数据集中第 k 个最大值。使用此函数可以根 据相对标准来选择数值。
=COUNT(value1, [value2],...)
注解 如果参数为数字、日期或者代表数字的文本 （例如，用引号引起的数字，如 "1"），则将被 计算在内。 逻辑值和直接键入到参数列表中代表数字的 文本被计算在内。 如果参数为错误值或不能转换为数字的文本， 则不会被计算在内。 如果参数为数组或引用，则只计算数组或引 用中数字的个数。不会计算数组或引用中的空单 元格、逻辑值、文本或错误值。 若要计算逻辑值、文本值或错误值的个数， 请使用 COUNTA 函数。 若要只计算符合某一条件的数字的个数，请 使用 COUNTIF 函数或 COUNTIFS 函数。
数字计算精度为15位。
突破：
使用定义名称。
当参数为单元格区域引用时， 可使用一对括号来包含多个单 元格区域的引用部分形成合并 区域引用作为函数参数。
1、提前将单元格格式设置为文 本后再输入数字 2、在输入数字时用半角单引号 开头。
在实际工作中，许多用户在计算数 字时会设置单元格格式为2为小数的数 值、货币格式或者是整数值。但在使用 公式进行数据处理的过程中，经常会发 现几个单元格之和与合计单元格显示的 数据之和不相等。
Num_chars 指定希望函数从文本 中返回字符的个数。
=CONCATENATE (text1,text2,...)
将两个或多个文本字符串合并为一个文本字符串。
注解 您也可使用连接符号 (&) 计算运算符代替 CONCATENATE 函数来连接文本项。例如 =A1&B1 与 =CONCATENATE(A1,B1) 返 回相同的值。
因为excel将数值按照15位计算精 度带入计算所致。如图，在excel选项 中勾选“将精度设为所显示的精度”， 可以解决上述情况的发生，但这样也降 低了工作薄中所有工作表的计算精度， 但不影响其他工作薄。
此外，也可以用ROUND函数对数值 进行修约后再求和。
方法一： 选中包含公式的单元格，单击“公式”
=RAND( )
返回大于等于 0 及小于 1 的均匀分布随机实数， 每次计算工作表时都将返回一个新的随机实数。
=RANDBETWEEN(bottom,top)
返回位于指定的两个数之间的一个随机整数。每 次计算工作表时都将返回一个新的随机整数。
=AND(logical1, [logical2], ...)
所有参数的计算结果为 TRUE 时，返回 TRUE；只 要有一个参数的计算结果为 FALSE，即返回 FALSE。
通过 AND 函数可以检验多个不同的条件，而不仅 仅是一个条件。
=OR(logical1,[logical2],...)
在其参数组中，任何一个参数逻辑值为 TRUE， 即返回 TRUE；任何一个参数的逻辑值为 FALSE，即 返回 FALSE。
1 EXCEL操作界面及设置 2 函数与公式通用技巧 3 常用函数
“公式”选项卡中，有“公式审核”、 “保护”等常用函数公式。
在“视图”选项卡中，通过勾选或取消勾选复选框来设置 界面中显示或者隐藏编辑栏、行号列标、工作表标签等。
Where is it？
右键单击工具栏→“自定义 快速访问工具栏”→弹出自 定义快速访问工具栏选项卡， 可以设置自定义访问工具栏。
=COUNTIF(range,criteria) 计算某个区域中满足给定条件的单元格数。例如：=COUNTIF(A2:A7,"<=20")
=LEFT(text,num_chars) =MID(text,start_num,num_chars) =RIGHT(text,num_chars)
根据所指定的字符数，LEFT 返回文本字符串中第一个字符或前 几个字符。
Array 为需要从中选择第 k 个最大值的数组或 数据区域。
K 为返回值在数组或数据单元格区域中的位置 （从大到小排）。
注解 如果数组为空，函数 LARGE 返回错误值 #NUM!。 如果 k ≤ 0 或 k 大于数据点的个数，函数 LARGE 返回错误值 #NUM!。 如果区域中数据点的个数为 n，则函数 LARGE(array,1) 返回最大值，函数 LARGE(array,n) 返回最小值。
=SMALL(array,k)
返回数据集中第 k 个最小值。
=MAX(number1,number2,...)
返回一组值中的最大值。 注解 参数可以是数字或者是包含数字的名称、数组或引用。 逻辑值和直接键入到参数列表中代表数字的文本被计算在内。 如果参数为数组或引用，则只使用该数组或引用中的数字。数组
①Baidu Nhomakorabea
②
=IFERROR(value,value_if_error)
如果公式计算出错误则返回您指定的值；否则返回公式结果。使用 IFERROR 函数来捕获和处理公式中的错误。 Value 是需要检查是否存在错误的参数。 Value_if_error 是公式计算出错误时要返回的值。计算得到的错误类型有：#N/A、#VALUE!、#REF!、#DIV/0!、 #NUM!、#NAME? 或 #NULL!。
算在内。 如果区域或单元格引用参数包含文本、逻辑值或空单
元格，则这些值将被忽略；但包含零值的单元格将被计算在内。 如果参数为错误值或为不能转换为数字的文本，将会导致错误。 若要在计算中包含引用中的逻辑值和代表数字的文本，请使用 AVERAGEA 函数。 若要只对符合某些条件的值计算平均值，请使用 AVERAGEIF 函数或 AVERAGEIFS 函数。
=SUBSTITUTE(text,old_text,new_text,instance_num)
在文本字符串中用 new_text 替代 old_text。
Text 为需要替换其中字符的文本，或对含有文本的单元格的引用。 Old_text 为需要替换的旧文本。 New_text 用于替换 old_text 的文本。 Instance_num 为一数值，用来指定以 new_text 替换第几次出现的 old_text。如果指定了 instance_num，则只 有满足要求的 old_text 被替换；否则将用 new_text 替换 Text 中出现的所有 old_text。
或引用中的空白单元格、逻辑值或文本将被忽略。 如果参数不包含数字，函数 MAX 返回 0（零）。 如果参数为错误值或为不能转换为数字的文本，将会导致错误。 如果要使计算包括引用中的逻辑值和代表数字的文本，请使用 MAXA 函数。
=MIN(number1,number2,...) =MEDIAN(number1,number2,...) =MODE(number1,number2,...)
=SUM(number1,number2, ...)
说明 直接键入到参数表中的数字、逻辑值及数字的文本表达式将被计算，请参阅下面的示例一和示例二。 如果参数是一个数组或引用，则只计算其中的数字。数组或引用中的空白单元格、逻辑值或文本将被忽略。请参阅 下面的第三个示例。 如果参数为错误值或为不能转换为数字的文本，将会导致错误。
criteria 是数字、表达式、单元格引用或 文本形式的条件，用于定义要对哪些单元格计算 平均值。例如，条件可以表示为 32、"32"、 ">32"、"苹果" 或 B4。
average_range 是要计算平均值的实际单 元格集。如果忽略，则使用 range。
注解： 忽略区域中包含 TRUE 或 FALSE 的单元格。 如果 average_range 中的单元格为空单元格，AVERAGEIF 将忽略它。 如果 range 为空值或文本值，则 AVERAGEIF 会返回 #DIV0! 错误值。 如果条件中的单元格为空单元格，AVERAGEIF 就会将其视为 0 值。 如果区域中没有满足条件的单元格，则 AVERAGEIF 会返回 #DIV/0! 错误值。 您可以在条件中使用通配符，即问号 (?) 和星号 (*)。 Average_range 不必与 range 的大小和形状相同。求平均值的实际单元格是通过使用 average_range 中左上方的单元格 作为起始单元格，然后加入与 range 的大小和形状相对应的单元格确定的。
符号 * ? ~
说明
实例
通配符，表示任何字符
=COUNTIF(A:A，“北京*有限公司”)
通配符，表示任何单个字符
=COUNTIF(A:A，“北京*有限??公司”)
通配符，用于解除字符的通配性 =COUNTIF(A:A，”北京~*有限公司”)
限制：
公式内容的长度不能超过1024个 字符； 公式中函数的嵌套不能超过7层； 公式中函数的参数不能超过30个；
1 EXCEL操作界面及设置 2 函数与公式通用技巧 3 常用函数
根据对指定的条件计算结果为 TRUE 或 FALSE，返回不同的结果。可以使用 IF 对数值和公式执行条件检测。 Microsoft Excel 还提供了其他一些函数，它们可根据条件来分析数据。例如，如果要计算某单元格区域内某个文本字符串 或数字出现的次数，则可使用 COUNTIF 和 COUNTIFS 电子表格函数。若要计算基于某区域内一个文本字符串或一个数值的总和， 可使用 SUMIF 和 SUMIFS 工作表函数。
Where is it？
1 EXCEL操作界面及设置 2 函数与公式通用技巧 3 常用函数
符号
说明
实例
优先顺序
：
=SUM(A1:B10) 区域运算符
_(空格) 引用运算符 =SUM(A1:B5 A4:D9) 交叉运算符，相当于=SUM(A4:B5)
1
，
=RANK(A1,(A1:A10,C1:C10)) 联合运算符
返回一组值中的最小值。 返回给定数值的中值。中值是在一组数值中居于中间的数值。 返回在某一数组或数据区域中出现频率最多（众数）的数值。
=AVERAGE(number1, [number2],...)
返回参数的平均值（算术平均值）。
注释： 参数可以是数字或者是包含数字的名称、单元格区域
或单元格引用。 逻辑值和直接键入到参数列表中代表数字的文本被计