Results of 1 - 1 of about 19633 for 20�����������������������... (0.004 sec.)
- [[20201124173436]]
- #score: 2681
- @digest: bdb0aca54fbe78514ef42955e4d09e46
- @id: 85873
- @mdate: 2020-11-24T12:04:44Z
- @size: 6876
- @type: text/plain
- #keywords: 度| (72282), 月度 (42771), 年06 (26936), 余", (22969), 度* (22399), 械2 (19139), 剰余 (18880), 械1 (18846), 年07 (15154), 機械 (14700), 年05 (13845), 余, (12619), 付機 (11091), 付"} (10014), 付, (8372), 日| (7922), 度" (6019), table (5412), 性", (5412), 度") (5223), distinct (5090), transformcolumntypes (4947), 検算 (4893), 月30 (4612), 値") (4089), 数量 (4060), 量2 (3382), デッ (3170), where (3108), 月21 (2914), ピボ (2563), sheet1 (2430)
- 『月別集計について』（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 ＞ ---- シート2の1行目は、既に入力されているという前提でよろしいですか? シート1のF列に作業列 F2=TEXT(EOMONTH(A2-20,1),"yy年mm月度") シート2の 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 ---- Power Queryだと、こんな感じでできました。 1）元データをテーブルに変換 ※見出しが勝手に変換されてしまいます 2）そのテーブル名を、"月別データ"に変更 3）空のクエリを作成し、詳細エディターに以下をコピペ 4）「閉じて次に読み込む」で、シート2に、テーブル出力 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... - http://www.excel.studio-kazu.jp/wiki/kazuwiki/202011/20201124173436.txt - [detail] - similar
PREV
NEXT
Powered by Hyper Estraier 1.4.13, with 97028 documents and 607992 words.
訪問者：