絞込み検索について教えてください！

質問

学校名　　　科　　　　入学式　　キャンプ　　 　　卒業式
○◎学校　　普通科　4月1日　6月中　　　　　　　3月3日
××学校　　夜間科　4月3日　8月10～14日　　　3月15日
△△学校　　普通科　4月7日　9月15日　 　　　　3月21日
◎●学校　　普通科　4月1日　7月24日・25日　　3月
◎●学校　　外語科　9月1日　4月24日・25日　　8月
　・
　・

こんな感じで学校の日程表があるとします。
エクセルなどで簡単に絞込み検索を掛けられる方法を探しています。

例えばどこかのセルなどに『9月』と入力すると9月のデータがある
△△学校と◎●学校の外語科の行(横一行全て)が出て来るようにしたいのです。

今は、エクセルのオートフィルターを使ってますが、データが大きくて各列で9月の日程が
あるものを探さなくてはいけなくて手間がかかっています。

でも、各学校の年間予定を見るなどの場合もあり、オートフィルターも崩したくないのです。
何か良い方法はあるでしょうか？
ご伝授下さい！よろしくお願いします。

回答No.6

Wendy02 · ベストアンサー

こんにちは。

＞すごーい、すごーい。ビックリしました。
もう、2年近く前から、この方法を宣伝していたのに、そうやって認めてくださったのは初めてです。（感謝!）私も、最初に使ったときに、Excelがデータベースになってしまうし、思った以上に検索スピードも速いので、自分が考えている以上に、すごいなって驚きました。

＞●月◎日など、日にちの指定までして検索する場合は、どう書き換えればいいのかをご教授いただければありがたいのですが。

直してみました。しばらく、使ってみてください。おかしなところがあったら、ここのスレッドにつけてください。早急に直すようにいたします。

改良点:
自動で検索モードを替えること、マニュアル検索モードを設定しました。

○使用説明
（使い方に慣れるまでには、少し時間が掛かるかもしれません）

設定:
ここでは、[J1] を、検索モードの起動キーにしています。（変更できます）
データベースの範囲も変更できます。
コードの中のユーザー設定の部分を書き換えれば可能です。

使い方:
[J1] に、「0」を入れれば、全画面表示（デフォルトモード）

＊日付検索:「4/1」をそのまま入力（ゼロを抜く書き方－注:「04/01」ではありません）また、「5/4/1」 でも、データの元が日付のシリアル値になっているなら可能です。特に、年をまたいでいるときの検索の場合、有効です。

うまく検索できなかった場合は、一旦、「0」で、全画面表示をしてください。
（うまく検索できない理由は、以下で述べられているテキストモードを残していることがあるからです。）

＊月の検索:　そのまま、数字を入れてください。
　[J1] に数字を入れるだけです。

テキストモードの使い方:
[J1] に、「-1」を入れれば、全画面表示（テキスト検索モード）
「4月1日」の検索は、そのまま、「4/1」と入れればよいです。特殊な検索としては、日付のシリアル値の4月を検索したい時に、「4/」とすれば、4月の部分を検索します。

マニュアルモード:
最初に、「-1」でテキストモードにしてください。
「科」の「普通科」だけを選びたい場合は、

[J2] の
　補助列

となっている部分を、「科」と書き換えて、その下に「普通科」を入れ、

「科」
「普通科」

と書き換え、　[J1] に、「-2」と入れると、そのまま検索を始めます。
「学校名」「○」とか、マニュアルに近い検索が可能です。
（ワイルドカードは、Excel 2002のみ、「○*（アスタリスク）」が必要です）

その絞りこみ検索状態のまま、もう一度、「-2」と入れると、その設定のまま全画面表示になりますので、「普通科」の部分を「夜間科」と書き換えるだけで、「-2」を入れれば、その設定で検索します。

通常モードに戻すには、「0」で全画面表示してください。そのままですと、検索しそこなうことがあります。
（注1:標準モードからいきなり「-2」を入れても、テキストモード検索はしません。）
（注2:標準モードに戻しても、数字の位置が文字列のように左側に行っている場合がありますが、月数検索には影響はありません。）
'---------------------------------------------------------------
ワークシート側の変更:

[補助列]を増やします。

ワークシートには、以下のようにお願いします。
H1:補助列1
H2～　（区切り文字を入れることにしました）
=TEXT(C2,"yy/m/d")&","&TEXT(D2,"yy/m/d") &","& TEXT(E2,"yy/m/d")
データのある分を下に、フィルコピーしてください。

なお、補助列が汚くなりますので、非表示にしてください。非表示にしても機能は変わりません。場所を移動する時は、マクロコードのほうのユーザー設定の部分も変更をしてください。ただし、データベースの範囲範囲内に、キーやCriteria（検索条件）は置けません。

前回のものをそっくり上書きしてください。

'---------------------------------------------------------------
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
'Wendy02 05/07/02 17:30　q=1484524
　Dim myCriteria As Range, DateChecker As Variant
　'
　'=======ユーザー設定==============================
　':データベースの範囲(補助列全部を含めること）
　Const myDataBase As String = "A1:H6"
　':検索条件を入れるキーのセル番地
　Const BaseRng As String = "J1"
　'================================================
　'
　If Target.Address(0, 0) <> BaseRng Then Exit Sub
　'Criteriaは、その下に置く--Criteriaの範囲は条件によって変わる
　'複数条件可能
　Set myCriteria = Range(BaseRng).CurrentRegion.Offset(1)

　With Range(BaseRng)
　Application.ScreenUpdating = False
　If ActiveSheet.FilterMode = True Then
　　ActiveSheet.ShowAllData
　　If .Value = -2 Then Exit Sub
　End If
　Application.EnableEvents = False
　　'設定の変更
　　If .Value = 0 Or .Value = -1 Then GoTo EndLine
　　On Error Resume Next
　　DateChecker = VarType(DateValue(Range(BaseRng).Text))
　　If VarType(Range(BaseRng)) = vbString And _
　　　Not IsNumeric(.Value) Then 'テキストモードで、入力値が数字でない時
　　　.Offset(2).FormulaLocal = "=""*""& " & BaseRng & "&""*"""
　　ElseIf Err() = 0 Then '日付のチェック
　　　DateChecker = Empty
　　　.Offset(1).Value = "補助列1"
　　　.Offset(2).FormulaLocal = "=""*""&TEXT(" & BaseRng & ",""m/d"")&""*"""
　　ElseIf .Value > 0 Then '標準－月検索
　　　.NumberFormat = "General"
　　　.Offset(1).Value = "補助列"
　　　.Offset(2).FormulaLocal = "=""*""&" & BaseRng & "&""月*"""
　　End If
　　On Error GoTo 0
　End With
　Range(myDataBase).AdvancedFilter _
　　　Action:=xlFilterInPlace, _
　　　CriteriaRange:=myCriteria.Resize(myCriteria.Rows.Count - 1), _
　　　Unique:=False
EndLine:
　With Range(BaseRng)
　　.Select
　　If .Value = 0 Then '0を選ぶと標準モード
　　　.NumberFormat = "General"
　　　.Offset(1).Value = "補助列"
　　　.Offset(2).FormulaLocal = "=""*""&" & BaseRng & "&""月*"""
　　ElseIf Range(BaseRng).Value = -1 Then '-1を選ぶとテキストモード
　　　.NumberFormat = "@"
　　.Offset(1).Value = "補助列1"
　　.Offset(2).FormulaLocal = "=""*""& " & BaseRng & "&""*"""
　　End If
　End With
　Application.EnableEvents = True
　Application.ScreenUpdating = True
End Sub

お礼

やーっと、やーっと動きました！

ああ、もう本当にすごいですっ。希望通りの動きです！！
こんなに素晴らしい構文を、惜しみなく書いて下さったという事が信じられません！！本当にありがとうございました。
マクロのすごさを思い知りました。

1ヵ月近く、色々な媒体で調べたり検索したりしていましたが、こちらで質問して良かったです。
ご回答下さった皆様、ありがとうございました。フィルタオプションの設定やリストについても知ることができたので、これから使っていこうと思います。

まだまだどんどん勉強していかなければ、と思いました。これからもどうぞよろしくお願いします♪

回答No.5

kaz001

Excel２００３をお使いでしたらマクロをつかわなくてもリストでできるかと思います。

データが入っている範囲を指定して
ツール→リスト→リストの作成
でできます。
第一行を見出しにする
をチェックしておけば一番上の行が乱しになりその右の「↓」で絞込みが出来ます。

さらに月と日にちを入力する列を分けておけば月ごとでの検索も可能になります。

またマクロは自分で記入しなくても
ツール→マクロ→新しいマクロを記録する
とすれば記録終了するまでの動作を自動的にマクロに記入してくれるため、プログラミングの知識がなくてもある程度のマクロなら自分で使えたりします。

いろいろ試してみるのも面白いですよ。ご参考までに。。

お礼

リ・リスト？？？

知らない事だらけですね～。
確認してみたら２００３でした。でも、どのように使えばいいのか良く分からなかったです。
まだまだ修行中ですので、すみません。これからどんどん勉強していきたいと思います。
ありがとうございました。

回答No.4

Wendy02

こんにちは。
少し試してみましたが、これは、#1 のmshr1962さんなどのフィルタ・オプションの場合は、Criteria ではできるのですが、複雑になりすぎますね。

データが、一見、テキストに見えますが、日付のシリアル値などが混在していますから、簡単には、それが出せないことに気が付きました。もちろん、これは、オート・フィルタ自身でも同じことですが、KenKen_SP さんがされたような、すべてをテキスト化して、まとめ、そこで、フィルタ・オプションや、オートフィルタで検索をすればよいと思います。

そこで、こちらは、フィルタ・オプションに、簡易マクロを使って行ってみることにしました。

最初を一行目にしていますが、データ側の行にあわせてください。

G1:補助列
G2:～　下へフィルハンドル・コピー
=TEXT(C2,"m月")&TEXT(D2,"m月")&TEXT(E2,"m月")

J1:5　'←検索条件(Criteria)
J2:　= G1 (数式:　[文字列と表示される]）
J3:　="*"&J1&"月*"　（数式:[この場合は5月と表示される]）

ワークシートの下方の[シートタブ]を右クリックで、[コードの表示]
"A1:G6"の部分は、最初の列から、「補助列」までの、実際のデータの広さにあわせてください。動かし方は、J1 に数字を入れるだけで、自動的に検索ができるようになっています。検索条件に、「0」を入れると、全てが表示されます。

'<シートモジュール>
'------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCriteria As Range
'============= 設定=======================
':データベースの範囲
　Const myDataBase As String = "A1:G6"　'←実際の場所を入れてください
':検索条件を入れる場所、ここではJ1
　Const BaseRng As String = "J1"　'←実際の場所を入れてください
'===========================================
If Target.Address(0, 0) <> BaseRng Then Exit Sub
'Criteriaは、その下に置く--Criteriaの範囲は条件によって変わる
Set myCriteria = Range(BaseRng).CurrentRegion.Offset(1)
Application.ScreenUpdating = False
If ActiveSheet.FilterMode = True Then
　ActiveSheet.ShowAllData
　If Range(BaseRng).Value = 0 Then GoTo EndLine
End If
Range(myDataBase).AdvancedFilter _
　　　　　　　Action:=xlFilterInPlace, _
　　　　　　　CriteriaRange:=myCriteria.Resize(myCriteria.Rows.Count - 1), _
　　　　　　　Unique:=False
EndLine:
Range(BaseRng).Select
'式を戻しておく
Range(BaseRng).Offset(2).FormulaLocal = "=""*""&J1&""月*"""
Application.ScreenUpdating = True
End Sub

お礼

実は私はマクロを知らないのです！

しかし折角の機会ですのでチャレンジしてみました。
モジュールをコピーするだけなのに四苦八苦してしまいましたが、やっと動いてきました～。
すごーい、すごーい。ビックリしました。

そこで厚かましいお願いなのですが、できれば●月◎日など、日にちの指定までして検索する場合は、どう書き換えればいいのかをご教授いただければありがたいのですが。
どうぞどうぞ、よろしくお願いします。

回答No.3

KenKen_SP

こんにちは。KenKen_SP です。

作業列を挿入して、行事の日付が入ったセルを全て連結し、その作業列で
オートフィルターをかけて、「9月」を含む、というオプションで抽出すれ
ば良いかと、、

例えば、

C列:入学式　D列:キャンプ　E列：卒業式　F列：作業列　とするなら、

F1に次の式を入力し、データの終わりまでオートフィルでコピーします。

= TEXT(C2,"m月") & TEXT(D2,"m月") & TEXT(E2,"m月")

F列にはご提示いただいたサンプルデータの場合、

4月6月中3月
4月8月10～14日3月
4月9月3月
4月7月24日・25日3月
9月4月24日・25日8月

こんな風に表示されます。そうしたら、この列にオートフィルターをかけ、
（オプション）で抽出条件を　9月　を含む　に設定します。

お礼

なるほど～。
色々とアイディアがありますね～。皆様の知識と創造力に驚きました。マクロを知らない私にも、充分理解できました。
これからも、どうぞよろしくお願いします。
本当にありがとうございました！！

回答No.2

sakeman

１．表の上に空白行を５行挿入（検索条件を設定するため）

２．例えばセルA1に「入学式」，セルB1に「キャンプ」，セルC1に「卒業式」、セルA2に検索する文字列「9月」,B3とC4にも「9月」と入力します。この場合、条件がANDの場合は同じ行に、ORの場合は今回のように行を変えて入力します。

３．データの表内のどこかのセルを選択し、[データ]→[フィルタ]→[フィルタオプションの設定]→[リスト範囲]を確認（自動的に入力されている筈）→[検索条件範囲]をドラッグして入力（この場合は「$A$1:$C$4」になります。→[ＯＫ]

４.これで「入学式」，「キャンプ」，「卒業式」に「9月」が含まれる行が検索されます。

・日付のデータの書式は文字列などに統一してください。

・検索条件を入力する行と表との間は1行以上の空白行を作ってください。（検索条件の表とデータの表を区別するため）

・元に戻す場合は、[データ]→[フィルタ]→[すべて表示]

・これを利用するとかなり複雑な検索も可能です。
お試し下さい。

お礼

早速教えていただいた通りにやってみました。フィルタオプションで絞込みできました！
ただ、実際のデータでは、項目が20個もあって見た目の点でちょっと・・・。
ですが、教えていただいたフィルタオプションは色々と使えそうなのでこれからも活用してみます！！
ありがとうございました♪

回答No.1

mshr1962

「データ」「フィルタ」「フィルタオプションの設定」を利用してみては？

空いている列に
検索月　入学式　　　キャンプ　卒業式
9月　　　=$AA$2&"*"
　　　　　　　　　　=$AA$2&"*"
　　　　　　　　　　　　　　　=$AA$2&"*"

※9月のセルがAA2としています。
「データ」「フィルタ」「フィルタオプションの設定」で
抽出条件範囲をAB1:AD4として実行

詳細はヘルプを確認してください。

お礼

フィルタオプションを初めて理解しました。ここに質問してよかったです。
ありがとうございました☆