複雑な条件のあるデータ抽出の方法

質問

例えば下記のようなデータから条件に合ったデータのみを
抽出する方法はあるんでしょうか。抽出されたデータは別
シートにて作成するものとします。

社員No　　部署No　　等級　　年齢　　給与
001　　　　　　10　　　　1　　　　21　　　A
002　　　　　　11　　　　2　　　　22　　　B
003　　　　　　11　　　　1　　　　18　　　B
004　　　　　　11　　　　2　　　　22　　　C
005　　　　　　10　　　　4　　　　40　　　E

例えば部署No=11かつ等級=2かつ年齢=22の人数（1)と
給与の平均値(2)を求める式です。
この場合(1)は2、(2)は（B+C)/2となるのですが・・・。
人事評価の参考資料として作成したいと考えています。
お忙しいところ申し訳ありませんが、どなたかご教示
下さい。

回答No.1

harukabcde · ベストアンサー

(B+C)/2
という表示は、できないでしょうね。

せめて給与が数値（実際の給与額とか）なら可能だと思います。

給与が数値なら
2行目から6行目にデータがあるとして

=SUMPRODUCT((B2:B6=11)*(C2:C6=2)*(D2:D6=22)*1)
で、人数が出ます

=SUMPRODUCT((B2:B6=11)*(C2:C6=2)*(D2:D6=22)*(E2:E6))
で、給与総額が出ます

お礼

ありがとうございます。人数を求めることはできましたが、給与総額を求めると#N/A表示になります。
どうしたらよろしいのでしょうか？

回答No.8

harukabcde

>ありがとうございます。人数を求めることはできましたが、給与総額を求めると#N/A表示になります。
どうしたらよろしいのでしょうか
---Ｅ列は、数値ですか？
数字に見えても文字の場合もあります。

念のため、上書きで打ち直してみてください

お礼

ありがとうございます。ちゃんと確認しましたら、できました。
ヘルプを読んでもよく分かりませんでしたが、やってみるとちゃんと抽出できました。とても便利な機能ですね！
本当に助かりました、ありがとうございました。

回答No.7

tresbien

N05です。
補足します。ある条件のデータのみを別シートに抽出して分析・・・ということですので
ピボットテーブルを作成してからテーブルの中のセルをダブルクリックしますと詳細データが別のシートに書き出されます。
これから個数、平均などを分析することも出来ます。
皆さん関数お得意だから、でも質問者さんの表はシンプルなので簡単な方法をお勧めします。

お礼

分析までは今のところ考えていませんが、データが簡単に
更新できるので、ピボットは使えますね。
ありがとうございます。

回答No.6

macchan1

ご質問の主旨は該当データを他シートに抽出したいということでしょうか？
この場合は、配列数式を利用すれば関数でも対応できます。

データの個数や平均値を他のシートに表示したい場合は＃1の回答のように（数式にシート名を追加する）SUMPRODUCT関数や配列数式を使用します。

平均が数値で入力されていない平均ですが、そのままではできません。例えば最も数が多いものを出すことはできますが、正確では無いと思います。
このような場合は、A,Bの評価を数値に換算してそれをもう一度評価に戻すことはできます。

ご希望の処理がいまひとつ不明です。また、皆さんの回答で不都合なことやうまくいかないことがあれば返答するようにして下さい。

補足

説明不足で申し訳ありません。給与のAやBは実際の数値
が入っています。おっしゃる通り＃1さんのSUMPRODUCT関数を使って作成したいと考えています。

回答No.5

tresbien

給与欄には給与の数値が入力されるのでしょう。
もし給与欄にAB・・と入力されているのでしたら、別に給与コード表でもあるのでしょうからVLOOKUP関数か何かで隣の列に実際の給与が入力されるのでしょう。

それなら、ピボットテーブルが良いです。
行フィールドには部署と等級、列フィールドには年齢のフィールド名をドラッグして、
データ範囲（計算フィールド）には給与を2回ドラッグし、フィールド設定で一方を個数もう一方を平均にすればいいです。

回答No.4

noname#109516

結果を自動的に別シートに作成するにはVBAを使う必要があると思いますが、この位であればオートフィルター機能を使用するのが一番手っ取り早いと思います。
(1)表の中でデータのあるセルをクリックし「データ」→「フィルタ」→「オートフィルタ」で項目名の行の項目の右横に▼マークが表示されます。
(2)▼マークをクリックすると抽出するデータを選択できます。オプションを使用すると以上・以下等抽出条件を変えることも出来ます。
(3)計算式を設定します。
給与欄の平均値を出すセルに「=subtotal(1,データの行の範囲)」
(4)▼マークをクリックし条件を設定してください。抽出された平均値が出ているはずです。
(5)どうしても他のシートに結果を残したいときは。抽出後の表全体を選択しコピーし他のシートに貼り付けて下さい。(このとき計算式はコピーされません)

このやり方のポイントはオートフィルタとsubtotal関数を利用することです。ヘルプ等を活用しトライして下さい

お礼

ありがとうございます。確認できました。
しかしながら別シートにて一覧で確認できるような
ものを作成できればと考えています。

回答No.3

kouzi_kouji

Ｎｏ２の補足ですが、給与はＡＢＣＥは、数値として入っていないと計算出来ません。
SUBTOTAL関数は、給与の列の下に配置して、
オートフィルターで、各列に抽出条件を設定して下さい。

回答No.2

kouzi_kouji

オートフィルターを用いて条件を指定して
SUBTOTAL関数の集計方法を、変えると人数を出したり平均を出したりすることが出来ます。
（１）は、集計方法を　２にすれば出来ますし、
（２）は、集計方法を　１にすれば計算出来ます。

SUBTOTAL(集計方法, 範囲1, 範囲2, ...)