EXCEL常用函数

本文档持续更新，最后修改日期：2025年11月9日
By 黑猫警长

注：本文中，源数据单元格一律为“A2”。

💡 新版 Excel 函数支持说明

随着 Microsoft 365 与 Excel 2021 的持续更新，Excel 已支持一系列现代函数，使公式更灵活高效：

  • LET：在公式中定义变量、复用结果，简化复杂公式结构
  • LAMBDA：可创建自定义函数逻辑，无需 VBA
  • 动态数组函数：如 FILTER、SORT、UNIQUE、TEXTJOIN 等，让数据处理更灵活

本文部分公式已应用 LET 函数进行优化。与旧式写法相比：

优势点 说明
性能更高 减少重复计算，提升处理速度
结构更清晰 使用命名变量让公式逻辑更直观
维护更方便 修改引用或逻辑时仅需调整变量定义

如果你使用 Microsoft 365 或 Excel 2021 及以上版本，推荐采用新版函数；
若仍在使用旧版 Excel（2016 及以前），可继续参考文中提供的传统公式以保持兼容性。

一、数据核验类函数

很多时候会让大家填写一些表格收集数据，但是在Excel中，当输入的数字超过11位时，数字就会以科学计数法显示，所以，如果不把单元格设置为数值格式，那数据就会只保留前十一位。为了发现此类错误，同时也发现长数字不小心输错的错误，使用以下函数。

1.1 身份证号校验函数

函数实现

本函数已兼容最新LET函数，只需将一开始的"A2"单元格更改为你所需要的单元格即可。

=LET(
    ID,A2,
    LENID,LEN(ID),
    SUMPRODUCT,
        MID(ID,1,1)*7+MID(ID,2,1)*9+MID(ID,3,1)*10+MID(ID,4,1)*5+
        MID(ID,5,1)*8+MID(ID,6,1)*4+MID(ID,7,1)*2+MID(ID,8,1)*1+
        MID(ID,9,1)*6+MID(ID,10,1)*3+MID(ID,11,1)*7+MID(ID,12,1)*9+
        MID(ID,13,1)*10+MID(ID,14,1)*5+MID(ID,15,1)*8+MID(ID,16,1)*4+
        MID(ID,17,1)*2,
    CHECKCODE,CHOOSE(MOD(SUMPRODUCT,11)+1,1,0,"X",9,8,7,6,5,4,3,2),
    IF(LENID=0,"空",
        IF(LENID=15,"老号",
            IF(LENID<>18,"位数不对",
                IF(CHECKCODE=IF(ISNUMBER(RIGHT(ID,1)*1),RIGHT(ID,1)*1,"X"),
                   "正确","错误")
            )
        )
    )
)
传统代码 

=IF(LEN(A2)=0,"空",
    IF(LEN(A2)=15,"老号",
        IF(LEN(A2)<>18,"位数不对",
            IF(CHOOSE/(
                MOD(SUM(
                    MID(A2,1,1)*7+MID(A2,2,1)*9+
                    MID(A2,3,1)*10+MID(A2,4,1)*5+
                    MID(A2,5,1)*8+MID(A2,6,1)*4+
                    MID(A2,7,1)*2+MID(A2,8,1)*1+
                    MID(A2,9,1)*6+MID(A2,10,1)*3+
                    MID(A2,11,1)*7+MID(A2,12,1)*9+
                    MID(A2,13,1)*10+MID(A2,14,1)*5+
                    MID(A2,15,1)*8+MID(A2,16,1)*4+
                    MID(A2,17,1)*2),11)+1,
                1,0,"X",9,8,7,6,5,4,3,2
            )=IF(ISNUMBER(RIGHT(A2,1)*1),RIGHT(A2,1)*1,"X"),
            "正确","错误"))))
原理说明（由ChatGPT生成）
点击展开

✅ 功能说明

该公式用于验证中国大陆18位身份证号码的有效性（基于国家标准的校验规则）。

🧮 校验规则概览：

  1. 空值检查：如果单元格为空，返回 "空"。
  2. 15位身份证判断：如果长度为15位，返回 "老号"。
  3. 长度错误：如果不是18位，返回 "位数不对"。
  4. 校验位判断（针对18位身份证）：
    • 前17位数字加权求和。
    • 对和取模 MOD(...,11)。
    • 通过 CHOOSE() 映射出校验码。
    • 与第18位（最后一位）比对，返回 "正确" 或 "错误"。

🔢 权重因子（固定）：
位置 1~17 对应权重如下：

🎯 校验码映射表（取模11后）：

模11余数 校验码
0 1
1 0
2 X
3 9
4 8
5 7
6 6
7 5
8 4
9 3
10 2

应用场景

检查excel表中，身份证号是否格式正确，避免输错或单元格格式未设为“文本”而导致丢后位。

1.2 银行卡号校验

=IF(A2="","空",
    IF(MOD(SUMPRODUCT(--(
        0&MID((0&MID(A2,ROW($1:$19),1))*
        2^MOD(ROW($1:$19)+MOD(LEN(A2),2),2),
        {1,2},1))),10),
    "错误","正确"))

说明：
第一层 IF：判断是否为空。
第二层 IF：使用 MOD(...,10) 和 SUMPRODUCT(...) 实现 Luhn 校验。
ROW($1:$19)：用于逐位提取数字（支持最多 19 位的卡号）。
2^MOD(...)：实现位权调整。
--(...)：将字符转为数字，供 SUMPRODUCT 运算。

二、信息提取类函数

2.1 身份证信息提取

很多时候明明有身份证号了，还要填什么出生日期或者性别。很麻烦。

出生日期提取

=--TEXT(MID(A2,7,8),"0-00-00")

格式设置建议：

格式类型 显示示例
短日期 1990-05-15
长日期 1990年5月15日

性别识别

=IF(MOD(MID(A2,17,1),2),"男","女")

2.2 （预留）

三、数字转换

3.1 小写数字转大写金额（人民币）

在财务报表或费用报销表中，经常需要在“合计金额”旁边显示中文大写金额，以防止篡改。
以下函数可将小写数字金额自动转换为中文大写金额（支持角、分、负数、零元整等情况）。

=IF(A2=0,"零元",
    IF(A2<0,
        TEXT(INT(ABS(A2)),"负[DBNum2]g/通用格式")&"元"&
        IF((INT(A2*10)-INT(A2)*10)=0,"",
            TEXT(INT(A2*10)-INT(A2)*10,"[DBNum2]")&"角")&
        IF((INT(A2*100)-INT(A2*10)*10)=0,"整",
            TEXT(INT(A2*10)-INT(A2*10),"[DBNum2]")&
            TEXT(INT(A2*100)-INT(A2*10)*10,"[DBNum2]")&"分"),
        TEXT(INT(A2),"[dbnum2]")&"元"&
        IF(INT(A2*10)-INT(A2)*10=0,"",
            TEXT(INT(A2*10)-INT(A2)*10,"[dbnum2]")&"角")&
        IF((INT(A2*100)-INT(A2*10)*10)=0,"整",
            TEXT(INT(A2*10)-INT(A2*10),"[DBNum2]")&
            TEXT(INT(A2*100)-INT(A2*10)*10,"[DBNum2]")&"分")
    )
)

示例

输入（A2） 输出结果
0 零元
123.45 壹佰贰拾叁元肆角伍分
88 捌拾捌元整
-56.7 负伍拾陆元柒角

版本更新记录

版本 更新内容 日期
v1.0 初版发布 2025-06-21
v1.1 增加银行卡号校验 2025年9月2日
v1.1 增加小写数字转大写 2025年11月9日
v1.2 部分函数进行LET函数适配 2025年11月9日
posted @ 2025-09-02 00:47  黑猫警长8283  阅读(70)  评论(0)    收藏  举报