需求1 匹配赋值
对比两列,值一致则获取第三列值到指定位置。如根据B
列及D
列匹配关系,将E
列值赋到C
列:
转换前:
A | B | C | D | E |
---|---|---|---|---|
1 | 甲 | 甲 | 202 | |
2 | 丙 | 乙 | 205 | |
3 | 乙 | 丙 | 307 | |
4 | 丁 | 丁 | 102 |
转换后:
A | B | C | D | E |
---|---|---|---|---|
1 | 甲 | 202 | 甲 | 202 |
2 | 丙 | 307 | 乙 | 205 |
3 | 乙 | 205 | 丙 | 307 |
4 | 丁 | 102 | 丁 | 102 |
实现:
vlookup(对比列, 参考区域, 值列, 匹配规则)
对比列:需要匹配的数据列
参考区域:包含参考列和值列的区域
值列:用于赋值的数据列在参考区域中顺序,起始列为1
匹配规则:FALSE
表示精确匹配
在C1单元格输入=vlookup(B1,$D$1:$E$4,2,FALSE)
,alt+enter
可实现上述需求。
注意:
参考列应位于参考区域中的第一列,否则无法正确返回匹配结果
需求2 拆分单元格填值
拆分合并后的单元格,同时将单元格内容填充至少拆分后的各个单元格。如将表格中B
列单元格拆分成三行,为每一行添加“大类名”信息:
转换前
A | B | C |
---|---|---|
1 | 大类名 | 中类1 |
2 | 中类2 | |
3 | 中类3 |
转换后
A | B | C |
---|---|---|
1 | 大类名 | 中类1 |
2 | 大类名 | 中类2 |
3 | 大类名 | 中类3 |
实现:
- 选中
B
列取消合并单元格 ctrl+g
,定位条件,空值- 在
B2
单元格输入=B1
,ctrl+enter
批量执行
需求3 重复单元格编码
某列单元格有重复值,需要为重复单元格额外编码以示区分。如A列为一级编码,存在重复值,需要对其进行编码,形成类似“0101”格式的二级编码以示区分:
处理前:
A | B |
---|---|
01 | |
01 | |
01 | |
01 | |
02 | |
02 |
处理后:
A | B |
---|---|
01 | 0101 |
01 | 0102 |
01 | 0103 |
01 | 0104 |
02 | 0201 |
02 | 0202 |
实现:
可以结合IF
函数、COUNTIF
函数实现功能。
IF
函数:IF
(条件,值1,值2),判断是否符合条件,符合返回值1,不符合返回值2
COUNTIF
函数:COUNTIF
(统计范围,值),统计范围内值得数量
可在B2
列输入下列公式实现上述需求:
=IF(
COUNTIF(A2:A1000,A2)=1,
A2&"01",
IF(
COUNTIF($A$1:A2,A2)<10,
A2&0&COUNTIF($A$1:A2,A2),
A2&COUNTIF($A$1:A2,A2)
)
)
对上述语句得解释:
- 若统计范围内,
A2
的值只有1个,则返回A2&"01"
,即A2
的编码连接01
字符串; - 若
A2
的值不为1
个,则返回第二个判断语句; - 第二个判断语句判断统计结果是否小于
10
(为了确认编码数量是否为两位),若小于10
,则返回A2&0&COUNTIF($A$1:A2,A2)
,即由A2、0、和A2以前的A2值统计量
构成编码,否则由A2、A2以前的A2值统计量
构成编码。
需求4 删除重复值
把某列中的重复值删除,每个重复值只保留一项
处理前:
A |
---|
01 |
01 |
01 |
01 |
02 |
02 |
处理后:
A |
---|
01 |
02 |
实现:
选中目标列
Excel——数据——数据工具——删除重复项
需求5 高亮重复值
实现:
Excel——样式——条件格式——突出显示单元格规则——重复值