excelで縦1列の名簿を縦3列の名簿に反映

質問

先日、以下の質問を致しました。
https://okwave.jp/qa/q9321281.html

excelでデータが縦に並んだ、名簿のマスターデータの
ブックがあります。（添付画像上部参照）

そのマスターデータから、別のexcelブックに
課名と氏名のみを抽出し、縦3列の名簿を
作成したいです。（添付画像下部参照）

今は課ごとにコピー＆ペーストにて対応していますが、
マスターデータが変更されたら、縦3列の名簿の内容も
自動的に反映されるようにしたいです。

前回の質問では
『課の人数は流動的なので、課のセルの固定はできません。』
としましたが、難しそうなので、課のセルは固定したとして
実現はできないでしょうか？

回答No.2

msMike · ベストアンサー

簡単のために、「マスターデータ」を Sheet1 として、貴方の「添付画像下部」が Sheet2 にあるものとしているので、参考になれ幸いです。後は貴方が応用するだけ！

ドナタかが「不可能」と断言された「関数だけ」でヤッテみました。

Sheet2 において、
C3: =IFERROR(INDEX(Sheet1!C:C,SMALL(IF(Sheet1!M:M=B$3,ROW(A:A),""),ROW(A1))),"")
C12: =IFERROR(INDEX(Sheet1!C:C,SMALL(IF(Sheet1!M:M=B$12,ROW(A:A),""),ROW(A1))),"")
C19： =IFERROR(INDEX(Sheet1!C:C,SMALL(IF(Sheet1!M:M=B$19,ROW(A:A),""),ROW(A1))),"")
J3: =IFERROR(INDEX(Sheet1!C:C,SMALL(IF(Sheet1!M:M=I$3,ROW(A:A),""),ROW(A1))),"")
P3: =IFERROR(INDEX(Sheet1!C:C,SMALL(IF(Sheet1!M:M=P$3,ROW(A:A),""),ROW(A1))),"")
【お断り】上式はすべて配列数式として入力のこと

「VBAだとプログラムの意味が分からない」のは私と同じだけど、「配列数式として入力」は分っかるのかなぁ～？
それも駄目なら、諦めましょ！
http://www.youtube.com/watch?v=h0N7g8X7erg

お礼

ご回答ありがとうございます。
無事に配列数式として入力してできました！！
私でも理解できる方法で助かりました！

回答No.6

bunjii

回答No.5の追加です。
B列、I列、P列に予め「課名」が入力されているものとします。
「氏名」を抽出する左上のセル番地がC3とすれば次の数式で「氏名」を抽出するすべてのセルの共通数式は次のようになります。
C3の数式
=IFERROR(INDEX([マスターデータ.xlsx]Sheet1!$C$1:$C$50,LARGE(INDEX(([マスターデータ.xlsx]Sheet1!$M$1:$M$50=INDEX(B:B,MAX(INDEX((B$3:B3<>"")*ROW(B$3:B3),0))))*ROW([マスターデータ.xlsx]Sheet1!$M$1:$M$50),0),COUNTIF([マスターデータ.xlsx]Sheet1!$M$1:$M$50,INDEX(B:B,MAX(INDEX((B$3:B3<>"")*ROW(B$3:B3),0))))-ROW()+MAX(INDEX((B$3:B3<>"")*ROW(B$3:B3),0)))),"")
C3セルの数式をC列、J列、Q列の必要なセルへコピーすれば目的に合います。
尚、ブック名（ファイル名）は3列にする名簿と同時に開いているときのものです。（[マスターデータ]が元データのブック名です）
マスターデータが閉じているときは数式を修正するとエラーになるかも知れません。
課名も自動的に抽出するには条件が明示されていませんので改めて質問してください。

回答No.5

bunjii

基本の数式はBook1.xlsxのC3セルに設定する次の数式になります。
=IFERROR(INDEX([マスターデータ.xlsx]Sheet1!$C$1:$C$50,LARGE(INDEX(([マスターデータ.xlsx]Sheet1!$M$1:$M$50=B$3)*ROW([マスターデータ.xlsx]Sheet1!$M$1:$M$50),0),COUNTIF([マスターデータ.xlsx]Sheet1!$M$1:$M$50,B$3)-ROWS(C$3:C3)+1)),"")
貼付画像の各課毎に数式を調整します。
例えばBook1.xlsxのC13セルは次のようになります。
=IFERROR(INDEX([マスターデータ.xlsx]Sheet1!$C$1:$C$50,LARGE(INDEX(([マスターデータ.xlsx]Sheet1!$M$1:$M$50=B$13)*ROW([マスターデータ.xlsx]Sheet1!$M$1:$M$50),0),COUNTIF([マスターデータ.xlsx]Sheet1!$M$1:$M$50,B$13)-ROWS(C$13:C13)+1)),"")
貼付画像のBook1.xlsxのC3セルをC12セルまでコピーします。
Book1.xlsxのC13セルをC19セルまでコピーします。
各列の先頭（C3、J3、Q3）はC3セルをJ3セルとQ3セルへコピーすれば自動的にチェック項目（部課）を相対変化します。
Book1.xlsxの「課名」を入力する位置が部課毎に絶対アドレスで参照しなければならないので基本の数式をすべての部課に適用することができません。

回答No.4

tsubu-yuki

> 課のセルは固定したとして
なら「なんとか」実現に近づいた、とは言えるかもしれません。
ＶＢＡもなし、配列数式として～もなし、
ただし、作業列だけは（解りやすくするためにも）使わせていただきます。

各関数の意味はお調べいただくとして、
その意図を「なんとか」読み解いてください。
かなり簡略化していますが、ご提示の表に似せた表を用意しました。
範囲はＢ：Ｐ列ですね。
ここに「作業用の列」として、Ｑ列を追加しています。
内容は
　　Ｑ５セル：=M5&TEXT(COUNTIF(M5:$M$5,M5),"000")
要するに、同じ課の上から数えて何番目を拾っています。
課員が１０００人以上ってことはないでしょう・・で３桁です。
　※ご自身の表の位置に合わせて変えてくださいね。（以下省略）

本番です。
Ｔ列以降に結果を表示しています。
Ｔ４セルに「管理課」の文字があるとして、
　　Ｕ４セル：=IFERROR(INDEX($C:$C,MATCH(T$4&TEXT(ROW(A1),"000"),$Q:$Q,0)),"")
長いですが、頑張って読み解いてください。
端的に言うと、
・管理課の上から〇〇番目が
・Ｑ列の何行目にあるのか調べて
・Ｃ列から該当する行を探して返す
・なお、見つからなければ空白を返す
という感じの式です。
これを、必要な分だけ行方向にフィル。
添付図ではＵ８セルまでフィルしています。
続けて、
　　Ｕ９セル：=IFERROR(INDEX($C:$C,MATCH($T$9&TEXT(ROW(A1),"000"),$Q:$Q,0)),"")
　　Ｕ１４セル：=IFERROR(INDEX($C:$C,MATCH($T$14&TEXT(ROW(A1),"000"),$Q:$Q,0)),"")
　　Ｘ４セル：=IFERROR(INDEX($C:$C,MATCH(W$4&TEXT(ROW(A1),"000"),$Q:$Q,0)),"")
　　ＡＡ４セル=IFERROR(INDEX($C:$C,MATCH(Z$4&TEXT(ROW(A1),"000"),$Q:$Q,0)),"")
とし、それぞれフィルします。
で、式は完成です。

作業列は文字列を白くしたり非表示にしたりすれば
見た目に邪魔、ということもなかろうと思います。

決して不可能ではありません。
が、なるべくであれば理解できる関数で作る方がいいです。
あとでメンテナンスできなくなるのが一番怖いことだと思いますから。
とりあえず、ＩＮＤＥＸとＭＡＴＣＨの組み合わせはいろいろ便利ですよ。

回答No.3

SI299792

ということは、マクロの組み込み方、マクロの実行方法はご存知ですね。

With Windows("Book1.xlsx").ActiveSheet
のところを、別のexcel ブック（つまり、出力側）の名前に変更するだけです。
マスターが４行目、出力が３行目で始まっているのが前提ですが。
表のレイアウトの変更がない限り、意味は分からなくていいと思います。
サンプルデータと実データでレイアウトが違うなら、話は別ですが。

もし、マクロの組み込み方、マクロの実行方法がわからないなら、ご記入ください。できるだけ説明します。

回答No.1

SI299792

私の作ったマクロは試しましたか？　マクロがうまく行かなかったのか（うまく行かなかったなら、どうなったのか）、マクロを使いたくない事情があるのかを書いてください。
　関数だけでは不可能だと思います。

補足

VBAだとプログラムの意味が分からないので、
どこをどのように変更したら良いか分からず、
自分のブックでの応用ができませんでした。