信创办公–基于WPS的EXCEL最佳实践系列 （公式和函数）

信创办公–基于WPS的EXCEL最佳实践系列 （公式和函数）

---

应用背景

熟练掌握Excel的函数工具能让我们在日常的使用中更加方便快捷。

相关知识

• 在使用函数时点击f4能起到锁定行和列的作用。
• 在使用count函数时，只能统计数字种类，不能对文本进行统计。
• 在使用函数时遇到大于或小于等文本条件时，都要用双引号括起来。

操作步骤

1、认识基本的初级函数

这类函数操作相似，在表格先输入等号，之后的输入它会自动转化为函数模式，在括号里面输入想要运算的范围（在输入函数时，可点击选中样本数字，更便捷选中范围）。要注意的是表格的格式必须是“常规函数”才能正常运算。得出结果后可以直接双击右下角的加号按钮，快速算出下列所有数据。

• 1.1 常用公式

函数名	功能
Sum	求和
Average	平均值
Count	计数
Max	最大值
Min	最小值

表1-1

语法：=函数名（范围）
说明：此类函数的参数只有一个运算范围，可以是简短的单元格或者数据区域。

• 1.2 实例

求和：输入“=sum（）”当光标在两括号中间时，选中一月到六月销售额，然后回车。如图1-1、1-2所示：

图1-1 使用“sum”函数

图1-2 使用“sum”函数结果

最大值：输入“=max（）”，同样光标在括号中间时，选中一到六月销售额，然后回车。如图1-3、1-4所示：

图1-3 使用“max”函数

图1-4 使用“max”函数结果

最小值：输入“=min（）”，当光标在括号中间时，选中一到六月销售额，然后回车。如图1-5、1-6所示：

图1-5 使用“min”函数

图1-6 使用min函数结果

平均值：输入“=average（）”然后选中数据，回车。如图1-7、1-8所示：

图1-7 使用“average”函数

图1-8 使用“average”函数结果

计数：输入“=count（）”，然后在括号里面选中所需数据，回车。如图1-9、1-10所示：

图1-9 使用“count”函数

图1-10 使用“count”函数结果

2、相对引用，绝对引用，混合引用

• 2.1 相对引用
  该引用方式引用的是当前行或列的实际偏移量，当把公式复制到其他单元格时，行或列引用会改变（上述双击加号快速算出整行整列数据的操作，即为相对引用）。

• 2.2 绝对引用
  该引用方式引用的是单元格的实际地址，复制公式时，行和列引用不会改变。

• 2.3 混合引用
  行和列中有一个是相对引用，另一个是绝对引用。

• 2.4 实例
  如果按下图输入， “可乐”的利润=销售额*利润率 能正常计算出来，但在向下引用的时候，将会得不出结果，原因是D2变成D3时，K1也会变成K2所以我们要锁定K1，这就是“绝对引用”。在选中k1单位格后，点击【F4】即可锁定。如图2-1所示。

图2-1 锁定k1单位格

在输入完K1后点击f4可以看到出现$符号，这时锁定成功。2-2所示。

图2-2 锁定成功

之后即可引用到整列，如图2-3所示。

图2-3 应用整列成功

下面这种我们需要对数据的行或列分别进行锁定，只需多点几次f4即可实现。如图2-4所示。

图2-4 输入等号，选择要相乘的两个单元格

锁定原价的列，锁定折扣的行，如图2-5所示。

图2-5 对行和列分别进行锁定

之后即可应用到整个表格，这是混合引用，如图2-6、2-7所示。

图2-6 锁定成功

图2-7 应用成功

3、统计函数

• 3.1 countif函数，sumif函数，averageif函数

单条件计数：Countif

• Countif是条件计数函数，按照要求的条件，统计符合要求的数据个数。
• 语法：=Countif（区域，条件）
• 说明：“条件范围”是用来计算的数据范围；“条件”是指明需要的数据的类型或范围。

单条件求和：sumif

• Sumif是条件求和函数，用于按计算满足要求的数据的总和。
• 语法：=sumif（区域，条件，[求和区域]）
• 说明：“条件范围”用于指明条件筛选的数据范围;“条件”用于指明筛选的条件；“求和范围”用于指定与筛选条件相对应的用于求和的数据范围。

单条件求平均值：averageif

• averageif是返回某个区域内满足给定条件的所有单元格的平均值（算术平均值）。 如果条件中的单元格为空单元格，averageif就会将其视为 0 值。

• 语法：=averageif（区域，条件，[求平均值区域]）

• 说明：“区域”用于指明平均值所求的区域/人员区域；“条件”用于指明筛选的条件；[求平均值区域]即求平均值的区域。

• 3.2 实例

Countif（区域，条件）

我们要算小红的订单数，输入“countif=（）”，选定“条件范围”为“销售” ，“条件”为“小红”，如图3-1、3-2所示。

图3-1 选定“条件范围”和“条件”

图3-2 “countif”函数应用成功

Sumif（区域，条件，[求和区域]）

我们需要算小红的销售额，“条件范围”为“销售”，“条件”为“小红”，求和范围为金额，如图3-3、3-4所示。

图3-3 选定“条件范围”和“条件”

图3-4 使用“sumif”函数成功

averageif（区域，条件，[求平均值区域]）
平均交易额同理，输入“=averageif（）”选择“区域”、“条件”和“[求平均值区域]”，然后回车，如图3-5、3-6所示。

图3-5 选中“条件范围”和“条件”

图3-6 应用“averageif”成功

Averageif（区域，条件）
金额数值超过50的平均值，输入“=averageif（）”选择“区域”和“条件”，然后回车，如图3-7、3-8所示。

图3-7 选择数据

图3-8 求超50金额的平均值结果

4、文本函数

• 4.1 截取函数和upper/lower函数

Left Right mid 函数

• 这是三个常用的文本截取函数，分别是“左截取函数”，“右截取函数”和“中间截取函数”。
• 语法：=left/right（字符串，[字符个数]）
  =mid（字符串，开始位置，字符个数）

upper/lower函数

• 将文本更改为大写小写

• 语法：upper/lower（字符串）

• 4.2 实例

Left right mid函数
要筛选出字段最左边一定位数的字符：输入“=left（）”，选中字符串，然后输入字符个数，最后回车，如图4-1、4-2所示。

图4-1 使用left函数

图4-2 使用left函数成功

要筛选出字段中一定位数的字符：输入“mid=（）”，选中字符串，输入开始位置和字符个数，然后回车，如图4-3、4-4所示。

图4-3 使用mid函数

图4-4 使用mid函数成功

要筛选出字段最右边一定位数的字符：输入“=right（）”，选中字符串，然后输入字符个数，最后回车，如图4-5、4-6所示

图4-5 使用right函数

图4-6 使用right函数成功

Upper lower 函数
要将一段英文转化成全部大写使用，输入“=upper（）”选中字符串，然后回车，如图4-7、4-8所示。

图4-7 使用upper函数

图4-8 使用upper函数成功

要将一段英文转化成全部小写使用，输入“=lower（）”选中字符串，然后回车。如图4-9、4-10所示。

图4-9 使用lower函数

图4-10 使用lower函数成功

• 4.3 trim函数，concatenate函数

Trim函数

• Tin去除文本中多余的空格
• 语法：trim（字符串）

Concatenate函数

• 将单个文本链接成一个文本

• 语法：=concatenate（字符串1，字符串2…）

• 4.4 实例

Concatenate函数
要将多个文本连接在一起：输入“=concatenate=（）”然后选中要连接的文本，如图4-11、4-12所示。

图4-11 使用concatenate函数

图4-12 使用concatenate函数成功

Trim函数
要去除多余空格：输入“=trim（）”选中字段，然后回车，如图4-13、4-14所示。

图4-13 使用trim函数

图4-14 使用trim函数成功

---

制作成员： 丘铭旭
排版： 丘铭旭
初审： 何翠婷
复审： 玩具熊