Google Spreadsheets将Matrix数据转换为关系行
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.