ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

搜索
EH技术汇-专业的职场技能充电站 妙哉!函数段子手趣味讲函数 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
HR薪酬管理数字化实战 Excel 2019函数公式学习大典 不用妈妈陪默写神器 打造核心竞争力的职场宝典
300集Office 2010微视频教程 数据工作者的案头书 免费直播课集锦 ExcelHome出品 - VBA代码宝免费下载
你的Excel 2016实战技巧学习锦囊 WPS表格从入门到精通 Excel VBA经典代码实践指南
查看: 3047|回复: 6

[求助] 用函数公式效验银行卡号和存折账号

[复制链接]

TA的精华主题

TA的得分主题

发表于 2020-3-9 19:19 | 显示全部楼层 |阅读模式
因为收集账号过程中层级较多,人员对EXCLE基本不了解,经常出现账号输入错误现象,以及很多低级问题,如为了账号输入后不变成科学计数法,在账号前面、中间和后面加逗号、顿号和空格等,导致银行发放时错误太多。
账号数据中同时有银行卡号和存折账号,希望能用一个函数公式效验账号正确与否。
账号都为本地银行账号,银行卡号前6位bin码固定(有4组),存折账号前8位bin码固定(只有1组),银行卡号和存折账号前2位都能固定为1组,但还是希望能用更多数字来判定,减少错误几率。已知银行卡号为16位,通过论坛的Luhn算法验证可行;存折账号为17位,Luhn算法验证不可行,有的符合规则,有的不符合,无法作为效验条件。大致思路如下图:

银行卡号和存折账号效验思路

银行卡号和存折账号效验思路

希望能明确返回上图错误的原因便于更正。
另:1、希望各位大神能优化下银行卡号Luhn验证算法,附件中的算法是论坛中可以效验16-19位的,希望可以优化成只效验16位银行卡号,提高效率。
2、希望有知道效验17位存折账号算法的大神能提供效验思路,多谢了。
才接触EXCLE,前期刚整明白身份证效验算法,对这个账号效验确实无从下手,希望论坛的高手们不吝赐教,多谢多谢。


银行账号验证.zip

742.78 KB, 下载次数: 58

银行卡号和存折账号效验

TA的精华主题

TA的得分主题

发表于 2020-3-10 15:45 | 显示全部楼层
你的条件有问题吧 81010000开头第二个条件又说是错误的,第三个条件又说还需要细查

TA的精华主题

TA的得分主题

 楼主| 发表于 2020-3-10 16:22 | 显示全部楼层
gxhcswj 发表于 2020-3-10 15:45
你的条件有问题吧 81010000开头第二个条件又说是错误的,第三个条件又说还需要细查

条件没错啊。第二个条件是 81010000开头的就判定为错误的。相当于是通过账号开头判断出三个结果:1、银行卡号 2、存折账号 3、账号错误

TA的精华主题

TA的得分主题

 楼主| 发表于 2020-3-11 12:34 | 显示全部楼层
顶上去,别让帖子沉了。起先考虑的if嵌套,嵌套太多感觉自己都整蒙了。论坛上看了一下简化if嵌套的思路,也不得要领,期待高手解惑。

TA的精华主题

TA的得分主题

发表于 2020-3-11 14:20 | 显示全部楼层
本帖最后由 loirol 于 2020-3-11 14:22 编辑

G2
  1. =IF(--LEFT(A2,8)=81010000,IF(LEN(A2)=17,"存折位数正确","存折位数错误"),IF(OR(--LEFT(A2,6)=621013,--LEFT(A2,6)=621523,--LEFT(A2,6)=622412,--LEFT(A2,6)=623055),IF(LEN(A2=16),IF(MOD(SUMPRODUCT(CEILING(MOD(MID(A2,LEN(A2)-ROW(INDIRECT("1:""IENT(LEN(A2),2)))*2+1,1)*2,9.9),1))+SUMPRODUCT(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1)-SUMPRODUCT(MID(A2,LEN(A2)-ROW(INDIRECT("1:""IENT(LEN(A2),2)))*2+1,1)*1),10)=0,"卡号正确","卡号错误"),"卡号位数错误"),"账号错误"))
复制代码




你这里的所有账号都是正确的?

TA的精华主题

TA的得分主题

 楼主| 发表于 2020-3-11 14:38 | 显示全部楼层

是的,都是正确的账号。
你好,我刚才试了一下,在EXCLE里提示公式有问题呢,如图 20200311143710.png

TA的精华主题

TA的得分主题

发表于 2020-3-11 14:50 | 显示全部楼层
jlxiyou 发表于 2020-3-11 14:38
是的,都是正确的账号。
你好,我刚才试了一下,在EXCLE里提示公式有问题呢,如图

直接看附件吧

副本银行账号验证.7z

668.78 KB, 下载次数: 64

您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

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

GMT+8, 2024-3-19 13:34 , Processed in 0.059348 second(s), 10 queries , Gzip On, Redis On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

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

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

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