Google 表格 - 转置 &询问 [英] Google Sheets - Transpose & Query

问题描述

我们有一个相对较大的数据集，当前格式"大约有 26K 行和 24 列.以下.但是，我们正在尝试将其重新格式化为下面所需的格式.我认为我们可能只需要转置和查询来实现这一点，但我们对任何实现它的方法持开放态度.

We have a relatively large data set, with roughly 26K rows and 24 columns in the "current format" below. However, we are trying to reformat it to the desired format below. I think we may need but Transpose and Query to achieve this but we are open to any method of reaching it.

关于如何重新格式化的任何建议将不胜感激.

Any suggestions on how to reformat this would be greatly appreciated.

推荐答案

JAK，即需要处理大量数据.并且在不访问实际工作表和数据的情况下尝试编写公式总是很困难的.也就是说，SPLIT/JOIN 组合将超过这么多数据的字符限制.但理论上，以下公式是可行的(假设您的实际数据是您的示例的扩展并从 A1:Y 运行.

JAK, that is a lot of data to process. And it is always difficult to try to write formulas without access to the actual sheet and data. That said, SPLIT/JOIN combos would exceed character limits with that much data. But in theory, the following formula would work (assuming your actual data is an extension of your example and runs from A1:Y.

=ArrayFormula({"ID","Value","Qty"; QUERY({VLOOKUP(MOD(SEQUENCE(COUNTA(A:A)*COUNTA(A1:Y1),1)-1,COUNTA(A:A))+2,{ROW(A:A),A:A},2),HLOOKUP(ROUNDUP(SEQUENCE(COUNTA(A:A)*COUNTA(A1:Y1),1)/COUNTA(A:A))+1,{COLUMN(A1:Y1);A1:Y1},2),
VLOOKUP(MOD(SEQUENCE(COUNTA(A:A)*COUNTA(A1:Y1),1,2)-2,COUNTA(A:A))+2,{ROW(A2:A),B2:Y},ROUNDUP(SEQUENCE(COUNTA(A:A)*COUNTA(A1:Y1),1)/COUNTA(A:A))+1)
},"SELECT Col1, Col2, Col3 WHERE Col3 IS NOT NULL ORDER BY Col1 Asc")})

如果您想在单独的工作表中进行设置，则需要编辑整个公式中的每个范围，以包括原始数据所在工作表的名称.

If you're wanting to set this up in a separate sheet, you'll need to edit every range throughout the formula to include the name of the sheet where the original data resides.

有关此公式的更多基础和解释:https://www.benlcollins.com/spreadsheets/unpivot-in-google-sheets

For more on the basis and explanation for this formula: https://www.benlcollins.com/spreadsheets/unpivot-in-google-sheets

这篇关于Google 表格 - 转置 &询问的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！