エクセルでの数式設定について

質問

エクセルを活用して以下の計算をしたいと考えております。
「患者ごとに必要な薬剤投与量を最も経済的に処方するバイアル本数の組み合わせの計算」

ある薬（Ｙ）について「３ｍＬ×体重（ｋｇ）」で算出した量を患者さんに処方するとします。
ｅｘ１）体重２０．５ｋｇの人には３×２０．５＝６１．５ｍＬの処方
ｅｘ２）体重５５．８ｋｇの人には３×５５．８＝１６７．４ｍＬの処方

その薬の元になるバイアルサイズが２５ｍＬ（２２円）と５０ｍＬ（４０円）であったとします。
ｅｘ１）では２５ｍＬ１本と５０ｍＬ１本を使用すると６６円となり５０ｍＬを２本使用するよりも経済的になります。
ｅｘ２）では２５ｍＬ１本と５０ｍＬ３本を使用すると最も経済的になります。

この計算をエクセルに“体重”を打ち込めば薬剤Ｙを最も経済的に処方する組み合わせを算出してくれる計算式をご教示ください。

なお小生エクセル２００７を利用しております。

回答No.4

tom04 · ベストアンサー

No.3です！
前回の質問は解決したみたいで、良かったですね。

ところで新たな問題ですが、
規則性がみつからないので↓のように一番効果的な場合の表を作ってみました。
（合っているかどうかは自身がありませんが・・・）

それを元に必要量を入力すれば40mlと100mlの必要本数を表示できるように考えてみました。
参考にしてみてください。

表のJ2=D1 J3=F1
K2セル　=INDEX($D$3:$F$20,MATCH($K$1,$C$3:$C$20,-1),MATCH(J2,$D$1:$F$1,0))
として、下へオートフィルでコピーしています。

尚、必要量が400mlを超えた場合はデータを作っていないため、エラーになります。

今一度、一番効率的な組み合わせを確認していただいた方がいいかもしれません。

以上、参考になれば幸いですが、他に良い方法があるかもしれませんので
的外れの回答なら無視してくださいね。m(__)m

お礼

tom04さま
ありがとうございます。
ご教示いただきました「一番効果的な場合の表」を作っておくという発想に感謝です！！問題が解決できました！
今後もよろしくお願いします。

回答No.6

mu2011

NO1です。
>バイアルサイズが４０ｍＬ（８１円）と１００ｍＬ（１８１円）の薬がでてきました・・・。
⇒患者側からは、４０・１００ｍｌは使用しないでほしいと思いますが如何でしょうか。
　仮に１００ｍｌが必要になった場合、１００ｍｌ（１８１円）を処方されるより、５０ｍｌＸ２（８０円）になります。
　予めｍｌ計算値のテーブル（表）を作成して確定する方法（NO4様）が変更も簡単ですからそちらをお勧めします。

お礼

mu2011さま
ありがとうございます。
アドバイスいただきました「一番効果的な場合の表」を作っておくという発想を採用させていただき問題が解決できました！
今後もよろしくお願いします。

回答No.5

takesun

確認ですがご質問はバイアルサイズが100、50、40、25mlの4種類
での話ですよね。
>これだと１１５ｍＬ使用の場合４０ｍＬ３本を使用するともっとも
>経済的になりますよね？
>（４０ｍＬ１本と１００ｍＬ１本よりも・・・）
そうだとしますと100ml１本と25ml１本が一番経済的かと。

40mlサイズは80円であっていますでしょうか。
これだと単価が高く、50mlを利用するほうが金額的には経済的です。
(投与量としては40mlより余分がでますので無駄ですけど)

ご質問がバイアルサイズが100、50、40、25mlの4種類での話だとして
単価的に利用価値があるかはおいておくとして、
40mlが曲者で計算が複雑になってしまいます。
(他は100÷2=50、50÷2=25と小さいバイアルサイズで割り切れる)
そうなると40mlを基準にした場合と25mlを基準にした場合の
2通りを計算して経済的な方を選択するというふうにしては
どうでしょうか(他に思いつきませんでした)。

添付では上の行で最小25mlを基準にした場合のバイアルサイズの個数
下の行では最小40mlを基準にした場合のバイアルサイズの個数を
いったん求めています。
そこから金額を比較して、安いほうを採用するようにしています。

C3=CEILING(B3,25)　25mlを基準にしたときの必要量 C4=CEILING(B3,40)　40mlを基準にしたときの必要量
D3=INT(C3/D$2)下へコピー
E3=INT(($C3-SUMPRODUCT($C$2:D$2,$C3:D3))/E$2)下と右へF列までコピー H3=SUMPRODUCT(D$1:G$1,D3:G3)下へコピー
H7=MIN(H3:H4)
D7=INDEX(D3:D4,MATCH($H7,$H3:$H4,0))　右へG列までコピー

40mlの方(4行目のケース)は単価の問題で採用される場合が
あるのかわかりませんが、ご参考までに。

お礼

takesunさま
ありがとうございます。
>バイアルサイズが100、50、40、25mlの4種類での話ですよね。
すいません・・・１００／４０と５０／２５は違う薬の組み合わせでした。説明が下手でご迷惑をおかけしました。
しかし「SUMPRODUCT」関数を活用したら表をすっきりとさせれそうですね！勉強してみます。
今後もよろしくお願いします。

回答No.3

tom04

こんばんは！
結局25mlを超えると50mlを使用した方が効率的という事だと思います。
その考え方で考えてみました。

色々方法はあると思いますが、
一例として　↓の画像の

C2セル　=B2*3
D2セル　=IF(MOD(C2,50)<=25,INT(C2/50),INT(C2/50)+1)
E2セル　=IF(C2-(50*D2)<0,0,1)
と数式を入れて、C2～E2セルを範囲指定した後にオートフィルでコピーしています。

尚、エラー処理は行っていません。
以上、当方使用のExcel2003での回答ですが
参考になれば幸いです。m(__)m

お礼

tom04さま
早速のご教示ありがとうございます。
２５ｍＬと５０ｍＬのケースはきちんと計算できそうです！！

現在採用医薬品のリストｕｐをし計算式を作っていたのですが・・新たな問題が発生しました。
バイアルサイズが４０ｍＬ（８１円）と１００ｍＬ（１８１円）の薬がでてきました・・・。これだと１１５ｍＬ使用の場合４０ｍＬ３本を使用するともっとも経済的になりますよね？（４０ｍＬ１本と１００ｍＬ１本よりも・・・）
この問題に対処するにはどうすればいいでしょうか？ご教示ください。

回答No.2

fujillin

2003ですが、多分同じだと思いますので。

すべての薬剤が同じ条件とは思えませんが、ご質問文の内容に限って考えれば…
できる限り50mLのサイズを利用した方が安くなるので、全投与量/50の余りが25以下の場合だけ25mLを1本使用するということになるでしょう。

境界条件を考えると、少し複雑になってしまいましたが、もう少しスマートな表記方法もあるかも知れません。
A、B・・列に順に、体重、必要投与量、50mLの本数、25mLの本数、金額と並んで出るとして、3行目から下に入力するとするなら…（1、2行はタイトル）
A3に　=3*A3
B3に　=ROUNDUP((B3-D3*25)/50,0)
C3に　=IF(OR(MOD(B3,50) > 25,MOD(B3,50)=0)
D3に　=C3*40+D3*22
として、下方にコピーすれば一応求められます。

このままだと、A列に入力されていないと残りのセルは0などが表示されてしまうので、IF(A3<>"",3*A3,"")のような式にすることで、A列の入力に合わせて結果が表示されるようにすることができます。

添付の図は、その場合の例です。

お礼

fujillinさま
早速のご教示ありがとうございます。
２５ｍＬと５０ｍＬのケースはきちんと計算できそうです！！

現在採用医薬品のリストｕｐをし計算式を作っていたのですが・・新たな問題が発生しました。
バイアルサイズが４０ｍＬ（８１円）と１００ｍＬ（１８１円）の薬がでてきました・・・。これだと１１５ｍＬ使用の場合４０ｍＬ３本を使用するともっとも経済的になりますよね？（４０ｍＬ１本と１００ｍＬ１本よりも・・・）
この問題に対処するにはどうすればいいでしょうか？ご教示ください。

回答No.1

mu2011

一例です。
A1にｍｌ算出値とします。
５０ｍｌは、=INT(FLOOR(A1+24,50)/50)
２５ｍｌは、=(A1>=FLOOR(A1+24,50))*ROUNDUP((A1-FLOOR(A1+24,50))/25,0)

お礼

mu2011さま
早速のご教示ありがとうございます。
２５ｍＬと５０ｍＬのケースはきちんと計算できそうです！！

現在採用医薬品のリストｕｐをし計算式を作っていたのですが・・新たな問題が発生しました。
バイアルサイズが４０ｍＬ（８１円）と１００ｍＬ（１８１円）の薬がでてきました・・・。これだと１１５ｍＬ使用の場合４０ｍＬ３本を使用するともっとも経済的になりますよね？（４０ｍＬ１本と１００ｍＬ１本よりも・・・）
この問題に対処するにはどうすればいいでしょうか？ご教示ください。