日常EXCEL函数应用实例

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