2.4g和5g要不要合并_Excel&Python合并处理Excel工作簿、工作表

Ps：文末有百度云数据提取方式及Python源码

一、合并处理工作簿

所用数据截图

1、Python 合并处理工作簿

import pandas as pd
import os

"""
目标：合并多张表格，每个表中仅有一个sheet
"""

# 定义表格所在路径
path = r"E:知乎01 Excel_Python合并excel工作簿和工作表datatables"
os.chdir(path)

# 存储汇总的结果
result = pd.DataFrame()

# os.listdir 循环遍历文件名，批量循环按需处理文件
for name in os.listdir():
    df = pd.read_excel(name)
    # 计算销售额字段
    df['销售额'] = df['访客数'] * df['转化率'] * df['客单价']

    # 按品牌对细分行业销售额进行汇总
    df_sum = df.groupby('品牌')['销售额'].sum().reset_index()
    df_sum['类目'] = name.replace('.xlsx','')

    # 合并数据
    result = pd.concat([result, df_sum])

# 将结果写入到汇总表中（可选）
result.to_excel('汇总.xlsx',sheet_name='data')

# 对最终的结果按销售额进行排序
final = result.groupby('品牌')['销售额'].sum().reset_index().sort_values('销售额', ascending=False)
print(final.head())

# pandas自作主张把实际销售额变成了科学记数法形式来展示，要还原数值，需更改一下原始的设置
# 不显示科学计数法，保留小数点两位数
pd.set_option('display.float_format',lambda x:'%.2f' % x)
print(final.head())

最后一步打印截图

2、Excel 合并处理工作簿

目标：将多个Excel文件合并成一个文件里的Sheet。

实现方式：利用Excel 的 Power Query（本人使用的是 Excel 2016）；这是Office 2016自带的一个查询功能，是Powerquery中的一个小应用，如果是2013版本需要单独安装这个功能

为了实现同Python部分一样的需求，在正式导入之前，在每个Excel表里，增加「类目」字段，手动把字段填充一下。形如：

2.1 新建查询，从文件夹导入

新建一个Excel，点击「数据」选项卡下的「新建查询」，在弹出的快捷菜单中，选择「从文件」- 「从文件夹」，选择需要合并的工作簿所在的文件夹。

确定好文件夹之后，再点击 「确定」，Excel 会打开一个查询编辑器。

打码部分是时间信息

2.2 获取工作簿的内容

继续，点击「添加列」选项卡下的「添加自定义列」。在弹出「添加自定义列」的窗口内，输入「列名字」，尽量使用中文；另外还要输入「自定义列公式」。

截图中的公式：=Excel.Workbook([Content]) 无论是大小写还是标点符号，一个都不能错，Power Query对公式的检查非常严谨。

上一步确定后，我们可以看到表格中，多了一列「汇总」的字段，这就是我们表格的内容啦~

2.3 整理数据

这一步，我们需要「展开字段」，第一次展开之后会出现Name、Data、Item、Kind、Hidden。

点击确定后，选中「汇总.Data」列 -「开始」-「删除列」-「删除其他列」。

然后，再展开「汇总.Data」列。设置「将第一行用作标题」。此外，因为文件夹下的所有Excel文件都有标题，因此，汇总时，需要剔除表中多余的标题！

将第一行用作标题

剔除多余标题

最后，点击「开始」工具选项卡下的「关闭并上载」，这样就可以啦~

上载数据

至此，我们就将「多份Excel文档」合并成了一份工作表sheet，就完成了！

2.4 新增文档/数据刷新

如果我们往「文件夹」中新增文档，直接右键，选择「刷新」就会自动更新数据。

2.5 完成透视需求

自定义公式，整理数据

Excel透视结果 同 Python 处理结果对比，完全一致

二、合并处理工作表

以下部分，只展示合并数据，不再处理数据！

本部分数据，有3个sheet，每个sheet 20条数据

1、Python合并处理工作表

import pandas as pd
name = r'E:知乎01 Excel_Python合并excel工作簿和工作表datasheets户外服装&速干背心.xlsx'
df = pd.read_excel(name,None)

# 获取Excel 表中 sheet 名字
keys = list(df.keys())
print(keys)  # ['2018-10', '2018-11', '2018-12']

# 存储汇总的结果
res_contact = pd.DataFrame()

for i in keys:
    res = df[i]
    res_contact = pd.concat([res_contact,res])

# 将结果写入到汇总表中
res_contact.to_excel(r'E:知乎01 Excel_Python合并excel工作簿和工作表datasheetsres_contact.xlsx')

2、Excel 合并处理工作表

目标：将一个Excel文件的多个Sheet合并成一个Sheet。

实现方式：利用Excel 的 Power Query（本人使用的是 Excel 2016）；这是Office 2016自带的一个查询功能，是Powerquery中的一个小应用，如果是2013版本需要单独安装这个功能。

2.1 新建查询

新建一份工作簿，点击「数据」选项卡下的「新建查询-自文件-工作簿」，然后选择需要合并工作表的文档。

选中需要操作的文档之后，点击「导入」，就会得到一个如下「导航器」；

2.2 数据处理

导入数据之后，首先点击导航器中的「工作簿名字」，然后点击「转换数据」，部分版本可能会显示为「编辑数据」。

版本不同，按钮名称的展示，可能略微有所差异！

上一步完成后，Excel会弹出一个Power Query编辑器，数据区域中总共有5列数据，分别是：Name、Data、Item、Kind、Hidden。这里我们只需要保留Data这一列，其他的全部删除，可以右击Data，选择「删除其他列」，这样就只保留了这一列数据。

接下来，需要将Data数据展开，点击「Data旁边的按钮」，然后点击「确定」，将数据展开。继续，在工具栏中点击「将第一行用作标题」。

展开Data数据

将第一行用作标题

由于原表中每个Sheet中，都含有标题，因此，需要把多余的标题剔除出去！

剔除多余标题数据

至此，我们就将所有的数据整理完毕了，整理下思路：①删除无用数据；②设置标题行；总共就这2个步骤。

2.3 导出数据、刷新数据

点击「开始」工具选项卡下的「关闭并上载」，这样就可以啦！

导出数据

数据刷新方面，以后每次在表中新增数据，直接右键，选择「刷新」就会自动更新数据。

数据刷新

三、文中所用数据及Python源码

详见百度云网盘：

链接：https://pan.baidu.com/s/1jruXq6vVvHDmXEiy-mbsyQ

提取码：r38b

复制这段内容后打开百度网盘手机App，操作更方便哦~