Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(指定なし : 指定なし)
1週間毎の集計&最終日の管理
投稿日時: 18/09/30 23:24:31
投稿者: satoshi3

いつもお世話になります。
 
在庫表の管理で、
Sheet1で、横にスクロールする形で管理しております。
・Sheet1の7行目のN列〜=WEEKNUM(N9,2)の式を入れております。(明日から40になります。)
・在庫数は、前日在庫-引数+配送=当日在庫の式を入れております。
・品数は40個ほどになります。
 
Sheet2で、1週間毎の集計をしたいのですが、中途半端なので助けて頂きたいです。
・H7に=SUMIF(Sheet1!$N$7:$HA$7,WEEKNUM(Sheet1!$N$9),Sheet1!N10:HA10)を入れて、
 1週間の販売数の集計をしています。
 
 @商品の入れ替え(行を入れ替えたりするので)があるので、
  Sheet1のコード@に紐づけるように集計したいです。(H7...H10...H13...)
 AH8にSheet1のコード@に紐づけるように日曜日の在庫数を入れたいです。(H8...H11...H14...)

 
ご教示お願い致します。
 
 

【Sheet1】
A	D	E	F	M	N	O	P	Q	R	S	T
					25	25	25	25	25	25	25
8					月	火	水	木	金	土	日
9	コード@コードA品名		6/18	6/19	6/20	6/21	6/22	6/23	6/24
10	600896	82407	筑前煮	引数	17	0	18	30	15	27	17
11	600896	82407	筑前煮	配送	48				96		
12	600896	82407	筑前煮	在庫	186	186	168	138	219	192	175
13	630331	46672	牛丼	引数	15	0	4	15	15	11	9
14	630331	46672	牛丼	配送	40				80		
15	630331	46672	牛丼	在庫	144	144	140	125	190	179	170
16	660669	45094	カレー	引数	37	0	18	23	36	28	21
17	660669	45094	カレー	配送	100				100		
18	660669	45094	カレー	在庫	285	285	267	244	308	280	259

 
【Sheet2】
A	B	C	H
5			25
6			6/18-6/24
7	600896	筑前煮	124
8	600896		175
9	600896		
10	630331	牛丼	69
11	630331		170
12	630331		
13	660669	カレー	163
14	660669		259
15	660669		

 
 

回答
投稿日時: 18/10/01 17:53:51
投稿者: WinArrow
投稿者のウェブサイトに移動

前提条件
 
Sheet1のデザインを若干変更できますか?
↓変更後デザイン

A     B   C   D   E   F  数量 
コード@ コードA 品名	区分	日付	週	
600896	82407   筑前煮	引数	6/18(月) 25  17
600896	82407	筑前煮	配送	6/18(月) 25  48
600896	82407	筑前煮	在庫	6/18(月) 25  186
600896	82407	筑前煮	引数	6/20(水) 25  18
600896	82407	筑前煮	配送	6/20(水) 25	
600896	82407	筑前煮	在庫	6/20(水) 25  168
600896	82407	筑前煮	引数	6/21(木) 25     30
600896	82407	筑前煮	配送	6/21(木) 25	
600896	82407	筑前煮	在庫	6/21(木) 25	138
600896	82407	筑前煮	引数	6/22(金) 25	15
600896	82407	筑前煮	配送	6/22(金) 25	96
600896	82407	筑前煮	在庫	6/22(金) 25	219
600896	82407	筑前煮	引数	6/23(土) 25	27
600896	82407	筑前煮	配送	6/23(土) 25	
600896	82407	筑前煮	在庫	6/23(土) 25	192
600896	82407	筑前煮	引数	6/23(土) 25	17
600896	82407	筑前煮	配送	6/25(月) 26	
600896	82407	筑前煮	在庫	6/25(月) 26	175

集計用
A    B  C   D
		週	 25
		最大日付 6/23(土)
600896	筑前煮	引数	 124
		配送	 144
		在庫	 192

 
計算式(説明上、同一シートにしてありますので、シートを変える場合は、適宜変更してください)
 
集計用
A列:入力
B列:入力 or VLOOKUPで参照
C列:入力
D列(21行目以降になっています)
D21:入力
D22:=SUMPRODUCT(MAX((F2:F20=D21)*(E2:E20))) 週の最大日付取得
D23:=SUMPRODUCT(($A$2:$A$20=A$23)*($D$2:$D$20=C23)*($F$2:$F$20=D$21)*($G$2:$G$20))
D24:=SUMPRODUCT(($A$2:$A$20=A$23)*($D$2:$D$20=C24)*($F$2:$F$20=D$21)*($G$2:$G$20))
D25:=SUMPRODUCT(MAX(($E$2:$E$20=D22)*($A$2:$A$20=$A23)*($D$2:$D$20=C25)*($G$2:$G$20)))
 
※D25セルの数式は、ちょっと自信ないので、いろいろなパターンで検証してみてください。
 
 
 
 
 

回答
投稿日時: 18/10/01 17:57:06
投稿者: WinArrow
投稿者のウェブサイトに移動

追加
 
変更後のデザインの右端の列はG列です。
 
なお、
変更後のデザイン表から
変更前のデザインには、ピボットテーブルを使えば変換可能です。

回答
投稿日時: 18/10/01 23:06:48
投稿者: WinArrow
投稿者のウェブサイトに移動

すみません
変更後デザインの表の下から3行目
>600896 82407 筑前煮 引数 6/23(土) 25 17
は、間違いです。
600896 82407 筑前煮 引数 6/25(月) 26 17
が正解です。
※質問者さんのデータでは、6/24になっていますが、
 数式検証のため、6/25に、変更してあります。
 
従って、集計表の
>600896    筑前煮    引数     124
も、124ではなく、107になります。
 
 
 
質問者さんは、「日曜日」の在庫を取得したいと希望されていますが、
 曜日ではなく、当該週の最大日で検索しています。
 理由は、日曜日のデータが存在しないかもしれないという想定をしました。
 

回答
投稿日時: 18/10/02 00:46:14
投稿者: Mike

Sheet1
   D   E   F   M   N  O  P  Q  R  S  T  U  》 Z  AA
7            週番  25  25  25  25  25  25  25  26 《  26 26
8            曜日  月  火  水  木  金  土  日  月 》  土 日
9 コード1 コード2 品名  状況 6/18 6/19 6/20 6/21 6/22 6/23 6/24 6/25 《 6/30 7/1
10 600896 82407 筑前煮 引数  17  0  18  30  15  27  17  39 》  19 11
11 600896 82407 筑前煮 配送  48         96       99 《    
12 600896 82407 筑前煮 在庫  186 186 168 138 219 192 175 235 》 170 159
13 630331 46672 牛丼  引数  15  0  4  15  15  11  9  12 《  10  4
14 630331 46672 牛丼  配送  40         80       93 》    
15 630331 46672 牛丼  在庫  144 144 140 125 190 179 170 251 《 206 202
16 660669 45094 カレー 引数  37  0  18  23  36  28  21  29 》  16 27
17 660669 45094 カレー 配送  100         100      23 《    
18 660669 45094 カレー 在庫  285 285 267 244 308 280 259 253 》 235 208

Sheet2
   B   C    H     I    J    K     L     M     N
5          25    26   27   28    29    30    31
6 コード1 品名  6/18-6/24 6/25-7/1 7/2-7/8 7/9-7/15 7/16-7/22 7/23-7/29 7/30-8/5
7 660669 カレー    163   142   113   103    87    85   119
8 660669       259   208   208   195    227    236   184
9 660669
10 600896 筑前煮    124   161   179   151    200    112   155
11 600896       175   159   91    98    21    47    43
12 600896
13 630331 牛丼     69   115   214   215    120    120   118
14 630331        170   202   157    95    123    77   115
15 630331

 
Sheet1 において、
1.セル D7 に、および、範囲 D7:HA50 に、それぞれ名前 Ref、dbase を付けておく。
2.範囲 D9:F50 を選択 ⇒ Alt+MC ⇒ [OK]
 
Sheet2 において、
3.列B に上図に示したようにコード番号を入力しておく。ただし、必ずしも昇順でなくても構わ
 ̄ ̄ないが、同じコードは3行にまとめておくこと。
4.式 =INDEX(INDIRECT(C$6),MATCH(B7,INDIRECT(B$6),0)) を入力したセル C7 のフィルハンドルを
 ̄ ̄「エイヤッ!」とダブルクリック
5.範囲 C7:C15 を選択して下記の[条件付き書式]で、式 C7=C6 に対してフォント色を白に設定
6.下記の左端に示したセルに、それぞれ右側に示す式を入力
 ̄ ̄ H7: =SUM(OFFSET(Ref,MATCH($B7,INDEX(dbase,,1),0)-1,MATCH("2018/1/1"+(H$5-1)*7,OFFSET(Ref,2,,,206),0)-1,,7))
 ̄ ̄ H8: =OFFSET(Ref,MATCH($B7,INDEX(dbase,,1),0)+1,MATCH("2018/1/1"+(H$5-1)*7+6,OFFSET(Ref,2,,,206),0)-1)
7.範囲 H7:H8 を[コピー]して、セル H10、H13 に[貼り付け]
8.範囲 H7:H14 を右側にズズーッとオートフィル

回答
投稿日時: 18/10/02 08:05:57
投稿者: んなっと

●Sheet2
 
     B    C D E F G     H     I
 5                   25    26
 6               6/18-6/24 6/25-7/1
 7 600896 筑前煮          124    112
 8 600896              175    168
 9 600896                     
10 630331  牛丼           69    65
11 630331              170    140
12 630331                     
13 660669 カレー          163    147
14 660669              259    267
15 660669                     
 
H7
=SUMIF(Sheet1!$N$7:$ZZ$7,H$5,INDEX(Sheet1!$N:$ZZ,MATCH($B7,Sheet1!$D:$D,0),))
H8
=LOOKUP(1,0/(Sheet1!$N$7:$ZZ$7=H$5),INDEX(Sheet1!$N:$ZZ,MATCH($B8,Sheet1!$D:$D,0)+2,))
H7:H9を選択して右方向・→下方向・↓
 
※ちなみに...
H6
=TEXT(HLOOKUP(H$5,Sheet1!7:9,3,),"m/d")&"-"&TEXT(LOOKUP(1,0/(Sheet1!$7:$7=H$5),Sheet1!$9:$9),"m/d")
右方向・→
 
 
●週番号25,26,....が年が変わって1に戻るようなことがない、
つまり増え続けるなら少しだけ簡単になります。
 
H8
=LOOKUP(H$5,Sheet1!$N$7:$ZZ$7,INDEX(Sheet1!$N:$ZZ,MATCH($B8,Sheet1!$D:$D,0)+2,))
H6
=TEXT(HLOOKUP(H$5,Sheet1!7:9,3,),"m/d")&"-"&TEXT(LOOKUP(H$5,Sheet1!7:9),"m/d")

投稿日時: 18/10/03 23:03:05
投稿者: satoshi3

WinArrowさん
Mikeさん
んなっとさん
 
遅くなりました。
いつも丸投げですが、皆様丁寧な回答ありがとうございます。
皆様のやり方を一つ一つ検証させてもらいました。
おかげで助かりました。
 
いつも本当にありがとうございます。