☰

将每 n 列堆叠在一起

tag-icon • tag-icon microsoft-excel macros
将每 n 列堆叠在一起

这可能是一个微不足道的问题，但我无法弄清楚。我有一个包含重复列的数据集，我需要找出一种方法将每第 n 列堆叠在一起。因此，如果我们考虑这一点：

原始数据

我们可以看到我们有重复的列，我需要我的数据像这样堆叠在一起：

后

我的重复列是常量，所以我总是会有 5 个重复列。任何帮助都将不胜感激！

答案1

这里需要一个循环公式OFFSET。

设置

因此，我们有一个标题行，并且数据位于 中，假设 。A2:ZZZZ38我们只需将其放在 上Sheet1。我们将在 上设置输出Sheet2。

精确

从概念上讲，我们可以很容易地理解我们想要Sheet2!A2（为了简单起见，我们以后就称这个单元A2格为）重现Sheet1!A2。在中，A3我们想要下一个单元格，依此类推 - 直到我们查看了中的所有记录Sheet1!A，然后我们想要返回到第一行，但超过 5 列。

这显然是一个案例OFFSET。

公式

=OFFSET(Sheet1!A$2,MOD(ROW()-2,COUNTA(Sheet1!A:A)-1),ROUNDDOWN((ROW()-2)/(COUNTA(Sheet1!A:A)-1),0)*5)

演示

我的数据比你的小很多；但足以证明概念。

这里是Sheet1：

在此处输入图片描述

输出Sheet2：

在此处输入图片描述

解释

OFFSET有三个部分。从哪里开始，向下移动多少行（垂直偏移），以及移动多少列（水平偏移）。

A-从哪里开始

Sheet1!A$2

非常简单。

B - 行偏移

MOD(ROW()-2,COUNTA(Sheet1!A:A)-1)

找出我们在哪一行并减去 2（我们从 开始A2，我们希望A2它是零偏移量）。将其除以我们要计数的记录数（此处表示显示的标题行），然后取余数。这只会生成一个从到的-1函数0[n-1]n是行数，然后从 0 重新开始。

换句话说，对于包含 8 条记录的表，将返回：

0, 1, 2, 3, 4, 5, 6, 7, 0, 1, 2, 3, 4, 5, 6, 7, 0 ...

C - 列偏移

ROUNDDOWN((ROW()-2)/(COUNTA(Sheet1!A:A)-1),0)*5

再次取值ROW()-2，并再次除以记录数 - 但这次MOD我们不是取该关系的余数，而是将其四舍五入为最接近的整数。这为我们提供了一个序列，该序列由等于记录数的 0 组成，后面跟着 1、2 等。然后将其乘以 5（参见下面的注释）。这提供了我们的列偏移量 - 每次循环行数重置时，我们都会跳过另外 5 列。

换句话说，对于包含 8 条记录的表，将返回：

0, 0, 0, 0, 0, 0, 0, 0, 5, 5, 5, 5, 5, 5, 5, 5, 10 ...

再次重申公式：

=OFFSET(Sheet1!A$2,MOD(ROW()-2,COUNTA(Sheet1!A:A)-1),ROUNDDOWN((ROW()-2)/(COUNTA(Sheet1!A:A)-1),0)*5)

这将不受限制地进入和向下Sheet2!A2复制A:E。

注意：

  1. 5 是硬编码的，因为您声明了数据的属性，即列重复 5 次。如果需要，可以将其扩展为动态函数（或者，当然，如果重复列的数量固定且不是 5，则只需更改即可）。这可以留到以后再问。
  2. 尽管文件很大，但这个公式应该不会花费任何时间来处理。它确实包含整个列引用，但在里面，COUNTA我不知道思考这会减慢您的速度。如果确实如此，您可以轻松将每个实例更改为，COUNTA(Sheet1!A:A)因为COUNTA(Sheet1!A2:A38)从技术上讲我们已经知道数据的大小，甚至可以简单地将该值硬编码为 37，只要您恰好有 37 行，这种方法就可以奏效。
  3. 我花了一半的时间才把这个问题搞定，这一半时间都是在小心处理算术运算的顺序，并将公式的各个部分分成不同的列，以解决我遇到的问题——原来是缺少一对括号。从本质上讲，这其实很简单；只需要知道它OFFSET存在，然后计算出逻辑/算术，以了解如何生成需要偏移的对序列。

相关内容