リストのデータをマトリックス図へ移す関数

添付写真は元データとなるリストを上部、データを移したいマトリックス表を下部に、同じシートに作成しています。実際は別々のシートです。
リストにある所属部署と等級を基準に、マトリックス表の部署と等級が交錯するセルに、該当する氏名と評価を表示されるようにしたいです。（添付写真のマトリックス表が理想形です）

Excel初心者で、自分なりに調べましたが、マトリックス表からのデータ抽出の方法しか見当たりませんでした。ご回答宜しくお願いします。

質問者からの補足コメント

• 

  追加質問させて下さい。
  
  マトリックス表に表示させる項目を増やす場合は、どの関数の範囲を広げればいいのでしょうか？元データとなるリストのF列に年齢を追加するとして教えていただければ幸いです。
  また、表示する人数を増やす為、行を増やすとすれば、どの関数の範囲を広げればいいのでしょうか？
  
  質問ばかりで申し訳ありませんが、宜しくお願い致します。

  No.1の回答に寄せられた補足コメントです。 補足日時：2019/03/19 17:07

• 

  お返事ありがとうございます。
  おっしゃられていることは分かります。
  教えていただいた数式の関数を分けて、調べましたが、以下の部分だけでも教えて下さい。
  (COLUMN()/2)*2)やINT(ROW()/3)*3)やMOD(COLUMN(),2)*2 +1)にみられる【＊2や＊3】はどの部分を指しているのでしょうか。

  No.2の回答に寄せられた補足コメントです。 補足日時：2019/03/20 12:12

• 

  詳細にご説明いただきありがとうございます。
  単体の関数では理解できるのですが、複数組み合わせると上手くいきません。
  添付の画像の通り、元データと部門別・等級別のシートを作成しています。元データシートは他のシートからVLOOKUP関数で導いています。一覧表のA列は20行ごと、部署は5列ごとになっています。
  
  =IFERROR(INDEX(元データ!$B$2:$I$200,SMALL(IF((元データ!$C$2:$C$200=INDEX($3:$3,INT(COLUMN()/5)*2))*(元データ!$D$2:$D$200=INDEX($A:$A,INT(ROW()/5)*3)),ROW(元データ!$B$2:$B$200)),MOD(ROW(),20)+1),MOD(COLUMN(),5)*2+1),"")
  
  申し訳ありませんが、上記の計算式を指摘していただけないでしょうか。

  
  No.3の回答に寄せられた補足コメントです。 補足日時：2019/03/20 20:31

No.4 回答者： fujillin 回答日時： 2019/03/21 04:11

No3です。

No2の回答を再読していただくのが宜しいように思います。

No.3 回答者： fujillin 回答日時： 2019/03/20 12:32

No2です

＞【＊2や＊3】はどの部分を指しているのでしょうか。
セルに計算式を入れて、ドラッグしてみるるわかると思います。
ROW()、COLUMN()は自身のセル位置を意味します。
（計算上は、ROW(A1)とかCOLUMN(B1)のように、都合の良い位置を指定しておくのもありです）

=INT(COLUMN()/2)*2 を入力して横にコピーすれば、2,2,4,4,6,6…と
=INT(ROW()/3)*3 を縦にコピーすると、9,9,9,12,12,12,15,15,15…と並ぶはずです。
それぞれセル結合された部署と等級（8行目とA列）を参照するためのセル位置を計算しています。
セル結合的なレイアウトになっている場合、このような計算が増加する分だけ、式が複雑でわかりにくいものになります。

一方、=MOD(COLUMN(),2)*2+1 を右方向にコピーすると、1,3,1,3,1,3…となり
元になる表から参照したい列数を示します。
（１列おきに、氏名と評価を参照しているので、1、3…となる）
この部分も、表の作り方次第では、不要にできるかもしれない計算と言えるかも知れません。

No.2 回答者： fujillin 回答日時： 2019/03/20 11:24

No1です。

＞表示する人数を増やす為、行を増やすとすれば、
＞どの関数の範囲を広げればいいのでしょうか？
No1の参考に挙げた式で、最終的に参照しているのは
　INDEX($B$1:$D$5, 行番号, 列番号)
の形式になっているので、対象範囲を$B$1:$D$5としています。
その範囲から欲しいデータを選択するために、「行番号」「列番号」を正しく計算する必要があるので、範囲だけ拡張しても何も起こりません。
併せて、行、列の計算など、全体を再構成する必要があります。

ついでながら、No1の回答で伝わっていなかったと思われるので、以下を補足しておきます。
回答の添付図では、データ入力は簡略化していますが、セルの結合した状態と同等になるように想定していますので、元のご質問にあるようなセルの結合を含む表に対して計算できるように関数式を作成してあります。
（セル結合を省いて計算式を簡略化するのなら、「部署名や等級を該当する各セルに入れておく」ことで実現できます。）


実は、No1の回答の意図は、回答の前半は
・ご質問の表形式には欠点があるように見受けられるし、表計算として勧められるものではないので考え直した方が良いのではないか。
ということと、後半は
・「どうしてもこの形式でやりたい」と言うのであれば、技術的には不可能ではないと思うけれど、大変、面倒でわかりにくいものになりやすいですよ。
（率直に言えば、やめた方が良いと思いますの意）
というつもりでした。

なお、追加質問に関しては、すでに別のトピで質問なさっていらっしゃるようなので、そちらにお任せすることにいたします。
https://oshiete.goo.ne.jp/qa/11035579.html

No.1 回答者： fujillin 回答日時： 2019/03/19 09:47

こんにちは

＞添付写真のマトリックス表が理想形です
大変失礼ながら、およそ表計算には向いていない表形式になっていると思われます。

セルの結合を行っているので、計算を無駄に複雑にしています。、
何よりも「該当者が4人以上いる場合に、記載欄が用意されていない」という大きな欠点があります。
（3人より少ないことが保証されているのなら別ですが）
元のデータが何人分あるのかは不明ですが、自動で処理させようというくらいの人数であるなら、ある程度の人数なのでしょうから、すぐに破綻しそうなことが目に見えています。

とは言うものの、不可能ではありませんので、以下は計算方法のご参考までに。

添付図は必要なデータしか入力していないので、一部ご提示のデータとは異なります。
B9セルに以下の式を入力し、
=IFERROR(INDEX($B$1:$D$5,SMALL(IF(($C$2:$C$5=INDEX($8:$8,INT(COLUMN()/2)*2))*($E$2:$E$5=INDEX($A:$A,INT(ROW()/3)*3)),ROW($B$2:$B$5)),MOD(ROW(),3)+1),MOD(COLUMN(),2)*2 +1),"")

Ctrl+Shift+Enterで確定します。（←配列数式なので必須です）
確定後、右、下方にフィルコピーしています。
（セル位置に依存する式になっていますので、移動する場合はセル範囲・位置計算の調整が必要になります）

この回答へのお礼

早速のご回答ありがとうございます！
添付いただいた写真通りにサンプルを作成したところ、希望通りの結果となりました。感動すら覚えました。

マトリックス表に表示させる項目を増やす場合は、どの関数の範囲を広げればいいのでしょうか？元データとなるリストのF列に年齢を追加するとして教えていただければ幸いです。

宜しくお願い致します。

お礼日時：2019/03/19 14:21