重新格式化“日期/重量"数组图表以使其成为“年龄/体重"图表 [英] Reformatting the array of a "Date/Weight" chart to make it an "Age/Weight" chart

问题描述

电子表格

我有一个电子表格，可以列出并绘制跨不同日期的权重.我已经将其扩展为一个数组，该数组在扩展的Date列上绘制原始的Data工作表值-在Chart 2工作表上可以看到.

I have a spreadsheet that tabulates and charts weights that were taken across various dates. I have expanded that to an array that plots the original Data sheet values on an expanded Date column - as can be seen on the Chart 2 sheet.

我现在要在Chart 3上执行的操作是将相同的重量数据绘制到一个数组中，该数组在几天之内将替换Date列，该数组具有Age-可以看到.我以为这是一个简单的过程，但我无法弄清楚.

What I am wanting to do now on Chart 3 is plot the same weight data into an array that has Age in days replacing the Date column - as can be seen. I had thought this was a simple process, but I cannot figure it out.

查看天使"列，我确定她的数据的偏移量是609-意味着单元格T609应该包含单元格H4中的数据，因为她在该称重当天已满675天.我有一个误解，认为我可以将OFFSET列H放在605行下，但是我发现OFFSET函数的工作方式并非如此. :(

Looking at the column for "Angel" I have determined that the offset for her data is 609 - meaning that cell T609 should contain the data in cell H4 as she was 675 days old on the day of that weighing. I had a misunderstanding that I could OFFSET column H down 605 rows, but I've discovered that's not how the OFFSET function works. :(

推荐答案

该解决方案能够处理正负偏移量

=IF($G$4-VLOOKUP(T2,$B$2:$C$24,2,0)>0,{ARRAYFORMULA(REGEXREPLACE(TRANSPOSE(SPLIT(REPT("♂♀", 
 (INDIRECT(ADDRESS(MATCH(INDEX(H4:H,MATCH(TRUE,INDEX((H4:H<>0),0),0)),H:H,0),7,4))-
 VLOOKUP(T2,$B$2:$C$24,2,0))-4-66), "♂")), "♀", "")); H4:H}, 
 {ARRAYFORMULA(REGEXREPLACE(TRANSPOSE(SPLIT(REPT("♂♀", 
 MATCH(INDIRECT(ADDRESS(MATCH(INDEX(H4:H,MATCH(TRUE,INDEX((H4:H<>0),0),0)),H:H,0),7,4))-
 VLOOKUP(T2,$B$2:$C$24,2,0),$S:$S,0)-4), "♂")), "♀", "")); 
 QUERY(INDIRECT(ADDRESS(MATCH(INDEX(H4:H,MATCH(TRUE,INDEX((H4:H<>0),0),0)),H:H,0),
 COLUMN(H1),4)&":"&SUBSTITUTE(ADDRESS(1,COLUMN(H1),4),1,)))})

这篇关于重新格式化“日期/重量"数组图表以使其成为“年龄/体重"图表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！