Excel横向自动填充公式时，如何实现指定行号递增而非列号？

Hey there! I get exactly what you're dealing with—Excel's default autofill behavior is great for most cases, but when you need to swap column increments for row increments, it can be frustrating. Let's break down a couple of solid solutions to get your formula working as intended.

The Core Problem #

When you drag a formula horizontally, Excel automatically increments column references (like C3 → D3) instead of row references. Your goal is to have the $C3:$Q3 range shift down a row each time you move right a column (so C13 uses $C3:$Q3, D13 uses $C4:$Q4, etc.).

Solution 1: Use OFFSET to Dynamically Shift Rows #

You mentioned trying OFFSET before, but let's clarify—OFFSET returns a cell/range reference, which works perfectly in your SUMPRODUCT formula. Here's how to structure it:

In cell C13, enter this formula:

=SUMPRODUCT(--(OFFSET($C$3:$Q$3, COLUMN()-COLUMN(C13), 0)=OFFSET($C$4:$Q$4, COLUMN()-COLUMN(C13), 0)))

How this works:

• COLUMN()-COLUMN(C13) calculates how many columns you've moved right from C13. For C13, this equals 0; for D13, it equals 1, and so on.
• The second parameter in OFFSET is the number of rows to shift. Adding that column offset value here means each time you drag right, the range shifts down one row.
• $C$3:$Q$3 and $C$4:$Q$4 are fixed starting ranges, so OFFSET adjusts them dynamically based on your horizontal position.

Solution 2: Use INDEX for a Non-Volatile Alternative #

If you prefer avoiding volatile functions (they recalculate more often, which can slow down large spreadsheets), INDEX is a great option. Try this formula in C13:

=SUMPRODUCT(--(INDEX($C:$Q, COLUMN()-COLUMN(C13)+3, 1):INDEX($C:$Q, COLUMN()-COLUMN(C13)+3, 15)=INDEX($C:$Q, COLUMN()-COLUMN(C13)+4, 1):INDEX($C:$Q, COLUMN()-COLUMN(C13)+4, 15)))

How this works:

• INDEX($C:$Q, [row_num], [col_num]) lets you specify a range by row and column numbers.
• COLUMN()-COLUMN(C13)+3 calculates the starting row: for C13, this is 3; for D13, it's 4, etc.
• The 1 and 15 in the formula refer to columns C (1st column of $C:$Q) and Q (15th column of $C:$Q), so we're defining the full C:Q range for each row.

How to Use #

1. Enter either formula in C13.
2. Hover over the bottom-right corner of C13 until you see the fill handle (a small square).
3. Drag the fill handle horizontally to D13, E13, and any other cells you need.
4. Double-check the formulas in the filled cells to confirm: D13 should reference $C4:$Q4 and $C5:$Q5, E13 should reference $C5:$Q5 and $C6:$Q6, etc.

Either of these methods will give you the row-incrementing behavior you need when filling horizontally. The OFFSET version is more concise, while INDEX is better for performance in large sheets.

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