巧用Excel 的vlookup()核验对比数据

巧用Excel核验对比数据

前言：

​ 因工作需要，最近有过一段时间核对数据的经验，主要用到的工具是利用 Excel 作为辅助利器，用过之后才知道Excel的功能如此强大，很对情况下数据E对比借助 Excel可以做到比用sql更方便高效。

场景：我手上有2份数据：数据A是一份本地库中的合同信息，数据B是上游合同数据，其中主键是合同编号，此时需要对比上游系统落地到我方本地库中的合同信息是否都正确，需要验证每个字段信息，揪出错误字段，让上游系统更正，或本地库更正。

这里记录总结下使用的一些技巧。关键利用 vlookup()函数

一、筛选、多字段组合排序、冻结首行

筛选、多字段组合排序、冻结首行是基础操作，在Excel的功能栏上就能设定。

（一）开始 -> 右侧筛选

（二）开始-> 右侧的自定义排序 【选中数据范围，可支持多字段排序】

（三）视图-> 冻结窗口 -> 冻结首行或冻结首列 【对应选择可以固定标题首行或首列】

二、函数使用（实用）

更多函数使用说明参考：微软Excel 函数（按字母顺序），这里只提供几个我使用比较多的几个记录

（一）vlookup函数

1.本地库合同数据A

2.上游系统合同数据B

vlookup() 函数的功能很强大
用于: 查找与引用：    在数组第一列中查找，然后在行之间移动以返回单元格的值
    
excel单元格内输入
=VLOOKUP (要查找的项, 要查找位置, 区域中包含要返回的值的列号, 返回近似匹配或精确匹配 指示为 1/TRUE 或 0/FALSE)

技巧：A数据，B数据分别，取出主键字段放在第一列作为辅助列，即可利用vlookup函数进行匹配找数据

1. A本地库合同数据

2. 上游系统合同数据

利用函数，从A数据找B数据中匹配的合同编号，查找后发现A数据第9行单元格没找到为#N/A。利用筛选框筛选出所有为#N/A的即使有差异的数据

（二）date lef，mid,right 函数

B上游系统合同信息表中的放款日期是字符串20211001 或者数字20211001，我方A本地库中存的放款时间是日期类型。
此时对比，有2种方式：1 .在导出数据时利用sql的函数，转成一致的导出对比；2. 在excel层面转换对比

这里教如何在excel中转换：详情如下图。

借助 Excel 单元格内 输入 =DATE(LEFT(F2,4),MID(F2,5,2),RIGHT(F2,2)) 即可以达到目的

LEFT(A,B) 截取A内容左侧B个字符
MID(A,B,C) 截取A内容第B个字符开始往右C个字符
RIGHT(A,B) 截取A内容右侧B个字符

（三）choose match 函数

合同表有个客户类别字段，存的是客户类别的代码，此时，可以利用Excel的choose函数进行映射
类似 语句if A2==1 then 集团参股，if A2 == 2 then 市场化

=CHOOSE(MATCH(A2,{"1","2"},),"集团参股","市场化")

（四）TEXT 函数

TEXT 函数可通过格式代码向 数字（日期实际上也是数字） 应用格式，进而更改 数字 的显示方式。 如果要按更可读的格式显示数字，或者将数字与文本或符号组合，它将非常有用。

注意: TEXT 函数会将数字转换为文本，这可能使其在以后的计算中难以引用。 最好将原始值保存在一个单元格中，然后在另一单元格中使用 TEXT 函数。 随后如果需要构建其他公式，请始终引用原始值，而不是 TEXT 函数结果。

=TEXT(Value you want to format, "Format code you want to apply")
例如：
    =TEXT(1234.567,"$#,##0.00")
货币带有 1 个千位分隔符和 2 个小数，如 $1,234.57。 请注意，Excel 将该值四舍五入到小数点后两位。
    
    =TEXT(TODAY(),"MM/DD/YY")   目前日期采用 YY/MM/DD 格式，如 12/03/14
    =TEXT(TODAY(),"DDDD")		 一周中的当天，如周日
    =TEXT(0.285,"0.0%")			 百分比，如 28.5%日期采用 YY/MM/DD 格式，如 12/03/14
    =TEXT(TODAY(),"DDDD")		 一周中的当天，如周日
    =TEXT(0.285,"0.0%")			 百分比，如 28.5%