エクセルのデータをグループ化し、条件を設定し数を数えたい。

質問

下の表のようなエクセルのデータについてお聞きします。

番号　区分　　
1001 1
1001 2 　　　　
1002 1
1003 2
1004 1
1004 2
1004 2

(1)同じ「番号」を一つのグループと見る。
（上の表の場合、４グループとみる。）
(2)同じグループに「区分」2があるかどうかを調べ、
2があるグループの数を調べる。
（上の表の場合、3つと数える。）
(3)番号の数はたくさんある。「区分」は5個程度です。グループの数を数える場合、「区分」が条件となり、(2のみ・1または2)と条件が変わる場合がある。

このようなことできますか？どなたか教えてください。

回答No.5

fmajin · ベストアンサー

「条件が変わる場合がある」とすると、私もピボットテーブルを使用することをオススメしますが、ちょっと補足しておきますね。

エクセルのバージョンが不明ですが、元データが入力された範囲にセルポインタを置いて、［データ］－［ピボットテーブルとピボットグラフ　レポート］コマンドを実行し、即座に［完了］ボタンをクリックすると、即座に新シートに空のピボットテーブルが作成されるはずです。

で、元の表にあった「番号」と「区分」のフィールド名が表示されていると思うので、そこから「番号」を表の左の「行のフィールド」までドラッグ&ドロップし、「区分」を表の上の「列のフィールド」までドラッグします。すると、全「番号」と全「区分」のクロス集計表ができあがります（表の右下の「データアイテム」部分はなくても大丈夫です）。

そして、表の上のほうに［区分｜▼］のようなボタンが表示されていると思うので、この［▼］ボタンをクリックして、条件に指定しないチェックボックスをオフにしましょう。たとえば「区分」が「2」の条件にしたいならば、「2」のチェックボックスがオンになった状態にします。そうすると、区分が「2」だけのグループが一度だけ表示されるようになります。

この状態のグループ数を数えればいいのですが、グループ数が多い場合は、A2セルに「=COUNT(A3:A65536)」とでも入力すれば、グループ数を数えることができます。これは、「番号」が数値になっていることを想定しているので、数値のみを数えるCOUNTで件数を数えています。
もし、「番号」が文字列の場合は、「COUNTA」関数を利用することになりますが、その場合はピボットテーブルに含まれる項目名も数えられることになるので、参照範囲内に含まれる項目名を差し引くように「=COUNTA(A3:A65536)-2」のような数式にします。

条件を変更したいときは、［区分｜▼］ボタンから条件を変更すればいいので、柔軟に運用できるのではないでしょうか。「1または2」ならば、両方ともチェックボックスをオンにすれば大丈夫です。
お試しください。

お礼

ありがとうございます。データの行数が１万行を超えていたので処理に困っていました。短時間で結果が分かり助かりました。
ピボットテーブルは難しいものと決めつけ避けていましたが、もう少し勉強し使いこなしたいと思います。

回答No.4

imogasi

例データ
A1:B1５
番号 区分
A列　　B列　　C列　　D列　　E列
1001 1 1001 1
1001 2 1 1003 1
1002 1 1005 1
1003 2 2 1004 1
1004 1 1001 0
1005 2 3 1003 0
1004 2 4 1001 0
1001 2 5 1006 1
1003 2 6 1005 0
1007 1
1001 2 7
1007 3
1006 2 8
1005 2 9
5
C列C2に
=IF(B2=2,MAX($C$1:C1)+1,"")　といれC15まで式複写
結果上記C列の通り。
D列D2に
=INDEX($A$2:$B$15,MATCH(ROW()-1,$C$2:$C$15,0),1)
と入れてD10まで式複写。
結果上記D列の通り。
E列E2に
=IF(COUNTIF($D$2:D2,D2)=1,1,0)　といれE10まで式を複写
結果上記E列の通り
E16に=SUM(E2:E15)と入れる。
結果は５．
imogasi方式の応用です。

お礼

ありがとうございます。今回は結果を出す時間に制限がありましたので、ピボットテーブルを使いました。後から試してみてなるほどと感心しました。

回答No.3

banker_U

簡単そうに見えて、重複行の処理が案外難しいですよね。
私の考えた方法は、２つ。

１つ目：
　No2さんの方法に補足して、出来上がったピボットテーブルをさらに縦にカウントする。意味わかるかなあ？

２つ目：
　補助列に条件判定式を入れておきます。
　例えば、　=IF(B2=2,1,0)　とか　=IF(OR(B2=1,B2=2),1,0)
　補助列末尾に　=SUBTOTAL(9,C2:C100) (100は適当に換える)
　カーソルをメニューからデータ－フィルタ－フィルタオプションの設定を選択
　抽出先は選択範囲内、リスト範囲はA,B列、重複するレコードは無視するにチェックを入れて、OK
　これで、SUBTOTAL式を入れたところに求める数値が出ているはずです。
　ちなみに、上記フィルタのところで、検索範囲の設定を使いこなすことができるのなら、補助列は使わなくてもやる方法はあります。これは考えてみてください。

お礼

ありがとうございます。ピボットテーブルは便利な機能ですね。

回答No.2

noname#95859

グループの数を調べるに直接は応えていませんが、簡単にやるとしたら、ピボットテーブルを使うのも手です。

Count 　　 　
of 番号 区分 Grand Total
番号 1 2
1001 1 1 2
1002 1 - 1
1003 - 1 1
1004 1 2 3
Grand Total 3 4 7

グループは左端でわかります。区分１に属する数、
区分２に属する数が簡単にわかります。
参考にして下さい。

お礼

ありがとうございます。ピボットテーブルを使いうまく処理ができました。

回答No.1

AirRock_Fan

複数の条件に合致するデータの個数を探す関数としてDCOUNTAを使用してください。

以下のURLが最も使い方としてわかりやすいようです。

参考URL：

http://dreamy.boy.jp/kan13.htm

お礼

ありがとうございます。ＤＣＯＵＮＴＡ関数で「同一の番号を持つ複数のデータ行を１つと見て、条件に当てはまる数を数える」ことをどのようにするのか分からなかったので、ピボットテーブルを使いました。