Excel(エクセル)で平均以上、平均以下を抽出する2つの方法

スポンサーリンク
スポンサーリンク
スポンサーリンク

Introduction

Ver. all 2013 2016 2019 365

エクセルで条件に応じた抽出を行うには、「オートフィルター機能」を使います。

しかし、この「オートフィルター機能」ですが、「平均より上」「平均より下」は簡単に抽出できますが、「以上」「以下」を抽出するにはひと手間かかります。

ちなみに、「以上」「以下」は対象となる数値を含み、「より上」や「より下」「未満」はその数値を含みません。

「100以上」と言われた場合は、100を含みますが、「100より上」と言われた場合は、100は含まないということですね。

 

フィルター機能で、抽出は簡単に終わらせるにゃ

スポンサーリンク
スポンサーリンク

平均より上やより下(未満)を抽出する方法

「以上」「以下」を基準とした抽出の前に、簡単に設定できる「平均より上」と「平均より下」の抽出方法を説明します。

「以上」「以下」だけの方法が知りたい方は、ここは飛ばしてください。

 

今回は、下の表の赤枠で囲んだ「合計」の平均より上のデータを抽出してみます。

まず、表内のどこかを選択しておきます

その状態で、[ホーム]タブ → [並べ替えとフィルター]をクリック

一覧から、[フィルター]を選びます

すると、全ての見出しの右側に▼が表示されます。

これが、オートフィルター機能が使用できる状態になったということ。

抽出の基準となるデータ列の▼をクリックしましょう。

平均を基準として抽出する場合は、[数値フィルター]に移動して、[平均より上]or[平均より下]を選びます

今回は[平均より上]を選びます。

すると下のように簡単に抽出結果が表示されます。

左の行番号を見ると、番号が飛び飛びになっているのが分かりますね。

結果を保存しておきたい場合は、コピーしておきましょう。

結果の範囲を選択して

[ホーム]タブ → [コピー]をクリック

貼り付け先を選択してから、[貼り付け]をクリック

※コピー貼り付けの際は、ショートカットキーの「Ctrl」+「C」(コピー)と「Ctrl」+「V」(貼り付け)を使用するのがお勧めです。

今回は、3行下に貼り付けてみました。

 


設定を解除したい場合は、設定した見出しの▼をクリック → [”〇〇”からフィルターをクリア]をクリックしてオフにします。

「オートフィルター機能」そのものを解除しても良いですね。

その場合は、[並べ替えとフィルター] → [フィルター]をクリックしてオフにします。

スポンサーリンク
スポンサーリンク

平均以上や以下を抽出する方法

では、「平均より上」ではなく「平均以上」のデータを抽出してみます。

その方法ですが、基準となる平均を算出している場合と、していない場合を別々で紹介します。

どちらとも「フィルターオプション」という機能を使用します

平均を算出したセルがある場合

平均を算出している場合は、少し楽です。

下の表を例として説明します。「平均」は「20」となっていますね。

説明の前に、この表で先ほど説明したオートフィルター機能で「平均より上」を抽出すると、

下のように、「20」は含まれないので、「40」だけが抽出されます。

 

では、「20」以上を抽出してみましょう。

平均の数値をそのまま利用する方法

では、「フィルターオプション機能」を使用します。

先ほど説明した「オートフィルター」の応用という感じの機能です。自分で抽出条件を作成するのがポイントです。

 

まず、表全体の見出しをコピーして、他の場所に貼り付けます

貼り付ける場所はどこでも良いのですが、抽出後の事を考えると、表の上か表の下が良いでしょう。

そして、抽出の対象となる見出しの下に「20以上」となる条件を作成します。

「個数」の下に「>=」を入力して平均の値を直接入力します。今回の場合は「20」になります。

条件が作成できたら、元の表内のどこかを選択しておきましょう

選択したら、[データ]タブ → [詳細設定]をクリック

[リスト範囲]には自動で元となる表全体が選択されているはずです。

下の[検索条件範囲]をクリックして、先ほど作成した条件範囲全体を選択します
※見出しも含めて選択します。

これで準備完了。[OK]をクリックすると抽出結果が表示されます。

ちゃんと「20以上」のデータが抽出されていますね。

平均のセルを使用する方法

「平均」のセル番地を使用することもできます。フィルターオプションで数値や不等号ではなく、「式」を利用することになります。

この方法からは、「フィルターオプション機能」でもあまり一般的ではない設定方法になります。

 

元の表と離れた場所に、「対象のセルが平均のセル以上かどうか」を判定する式を作成します。

今回の場合は下のような式になります。

=C3>=$C$7

言葉で説明すると、

「C3」が「C7」以上

という意味になります。

今回のようにフィルターオプションで「式」を利用する場合は幾つか決まりがあります。

  • 最初に「=」を入力する
  • 判定するセルには、表の最初のセルを参照する
  • 比較対象となるセルは「$」を付けて絶対参照にする

※絶対参照の説明が必要な方は、こちらから。

「Enter」を押して確定すると、セルには「TRUE」か「FALSE」と表示されますが、気にする必要はありません。

では、フィルターオプション機能を使用します。

元の表内のどこかを選択しておき、[データ]タブ → [詳細設定]をクリック

[リスト範囲]には、自動で元となる表全体が選択されます。

その下の[検索条件範囲]に注意が必要です。

ここで、フィルターオプション機能で「式」を利用する際の決まりに一つ追加です。

  • [検索条件範囲]では、1つ上のセルも含んで選択する

データを入力していなくても、上のセルを見出しとして認識させるわけですね。

今回の場合は、下のように選択します。

※ちなみにこの場所、適当な見出しを付けておくのは問題ないのですが、元の表の見出しをコピーしておくと抽出できなくなってしまうので注意です。

 

[OK]をクリックすると抽出結果が表示されます。

平均を算出したセルがない場合

次に、下のように平均を算出していない表での抽出方法です。

先ほどと同じく「フィルターオプション機能」を使用します。さらに「AVERAGE関数」も使用します。

 

元の表から離れた場所に、下のように式を入力します。

=C3>=AVERAGE($C$3:$C$6)

言葉で説明すると、

「C3」が「C3からC6」の平均以上

という意味になります。

AVERAGE関数の引数(括弧の中)を絶対参照にするのを忘れずに。
※絶対参照の説明が必要な方は、こちらから。

「Enter」キーを押すと「FALSE」と表示されますが気にせずに。

元の表内のどこかを選択しておき、[データ]タブ → [詳細設定]をクリック。

[検索条件範囲]では、一つ上のセルも含めて選択します。

[OK]をクリックすると抽出結果が表示されます。

まとめ

後半に説明したフィルターオプションで「式」を使用する方法はあまり一般的では無いかもしれません。

通常の使用法は下のリンク先で説明していますので、興味のある方は参考にしてください。

Excel(エクセル)のフィルター機能で自分で検索条件を作成する方法|フィルターオプション機能
Excel(エクセル)のフィルター機能で、3つ以上の検索条件を設定したり、複雑な検索条件を作成するにはフィルターオプション機能を使用します。

 

フィルターオプションを使用すると、いろんな抽出が可能になるにゃ