【Excel】半分の量の場合掛け率を変える
量によって掛率が変わることって滅多にないとは思いますが、私の仕事にはあったので紹介します。
今回は半分の場合にのみ対応しています。
細かい量には対応できません。
とりあえずSheet1は単価と掛け率を表にしておく
Sheet1
種類が紙になっているのは他にいい例が思いつかなかっただけです。
勿論実際の紙がこんな感じで売られているわけではありません。
（かといって本当の商品は特殊なので伏せます）
Sheet2
Sheet2に実際の入力データを入れる。
【例１】
C2=IF(ISERROR(VLOOKUP(A2,Sheet1!$A$2:$C$6,3,FALSE)),"",
VLOOKUP(A2,Sheet1!$A$2:$C$6,3,FALSE))
これは普通のVLOOKUPにISERROR関数を組み合わせてエラーを表示させないようにしたものです。
エラーを表示してもいいのなら
=VLOOKUP(A2,Sheet1!$A$2:$C$6,3,FALSE)
でもOK。
問題はここからです。
【例２】
D2=IF(B2="","",C2*(INT(B2/1)+IF(MOD(B2,1)>0,VLOOKUP(A2,Sheet1!$A$2:$D$6,4,0))))
IF(B2="","",○○）まずこの単純なIF関数でB2が空白なら空白という入力をし、○○の部分に空白で無い場合の条件を入れていきます。
同じくエラー表示してもいいなら
=C2*(INT(B2/1)+IF(MOD(B2,1)>0,VLOOKUP(A2,Sheet1!$A$2:$D$6,4,0)))
でもいいです。#VALUE!と表示されるだけです。
で、○○の中を分解して解説します。
=C2*(INT(B2/1)+IF(MOD(B2,1)>0,VLOOKUP(A2,Sheet1!$A$2:$D$6,4,0)))
まずこのINT(B2/1)
INT関数はINT(数値)で数値の小数点を切り捨てる関数。
INT(B2/1)はB2を１で割って、余りを切り捨てるという意味。
Sheet2の２行目でいえば、まずB2を１で割ると１、そこにC2を掛けるので、
１×２００＝２００が出ます。
次にIF(MOD(B2,1)>0,VLOOKUP(A2,Sheet1!$A$2:$D$6,4,0))
の(MOD(B2,1)の部分について。
MOD関数は割り算の余りを求める関数です。
先ほどB2の整数だけ抽出したので、今度は0.5の部分だけ抽出して、
Sheet1のD2の数値を当てはめます。
MOD関数でB2の小数点以下の数値が0より大きかった場合、
VLOOKUPでA2の値に当てはまるSheet1のD列の掛け率を示します。
すると最初に=C2*があるので、
Sheet2のD2=C2*(200+140)
という意味になるわけですね。
自分で書いててめちゃくちゃ分かりづらいなと思いました。
すみません。
これMOD(B2,1)>0が0以上だったらという意味でしかないので、
例えば数量を1.3にしようが何にしようがSheet1で記述した掛け率にしかならないので、細かい量には対応できないんですよね。
殆ど使い道はないかと思いますが折角書いてみたので。
参考記事
121ware.com > お楽しみ > ちょっとまめ知 > 簡単 Excel 関数 ! 割り算の余りもわかる
