アニメ調査室(仮): 表の行と列を入れかえて参照する数式

表の行と列を入れかえて参照する数式

2013年12月01日

カテゴリ : 日記

表計算ソフトで表を作るときに、表の向きを入れ替えたいときがあります。
こういうとき普通は、「コピー」、「形式を選択して貼りつけ」で解決します。
しかし、元の表が増えていく場合は、数式を使って参照する方が便利です。
そのために、どんな数式を書いたらよいでしょう?
という話。

問題

表(A1:E3)の値を行と列を入れ替えて参照したい。(A5:C9)
A5セルに入る数式は?

条件

参照先 (A5:C9) に数式をコピーして正しく参照できること。
表の量が増えても数式の修正が不要なこと

.	A	B	C	D	E
1	すいか	なし	バナナ	みかん	りんご
2	300円	150円	120円	100円	180円
3	A店	C店	E店	D店	B店
4	.	.	.	.	.
5	すいか	300円	A店	.	.
6	なし	150円	C店	.	.
7	バナナ	120円	E店	.	.
8	みかん	100円	D店	.	.
9	りんご	180円	B店	.	.

座標で考える

分かりやすくするため、値を座標に置き換えてみます。

元の表の座標

.	A	B	C	D	E
1	(1,1)	(1,2)	(1,3)	(1,4)	(1,5)
2	(2,1)	(2,2)	(2,3)	(2,4)	(2,5)
3	(3,1)	(3,2)	(3,3)	(3,4)	(3,5)

行と列を入れ替えた参照座標

.	A	B	C
5	(1,1)	(2,1)	(3,1)
6	(1,2)	(2,2)	(3,2)
7	(1,3)	(2,3)	(3,3)
8	(1,4)	(2,4)	(3,4)
9	(1,5)	(2,5)	(3,5)

座標の入れ替えとは

行と列を入れ替えたので、 A6セルは、元の(1,2)の値を参照しています。

座標を入れかえる

コピー&ペーストするだけで数値が変わるようにするため、座標を表示する関数を使います。

ROW関数

セルの行数を表示する、列は関係ない。
参照元が「A4」でも「E4」でも行数は「4」で変わらない。

ROW(C4)=4 (4行目)

COLUMN関数

セルの列数を表示する、行は関係ない。
参照元が「C1」でも「C8」でも列数は「3」で変わらない。

COLUMN(C4)=3 (C列＝3列目)

この 2つの関数を逆に使うと、行と列を入れ替えた座標が得られます。

行座標に、列数を示すCOLUMN関数
列座標に、行数を示すROW関数

例1) 縦方向に数式をコピー

数式	座標
COLUMN(A1),ROW(A1)	(1,1)
COLUMN(A2),ROW(A2)	(1,2)
COLUMN(A3),ROW(A3)	(1,3)

例2) 横方向に数式をコピー

数式	COLUMN(A1),ROW(A1)	COLUMN(B1),ROW(B1)	COLUMN(C1),ROW(C1)
座標	(1,1)	(2,1)	(3,1)

セルを参照する関数

セルを参照する関数といえば、INDEX関数があります。
しかし、INDEX関数は、参照範囲 (始点と終点) を先に指定しないといけません。
そのため、リストの量が増えると、数式をコピーしてもエラーになってしまいます。

そこで、セル参照には、OFFSET関数を使います。
OFFSET関数はINDEX関数と違い、基準セル (始点) を指定するだけでセル参照が出来ます。
そのため、リストの量が変わった時も、範囲を指定しなおす必要がありません。

OFFSET関数使用時の注意点

OFFSET関数の位置指定は、基準セルからの相対位置になります。
INDEX関数のように、行数や列数を指定するわけではありません。

通常は、元の表の左上を基準セルにして、参照したい座標から -1 する方法が簡単です。

INDEX関数、OFFSET関数で、同じセルを指定する時の違い

INDEX(範囲,行,列)
OFFSET(基準セル,行-1,列-1)

使用例. (表の範囲 A1:E3、基準セル A1)

A4 : 座標 (4,1) → 相対位置 (3,0)

INDEX($A$1:$E$3,4,1)
OFFSET($A$1,3,0)

C3 : 座標 (3,3) → 相対位置 (2,2)

INDEX($A$1:$E$3,3,3)
OFFSET($A$1,2,2)

問題の答え

問題に戻ります。

.	A	B	C	D	E
1	すいか	なし	バナナ	みかん	りんご
2	300円	150円	120円	100円	180円
3	A店	C店	E店	D店	B店
4	.	.	.	.	.
5	すいか	300円	A店	.	.
6	なし	150円	C店	.	.
7	バナナ	120円	E店	.	.
8	みかん	100円	D店	.	.
9	りんご	180円	B店	.	.

A5セルに入る関数は、こうなります。

回答

OFFSET($A$1,COLUMN(A1)-1,ROW(A1)-1)

これを表の範囲分(A5:C9)コピーすれば、行と列を入れ替えた表の完成です。
もっとシンプルな方法を知っている方は、教えてください。

終わりに

なんでアニメブログでこんな記事書いているのかって?
ただのネタ切れです。
(アンケートの集計に使っているので、自分としては全く無関係でもないんだけどね)

アニメ調査室(仮) : 表の行と列を入れかえて参照する数式