こんにちは。ヒトツメです。
今日は、Excelを使ったデータベースを、複数行に拡張する場合のやり方について考えていきたいと思います。
言葉だけだと分かりにくいので、実際にやりたいことを示すと、次の画像のような場合です。項目ごとに指定された行数分データを拡張する場合のやり方です。
あまりしょっちゅう使う技術ではないですし、少量であれば手で作業すれば対応可能ですが、企業の情報をそれぞれの契約数に応じて拡張したいような場合に役に立つと考えられます。
OFFSET関数を使ってみる
このような場合、最初に思いつくのが、OFFSET関数を使うようなやり方です。
OFFSET関数はすでに何度か取り上げているので、詳細な説明は割愛しますが、指定した場所から縦横にそれぞれ指定した数字分移動した先のセルの結果を拾ってくることができます。
このOFFSET関数と、ROW関数やCOLUMN関数を活用することで、縦に3つ移動するごとに参照先を1つ移動させるといった操作を行うことが可能になり、項目を拡張することができます。
例えば、下の画像のような場合に、B1セル以下に「=OFFSET($A$1,(ROW(A1)-1)/3,0)」と入れることで、項目を3つずつに拡張することができます。
ただ、このやり方だと、項目ごとに拡張する行数がばらばらだった場合にも対応できるようにしようとすると、計算式がかなりややこしくなってしまいます。
できなくはありませんが、かなり大変です。
INDEX関数とMATCH関数を使う
そこで、個人的に推奨したいやり方は、INDEX関数とMATCH関数を使うやり方です。
最初に現れる行数を指定する
まず下準備として、それぞれの項目ごとに拡張する行数から、その項目が最初に現れるべき行数を指定してあげます。
最初の項目のところに「1」それ以降、すぐ上の項目の行数を足し合わせていくことで、最初に現れるべき行数を指定することができます。
出力行数を指定する
次に、出力先のすぐ横に、出力する行数を指定する部分を用意します。
今回は10行目まで出力すれば問題ないので、1から10までを順に入れていきます。
INDEX＋MATCＨ関数で呼び出す
ここまで準備ができれば、あとはINDEX関数とMATCH関数で項目を呼び出すだけです。
結論から言ってしまうと、上の画像でいえば、F2セル以下に「=INDEX(B:B,MATCH(E2,A:A,1),)」と入れます。
この時、MATCH関数の第三引数を0にしてしまうと、1、3、7、10の行にそれぞれの項目が出るだけになりますが、これを「1」にすることで、1以上3未満の場合はAを、3以上7未満の場合にはBを、といった形で結果を出力できる、ということです。
さいごに
答えを聞いてしまうとあっけないですが、行の拡張というと、マクロを使わないと難しいのでは？と思ってしまいがちです。
あまりこの操作自体の需要がないかもしれませんが、頭の体操として考えてもらえると、Excelの練習にもなっていいかもしれません。
コメント