Excel常用函数（一）

灰太狼 2022-12-24 00:38 194阅读 0赞

Excel常用函数（一）的表格下载链接：

**文章目录**

一、公式中常用的运算符和运算函数

二、函数引用方式

三、格式转换

3.1 货币符号与单位的添加

3.2 英文大小写转换

3.3 文本数值的转换

3.4 数字及金额的大小写转换

3.5 TEXT与格式转换

四、文本处理

4.1 文本的连接

4.2 文本的提取

4.3 文本的查找和替换

4.4 字符与字节数的计算

4.5 两列数据的核对

4.6 数据的清理

五、汇总数据

5.1 统计汇总三大函数

5.2 描述性统计

5.3 数据排名

5.4 增长率的计算

--------------------

# 一、公式中常用的运算符和运算函数 #

1.  加：SUM（1,2,3）
2.  乘：PRODUCT（1,2,3）
3.  乘幂：POWER（2,3）
4.  绝对值：ABS（-100）
5.  四舍五入：ROUND（12.345,2）
6.  取实数，向下取整：INT（12.345）
7.  求余数：MOD（20,3）
8.  求平方根：SQART（81）
9.  求乘积的和：SUMPRODUCT

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTk4NDA0NA_size_16_color_FFFFFF_t_70][]

--------------------

# 二、函数引用方式 #

1.  相对引用：
2.  绝对引用：行和列前面加美元符号$。输入方法：输入公式数据sum(B2:E2)后，按住 F4 键；如果是笔记本，则按住 Fn 和 F4 键。
3.  半绝对引用半相对引用：输入公式数据后，按F4键就是添加美元符号成为行列绝对引用；再按一次F4键，就只有行绝对引用，列的绝对引用被取消；再按一次F4键，就只有列绝对引用，行的绝对引用取消；再按一次F4键，行和列的绝对引用都被取消，成为相对引用。

或者简单点的方式就是：光标定位住需要绝对引用的行或者列，按 F4 键（笔记本按 Fn 和 F4 键）。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTk4NDA0NA_size_16_color_FFFFFF_t_70 1][]

一键求和：

例如求多处总计数，选中所有数据，然后按 Alt 和 = 即可对所有数据求和。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTk4NDA0NA_size_16_color_FFFFFF_t_70 2][] ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTk4NDA0NA_size_16_color_FFFFFF_t_70 3][]

占比中的绝对引用：

原来是小数值占比如0.25，按住 Ctrl 和 Shift 和 5 即可转换为百分比25%

在求占比下拉公式时会发现报错，因为下拉公式时用了相对引用，应该用绝对引用使得每次都除以总计数。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTk4NDA0NA_size_16_color_FFFFFF_t_70 4][] ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTk4NDA0NA_size_16_color_FFFFFF_t_70 5][]

VLOOKUP函数中的绝对引用：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTk4NDA0NA_size_16_color_FFFFFF_t_70 6][]

提示：上述公式中，如果要对A13进行绝对引用，就把在输入公式中选中A13，然后按 F4 键（笔记本按 Fn和F4 键）即可。

--------------------

# 三、格式转换 #

## 3.1 货币符号与单位的添加 ##

如果直接在计算值后面加单位，则最后的合计无法计算，因为加了连接符后变成了文本，无法计算。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTk4NDA0NA_size_16_color_FFFFFF_t_70 7][]

所以，建议用自定义格式添加单位：

首先，Ctr l和 1 快速打开设置单元格格式，自定义格式：0.00 件，这样只是改变单元格的显示，不会改变数据本身。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTk4NDA0NA_size_16_color_FFFFFF_t_70 8][] ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTk4NDA0NA_size_16_color_FFFFFF_t_70 9][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTk4NDA0NA_size_16_color_FFFFFF_t_70 10][]

快捷键：把数值转为成某种格式：

1、转换成人民币格式：单击单元格，按Ctrl 和 Shift 和 4

2、转换成时间格式：单击单元格，按Ctrl 和 Shift 和 2

3、转换成日期格式：单击单元格，按Ctrl 和 Shift 和 3

4、转换成百分比格式：单击单元格，按Ctrl 和 Shift 和 5

5、转换成科学计数法格式：单击单元格，按Ctrl 和 Shift 和 6

## 3.2 英文大小写转换 ##

1、UPPER：全部字母转换成大写

2、LOWER：全部字母转换成小写

3、PROPER：首字母转换成大写

## 3.3 文本数值的转换 ##

用 VALUE() 或者 NUMBERVALUE() 函数。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTk4NDA0NA_size_16_color_FFFFFF_t_70 11][]

## 3.4 数字及金额的大小写转换 ##

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTk4NDA0NA_size_16_color_FFFFFF_t_70 12][]

提示：dunum2 表示把阿拉伯数字转化为中文本大写：壹、贰、叁...；dbnum1 表示把阿拉伯数字转化为中文本：一、二、三...

## 3.5 TEXT与格式转换 ##

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTk4NDA0NA_size_16_color_FFFFFF_t_70 13][]

--------------------

# 四、文本处理 #

## 4.1 文本的连接 ##

选中整列的文本用快捷键：Ctrl 和 Shift 和 向下箭头

文本的连接可用 & 、CONCATENATE 或者 PHONETIC ，其中 PHONETIC 最为快捷方便。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTk4NDA0NA_size_16_color_FFFFFF_t_70 14][]

## 4.2 文本的提取 ##

文本的提取有 LEFT()、RIGHT() 、MID() 函数。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTk4NDA0NA_size_16_color_FFFFFF_t_70 15][]

## 4.3 文本的查找和替换 ##

文本的查找用FIND()函数

文本的替换用REPLACE()函数、 SUBSTITUTE()函数。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTk4NDA0NA_size_16_color_FFFFFF_t_70 16][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTk4NDA0NA_size_16_color_FFFFFF_t_70 17][]

## 4.4 字符与字节数的计算 ##

提示：1个汉字包含2个字节，一个字母/数字包含1个字节

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTk4NDA0NA_size_16_color_FFFFFF_t_70 18][]

## 4.5 两列数据的核对 ##

文本数据的核对用EXACT()

数值数据的核对用DELTA()

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTk4NDA0NA_size_16_color_FFFFFF_t_70 19][]

## 4.6 数据的清理 ##

当数据中有空格或者回车符时，需要清除这些不必要的格式。例如英文中每个单词间有一个空格即可，但是却空出很多个空格，这时就需要清除不必要的空格，用trim()函数。当有回车符，不必要跳转到下一行时，就需要清除回车符，用clean()函数。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTk4NDA0NA_size_16_color_FFFFFF_t_70 20][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTk4NDA0NA_size_16_color_FFFFFF_t_70 21][]

--------------------

# 五、汇总数据 #

## 5.1 统计汇总三大函数 ##

SUM：求和， COUNT：计数、 AVERAGE：平均

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTk4NDA0NA_size_16_color_FFFFFF_t_70 22][]

## 5.2 描述性统计 ##

MAX 、MIN、 MEDIAN 、LARGE 、SAMLL 等

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTk4NDA0NA_size_16_color_FFFFFF_t_70 23][]

## 5.3 数据排名 ##

数据排名用RANK函数。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTk4NDA0NA_size_16_color_FFFFFF_t_70 24][]

## 5.4 增长率的计算 ##

环比指的是下个月比上个月的增长率，用下个月的数值除以上个月的数值，再减去1

同比指的是这年的这个月比上一年的同一个月份的增长率，用这年的这个月的数值除以上一年的同一个月份的数值，再减去1

复核增长率用最后一年的数值除以第一年的数值，乘以1/n（n指的是增长的时间，例如2010到2016就是增长了6年，就是1/6），最后减去1

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTk4NDA0NA_size_16_color_FFFFFF_t_70 25][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTk4NDA0NA_size_16_color_FFFFFF_t_70 26][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTk4NDA0NA_size_16_color_FFFFFF_t_70 27][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTk4NDA0NA_size_16_color_FFFFFF_t_70]: /images/20221120/094ae1e0d7dd43a6850419f0dac9b6b2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTk4NDA0NA_size_16_color_FFFFFF_t_70 1]: /images/20221120/da8f4a1f67c8430a9d2824193ff0945c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTk4NDA0NA_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20201126161653306.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTk4NDA0NA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTk4NDA0NA_size_16_color_FFFFFF_t_70 3]: /images/20221120/99fb045813b648cda43547c9f0d36bc1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTk4NDA0NA_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20201126163457679.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTk4NDA0NA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTk4NDA0NA_size_16_color_FFFFFF_t_70 5]: /images/20221120/99a5a09a91414354b7d38d4a9d4fc181.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTk4NDA0NA_size_16_color_FFFFFF_t_70 6]: /images/20221120/d7e349ceb59a41d59bf08671480ac802.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTk4NDA0NA_size_16_color_FFFFFF_t_70 7]: /images/20221120/6dd7f5a2f3304d778734a17c5853410f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTk4NDA0NA_size_16_color_FFFFFF_t_70 8]: https://img-blog.csdnimg.cn/20201126174151383.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTk4NDA0NA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTk4NDA0NA_size_16_color_FFFFFF_t_70 9]: /images/20221120/d346f6b84fb74692b0d488e82b415cec.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTk4NDA0NA_size_16_color_FFFFFF_t_70 10]: /images/20221120/054e40448249485295f23932c9bd567f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTk4NDA0NA_size_16_color_FFFFFF_t_70 11]: /images/20221120/bcc7fd29189f49ae901873b7b78a6388.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTk4NDA0NA_size_16_color_FFFFFF_t_70 12]: /images/20221120/5974ffe7a1d14146a92058dbf9c068ce.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTk4NDA0NA_size_16_color_FFFFFF_t_70 13]: /images/20221120/3c3042bf35dc4c5fa7b25750d9fc883d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTk4NDA0NA_size_16_color_FFFFFF_t_70 14]: /images/20221120/81bc903d92fc4327af6fd8393c2c4f80.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTk4NDA0NA_size_16_color_FFFFFF_t_70 15]: /images/20221120/2bd9b0cd64894755bba970186d3f37c4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTk4NDA0NA_size_16_color_FFFFFF_t_70 16]: /images/20221120/5e1c78ed14f745619a4802bc947a0d67.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTk4NDA0NA_size_16_color_FFFFFF_t_70 17]: /images/20221120/0e62279c0a4f4d81b6c6ff3364ca5ca2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTk4NDA0NA_size_16_color_FFFFFF_t_70 18]: /images/20221120/2e1509f23c4a4e1aac45a7f63d7e7e09.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTk4NDA0NA_size_16_color_FFFFFF_t_70 19]: /images/20221120/e165e695cd8b423e9b46f9defea9e8d0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTk4NDA0NA_size_16_color_FFFFFF_t_70 20]: /images/20221120/2bb006bcec26450d82fa41150c49600b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTk4NDA0NA_size_16_color_FFFFFF_t_70 21]: /images/20221120/0c8066644d144724abb45f0aaf950467.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTk4NDA0NA_size_16_color_FFFFFF_t_70 22]: /images/20221120/f23e7a7a302c48b2b2b20ca2a8738f13.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTk4NDA0NA_size_16_color_FFFFFF_t_70 23]: /images/20221120/0238f233dde44407ba5398526cc67f88.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTk4NDA0NA_size_16_color_FFFFFF_t_70 24]: /images/20221120/96c610010ffa49d1a98766b1785a005f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTk4NDA0NA_size_16_color_FFFFFF_t_70 25]: /images/20221120/b8c3496c0ca84fda8e89c445294d3581.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTk4NDA0NA_size_16_color_FFFFFF_t_70 26]: /images/20221120/3d8ade0ed06d452b86377aa301d36680.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTk4NDA0NA_size_16_color_FFFFFF_t_70 27]: /images/20221120/79b9ee9d82d04b289610e68941dfa576.png