EXCEL2000 SUMIF(範囲,"TODAY()",合計範囲) だけでは出来ないので

質問

本日契約成立の株の売買が1個の場合→売買手数料3150円です。
複数売買の場合→売買手数料は3150円を分割します。（機械的にセルの数だけ均等分割可。1日が終了し金額確定してからその金額で計算式を上書き訂正します。別案として契約金額に応じて按分出来ればなおうれしいです）
ただし手数料は1日の契約合計300万円ごとに3150円となる。300万＝3150　301万＝6300円 601万＝9450円
帳簿の現状
セルＢ買い年月日　Ｃ買い数量　Ｄ買い単価　E支払金額手数料含む　Ｆ品名（銘柄）　Ｇ売り年月日　Ｈ売り数量　I売り単価　Ｊ売上金額手数料含む　Ｋ売買損益

過去から未来に渡る売買帳です。Eの支払金額欄とＪの売上金額欄に前もって数式を入れて置きたいのです。

回答No.5

ki-aaa · ベストアンサー

約定金額を３００万以下、６００万以下、９００万以下、９００万を超えるにしました。

当日以外の日は、スペースにしました。

それとこの式は、ファイルが重くなる見本のような式なので（電卓でこの式どおり計算するとわかります）、変えました。

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

imogasi

下記で何か参考になりませんでしょうか。モデル的に簡略化した積もり。
＜Ａ１０：Ｅ１９のデータ＞
（２００３／６／１１は指定日をいれている。売価より
右は関数式で算出。）
売買日 株数 株価 売価 2003/6/11
2003/6/10 400 100 40000
2003/6/11 300 150 45000 616
2003/6/12 100 200 20000
2003/6/11 200 300 60000 822
2003/6/11 500 250 125000 1712

230000 3150

3150
＜Ｄ１０：Ｅ１９の関数式＞
売価 37783（２００３／６／１１のこと）
=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)
↑６／１１日分だけ集計
=IF(D17<3000000,3150,ROUNDUP(D17/3000000,0)*3150)↑手数料計算式

お礼

何度も　申し訳ございません。感謝申し上げます。
参考と言うことで、拝見しているのですが、いまいち現状では理解できませんので、楽しい宿題といたします。
本当にありがとう誤字ました。
またの機会によろしければお助けを希望し
これで閉めさていただきます。　ありがとうございました。

回答No.4

ki-aaa

私も、株の売買管理表をつくったことがあり、興味深く見ていました。

同一銘柄の買い増しとか、その銘柄の一部分だけを売却したばあいの処理とか、余計な心配をしていました。

本題に入りまして
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))))

ただ、下の二つの式は、必要な行以外に書き込むと、それを計算してしまいます。（つまり、当日以外の日でも計算してしまう）ので、注意が必要です。

これでいいと思いますが

お礼

ありがとうございました。
希望する物で、心より感謝申し上げます。

按分の式がいまいち理解できないのですが、
手数料を、もう一段階広げる（1200万円の12600円）こと出来ますか？　試行錯誤で式追加してもうまく行きません。
また、手数料欄に当日以外の日は計算しない様に、IF関数でAセルが10より小さいと空白を返すことを試みましたが、だめでした。

補足

その後色々試行して
[手数料]式のIF関数の<偽の場合>の""(空白)を削除しカンマだけ（又は0)に修正しました。
理由＞
売買精算額の式　[株数]ｘ[単価]-[（or+）手数料]で
日付欄で入力ミスし変更すると[♯VALUE!]が表示されるのを防ぐためです。
そして{精算額]の式を以下のようにしました。
IF(手数料＞0,株数ｘ単価-(or＋),"")

これで満足ですが、質問完了を数日待ちます。
手数料対象 合計金額300万ｘ４か５段階までの式をご案内下されれば幸いです。

回答No.3

imogasi

>ボタンの置き場所にも困ります
そうですね。コントロール（ボタンなど）はページがスクロールした時に隠れるのは気になっています。
他に（１）ツールボタン（２）メニュやショートカット（３）図形やコントロールなどありますので、（１）や（２）を勉強して見てください。
＞行の追加が起こる様で、これは困ります。
行の挿入は避けられないと思いますが、なぜ困るのでしょうか。
＞試みてみましたが、うまく行きません
実際の項目並びと違うようですが、小数例ではテスト済みです。
＞ＶＢＡを持ち出した理由について
小生の経験では、エクセルを仕事に使うとなると、随所に
ＶＢＡを使わないと、不便なままと言うケースを多々経験しましたので。

お礼

色々ありがとうございました。
♯4のki-aaa様の式で解決しました。
整理不備の質問を反省し、板を汚しましたことを
お詫びいたします。
読者の皆様にも。
ごめんなさい。

補足

ご返事ありがたく感じております。
行数の増加を嫌うのは、色々とデータを他の列に表示しているからです。ワープロ機のカルク出身ですのでシート分けよりスクロールで他の値見をしますから。

うかつにも今気づいたのですが 　ごめんなさい。
[今日の売買]シートに一日が終わると消すコーナーがあります。
単価　株数　金額　の3項目だけで10行あり、11目に約定（と売買予定分）金額の合計があり手数料の目安にしています。

これを利用して、12行目にIF関数を入れ子して手数料を出し、COUNTA()関数で抽出したセル数で割れば単純分割された手数料が出る所までは、考えました。
按分するとなるとVBAですか。（このコーナーの左右にも大切なデータブロックがあり行の増減が必要なら別シートに新設しますが）

回答No.2

imogasi

#1の補足ありがとうございました。
ご質問の内容は「売却日が当日日付けの明細を抜き出し
（抜き出さないでも対象として）売却合計金額を計算し
、手数料金額を割りだし、手数料を配分したい」と言うことですね。
ただ関数では明細行の抜き出しが難しいので、計算上だけ
でやることになります。フィルター等を使わないのでしょうね。
本件と関連して、
VBAと言うものですが、過去に購入した単位の一部を売却した時の操作をプログラムで作ってみました。
（１）コマンドボタンを１つ貼りつけます。
（２）そのボタンをダブルクリックすると
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/1 200 230 46000 Ａ物産
2003/8/31 300 520 156000 Ｂ興業
2003/7/20 250 800 200000 Ｃ製鉄
（操作）
（１）2003/8/31 300をポイント
（２）ボタンをクリック
（３）本日売却株数を入力（例200株）
（結果）
購入年月日 購入株数 購入株価 支払い金額 銘柄 売却日 売却株数
2003/10/1 200 230 46000 Ａ物産
2003/8/31 200 520 104000 Ｂ興業 2003/9/15 200
2003/8/31 100 520 52000 Ｂ興業
2003/7/20 250 800 200000 Ｃ製鉄
（後記）
あと（２）売却ボタン（３）手数料按分ボタンを作って
プログラムすると、随分売却以後の操作が楽になると思いますが、ＶＢＡは、ダメでしょうね。
ついでに売却ボタンをもう１つ貼りつけ、下記ＶＢＡを
貼りつけます。
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
（操作）売却銘柄の行をポイントしボタン（２番目）をクリック。
売却株価を聞いてくるので３２０（円）を入れる。
（結果）
Ｂ興業 2003/9/15 200 320 64000
と入る。
手数料按分ボタンもＶＢＡで考えてみます。

お礼

夜になって、試みてみましたが、うまく行きません。ボタンなど使ったことなく、参考書でやっと作りましたが、思考がうろついて、核心を突けないで挫折しております。行の追加が起こる様で、これは困ります。

うまく出来たときの話ですが、ボタンの置き場所にも困ります。一日しか使わないので、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やＬに均等に表示。これでは実際手数料金とは異なりますが妥協案でした。
できれば約定金額により按分した手数料金額としたいと言うことでした。その日のうちに記録セルの式は値のみに替えます。

補足

早速ありがとうございました。
今取り込んでおりますので 夜にでもトライしてみます。
VBAは出来ません。入門書を持っているのですが、老人を理由にさぼっております。
しかし、今は無き[PC相談]で書いていただいた物をコピーして便利に使っております。
エクセル標準モジュールにModule1～8まで入ってます。
その中の圧巻は[Ctrl]+[Alt]+[矢印キー]で選択セルの削除や追加ですが。

回答No.1

imogasi

ビジネスに絡んだ質問になると、業界知識や世間常識が必要になって、まず理解するのがひと苦労です。
質問は長文であるが、エクセルのＴＩＰ（技術的論点。何について答えれば良いかの点）までたどり着けませんでした。私は力不足で答えられないかもしれませんが、質問を読まれる人のために、良ければ下記質問に補足して頂けますか。
（小さな疑問・確認）
（１）本日契約成立の株の売買が1個=1銘柄？
（２）・・の場合→売買手数料3150円です。＝（後記から、株数Ｘ株価＝300万円までの制約があるのでは？以内なら－－＞3150円　
（３）複数売買の場合＝複数銘柄？を同一日売りまたは買の場合？買い＋売りも合算
（４）→売買手数料は3150円を分割します。＝Ａ銘柄Ｘ株価Ｃ＋Ｂ銘柄Ｘ株価Ｄ＜＝３００万円の時、どう分割する？
（５）（機械的にセルの数だけ均等分割可。＝セルとは何ですか。エクセルのセル？。入力行のこと？後記ではせるは列の意味らしいが。
（６）1日が終了し金額確定してから＝なぜ金額が市場が閉まるまで確定しない？売却価格、購入価格の通知は即座ではないですか？
（７）その金額で＝なんの金額？計算式は？
（８）計算式を上書き訂正します。＝なぜ計算式を上書きする必要あり？どのようにする？
（９）別案として契約金額＝契約金額とは、売却や買入れ金額のこと？
（１０）・・に応じて按分出来れば＝銘柄ごとに、株買い入れ、売却価格で比例按分？
（１１）過去から未来に渡る売買帳です。＝売買の日々今後
記録が増える。
（１２）Eの支払金額欄とＪの売上金額欄に前もって数式
を入れて置きたいのです。＝計算式・関数式を入れておきたい
ということ。もっともでしょう。
（１３）取引証券会社は１社で、手数料計算の基礎となる、契約金額は、売り金額だけで計算ですか、買い金額も合算で計算ですか。
（１４）ただし手数料は1日の契約合計300万円ごとに3150円となる。300万＝3150円、301万＝6300円、601万＝9450円・・
式は=IF(金額<300,3150,roundup(金額/300,0）*3150)
<帳簿の現状データ項目>
Ａ列＝空き？
Ｂ列＝株購入年月日　
Ｃ列＝買い株数　
Ｄ　＝買い株１株単価
Ｅ　＝支払金額（Ｃ＊Ｄ＋手数料？）
　　　手数料含む　
Ｆ　＝銘柄　
Ｇ　＝株売却年月日
Ｈ　＝売却株数
Ｉ　＝売り株一株単価
Ｊ　＝売却金額（Ｈ＊Ｉ＋手数料？）
　　　手数料含む　
Ｋ　＝売買損益（Ｊ－Ｅ。Ｃ＝Ｈでないとこう単純ではない？
　　　売買手数料を支払を含めて。
（疑問）
（１）ある日に同一銘柄２回購入や売却はあるか？
（２）ある日に購入したある名柄は別の日に、一部売却があり得るのでは。そうすると複雑になるような気がしますが。売却記録が２行以上必要では？どう対処されているのでしょう。

補足

下手な質問文で大変ご迷惑をおかけいたしました。
皆様にお詫びいたします。またご丁寧なご指摘には心より感謝申し上げます。
[帳簿]と記述したシートは[売買記録]シートです。売買の度にデータを入力してます。
（小さな疑問・確認）への回答
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列の未来の月日に計算式[売り単価ｘ株数-3150]が入っています。証券会社が按分した正しい手数料込みの金額を入力訂正します。
9）契約金額はミスです。約定金額に応じた按分比率で手数料が計算できたら良いなと言う願望です。
10）按分は売買それぞれの約定金額に応じた比率です。
11）何年間にも渡り行が増えてゆきます。先週末で686行記入されています。1年分をその年の申告に使います。
12）1日が終わると式は不要になり数値のみにします。
13）証券会社は1社です。手数料は売や買の区別無く約定金額（取引が成立した時の株数ｘ株価です）により按分されます。

疑問
１）同一銘柄の複数売買はあります。
２）一部売りの場合は過去に購入した株数を売り株数に合わせます。残りを日付を過去と同じにして新しい行に買いを新設します。

売買の途中で手数料がボックスレートのどの位置にあるかを確認しながら、売買をします。小口取引なので、2円の利食いを1000株すると2000円利益ですが手数料3150円だとどう決断するか。同日買った株が幾つかあり、1000株売っても手数料が500円ならその銘柄から逃げても良いなど決断の参考です。
[今日の取引]と言うシートがあります。
今年の各月毎の損益、過去10営業日の損益、をみれる様にしています。もし按分された手数料が売買毎に明示できた場合、さらに便利になると考えております。

よくわかりませんがエクセルのアドインは以下の物がONになっています。
Lookupウイザード
ソルバーアドイン
条件付き合計式ウイザード
分析ツール
分析ツール　VBA