[[20230206173218]]　『集計データを指定の日付期間で絞り込みたい』（ta）　ページの最後に飛ぶ

『集計データを指定の日付期間で絞り込みたい』（ta）

目標：VBAにて、20●●/10/01から20●●/09/30の期間でオートフィルターをかけたい
課題：参照元となるデータが”20221013”等の8桁かつ文字列データであるので、日付か数値に直さなければならない
運用状況：年度で絞り込みたいため、マクロ実行時にinputboxで”2022”などと入力するだけでその年度が絞り込まれるのが理想

前提条件：データの量は毎月変動
　　　　　社内システムで自動抽出される都合上、最初から文字列にしないことが不可能であるため毎月変換が必要

試したこと：
With

 Dim i As Long
  With Range("K2", Cells(Rows.Count, "K").End(xlUp))
   .NumberFormatLocal = "@"
    For i = 1 To .Count
     With .Cells(i)
     .Value = CStr(.Value)
     End With
    Next i
  End With
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
With
   maxRow = sht1.Range("K1").End(xlDown).Row
   For i = 2 To maxRow
    If IsNumeric(Range("K" & i).Value) = True Then
    Range("K" & i).Value = Val(Range("K" & i).Value)
    End If
   Next
End With

数値に変換する方法を調べて、自分なりにいくつか試してみたが、どうやっても文字列から変わることがなかった。原因不明。
その後CDateとFormatを使った日付変換をやろうとしたが、調べても列全体の可変データへ実行する方法が見つかりませんでした。
dt = CDate(Format(●, "####/##/##"))
　　　　　　　　　↑この部分は定数でないとダメ？
代替案よろしくお願いいたします。

＜使用 Excel：Microsoft365、使用 OS：Windows10 ＞

 .NumberFormatLocal = "@"

↑これは数値の表示形式を「文字列」にする設定です。
このセルに日付の値を入れてもシリアル値には変換してくれません。
（ふなば） 2023/02/06(月) 18:09:49

 .NumberFormatLocal = "@"

この数式が意味がなかったことは理解しました。
ありがとうございます。
ではどうするべきでしょうか？
（ta） 2023/02/06(月) 18:29:18

文字の日付とシリアル値の日付を理解してから出直してこい。
でないと話がつながらないような。

＞この数式が意味がなかったことは理解しました。

数式じゃねえし。
（出直し） 2023/02/06(月) 18:38:31

 .NumberFormatLocal = "G/標準"

これでもいいですし

 .NumberFormatLocal = "####!/##!/##"

これでもいいですし

無理に日付に変換しなくても20221001以上20230930以下の値でフィルタをかけられれば望みの結果を得られるのではないでしょうか。
（ふなば） 2023/02/06(月) 19:01:29

 ＞社内システムで自動抽出される都合上、最初から文字列にしないことが不可能であるため毎月変換が必要
 これが是なら、すべて文字列として抽出しなければいけないっすよね。
 例示は2021/1/1～2021/12/31までを8桁日付にして、配列に入れ
 オートフィルターで絞り込みしてます。

    Sub test()
        Dim dicDateList As Variant
        Dim d As Variant
        Dim myYear As Variant
        myYear = InputBox("年数を入力してください", , "2021")
        If IsDate(myYear & "/1/1") = False Then
            MsgBox "年数を入力してください"
            Exit Sub
        End If
        Set dicDateList = CreateObject("Scripting.Dictionary")
        For d = DateValue(myYear & "/10/1") To DateValue(myYear + 1 & "/9/30")
            dicDateList(Format$(d, "yyyymmdd")) = ""
        Next
        With Sheets("Sheet1")
            .AutoFilterMode = False
            .[a1].AutoFilter 1, dicDateList.keys, xlFilterValues
        End With
        MsgBox "フィルターしました"
    End Sub

 ふなばさん
 ＞無理に日付に変換しなくても20221001以上20230930以下の値でフィルタをかけられれば望みの結果を得られるのではないでしょうか。
 相手が文字列だとできなかったんですけど、なんかコツってあります？
（稲葉） 2023/02/06(月) 19:10:08

確認ですが、作業列にマクロで数式を書き込み、日付型データに変換してから、オートフィルタで抽出するのはダメなんですか？

＞データの量は毎月変動
↑とのことですが、フィールド（列）は固定だったりしませんか？

（もこな２） 2023/02/06(月) 19:19:45

稲葉さん

指摘ありがとうございます。
taさんの当初の質問の後半部分のコードを活かし
数値の表示形式を文字列化させなければ数値として使えるのではないかという考えです。

（ふなば） 2023/02/06(月) 19:22:58

 データの区切り位置でウイザードの最後まで行くと
 列のデータ形式を選べます
 そこで日付をチェックすれば列を一気に日付に変換できますよ
（´・ω・`） 2023/02/06(月) 19:26:01

 ＞数値の表示形式を文字列化させなければ数値として使えるのではないかという考えです。
 なるほど！
 気が利かないですみません。

（´・ω・`）さん

 8桁日付で変換できたんですね・・・やってみたら一発でした
 勉強になりました。

（稲葉） 2023/02/06(月) 19:35:53

 私は、数値や日付への変換自体が必要ないと思います。
 文字列なりに大小比較ができるので。

 １．オートフィルタの場合は

   例：2021/10/01から2022/09/30にしたい場合

  　　条件に"a"など適当な文字を末尾に追加して・・

  　  範囲.AutoFilter Field:=△, Criteria1:=">20210930a" _
  　 　      , Operator:=xlAnd, Criteria2:="<20220930a"

 ２．単なる文字列同士の比較の場合なら、 何の工夫もなく判定できると思います。

（半平太） 2023/02/06(月) 19:40:45

 質問者じゃないんですが教えてください。

 半平太さん
 ">20210930a"　"<20220930a"
 この条件でできたのですが、これって内部的にどう処理されている感じですか？

 文字コードとかで検索かけてるなら
 ">20210930"　"<20220930"　これでもできておかしくない気がするんですけど・・・

 MSに聞けってのは最後まで取っといていただけると助かります。

（稲葉） 2023/02/06(月) 19:51:52

 ＞ 文字コードとかで検索かけてるなら
 ＞ ">20210930"　"<20220930"　これでもできておかしくない気がするんですけど・・・

 多分、それだと、数値なのか数字なのか区別できないので、数値として比較するんでしょうね。
 "a"が混じれば明らかに文字列なので、文字列の大小で比較してくれるのではないかと思っています。

（半平太） 2023/02/06(月) 20:04:37

 Sub Sample()
   With Worksheets.Add
     With .Range("A2")
       Dim r400 As Range
       Set r400 = .Resize(400)
       'r400.NumberFormatLocal = "@"　'ここでやると失敗する
       .Value = #9/1/2022#
       .AutoFill r400, xlFillDays
       Dim r
       For Each r In r400
         r.Value = Format(r.Value, "yyyymmdd")
       Next
       r400.NumberFormatLocal = "@"  'ここでやると成功する
       With .Offset(-1)
         .Value = "日付"
         .AutoFilter Field:=1, Criteria1:=">=20221001", Operator:=xlAnd, Criteria2:="<=20230930"
       End With
     End With
   End With
 End Sub
私も便乗ですみません。
半平太さんのアイデアが面白いと思って実験してみたんですが、
上記コードでは数値の表示形式を日付入力前に文字列設定するとオートフィルタに失敗、
入力後に数値の表示形式を設定するとオートフィルタに成功します。
これってどう処理されてるんでしょうか？
（ふなば） 2023/02/06(月) 20:09:01

あー、これは先に数値になってるからか。
変な便乗質問してしまってすみません。上記は無しにしてください。
（ふなば） 2023/02/06(月) 20:13:43

  ＞"a"が混じれば明らかに文字列なので、文字列の大小で比較してくれるのではないかと思っています
 ふーむ、、、
 検索文字列だけに文字列指定しているのに
 20210101と20210101aを比較したら、絶対別物なのに
 ｢より大きい｣が範囲に入るのが不思議ですがテクニックですね、、、
 使う機会がえられれば、使いたいと思います！
 ありがとうございました
（稲葉） 2023/02/06(月) 20:28:45

 ＞ 検索文字列だけに文字列指定しているのに
 ＞ 20210101と20210101aを比較したら、絶対別物なのに

 あれ？　別物ですけど。

 　　  ">20210930a"　これで 20211001 以降です。

 もし、">20211001a"  なら　 20211002 以降です。

（半平太） 2023/02/06(月) 20:40:57

 あ、ごめんなさい
 8桁と9桁って意味でした
 100から200が欲しいのに数えるのに1000から2000で検索してるような
 感覚でした
（稲葉） 2023/02/06(月) 20:43:34

 文字列の比較は、桁数関係なく頭からですから(数値じゃないので）。

 ※数字(文字列）の比較では「10」より「2」の方が大きいです。

（半平太） 2023/02/06(月) 20:50:13

 そうなんですね
 明日時間作って調べてみます！
（稲葉） 2023/02/06(月) 20:59:03

まずは皆様、貴重なご意見本当にありがとうございます。
また自分が本当に無知で申し訳ないです。

稲葉さんからいただいたコードでおそらく現状ぶつかっていた課題は突破されたかと思います。
ありがとうございます。

半平太さんからいただいたものについてですが、
なるほどそういう処理なのかと大変勉強になりました。
そこで重ねて質問なのですが、

  　  範囲.AutoFilter Field:=△, Criteria1:=">20210930a" _
  　 　      , Operator:=xlAnd, Criteria2:="<20220930a"
この2021と2022部分がこのままだと、この年分しか抽出されませんよね？
毎回VBAを書き換えるとなってしまうのでこの部分を可変させる方法はあるのでしょうか？

よろしくお願いいたします。
（ta） 2023/02/07(火) 09:55:44

 ＞この部分を可変させる方法

 スタート日とエンド日を決めるのと殆ど同義ですけども・・ 
                                                            　　前日にする
                  　　　　　　　　　　　　　　　　　　　　　　　　↓
 スタート日が動的に決まったら　Criteria1:=">" & Format(スタート日 - 1, "yyyymmdd") & "a"
 エ ン ド日が動的に決まったら　Criteria2:="<" & Format(エンド日, "yyyymmdd") & "a"

（半平太） 2023/02/07(火) 12:54:09

ご回答ありがとうございます。
とても勉強になりました。
皆様ありがとうございました。
（ta） 2023/02/07(火) 14:29:30

コメント返信：

[ 一覧(最新更新順) ]

[[20230206173218]] 『集計データを指定の日付期間で絞り込みたい』（ta） ページの最後に飛ぶ

[[20230206173218]]　『集計データを指定の日付期間で絞り込みたい』（ta）　ページの最後に飛ぶ