Excel。1行おきの数値を合計するには、FILTER関数を使うと便利です。【Every other line】

Excel。1行おきの数値を合計するには、FILTER関数を使うと便利です。

＜SUM+FILTER+MOD+ROW関数＞

次のような表があります。

やりたいことは、1Fと2Fの金額合計を求めたいわけです。

つまり、1行おきで合算したい。

A列が1F販売金額となっていれば、SUMIF関数をつかうことで、合計を求めることもできます。

ただ今回の表は、2024年という文字列があって、2025年2026年という文字列もついています。

つまり、同じ条件でというわけにはいきません。

A列から、色々作業をしなければいけません。

では、何かいい方法はないのでしょうか。

以前は、SUMPRODUCT関数をつかって求めていましたが、FILTER関数をつかう方法で、求めてみようと思います。

E2には、FILTER関数をつかった数式を設定しました。

=SUM(FILTER(B2:B7, MOD(ROW(B2:B7),2)=0))

これで、1Fの金額合計を求めることができました。

2Fの数式は、

=SUM(FILTER(B2:B7, MOD(ROW(B2:B7),2)=1))

という数式で求められます。

数式を説明します。

SUM関数の引数の中から説明するとわかりやすいので、まずはFILTER関数から説明します。

FILTER関数は、抽出する関数です。

FILTER関数の最初の引数は、「配列」。

範囲のことなので、B2:B7を設定します。

次の引数は、「含む」。

抽出条件です。

その抽出条件には、MOD(ROW(B2:B7),2)=0

という数式を設定します。

MOD+ROW関数は、そのセルが偶数行なのか、奇数行なのかを判断するときに使う方法です。

MOD関数は、割った余りを求める関数です。

ROW関数は、セルの行番号を求める関数です。

つまり「セルの行番号を2で割った値が、ゼロ」というのが抽出条件になるというわけです。

これで、偶数行なのか奇数行なのか、わかります。偶数奇数ですから、一行おきが対象になるという仕組みです。

あとはSUM関数で合計する。

このように、FILTER関数をつかうことで、一行おきを対象にした合計を求めることができます。

Excel。住所から横浜市のデータだけを抽出して別表をつくりたい【address】

Excel。住所から横浜市のデータだけを抽出して別表をつくりたい

＜FILTER+IFERROR+FIND関数＞

住所から横浜市が含まれているデータを行全体で抽出したい。

抽出したデータの別表をつくりたいということなんですね。

住所には、都道府県から入力されているので、横浜市を含むという「*横浜市*」のようなワイルドカードをつかう方法があります。

また、オートフィルターで、横浜市を含むという条件で抽出する方法もあります。

今回は、FILTER関数をつかって、処理してみましょう。

FILTER関数は、抽出して別表をつくることができる関数です。

=FILTER(A2:D11,IFERROR(FIND(D13,D2:D11)>0,0),"")

と、A15に設定するだけで、横浜市を含むデータを抽出することができます。

FILTER関数は、スピル機能対応の関数なので、オートフィルで数式をコピーする必要はありません。

今回は、D13に条件を入力することで、その条件に合致するデータを抽出するようにしましたが、D13に用意しない場合には、

=FILTER(A2:D11,IFERROR(FIND("横浜市",D2:D11)>0,0),"")

というように数式を設定してもOKです。

では、数式を確認してみましょう。

FILTER関数よりも、先に、FILTER関数内の引数にある数式を確認しましょう。

FIND関数をつかっています。

これは、セル内に、横浜市という文字列があるかないかを処理しています。

左から何文字目に登場するかという数値を返してくれます。

神奈川県横浜市　ですから、5文字目に横浜市がありますので、5を返してくれるというわけです。

ただ、FIND関数の欠点は、該当のデータがなかった場合、#VALUE!というエラーが発生してしまうことです。

エラーがあると、最終的にFILTER関数をつかってデータを抽出したくても、#VALUE!というエラーが表示されてしまうので、FIND関数の時点でエラーを処理する必要があります。

そのため、IFERROR関数をつかって、エラーを表示しないようにします。

その場合、空白とせず、0にします。

よって、FILTER内の引数は、「IFERROR(FIND("横浜市",D2:D11),0)」となるわけです。

では、FILTER関数を確認します。

最初の引数は、「配列」。

範囲なので、A2:D11と設定します。

スピル機能がありますから、絶対参照にする必要はありません。

2つ目の引数は、「含む」。

条件です。

ここで、先ほど確認した、「IFERROR(FIND(D13,D2:D11)>0)」を設定します。

「>0」としたのは、0よりおおきければ、該当の文字が含まれていることを意味しています。

このためIFERROR関数で空白ではなく、0にしたわけです。

3つ目の引数は、「空の場合」。該当データがなかった場合は、「””」空白にします。

FILTER+IFERROR+FIND関数を組み合わせることで、関数だけで、該当する含むデータを抽出して、手早く別表にすることができます。

Excel。横長の表から必要な列だけを手早く抽出するにはどうしたらいい【Columns】

Excel。横長の表から必要な列だけを手早く抽出するにはどうしたらいい

＜FILTER+COUNTIF関数＞

横長の表から、必要な列だけを抽出するとなると、なかなか面倒です。

今回は、4月から6月の売上列を抽出した表をつくりたいわけです。

サンプルなのでデータ量が多くありません。

このような場合、コピペで対応できるとは思いますが、データ量が多い場合、列を選択するだけでも大変です。

そこで、FILTER関数とCOUNTIF関数を組み合わせるだけで、手早く、抽出した表をつくることができます。

では早速やってみましょう。

事前準備として、抽出したい列の見出し行を用意します。

A7:E7に抽出したい見出しを用意しました。

A8に次の数式を設定します。

=FILTER(A2:H5,COUNTIF(A7:E7,A1:H1))

これだけで、抽出することができました。

 

FILTER関数は、スピル機能対応なので、オートフィルで数式をコピーする必要はありません。

これで、完成しました。

では、この数式を確認しておきましょう。

FILTER関数は、抽出する関数です。

最初の引数は、「配列」。データなので、A2:H5を範囲します。

見出しは不要です。

2つ目の引数は、「含む」。

条件です。

この引数に、1行の配列を指定すると、列の抽出を行うことができます。

そこで、COUNTIF関数をつかって、用意した見出しと一致したら「1」と算出されてます。

1となった列が抽出されるという仕組みです。

それでは、データタブの「数式の検証」をつかって確認してみます。

数式の計算ダイアログボックスにある検証ボタンを2度クリックします。

COUNTIF関数の結果を見ると、用意した見出しのところが「1」になっていることが確認できます。

ただし、この数式には、欠点があります。

左から同じ順番でないと、抽出することができません。

6月売上,5月売上,4月売上 というように、順番を変更してしまうと、抽出はされますが、見出しと合致しないデータになってしまいます。

このように、FILTER関数とCOUNTIF関数を組み合わせることで、手早く指定した表を抽出することができます。

Excel。FILTER関数で抽出結果に0（ゼロ）が表示されてしまうので対応したい【ZERO display】

Excel。FILTER関数で抽出結果に0（ゼロ）が表示されてしまうので対応したい

＜FILTER+IF関数＞

抽出するのに便利なFILTER関数ですが、ちょっと困ることがあります。

次の表をつかって説明します。

A1:D11にデータがあります。

チームがBで、合宿参加のデータを抽出したいので、F4にFILTER関数をつかって、抽出しました。

F4につくった数式は、

=FILTER(A2:D11,C2:C11=G1,"データなし")

最初の引数は、「配列」。

範囲選択なので、A1:D11。

FILTER関数は、スピル機能対応の関数なので、絶対参照は不要です。

2つ目の引数は、「含む」。

条件なので、C2:C11=G1 と等しいデータという条件で抽出します。

G1はBなので、チームBを抽出します。

最後の引数は、「空の場合」。

該当データがない場合は、どうするかということで、「データなし」と表示する設定をしました。

抽出結果は、チームBの人が抽出できたのですが、よくみると、合宿参加の列。

I4に「0（ゼロ）」と表示されています。

抽出元のデータは、空白なのですが、0（ゼロ）と表示されてしまっています。

これでは、今回たまたま「○（まる）」ですが「0（ゼロ）」だった場合、空白で0が表示されているのか、抽出元が0なのかわかりません。

では、どのようにしたら、抽出元が空白の場合、抽出結果も空白にすることができるのでしょうか。

抽出元が空白なので、0になってしまいます。

そこで、空白を意味する「””（ダブルコーテーション×2）」に置換した状態にして抽出する必要があります。

よって、FILTER関数を次のように修正することで対応できます。

=FILTER(IF(A2:D11="","",A2:D11),C2:C11=G1,"データなし")

これで、0ではなく空白のままにすることができました。

修正したのは、「配列」のところにIF関数を追加しました。

IF(A2:D11="","",A2:D11)

A2:D11で、空白セルは、空白。

そうでなければ、A2:D11のセルに設定されているデータのままという意味です。

このように、抽出で便利な関数のFILTER関数ですが、ケースによっては、アレンジする必要があります。

Excel。FILTER関数で抽出したデータの件数を結果に合わせて求めたい【count】

Excel。FILTER関数で抽出したデータの件数を結果に合わせて求めたい

＜FILTER関数　ROWS関数＞

A1:D8に店舗販売のデータがあります。

この店舗販売のデータから、G1に設定した地域名をつかって、F5を起点として該当するデータを抽出したいわけです。

そこで、FILTER関数をつかうことにしました。

F5に設定した数式は、

=FILTER(A2:D8,C2:C8=G1,"該当なし")

これで、地域が関西のデータを抽出することができました。

FILTER関数を確認しておきます。

最初の引数は、「配列」。

範囲のことなので、A2:D8。

FILTER関数は、スピル機能対応の関数なので、絶対参照は不要です。

2つ目の引数は、「含む」。

条件のことなので、C2:C8=G1。

条件の列はC2:C8で、抽出条件は、G1に入力されています。

それと合致するかどうかという条件式です。

3つ目の引数は、「空の場合」。

データがなかった場合にどう処理するのかということなので、”該当なし”と表示する様にしました。

スピル機能対応の関数なので、オートフィルで数式をコピーする必要はありません。

さて、抽出することはできましたが、今回やりたいことは、抽出された件数をG2に求めたいわけです。

件数なので、COUNT関数をつかってみればいいはずです。

G2に、

=COUNT(F5:F6)

と設定すれば、2件と求めることができました。

一度だけならば、これでいいのですが、地域を「関東」に変更してみると、3件抽出されてましたが、件数は2のままです。

範囲は、F5:F6と固定されていますので、連動してくれません。

仮に、F列の下方向に、何もデータがなければ、F5:F200とか想定される上限の範囲設定をしていてもいいかもしれません。

しかし、下方向にデータがある場合などには、少し都合が悪くなります。

抽出に連動した範囲にすることはできないのでしょうか。

そこでROWS関数をつかうことにします。

ROWS関数は、範囲に含まれる行数を求めることができる関数です。

ROW関数は、セル番地の行数なので、ROW関数では対応できません。

G2にROWS関数の数式を設定します。

=ROWS(F5#)

これで確定します。

条件を関東に変更しても、件数は3件と正しく求めることができました。

FILTER関数で自動的に範囲が変動しても、ROWS関数は抽出結果に合わせて行数を求めてくれます。

行数はデータの件数と同じなので、COUNT系の関数をつかわなくても、抽出したデータの件数を求めることができるというわけです。

Excel。FILTER関数でOR条件の抽出条件の設定方法を確認します。【conditions】

Excel。FILTER関数でOR条件の抽出条件の設定方法を確認します。

＜FILTER関数＞

FILTER関数をつかうことで、オートフィルターなどつかわなくても、該当するデータを抽出することが容易になりました。

次の表をつかって、FILTER関数のOR条件の設定方法を確認してみましょう。

抽出条件ですが、店舗名が本店と、地域は関西のデータを抽出したいとした場合、オートフィルターだけでは、対応することができません。

店舗名を本店で抽出した段階で、地域は、本店の関東のみしか表示されません。

つまり、地域の関西は抽出できないわけです。

そこで、オートフィルオプションをつかって、検索条件をつくり、それから抽出するわけで、面倒な工程を必要とします。

そのため、FILTER関数をつかうことで、「店舗名が本店と、地域は関西のデータを抽出したい」という高度な条件にも、手早く対応することができるというわけです。

F2にFILTER関数をつかった数式を設定しました。

 

F2に設定した数式は、

=FILTER(B2:D8,(C2:C8="関西")+(B2:B8="本店"),"")

FILTER関数は、スピル機能に対応した関数です。

設定するだけで、ゴーストが発生するので、オートフィルでの数式のコピーは不要です。

「店舗名が本店と、地域は関西のデータを抽出したい」

という抽出条件に合致していることが確認できます。

FITER関数だけで、高度な条件に対応したデータを抽出できたというわけです。

FILTER関数の引数を確認すると、

最初の引数は、「配列」。

今回は、A列を除いても対応できることを確認したかったので、B2:D8 と設定しました。

2つ目の引数、「含む」が、今回のポイントです。

OR条件の場合には、「+」をつかって、「含む」を増やすことができます。

なお、AND条件は、「*（アスタリスク）」をつかいます。

よって、2つの条件なので、(C2:C8="関西")+(B2:B8="本店")　と設定しました。

3つ目の引数、「空の場合」は、条件に合致しないデータがあった場合ということなので、「””（ダブルコーテーション×2）」で空白としました。

オートフィルターで抽出条件が単純でない場合などには、FILTER関数をつかうといいかもしれませんね。

Excel。FILTER関数をつかって必要な行を抽出したい【row】

Excel。FILTER関数をつかって必要な行を抽出したい

＜FILTER関数＞

必要な行だけを表から抽出するのにも、FILTER関数をつかうことができます。

ただ、列抽出と異なる所がありますので、そこがポイントになるかと思います。

次の表を用意しました。

色鉛筆とボールペンの行だけを抽出するとします。

見出し行は、先にコピーしておきます。

FILTER関数をつかった数式を設定します。

A10に設定した数式は、

=FILTER(A2:F5,{0;1;0;1})

スピル機能によって、数式が拡張されてゴーストが発生しますので、絶対参照は不要です。

最初の引数は、配列なので、範囲選択ですが、A2:F5

問題は、2つめの引数の含む　です。

{0;1;0;1}

と設定してあります。

FILTER関数は、「,（カンマ）」で区切ると、列抽出で、「;（セミコロン）」で区切れば、行抽出できる仕組みになっています。

この0と1ですが、0はFALSEで1はTRUEです。

わかりやすいように、抽出元の表に0と1を追記してみました。

「1」が設定されている、行だけが抽出されていることが確認できます。

なお、VSTACK関数でも、行方向で抽出することができます。

Excel。FILTER関数で必要な列だけを抽出するには【Columns】

Excel。FILTER関数で必要な列だけを抽出するには

＜FILTER関数＞

表から必要な列だけを手早く抽出するには、HSTACK関数などがありますが、今回は、FILTER関数をつかった場合、必要な列だけ抽出するやり方を紹介します。

FILTER関数は、名前の通り、表から条件に合ったデータを抽出できる関数です。

次の表を用意しました。

A列の商品名とD列E列の10月と11月だけを抽出した表をつくりたいとします。

見出しは用意したとして、つくっていきます。

A10にFILTER関数をつかった数式を設定しました。

=FILTER(A2:F5,{1,0,0,1,1,0})

これで、商品名と10月・11月の列だけを抽出することができました。

引数を確認してみましょう。

最初の引数が、配列。範囲のことなので、A2:F5

次の引数が、含む。なのですが、FILTER関数の{1,0,0,1,1,0}は何を意味しているのでしょうか。

7行目に1と0を入力して、わかりやすくしました。

単純に、1ならば、抽出対象とする。0ならば、抽出対象にしないという意味です。

それぞれを「,（カンマ）」で区切ってあげることで、設定することができます。

なお、この１と０は、1が「TRUE」で0が「FALSE」の意味です。

列が増えると、設定するのが、少し大変なので、単純に必要な列だけを抽出するならば、HSTACK関数でもいいように思えます。

Excel。複数条件を指定して、手早く別表に抽出したい【extraction】

Excel。複数条件を指定して、手早く別表に抽出したい

＜FILTER関数＞

オートフィルターをつかうことで、データを抽出することは、簡単にできます。

しかし、抽出したデータを、別の場所にコピーして貼り付けるとなると、単純な作業ですが、面倒です。

そこで、FILTER関数をつかうことで、抽出し、別の場所に抽出結果を表示することができます。

抽出条件をF1:G2に用意しました。

店舗名が新宿店で、商品名が消しゴムのデータを抽出して、F5を起点に結果を表示していきます。

F5に設定する数式は、

=FILTER(A2:D11,(B2:B11=G1)*(C2:C11=G2),"該当データなし")

これだけで、抽出して、さらに、別なところに貼り付けることができます。

FILTER関数の最初の引数は、「配列」です。

つまりデータのことなので、A2:D11を設定します。

2番目の引数は、「含む」。

条件です。

FILTER関数で複数条件を作る時には、「*（アスタリスク）」で接続させていきます。

(B2:B11=G1)*(C2:C11=G2)と設定します。

また、スピル機能によって数式の範囲が広がりますので、絶対参照にする必要はありません。

3番目の引数は、「空の場合」。

これは、抽出するデータがなかった時、どうしますかということなので、「該当データなし」と表示するようにしました。

Excel。FILTER関数で、範囲または配列をフィルターすることができます。【FILTER】

Excel。FILTER関数で、範囲または配列をフィルターすることができます。

＜関数辞典：FILTER関数＞

FILTER関数

読み方： フィルター　　

分類： 検索/行列 

FILTER(配列,含む,[空の場合])

範囲または配列をフィルターする

Excel。オートフィルターをつかわなくても、FILTER関数で別表を抽出できます。【extract】

Excel。オートフィルターをつかわなくても、FILTER関数で別表を抽出できます。

＜FILTER関数＞

抽出した結果を、別の表にする場合、オートフィルターで抽出してコピーするのが一般的です。

ただ、新しく追加されたFILTER関数をつかえば、元の表はそのままで、オートフィルターをつかわずに、手早く、別表として抽出することができます。

次の表をつかって説明します。

 

A1:C6の表から、売上高が800以上のデータを、このA1:C6の表はそのままで、別表として抽出したいわけです。

そこで、FILTER関数を使えばいいというわけです。

A9にFILTER関数の数式を設定します。

=FILTER(A2:C6,C2:C6>=800,"なし")

あとは、スピル機能によって、ゴーストが発生しますので、数式をコピーする必要はありません。

このFILTER関数と組み合わせると、さらに手早く抽出することもできます。

例えば、SORT関数と組み合わせれば、抽出と同時に並べ替えも終わった表を抽出することもできます。

最後に、A9に設定した数式を説明します。

最初の引数は、配列。範囲選択ですね。「A2:C6」を設定します。

2つ目の引数は、含む。条件ですね。800以上が該当するようにしたいので、「C2:C6>=800」と設定します。

最後の引数は、空の場合。データがないならどうするのかということなので、「なし」と表示するようにしました。

Excel。FILTER関数の条件を直接日付で設定する場合DATE関数が必要です。

＜FILTER＋DATE関数＞

オートフィルターをつかわなくても、関数で該当するデータを抽出できるFILTER関数ですが、日付を条件として抽出するには、DATE関数が必要になります。

A1:C6の表から、来訪日が2023/9/4のデータを別セルに抽出したいわけです。

そこで、FILTER関数を使用すると、手早く抽出することができます。

A9にFILTER関数をつかった数式は、

=FILTER(A2:C6,B2:B6=DATE(2023,9,4),"")

これで、抽出することができました。

なお、スピル機能によって、ゴーストが生まれるので、オートフィルを使わなくても、数式をコピーしてくれます。

なお、算出結果は「シリアル値」で表示されてしまうので、表示形式を使って日付に戻す必要があります。

さて、今回のポイントは、引数の条件に、直接、日付を入力する時にはDATE関数が必要ということです。

FILTER関数の2つ目の引数が、条件です。

条件に日付を使う時に、DATE関数が必要になります。

例えば、次のように、数式を変更してみましょう。

=FILTER(A2:C6,B2:B6="2023/9/4","")

日付にDATE関数を使わずに、「”（ダブルコーテーション）」で囲んでみると、表示してくれません。

 

「"2023/9/4"」とすると、日付ではなくて、文字として認識するのが原因のようです。

ちなみにExcel VBAやAccessの日付型で使用する「#」で囲んでみても、日付扱いにならず、「”（ダブルコーテーション）」と同じ空白の表示になってしまいます。

なお、セル番地に日付を入力しておけば、DATE関数を使用する必要はありません。

E1に日付を入力した場合のFILTER関数をつかった数式です。

=FILTER(A2:C6,B2:B6=E1,"")

Excel。1行おきにデータを並べ替えもして手早く抽出するにはどうしたらいい。【extract】

Excel。1行おきにデータを並べ替えもして手早く抽出するにはどうしたらいい。

＜SORT＋FILTER関数＞

2行1組のデータから、1行おきにデータを抽出したいのですが、抽出後、並べ替えをするなら、抽出した時に、並べ替えが終わっていたら、作業効率がいいわけですね。

次の表をつかって、説明します。

それぞれの店舗のデータは、販売数のデータと、売上高のデータで構成されています。

売上高だけのデータを抽出して、さらに、6月の売上高を降順にしたいとします。

オートフィルター機能をつかったとしても、ちょっと面倒な作業なわけですね。

ところが、SORT関数とFILTER関数を組み合わせてつかうことで、手早く抽出して並べ替えもおこなうことができます。

必要な見出しをA11:E11に設定しておきます。

A12に次の数式を設定します。

=SORT(FILTER(A2:E9,B2:B9="売上高"),5,-1,FALSE)

これで、売上高のデータで6月のデータの降順で抽出することができました。

そして、この数式は、オートフィル機能をつかわなくても、スピル機能によって、自動的に数式が拡張されます。

では、数式を確認していきましょう。

SORT関数は、並べ替えを行う関数です。

最初の引数は、「配列」。

つまり範囲選択ですね。

ここにFILTER関数をネストしてます。

FILTER関数の説明はあとに回します。

2番目の引数は「並べ替えインデックス」。

左から何列目を並べ替えの対象としますかという設定です。

6月で並べ替えをしたいわけですから、左から5つ目なので、「5」

3番目の引数は「並べ替えの順序」。

「-1」を設定することで降順と指示できます。

ちなみに「1」だと昇順です。

最後の引数は「並べ替えの基準」。

「FALSE」の「行で並べ替え」を設定します。

「TRUE」にすると、「列で並べ替え」を設定できます。

「配列」で設定したFILTER関数も確認しておきます。

最初の引数は、「配列」。

範囲選択ですね。

「A2:E9」を設定します。

2番目の引数は、「含む」。

これが条件に該当します。

「B2:B9="売上高"」とすることで、B2:B9で売上高が対象にすることができるというわけですね。

FILTER関数と色々な関数を組み合わせて使ってみることで、意外な方法が見つかるかもしれませんね。

Excel。1行おきに抽出したデータを別の場所に、手早く取り出したい【extract】

Excel。1行おきに抽出したデータを別の場所に、手早く取り出したい

＜FILTER+MOD+ROW関数＞

帳票などで、1行ごとのデータを抽出したい場合、数式をつかって、1行おきになるように判断させます。

その結果をオートフィルターで抽出して、コピーするという方法をよく採用していましたが、FILTER関数をつかうことで、手早く抽出し、別の場所に取り出すことができます。

次の表のようにしたいわけです。

 

A1:E9の表は、販売数と売上高が交互になった表であることがわかります。

売上高のデータだけ、つまり1行おきにデータを抽出したいわけですね。

どうやったら、抽出することができるのかと考えるところですが、FILTER関数をつかえば、手早く抽出することができます。

11行目に見出し行をコピーしておきます。

A12に次の数式を設定します。

=FILTER(A2:E9,MOD(ROW(B2:B9),2)=1)

スピル機能によって、オートフィルで数式をコピーする必要はありません。

FILTER関数は、わかりやすい関数なので、使い勝手もいいように思えます。

それでは、数式とFILTER関数の引数を確認しておきましょう。

最初の引数は、「配列」です。データの範囲ですから、A2:E9と設定します。

2番目の引数は、「含む」です。これは、条件のことです。

抽出する条件ですが、1行おきに抽出したいわけなので、MOD+ROW関数の組み合わせで対応することができます。

設定した条件は、

MOD(ROW(B2:B9),2)=1

ROW関数は行番号を算出する関数です。

MOD関数は、除算した余りを算出する関数です。

その値を2で除算した余りが1と等しいかという条件をつくったわけですね。

これで、一行おきにデータを抽出することができます。

なお、MOD+ROW関数で、行が交互になるような条件をつくりましたが、「販売数」・「売上高」という項目で区別できるので、FILTER関数だけでも抽出することができます。

=FILTER(A2:E9,B2:B9=”売上高”)

このようにFILTER関数と他の関数を組み合わせてつかうことで、抽出作業が改善できるかもしれませんね。

Excel。テーブルから簡単にOR条件で抽出した別表を作れるFILTER関数【FILTER】

Excel。テーブルから簡単にOR条件で抽出した別表を作れるFILTER関数

＜FILTER関数＞

テーブルから条件で抽出して別表を手早く作ることができるFILTER関数。

オートフィルターなどでは、抽出が面倒になるOR条件も、FILTER関数をつかうことで、手早く抽出することができます。

今回は、店舗名から売上高までを対象として、売上高が1200より大きい。

または、店舗名が渋谷だったら抽出するという条件とします。

さて、このFILTER関数をつかって、OR条件をつかうときには、どのようにしたらいいのでしょうか？

テーブルにした次の表でFILTER関数をつかってみました。

 H2には、FILTER関数をつかった数式を設定しました。

=FILTER(売上表OR[[店舗名]:[売上高]],(売上表OR[売上高]>1200)+(売上表OR[店舗名]="渋谷"))

OR関数を使いたくなってしまいますが、「＋」をつかって条件を接続することでOR条件にすることができます。

FILTER関数は、アイディアで色々使えそうな関数ですので、色々試してみると、作業効率が改善できる場合もあるかもしれませんね。

Excel。FILTER関数でAND条件をつかった抽出には「*」をつかいます【Wildcard】

Excel。FILTER関数でAND条件をつかった抽出には「*」をつかいます

＜FILTER関数＞

オートフィルターで抽出するのは簡単ですが、条件が複雑になり、さらにその抽出したデータをコピーするとなると、なかなか面倒な作業になってきます。

そこで、FILTER関数をつかうと、手早く対応することができます。

そして、今回は抽出条件を「AND条件」で抽出する場合の引数を紹介します。

「売上表AND」とテーブル名を設定したテーブルを用意しました。

店舗名が新宿でかつ、売上高が1200より大きいデータを抽出します。

さらに、フィールドも店舗名・商品名・売上高だけの表にしたいとします。

H2に次の数式を設定します。

=FILTER(売上表AND[[店舗名]:[売上高]],(売上表AND[売上高]>1200)*(売上表AND[店舗名]="新宿"))

あとは、スピル機能によって、オートフィルで数式をコピーしなくても数式が拡張されます。

このように算出することができました。

FILTER関数の引数を確認しましょう。

最初の引数の「配列」には、テーブルを設定しますので「売上表AND[[店舗名]:[売上高]]」と入力します。

2番目の引数は、「含む」ですが、条件ですね。

「(売上表AND[売上高]>1200)*(売上表AND[店舗名]="新宿")」と設定します。

これで、「店舗名が新宿でかつ、売上高が1200より大きいデータ」という条件を設定することができます。

そして、「*（アスタリスク）」をつかって接続することで、AND条件にすることができます。

Excel。FILTER関数を使えば必要な列だけの抽出した表が簡単につくれます。【extracted table】

Excel。FILTER関数を使えば必要な列だけの抽出した表が簡単につくれます。

＜FILTER関数＞

テーブルから必要な列だけの別表をつくりたい。

しかも、条件に合うデータのみを抽出した表にしたいならば、FILTER関数をつかうことで、手早く作ることができます。

次の表で確認していきます。

A1:F11には、「ランチ売上表」というテーブル名を設定したテーブルがあります。

このテーブルから、「店舗名・商品名・売上高」の列だけで、さらに「売上高が1500より大きい」データのみを抽出した表を作成したい時には、FILTER関数を使えば、手早くつくることができます。

FILTER関数を使わないならば、該当する列をコピーして、オートフィルターをつかって抽出するなどの方法がありますが、簡単な作業を繰り返すことになるので、意外と面倒な作業となってしまいます。

では、H2にどのような数式をつくったのか、確認していきます。

=FILTER(ランチ売上表[[店舗名]:[売上高]],ランチ売上表[売上高]>1500)

この数式だけで完了します。

オートフィルで数式をコピーする必要もありません。

スピル機能のおかげで、一発で終了します。

引数を確認しておきます。

最初の引数「配列」には、「ランチ売上表[[店舗名]:[売上高]]」と設定しました。

これは、テーブルの店舗名フィールドから売上高フィールドまでという意味になります。

次の引数「含む」ですが、これが条件になります。

「ランチ売上表[売上高]>1500」とすることで、「売上高が1500より大きい」という条件として設定することができます。

FILTER関数に限らず、スピル機能と組み合わせることで、さらに便利になる関数がありますので、色々試してみると、作業効率を改善できるかもしれません。

Excel。オートフィルター不要。数式だけで別シートにデータを抽出できます。【Data extraction】

Excel。オートフィルター不要。数式だけで別シートにデータを抽出できます。

＜FILTER関数＞

データから該当するデータを抽出して、その結果を別シートに転記する作業は、簡単ですが、ちょっと面倒でした。

次のような表から、例えば、英語が50点以上のデータを別シートに転記する場合の作業を考えてみましょう。

抽出しなければ、データをコピーすることは出来ませんので、オートフィルターをつかって、英語の点数が50点以上のデータのみになるように抽出します。

その結果をコピーして、転記するシートに貼り付けて、作業は終了という流れが、比較的煩雑な作業をしなくても、対応できる方法かと思われます。

しかし、このような作業をしなくても、「FILTER関数」を使えば、とても簡単に抽出したデータを別シートに転記することができます。

では、FILTER関数をつかって、作業してみましょう。

転記先のシートに数式を作成します。

そして、この関数は、スピル機能をつかうことで、オートフィルで数式をコピーする必要もありません。

 

では、A2に次の数式を作成して確定させてみましょう。

=FILTER(成績シート!A2:E6,成績シート!E2:E6>=50)

英語の点数が50点以上のデータのみが転記されたことがわかります。

数式を確認してみましょう。

最初の引数は、「配列」。

データの範囲ということですね。A2:E6を範囲選択しました。

次の引数は、「含む」。

条件があるフィールドを範囲選択します。

今回は、英語の点数が条件になるので、E2:E6ということになります。その範囲に、「>=50」と50以上という条件を加えます。

たった、これだけで、該当するデータを抽出し、さらに別シートに転記する作業も行うことができました。

最近のExcelには、Office365で登場した新しい関数が追加されましたので、いままで行っていた作業に新しく登場した関数をつかってみると、意外と作業効率を改善できるかもしれませんね。

また、今回紹介した、「FILTER関数」は、アイディアによって、様々な使い方ができそうなので、色々工夫して使ってみると、新たな発見がある気がしますね。