エクセルで振込み手数料を自動的に引いた金額を表示してくれる関数を教えて下さい。

質問

仕事でわからなくて困っております。
毎月、業者から届いている請求書の請求金額を一覧表にして上司に提出しております。
今までは金額そのままの記載すれば良かったのですが　
今度から請求金額から振り込み手数料を引いた金額を振り込むことになったそうで、
今までの請求金額そのままの表と、振込み手数料を引いた金額もあわせて一覧表にするようにと言われました。

振り込み金額は相手先の銀行が同じ銀行か他行であるかと、振込み金額が3万円以上か3万円未満かで違ってしまいます。
銀行が同行の場合（仮にAタイプとして）　3万円未満では105円
　　　　　　　　　　　　　　　　　　　 3万円以上では315円
銀行が他行で　(仮にBタイプとして）3万円未満では420円
　　　　　　　　　　　　　　　　　3万円以上では630円

この条件で自動的に振込手数料を引いた金額が出せる関数、あるいは方法をご存知の方　ぜひ教えて下さい。

まず考えたのは、業者さんとその振込口座の表を作れば、VLOOKUPを使ってその業者さんの名前を入力すると自動的にAかBか表示できるかなと思って
それでもしそこがAだったら　そして金額が3万円未満だったら105円になるという数字が出せれば　請求金額のセルからその105のセルをマイナスするというので作れるのかなと一人で考えておりましたが
Aだったら　そして金額が3万円未満だったらという関数がわからなです。　
そしてこの考え方でよいのかも　わかりません。

どうぞよろしくお願いいたします。

回答No.4

soixante · ベストアンサー

＞Aだったら　そして金額が3万円未満だったらという関数がわからなです。

そもそものこのご質問に回答しますと、
条件１かつ条件２を満たす場合のＩＦ関数
IF(AND(条件１、条件２）,両条件を満たす時の処理,それ以外の処理）

あなたの考えた方法でもいいと思いますよ。自分で理解しながら表を作成したら後からのメンテも楽です。
振り込まれる口座（あなた側）を「あいう銀行」とします。

Ａ列；相手方の銀行名
Ｂ列；金額
Ｃ列：振込手数料

Ｃ２セル：
=IF(AND(A2="あいう銀行",B2<30000),105,IF(AND(A2="あいう銀行",B2>=30000),315,IF(AND(A2<>"あいう銀行",B2<30000),420,630)))

ちなみに：
IF関数のネスト（入れ子）というやつです。
テストの点数が80点以上なら合格、50点以上なら追試、49点以下なら落第
として
IF(A1>=80,"合格",IF(A1>=50,"追試","落第"))

まず一つ目の条件（80点以上か）を判定し、満たしてれば「合格」。
満たしてない場合に、次のＩＦ文の判定に行きます。
50点以上なら「追試」
その条件も満たしてなければ最後の「落第」になります。

上に示したあなたのケースでは、
同行かつ３万円未満なら、105円。それを満たさない場合、
同行かつ３万円以上なら、315円。それを満たさない場合、
他行かつ３万円未満なら、420円。それを満たさない場合、
630円。

という流れになっています。
ご参考まで。

お礼

IF関数　こんなにいろいろ入れて平気なんですね？
私は単純なIF関数しか使っていなかったので驚きです。
これは引数のダイアログボックスに入力するときどうやってやるのですか？
そんなことしないで数式バーにこうやっていれちゃえばよいのでしょうか？
でもそれだと理解できるのかなー
ただこの方法ならやってみる前に少しわかりますので挑戦しやすそう。
ありがとうございました。

回答No.9

keithin

回答のNo.5でお話しした式は，1.05の事は全く気にしないで書いた式を鵜呑みにしてそのまま使ってくださればいいですよ。

＞うーん　1.05って何ですか？

え？　5%は消費税ですよ？

同行宛3万円未満では，手数料100円です。５％の消費税で105円なので合わせて1.05です。
315円は300円と15円，420円は400円と20円，630円は600円と30円です。

100円，300円，400円，600円と切りの良い手数料で見た方が計算の仕組みを理解しやすいかと最初は思いましたが，1.05倍がかえって判らなくしている様子でしたので，No.5でお話しした式では1.05を使うのをやめて105円,315円，420円，630円のそのままの数字を計算しています。

計算の考え方もNo.5で図解して説明してみましたので，最初の1.05で立ち止まってしまわず，もう一度目を通してみてください。

お礼

なるほど　そうなんですね
理解力が足らなくて何度も説明していただいて申し訳なかったです。
でも今回でよおくわかりました。

今回教えていただいて本当にいろいろな方法があるのがわかってとても勉強になりました。
おかげさまで手数料が自動で出てくる関数の入った表を作ることが出来きました！
感謝しております。
ありがとうございました。

回答No.8

tom04

No．7です！
たびたびごめんなさい。

前回アップした画像の表内で配置が違っていました。
K4とL3セルの数値を入れ替えてください。

どうも何度も失礼しました。m(__)m

お礼

わざわざ修正のお知らせまでありがとうございました。

この表　とても参考になりました。
いろいろな方法を教えていただいて感謝しています。
それぞれ試したら私の場合1日かかってしまいそう（笑）
でもなんとか挑戦して作ってみます。
がんばります。

回答No.7

tom04

こんにちは！
すでに良い回答がたくさん出ていますので、参考程度で目を通してみてください。

勝手に↓の画像のように表を作ってみました。
（アップした画像が少し小さくて見づらいかもしれません）

E2セルに
=IF(D2="","",IF(C2=$K$1,VLOOKUP(B2,$I$3:$L$4,3,1),VLOOKUP(B2,$I$3:$L$4,4,1)))
F2セルに
=IF(D2="","",D2-E2)
G2セルに
=IF(B2="","",B2-D2)
という数式を入れ、E2～G2セルを範囲指定し、
G2セルのフィルハンドルで下へコピーすると画像のような感じになります。

以上、参考になれば幸いです。m(__)m

回答No.6

KURUMITO

あなたの考え方に全く同感です。そのような内容で作成するとしたら全体像は次のようにするとよいでしょう。
シート2はデータベースの表として、例えばＡ1セルには会社名、Ｂ1セルには振込先銀行名、Ｃ1セルには口座番号、Ｄ1セルには振込手数料分類とでも項目名を入力しそれぞれのデータが2行目から下方にあるとします。
Ｄ列にはＡおよびＢのどちらかが入力されるとします。
そこで本題のシート1ですがＡ1セルに会社名、Ｂ1セルに振込額、Ｃ1セルに振込手数料、Ｄ1セルに送金額とでも入力します。なお、必要であれば銀行名や口座番号を表示する列を作っても良いでしょう。
Ａ列の2行目から下方に会社名を入力するとして、Ｂ列では振込金額を入力します。Ｃ2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(B2="","",IF(VLOOKUP($A2,Sheet2!$A:$D,4,0)="A",IF($B2>=30000,315,105),IF($B2>=30000,630,420)))

また、Ｄ2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(C2="","",B2-C2)

ご参考になりましたら幸いです。

回答No.5

keithin

1.05しないほうがイメージしやすいかと思いましたが，いっしょでしたね。
あと振り出しはいつも自社口座ですね。

=105 + IF(先様セル<>"うちの銀行"，315) + IF(金額セル>=30000, 210)
でいいです。

　　　　　３万円以上なら
　　　　　　　+210円　
自行　105円　→　　315円
　+315円↓　　　　
他行　420円　→　　630円
　　　　　　　+210円
　　　　　３万円以上なら

お礼

何度も書いて下さってありがとうございました。

うーん　1.05って何ですか？
私が未熟なせいで
難しくてわからないですー

回答No.3

某HN クロメート(Chromate)（@CoalTar）

＞そしてこの考え方でよいのかも　わかりません。
は Excelで検証してみるとわかりやすいです。(添付図参照)
答えはありませんが、
ある金額のとき購入金額が増えると安くなるのを許すか否かでしょうね。会社の方針というか、処理方法によると思います。煩雑になるので。

で、添付図の提案の方法は29,999で請求額(振り込み額)がとまる方法です。(安くはならないだけですが)
G3セルは
=(A3>=30315)*210+105+(A3>=30105)*(A3<30315)*(A3-30104)
としました。エラー処理などしていません。
B3セルの =(A3>=30000)*210+105 と比べてわかりにくいと思います。

ちなみに、振り込み手数料のD3、H3は
=(C3>=30000)*210+105
=(G3>=30000)*210+105
です。
参考まで(経理の専門家ではありませんのであしからず)

お礼

ありがとうございます。
まず表の作り方がわからないのが最大の問題でしたが
実はこれも心配の種でした。
30000円の請求だからといって手数料315円引いて振り込むと・・・29685円となり振り込み手数料が105円となってしまうではないですか。
そうすると実際に支払うのは29790円ですよね。
一体どうすればいいのかと、　このように表にするととてもよくわかりますね。参考になりました。
関数がわかってから次はこれをどうするか考えなくては。

回答No.2

keithin

=1.05* (100 + IF(銀行が他行, 300) + IF(金額>=３万，200))
のようになりますね。

「銀行が他行か」の方は，そのお仕事であなたが作りたい表のレイアウトに応じてまた検討してみてください。
単純には「IF(振り出す口座の銀行名<>宛先の銀行名,300)」などのようで良いと思いますが。

お礼

すごい
この数式は一体どうゆう意味なんだろう？

これを参考にして表の作成をしてみます。前に進めて助かります。
ありがとうございました。

回答No.1

popuplt

方法は他にも色々あると思いますが、１案です
A1＝銀行名（A）
B1=金額　として
=INDEX({630,420;315,105},(A1="A")+1,(B1<30000)+1)

お礼

こんなにすぐに回答をありがとうございます！
これはINDEXという関数なのですか？
この数式？を入力して早々に表を作ってやってみます。

まずはお礼まで