如何适配Excel INDEX公式将双列数据批量拆分为多组双列？

Absolutely, you can adapt that formula to handle your two-column dataset! Here's a single, flexible formula that will populate all your target ranges (C1:D10, E1:F10, etc.) correctly:

=INDEX($A$1:$B$100, (INT((COLUMNS($C1:C1)-1)/2)*10)+ROW(), MOD(COLUMNS($C1:C1)-1,2)+1)

How to use it: #

1. Paste this formula into cell C1
2. Drag it right to fill columns D, E, F, and so on—you’ll need 20 total columns to cover all 100 rows (10 groups × 2 columns each)
3. Drag down from C1 to C10; this will automatically fill all rows in every two-column group

Formula breakdown: #

Let’s break down each component so you can tweak it for your needs:

• $A$1:$B$100: This references your original data range. Adjust this if your dataset starts/ends at different cells.
• INT((COLUMNS($C1:C1)-1)/2): Calculates which 10-row group we’re working with. Every two columns you drag right, this number increments by 1 (group 0 for C-D, group 1 for E-F, etc.).
• (INT(...)*10)+ROW(): Computes the exact row in your original data. Multiply the group number by 10 to jump to the start of the group, then add the current row number to get the position within that group.
• MOD(COLUMNS($C1:C1)-1,2)+1: Alternates between 1 and 2 to reference column A or B in your original data. For the first column of each group (C, E, G...), it uses column 1 (A); for the second (D, F, H...), column 2 (B).

Quick notes: #

• If you want to start the split in a different column (not C), replace $C1:C1 with a reference to your starting cell (e.g., $G1:G1 if starting at G1).
• This formula is non-volatile (unlike the OFFSET function), so it won’t slow down your workbook even with large datasets.

内容的提问来源于stack exchange，提问作者Luís Jervell