设置
  • 日夜间
    随系统
    浅色
    深色
  • 主题色

公式太难,手动太慢,这才是 NO.1 的 Excel 整理工具

秋叶Excel 2022/10/8 18:33:04 责编:云熙

每年公司的行政人事部,都会对公司的活动经费进行规划,以及预算审核。

不同部门,在不同月份,活动经费的数额可能不同。

活动经费是指举办活动所需要各种开销的费用。例如组织参加活动的人员经费、场所经费、设备经费等所有针对活动的费用。

为了方便登记、查看、以及打印,他们会将表格制作成如下图的样子。

这样看起来比较直观,部分表哥表姐也喜欢把表格做成这种样式。

但是如果需要更快速地分析活动经费的分配情况,将其整理为一维表的格式,然后利用数据透视表分析,可能更加合适。

那如何整理上述数据呢?

如果你是 Office365 的话,可以直接使用 Vstack 函数,将不同的数据区域,按照竖直方向进行拼接。

=VSTACK一月,二月,三月,四月,五月,六月,七月八月,九月,十月,十一月,十二月)

注:对应区域事先做好了区域命名。

如果你不是 Office365,直接使用传统 Excel 函数做法,很难做出来。这时,就需要用到数据整理的利器 ——PoweQuery

下面我来简单介绍一下它的做法。

温馨提示:本文会涉及几个简单的 M 函数,不会 M 函数的小伙伴也不用担心,大致有个印象就可以。

PQ 需要 Office2016 及以上版本。M 函数是 PQ 中所使用的函数。

1、具体操作

我们事先进行预处理操作,导入表,筛选去掉列中的表头和 null(空)值。

❶ 将数据导入到 PQ 编辑器中。

全选数据区域,在【数据】选项卡中,选择【来自表格 / 区域】-创建表-【确定】,进入 PQ 编辑器。

❷ 单击部门的筛选下三角,取消勾选【部门】和【null】,单击【确定】按钮。

对于多区域的表,利用 PowerQuery,我们可以按照多种形式进行合并,下面我介绍的做法是按照每一行的的方式,进行合并。

接下来,我们进行表格转换处理。

第一步:将行转 List

利用 Table.ToList,将表中的每一行形成 List。

Table.ToList:将表按行方向形成 List

=Table.ToList表,each _)将表中的每一行形成列表,each _ 可以对每一行进行进一步操作。

以下图为例,公式返回的结果中,列表中的每一行,就是表中每行所对应的数据。

第二步:移除每一行中的 null 值

List.RemoveNulls:移除列表中的 null

=List.RemoveNulls列表) 将列表中的 null 值删除掉

第三步:拆分每一个 list

我们可以看到每一个 List 之间,三个数据为一组。

所以我们直接利用 List.Split 函数将数据进行拆分处理。

List.Split:列表拆分

= List.Split列表,每次拆几个)

Split 是分开的意思,List.Split 的意思就是将列表按照每 N 个拆开,形成单独的 List。

第四步:转表

将每个 List 按行进行转表,这里我们用 Table.FromRows 函数(也可以用 Table.FromList)。

Table.FromRows:将 list 形成的列表转换为行方向的表。

=Table.FromRows(list 形成的列表,转换为表对应的字段)若第二参数未写,默认的表标题为 Column1,Columns2……

那我们需要的表标题,可以怎么样获取呢?

获取标题行,我们可以先用 Table.ColumnNames 获取标题的 List,然后 List.FirstN 取前三个标题。

Table.ColumnNames:获取表中的标题

=Table.ColumnNames表)可以获取表中的所有标题,返回一个 List

List.FirstN:获取列表中的前 N 个

=List.FirstN列表,前 N 个)

我们将写好的标题函数贴在,Table.FromRows 函数的第二参数上,此时就已经完成拆分后的转换,最后进行表合并即可。

第五步:合并

最后利用 Table.Combine 进行合并,到这里就完成了。

= Table.Combine(     Table.ToList筛选的行,                  each Table.FromRows(List.Split(List.RemoveNulls(_),3),                                      List.FirstN(Table.ColumnNames(源),3))))

Table.Combine:将列表中的多个 Table 表进行合并

= Table.Combine多个 Table 形成的 List)

将处理好的数据上传到表。

2、延伸拓展

上面讲的是按照每一行进行转换合并,那么按照每一列进行合并,这应该怎么做?

其实跟前面也是一样的思路,只不过使用的函数稍微有点变化。

如下图:

❶ 将表中每一列转换列表 (Table.ToColumns),

❷ 移除 null 值 (List.Select),

❸ 每 3 列进行拆分(List.Split),

❹ 列表循环 (List.Transform),按列转表 (Table.FromColumns),

❺ 最后合并 (Table.Combine)

= Table.Combine(   List.Transform(      List.Split(          List.Select(Table.ToColumns筛选的行),                      each _{0}<>null),      3),   each Table.FromColumns_,List.FirstN(Table.ColumnNames(源),3))))

3、写在最后

本文讲解的是,将间隔相同的多区域表,进行数据合并。

手动复制粘贴的做法可以是可以,但是数据更改后,无法自动更新。

利用 Office365 中新出的 Vstack 函数,我们可以将多个区域直接进行竖直方向的拼接。但是由于版本限制,大多数人是没有这个函数的。

数据整理,最常用的利器就是 PowerQuery。利用它,基础的界面操作就可以完成很多整理工作,但是稍微复杂一点点的,就得需要一丢丢 M 函数。

对于没有 M 函数基础的小伙伴,本文可能看得有点吃力,但是如果我们的数据规范的话,其实也并不需要搞这些。

所以大家平时最好规范一下数据的记录,这样就可以减少很多不必要的工作啦٩(๑・̀ω・́๑)۶。

本文来自微信公众号:秋叶 Excel (ID:excel100),作者:小爽,编辑:竺兰

广告声明:文内含有的对外跳转链接(包括不限于超链接、二维码、口令等形式),用于传递更多信息,节省甄选时间,结果仅供参考,IT之家所有文章均包含本声明。

相关文章

关键词:数据

软媒旗下网站: IT之家 最会买 - 返利返现优惠券 iPhone之家 Win7之家 Win10之家 Win11之家

软媒旗下软件: 软媒手机APP应用 魔方 最会买 要知