excel - 在 Excel 中的两个日期之间的每一行中生成日期
问题描述
对于下面的示例,我想为每个产品生成与“Date 2”和“Date 1”之间的日期一样多的行,并在第四列中包含“Date 2”和“Date 1”之间的每个唯一日期行。
这是输入
这是所需的输出
到目前为止,我已将以下公式插入 D2(日期列)。这将生成从“Date In”到“Date Out”的所需日期值。但是,要使此公式起作用,应将每个产品编号行复制到 (Date Out-Date In+1) 的总计数。这就是我卡住的地方。
=IF(A1<>A2;B2;D1+1)
请注意,原始数据中有数百个具有不同日期值的产品代码。
提前致谢!
解决方案
这在 PowerQuery aka Get&Transform 中很容易做到。
选择您的数据,在功能区上的“数据”下,选择“从表/范围”并导入到 PQ(包括标题)。
在“添加列”选项卡上,单击“自定义列”并使用以下公式:
={Number.From([Date In])..Number.From([Date Out])}
在新添加的列上,单击顶部显示两个箭头的小按钮,然后选择“扩展到新行”。
将列的数据类型更改为日期。右键单击列的标题,选择更改类型并选择“日期”。
如果需要,将“日期输入”和“日期输出”的类型更改回“日期”(如果它们更改为“日期\时间”)。
关闭 PQ 并保存您的结果。
就这么简单,如果您有完整的带有“日期输入”和“日期输出”的产品列表,它会很好地工作。
快速解释公式:
- 要在 PQ 中创建一个列表,我们使用
{..}
. - 要在我们使用的数字之间创建一个列表
{1..10}
。 - 我们可以使用变量而不是数字:
{[Date In]..[Date Out]}
. - PQ 不需要日期,但需要它们的数值才能工作,因此我们可以
Number.From
在最终公式中使用:{Number.From([Date In])..Number.From([Date Out])}
.
您会注意到您的结果数据位于不同的工作表中,并且您的源数据已转换为适当的表格。一个很酷的功能是,一旦您将数据添加到此源(新行),并在“数据”选项卡上点击“全部刷新”,它将自动调整您的结果。正如下面的评论中提到的,也可以只刷新结果表本身:
此外,如果您有 Excel O365 并且碰巧想要创建一个日期数组,Date In
您Date Out
可以使用SEQUENCE()
:
=SEQUENCE(C2-B2+1,,B2)
推荐阅读
- redis - Redis 映像目前是否可以在 Windows 上与 Docker 一起使用?
- installation - Pdf2Html 安装
- android - Android MMS Content Provider 不存储事务 ID
- c++ - 对没有数组的输出数字进行排序
- java - 如何确定通过brew服务运行的Java elasticsearch
- javascript - 将 json 数据从 javascript 发送到 python 时发生内部服务器错误
- android - 如何将 firebase firestore 用于我的桌面应用程序?
- android - 无法将不同的 Drawable 设置为 recyclerview 项目
- angular - Angular 6 子组件修改父输入,即使它不是双向绑定
- python - BeautifulSoup attrs 返回列表而不是字典