エクセルでの該当する条件に一致する値の抽出

質問

エクセル初心者で困っています。
どなたかお助け下さい。

以下の様な表があります。

A 　 B 　C 　D 　 E 　F 　 G
1商品名 入荷数　消費1　消費2　性能1　性能2　性能3
2○○　　2　　　1　　　2　　　△△　 ◆◆　 □□　　
3××　　2　　　1　　　2　　　□□　 △△　 ◆◆
4○○　　2　　　1　　　2　　　●●　 □□　 ◎◎

この表を基に、別シートを以下の様に作成して

A 　 B 　C 　D 　 E 　F 　 G
1性能　【◆◆】
2
3商品名 入荷数　消費1　消費2　性能1　性能2　性能3
4○○　　2　　　1　　　2　　　△△　 ◆◆　 □□　　
5××　　2　　　1　　　2　　　□□　 △△　 ◆◆

セルB1に性能名を入力して、入力した値と
基となるシートのEFGの列から一致する値があった場合
該当する行をそのまま表示するような式を作りたいのですが可能でしょうか？

分かりづらい質問で申し訳ありませんが
ご回答いただけるとありがたいです。

回答No.5

imogasi · ベストアンサー

Googleででも「imogasi方式」で照会すれば私の回答した質問に関連した、関数による抜き出し問題が出ています。関数しか知らない人は何でも関数でやらざるを得ないケースが多い。
そこには他の方の回答も載ってますが、初心者には理解できない関数式の組み合わせの方法や配列数式というのも在ります。概して関数による抜き出しは難しく。抜き出し先のシートは全セルが関数で埋まり、処理が重たくなるわけです。
ーー
「imogasi方式」は作業列（下記の場合H列）を使います。それだけにやや判りやすいと思う。
Sheet1の各行で性能１-性能Xまでの列に◆◆があれば（COUNTIF関数利用）、上の行から連番を振って行きます。
例データ
商品名 入荷数 消費1 消費2 性能1 性能2 性能3 連番
○○ 2 1 2 △△ ◆◆ □□ 1
×× 2 1 2 □□ △△ ◆◆ 2
○○ 2 1 2 ●● □□ ◎◎
aa 2 1 2 ◆◆ xx ss 3
bb 2 1 2 zz dd tt
cc 2 1 2 uu yy ◆◆ 4
SHEET2のA1に◆◆
ーーー
Sheet1の
H2の式は　=IF(COUNTIF(E2:G2,Sheet2!$A$1)>0,MAX($H$1:H1)+1,"")
下方向に式を複写する。
これで連番が振られる。
Sheet2は
A3に　=INDEX(Sheet1!$A$1:$G$7,MATCH(ROW()-2,Sheet1!$H$1:$H$7,0),COLUMN())
横方向にG列まで式を複写。
A3:G3の式をA６：G6まで式を複写。
結果
◆◆
商品名 入荷数 消費1 消費2 性能1 性能2 性能3
○○ 2 1 2 △△ ◆◆ □□
×× 2 1 2 □□ △△ ◆◆
aa 2 1 2 ◆◆ xx ss
cc 2 1 2 uu yy ◆◆
ーーー
本来はVBAで処理するなどに適した課題だと思う。
＝＝＝＝
操作でフィルタオプションの設定などを勉強するのが、エクセルの
予期する方法と思う。

お礼

はい。。。
色々と過去の質問・回答なども拝見させていただいたのですが
いかんせん初心者なもので難解でした^^;
分かりやすい解説に今後の勉強までご教示いただき有難うございます。
VBAなども視野に入れてスキルアップを図っていきたいと思います。
有難うございました！

回答No.4

135ok

No.１です。一部訂正します。

一例です。
添付資料を参照ください。

作業列を入れて
No.　判定　商品名 入荷数　消費1　消費2　性能1　性能2　性能3
Ａ２　=IF(B2="","",COUNTIF($B$2:B2,B2))
Ｂ２　=IF(Sheet2!$B$1="","",IF(COUNTIF(G2:I2,Sheet2!$B$1),"○",""))

Ｂ、Ａの順番で説明します。
Ｂ２は、Sheet2!$B$1が空欄の時は空欄、以外は、文字が含まれている場合は○を、無い場合は空欄を表示。
Ａ２は、Ｂ２が空欄の時は空欄、以外は、Ｂ列に○が何個あるか表示。
（表示方法は、上から１、２）

sheet2
Ａ列に作業列を作成し、番号をつける。
Ｂ４　=IF(ISNA(VLOOKUP($A4,Sheet1!$A$1:$I$4,COLUMN(A4)+2,0)),"",VLOOKUP($A4,Sheet1!$A$1:$I$4,COLUMN(A4)+2,0))
　　　*Ａ列の文字がsheet1に無い時は空欄、以外は、番号にあった値をsheet1から表示。

お礼

数式に対する補足までいただき有難うございます。
今後は自分でも色々と勉強してスキルアップしたいと思います。
有難うございました。

回答No.3

KURUMITO

シート1に元のデータがあるとしてＨ2セルには次の式を入力して下方にオートフィルドラッグします。
=IF(Sheet2!B$1="","",IF(COUNTIF(E2:G2,Sheet2!B$1)=0,"",MAX(H$1:H1)+1))
シート１のＡ1セルからＧ1セルをコピーしてシート2のＡ3セルに貼り付けます。
シート2のＡ4セルには次の式を入力して右横方向にオートフィルドラッグしたのちに下方にもオートフィルドラッグします。
=IF(OR(A$3="",COUNTIF(Sheet1!$H:$H,ROW(A2))=0),"",INDEX(Sheet1!$A:$G,MATCH(ROW(A1),Sheet1!$H:$H,0),COLUMN(A1)))

お礼

有難うございます。
参考にさせていただきます。
大変助かりました。

回答No.2

tom04

こんばんは！
色々方法はあるかと思いますが・・・

一例です。
↓の画像のような感じで良いのでしょうかね？
もしそうであれば、

Sheet1のA列を作業用の列とさせてもらっています。

Sheet1のA2セルに
=IF(Sheet2!$B$1="","",IF(COUNTIF(B2:H2,Sheet2!$B$1),ROW(A1),""))
という数式を入れて、オートフィルで下へコピーします。
（Sheet2の数式が1000行まで対応できるようにしていますので、
1000行くらいまでコピーしても構いません）

そして、Sheet2のA4セルに
=IF(COUNT(Sheet1!$A$2:$A$1000)>=ROW(A1),INDEX(Sheet1!$B$2:$H$1000,SMALL(Sheet1!$A$2:$A$1000,ROW(A1)),COLUMN(A1)),"")
という数式を入れ、オートフィルで列方向と行方向にコピーすると
画像のような感じになります。

以上、参考になれば幸いですが、
他に良い方法があれば読み流してくださいね。m(__)m

お礼

分かりやすいように添付ファイルまで付けていただき
有難うございます。
今後は自分でも色々と勉強してスキルアップしたいと思います。
大変助かりました。
有難うございました。

回答No.1

135ok

一例です。
添付資料を参照ください。

作業列を入れて
No.　判定　検索　商品名 入荷数　消費1　消費2　性能1　性能2　性能3
Ａ２　=IF(B2="","",COUNTIF($B$2:B2,B2))
Ｂ２　=IF(Sheet2!$B$1="","",IF(ISNUMBER(FIND(Sheet2!$B$1,$C2)),"○",""))
Ｃ２　=H2&I2&J2

Ｃ、Ｂ、Ａの順番で説明します。
Ｃ２は、性能１～３の文字を足して検索対象としています。
Ｂ２は、Sheet2!$B$1が空欄の時は空欄、以外は、文字が含まれている場合は○を、無い場合は空欄を表示。
Ａ２は、Ｂ２が空欄の時は空欄、以外は、Ｂ列に○が何個あるか表示。
（表示方法は、上から１、２）

sheet2
Ａ列に作業列を作成し、番号をつける。
Ｂ４　=IF(ISNA(VLOOKUP($A4,Sheet1!$A$1:$J$4,COLUMN(A4)+3,0)),"",VLOOKUP($A4,Sheet1!$A$1:$J$4,COLUMN(A4)+3,0))
　　　*Ａ列の文字がsheet1に無い時は空欄、以外は、番号にあった値をsheet1から表示。

この投稿のマルチメディアは削除されているためご覧いただけません。