Countifsとsumifの使い方について

質問

投資時期　　投資金額　　売却時期　　　売却方法　　　　　売却金額
A社　2004/1　　　100　　　　2006/1　　　　　IPO　　　　　　　　　　　500
B社　2004/12　　　200　　　　2007/4　　　　トレードセール　　　　　300
C社　2005/3　　　150　　　　2006/12　　　　トレードセール　　　　　100
D社　2005/8　　　100　　　　2008/11　　　　トレードセール　　　　　100
E社　2005/9　　　400　　　　2010/5　　　　トレードセール　　　　　　100
F社　2008/3　　　500　　　　2011/1　　　　トレードセール　　　　　　100
G社　2009/7　　　200　　　　2011/9　　　　トレードセール　　　　　　100
H社　2010/2　　　100　　　　2011/10　　　　IPO　　　　　　　　　　　　200
I社　2011/5　　　100　　　　2013/1　　　　トレードセール　　　　　　　200
J社　2012/4　　　100　　　　2013/9　　　　トレードセール　　　　　　200

上記の表があります。
2011年度（2010/4/1～2012/3/31）の間に売却した会社で、かつ、売却金額が投資金額を上回っている会社の社数を示したいのですが、どのような関数をかけばよろしいでしょうか。

また、売却金額が投資金額を上回っている場合、売却金額の合計と、いくら利益がでたのかを、年度別に知りたいです。

よろしくお願いします。

回答No.6

okMumin

No4,No5です。何度もすいません…

J列のプラス企業数、単なる「企業数」に訂正させてください。
K列2行目以下に追加で
=IF(J2="","",IF(I2>0,J2,""))
　1行目はJ列と文字列部分・列番号のみ異なり
「 ="プラス投資先数："&COUNTA(K2:K9)-COUNTBLANK(K2:K9) 」。

回答No.5

okMumin

No4です、早速すいませんが、訂正です。列A「投資先企業名」の仮定のつもりでした。

回答No.4

okMumin

他の方々の解答ともし重複していたらごめんなさい。それと、質問の内容を勘違いしていたらごめんなさい。この後、補足等を読ませて頂きます。
　若干、単語や数字・桁数が違うのをお許しください。QuickOfficeでExcelより機能が少ない(関数ISEVENやSUBTOTAL無し、ピボットテーブル無し、並替えや抽出も無い)のですが、挑戦しました。オリジナルデータは投資時期(列A)～売却方法(列F)、最大行数が9という仮定です。質問１：「一定期間内に利益を出した投資先の数」、質問２：「質問１より広範囲のデータを4月末で区切った年毎の売却額・利益額の各合計」と見なしています。売却額の位置により前処理が別れます。以下のロ）で上手くいかなければ、もう少し判断材料が必要ですが、QuickOfficeで容易に出来ない可能性もあり、目下パソコンにあまり触れない状況の私にはちゃんとお答え出来ないかもしれません。

前処理１：売却額を単独で列Eにする
　イ）投資時期と違うセル(各行1つ下)の場合：例えば最右の隣列のデータ先頭行以下に「=MOD(ROW(),2)」。結果の「1」or「0」を適宜入替えソートし範囲選択の単純なコピペ。済んだらこの式は不要。
　ロ）投資時期と同じセルの場合：先頭行(例はK列2行目)以下に(1)「=REPLACE(A2,IF(ISERROR(FIND("　",A2)),FIND(" ",A2),FIND("　",A2)),1," ")」。L列は(2)「=RIGHT(TRIM(K2),15)/1」。(1)の"　"内のスペース1つ目は全角1文字分、2つ目は半角1文字分、3つ目は1つ目と同じ。都合で変える。企業名と売却額との間に元から充分なスペースあれば(1)は不要、(2)「K」でなく「A」。(2)「15」はバラつく投資額の桁数と余裕の半角スペースを加えた適当な数。これも都合で。もし(2)の結果で部分的にエラーが出たら、エラー表示の行のA列の企業名と売却額との間にスペースを増やす。取出した数値を投資額のデータとして値コピーし使う。書式は計算出来るものに変える。

前処理２：売却時期のデータが文字列なら、加工(並替えする為)。この列を一旦、新規シート名「DB化」のD列2行目以下に別コピーしたとして、
「=DATE(LEFT('DB化'!D2,4),IF(LEN('DB化'!D2)=6,RIGHT('DB化'!D2,1),RIGHT('DB化'!D2,2)),1)」。書式を日付(yyyy/mm/dd)にして値コピーし、表のD列に貼付け後書式を日付(yyyy/mm)に。同シート上なら「'DB化'!」部分は削除。

前処理３：列G「損益」※単純な引き算なので、式はここでは省略。

　...ここから本題です。
　質問１について：売却時期を昇順に並べ、必要な期間をコピー。新たな表として別シートのセル番地A1に貼り付けたら、企業名順に並替え。
《新たに作る列と1行目のタイトル》
H；売却額累計(企業)　
I；損益累計(企業)　　
J；="プラス企業数："&COUNTA(J2:J9)-COUNTBLANK(J2:J9)
　※J列には答えが出る。Excelなら別の式もあり(他の幾つかの部分も)。文字列部分はセル書式のユーザー定義でも可。
《新たに作った列と2行目以下の式》
H; 2行目; =E2
H; 3行目以下; =IF(J2="",H2+E3,E3)
I; 2行目; =G2
I; 3行目以下; =IF(J2="",I2+G3,G3)
J; 2行目以下; =IF(A2=A3,"",A2)

質問２について：前処理をした最初の表を売却時期の昇順に並べる。
《新たに作る列と1行目のタイトル》
H；売却額累計
I；損益累計
J；年度区切
《新たに作った列と2行目以下の式》
H; 2行目; =E2
H; 3行目以下; =IF(J2="←",E3,H2+E3)
I; 2行目; =G2
I; 3行目以下; =IF(J2="←",G3,I2+G3)
J; 2行目以; =IF(YEAR(D2)=YEAR(D3),IF(D2=D3,"",IF(AND(MONTH(D2)<5,MONTH(D3)>4),"←","")),IF(AND(YEAR(D3)-YEAR(D2)=1,MONTH(D3)<5),"","←"))
　※H列とI列の各2行目は「質問１について」と同じ。

　以上ですが、どんなもんでしょうか？

回答No.3

bunjii

>また、売却金額が投資金額を上回っている場合、売却金額の合計と、いくら利益がでたのかを、年度別に知りたいです。
こちらの処理と前項の処理は同じです。
添付画像のようにシートを作成したとき下記のように関数式を入力します。
I2からK2セルを下へ必要数コピーすれば完了です。

I2=COUNTIFS(D:D,">="&H2,D:D,"<"&H3)
J2=COUNTIFS(D:D,">="&H2,D:D,"<"&H3,G:G,">0")
K2=SUMPRODUCT(F$2:F$1000,(D$2:D$1000>=H2)*1,(D$2:D$1000<H3)*1)

回答No.2

MarcoRossiItaly

No.1 です。連続ですみませんが、書き漏らしたことを補足します。

No.1 では年度を YEAR 関数で計算すると説明しましたが、これは厳密には年です。年別です。もしも年度というのが役所の会計年度（4 月～ 3 月）のことであるならば、次式で計算できます。

=year(c2)
↓
=(c2<>"")*(year(c2)-(month(c2)<=3))

回答No.1

MarcoRossiItaly

まず何よりも先に、F 列などに、「利益（課税前）」として「=e2-b2」を計算してください。その後、下の処理に進みます。

＞2011年度（2010/4/1～2012/3/31）の間に売却した……会社の社数を……
＞また、……売却金額の合……年度別に……

いずれも、ピボットテーブルを使います。

ピボットテーブルレポートをフィルタするまでもなく年度別で表示されているものとするには、あらかじめ年度を算出する列を用意しておきます。日付がシリアル値である場合、「=year(c2)」という数式で計算します。

または、いずれも、個数は COUNTIF か COUNTIFS、合計は SUMIF か SUMIFS 関数で計算することも可能です。

関数の場合、会社数なら「=countif(f:f,">0")」という数式など。年度別については、日付がシリアル値になっている場合、「=sumifs(f:f,c:c,">=2013/4/1",c:c,"<2014/3/31")」といった感じで計算できます。シリアル値ではなく文字列である場合、文字列の生成規則によって適切な数式を考えてください。