XLOOKUP関数の応用

VBA等のプログラミングツールを使わずに、Excelの関数のみで、あるデータベースの特定の行をある条件に基づき、列に移動させる。

• はじめに
• ゴール
• 目次
• 準備
• 実装
• さいごに

さて、今回やりたいことは、以下のようなケースを想定しております。

まずは、適当なデータを作成したいと思いますが、リアリティがあった方がいいと思いますので、無料のデータをダウンロードしていきたいと思います。

いろいろなテストデータを無料で作成してくれるウェブサイトがありますが、今回は、下記サイトのテストデータ生成サイトを利用したいと思います。

参考

TM – WebTools

tm-webtools.com

いろいろな列項目を設定できますが、とりあえず、今回は、都道府県別に人を横並びにしてみたいと思いますので、なんとなく必要そうな「都道府県コード」「都道府県」「姓」「名」「メールアドレス」くらいにしておきたいと思います。

こちらのデータをCSVでダウンロードして、新規Excelデータから、そのデータを「データ」＞「テキストまたはCSVから」で、ダウンロードしたファイルを指定して、そのままPower Queryで都道府県コード別に並べ替えしておきましょう。

さて、こちらでデータの準備は整ったので、変換していきたいと思います。

1. STEP

   列をまとめる

   さて、準備してダウンロードしたデータとは別に、新たなワークシートに、データを作成していきます。

   今回は47都道府県とわかっておりますので、Excelの「フィル」＞「連続データの作成」で下記のように設定し、番号を47まで振っていきます。

   A2セルにカーソルを合わせた状態で操作します

   

2. STEP

   都道府県コードに都道府県を入力

   実際に仕事で使用したものは、得意先コードと得意先名といったものになりますが、やっていることは同じなので、同じように処理していきます。

   これは、単純なVLOOKUP関数で十分でB2セルに下記の関数を入力し、以下同様に埋めていきます。

   =IFERROR(VLOOKUP(A2,テストデータ___TM_WebTools[[都道府県コード]:[出身都道府県]],2,FALSE),””)

   今回ダウンロードしたデータでは、一部ない都道府県があったので、エラーを除去するためIFERRORを使ってます。また、データ範囲は、テーブルの名称となってますが、セルの範囲で構いません。

   

3. STEP

   行列の設定

   こちらの設定は意味がわからない人もいるかもしれませんが、今回の最も重要な設定です。

   元データに、新たに、3列追加して、名前は何でもいいですが、とりあえず、それぞれ「Row」「Column」「Cell」として、それぞれを下記のように設定します。

   

   Row	Column	Cell
   =テストデータ___TM_WebTools[@都道府県コード]	1	=F2*100+G2
   =テストデータ___TM_WebTools[@都道府県コード]	=IF(F3=F2,G2+1,1)	=F3*100+G3
   =テストデータ___TM_WebTools[@都道府県コード]	=IF(F4=F3,G3+1,1)	=F4*100+G4
   =テストデータ___TM_WebTools[@都道府県コード]	=IF(F4=F3,G3+1,1)	=F5*100+G5

4. STEP

   グループごとに行から列へ

   さて、最後に仕上げていきます。

   作りながら思いましたが、最終的には、Excelファイルを確認いただくのが早いかと思いますので、最後にサンプルデータを添付したいと思います。

   まず、データ整理用のワークシートの方に移り、必要な列を追加していきます。

   今回は、適当に各県に10人くらいいると想定して10人分の列を作成します。

   こちらは、MAX関数などを使えば各県に何人ぐらいいるかわかりますが、その辺は割愛します

   

   「姓」「名」「メールアドレス」の列のそれぞれに、1,2,3…と入力しておくことがポイントになります。

   最後に、それぞれに下記のような関数を設定してきます。ここで、XLOOKUP関数を使っていきます。

   

   姓1	名2	メールアドレス1	姓2	名2	メールアドレス2
   =XLOOKUP($A3*100+C$1,Sheet2!$H$2:$H$201,テストデータ___TM_WebTools[姓],””)	=XLOOKUP($A3*100+D$1,Sheet2!$H$2:$H$201,テストデータ___TM_WebTools[名],””)	=XLOOKUP($A3*100+E$1,Sheet2!$H$2:$H$201,テストデータ___TM_WebTools[メールアドレス],””)	=XLOOKUP($A3*100+F$1,Sheet2!$H$2:$H$201,テストデータ___TM_WebTools[姓],””)	=XLOOKUP($A3*100+G$1,Sheet2!$H$2:$H$201,テストデータ___TM_WebTools[名],””)	=XLOOKUP($A3*100+H$1,Sheet2!$H$2:$H$201,テストデータ___TM_WebTools[メールアドレス],””)
   =XLOOKUP($A4*100+C$1,Sheet2!$H$2:$H$201,テストデータ___TM_WebTools[姓],””)	=XLOOKUP($A4*100+D$1,Sheet2!$H$2:$H$201,テストデータ___TM_WebTools[名],””)	=XLOOKUP($A4*100+E$1,Sheet2!$H$2:$H$201,テストデータ___TM_WebTools[メールアドレス],””)	=XLOOKUP($A4*100+F$1,Sheet2!$H$2:$H$201,テストデータ___TM_WebTools[姓],””)	=XLOOKUP($A4*100+G$1,Sheet2!$H$2:$H$201,テストデータ___TM_WebTools[名],””)	=XLOOKUP($A4*100+H$1,Sheet2!$H$2:$H$201,テストデータ___TM_WebTools[メールアドレス],””)
   =XLOOKUP($A3*100+C$1,Sheet2!$H$2:$H$201,テストデータ___TM_WebTools[姓],””)	=XLOOKUP($A5*100+D$1,Sheet2!$H$2:$H$201,テストデータ___TM_WebTools[名],””)	=XLOOKUP($A5*100+E$1,Sheet2!$H$2:$H$201,テストデータ___TM_WebTools[メールアドレス],””)	=XLOOKUP($A5*100+F$1,Sheet2!$H$2:$H$201,テストデータ___TM_WebTools[姓],””)	=XLOOKUP($A5*100+G$1,Sheet2!$H$2:$H$201,テストデータ___TM_WebTools[名],””)	=XLOOKUP($A5*100+H$1,Sheet2!$H$2:$H$201,テストデータ___TM_WebTools[メールアドレス],””)

   簡単に説明しますが、ステップ3で、各行の人名等に検索する参照値を設定して、その参照値を新しく整理する用のデータベースへ、転記する際に、XLOOKUP関数を用いています。

   ポイントはそれぞれの参照値を計算式で設定しているところになります。

   詳しくは、最後にあるExcelファイルをダウンロードしてご確認ください。

   XLOOKUPを使う理由としては、参照値がテーブルの左側にこないため、VLOOKUP関数が使用できないためです

いかがでしたか。

私自身は、VBAやその他のプログラミング言語での知識は全くありませんので、Excelの関数だけで処理してみました。

ひょっとしたら、VBAやその他のプログラミング言語で処理した方が早いかと思いますが、そのような知識が全くなくても、関数さえ知っていれば処理できますので、使える人の範囲は増えるかと思います。

正直なところ、記事を書きながら、分かりづらいなと思いましたので、以下よりExcelファイルをダウンロードしていただき、ご確認していただければと思います。