EXCELにおける集計の手法についてお聞かせください。


以下のような表があるとします。
A列:地区
B列:名前
C列:年齢

地区毎/年代毎の人数を集計したいものとします。
年代毎とは、年齢を10代未満,10代,20代,....,100以上の11通りに分けたものとします。
また、地区の種類については一定数のものとします(例えば東京23区のように)

『ピボットテーブルを使わず』に集計する手法をお聞かせください。
『作業用セル』もあまり使いたくありません。

当方の手法:
例えばある地区Aに対する40代の人数をカウントするのに、
=sum(if(表!A列=地区A,if(表!C列 >=40,1,0),0))
-sum(if(表!A列=地区A,if(表!C列 >=50,1,0),0))
と、配列を使ったのですが、再計算を行った場合に時間がかかりました。

回答の条件
  • 1人3回まで
  • 登録:2007/02/04 20:31:31
  • 終了:2007/02/05 17:15:41

ベストアンサー

id:castiron No.1

castiron回答回数418ベストアンサー獲得回数302007/02/04 20:47:32

ポイント100pt

=SUMPRODUCT((A1:A6="葛飾区")*(INT(C1:C6/10)=1))

"葛飾区":地名・地区名

INT(C1:C6/10)=1:1は10代,2に変えれば20代

どないだ

id:taisin0212

castiron様、ありがとうございます。

数学の授業で習った「行列の積」みたいなものをイメージすればよいのでしょうか。

私(達)の手法もそうなのですが、EXCELで配列を扱うと処理時間がかかりますか?

何はともあれ、SUMPRODUCT()を用いた方がスマートで解りやすいです。

ありがとうございます。

2007/02/04 21:30:32

その他の回答(2件)

id:castiron No.1

castiron回答回数418ベストアンサー獲得回数302007/02/04 20:47:32ここでベストアンサー

ポイント100pt

=SUMPRODUCT((A1:A6="葛飾区")*(INT(C1:C6/10)=1))

"葛飾区":地名・地区名

INT(C1:C6/10)=1:1は10代,2に変えれば20代

どないだ

id:taisin0212

castiron様、ありがとうございます。

数学の授業で習った「行列の積」みたいなものをイメージすればよいのでしょうか。

私(達)の手法もそうなのですが、EXCELで配列を扱うと処理時間がかかりますか?

何はともあれ、SUMPRODUCT()を用いた方がスマートで解りやすいです。

ありがとうございます。

2007/02/04 21:30:32
id:garyo No.2

garyo回答回数1782ベストアンサー獲得回数962007/02/04 20:50:58

ポイント20pt

この手の処理はEXCELはあまり得意ではないのでデータベース(例えばACCESS)の使用も検討されると良いとおもいます。

一度ACCESSにインポートしてクロス集計を行うのが一番早いと思います。

ACCESSにインポートする前にD列に「=INT(C列/10)」を追加しておくと良いでしょう。

A列を列、D列を行にクロス集計をかければ、1回で出てきます。

id:taisin0212

garyo様、ありがとうございます。

実は、ACCESSの食わず嫌いな上司からの相談事項を皆様にお願いした次第です。

私個人は、元表を加工せずACCESSに取り込み、

SELECT文を書くなかで加工処理を行いEXCELに書き出すのが大好きです。

2007/02/04 21:10:32
id:orion9 No.3

orion-p回答回数17ベストアンサー獲得回数02007/02/04 20:51:29

ポイント20pt

sumproduct関数はいかがでしょうか?

id:taisin0212

orion9様、ありがとうございます。

castiron様も挙げられた、sumproduct()ですね。

まったく勉強していなかった関数なので、これより勉強いたします。

2007/02/04 21:20:38
  • id:taisin0212
    castiron様、garyo様、orion9様、ありがとうございます。
    EXCELで配列を扱うと、データ件数によっては、処理時間が
    大分かかるのでしょうか?

    6500件のデータゆえなのか、PCのスペックの問題なのか、
    再計算中は「フリーズしてるの?」と声が漏れそうです。

    引き続き、皆様のご意見をお聞かせくださいませ。
  • id:castiron
    なんかA:A、C:Cなど列全体を指定すると計算してくれませんね。
    7000くらい乱数でデータ作ってやってみましたけど
    計算時間はほとんどかかりませんでしたよ?
    CPU:Pentium4 3.2Ghz
  • id:taisin0212
    列全体を指定するとエラーになりました。
    行列の積算と考えるとわからなくもないのですが、
    sum()では含めてもOKなのですから、統一性を考えると
    列全体の指定が出来てほしいと思います。

    わざわざデータを作っていただいてのテスト、ありがとうございます。
    現在、こちらのPCスペックの確認は出来ないのですが、
    Pen4:3.2Ghzよりロースペックです。メモリは1GBです。
  • id:taisin0212
    castiron様、ご報告申し上げます。
    本日、再度sumproduct()を記述したところ、
    再計算に時間がかかることなく、計算されました。
    元表のフィルタを外したのが良かったのでしょうか?

    現在sumproduct()をご案内いただいてますが、
    他の関数,ロジックで、ためになるものがありましたら
    お教えください。
  • id:garyo
    taisin0212様
    データ数が1万件を超えるようであればデータベースを推奨します。
    私も昔EXCELで出荷データ処理をやっていた時に、データ数が少ない時は問題なかったのですが、何千、何万を超えるあたりで処理時間がかかりすぎて、データベース(当時はdBaseでした)の勉強をしました。

    データ量がさらに増えるようであれば、上司殿にデータベースを使うよう進言したほうがいいと思います。
  • id:rikuzai
    基本的には縦系列に地区、横系列に年代のクロス集計ということですよね?
    データベースの項目ごとに条件を指定してカウントをするものに、
    DCOUNT関数もありますが、これは複雑な条件付けをする一項目に特化したものなので、集計にはあまり向きません。

    作業用セルが可なら軽い方法はいくらでもありますが、
    なしなら基本的に配列だけだと思います。

    ただ、SUMPRODUCTの使用についても、集計表の作り方によってはもっと数式を簡素化することができますよ。
  • id:castiron
    私は大学で画像処理やっているんですがせいぜい65000個のセルが最大だしだいたいプログラムの中で表示の関係もあってデータを間引きするのであまりデータベースや膨大な数のデータをあつかったことがないので実行速度とかの事は分かりかねます。
    (データベースは友達のプログラムを作ったときにさわった程度)
    一応「~をするには~」程度しか分かりません。
    おすすめの関数と言うことなので
    SUMIF,COUNTIF、VLOOKUP、HLOOKUP、INDEXなどをいろいろと計算の工夫する際に使う関数なので紹介します。(私はですけど)
    これ以上手間のかかるものはマクロを組んでます。
  • id:taisin0212
    castiron様、garyo様、orion9様、rikuzai様、
    この度はありがとうございました。

    検索サイトでsumproduct()を調べれば、色々なサイトが
    見つかりますので、勉強いたします。

    perlももう一度勉強した方が良いのかもしれません。

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません