[[20130207142934]]　『複雑な条件分岐でデータを抽出』（MADAO）　ページの最後に飛ぶ

『複雑な条件分岐でデータを抽出』（MADAO）

 マクロ初心者なのに無謀な挑戦をしていて行き詰りました。。。

 A～W列まで値が入っている（中には空白のセルもありますが）シートがあります。
 その中から、5つの条件のいずれか、または複数条件を選択して、条件に一致するデータを
 別シートに抽出したいのですが。。。

 例えば下記の5つのコンボボックス（ユーザーフォーム）があります。

 １.ComboArea = 「東京」「福岡」「全域」
 ２.ComboKigyo =「メーカー」「特約店」「全件」
 ３.ComboName = 「本多」「松田」「全員」
 ４.ComboBusyo =「営業」「技術」「全件」
 ５.ComboSyurui = 「在庫」「在庫以外」「全件」

 このようなリストが最初からコンボボックスに設定されています。

 ユーザーが、5つの条件から自分が抽出したいものを選択して、一致するデータを別シートに転記します。

 が、条件が複雑すぎて、条件分岐の仕方がどうしたらよいかと…

 条件指定の仕方は
 ・すべてのコンボボックスでいずれかの値を選択する。空欄のものがある場合は「条件を指定してください」
 のメッセージを出す。
 ※空欄の場合は、その条件は「全部抽出」という考えでもOK

 ・「東京」だけを選択して他は全部「全件（全部抽出）」という場合、
 「メーカー」「営業」と複数の条件を選択して他全部抽出の場合、
 全てのコンボボックスで条件を指定する場合、といくつかのパターンがある。

 ・「営業」を選択した場合、セルには「営業第一」「営業第二」など入っているものがあり、これは
 LEFT関数で最初の2文字を指定して「営業○○」に一致するものを抽出する。

 というような感じなのですが（わかりにくくてすみません）、私が知っているIF～文では
 ものすごい条件分岐が必要になるので、5つの条件の様々なバリエーションをどうやって指定すればよいかと途方に暮れております。。。

 非常にわかりにくい文章ですみません。
 イメージではシートにフィルタを次々にかけていく感じです。

 VBAでオートフィルタが使えるのは知っているのですが、ネットの情報を見ても使い方が
 よく分からなくて…

 どのようにすればよいかお教えください。

 Excelは2010です。

 一気に、五つの条件でフィルターを掛けずに、
 Area,Kigyo,Name,Busyo,Syurui と、順序に処理していけば ?
   Select Case ComboArea.Value
      Case "東京", "福岡"
         'Filter処理
   End Select
   以下繰りし
 (暇人)

 ＞イメージではシートにフィルタを次々にかけていく感じです。
オートフィルタの使い方をご存知なら、
いったん「フィルタを次々にかけていく」と言う操作を
マクロで記録していくといいです。
　
オートフィルタの列（Field）に対して順番にフィルタリングしていく、
という操作はマクロでもそれほど違ったことをしているわけではないので。
　
で、記録されている部分のCriteria1とかCriteria2が定数になっているものを
「5つのコンボボックス」の値で置き換えていく、というコード改造の仕方に
なってくると思います。
　
（みやほりん）

 ＞「営業○○」に一致するものを抽出する。
これを見落としていた。
検索したい文字列として　"営業*"　のように*（アスタリスク＝ワイルドカード）
をコンボボックスで入力・表示するようにするか、
私がやるなら、子セクションに対する親セクションの列を設けてしまいますけれども。
（みやほりん）

 暇人様、みやほりん様、ありがとうございます。

 抽出条件に「期間指定」を追加することになり、テキストボックスを2つ配置して
 「開始日：TextSDate」「終了日：TextEDate」としました。

 シートのC列（3列目）に「2013/2/8」という日付型のデータがあり、そこから
 「2013/1/1～2013/1/31」というように範囲指定、または「2013/1/1以降全部」「2013/1/31まで全部」
 というデータをフィルターで絞り込もうとしました。

 それをマクロの記録でやってみたところ、

 Sub 期間指定()
 '
 ' 期間指定 Macro
 ' 日付の期間を指定する（1/1～1/31）
 '

 '
    ActiveSheet.Range("$A$3:$X$491").AutoFilter field:=3
    ActiveSheet.Range("$A$3:$X$491").AutoFilter field:=3, Criteria1:=">=1/1", _
        Operator:=xlAnd, Criteria2:="<=1/31"
 End Sub

 となったので、これをコードに組み込んでみました。

 Option Explicit
 Public ws1 As Worksheet
 Public kaisi As Variant
 Public syuryo As Variant

  Private Sub フィルタリング処理()

 Dim i As Long
 Dim z As Long

 Set ws1 = ThisWorkbook.Worksheets("リスト")

 z = ws1.Range("B" & ws1.Rows.Count).End(xlUp).Row

 If IsDate(TextSDate.Value) Then
    kaisi = CDate(TextSDate.Value)
 Else
    kaisi = ""
 End If

 If IsDate(TextEDate.Value) Then
    syuryo = CDate(TextEDate.Value)
 Else
    syuryo = ""
 End If

    If TextSDate.Value <> "" Or TextEDate.Value <> "" Then
        ws1.Range("A3").AutoFilter field:=3, Criteria1:=">=kaisi" _
        , Operator:=xlAnd, Criteria2:="<=syuryo"

    End If

  End Sub

 これを実行したところ、該当データが存在するにも関わらず該当データなしの状態になりました。

 どこがおかしいのでしょうか？

 （MADAO)

 ＞該当データなしの状態
のときに、オートフィルタのかかった状態のC列のドロップダウンリストから
「（オプション）」を選択してみてください。
kaisi 以上　AND　syuryo　以下　のように表示されていませんか？
ということは、日付として条件がセットされなかった、ということです。
　
 ">=kaisi"
 ">="&kaisi
　
上記の二つの違い、わかりますか？
前者は">=kaisi"というひとかたまりの文字列。
後者は">="と変数kaisi（の中身）を結合した文字列。
つまり、前者は変数のつもりで変数になっていなかったということです。
プログラムは、書いたとおりに実行してくれますからねぇ。
　
（みやほりん）

 みやほりん様、ありがとうございます。

 そういうことなんですね
 よく考えたらその通りですね。。。

 今試したところ、教えていただいた内容でうまくいきました。

 が、一度フィルターをかけた後、例えば「期間指定の範囲を変えたい」と思ってテキストボックスの内容を
 変更して再実行すると、既にフィルターがかかった状態から更にフィルターをかけることになり、正しいデータが
 絞り込めないので、条件を変更した際に一度フィルターをクリアして再度絞り込む、というような手順が
 必要になりそうです。

 絞り込むためのコマンドボタンを押す際に、最初にフィルターを全て解除するコードを書いた方がいいかどうか考え中です。

 （理想は、本来のシートはあまりいじらずに、作業用のシートか何かを用意してそこで作業を行い、転記用のシートに
 結果を書き込みたいのですが、まだオートフィルターのコードがうまく扱えなくて、方法を考えてます）

 （MADAO）

 ＞正しいデータが 絞り込めないので、
フィルタをかけた後の各コンボボックスの状態がどうなっているか
見えていないので正確な判断は出来ませんが、
コンボボックスの状態が保持されたまま再度同じ条件でフィルタを
かけた場合は同じフィルタ結果が表示されるはずです。
これは手動の場合も同じです。
　
「期間指定」の部分だけ変更した場合は、変更されたフィールドのみ
抽出結果が異なる状態でフィルタリングされると思うのです。
この点、ご確認下さい。
　
＞理想は、
はい、そのような抽出方法にはAdvancedFilterメソッドが適しています。
これは別のシートに抽出結果を出力できます。
Excelのコマンドでは「フィルタオプションの設定」に当ります。
　
この場合、まずはフィルタオプションの使い方をマスターし、
それをマクロの記録を行ってみると、比較的簡単にマクロ作成することが出来ます。
下記、自作のつたない説明ページですが、参考にしてください。
http://miyahorinn.fc2web.com/frame.html
　
フィルタオプションをマクロ化する場合は、
オートフィルタで各フィールドの抽出条件を設定した部分を
ワークシート上の「抽出条件セル範囲」のセルに書き込んでいく形になります。
（みやほりん）

 みやほりんさま、引き続きありがとうございます。

 フィルタオプションをマクロで記録して、それをコードに組み込んだのですが、「無効なフィールドです」のエラーが出ます…

 「作業用」というシートを作り、マクロの記録で作成されたコード

 Sub Macro4()
 '
 ' Macro4 Macro
'

     Sheets("リスト").Range("A3:X55").AdvancedFilter Action:=xlFilterCopy, _
        criteriarange:=Range("作業用!Criteria"), copytorange:=Range("A1:M1"), unique _
        :=False
  End Sub

 （抽出の条件は「作業用」シートのAA1～AG1セルに見出しをつけ、条件にあったデータをA～Mに抽出するようにしています）

 マクロの記録を元に、ネットの情報も参考にしながら書いたコード

  Set ws1 = ThisWorkbook.Worksheets("リスト")
  Set sh = ThisWorkbook.Worksheets("作業用")

 　z = ws1.Range("B" & ws1.Rows.Count).End(xlUp).Row

    ws1.Range("A3:W" & z).AdvancedFilter _
        Action:=xlFilterCopy, _
        criteriarange:=sh.Range("AA1:AG1"), _
        copytorange:=sh.Range("A1:M1"), _
        unique:=False

 下のAdvancedFilterのコードのところが黄色く反転して「無効なフィールドです」というエラーが出ます。
 「作業用」シートのAA～AGには抽出条件は書き出されています。

 「作業用」の見出しは「リスト」の見出しとまったく同じ文字列にしています

 どこの書き方がおかしいのでしょうか？

 （MADAO）

 criteriarangeは抽出条件のフィールドの見出しと抽出条件が書き出されるので、
通常１行の記述にはならなくて、２行以上のセル範囲になります。
　
criteriarange:=sh.Range("AA1:AG1")　で見出しだけが記入されていると
全件抽出されるので、おそらく見出しがなくて抽出条件だけ
この範囲に記述されているのではないでしょうか。
　
「無効なフィールドです」＝「有効な見出しがない」
というエラーだと推測します。
（みやほりん）

 みやほりん様

 ついさっきそれに気づいてsh.Range("AA1:AG2")に修正しましたが、やはり同じエラーが出ます。。。

 （MADAO）

 こちらではサンプルデータにてその修正を行ったコードで動いているので、
コード以外に問題があるのかな？
その状態で「フィルタオプションの設定」による手動抽出は実行可能か。
変数zの値はどうなっているか。
これらが気になる点。
（みやほりん）

 みやほりん様

 最初に試したフィルタオプションではうまく抽出できたのですが、どうやら条件指定の仕方が悪いみたいで
 手動でフィルタオプションをかけても抽出できません。。。

 例えば、抽出条件で

 　　　　AA　　　　　AB　　　 　AC　　　AD　　　AE　　AF
 1　　出荷日　　　出荷日　　　納品日　納品日　エリア　部署
 2　>=2013/1/1　<=2013/1/31　　　　　　　　　　東京　 =営業*

 このように入力して、
 「1/1～1/31に出荷した、東京エリアで、営業○○部署のデータ」
 を抽出しようとしましたがデータが出てきません。

 仮にAE列の「エリア：東京」だけを条件にしたら該当データが抽出されます。

 条件指定の仕方が悪いようです。。。

 フィルタオプションではなく、直接データシートにフィルタをかける時は上記のような記述でうまくいったのですが。。。

 ちなみに　変数z はちゃんと「リスト」シートの最終行を取得しています。

 （MADAO）

 Sub TEST()
     Dim ws1 As Worksheet
     Dim sh As Worksheet
     Dim z As Long

     Set ws1 = ThisWorkbook.Worksheets("リスト")
     Set sh = ThisWorkbook.Worksheets("作業用")
     z = ws1.Range("B" & ws1.Rows.Count).End(xlUp).Row
     ws1.Range("A3:W" & z).AdvancedFilter _
         Action:=xlFilterCopy, _
         criteriarange:=sh.Range("AA1:AG2"), _
         copytorange:=sh.Range("A1:M1"), _
         unique:=False
 End Sub
　
こちらではなんのひねりもなく上記のコードで抽出できてますけどねぇ・・・
「無効なフィールドです」が出るあたり、見出しが「リスト」「抽出範囲」「抽出条件範囲」
のどれかで、見出しの文字列に差異があるのだと思うのですけれども。
　
あと、日付で抽出できない原因として、
・日付フィールドが実は文字列
・日付フィールドが8桁数値だが、表示形式にて「0000/00/00」で日付に見えるようにしている
・日付フィールドが月日だけの表示になっているが、実は年が違う
　
（みやほりん）

 みやほりん様

 見出しを再度「リスト」シートからコピーしましたけどダメでした。。。

 ネットで検索していたら、条件のところが「文字列」でないといけないと書いてあったので、AA列から先を「文字列」にし、
 そこに手動で書いた条件を元にフィルタオプションを実行したら抽出できました。

 ところが、VBAで実行しようとするとやはり「無効なフィールドです」のエラーが出ます。。。

 日付のところはちゃんと日付形式になっています。

 コードを見直してみやほりん様のコードとまったく同じにしましたがやはりできません。。。

 わけがわからずお手上げ状態です。。。

 （MADAO）

 バージョンの違いもあるのかなぁ。（こちらは2003での確認）
途中投稿していただいたコードの中に、
　＞=Range("作業用!Criteria")
と言う部分が見えます。
"作業用!Criteria"　は自動で抽出条件範囲に「名前定義」されるものです。
同じく、"作業用!Extract"という名前も定義されているはずです。
これらをいったん削除してみて、再度実行してみてはいかがでしょう。
　
苦し紛れの回答です。
下記にそれらしき記述があったので。
現象が再現できないのでこの方法で対処できるか未確認。
　
[[20060815232153]]　『数値が０ではないものを抽出したい』（VBA初心者なおパパ）
　
（みやほりん）

 すみません、すっごく基本的な間違いをしてました。。。

 元々の「リスト」に新しく列を追加したのを忘れていて、そこで「リスト」の範囲が狂っていました。
 （古い「リスト」の一覧を印刷したものを見ながらやっていたので今まで気づきませんでした…バカ…）

 お騒がせいたしましたm(__)m

 (MADAO)

 すみません、また問題が発生しました。

 「売上したかどうか」を判別するのに、「リスト」シートの「売上日」（日付型）が空白かどうかで判定しています。

 例えば、
 ComboUriが「売上済」→シートの「売上日」が「空白以外」
 ComboUriが「未計上」→シートの「売上日」が「空白」
 ComboUriが「全件」→全件抽出

 これで、

 Dim Uri As Variant

 If ComboUri.Value = "売上済" Then
    Uri = "<>"
 ElseIf ComboUri.Value = "未計上" Then
    Uri = "="
 ElseIf ComboUri.Value = "全件" Then
    Uri = ""
 End If

 このように条件を設定しましたが、「売上済」「全件」は抽出できるのに「未計上」だけ抽出できません。

http://www6.plala.or.jp/MilkHouse/practical/contents305/contents30506.html

 ここで「空白セルの場合は"="イコール」と書いてあったので上記のコードにしたのですが、
 元データが日付型だからなのか、どうしても1件も抽出できません。

 条件式を書く作業用シートは上記のURLに書いてあるように「文字列」形式にしています。

 何かご教授をお願いします…

 (MADAO)

 ※追加で分かったことがあります。

 「売上済」を抽出する際、「リスト」シートに「売上日（日付形式）」または「-」などが入っていて
 「空白でないセル」として条件設定をしていたのですが、今再度やってみると「売上済」
 を抽出しようとしても「売上日」に「日付」が入っているものは抽出されません。
 （「-」など文字列形式のものだけが抽出される）
 日付型のデータにオートフィルタをかける際は別の条件指定が必要なのでしょうか？

 何日か見落としていたのでまだご覧になっているでしょうか。
こちらの検証では、AutofilterでもAdvancedFilterでも
"<>"　で空白でないセルが、
"="　で空白のセルが抽出されます。（Excel2003）
　
とくに特別な指定は必要なかったように思うのですが・・・。
（みやほりん）

コメント返信：

[ 一覧(最新更新順) ]

[[20130207142934]] 『複雑な条件分岐でデータを抽出』（MADAO） ページの最後に飛ぶ

[[20130207142934]]　『複雑な条件分岐でデータを抽出』（MADAO）　ページの最後に飛ぶ