[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『月別集計について』（kochan）
お世話になります。
Sheet1にはA列に日付、B列以降は機械名・数量が20列ほど入っています。
また、5/30は2行発生するなど、行数も複数になる場合があります。
【Sheet1】
A B C D E F
1 日付 機械1 数量 機械2 数量
2 5/21 AAA 12 ABA 81
3 5/30 BBB 60 CCC 12
4 5/30 DFD 60 BBB 12
5 6/10 AAA 33 VVV 67
上記データをSheet2で月毎に集計したいのですが、5/21〜6/20が6月度、
6/21〜7/20が7月度というくくりで数量を集計ができないかと悩んでおります。
【Sheet2】
A B C D E F
1 月度 AAA BBB CCC DFD
2 6月度 * * * *
3 7月度 * * * *
3 8月度 * * * *
4 9月度 * * * *
もし、B2に入れる関数で良い方法が在ればご教授いただければ幸いです。
どうぞよろしくお願い致します。
＜ 使用 Excel：Excel2016、使用 OS：Windows10 ＞
シート２の1行目は、既に入力されているという前提でよろしいですか？
シート１のF列に作業列
F2=TEXT(EOMONTH(A2-20,1),"yy年mm月度")
シート２の
B2=SUMPRODUCT((Sheet1!$F$2:$F$100=$A2)*(((Sheet1!$B$2:$B$100=B$1)*Sheet1!$C$2:$C$100)+((Sheet1!$D$2:$D$100=B$1)*Sheet1!$E$2:$E$100)))
|[A] |[B] |[C] |[D] |[E] |[F] |[G] |[H]
[1] |日付 |機械1|数量 |機械2|数量 |年月 |検算 |
[2] |5月1日 |A | 10|D | 10,000|20年05月度| |
[3] |5月10日|B | 100|D | 10,000|20年05月度| |
[4] |5月20日|C | 1,000|D | 10,000|20年05月度|31,110|
[5] |5月21日|A | 10|A | 10|20年06月度| |
[6] |5月30日|B | 100|A | 10|20年06月度| |
[7] |5月31日|C | 1,000|A | 10|20年06月度| |
[8] |6月1日 |D |10,000|A | 10|20年06月度| |
[9] |6月10日|B | 100|A | 10|20年06月度| |
[10]|6月20日|B | 100|A | 10|20年06月度|11,370|
[11]|6月21日|A | 10|D | 10,000|20年07月度| |
[12]|6月30日|B | 100|E | 100,000|20年07月度| |
[13]|7月1日 |C | 1,000|F |1,000,000|20年07月度|11,110|E,F除く
|[A] |[B]|[C]|[D] |[E] |[F]
[1] | |A |B |C |D |検算
[2] |20年05月度| 10|100|1,000|30,000|31,110
[3] |20年06月度| 70|300|1,000|10,000|11,370
[4] |20年07月度| 10|100|1,000|10,000|11,110
[5] |20年08月度| 0| 0| 0| 0|
[6] |20年09月度| 0| 0| 0| 0|
[7] |20年10月度| 0| 0| 0| 0|
[8] |20年11月度| 0| 0| 0| 0|
[9] |20年12月度| 0| 0| 0| 0|
[10]|21年01月度| 0| 0| 0| 0|
[11]|21年02月度| 0| 0| 0| 0|
[12]|21年03月度| 0| 0| 0| 0|
[13]|21年04月度| 0| 0| 0| 0|
でいかがでしょうか？ （稲葉） 2020/11/24(火) 18:31
＞B列以降は機械名・数量が20列ほど入っています。 ((Sheet1!$B$2:$B$100=B$1)*Sheet1!$C$2:$C$100) ↑このブロックを列数分作り、+でつなげてください。
行数もできるだけ小さい範囲で可変してください。 ((Sheet1!$B$2:$B$100=B$1)*Sheet1!$C$2:$C$100) +((Sheet1!$D$2:$D$100=B$1)*Sheet1!$E$2:$E$100) +((Sheet1!$F$2:$F$100=B$1)*Sheet1!$G$2:$G$100) +((Sheet1!$H$2:$H$100=B$1)*Sheet1!$I$2:$I$100) てな具合で。
これで計算が遅くなるようでしたら、列を行に直して集計しやすい表にすれば、ピボットテーブルで集計できると思います。 （稲葉） 2020/11/24(火) 18:49
私がやるなら元となるデータを次のようにできないか、検討してみます。 A B C D 1 日付 機械 数量 月度 2 5月20日 AAA 12 5月度 3 5月21日 ABA 81 6月度 4 5月30日 BBB 60 6月度 5 5月30日 CCC 12 6月度 6 5月30日 DFD 60 6月度 7 5月30日 BBB 12 6月度 8 6月10日 AAA 33 6月度 9 6月10日 VVV 67 6月度 D2のセルには次の数式 =IF(DAY(A2)>20,MONTH(DATE(YEAR(A2),MONTH(A2)+1,1)),MONTH(A2))&"月度" あとはピボットテーブルで次のような集計ができます。 合計 / 数量 列ラベル 行ラベル AAA ABA BBB CCC DFD VVV 総計 5月度 12 12 6月度 33 81 72 12 60 67 325 総計 45 81 72 12 60 67 337
（みやほりん） 2020/11/24(火) 18:50
＞D2のセルには次の数式 ＞=IF(DAY(A2)>20,MONTH(DATE(YEAR(A2),MONTH(A2)+1,1)),MONTH(A2))&"月度"
↓ でも同じ結果になると思います。 =MONTH(EDATE(A2-20,1))&"月度"
参考まで （笑） 2020/11/24(火) 19:07
もし元となるデータを変換できるのであれば、クエリで簡単にできると思います。 項目名がかぶらない様に、 機械1に対して、数量1 機械2に対して、数量2など項目名を変更してあげて、下記のようなSQL組めばできるかと。 SELECT `Sheet1$`.日付, `Sheet1$`.機械1 , `Sheet1$`.数量1 FROM `Sheet1$` WHERE `Sheet1$`.機械1 IS NOT NULL UNION SELECT `Sheet1$`.日付, `Sheet1$`.機械2, `Sheet1$`.数量2 FROM `Sheet1$` WHERE `Sheet1$`.機械2 IS NOT NULL UNION SELECT `Sheet1$`.日付, `Sheet1$`.機械3, `Sheet1$`.数量3 FROM `Sheet1$` WHERE `Sheet1$`.機械3 IS NOT NULL UNION SELECT `Sheet1$`.日付, `Sheet1$`.機械4, `Sheet1$`.数量4 FROM `Sheet1$` WHERE `Sheet1$`.機械4 IS NOT NULL
（稲葉） 2020/11/24(火) 19:19
１）元データをテーブルに変換 ※見出しが勝手に変換されてしまいます ２）そのテーブル名を、"月別データ"に変更 ３）空のクエリを作成し、詳細エディターに以下をコピペ ４）「閉じて次に読み込む」で、シート２に、テーブル出力
let
ソース = Excel.CurrentWorkbook(){[Name="月別データ"]}[Content],
ピボット解除された他の列 = Table.UnpivotOtherColumns(ソース, {"日付"}, "属性", "値"),
追加されたインデックス = Table.AddIndexColumn(ピボット解除された他の列, "インデックス", 0, 1, Int64.Type),
挿入された剰余 = Table.AddColumn(追加されたインデックス, "剰余", each Number.Mod([インデックス], 2), type number),
ピボットされた列 = Table.Pivot(Table.TransformColumnTypes(挿入された剰余, {{"剰余", type text}}, "ja-JP"), List.Distinct(Table.TransformColumnTypes(挿入された剰余, {{"剰余", type text}}, "ja-JP")[剰余]), "剰余", "値"),
並べ替えられた行 = Table.Sort(ピボットされた列,{{"インデックス", Order.Ascending}}),
下方向へコピー済み = Table.FillDown(並べ替えられた行,{"0"}),
削除された列 = Table.RemoveColumns(下方向へコピー済み,{"属性", "インデックス"}),
フィルターされた行 = Table.SelectRows(削除された列, each [1] <> null and [1] <> ""),
変更された型 = Table.TransformColumnTypes(フィルターされた行,{{"日付", type date}, {"0", type text}, {"1", Int64.Type}}),
追加されたカスタム = Table.AddColumn(変更された型, "月度", each Date.ToText(Date.AddMonths(Date.AddDays([日付],-20),1), "yyyy年M月度")),
削除された列1 = Table.RemoveColumns(追加されたカスタム,{"日付"}),
ピボットされた列1 = Table.Pivot(削除された列1, List.Distinct(削除された列1[#"0"]), "0", "1", List.Sum)
in
ピボットされた列1
（マナ） 2020/11/24(火) 21:04
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.