[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『日付の一定期間の数字の合計を出したい』（fnseven）
日付の月毎に数字の合計と回数を月毎に出したいです。
ご教授お願いします。
元データ
日付 停止時間
2021/4/1 30分
2021/4/4 60分
2021/4/10 1000分
2021/4/21 1500分
2021/4/22 30分
2021/4/25 60分
↓
↓
↓
2021/12/15 120分
2021/12/29 30分
上記のような表から 下記の表のように
4月〜12月までの間で月毎に算出したいです。
（4月まとめ）
30分以下の停止時間 2回 停止時間合計60分
30分以上240分以下の停止時間 2回 停止時間合計120分
240分以上1440分以下の停止時間 2回 停止時間合計1000分
1440以上の停止時間 1回 停止時間合計1500分
（5月まとめ）
〜
〜
〜
〜
（6月まとめ）
〜
〜
〜
〜
宜しくお願いします。
＜ 使用 Excel：Office365、使用 OS：Windows10 ＞
別案です。 "時間区分"の列を追加したうえで、"ピボットテーブル"を使うと、 月の判定も自動でしますし、便利かと思います。 ピボットテーブルは食わず嫌いの方が多いですが、結構使える奴ですよ。 一度作成してしまえば、あとは、データを更新していくだけの簡単操作です。 ・"時間区分"の設定は、MATCH関数、INDEX関数を使うとよいと思います。 ・また、ピボットテーブルでの"時間区分"のソート順は、 手作業（"アイテムをドラッグして並び替える"と記載されています）が早いでしょうか。 ・ちなみに、３０分以上、３０分以下と重なっているので、修正が必要でしょうか。 （γ） 2022/01/03(月) 17:22
A列が日付、B列に停止時間が数値で入力されているとして D,E,F列に書き出し。
Sub sample()
Dim dic As Object
Dim rng As Range
Dim arr, v
Dim i As Long, cnt As Long
Dim sd As String, ed As String, memo As String
Dim c As String, s As String
Set dic = CreateObject("Scripting.Dictionary")
For Each rng In Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp))
If Not rng.Value = "" Then
On Error Resume Next
dic.Add Year(rng.Value) & "/" & Month(rng.Value) & "/1", ""
On Error GoTo 0
End If
Next rng
arr = dic.keys
For i = LBound(arr) To UBound(arr)
cnt = cnt + 1
sd = arr(i)
ed = Format(WorksheetFunction.EoMonth(arr(i), 0), "yyyy/m/d")
memo = "30分以下の停止時間,30分超え240分以下の停止時間,240分超え1440分以下の停止時間,1440分超えの停止時間"
With Cells(cnt, 4)
.Value = "(" & Month(sd) & "月まとめ)"
.Offset(1).Resize(4).Value = WorksheetFunction.Transpose(Split(memo, ","))
End With
c = "=COUNTIFS(A:A,"">=" & sd & """,A:A,""<=" & ed & """,B:B,"""
s = "=SUMIFS(B:B,A:A,"">=" & sd & """,A:A,""<=" & ed & """,B:B,"""
For Each v In Array("<=30"")", ">30"",B:B,""<=240"")", ">240"",B:B,""<=1440"")", ">1440"")")
cnt = cnt + 1
With Cells(cnt, 5)
.Formula = c & v
.Offset(, 1).Formula = s & v
End With
Next v
Next i
End Sub
（寒） 2022/01/03(月) 23:33
年月 〜30分 〜240分 〜1440分 1441分〜 2021年04月停止回数 2回 2回 1回 1回 2021年04月合計時間 60分 120分 1000分 1500分 2021年12月停止回数 1回 1回 2021年12月合計時間 30分 120分
'------
let
ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
置き換えられた値 = Table.ReplaceValue(ソース,"分","",Replacer.ReplaceText,{"停止時間"}),
変更された型 = Table.TransformColumnTypes(置き換えられた値,{{"停止時間", Int64.Type}, {"日付", type date}}),
追加されたカスタム = Table.AddColumn(変更された型, "年月", each Date.ToText([日付], "yyyy年MM月")),
追加された条件列 = Table.AddColumn(追加されたカスタム, "区分", each if [停止時間] <= 30 then "〜30分" else if [停止時間] <= 240 then "〜240分" else if [停止時間] <= 1440 then "〜1440分" else "1441分〜"),
グループ化された行 = Table.Group(追加された条件列, {"年月", "区分"}, {{"停止回数", each Table.RowCount(_), Int64.Type}, {"合計時間", each List.Sum([停止時間]), type nullable number}}),
追加されたサフィックス1 = Table.TransformColumns(グループ化された行, {{"停止回数", each Text.From(_, "ja-JP") & "回", type text}}),
追加されたサフィックス2 = Table.TransformColumns(追加されたサフィックス1, {{"合計時間", each Text.From(_, "ja-JP") & "分", type text}}),
ピボット解除された列 = Table.UnpivotOtherColumns(追加されたサフィックス2, {"年月", "区分"}, "属性", "値"),
ピボットされた列 = Table.Pivot(ピボット解除された列, List.Distinct(ピボット解除された列[区分]), "区分", "値"),
結合された列 = Table.CombineColumns(ピボットされた列,{"年月", "属性"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"年月"),
並べ替えられた列 = Table.ReorderColumns(結合された列,{"年月", "〜30分", "〜240分", "〜1440分", "1441分〜"})
in
並べ替えられた列
（マナ） 2022/01/04(火) 20:51
年月 〜30分 〜240分 〜1440分 1441分〜
2021年04月停止回数 2回 2回 1回 1回
合計時間 60分 120分 1000分 1500分
2021年12月停止回数 1回 1回
合計時間 30分 120分
'-------
並べ替えられた列 = Table.ReorderColumns(結合された列,{"年月", "〜30分", "〜240分", "〜1440分", "1441分〜"}),
置き換えられた値1 = Table.ReplaceValue(並べ替えられた列,each [年月],each if Text.EndsWith([年月], "合計時間") then "合計時間" else [年月],Replacer.ReplaceText,{"年月"})
in
置き換えられた値1
'-------
参考；
Replace value in Power Query based on condition
https://community.powerbi.com/t5/Desktop/Replace-value-in-Power-Query-based-on-condition/td-p/1367325
（マナ） 2022/01/06(木) 23:32
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.