澳门新萄京官方网站-www.8455.com-澳门新萄京赌场网址

澳门新萄京官方网站:如何不加班完成上百份E

2019-09-22 作者:办公软件   |   浏览(96)

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

如何跳到表的最后一行?

澳门新萄京官方网站 1

前言

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


问题:平时的工作中,很多时候数据处理都离不开excel这个电子表格软件。用得好,能提高工作效率。用得不好,也就跟手工处理差不多。你的生活中,工作中见过最好或最差劲的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了。


最牛的方法当然是摸透excel的运作规律,从内部对excel进行改造,下来展示给你的excel用法,绝对让你惊掉下巴!

澳门新萄京官方网站 6


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

结语

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

澳门新萄京官方网站 7

每个文件的样本数据如下,包含一个季度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】查看历史文章,有详细介绍。
合并上百份报表的操作思路:
操作步骤:
第一步 将要合并的文件放到我们待合并文件夹

Excel文件确实可以通过压缩软件打包进压缩包中,但是把Excel自身变成压缩文件,多数人应该还没有听说过。

澳门新萄京官方网站 8

批量删除空行

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


澳门新萄京官方网站 9

01 Excel与压缩包迷之关系

其实,Office三姐妹在2007(或以上版本)中采用的后缀分别是xlsx、docx、pptx。

澳门新萄京官方网站 10

这些格式相比旧版本都多了个X,它们本质上是一个压缩包,这样的好处是存储相同容量的信息将占用较小的空间。

具体到Excel,构成Excel的所有源码文件都在这个压缩包中,因此通过将其后缀更改为.zip或.rar即可提取构成Excel的核心源码文件。

是不是感觉发现了微软的小秘密。

我们将Excel修改后缀为zip,解压出内部文件,看看都有什么?3个文件夹1个xml文件。

这里面是什么鬼东西,能怎么玩?让我们一步步扒光它……

澳门新萄京官方网站 11

小结Excel变成压缩包的原理图。

澳门新萄京官方网站 12

PowerQuery汇总

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

澳门新萄京官方网站 13

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

澳门新萄京官方网站 14

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

澳门新萄京官方网站 15

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

澳门新萄京官方网站 16

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

澳门新萄京官方网站 17

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

澳门新萄京官方网站 18

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

澳门新萄京官方网站 19

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

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

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

02 批量导出Excel中的图片

如果我们的Excel工作表中有很多图片,比如做的员工通讯录,里面含有每一个员工的头像照片,需要将他们批量导入了,一个一个另存为速度就太慢了。

将其转化为压缩包,然后解压,你会赫然发现,所有图片已经静静地在那里等你。

Excel等信息存放在解压后的xl文件夹中的media文件夹里,如图是获取Excel中所有图片的步骤。

澳门新萄京官方网站 20

Python汇总

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

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

澳门新萄京官方网站 21

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

澳门新萄京官方网站 22

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

澳门新萄京官方网站 23

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

回答:谢诚邀:

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

澳门新萄京官方网站 24

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

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

澳门新萄京官方网站 25

1.点击A1单元格,点击数据选项卡——数据工具——合并计算;

澳门新萄京官方网站 26

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

澳门新萄京官方网站 27

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

澳门新萄京官方网站 28

4.勾选首行及最左列;

澳门新萄京官方网站 29

5.补全A1 单元格标签。

澳门新萄京官方网站 30

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

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

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

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

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

回答:powerquery首选

澳门新萄京官方网站 31

03 破解工作表加密

如图第二张表「销售记录」是使用密码保护的工作表。可是自己设置的密码却忘记了,该怎么办?

澳门新萄京官方网站 32

这就叫做搬起石头砸自己的脚吧。

①首先修改Excel的后缀名为「.zip」或者「.rar」,是压缩文件的格式就行。

注意:Excel会提示你修改后缀名之后会导致文件不可用,不要理它,点击「是」。

澳门新萄京官方网站 33

②用压缩软件打开刚才创建的压缩包,从压缩包中\xl\worksheets\路径下找到加密的工作表。因为我们只有sheet2为加密工作表,所以打开这个工作表即可。

通常,为了防止乱搞,把原始Excel文件搞乱,我建议大家把此文件复制到桌面之后再进行下一步操作。

澳门新萄京官方网站 34

③假设你已经将压缩包中的Sheet2复制到桌面了,那么用打开它, ,输入“pro”查找找到加密代码,找到之后将<>中的那一坨连同<>符号一起删掉,然后保存。

<sheetProtection algorithmName="SHA-512" hashValue="0botjAbUl3c8nnRthv/9xqEJw1FA6ErQJGpCGWzty5mQjyr89b11v9ffpnHIJOm3mk9Rv3YgnF10xQ3FEjTbvQ==" saltValue="5wf6E/o5M RHJTelz8A5hg==" spinCount="100000" sheet="1" objects="1" scenarios="1"/>

澳门新萄京官方网站 35

④将桌面的上已经删除密码保护的sheet2直接拖到压缩的文件的\xl\worksheets\路径下,替换到原来的sheet2。

澳门新萄京官方网站 36

⑤把zip文件改回.xlsx文件,然后大摇大摆去修改工作表吧,因为excel工作表保护密码消失了。

澳门新萄京官方网站 37

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

04 批量修改工作表名称

一个工作簿中存放了一整年的销售记录表,有一个汇总表以及1-12月的销售明细。现在想把工作表名称中“2008年”字样统一改为“2010年”,同样使用压缩文件的方式可以快速搞定。

澳门新萄京官方网站 38

①将Excel文件的扩展名修改为.zip

②双击zip压缩包→xl文件夹,找到workbook.xml文件,并将workbook.xml复制到桌面(注意不要解压压缩文件)。

澳门新萄京官方网站 39

③以记事本的方式打开桌面上的workbook.xml文件,单击菜单命令“编辑”→“替代”,在弹出的“替换”对话框中,“查找内容”文本框输入:2008年,“替换为”文本框输入:2010年,单击“全部替换“按钮,关闭”替换“对话框;

澳门新萄京官方网站 40

④保存文件,并将其粘贴回压缩文件夹中,会提示“此位置已包含同名文件”,点击复制与替换。

澳门新萄京官方网站 41

⑤将zip文件扩展名再改回原来的.xlsx,然后打开此Excel文件,发现所有的工作表名称都已经改变了。

澳门新萄京官方网站 42

澳门新萄京官方网站 43

05 批量修改批注

通过摸索,星爷发现,Excel转化成压缩包之后,其中xl文件夹是放置Excel中各种元素的地方,比如Excel中插入了图片,在xl文件夹中就会生成一个media文件夹;在Excel单元格中插入了批注,xl文件夹中就会生成一个comments1.xml文件,用来存放批注。

因此,在Excel文件中难以批量操作的“批注”,就可以利用压缩包方法解决。

比如批量修改批注。

双击zip压缩文件,双击打开 xl 文件夹,找到名为comments1.xml的文件,将其复制到桌面。

澳门新萄京官方网站 44

右键单击comments1.xml文件,在右键菜单中依次单击——。在记事本中打开后,所有Excel中批注的内容都在这个文件中了,在这里我们可以方便的使用查找替换进行批量性该,比如:删掉所有批注中的“学费”两字。

如图可以采用替换的方式,将学费替换为空值。

澳门新萄京官方网站 45

完成之后保存comments1.xml文件,并将其粘贴回压缩文件包中。

最后zip文件扩展名再改回原来的.xlsx,然后打开此Excel文件,工作表中所有地方的批注都进行了相应的改变。

知道了这个原理之后,我们应该还能解锁更多的玩法,等着你去发现。

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

今日互动

关于Excel与压缩包,你还有哪些玩法?

·The End·

作者:安伟星,微软Office认证大师,领英中国专栏作者,《玩转职场Excel》图书作者

回答:

17个数据透视表和SQL实用动画:

1、多角度分析数据

澳门新萄京官方网站 46

2、更改值的汇总依据

澳门新萄京官方网站 47

3、排序让数据更加直观

澳门新萄京官方网站 48

4、原来手工也能排序

澳门新萄京官方网站 49

5、对销售额进行排名

澳门新萄京官方网站 50

6、筛选销售额前5名的客户

澳门新萄京官方网站 51

7、让更改的数据源随时刷新

澳门新萄京官方网站 52

8、插入表格,让数据源“动”起来

澳门新萄京官方网站 53

9、日期组合真奇妙

澳门新萄京官方网站 54

10、手工组合实现客户分级

澳门新萄京官方网站 55

11、善借辅助列,实现客户实际销售额分析

澳门新萄京官方网站 56

12、利用数据透视图让分析更直观

澳门新萄京官方网站 57

13、查询所有记录

澳门新萄京官方网站 58

14、查询不重复客户

澳门新萄京官方网站 59

15、查询符合条件的客户

澳门新萄京官方网站 60

16、统计产品购买金额

澳门新萄京官方网站 61

17、统计批发跟零售金额

澳门新萄京官方网站 62

回答:

最开始使用Excel的时候,什么都不懂。有谁不是从最开始的手动输入进阶过来的?

所以我刚上班的时候用Excel也是low爆了,快捷键只知道一个Ctrl C和Ctrl V。尤其是在录入身份证信息的时候,不管怎么弄都是无法显示完整的数字,无奈只能求助隔壁的小姐姐。

澳门新萄京官方网站 63

现在终于知道身份证号要怎么设置,才能够完整显示了。这里也给大家附上解决方案:

①先选中单元格,再右键点击“设置单元格格式”,选择“文本”。

澳门新萄京官方网站 64

②然后就可以输入身份证号,并在数字前插入英文单引号,再按下回车键即可。

澳门新萄京官方网站 65

不过等遇到的问题多了,百度查找答案后,也就慢慢积累了一些Excel技巧。例如以下这几个我就觉得挺好用!

1、ctrl E(快速填充)

拆分单元格信息

澳门新萄京官方网站 66

手机号码分段显示和加密

澳门新萄京官方网站 67

手机号码分段显示和加密

澳门新萄京官方网站 68

快速提取身份证号码中的生日

澳门新萄京官方网站 69

2、[F4]键

轻松插入多个空白行

澳门新萄京官方网站 70

高效合并多个单元格

澳门新萄京官方网站 71

为多个单元格设置填充颜色

澳门新萄京官方网站 72

许多小伙伴都认为Excel函数公式很高级,会用Excel函数公式就是牛。虽然我觉得Excel技巧里没有最牛,只有更牛。但是不得不承认,Excel函数公式用对了确实可以大幅度提高工作效率。例如以下这几个函数格式:

1、提取出生年月

=TEXT(MID(C3,7,8),"0-00-00")

澳门新萄京官方网站 73

2、计算及格人数

=COUNTIF(B2:B13,">=60")

澳门新萄京官方网站 74

最后再给大家一个经验之谈:如果想要学习怎么用Excel,光看书看教程都是纸上谈兵,尤其是公式基本上看过不用的话很快就忘记。建议是遇到问题后,先百度搜一下,然后边学边用,这样可以加深学习印象哦。

澳门新萄京官方网站 75

回答:

最牛的是用Excel做商业建模,比如投资决策模型,整本书看完完全不能理解的节奏。这个时候excel工具已经是其次了,关键是模型背后的思考和解决问题的方式。

次一点的是Power Query 和VBA ,前者用在数据处理,后者重在应对复杂业务逻辑。

再次一点是超级链接和函数,比如跨文件取数,跨表页数据更新。

更次一点大概就是数据透视和图表展示。

LOW的用法是一个单元格一个单元格敲公式,改公式,不知道用替换。

更LOW的用法是,看到excel 里数字前的绿色三角一个个点掉转换成数字,

最最LOW的是ctrl c 和 ctrl v ,重复操作超过五次,就应该考虑用便捷方式了。

自从接触财务以后,反正我已经信了EXCEL是最好的BI工具。

回答:

澳门新萄京官方网站 76

最牛

1 小试刘刀。老师在数理统计上用Excel的函数做概率实验,只是几个函数,但是对于普通的职场小白这可是神技能啊。

澳门新萄京官方网站 77

2 大神吧

函数 vba,分分钟get前面那个当做计算器用过的人。

澳门新萄京官方网站 78

3 骨灰级

绘画用什么,ps,不不不,国外大神告诉你用什么,吓得我瑟瑟发抖。

澳门新萄京官方网站 79

澳门新萄京官方网站 80

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

菜鸟

1.哎,那个表格在哪里啊!!!!!!

2.表格就是放东西的,玩什么玩。。

澳门新萄京官方网站 81

回答:

Excel中最牛的用法,一定那些可以轻松用于解决实际工作中的大问题的功能,而不是那些只是看起来很牛,而学不会或用不来的东西,因为那个跟你没有关系,或者说对你来说没有意义。因此,我现在非常推荐大家在学好Excel基础功能的情况下,加紧对Excel2016新功能(Excel2010或Excel2013可到微软官方下载相应的插件)的学习,不仅超级强大,而且十分简单易学。举例如下:

澳门新萄京官方网站 82

Power Query秒解顾客最后消费记录神难题

小勤:大海,为提高顾客服务体验,公司现在要求除了将顾客的所有消费记录提出来外,还要求将顾客的最后一次消费记录提取出来,发给现场的销售人员,方便他们提供更好的服务。

大海:鹅……厉害了噢。开始这么高级的服务了。

小勤:是啊,但我就惨了,每天数据都在增加,每天出报表……

大海:这个问题啊,以前有些大神专门研究过,还写过不少神公式,给你看看:

澳门新萄京官方网站 83

还有最后消费金额的公式:

澳门新萄京官方网站 84

小勤:晕啊。这么复杂。

大海:真说起来,这个公式的复杂程度其实不算个大问题,更麻烦的是这个公式涉及数据量大的时候,计算起来会很卡。

小勤:那怎么办好呢?

大海:现在有PowerQuery,秒解,简单到没朋友,看啊。

Step-1:获取数据

澳门新萄京官方网站 85

Step-2:反转行

澳门新萄京官方网站 86

Step-3:对姓名列删除重复项

澳门新萄京官方网站 87

Step-4:再次反转行(如不需要保持原数据顺序,此步骤可省略)

澳门新萄京官方网站 88

Step-5:数据上载

澳门新萄京官方网站 89

小勤:666!只要点2下就好了!

大海:你说呢?以后新订单数据进来就刷一下。

小勤:嗯。这太好了,要不能这样的话,公司要真靠数据来提升服务水平可太难了。

大海:的确,服务的提升必须要有最新的数据来保证,这就是快速(敏捷)数据分析的价值啊。

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

Power Query:2步搞定数据转换神难题

小勤:大海,刚有个同事在问我这种情况怎么办!公式怎么写啊?

澳门新萄京官方网站 90

大海:为什么又要公式?

小勤:还有大批量数据都需要做这样的迁移啊。公式不是更加自动一点吗?

大海:PowerQuery不是更加自动吗?第一反应为什么不是用Power Query?

小勤:鹅……对哦!Power Query是可以整合、转换数据,并可以自动刷新的。

大海:嗯。就这个问题来说,去掉数据获取(导入)、数据上载头尾两个简单步骤后,用Power Query只要2步就搞定了:逆透视后再透视。

Step-0:数据获取

澳门新萄京官方网站 91

Step-1:逆透视

澳门新萄京官方网站 92

Step-2:透视

澳门新萄京官方网站 93

最后,数据上载

澳门新萄京官方网站 94

小勤:真是太简单了!那如果写公式的话呢?

大海:公式的话可以看作多条件求和或多行列数据匹配的情况考虑,比如用sumifs、sumproduct、if index match等组合的方式,但都比较复杂,而且有个前提:业务经理、月份、存货分类等行列标题的内容要先生成,即结果表的框架先生成,只用公式读取其中的数值内容。如果要连行列标题(业务经理、月份、存货分类)都要靠公式整出来的话,基本没救。

小勤:好吧。有合适的工具,掌握有效的技能,真是事半功倍啊!

澳门新萄京官方网站 95

更多Power Query精彩实战案例

可以用Power Query来轻松迅速解决的Excel问题举不胜举,我曾经分享过一系列的实战案例,供参考:

澳门新萄京官方网站 96


澳门新萄京官方网站 97

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

我是大海,微软认证Excel专家,企业签约Power BI顾问

澳门新萄京官方网站 98

让我们一起学习,共同进步!

回答:

Excel简直就是神器,实在太强大了,不仅可用来办公,还能制作游戏,甚至是进行画作创作。下边是无意中发现的,一个日本老人用Excel来画画,其所创作的画作多为日式传统风格的山水风景图。至于为何选择Excel表格类来进行创作,老人表示“成本是首要原因,专业的绘画应用通常价格昂贵并且平台门槛较高,而Excel只需要一台电脑即可。此外,它简易的操作可以让上色更为顺手”。下图为老人的画作:澳门新萄京官方网站 99澳门新萄京官方网站 100澳门新萄京官方网站 101澳门新萄京官方网站 102

回答:

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

先说一下我见过的最low的Excel的用法。

学校收《报考指南》书费和体检费,我收起了学生的费用去交给负责这件事的副主任。我拿着钱进屋看到副主任的桌子上,凌乱的放着用纸包起来的钱。眼睛盯着电脑,手里拿着手机,走近一看,他居然在用手机上的计算器算各班的应交费用。我当时的表情是这样的,

澳门新萄京官方网站 103

能把Excel用到这种程度我也真是服服的了。

澳门新萄京官方网站 104

至于最牛逼的,我没有见过,我觉得我用的就比较牛逼。

我负责学校的成绩分析,成绩出来后我可以在半个小时内,生成全校一千多学生,一百位老师的,各种成绩数据。包含每位老师的单进线、双进线、完成率、折合完成率、综合完成率,老师在全校的排名,在组内的排名,在班内的排名,各个班级的排名,各个教研组的排名。

我还可以把,全年级的课程表放到一个Excel文件内,实现班级成绩表查询,每位老师的个人成绩表查询,每个教研组的成绩表查询。

好吧,这些用法也算不上高大上,但是作为一个一线老师能够用到这样,我觉得可以啦。

我是辛哥,如果您觉得对,请您关注我,如有不当,欢迎评论区留言指教!

澳门新萄京官方网站 105

回答:

最牛的:

大三的时候参加过一个叫做企业竞争模拟大赛的活动,见到别的团队用的Excel预测表,可以把当期的财务数据录取进去,输入一些本期的决策数据,自动规划求解出一个最优解,更牛逼的是可以预测出之后八期的结果。里面用到的公式啥的就不说了,那都已经跟小儿科了,用到的数据分析工具,规划求解之类的,还有vba。简直就是一个独立的程序。

工作以后见到同事用Excel链接服务器,每天自动更新公司运营数据,生成图表,全自动哦,至今也没搞懂他那个表是怎么写的。

最low的,这个好像不太好说。比较常见的一列数据有数值有文本,有的有空格有的没空格,最烦遇到这样的数据,压根没法批量处理

回答:

各有各的牛:

有图表牛的,南丁格尔玫瑰图。

有VBA牛的,表格合并,拆分,格式化。

有函数牛的:VLOOKUP,SUMPRODUCT,SUMIFS,INDEX,MATCH等等,加上数组函数,没有做不到,只有想不到。

LOW的,

澳门新萄京官方网站:如何不加班完成上百份Excel报表的合并,如何将多个Excel文件汇总到一个文件当中。第一low,合并单元格。

第二low,数据不规范,空格、文本数字混合,日期小数点等等。

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

澳门新萄京官方网站 106

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

澳门新萄京官方网站 107

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

澳门新萄京官方网站 108

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

澳门新萄京官方网站 109

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

澳门新萄京官方网站 110

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

澳门新萄京官方网站 111

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

澳门新萄京官方网站 112

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

澳门新萄京官方网站 113

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

澳门新萄京官方网站 114

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

澳门新萄京官方网站 115

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

澳门新萄京官方网站 116

澳门新萄京官方网站 117

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

澳门新萄京官方网站 118

澳门新萄京官方网站:如何不加班完成上百份Excel报表的合并,如何将多个Excel文件汇总到一个文件当中。6、我们简单拖拽看下效果,一个185万行数据产生的透视表就被我们做出来了。

澳门新萄京官方网站 119

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

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

本文由澳门新萄京官方网站发布于办公软件,转载请注明出处:澳门新萄京官方网站:如何不加班完成上百份E

关键词: