【無料配信♪】Renta !全タテコミ作品第1話

関数に不慣れなためお伺いできればと存じます。

したいことは以下の通りです。
(1)収支(2)勝率(3)損益比率を出したいと思っています。
その際、参照のために呼び込むデータの行数は毎回決まっていないため、
最終行を指定せずに計算できる式を探しています。

※参照データを呼び込んだら、予め仕込んである関数によってそのときの最終行までの計算を行ってくれる式

ひとまず、添付画像にあるように
【I列】回収額をもとに(1)収支を出す計算
【J列】収支をもとに(2)勝率を出す計算
【J列】収支をもとに(3)損益比率を出す計算
の関数を作ってみたのですが、(参照する回収額の行数が毎回、決まっていないため)
収支、勝率、損益比率の式をその都度、変化する最終行までのデータをもとに計算を行うようにするにはどのように関数を変えればよいでしょうか。
なお、勝率については収支が0のときは勝ちに含めないため">1"としています。

それぞれ、式が成り立たないためとりあえずの範囲(3行目とか8行目とか)を入力しています。

【I列】 回収
【J列】 収支=I3-H3
【K列】勝率=COUNTIF(J$3:J3,">1")/COUNTA(J$3:J3)*100
※0は勝ちに含めません
【L列】損益比率
=AVERAGEIF(J$3:J8,">0")/-AVERAGEIF(J$3:J8,"<0")

ご査収いただけますと幸いです。

「【Excel】都度、変化する参照データを」の質問画像

A 回答 (3件)

他の回答者さんも述べておられますが、J列、K列、L列に予め数式を入れておいて、H列、I列に数値が入った段階でJ列、K列、L列が表示されるという方法で、ご質問者のご希望はかなうのではないかと思います。


元データが別ブックや別シートにあっても、このシートのH列・I列にそのデータをコピペするなどして使用するとするとすれば十分使えると思います。
また別ブックや別シートからのH列・I列読み込みやJ列、K列、L列への数式記述ををVBAにする手もあります。
私が考えるJ3、K3、L3に記述すべき数式を以下に示します。
J3=IF(COUNTBLANK(H3:I3)=0,I3-H3,"")
K3=IF(COUNTBLANK(H3:I3)=0,COUNTIF(J$3:J3,">0")/COUNTA(J$3:J3)*100,"")(ご質問者は"0を含まない"を">1"と表現しておられますが、"0を含まない"は">0"だと思います)
L3=IF(COUNTBLANK(H3:I3)=0,IFERROR(AVERAGEIF(J$3:J3,">0")/-AVERAGEIF(J$3:J3,"<0"),0),"")
「【Excel】都度、変化する参照データを」の回答画像3
    • good
    • 1
この回答へのお礼

ご回答ありがとうございます。ご記載いただいた関数で目的が叶いました。また、勝率の式についてもご指摘いただき感謝いたします。不慣れなため、図入りの丁寧なご説明がとてもわかり易かったです。お忙しい中、親切なご対応をありがとうございました…!

お礼日時:2020/09/19 01:28

こんにちは



損益比率以外の計算式は行数には関係のない式になっているので、あらかじめセットしておいても問題はないと思われます。
その際に、データの無い行にも計算結果が表示されるのが嫌でしょうから、例えばJ列なら
 =IF(H3,I3-H3,"")
のような式にして、あらかじめ十分下方の行までフィルコピーしておくことで、H列の値が存在しない行は空白表示になります。(K列も同様)

「損益比率」は何を計算したいのか(6回先までを含めた平均???)よくわかりませんが、現状の式が正しいのであれば、上記の考えをそのまま応用すればよろしいかと。
もしも、列全体の平均を求めたいのであるなら、範囲指定を列全体(J:J)にしておけば良いですが、その場合は、各行とも全て同じ値になるので、同じ内容を何度も表示する必要はなく、1か所に表示しておくだけで十分ですね。
    • good
    • 1
この回答へのお礼

返信が遅くなり申し訳ございません。ご回答ありがとうございます。教えていただいた方法で収支を出してみました、確かにこの方法は快適です。関数の基礎的なことがわかっていないため、丁寧なご説明が助かりました。ありがとうございます!

お礼日時:2020/09/19 00:38

データを呼び込むというのがどのような挙動かわかりませんが、範囲は予め多めにしておいても(100行とか)計算できるんじゃないですか?


それでは具合が悪いというなら、どのようにダメなのか具体的に書いてください。
    • good
    • 1
この回答へのお礼

返信が遅くなり申し訳ございません。ご回答ありがとうございます。

お礼日時:2020/09/19 00:32

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています


このQ&Aを見た人がよく見るQ&A

このカテゴリの人気Q&Aランキング