excel数据关联同时更新(excel两张表格关联方法)

一本书教会你分分钟搞定数据分析!在使用Excel进行数据整理的过程中,一种常见的情况是,需要整合的信息分别处于不同的工作表甚至工作簿中。例如,销售表和价格表、工资表和员工信息表等。处理这类问题常用的方法或工具有公式、PowerQuery等。

excel数据关联同时更新(excel两张表格关联方法)

使用函数查询商品信息表

如图4-124所示,一个工作簿内存在多张工作表,分别为“商品销售数据表”和“价格表”。现在需要根据两张表之间商品编码的关系,将价格表的“商品名称”“价格”两个字段的数据写入商品销售数据表。操作步骤如下。步骤1:选取商品销售数据表,在E1和F1单元格分别输入字段名“商品名称”“单价(元)”。步骤2:在 E2 单元格输入以下VLOOKUP公式,并复制填充至 E2:F71 单元格区域。($B2,价格表!$A:$C,MATCH(E$1,价格表!$A$1:$C$1,0),0)

图 4-124 商品销售数据表和价格表$B2单元格的商品编码是查找值,价格表!$A:$C是查找范围。使用MATCH函数,查找E$1单元格中的字段名在价格表$A$1:$C$1中的位置,返回结果为2,表示VLOOKUP函数返回查找区域中第2列的内容。第四参数使用0,表示使用精确匹配的方式进行查找。公式返回结果如图4-125所示。

图 4-125 VLOOKUP 关联查询结果

更多使用函数与公式实现数据关联和匹配的技巧请参阅第 4 章和第 5 章内容。

excel数据关联同时更新(excel两张表格关联方法)

用Power Query数据关联与匹配

如图4-126所示,一个文件夹内存在多个工作簿,其中包含“商品销售数据表”“商品价格表”等。

图4-126一个文件夹内两个工作簿使用PowerQuery实现数据关联并匹配操作步骤如下。步骤1:新建一个工作簿并打开。在【数据】选项卡下依次单击【获取数据】下拉按钮→【自文件】→【从工作簿】命令,如图4-127所示。

图4-127从文件夹获取数据步骤2:在弹出的【导入数据】对话框中,选择目标工作簿,如“商品价格表.xlsx”,并单击【导入】按钮。在弹出的【导航器】对话框中,单击目标工作表,如“商品价格表”,单击【转换数据】按钮,如图4-128所示。

图4-128 导入商品价格表

图4-130 将查询合并为新查询

步骤5:在弹出的【合并】对话框中,将主要表设置为【商品销售数据表】,匹配表设置为【商品价格表】。联接种类保持默认选项【左外部(第一个中的所有行,第二个中的匹配行)】。先后单击主要表和匹配表的“商品编码”字段,也就是将该字段作为匹配列。单击【确定】按钮,如图4-131所示。

图 4-131 设置【合并】对话框

步骤 6:系统会自动生成一个名为“Merge1”的查询,在该查询的数据预览窗口,单击【商品价格表】字段右侧的扩展按钮,在弹出的选项菜单中取消选中【商品编码】复选框,取消选中【使用原始列名作为前缀】复选框,单击【确定】按钮,如图 4-132 所示。

图 4-132 扩展“商品价格表”字段

图 4-134 设置导入数据对话框

数据导入当前工作表后,如图4-135所示。

图 4-135 数据导入工作表

推荐阅读北京大学出版社Excel数据处理与分析应用大全

内容简介:

本书全面系统地介绍了以 Excel 为核心工具开展数据处理与数据分析的各项技术,深入揭示背后的概念原理,并配以大量典型实用的应用案例,帮助读者全面掌握数据分析工作的必备技能。全书分17 章,内容包括数据分析概述、数据采集、数据输入、数据整理、借助公式快速完成统计计算、借助数据透视表快速完成统计计算、基础统计分析、中心极限定理、假设检验、t 检验和卡方检验、方差分析、回归分析、时间序列分析、规划求解、Excel 数据表格美化、数据可视化和其他常用数据分析工具, 方便读者随时查阅。

本书适合各层次的数据分析从业人员,既可作为初学者的入门指南,又可作为中、高级用户的参考手册。书中大量的实例还适合读者直接在工作中借鉴。

本书当当网限时5折,数量有限,手慢无!

发表评论

登录后才能评论