この記事では、データ分析する上で知っておくと便利な4つのエクセル術について説明します。 

IF関数

IF関数とは、英語の「IF」が「もし〇〇なら」と訳されるように、設定した条件にしたがって、値を表示させる関数となります。 

図1で示した通り、セル「G2」のように数値を表示することもできますし、セル「H2」のように文字列で表示することもできます。文字列の場合は“”で囲うことを注意してください。（“と”の間に何も入力しないときは「空白」という意味になります） 

図1 IF関数例

また、複数の条件を組み合わせることも可能です。例えば、以下は応用例です。 

①=IF(and(A2=〇,B2=▽),●,◆)
②=IF(or(A2=〇,B2=▽),●,◆)
③=IF(A2=〇,●,IF(A2=▽,▼,IF(A2=◇,◆,×))) 

①については、「and」でくくることで、「もしA2が〇「かつ」B2が▽ならば」という、両方の条件に当てはまれば、という意味になりますし、②については「or」でくくることで、「もしA2が〇「あるいは」B2が▽ならば」という、どちらかの条件に当てはまれば、という意味になります。③についてはIFを重ねていくことで、「もしA2が〇であれば●、もしA2が▽であれば▼、もしA2が◇であれば◆、A2がそれ以外だったら×」という、左から順番に条件を確認していくこともできます。 

〇〇IFS関数

SUMIFS関数やAVERAGEIFS関数、COUNTIFS関数など、何かしらの条件「IF」に当てはまるものの「合計：SUM」や「平均：AVERAGE」、「数：COUNT」を計算する関数となります。 

SUMIFS関数は、図2のように、合計値を求めたいセル群を選択し、条件範囲と条件を選択します。「条件」については図2のようにセルを選択するでもよいですし、数値や文字列を入力するという方法でも同じように計算されます（図2の例では、F2を“果物”と入力しても同じ結果となります）。 

図2 SUMIFS関数例

また、SUMIFSをAVERAGEIFSとすると平均が計算されますし、MAXIFSとすると最大値が計算されます。 

図3のCOUNTIFS関数は、検索条件範囲における条件に合致する「セルの数」をカウントする関数となります。EC事業においては、例えば〇回以上購入している客数や〇〇商品を購入している購入件数をカウントしたりと活用されます。

図3 COUNTIFS関数例

またSUMIFS関数やCOUNTIFS関数の関数名にあるように、「IFS」と“S”が付いています。

その名の通り、=SUMIFS(合計対象範囲,条件範囲①,条件①,条件範囲②,条件②,条件範囲③,条件③)のように、条件については複数設定することが可能です。

VLOOKUP関数

エクセルの関数「VLOOKUP」は、データを整理する際に最も活用する関数です。

VLOOKUPは、データ分析において、異なるデータを繋ぎ合わせるときに頻繁に活用します（図4）。 

図4 VLOOKUP関数例

図4は、A列に記載されている各果物の金額を、セルD1～セルF8の表の中から引っ張ってくる計算式になります。

図41は、セルA1～B4の作成する表と、セルD1～セルF8の参照用の表が同じエクセルファイルの同シートに存在していますが、それぞれ別シートや別ファイルでも問題ありません。同じように計算できます。 

VLOOKUPの使い方は、以下の基本式に則り、値を入力します。 

=vlookup（検索値,検索範囲,列番号,[検索方法]）

まず、図4のセルB2に、 

=vlookup（A2,D2:F8,3,0） 

と入力します。

「vlookup」という文字は小文字でも大文字でも構いません。  

1番目の引数【検索値】

1番目の引数には、検索したいデータ、もしくは、そのデータを入力するセルを指定します。

ここでは、検索したいデータについて果物名で紐付けを行うので、「りんご」が入ったセル「A2」を選択します。 

2番目の引数【検索範囲】

2番目の引数には、検索の対象となる表の範囲を指定します。

ここでは、各果物の金額が記載されている表から紐付けを行うので、表全体である「D2:F8」を指定します。 

3番目の引数【列番号】

VLOOKUP関数は、指定した表の「左端の列」を縦方向（行単位）に検索するよう設計されています。

3番目の引数では、検索する表の左端から“何番目”の列から抽出するか指定します。

列位置は、2番目の引数で指定した表の「左端から何列目か」を指定します。

ここでは果物の金額を紐付けたいので、金額は表の左端から“3番目”ですので「3」を入力します。 

4番目の引数【検索方法】

この引数には、検索したいデータが表の左端列で見つからなかった場合にどうするかを設定します。

検索したいデータが表の左端列で見つからなかった場合にエラー表示させるときは「0」を入力します。

この状態で、もし検索したい果物“りんご”が参照元の表になかった場合は、エラー表示の「#N/A」が表示されます。検索したいデータと完全一致するときのみ表示させたい場合に使用します。

反対に、完全一致するデータが見つからなかった場合に、そのデータを超えない最大値を検索する場合は「0」の代わりに「1」を入力します。例えば、年齢から年代を紐付ける際や、RFM分析をする際に、顧客ごとの売上金額や購入頻度から各RFMのランクを紐付ける際に活用できます。 

ピボットテーブル

エクセルのピボットテーブルは、先ほどの関数「VLOOKUP」と同様、本当によく使います。

当ブログで取り扱っているさまざまな分析方法におけるクロス分析と呼ばれる作業の大半は、この機能によって可能となります。

クロス分析というのは、与えられた多量のデータのうち、2つないし3つ程度の項目に着目して、データの分析や集計を行うことです。 

図5のような列、行ともに容量の多い形式のデータにおいて、ピボットテーブルは大活躍します。 

図5 大容量データ例

ピポットテーブルの作成

まず、図5のような大容量データを全選択します。エクセル上部にあるメニューバーから【挿入】⇒【ピボットテーブル】を選択して、OKボタンを押すと、新たにシートが作成されてピボットテーブルが作成されます。

作成されたシートの右側に出る【ピボットテーブルのフィールド】の下の各テーブルにドラッグしていくことで、クロス集計ができます。

クロス集計する前に、設定しておきたいポイントがあります。

【デザイン】⇒【レポートのレイアウト】を選択して、【表形式で表示】と【アイテムのラベルをすべて繰り返す】を選択します。

そして、【デザイン】⇒【小計】⇒【小計を表示しない】を選択します。 

この作業により、図6のようなクロス分析用の表が作成することができます。 

図6 ピボットテーブルによるクロス分析例

ぜひ、いろいろな数値データをもとにして使ってみてください。 

＼ この記事を読んでいる人におすすめ！ ／