絶対参照を使わずに、参照セルを固定する方法

カテゴリ : 日記

これまでも何度か書いている、
アニメとは直接関係ない、表計算ソフトのお話、その3。

記事タイトルだけでは、何を言っているのか分からないかもしれませんが、
例えば、こんな感じの表Aと表Bがあったときに、 共通で使える数式を考えます。

(「共通で」とは、数式をコピペして修正なしに使い回せるという意味)

表示したい結果

. A B
1 表A A
2 1 A1
3 2 A2
4 3 A3
5 . .
6 表B B
7 1 B1
8 2 B2
9 3 B3
10 . .

表Aは、数字の前にA、表Bは、Bを付けたい。
(B列)

絶対参照を使った場合

. A B数式
1 表A A .
2 1 A1=B$1&A2
3 2 A2=B$1&A3
4 3 A3=B$1&A4
5 . . .
6 表B B .
7 1 A1=B$1&A7
8 2 A2=B$1&A8
9 3 A3=B$1&A9
10 . . .

参照が固定なので、表Aの数式をコピペすると、表Bも「Ax」になってしまう。
そのため、表Bでは、数式の修正が必要になる。
(B$1→B$6)


.

相対位置で考えてみる

絶対参照だとうまくいかないので、相対位置で考えてみます。

. A B相対位置
1 表A A.
2 1 A1B1は、B2 の "1" 行上
3 2 A2B1は、B3 の "2" 行上
4 3 A3B1は、B4 の "3" 行上
5 . . .
6 表B B.
7 1 B1B6は、B7 の "1" 行上
8 2 B2B6は、B8 の "2" 行上
9 3 B3B6は、B9 の "3" 行上
10 . . .

ここで、
相対位置がA列の行数表示と同じことを利用します。


. A B相対位置
1 表A A.
2 1 A1B1は、B2 の "A2" 行上
3 2 A2B1は、B3 の "A3" 行上
4 3 A3B1は、B4 の "A4" 行上
5 . . .
6 表B B.
7 1 B1B6は、B7 の "A7" 行上
8 2 B2B6は、B8 の "A8" 行上
9 3 B3B6は、B9 の "A9" 行上
10 . . .

これで、
絶対参照を使わずに参照を指定できそうです。



回答例 : OFFSET関数を使った、相対位置の固定

OFFSET関数は、基準セルからの相対位置を指定できます。

. A B数式
1 表A A.
2 1 A1=OFFSET(B2,-A2,0)&A2
3 2 A2=OFFSET(B3,-A3,0)&A3
4 3 A3=OFFSET(B4,-A4,0)&A4
5 . . .
6 表B B.
7 1 B1=OFFSET(B7,-A7,0)&A7
8 2 B2=OFFSET(B8,-A8,0)&A8
9 3 B3=OFFSET(B9,-A9,0)&A9
10 . . .

これなら、数式を修正せずに参照が切り替わります。

まぁ、ちょっと反則気味ですね。
これが成立するためには、表の行数を数えるA列が必須です。


関連


この記事へのコメント

  • コメントはありません。

この記事へのトラックバック

  • トラックバックはありません。