エクセルで銀行の手数料を自動で計算したい

質問

はじめまして。

エクセルで銀行の振込み手数料を自動的に計算させたいのですが、自力ではできなかったので質問させていただきます。ぜひお力をお貸しください。

（窓口）
　　　　　　　　3万未満　　　3万以上
同一店内　　　３２４　　　　　５４０
他校本支店　 ３２４　　　　　５４０
他行　　　　　　６４８　　　　　８６４

（ATM）
　　　　　　　　３万未満　　　３万以上
同一店内　　　１０８　　　　　２１６
他校本支店　 １０８　　　　　３２４
他行　　　　　　４３２　　　　　６４８

基本はATMなのですが、５０万円以上は窓口を利用します。それもうまく反映させたいです。
よろしくお願いします。

回答No.8

chie65535

追記。

自行のコードや名称が固定であれば、以下のように「決まった値」と比較しても良いです。

・名称で比較したい場合

=CHOOSE(((A1>=500000)+(A1>=30000))*3+3-((F1="三菱東京ＵＦＪ")*(H1="丸の内")+(F1="三菱東京ＵＦＪ")),100,100,400,200,300,600,500,500,800)*1.08

・コードで比較したい場合

=CHOOSE(((A1>=500000)+(A1>=30000))*3+3-((G1="0005")*(I1="002")+(G1="0005")),100,100,400,200,300,600,500,500,800)*1.08

のようになります。

お礼

chie65535さん

ありがとうございます。
名称の数式を使ったのですが、今度はほとんどが８６４円になってしまいました。

回答No.7

chie65535

＞ただ、できたらで結構ですので名称で入力できる数式もお教え願えませんか。
＞よろしくお願いします。

コードで比較しても、名称で比較しても、同じような数式になります。

例えば

A列：金額
B列：自行名称
C列：自行コード
D列：自行支店名
E列：自行支店コード
F列：振込先銀行名称
G列：振込先銀行コード
H列：振込先支店名
I列：振込先支店コード

になっていたとします。

銀行の比較の際に、名称同士で比較するのであれば「B列とF列」の比較になるので、数式は（比較部分のみ抜き出すと）「B1=F1」とか「B5=F5」になります。

銀行の比較の際に、コード同士で比較するのであれば「C列とG列」の比較になるので、数式は（比較部分のみ抜き出すと）「C1=G1」とか「C5=G5」になります。

CHOOSE文での例で言えば、１行目（１件目）は

・名称で比較したい場合

=CHOOSE(((A1>=500000)+(A1>=30000))*3+3-((B1=F1)*(D1=H1)+(B1=F1)),100,100,400,200,300,600,500,500,800)*1.08

・コードで比較したい場合

=CHOOSE(((A1>=500000)+(A1>=30000))*3+3-((C1=G1)*(E1=I1)+(C1=G1)),100,100,400,200,300,600,500,500,800)*1.08

のようになります。

参照するセルがちょっと変わるだけで「どちらも、式そのものは殆ど一緒」です。

違うのは「名称同士を比較する」か「コード同士を比較する」の違いだけです。

回答No.6

chie65535

実際にハマりやすい事例として、以下のような事例があります。

「三菱東京ＵＦＪ」と「三菱東京UFJ」が不一致（全角と半角）

「三菱東京ＵＦＪ」と「東京ＵＦＪ」が不一致（「三菱」が抜けてる）

「するが銀行」と「スルガ銀行」が不一致（平仮名と片仮名）

「八十二銀行」と「八二銀行」が不一致（十が抜けてる）

「北國銀行」と「北国銀行」が不一致（異字体）

「近畿大阪銀行」と「近畿大坂銀行」が不一致（阪と坂が違う）

「トウホウギンコウ」と「トウホクギンコウ」の入力ミス（どちらも存在する）

「りそな銀行」と「埼玉りそな銀行」の混同（どちらも存在する）

「市ヶ谷支店」と「市ケ谷支店」の不一致（ヶとケが違う）

貴方は、これらの「一致、不一致の判定ミス」を「全件、肉眼で確認する」のが可能ですか？不可能ですよね？

なので「名称比較は絶対にやっちゃ駄目」なんです。

お礼

chie65535さん

ありがとうございます。例までつけていただいてわかりやすいです。

現状として、(1)銀行が３つしかない(2)件数が少ない
といったため、名称入力のほうがわかりやすいと感じております。

回答No.5

chie65535

＞なお、名称で入力できる数式はないのでしょうか？そちらのほうがわかりやすいのであればお教え願えないでしょうか。

名称同士を比較すれば、一致不一致は検査出来ますが、それをやると、酷い目に遭います。

片方に見えない空白が付いていて同じ銀行なのに不一致になっちゃうとか、カナの大文字小文字が違ってて同じ銀行同じ支店なのに不一致になっちゃうとか、異字体の漢字が不一致になって同じ銀行同じ支店なのに不一致になっちゃうとか、貴方には予想も出来ないような「不具合」に見舞われます。

「小さい番号は0を補って必ず４桁にする」と言うのも「5と05、05と005、5と0005」などが「不一致」と判定されない為の保険なのです（このように「桁数を合わせる」などの処理を「正規化する」と言います）

振込など、金銭が関わるプログラムは「間違ったら取り返しがつかなくなる」ので「名前の方が判りやすい」などの「人間的な考え」は捨てなければなりません。

何らかの処理を行う場合は、名称比較、名前比較などは排除し「正規化済みの一意のコードで比較する」のを徹底しましょう。

お礼

chie65535さん

ありがとうございます。詳しい説明、よくわかりました。
コード入力していこうと思います。
ただ、できたらで結構ですので名称で入力できる数式もお教え願えませんか。
よろしくお願いします。

回答No.4

chie65535

追記。

同一銀行、同一支店の比較は、下記ページにある「銀行コード」「支店コード」で行って下さい。

http://bkichiran.hikak.com/

銀行名、支店名等の名称では比較してはいけません。

銀行コードは必ず４桁、支店コードは必ず３桁にして、小さい数字は「0001」や「0009」など、頭に0を補ってから処理して下さい。

お礼

chie65535さん

ありがとうございます。
銀行コードというものをはき違えておりました。

なお、名称で入力できる数式はないのでしょうか？そちらのほうがわかりやすいのであればお教え願えないでしょうか。

回答No.3

chie65535

＞回答いただいた数式を入力しましたが、ほとんどが、６４８円になってしまいました。

式は合っている筈です。画像の通り検証済みです。

お礼

chie65535 さん

たびたび恐れ入ります。
たとえば、振込み元番号や支店番号を文字で表すことはできますか？
例：〇△銀行　と　×□支店　のようなことです。

回答No.2

chie65535

因みに、

=CHOOSE(((金額>=500000)+(金額>=30000))*3+3-((振込を行う銀行の銀行番号=振込先口座の銀行番号)*(振込を行う銀行の支店番号=振込先口座の支店番号)+(振込を行う銀行の銀行番号=振込先口座の銀行番号)),108,108,432,216,324,648,540,540,864)

と書いても良い。

消費税が変わる場合を考慮した場合は

=CHOOSE(((金額>=500000)+(金額>=30000))*3+3-((振込を行う銀行の銀行番号=振込先口座の銀行番号)*(振込を行う銀行の支店番号=振込先口座の支店番号)+(振込を行う銀行の銀行番号=振込先口座の銀行番号)),100,100,400,200,300,600,500,500,800)*1.08

と書くのが良いかも知れない。

お礼

chie65535 さん

ありがとうございます。新しく書いていただいた数式を入力しましたが、やはりまた、ほとんどが６４８円になりました。。。

回答No.1

chie65535

＞同一店内
＞他校本支店
＞他行

区分が間違っていると思うけど。

これは、普通の銀行は

同一店
同行本支店
他行

の筈。

で、手数料を計算する上で「同一店、同行本支店、他行」の区別をする為には「振込を行う銀行の銀行番号」「振込を行う銀行の支店番号」「振込先口座の銀行番号」「振込先口座の支店番号」が必要になる。

エクセルで書くと

=IF(金額>=500000,IF(振込を行う銀行の銀行番号=振込先口座の銀行番号,540,864),IF(金額>=30000,IF(振込を行う銀行の銀行番号=振込先口座の銀行番号,IF(振込を行う銀行の支店番号=振込先口座の支店番号,216,324),648),IF(振込を行う銀行の銀行番号=振込先口座の銀行番号,108,432)))

となります。

お礼

chie65535さん

ありがとうございます。ご指摘通り、他校本支店は誤りで、当行本支店が正解です。

回答いただいた数式を入力しましたが、ほとんどが、６４８円になってしまいました。

補足

他校本支店は誤りで、当行本支店が正です。
失礼しました。