整理数据时用到的EXCEL（WPS）公式整理

最新推荐文章于 2024-09-20 21:21:58 发布

OLIVIA088

最新推荐文章于 2024-09-20 21:21:58 发布

阅读量3.5k

点赞数 3

分类专栏： EXCEL 文章标签： VLOOKUP MID IFERROR MOD EXCEL(WPS)

本文链接：https://blog.csdn.net/OLIVIA088/article/details/83276628

版权

本文整理了在整理系统基础数据时遇到的EXCEL（WPS）公式，包括提取复姓、名、拼音及从身份证中提取出生年月、性别等信息的方法。如使用VLOOKUP结合IFERROR判断复姓，MID提取姓名部分，MOD确定性别等。

摘要由CSDN通过智能技术生成

最近在整理系统基础数据（机构和人员），并把数据导入数据库，整理过程中查询使用了一些公式，为了方便自己以后学习巩固也方便他人查阅，在此把我遇到的做一个整理汇总~~废话不多开始啦

一、提取姓名中的姓（包括复姓）

总体思路：提取当前需要处理的姓名的前两个字，在复姓表中找，如果找到那就是复姓返回复姓；如果没有找到那它就不是复姓，返回该姓名的第一个字

1、从网上下载现在使用的复姓（告诉程序复姓有哪些）

2、当前需要处理的数据截取前两个字，然后做查找和判断；详解请看下图，所用公式为：=IFERROR(VLOOKUP(LEFT(A2,2),复姓表!A:A,1,FALSE),LEFT(A2,1))

二、提取姓名中的名

在提取了姓之后，提取名就很简单啦~~

总体思路：先判断是单姓还是复姓。如果是复姓，从第三个字往后截取为名；如果是单姓，从第二个字往后截取为名。公式为：=MID(A2,2,5)，效果如下图

三、把姓名转化为拼音

1、在excel或wps中使用Alt+F11打开VB代码编辑窗口，点击插入-模块-打开vb编辑窗口。如下图

2、在面板上粘贴以下代码：

Function pinyin(p As String) As String

i = Asc(p)

Select Case i

Case -20319 To -20318: pinyin = "a "

Case -20317 To -20305: pinyin = "ai "

Case -20304 To -20296: pinyin = "an "

Case -20295 To -20293: pinyin = "ang "

Case -20292 To -20284: pinyin = "ao "

Case -20283 To -20266: pinyin = "ba "

Case -20265 To -20258: pinyin = "bai "

Case -20257 To -20243: pinyin = "ban "

Case -20242 To -20231: pinyin = "bang "

Case -20230 To -20052: pinyin = "bao "

Case -20051 To -20037: pinyin = "bei "

Case -20036 To -20033: pinyin = "ben "

Case -20032 To -20027: pinyin = "beng "

Case -20026 To -20003: pinyin = "bi "

Case -20002 To -19991: pinyin = "bian "

Case -19990 To -19987: pinyin = "biao "

Case -19986 To -19983: pinyin = "bie "

Case -19982 To -19977: pinyin = "bin "

Case -19976 To -19806: pinyin = "bing "

Case -19805 To -19785: pinyin = "bo "

Case -19784 To -19776: pinyin = "bu "

Case -19775 To -19775: pinyin = "ca "

Case -19774 To -19764: pinyin = "cai "

Case -19763 To -19757: pinyin = "can "

Case -19756 To -19752: pinyin = "cang "

Case -19751 To -19747: pinyin = "cao "

Case -19746 To -19742: pinyin = "ce "

Case -19741 To -19740: pinyin = "ceng "

Case -19739 To -19729: pinyin = "cha "

Case -19728 To -19726: pinyin = "chai "

Case -19725 To -19716: pinyin = "chan "

Case -19715 To -19541: pinyin = "chang "

Case -19540 To -19532: pinyin = "chao "

Case -19531 To -19526: pinyin = "che "

Case -19525 To -19516: pinyin = "chen "

Case -19515 To -19501: pinyin = "cheng "

Case -19500 To -19485: pinyin = "chi "

Case -19484 To -19480: pinyin = "chong "

Case -19479 To -19468: pinyin = "chou "

Case -19467 To -19290: pinyin = "chu "

Case -19289 To -19289: pinyin = "chuai "

Case -19288 To -19282: pinyin = "chuan "

Case -19281 To -19276: pinyin = "chuang "

Case -19275 To -19271: pinyin = "chui "

Case -19270 To -19264: pinyin = "chun "

Case -19263 To -19262: pinyin = "chuo "

Case -19261 To -19250: pinyin = "ci "

Case -19249 To -19244: pinyin = "cong "

Case -19243 To -19243: pinyin = "cou "

Case -19242 To -19239: pinyin = "cu "

Case -19238 To -19236: pinyin = "cuan "

Case -19235 To -19228: pinyin = "cui "

Case -19227 To -19225: pinyin = "cun "

Case -19224 To -19219: pinyin = "cuo "

Case -19218 To -19213: pinyin = "da "

Case -19212 To -19039: pinyin = "