[[20200501005727]] 『数量別単価の適用方法』(ヤマト) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『数量別単価の適用方法』(ヤマト)

初心者です。
NO.1〜8までの商品のうち、No.1〜6のみ数量別単価があります。
しかし、同じアイテムを複数購入した時だけに適用される訳ではなく、No.1〜6の物を合わせて5個以上購入した場合でも、受注票に各アイテムの5個〜の単価が適用されるようにしたいです。
※5個以下の場合は、各アイテムの1〜4個の単価が適用
ご教授のほど、よろしくお願い致します。

[受注票]
例1 No.1+4= 計5個
NO.1 650000 AAA 2個 18,000円
NO.4 650003 DDD 3個 12,000円

例2 No.1+2+3=計4個
No.1 650000  AAA 1個  20,000円
No.2 650001  BBB  2個  18,000円
No.3 650002 CCC 1個 15,000円

[価格表]
No. 品目コード  品名   1〜4個   5個〜
1  650000   AAA    20,000  18,000
2  650001   BBB    18,000  12,000
3  650002   CCC    15,000  10,000
4  650003   DDD    14,800  12,000
5  650004   EEE    16,000  11,000
6  650005   FFF    17,000   10,000
7  750000   GGGG   50,000
8  750001   HHHH   55,000  

 

< 使用 Excel:Excel2013、使用 OS:Windows7 >


 No7と8はNo1〜6とは絶対組み合わないの?
 組み合うのであればどう処理するの?
(GobGob) 2020/05/01(金) 06:39

 数量のみ気にするとしてw。

 	A	B	C	D	E	F	G	H	I	J	K
1	No.	コード	品名	数量	金額		No.	コード	品名	1〜4	5〜
2	1	650000	AAA	2	18000		1	650000	AAA	20000	18000
3	4	650003	DDD	3	12000		2	650001	BBB	18000	12000
4							3	650002	CCC	15000	10000
5							4	650003	DDD	14800	12000
6							5	650004	EEE	16000	11000
7							6	650005	FFF	17000	10000
8							7	750000	GGGG	50000	50000
9							8	750001	HHHH	55000	55000
10											

 G:K列にリスト

 E2 =SUMIF(G:G,A2,IF(SUM(D:D)>4,K:K,J:J))
 下へコピー。
(GobGob) 2020/05/01(金) 07:43

 価格表シート					

 	A	B	C	D	E
 1	No.	品目	品名	1〜4個	5個〜
 2	1	650000	AAA	20,000	18,000
 3	2	650001	BBB	18,000	12,000
 4	3	650002	CCC	15,000	10,000
 5	4	650003	DDD	14,800	12,000
 6	5	650004	EEE	16,000	11,000
 7	6	650005	FFF	17,000	10,000
 8	7	750000	GGGG	50,000	
 9	8	750001	HHHH	55,000	

 受注票シート					

 	A	B	C	D	E
 1	No.	品目	品名	個数	価格
 2	1	650000	AAA	2	
 3	4	650003	DDD	3	
 4					
 5					

 受注票シートのE2セル =IF(A2="","",VLOOKUP(A2,価格表!A:E,IF(SUM(D:D)<5,4,5),FALSE))
 下方向にコピー
(bi) 2020/05/01(金) 08:06

回答ありがとうございます❗
No.7と8は1〜6と組み合わせて手配する時もありますが、No.7.8は別シリーズで数量別単価が無いため、ただ同時に手配をするだけで、割引条件には入りません。
月末の支払処理の際に、いくらで購入したのか一覧表時で見たいなと思いまして。(いつも各受注票を手動で計算してます…) 
回答頂いた数式で試してみます❗
自分では到達出来なかった複雑な数式で、ただただビックリです。
有難うございました。
(ヤマト) 2020/05/01(金) 21:02

検証するのに時間がかかるかもしれないと(超初心者なので)、取り急ぎ
お礼のみご連絡させて頂いたのですが、先程間違いないく数量別単価が反映する事
を確認出来ました!
正直、数式の理屈はよく理解していないのですが、諦めていた自動化が出来た事が
嬉しすぎて、結果をご報告させて頂こうと再度メールさせて頂きました。
本当にお忙しい中、有難うございました。
(ヤマト) 2020/05/01(金) 21:43

以前にご質問させて頂いた件です。追加で教えてください!
1ヶ月毎に受注をしめて受注票シートで支払金額を計算するのですが、
同じ月内で5本以上購入して数量別単価が適用された日と、5本以内
の購入で通常単価で購入する日が出てきました。
今のままだと、通常単価で購入した場合も1ヶ月以内に5本以上で
購入実績があると、数が加算され、全て数量別単価に計算されてしまう
事が分かりました。
同じ発注日毎に適用されるようにする計算式ってありますか…?
出来れば、価格表は別シートにあるパターンで教えて下さい。
自力ではお手上げです、すみませんが宜しくお願いします!

A B C D    E
発注日 品目  品名  個数   価格
5/20 650000 AAA  4  18000
5/20 650001 BBB 1 12000
5/27 650000 AAA  2  20000

(ヤマト) 2020/05/23(土) 20:30


 <受注票>

 E2セル =IF(A2="","",VLOOKUP(B2,価格表!B:E,IF(COUNTIF(価格表!B$2:B$7,B2)*SUMPRODUCT(SUMIFS(D:D,A:A,A2,B:B,価格表!B$2:B$7))<5,3,4),FALSE))

 下にコピー

 <結果図>
  行  ___A___  ___B___  __C__  __D__  __ E __
   1  発注日   品目     品名   個数   価格   
   2  5月20日  650000   AAA       4   18,000 
   3  5月20日  650003   DDD       1   12,000 
   4  5月27日  650000   AAA       2   20,000 
   5  5月20日  750001   HHHH      2   55,000 
   6  5月27日  750001   HHHH    100   55,000 

 <価格表 サンプル>
  行  _A_  ___B___  __C__  ___D___  __ E __
   1  No.  品目     品名   1〜4個   5個〜  
   2    1  650000   AAA    20,000   18,000 
   3    2  650001   BBB    18,000   12,000 
   4    3  650002   CCC    15,000   10,000 
   5    4  650003   DDD    14,800   12,000 
   6    5  650004   EEE    16,000   11,000 
   7    6  650005   FFF    17,000   10,000 
   8    7  750000   GGGG   50,000          
   9    8  750001   HHHH   55,000          

(半平太) 2020/05/23(土) 21:11


すごいです!
どうしてこんな複雑な計算式が分かるのか…尊敬します。
もう無理って思ってた悩みが解決しました。
お忙しい中お時間とって頂き、本当にありがとうございました!

(ヤマト) 2020/05/24(日) 10:07


半平太さん、先ほどは有難うございました!
もしお時間があればでいいのですが、教えて頂いた計算式の解説をお願い
出来ないでしょうか?
恥ずかしながら、計算式の理屈を理解しておらず、せっかく教えて頂いた
事を数式だけ使用するのではなく、やっぱりきちんと理解して身に付けたい
のです…!

計算式の値が、受注票の値を示してるのか価格表の値を示してるのかさえ
よく分かっていない状態です…勉強不足ですみません。
もちろん数式を教えて頂いただけでも本当にに有難い事なので、
お忙しい場合はご返信頂けなくてもかまいません。
その場合は地道に理解につとめていきます!
すみませんが、よろしくお願いいたします。

(ヤマト) 2020/05/24(日) 16:51


 一部を関数の引数用語に換えると、たったこれだけですよね。
                    ↓
  =IF(A2="","",VLOOKUP(B2,価格表!B:E,列番号,FALSE))

 ポイントは正しい「列番号」をどう取得するかに掛かっています。

 列番号を決めている部分             
 IF(COUNTIF(価格表!B$2:B$7,B2)*SUMPRODUCT(SUMIFS(D:D,A:A,A2,B:B,価格表!B$2:B$7)) <5 ,3 ,4)
  ~~~~~~~~~~~~↑~~~~~~~~~~~~↑~~~~~~~~~~ ~~~~~~~~~~~~~~↑~~~~~~~~~~~~~~~~~~~~~~ ~~ ~~ ~~ 
        (1)     (2)                         (3)

 (1)当該品目番号が「価格表の割増割引商品」に有るか(有れば1となるが、無ければ0となりどう転んでも (1)*(3)=0で、5未満で列番は3が採用される
 (2)上記(1)が「有=1」の場合は下記(3)で決まる
 (3)受注票の日付列と当該日付が同じ中で「価格表の割増割引商品」該当の売上個数が幾つあるか求める(5未満なら列番は3、そうでなければ4が採用される
  ※ SUMIFSで価格表B$2:B$7のひと品目ずつ個数を算出し(結果は配列になる)、それをSUMPRODUCTで合算する。

(半平太) 2020/05/24(日) 17:46


半平太さん、お忙しい中ご回答頂き有難うございました!
自分の理解不足で頭の中がもやもやしてたのが、解説して頂いた事で
本当にスッキリして、もっとExcelの事勉強したくなりました。

解説までしてほしいとか図々しいお願いをして申し訳ありませんでした。
初心者にも分かりやすくご説明頂いて嬉しかったです。
本当に有難うございました!

(ヤマト) 2020/05/24(日) 18:49


コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.