Relative reference deeper · Martinとコード

A shift is not only down

コピーing right: =A2+B2 from C2 to D2 → =B2+C2. Columns +1.

Diagonal: you drag the square down right — rows and columns shift.

The spreadsheet counts the shift itself. You watch if it makes sense.

💡

Click a cell with a formula. Coloured cells (coloured rectangles) show what the formula reaches. Click another cell — you will see other colours.

A formula to the right

メモs A20 5, B20 3. C20 =A20+B20 → 8.
Drag the square of C20 to E20 (right). D20 =B20+C20? No — C20 had formula A20+B20. D20 will be =B20+C20 only when you copy right…

A better test: C20 =A20+B20. コピー to D20: =B20+C20 — shift +1 column. A20→B20, B20→C20.
Click D20. In the formula bar check the shift.

A range changes when dragging

=SUM(B2:B5) dragged down one row → =SUM(B3:B6). The whole range slides. Sometimes you want that. Sometimes not — then $B$2:$B$5 or only $B$2:B$5.

⚠️

An average dragged down. When in B8 you have =AVERAGE(B3:B7) and you drag to B9, you get =AVERAGE(B4:B8) — the range shifted too. For a summary under a table do not drag the average down. One formula, one cell.

A column of differences next to data

Grades column C Target — in C3:C8 numbers 1 (target a one).
D3 =B3-C3 — difference grade minus target. 2−1=1 (worse than the target).
Drag D3 down. D4 =B4-C4 and so on.
Change B3 — D3 moves. C3 a fixed target 1.

Pocket money — a percent column (practice)

E1 100 (a target reserve). F2 =D2/$E$1 — the share of left to 100. Drag F2:F8. D shifts, E1 is fixed.

🏫 Grade average

One sheet, two columns: subject and grade. At the bottom a sum and an average.

💰 Pocket money

Rows = days, columns = income / spend / left.

📊 Class survey

One question, answers in one column, later a chart.

🏆 Mini dashboard

Three numbers at the top and one chart. Numbers as a link, not copied by hand.

Now you 💪

You copied a formula down — addresses +1 row.
You tried right — columns +1.
For an average under the table you did not drag the formula down for no reason.
Difference column D on grades works after dragging.
You check coloured rectangles in a formula with your eyes.

✅

Check: You understand a relative reference shift in all directions.

What to take from this lesson

Down = row +1. Right = column +1.
Range B2:B5 dragged down → B3:B6.
A summary under a table is usually one cell, not a series.
Combine absolute $ with relative as needed.
Coloured previews of cells in a formula help a check.