エクセル関数で、年/月で集計したいのですが。。。

質問

いろいろ調べてみたのですが、上手く行く方法が見つからず、
どなたか助けてください。

添付画像のように、集計をしたいと考えています。
シート名：記録と集計　で2つのシートを使用しています。

人の種類のカウントまでは、
=SUMPRODUCT((記録!$I:$I<>"")/COUNTIF(記録!$I:$I,記録!$I:$I&""))
なんとかできているのですが、
こちらをさらに、年月で集計したいのですが。。。

よろしくお願い致します。

回答No.3

MackyNo1 · ベストアンサー

配列を使った数式を利用する場合に、例示の数式のように列全体を指定すると、再計算に時間がかかるので、シートの動きが重くなるなどのデメリットがありますので注意が必要です。

今回のケースでしたら（A2セルに文字列で年月が入力されている場合）B2セルに以下のようなMATCH関数を利用した数式で対応するほうが簡単かもしれません。

=SUMPRODUCT((MATCH(記録!$C$1:$C$1000&記録!$I$1:$I$1000,記録!$C$1:$C$1000&記録!$I$1:$I$1000,0)*(記録!$C$1:$C$1000=B$1)=ROW($1:$1000))*1)

お礼

とても、親切なお心遣いまで、ありがとうございます！

素敵です☆
できました（涙）！
ありがとうございます～♪
記録!$C$1:$C$1000のCをGに変えて、私の方でも再現できました☆
（添付画像が小さすぎてすみませんでした。）

実は他にも、数式をかなり使い込んで色々な操作をしてまして。。。
あえてデメリットのコメントを添えてくださり、感謝いたします！！
毎日増えていくデータなので、無限を想定しておりましたが、
5000くらいで区切るように、全ての数式を見直そうと思います！

とても助かりました！
ありがとうございました☆

回答No.5

esupuresso

＞今の人のカウントですと、全ての月でみた、人の種類3名になっているのですが、
2012/01は、1名となれば、いいのですが。。。

Sheet2のセルB2に　=COUNTIF(Sheet1!$G:$G,B$1)　で「1」と出ますよね？
この数式を右方向へコピーすると年月の集計は出来ますが・・・

＞人の種類のカウントまでは、
=SUMPRODUCT((記録!$I:$I<>"")/COUNTIF(記録!$I:$I,記録!$I:$I&""))
なんとかできているのですが・・・
例えば「田中」さんの数を調べたい場合　=COUNTIF(Sheet1!$I:$I,"田中")　とか、
「佐藤」さんでしたら　=COUNTIF(Sheet1!$I:$I,"佐藤")　の式でいいと思いますよ
他にもデータベース関数の「DCOUNTA」を使うとか・・・

テーブルが大きい場合の配列数式は結構メモリを食いますのでなるべく単純な式を使った方が作業的にもスムーズに進むと思いますけどねぇ・・・？

お礼

考えて頂いて、ありがとうございます！

　>Sheet2のセルB2に　=COUNTIF(Sheet1!$G:$G,B$1)　で「1」と出ますよね？
テストデータなので簡単なデータになっているのですが、実際は、毎日いろいろな人のデータが入ってくるデータなんです。。。
上記ですと、その月の件数はでますが、月に2回以上同じ人がいる事がぜんていなので、あの様な式になっています。
単に集計をしたいときは、ピボットで簡単にできるのですが、関数で組む必要がありまして。。。

　＞例えば「田中」さんの数を調べたい場合　=COUNTIF(Sheet1!$I:$I,"田中")
メンバーが特定されていなく、新規の人も随時増えていくのと、リアルタイムで集計させる必要がある為、
全てを自動集計させる式を必要としております。

そうですね！
私自信のメモリがたらないので、できれば、シンプルな式が嬉しいです♪

とても、心強い気持ちになりました！
ありがとうございました～☆

回答No.4

某HN クロメート(Chromate)（@CoalTar）

記録シートに作業列を作り
=TEXT(H2,"yyyymm;;")&I2
で同様に集計したら？

お礼

お考え頂きまして、ありがとうございます！！
スマートでとても良かったのですが、掲載の式に参照を新しく用意したさ行列に変えた所、
全体の種類の個数のカウントになっているので、
月ごとにするには、さらに、関数を足せばできそうですね～！！
参考になりました～！
ありがとうございました～☆

回答No.2

KURUMITO

回答Ｎｏ１です。失礼しました。
Ｂ2セルには次の式を入力します。

=SUMPRODUCT((INDEX(記録!$I:$I,MATCH(B$1,記録!$G:$G,0)):INDEX(記録!$I:$I,MATCH(B$1,記録!$G:$G,0)+COUNTIF(記録!$G:$G,B$1)-1)<>"")/COUNTIF(INDEX(記録!$I:$I,MATCH(B$1,記録!$G:$G,0)):INDEX(記録!$I:$I,MATCH(B$1,記録!$G:$G,0)+COUNTIF(記録!$G:$G,B$1)-1),INDEX(記録!$I:$I,MATCH(B$1,記録!$G:$G,0)):INDEX($I:$I,MATCH(B$1,記録!$G:$G,0)+COUNTIF(記録!$G:$G,B$1)-1)&""))

お礼

とんでもないです！！
私の説明が全く足りていなく、申し訳なかったです！

ありがとうございます～！！
すごい☆
最後のINDEXの$I:$Iに記録!をつけ、できました！！
式が長いというだけで、私のたらない脳がついていかなくて、くるくるしてしまいました♪
ものすごく参考になりました！
ありがとうございました～☆

回答No.1

KURUMITO

Ｂ３セルには次の式を入力して右横方向にドラッグコピーします。

=SUMIF(記録!$G:$G,B1,記録!$J:$J)

補足

早くにご回答、ありがとうございます。

ごめんなさい！
現在金額の合計ではなく、人のカウントの式の方で、悩んでおりまして、
今の人のカウントですと、全ての月でみた、人の種類3名になっているのですが、
2012/01は、1名となれば、いいのですが。。。
おそらく、この式の頭に、
=SUMPRODUCT((記録!$I:$I<>"")/COUNTIF(記録!$I:$I,記録!$I:$I&""))
　　　　　　　　↑　
さらに、関数の数式を足せば、いいのではと思い、色々試しているのですが。。。

説明が全く足りておらず、お手数おかけしごめんなさい。