你不知道的Power Query技巧之提取列与拆分文本数字 2024-06-05 09:14:54 按分隔符提取列接下来就该对剩下的“明细.2”列“开刀”了。这列的每个单元格里都有两个空格,可以据此将这 1 列分成 3 列:第 1 列“部门”;第 2 列“科目”和“借方金额”的混合;第 3 列“贷方金额”和“方向”的混合。用【拆分列】下拉选项里的【按分隔符】→【空格】来实现拆分自然是最简便的办法,但是,本着“折腾出真知”的原则,不妨再来尝试一下其他招数。如果需要“挖出”“部门”的内容也很简单,因为第一个空格就在每个部门后面,在“异空间”里是可以按分隔符提取列的。如图 3-11 所示,在【添加列】选项卡下【提取】下拉选项里选择【分隔符之前的文本】选项,并在弹出的对话框里按空格键,作为输入的分隔符,单击【确定】按钮,再把标题名由自动生成的“分隔符之前的文本”改成“部门”,并且将这一列用鼠标拖曳到“凭证号”列和“明细.2”列之间,“部门”列就“独立”了。要点提示:提取指定分隔符之前的文本转换:【Power Query编辑器】→选取对象→【转换】→【提取】→【分隔符之前的文本】→输入分隔符→设置高级选项→【确定】添加:【Power Query编辑器】→选取对象→【添加列】→【提取】→【分隔符之前的文本】→输入分隔符→设置高级选项→【确定】“部门”列通过使用【分隔符之前的文本】完美“独立”了,那么后面的数据呢?同样可以使用【提取】下拉选项里的功能来操作。如图 3-12 所示,要把“明细.2”列里两个空格之间的内容提取出来,就可以使用【分隔符之间的文本】命令,当然,在其弹出的对话框里就不是输入一个分隔符了,而是要分别在【开始分隔符】和【结束分隔符】的位置各输入一个空格,再单击【确定】按钮,由此生成“分隔符之间的文本”列,也就是“科目”和“借方金额”的混合。因为还需要进一步的处理,所以可以不改标题名,只将其位置移到“部门”列和“明细.2”列之间。要点提示:提取指定分隔符之间的文本转换:【Power Query编辑器】→选取对象→【转换】→【提取】→【分隔符之间的文本】→分别输入【开始分隔符】和【结束分隔符】→设置高级选项→【确定】添加:【Power Query编辑器】→选取对象→【添加列】→【提取】→【分隔符之间的文本】→分别输入【开始分隔符】和【结束分隔符】→设置高级选项→【确定】如图 3-13 所示,“明细.2”这一列经历了分隔符之前和之间的提取后,只剩下最后一次提取了,那就是第二个空格之后的“贷方金额”和“方向”的混合内容。要实现这一目的,可以用【转换】选项卡下【提取】下拉选项里的【分隔符之后的文本】命令,在弹出的对话框里输入一个空格作为分隔符。接下来不要急着单击【确定】按钮,因为“明细.2”列里的空格有两个,而按分隔符提取内容时,其分隔符的位置是从左往右扫描的。此处可以把【高级选项】展开,选择【从输入的末尾】选项,然后再单击【确定】按钮,这样分隔符位置的扫描方向才会改成从右往左,从而把右边一个空格之后的文本愉快地提取出来。要点提示:提取指定分隔符之后的文本转换:【Power Query编辑器】→选取对象→【转换】→【提取】→【分隔符之后的文本】→输入【分隔符】→设置高级选项→【确定】添加:【Power Query编辑器】→选取对象→【添加列】→【提取】→【分隔符之后的文本】→输入【分隔符】→设置高级选项→【确定】拆分文本与数字“长得”依然相当不像话的两列,它们都是文本和数字“纠结”在一起的,只不过一个文本在左,另一个数字在左。这些数据既没有固定的字符数,也没有包含特定的分隔符,要拆分开来似乎极为麻烦。但是在“异空间”里,这种麻烦却不是麻烦,因为在【拆分列】里有两个“专治”这类数据的功能:【按照从非数字到数字的转换】(图 3-14)和【按照从数字到非数字的转换】,这两个功能的作用类似,就是一键把数字和非数字混在一起的列拆分开来,唯一的区别是数字和非数字的排列方向。要点提示:拆分数字与非数字混排的内容非数字在左:【Power Query编辑器】→选取对象→【主页】或【转换】→【拆分列】→【按照从非数字到数字的转换】数字在左:【Power Query编辑器】→选取对象→【主页】或【转换】→【拆分列】→【按照从数字到非数字的转换】然而不幸的是,“异空间”对“数字”这个概念大概是有什么误会,拆出来的结果把小数点都给“郁闷”着了,只好再加一步【合并列】的操作,将因小数点的存在而被强制拆分的两列再重新合并到一起,如图 3-15 所示。将这“长得”相当不像话的两列分别依次拆分、合并完成以后,就变成了 4 列,再分别重命名为“科目”“借方金额”“贷方金额”和“方向”,这个查询表就大致成形了。 赞 (0) 相关推荐 POWER QUERY--拆分数据列 点击上方 蓝色 文字 关注我们吧! 送人玫瑰,手有余香,请将文章分享给更多朋友 动手操作是熟练掌握EXCEL的最快捷途径! 提到拆分列,我们首先想到的是EXCEL自带的分列功能,以及通过公式来进行数 ... 新同事发来一个统计表,气的要辞职 ! 以前曾有一个同事给小明发来一个销售统计表,气的小明差点辞职. 至少,前面同事还用逗号把型号.销量分开.但今天这位新同事直接要把小明气晕过去:逗号也省了 这......小明气的又想辞职,同事没有最笨,只 ... Excel一个单元格中多个用符号间隔的数字,如何实时求和? 说明: Power Query编辑器 内置于 Office Excel2016或更高的版本中. Power Query编辑器 属于微软办公软件内置组件,国产金山WPS无法使用. 建议: 建议同学们下载 ... 我给客户转换了一个表格,操作很简单,但是大多数人不一定会 有客户遇到了一个问题.他有下面的一个表格: 他希望的表格结果是这样的: 仔细想想,除了粘贴,复制,选择型粘贴外,还有什么更好的办法吗? 当然,更好的办法来自于Power Query.我使用Power ... PQ实战 | 这种类似VLOOKUP的查找为什么不对? 今天是一期网友的问题答疑!主要是数据源不规范到底的简单问题复杂化! 提供两种解法,一种是Power Query主题解法,然后补充一种工作表函数解法 需求说明: 1.查找内容,可能有多个,每个都要到数据 ... 用通配符进行替换,居然还能这么玩儿? | PQ技巧 很多朋友都在问,Power Query里能不能使用通配符,比如最常用的星号(*),在Excel里可以实现如下图中的批量替换: 可惜,在Power Query里是没有的,比如,如果你想在替换值功能里用* ... POWER QUERY--从列数据中提取字符 点击上方 蓝色 文字 关注我们吧! 送人玫瑰,手有余香,请将文章分享给更多朋友 动手操作是熟练掌握EXCEL的最快捷途径! 从列数据中提取字符,使用的是"提取"命令.该命令出现在 ... 混杂数据分列及动态统计 原创作者 | 李锐 微信公众号 | Excel函数与公式(ID:ExcelLiRui) 个人微信号 | (ID:ExcelLiRui520) 混杂数据分列及动态统计 工作中有时候需要经常从数据源中按要 ... 你不知道的Power Query技巧之标题升降与格式修正 标题的升降 CSV文件还有一个"兄弟",那就是纯文本"TXT"文件,比如这一次用的示例(素材:03-各种拆.txt).只不过这个示例文件尚未经过"异空 ... Power Query技巧之合并多个规范工作簿中的数据表 若是相同结构的数据源表不在同一工作簿内,而是保存在不同的工作簿呢?遇到这样的问题,表格结构的规范程度不同,解决的方法也不同. 比如像示例文件这样(素材文件夹:07-多簿规范标题)规范的表格,相同结构的 ... Power Query技巧之合并字符串 横向合并 数据源:Sheet1 中的"超级表"命名为"表 1",包含"省级"与"市级"两列数据. 目标:在不改变数据源和 ... Power Query技巧之拆分与扩展 拆分合并单元格并转换为二维表 数据源:Sheet1 表中包含"品名"与"明细"两列数据,"品名"列里包含合并单元格,"明细&quo ... Power Query技巧之合并字符串 横向合并 数据源:Sheet1 中的"超级表"命名为"表 1",包含"省级"与"市级"两列数据. 目标:在不改变数据源和 ... Power Query技巧之计算带文本的数据 计算文本型数字 数据源:Sheet1 中的"超级表"命名为"表 1",包含"品名"和"数量"两列,"数量&qu ... Excel教程:Power Query,万能的批量数据替换技巧! 每天一点小技能 职场打怪不得怂 编按:说到Excel的替换操作,大家首先想到的一定是SUBSTITUTE和REPLACE函数.可是,今天需要处理的替换问题,这两个函数也束手无策,那要怎么做呢?下面,小 ... 杂乱文本中提取信息,ctrl+E还没有智能,power query一个函数搞定 杂乱文本中提取信息,ctrl+E还没有智能到随心所欲,而且,实际工作中,这种操作往往不仅仅是为了提取信息,而是希望提取出来后跟其他的内容一起进行相关的分析. 所以更推荐使用新功能power query ... 怎么隔列删除/隐藏数据?Excel或Power Query里,这样做非常快! 小勤:怎么隔列删除/隐藏数据? 大海:在Excel里的话其实比较简单,比如先加个辅助行(隔列加一些标志信息),定位(快捷键Ctrl+G)到相应的标志信息,然后就可以删除或隐藏了.如下动画所示: 小勤: ...