本日契約成立の株の売買が1個の場合→売買手数料3150円です。
複数売買の場合→売買手数料は3150円を分割します。(機械的にセルの数だけ均等分割可。1日が終了し金額確定してからその金額で計算式を上書き訂正します。別案として契約金額に応じて按分出来ればなおうれしいです)
ただし手数料は1日の契約合計300万円ごとに3150円となる。300万=3150 301万=6300円 601万=9450円
帳簿の現状
セルB買い年月日 C買い数量 D買い単価 E支払金額手数料含む F品名(銘柄) G売り年月日 H売り数量 I売り単価 J売上金額手数料含む K売買損益
過去から未来に渡る売買帳です。Eの支払金額欄とJの売上金額欄に前もって数式を入れて置きたいのです。
No.5ベストアンサー
- 回答日時:
約定金額を300万以下、600万以下、900万以下、900万を超えるにしました。
当日以外の日は、スペースにしました。
それとこの式は、ファイルが重くなる見本のような式なので(電卓でこの式どおり計算するとわかります)、変えました。
E2に・・・
=IF(TODAY()<>B2,"",IF($B$1=0,,IF($B$1<=3000000,C2*D2*3150/$B$1,IF($B$1<=6000000,C2*D2*6300/$B$1,IF($B$1<=9000000,C2*D2*9450/$B$1,C2*D2*12600/$B$1)))))
L2に・・・
=IF(TODAY()<>I2,"",IF($B$1=0,,IF($B$1<=3000000,J2*K2*3150/$B$1,IF($B$1<=6000000,J2*K2*6300/$B$1,IF($B$1<=9000000,J2*K2*9450/$B$1,J2*K2*12600/$B$1)))))
B1に=SUM(A:A)・・・(B1はA列以外だったらあいてるセルならどこでもいい)C1だったら、上の式は$C$1にしてください。
出来ました!!
感謝 感涙 感動しております。
本当にありがとうございました。
微力ながら
エクセル大好き人間としては
至福の境地であります。
ありがとうございました。
No.6
- 回答日時:
下記で何か参考になりませんでしょうか。
モデル的に簡略化した積もり。<A10:E19のデータ>
(2003/6/11は指定日をいれている。売価より
右は関数式で算出。)
売買日株数株価売価2003/6/11
2003/6/1040010040000
2003/6/1130015045000616
2003/6/1210020020000
2003/6/1120030060000822
2003/6/115002501250001712
2300003150
3150
<D10:E19の関数式>
売価37783(2003/6/11のこと)
=B11*C11=IF(A11=$E$10,ROUND($D$19*D11/$D$17,0),"")
=B12*C12=IF(A12=$E$10,ROUND($D$19*D12/$D$17,0),"")
=B13*C13=IF(A13=$E$10,ROUND($D$19*D13/$D$17,0),"")
=B14*C14=IF(A14=$E$10,ROUND($D$19*D14/$D$17,0),"")
=B15*C15=IF(A15=$E$10,ROUND($D$19*D15/$D$17,0),"")
↑比例配分
=SUMIF(A11:A15,"2003/6/11",D11:D15)=SUM(E12:E15)
↑6/11日分だけ集計
=IF(D17<3000000,3150,ROUNDUP(D17/3000000,0)*3150)↑手数料計算式
何度も 申し訳ございません。感謝申し上げます。
参考と言うことで、拝見しているのですが、いまいち現状では理解できませんので、楽しい宿題といたします。
本当にありがとう誤字ました。
またの機会によろしければお助けを希望し
これで閉めさていただきます。 ありがとうございました。
No.4
- 回答日時:
私も、株の売買管理表をつくったことがあり、興味深く見ていました。
同一銘柄の買い増しとか、その銘柄の一部分だけを売却したばあいの処理とか、余計な心配をしていました。
本題に入りまして
A列があいてるようなので
A2に次の式を貼り付け、必要な分だけ下にコピーしてください。
=IF(AND(B2=TODAY(),I2=TODAY()),C2*D2+J2*K2,IF(B2=TODAY(),C2*D2,IF(I2=TODAY(),J2*K2,"")))
E2に・・・
=IF(SUM(A:A)=0,"",IF(SUM(A:A)<=3000000,C2*D2*3150/SUM(A:A),IF(SUM(A:A)<=6000000,C2*D2*6300/SUM(A:A),C2*D2*9450/SUM(A:A))))
L2に・・・
=IF(SUM(A:A)=0,"",IF(SUM(A:A)<=3000000,J2*K2*3150/SUM(A:A),IF(SUM(A:A)<=6000000,J2*K2*6300/SUM(A:A),J2*K2*9450/SUM(A:A))))
ただ、下の二つの式は、必要な行以外に書き込むと、それを計算してしまいます。(つまり、当日以外の日でも計算してしまう)ので、注意が必要です。
これでいいと思いますが
この回答への補足
その後色々試行して
[手数料]式のIF関数の<偽の場合>の""(空白)を削除しカンマだけ(又は0)に修正しました。
理由>
売買精算額の式 [株数]x[単価]-[(or+)手数料]で
日付欄で入力ミスし変更すると[♯VALUE!]が表示されるのを防ぐためです。
そして{精算額]の式を以下のようにしました。
IF(手数料>0,株数x単価-(or+),"")
これで満足ですが、質問完了を数日待ちます。
手数料対象 合計金額300万x4か5段階までの式をご案内下されれば幸いです。
ありがとうございました。
希望する物で、心より感謝申し上げます。
按分の式がいまいち理解できないのですが、
手数料を、もう一段階広げる(1200万円の12600円)こと出来ますか? 試行錯誤で式追加してもうまく行きません。
また、手数料欄に当日以外の日は計算しない様に、IF関数でAセルが10より小さいと空白を返すことを試みましたが、だめでした。
No.3
- 回答日時:
>ボタンの置き場所にも困ります
そうですね。コントロール(ボタンなど)はページがスクロールした時に隠れるのは気になっています。
他に(1)ツールボタン(2)メニュやショートカット(3)図形やコントロールなどありますので、(1)や(2)を勉強して見てください。
>行の追加が起こる様で、これは困ります。
行の挿入は避けられないと思いますが、なぜ困るのでしょうか。
>試みてみましたが、うまく行きません
実際の項目並びと違うようですが、小数例ではテスト済みです。
>VBAを持ち出した理由について
小生の経験では、エクセルを仕事に使うとなると、随所に
VBAを使わないと、不便なままと言うケースを多々経験しましたので。
この回答への補足
ご返事ありがたく感じております。
行数の増加を嫌うのは、色々とデータを他の列に表示しているからです。ワープロ機のカルク出身ですのでシート分けよりスクロールで他の値見をしますから。
うかつにも今気づいたのですが ごめんなさい。
[今日の売買]シートに一日が終わると消すコーナーがあります。
単価 株数 金額 の3項目だけで10行あり、11目に約定(と売買予定分)金額の合計があり手数料の目安にしています。
これを利用して、12行目にIF関数を入れ子して手数料を出し、COUNTA()関数で抽出したセル数で割れば単純分割された手数料が出る所までは、考えました。
按分するとなるとVBAですか。(このコーナーの左右にも大切なデータブロックがあり行の増減が必要なら別シートに新設しますが)
色々ありがとうございました。
♯4のki-aaa様の式で解決しました。
整理不備の質問を反省し、板を汚しましたことを
お詫びいたします。
読者の皆様にも。
ごめんなさい。
No.2
- 回答日時:
#1の補足ありがとうございました。
ご質問の内容は「売却日が当日日付けの明細を抜き出し
(抜き出さないでも対象として)売却合計金額を計算し
、手数料金額を割りだし、手数料を配分したい」と言うことですね。
ただ関数では明細行の抜き出しが難しいので、計算上だけ
でやることになります。フィルター等を使わないのでしょうね。
本件と関連して、
VBAと言うものですが、過去に購入した単位の一部を売却した時の操作をプログラムで作ってみました。
(1)コマンドボタンを1つ貼りつけます。
(2)そのボタンをダブルクリックすると
Private Sub CommandButton1_Click()
End Sub
が出ます。その中間に下記をコピー貼りつけします。
Private Sub CommandButton1_Click()
r = ActiveCell.Row
k = Cells(r, "b")
ActiveCell.EntireRow.Copy
ActiveCell.EntireRow.Insert xlDown
ActiveCell.EntireRow.PasteSpecial
Application.CutCopyMode = False
x = InputBox("一部売却株数=")
'------
Cells(r, "B") = x
Cells(r, "d") = x * Cells(r, "c")
'------
Cells(r, "f") = Date
Cells(r, "g") = x
'------
Cells(r + 1, "b") = k - x
'------
End Sub
(元データ)
購入年月日購入株数購入株価支払い金額銘柄売却日売却株数
2003/10/120023046000A物産
2003/8/31300520156000B興業
2003/7/20250800200000C製鉄
(操作)
(1)2003/8/31300をポイント
(2)ボタンをクリック
(3)本日売却株数を入力(例200株)
(結果)
購入年月日購入株数購入株価支払い金額銘柄売却日売却株数
2003/10/120023046000A物産
2003/8/31200520104000B興業2003/9/15200
2003/8/3110052052000B興業
2003/7/20250800200000C製鉄
(後記)
あと(2)売却ボタン(3)手数料按分ボタンを作って
プログラムすると、随分売却以後の操作が楽になると思いますが、VBAは、ダメでしょうね。
ついでに売却ボタンをもう1つ貼りつけ、下記VBAを
貼りつけます。
Private Sub CommandButton2_Click()
r = ActiveCell.Row
y = InputBox("売却株価")
Cells(r, "f") = Date
Cells(r, "g") = Cells(r, "B")
Cells(r, "h") = y
Cells(r, "i") = Cells(r, "g") * Cells(r, "h")
End Sub
(操作)売却銘柄の行をポイントしボタン(2番目)をクリック。
売却株価を聞いてくるので320(円)を入れる。
(結果)
B興業2003/9/1520032064000
と入る。
手数料按分ボタンもVBAで考えてみます。
この回答への補足
早速ありがとうございました。
今取り込んでおりますので 夜にでもトライしてみます。
VBAは出来ません。入門書を持っているのですが、老人を理由にさぼっております。
しかし、今は無き[PC相談]で書いていただいた物をコピーして便利に使っております。
エクセル標準モジュールにModule1~8まで入ってます。
その中の圧巻は[Ctrl]+[Alt]+[矢印キー]で選択セルの削除や追加ですが。
夜になって、試みてみましたが、うまく行きません。ボタンなど使ったことなく、参考書でやっと作りましたが、思考がうろついて、核心を突けないで挫折しております。行の追加が起こる様で、これは困ります。
うまく出来たときの話ですが、ボタンの置き場所にも困ります。一日しか使わないので、A列にでも置いて毎日下へ引きずることを考えています。ボタンではなくショートカットキーCtrl+Alt+Tを使えればいいのですが。Tは手数料の意。
なんか面倒そうで、ごめんなさい。アドバイス終了も可ですよ。ありがとうございました。
もしお嫌でなければ、
実際のシートは次の様になってます。
A空き B買い約定日 C株数 D単価 E手数料 F買い精算額 G平均株価 H銘柄 I売り約定日 J株数 K単価 L手数料 M売り精算額 N売買益
計算のための列を幾つか追加する必要もあるでしょう。
質問前の私案
B列 I列の日付をSUMIF関数でTODAY()を条件にセルの個数を出し、取引総額が300万毎に3150円をもとに手数料総額を出し、セル数で金額を割り、それをTODAY()に合致するEやLに均等に表示。これでは実際手数料金とは異なりますが妥協案でした。
できれば約定金額により按分した手数料金額としたいと言うことでした。その日のうちに記録セルの式は値のみに替えます。
No.1
- 回答日時:
ビジネスに絡んだ質問になると、業界知識や世間常識が必要になって、まず理解するのがひと苦労です。
質問は長文であるが、エクセルのTIP(技術的論点。何について答えれば良いかの点)までたどり着けませんでした。私は力不足で答えられないかもしれませんが、質問を読まれる人のために、良ければ下記質問に補足して頂けますか。
(小さな疑問・確認)
(1)本日契約成立の株の売買が1個=1銘柄?
(2)・・の場合→売買手数料3150円です。=(後記から、株数X株価=300万円までの制約があるのでは?以内なら-->3150円
(3)複数売買の場合=複数銘柄?を同一日売りまたは買の場合?買い+売りも合算
(4)→売買手数料は3150円を分割します。=A銘柄X株価C+B銘柄X株価D<=300万円の時、どう分割する?
(5)(機械的にセルの数だけ均等分割可。=セルとは何ですか。エクセルのセル?。入力行のこと?後記ではせるは列の意味らしいが。
(6)1日が終了し金額確定してから=なぜ金額が市場が閉まるまで確定しない?売却価格、購入価格の通知は即座ではないですか?
(7)その金額で=なんの金額?計算式は?
(8)計算式を上書き訂正します。=なぜ計算式を上書きする必要あり?どのようにする?
(9)別案として契約金額=契約金額とは、売却や買入れ金額のこと?
(10)・・に応じて按分出来れば=銘柄ごとに、株買い入れ、売却価格で比例按分?
(11)過去から未来に渡る売買帳です。=売買の日々今後
記録が増える。
(12)Eの支払金額欄とJの売上金額欄に前もって数式
を入れて置きたいのです。=計算式・関数式を入れておきたい
ということ。もっともでしょう。
(13)取引証券会社は1社で、手数料計算の基礎となる、契約金額は、売り金額だけで計算ですか、買い金額も合算で計算ですか。
(14)ただし手数料は1日の契約合計300万円ごとに3150円となる。300万=3150円、301万=6300円、601万=9450円・・
式は=IF(金額<300,3150,roundup(金額/300,0)*3150)
<帳簿の現状データ項目>
A列=空き?
B列=株購入年月日
C列=買い株数
D =買い株1株単価
E =支払金額(C*D+手数料?)
手数料含む
F =銘柄
G =株売却年月日
H =売却株数
I =売り株一株単価
J =売却金額(H*I+手数料?)
手数料含む
K =売買損益(J-E。C=Hでないとこう単純ではない?
売買手数料を支払を含めて。
(疑問)
(1)ある日に同一銘柄2回購入や売却はあるか?
(2)ある日に購入したある名柄は別の日に、一部売却があり得るのでは。そうすると複雑になるような気がしますが。売却記録が2行以上必要では?どう対処されているのでしょう。
この回答への補足
下手な質問文で大変ご迷惑をおかけいたしました。
皆様にお詫びいたします。またご丁寧なご指摘には心より感謝申し上げます。
[帳簿]と記述したシートは[売買記録]シートです。売買の度にデータを入力してます。
(小さな疑問・確認)への回答
1)1個の場合とは本日の売か買いどちらか1回=[売買記録]シート上の EかJ列の本日は1個のセルにしか値がない場合。
2)ミスです。正しくは 300万円まで3150円。300万+1円~600万円まで3300円。600万+1円~900万円まで9450円です。
3)同一銘柄の売買も含めて1日に約定した金額の合計に上記のボックスレート(松井証券)がかかります。その総手数料金を約定毎の金額比率で按分され、加減し、銘柄毎の支払金額、売却金額となります。
4)簡素化のため均等割でも可という意味です。出来れば按分したいですが。
5)セルの数だけ=今の日付のあるE列(支払金額)とJ列(売却金額)のセルの数で均等割。=約定した回数で均等割。
6)終了しないと(1日の売買高により変動する)手数料が確定しないから。終了して証券会社のバッチ処理が終了する16時以降に見ることが出来ます。
7)その金額とは証券会社が発表した各取引毎の約定金額で下の↓(便宜上のつもりが役立たずな)計算式を消す。
8)EやJ列の未来の月日に計算式[売り単価x株数-3150]が入っています。証券会社が按分した正しい手数料込みの金額を入力訂正します。
9)契約金額はミスです。約定金額に応じた按分比率で手数料が計算できたら良いなと言う願望です。
10)按分は売買それぞれの約定金額に応じた比率です。
11)何年間にも渡り行が増えてゆきます。先週末で686行記入されています。1年分をその年の申告に使います。
12)1日が終わると式は不要になり数値のみにします。
13)証券会社は1社です。手数料は売や買の区別無く約定金額(取引が成立した時の株数x株価です)により按分されます。
疑問
1)同一銘柄の複数売買はあります。
2)一部売りの場合は過去に購入した株数を売り株数に合わせます。残りを日付を過去と同じにして新しい行に買いを新設します。
売買の途中で手数料がボックスレートのどの位置にあるかを確認しながら、売買をします。小口取引なので、2円の利食いを1000株すると2000円利益ですが手数料3150円だとどう決断するか。同日買った株が幾つかあり、1000株売っても手数料が500円ならその銘柄から逃げても良いなど決断の参考です。
[今日の取引]と言うシートがあります。
今年の各月毎の損益、過去10営業日の損益、をみれる様にしています。もし按分された手数料が売買毎に明示できた場合、さらに便利になると考えております。
よくわかりませんがエクセルのアドインは以下の物がONになっています。
Lookupウイザード
ソルバーアドイン
条件付き合計式ウイザード
分析ツール
分析ツール VBA
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- 債券・証券 仕訳のこの問題が分かりません。教えていただけるとありがたいです 1 2022/06/15 20:34
- 株式市場・株価 同日中の特定口座内での株式売買における実質損益について 4 2022/06/22 05:18
- 投資・株式の税金 特定居住財産の譲渡損失の損益通算及び繰越控除の特例 2 2022/12/30 10:00
- 不動産業・賃貸業 特定居住財産の譲渡損失の損益通算及び繰越控除の特例 1 2022/12/30 11:44
- その他(お金・保険・資産運用) 至急!【Wolt】各メニューの価格設定の簡単な計算方法 3 2023/03/05 11:58
- 法学 以下の民法の問題を教えて頂きたいです。 AとBは、A所有の甲土地をAがBに1億円で売る契約を締結し、 2 2023/07/17 13:41
- 財務・会計・経理 事業復活支援金の事前確認について 2 2022/04/11 16:07
- その他(税金) 事業復活支援金の事前確認ってどこまでチェックされるのでしょうか? 1 2022/04/22 11:55
- 簿記検定・漢字検定・秘書検定 簿記3級 仕訳の問題です。 2 2022/09/23 22:03
- 消費税 消費税の納税額の計算 1 2023/02/19 18:12
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
【マクロ】エクセルにかいてあ...
-
エクセルの行の抽出について質...
-
Excel初心者です。 詳しい方、...
-
スプレッドシート クエリ関数 1...
-
別シートからの文字を変更
-
Excel初心者です。 詳しい方、...
-
Excel 2019 のピボットテーブル...
-
MOS365 Excel Expert / Excel R...
-
エクセルの数式で教えてください。
-
数学 Tan(θ)-1/Cos(θ)について...
-
4つのパターンを表示するEXACT...
-
各ページの1番上の表示について
-
INDIRECTを使わず excelで複数...
-
関数を教えて下さい。
-
Excelのグラフ軸について
-
Excel:一部のフォントでセルの...
-
エクセルで指定した日付、店舗...
-
Excel ウインドウ枠の固定をす...
-
EXACT関数とIF関数の組み合わせ...
-
写真のコピー
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
ファイル内にある数字の出現回...
-
Excel関数の先頭に「@」が入っ...
-
エクセルの気味悪い不思議
-
Excel VBAで、実行時にsheet上...
-
表示されている人数だけを数え...
-
他人が作ったマクロの理解
-
Excelの関数について質問です。
-
Excel 集計表
-
エクセル 日時の計算式について
-
Excelの関数に関して質問です。...
-
エクセル:セル内の文字列の下...
-
絞り込み検索
-
エクセルの関数で
-
エクセルの書式設定について教...
-
余分なEXCELファイルに印刷され...
-
VBA 同一シート内での転記の仕方
-
長期休みの関数はありますか
-
Excelの空のセル
-
エクセルで入力してある文を別...
-
Excelのマクロで、セルを結合し...
おすすめ情報