Introduction
Ver. all 2013 2016 2019 365
エクセルで条件に応じた抽出を行うには、「オートフィルター機能」を使います。
しかし、この「オートフィルター機能」ですが、「平均より上」「平均より下」は簡単に抽出できますが、「以上」「以下」を抽出するにはひと手間かかります。
ちなみに、「以上」「以下」は対象となる数値を含み、「より上」や「より下」「未満」はその数値を含みません。
「100以上」と言われた場合は、100を含みますが、「100より上」と言われた場合は、100は含まないということですね。
フィルター機能で、抽出は簡単に終わらせるにゃ
平均より上やより下（未満）を抽出する方法
「以上」「以下」を基準とした抽出の前に、簡単に設定できる「平均より上」と「平均より下」の抽出方法を説明します。
「以上」「以下」だけの方法が知りたい方は、ここは飛ばしてください。
今回は、下の表の赤枠で囲んだ「合計」の平均より上のデータを抽出してみます。
まず、表内のどこかを選択しておきます。
その状態で、［ホーム］タブ → ［並べ替えとフィルター］をクリック。
一覧から、［フィルター］を選びます。
すると、全ての見出しの右側に▼が表示されます。
これが、オートフィルター機能が使用できる状態になったということ。
抽出の基準となるデータ列の▼をクリックしましょう。
平均を基準として抽出する場合は、［数値フィルター］に移動して、［平均より上］or［平均より下］を選びます。
今回は［平均より上］を選びます。
すると下のように簡単に抽出結果が表示されます。
左の行番号を見ると、番号が飛び飛びになっているのが分かりますね。
結果を保存しておきたい場合は、コピーしておきましょう。
結果の範囲を選択して、
［ホーム］タブ → ［コピー］をクリック。
貼り付け先を選択してから、［貼り付け］をクリック。
※コピー貼り付けの際は、ショートカットキーの「Ctrl」+「C」（コピー）と「Ctrl」+「V」（貼り付け）を使用するのがお勧めです。
今回は、３行下に貼り付けてみました。
設定を解除したい場合は、設定した見出しの▼をクリック → ［”〇〇”からフィルターをクリア］をクリックしてオフにします。
「オートフィルター機能」そのものを解除しても良いですね。
その場合は、［並べ替えとフィルター］ → ［フィルター］をクリックしてオフにします。
平均以上や以下を抽出する方法
では、「平均より上」ではなく「平均以上」のデータを抽出してみます。
その方法ですが、基準となる平均を算出している場合と、していない場合を別々で紹介します。
どちらとも「フィルターオプション」という機能を使用します。
平均を算出したセルがある場合
平均を算出している場合は、少し楽です。
下の表を例として説明します。「平均」は「20」となっていますね。
説明の前に、この表で先ほど説明したオートフィルター機能で「平均より上」を抽出すると、
下のように、「20」は含まれないので、「40」だけが抽出されます。
では、「20」以上を抽出してみましょう。
平均の数値をそのまま利用する方法
では、「フィルターオプション機能」を使用します。
先ほど説明した「オートフィルター」の応用という感じの機能です。自分で抽出条件を作成するのがポイントです。
まず、表全体の見出しをコピーして、他の場所に貼り付けます。
貼り付ける場所はどこでも良いのですが、抽出後の事を考えると、表の上か表の下が良いでしょう。
そして、抽出の対象となる見出しの下に「20以上」となる条件を作成します。
「個数」の下に「>=」を入力して平均の値を直接入力します。今回の場合は「20」になります。
条件が作成できたら、元の表内のどこかを選択しておきましょう。
選択したら、［データ］タブ → ［詳細設定］をクリック。
［リスト範囲］には自動で元となる表全体が選択されているはずです。
下の［検索条件範囲］をクリックして、先ほど作成した条件範囲全体を選択します。
※見出しも含めて選択します。
これで準備完了。［OK］をクリックすると抽出結果が表示されます。
ちゃんと「20以上」のデータが抽出されていますね。
平均のセルを使用する方法
「平均」のセル番地を使用することもできます。フィルターオプションで数値や不等号ではなく、「式」を利用することになります。
この方法からは、「フィルターオプション機能」でもあまり一般的ではない設定方法になります。
元の表と離れた場所に、「対象のセルが平均のセル以上かどうか」を判定する式を作成します。
今回の場合は下のような式になります。
=C3>=$C$7
言葉で説明すると、
という意味になります。
今回のようにフィルターオプションで「式」を利用する場合は幾つか決まりがあります。
- 最初に「=」を入力する
- 判定するセルには、表の最初のセルを参照する
- 比較対象となるセルは「$」を付けて絶対参照にする
※絶対参照の説明が必要な方は、こちらから。
「Enter」を押して確定すると、セルには「TRUE」か「FALSE」と表示されますが、気にする必要はありません。
では、フィルターオプション機能を使用します。
元の表内のどこかを選択しておき、［データ］タブ → ［詳細設定］をクリック。
［リスト範囲］には、自動で元となる表全体が選択されます。
その下の［検索条件範囲］に注意が必要です。
ここで、フィルターオプション機能で「式」を利用する際の決まりに一つ追加です。
- ［検索条件範囲］では、1つ上のセルも含んで選択する
データを入力していなくても、上のセルを見出しとして認識させるわけですね。
今回の場合は、下のように選択します。
※ちなみにこの場所、適当な見出しを付けておくのは問題ないのですが、元の表の見出しをコピーしておくと抽出できなくなってしまうので注意です。
［OK］をクリックすると抽出結果が表示されます。
平均を算出したセルがない場合
次に、下のように平均を算出していない表での抽出方法です。
先ほどと同じく「フィルターオプション機能」を使用します。さらに「AVERAGE関数」も使用します。
元の表から離れた場所に、下のように式を入力します。
=C3>=AVERAGE($C$3:$C$6)
言葉で説明すると、
という意味になります。
AVERAGE関数の引数（括弧の中）を絶対参照にするのを忘れずに。
※絶対参照の説明が必要な方は、こちらから。
「Enter」キーを押すと「FALSE」と表示されますが気にせずに。
元の表内のどこかを選択しておき、［データ］タブ → ［詳細設定］をクリック。
［検索条件範囲］では、一つ上のセルも含めて選択します。
［OK］をクリックすると抽出結果が表示されます。
まとめ
後半に説明したフィルターオプションで「式」を使用する方法はあまり一般的では無いかもしれません。
通常の使用法は下のリンク先で説明していますので、興味のある方は参考にしてください。
フィルターオプションを使用すると、いろんな抽出が可能になるにゃ