【Excel】「同じ部の副部長2名を1行で表示」するようなクロス集計表を作る(COUNTIFで連番列を作り、VLOOKUPのKEYに使う)

 

項目毎に複数の要素があり、その要素数が不定な時に、1行に並べたい時にも、このワザは使えます。

 

説明しずらいのですが、こんなケースです。
Excelクロス集計1行にまとめる
上の図のように、
一覧リストから、名簿を作る際に、同じ役職で複数名いて、1行に並べることができます。
COUNTIF関数で「連番列」を作り、その「連番列」が横軸になるわけです。

 

これも、慣れれば15分かかりません。
※Accessのクロス集計を使ったり、
ピボットで社員番号を列表示して(ピボットは数値のみなので)、VLOOKUPで氏名を呼び出したり、
VBAを書いていた5年前の自分に教えてあげたい。。

 

連番を振って、その連番を使い、複数のKEYを作り、VLOOKUP関数で表示します。
何を言っているかわかりづらいと思いますので、以下一つずつ手順を記載します。

1.元データと最終形をイメージする

(図をクリックすると拡大します)

2.元の名簿の下準備

一番左に3列挿入する。
一番上に1行挿入して、COLUMUN()で列番号を表示する。

(図をクリックすると拡大します)

3.複数のKEYを作る

(図をクリックすると拡大します)

 

①C列を「KEY1」とする。
重複をチェックするべき要素を結合する。
=G3&”-“&I3 (所属部-職位名 例:営業一部-部長)
※「-」ハイフンは、後から見やすくするためです。

 

②B列で「KEY1」の重複をチェックし、連番を振る。
=COUNTIF($C$3:C3,C3)
COUNTIF関数の連番の振り方については、
【Excel】重複がある際に適切なデータを選ぶ(COUNTIF関数を使う)の、「2.一番上の行を取得したい時」をご参照ください。

 

③A列を「KEY2」として、C列とB列を結合させる
=C3&”-“&B3(所属部-職位名-連番 例:営業一部-部長-1)
◆このKEY2をVLOOKUP関数に使う

4.出来上がりの「リストの箱」とVLOOKUP関数に使うキー廻りをセットする。

(図をクリックすると拡大します)

 

・左軸 所属部
・横軸1 職位名
    連番の最大値まで「箱」を作る
    (最大値は、適宜MAX関数やら、ピボットやら使っても良いし、多めに作って、後で消しても)
・横軸2 連番
・横軸3 表示するデータの列番号をセットする
    VLOOKUP関数の「○」列目

 

※最大値の確認をピボットテーブルで実施する場合

5.行列を複合させたKEYを作り、VLOOKUP関数で、3で作ったA列を参照する

=IFERROR(VLOOKUP(($V6&”-“&W$1&”-“&W$2),$A:$I,W$3,0),””)
(IFERROR関数により、VLOOKUPで該当が無い時のエラーコードを表示しないようにする)

 

そして範囲内全てに「式コピー」
(図をクリックすると拡大します)

6.作業用の行を隠し、適宜レイアウトを整える。

出来ました。今回はIDと姓名だけにしましたが、項目はいくらでも増やせます。