EXCELの関数を使って表示出来るかどうかの相談です

質問

EXCELの関数を使って表示出来るかどうかの相談です
自分でも考えてみたんですがどうしてもわかりません
詳しい方がいましたら教えていただけますでしょうか
例題
りんご、いちご、ばなな、メロンの４つがそれぞれ
1単位200円、200円、300円、400円と有ります。
2点セット以上でそれぞれ割引がつきます
2点セットの場合は－50円、3点セットの場合は-100円、4点セットの場合は－150円に
なります。メロンが含まれている場合は更に－50円お得になります。
（4点セットにした場合は実質メロンが含まれているため200円の割引になります）
例えばりんごといちごのセットを2セットの場合は(400-50)+（400-50）=700円になります
ばななとメロンのセットを1セットの場合は700-(50+50)=600円になります
りんご2、いちご2、ばなな2、メロン1の場合は
4点セット(200+200+300+400-200)と3点セット(200+200+300-100)で合計1500円になります
関数、もしくは表などをうまく使って
割引額と合計金額を表示させる良い方法はありますでしょうか

回答No.3

KURUMITO · ベストアンサー

次のようにしてはどうでしょう。
Ｈ１セルから下方にりんご、いちご、ばなな、メロンと入力してそれぞれの単価をI１せるからI４セルに入力します。
Ａ１セルにはりんご、Ｂ１セルにはいちご、Ｃ１セルにはばなな、Ｄ１セルにはメロンと入力します。
それぞれの下の行にはそれぞれの個数を入力します。
Ｅ列は単価と数を掛けた値で次の式をＥ２セルに入力して下方にオートフィルドラッグします。

=A2*I$1+B2*I$2+C2*I$3+D2*I$4

割引額はＦ列に表示させるとしてＦ２セルには次の式を入力し下方にオートフィルドラッグします。

=IF(COUNTIF(A2:D2,">="&SMALL(A2:D2,1))=4,SMALL(A2:D2,1)*(150+IF(D2>=SMALL(A2:D2,1),50,0)),0)+IF(COUNTIF(A2:D2,">="&SMALL(A2:D2,1))=3,SMALL(A2:D2,1)*(100+IF(D2>=SMALL(A2:D2,1),50,0)),0)+IF(COUNTIF(A2:D2,">="&SMALL(A2:D2,1))=2,SMALL(A2:D2,1)*(50+IF(D2>=SMALL(A2:D2,1),50,0)),0)+IF(COUNTIF(A2:D2,">="&SMALL(A2:D2,1))=1,0,0)+IF(COUNT(A2:D2)<2,0,IF(AND(SMALL(A2:D2,1)<>SMALL(A2:D2,2),COUNTIF(A2:D2,">="&SMALL(A2:D2,2))=3),(SMALL(A2:D2,2)-SMALL(A2:D2,1))*(100+IF(D2>=SMALL(A2:D2,2),50,0)),0))+IF(COUNT(A2:D2)<2,0, IF(AND(SMALL(A2:D2,1)<>SMALL(A2:D2,2),COUNTIF(A2:D2,">="&SMALL(A2:D2,2))=2),(SMALL(A2:D2,2)-SMALL(A2:D2,1))*(50+IF(D2>=SMALL(A2:D2,2),50,0)),0))+ IF(COUNT(A2:D2)<3,0,IF(AND(SMALL(A2:D2,2)<>SMALL(A2:D2,3),COUNTIF(A2:D2,">="&SMALL(A2:D2,3))=2),(SMALL(A2:D2,3)-SMALL(A2:D2,2))*(50+IF(D2>=SMALL(A2:D2,3),50,0)),0))

合計金額はＧ列でＧ２セルには次の式を入力して下方にオートフィルドラッグします。

=E2-F2

お礼

返答ありがとうございます
すごく詳しいですね。
使った事のない関数もあるので
ちょっと勉強してみます

回答No.8

lll49erlll

面白そうなので 参加させてください＾＾（失礼＾＾；）

私は、出来るだけ作業セルなどを使って、簡便な式で見通しが効くように考える派
ですので 下記の画像のように考えてみました。

注意するところは、セット引きの欄ですが、
　質問内容にあるように ７セットの場合は、１セットでは、割引がないので次の
２セット目から考えて６セット×50円=300円ではなくて
３セットの100円と４セットの200円の合計 250円と考えるのですよね。
それから言うと ５セットは ２セットの50円と3セットの100円、、、
　8セットは考えようじゃ２＋２＋２＋２や３＋３＋２や　４＋４とか考えられ、
　それぞれ割引額が異なります
９セットは３＋３＋３や、２＋２＋２＋３なども考えられますが、ここでは一番
分割数が少ない割り方にするとします（８セットは４＋４、９は３＋３＋３）

その式が=((F4-1)-INT((F4-1)/4))*$H$3　で単純に ２セット目から数えた数より
４セットを超えるごとに１セット分減った割引額になることを補正してます。
　他の式は単純な 足し算、かけ算です。

お礼

ありがとうございます
こ、これはすごく分かりやすいですね！！

ただ説明不足な点があってすいません・・・
同じ商品についてはセットが適用しないという事を
追記で残しとけば良かったです・・・
りんごを二つだけ購入した場合は
割引が適用されず200+200=400
りんご二つとメロン一つを買った場合は
((200+400)-100)+400=900
（りんごとメロンの2点セット）+メロン料金になります。

入れ違いで回答を打ち切ってしまったもので
その点も詳しく教えて頂きたかったのですが・・・
提示して頂いた表を元に自分で考えてみて
又分からない事がありましたら
改めて質問させて頂きます

回答No.7

hananoppo

A3～D3セルにりんご、いちご、ばなな、メロンの単位数を入力するものとします。
また、E3セルに割引前金額を、F3～L3セルに1セット目、2セット目、3セット目、4セット目、5セット目、メロンセット、計の割引額を、M3セルに合計金額を表示させるものとします。
E3～M3セルには次の数式を入力します。

E3 =A3*200+B3*200+C3*300+D3*400
F3 =CHOOSE(COUNTIF(A3:D3,">=1")+1,0,0,50,100,150)
G3 =CHOOSE(COUNTIF(A3:D3,">=2")+1,0,0,50,100,150)
H3 =CHOOSE(COUNTIF(A3:D3,">=3")+1,0,0,50,100,150)
I3 =CHOOSE(COUNTIF(A3:D3,">=4")+1,0,0,50,100,150)
J3 =CHOOSE(COUNTIF(A3:D3,">=5")+1,0,0,50,100,150)
K3 =MIN(MAX(A3:C3),VALUE(D3))*50
L3 =SUM(F3:K3)
M3 =E3-L3

なお、F3～L3セルにCHOOSE関数を使っていますが、LOOKUP関数やINDEX関数でもよいです。
また、5セットまで対応させていますが、6セット以上が考えられる場合は6セット目以降を追加してください。

ただ、この計算には一つ問題があります。それはメロンの単位数が他の果物より多い場合です。
例えば、りんご、いちご、メロンの単位数が2、2、4の場合を考えてみます。
この計算では、りんご、いちご、メロンのセットが2組できると考えて、割引額は(100円＋50円)×2＝300円になります。
しかし、りんご、メロンのセットが2組、いちご、メロンのセットが2組できると考えると、割引額は(50円＋50円)×4＝400円になり、最初の計算より多くなります。

お礼

ありがとうございます
参考にさせて頂きます

回答No.6

MackyNo1

画像が添付できていなかったので再度アップしてみます。

回答No.5

MackyNo1

添付画像のような表を作成して、それぞれの数値をB3:E3セルに入力すれば自動的に集計することができます。

B4セル（E6までオートフィル）
=MAX(B3-SMALL($B3:$E3,ROW(A1)),0)

B9セル（B12までオートフィル）
=MAX(B3:E3)-MAX(B4:E4)

C9セル
=SUMPRODUCT((B3:E3>0)*$B$2:$E$2)*B9

D9セル
=150*B9+MIN(B3:E3)*50

以上で基本的な数値が取得できますので、後は適宜SUM関数などで集計する式を入力してください。

必要に応じて4行目から6行目までを、非表示にするなどの対応してください。

お礼

図解も提示して頂きありがとうございました
色々と参考にさせていただきます

回答No.4

tom04

こんにちは！

○セットということは複数品目を購入して初めて成立する！ということですよね？
仮にある一品目だけをたくさん購入しても「割引」はない！という解釈での方法です。

一つの案として・・・

↓の画像のように各品目の価格表と割引額表を作ってみました。

F2～G5セルは品名と価格を入力
H2セルに
=IF(SUMPRODUCT(($A$2:$A$8=$F2)*($B$2:$B$8))>=COLUMN(A1),1,"")
という数式をいれ、「ばなな」のH4までオートフィルでコピー
「メロン」は別格なので、H5セルに
=IF(SUMPRODUCT(($A$2:$A$8=$F5)*($B$2:$B$8))>=COLUMN(A4),2,"")

H6セルに
=IF(COUNT(H2:H5)<2,"",(SUM(H2:H5)-1)*50)
という数式を入れています。

H2～H6セルを範囲指定し、H6セルのフィルハンドルで右へオートフィルでコピー！

そして、「単価」のC2セルに
=IF(COUNTBLANK(A2:B2),"",VLOOKUP(A2,$F$2:$G$5,2,0))
「小計」のD2セルに
=IF(COUNTBLANK(A2:C2),"",B2*C2)
として、C2・D2セルを範囲指定し、D2セルのフィルハンドルで8行目までコピー！

D9セルは単純に
=SUM(H6:L6)
としています。

最後にD10には
=SUM(D2:D8)-D9

これで画像のような感じになります。
以上、長々と書きましたが
参考にならなかったらごめんなさいね。m(__)m

お礼

ありがとうございました
あんまり関数は詳しくないですが
色々な方法を試してみます

回答No.2

layy

「りんご、りんご、いちご、いちご、ばなな、ばなな、メロン」
の場合
「りんご、いちご、ばなな、メロン」
と
「りんご、いちご、ばなな」
のセットで考えていますので、
「りんご、りんご、いちご」
のセットはないということでしょうか？。

「りんご、いちご、ばなな、メロン」
で考えられるケースは数通りですから、
それを一覧にしてやれば、割引額、合計金額も明確になると思います。

あとは
今回分がその一覧のどれに該当するか関数で見つけて金額を表示
そんな仕掛けかと思います。

イメージでは
「商品」「代金」「割引」「合計」
「りんご」「２００」「０」「２００」
「りんご、いちご」「４００」「－５０」「３５０」
「りんご、いちご、ばなな」「７００」「－１００」「６００」
「りんご、いちご、ばなな、メロン」「１１００」「－２００」「９００」
・・・・

お礼

返答ありがとうございます
自分で色々と考えてみます

回答No.1

hoiho1010

できますが文字でわかるかな、、

Vlookup関数とIf関数、更にCOUNTA関数、SumIF関数を使います

合計金額を表示するセルと割引をするセルは分けます
合計金額表示のセルはSUM（合計範囲）-割引セルとします
割引セルには入力したデータの数をCOUNTA関数で数え、
IF関数で2点セットの場合、3点セットの場合と条件分岐します
更に　SumIF関数でメロンがあるか調べこれによって-50をするかしないか判断します

これで出来ますけど・・。

お礼

ありがとうございます。
多くの方に返答して頂けましたので
色々と試してみます