![使用公式转置具有集合结构的大型数据集](https://linux22.com/image/1526549/%E4%BD%BF%E7%94%A8%E5%85%AC%E5%BC%8F%E8%BD%AC%E7%BD%AE%E5%85%B7%E6%9C%89%E9%9B%86%E5%90%88%E7%BB%93%E6%9E%84%E7%9A%84%E5%A4%A7%E5%9E%8B%E6%95%B0%E6%8D%AE%E9%9B%86.png)
我正在尝试找到一种以某种方式转置大型数据集(结构如下所示)的方法,并想知道如何使用公式在 Excel 中实现它,以避免耗时且容易出错的复制粘贴转置!
sheet A
我想在单独的工作表 ( )上进行数据转换B
。我基本上对不同组的多个特征进行了三次测量。组数可以变化,但测量结果始终是三次。感谢您提供的任何帮助
答案1
您可以尝试一下INDEX
、COLUMN
和ROW
公式以及一些算术。INDEX
将根据行号和列号为您查找单元格的值。对于简单的转置,您只需要将COLUMN
当前单元格的作为行,将ROW
当前单元格的作为列。但是,您的操作比简单的转置稍微困难一些,因此我们需要一些算术。经过反复试验,我得出了以下公式。
假设工作表 A 包含左上角的数据(即 B2 包含值 5),则工作表 B 上的以下公式将起作用:
=INDEX('Sheet A'!$1:$1048576, MOD(COLUMN()+1,3)+3*ROW()-4, 1+(COLUMN()+1)/3)
列号从 1 开始,因此MOD(COLUMN()+1,3)
该组为 0nA 列,1 为 B,2 为 C。该3*ROW()
部分对应每个组的三行n在工作表 A 中。1+(COLUMN()+1)/3
将工作表 B 中的所有第 1 类列映射到工作表 A 中的第 2 列,将第 2 类列映射到工作表 A 中的第 3 列,等等。