人気ブログランキング | 話題のタグを見る

Excelワザ_重複データを除いたデータの数をカウント

さて、ウォーミングアップの次は、
Mさんから頂いた課題。
「データがどんどん増えていき、
重複する町内名が出てくるが、
重複データを除いた実際の町内の数はいくつか?」
という課題。
昨日のブログの可変する範囲名のつけ方。
一昨日のブログのSUMPRODUCT関数を使った
(Sumproductの補足も)
複数条件のデータの取り出し。
これらを使って、やってみましょう。

その前に、
いろいろなやり方を検証してみよう。
Excelワザ_重複データを除いたデータの数をカウント_b0186959_9394871.jpg


準備として、
A列の番号は、自働的にふれるようにしておく。
セルA2に「1
セルA3には「=IF(B3="","",A2+1)
という式で、連番をつけておく。

簡単なものから
<方法1>
1.別シートに必要な列だけ(A列とB列)コピー。
2. データ→重複の削除
3. 町内名にチェック→OK
Excelワザ_重複データを除いたデータの数をカウント_b0186959_9523980.jpg

Excelワザ_重複データを除いたデータの数をカウント_b0186959_10393473.jpg

これで、重複を除いた町内は「7」ということが分かる。

<方法2>
ピボットテーブルを使う。
1.挿入→ピボットテーブル
→新しいシート
行エリアに町内名
値エリアに町内名(会員名でも可)
Excelワザ_重複データを除いたデータの数をカウント_b0186959_1041746.jpg

行ラベルに表示された町内名を
COUNTA関数で出すと、「7」
データの総数は「25」ということが分かる。
それぞれの町内の数もわかる。

さて、方法1,2どちらも
データが変化しない場合です。
データが増えたり、減ったりする場合は、
いちいち範囲を選択しなおしたり、
データ更新をしないとだめです。
そこで、OFFSET関数。
そして、SUMPRODUCT関数。

<方法3>
1.町内名を名前定義。
Excelワザ_重複データを除いたデータの数をカウント_b0186959_10162064.jpg

2.例、セルG2に式
=SUMPRODUCT(1/COUNTIF(町内名,町内名))
Excelワザ_重複データを除いたデータの数をカウント_b0186959_1029796.jpg

この式で、町内名が増減しても
一意の町内の数が出てくれます。
例)№7~下のデータを削除すると、
Excelワザ_重複データを除いたデータの数をカウント_b0186959_10434425.jpg


by school-pc | 2015-01-30 09:39 | Comments(0)