Google Spreadsheets将Matrix数据转换为关系行 [英] Google Spreadsheets Convert Matrix data into Relational rows

问题描述

我的Google电子表格数据是带有孔"的矩阵形式.请参见

My Google spreadsheet data is in a matrix form with "holes". See https://docs.google.com/spreadsheet/ccc?key=0Ati5T34RP9mEdGJfcUFzVDV4elZIbExkTGM4aktQT2c&usp=drive_web#gid=0

我想根据上述数据创建另一个工作表，以使其看起来像我在

I'd like to create another sheet from the above data to look like what I've manually done in https://docs.google.com/spreadsheet/ccc?key=0Ati5T34RP9mEdGJfcUFzVDV4elZIbExkTGM4aktQT2c&usp=drive_web#gid=3

有什么想法吗?

推荐答案

这应该适用于最新版的表格:

This should work on the latest version of Sheets:

=ArrayFormula(QUERY(VLOOKUP(HLOOKUP('Table Chart'!A2,'Table Chart'!A2:A,INT((ROW('Table Chart'!A2:A)-ROW('Table Chart'!A2))/COLUMNS('Table Chart'!B1:G1))+1,0),{'Table Chart'!A2:G,IF(ROW('Table Chart'!A2:A),'Table Chart'!B1:G1)},{SIGN(ROW('Table Chart'!A2:A)),MOD(ROW('Table Chart'!A2:A)-ROW('Table Chart'!A2),COLUMNS('Table Chart'!B1:G1))+{2,2+COLUMNS('Table Chart'!B1:G1)}},0),"select Col1, Col3, Col2 where Col2 is not null",0))

为了完整起见，这将在以前的版本中起作用，但是日期会转换为文本字符串；可以将它们转换为数值，但是公式将变得更长:

For the sake of completeness, this will work on the previous version, but the dates are converted into text strings; they can be converted into numerical values, but the formula would then become even longer:

=ArrayFormula(REGEXREPLACE(TRANSPOSE(SPLIT(CONCATENATE(REPT('Table Chart'!A2:A&CHAR(9)&'Table Chart'!B1:G1&CHAR(9)&TEXT('Table Chart'!B2:G,"d-MMM-yyyy")&CHAR(10),LEN('Table Chart'!B2:G)>0)),CHAR(10))),"^(.+?)\t(.+?)\t(.+)$",{"$1","$2","$3"}))

尽管可以调用上述公式，然后仅将其转换为值，但Google Apps脚本将为您提供一次性运行"解决方案，该解决方案比一直在重新计算的公式更好(IMO).但是，您可能需要它们动态更新.

Google Apps Script will give you a "run once" solution which will be better (IMO) than a formula that is recalculating all the time, although the above formulae could be invoked and then just converted to values only. However, you might need them to update dynamically.

如果您想要GAS解决方案，则可以在其中添加google-apps-script标记以邀请其他答案.

If you wanted a GAS solution, maybe add the google-apps-script tag in to invite other answers.

这篇关于Google Spreadsheets将Matrix数据转换为关系行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！