[[20210227012103]] 『すごく長い計算式をなんとかしたい』(もね) ページの最後に飛ぶ

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

 

『すごく長い計算式をなんとかしたい』(もね)

過去に同じような内容の質問をされている方がおられましたが、長さのレベルが違うような気がするので、あらたに質問をさせてください。

【やりたいこと】

 枚数により下記の要領で手数料を計算したい


【手数料】

 50枚以下--------------- 無料
 51〜200枚------------- 110円
 201〜1000枚---------- 100枚ごとに110円加算
 1001枚〜2000枚------ 1100円
 2001枚以上------------ 1000枚ごとに220円加算


 D1に枚数が入り、E1に手数料を表示させる

  D1   E1
  ↑   ↑

   (枚数) (式入力して手数料を表示)

 E1に、IF関数を使ってどんどん式を入力したら
 ものすごく長くなってしまった
 ↓
=IF(D1<=50,0,IF(AND(D1>=51,D1<=200),110,IF(AND(D1>=201,D1<=1000),IF(MOD(D1,100)=0,(ROUNDDOWN(D1/100,0)-2)*110+110,(ROUNDDOWN(D1/100,0)-1)*110+110),IF(AND(D1>=1001,D1<=2000),1100,IF(MOD(D1,1000)=0,(ROUNDDOWN(D1/1000,0)-2)*220+1100,(ROUNDDOWN(D1/1000,0)-1)*220+1100)))))

 これをなんとかするためには、
 少しはVBAがわかるのでユーザー関数にしたらいいのかなとも考えましたが、
 式レベルでなんとかなるものならと思っています。
 アドバイスを頂ければ助かります。
 よろしくお願いします。

< 使用 Excel:Excel2019、使用 OS:Windows10 >


 料金テーブルつくって、VlookUPとかで引いた方がいいですよ
(´・ω・`) 2021/02/27(土) 02:55

アドバイス有難うございます。

VlookUPも考えたんですが、100枚ごととか、1000枚ごととかに、加算という処理があるので、Vlookupだけでもスッキリとは対応できないのでまだやっていません。

どうにもいい案が浮かばなかったら、それをやってみます。

(もね) 2021/02/27(土) 03:13


=IF(D1>=1001,FLOOR(D1-1001,1000)*220+1100,
IF(D1>=201,FLOOR(D1-101,100)*110+110,
IF(D1>=51,110,0)))

あんまり短くなってないか。
(めざめるパワー) 2021/02/27(土) 05:15


 料金テーブルを1枚毎につくれば、エクセルの行数は1048576行あるので、
 0枚から1048575枚まで対応出来ます

 また、VLOOKUP関数の第4引数をTrueにすれば、近似値(検索値未満の最大値)を
 返すので、テーブルはずっと小さくできます。

 もっと法則性をちゃんと捉えれば計算式でも...と書こうと思ったら
 めざめるパワーさんの式がとても簡便なので、書くのやめます。

 2019では、IFS関数でもかけますね
(´・ω・`) 2021/02/27(土) 05:19

おはようございます。

めざめるパワーさん、ありがとうございます。

パッと見ただけで、短くなってるのがわかります。
rounddownがない分、見やすいです。
あとで実際入力してやってみます!
(今はiPhoneからなので)
(もね) 2021/02/27(土) 07:11


(´・ω・`)さん、おはようございます。

再度のアドバイス、ありがとうございます。
IFS関数で書くとどうなるかも考えてみます。

いろいろ試しておくと、今後にも役立つので!
(もね) 2021/02/27(土) 07:15


EVALUATE と VLOOKUP の組合せでいけそうに思います。

VLOOKUPの照合表(場所は仮です)

   H      I
4    0   0
5   51 110
6 201  ROUNDUP(A1/100,0)*110  ←ここの式が読み取れていません(変更してください)
7 1001 1100
8 2001  ROUNDUP(A1/1000,0)*220 ←ここの式が読み取れていません(変更してください)

(表がずれましたら、左から行番号、枚数、料金及び計算式です)

A1に枚数、B1に料金を計算するとします。

B1を選択して名前を定義します。(数式→名前の管理)
名前: FEE (任意です)
参照範囲: =EVALUATE(VLOOKUP(Sheet1!A1,Sheet1!$H$4:$I$8,2))

セルB1に戻り  =FEE

これでI列で定義した「計算式」で料金が算出されます。

(メジロ) 2021/02/27(土) 10:01


メジロさん

おはようございます。
ものすごくわかりやすい説明をありがとうございます!!

Evaluate 関数は使ったことがありませんでした。
Vlookupの検索用表もこういう作り方は初めてです。

やってみます!
(もね) 2021/02/27(土) 10:21


5時の投稿で倍率間違えてたので訂正。

 =IF(D1>=1001,FLOOR(D1-1001,1000)*0.22+1100,
IF(D1>=201,FLOOR(D1-101,100)*1.1+110,
IF(D1>=51,110,0)))
(めざめるパワー) 2021/02/27(土) 10:56

連投失礼。
正の数に限られているのでFLOORからINTに修正。

 =IF(D1<51,0,
IF(D1<201,110,
IF(D1<1001,INT((D1-101)/100)*110+110,
INT((D1-1001)/1000)*220+1100)))

お目汚し。
(めざめるパワー) 2021/02/27(土) 11:10


 こんなのも 参考までに。

 =CHOOSE(VLOOKUP(D1,{0,1;51,2;201,3;1001,4;2001,5},2,1),

    0,
  110,
 "100枚ごとに110円加算の式",
 1100,
 "1000枚ごとに220円加算の式")
(チオチモリン) 2021/02/27(土) 12:39

めざめるパワーさん

 頂いたアドバイスを使っていろいろやってみようと思い、PCを起動したところです。
 ありがとうございます!
 
(もね) 2021/02/27(土) 15:36


チオチモリンさん

 アドバイス有難うございます!

 こういう関数の組み合わせでもいけるんですね。
 ほんといろいろなアドバイスが頂けて感謝しきりです。

 やりたいことができるだけでなく、
 新しい知識やテクニックを得ることができてほんと嬉しいです。

(もね) 2021/02/27(土) 15:42


めざめるパワーさん

 条件式に = を入れないようにしただけでも目がチカチカしなくなりました。
 教えて頂いた式はスマートで、長くて見にくい印象が払しょくされています!
 これなら、式レベルで大丈夫な感じです。(^-^)
 感謝です!

 せっかくなので、他の方々から頂いたアドバイスのもやってみます!
 いろんなやり方を知ることができて嬉しいので。

(もね) 2021/02/27(土) 16:40


メジロさん

 ナビして頂いた通りにやってみたら、計算されました!
 こういう使い方は初めてなので、感動です。
 あらためて、有難うございます。

 検索用の表の計算式のところを自分なりにもう少し考えてみたいと思います。
 
(もね) 2021/02/27(土) 17:05


コメント返信:

[ 一覧(最新更新順) ]


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