【EXCEL】全シートでand検索ってできますか

質問

シート毎ではフィルターを使ってAND検索が出来ますが、シートをまたいで(ブック内全体で)行のand検索をかける機能は無いでしょうか？
検索ボタンからの検索だとひとつの文字列しか検索できません。

現在シートが40枚に分かれているブックに部品の情報を載せています。その一枚一枚をフィルタをかけて検索する作業を簡略化したいのですが、いい方法は無いでしょうか？

すべてのデータを一枚のシートにして、フィルタで検索というのも考えましたが、今後の管理のことを踏まえるとシートは分けておきたいです。

データ数は5000程。今後も順次増えていきます。
検索項目は4項目で形状、部品分類、使用数、横幅です。
シートはメーカーで分かれています。
検索結果の行を抜き出すか、Ctrl+Fの検索結果のように一覧になって出てきてくれるのが、理想的な形です。

コマンドやそういった機能があるのが一番なんですが、出来ないならマクロ・ＶＢＡも勉強使用かと思います。

ECXEL2013を使用しています。

スキルは仕事に使うコマンドと関数が使える程度です。
ＶＢＡで標準モジュールに貼り付けるだけなら出来ますが、自分で組んだことはありません。

どなたかいい知恵がありましたら、お貸しください。よろしくお願いします。

回答No.6

MackyNo1 · ベストアンサー

＞すべてのデータを一枚のシートにして、フィルタで検索というのも考えましたが、今後の管理のことを踏まえるとシートは分けておきたいです。

Excelをデータベースとして使用するなら、今後の管理を考えるとシートは分けるべきではありません。
一枚のシートにまとめてあれば、逆に個別のシートに分割したり、特定の条件のデータを集計するのは簡単なことです。

どうしても、シートを分ける（管理するシートを増やしたいということなら、マクロ処理が必要になります。

まず、検索条件が変動し、かつ複数項目のデータ抽出を効率的に行うには、フィルタオプションの設定を利用するのがお勧めです。
さらに、多数のシートで同様の条件を一括設定するには作業グループの機能を利用します。

これらの前提でフィルタオプションを利用する基本操作を説明すると、すべてのシートを作業グループにして、空白列に検索項目の形状、部品分類、使用数、横幅という項目名を入力し、その下にそれぞれの検索条件（対処にしない場合は空白のまま）を入力します。

次に作業グループを解除して、データタブの「詳細設定」をクリックし、リスト範囲にA列からデータ範囲の列全体を指定し、検索条件範囲に上記の４つの項目名とその下の行を選択し、OKします（必要に応じて他シートに抽出する）。

このようにして２つ目以降のシートで同様の操作を行います。

この操作をマクロの記録で記録して、そのコードを修正して、最終的にはシートを変更して４０回繰り返すコードに変更することになります（無理に繰り返し操作を入れなくても、１回分の操作をコピーし、シートを順次選択して、そのあとにそのコードを貼り付ればよい）。

ひとまず、ここまでやってみて、わからないことや不都合な点があれば、問題点を絞って再質問するようにしてください。

お礼

この設定は出来ました。
もう一度質問まとめてから質問します。

回答No.5

bunjii

40シートからの抽出を関数で処理するのは無理かと思います。
貼付画像はSheet1、Sheet2、Sheet3に模擬データを作成して検査条件を3項目でSheet4へ抽出してみました。
Sheet4のB列、C列、D列に検査条件を列記してSheet1、Sheet2、Sheet3のB、C、Dの各列に一致する組み合わせのSheet名と行番号を検査します。
複数の該当があるときはSheet1、Sheet2、Sheet3の順で後方のシート名を抽出し、行番号は大きい方を採用しています。
Sheet4!F2=MAX(MAX(INDEX((Sheet1!B$2:B$21=B2)*(Sheet1!C$2:C$21=C2)*(Sheet1!D$2:D$21=D2)*1,0)),MAX(INDEX((Sheet2!B$2:B$21=B2)*(Sheet2!C$2:C$21=C2)*(Sheet2!D$2:D$21=D2)*2,0)),MAX(INDEX((Sheet3!B$2:B$21=B2)*(Sheet3!C$2:C$21=C2)*(Sheet3!D$2:D$21=D2)*3,0)))
Sheet4!G2=MAX(INDEX((INDIRECT("Sheet"&F2&"!B$2:B$21")=B2)*(INDIRECT("Sheet"&F2&"!C$2:C$21")=C2)*(INDIRECT("Sheet"&F2&"!D$2:D$21")=D2)*ROW(A$2:A$21),0))
Sheet4!A2=IF(COUNTA(B2:D2)=3,INDEX(INDIRECT("Sheet"&F2&"!a:a"),G2),"")
其々のセルを下へ必要数コピーします。
Sheet4のF列は条件付き書式で値が0のとき文字の色を白にしてあります。
Sheet4のG列は同様にエラーのとき文字の色を白にして見えなくしています。
模擬データは各シート共21行目までとしてありますのでその範囲で数式を組み立てています。
実際のデータが40シートとのことなのでSheet4!F2の数式は途轍もない長さになるでしょう。
従って、VBAで処理されることをお勧めします。

回答No.4

naoto0216

>現在シートが40枚に分かれているブックに部品の情報を載せています。その一枚一枚を
>フィルタをかけて検索する作業を簡略化したいのですが、いい方法は無いでしょうか？

>すべてのデータを一枚のシートにして、フィルタで検索というのも考えましたが、今後の管理
>のことを踏まえるとシートは分けておきたいです。

方法は別として仮に実現できたとしても、抽出結果を確認するのに40シート全て
見なくちゃいけなくなると思うので、やはり1枚のシートにまとめた上で抽出した方が
よいのではないでしょうか。

例えばこういうツールを使用すると、複数シートのデータを一つのシートにまとめて
くれますよ（各シートの項目は一律じゃないといけないみたいです）。
http://www.vector.co.jp/soft/winnt/business/se475869.html?ds

回答No.3

msMike

シートをまたいで(ブック内全体で)行のand検索をかける機能は無いけど、単発検索で良ければ可能ですけど。
複数(あるいは全部)のシートを選択しておけば。
ご存知なかったなら、今までの半分の手間で済む？

回答No.2

Chiquilin

#1です。 AND検索なのを忘れてました。やはりマクロで一ヶ所にデータを
集めるのが簡単でいいと思います。

回答No.1

Chiquilin

> 現在シートが40枚に分かれているブック
後で困るのが目に見えているのに……

フィルタ機能ではなく 検索機能のオプションでブック単位の検索を掛けたらどう
ですか？

> 自分で組んだことはありません。
VBAは せめて基本だけでも勉強してから挑戦して下さい。

抽出データを別シートに表示させる場合ですが 全部を一ヶ所に集めてからソート
を掛けて 不要なデータ行範囲を一括で削除した方が処理が速いです。