ＥＸＣＥＬでオートフィルタのような条件検索を関数を使って作りたい！

質問

いつもお世話になっております。
タイトルのごとく、ＥＸＣＥＬでオートフィルタのような条件検索を関数を使って作りたいのですが、
たとえば、フォームのコンボボックスを複数設置し、その条件にあう答えを、特定のセルに表示させたいと思っています。
コンボボックスは２０個ほど（すべての条件が揃わなくても結果がでるようにしたいと思っています）、その答えは１００個ほどあります。
かなり手間になるかもしれませんが、どなたかご教示願います！

回答No.3

Miusuke · ベストアンサー

「関数による条件検索」ということであれば、
頑張って条件を作れば出来ると思います。

～流れ～
(1)コンボボックスに表示させる内容をつくります。
　（名前の定義）
(2)上記(1)で作った選択肢の選択肢を作ります。
　（こちらも名前の定義）
(3)入力規則を利用して、(1)で選択した内容から(2)で更に条件を絞れるようにします。
(4)任意のセルに、今表示されている一番詳細な条件が表示されるようにします。（IF文）

条件が沢山あるみたいなので、条件２個バージョンで行きます！

*やりかた*
■シート１
　 │A　 B C D
-------------------
1　│　a a b c
2　│　b あ　か　さ
3　│　c い　き　し

(1)
挿入→名前→定義で
名前：選択肢
範囲：=Sheet1!$A$1:$A$2

(2)
挿入→名前→定義で
名前：a
範囲：=Sheet1!$B$2:$B$3

(2)
挿入→名前→定義で
名前：b
範囲：=Sheet1!$C$2:$C$3

(2)
挿入→名前→定義で
名前：c
範囲：=Sheet1!$D$2:$D$3

■シート２
A1セル選択→データ→入力規則→設定→入力値の種類：リスト→元の値：=選択肢
B1セル選択→データ→入力規則→設定→入力値の種類:リスト→元の値：=INDIRECT(A1)
A3セル:=IF(A1="",IF(B1="","",B1),A1)

コンボボックスを２０個作るとなると、
aが選択された場合の名前の定義
bが選択された場合。。。。。。
と、かなり沢山の条件を作成したシートを作らなければいけないので
大変だと思います。
ただ、このやり方なら条件を変更したい時など、別シートに再度入力すれば良いので、
自分以外の人が触る場合もやり方を教えれば大丈夫と思います。
条件作成の表を分かりやすく作成することも工夫が必要ですね。。。

こんな感じでいかがでしょう？？

お礼

Miusuke様、お忙しいところ、本当にありがとうございました。
早速チャレンジしてみます。
ＶＢＡじゃないとダメなのか、諦めておりました。
本当に感謝しております。

回答No.2

imogasi

こんな問題は、当然VBAなどを勉強のこと。
VBAができないならあきらめること。
何でも質問者がしたいと思えば、関数程度でできると思うのは大間違い。
他人に回答をもらうのでなく自分のその方面の力を蓄えること。
関数は抜き出しは苦手です(それには理由があります）。
２０条件などとんでもない。複雑すぎる。
１条件でも難しいが。
とりあえず１つのやり方を説明します。
例データ　Sheet2とShee3を使う（理由はない）
Sheet2に
A２:B８（C列は式結果）
大村 石川県金沢市
木下 石川県輪島市
鈴木 東京都青梅市 1
佐藤 三重県津市
山田 北海道津市
近藤 愛媛県西条市
浅野 東京都青梅市 2
C2に
=IF(NOT(ISERROR(FIND(Sheet3!$B$1,$B$2:$B$10))),MAX($C$1:C1)+1,"")
と入れて下方向に式を複写する。
結果上記C列の通り。
ーー
Sheet３にいって、条件をB1セルに入れる。
この場合「青梅市」と入れる。
Sheet3の
A2には
=INDEX(Sheet2!$A$2:$B$10,MATCH(ROW()-1,Sheet2!$C$2:$C$10,0),1)
ｂ２に
=INDEX(Sheet2!$A$2:$B$10,MATCH(ROW()-1,Sheet2!$C$2:$C$10,0),2)
と入れて、下方向に式を複写する
結果
青梅市
鈴木 東京都青梅市
浅野 東京都青梅市
B1を　「輪島市」に変えると瞬時に
輪島市
木下 石川県輪島市
#N/A #N/A
#N/Aが出ないようにする手ももちろんありますが、内容があれこれに
なるので略。
以上はimogasi方式といっています。WEBでimogasi方式で照会すれば１条件抜き出しの、過去の質問の他の回答者の回答も見られます。
ーー
同じ考えで２条件ぐらいまでは、式を複雑化すれば対処できそうですが、それ以上は複雑になりすぎる。

お礼

早速のご回答、ありがとうございます。
勉強不足で失礼いたしました。
しかも詳細な解説を頂き、感謝しております。
ご回答を参考にして、マクロを勉強してみます。

回答No.1

zap35

質問の内容を実現するためにはアドバンスドフィルタの機能を理解する必要があります。

まずSheet1に以下のデータがあるとします
　　　　　Ａ列　　Ｂ列　　Ｃ列
１行目　品名　　日付　　数量
２行目　りんご　1月1日　　1
３行目　りんご　1月1日　　6
４行目　みかん　1月1日　　14
５行目　りんご　1月2日　　2
６行目　りんご　1月2日　　7
７行目　みかん　1月2日　　15
８行目　りんご　1月3日　　3
９行目　りんご　1月3日　　8
10行目　みかん　1月3日　　16

次にSheet2に検索条件を入力します
　　　　　Ａ列　　Ｂ列　　Ｃ列
１行目　品名　　日付　　数量
２行目　りんご　1月2日　（空白）

「データ」→「フィルタ」→「フィルタオプションの設定」を選択し「指定した範囲」にチェックをつけて、
　　リスト範囲　「Sheet1!$A$1:$C$10」
　　検索条件範囲「Sheet2!$A$1:$C$2」
　　抽出範囲　　「Sheet2!$D$1」
をそれぞれ指定して「OK」してみてください。
すると
　　　　　Ｄ列　　Ｅ列　　Ｆ列
１行目　品名　　日付　　数量
２行目　りんご　1月2日　　2
３行目　りんご　1月2日　　7
が抽出されるはずです。

毎回フィルタオプションのパラメータを指定するのは大変ですから、マクロにしてしまえばいいですね。マクロサンプルも書きました。

以下のマクロをALT＋F11でVBE画面を開き、左上のVBA Projectでシート名を右クリックし「挿入」→「標準モジュール」で表示される画面に貼り付けて下さい。マクロの実行はワークシート画面に戻ってALT＋F8でマクロ一覧を開き、マクロ名を選択して「実行」ボタンです。

Sub Macro2()
Dim lastR As Long
lastR = Sheets("Sheet1").Range("A65536").End(xlUp).Row
With Sheets("Sheet2")
　　Sheets("Sheet1").Range("A1").Resize(lastR, 3).AdvancedFilter _
　　　　　　Action:=xlFilterCopy, CriteriaRange:=.Range("A1:C2"), _
　　　　　　CopyToRange:=.Range("D1"), Unique:=False
End With
End Sub

コンボボックスは検索条件を入力する手段ですから、それは好きにしてください。手で入力しても、コンボボックスから選択しても検索条件が入力できればアドバンスドフィルタで抽出が可能です。

質問文に書いてある情報が乏しいのでアドバイスできるとしたらこんなところでしょうか。後はご自身で勉強してみてください。フィルタオプションの設定を行うときに「ツール」→「マクロ」→「新しいマクロの記録」でマクロ記録をとってみれば勉強のとっかかりになると思います

お礼

早速のご回答、ありがとうございます。
やはり関数では無理ですか。
勉強不足で失礼いたしました。
ご回答を参考にして、マクロを勉強してみます。