Excel。一行おきのデータを集めて別表にするためのテクニック
＜OFFSET関数+ROW関数＞
以前一行おきの合計方法をというのをご紹介したことはありましたが、今回のリクエストは、一行おきのデータをそれぞれ抽出して、別の表にしたいというもの。
たしかに現場ではありがちな表なのですが、本来ならば、一行で管理したほうがいいのですが、
なかなか、データベースというものを理解していないと、このような表を作りがちですよね。
今回は下記のような表を、2つの表に分けていきます。
オレンジ色の見出し行の表は、2014年と2015年が2行一組となっている表です。
これを水色の見出し行の表には、2014年のデータを、
青色の見出し行の表には、2015年のデータを表示させたいというのが、
今回のリクエストということになります。
自力で、一行ずつにコピーして貼り付けるということでもいいかもしれませんが、
大変すぎますので、ここは、OFFSET関数を使っていきましょう。
A18に数式をつくって、あとは、下方向・右方向にオートフィルハンドルを使って、
数式をコピーして、2014年の表を作ることをしていきます。
A18をクリックして、OFFSET関数ダイアログボックスを表示しましょう。
では、参照から設定していきます。
参照には、起点となるA2 を入力します。
本来ならば、絶対参照を設定したいところですが、一行おきのデータを参照させたいので、
1つずつ下にずれていくようにします。
この為、絶対参照は設定しません。
行数ですが、起点から1行下を参照させたいので、ここには、1 と入力したわけですが、
数式をコピーしても1となってしまいます。
さらに、一行おきになっていますので、データは、
新宿店は、起点のA2から見て、1行下のA3を、
渋谷店は、起点のA3から見て、2行下のA5を、
池袋店は、起点のA4から見て、3行下のA7を、
というようなパターンになっていますので、1・2・3…となるものがないかと、探してみると、
行番号が使えるのでは？と気が付けば、最高！！
行番号を算出するのは、ROW関数ですので、
行数には、ROW(A1) と入力します。
1という数字がほしいだけですので、別にA1じゃなくて、B1でも構いませんが、
わかりにくくなりますから、A1としました。
数式は、
=OFFSET(A2,ROW(A1),0)
列数は左右には動きませんので、0（ゼロ）を入力します。
あとは、OKボタンをクリックすると、A18に、新宿店が算出されましたので、
オートフィルハンドルを使って、下方向・右方向に数式をコピーしましょう。
続いて、2015年も作っていきましょう。
G列の店舗名は同じですので、G18には、A18と同じ数式の
=OFFSET(A2,ROW(A1),0)
を設定すればOKですね。
H列以降は、1行ずれますので、
行数のところが、ROW(B2)と変わるだけです。その点を修正しましょう。
H18の数式は、
=OFFSET(B2,ROW(B2),0)
ですね。
あとは、数式をコピーすれば完成ですね。
OFFSET関数は非常に便利な関数ですが、
ROW関数とネストするとこんなことも出来るようになりますよ。
