[[20230117104622]]　『パワークエリ　特定期間の抽出』（とっど）　ページの最後に飛ぶ

『パワークエリ　特定期間の抽出』（とっど）

こんにちは。
お手数をお掛け致しますが、お知恵をお貸しください。

【最終的にやりたいこと】
・温度が20°以上、20°以下に切り替わった"期間のデータ"を
　フィルタで検出できるようにしたく。
<条件>
・期間のデータの取得条件は以下のようにしたい。
　１．温度が20°以上に切り替わった時間から5分前
　２．温度が20°以上に切り替わった時間から5分後
　※E列範囲に1がある期間のデータを取得したく。
・何個目の期間データかを把握するため、カウントしたい。
　※F列　カウンタで何回目の期間データかをカウントする
→最終的にカウンタでフィルタを行うことで、
　任意のデータを取得したい。

【考えていること】
・温度が20°以上の場合、閾値に1を立てる
・温度が20°以上に切り替わった時間から5分前、また
　温度が20°以上に切り替わった時間から5分後、のE列に1を立てる
・範囲のデータの発生回数をF列でカウント

 A列　　B列　　C列　　D列　　E列　　F列
日付　 時刻　 温度　 閾値　 範囲　 カウンタ
1/16  23:46　 18.1     0      0      0  
1/16  23:47　 18.4     0      1      1   
1/16  23:48　 18.9     0      1      1
1/16  23:49　 19.3     0      1      1
1/16  23:50　 19.5     0      1      1
1/16  23:51　 19.8     0      1　    1
1/16  23:52　 20.0     1      1      1
1/16  23:53　 20.3     1      1      1
時間経過
1/17  00:53　 20.3     1      1      1
1/17  00:54　 20.0     1      1      1
1/17  00:55　 19.8     0      1      1
1/17  00:56　 19.6     0      1      1
1/17  00:57　 19.4     0      1      1
1/17  00:58　 19.2     0      1      1
1/17  00:59　 19.0     0      1      1
1/17  01:00　 18.7     0      0      0
時間経過
1/17  18:00　 19.0     0      0      0
1/17  18:01　 19.2     0      1      2
1/17  18:02　 19.3     0      1      2
1/17  18:03　 19.5     0      1      2
1/17  18:04　 19.7     0      1      2
1/17  18:05　 19.9     0      1      2
1/17  18:06　 20.0     1      1      2

長文となり、申し訳御座いませんが、お知恵をお貸しください。

以上

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

＞温度が20°以上に切り替わった時間から5分後、のE列に1を立てる
20°以下が１になっているのはなぜですか。
20°以上が対象ですよね。
＞・範囲のデータの発生回数をF列でカウント
カウント数の計算方法はどうしているのですか。
（わからん） 2023/01/17(火) 11:03:20

すいません、記載ミスです。

温度が20°以上に切り替わった時間から5分前、また
温度が20°以下に切り替わった時間から5分後、のE列に1を立てる

20°に切り替わったタイミングの前後5分間のデータを"期間データ"として取得したく。
最終的にＥ列に1がある期間のデータを集計したく。

カウント計算はアイデアなく、よい方法があればおしえて下さい。

以上
（とっど） 2023/01/17(火) 12:17:02

 パワークエリご指定ですか？ マクロじゃだめですか？
 途中にはいってる　時間経過　っていう邪魔な行は実際にあるんでしょうか
（´・ω・`） 2023/01/17(火) 12:53:58

 フラグ立てるだけなら関数でやるのが一番簡単かもしれません
（´・ω・`） 2023/01/17(火) 12:54:55

ご回答有難う御座います。

マクロではなくパワークエリで対応したく。
途中にはいっている時間経過は実際にありません。
時間経過を説明するために入れております。

以上
（とっど） 2023/01/17(火) 15:26:36

 >途中にはいっている時間経過は実際にありません。 
 切り替わった時間はどう判断しているのだろうか。
（?） 2023/01/17(火) 15:46:08

切り替わりは、温度が20°以上かどうかで判断しています(D列)。
欲しいデータは切替ったタイミングではなく、20°以上に
切り替わったタイミングの5分前(E列に1があるところ)からとなります。
例ではE列に１を入力していますが、これをクエリで自動でできないか検討しています。

以上
（とっど） 2023/01/17(火) 16:20:30

 ある行に着目すると、その行の時刻5分前から5分後の間の最大の温度が20度以上だったら、フラグを立てる
 という作業をパワークエリでやればいいのでは？
（´・ω・`） 2023/01/17(火) 16:38:31

 let
    ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
    変更された型 = Table.TransformColumnTypes(ソース,{{"日付", type date}, {"時間", type time}, {"温度", Int64.Type}}),
    追加された日付時刻 = Table.AddColumn(変更された型, "日付時刻", each DateTime.From([日付])+Duration.From(Text.From([時間])) as datetime),
    追加された対象期間 = Table.AddColumn(追加された日付時刻, "対象期間", each List.Max(Table.SelectRows(追加された日付時刻,(r)=>(r[日付時刻]<=[日付時刻]+#duration(0,0,5,0) and r[日付時刻]>=[日付時刻]-#duration(0,0,5,0)))[温度])>=20),
    削除された列 = Table.RemoveColumns(追加された対象期間,{"日付時刻"})
 in
    削除された列
（´・ω・`） 2023/01/17(火) 20:42:45

Excel的な発想では「上行/下行の値との比較」は処理負担を減らす常套手段ですけど
Power Queryでそれをやるとパフォーマンスがかえって悪くなります。

敢えてやるなら温度列をリストにしてずらして結合したものと比較するのが比較的ましなや
り方かと思います。何千行かでやってみると処理の速さが相当に違ってきます。

「日付」「時刻」「温度」の「テーブル1」があるものとして

    閾値リスト = List.Transform(テーブル1[温度], each Number.From(_>=20)),
    範囲リスト = [
        先頭値 = 閾値リスト{0},
        末尾値 = List.Reverse(閾値リスト){0},
        リストA = 閾値リスト&List.Repeat({末尾値}, 10),
        リストB = List.Repeat({先頭値}, 5)&閾値リスト&List.Repeat({末尾値}, 5),
        リストC = List.Repeat({先頭値}, 10)&閾値リスト,
        リスト結合 = List.Zip({リストA, リストB, リストC}),
        リスト編集 = List.Transform(
            リスト結合, each Number.From(List.Sum(_)>0)
        ),
        下位削除 = List.Skip(List.Reverse(リスト編集), 5),
        上位削除 = List.Skip(List.Reverse(下位削除), 5)
    ][上位削除],
    カウンタリスト = [
        リスト生成 = List.Generate(
            ()=> [i=0, x=0, xx=0],
            each [i]<=List.Count(範囲リスト),
            each [
                i=[i]+1,
                x=[x]+Number.From(範囲リスト{i-1}=0 and 範囲リスト{i}=1),
                xx=範囲リスト{i}*x
            ],
            each [xx]
        ),
        下位削除 = List.Reverse(List.Skip(List.Reverse(リスト生成)))
    ][下位削除],
    テーブル結合 = Table.FromColumns(
        Table.ToColumns(テーブル1) & {閾値リスト} & {範囲リスト} & {カウンタリスト},
        type table [日付=date, 時刻=time, 温度=number, 閾値=Int64.Type, 範囲=Int64.Type, カウンタ=Int64.Type]
    )

読み込み時間を減らすために List.Lastは避けています。
（d-q-t-p） 2023/01/18(水) 12:29:31

皆様

ご回答ありがとうございます。

ご提案して頂いた内容を試してみます。

以上
（とっど） 2023/01/19(木) 16:02:59

コメント返信：

[ 一覧(最新更新順) ]

[[20230117104622]] 『パワークエリ 特定期間の抽出』（とっど） ページの最後に飛ぶ

[[20230117104622]]　『パワークエリ　特定期間の抽出』（とっど）　ページの最後に飛ぶ