PowerQuery中如何实现Excel的datedif函数功能?

Excel学习世界 2024-09-19 21:49:51

Excel 中有一个隐藏函数 datedif,经常用来计算两个日期之间相差的日子,这个日子可以是各种单位。

有同学提问:Power Query 是否有类似 datedif 的函数?Excel函数(五) – 用 datedif 写情书

那么就来看看今天的案例。

案例:

计算下图 1 中的每个日期与 2025 年 12 月 31 日相差的年数、月数和天数。

效果如下图 2 所示。

解决方案:

如果用 datedif 函数,这个案例很容易。

1. 年:选中 C2:C31 区域 --> 输入以下公式 --> 按 Ctrl+Enter:

=DATEDIF(B2,"2025/12/31","y")

datedif 函数释义在这里就不展开了,具体详解大家可以参阅之前的推文。

2. 月:选中 D2:D31 区域 --> 输入以下公式 --> 按 Ctrl+Enter:

=DATEDIF(B2,"2025/12/31","m")

3. 日:选中 E2:E31 区域 --> 输入以下公式 --> 按 Ctrl+Enter:

=DATEDIF(B2,"2025/12/31","d")

接下来回到今天的案例:在 PQ 中有没有上述公式?

1. 选中数据表的任意单元格 --> 选择工具栏的“数据”-->“从表格”

2. 在弹出的对话框中保留默认设置 --> 点击“确定”

表格已上传至 Power Query。

3. 点击“日期”左侧的格式按钮 --> 在弹出的的菜单中选择“日期”

4. 在弹出的对话框中点击“替换当前转换”。

5. 选择工具栏的“添加列”-->“自定义列”

6. 在弹出的对话框中输入以下公式 --> 点击“确定”:

#date(2025,12,31)

#date 的作用是生成一个日期。

7. 再次选择工具栏的“添加列”-->“自定义列”

接下来我们先来计算相隔的天数,因为这个比较简单,有个现成的 M 函数。

8. 在弹出的对话框中将列名设置为“日”--> 输入以下公式 --> 点击“确定”:

Duration.TotalDays([自定义]-[日期])

公式释义:

计算参数中两个日期相减以后,所相差的天数。

接下来计算年和月,这回没有专门的函数了,需要自己设置计算公式。

9. 选择“添加列”-->“自定义列”

10. 在弹出的对话框中将列名设置为“年”--> 输入以下公式 --> 点击“确定”:

Date.Year([自定义])-Date.Year([日期])

公式释义:

这个公式的作用就是用 Date.Year 函数分别将两个日期中的年份提取出来,再相减,得出差值

11. 再一次选择“添加列”-->“自定义列”

12. 在弹出的对话框中将列名设置为“月”--> 输入以下公式 --> 点击“确定”:

(Date.Year([自定义])-Date.Year([日期]))*12+Date.Month([自定义])-Date.Month([日期])

公式释义:

(Date.Year([自定义])-Date.Year([日期]))*12:计算两个日期相差的年份,并乘以 12,转换成月份;

Date.Month([自定义])-Date.Month([日期]):提取出两个日期的月份,并计算差值;

将上述两个数值相加,就是日期之间差异的月份数

13. 删除前三列。

14. 将“日”拖动到最后一列。

15. 选择工具栏的“主页”-->“关闭并上载”-->“关闭并上载至”

16. 在弹出的对话框中选择“表”--> 选择“现有工作表”及所需上载至的位置 --> 点击“加载”

右侧的绿色表格中的结果就跟 datedif 计算出来的完全一样。

0 阅读:1

Excel学习世界

简介:Excel 学习交流