excelの関数振込手数料について

質問

イロイロ考えてもわからないので、みなさん助けてください。
現在　下のような関数を使って値を出しているのですが、金額によってずれるので、
このようなときは、どう対処すればよいか教えてください！
振込する際に銀行の手数料を引いて振り込んでいます。
当社指定銀行だと、105円その他の銀行だと　３００００円未満315　以上だと５２５円です。
他銀行の場合Y4に1と入力して判断させています。

F(OR(AH4="",AH4=0),"",IF($Y$4=1,IF(AH4>30000,2,1)*210)+105)　・・・AI4に此方の関数
振込手数料を計算する式なのですが、通常は問題ないのですが、
AHの金額が30525円に以下の時実際の振込額と変わってきます。

　　　　　　Y　　　　　　　　　AH　　　　　　　AI　　　　　　　AJ

3　　　　銀行識別　　　　　支払額　　　　手数料　　　　振込額

4　　　　　　1　　　　　　　　30458　　　　　　525　　　　　　29933

5

振り込むとき２９９３３円なので手数料は、315えん・・・て事ですよね。
そこで、　(AH4>30000,2,1をAJに変えれば良いと考えたのですが、
うまく動きませんでした、
で　(AH4>30000,2,1を(AH4>30525,2,1に　変えれば良いと考えましたが、
違う気がします・・・・・
どなたか　教えてください・・・・お願いします。

回答No.14

某HN クロメート(Chromate)（@CoalTar） · ベストアンサー

#7,9,11です

＞当社指定銀行だと、105円その他の銀行だと　３００００円未満315　以上だと５２５円です。
どうも、これが会社の決め事のようですね。
つまり、支払額が
30,000円未満 ：手数料315円
30,000円以上：手数料 525円
なんて、会社本位な。。。と思ってしまいます。
少なくとも、支払額30,314円までは315円の手数料ですみそうだし。
指定銀行なら安く済むことを考えると、一律で手間賃を引いて。。。
深読みしすぎですねm(_ _)m

AI4セルに
＞IF(OR(AH4="",AH4=0),"",IF($Y$4=1,IF(AH4>30000,2,1)*210)+105)
ではなく、検証のための相対参照と30,000円以上の修正を盛り込み
=IF(OR(AH4="",AH4=0),"",IF(Y4=1,IF(AH4>=30000,2,1)*210)+105)
下へオートフィル

AJ4セル =AH4-AI4
AK4セル =IF(Y4=1,AI4-IF(AJ4<30000,315,525),0)

添付図参照 AJ9セルとAJ10セルでは振込額が逆転します(働くと損の最大)

お礼

ありがとうございました。
お礼が遅くなりました。　当方も振込額の平均で一律だと
確かるのですが、会社としても
希望者に週払いしてる手数料が、思いのほか経理的にきついらしいのです。
まあ社員の片野も指定銀行の口座を進めているのですが、
なかなか思いがちたわりません・・・　　愚痴ですが・・・
今回皆さんに助けてもらい大変助かりました。

回答No.13

tom04

No.10です！
色々回答を頂いているみたいですが・・・

結局Excelの問題ではなく、帳簿上の問題になってくると思います。

方法として、前回投稿したように30000円以上の場合は一旦振込手数料525円を計上し、実際の振込額も計上しておきます。

そして、差額の210円に関しては借方に計上すれば帳簿上は貸借の金額は合致すると思います。

借方に210円を計上し、項目としては「雑収入」とか「振込手数料差額」としてはどうでしょうか？

的外れならごめんなさいね。m(__)m

お礼

なるほど　そうですよね！
ありがとうございます。
先ほど別件で会計士が来たので、質問したところ
雑収入的な話をしてました。ただ税理士も銀行の振込額と
合致したほうが　やり易いとコメントして帰りました・・・・笑い
あと　振り込んだとき私も当方の総振込額などが、
合えば　安心するし　気持ちがよいもんですから・・・
皆さんにイロイロかんがえてもらい
ホントかんしゃしてます。
心から　ありがとうございます。

回答No.12

mu2011

NO7です。
>この部分IF($Y$4=1,IF(AH4>30000,2,1)*を
>IF($Y$4=1,IF(AH4>30524,2,1)*に変えれば良いのですかね！
　⇒上記の式で30000～30524円の実質の銀行手数料は315円で算出できますが、210円の差分ありなので、別欄にこの金額範囲ならば、仮に「差分あり」と表示するとか。

お礼

ホントにイロイロ考えていただきありがとうございます。
表示の件も大変良いですね！

補足

考えてもらうついでに・・・・
こんなことですかね。AHの値を使わず、別にセルを増やし
AHの値が３００００～30524円の時は、２１０円引く
その値を元に手数料や振込額を出すことで解決できますか？

回答No.11

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

#3,9です。
いくら回答者に聞いても、解答は出ませんよ。
(会社での)決め事を返答していないのですから。
下記のような案は出せますけどね。

案1 境界領域は受給者が負担する。
30,315円以上の場合、一律525円とする
欠点 30,315円～30,524円で受給額が減り
受給者が損をする。

案2 境界領域は会社が負担する。
30,525未満の場合、一律315円とする
欠点 30,315円～30,524円で受給額が増え
会社が損をする。

案3 上記の折衷案(折半)
30,315円以上、30,420円未満の場合、315円
30,420円以上、30,525円未満の場合、525円
とする。
欠点は半分になる。

案4 当方の案(数式提示済み)
30,315円以上、30524円未満の場合、
振込額を29,999円とする。

お礼

何度も回答いただきありがとうございます。

補足

当方の希望としましては、実質銀行振り込みした際の
銀行とのデーターと同じになればよいので、
今回の場合AH（支払額）　30248円　AI　手数料　３１５円　AJ振込額29933
この数字が実際の金額になるので、
どうかして今回の場合の時だけ支払額から２１０円引くよう式ができればよいのですが・・・・

回答No.10

tom04

こんばんは！
実際問題として30000円の境界の扱いはかなり難しいみたいですね！

支払額によって「差引計算上」は525円となっても、振込額が30000円未満だと
「本来の振込手数料」は315円になると思います。

これを条件分けし、関数で行おうとすればかなり難しいと思います。
支払額が30000円以上の場合、振込手数料は当然525円となりますが、振込手数料を差し引いた場合、
振込額が30000円未満となることがあります。
その場合は振込料は当然315円となってしまいますので、もう一度振込手数料を315円で計算しなおすと、今度は振込額が30000円を超えてしまうという悪循環に陥る範囲の支払額があると思います。

そこで一つの案ですが、↓の画像はあまり深く考えず支払額が30000円以上の場合は手数料を525円、
30000円未満の場合は315円と表示させ、条件付書式で先ほど案件金額分に関して赤く表示するようにしてみました。
要するに注意を促すだけの方法になってしまいます。

AI4セルに
=IF(AH4="","",IF(Y4="",105,IF(AH4<30000,315,525)))
AJ4セルに
=IF(AH4="","",AH4-AI4)
として、AI4・AJ4を範囲指定しAJ4のフィルハンドルで下へコピーした後に
AI列すべてを範囲指定　→　条件付書式　→　「数式が・・・」を選択
数式欄に
=AND(AI1=525,AJ1<30000)　として　→　書式　→　塗りつぶしで「赤」を選択しています。

色々サイトを検索してもかなり厄介なようなので、↓のURLに載っている信用金庫の場合は、
差額の210円が生ずる場合は「当方負担」に変更してもらうか、「支払金額」を変更してもらっているみたいですね。

http://www.hokuto-shinkin.co.jp/ibbb/ib05.html#tag1

この程度ですがごめんなさいね。m(__)m

お礼

いりいろ　考えてもらい感謝します。
おしゃる通り　その都度ピックアップして、計算しなおすしかないかも
大変良い　アイデアなのかもしれません、
どうしてもできないときは、
ぜひ採用させていただきます。

回答No.9

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

＃３です
数式から算出した
=IF(OR(AH4="",AH4=0),"",IF($Y$4<>1,105,
CHOOSE(MATCH(AH4,{0,30315,30525}),315,AH4-29999,525)))
ではなく、文章から
=IF(OR(AH4="",AH4=0),"",IF($Y$4=1,105,
CHOOSE(MATCH(AH4,{0,30315,30525}),315,AH4-29999,525)))
なのかもしれませんが、

もっと根本的なところで
振込金額(相手に渡す金額)が29,999円だったら手数料は315円。
振込金額(相手に渡す金額)が30,000円だったら手数料は525円。
よって、銀行に支払う金額に30,458円は存在しません。(他銀行の場合)

当方、深読みしすぎたのかもしれません。

お礼

ありがとうございます。
感謝します。

補足

説明がの仕方がわかりませんが！
AHのセルには、IF(OR(AF5="",AG5=""),"",INT(SUMPRODUCT(($B$16:$B$46>=AF5)*($B$16:$B$46<=AG5)*($F$16:$F$46)*($G$16:$G$46)/"1:0")))　このような式が入っています
AFとAGには計算したい範囲の日付が、
B16：H47まで表がありまして（勤怠）
B16:B46まではカレンダー（日にち）F16:F46働いた時間G16:G46時給単価がはいってます。　　
今回の件はやはり　この関数にてを加えないといけない気がしますが・・・・
このような条件の場合210えん+するような・・
しかしわかりませんね。

回答No.8

imogasi

参考までに
銀行の振りこみ手数料は、銀行に持ち込んで依頼した振りこみ金額（振り込み手数料を控除した額）で決ります。
この点間違えないようにして
ーー
他行振込みの場合
・３０５２５円以上は５２５円を引き、（かならず振り込み手数料は５１５円必要だから）
・３０３１５円未満なら３１５円を引く（かならず３１５円で良い、と言うか３１５円は必要だから）
・その間の
３０３１５円から３０５２５円の場合は２９９９９円を越える金額を手数料として、２９９９９円で３１５円払って振り込みするの
が一番お客の手取りが多いのかなと思った。
ーーー
同一行宛の振り込みも同じく考えては。
ーー
ただし相手にとっても、確認がややこしいことはややこしいのでためらう。

お礼

ありがとうございます。
おしゃるとうりですね！
実質振込額は、そのとうりですよね！
関数で出すことは　できませんかね。
いつも銀行の振込額との差が出てから　あたりをつけて計算しなおしてます。

回答No.7

mu2011

>現在　下のような関数を使って値を出しているのですが、金額によってずれるので、
>このようなときは、どう対処すればよいか教えてください！
　⇒先方払い振込は、「支払い額は手数料込み」になるから支払い額が３００００～３０５２４円間は
　　銀行側の資金移動手数料が３００００未満の為に３１５円と手数料額が２１０円不一致になる。
　　この仕組みを認識した上で手数料表示をどうするのかを決める事が先決ですので回答でき
　　ないので、上司と相談しては如何でしょうか。

お礼

回答ありがとうございます。
なんだか　考えては振り出しにに繰り返しで・・・
わたしの頭の中が・・・・
検討してみます。　ありがとうございます。

補足

この部分IF($Y$4=1,IF(AH4>30000,2,1)*を
IF($Y$4=1,IF(AH4>30524,2,1)*に変えれば良いのですかね！

回答No.6

kagakusuki

すみません、再度確認させて頂きたいのですが、最初の御質問文中の記述では、

>３００００円未満315　以上だと５２５円です。

とありますが、ANo.4に対する補足では

>３万円未満が315円で

となっていますが、「30000円未満、315円以上」は、「３万円未満」の範囲に含まれますから、矛盾があります。
　もしかすると、最初の

>３００００円未満315　以上だと５２５円です。

という記述は、「30000円未満の場合の手数料は315円、30000円以上の場合の手数料は525円」の書き間違いでしょうか？

　もし、そうだとしますと、手数料の計算する基準が異なりますので、ANo.4の関数では、正しい手数料を計算する事は出来ません。
　「30000円未満の場合の手数料は315円、30000円以上の場合の手数料は525円」の場合の関数は以下の様になります。

AJ4セルの値を基に手数料を計算する関数

=IF(ISNUMBER(AJ4),IF(Y4=1,IF(AJ4<30000,315,525),105),"")

AH4セルの値を基に手数料を計算する関数

=IF(ISNUMBER(AH4),IF(Y4=1,IF(AH4<30315,315,525),105),"")

お礼

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

補足

頭悪くて　すみませんです。
なんか小学校くらいの時習ったような・・・・・
29999円までが３１５円で
３００００円かわ５２５円です。

ごめんなさい

回答No.5

kagakusuki

>手数料は先方負担なので、振込金額から手数料を引くと実質銀行の手数料が変わってくるので、分からなくなります。

　何故、振込金額から手数料を引いておられるのでしょうか？
　振込金額には手数料は含まれていませんから、手数料を引く必要はありません。
　振込金額が判明しているのであれば、その金額をAJ4に直接入力し、そのAJ4の値が「315円未満なのか」、「315円以上30000円未満なのか」、「30000円以上なのか」を判定して、AIセルの手数料の値を求めれば済む話です。
　そうして、求めた手数料と手入力した振込額（AIセルの値とAJセルの値）を足した値が、支払額であるAHセルの値になります。

　それとも、ANo.2に対する補足で述べられた「振込金額」とは、本当は「振込金額」ではなく、「支払金額」の事なのでしょうか？

　どうやら、質問者様は相当混乱されておられる様なので、どの様な状況なのかを、整理して頂かないと、質問者様が誤った情報を伝えられた事により、その情報を信じた回答者が、誤った回答してしまうおそれがあります。

　ですから、どの様な状況なのかを整理するために、以下の情報をお知らせ願います。

(1)金額を振込むのは、先方なのか、それとも、質問者様のお勤め先なのか？

(2)判っている金額は、金額を振り込んだ際に、振り込んだ側が金融機関に渡した金額の全額（支払金額＝振込金額＋手数料）なのか、それとも、振り込まれた側が受け取った金額（振込金額のみで、手数料を含んだ支払金額ではない）なのか、どちらであるのか？

お礼

引き続きの回答感謝します。

補足

イロイロご迷惑をかけます。
表現の方法が悪いみたいですね！
金額を支払うのは、当社で、AHの支払額このような式が書いてあります。
IF(OR(AF5="",AG5=""),"",INT(SUMPRODUCT(($B$16:$B$46>=AF5)*($B$16:$B$46<=AG5)*($F$16:$F$46)*($G$16:$G$46)/"1:0")))
指定した期間の金額をだしてます。
この金額から振込手数料を出して（AI）　引いた金額が（AJ)になるのですが、
この説明でよかったのですかね。　スミマセン

回答No.4

kagakusuki

指定銀行以外の金融機関を使用する場合における、30000円以上の場合の手数料と、315円未満の場合の手数料に関して記述がないため、このままでは、正しい関数を組む事が出来ません。
　ですから、30000円以上の場合の手数料と、315円未満の場合の手数料に関してもお教え下さい。
　それから、振り込み手数料は支払額によって決まるのではなく、振込額によって決まるのですから、AH4セルの値を基準に計算するのではなく、AJ4セルに振込額を入力すると、AJ4セルの値を元に、AI4セルの値が決まる様に、関数を組むのが正しいやり方です。
　ですから、AI4の関数は次の様な形式になります。

=IF(ISNUMBER(AJ4),IF(Y4=1,LOOKUP(AJ4,{0,315,30000;「315円未満の場合の手数料」,525,「30000円以上の場合の手数料」}),105),"")

　或いは、次の様な形式の関数でも同じ事が出来ます。

=IF(ISNUMBER(AJ4),IF(Y4=1,IF(AJ4<315,「315円未満の場合の手数料」,IF(AJ4<30000,525,「30000円以上の場合の手数料」)),105),"")

　尚、どうしてもAH4セルの値を基準に計算しなければならない場合には、AI4の関数は次の様な形式になります。

=IF(ISNUMBER(AH4),IF(Y4=1,IF(AH4<315+「315円未満の場合の手数料」,「315円未満の場合の手数料」,IF(AH4<30000+525,525,「30000円以上の場合の手数料」)),105),"")

お礼

ありがとうございます。
ためしてみます。

補足

指定銀行　1の場合は、　３万円未満が315円で　３万以上が５２５円です。
指定銀行の場合　Y4に何も数字がない時　一律105円です。
私も　AJ4で取るのが正しいのでは、と考えましたが、できませんでした！

AH4セルに支払金額が計算されて出てきます。それを元にAI4で手数料を計算させて　差引金額がAj4となってます。