[[20220912092843]]　『PowerQueryで時間の計算』（Aru）　ページの最後に飛ぶ

『PowerQueryで時間の計算』（Aru）

いつもお世話になっております。
早速ですが質問です。

各列の時間の合計を算出したいのですが、"分"が60進数の小数点で記載されております。PowerQueryだけで計算する方法があるのかご教示頂けませんでしょうか。

無い場合はEXCEL関数でやるしかないと考えております。

【例】A、B列が元データとしてあり、C列をPowerQueryで表示させたい
※時間と分のみで、秒はございません。

A列：60進数、B列：60進数、C列10進数

A列　　B列　　C列
1.45 1.15 3.00
1.15 1.15 1.30

ご教示のほど宜しくお願い致します。

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

 Textとして読んで→ . を : に置換 → Durationに変換 → 計算
 すればできると思います（検証してません）
（´・ω・`） 2022/09/12(月) 10:41

こんな感じ

テキスト型に変換
.を:に置換
期間型に変換
加算
合計時間数を計算

（マナ） 2022/09/12(月) 10:43

↑加算でなくて、統計-合計でした

（マナ） 2022/09/12(月) 10:52

 スミマセン
 PowerQueryをマトモに使える環境が無いのでやった事無いし、調味本位程度の質問なんですけど、
 文字列に変換する前の値がたまたま整数だったりしてもこれイケるんでしょうか?

 SQLで脳内変換してみて疑問に感じました。 いや、イケるんなら便利だなーと思って^^;

（白茶） 2022/09/12(月) 16:28

 だめですねぇ
 小数点以下があるときは　
 数値の 1.5  → 文字列の 1:50 → 期間の1時間50分 　となりますが、
 小数点以下がないと
 数値の 1  　→　文字列の 1 →　期間の 1日

 と期間に変換すると 1日 になっちゃいますね

 再考を求められました．．．
（´・ω・`） 2022/09/12(月) 16:39

 となれば、普通にやるだけですね。

 RoundDownで整数部取り出して、元の値から引いて小数部とりだして
 (整数部+小数部*100/60 )/24 をDuration.Fromで期間に変換して．．．　（検証してません）
（´・ω・`） 2022/09/12(月) 16:46

 あぁ、そうなんですね^^;

 承知しました。ありがとうございます

（白茶） 2022/09/12(月) 16:48

ご指摘ありがとうございます。
確かにだめですね。
期間に型変更する前に、":00”を付加するとか必要。

（マナ） 2022/09/12(月) 17:15

だめでした。

数値の 1.5 → 文字列の 1:5 → 期間の1時間5分

となってします。

（マナ） 2022/09/12(月) 18:12

最初に、100.0001を加算して、テキスト型に変換することにしました。
で、２～６文字目を抽出します。

 let
    ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
    変更された型 = Table.TransformColumnTypes(ソース,{{"列1", type number}, {"列2", type number}}),
    列に加算済み = Table.TransformColumns(変更された型, {{"列1", each _ + 100.0001, type number}}),
    列に加算済み1 = Table.TransformColumns(列に加算済み, {{"列2", each _ + 100.0001, type number}}),
    変更された型1 = Table.TransformColumnTypes(列に加算済み1,{{"列1", type text}, {"列2", type text}}),
    抽出されるテキスト範囲 = Table.TransformColumns(変更された型1, {{"列1", each Text.Middle(_, 1, 5), type text}, {"列2", each Text.Middle(_, 1, 5), type text}}),
    置き換えられた値 = Table.ReplaceValue(抽出されるテキスト範囲,".",":",Replacer.ReplaceText,{"列1", "列2"}),
    変更された型2 = Table.TransformColumnTypes(置き換えられた値,{{"列1", type duration}, {"列2", type duration}}),
    挿入された合計 = Table.AddColumn(変更された型2, "加算", each List.Sum({[列1], [列2]}), type duration),
    計算された合計時間 = Table.TransformColumns(挿入された合計,{{"加算", Duration.TotalHours, type number}})
 in
    計算された合計時間

（マナ） 2022/09/12(月) 18:44

 ド素人がスミマセン。
 例えば↓こういうのって当初案に使えませんかね?　(あんまりよく分かってないですけど...^^;)

 = Table.TransformColumns(ソース,{{"列1", each Number.ToText(_, "F2"), type text}})

（白茶） 2022/09/12(月) 18:55

さすが白茶さん。大変勉強になりました。

（マナ） 2022/09/12(月) 19:11

質問者さんがおいてけぼりになると困るので

 白茶さんの方法は、１ステップで処理できるのですが
 数式バーあるいは詳細エディターの編集が必要になります。
 操作だけでする場合は、下式をカスタム列の追加で。

 =Number.ToText([列1],"F2")

（マナ） 2022/09/12(月) 19:36

列1・列2に 24時間以上がないなら

= Time.From(Number.ToText([列1]*100, "0\:00"))

でもいいかもしれません。
（d-q-t-p） 2022/09/13(火) 09:56

 遊んでみました。

 「.」を「:」に変換して CSV で保存後 PowerQuery で読み込む

     |[A]  |[B]  |[C]  |[D]  
 [1]|時間1|時間2|時間3|時間4
 [2]| 1.45| 1.15|    3|3:00 
 [3]| 1.15| 1.15|  1.3|1:30 
 D1=DOLLARDE(C2,60)/24

　PowerQuery で編集しエクセルに読み込み結果

    |[A]  |[B]  |[C]  |[D]       |[E]    |[F]   
 [1]|時間1|時間2|時間3|時間4     |Column1|10進数
 [2]| 1.45| 1.15|    3|0.03:00:00|       |     3
 [3]| 1.15| 1.15|  1.3|0.01:30:00|       |   1.5

 ここまで来るのに手数がかかりますよー。

 小数点で時間を表すなら PowerQuery は使用しない方がいいでしょう。
（nm） 2022/09/13(火) 21:47

横から失礼いたします。
パワークエリ修行中の身ですが、自分の勉強のためにやってみました。
みなさまのやりとりと以前教えていただいた知識を総動員するとこんな感じになりました。

let

    ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
    変更された型 = Table.TransformColumnTypes(ソース,{{"A列", type text}, {"B列", type text}}),
    置き換えられた値 = Table.ReplaceValue(変更された型,".",":",Replacer.ReplaceText,{"A列", "B列"}),
    変更された型1 = Table.TransformColumnTypes(置き換えられた値,{{"A列", type duration}, {"B列", type duration}}),
    追加されたカスタム = Table.AddColumn(変更された型1, "C列", each Number.From([A列]+[B列]*24))
in
    追加されたカスタム

（まっち） 2022/09/14(水) 13:18

一行ミスしてました。

    追加されたカスタム = Table.AddColumn(変更された型1, "C列", each Number.From(([A列]+[B列])*24))

（まっち） 2022/09/14(水) 13:20

 質問者が行方不明のままなので、どうしようかなと思いましたが、
 識者の皆様の指摘をまとめました

 1.15 + 1.15 = 2.3 なんだろーなぁと思いながら...

 let
    ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
    変更された型 = Table.TransformColumnTypes(ソース,{{"列1", type number}, {"列2", type number}}),
    追加されたカスタム = Table.AddColumn(変更された型, "列1のテキスト", each Number.ToText([列1],"F2")),
    追加されたカスタム1 = Table.AddColumn(追加されたカスタム, "カスタム", each Number.ToText([列2],"F2")),
    置き換えられた値 = Table.ReplaceValue(追加されたカスタム1,".",":",Replacer.ReplaceText,{"列1のテキスト", "カスタム"}),
    変更された型1 = Table.TransformColumnTypes(置き換えられた値,{{"列1のテキスト", type duration}, {"カスタム", type duration}}),
    挿入された合計 = Table.AddColumn(変更された型1, "加算", each List.Sum({[列1のテキスト], [カスタム]}), type duration),
    追加されたカスタム2 = Table.AddColumn(挿入された合計, "カスタム.1", each Duration.Hours([加算])+Duration.Minutes([加算])/60),
    削除された列 = Table.RemoveColumns(追加されたカスタム2,{"列1のテキスト", "カスタム", "加算"})
 in
    削除された列
（´・ω・`） 2022/09/14(水) 13:40

蛇足ですが、操作手順
最初は、Power Queryエディターので、目的のメニューを探すだけで一苦労なので。

 ・[列1]列を選んで、列の追加/カスタム列
 　  新しい列名：カスタム　　カスタム列の式：=Number.ToText([列1],"F2")
 ・[列2]列を選んで、列の追加/カスタム列
 　  新しい列名：カスタム.1　　カスタム列の式：=Number.ToText([列2],"F2")
 ・[カスタム][カスタム.1]列を選んで、右クリック/値の置換 で、「.」を「:」に置換
 ・[カスタム][カスタム.1]列を選んで、右クリック/型の変更/期間
 ・[カスタム][カスタム.1]列を選んで、右クリック/変換/合計時間数
 ・[カスタム][カスタム.1]列を選んで、右クリック/合計
 ・[加算]列を選んで、右クリック/他の列の削除

（マナ） 2022/09/15(木) 08:29

コメント返信：

[ 一覧(最新更新順) ]

[[20220912092843]] 『PowerQueryで時間の計算』（Aru） ページの最後に飛ぶ

[[20220912092843]]　『PowerQueryで時間の計算』（Aru）　ページの最後に飛ぶ