エクセルで検索

質問

エクセルシートに商品名が１０００種類くらいあります。
そのなかからaaaの文字列を持つものをすべて検索し、検索結果の中からaaa-xxを特定のセルに表示させたいのです。
現在は、編集→検索→aaaを入力→すべて検索→aaa-xxを選択→上の入力欄に表示されたものをコピーして特定のセルにペースト　という手順でやています。
この操作をもっと単純にできないでしょうか。
ご存知の方よろしくお願いいたします。

（特定のセルに商品名をペーストすると、その商品の月別売上が表とグラフになるようになっています。）

回答No.4

Nouble · ベストアンサー

利用者側が判らないシート(または関数)は破棄される
と言う原則に立ち返ってみました

Ａ３から下にずらっと商品名データがＡ１００３まで羅列しているものとします

Ｄ３とＣ５：Ｄ１００５て使って良いですか？
まずＤ３を選択しセルの書式設定から表示形式・ユーザー定義と進み
「@"を検索します"」
と設定してください

次にＤ４を選択
上のデータプルダウンメニューから「入力規則」を選択
設定タブ内の「入力値の種類」の項から「リスト」を選択
同タブ内の　「元の値」に「=$D$5:$D$65536」と入力
「空白を無視する」と「ドロップダウンリストから選択する」の双方のチェックにレ印を付けておいてください

Ｃ４を選択しセルの書式設定から表示形式・ユーザー定義と進み
「""」と設定した上で数字で2と入力してください

Ｃ５を選択しセルの書式設定から表示形式・ユーザー定義と進み
「"Ｃｅｌｌ　A"#」と設定してください

では行きます
C5　=IF(ISERROR(MATCH($D$3&"*",INDIRECT("A"&C4+1&":A2500"),0)),"",MATCH($D$3&"*",INDIRECT("A"&C4+1&":A2500"),0)+C4)
D5　=IF(C5<>"",OFFSET($A$1,C5-1,0,1,1),"")
と入力後
C5:D5を選択しC6:D1003にコピーしてください

検索にヒットしたデータ一覧とその所在が現れ
Ｄ４をクリックすると現れる▼をクリックすることにより上記のリストが入力値候補として選択できます

ちょっと説明しますと
MATCH構文はMATCH(検索キー,検索範囲)で
　　　　　検索範囲の最初から数えて何番目で検索キーが見つかったかを返します
ISERROR構文はISERROR(評価対象)で
　　　　　評価対象がエラー値を示したときにTrueを返します
INDIRECT構文はINDIRECT(セル範囲)またはINDIRECT("セル範囲")で
　　　　　「"」で囲われているときはセル範囲の値を読み出し
　　　　　囲われていないときはセル範囲に書かれているC10などの文字から相対参照をして値を読み出します
　　　　　尤も通常はOFFSET構文同様
　　　　　セル範囲を配列数式として読み出すためや
　　　　　参照先を可変としたいときによく使われます
　　　　　OFFSET構文との違いは参照先を文字列で指定することです

OFFSET構文はOFFSET(基準位置,基準位置から参照先左上隅までの縦方向の距離,基準位置から参照先左上隅までの横方向の距離,参照行数,参照列数)で
　　　　　INDIRECT構文同様
　　　　　セル範囲を配列数式として読み出すためや
　　　　　参照先を可変としたいときによく使われますが
　　　　　INDIRECT構文との違いは参照先を基準位置以外のパラメーターを数値で指定することです
&は単純にその前後のものを文字列とみなし結合します
「&"*"」の部分は与えられた検索キーに対し正規表記文字(ワイルドカード)の*を追加することにより検索キーから始まる文字列なら何にでもヒットするようにしています

もっと分かり易い構文を示そうと思ったのですが力及びませんでした
陳謝　<(_ _)>

如何でしょうか?

お礼

これはすごい！
大作をありがとうございます。
確かにこれならＤ３に文字列を入力すると検索できます。
これで１つの目的は達成です！
次は値だけをコピペすれば最終目標達成ですが、こちらはなんとかなりそうです。

ありがとうございました。

（この方法はいろいろ応用も利きそうです。すべて理解するのに少し時間がかかりそうですが、研究してみます。）

回答No.6

mu2011

No3です。
別セルに入力された変数をダウンリストに適用する方法で如何でしょうか。
尚、別セルは絶対参照（例えば、C1ならば$C$1）として下さい。
=IF(ROW($A1)-1<COUNTIF($B$1:$B$1000,別セル&"*"),INDEX(B$1:B$1000,SMALL(IF(LEN($B$1:$B$1000)<>LEN(SUBSTITUTE($B$1:$B$1000,別セル,"")),ROW($A$1:$A$1000),999),ROW($A1))),"")

お礼

出来ました！完璧です。
目的のものができました。
こちらの方法も、いろいろ応用が利きそうですね。

Nouble様とmu2011様の方法、どちらも完璧です。
違いはNouble様の方法だとリストのフォントサイズが小さい、mu2011様の方法は検索に一瞬のタイムラグ（私のパソコンのせいでしょうか？）があることぐらいです。

お二人にありがとうポイント20pt差し上げたいのですが、、、
投稿順ということでお許しください。

ありがとうございました。

回答No.5

Nouble

ｐｓ
D4のセルにプルダウンメニューを設定したので、
コピペはしても全然構わないですが
基本不要ですよ　v(^^;)

但しこのプルダウンメニューに現れる選択リストが
ロールダウンして真っ白になっていたりすることがちょくちょく見受けられるので
そういう時はリストの横に現れるスクロールバーでロールアップしてくださいね

後、ANo.4の文中では

Ｃ４を選択しセルの書式設定から表示形式・ユーザー定義と進み
「""」と設定した上で数字で2と入力してください

と書きましたが
この「""」は「"該当セル位置"」とか「"セル座標"」とかにした方が
利用に際し分かり易いかも知れませんね

私自身、この他にも
「あ～　あそこはもっとこうすれば良かったな～　」とか
「機能を持たせたセルのHelpを表示形式や近隣のセルに書き込んでおけば良かったな～」とか
「あそこの文言はもっとこうすればいいかも?」とか
一杯出てきているのですが
ちょっと割愛しますので　(^^;)
心ゆくまで改変をお願いしますね

そして出来れば長くご愛用頂ければ幸いです　<(_ _)>
それこそ正しく回答者冥利に尽きるというものですよね

最後に、
本来の表が1部でも開示できるなら
その表に合わせて構文を書き換えますので
また教えてくださいね

お礼

なるほど！コピペは不要ですね
完璧です！リストを選ぶだけで表とグラフが出来るようになりました。やっと目的のものが出来ました。

エクセルは奥が深いですね。もっと使いこなせるように勉強するつもりです。

ありがとうございました。

回答No.3

mu2011

Ｎｏ１です。
特定セルに抽出した結果をダウンリスト表示で選択する方法ですが如何でしょうか。
（１）空き列をＤ列としてD1に次の数式を設定、必要分下方向にコピー。
　　仮にaaaの検索列をＢ列とします。
　　尚、配列数式の為、入力完了後shift＋ctrl＋enterを同時押下して下さい。
　　=IF(ROW($A1)-1<COUNTIF($B$1:$B$1000,"aaa*"),INDEX(B$1:B$1000,SMALL(IF(LEN($B$1:$B$1000)<>LEN(SUBSTITUTE($B$1:$B$1000,"aaa","")),ROW($A$1:$A$1000),999),ROW($A1))),"")
（２）特定セルを選択し、データ→入力規制
（３）入力種類を「リスト」、元の値に=OFFSET($D$1,0,0,MATCH("",$D:$D,-1)-1,1)を設定
因みにＤ列は表示なしにした方が良いと思います。

お礼

ありがとうございます。大変複雑で私には数式の意味が理解できませんが、なるほどaaa-*に関してはプルダウンリストが表示されました。
しかし検索はbbb-*、ccc-*、、、、についても行いたいのです。
説明不足で申し訳ございません。

回答No.2

Cupper

商品名や売上げが入力されている表をそのまま参照（または必要な部分だけ参照）して、参照した先でオートフィルタを付けてはどうですか？
元の表は一切変更されませんのでグラフが消えることはありません。

お礼

ご返答ありがとうございます。
確かにその通りなのですが、その操作を簡略化したいのです。
もう少し詳しく述べますと、
シート２～１３には１月から１２月の各月の商品の売り上げデータ。
各月とも８００～１０００種類ほどの商品の売り上げがあります。ただ各月の商品構成はバラバラです。
シート１には１～１２月に売り上げた商品がすべて羅列されています。
商品名はaaa-xx、aaa-yyのように包装単位の違うものが複数あります。
（実は-xx、-yyの部分が非常に長いので、aaaで検索してaaa-xxを選択するのが楽なのです。）
そして、検索したaaa-xxをD4セルに入力すると、マクロでaaa-xxに関する１年分の売り上げ表とグラフが自動作成されるようになっています。

検索した文字列をなんとか自動的にD4セルに放り込みたいのです

回答No.1

mu2011

商品名が同一列になっているのならば、データ→オートフィルタを設定して、
ダウンリストのオプションで抽出条件を「aaa*」「と等しい」で抽出する方法は如何でしょうか。

お礼

早速のご回答ありがとうございます。
この方法ですと、画面に検索結果が表示されますが、データの横にある表とグラフが見かけ上消えたようになってしまいます。表示を「すべて」にすれば元に戻りますが、
「パソコンに慣れていない人でも商品の売上結果を検索できる」が目標ですので、なるべく単純にしたいのです。