リストから必要なデータを自動抽出したい

質問

表1のリストから表2を自動で作成したいのですが、方策が思い浮かばず困っております。
　※表1は変動するので範囲選択時は最終行と最終列として自動認識させたい。

関数やマクロをうまく組み合わせれば出来そうな気がするのですが・・・宜しくご教授願います。

回答No.3

KURUMITO · ベストアンサー

多少複雑な作業になりますが作業列や行を作って対応します。
ここでの方法はデータが追加されてシート１ではC列からZ列までにデータが入力され、１０００行目までの表に対応できる式などを示します。それ以上になるのでしたらここでの方法を範囲を広げて応用してください。
シート１のAA５セルには次の式を入力して式を確定する段階でCtrlキーとShiftキーを押しながらEnterキーを押します。その式をAZ５セルまで横方向にドラッグコピーしたのちに１０００行目まで下方にもドラッグコピーします。

=IF(COLUMN(A1)>COUNT($D5:$Z5),"",SMALL(IF($D5:$Z5<>"",COLUMN($A5:$W5),1000),COLUMN(A1)))

シート３に表から抽出するe,k,bなどの表をA１セルから下方に入力した場合としていますがここでは式を簡単にするためにシート２のC１セルから横方向にe,k,d,aのように列を変えてそれぞれ入力します。
そこで再び作業列をシート２の２行目と３行目に用意します。
C２セルには次の式を入力して右横方向にドラッグコピーします。

=IF(COUNTIF(Sheet1!$C$4:$C$1000,C$1)=0,"",COUNT(INDEX(Sheet1!$D$4:$D$1000,MATCH(C$1,Sheet1!$C$4:$C$1000,0)):INDEX(Sheet1!$Z$4:$Z$1000,MATCH(C$1,Sheet1!$C$4:$C$1000,0))))

B３セルには０を入力します。
C３セルには次の式を入力して右横方向にドラッグコピーします。

=IF(AND(C1<>"",C2<>""),B3+C2,IF(AND(C1<>"",C2=""),B2+1,""))

そこでお求めの表２ですがC５セルには次の式を入力して下方にドラッグコピーします。

=IF(ROW(A1)>MAX(B$3:Z$3),"",INDEX(C$1:Z$1,MATCH(ROW(A1)-0.1,B$3:Z$3,1)))

D５セルには次の式を入力してE５セルまでドラッグコピーしたのちに下方にもドラッグコピーします。

=IF(OR($C5="",COUNTIF(Sheet1!$C$4:$C$1000,$C5)=0),"",IF(COLUMN(A1)=1,INDEX(Sheet1!$D$4:$Z$4,INDEX(Sheet1!$AA$4:$AZ$1000,MATCH($C5,Sheet1!$C$4:$C$1000,0),COUNTIF(C$5:C5,C5))),IF(COLUMN(A1)=2,INDEX(Sheet1!$D$5:$Z$1000,MATCH($C5,Sheet1!$C$5:$C$1000,0),INDEX(Sheet1!$AA$5:$AZ$1000,MATCH($C5,Sheet1!$C$5:$C$1000,0),COUNTIF($C$5:$C5,$C5))),"")))

これでシート１での表がZ列まで、１０００行までのものについて、また、表を作るための検索項目であるe,k,d,aなどについてもかなりの数まで入力しても処理することができます。
マクロを使っても対処することができますが表１での入力した結果を表２に瞬時に反映できる点では関数で処理することが優れています。最初に関数などを設定するのは大変ですが一度入力して設定しておけばよいのでぜひご検討ください。

回答No.4

某HN クロメート(Chromate)（@CoalTar）

添付図参照で

作業列 B1セルに
=IF(A1="","",MAX(1,COUNT(INDEX($D$5:$G$9,MATCH(A1,$C$5:$C$9,0),))))
下へオートフィル

表2のI4セル
=A1
I5セル
=IF(COUNTA(I$4:I4)>=SUM(B:B),"",
IF(SUM(B$1:INDEX(B:B,MATCH(I4,A:A,0)))>COUNTA(I$4:I4),I4,
INDEX(A:A,MATCH(I4,A:A,0)+1)))
下へオートフィル

＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊

J4セル
=IF(ISNA(MATCH(I4,$C$5:$C$9,0)),"",
INDEX($D$4:$G$4,SMALL(IF(
INDEX($D$5:$G$9,MATCH(I4,$C$5:$C$9,0),)<>"",{1,2,3,4}),
COUNTIF($I$4:I4,I4))))
[Ctrl]+[Shft] ＋[Enter] で確定、配列数式。{ }で挟まれる
下へオートフィル

K4セル
=IF(J4="","",INDEX($D$5:$G$9,MATCH(I4,$C$5:$C$9,0),MATCH(J4,$D$4:$G$4,0)))
下へオートフィル

I:K列を切り取って Sheet2へ
A:B列を切り取って Sheeet3へ

回答No.2

noname#204879

とてもお奨めできるものではないけど、面倒だが“やってやれないことはない”例を添付図に示しておきます。説明は割愛します。

お礼

なるほど！確かに面倒かもしれないけど実現できそうです。
マクロで処理すれば何も問題ないと思います。
是非チャレンジしてみたいと思います。
貴重なアドバイスありがとうございました。

回答No.1

KURUMITO

表２のC列でアルファベットの種類とかはどのように決まるのですか？
表１ではKが無いから空白となっていますがそもそもそのKはどこに入力されているのでしょう。

補足

説明が不足しておりました。申し訳ございません。
Sheet3のA1セルにe、A2セルにk、A3セルにd、A4セルにaが入力されています。
このA列に入力されるアルファベットやその数は変動します(ここでは4個）。
表２のC列にはSheet3の列に入力に入力されているアルファベットが入るようにし且つ表１に存在するかしないか自動（数式やマクロ）で判断し、マッチしたアルファベットの数全てについて名前と数値を表示した表２を作成したいです。
　※kのように表１に存在しなかった場合でも表２では空白のままで表示させたいのです。
何卒宜しくお願い致します。