使用公式进行转换的行复制 [英] Row copy with transformation using formulas

问题描述

示例工作表已链接.

将表格另存为

fromDate    toDate      Data
2018-01-01  2018-01-05  A
2018-02-01  2018-02-03  B
... so on

需要更改/转换为以下格式，其中日期范围中的每一天都已转换为一行.

Need to change/transform to the following form where each day in date range has been converted to a row.

Date        Data
2018-01-01  A
2018-01-02  A
2018-01-03  A
2018-01-04  A
2018-01-05  A
2018-02-01  B
2018-02-02  B
2018-02-03  B
... so on

可以使用公式吗?

编辑1;添加了表格定制功能来完成相同的任务.再次，可以使用内置公式来做到这一点吗?

EDIT 1 ; added Sheets custom function to accomplish the same. again, could this be done using built-in formulae?

function DateRangeToRows(values) {
  var new_values=[];
  for (var r=0; r<values.length; r++) {
    var stt=values[r][0], end=values[r][1];
    while (stt<=end) {
      new_values.push([new Date(stt)].concat(values[r].slice(2)));
      stt.setDate(stt.getDate()+1);
    }
  }
  return new_values;
}

推荐答案

这个想法是建立一个2d数组，其中每一行都是日期范围内的不同日期，然后将其拆分为单独的行，但此刻我为了进行测试，我将日期范围的最大长度硬编码为10

The idea is to build a 2d array where each row is the different days in the date range, then split it out into separate rows, but at the moment I'm hard-coding the maximum length of the date range as 10 just to test it

=ArrayFormula(split(transpose(split(textjoin("#",true,if((transpose(row(1:10))+A2:A3-1)>B2:B3,"",transpose(row(1:10))+A2:A3-1&"|"&C2:C3)),"#")),"|"))

下一步将使用间接方法将数据中的最大日期范围替换为1:10，这将导致以下情况:

The next step will be to replace the 1:10 with the maximum date range from the data, using Indirect which leads to this:

=ArrayFormula(split(transpose(split(textjoin("#",true,if((transpose(row(indirect("1:"&max(B2:B3-A2:A3)+1)))+A2:A3-1)>B2:B3,"",transpose(row(indirect("1:"&max(B2:B3-A2:A3)+1)))+A2:A3-1&"|"&C2:C3)),"#")),"|"))

无法更改对全列的引用(A2:A等)，因为它需要工作表具有额外的行，但是您可以选择任意数量的行并仅选择使用的行:

It isn't possible to change the references to full columns (A2:A etc) because it would need the sheet to have extra rows, but you can choose an arbitrary number of rows and select only used rows:

=ArrayFormula(query(split(transpose(split(textjoin("#",true,if((transpose(row(indirect("1:"&max(B2:B10-A2:A10)+1)))+A2:A10-1)>B2:B10,"",transpose(row(indirect("1:"&max(B2:B10-A2:A10)+1)))+A2:A10-1&"|"&C2:C10)),"#")),"|"),"select * where Col1>0"))

这篇关于使用公式进行转换的行复制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！