如何使用Google Sheets公式将数据矩阵转换为关系表? [英] How to convert a data matrix into a relational table using Google Sheets formula?
本文介绍了如何使用Google Sheets公式将数据矩阵转换为关系表?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我得到了一组矩阵形式的数据,我正在尝试使用一个公式从该矩阵生成关系表。以下是我试图实现的目标:
矩阵:
| 3/5 | 4/5 | 5/5 |
---------------------------
Player1 | 1 | 0 | 1 |
Player2 | 0 | 1 | 2 |
Player3 | 1 | 1 | 2 |
目标:
Player | Date | Value |
------------------------
Player1 | 3/5 | 1 |
Player3 | 3/5 | 1 |
Player2 | 4/5 | 1 |
Player3 | 4/5 | 1 |
Player1 | 5/5 | 1 |
Player2 | 5/5 | 2 |
Player3 | 5/5 | 2 |
点击此处查看真实数据: https://docs.google.com/spreadsheets/d/1DNaoBnYMNbznUoAJahW9-UfGq6LbTG3TXgXPz90bWas
不管怎样,我按照这个answer中的说明得出了这个公式:
=ArrayFormula(QUERY(VLOOKUP(HLOOKUP(matrix!A3,matrix!A3:A,INT((ROW(matrix!A3:A)-
ROW(matrix!A3))/COLUMNS(matrix!B1:Z1))+1,0),{matrix!A3:T, IF(ROW(matrix!A3:A),
matrix!B1:Z1)}, {SIGN(ROW(matrix!A3:A)), MOD(ROW(matrix!A3:A) -
ROW(matrix!A3), COLUMNS(matrix!B1:Z1)) + {2, 2+COLUMNS(matrix!B1:Z1)} }, 0 ),
"select Col1, Col3, Col2 where Col2 is not null and Col2 != 0 order by
Col3,Col2", 0))`
它在某种程度上是可行的,但缺点是,为了生成行,它在矩阵工作表中使用了大量空行,并且实际上要求矩阵工作表至少有numCols*numRow的行数。现在,我只需在矩阵中添加3500个空行就可以了,但不知何故,我觉得应该有更好的方法。对如何改进有什么建议吗?
推荐答案
=ARRAYFORMULA({"Player", "Date", "Value";
SORT(SPLIT(TRANSPOSE(SPLIT(TRIM(QUERY(TRANSPOSE(QUERY(TRANSPOSE(
IF(Sheet1!B2:Z<>"","♠"&Sheet1!A2:A&"♦"&Sheet1!B1:1&"♦"&Sheet1!B2:Z,)),,999^99)),,999^99)),
"♠")), "♦"), 2, 1, 1, 1)})
这篇关于如何使用Google Sheets公式将数据矩阵转换为关系表?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文