フィルタを掛けずに、フィルタっぽくデータ抽出するためのEXCEL関数をお勉強。

表のとある列のうち、Aというワードの行だけデータ抽出したい、なんてエクセルを触っていると思うことがあります。

フィルタをかければ一発解決することなのですが、事情があってフィルタを使えないことも。

そんなときになんとか関数を使ってできないかというところで教わったやり方が目から鱗で、ご紹介しようと思います。

 

 

問題のシチュエーション

例えば上のようなデータがあったとして、この中から

性別が男で、年代が40

のものだけ抽出したいとします。

もしフィルタが使えれば、そのまんま「性別=男」、「年代=40」でフィルタを掛けてしまえば欲しいデータだけが表示され、後はどこかにコピペすればよいだけです。

ただ今回はフィルタが使えない(使いたくない)というシチュエーションを考えます。

元データをエクセルに貼り付けたら、あとは自動的に処理が走って、いい感じのレポートが自動でできるようにしたい

なんて時はいちいちデータはりつけた後にフィルタかけて~という手間も省きたいのです。

さてどうすればよいでしょう。

 

 

スプレッドシートではFILTER関数という便利なものがある ※EXCELでは2018/10/23現在使えない

 

ちなみにGoogleスプレッドシートでは、EXCELにはないFILTER関数というものがあり、このようなデータ抽出が一発でできます。

FILTER

ソース範囲をフィルタ処理して、指定した条件を満たす行または列のみを返します。

構文

FILTER(範囲, 条件1, [条件2, ...])

  • 範囲 – フィルタ処理するデータを指定します。
  • 条件1 – 範囲の 1 列目または 1 行目に対応する TRUE 値または FALSE 値を含む列か行、あるいは TRUE または FALSE と評価される配列数式を指定します。
  • 条件2 ... – [省略可] – 範囲内の対応する行または列が FILTER を通過するかどうかを示す TRUE または FALSE のブール値を含むその他の行または列を指定します。これらの行や列と評価される配列数式を含むこともできます。条件のすべてで同じ種類(行または列)を対象にする必要があります。行の条件と列の条件を混合することはできません。
    • 条件の引数には、範囲と同じ長さを指定する必要があります。

ドキュメントエディタヘルプ

 

今回の例では、

=FILTER(A1:D11,A1:A11=”男”,B1:B11=40)

で一発で取得できます。便利。

ちなみにFILTER関数の中の条件設定箇所で、ORやANDは使えないようです。

今回のように「性別=男 AND 年代=40」であれば、そのままカンマで条件を増やしていけば大丈夫です。

(=FILTER(A1:D11,A1:A11=”男”,B1:B11=40))

ただ「性別=男 OR 年代=40」のようにOR条件で抽出したい場合は、

=FILTER(A1:D11,(A1:A11=”男”)+(B1:B11=40))

というように条件箇所はカッコでくくって足すように記載することで解決です。

 

方法はシンプルで、複数行をくっつけて、番号振ってキーにする。

 

とはいえEXCELではFILTER関数がないので一工夫する必要があります。

方法としては、複数行をくっつけて番号をふって、それをキーにVLOOKUPなりINDEX+MATCHなりで引っ張るという方法を取ります。

今回の例では、

「男_40_1」みたいに、「性別_年代_番号」というものを各行ごとに作成して、それをキーにするということです。

3STEPにわけて具体的に解説します。

 

STEP1:複数行くっつける

まずは「男_40」を作りましょう。※”_”は入れなくてもよいです。わかりやすくするためにつけてます。

作り方は2種類あって好きな方でよいですが、個人的には後者が好きです。

 

①&でつなぐ

「セル1&”_”&セル2」というように入力すると、「セル1_セル2」というように、&をまたいだ各パーツがくっつきます。

 

②CONCAT関数を使う

=CONCAT(セル1,”_”,セル2)

と入力すると、「セル1_セル2」というようにカンマを挟んだものがくっついて出力されます。

 

くっつけたら、オートフィルですべての行でこの処理を行います。

 

STEP2:番号をふってまたくっつける

次は番号をふります。(男_40_1をつくる)

番号をふる理由としては、「性別_年代」に重複があるためです。

最終的にくっつけたものをキーにVLOOKUPなりをしたいので、重複があるとうまくいきません。

COUNTIF関数を使います。

=COUNTIF($E$3:E3,E3)

と入力し、それをオートフィルで↓までコピーします。

やっていることは、各行ごとにE列のE3からその行までの範囲の中で、その行のE列の値が何回出てきているかカウントしています。

言葉だとわかりづらいので、↓のキャプチャがわかりやすいと思います。

「男_20」~「男_40」の範囲(青色)の中で、「男_40」が何回出てきているかをカウントして数字を返してくれます。

ポイントは絶対参照にする箇所です。

=COUNTIF($E$3:E3,E3)

太字の箇所だけ絶対参照にするので、間違えないように気をつける必要があります。

これで、男_40が2回でてきても、それぞれに1、2と番号が振られるので、行を識別できるようになりました。

 

STEP1と同様に、「性別_年代_番号」でくっつけてしまいましょう。

 

STEP3:関数を使って引っ張る

ここまでできたらもう終わったも同然です。

どこか別の箇所に、「性別_年代_番号」の場所を作って、そこをキーにして各値を持ってくるだけです。

こんな感じで場所をつくってあげておいて、

INDEX+MATCHでそれぞれ引っ張る、と。

 

どうやらスプレッドシートのFILTER関数等がEXCELでも使えるようになるらしい!!

 

とはいえEXCELでこれやるのはとても面倒です。

Calculating with ease using dynamic arrays

With dynamic arrays, we continue to invest in making advanced formulas easier to use. Using dynamic arrays, any formula that returns an array of values will seamlessly “spill” into neighboring unoccupied cells, making it as easy to get an array of values returned as it is to work on a single cell. You can immediately harness the power of dynamic arrays by using one of the new FILTER, UNIQUE, SORT, SORTBY, SEQUENCE, SINGLE, and RANDARRAY functions to build spreadsheets that would previously have been nearly impossible. So now, rather than writing many complex formulas to solve a multi-cell problem, you can write one simple formula and get an array of values returned. Read more about dynamic arrays in this Tech Community blog.

https://www.microsoft.com/en-us/microsoft-365/blog/2018/09/24/bringing-ai-to-excel-4-new-features-announced-today-at-ignite/#.W6lmE0xk0D8.twitter

New Functions
Here is the full set of functions that will be accompanying dynamic arrays.
• FILTER – filters an array of data based on criteria you define.
• UNIQUE – returns a list of unique values from a list or range.
• SORT – sorts an array of values.
• SORTBY – sorts an array based on a corresponding array.
• SEQUENCE – generates a list of sequential numbers, such as 1, 2, 3, 4.
• SINGLE – accepts a range or array and returns a single value using implicit intersection.
• RANDARRAY – returns an array of random numbers between 0 and 1.
We cannot wait to see how our users use these new building blocks in their spreadsheets.
Availability notes:
Dynamic arrays functionality is available in Preview for users signed up for the Office 365 Insiders Program starting today. We will initially roll out to a subset of Insider users on Windows so that we can gather feedback and monitor feature quality. Over the next few months, we’ll be increasing the number of Insider users with access to dynamic arrays and light up support for Excel on Mac, web, and mobile.
Office365 Insiders Programというのは、オフィスの最新機能をお試しで使えるサンプルモニターみたいなやつのようです。

今はモニターにだけ使ってもらってフィードバックを回収している段階のようです。

このエントリが書かれていたのが9/25でしたが、正式に搭載はいつ頃になるのでしょうか。楽しみですね!