一些实用的Excel函数组合

随笔杂谈 Office Excel

发布时间 : 2022-01-09 23:01

阅读 :

身份证相关函数组合
查找相关函数组合
其它一些常用函数组合
1. 混合文本数字提取数值
2. 统一小数点位数，末位如果是零则变 1

身份证相关函数组合

近期有个需求，大量的数据需要检查，为了减少失误或者及时发现错误，并且还易于自己以及其他人操作，我写了一些 Excel 函数组合。其中身份证校验相关操作最重要，以下拿身份证（18 位）校验举例：

身份证校验

校验码计算公式

设 $A_1,A_2...A_{17}$ 分别为身份证号码 1 到 17 位
(1) 计算加权求和 $A$ 并取余得到 $S_1$ ：

$A=7A_1+9A_2+10A_3+5A_4+8A_5+4A_6+2A_7+1A_8+6A_9+3A_{10}+7A_{11}+9A_{12}+\\10A_{13}+5A_{14}+8A_{15}+4A_{16}+2A_{17}\\$

$S_1=A \bmod 11$

(2) $S_1$ 对应身份证校验码 $S_2$ ：

$S_1= \begin{cases} 0 \implies S_2=1\\ 1 \implies S_2=0\\ 2 \implies S_2=\text{X}\\ 3 \implies S_2=9\\ 4 \implies S_2=8\\ 5 \implies S_2=7\\ 6 \implies S_2=6\\ 7 \implies S_2=5\\ 8 \implies S_2=4\\ 9 \implies S_2=3\\ 10 \implies S_2=2\\ \end{cases}$

EXCEL 函数组合

//常规写法
IF(
LOOKUP(
MOD(
MID(A1,1,1)*7+MID(A1,2,1)*9+MID(A1,3,1)*10+MID(A1,4,1)*5+MID(A1,5,1)*8+MID(A1,6,1)*4+MID(A1,7,1)*2+MID(A1,8,1)*1+MID(A1,9,1)*6+MID(A1,10,1)*3+MID(A1,11,1)*7+MID(A1,12,1)*9+MID(A1,13,1)*10+MID(A1,14,1)*5+MID(A1,15,1)*8+MID(A1,16,1)*4+MID(A1,17,1)*2,11
),
{0;1;2;3;4;5;6;7;8;9;10},
{"1";"0";"X";"9";"8";"7";"6";"5";"4";"3";"2"})=UPPER(MID(A1,18,1)),
"正确",
"错误"
)
//以上无视“X”位大小写，如需判断，IF判断嵌入EXACT函数

//精简写法
VLOOKUP(
MOD(
SUMPRODUCT(
MID(A1,
ROW(INDIRECT("1:17")),1)*{7;9;10;5;8;4;2;1;6;3;7;9;10;5;8;4;2}),11),
{0,1;1,0;2,"X";3,9;4,8;5,7;6,6;7,5;8,4;9,3;10,2},2,0)

身份证掩盖

=LEFT(A1, 6)&REPT("*",8)&RIGHT(A1,4)

按照固定长度关键字提取某单元格文本固定长度信息

一些人工采集的信息，每个人的记录风格或习惯还不太一样，比如 , 和 ， ，就算是同一个人不同时候也会打出不一样的标点符号，但大同小异，规律还是有的，而且长度还固定，就可以把其中的信息截取出来：

=MID(A1,SUM(IFERROR(SEARCH({"居民身份证为","身份证号码：","身份证号码:"},A1)+6,0)),18)

Ctrl+Shift+Enter

查找相关函数组合

A-Z 列范围内的解耦操作

为了省下复制还要注意粘贴位置和调整函数参数，把函数的参数通用化进行解耦

1、获取表头对应内容所在列号所在位置

MATCH("姓名",$1:$1,0)

2、把列号数字变成字母 (A-Z 列之内）

CHAR(MATCH("姓名",$1:$1,0)+64)

3、获取该单元格所在行对应 “姓名” 是谁

INDIRECT(CHAR(MATCH("姓名",$1:$1,0)+64)&ROW(),TRUE)

3.1、部分函数作为参数时会变成数组，此时需要搭配 INDEX

INDIRECT(INDEX(CHAR(MATCH("姓名",$1:$1,0)+64)&ROW(),1,1),TRUE)

单元格多关键字查找

=IF(SUM(IFERROR(FIND({"李四","张三"},H2),0))>0,"存在","不存在")

#如需搭配通配符使用SEARCH
=IF(SUM(IFERROR(SEARCH({"李?四","张?三"},H2),0))>0,"存在","不存在")

CTRL+SHIFT+ENTER 兼容数组执行

多条件查找

=VLOOKUP(H3&I3,IF({1,0},D:D&E:E,C:C),2,0)

CTRL+SHIFT+ENTER 兼容数组执行

一对多查询

假设要查找部门某职位有谁
新建辅助列 A

# H3是查找的职务，E是职务列，找到相同则+1
=(E2=$H$3)+A1

查找

=IFERROR(VLOOKUP(ROW(A1),A:C,3,0),"")

其它一些常用函数组合

混合文本数字提取数值

单元格内只能有中文

=MIDB(A1,SEARCHB("?",A1),2*LEN(A1)-LEN(A1))

统一小数点位数，末位如果是零则变 1

法一：数值计算

=IF(RIGHT((A1*1000000),1)="0",A1+"0.000001",A1)

法二：操作字符

=IF(RIGHT(TEXT(A1,"0.000000"),1)="0",LEFT(TEXT(A1,"0.000000"),LEN(TEXT(A1,"0.000000"))-1)&"1",TEXT(A1,"0.000000"))

转载请注明来源，欢迎对文章中的引用来源进行考证，欢迎指出任何有错误或不够清晰的表达。可以邮件至feicyblog@hotmail.com