Microsoft Excel是Microsoft为使用Windows和Apple Macintosh操作系统的电脑编写的一款电子表格软件。直观的界面、出色的计算功能和图表工具,再加上成功的市场营销,使Excel成为最流行的个人计算机数据处理软件。 数据源: “姓名”“基数”“个人比例”“个人缴纳”“公司比例”“公司缴纳”“总计”,共7列7行数据,其中姓名列,第1、2行与第6、7行内容重复 目标: 留下第1、2、6、7姓名列中内容重复的行 操作过程: 选取指定列》【主页】》【保留行】》【保留重复项】 ? M公式: = let columnNames = {"指定列名"}, addCount = Table.Group(步骤名, columnNames, {{"Count", Table.RowCount, type number}}), selectDuplicates = Table.SelectRows(addCount, each [Count] > 1), removeCount = Table.RemoveColumns(selectDuplicates, "Count") in Table.Join(步骤名, columnNames, removeCount, columnNames, JoinKind.Inner) 说明: = let?columnNames = {"指定列名"}, addCount = Table.Group(步骤名, columnNames, {{"Count", Table.RowCount, type number}}), selectDuplicates = Table.SelectRows(addCount, each [Count] > 1), removeCount = Table.RemoveColumns(selectDuplicates, "Count") in Table.Join(步骤名, columnNames, removeCount, columnNames, JoinKind.Inner)
? = let columnNames = {"指定列名"},?addCount = Table.Group(步骤名, columnNames, {{"Count", Table.RowCount, type number}}), selectDuplicates = Table.SelectRows(addCount, each [Count] > 1), removeCount = Table.RemoveColumns(selectDuplicates, "Count") in Table.Join(步骤名, columnNames, removeCount, columnNames, JoinKind.Inner)
? = let columnNames = {"指定列名"}, addCount = Table.Group(步骤名, columnNames, {{"Count", Table.RowCount, type number}}),?selectDuplicates = Table.SelectRows(addCount, each [Count] > 1), removeCount = Table.RemoveColumns(selectDuplicates, "Count") in Table.Join(步骤名, columnNames, removeCount, columnNames, JoinKind.Inner)
? = let columnNames = {"指定列名"}, addCount = Table.Group(步骤名, columnNames, {{"Count", Table.RowCount, type number}}), selectDuplicates = Table.SelectRows(addCount, each [Count] > 1),?removeCount = Table.RemoveColumns(selectDuplicates, "Count")?in Table.Join(步骤名, columnNames, removeCount, columnNames, JoinKind.Inner)
? = let columnNames = {"指定列名"}, addCount = Table.Group(步骤名, columnNames, {{"Count", Table.RowCount, type number}}), selectDuplicates = Table.SelectRows(addCount, each [Count] > 1), removeCount = Table.RemoveColumns(selectDuplicates, "Count") in?Table.Join(步骤名, columnNames, removeCount, columnNames, JoinKind.Inner)
? 将所有步骤拆分如图所示。 ? 其中核心部分: addCount = Table.Group( 更改的类型,"姓名", {{"Count", Table.RowCount, type number}}), selectDuplicates = Table.SelectRows(addCount, each [Count] > 1), removeCount = Table.RemoveColumns(selectDuplicates, "Count"), 最后一步 = Table.Join(更改的类型, "姓名", removeCount, "姓名", JoinKind.Inner) 最终效果: 数据只剩下姓名列中重复的四行数据 ? 多说一句: 好吧,我承认,我被这个公式惊到了!这其实已经不是一个简单的公式,而是一段M代码,这不是有let开头,in结尾么…… Excel整体界面趋于平面化,显得清新简洁。流畅的动画和平滑的过渡,带来不同以往的使用体验。 |
温馨提示:喜欢本站的话,请收藏一下本站!