Excel单元格跨引用设置：间隔行数据批量链接需求

To achieve the desired behavior where dragging formulas in J2, K2, L2 down picks up corresponding B/C/D column cells at 4-row intervals (B3→B7→B11, etc.), the INDEX function is your best bet—it’s non-volatile (doesn’t recalculate unnecessarily) and perfect for structured row references.

Formulas to Implement: #

• For cell J2:

  =INDEX(B:B, 3 + (ROW()-2)*4)
  

• For cell K2:

  =INDEX(C:C, 3 + (ROW()-2)*4)
  

• For cell L2:

  =INDEX(D:D, 3 + (ROW()-2)*4)
  

How This Works: #

• ROW() returns the current row number of the formula cell. For J2, that’s 2.
• (ROW()-2)*4 calculates the row offset from your starting point (row 3). For J2, this equals 0 (so we get B3); for J3, it equals 4 (jumping to B7); for J4, it equals 8 (landing on B11)—exactly the interval you need.
• INDEX([column], [row number]) pulls the value from the specified row in the target column.

Alternative (Volatile) Option with OFFSET: #

If you prefer OFFSET (note: it recalculates whenever any cell in the sheet changes, which can slow down large workbooks), use these formulas instead:

• J2: =OFFSET(B$3, (ROW()-2)*4, 0)
• K2: =OFFSET(C$3, (ROW()-2)*4, 0)
• L2: =OFFSET(D$3, (ROW()-2)*4, 0)

Once you enter the formula in J2, K2, L2, just drag the fill handle down to J3/J4, K3/K4, L3/L4—they’ll automatically reference the correct interval rows in B, C, D.

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