【XLOOKUP,COUNTIF】データ分析の為のExcel関数を解説！

前ページ｜次ページ

前回の覚えておくべきExcelの超基本の関数の発展編として、今回の第3章では、Excelを用いたデータ解析で頻出する関数について解説していきます。

以下の2つが今回登場する関数です。

XLOOKUP関数
COUNTIF関数

こちらはより実践向きの関数で、データ解析を行う上では基本の関数となりますので、業務でExcelを使っている、もしくは使う予定のある方は是非しっかり読んでいってください！

本連載講座【Excelによる統計解析講座】では、Excel未経験の方、自身の無い方でも順を追って学習でき、基礎からデータ分析に必要なスキルまでを身に付ける事が出来ます。

画像が多く、ビジュアルで理解しやすくなっておりますので、読み物が得意ではないという方も大丈夫です！

また、第７章からは、統計学の分野も扱う為、様々な方に役立つ講座となっております。

そのため、

Excelを仕事で使う可能性のある方
Excelに自信のない方
データ分析をExcelでやりたい方

等は是非、TechTeacherが運営する【Excelによる統計解析講座】を使って学習していって下さい！

〈目次〉

第1章：【テーブル】Excelのテーブル（フィルター）を解説！

第2章：【SUM,AVERAGE】Excelの基本的な関数を解説！

第3章：【XLOOKUP,COUNTIF】データ分析の為のExcel関数を解説！

第4章：【棒,折れ線,散布図】Excelの基本的なグラフを解説！

第5章：【ピボットテーブル】Excelの便利機能”ピボットテーブル”を紹介！

第6章：【プレゼン資料】Excelグラフの体裁の整え方について解説！

第7章：【相関分析】相関係数と共分散をExcelを用いて解説！

第8章：【確率変数・正規分布】確率変数や正規分布をExcelで解説！

第9章：【t検定・z検定】母平均と母分散の仮説検定について解説！

第10章：【t検定・z検定】2つの集団の母平均・母比率の仮説検定を解説！

第11章：【仮説検定】カイ二乗検定とF検定をExcelで一瞬で解く！

第12章：【一元配置・二元配置】分散分析についてExcelを用いて解説！

第13章：【回帰分析】Excelを使って単回帰分析と重回帰分析を解説！

また、以下から、今回用いるファイルをダウンロードしてください。
ファイルをダウンロード

XLOOKUP関数
COUNTIF関数
まとめ

XLOOKUP関数

まず、XLOOKUP関数とは、指定した範囲で特定のデータを持つデータを探し出し、そのデータに対応する欲しい値を取り出す事が出来る関数です。

これだけ言われても多くの人はピンと来ないと思うので、これから具体的に説明していきます。

このXLOOKUP関数は Microsoft 365 から、VLOOKUP関数とHLOOKUP関数の後継として導入された関数で、この2つの関数がより使いやすくバージョンアップした様な関数です。

VLOOKUP関数は左端1列の垂直方向(=Vertical)、HLOOKUP関数は上端1行の水平方向(=Horizontal)の値に関して、特定の値を検索(=Look up)する事の出来る関数です。

これに対しXLOOKUP関数は、1つの関数で、任意の行または列における特定の値を検索する事が出来、これだけでもXLOOKUP関数の凄さが少し伝わると思います！

XLOOKUP関数の引数

まず、XLOOKUP関数の引数を紹介します。XLOOKUPの引数は以下の6つです。

=XLOOKUP([検索値],[検索範囲],[戻り範囲],[見つからない場合],[一致モード],[検索モード])

更に、これらの引数の意味は以下の様になります。

[検索値]：どのデータを
[検索範囲]：どこから検索して
[戻り範囲]：どの範囲の値から返すのか
[見つからない場合]：見つからなかった場合は何を表示するのか
[一致モード]：どこまでを一致として判断するか
[検索モード]：どのような順序で検索するか

「6つも引数があって覚えられない、難しい…」と思うかもしれませんが、実際に用いる際には前半の3つで大丈夫です！

そこで今回は、ひとまず3つの引数で使ってみて、残りの3つの引数に関しては、その後に紹介したいと思います。

XLOOKUP関数で一つの値を見つける

まず、前章と同様に、2013年から2019年の各地における各種肉の価格のデータについて、八戸市(地域コード2203)における牛肉の価格をXLOOKUP関数を使って探してみましょう。

なお下の画像では、見やすさの為に前章の内容から肉の種類に関して、牛肉(ロース)と鶏肉のデータのみを抽出しています。
また、関数が入っているセルと同様の式を右にも入れる事で、出力結果を表示しています。

ここでは、[検索値]を地域コードの2203、[検索範囲]をB列、[戻り範囲]をC列として、「[地域コード(B列)]内を検索して、[2203(G3)]のデータに対応する[牛肉(ロース)の値段(C列)]」を検索しました。

このようにしてXLOOKUP関数を用いる事で、八戸市における牛肉の価格は705円である事が分かります。

XLOOKUP関数で複数の値を見つける

ここで、前行程において、牛肉(ロース)の値段だけでなく、鶏肉の値段も探したいとします。つまり、「[地域コード]内を検索して、[2203]のデータに対応する[牛肉(ロース)と鶏肉の値段]」を検索するという事です。

このような時には、以下の様に、戻り値の範囲を2列指定します。

画像から分かる様に、（出力結果において）関数を入力したのはN6であるものの、鶏肉の出力結果がO6にはみ出す(=スピル)事で、八戸市の牛肉(ロース)の値段に加えて鶏肉の値段まで探し出すことが出来ました。

XLOOKUP関数の下3項目の引数

さて、最後に、これまで省略してきた下3項目の引数について解説します。

まず、[見つからない場合]について、前述したように、検索対象が見つからなかった場合に表示する内容を指定する引数です。今回の様に、省略した場合、初期値として、「#N/A」が指定されています。

次に、[一致モード]について、以下の様な設定値を指定する事が出来ます。

設定値	内容
0(既定)	完全一致。見つからない場合は、[見つからない場合]で指定した内容を返します。
-1	完全一致。見つからない場合は、検索値の次に小さな値を返します。
1	完全一致。見つからない場合は、検索値の次に大きな値を返します。
2	ワイルドカードの一致。

表からも分かる様に、この[一致モード]を引数として省略した場合、既定値として0が指定され、検索値と完全一致しない場合は、[見つからない場合]で指定された内容を返します。

また、設定値2のワイルドカードに関して、詳しくは後述しますが、「*」や「?」など、[検索値]に用いる事で、検索値に影響を与えるワイルドカードと呼ばれる記号を考慮して検索する事が出来ます。

最後に、[検索モード]について、以下の様な設定値を指定する事が出来ます。

設定値	内容
1(既定)	先頭の項目から検索を実行します。
-1	末尾の項目から検索を実行します。
2	昇順で並べ替えられた検索範囲を使用して二分探索を実行します。
-2	降順で並べ替えられた検索範囲を使用して二分探索を実行します。

表からも分かる様に、この[検索モード]を引数として省略した場合、既定値として1が指定され、先頭の項目から検索を実行します。

実は、今回用いた八戸市のデータは、2013年~2019年の各年に2,3つで計7つのデータが存在し、今回はその一番上にあった、2019年のデータが出力されていたのです。

また、設定値に”2″又は”-2″を指定した時、コンピュータサイエンスにおいて最も基本的な検索アルゴリズムの一つである二分探索が用いられます。
これにより、数万件以上のデータでも高速で検索する事が出来ます。しかし、予め、データを昇順または降順に並べ替えておく必要があります。

COUNTIF関数

COUNTIF関数とは、条件に当てはまるセルの数を数える(=Count)事が出来る関数です。

これに似た関数として、数値の入っているセルを数えるCOUNT関数、文字列の入っているセルを数えるCOUNTA関数、空白セルを数えるCOUNTBLANK関数など様々なものがあります。

なお、これらの3つの関数は、=[関数名]([カウントするセル範囲])で実行する事が出来ます。

本節では、データ分析に頻繁に用いられるCOUNTIF関数について解説していきます。

COUNTIF関数の引数

COUNTIF関数の引数と書き方は、以下の様になります。

=COUNTIF([数える範囲],”[条件]”)

これに関しては、「[数える範囲]において、[条件]を満たすセルがいくつあるか」といった形で覚えていただければ大丈夫です！

では、実際に使ってみましょう！

COUNTIF関数で数値・文字を数える

まず今回は、COUNTIF関数を用いて、「牛肉(ロース)の値段が700円以上」という条件を満たすセルの数を数えてみましょう。

この結果から、2013年から2019年の各地における牛肉(ロース)の価格のデータにおいて、700円を超えるデータ数は387個である事が分かります。

次に、条件に文字列を指定して、「地方が関東地方」という条件を満たすセルの数を数えてみましょう。

ただし、簡単のため、地域コードは地方名に変換してあります。

この結果から、2013年から2019年の各地における各種肉の価格のデータにおいて、関東地方のものは161個である事が分かります。

COUNTIF関数でワイルドカードを使う

ここで、ワイルドカードについて説明します。

Excelには、条件式等によく用いられる、ワイルドカード(「*」や「?」など)が存在し、これらは以下の様に用いる事が出来ます。

* (アスタリスク)	0文字以上の任意の文字列を表します。 ex) ・”山”の場合：登山、富士山、かちかち山、風林火山/など・”山*”の場合：登山、風林火山、山脈、山羊、案山子/など
? (クエスチョン)	任意の1文字を表します。 ex) ・”?山”の場合：登山、剣山、海山、外山/など・”?山?”の場合：案山子、枯山水、梁山泊/など