[[20220813163019]]　『週別計算をしたい。SUMIF？』（りんご）　ページの最後に飛ぶ

『週別計算をしたい。SUMIF？』（りんご）

教えて下さい。
最終的なアウトプットは下記のようにしたいですが、集計にあたり色々と条件があります。
＜Output＞

   A    B       C       D
 1 月	売上週	A商品	Ｂ商品
 2 May	wk18	2	1
 3 	wk19	2	2
 4 	wk20	3	
 5 	wk21	1	4
 6 June	wk22	5	7
 7 	wk23	4	
 8 	wk24	4	1
 9 	wk25		2

やりたい事は下記ピボットで別Excelからまとめたものを、上記のようなOutputの形式にしたいです。
例えば、ピボットで集計した場合、wk22は5月と6月にまたがっていますが、outputでは最新月の方の6月にカウントしたいです。

＜ピボット集計結果＞

   F    G       H       I
 1 月	売上週	A商品	Ｂ商品
 2 5月	wk18	2	1
 3 	wk19	2	2
 4 	wk20	3	
 5 	wk21	1	4
 6 	wk22	2	4
 7 6月	wk22	3	3
 8 	wk23	4	
 9 	wk24	4	1
 10 	wk25		2

自分で考えたのは、予めoutputに、5月はwk〇週～〇週、6月はwk〇週～〇週と調べて、表を作っておいて、そこに下記関数でA商品とB商品の足し算をSUMIFでする。
A商品の式(C2セル) =IF(SUMIF($G:$G,$B2,H:H)=0,"",SUMIF($G:$G,$B2,H:H))

理想は、outputにはwkの連番だけ最初に記入しておいて、月の切れ目もどちらになるかも含めて自動で関数(もしくはVBAで)でいれられないかと思っています。

週を計算する式は、ISOWEEKNUMを使用しています。
（その年の最初の木曜日を含む週を第 1 週とし、週の始まりを月曜日とする方式）
今回の例では5/30(月)～6/5(日)までがwk22にあたります。
・22週が5月と6月に売り上げが跨る場合は最新の月=6月にカウントしたい。
・もし、22週が5月にしか売り上げない場合（5/30, 31は売上ありだが、6/1～6/5は売れなかった）は22週は5月にカウントしたい。
・もし22週は全く売れなかった場合は、最新の月=6月にカウントしたい。
↓
更に、下記のケースも重なった場合、可能なら織り込みたい（但し稀なケースなので手動でやるしかないかと思っていますが…）
月によっては、最新の月の週が土日のみにあたる場合もあると思いますが、その場合は前の月にカウントしたい（土日は販売していない為））
例：2023/3/27(月)-3/31(金)まではWK13で売上無し。4/1-4/2もWK13で売上無しだが、土日の為、そもそも販売していないからWK13は3月にカウントしたい。

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

Power Queryでできそうですが
興味があれば検索してみてください。

（マナ） 2022/08/13(土) 17:38

ありがとうございます。
Power QueryだとVBAともまた違って色々な事が出来るのですね。
未知の領域なので、勉強してみます。

今回は複数の人で出来るようにしたいので、馴染みのある関数かマクロで対応したいと思っていますが、
関数だとこのようなケースはは厳しいでしょうか。
（りんご） 2022/08/13(土) 18:17

関数は得意ではないので、全くわかりません。

（マナ） 2022/08/13(土) 19:03

B1選択、Shift + Ctrlを押しながら、↓キー
Shiftを押したまま、←キー
Shift + Tab を1回
Shiftを押したまま、←キー
検索と選択、ジャンプ、セル選択で空白セル
数式バーに1番上の空白セルにそのセルの上のセルを
参照する数式を数式バーで入力
=A2
Ctrl を押しながらEnterで確定
（ひとりよがり） 2022/08/13(土) 20:33

 １．実際の商品は、何種類(何列）あるんですか？

 ２．最終商品列の右隣に当該行の小計が算出されているなんて事はないですか？

（半平太） 2022/08/14(日) 09:06

ひとりよがり様
ありがとうございます。
教えて頂いた方法だと作業列作らずに空白セルに上の行を入れられるのですね。勉強になりました。

半平太様
実際の商品は2種類なのですが、仰る通り、A商品の内訳、B商品の内訳がピボットの小計列で隠れています。。
なので、A商品の列、B商品の列を特定して拾いたいです…。（内訳は2列の場合もあれば、3列の場合もあったりして、B商品の始まる列番号が変わってきてしまうので…。
何か良い案はありますでしょうか。

（りんご） 2022/08/14(日) 09:46

 ちょっと話の展開がおかしくなった気がしますが、
 実際のピボット集計結果は質問文の通りではないと言うことですか？

 下図の様に商品がランダムに並んだ集計結果を利用して、A商品  B商品の2商品についてだけ、
 貴希望図の様に加工したいと言うことなんですか？

 <ピボット集計結果>
 行  _____F_____  ___G___  __H__  ___I___  __J__  __K__ 
  1  合計 / 数量           商品                         
  2  月           売上週   A商品  AC商品   B商品  総計  
  3  5月          wk18        2        1      1      4 
  4               wk19        2        2      2      6 
  5               wk20        3               3      6 
  6               wk21        1        4      4      9 
  7               wk22        2        2      5      9 
  8  5月 集計                10        9     15     34 
  9  6月          wk22        2        2      6     10 
 10               wk23        4               7     11 
 11               wk24        4        1      8     13 
 12               wk25                 2      9     11 
 13  6月 集計                10        5     30     45 
 14  総計                    20       14     45     79

（半平太） 2022/08/14(日) 10:41

参考までに、Power Queryだと、

今あるピボットテーブルを元データとするのではなく
別ブックの元データを利用します。
関数はわかりませんが、マクロでもそのほうが簡単だと思います。

１）別ブックの元データがこんな感じとして

      -A-        -B-     -C-     -D-
 1  売上日     売上週    商品    売上
 2  2022/5/2    wk18     A商品    1   
 3  2022/5/3    wk18     A商品    1

２）Power Queryで売上週と商品でグループ化して
　　シートにピボットテーブル形式で読み込む

 let
    //ここで別ブックのファイルパス指定
    ソース = Excel.Workbook(File.Contents("D:\***\****\*****.xlsx"), null, true),
    //ここで別ブックのシート名を指定
    対象Sheet = ソース{[Item="Sheet1",Kind="Sheet"]}[Data],
    昇格されたヘッダー数 = Table.PromoteHeaders(対象Sheet, [PromoteAllScalars=true]),
    変更された型 = Table.TransformColumnTypes(昇格されたヘッダー数,{{"売上日", type date}, {"売上週", type text}, {"商品", type text}, {"売上", Int64.Type}}),
    挿入された月の開始日 = Table.AddColumn(変更された型, "月の開始日", each Date.StartOfMonth([売上日]), type date),
    グループ化された行 = Table.Group(挿入された月の開始日, {"売上週", "商品"}, {{"合計", each List.Sum([売上]), type nullable number}, {"月", each List.Max([月の開始日]), type date}})
 in
    グループ化された行

３）内訳というのがよくわかりませんが、たぶん ↑ に影響はないと思います。
　　要は、Power Queryでデータ加工した上で、ピボットテーブルにするということです。
　　（ピボットテーブルでなく、普通のテーブル形式で読み込んでもよいのですが）

（マナ） 2022/08/14(日) 10:45

 １．A1にスタート月の初日を入れる。(例:2022/5/1)。
 　　セル表示形式で、ユーザー定義を　「m"月開始"」と設定して「5月開始」と表示させる。

 ２．数式の入力
 (1) M3セル(2商品計) =BYROW(H3:J100,LAMBDA(r,SUM(SUMIF(H$2:J$2,C1:D1,r))))

 (2) N3セル =F3

 (3) N4セル =IF(G4="","",IF(AND(G2=G4,M2,M4=0),"",IF(AND(G1=G3,M1,M3=0),F3&"",F4&"")))
     この数式だけは下にコピーする必要があります。(これ以外は、他のセルにコピーは不要）

 (4) B2セル ="wk"&ISOWEEKNUM(WORKDAY(A1,1))+SEQUENCE(COUNTA(UNIQUE(G:G))-2,1,0)
 (5) C2セル =SUMIF($G2:$G100,$B2#,INDEX($H2:$K100,0,MATCH(C1,$H2:$K2,0)))
 (6) D2セル =SUMIF($G2:$G100,$B2#,INDEX($H2:$K100,0,MATCH(D1,$H2:$K2,0)))

 (7) A2セル =XLOOKUP(B2#,G3:G100,N3:N100,"",0,-1)

 <結果図>
 行 ____A____ ___B___ __C__ __D__  E  _____F_____ ___G___ __H__ ___I___ __J__ __K__ _L_ ___M___ _N_
  1  2022/5/1 売上週  A商品 B商品     合計 / 数量         商品                                     
  2 5月       wk18        2     1     月          売上週  A商品 AC商品  B商品 総計      2商品計    
  3           wk19        2     2     5月         wk18       2       1     1     4           3  5月
  4           wk20        3     3                 wk19       2       2     2     6           4    
  5           wk21        1     4                 wk20       3             3     6           6    
  6           wk22        0     1                 wk21       1       4     4     9           5    
  7 6月       wk23        4     7                 wk22       0       2     1     3           1    
  8           wk24        4     8     5月 集計              10       9    15    34          25    
  9           wk25        0     9     6月         wk22       0       2     0     2           0    
 10                                               wk23       4             7    11          11  6月
 11                                               wk24       4       1     8    13          12    
 12                                               wk25               2     9    11           9    
 13                                   6月 集計              10       5    30    45          40    
 14                                   総計                  20      14    45    79          65    
 15                                                                                           0

（半平太） 2022/08/14(日) 11:58

半平太様
混乱させて申し訳ありません。
小計行はありません。
また、A商品、B商品以外も存在しません。集計したいのは、A商品、B商品の2列です。
ただし、A商品、B商品の内訳が更に存在し、ピボット作成時にグループ化しています。
A商品は、A-1,A-2の合計、B商品はB-1,B-2…の合計のように、ピボット作る時にグループ化して、内訳列は非表示としています。
ここまで考えると複雑なので、内訳は特に考えずに単純にA商品とB商品の値を週別に計算するという、一番最初に書いた質問について
まず検討出来ればと思っております。

マナ様
ありがとうございます！
ご提示して頂いたコードを再現できるように、まずはPower queryいじってみたいと思います。
（りんご） 2022/08/14(日) 12:00

半平太様
色々ありがとうございます。
今、再現しようとトライしていますが、家のPCがOffice365に対応していない為、所々使用できない部分がありますので、まずは一つづつ意味を理解してみて、後日やってみたいと思います。
配列関数等、知らない関数が色々含まれていたので、今後の勉強の参考にもなり、大変ありがとうございます。

（りんご） 2022/08/14(日) 12:37

 想定レイアウトが実態と異なるのであれば、検討は徒労になります。

（半平太） 2022/08/14(日) 13:29

こんな感じですかね？
_

代表式、

=SUMPRODUCT(($B2=OFFSET($F$1,1,1,COUNTA($G:$G)-1,1))*OFFSET($F$1,1,COLUMN()-1,COUNTA($G:$G)-1,1))
_

検証ファイル、

https://1drv.ms/x/s!AjviygfJDgV_gY80mSoI5PIx_BSY7g
_

因みに、

月次代わりの　記載条件が、
不明だったので、
其処の　抜き出しは、
してませんし、

ピボットテープルへの　数値参照のための、
式も　含めてませんよ。
_

尚、

ファイルは　1度、
ローカルに　別名保存して、
其の　保存ファイルを、
扱うように　してくださいね、

別名保存でないと、
意味が　無いですよ。
_

さすれば、

閲覧も、編集も、
叶うものと　思いますよ。
（Nouble） 2022/08/18(木) 15:42

コメント返信：

[ 一覧(最新更新順) ]

[[20220813163019]] 『週別計算をしたい。SUMIF？』（りんご） ページの最後に飛ぶ

[[20220813163019]]　『週別計算をしたい。SUMIF？』（りんご）　ページの最後に飛ぶ