Excel一列数据按每十个一组拆成多列：OFFSET公式实战拆解

大家好，我是保哥。这篇文章是我从自己实际工作里抠出来的笔记。我手头那会儿有一份将近两千行的 Excel 表，每行一个会员账号，需要按运营同事的要求每十个账号一行发到对接群里。一开始我用最笨的办法——选十行、复制、粘贴到一行、再回去往下选，几天下来不仅手酸，还出过两次错把同一段数据发了两遍的事故。后来我把这事拆开来研究，最终用一条 OFFSET 公式把它彻底自动化了。下面把过程、原理、变体写法以及踩过的坑都讲清楚，方便你在自己工作里直接套用。

一、问题到底是什么：把竖向一列切成横向多列

先把场景说明白，避免你套用时方向搞反。原始数据是这样的：

A列
A1: 数据1
A2: 数据2
A3: 数据3
...
A2000: 数据2000

目标输出是这样的：

      B列    C列    D列   ...   K列
第1行 数据1  数据11 数据21 ...  数据91
第2行 数据2  数据12 数据22 ...  数据92
...
第10行 数据10 数据20 数据30 ...  数据100

也就是把 A 列按每十个为一组，从上到下读出来后摆成十行 N 列的方阵。读取顺序是先沿着列往下走（先取 A1 到 A10 放到 B1 到 B10），走完一列再跳到下一组（A11 到 A20 放到 C1 到 C10）。

这种摆法在做对账文件、群发账号包、批量打印标签的时候很常见，本质都是「一维线性数据 → 二维网格」的位置映射。理解清楚这层映射，公式就只是把映射写成 Excel 能懂的样子。

二、为什么我最终选了 OFFSET 而不是 INDEX 或 TRANSPOSE

刚开始研究的时候我尝试过几条路：

第一条是 TRANSPOSE。它本身只能做整体转置，把一列变成一行，没法做「每 10 个折一次」的分段，第一个想法直接被否掉。

第二条是 INDEX。INDEX 也能做，写法是 =INDEX($A:$A, ROW(A1)+10*(COLUMN(A1)-1))，逻辑跟 OFFSET 几乎一致，只不过参数顺序不一样。我自己用着觉得 OFFSET 更直观，因为它的语义就是「从 A1 这个锚点出发，往下挪多少行、往右挪多少列」，跟我脑子里的位置映射完全对得上。

第三条是用 Power Query 拆。这条路在 Excel 2016 以上版本是可行的，但对一次性需求来说，开 PQ、加载、写 M 函数、再加载回工作表的步骤太重，没必要。

所以最后我落在 OFFSET 上。这条公式的好处是：单元格里就能看见结果、随便往右拉就能扩展、原数据更新时结果跟着自动刷新。

三、核心公式逐字拆解

这是我最终落地的那条公式，复制到 B1：

=OFFSET($A$1, ROW(A1)-1+10*COLUMN(A1)-10, 0, 1, 1)

看着像一串符号，其实只做一件事：算出当前要从 A 列的第几个位置取数。

OFFSET 的标准语法是 OFFSET(reference, rows, cols, [height], [width])，含义是：从 reference 出发，往下挪 rows 行，往右挪 cols 列，返回一个 height × width 大小的区域。我把每个参数拆开讲：

第一个参数 $A$1 是锚点。两个美元符号是绝对引用，意思是无论这条公式被拖到 B1、B10、还是 K10，这个锚点永远咬死在 A1 不动。这是公式能正确扩展的前提。

第二个参数 ROW(A1)-1+10*COLUMN(A1)-10 是行偏移量，也就是要往下挪多少格。这是整条公式的灵魂，单独再拆一次：

• ROW(A1) 在 B1 里返回 1，在 B2 里返回 2，往下拖一行就加一。-1 是因为 OFFSET 的偏移量是「相对锚点」的，A1 自己的偏移是 0 不是 1。
• COLUMN(A1) 在 B1 里返回 1，把公式往右拖到 C1 就变成 2，再往右是 3。10*COLUMN(A1)-10 等价于 10*(COLUMN(A1)-1)，意思是每往右挪一列，就把行偏移整体抬高 10。
• 两段相加：第一列（B 列）的行偏移是 0、1、2 ... 9，第二列（C 列）是 10、11、12 ... 19，第三列是 20 到 29，正好就是「每十个一组」。

第三个参数是列偏移量，固定写 0，意思是永远只从 A 列取数，不往旁边跑。

第四、五个参数 1, 1 是结果区域的尺寸：高 1 格、宽 1 格，也就是只返回一个单元格。这两个参数其实可以省略，写成 =OFFSET($A$1, ROW(A1)-1+10*COLUMN(A1)-10, 0) 效果一样，我把它写出来纯粹是因为读起来更明显。

四、操作步骤：从空表到二维数据只要四步

下面是我自己实际操作的顺序，你照着做就行：

第一步，把原始数据放到 A 列。从 A1 开始，连续往下，中间不要有空行。如果有空行，OFFSET 会照样返回那个空格，最终结果里也会有断点。

第二步，B1 单元格输入公式：

=OFFSET($A$1, ROW(A1)-1+10*COLUMN(A1)-10, 0, 1, 1)

回车后 B1 应该显示 A1 的值。如果显示的是 0 或者 #REF!，先回头检查锚点是不是 $A$1、A1 是不是真的有数据。

第三步，把 B1 往下拉到 B10。这一列是第一组，应该正好显示 A1 到 A10 的内容。这一步是验证公式是否正确的关键节点，如果这一步对了，后面就只是规模扩大。

第四步，选中 B1:B10 这十个格子，把右下角的填充柄往右拖。要拖多少列，取决于你原始数据的总行数除以 10。两千行就拖到第两百列（也就是拖到 GR 列附近）。拖完之后整个矩阵自动填好。

最后做一步收尾：选中整个 B 列到末尾的结果区域，复制，原地选择性粘贴为「数值」。这一步必须做，否则公式仍然依赖 A 列，把数据剪到别处或者把 A 列删掉，结果就全部 #REF! 报错了。

五、几个常见变体：换分组大小、跨工作表、横向数据

实际工作里需求会有点变化，这里给几个我用过的变体。

变体一：每组 N 个而不是 10 个。 把公式里两个 10 都改成 N。比如每组 20 个就写：

=OFFSET($A$1, ROW(A1)-1+20*COLUMN(A1)-20, 0)

往下拉的时候也要拉到第 20 行而不是第 10 行。这两个数字是一对，必须同步改。

变体二：源数据在另一张工作表。 把锚点替换成跨表引用：

=OFFSET(Sheet1!$A$1, ROW(A1)-1+10*COLUMN(A1)-10, 0)

这样源数据放在 Sheet1，公式可以放到任意一张新表，不会污染源文件。

变体三：源数据在一行里，要折成多行。 把 ROW 和 COLUMN 的角色交换，写成：

=OFFSET($A$1, 0, ROW(A1)-1+10*COLUMN(A1)-10)

这条从 A1 起横着读，每十个折成一行。

变体四：希望结果按「先填行再填列」的顺序排，也就是 B1=A1, C1=A2, D1=A3，每行 10 个。 这种就把行列偏移互换：

=OFFSET($A$1, (ROW(A1)-1)*10+COLUMN(A1)-1, 0)

这条公式拖出来会让 A 列的连续数据按「一行 10 个」横着平铺，是另一种常见摆法。我两种都用过，按运营同事截图时希望看到的方向选就行。

六、踩过的坑和实战建议

这是我用这条公式踩过的坑，列出来供你避雷。

第一个坑是锚点忘了写绝对引用。一旦写成 OFFSET(A1, ...)，往右拖一列后锚点也跟着变成 B1，公式逻辑直接错位，但表面上看不太出来，因为 B 列还是有值，只是值不对。这种错最隐蔽，建议拉完之后随手抽几个格子比对原数据。

第二个坑是数据里有空格或合并单元格。OFFSET 不会跳过空行，合并单元格也只在合并区域的左上角有值、其他位置是空的。处理之前最好先把 A 列做一次「定位空值 → 删除整行」清理，或者把合并单元格全部拆开。

第三个坑是大表格不转数值就直接发出去。两千行的源数据展开后是 200 列的公式区域，每个格子都依赖 A 列，文件体积会膨胀，对方收到打开还得等 Excel 算半天。一定要做选择性粘贴为数值这一步，发出去的文件才会清爽。

第四个坑是数据带前导零或长串数字（像手机号、银行卡号）。OFFSET 取值时会保留原本的格式，但如果原始 A 列被识别成了「数值」，前导零早在录入时就被吞掉了，公式也救不回来。源数据这一列在录入前就要设成「文本」格式。

第五个建议是把这条公式收藏成自己的小工具。我自己在云笔记里专门留了一页放这种 Excel 模板公式，下次再遇到「按 N 个一组折叠」的需求，直接复制一条改两个数字就完事，不用再重新推导。

七、把这套思路推广到「分页打印」「按客户分组」等场景

OFFSET 这条公式背后的能力，本质是把一维下标重映射到二维坐标。理解了这一层，能套用的场景比「每十个一组」要广得多，下面三个是我自己在不同项目里实际用过的延伸用法。

第一个场景是分页打印商品标签。我以前帮一个仓库做过条码标签打印，标签纸是一张 A4 上六行四列共二十四个标签，仓库给我的是一份按编码顺序排好的商品清单，一列三千多行。我用的是同一套思路，只是把「每十个一组」改成「每二十四个一组」，并且让结果按二维方向排列得更紧凑：行偏移用 MOD(序号, 6)，列偏移用 INT(序号/6)，再嵌一层 OFFSET 就能直接喂给打印模板。整套方案一次成型，后续每次接到新清单粘到 A 列里，标签自动重新排，省下的不是几分钟而是几个小时。

第二个场景是按客户分组拆账单。每个月底我会从系统导出一份当月所有订单明细，需要按客户名称分别发对账给每家客户。原始数据是混在一起的两万行，每个客户行数不固定。我用 OFFSET 加 MATCH 加 COUNTIF 组合，先算出每家客户在原表里第一次出现的行号和总条数，再用 OFFSET 一次性把这家客户的所有行整段拉到目标工作表里。这种「按 key 分段」比「按固定数量分段」要难一些，但思路相通，都是把抽数公式做成只跟坐标相关的纯函数。

第三个场景是把多列日报合并成单列时序数据。市场部每天给我一份当日各渠道转化数据，是横排七列，我做长期趋势分析时需要把这七列拍平成一列时序。这相当于本文做的事情反向操作，公式从 =OFFSET($A$1, ROW(A1)-1+10*COLUMN(A1)-10, 0) 改成 =OFFSET($A$1, INT((ROW(A1)-1)/7), MOD(ROW(A1)-1, 7))，往下拉就行。同一种工具，调换坐标算式，正反两种工作都能做。

这三个例子串起来其实想说一件事：与其每遇到一个新场景就上网搜公式，不如花半小时把 OFFSET、INDEX、MOD、INT、ROW、COLUMN 这几个基础函数的语义吃透，剩下的全是组合题。Excel 高手和新手的差距很多时候不在记得多少公式，而在面对一个新需求时能不能在脑子里把它翻译成「坐标变换」。

八、FAQ

问：公式拖到很右边之后出现 0 是怎么回事？

答：当 OFFSET 算出来的位置已经超过 A 列的最后一个有数据的格子时，它会返回那个空白格的值，Excel 把空白格当作 0 显示。解决办法有两种：一是少拖几列，刚好覆盖到数据末尾；二是在公式外面套一层 IF 判空，写成 =IF(OFFSET($A$1, ROW(A1)-1+10*COLUMN(A1)-10, 0)="", "", OFFSET($A$1, ROW(A1)-1+10*COLUMN(A1)-10, 0))，让超界的格子显示空字符串而不是 0。

问：能不能不写公式，用菜单点几下完成？

答：可以，Excel 2021 及 Microsoft 365 内置了 WRAPCOLS 和 WRAPROWS 函数，专门做这种折叠。比如 =WRAPCOLS(A1:A2000, 10) 一条就够了。但这两个函数在 2019 及更早版本里不存在，所以我文章里仍然用 OFFSET，兼容范围广得多。

问：处理超过十万行的数据会卡吗？

答：会。OFFSET 是易失性函数，工作表里任何一处计算都会触发它重新算，十万行展开成上万列时计算量惊人。这种规模我建议直接走 Power Query，或者写一段 VBA 一次性写入数值，性能差距能拉到几十倍。OFFSET 适合千行到万行这种中等规模。

问：拆完之后想再拼回一列怎么办？

答：反向操作可以用 TOCOL 函数（Microsoft 365）：=TOCOL(B1:K10)，会把整个矩阵按列优先重新拍回一列。老版本 Excel 没这个函数，可以把矩阵复制到另一张表，挨列粘贴成一长条，或者用 Power Query 的「逆透视」一步搞定。

以上就是我把一列数据按每十个一组拆成多列的全部做法。这种小活看着不起眼，但一年里能省下来的时间累积起来真的不少。希望这篇笔记对你也有用。