时间:来源: 网络作者: 未知点击: 次

Excel技巧：Excel如何批量匹配两张不同表格数据？（Vlookup函数法）

上一期我们给大家介绍利用设置“重复值”的条件格式来判断两个表的差异。不过也有小伙伴提出异议，觉得还要把其他表的数据复制到一张表好麻烦。有没有不移动表格也能匹配数据的方法？答案是肯定的，赶紧和大家分享一下。

场景：适合销售、HR、物流、运营、财务部等需要用Excel进行数据分析比较的办公人士。

问题：Excel如何批量匹配两张不同表格数据？

解答：利用Vlookup函数搞定。

Vlookup函数算是必须要掌握的企业级函数，利用该函数是否有对应的匹配数据，来判断两个表数据之间的差异。同样假设有两张表：表A和表B，长得都差不多，如何快速知道两张表的差异呢？

具体操作如下：Vlookup函数法：本例我们利用两个表的唯一关键字段（员工编号）来实现两个表的匹配。首先第一步，在表A中B2单元格中输入Vlookup以下函数公式。

=VLOOKUP(C2,表B!$C$2:$F$10,1,0)然后双击或下拉B2单元格右下角的数据填充柄，如果匹配成功则显示相同的员工编号，如果表A的数据在表B没有，则会显示错误，如下图B3和B9单元格显示。

需要注意的是，利用vlookup函数需要对两个表进行分别的对比匹配。所以使用起来相对比较麻烦，而且您需要对Vlookup函数使用非常熟练。

这里需要补充一点的是，vlookup函数匹配如果不成功，就会出现类似上图的“#N/A”的错误显示。为了让单元格的显示比较“圆满”。Vlookup函数通常与一个函数搭配使用，那个函数叫做iferror

也就是刚刚出错的单元格，变成0。Iferror函数的用法也很简单，如果Vlookup匹配成功则显示成功的值，如果不成功则显示为0。

总结：实际Vlookup函数不算是进行两表数据匹配最好的方法，但Vlookup函数绝对是进行表格数据匹配必须掌握的企业级函数。

该技巧Excel2010版本及以上有效。

• pandas提供了读取与存储关系型数据库数据的函数与方法。除了pandas库外还需要使用SQLAlchemy库建立对应数据库连接。SQLAlchemy配合相应数据库的python连接工具，使用create_engine函数，建立一个数据库连接。

接收string。表示读取的数据的表名或者SQL语句。无默认
接收数据库连接。表示数据库连接信息。无默认
接收int、sequence或者False。表示设定的列作为行名，如果是一个数列，则是多重索引。默认为None
接收list。表示读取数据的列名。默认为None

testdb数据库数据表清单为: 
使用read_sql函数+表格名称读取的订单详情表长度为: 3611

to_sql方法常用参数及其说明

接收string。代表数据库表名。无默认
接收数据库连接。无默认
接收fail、replace和append。fail表示如果表名存在，则不执行写入操作；replace表明如果存在，则将原数据库表删除，再重新创建，则不执行写入操作；append表示再原数据库表的基础上追加数据。默认为fail
接收boolean。表示是否将行索引作为数据传入数据库。默认为True
接收string或者sequence。代表是否引用索引名称，如果index参数为True，此参数为None，则使用默认名称。如果为多重索引，则必须使用sequence形式。默认为None。
接收dict。代表写入的数据类型（列名为key，数据格式为values）。默认为None

新增一个表格后testdb数据库数据表清单为： 

文本文件：由若干行字符构成的计算机文件，典型的顺序文件；CSV：用分隔符分隔的文件格式，文字分隔文件。

接收string。代表文件路径。无默认
接收int或sequence。表示将某列数据作为列名。默认为infer，表示自动识别
接收array。表示列名。默认为None
接收dict。代表写入的数据类型（列名为key，数据格式为values）。默认为None
接收c或者python。代表数据解析引擎。默认为c
接收int。表示读取前n行。默认为None

使用read_table读取的订单信息表的长度为： 945
使用read_csv读取的订单信息表的长度为： 945

分隔符为;时订单信息表为：
 9 # 使用gbk解析菜品订单信息表

to_csv函数常用参数及其说明

接收string。代表文件路径。无默认
接收string。代表分隔符。默认为‘,’
接收string。代表缺失值。默认为“”
接收list。代表写出的列名。默认为None
接收boolean。代表是否将列名写出。默认为True
接收boolean。代表是否将列名写出。默认为True
接收特定string。代表数据写入模式。默认为v
接收特定string。代表存储文件的编码格式。默认为None

订单信息表写入文本文件前目录内文件列表为：
订单信息表写入文本文件后目录内文件列表为：

read_excel函数的常见参数及其说明

接收string。表示文件路径。无默认
接收string、int。代表Excel表内数据的分表位置。默认为0
接收int或sequence。表示将某行数据作为列名，取值为int的时候，代表将该列作为列名。取值为sequence时，则代表多重列索引。默认为infer，表示自动识别
接收array。表示列名。默认为None
接收int、sequence或者False。表示索引列的位置，取值为sequence时代表多重索引。默认为None
接收dict。代表写入的数据类型（列名为key，数据格式为values）。默认为None

客户信息表长度为： 734

客户信息表写入excel文件前目录内文件列表为：
客户信息表写入excel文件后目录内文件列表为：

• 读取订单详情数据库数据

订单详情表1的长度为: 2779
订单详情表2的长度为: 3647
订单详情表3的长度为: 3611

• 读取订单信息csv数据

订单信息表的长度为： 945

• 读取客户信息Excel数据

客户信息表的长度为：734

• size：元素个数；ndim：维度数；shape：数据形状

订单详情表的元素个数为： 52801
订单详情表的维度数为： 2

订单详情表转置前形状为： (2779, 19)
订单详情表转置后形状为为： (19, 2779)

• 使用字典访问内部数据的方法访问DataFrame单列数据

订单详情表中的order_id的形状为: 

• 使用访问属性和的方法访问DataFrame单列数据

订单详情表中的dishes_name前5个元素为： 

订单详情表的1-6行元素为： 

订单详情表中前五行数据为 
订单详情表中后五个元素为： 

• loc使用方法：DataFrame.loc[行索引名称或条件，列索引名称]
• 使用loc和iloc实现单列切片

• 使用loc、iloc实现多列切片

• 使用loc、iloc实现花式切片

列位置为1和3行位置为3的数据为： 列位置为1和3行位置为2,3,4,5,6的数据为：

• 使用loc和iloc实现条件切片

• 使用iloc实现条件切片

• ix使用方法：DataFrame.ix[行索引的名称或位置或条件，列索引名称或位置]

列位置为5,行位置为2至6的数据为：

• 问题：ix使用报错，因现在版本不适用ix

drop方法的重要参数及其说明

接收string或array。代表删除的行或列的标签。无默认
接收0或1.代表操作的轴向。默认为0
接收int或者索引名。代表标签梭子啊级别。默认为None
接收boolean。代表操作是否对原数组生效。默认为False

数值型特征的描述性统计

Numpy中的描述性统计函数

订单详情表中amount（价格）的平均值为： 45.045

• 实现销量和价格的协方差矩阵计算

订单详情表中amount（价格）的平均值为： 45.045

订单详情表counts和amounts两列的描述性统计为：

类别型数据的描述性统计

pandas描述性统计方法

订单详情表dishes_name频数统计结果前10为：
五色糯米饭(七色) 58
芝士烩波士顿龙虾 55

• category类型特征的描述性统计

订单信息表dishes_name的描述统计结果为：

• 查看餐饮数据的大小和维度

订单详情表的维度为： 2
订单信息表的维度为： 2
客户信息表的维度为：2
订单详情表的元素个数为： 52801
订单信息表的元素个数为： 19845
客户信息表的元素个数为：27158

订单详情表counts和amounts两列的描述性统计为：
订单信息表order_id(订单编号)与dishes_name(菜品名称)的描述性统计结果为： 

• 剔除全为空值或者所有元素取值相同的列–定义一个函数去除全为空值的列和标准差为0的列

去除的列的数目为： 7

转换字符串时间为标准时间

最基础的时间类。表示某个时间点。绝大多数的场景中的时间数据都是Timestamp形式
表示单个时间跨度，或者某个时间段，例如某一天、某一小时等
表示不同单位的时间，例如1d、1.5h、3min、4s等，而非具体的某个时间段

接收string。表示时间的间隔频率。无默认
接收string。表示生成规则时间数据的起始点。无默认
表示需要生成的周期数目。无默认
接收string。表示生成规则时间数据的终结点。无默认
接收timezone。表示数据的时区。默认为None

• 转换字符串时间为标准时间

• Timestamp的最小时间和最大时间

• 提取datetime数据中的时间序列

dateIndex中的星期名称数据前5个为：

Timedelta类周期名称、对应单位及其说明

• 使用Timedelta实现时间数据的加运算

lock_time在加上一天前前5行数据为：
lock_time在加上一天前前5行数据为：

• 使用Timedelta实现时间数据的减运算

• 时间字符串转化为标准时间格式–订单信息表时间数据转换
  在订单信息表中存在两个时间特征，use_start_time和lock_time，分别表示了开始的时间和结算订单的时间。需要将这两个特征转化为标准的时间格式

• 提取菜品数据中的年月日和信息特征
  use_start_time和lock_time两个特征中的时间信息基本一直，故只需要提取其中一个特征的时间信息即可。

订单详情表中的前5条数据的年份信息为： [, , 2016]
订单详情表中的前5条数据的月份信息为： [8, 8, 8, 8, 8]
订单详情表中的前5条数据的日期信息为： [1, 1, 1, 1, 1]
订单详情表中的前5条数据的星期信息为： [0, 0, 0, 0, 0]

• 查看订单时间表时间统计信息
  通过求取最早时间和最晚时间的差值来计算整体订单的时间跨度，还能够将use_start_time和lock_time两个特征做加减运算，可以看出开始点餐至结束订单的时间。

可以发现最短时间和最长时间均为异常值，开始时间不可能在结束订单时间之后，点餐时间也不可能持续16天，可以考虑对这部分数据做合适的处理

使用groupby方法拆分数据

groupby方法的参数及其说明

接收list、string、mapping或generator。用于确定分组的依据。如果传入的时一个函数，则对索引进行计算并分组；如果传入的时一个字典或者Series，则字典或者Series的值用来作为分组依据；如果传入一个NumPy数组，则于数据的元素作为分组依据；如果传入的时字符串或者字符串列表，则使用这些字符串所代表的字段作为分组依据。无默认
接收int。表示操作的轴向，魔派任对列进行操作。默认为0
接收int或者索引名。代表标签所在级别。默认为None
接收boolean。表示聚合后的聚合标签是否以DataFrame索引形式输出。默认为True
接收boolean。表示是否对分组依据、分组标签进行排序。默认为True
接收boolean。表示是否显示分组标签的名称。默认为True
接收boolean。表示是否在允许的情况下返回数据进行降维。默认为False

groupby常用描述性统计方法及说明

计算分组的数目，包括缺失值
对每个分组中的组员进行标记，0~n-1

• 对菜品订单详情表依据订单编号进行分组

• groupby求均值、标准差、中位数

订单详情表分组后前5组每组的均值为：
订单详情表分组后前5组每组的标准差为：
订单详情表分组后前5组每组的大小为： 

使用agg方法聚合数据

接收list、dict、function。表示应用与每行或每列的函数。无默认
接收0或1。表示操作的轴向。默认为0

• 使用agg求出当前数据对应的统计量

订单详情表的菜品销量与售价的和与均值为：

• 使用agg分别求字段的不同统计量

订单详情表的菜品销量总和与售价的均值为：

• 使用agg方法求不同字段的不同数目统计量

print('菜品订单详情表的菜品销量总和与售价的总和与均值为：\n',detail.agg({

菜品订单详情表的菜品销量总和与售价的总和与均值为：

• 在agg方法中使用自定义函数

print('菜品订单详情表的菜品销量两倍总和为：','\n',

菜品订单详情表的菜品销量两倍总和为： 

• agg方法中使用的自定义函数含Numpy中的函数

print('订单详情表的菜品销量两倍总和为：\n',
print('订单详情表的菜品销量与售价的和的两倍为：\n',

订单详情表的菜品销量两倍总和为：
订单详情表的菜品销量与售价的和的两倍为：

• 使用agg方法做简单的聚合

print('订单详情表分组后前3组每组的均值为：\n', 
print('订单详情表分组后前3组每组的标准差为：\n', 

订单详情表分组后前3组每组的均值为：
订单详情表分组后前3组每组的标准差为：

• 使用agg方法对数据分组使用不同的聚合函数

print('订单详情分组前3组每组菜品总数和售价均值为：\n', 

订单详情分组前3组每组菜品总数和售价均值为：

使用apply方法聚合数据

apply方法的重要参数及其说明

接收functions。表示应用与每行或每列的函数。无默认
接收0或1。表示操作的轴向。默认为0
接收boolean。表示是否进行广播。默认为False
接收boolean。表示是否直接将ndarray对象传递给函数。默认为False
接收boolean或者None。表示返回值的格式。默认为None

• apply方法的基本用法

print('订单详情表的菜品销量与售价的均值为：\n',

订单详情表的菜品销量与售价的均值为：

• 使用apply方法进行聚合操作

订单详情表分组后前3组每组的均值为： 
订单详情表分组后前3组每组的标准差为： 

• 使用transform方法将销量和售价翻倍

print('订单详情表的菜品销量与售价的两倍为：\n',

订单详情表的菜品销量与售价的两倍为：

• 使用transform实现组内离差标准化

print('订单详情表分组后实现组内离差标准化后前五行为：\n', 

• 按照时间对菜品订单详情表进行拆分

订单详情表前5组每组的数目为：

• 使用agg方法计算单日菜品销售的平均单价和售价中位数

订单详情表前五组每日菜品均价为：
订单详情表前五组每日菜品售价中位数为：

• 使用apply方法统计单日菜品销售数目

订单详情表前五组每日菜品售出数目为：

接收DataFrame。表示创建表的数据。无默认
接收string。用于指定要聚合的数据字段名，默认使用全部数据。默认为None
接收boolean。表示汇总（Total）功能的开关，设置为True后，结果集中会出现名为“All”的行和列。默认为True
接收boolean。表示是否删掉全为NaN的列。默认为False

• 使用订单号作为透视表索引制作透视表

以order_id作为分组键创建的订单透视表为：

• 修改聚合函数后的透视表

print('以order_id作为分组键创建的订单销量与售价总和透视表为：\n',

以order_id作为分组键创建的订单销量与售价总和透视表为：

• 使用订单号和菜品名称作为索引的透视表

以order_id和dishes_name作为分组键创建的订单销量与售价总和透视表为：

• 指定菜品名称为列分组键的透视表

以order_id作为行分组键counts作为值创建的透视表前5行为：

• 对透视表中的缺失值进行填充

• 在透视表中添加汇总数据

使用crosstab函数创建交叉表

crosstab函数的常用参数及其说明

接收string或list。表示行索引键。无默认
接收string或list。表示列索引键。无默认
接收array。表示聚合数据。默认为None
表示行分组键名。无默认
表示列分组键名。无默认
接收function。表示聚合函数。默认为mean
接收boolean。默认为True。表示汇总（Total）功能的开关，设置为True后，结果集中会出现名为“All”的行和列。
接收boolean。表示是否删掉全为NaN的列。默认为False
接收boolean。表示是否对值进行标准化。默认为False

• 使用crosstab函数制作交叉表

• 创建单日菜品成交总额与总数均价透视表

print('订单详情表单日菜品成交总额与总数透视表前5行5列为：\n',

订单详情表单日菜品成交总额与总数透视表前5行5列为：

• 创建单个菜品单日成交总额透视表

print('订单详情表单日单个菜品成交总额交叉表后5行5列为：\n',

订单详情表单日单个菜品成交总额交叉表后5行5列为：
 dishes_name 黄尾袋鼠西拉子红葡萄酒 黄油曲奇饼干 黄花菜炒木耳 黑米恋上葡萄 All