複数の列要素を組み合わせたクロス集計表を、短時間で作りたい時のオススメの方法です。
書式を整える時間と除けば10~15分で出来ます。

↓こんなイメージの表です。(クリックすると拡大表示します)
ピボットテーブルは数値表示のみなので、対応できないですし、Accessでクロス集計で作業するには列要素が多すぎます。

こんな考え方で対応します。

「行と列の組み合わせでVLOOKUPのKEYを作る」戦法が有効です。

 

(ちなみに、同じ発想でこんなのもリストから一発で作れます。これも慣れれば15分で出来ます。
↓作り方は、次回にご案内しています。クリックすると拡大表示します。)
(副部長が二人以上いる場合に備えて、部署と役職名をKEYに重複連番を振るのがコツ)

 

事例でご案内します。

事例説明

ニーズ :複数の列要素(数値と文字両方あり)があるクロス集計表を作りたい。

 

依頼事項:営業一部から三部の各人の3年間の実績を一覧出来るようにまとめる。

 

留意点:
・単純に売上だけでなく、担当先毎の売上も表示する。(特に重要なA社とB社がわかるようにする)
・プロジェクトを担当したことがあれば表示する。
・後から他の要素も必要になる可能性があるので、簡単に表示要素が変更できるように作る。

 

対応方法
「行と列の組み合わせでVLOOKUPのKEYを作る」戦法
・試行錯誤は必要ですが、イメージさえ出来れば、作業自体は15分もあれば出来ます。

 

元データの特徴
・経年毎・各人別の売上データベースがある。

 

・出来上がり(クリックすると拡大表示します)

 

必要なスキル
以下の様に基礎的なスキルがあれば、作業できます。
・VLOOKUP関数(とIF関数とIFERROR関数)が使える。
・複合参照(どちらかに「$」がつくもの。「$A1」 か「A$1」)を理解して使用できる。
・ピボットテーブル作成スキル
・異動している人がいて、複数の行に表示している場合は、その表示が出来るようにCOUNTIF関数

・セルに色をつけるなら条件つき書式

手順です。

(私の考える最短ルートです。これなら、Excel作業は仕上げを除けば15分で出来る筈です。)

1.今回は元リストを作成する際にAccessのクエリを作り、エクスポートする。

Excelでの作業から勘案して、年度は降順にする。
(重複するデータがある時はVLOOKUP関数は上の行のデータを見るので)
(注意)クエリを作るときは、名称だけでなく、コードもつけること
(例:所属名称だけでなく、所属コードも。そうしないと並び替えで不自由します。)

2.エクスポートしたリスト

これを元データにする。(クリックすると拡大表示します。)

3.作業前の留意事項

人によって、以下のようなケースがあるので、クロス集計表上で上手く反映するように考えておく。
・営業一部から三部に異動している(=年度によって所属部が異なる)
・改姓している(=同じ社員番号でも姓名が異なる)
・同じ部署にいて昇格等で職位が異なる(=同じ社員番号・部署でも年度により、「リーダー」→「課長」になっている)

4.[元データシート作業]KEY列を作る

一番左に列挿入して、KEY列を作る。(VLOOKUP関数に使うので左側にする)
今回は上記留意事項から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を作る戦法】が、お手軽度と自由度が高いです。