「フィルター」カテゴリーアーカイブ

フィルターオプション 数式

フィルターオプションフィルターオプション ワイルドカード等、とやりました。今回は検索条件に数式を使います。

これまでも=”=みかん”などと式を使ってきましたがこれらは比較演算子という分類になります。
= (等号) 左辺と右辺が等しい
> (より大記号) 左辺が右辺よりも大きい
< (より小記号) 左辺が右辺よりも小さい
>= (より大か等しい記号) 左辺が右辺以上である
<= (より小か等しい記号) 左辺が右辺以下である
<> (不等号) 左辺と右辺が等しくない
これらになります。IF関数の論理式に使われますね。

今回は比較演算子だけではなくほかの計算式でフィルターオプションの検索条件を作ります。

以下のような表で計が平均以上の商品をフィルターオプションで抽出したいと思います。

F列からI列までに検索用の見出し、L列からO列までは抽出用の見出しが用意されています。まず、計のの平均を元の表の最下行28行目に算出しておきます。

これでセルD81に計の平均が出ましたので抽出見出列 計の下に=”>=d81″でうまくいきそうです。

ところがこれがうまくいきません。セル参照がうまくいっていないようです。=”>=d81″のd81の代わりに481という数値ならうまくいきます。

セル参照が入っている場合やプラスやマイナス等の計算式もうまくいきません。

  • 比較演算子以外の計算式を使う場合はダブルクォーテーションで括らないこと
  • 検索用の見出しは元の表の見出しを使わないこと
  • 計算式はTRUEまたはFALSEを返すこと
  • 左辺は元の表の見出しを除く一番上の行を使うこと

というような約束事があります。

セルJ2に=(D2>=$D$28)と入力します。これはイコールの右辺がTRUEかFALSEを返す(TRUEとFALSEを参照してみてください)式になっています。元の表のD列に対して評価しますので初めのD2は相対参照になりD28は絶対参照になります。絶対参照と相対参照参照

更にセルJ1には見出しは特に必要ありませんが条件式とかにしておくことが多いようです。

もちろんこれで終了でもいいのですが28行目に平均値を出していますが、数式の中で平均値を使うことも可能です。

28行目の平均を削除します。セルJ2の数式の$d$28の代わりにAVERAGE関数を使い平均を計算します。
=(D2>=AVERAGE($D$2:$D$27))
引数は元の表のすべての行に対してなので絶対参照にしてください。

他の行についても計算式を使うならJ列に追記してください。

少しややこしくなりますが、ここでつかる計算式はTRUEかFALSEをかえせばいいのですから、複数の条件を指定することが可能です。

計の値が最大かまたは、最小のものを抽出する式を入力します。
=(OR(D2=MAX($D$2:$D$27),D2=MIN($D$2:$D$27)))

この式はOR(A,B)という形でAかBならTUREそうでなければFALSEを返します。Aの部分がMAX関数でセルD2:D27の範囲の最大値、Bの部分がMIN関数でセルD2:D27の範囲の最小値を表します。つまり、最大値か最小値と等しかったらTUREになりますので計の(セルD2)の最大値と最小値を抽出します。 MAX関数 MIN関数 AND関数 OR関数も参照してみてください。

このように複数の条件を1つの式にまとめると1つで、計の最大と最小が抽出されます。

フィルターオプション ワイルドカード等

フィルターオプションよりもう少し細かい使い方をご紹介します。

今回は、抽出先を指定した範囲として常に元の表はそのままで抽出結果を別の場所にします。

以下のような表で商品名がみかんのデータをフィルターで抽出します。

セルF1に元の表の見出しと同じ商品名という見出しを入力します。見出しの下に=”=みかん”と入力します。

元の表のセルをクリックしデータタブの並べ替えとフィルター詳細設定をクリックします。フィルターオプションの設定が表示されますので検索条件範囲にセルF1からセルF2をドラッグして指定します。(自動で$マークの付いた絶対参照になります。)

抽出先の指定した範囲を選択しセルk1をドラッグで指定します。オプションの設定のOKボタンをクリックします。

元の表の見出しを含めて、商品名がみかんのものが抽出されます。

次に抽出先を少し変更します。セルk1とセルk2だけ残して抽出先のデータをいったん削除します。

この状態でサイト商品名がみかんのものだけ抽出します。フィルターオプションの設定で、抽出先を先ほどの2つの見出しセルK1からセルL1のセルをドラッグで選択します。OKボタンをクリックします。

指定した見出しのデータのみ抽出されます。抽出先の見出しの並び順は元の表通りでなくても大丈夫です。

通常のフィルターの場合同じ項目(列)で2つのAND条件が設定できました。例えば単価が100円以上200円未満だとかです。フィルターオプションは横にAND条件縦にOR条件になります。複数のANDの場合は、列見出しを複数作ります。

単価が100円以上、200円未満の場合は検索条件範囲に単価を2つ作ります。

セルF1には=”>=100″、セルG2には=”<200″と入力します。検索条件範囲セルF1からG2として、抽出先範囲はいったんデータを削除してから任意のセル(セルK1)を選択します。

OKボタンをクリックします。

単価のAND条件がうまくいきました。

文字列の抽出でワイルドカードを使う

フィルター テキストフィルターでも出来たように任意の文字、または、文字列を表す

* 任意の長さの文字(0文字以上)
? 任意の1文字

をワイルドカードと言ったりします。

コーヒーやチョコレートなどの伸ばす長音を含む商品だけを抽出する場合は

=”*ー*”

という風に指定します。*は0文字以上何文字でもいいので『ー』が商品名に含まれていれば該当します。

=”*ん”の場合は『ん』で終わっていれば抽出されます。『ん』一文字でも該当します。『みかん』は該当しますが、『こんにゃく』は『ん』で終わっていないので抽出されません。

商品名で「=”*に*”」とすれば何が抽出されるでしょうか?

『こんにゃく』と『にんにく』が抽出されました。『に』を含んでいればいいので今回の表にはありませんが、『にんじん』や『かに』なども抽出されます。

分かりやすいようにそれぞれ別個に指定しましたが、複数行組み合わせて商品名が『みかん』で価格が『100円以上』『200円未満』で数量が『5以上』か(ORで別の行に)商品名に『に』を含むもののような使い方ができます。

フィルターオプション

1つの列に対して複数(2つ以上)の条件を指定したいときにフィルタオプションを使います。

フィルターを駆使すればいろいろな抽出が可能なのですが、フィルタオプションを使うとどのようなフィルタをかけたが目に見える形で残ります。また、フィルターは元の表の行が折りたたまれて非表示になりますが、フィルタオプションは抽出結果を別の場所に表示することも可能です。

上のような表の場合見出し列にボタンがありフィルターの図柄になっています。また、列見出しが青色で番号が飛んでいますのでフィルターがかけられてるのが分かります。ただ、どのようなフィルターがかかっているかはこれを見る限りではわかりません。

フィルタオプションはフィルターをかけたい表と同じ見出し行を別の場所に作成し、その下にフィルターの条件を数式で入力します。

一般的にフィルターをかけたい表の上部にフィルタオプション用の見出しを作る場合が多いです。(横に作ると元の表が折りたたまれる場所によっては、フィルタオプション用の表も一緒に折りたたまれて見えなくなってしまいます。)

通常のオートフィルタ オプションでは、ANDとORが選択でき2つ指定できます。

フィルタオプションは横にAND、縦にORということで沢山の項目でフィルタをかけられます。

例えばみかんで単価が100円以上のものを抽出する場合はセルA2とセルB2に条件を入力します。

数式なので=イコールを入力してから改めて文字列として式を入力します。セルに表示されているのは=みかんと>100なのですが入力は=”みかん”と=”>100″の様にダブルクォーテーションで括って文字列として入力しています。

次にデータタブの並べ替えとフィルターの詳細設定をクリックします。フィルタオプションの設定のダイアログボックスが表示されます。この時元の表の中をクリックして選択しておくとリスト範囲が自動で選択されます。

。フィルタオプションの設定のダイアログボックスの検索条件範囲に先ほど作った見出しと数式のある範囲をドラッグして選択します。今回はセルA1からセルD2になります。

いづれも、自動で$マークの付いた絶対参照になります。OKボタンをクリックします。

これで商品名みかんで単価が100より大きいものが抽出されました。両方の条件を満たすAND条件です。

>100を1行下にするとどちらかの条件を満たすORになります。

セルB2の>100をセルB3にずらします。再度詳細設定をクリックし、検索条件範囲をセルA1からセルD3に変更しOKボタンをクリックします。

商品名はみかん、単価は100より大きいのどちらかの条件を満たしているものが抽出されました。検索範囲の選択は慎重に行ってください。条件が入っているのに範囲が選択されていないとか、空白行まで選択してしまうとすべての行が抽出されてしまうので注意してください。

2つの条件くらいなら普通のフィルターでもできますが、たくさんの項目でフィルターをかける場合や、フィルターの条件が見えているので便利です。

通常のフィルターでは元データが折りたたまれますし、フィルタオプションでも元のデータが折りたたまれますがフィルター オプションの設定から指定した範囲を選択すると別の場所に抽出結果が表示されます。(元データは折りたたまれません。)

元の表が折りたたまれませんのでフィルタオプションの見出しを横に作ります。並べ替えとフィルターの詳細設定をクリックします。検索条件範囲を指定しなおします。フィルタオプションの設定のダイアログの抽出先を指定した範囲を選択し、抽出範囲のボックスに抽出先の左上のセルを指定します。OKボタンをクリックします。(矢印は関係ありません)

これで元の表が折りたたまれることなく抽出条件が明示され、抽出データが別のセルに書き出されました。

フィルター テキストフィルター

フィルター (データの抽出)でやったような性別によるフィルターなら、男、女、非公開、未選択など、あっても数種類なら選択肢からの抽出で十分です。(最近は性同一性障害への配慮から性別欄自体が消えつつありますのであまり適切な例ではないかもしれません)

ちょっと例題が無理やりですが、特定の文字を基準にフィルターをかけることが可能ですのでご紹介します。

このようなでたらめの住所録で名前の列でテキストフィルターをかけてみます。

セルA1の名前の右の三角をクリックしテキストフィルターをポイントします。指定の値に等しいとか等しくないとかのメニューが表示されます。

指定の値に等しいは完全一致、指定の値に等しくないは完全一致の裏ですね。

例えば『吉村 佳乃』で指定の値に等しい(ドロップダウンのと等しい)でフィルタをかけると完全に一致する『吉村 佳乃』さんだけが抽出されました。同姓同名(同じ文字列)の人がいればその人も抽出されます。

『吉村 佳乃』で指定の値に等しくないは『吉村 佳乃』以外の人全部が抽出されます。

指定の値で始まるで『吉村』と入力すると『吉村 佳乃』と『吉村 真一』が抽出されます。

指定の値で終わるで『乃』とすると乃で名前が終わる3名が抽出されました。

指定の値を含むは始まる、終わる以外にそれらをどこかに含んでいれば抽出対象になります。含まないはその裏ですね。

『村』を含むで抽出すると吉村さんも、高村さんも村上さんも抽出対象になります。とにかく『村』という文字さえ含まれていれば選択されます。

では、2文字目に村という文字が含まれている人だけを抽出するのどのようにすればよいでしょうか。

オートフィルターのオプションのダイアログの左下に書いてあります。

半角のはてな?を使えば?が1文字分になってくれます。オールマイティな文字だと考えるとわかりやすいかもしれません。

?村で始まるにすると、? はある時は田村の田、ある時は吉村の吉、またある時は梅村の梅の文字の代わりになります。〇村さんみたいに伏字で〇はどんな文字(1文字)でもいい世的な感じです。

吉村、高村、岡村、、田村浦などが抽出されます。村田は抽出されませんし、三田村も抽出されません。?は任意の1文字なので三田村の様に3文字目に村が来る人を抽出したいなら、??村と?を2回続けて入力するとうまくいきます。

*は任意の文字列となっており?とは少し違います。?は必ず1文字ですが、*は1文字とは限りません。何文字で*1つで表せます。

*村で始まるでフィルターをかけてみます。

*は任意の(長さの)文字列ですので吉村は該当します。三田村も該当します。少し驚かれるかもしれませんが、村上も該当します。

*は任意の文字列ということで空の文字も該当する。または任意の長さの文字として0文字も該当すると考えていただければ理解しやすいかもしれません。

フィルター 数値フィルターでもやりましたようにオートフィルターのオプションは2つ入力ボックスがあり間にANDとORのチェックボックスがありますので2つの条件で抽出することが可能です。

*村で始まる AND *子で終わるにすれば、□村 □子さんが抽出されます。□は任意の文字列(0文字も含む)ということで以下の図のような結果になります。村上翔子、三田村璃子、村奥育子など。

テキストフィルターをポイントしたときのメニューには表示がありませんが、以上と以下がドロップダウンのリストにあります。

これはCODE関数の戻り値を基準にそれ以上、それ以下の値に該当すると思われます。

フィルター 複数項目で抽出 

フィルターは1列に足してだけではなく複数列に対しても設定することが可能です。

下のような表に対して支店は問わずに第1営業課で売り上げが3000000以上の人だけを抽出するばあい、所属部署と売り上げに対してフィルターを設定します。

どちらの列に対してフィルターをかけるかの前後の違いはありません。所属部署からでも売り上げからでも構いません。

とりあえず、所属部署で第1営業課だけを抽出します。

次に売り上げの列で数値フィルターをかけます。

今回は指定の値以上をクリックします。オートフィルターのオプションが表示されますので3000000と入力しOKボタンをクリックします。

これで2つの列にフィルターをかけて第1営業課で売り上げが3000000以上の人だけを抽出することができました。今回は2つの列でしたが、必要であればすべての列にフィルターをかけて抽出することが可能です。

フィルター 色フィルター

セルの塗りつぶしの色を使ったエクセルの機能はそう多くないのですが、色フィルターは塗りつぶしの色をもとに抽出が可能です。

以下のような表でセルを塗りつぶされている行があります。表の項目にないような意味があるのでしょう。例えば自分が顧問のクラブの部員の男子と女子にそれぞれ塗りつぶしているとかですね。

フィルターで(国語のセルが)薄い青の人だけ抽出してみます。

表の中のセルを選択し、ホームタブの編集の並べ替えとフィルターのフィルターをクリックします。

国語の右のドロップダウンのボタンをクリックします。

色フィルターをポイントすると国語の列のセルの塗りつぶしの色が表示されます。青を抽出するなら青を、オレンジならオレンジをクリックします。これら以外に塗りつぶしなしも選択できます。

塗りつぶしの色をもとに抽出できました。

フィルター 数値フィルター2

フィルター 数値フィルターの続きです。

表の中のセルを選択し、ホームタブの編集の並べ替えとフィルターのフィルターをクリックします。

今回も数値フィルターですから数値の列、国語の列でフィルターをかけてみます。国語の右のドロップダウンのボタンをクリックします。

トップテンを選択します。

トップテンオートフィルターのダイアログボックスが開きますのでOKボタンをクリックします。

国語の点数の大きいベスト10が抽出されました。今回該当項目がありませんが、もし、国語の10番目の点数である77点の人が複数いた場合どうなるでしょうか。ベスト10の点数巣の人はすべて該当することになるので11人とか12人が抽出されます。

トップテンオートフィルターは上位だけでなく、ダイアログボックスで下位を選択することができますし、10を3(任意の数値)にしてベスト3にすることができます。

更に項目をパーセントに変更すれば全体(表の項目の行数)対する割合で抽出することができます。

次に平均より上を試してみます。

数値フィルターの平均より上をクリックします。

国語が平均より上の人が抽出されました。

これはこれでOKなのですが国語の平均点がいくつかわからないのと何人が抽出されたのが分かりません。

AVERAGE関数SUBTOTAL関数関数で表の下に国語の平均点と抽出された人数を表示する表を作成してみます。

セルE50に
=AVERAGE(F2:F48)

と入力します。(オートサムの右のボタンから平均を選択し範囲をドラッグする)小数点以下が表示されていますので小数点の表示桁下げで調整してください。

セルE51には
=SUBTOTAL(2,F2:F48)

と入力するのですが途中で手入力で=subまで入力すると以下のような選択ができるようになります。

SUBTOTALの上でダブルクリックします。すると集計方法の候補が表示されます。今回は人数を数えてほしいので2-COUNTをダブルクリックします。

そうすると
=SUBTOTAL(2

まで関数が補完されるのでカンマ(,)を入力しカウントしたいセルF2:F48をドラッグします。

最後に小かっこ閉じを入力してエンターキーを叩きます。

現在フィルターで抽出をかけていませんので全員の47が表示されています。

先ほどと同じように平均より上でフィルターをかけます。

こちらの方が少しわかりよいかもしれません。

フィルター 数値フィルター

数値の列に対してフィルターを適用してみます。下のような表に対してフィルターをかけます。表の中をクリックしてホームタブの編集の並べ替えとフィルターのフィルターをクリックします。

数値の列である国語の列のフィルターをかけてみます。国語の点数が網羅されています。

点数を選択してフィルターをかけても検索したような感じになってしまいます。

数値フィルターを使えば条件を指定して抽出することができます。

数値フィルターをポイントするとサブメニューが表示され沢山の選択候補が表示されます。

指定の値に等しい、指定の値に等しくないなどたくさんありますが、赤枠の8つは基本的に同じものになります。選択するとオートフィルターのオプションというダイアログボックスが表示されます。抽出条件の指定の後ろのドロップダウンメニューが自動で指定のものが選択されているだけになります。

指定の値に等しいを選択するとドロップダウンメニューの項目が『と等しい』が自動で選択されています。ほかの等しくないとかより大きいなども選択したものがドロップダウンメニューの選択値となっています。

国語が90点以上の人を抽出したい場合は数値フィルター以下の様に設定します。

1つ目のボックスに90と入力し右のプルダウンで以上を選択します。OKボタンをクリックします。

ボックスが2つあるので2つの条件が指定できます。例えば国語の点数が70点以上で90点未満の人を抽出したい場合は、1つ目に70点以上2つ目に90点より小さい(未満)を指定すればいいです。

以上と以下はその値を含み、より大きいとより小さいはその値を含みません。また、指定する順番は90より小さいを先にしても同じことです。

また、2つのうち1つの条件だけでも満たしているものを抽出する場合は真ん中のORにチェックを入れます。

90以上 OR 10以下に設定すれば90点以上の人と10点以下の人が抽出されます。

数値フィルターについてはまだ少しありますが別途ご紹介したいと思います。

フィルター SUBTOTAL関数

フィルターを使ってデータを抽出したときに抽出されて行についてのみ集計してみます。

41行目セルE41にオートサムで合計を表示してみます。

オートサムで合計が表示されました。

次にこの表にフィルタを設定します。表の中をクリックしておきます。ホームタブの編集の並べ替えとフィルターからフィルターをクリックします。

店名で本店だけを表示します。店名の右のドロップダウンのボタンをクリックします。本店だけにチェックを入れます。

なぜか空白セルなるものがありますが、合計のセルのある最下行(41行目)もフィルターが有効になっています。本店だけ表示されましたが、本店だけの売り上げの合計が欲しいです。このまま最下行である42行目に本店だけの合計を表示する式を入力してもいいのですが、フィルターを解除したときに合計の行が2行になってしまいます。

いったん、フィルターを終了して合計のセルを削除します。再度フィルターを設定し、本店だけ表示します。

今回は空白のセルの選択肢がありません。この状態で最下行に合計を表示させてみます。セルE41を選択し、オートサムのボタンをクリックします。

数式バーを見ていただくと=sum()ではなく=SUBTOTAL(9,E2:E40)当数式が入力されています。とりあえずエンターキーを叩いて確定しておきます。

フィルターで店名を変更しても表示されている売り上げの合計だけが計算されているのが分かります。

もちろんフィルターを解除してすべてのデータを表示した状態でもきちんとすべてを合計してくれます。

フィルタをかけた状態でオートサムのボタンをクリックすると自動でSUBTOTAL関数の合計が選択されますので手入力する必要はありません。

ちなみにあまり使わないかもしれませんが、SUBTOTAL関数の第1引数を変更すると表示されている行数を表示してくれたりもします。

以下は少し複雑なことになっていますので飛ばしていただいても構わないと思います。

まず第1引数の一覧

1   AVERAGE
2   COUNT
3   COUNTA
4   MAX
5   MIN
6   PRODUCT
7   STDEV
8   STDEVP
9   SUM
10  VAR
11  VARP

第1引数を1とすればフィルタにかけられた数値の平均になります。9がsumなので合計ですね。

SUBTOTAL関数ふるまいですが、通常(第1引数の書き方による)ではフィルターに対して非表示の行は計算対象になりませんが、手動で行を非表示にした場合(フィルターによらない非表示行)は計算対象になります。

手動で非表示にした行も計算対象から外したい場合は第1引数に100を足した数値を設定します。

101  AVERAGE
102  COUNT
103  COUNTA
104  MAX
105  MIN
106  PRODUCT
107  STDEV
108  STDEVP
109  SUM
110  VAR
111  VARP

SUBTOTAL関数の第1引数を109にするとフィルタで除外されている行と手動で非表示にされている行は計算対象にはなりません。

詳しくはMicrosoftのSUBTOTAL 関数を参照してみてください。

フィルター (データの抽出)

フィルターをかける

エクセルは表計算ソフトですが、グラフを作成したり、データベースのようにデータを並べ替えたり抽出したりすることが可能です。

フィルターをかけるとデータをいろいろな方法で抽出することが可能です。

以下のような住所録があります。この住所録から女性だけの住所録を作成する場合、一行一行女性であることを性別の列を目視で確認してコピーして別のシートに張り付けるような面倒くさいことはしませんよね。

性別の列で並べ替えをして女性のひと塊をコピーして別のシートに張り付けたりしてますか。全然いいのですが、そうなると電話番号の変更があったり、住所変更があったりすると2つのシートのデータを変更しないとおかしなことになってきます。

実は、これはよくある現象で、社内で見ているデータが違うというようなことが起こってきます。フィルターを使えば新たにシートにコピーするわけではないのでこのようなことは起きにくくなります。

フィルターを設定します。表の中の任意のセルを選択します。ホームタブの編集の並べ替えとフィルターをクリックし、フィルターをクリックします。

各列の見出しの右にドロップダウンのボタンが表示されます。

性別の右のドロップダウンのボタンをクリックします。今回情勢だけの名簿が欲しいので男のチェックをクリックしチェックを外した状態にしてOKボタンをクリックします。

これで女性だけの名簿になりました。

女性は8名だけですが、行番号を見ていただくと8番目の方は42行になっています。これはフィルターに引っかからなかった男の分の行が折りたたまれて非表示になっている状態です。男の名簿が消えてなくなったわけではありません。

名簿にフィルターをかけて女性だけになっている状態から男性だけの名簿にする場合は再度性別の見出しの右のドロップダウンのボタンをクリックして今度は男だけがチェックの入っている状態でOKボタンをクリックします。

元の男女とも表示したい場合はすべて選択を何度かクリックして男女ともにチェックが入っている状態にしてOKボタンをクリックするか、ホームタブの編集の並べ替えとフィルターをクリックし、クリアをクリックします。そうすると男女ともに表示されるようになります。

さらに、フィルター自体を終了する場合は再度ホームタブの編集の並べ替えとフィルターをクリックし、フィルターをクリックします。これでフィルターは自動で解除されて、ドロップダウンのボタンも消えて元の通りになります。