エクセルの関数で振込手数料計算の仕方

質問

今、エクセルで支払明細書を作成しているのですが、銀行別・支店別での振込手数料を自動計算したいのですが、IF関数を上手く使いこなせなくてエラーが出てしまいます。

●「三菱東京UFJ」・「大阪営業部」だと手数料が0円
●「三菱東京UFJ」で金額が30,000円未満だと105円、30,000以上だと315円
●「三菱東京UFJ」以外で金額が30,000円未満だと525円、30,000以上だと735円

このような条件で計算式を作成することは可能ですか？
可能なら、計算式を教えてください。
宜しくお願いします。

ちなみに、今は下記のような計算式を使っています。
=IF(H1052<10000,0,VLOOKUP(IF(D1052="三菱東京UFJ",1,2),Sheet2!$A$2:$C$3,IF(H1052<30000,2,3),FALSE))

※別シートに手数料を記入しています。後、当方負担と先方負担も計算できるようにしています。
上記の式は先方負担の場合です。

回答No.3

KURUMITO · ベストアンサー

回答No1です。おそらくお望みの式は次のようにしたいとのことでしょう。
例えばお求めの表がシート1に有ってD列に銀行名が、H列に振込金額がそれぞれ入力されるとします。そこで例えば振込手数料をJ列に表示させるとします。すべてのデータは2行目から下方にあるとします。
振込の計算に当たってはシート2のA2セルから下方に銀行名が入力されており、B1セルには30000円未満、C1セルには30000円以上と項目名が有り、それぞれの手数料が2行目以降に入力されているとします。
なお、シート2のA列での銀行名についてはその他の銀行を一括でまとめることにして、「その他」という銀行名にしておくことにしてB列には５２５、C列には735と入力しておきます。
そこでシート1の振込手数料では例えばJ２セルには次の式を入力して下方にドラッグコピーすればよいでしょう。

=IF(H2="","",IF(COUNTIF(Sheet2!A:A,D2)=0,VLOOKUP("その他",Sheet2!A:C,IF(H2<30000,2,3),FALSE),VLOOKUP(D2,Sheet2!A:C,IF(H2<30000,2,3),FALSE)))

この式の意味はH列が空白ならば空白にしなさい、そうでない場合で銀行名がシート2のA列に載っていない場合にはシート2のA列で「その他」の名前の行を検索し、H列の値が30000未満の時はシート2のB列の値を、そうでない場合にはC列の値を表示しなさい、銀行名がシート2のA列に載っている場合にはシート2のA列でその銀行名の行を検索し、H列の値が30000未満の時はシート2のB列の値を、そうでない場合にはC列の値を表示しなさいという意味になります。
COUNTIF関数をVLOOKUP関数の前に置くことでシート2のA列に名前が載っていない場合の処理の仕方を示しています。この操作をしませんと、シート2のA列に銀行名が載っていない場合にはVLOOKUP関数がエラーとなってしまいます。

お礼

回答ありがとうございます。

一応、私が使用している項目はこうなっています。

D列　　　E列　　　F列　　　　　G列　　　　　H列　　　　　　I列
銀行名　支店名　請求額　　先方負担　　当社負担　　支払額

シート2
A列　　　B列　　　　　C列
分類　　3万未満　　3万以上
1　　　　　105　　　　　315
2　　　　　525　　　　　735

それで、KURUMITOさんの式を参考に、なるべく元から使用している式の内容を変えずに
=IF(H1172<10000,0,IF(AND(D1172="三菱東京UFJ",E1172="大阪営業部"),0,VLOOKUP(IF(D1172="三菱東京UFJ",1,2),Sheet2!$A$2:$C$3,IF(H1172<30000,2,3),FALSE)))

とういう式を入れたら、計算してくれたのですが・・・。
大丈夫でしょうか？
一応、成り立ってるとは思います。

回答No.2

soixante

ネスト（入れ子）が何重にもなるようなIF文ならば、可読性を高めるためにも、間違いを防ぐためにも作業列を使ったほうがよいような気もします。

私ならば、それぞれの項目で判定をかけてみます。

元データが分かりませんが、

D列が振り込まれてきた銀行名
E列が振り込まれてきた支店名
H列が振り込まれた来た金額
1行目がタイトル行、2行目以下がデータ

とされているならば、
I列で、三菱東京UFJ　か否かの判定
J列で、大阪営業部　か否かの判定
K列で、30000円以上か、未満かの判定
L列で、上記3条件のまとめ

としてみます。
具体的には、
I2セルに
=IF(D2="三菱東京UFJ",1,0)

J2セルに
=IF(E2="大阪営業部",1,0)

K2セルに
=IF(H2>=30000,1,0)

L2セルに
=I2*100+J2*10+K2

としてみます。

L列の判定は以下のようになります。

・三菱東京UFJ　かつ　大阪営業部の場合
110　・・・0
111　・・・0

・三菱東京UFJ　で　それ以外支店の場合
100　・・・105
101　・・・315

・それ以外銀行の場合
10　・・・　525
0　・・・　525

11　・・・735
1　・・・735

の6パターンです。

※10、11は、他行の場合で、たまたま「大阪営業部」という部店名があった場合ですが。

この6パターンを、Sheet2　にでも一覧表として、そこからVlookupで引いてきてはどうでしょう。

作業列を非表示にしておけば問題ないと思います。

お礼

回答ありがとうございます。

そういうやり方もあるんですね。
わかりやすくて、間違いもなさそうだし参考になります！

ありがとうございました。

回答No.1

KURUMITO

例えばD1セルから下方に銀行名、H1セルから下方に振込金額が有るとしたら振込手数料は次の式で表されますね。

=IF(H1="","",IF(D1="三菱東京UFJ・大阪営業部",0,IF(D1="三菱東京UFJ",IF(H1<30000,105,315),IF(H1<30000,525,735))))、