【Excel】複数の列要素（数値と文字両方あり）があるクロス集計表を作る（行と列の組み合わせでVLOOKUPのKEYを作る）
複数の列要素を組み合わせたクロス集計表を、短時間で作りたい時のオススメの方法です。
書式を整える時間と除けば10～15分で出来ます。
↓こんなイメージの表です。（クリックすると拡大表示します）
ピボットテーブルは数値表示のみなので、対応できないですし、Accessでクロス集計で作業するには列要素が多すぎます。
こんな考え方で対応します。
「行と列の組み合わせでVLOOKUPのKEYを作る」戦法が有効です。
（ちなみに、同じ発想でこんなのもリストから一発で作れます。これも慣れれば15分で出来ます。
↓作り方は、次回にご案内しています。クリックすると拡大表示します。）
（副部長が二人以上いる場合に備えて、部署と役職名をKEYに重複連番を振るのがコツ）
事例でご案内します。
事例説明
ニーズ ：複数の列要素（数値と文字両方あり）があるクロス集計表を作りたい。
依頼事項：営業一部から三部の各人の３年間の実績を一覧出来るようにまとめる。
留意点：
・単純に売上だけでなく、担当先毎の売上も表示する。（特に重要なA社とB社がわかるようにする）
・プロジェクトを担当したことがあれば表示する。
・後から他の要素も必要になる可能性があるので、簡単に表示要素が変更できるように作る。
対応方法
・「行と列の組み合わせでVLOOKUPのKEYを作る」戦法
・試行錯誤は必要ですが、イメージさえ出来れば、作業自体は15分もあれば出来ます。
元データの特徴
・経年毎・各人別の売上データベースがある。
・出来上がり（クリックすると拡大表示します）
必要なスキル
以下の様に基礎的なスキルがあれば、作業できます。
・VLOOKUP関数（とIF関数とIFERROR関数）が使える。
・複合参照（どちらかに「＄」がつくもの。「$A1」 か「A$1」）を理解して使用できる。
・ピボットテーブル作成スキル
・ピボットテーブル作成スキル
・異動している人がいて、複数の行に表示している場合は、その表示が出来るようにCOUNTIF関数
・セルに色をつけるなら条件つき書式
手順です。
（私の考える最短ルートです。これなら、Excel作業は仕上げを除けば15分で出来る筈です。）
1．今回は元リストを作成する際にAccessのクエリを作り、エクスポートする。
Excelでの作業から勘案して、年度は降順にする。
（重複するデータがある時はVLOOKUP関数は上の行のデータを見るので）
（重複するデータがある時はVLOOKUP関数は上の行のデータを見るので）
（注意）クエリを作るときは、名称だけでなく、コードもつけること
（例：所属名称だけでなく、所属コードも。そうしないと並び替えで不自由します。）
（例：所属名称だけでなく、所属コードも。そうしないと並び替えで不自由します。）
2．エクスポートしたリスト
これを元データにする。（クリックすると拡大表示します。）
3．作業前の留意事項
人によって、以下のようなケースがあるので、クロス集計表上で上手く反映するように考えておく。
・営業一部から三部に異動している（＝年度によって所属部が異なる）
・改姓している（＝同じ社員番号でも姓名が異なる）
・同じ部署にいて昇格等で職位が異なる（＝同じ社員番号・部署でも年度により、「リーダー」→「課長」になっている）
4．[元データシート作業]KEY列を作る
一番左に列挿入して、KEY列を作る。（VLOOKUP関数に使うので左側にする）
今回は上記留意事項から2パターンのKEY列が必要なので、2列追加する。
今回は上記留意事項から2パターンのKEY列が必要なので、2列追加する。
1列目は、[社員番号] & “-” & [所属部] & “-” & [年度]
2列目は、[社員番号] & “-” & [所属部]
※本来、列結合時に「－」をつける必要は無いが、後から見た時の分かり易さの為に、ハイフンをつけている。
5．[元データシート作業]列番号表示行を作る
VLOOKUP関数の列番号設定時に利用するために、一番上に空白行を作り、
列番号を表示しておく。「=COLUMN()」
6．[元データシート作業]ピボットテーブルを作る
挿入／ピボットテーブル
行：所属部C／所属部／社員番号
※ここで姓名も付けたいところだが、改姓している場合、2行になってしまうので、【UNIQUE[ユニーク]にするもの】のみ行選択する。
デザインメニューにより、①②を設定することで、データが1行に表示される。
①小計／小計を表示しない
②レポートのレイアウト／表形式で表示
①小計／小計を表示しない
②レポートのレイアウト／表形式で表示
↓こんな感じです。（クリックすると拡大表示します。）
7．ピボットテーブル上の省略表示された重複行を復元する。
以下は[ピボットテーブルシート作業]の作業
=IF(A5=””,D4,A5)
以下のVLOOKUP関数の作業は、複合参照（どちらかに「＄」がつくもの。「$A1」 か「A$1」）を適切に使うことで、一つの式を一気にコピーできるようにします。
8．下準備
・表示列追加（重複チェック列等）
・見出し列データ・VLOOKUP関数の列番号・VLOOKUPで使う年度を入力
重複行の判定については、【Excel】重複がある際に適切なデータを選ぶ（COUNTIF関数を使う）をご参照ください。
9．固有データをVLOOKUP表示する。
KEY2を使ってVLOOKUP。
年度によって職位変更や改姓表示があったものは、最新のものが表示される（VLOOKUP関数検索で上の行のデータが表示される。）
一つ式を作ったら右列にもコピー（「I5」セルを「J5」にコピー）
=VLOOKUP(($C5&”-“&$E5),元データ!$B:$Z,I$3,0)
10．年度毎データをVLOOKUP表示する。
KEY1を使ってVLOOUP。この場合は非該当（データが無いケース）があるので、IFERROR関数も加える。
一つ式を作ったら右列にもコピー（
=IFERROR(VLOOKUP(($C5&”-“&$E5&”-“&K$2),元データ!$A:$R,K$3,0),””)
↓クリックして拡大表示
11．作成済みの1行を下までコピー
5行目を一番下までコピー
12．仕上げ
11迄でほぼ出来上がったものを仕上げる。
①「0」値を非表示にする。（オプション／詳細／「0」値を非表示）
②作業用の列や行をグループ化して、最後は非表示にする。
③罫線を引いたりや書式や列幅を整える。
④必要に応じて条件付き書式でA社・B社に色をつける。
※③④については、2019年度の「ひとかたまり」作成後、書式コピーと列幅コピーを使うと便利
（↓クリックすると拡大表示）
※ここで凝ると、思った以上に時間がかかるので、ほどほどにして切り上げましょう
（補足）
リスト以外の分析
Accessからエクスポートしたリストがあれば、会社毎の売上や、年度との相関関係等、
ピボットテーブルやCOUNTIF（COUNTIFS）関数やSUMIF（SUMIFS）関数で様々な分析が出来ます。
一覧のリストの作成時間を最小限にして、ここに時間をかけるようにしましょう。
他の手段
「MATCH関数とINDEX関数の組み合わせ」
「マクロを作る」
「Accessのクロス集計クエリを複数作って組み合わせる」等が考えられますが、
この【行と列の組み合わせでVLOOKUPのKEYを作る戦法】が、お手軽度と自由度が高いです。
