Google Sheets隔行转置：将错位X/Y值归至同一行的函数方案

Option 1: Drag-and-Drop Formula #

This is a simple, manual approach for applying the formula to each row:

1. In cell B2, enter this formula to pull your X values (every even-positioned row from column A starting at A2):
   =IFERROR(INDEX(A:A, 2*ROW()-2), "")
2. In cell C2, enter this formula to grab the corresponding Y values (the row immediately after each X value):
   =IFERROR(INDEX(A:A, 2*ROW()-1), "")
3. Select both B2 and C2, then click and drag the small fill handle at the bottom-right corner down until you’ve covered all your data. The formulas will automatically adjust to pair A4→B3, A5→C3, and so on.

Option 2: Array Formula (Auto-Populate All Rows) #

Skip dragging formulas entirely with array formulas that populate all rows at once:

1. In cell B2, enter:
   =ARRAYFORMULA(IFERROR(INDEX(A:A, SEQUENCE(ROUNDUP(COUNTA(A:A)/2), 1, 2, 2)), ""))
   This creates a sequence of row numbers starting at 2, incrementing by 2 (2,4,6...) and pulls those values from column A.
2. In cell C2, enter:
   =ARRAYFORMULA(IFERROR(INDEX(A:A, SEQUENCE(ROUNDUP(COUNTA(A:A)/2), 1, 3, 2)), ""))
   This does the same but starts at 3, incrementing by 2 (3,5,7...) to capture the Y values.

The logic is nearly identical in Excel, with minor tweaks based on your version:

Option 1: Drag-and-Drop Formula #

1. In B2:
   =IFERROR(INDEX(A:A, 2*ROW()-2), "")
2. In C2:
   =IFERROR(INDEX(A:A, 2*ROW()-1), "")
3. Drag the fill handle down to apply the formula to all rows.

Option 2: Dynamic Array Formula (Excel 365/2021+) #

If you have Excel 365 or 2021, use dynamic arrays to auto-spill results:

1. In B2:
   =IFERROR(INDEX(A:A, SEQUENCE(ROUNDUP(COUNTA(A:A)/2),1,2,2)), "")
2. In C2:
   =IFERROR(INDEX(A:A, SEQUENCE(ROUNDUP(COUNTA(A:A)/2),1,3,2)), "")
   The formula will automatically spill down to cover all matching pairs.

Option 3: Legacy Excel (Pre-365) #

For older Excel versions, enter the array formula using Ctrl+Shift+Enter:

1. Select the range where you want X values (e.g., B2:B1000 for 1000 pairs).
2. Enter =IFERROR(INDEX(A:A, 2*ROW(B2:B1000)-2), "") and press Ctrl+Shift+Enter.
3. Repeat for Y values in column C with =IFERROR(INDEX(A:A, 2*ROW(C2:C1000)-1), "").

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