前回の覚えておくべき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で一瞬で解く！
また、以下から、今回用いるファイルをダウンロードしてください。
ファイルをダウンロード
本ブログを運営しているTech Teacherは、
プログラミング家庭教師サービスを運営しています。
完全マンツーマン・フルオーダーメイドで
あなたが必要な指導を提供します。
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) ・”?山”の場合：登山、剣山、海山、外山/など ・”?山?”の場合：案山子、枯山水、梁山泊/など |
よって、これらのワイルドカード(今回は”*(アスタリスク)”)を用いて、中国地方と四国地方のデータがいくつあるのかを数えてみましょう。
この結果から、四国地方と中国地方のデータ数の合計は、70個である事が分かります。
(発展)COUNTIFS関数で複数条件を満たすデータ数を数える
最後に、複数条件を満たすデータ数を数えてみましょう。複数条件なので、今回用いる関数はCOUNTIFS関数になり、その書き方は以下の様になります。
=COUNTIFS([条件1を適応する範囲],”[条件1]”,[条件2を適応する範囲],”[条件2]”,…,[条件nを適応する範囲],”[条件n]”)
これに関しては、「[条件1を適応する範囲]で[条件1]を満たし、かつ[条件2を適応する範囲]で[条件2]を満たし、…、かつ[条件nを適応する範囲]で[条件n]を満たすデータはいくつあるか」というように覚えていただければ大丈夫です！
これを用いて今回は、「近畿地方で牛肉(ロース)の価格が700円以上」という条件を満たすデータの個数を数えてみましょう。
この結果から、2013年から2019年の各地におけるデータにおいて、近畿地方で牛肉(ロース)の価格が700円以上のデータの個数は71個である事が分かります。
『Tech Teacher』3つの魅力
魅力1. オーダーメイドのカリキュラム
『Tech Teacher』では、決められたカリキュラムがなくオーダーメイドでカリキュラムを組んでいます。「質問だけしたい」「相談相手が欲しい」等のご要望も実現できます。
魅力2. 担当教師によるマンツーマン指導
Tech Teacherでは、完全マンツーマン指導で目標達成までサポートします。
東京大学を始めとする難関大学の理系学生・院生・博士の教師がが1対1で、丁寧に指導しています。
そのため、理解できない箇所は何度も分かるまで説明を受けることができます。
魅力3. 3,960円/30分で必要な分だけ受講
Tech Teacherでは、授業を受けた分だけ後払いの「従量課金制」を採用しているので、必要な分だけ授業を受講することができます。また、初期費用は入会金22,000円のみです。一般的なプログラミングスクールとは異なり、多額な初期費用がかからないため、気軽に学習を始めることができます。
まとめ
・魅力1. 担当教師によるマンツーマン指導
・魅力2. オーダーメイドのカリキュラム
・魅力3. 3,960円/30分で必要な分だけ受講
質問のみのお問い合わせも受け付けております。
まとめ
今回のExcelによる統計解析講座第3章では、以下の2つの関数について解説を行いました。
- XLOOKUP関数
- COUNTIF関数
これらの関数は、Excelでデータ解析を行う際には基本に当たる部分なので、業務や作業で使う、使う予定の方は何回も読んで、マスターして頂けると嬉しいです！
