M语言之编写M公式的两大“法宝” 2024-04-24 05:48:15 “乾坤挪移”大法除了【添加列】选项卡下的功能以外,【自定义列】还可以实现很多命令按钮无法直接实现的功能。但是初学者经常会遇到这样的困惑:面对海量M函数,不知道什么情况下“找谁帮忙”。不要紧,有专门解决此困惑的“乾坤挪移”大法,分两步走:先获取公式,从选项卡下找到相关的命令进行操作,再移为己用,将由此操作步骤自动生成的M公式“挪移”到【自定义列】中。比如需要新生成一列,这一列里每一个数都是“数量”列的总计。可是计算总计该用哪个M函数,其中的参数又该如何使用,这些都是未知的,这时,此“乾坤挪移”大法就可以“发威”了。获取公式:如图 4-9 所示,选取“数量”列,单击【转换】选项卡下的【统计信息】下拉按钮,在下拉选项中选择【求和】,然后复制在【编辑栏】里自动生成的M公式(复制的内容不包括最前面的等号)。要点提示:求和,统计最小值、最大值、平均值等转换成一个值:【Power Query编辑器】→选取数值对象→【转换】→【统计信息】→【求和】或【最小值】或【最大值】或【平均值】计算被选取多列中每行的值:【Power Query编辑器】→选取多列数值对象→【添加列】→【统计信息】→【求和】或【最小值】或【最大值】或【平均值】移为己用:删除“计算的总和”这一步骤,单击【添加列】选项卡下的【自定义列】按钮,将刚才复制的M公式粘贴到【自定义列】对话框中自定义列公式的等号后,输入新列名“数量总计”后单击【确定】按钮,求和的新列就生成了,如图 4-10 所示。再回过头来看这个用于求和的M函数“List.Sum”的参数。“[数量]”很好理解,就是对“数量”列数据的引用,用这个M函数的目的就是对“数量”列进行求和。只是在“[数量]”前面还多了一个“已添加自定义”,这又是什么呢?原来,在使用某些M函数时,不仅需要确定引用的列,还要确定引用的查询表,而“已添加自定义”则是整个查询操作中的一个步骤,意思是,这个M函数引用的是“已添加自定义”这一步骤所生成的查询表里的“数量”列。如果在【应用的步骤】里将“已添加自定义”这一步骤删除,会产生什么样的影响呢?本着“折腾出真知”的原则,可以尝试一下。“已添加自定义”是一个中间步骤,删除时“异空间”会弹出一个对话框来询问是否确定要删除。有些中间步骤的删除确实会影响后续步骤,不过这里不用担心,单击【删除】按钮即可。将这一步骤删除以后再来看这个M公式,会发现唯一的变化就是其中的参数自动变成了前一个步骤的“添加打折列”,公式运行的结果没有受到半点影响,如图 4-11 所示。最后,将“已添加自定义 1”这个步骤的名称修改成更具描述性的“添加数量总计列”,才更符合规范。“依葫芦画瓢”法“List.Sum”是一个比较简单的M函数,再来看一个稍微复杂一点的M函数“Table.TransformColumns”,它的功能是对指定列进行各种转换操作。【转换】选项卡下的很多操作所生成的M公式里都有它的身影。例如,要为“品名”列里的内容添加统一前缀“新品”,单击【转换】选项卡下的【格式】下拉按钮,在下拉选项中选择【添加前缀】,在弹出的对话框里输入“新品”,然后单击【确定】按钮。这时从【编辑栏】里就可以看到图 4-12 所示的M公式。这个M公式里的第一个参数很好理解,就是“添加数量总计列”这一步骤所生成的查询表。第二个参数就比较有意思了,居然有两对大括号,这是在为同一参数里的子参数划定“势力范围”。M函数“Table.TransformColumns”只有两个参数,然而一个查询表里有那么多列,每列的转换又有那么多种类,光靠第二个参数实在说不清楚,所以就加了大括号,而且还是两层,大括号里的就是子参数。里层的大括号里是对每列进行转换的具体处理手段,有三个子参数:第一个子参数是外加一对半角引号的“品名”列列名,表示转换对象是“品名”列;第二个子参数就是具体的操作,这里是添加前缀,使用了连接符“&”,将前缀“新品”和这一列中的原始内容进行连接;第三个子参数是为转换后的列设置数据类型,“text”是文本类型。此M公式中包含了“each”和“_”的结构,“each”可以理解成“每一个”,而“_”是一种省略写法,在这里表示“品名”列里每一个具体的值。如此看来,里层大括号中的内容可以理解成,对“品名”这一列里的每一个值前面连接一个“新品”,生成的结果为文本类型。图 4-13 展示了M函数“Table.TransformColumns”的各参数。在理解了这个M函数各参数的含义以后,如果需要改变或增加其功能,只要在已有结构的基础上,按照语法规则“依葫芦画瓢”对其进行修改即可。比如,想要用一个M公式同时实现对品名添加前缀和将“单价”列里的数据全部加 20%两个功能,就可以如图 4-14 所示,将公式修改如下:修改后的公式又添加了一对里层的大括号,第一对大括号里的子参数是原有的,第二对大括号里的子参数是新输入的,其作用是对“单价”列里的每一个值乘以 1.2,生成的结果为小数类型。这两对大括号之间用半角逗号分隔。如果还有别的转换计算,可以按照这个规律继续增加大括号组,写入子参数。注意,参数与参数之间是否有空格无关紧要,加空格的目的无非是让整个公式的结构看起来更加清晰,但是同一参数内的一些固定用法,如“each”和“_”之间,或者“type”和“text”之间等,空格不可缺少。 赞 (0) 相关推荐 03 Excel 公式结构(图示及说明) 公式结构(图示)如下: 普通公式说明: 1. 公式 实际上 也是 表达式 的 一种类型. 2. 公式必须以 等号"="开始,表明后面所写的内容是公式. 3. 公式中 每个函数 的 ... MathType怎么写分段函数? 分段函数是数学里面特有的一种函数,它是对于自变量x的不同的取值范围,有着不同的解析式的函数.它的特点就是有一个大括号,然后有至少2个函数解析式,写这样的函数离不开专业的公式编辑器,下面就来学习具体编辑 ... PQ-综合实战:根据关键词匹配查找对应内容 今天的文章有点儿长,步骤有点儿多,但这个综合操作很值得练好,以后扩展应用的空间很大. 小勤:大海,公司现在要对产品根据关键词进行分类,有位大神写了个公式,不是很复杂,但基本效果实现了: 大海:嗯.这个 ... 利用PowerQuery,批量合并多个Excel的指定列 PowerQuery批量汇总多个Excel工作簿的功能非常实用,即使没有学习Power BI,也应该学会这个功能,如果你还不会,可以看看这两篇文章: 使用Power Query是一种什么体验? 批量合 ... PQ-M及函数:结合前期案例,学习自定义函数 小勤:<单个格式化表单转数据明细>的方法学会了,赶紧给我讲一下怎么实现批量的转换吧. 大海:不着急嘛,要实现批量的转换,需要学点儿新的知识--自定义函数. 小勤:自定义函数?就是自己写函数 ... “我面试了几十个大学生,发现他们竟然还在用分列法拆分数据,难怪效率那么低……” 每天一点小技能 职场打怪不得怂 编按:在日常办公中,我们难免会收到不按规范录入的数据表,所以拆分单元格重置数据源就成了Excel人必会的办公技能之一,其中分列法更是最受欢迎的技巧之一.可是,今天小E讲 ... 很有意思的括号内数字求和 大家好,今天和大家分享"很有意思的括号内数字求和",前面是一个订单号,后面跟着一对小括号,小括号里是销量,小括号不确定,但是不会超过10个,现在要把这些销量加起求和,如下图,就是把 ... PQ基础-数据转换5:添加简单自定义列 在Power Query中,添加自定义列一般涉及到函数的使用,其实就跟Excel中在一个新的列里写公式一样,本文通过一个简单的例子,先说明怎样添加自定义列,以及如何对自定义列编写公式.数据如下: 要求 ... 分组时需要求和的数据有几十列,有快捷方法吗? - 1. 问题 - 在我以前的文章中,涉及分组依据操作的内容,需要聚合(求和等)的列通常不会太多,因此,手工操作一下也很快,但有朋友还是碰到了需要对几十列进行求和的问题,这个时候,如果还是手工一项项地 ... 认识Power Query的自定义函数 使用Power Query进行复杂一些的数据处理,离不开M函数,目前已经有超过700个函数了,基本上各式各样的数据处理需求都可以使用M函数实现,如果你觉得这些还不够,或者使用起来不是很方便,也可以在P ... Power Query批量合并Excel,数据不是从第一行开始怎么办? 这种情况确实很常见,因为大多数Excel表都会有个表头信息,具体的明细数据从下面的某行才开始,比如下面示例中的三个表就是这样: 这三张表的数据都是从第5行才开始的,并且数据字段的顺序也不一致,这种情况 ... PQ-M及函数:对比Excel,一次搞定5个最常用的文本函数 小勤:大海,最常用的文本类处理函数给我讲讲呗. 大海:好的,在PowerQuery里,文本类函数可能用得比数值计算函数还要多.这里我们通过和Excel里的常用文本处理函数进行对比的方式来学,可能会效果 ...