エクセルの数式について教えてください！

質問

株取引の表計算について、
①　I列セルの「手数料」（赤色）
②　N列セルの「税」（青色）
③　O列セルの「手数料」（緑色）
を自動計算する数式を教えてください。

条件１：４種類の取引（「買」「売」「信買」「信売」）
条件２：売買額ごとの手数料

参照したい手数料一覧は、Q1セルです。
例えばQ5セルは、「買」または「売」の売買額が5万円以上10万円以下の場合、手数料99円、という意味です。

①　I列セルの「手数料」（赤色）
（例）
I3セルは、D3「買」で、かつ、H3「16万円」なので、115円と自動計算したい。
I2セルは、信用「信買」で、かつ、H4が23万円なので、198円と自動計算したい。

②　N列セルの「税」（青色）
取引によって利益が発生した場合、つまり、P列「損益」がプラスであった場合、利益の20.315%が課税（N列「税」）され、利益から引かれます。
N列を自動計算する数式を教えてください。
なお、税が発生しなけば（つまりP列「損益」がマイナスであれば）「－」と表示したいです。

③　O列セルの「手数料」（緑色）
①と同様、M列「売買額」に応じて自動計算したいが、②を加えた額を表示させたい。

以上、長々とすいません。
よろしくお願いいたします。

回答No.2

imogasi · ベストアンサー

＃１です。＃１は抽象的なので、
小生は株式取引の基本的なことが判っていなくて、大きな誤りがあるかもしれない。
VLOOKUP関数の使い方についてのヒントだと思って、読んでかつテストしてください。
（１）式まで具体例を挙げてみる。
（２）手数料算出計算に限っての解説です。他は掛け算や引き算と思うので略。
（３）列や行の内容の配置は、当方の好みで解説している。
　　　質問者の場合に当てはめて、修正すること。
（４）中心話題は、VLOOKUPの参照表（セル範囲）（第2引数で指定）が複数ある場合の、参照表や
　　　テストデータの作り方の解説です。
ーー
・質問画像のD列の取引の買・売・信買・信売は４種あってもおかしくないが、本件では現物と信用の
2種類らしいから、２種類作る（れば良い）が、だい３引数に当たる部分の選択肢は４つ指定である。
R,S列の組とT,U列の組の２種類（２箇所）である。
・D列は４種の語句（買・売・信買・信売）のどれかが（漢字がこの通り正確に）現れるとする。
・I列が関数で計算された手数料です。
ーー
参照表（手数料の）
手作りする。
R,S列　　T,U列　１－７行のデータ　
買・売 信買・信売
0 0 0 0
1 55 1 99
50001 99 100001 145
100001 115 200001 198
200001 275 500000 365
500001 275
ーー
テストデータ

D列とH列
手作りする。
取引 売買額
買 0
買 1
買 30000
買 49999
買 50000
買 50001
買 70000
買 99999
買 100000
買 100001
買 199999
買 200000
買 200001
買 499999
買 500000
買 500001
買 1200000
この下に、同じくD列とH列、の「売」のデータを続ける。

売 0
売 1
売 30000
売 49999
売 50000
売 50001
売 70000
売 99999
売 100000
売 100001
売 199999
売 200000
売 200001
売 499999
売 500000
売 500001
売 1200000

この下へ、同じくD列とH列へ、信買と信売のテストデータを作る。（コピペで一部修正で）

ーー
I2の式
=VLOOKUP(H2,CHOOSE(MATCH(D2,{"買","売","信買","信売"},0),$R$2:$S$7,$R$2:$S$7,$T$2:$U$7,$T$2:$U$7),2,TRUE)
を入れて、下方向に式複写。
この部分を印刷して、じっくり金額ー手数料の対応が正しいかチェックして、間違いがあれば、参照表や関数式を正して

ください。
＝＝
参照表が、「１株当たりの金額」か、売買金額の「料率」かよくわからない。
この辺は正しく修正してください」。
＝＝
結果
「買」いの場合の部分だけ掲出。あとはシートで出して印刷でもしてチェックしてください。
買 0 0
買 1 55
買 30000 55
買 49999 55
買 50000 55
買 50001 99
買 70000 99
買 99999 99
買 100000 99
買 100001 115
買 199999 115
買 200000 115
買 200001 275
買 499999 275
買 500000 275
買 500001 275
買 1200000 275
ーー
他に回答が出ない理由が想像される。
テストや文章作成に大変時間がかかった。これで勘弁してもらう。

お礼

回答ありがとうございます。
先程、この通りにバックテストして上手くいきました。
どうもありがとうございました。

回答No.3

HohoPapa

手数料テーブル（R:U列）を添付画像のように手直ししてよければ
添付画像のようになるのではないかと思います。

求めている仕様を読み違えているかもしれませんので、確認してください。
また、税の計算に円未満の端数が起きる場合は、小数点以下第一位を四捨五入しました。

I3=IF(D3="買",VLOOKUP(H3,$R$4:$S$9,2,1),VLOOKUP(H3,$T$4:$U$8,2,1))
N3=IF(P3>0,ROUND(P3*0.20315,0),"-")
O3=IF(D3="買",VLOOKUP(M3,$R$4:$S$9,2,1),VLOOKUP(M3,$T$4:$U$8,2,1))

お礼

お礼が遅れて申し訳ありません。
こういう方法もあるのですね。
どうもありがとうございました。

回答No.1

imogasi

これは
（１）VLOOKUP関数の利用の課題でしょう。
（２）またVLOOKUP関数で２種類あるうちのTRUE型（第4引数）でしょう。
２つの差を理解しておくこと。
（３）また、本件では、場合によって見る参照表を２つに指定し分けないとならないでしょう。
　　やや中級以上の課題でしょう。
(注）ＶＬＯＯＫＵＰはエクセルの関数を使い始める者の、まず学ぶもので、常識です。ＷＥＢででも勉強のこと。
（４）ＶＬＯＯＫＵＰ関数のＴＲＵＥ型の参照表を作るとき、金額目盛には、昇順に指定するが、
「以下、未満、以上」の区分によって、区分け付近の１の違いに注意すること。
ーー
(注）（３）は２，３やり方があるようだ。WEB照会すれば少ないが記事がある。
ーー
株などの売買に関しては、商習慣について、小生（年はとっているが）は詳しくない。
また税についても、知識が必要だ。
よほど、質問者は、質問に注意書きを書くべきだろう。
ーー
金儲けの話なので、フリーソフト、証券会社の提供ソフトや出来合いソフト、市販ソフトも沢山あるだろう。
それを購入するとか。また授業料を払ってでも、エクセルを勉強に行くとか。
ーー
ここの読者（予想）と（予想）回答適任者がずれているのかも。

お礼

お礼が遅れてしまい、大変申し訳ありませんでした。
よく理解できました。
ありがとうございました。