您的位置:新葡亰496net > 新葡亰496net > 活该你加班,如何不加班完毕上百份Excel报表的联

活该你加班,如何不加班完毕上百份Excel报表的联

发布时间:2019-06-16 07:49编辑:新葡亰496net浏览(87)

    问题:如何将多个Excel文件汇总到一个文件当中?

    如何跳到表的最后一行?

    图片 1

    前言

    在日常工作过程中,我们有可能需要把多个Excel工作簿的内容汇总到一张Excel工作表中。如果通过复制粘贴的方式也并不是不可行,只是会浪费大量的时间和人力。在这样的情况下,我们一起讨论一种可以比较方便和快捷的进行多个工作簿合并成一个工作表的工作。这样我们可以分成两步去操作:首先:多个工作簿合并到一个“汇总工作簿”;其次:将这个“汇总工作簿”中的工作表进行合并,合并成一个工作表


    Excel办公软件可以说是我们每天在办公时都会用到的工具,有时候会需要将各种数据整理在表格中,但是有些刚入职场的小白或者是对于excel不熟悉的人来说,使用exce可能会有点困难,今天小编给大家分享几个比较实用的excel技巧。

    回答:这里介绍2种方法,一种是使用Excel的PowerQuery汇总,一种是使用Python汇总,下面我简单介绍一下实现过程,主要内容如下:

    1. 选中单元格, 当鼠标指示变成手的形状, 双击单元格下边框, 会跳到最后一条数据(如果其中有空数据, 会被阻断), 同理, 当双击单元格上边框 会跳到第一行数据.
    2. control/command ↑↓(上下箭头)

    几百个业务人员的数据反馈,几十家分公司的每日汇总,各渠道数据的汇总,部门内部数据的汇总等等,相信在很多没有完善CRM数据系统的公司,可能每天都都需要有人去做这样的事情。总之,非高效没意义的工作我们能自动化智能化的代替我们则会想方设法去代替。今天我们就用Excel中的PowerBI系列插件帮我们去完成这样的工作。

    第一步:多个工作簿合并到一个工作簿

    在由多个工作簿合并到一个工作表之前,我们先把多个工作簿合并到一个工作簿。

    1、新建一个工作薄,将其命名为合并后的名字,例如叫做:汇总工作簿。
    2、打开此工作簿:“汇总工作簿”
    3、在“汇总工作簿”下任一个工作表标签上点击右键,选择“查看代码”。

    图片 2

    查看代码.png

    4、在打开的VBA编辑窗口中粘贴以下代码:

    Sub 工作薄间工作表合并()
    Dim FileOpen
    Dim X As Integer
    Application.ScreenUpdating = False
    FileOpen = Application.GetOpenFilename(FileFilter:="Microsoft Excel文件(.xlsx),.xlsx", MultiSelect:=True, Title:="合并工作薄")
    X = 1
    While X <= UBound(FileOpen)
    Workbooks.Open Filename:=FileOpen(X)
    Sheets().Move After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
    X = X 1
    Wend
    ExitHandler:
    Application.ScreenUpdating = True
    Exit Sub
    errhadler:
    MsgBox Err.Description
    End Sub

    5、关闭该VBA编辑窗口
    6、在Excel中,开发工具---宏,选“工作薄间工作表合并”,然后“执行”。

    图片 3

    开发工具-宏.png

    图片 4

    执行“工作薄间工作表合并”.png

    7、在打开的对话窗口中,选择你要合并的多个工作薄。
    8、等待运行。而后就OK了。
    9、打开名为“汇总工作簿”的Excel文件,我们就能看到多个其他工作簿以多个Sheet页的形式合并到了这一个工作簿中。

    一、同时查看两个工作表

    为了更好的说明问题,在我这里新建了4个excel文件,放在一个文件夹下面,后面就是对这4个文件汇总:

    Workbook为工作簿对象,对应一个Excel文件.
    workbook 是一个excel文件(xxx.xls),worksheet是里面包含的表,

    所以,今天我们的学习目标就分为以下两方面:
    完成上百甚至上千上万份同格式的文件的汇总合并

    第二步:多个工作表合成一个工作表

    在“汇总工作簿”工作簿中,有很多个Sheet页,我们的最终目的是把这多个Sheet页合并到一个Sheet页的多行。其操作办法如下:
    1、在“汇总工作簿”中,新建一个sheet页。
    2、在新建的sheet标签上单击右键,选择“查看代码”
    3、在打开的VBA编辑窗口中粘贴以下代码:

    Sub 合并当前工作簿下的所有工作表()
    Application.ScreenUpdating = False
    For j = 1 To Sheets.Count
    If Sheets(j).Name <> ActiveSheet.Name Then
    X = Range("A65536").End(xlUp).Row 1
    Sheets(j).UsedRange.Copy Cells(X, 1)
    End If
    Next
    Range("B1").Select
    Application.ScreenUpdating = True
    MsgBox "当前工作簿下的全部工作表已经合并完毕!", vbInformation, "提示"
    End Sub

    4、关闭该VBA编辑窗口
    5、在Excel中,开发工具---宏,选“合并当前工作簿下的所有工作表”,然后“执行”。

    图片 5

    合并当前工作簿下的所有工作表.png

    6、等待运行,而后就OK了。


    如果要核对两个月的报表差异,而且这两个报表是在同一个工作簿中的,要如何处理呢?

    图片 6


    结合PowerPivot完成上百万数据的透视分析

    结语

    我们上面提到的是如何使用VBA来实现多个工作簿合成一个工作簿以及一个工作簿中的多个工作表按行合并。
    上面的方法是在知乎上摘录的,
    提姆葉提供了工作簿的合并;
    Anson提供了同一个工作簿中的工作表按行合并的方法。
    另外,我还在知乎上看到一个办法,就是雷公子提供的用PowerQuery动态汇总文件夹下的多个Excel文件。链接如下:https://zhuanlan.zhihu.com/p/26164792 。但是由于我个人并没有尝试过使用PowerQuery去进行合并,所以并不清楚这个效果如何。
    总之这里是一个解决方法的汇总,如果有和我一样有类似需求的小伙伴,可以尝试用这边的方法去解决这个问题。
    以上~

    其实很简单:依次单击视图→新建窗口,会出现两个Excel的窗口,并且在文件名后自动加上了序号1和2。

    每个文件的样本数据如下,包含一个季度3个月:

    多个 Sheet 合并为 一个 Sheet

    • 如何将一个 Excel 工作簿中的多个工作表合并成一个工作表?

    对上文中的代码做了小小的修改(下面的代码加了注释,不能运行)

    Sub mergeSheets() //sub 代表一个过程
    Application.ScreenUpdating = False// 不用显示宏的执行过程, 只显示最后的结果,最记得 把这个属性 设为  True
    For j = 1 To Sheets.Count //遍历 Sheets
    // 当 Sheet 的名字 不等于当前Sheet 名字时
    If Sheets(j).Name <> ActiveSheet.Name Then
    X = Range("A65536").End(xlUp).Row   1
    Sheets(j).UsedRange.Copy Cells(X, 1)
    End If
    Next
    Range("B1").Select
    Application.ScreenUpdating = True
    MsgBox "success finished", vbInformation, "alert"
    End Sub
    

    下面是可以直接用的代码

    Sub mergeSheets()
    Application.ScreenUpdating = False
    For j = 1 To Sheets.Count 
    If Sheets(j).Name <> ActiveSheet.Name Then
    X = Range("A65536").End(xlUp).Row   1
    Sheets(j).UsedRange.Copy Cells(X, 1)
    End If
    Next
    Range("B1").Select
    Application.ScreenUpdating = True
    MsgBox "success finished", vbInformation, "alert"
    End Sub
    

    如果觉得以上内容对你有所帮助,我们就接着往下看:
    PS:关于PowerQuery的简单介绍可以关注我们【简快Excel】查看历史文章,有详细介绍。
    合并上百份报表的操作思路:
    操作步骤:
    第一步 将要合并的文件放到我们待合并文件夹

    图片 7

    图片 8

    批量删除空行

    数据(Data)->筛选(Filter)
    选中某一列的列头, 筛选出空格,确定,右键删除


    图片 9

    此时再点击【视图】→ 全部重排,就可以同时显示两个窗口。然后在不同窗口中选择工作表,就可以同时查看不同工作表中的数据了:

    PowerQuery汇总

    1.首先,新建一个空的Excel文件,依次选择“数据”->“新建查询”->“从文件”->“从文件夹”,如下:

    图片 10

    2.接着在弹出的对话框中选择需要合并的Excel文件夹,如下:

    图片 11

    3.点击确定后,就会显示出该文件夹下面所有的Excel文件,如下,我们点击“Combine”进行合并:

    图片 12

    4.接着我们选中Sheet1进行数据合并,如下:

    图片 13

    点击确定后,就会进入查询编辑器中,预览我们合并的数据,如下:

    图片 14

    5.这里我们不需要第一列数据,所以直接选中这列,右键删除就行,最后选中菜单栏左上角的“Load & Closed”,即关闭并上载,就能合并数据,如下:

    图片 15

    合并后的数据如下,至此,我们就完成了将多个Excel文件汇总到一个Excel文件中:

    图片 16

    如何把excel中的一个工作表单独保存为excel文件

    • 如何把excel中的一个工作表单独保存为excel文件

    我们先放入10份文件测试,完成后再给大家演示100份文件的合并
    第二步 我们在文件夹外新建一个Excel文件汇总数据使用
    第三步 新建查询开始链接我们的源数据
    我们点击数据标签下边的【新建查询】→【从文件】→【从文件夹】,如下图:

    图片 17

    Python汇总

    这个也挺简单的,主要用到pandas这个数据处理库,可以快速读取excel文件,并合并,下面我简单介绍一下实现过程,感兴趣编程的朋友可以试一下,也就不到10行代码:

    1.安装pandas,这个直接在cmd窗口输入命令“pip install pandas”就行,如下:

    图片 18

    2.安装完成后,我们就可以合并Excel文件数据了,主要代码如下,很简单,ConcatExcel函数第一个参数dir_name是需要合并的Excel文件夹,第二个参数sheet_name是合并的Sheet页:

    图片 19

    点击运行程序,合并后的效果如下,与上面PowerQuery合并效果类似:

    图片 20

    至此,我们就完成了将多个Excel文件汇总到一个Excel文件中。总的来说,整个过程不难,PowerQuery和Python这2种方法都很简单,只要你熟悉一下流程,多动手练习几遍,很快就能掌握的,网上也有相关教程和资料,感兴趣的话,可以搜一下,希望以上分享的内容能对你有所帮助吧,也欢迎大家评论、留言。

    回答:谢诚邀:

    工作中常会遇到这样一些场景:一个工作簿内有无数个工作表,格式相同,一些列相同,需要把这些工作表的内容汇总到一个工作表里。M公司近期新开了一些店铺,为了调研店铺在这些商圈的吸引力,公司委派小白这个月每天都要提报这些店铺的进店人数,如下表:

    图片 21

    很快一个月过去了,小白也做了30份这样的表格。到月底时,市场部领导要求分析这些店铺每天进店人数的走势图。小白听到这个噩耗差点吐血。要做趋势图总得先把这些数据放在一起吧,30个这样的,我得重复这么多次复制粘贴。。。

    接下来小E和大家分享,遇到这样的情形该如何处理,我们选取3个工作表为例说明,把1日、2日、3日的数据合并放在汇总表里:

    图片 22

    活该你加班,如何不加班完毕上百份Excel报表的联结。1.点击A1单元格,点击数据选项卡——数据工具——合并计算;

    图片 23

    2.在引用位置区域选择1日工作表中内容,再点击添加;

    图片 24

    3.同上一步骤,把2日,3日的内容如是添加;

    图片 25

    4.勾选首行及最左列;

    图片 26

    5.补全A1 单元格标签。

    图片 27

    回答:选择PQ的话得高版本。现在很多办公人员用的都是低版本,所以用vba较多。再说多个Excel文件合并到一个文件内。这种情况每个人遇到的情况比较少。所以vba也不用。只需要手工移动或复制过去就可以。

    这类问题最好的解决办法就是以后把同类型的文件都直接写到一个Excel工作簿的工作表中。如果数据比较庞大,就写到Access中,以便方便数据汇总和分析的时候用。

    回答:建议搜搜悟空问答,类似问题不少。

    我知道的方法至少有三种:插件、vba和powerquery,个人推荐powerquery。

    回答:这个得利用VBA功能,

    回答:powerquery首选

    图片 28

    二、给Excel添加密码

    完成后点击浏览找到我们的文件夹路径点击确定,也可以复制我们的路径直接点击确定即可。

    如果咱们有比较重要的资料,肯定是不希望让其他人随便看到的,比如说客户信息啊、草榴网址啊等等这些。如何给文件加上一个密码呢?

    图片 29

    其实很简单:依次单击【文件】→【保护工作簿】→【用密码进行加密】,然后输入密码即可。

    然后我们点击编辑,即可进入PowerQuery界面。
    第四步 PowerQuery编辑获取数据
    从第三步的表里我们看到了这几列包含了我们的文件名,后缀,创建日期,修改日期,访问日期,以及他的文件夹路径等属性,就是没有直接看到我们表里的数据,其实我们表里的数据是被以二进制的方式存储在【Content】列了。我们仅仅需要一个函数就能获取其中的内容,我们具体操作下。
    1、添加列里,点击自定义列,输入=Excel.Workbook([Content],true)

    图片 30

    图片 31

    三、填充合并单元格

    注意:公式需要区分大写
    2、然后,我们删除我们不需要的干扰列,我们按住ctrl选择我们的表名称[Name]列和[自定义]列

    咱们说过,在工作表中尽量不使用合并单元格,否则就是给自己挖坑。那么对已有合并单元格的工作表,如何快速整理呢?

    图片 32

    其实很简单:只要选中数据区域,先取消合并单元格。

    3、展开自定义列,对Kind列筛选,让其等于Sheet,避免表格中有表格引用,或者区域名称导致汇总出现重复或者部分错误,这块我们就能看到我们具体的每个工作簿中工作表的名称,每个工作表的内容将被存储在了[Data]列,我们随便点击一个单元格便能看到里边的数据。

    然后按Ctrl G,定位空值。

    图片 33

    再输入等号,按方向键的上箭头,最后按Ctrl 回车即可。

    4、我们点击[Data]列的展开按钮,去掉使用原始列名作为前缀的勾选,点击确定。

    图片 34

    图片 35

    四、Excel转pdf

    5、我们就看到数据已经被合并好了,没有用的列我们删除即可:

    怎么把excel转换成pdf文件呢?

    图片 36

    其实将excel转换成pdf文件是很简单的,只要使用pdf转换工具就可以完成了。

    6、接着我们修改下数据类型即可。
    7、我们点击关闭并上载下拉功能,关闭并上载至,按钮,我们简单解释一下,上边的加载到表,就是加载到我们的EXCEL界面,数据量在100万以内的选择这个没有任何问题。下边的【将此数据添加到数据模型】勾选后可以将查询获取的汇总数据加载到PowerPivot中,PoewrPivot配合仅创建连接表(即不直接存储数据到Excel表格区域)

    直接进入迅捷pdf在线转换器页面,选择文档转换中的“excel转pdf”选项,点击“添加文件”选项,将要转换的excel文件添加进来,文件被添加完成之后,点击“开始转换”按钮,稍微等待一会之后文件就会被转换完成,将转换完成的文件下载保存即可。

    图片 37

    图片 38

    8、我们先选择加载到表,看下效果,我们看到10个表460行的数据就被我们加载完成了:

    五、星号*咋替换?

    图片 39

    要将单元格中的星号*替换为×,如果直接替换,就会把工作表中所有的内容都替换掉。如何实现精确替换呢?

    9、我们探索下100 的表格的合并效果以及我们PowerQuery的优势,我们复制原始数据10份,在待合并文件夹。

    其实很简单,按Ctrl H,调出替换对话框,在查找内容编辑框中输入:

    图片 40

    ~*

    10、我们需要重新做一遍刚才的工作吗?NO!NO!NO!我们只需要刷新下我么的数据源即可,以下方法学习一种即可
    刷新方法1:数据标签下边的全部刷新功能

    只要前面加上一个小尾巴,Excel就听话了。

    图片 41

    图片 42

    刷新方法2:在当前表格里右键,点击刷新

    以上内容就是小编今天给大家分享的方法,喜欢的小伙伴可以进行收藏使用哦。

    图片 43

    刷新方法3:在右侧查询界面,右键刷新,如果没有看到右侧的工作表查询的话,点击数据标签下的显示查询按钮即可

    图片 44

    11、点击刷新,我们等待不到2s钟的时间,100份报表就帮我们汇总完成了,有兴趣的同学也可以测试下1000份。

    图片 45

    关于PowerQuery文件夹的合并就介绍这么多,如果原始数据非Excel格式的话而是Csv的话,把Excel.Workbook替换为Csv.Document([Content],[Delimiter=”,”, Encoding=936]),其他操作都一样。
    接下来我们探索下如何利用这个方法实现千万级别的数据透视分析
    1、我们修改原始文件,中两个表的数据量到92万行,保存关闭

    图片 46

    2、直接刷新,我们看到表格会报错,因为我们可以看到右侧加载了185万行数,Excel文件里直接放不下了,好了我们看下怎么处理吧。

    图片 47

    3、我们在我们右侧我们的查询上点击右键,选择【加载到】,然后如下图所示选择,点击确定。

    图片 48

    4、我们点击PowerPivot下的管理按钮,查看下我们的数据,如果没有PowerPivot标签点击Ecxel的【COM加载项】勾选下即可,注意需要13或者16专业增强版以上才有此功能:

    图片 49

    图片 50

    5、我们185万的数据就被存储在Excel的PowerPivot中的,接下来我们点击插入透视表,选择使用此工作簿的数据模型为源数据即可。

    图片 51

    6、我们简单拖拽看下效果,一个185万行数据产生的透视表就被我们做出来了。

    图片 52

    总结:
    1、快速合并文件夹文件,我们看到我们仅需一个函数Excel.Workbook配合可视化的一些操作即可完成。2、后续只需要一键刷新即可完成汇总。3、通过Excel插件PowerPivot的配合我们完成了185万行数据的透视表的创建,对传统Excel分析的能力做了一个很大的突破。
    其实关于Excel中商务智能系列套件对我们未来的很多分析业务需求提供了更多的可能,本文所用示例文件可以加群198086726获取,更多文章欢迎关注【简快Excel】(PowerBIPro)如果本文对你有帮助,欢迎点赞转发。

    本文作者:雷公子,如需转载请联系授权

    本文由新葡亰496net发布于新葡亰496net,转载请注明出处:活该你加班,如何不加班完毕上百份Excel报表的联

    关键词: