关闭

15、Power Query-行列管理实例应用

标签: Power Query
3533人阅读 评论(1) 收藏 举报
分类:

Power Query-行列管理实例应用

数据源如下图,是学生三门课程的考试成绩,其中有一次通过的,有考过多次的:


要求:

1、找出每个课程考试三次以上的(包含三次);

2、找出三个课程同时一次考试通过的;

3、找出每个课程一次考试通过的;

貌似以现在我们的excel功能很难做到,下面我们就用PQ的方式跟大家讲讲具体的实现。

我们用之前学过的数据导入方式;"从excel文件导入“的方式导入。


上图就是我们处理过后的数据源。

然后我们需要找出没门课程考试三次及以上的童鞋,看似有点难度,其实就是要找出前面两列数据出现过三次及以上的记录。

所以我们需要选中前面两列,点击”开始“——”删除重复项“——”保留重复项“(2013版本是”保留行“——”保留重复项“)。


现在就把一次通过的记录都删掉了。


留下来的都是两次及两次以上的记录,跟我们的需要貌似还差一点。

其实我们进行每一次的操作,excel都是有代码支持,最简单的方式就是进入它的高级编辑器,找到如下字眼:

selectDuplicates = Table.SelectRows(addCount, each [Count] > 1)

像我英文这么好,编程又这么好的人一眼就看出来是什么意思了,这里的>1其实就是找出出现过一次以上的记录行。

很明显咯,我们把这里改成">2"不就大功告成了!



然后这里已经可以满足我们的要求了,但是我们是很严谨的啊,继续,修改列名,然后分组依据,求出他考了到底多少次,最后的分数是多少。



这样应该差不多了。

第二个需求是找出一次性通过的名单。

这个可以依葫芦画瓢了,选中前面两列,”保留行“——”保留重复项“。


我们把高级编辑器里改成”<2”,不就是一次通过的么!


将它转换程一个二维表。

选中“姓名”列,“转换”——“透视列”。



最后一个需求有点变态,是要找出所有课程都一次通过的名单。比如上面的陶勇,他的英语没有通过,是不给显示的。

我们依旧用透视列的功能。



千万记得选中“不要聚合”,为什么呢?因为只有一条记录的才会显示正确,否则都是error。

最后我们选中分数列(最好是对于数字列全部选中)用行列功能管理里的“开始”——“删除行”——“删除错误”。



搞定,相当流弊的功能啊!

1
0
查看评论

Power Query

查询增强版(Power Query)是一个Excel插件,是Power BI的一个组件。 Power Query 在Excel中通过简化数据发现、访问和合作的操作,从而增强了商业智能自助服务体验。 查询增强版(Power Query)是一个Excel插件,是Power BI的一个...
  • zhongguomao
  • zhongguomao
  • 2016-12-23 14:04
  • 865

1、大道至简的数据处理工具-(Microsoft Power Query入门)

大道至简的数据处理工具-Microsoft Power Query 告别复杂的excel函数,excel VBA编程,让一切回归简单与职能。 什么样的人群适合这样的一个工具: 1、出纳、会计、统计、仓管、数据分析等与数据打交道的的岗位。 2、不想学处理数据的函数、VBA编程等复杂的功能,但工作又有需...
  • zhongguomao
  • zhongguomao
  • 2016-12-23 15:23
  • 13338

6、Power Query-SQL与PQ技术的强强联合

数据来源之ODBC——SQL与PQ技术的强强联合 比如有的功能SQL比较简单,又比如有的功能用Power Query更加简单,我们可以用他们联合的方式增强工作效率。 本节要求: 求出姓名为两个字的人,并且工资大于等于4000元人名币的,给他们做一个排名。 效果图: 数据来源:D:\Power Q...
  • zhongguomao
  • zhongguomao
  • 2016-12-29 09:51
  • 3076

8、Power Query-合并查询详解

精解合并查询——比SQL内外连接更方便 要求:比如有两个工作表《工资2014年》,《工资2015年》,现在要进行合并,可能有的人在2014,有的人在2015。 当然我们还是用之前讲过的方法,先把他们两个表存为连接。 之后进行合并,Power Query——合并。 这里我们看到联接种类有六...
  • zhongguomao
  • zhongguomao
  • 2016-12-30 09:59
  • 5126

29、Power Query-分支语句的进阶

本节重点:Power Query-分支语句的进阶 例子:同样是之前的数据源,现在要根据多列进行判断,要求求出每门功课都及格的人数。 比如上图中的“休闲海”小朋友,他的三门功课都是">=60"的,即显示“合格”,不需要进行补考。 分析:我们发现之前用的“条件列”...
  • zhongguomao
  • zhongguomao
  • 2017-01-12 14:55
  • 3251

9、Power Query-多表记录合并

Power Query追加查询——多表记录合并如此简单 之前我们讨论过两个表的合并,若大于两个表怎么办呢? 如下图:三个表的合并
  • zhongguomao
  • zhongguomao
  • 2016-12-30 15:40
  • 3888

代码维护:改进代码的一些方法

代码维护:改进代码的一些方法0 引言应用的整个生命周期的维护通常都被低估了。然而,维护毫无疑问是花费最多、也是最难执行的阶段。因此,初始开发阶段的代码质量直接影响维护的成本。下面一些数字说明了这一点:·       &#...
  • winboy20
  • winboy20
  • 2003-03-14 09:13
  • 880

2、Power Query-动态汇总单元格区域数据

数据来源之单元格区域数据(动态汇总单元格区域数据) 这次主要讲解power query的基本操作界面。 任务:求出各个行业第一、二季度的总数据。 例子非常简单,聪明的朋友可能会认为用透视图更加简单,这里我们只是举例。 首先获取数据(从表格) 数据——从表格  表包含标题,有点像“插入...
  • zhongguomao
  • zhongguomao
  • 2016-12-23 21:42
  • 5571

37、Power Query-不使用IF嵌套进行匹配

本节要点:Power Query-不使用IF嵌套进行匹配(突破IF语句嵌套写法) 标题可能很笼统,话不多说,直接看案例。 左表是学生成绩表,右边是成绩范围对应的等级,目的是匹配到学生成绩的等级。 这次我们不使用IF语句进行判断,结合之前所学,讲解一个新的方法。 首先依旧是将左右两个表...
  • zhongguomao
  • zhongguomao
  • 2017-01-16 10:31
  • 2128

3、Power Query-智能汇总工作簿下的指定或所有工作表数据

数据来源之工作表(智能汇总工作簿下的所有工作表数据) 本节要求:统计出数据源工作表里面各个类别,各年的总数量。 比如下图的,是在D盘Power Query文件夹下面的一个数据源文件: 其实会用sql语句的童鞋会觉得特别简单,这里我们用新方法来实现。 选择“Power Query”下面的“从文件”...
  • zhongguomao
  • zhongguomao
  • 2016-12-24 12:04
  • 4289
    个人资料
    • 访问:4653014次
    • 积分:65388
    • 等级:
    • 排名:第42名
    • 原创:1753篇
    • 转载:2553篇
    • 译文:101篇
    • 评论:343条
    博客专栏
    最新评论
    微信公众号
      为你推荐最新的博文~更有惊喜等着你