ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

搜索
EH技术汇-专业的职场技能充电站 妙哉!函数段子手趣味讲函数 Excel服务器-会Excel,做管理系统 效率神器,一键搞定繁琐工作
HR薪酬管理数字化实战 Excel 2021函数公式学习大典 Excel数据透视表实战秘技 打造核心竞争力的职场宝典
让更多数据处理,一键完成 数据工作者的案头书 免费直播课集锦 ExcelHome出品 - VBA代码宝免费下载
用ChatGPT与VBA一键搞定Excel WPS表格从入门到精通 Excel VBA经典代码实践指南
查看: 2820|回复: 3

[分享] 利用公式快速格式化零件号

[复制链接]

TA的精华主题

TA的得分主题

发表于 2018-9-4 08:23 | 显示全部楼层 |阅读模式
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
本帖最后由 数据先生 于 2018-9-4 08:32 编辑

0
案例背景

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

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

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

02
案例效果

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

2.jpg
其中还有一些特殊要求
①  零件号中没有i,o
②  所有的字母均为大写
③  零件号最短为9位,最长为14位
我们来看一下最终实现的效果,只需要将写好的公式复制进去即可
效果图片.gif

大家不要被公式的内容吓到了,虽然公式很长,但其实逻辑上并不复杂,下面我们就来一步一步的看看公式是如何完成的。(注:公式都是以第二行单元格为例)
03
清除零件号中非法空格


手动录入的零件号容易出现非法空格,所以我们要先将零件号中的非法空格去掉。
这里我们可以用substitute函数将空格替换掉


4.png

公式:
SUBSTITUTE(A2," ","")

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


根据大众的要求,零件号中所有字母均为大写,所以我们要将零件号中的小写字母替换成大写的。
这里我们可以用upper函数将小写字母替换成大写的

5.png

公式:
UPPER(B2)

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


根据大众的要求,零件号中不能存在I和O这两个字母(为了区分1和0),如果出现了要将他们替换成1和0.
这里我们依然可以用substitute函数将I和O替换掉
6.png

公式:
SUBSTITUTE(SUBSTITUTE(C2,"I","1"),"O","0")

06
判断是否为标准件


我们之前提到过,标准的零件号是以N_ _ 开头的,我们在这里要区分一下该零件号是不是标准的,如果是的话,要把它变成“N_ _”格式的
这里我们来用if函数判断一下左边第一个字母是不是N,如果是的话,就把它变成“N_ _”,如果不是就不变。
7.png


公式:
IF(LEFT(D2,1)="N",REPLACE(D2,1,1,"N  "),D2)
07
分隔零件号

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

1.车型代码
车型代码就是零件号的前三位
我们可以用mid函数来截取出零件号的前三位

8.png

公式:
MID(E2,1,3)

2.前中间号
前中间号就是零件号的第四位到第六位
我们依然可以用mid函数来截取出零件号的第四位到第六位

9.png

公式:
MID(E2,4,3)

3.后中间号
后中间号就是零件号的第七位到第九位
我们依然可以用mid函数来截取出零件号的第七位到第九位


10.png

公式:
MID(E2,7,3)











TA的精华主题

TA的得分主题

 楼主| 发表于 2018-9-12 08:39 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
新发了几个帖子,欢迎大家前来拍砖
从题库中随机抽取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技术论坛)

TA的精华主题

TA的得分主题

 楼主| 发表于 2018-9-4 08:35 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
4.改进码与颜色码
改进码与颜色码这里稍微复杂一些,需要根据零件号的长度来区分一下
如果零件号的长度是9,说明该零件号没有改进码与颜色码,就返回一个空
如果零件号的长度是10/11,说明该零件号有1/2位改进码而没有颜色码
如果零件号的长度是12,说明该零件号有3位颜色码而没有改进码
如果零件号的长度是13/14,说明该零件号有3位颜色码且有1/2位改进码
如果长度大于14或者小于9,表示该零件号不符合标准
11.png

公式:
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
生成标准零件号
我们将拆解完的零件号重新合在一起,就是我们想要的标准零件号了(注意每一项只见要用空格分开);如果是不符合标准的零件号,我们就返回太长/太短
12.png

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


09
合并公式


我们的公式已经写好了,只是如果日后每次用的时候都像现在这样一步一步的套公式未免有些麻烦,我们可以将上面的公式合并为一个大公式。
我们可以用逐层嵌套的方式来合并公式。例如:C2中的公式为“=UPPER(B2)”,我们将“B2”替换为B2单元格中的内容,就将B2与C2合到了一起,以此类推,最后合成一个大公式。
最后合并完成后我们只保留最重要的3列

13.png



评分

1

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2018-9-4 08:36 | 显示全部楼层
关注《鱼熊企管咨询》微信公众号后发送“大众零件号”即可下载相关文档。
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2024-11-24 19:14 , Processed in 0.044900 second(s), 16 queries , Gzip On, MemCache On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

沪公网安备 31011702000001号 沪ICP备11019229号-2

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:李志群律师

快速回复 返回顶部 返回列表