これまでも何度か書いている、
アニメとは直接関係ない、表計算ソフトのお話、その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 | A1 | B1は、B2 の "1" 行上 |
| 3 | 2 | A2 | B1は、B3 の "2" 行上 |
| 4 | 3 | A3 | B1は、B4 の "3" 行上 |
| 5 | . | . | . |
| 6 | 表B | B | . |
| 7 | 1 | B1 | B6は、B7 の "1" 行上 |
| 8 | 2 | B2 | B6は、B8 の "2" 行上 |
| 9 | 3 | B3 | B6は、B9 の "3" 行上 |
| 10 | . | . | . |
ここで、
相対位置がA列の行数表示と同じことを利用します。
| . | A | B | 相対位置 |
|---|---|---|---|
| 1 | 表A | A | . |
| 2 | 1 | A1 | B1は、B2 の "A2" 行上 |
| 3 | 2 | A2 | B1は、B3 の "A3" 行上 |
| 4 | 3 | A3 | B1は、B4 の "A4" 行上 |
| 5 | . | . | . |
| 6 | 表B | B | . |
| 7 | 1 | B1 | B6は、B7 の "A7" 行上 |
| 8 | 2 | B2 | B6は、B8 の "A8" 行上 |
| 9 | 3 | B3 | B6は、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列が必須です。
この記事へのコメント