スポンサーリンク

Excelで範囲指定し絞り込みするフィルター機能と、SUBTOTAL関数での計算をご紹介


エクセルシート上の欲しい情報を取り出す際には、手間をかけずに範囲指定や絞り込みをしたいですよね。Excelには絞りこみを効率的に行える仕組みや関数が存在しており、合計を求めるSUM関数や最大値・最小値・標準偏差の計算の代用にもなるものとフィルタと組み合わせる方法もあります。今回は、ExcelやGoogleスプレッドシートにおける範囲の絞りこみ機能であるフィルターと、エクセルならではのSUBTOTAL関数についてご紹介します。



◎エクセルのfilter関数と組み合わせ、SUBTOTAL関数の使い方


エクセルのfilter関数と合わせて利用したいのが、SUBTOTAL関数です。簡単に説明すると、Excelであてはまる範囲を指定し集計値を算出する関数であり、Googleスプレッドシートのように条件に該当するものを取り出すわけではありません。しかし、Excelでの範囲を指定の合計値や平均をフィルター機能との併用で求められるので、一緒に押さえておきましょう。


ExcelにおけるSUBTOTAL関数の構文は、『=SUBTOTAL(集計関数,参照部分)』です。参照部分はセル範囲を最大で250個ほどまで指定できます。複数必要な場合には『,』を含めて直後に別の参照部分を書き加えましょう。集計関数は11種類が設定されており、Excelで平均値を求める『AVERAGE』・個数を求める『COUNT』『COUNTA』・和算積算の『SUM』関数『PRODUCT』・エクセルで不偏標準偏差や標本標準偏差を求める『STDEV』『STDEVP』・不偏分散や標本分散を求める『VAR』『VARP』などです。これらは、関数の名称を直接入力するのではなく、与えられている番号を打ち込みます。非表示の値を含める場合には1~11、非表示の値を含めない場合には101~111です。それぞれの数字が該当する集計関数は以下の通りとなっています。



◎Excelでの範囲指定した部分の絞り込みができるフィルタ機能、Googleスプレッドシートの関数との違いは?


ExcelおよびGoogleスプレッドシートにおける共通点は、フィルタ機能を直接操作して絞り込みができる点です。以下で詳しく説明しますが、本来のシート上には存在していないフィルタ機能を全体あるいは特定の範囲を設定して生じさせます。一方、両者が異なるのは、関数で絞り込みができるかです。


Googleスプレッドシートでの絞り込みは、取り出したい部分にFilter関数を入力することで該当するデータを抜き出せます。Excelにおける絞り込みでは、同じように直接値を出す機能はありません。上記で説明していますが、絞り込み機能と組み合わせて計算をする方法は存在しています。Googleスプレッドシートでの絞り込みについてはこちらの記事で詳しく触れておりますので、参考にしていただければ幸いです。

はじめてのプログラミングコース



◎Excelやスプレッドシートでの範囲の絞りこみ機能、フィルターの使い方について



●エクセルシート上にフィルター機能の▼ボタンを表示させる方法は?


Excelで範囲を設定し、情報の絞り込みを行うには、フィルター機能を意味する「▼」のボタンを出現させる必要があります。エクセルシートにあるデータの並べ替えもできますから、表示方法をしっかりと押さえておきましょう。


①Excelシートの上側にある、緑色の部分のすぐ下のうち、『ホーム』のボタンをクリックします。左側にバインダーの絵と『貼り付け』という文字があれば正解です。そこから右側に移動してみまましょう。エクセルシートのフィルター機能を表示させるには、右端からやや戻ったところにある『並べ替えとフィルター』を使います。その部分を押したときに出てくるのが、1枚目の画像で表示されているものです。


②この機能を使うためには、先にエクセルシート上でフィルター機能を使う範囲を指定する必要があります。数字や文字列が入力されている全ての列に対して連動させて絞り込み操作を行いたい場合には、どれか一つのセルをクリックすれば大丈夫です。しかし、Excelの特定の列のみに対して絞り込みや並べ替えをしたい場合もあるかもしれません。その際には、先に作業する範囲を設定しておきましょう。


③マウスまたはパソコンキーボードよりも手前側にあるタッチパッドを使った操作です。開始したい部分に矢印マークを動かし(マウスを前後左右に動かすあるいはタッチパッドの下以外の部分を触ることで動かせます)、左クリック(パッドなら左下)を押し続けましょう。その状態でタッチパッドの中央部またはマウスを動かすことで、指定された範囲の色が変わります。【2枚目の画像↓】


④その状態で改めて 『並べ替えとフィルター』をクリックしましょう。中央部下側にある『フィルター(F)』を選択すると、3枚目の画像↑のようにフィルター機能が各列に付きました。なお、ここでその手前にある『昇順(S)』を押すとエクセル上で指定された数字なら小さい順に、文字列ならA~Z・あいうえお順に並びます。『降順(O)』を押すとその逆に、『ユーザー設定での並べ替え(U)』ではさらに細かい設定も可能です。エクセルでは絞り込みと同じくよくする作業ですから、一緒に押さえておきましょう。Excelでの数字や文字列の並び替えについてはこちらの記事で詳しく触れています。



○Excelでのフィルター機能を付ける際の注意点は?


エクセルシート上でフィルター機能を使う際の注意点は、操作できる範囲は縦向きつまり列のみであることです。行つまり横向きのデータを範囲としてフィルター機能を使おうとしても、4枚目の画像のようにすべての文字列や数字の横が▽で埋め尽くされてしまいます。Excelでのフィルター機能を付けるときには、絞り込みをするデータが縦向きに並んでいるかを調べておきましょう。横向きのデータを縦向きに変える方法は、全体をコピーし特殊な方法で貼り付ける流れです。貼り付けるセル番地にカーソルを置いたあとに、右クリックして『貼り付けのオプション』を探します。その下にある『行列を入れ替える(T)』(文字は表示されていないので、縦向きと横向きが変わっている絵を見つける)を選択しましょう。



エクセル上の対象範囲にフィルター機能が付けば、準備は完了です。様々な項目がある中で、絞り込みを行いたい要素の見出しに付いている▼マークを押しましょう。すると、5枚目の画像のように表示させる項目がでてきますので、絞り込むものだけを残して消したいもののチェックを外します。最後に下側にある『OK』のボタンを押せばExcelあるいはGoogleスプレッドシート上には該当範囲のうち絞り込みの条件にあてはまるもののみが表示されます。フィルタ機能の▽が漏斗型(科学実験で使う、液体と固体を分ける器具)になっているかで確かめても良いかもしれません。なお、複数の項目を使っての絞り込みの場合には、どうように別の列で残す項目を選びましょう。取り出したデータはコピーもできますので、別のセルやシートに貼り付けて改めてまとめるのもおすすめです。



○エクセルデータの絞り込み、フィルター機能を使って解除する方法


Excel上の情報で絞り込んだものを改めて見直したり、誤って非表示にした場合には解消したりしたい場合もあるかもしれません。その際には、『Ctrl』『Z』の同時押しで元の状態に戻すのも可能ですが、クリア機能を使えばもっと簡単に解除できます。上記でも紹介した 『並べ替えとフィルター』の項目を開けてみると、エクセルシート上で絞り込みをしている場合には下の『クリア(C)』の文字が出てきていることでしょう。(何も絞り込んでいない場合には文字が透明になっています。)その部分を押すと、元の絞り込み前の状態に戻ります。この『クリア(C)』を使う際には、Excel上で処理したすべての絞り込みの作業が解除される点に注意です。一つだけ戻る場合には 『Ctrl』『Z』の同時押しなどで対応する方が早いかもしれません。



◎まとめ


今回は、エクセルやGoogleスプレッドシートにおける範囲の絞りこみ機能である、 フィルタとSUBTOTAL関数についてご紹介しました。  フィルタ 機能によって情報を絞り込むことで見やすい資料になります。また、平均値・最大値・最小値・分散・標準偏差などの計算を代用する関数も合わせて使い、効率良くデータをまとめていきましょう。最後までお読みいただきありがとうございました。

コメント

タイトルとURLをコピーしました