利用公式快速格式化零件号

数据先生

0

案例背景

昨天，数据先生收到用户（昵称：去看海豚湾）留言：
本人是大众体系内的物料计划员一枚，第三方物流发过来的物料统计表都是手工输入的，画风如下：

我得花几个小时的时间逐步的把它们转换成大众标准零件号格式，才能与系统上的零件号匹配。
问题是每次接收报表都得做这些枯燥琐碎的破事，让我对工作感到了绝望。

数据先生认为只要是有规律反复重复的事情都可以用excel搞定。经过一段时间研究，问题得以完美解决。

02

案例效果

首先给大家看一下大众的零件号标准格式

其中还有一些特殊要求

①  零件号中没有i，o

②  所有的字母均为大写

③  零件号最短为9位，最长为14位

我们来看一下最终实现的效果，只需要将写好的公式复制进去即可

大家不要被公式的内容吓到了，虽然公式很长，但其实逻辑上并不复杂，下面我们就来一步一步的看看公式是如何完成的。（注：公式都是以第二行单元格为例）

03

清除零件号中非法空格

手动录入的零件号容易出现非法空格，所以我们要先将零件号中的非法空格去掉。

这里我们可以用substitute函数将空格替换掉

公式：

SUBSTITUTE(A2," ","")

04

将零件号中小写字母转大写

根据大众的要求，零件号中所有字母均为大写，所以我们要将零件号中的小写字母替换成大写的。

这里我们可以用upper函数将小写字母替换成大写的

公式：

UPPER(B2)

05

将零件号中的“IO”替换成“10”

根据大众的要求，零件号中不能存在I和O这两个字母（为了区分1和0），如果出现了要将他们替换成1和0.

这里我们依然可以用substitute函数将I和O替换掉

公式：

SUBSTITUTE(SUBSTITUTE(C2,"I","1"),"O","0")

06

判断是否为标准件

我们之前提到过，标准的零件号是以N_ _ 开头的，我们在这里要区分一下该零件号是不是标准的，如果是的话，要把它变成“N_ _”格式的

这里我们来用if函数判断一下左边第一个字母是不是N，如果是的话，就把它变成“N_ _”，如果不是就不变。

公式：

IF(LEFT(D2,1)="N",REPLACE(D2,1,1,"N  "),D2)

07

分隔零件号

接下来，我们要按照前面的大众零件号标准格式来将零件号分成4部分

1.车型代码

车型代码就是零件号的前三位

我们可以用mid函数来截取出零件号的前三位

公式：

MID(E2,1,3)

2.前中间号

前中间号就是零件号的第四位到第六位

我们依然可以用mid函数来截取出零件号的第四位到第六位

公式：

MID(E2,4,3)

3.后中间号

后中间号就是零件号的第七位到第九位

我们依然可以用mid函数来截取出零件号的第七位到第九位

公式：

MID(E2,7,3)

数据先生

新发了几个帖子，欢迎大家前来拍砖
从题库中随机抽取100道题生成试卷
http://club.excelhome.net/thread-1435180-1-1.html
(出处: ExcelHome技术论坛)

利用邮件合并功能批量生成参会证
http://club.excelhome.net/thread-1434167-1-1.html
(出处: ExcelHome技术论坛)


利用公式快速格式化零件号
http://club.excelhome.net/thread-1433962-1-1.html
(出处: ExcelHome技术论坛)


excel利用旋风图加强数据对比效果
http://club.excelhome.net/thread-1433542-1-1.html
(出处: ExcelHome技术论坛)

用excel生成适合手机查看的报表图片
http://club.excelhome.net/thread-1429695-1-1.html
(出处: ExcelHome技术论坛)

数据先生

4.改进码与颜色码

改进码与颜色码这里稍微复杂一些，需要根据零件号的长度来区分一下

如果零件号的长度是9，说明该零件号没有改进码与颜色码，就返回一个空

如果零件号的长度是10/11，说明该零件号有1/2位改进码而没有颜色码

如果零件号的长度是12，说明该零件号有3位颜色码而没有改进码

如果零件号的长度是13/14，说明该零件号有3位颜色码且有1/2位改进码

如果长度大于14或者小于9，表示该零件号不符合标准

公式：

IF(LEN(E2)=9,"  ",IF(LEN(E2)=10," "&MID(E2,10,1),IF(LEN(E2)=11,""&MID(E2,10,2),IF(LEN(E2)=12,"   "&MID(E2,10,3),IF(LEN(E2)=13,""&MID(E2,10,1)&" "&MID(E2,11,3),IF(LEN(E2)=14," "&MID(E2,10,2)&" "&MID(E2,12,3),"未知"))))))

08

生成标准零件号

我们将拆解完的零件号重新合在一起，就是我们想要的标准零件号了（注意每一项只见要用空格分开）；如果是不符合标准的零件号，我们就返回太长/太短

公式：

IF(LEN(E2)>14,"太长",IF(LEN(E2)<9,"太短",F2&" "&G2&""&H2&I2))

09

合并公式

我们的公式已经写好了，只是如果日后每次用的时候都像现在这样一步一步的套公式未免有些麻烦，我们可以将上面的公式合并为一个大公式。

我们可以用逐层嵌套的方式来合并公式。例如：C2中的公式为“=UPPER(B2)”，我们将“B2”替换为B2单元格中的内容，就将B2与C2合到了一起，以此类推，最后合成一个大公式。

最后合并完成后我们只保留最重要的3列

数据先生

关注《鱼熊企管咨询》微信公众号后发送“大众零件号”即可下载相关文档。