Excel数据比对等常用公式函数及操作技巧3王玉祥

Excel数据比对等常用公式函数及操作技巧

王玉祥/文

说明：在单元格中输入公式函数时，必须在英文状态下。

【一】数据比对:

示例公式：=VLOOKUP(M4,[6月低保数据.xls]第1页!$D$2:$D$8552,1,FALSE)

操作技巧：

1、打开要“比对”的两个电子表格，选定要比对的“母件表格”（一般是数据多的文件，根据需要而定），在该表头下的第一行表后边单元格输入“=VLOOKUP”：如图：

双击“VLOOKUP”，单元格中弹出“=VLOOKUP（l）”这时，选中要比对的选项，如“身份证号码”中自上而下的第一个单元格M4，然后输入逗号“，”。

2、选“子件表格”（标准数据的表格），然后选择要比对的项，如身份证号码项，全选，（全选可以这样操作：点上方第一个单元格，按住“shift”），然后单击下边最后一个单元格，方便快捷；用拖动也可以，但费时间。

如图：

3、返回母件表格界面，在公式后面继续输入“,1,”,在界面上会自动出现：如图

选中“精确匹配”并双击。显示为右上图。

4、点公式栏上的“√”或者“Enter回车键”，就显示第一个比对数据。

5、选中第一个比对数，呈现“+”时，双击“+”或下拉，整列数据就可以比对出来了，出现“#N/A”的单元格，就说明母件和子件没有相同的比对数。操作完毕。

注意：在同一个电子表格文档中的工作表之间比对数据不准确，在筛选状态下比对数据也不准确。

【二】身份证号码排序法：根据身份证号码转化成岁数，然后使用排序进行升序或降序

示例公式：=YEAR(NOW())-MID(F2,7,4)

操作技巧：

1、在打开的Excel表格中，找到身份证号码的列，在身份证号码的列后面新插入一列。在新插入列上方对着身份证号码的第一个单元格输入公式：=YEAR(NOW())-MID(F2,7,4)，选中“F2”在F2呈阴影时，点击前列（身份证号码列）对应的单元格，按下Enter回车键或公式栏上的“√”，得到对应的第一个“岁数”，选中这一个“岁数”的单元格，单元格右下角呈“+”时，双击“+”，这时，整列的单元格就全部得出相应的岁数。

2、将岁数列进行排序，排序好后，，把岁数列删除，保存就可以了。

【二】把身份证号码或银行账号的部分数字变成“*”号

示例公式：=REPLACE(F2,7,8,"****")

操作技巧：

1、在该电子表格中“身份证号码”或“银行账号”后面新插入列，在这列的上边对应的第一个单元格输入公式：=REPLACE(F2,7,8,"****")，注明：F2指的是对应的第一个身份证号码或银行账号的单元格。

2、输入公式后，点回车键Enter或公式栏上的“√”，得到相对应的带“*”号的数据，选中该单元格，单元格右下角呈“+”时，双击“+”，这时，整列的单元格就全部得出相应的带“*”号的数据。

3、复制带“*”号的数据，粘贴为数值到“身份证号码”或“银行账号”的列，然后将新插入的那列删除，保存文档就可以啦。

【三】从身份证号码提取出生“年月日”

示例公式：=MID(F2,7,8)

操作技巧：

1、在该电子表格中“身份证号码”后面新插入列，在这列的上边对应的第一个单元格输入公式：=MID(F2,7,8)，注明：F2指的是对应的第一个身份证号码的单元格。

2、输入公式后，点回车键Enter或公式栏上的“√”，得到相对应的“年月日”，选中该单元格，单元格右下角呈“+”时，双击“+”，这时，整列的单元格就全部得出相应的“年月日”。

【四】从残疾证号提取“身份证号码”

示例公式：=LEFT(G2,18))

操作技巧：

1、在该电子表格中“残疾证号”后面新插入列，在这列的上边对应的第一个单元格输入公式：=LEFT(G2,18))，注明：G2指的是对应的第一个残疾证号的单元格。

2、输入公式后，点回车键Enter或公式栏上的“√”，得到相对应的“身份证号码”，选中该单元格，单元格右下角呈“+”时，双击“+”，这时，整列的单元格就全部得出相应的“身份证号码”。

【五】从身份证号码中识别男女性别并提取“男”、“女”字样示例公式：=IF(MOD(IF(LEN(D2)=15,MID(D2,15,1),MID(D2,17,1)),2)=1,"男","女")

操作技巧：

1、在该电子表格中“身份证号码”后面新插入列，在这列的上边对应的第一个单元格输入公式：=IF(MOD(IF(LEN(D2)=15,MID(D2,15,1),MID(D2,17,1)),2)=1,"男","女")注明：D2指的是对应的第一个身份证号码的单元格。

2、输入公式后，点回车键Enter或公式栏上的“√”，得到相对应的“男或女”，选中该单元格，单元格右下角呈“+”时，双击“+”，这时，整列的单元格就全部得出相应的“男或女”。

【六】日期格式批量转换：20100415转换成2010-04-15.

示例公式：=TEXT(A2,"0-00-00")

操作技巧：

1、在该电子表格中“日期”后面新插入列，在这列的上边对应的第一个单元格输入公式：=TEXT(A2,"0-00-00")

注明：A2指的是对应的第一个日期的单元格。

2、输入公式后，点回车键Enter或公式栏上的“√”，得到相对应的“2020-04-15”日期格式，选中该单元格，单元格右下角呈“+”时，双击“+”，这时，整列的单元格就全部得出相应的“2020-04-15”日期格式。

3、复制“2020-04-15”日期格式。粘贴为数值到“20200415”日期格式，然后将新插入的那列删除，保存文档就可以啦。

【七】日期格式批量转换：2010-04-15转换成20100415.

示例公式：=TEXT(B2,"emmdd")

操作技巧：

1、在该电子表格中“日期”后面新插入列，在这列的上边对应的第一个单元格输入公式：=TEXT(B2,"emmdd")

注明：B2指的是对应的第一个日期的单元格。

2、输入公式后，点回车键Enter或公式栏上的“√”，得到相对应的“20200415”日期格式，选中该单元格，单元格右下角呈“+”时，双击“+”，这时，整列的单元格就全部得出相应的“20200415”日期格式。

3、复制“20200415”日期格式。粘贴为数值到“2020-04-15”日期格式，然后将新插入的那列删除，保存文档就可以啦。

【七】EXCEL列里怎么批量加同样的文字作前缀。

示例公式：=IF(A1="","","ABC"&A1)