常用Excel函数使用示例(分列、VLOOKUP、COUNTIF、AND、绝对引用等)

- 日理万妓 2023-02-16 12:05 18阅读 0赞

### 文章目录 ###

*  一、后置负号转换为前置负号
 *   *  1.1 方法1
     *  1.2 方法2：分列（最快捷实现方式，推荐此种方式）
     *  1.3 方法3：分列
 *  二、VLOOKUP使用示例
 *  三、行或列数据重复检查-IF & COUNTIF
 *  四、AND和OR函数使用示例
 *  五、绝对引用、相对引用、混合引用
 *   *  5.1 概念
     *  5.2 引用格式和如何添加引用
     *  5.3 引用示例

# 一、后置负号转换为前置负号 #

**业务场景**：使用SAP系统导出数据到excel表格，负号都是后置的，导致无法进行相关的数字操作。

## 1.1 方法1 ##

**解决思路**：判断字符串最后一位是否为负数（-）  
如果不是，则为正数，无需转换；如果是，则为负数，截取负号之前的字符串，然后取反  
**实现效果如下**：  
![在这里插入图片描述][20200609192927521.jpg]  
A列值设置如下：  
**A1 =IF(RIGHT(B2,1)="-",0-LEFT(B2,LEN(B2)-1),B2)**  
IF函数： IF(判断条件,真值,假值)。判断语句：判断条件为真，返回“真值”，反之返回“假值”  
RIGHT函数：RIGHT(字符串，字符个数)。从最右边第一位开始截取的字符个数  
LEFT函数： LEFT(字符串，字符个数)。从最右左边第一位开始截取的字符个数  
LEN函数： LEN(字符串)。字符串长度

## 1.2 方法2：分列（最快捷实现方式，推荐此种方式） ##

原始待清洗数据  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdsZWk4ODA1MjY_size_16_color_FFFFFF_t_70_pic_center]  
选定A列中所有需要清洗的数据，菜单栏切换到“数据”，然后点击“分列”按钮，如下所示  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdsZWk4ODA1MjY_size_16_color_FFFFFF_t_70_pic_center 1]  
上图中，选择“分隔符号”，然后点击“下一步”  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdsZWk4ODA1MjY_size_16_color_FFFFFF_t_70_pic_center 2]  
分隔符号选择“Tab键(T)”，点击“下一步”  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdsZWk4ODA1MjY_size_16_color_FFFFFF_t_70_pic_center 3]  
“目标区域”即分列后的数据存放位置。  
设置目标区域时，选择和选定数据首行的其他列的单元格即可，对于上图中，选择B2即可。  
点击“完成”  
![在这里插入图片描述][20200823191946613.jpg_pic_center]

## 1.3 方法3：分列 ##

原始待清洗数据  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdsZWk4ODA1MjY_size_16_color_FFFFFF_t_70_pic_center 4]  
选定A列中所有需要清洗的数据，菜单栏切换到“数据”，然后点击“分列”按钮，如下所示  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdsZWk4ODA1MjY_size_16_color_FFFFFF_t_70_pic_center 5]  
上图中，选择“分隔符号”，然后点击“下一步”  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdsZWk4ODA1MjY_size_16_color_FFFFFF_t_70_pic_center 6]  
分隔符号选择“其他(O)”，输入“-”，点击“下一步”  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdsZWk4ODA1MjY_size_16_color_FFFFFF_t_70_pic_center 7]  
“目标区域”即分列后的数据存放位置。  
设置目标区域时，选择和选定数据首行的其他列的单元格即可，对于上图中，选择B2即可。  
点击“完成”  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdsZWk4ODA1MjY_size_16_color_FFFFFF_t_70_pic_center 8]  
如上图所示，处理后数据，都变成了正数，显示不满足需要的结果。  
C列使用函数IF，公式：**=IF(B\[n\]<>A\[n\],-B\[n\],B\[n\])**，其中\[n\]表示当前行号，如C2的值为=IF(B2<>A2,-B2,B2)  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdsZWk4ODA1MjY_size_16_color_FFFFFF_t_70_pic_center 9]

# 二、VLOOKUP使用示例 #

应用场景：比较A列和C列是否存在相同的值，对比结果在E列中显示  
E\[n\] =VLOOKUP(A\[n\],C:C,1,FALSE)  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdsZWk4ODA1MjY_size_16_color_FFFFFF_t_70]

# 三、行或列数据重复检查-IF & COUNTIF #

C2 = IF(COUNTIF(B:B,B2)>1,B2&“重复”,"")  
Cn = IF(COUNTIF(B:B,Bn)>1,Bn&“重复”,"")  
D2 = IF(COUNTIF(B:B,B2)>1,COUNTIF(B:B,B2),"")  
Dn = IF(COUNTIF(B:B,Bn)>1,COUNTIF(B:B,Bn),"")  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdsZWk4ODA1MjY_size_16_color_FFFFFF_t_70 1]

# 四、AND和OR函数使用示例 #

语法：AND(逻辑值1,逻辑值2,逻辑值3,…) 逻辑值1、逻辑值2、逻辑值3 同时满足条件时， 返回结果为TRUE，否则为FALSE  
语法：OR(逻辑值1,逻辑值2,逻辑值3,…) 逻辑值1、逻辑值2、逻辑值3 有一个满足条件时，返回结果为TRUE，否则为FALSE

一般结合其他函数使用  
示例：结合IF函数使用  
语数外三个科目同时满足大于等于60时，判断为“合格”，否则“不合格”  
第2行 =IF(AND(A2>=60,B2>=60,C2>=60),“合格”,“不合格”)  
第3行=IF(AND(A3>=60,B3>=60,C3>=60),“合格”,“不合格”)  
第n行=IF(AND(An>=60,Bn>=60,Cn>=60),“合格”,“不合格”)  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdsZWk4ODA1MjY_size_16_color_FFFFFF_t_70 2]

# 五、绝对引用、相对引用、混合引用 #

## 5.1 概念 ##

绝对引用：公式中设定的单元格（如A1），通过上下或左右拉动方式复制该公式，单元格锁定，无法改变行或列编码  
相对引用：公式中设定的单元格（如A1），通过上下或左右拉动方式复制该公式，单元格未锁定，上下拉动改变行编码，左右拉动列编码  
混合引用：公式中设定的单元格（如A1），通过上下或左右拉动方式复制该公式，单元格部分锁定，如果设定列，则无法改变列编码，如 果锁定行，则无法改变行编码

## 5.2 引用格式和如何添加引用 ##

H2中公式：=IF(COUNTIF($E 2 : 2: 2:E$18,E2)>1,“重复”,"") 。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdsZWk4ODA1MjY_size_16_color_FFFFFF_t_70 3]

绝对引用： 列 编 码 列编码 列编码行编码  
H2中公式：=IF(COUNTIF($E 2 : 2: 2:E$18,E2)>1,“重复”,"") 。

相对引用：列编码行编码  
H2中公式：=IF(COUNTIF(E2:E18,E2)>1,“重复”,"") 。

混合引用： 列 编 码 行 编 码 或 列 编 码 列编码行编码 或 列编码 列编码行编码或列编码行编码  
H2中公式：=IF(COUNTIF(E$2:E 18 , E 2 ) > 1 , " 重 复 " , " " ) 。 或 H 2 中 公 式 ： = I F ( C O U N T I F ( 18,E2)>1,"重复","") 。 或 H2中公式：=IF(COUNTIF( 18,E2)>1,"重复","")。或H2中公式：=IF(COUNTIF(E2:$E18,E2)>1,“重复”,"") 。

**如何添加引用**  
选择需要添加引用的行编码或列编码，按F4键即可。

## 5.3 引用示例 ##

需求：H列3-18行，复制H2公式，保持E2:E18锁定不变。  
H2设置如下：  
IF(COUNTIF($E 2 : 2: 2:E$18,E2)>1,“重复”,"") 。  
或  
因为是向下拉动，故只需要锁定行编码即可  
H2中公式：=IF(COUNTIF(E$2:E 18 , E 2 ) > 1 , " 重 复 " , " " ) 。 H 7 复 制 公 式 如 下 ： = I F ( C O U N T I F ( 18,E2)>1,"重复","") 。 H7复制公式如下： =IF(COUNTIF( 18,E2)>1,"重复","")。H7复制公式如下：=IF(COUNTIF(E 2 : 2: 2:E$18,E7)>1,“重复”,"")  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdsZWk4ODA1MjY_size_16_color_FFFFFF_t_70 4]

[20200609192927521.jpg]: https://img-blog.csdnimg.cn/20200609192927521.jpg
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdsZWk4ODA1MjY_size_16_color_FFFFFF_t_70_pic_center]: https://img-blog.csdnimg.cn/20200823191842324.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdsZWk4ODA1MjY=,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdsZWk4ODA1MjY_size_16_color_FFFFFF_t_70_pic_center 1]: https://img-blog.csdnimg.cn/2020082319185279.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdsZWk4ODA1MjY=,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdsZWk4ODA1MjY_size_16_color_FFFFFF_t_70_pic_center 2]: https://img-blog.csdnimg.cn/20200823191903281.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdsZWk4ODA1MjY=,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdsZWk4ODA1MjY_size_16_color_FFFFFF_t_70_pic_center 3]: https://img-blog.csdnimg.cn/20200823191919413.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdsZWk4ODA1MjY=,size_16,color_FFFFFF,t_70#pic_center
[20200823191946613.jpg_pic_center]: https://img-blog.csdnimg.cn/20200823191946613.jpg#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdsZWk4ODA1MjY_size_16_color_FFFFFF_t_70_pic_center 4]: https://img-blog.csdnimg.cn/20200822124954921.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdsZWk4ODA1MjY=,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdsZWk4ODA1MjY_size_16_color_FFFFFF_t_70_pic_center 5]: https://img-blog.csdnimg.cn/20200822125016263.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdsZWk4ODA1MjY=,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdsZWk4ODA1MjY_size_16_color_FFFFFF_t_70_pic_center 6]: https://img-blog.csdnimg.cn/20200822125154436.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdsZWk4ODA1MjY=,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdsZWk4ODA1MjY_size_16_color_FFFFFF_t_70_pic_center 7]: https://img-blog.csdnimg.cn/20200822125238425.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdsZWk4ODA1MjY=,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdsZWk4ODA1MjY_size_16_color_FFFFFF_t_70_pic_center 8]: https://img-blog.csdnimg.cn/2020082212554445.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdsZWk4ODA1MjY=,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdsZWk4ODA1MjY_size_16_color_FFFFFF_t_70_pic_center 9]: https://img-blog.csdnimg.cn/20200822130139153.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdsZWk4ODA1MjY=,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdsZWk4ODA1MjY_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200615190257872.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdsZWk4ODA1MjY=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdsZWk4ODA1MjY_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200703144614640.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdsZWk4ODA1MjY=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdsZWk4ODA1MjY_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200706201905914.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdsZWk4ODA1MjY=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdsZWk4ODA1MjY_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20200710113950272.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdsZWk4ODA1MjY=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdsZWk4ODA1MjY_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20200710115519984.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdsZWk4ODA1MjY=,size_16,color_FFFFFF,t_70