Excel。横方向のデータを縦方向に手早くセル参照するには、どうしたらいいの？
＜OFFSET+ROW関数＞
Excelには、簡単そうに見えて、意外とどうやったらいいのか。
行うとしても面倒な処理になることが結構あります。
次のようなケースもそのひとつです。
やりたいことは、B7:D7のデータをH2:H4にコピーすることです。
横方向を縦方向に貼り付けるコピーがあるのですが、今回は、セル参照させたいわけです。
横方向を縦方向にするだけなのですが、結構大変です。
セル参照の数式をオートフィルで下方向にコピーすればいいと考えますが、うまくいきません。
H2には、
=B7という数式を設定したセル参照をつくり、オートフィルで数式をコピーしても、列方向にコピーしたのではなく、行方向にコピーしたので、
H4の数式は、
=B9となってしまっています。
本当ならば、D7としたいわけです。
オートフィルで数式をコピーするときには、横方向なら横方向、縦方向なら縦方向にコピーするので、今回のように、横方向を縦方向では、オートフィルで数式をコピーすることができないわけです。
当然、一つずつ、セル参照を設定するのは、大変というか、データ量があれば、無理です。
また、この程度のことで、わざわざ、Excel VBAでマクロをつくるというのも、面倒です。
そこで、登場するのが「OFFSET関数」です。
OFFSET関数は、セルから指定した行・列方向のデータを参照することができます。
まずは、動きを確認したいので、OFFSET関数のみで作り直してみます。
H2には、
=OFFSET($B$7,0,0)
最初の引数は、起点となるセル番地なので、$B$7。
オートフィルで数式をコピーすることを前提としているので、絶対参照も設定しておきます。
２つ目の引数は、行数。
起点から何行のところという意味ですが、今回は、列方向のみで、行方向（横方向）は変動しないので、「0（ゼロ）」
3つ目の引数が、列数。
ここがポイントになるわけですね。
１列ずつスライドするので、「+1」ずつしたいわけです。
H4の数式は、
=OFFSET($B$7,0,2)
オートフィルで数式を縦方向にコピーしても、3つ目の引数は、「+1」されません。
そこで、アイディアが必要となるわけです。
なお、残りの引数は、今回関係ないので、省略します。
さて、3つ目の引数の列数に、ROW関数をつかうことで、「+1」させることができます。
H2の数式を次のように変更します。
=OFFSET($B$7,0,ROW(A1)-1)
ROW関数は、行番号を算出してくれる関数です。
ROW(A1)とすれば、「1」を算出してくれますので、その値から「-1」すれば、「0（ゼロ）」になります。
H4の数式は、
=OFFSET($B$7,0,ROW(A3)-1)
と「A1」が「A3」に変わっています。
ROW(A3)なので、「3」。マイナス1とすれば、「2」を算出してくれます。
よって、オートフィルで数式をコピーすることで、横方向のデータを縦方向でセル参照することができました。
OFFSET関数を知ることで、日頃面倒な作業も、改善できるかもしれませんので、OFFSET関数が使えないか、チェックするというのもいいかもしれませんね。
