英文:
Google Sheets formula to transpose each non-blank row and column combination into unique rows
问题
在Google表格中，我有一组数据，其中列A中有ID，列B及以后是与日期相关的，第1行是日期，第2行及以后是该ID和日期组合的数值。
例如：
| ID | 2023-01-01 | 2023-01-02 | 2023-01-03 | 2023-01-04 | 2023-01-05 | 2023-01-06 | 2023-01-07 |
|---|---|---|---|---|---|---|---|
| 100001 | 9 | 10 | 11 | 3 | 12 | 3 | |
| 100002 | 7 | 11 | 11 | 18 | 16 | 12 | |
| 100003 | 3 | 20 | 8 | 7 | 11 | 4 | |
| 100004 | 7 | 19 | 12 | 4 | 12 | ||
| 100005 | 8 | 17 | 3 | 5 | 4 | 16 | 16 |
我想在第二张表中创建一个公式，以将这些数据过滤并转换为以下格式，以便每个ID和日期组合都是一个独立的行，ID在第1列，日期在第2列，数值在第3列，跳过数值为空的行，就像这样：
| ID | 日期 | 值 |
|---|---|---|
| 100001 | 2023-01-01 | 9 |
| 100001 | 2023-01-02 | 10 |
| 100001 | 2023-01-04 | 11 |
| 100001 | 2023-01-05 | 3 |
| 100001 | 2023-01-06 | 12 |
| 100001 | 2023-01-07 | 3 |
| 100002 | 2023-01-01 | 7 |
| 100002 | 2023-01-02 | 11 |
| 100002 | 2023-01-03 | 11 |
| 100002 | 2023-01-04 | 18 |
| 100002 | 2023-01-06 | 16 |
| 100002 | 2023-01-07 | 12 |
我以前做过这个，但我真的想不起来了，而且我已经搜索了很多但找不到解决方案。
我认为它需要一个带有拆分和转置的数组公式。
非常感谢任何帮助！
英文:
In Google Sheets I have a set of data with IDs in Column A and Columns B onwards are date related with Row 1 being the date and Rows 2 onwards being a number value for that ID and date combination.
For example:
| ID | 2023-01-01 | 2023-01-02 | 2023-01-03 | 2023-01-04 | 2023-01-05 | 2023-01-06 | 2023-01-07 |
|---|---|---|---|---|---|---|---|
| 100001 | 9 | 10 | 11 | 3 | 12 | 3 | |
| 100002 | 7 | 11 | 11 | 18 | 16 | 12 | |
| 100003 | 3 | 20 | 8 | 7 | 11 | 4 | |
| 100004 | 7 | 19 | 12 | 4 | 12 | ||
| 100005 | 8 | 17 | 3 | 5 | 4 | 16 | 16 |
I would like to create a formula in a second sheet to filter and convert this data into the following format so that each ID and date combination is an individual row with the ID in column 1, the date in column 2, and the number value in column 3, skipping the rows where the number value is blank, like this:
| ID | Date | Value |
|---|---|---|
| 100001 | 2023-01-01 | 9 |
| 100001 | 2023-01-02 | 10 |
| 100001 | 2023-01-04 | 11 |
| 100001 | 2023-01-05 | 3 |
| 100001 | 2023-01-06 | 12 |
| 100001 | 2023-01-07 | 3 |
| 100002 | 2023-01-01 | 7 |
| 100002 | 2023-01-02 | 11 |
| 100002 | 2023-01-03 | 11 |
| 100002 | 2023-01-04 | 18 |
| 100002 | 2023-01-06 | 16 |
| 100002 | 2023-01-07 | 12 |
I've created an example sheet with some sample data in the input and desired output formats here: https://docs.google.com/spreadsheets/d/1qtKCjFhxCqjpiOK0DnOWjptq_UYLTXlfS5cXM0wiCrw/edit#gid=0
I have achieved this before but for the life of me I cannot remember how, and I have searched a fair bit but can't find a solution.
I believe it requires an array formula with split and transpose.
Any help would be greatly appreciated!
答案1
得分: 0
你可以尝试：
=let(Σ,reduce({"ID","Date","Value"},sequence(counta('源数据'!A2:A)),lambda(a,c,{a;
reduce(wraprows(,3,),sequence(counta('源数据'!B1:1)),lambda(x,y,{x;index('源数据'!A2:A,c),index('源数据'!B1:1,y),index('源数据'!B2:1000,c,y)}))})),
filter(Σ,index(Σ,,3)<>""))
英文:
You may try:
=let(Σ,reduce({"ID","Date","Value"},sequence(counta('Source Data'!A2:A)),lambda(a,c,{a;
reduce(wraprows(,3,),sequence(counta('Source Data'!B1:1)),lambda(x,y,{x;index('Source Data'!A2:A,c),index('Source Data'!B1:1,y),index('Source Data'!B2:1000,c,y)}))})),
filter(Σ,index(Σ,,3)<>""))
答案2
得分: 0
Here's the translated content:
=ARRAYFORMULA(
let(
ids, tocol('Source Data'!A2:A, 1),
dates, torow('Source Data'!B1:1, 1),
values, 'Source Data'!B2:INDEX('Source Data'!B2:ZZZ, COUNTA(ids), COUNTA(dates)),
table, SPLIT(tocol(ids&"|"&dates&"|"&values), "|"),
headers, {"ID", "Date", "Value"},
query({headers; table}, "where Col3 is not null format Col2 'yyyy-mm-dd'", 1)
)
)
Note: This is a formula in Excel or Google Sheets that uses ARRAYFORMULA, SPLIT, and QUERY functions to manipulate data in a sheet named 'Source Data'.
英文:
An approach using ARRAYFORMULA & SPLIT, if you are interested:
=arrayformula(let(
ids,tocol('Source Data'!A2:A,1),
dates,torow('Source Data'!B1:1,1),
values,'Source Data'!B2:index('Source Data'!B2:ZZZ,counta(ids),counta(dates)),
table,split(tocol(ids&"|"&dates&"|"&values),"|"),
headers,{"ID","Date","Value"},
query({headers;table},"where Col3 is not null format Col2 'yyyy-mm-dd'",1)))
</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论