Excel数据集5列转两列公式实现求助（无VBA/宏权限）

Hey there! I’ve got a straightforward, non-array formula solution that’ll handle your 10k-row dataset smoothly—no slowdowns, no VBA required. Let’s break it down step by step.

Core Logic #

We need to map every 4 rows in the transposed output to one row in your original data:

• The first column of the output repeats the original first column value 4 times (once for each of columns 2-5 in the source)
• The second column cycles through columns 2-5 of the original data, then moves to the next row’s columns 2-5, and so on.

Assumptions #

Let’s say your original data is in A1:E[LastRow] (column A is your first column, B-E are the columns to transpose). We’ll put the transposed result starting at G1 (you can adjust this to any cell).

Step 1: Formula for the First Output Column (Original Column A) #

In cell G1, enter this formula and drag it down as far as needed:

=INDEX($A:$A, INT((ROW()-1)/4)+1)

• INT((ROW()-1)/4)+1 calculates which original row to pull from: rows 1-4 in the output map to row 1 of the source, rows 5-8 map to row 2, etc.
• INDEX($A:$A, ...) grabs the corresponding value from column A.

Step 2: Formula for the Second Output Column (Original Columns B-E) #

In cell H1, enter this formula and drag it down:

=INDEX($A:$E, INT((ROW()-1)/4)+1, MOD(ROW()-1,4)+2)

• The first argument in INDEX uses the same row calculation as Step 1 to target the right original row.
• MOD(ROW()-1,4)+2 cycles through columns 2-5:
  • Row 1: MOD(0,4)=0 → 0+2=2 (column B)
  • Row 2: MOD(1,4)=1 → 1+2=3 (column C)
  • Row 3: MOD(2,4)=2 → 2+2=4 (column D)
  • Row 4: MOD(3,4)=3 → 3+2=5 (column E)
  • Row 5: MOD(4,4)=0 → 0+2=2 (column B of the next original row)

Adjusting for Different Starting Rows #

If you don’t start the transposed data at row 1 (e.g., starting at G5), just adjust the ROW() offset. For example:

• First column formula: =INDEX($A:$A, INT((ROW()-5)/4)+1)
• Second column formula: =INDEX($A:$E, INT((ROW()-5)/4)+1, MOD(ROW()-5,4)+2)
  (Subtract the row number of your starting cell from ROW() to fix the offset.)

Why This Works Better Than Array Formulas #

This uses regular (non-array) formulas, which are way faster for large datasets. Even with 10,000 original rows (resulting in 40,000 transposed rows), these formulas will calculate quickly without lag.

内容的提问来源于stack exchange，提问作者Keer Kolloft