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)vlookup比对两个表格
操作技巧：
1、在该电子表格中需批量添加相同前缀的列后边新插入列，在这列的上边对应的第一个单元格输入公式：=IF(A1="","","ABC"&A1)
注明：A1指的是对应的第一个单元格。"ABC"是相同前缀字符，可以更换文字。
2、输入公式后，点回车键Enter或公式栏上的“√”，得到相对应的“带相同前缀的文字”选中该单元格，单元格右下角呈“+”时，双击“+”，这时，整列的单元格就全部得出相应的“带相同前缀的文字”。
3、复制“带相同前缀的文字”整列，粘贴为数值到原文字列，然后将新插入的那列删除，保存文档就可以啦。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。