Excel (一般機能)

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

 
(Windows 7 Professional : Excel 2007)
他のシートに掛け算 
投稿日時: 18/09/23 22:58:43
投稿者: satoshi3

いつもお世話になっております。
 
Sheet1で、製造ライン、商品コード、商品名、B数、B単価、毎日の製造数を記載しています。
Sheet2で、毎日の製造金額が反映されています。(B数×B単価×製造数)
 
但し、Sheet2の製造金額(D列~)は、単純にB数(E列)×B単価(F列)×製造数(G列~)になっており、
ラインでまとめたり、商品の入れ替えがしにくい状態になっております。
 
それを、商品コードに合わせて、順番が変わっても、計算ができるようにしたいです。
 
 
ご教示お願い致します。
 
【Sheet1】

A	B	C	D	E	F	G	H
					9月1日	9月2日	9月3日
ライン	商品コード	品名	B単位	単価	土	日	月
P	660779	肉じゃが	35	451	2	2	2
T	662855	牛丼	23	117	0	4	0
T	662856	カレー	10	117	0	0	0
R	662960	うどんだし	50	135	5	5	2
R	663024	そばだし	50	135	0	0	4
P	662972	あんかけ	54	410	2	0	0
大	662970	焼き鳥タレ	53	650	1	1	1

 
【Sheet2】※現状
A	B	C	D	E	F
			1	2	3
ライン	商品コード	品名	土	日	月
P	660779	肉じゃが	31570	31570	31570
T	662855	牛丼	0	10764	0
T	662856	カレー	0	0	0
R	662960	うどんだし	33750	33750	13500
R	663024	そばだし	0	0	27000
P	662972	あんかけ	44280	0	0
大	662970	焼き鳥タレ	34450	34450	34450

 
 
【Sheet2】※理想
A	B	C	D	E	F
			1	2	3
ライン	商品コード	品名	土	日	月
P	660779	肉じゃが	31570	31570	31570
P	662972	あんかけ	44280	0	0
R	662960	うどんだし	33750	33750	13500
R	663024	そばだし	0	0	27000
T	662855	牛丼	0	10764	0
T	662856	カレー	0	0	0
大	662970	焼き鳥タレ	34450	34450	34450

回答
投稿日時: 18/09/23 23:14:07
投稿者: WinArrow
投稿者のウェブサイトに移動

現在は、どのようにして計算しているんでしょうか?
 
Sheet2に列を追加して(又は挿入して)
B単位、単価をVLOOKUP関数で取り込む方が手っ取り早いと思います。
その列が間ざわりならば非表示に数ればよいと思います。

投稿日時: 18/09/23 23:36:34
投稿者: satoshi3

申し訳ないです。
 
訂正します。
現状は、B数(D列)×B単価(E列)×製造数(F列~)です。
 
肉じゃがの場合、D3セルに、=SUM(Sheet1!D3*Sheet1!E3*Sheet1!F3)
        D4セルに、=SUM(Sheet1!D3*Sheet1!E3*Sheet1!G3)
が月末まで連続で入っております。
 
 
 
 

回答
投稿日時: 18/09/24 08:57:23
投稿者: sy

シート2のD列以降の製造金額の列は、シート1の製造数の列と日付の並びは必ず同じですか?
同じなら単純にSUMIF3つを掛けるだけですよね。

=SUMIF(Sheet1!$B:$B,$B3,Sheet1!$D:$D)*SUMIF(Sheet1!$B:$B,$B3,Sheet1!$E:$E)*SUMIF(Sheet1!$B:$B,$B3,Sheet1!F:F)

回答
投稿日時: 18/09/24 09:40:21
投稿者: WinArrow
投稿者のウェブサイトに移動

追加質問
 
Sheet2の商品コードがSheet1の表の検索キーになっていますが、
Sheet2の商品コードの入力ミスなど、アンマッチになる可能性はないのでしょうか?

回答
投稿日時: 18/09/24 11:15:15
投稿者: Mike

参考までに確認させてください。
Sheet1 のデータ範囲の最大右端列は(一か月分の) AJ列までとしてOKですか?
つまり、AK列以右を作業用に使用しても構わないと?

投稿日時: 18/09/24 20:33:01
投稿者: satoshi3

WinArrowさん
syさん
Mikeさん
 
ありがとうございます。
 
WinArrowさん
元々、Sheet1のA列&B列はなく、C列以降しかなかったのですが
商品の入れ替え(総数60ほどですが)とラインごとに整理したかったので
挿入しました。
Sheet2のA列&B列もなく、D列以降、掛け算がのみ入っている状態でした。
追加して、Vlookupか何かでSheet1から引っ張って作成をしようかと考えていました。
その場合、C列以降はVlookupで引っ張れますが、A列が引っ張れないですね。。。
 
アンマッチの場合、C列の品名のエラーでわかるようにしたいと思っていました。
 
syさん
列と日付の並びは同じではないです。
Sheet1は、F列から1日、2日の並びになり、
Sheet2は、D列から1日、2日の並びになります。
 
 
Mikeさん
AJ列まででOKです。
但し、AKに総B数=SUM(A3:AJ3)
   ALに総P数=D3*AK3
が入っております。
 
AM列以降は何も入っていません。
 
よろしくお願い致します。
 

投稿日時: 18/09/24 20:35:37
投稿者: satoshi3

WinArrowさん
 
A列とB列を入れ替えて、
A列を商品コードにした方が、無難ですね。
 

投稿日時: 18/09/24 20:38:03
投稿者: satoshi3

ちなみに、
Sheet2のC列の品名は、現状、リンク貼り付けだけでした。

回答
投稿日時: 18/09/24 21:16:54
投稿者: sy

引用:
syさん
列と日付の並びは同じではないです。
Sheet1は、F列から1日、2日の並びになり、
Sheet2は、D列から1日、2日の並びになります。
ちょっと誤解を与えてしまったかな。
私が言いたかったのは、開始列は別にD列やF列とずれていても構いません。
シート1にあって、シート2に無い日付や、その逆が無いかと言う事です。
 
例えば、
シート1はF列から、1,2,3,4,5,6,7, 8, 9,10
シート2はD列から、1,2,3,4,6,8,9,10,11,12
のような事が無いかと言う事です。
 
上記のような事が無く以下のように同じ日付同士なら、私の提示した式で出来る筈です。
シート1はF列から、1,2,3,4,5,6,7,8,9,10
シート2はD列から、1,2,3,4,5,6,7,8,9,10

回答
投稿日時: 18/09/24 23:40:38
投稿者: WinArrow
投稿者のウェブサイトに移動

satoshi3 さんの引用:
WinArrowさん
元々、Sheet1のA列&B列はなく、C列以降しかなかったのですが
商品の入れ替え(総数60ほどですが)とラインごとに整理したかったので
挿入しました。
Sheet2のA列&B列もなく、D列以降、掛け算がのみ入っている状態でした。
追加して、Vlookupか何かでSheet1から引っ張って作成をしようかと考えていました。
その場合、C列以降はVlookupで引っ張れますが、A列が引っ張れないですね。。。
 
アンマッチの場合、C列の品名のエラーでわかるようにしたいと思っていました。
 

 
話が見えないですが・・・
 
Sheet1もSheet2もA列,B列がない・・・て
最初の説明と違うのですか?
 
もともと、私は、Sheet2の存在が不思議なんです。
Sheet1の製造数の隣列に、計算しておけば、よいと考えていたんですが・・・
Sheet1でラインで並べ替えれば、対応できると思うのですが・・・
それではダメなんですか?
 

投稿日時: 18/09/25 00:42:52
投稿者: satoshi3

syさん
WinArrowさん
ありがとうございます。
 
syさん
理解不足で申し訳ないです。
その通りです。
syさんのご教示頂いた式で問題なく出来上がりました。
 
 
WinArrowさん
説明不足で申し訳ないです。
 
もともと、2つのSheetに分かれたフォーマットになっております。
少しだけ手を加えて計算しやすく、整理しやすくしたかったので。
 
1つのSheetでも出来上がるのは、わかります。
提出するので、あまりにも変更してしまうと後々問題になってきますので。
 
ご容赦ください。

回答
投稿日時: 18/09/25 08:16:28
投稿者: WinArrow
投稿者のウェブサイトに移動

引用:

1つのSheetでも出来上がるのは、わかります。
提出するので、あまりにも変更してしまうと後々問題になってきますので。
  
ご容赦ください。
 

許すのは、私ではない。
 
2つのシートに分けて管理することの方が後々問題になってきます。
 
Sheet2とSheet1の整合性を質問したら、
>アンマッチの場合、C列の品名のエラーでわかるようにしたいと思っていました。
という回答でした。
>Sheet2のC列の品名は、現状、リンク貼り付けだけでした。
アンマッチの対応とは矛盾するね?
リンク貼り付けするあら、品名だけではなくA列〜D列まで、リンク貼り付けする方がよいけど、
並べ替えはできませんね。
 
アンマッチの対応の件
Sheet2有、SHeet1無は、これで対処できますが、
逆に
Sheet1有、Sheet2無は、これでは対処できないですよね?
 
Sheet1の右側に計算式を入れておいて、
提出(印刷ってことかな?)するときには、前半の列を、バサーっと非表示にするだけで対応できるのではないでしょうか?
 
Vlookup関数は、配列を含む関数ですので、遅い、メモリ必要、ファイル容量も増える。
など(60件x30日x3か所)デメリットがあることも理解した方がよい。
 
 
 

回答
投稿日時: 18/09/25 08:59:17
投稿者: Mike

satoshi3 さんの引用:
AJ列まででOKです。
但し、AKに総B数=SUM(A3:AJ3)
   ALに総P数=D3*AK3
が入っております。
AM列以降は何も入っていません。
「総B数=SUM(A3:AJ3)」って間違ってませんか?チコちゃんに叱られないように!

回答
投稿日時: 18/09/25 09:04:22
投稿者: Mike

satoshi3 さんの引用:
AJ列まででOKです。
但し、AKに総B数=SUM(A3:AJ3)
   ALに総P数=D3*AK3
が入っております。
AM列以降は何も入っていません。
「総B数=SUM(A3:AJ3)」って間違ってませんか?
B数→製造数
A→F
では?
チコちゃんに叱られないように!

回答
投稿日時: 18/09/25 11:52:08
投稿者: Mike

Sheet1
  A   B    C    D  E  F  G  H  《   AN      AO    AP
1                 9/1 9/2 9/3 》                
2 ライン 商品コード 品名   B単位 単価 土 日 月 《 L&Cog    L&Casc    Ordr
3 P  660779 肉じゃが  35 451  2  2  2 》   80660779    806779  1
4 T  662855 牛丼    23 117  4  0   《   84662855    806972  6
5 T  662856 カレー   10 117  0  0   》   84662856    826960  4
6 R  662960 饂飩だし  50 135  5  5  2 《   82662960    826024  5
7 R  663024 そばだし  50 135  0  0  4 》   82663024    846855  2
8 P  662972 あんかけ  54 410  2  0  0 《   80662972    846856  3
9 大  662970 焼鳥たれ  53 650  1  1  1 》  16999662970 16999662970  7
10                       《 100000000000 100000000000  8
11                       》 100000000000 100000000000  8
12                       《 100000000000 100000000000  8
13                       》 100000000000 100000000000  8
14                       《 100000000000 100000000000  8
15                       》 100000000000 100000000000  8
 
Sheet2
  A    B   C    D   E   F  G  H
1             9/1  9/2  9/3 9/4 9/5
2 ライン 商品コード 品名    土  日  月 火  水
3 P   660779 肉じゃが 31570 31570 31570
4 P   662972 あんかけ 44280   0   0
5 R   662960 饂飩だし 33750 33750 13500
6 R   663024 そばだし   0   0 27000
7 T   662855 牛丼   10764   0
8 T   662856 カレー    0   0
9 大  662970 焼鳥たれ 34450 34450 34450
10
11
12
13
14
15
 
Sheet1!AN3: =IF(A3="",10^11,(CODE(A3)&B3)*1)
Sheet1!AO3: =SMALL(L_Corg,ROW(A1))
Sheet1!AP3: =MATCH(AO3,L_Corg,0)
Sheet2!A3: =IF(ROW(A1)>COUNTA(INDIRECT($A$2)),"",INDEX(INDIRECT(A$2),INDEX(Ordr,ROW(A1))))
このセルを右2列にオートフィル
Sheet2!D3: =IF(OFFSET(Sheet1!F$3,INDEX(Ordr,ROW(C1))-1,)="","",INDEX(B単位,INDEX(Ordr,ROW(C1)))*INDEX(単価,INDEX(Ordr,ROW(C1)))*OFFSET(Sheet1!F$3,INDEX(Ordr,ROW(C1))-1,))
このセルを右方へズズーッと(列AH まで)オートフィル
範囲 A3:AH3 を下方へズズーッとオートフィル

回答
投稿日時: 18/09/25 18:22:52
投稿者: Mike

書き忘れましたが、下記は定義した名前のリストです。
 

  名前一覧
名前   参照範囲
B単位  Sheet1!D3:D15
L&Casc  Sheet1!AD3:AD15
L&Corg  Sheet1!AN3:AN15
Ordr   Sheet1!AP3:AP15
ライン   Sheet1!A3:A15
商品コード Sheet1!B3:B15
単価   Sheet1!E3:E15
品名   Sheet1!C3:C15

投稿日時: 18/09/25 22:21:51
投稿者: satoshi3

WinArrowさん
その通りです。
やっと理解できました。。。
 
Mikeさん
Aではなく、Fです。B数は製造数です。
そして、わざわざありがとうございます。
こんなやり方もあるんですね。
 
皆様
ありがとうございました。