4.统计应在本月结束的班级数量,部门内部数据的

作者: 操作系统  发布:2019-08-30

场景

先把CRM中的一片段顾客记录抓抽取来,然后再把CRM中的一部分商机记录抓收取来,并在Excel中涉及那五个数据源,最终将它们放在Excel中展现出来。

9159金沙官网 1

【需求】

那是三个小例子,供给非常粗略:
1.计算甘休最近,全数笔者承担的班级数量和学习者数量;
2.总括全数曾经甘休的班级的数量,以及相应截至不过从未了结的班级数量;
3.总计上一个月起来的班级数量,并列出精心;
4.总计应在上一个月终结的班级数量;
5.两周报告二遍以上数量。

9159金沙官网 2

班级原始数据.png

9159金沙官网 3

使用Excel PowerQuery和PowerPivot分析Dynamics CRM数据

恐怕我们也开采,博主撰写的稿子大多数都和Developer相关,涉及到的都以一些须要标准编制程序知识手艺驾驭的编制程序知识。明天啊,小编给大家介绍部分不等同的事物,告诉大家怎么使用Excel去深入分析Dynamics CRM系统中的数据。

Excel软件我们都精晓啊,不打听的同学能够去面壁了,微软对Excel软件的器重度是那是无需提的,它在Office产品套件里面包车型大巴身价也是独占鳌头,随着近来多少个本子的Release,用Excel做出一些粲焕的BI体现效果也是很自在的事体呀!

那篇小说中,博主将选择的Excel 贰零壹陆以及Dynamics CRM 二〇一六Online为我们做示范,若是有想学学小说中例子的相爱的人,须求先企图好相应的软件。这里大家须求使用Excel 二〇一四中的多少个轻重级功效,PowerQuery和PowerPivot。

6、大家简要拖拽看下效果,三个185万行数据发生的透视表就被大家做出来了。

【步骤】

1.起家单独的项目文件夹
原始新闻是从系统导出的数据,不作任何更改,也不重命名。若无独自的品类文件夹,由于系统导出的数目命名都以一模二样的,所以很轻松就和其余导出的数据文件混淆了,除非小编重命名——作为懒人来说,重命名也很讨嫌啊。

之所以小编索要独自为此工作树立三个连串,作者用文件夹的格局来归并保管原始数据和计算结果报表。

9159金沙官网 4

工程文件夹.png

为啥要独自创建贰个“原始音信”文件夹呢?那是双子座人格障碍——原始数据和结果数据混在一道延续令人纠结,是不?

2.导出原来数据,归入项目文件夹的子文件夹“原始音讯”文件夹中,不作任何改换,也不重命名。系统中程导弹出来的是如何,正是怎么着。

3.新建叁个Excel文件,重命名字为“班级总结结果”。

4.行使PowerQuery的参数表格塑造参数表格及其函数。
若是只是小编一位用,其实参数表格没须求。不过自个儿有个野心——想让别的同事拷贝笔者那一个工程文件夹到ta计算机后,ta没有必要做任何修改就能够间接使用模板。

参数表格建构艺术见《PowerQuery的参数表格用法》。参数表格唯有二个参数,其值是二个公式:

=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1)&"原始信息"

本条公式的指标便是为着让此工程文件能在其他Computer上无障碍使用,不用修改数据源的路径。

9159金沙官网 5

参数表格参数值.png

5.用PowerQuery对数据源进行引入和整理,代码如下:

let
源 = Folder.Files(fnGetParameter("班级汇总报表")),
删除的其他列 = Table.SelectColumns(源,{"Content"}),
合并的二进制 = Binary.Combine(删除的其他列[Content]),
#"导入的 CSV" = Csv.Document(合并的二进制,[Delimiter=",", Columns=14, Encoding=936, QuoteStyle=QuoteStyle.None]),
提升的标题 = Table.PromoteHeaders(#"导入的 CSV"),
更改的类型 = Table.TransformColumnTypes(提升的标题,{{"序号", Int64.Type}, {"班级名称", type text}, {"起止日期", type text}, {"所属域", type text}, {"承办单位", type text}, {"班级管理员", type text}, {"应到人数", Int64.Type}, {"实到人数", Int64.Type}, {"参加率", type text}, {"班级状态", type text}, {"整体满意度", Int64.Type}, {"预算费用", Int64.Type}, {"实际费用", Int64.Type}, {"", type text}}),
用分隔符分列 = Table.SplitColumn(更改的类型,"起止日期",Splitter.SplitTextByDelimiter("至", QuoteStyle.Csv),{"起止日期.1", "起止日期.2"}),
更改的类型1 = Table.TransformColumnTypes(用分隔符分列,{{"起止日期.1", type date}, {"起止日期.2", type date}}),
筛选的行 = Table.SelectRows(更改的类型1, each not Text.Contains([班级名称], "提报")),
重排序的列 = Table.ReorderColumns(筛选的行,{"序号", "班级名称", "实到人数", "起止日期.1", "起止日期.2", "所属域", "承办单位", "班级管理员", "应到人数", "参加率", "班级状态", "整体满意度", "预算费用", "实际费用", ""}),
删除的其他列1 = Table.SelectColumns(重排序的列,{"班级名称", "班级状态","实到人数", "起止日期.1", "起止日期.2"}),
重命名的列 = Table.RenameColumns(删除的其他列1,{{"起止日期.1", "线上学习开始日期"}, {"起止日期.2", "线上学习结束日期"}})
in
重命名的列

马虎多余的字段和代码格式吧。笔者也未有对每二个手续举办有含义的命名。

在意“源=……”这一行,使用了参数表格函数引进数据源路径。
专心“用分隔符分列”那一个手续,那是因为原来数据的班级开班和了结日期是在同贰个字段里,因而要求将其拆开为“开端日期”和“结束日期”多个字段。

6.将PowerQuery查询结果加载到数据模型,仅创立链接。

7.取得上个月上马明细。
那边本身是一直援引了前方创制好的询问,然后用公式筛选,代码如下:

let
  源 = 原始信息,
  筛选的行 = Table.SelectRows(源, each Date.IsInCurrentMonth([线上学习开始日期]))
in
  筛选的行

那边也足以用PowerQuery的Evaluate来创建查询,但是比较麻烦,还比不上直接援用查询然后筛选。

将其加载到表,同仁一视命名字为“前段时间精心”。

只顾用Date.IsInCurrentMonth()的指标也是为着偷懒,要不然能够直接筛选具体的月度来兑现——但那样太费事,不是?

8.进入PowerPivot,成立须求的衡量值:

总班级数=COUNTROWS('原始信息')
总参加学员数=SUM('原始信息'[实到人数])
已结束的班级=CALCULATE(COUNTROWS('原始信息'),FILTER('原始信息','原始信息'[班级状态]="已结束"))
本月=MONTH(NOW())
本月开始线上学习的班级数=CALCULATE(COUNTROWS('原始信息'),FILTER('原始信息',MONTH('原始信息'[线上学习开始日期])=[本月]))
本月参加学员数=CALCULATE(SUM('原始信息'[实到人数]),FILTER('原始信息',MONTH('原始信息'[线上学习开始日期])=[本月]))
本月应结束班级数=CALCULATE(COUNTROWS('原始信息'),FILTER('原始信息',MONTH('原始信息'[线上学习结束日期])=[本月]))
上月应结束班级数=CALCULATE(COUNTROWS('原始信息'),FILTER('原始信息',MONTH('原始信息'[线上学习结束日期])=[本月]-1))

9创办基于数据模型的数据透视表,将索要的字段拖入数据透视表,最后结果如下:

9159金沙官网 6

计算结果.png

很简陋的贰个表,因为是里面使用,作者就偷懒了。
公式和总计结果中,笔者塞入了三个“下三个月应终结班级数”,那些是个bonus,能够无视。

使用Excel2010 PowerPiovt消费OData Service

未焚徙薪专门的学业

在那几个例子里面,作者筹划用PowerQuery去调用Dynamics CRM Odata Endpoint,并赢得自个儿的盼望数据。这里提到到什么样组织OData Query,在这么些事例中,笔者使用了上面四个OData Query:

 

Account Data Source:
https://ghostbear.api.crm6.dynamics.com/xrmservices/2011/organizationdata.svc/AccountSet?$select=AccountId,Name,AccountNumber,OwnerId,Address1_Country,Address1_City,Address1_Line1

Opportunity Data Source:
https://ghostbear.api.crm6.dynamics.com/xrmservices/2011/organizationdata.svc/OpportunitySet?$select=OpportunityId,Name,ParentAccountId,BudgetAmount,StepName

接下来利用PowerPivot对取到的八个数据源进行关联操作,顾客数量为主数据,商业机械数据为从数量,并用PivotTable的格局将它们展示出来。

4、大家点击[Data]列的进行按键,去掉利用原本列名作为前缀的勾选,点击明确。

【思路】

因为每两周就要报告一遍计算结果。由此小编希望完毕自动化——作者只要求导出原始数据,然后展开报告,刷新,就获取新型结果。为了促成那几个指标,作者索要同期用到PowerQuery和PowerPivot。

PowerQuery的法力是引进数据源,并赢得当月上马明细;PowerPivot的机能是依据DAX来总结并搜查缴获其他数据。

是因为篇幅有限,只演示如何消费Odata数据。它还可支撑七种数据库,援助文件数量源CVS等。 越来越多职能等待你开掘。

具体步骤

利用PowerQuery获取Dynamics CRM数据源,须要输入CRM的印证消息

9159金沙官网 7

输入CRM验证音信

9159金沙官网 8

加载成功的CRM数据源

9159金沙官网 9

将PowerQuery数据源加载进PowerPivot模型:选拔在Excel中开展预加载的数码表格,并点击“Add to data model”

9159金沙官网 10

 

拖拽字段创制表的主从关系,并将创造好的数据模型插入到Excel中

9159金沙官网 11

配备体现PowerPivot数据模型的图样

9159金沙官网 12

点击刷新数据,PowerQuery会自动获取最新的系统数据

9159金沙官网 13

是还是不是很酷呢?通过如此的措施,终端客商都能够做出功效壮大的表格哦!我们快捷试试啊!

PowerQuery和PowerPivot分析Dynamics CRM数据 可能我们也意识,博主撰写的稿子超越六分之三都和Developer相关,涉及到的都以部分供给规范编制程序知...

9159金沙官网 14

【反思】

实际聊起底建设构造衡量值时,小编能够不必那么麻烦,利用好切成条器和筛选,能够获取到改变态的总计结果。可是思念到不见得使用此报表的人手会动用这一个复杂的多少透视表作用,所以本身间接把对方想要的多寡写死显示出来。

9159金沙官网 15

9159金沙官网 16

【分享方法】

将工程文件夹全体拷给须求的人,然后ta导出自个儿须要的班级数据,放到“原始音信”文件夹,打开总计结果文件,刷新,就能够得到最新结果。

有个前提:最佳是Excel 2015。假设是Excel 2011等等的老版本,确认保障PowerQuery和PowerPivot插件启用。

笔者们这里Append一张差别表结构的数码到Category表前边,如下图:

9159金沙官网 17

作者:Petter Liu
9159金沙官网,出处:
正文版权归小编和新浪共有,接待转发,但未经小编同意必得保留此段表明,且在篇章页面鲜明地方给出原著连接,否则保留追究法律权利的职分。
该作品也还要透露在本人的独门博客中-Petter Liu Blog。

组合PowerPivot实现上百万数量的透视深入分析

         Excel是大家熟谙的办公室软件。PowerQuery是一个允许连接三种数据源的Excel插件。它能从叁个网页上智能查询数据。使用PowerQuery能集合数据集使用join,merge操作。PowerQuery能生成的加载与清洗的台本。它能从此时下载。如下图是设置后,展开时:

3、张开自定义列,对Kind列筛选,让其也正是Sheet,防止表格中有报表援用,可能区域名称导致汇总出现重复或然有些错误,那块大家就能够见到大家实际的各种职业簿云南中华南理工科业余大学学学程公司作表的称号,每一种工作表的故事情节将被寄存在了[Data]列,大家随便点击一个单元格便能见到里面包车型大巴数量。

 

9159金沙官网 18

9159金沙官网 19

9159金沙官网 20

 

总结:
1、火速合併文件夹文件,大家看出大家仅需贰个函数Excel.Workbook合作可视化的部分操作就能够成功。2、后续只须要一键刷新就能够到位集中。3、通过Excel插件PowerPivot的极度我们做到了185万行数据的透视表的创立,对守旧Excel剖析的技能做了二个十分的大的突破。
实则关于Excel中商务智能连串套件对我们前途的相当多解析事情供给提供了越来越多的也许,本文所用示例文件能够加群197986726收获,更加多小说应接关切【简快Excel】(PowerBIPro)假如本文对你有帮助,迎接点赞转载。

9159金沙官网 21

刷新方法3:在左边查询分界面,右键刷新,若无看出左侧的工作表查询的话,点击数据标签下的显得查询按键就可以

你或然感兴趣的稿子:

9159金沙官网 22

点击Done后,自动把多少下载到EXCEL Sheet中:

5、大家就见到数据已经被联合好了,未有用的列大家删除就可以:

企望对您有赞助。

9159金沙官网 23

9159金沙官网 24

4、大家点击PowerPivot下的保管按钮,查看下我们的数目,若无PowerPivot标签点击Ecxel的【COM加载项】勾选下就可以,注意需求13要么16正经巩固版以上才有此成效:

让大家尝试From OData Feed, 这里连接公开的OData Feed,

9159金沙官网 25

多少是如此的预览,我们得以点击前面Produts会活动接二连三到具体表,在那时候可导航表关系:

9159金沙官网 26

9159金沙官网 27

本文笔者:雷神子,如需转发请联系授权

9159金沙官网 28

9159金沙官网 29

9、我们追究下100 的报表的拜候效果以及大家PowerQuery的优势,大家复制原始数据10份,在待合併文件夹。

9159金沙官网 30

3、大家在大家侧面大家的查询上点击右键,选取【加载到】,然后如下图所示接纳,点击鲜明。

9159金沙官网 31

9159金沙官网 32

8、大家先接纳加载到表,看下效果,我们看出拾二个表460行的数码就被大家加载成功了:

关于PowerQuery文件夹的合併就介绍这么多,假使原来数据非Excel格式的话而是Csv的话,把Excel.Workbook替换为Csv.Document([Content],[Delimiter=”,”, Encoding=936]),别的操作都一样。
接下去我们探究下什么样利用这么些艺术完毕相对级其他数量透视剖析
1、大家修改原始文件,中七个表的数据量到92万行,保存关闭

之所以,明天大家的求学目的就分为以下两地点:
成就上百以致上千上万份同格式的文书的汇总合併

10、我们供给再行做叁遍刚才的干活呢?NO!NO!NO!我们只须要刷新下小编么的数据源就可以,以下办经济学习一种即可
刷新方法1:数据标签下面的一体刷新功能

咱俩先放入10份文件测验,完结后再给大家演示100份文件的联合
第二步 大家在文书夹外新建三个Excel文件汇总的数量据应用
其三步 新建查询起头链接大家的源数据
大家点击数据标签下面的【新建查询】→【从文件】→【从文件夹】,如下图:

瞩目:公式供给区分大写
2、然后,大家删除大家没有需求的苦恼列,大家按住ctrl选取大家的表名称[Name]列和[自定义]列

刷新方法2:在脚下表格里右键,点击刷新

9159金沙官网 33

9159金沙官网 34

5、我们185万的数量就被存放在Excel的PowerPivot中的,接下去大家点击插入透视表,选拔采纳此专门的学问簿的数据模型为源数据就能够。

9159金沙官网 35

11、点击刷新,大家静观其变不到2s钟的时日,100份报表就帮大家汇总产生了,有意思味的同窗也能够测量试验下一千份。

2、直接刷新,我们看来表格会报错,因为大家得以见见左边加载了185万行数,Excel文件里直接放不下了,好了大家看下怎么管理吧。

产生后点击浏览找到大家的公文夹路线点击显著,也足以复制大家的门道直接点击分明即可。

6、接着大家修改下数据类型就可以。
7、我们点击关闭并上载下拉效力,关闭并上载至,按键,大家大概解释一下,上边的加载到表,正是加载到大家的EXCEL界面,数据量在100万之内的选拔那些从未其他难题。下面的【将此数量拉长到数据模型】勾选后得以将查询获得的汇聚数据加载到PowerPivot中,PoewrPivot合营仅创制连接表(即不直接存款和储蓄数据到Excel表格区域)

9159金沙官网 36

9159金沙官网 37

9159金沙官网 38

比如认为以上内容对你具有接济,大家就随即往下看:
PS:关于PowerQuery的简练介绍能够关注我们【简快Excel】查看历史篇章,有详尽介绍。
联合上百份报表的操作思路:
操作步骤:
先是步 将在合併的文件放到大家待合併文件夹

几百个业务人员的数码反映,几十家支行的每日汇总,各渠道数据的聚焦,部门中间数据的汇聚等等,相信在不计其数尚未宏观CRM数据系统的商铺,可能每一日都都急需有人去做那样的专门的学问。可想而知,非高效没意义的做事大家能自动化智能化的代表大家则会设法去顶替。明天大家就用Excel中的PowerBI体系插件帮我们去完毕这么的劳作。

然后我们点击编辑,就可以步向PowerQuery分界面。
第四步 PowerQuery编辑获取数据
从第三步的表里我们看到了这几列满含了大家的文本名,后缀,创设日期,修改日期,访谈日期,以及她的文件夹路线等天性,就是从未一直看出我们表里的多寡,其实大家表里的数据是被以二进制的办法存款和储蓄在【Content】列了。大家仅仅要求三个函数就会获取当中的始末,我们具体操作下。
1、增添列里,点击自定义列,输入=Excel.Workbook([Content],true)

9159金沙官网 39

本文由9159.com发布于操作系统,转载请注明出处:4.统计应在本月结束的班级数量,部门内部数据的

关键词: 9159.com 9159金沙官网