【データ分析】よく使われる43Excel関数とその使い方！
データ分析に欠かせない43のExcel関数、最も総括的なまとめを提供している。
Excelは私たちが仕事でよく使用するツールであり、データ分析においては、データを扱うための最も基本的なツールだ。 伝統的な業界のデータアナリストの多くにっとて、ExcelとSQLをマスターすれば十分だ。
初心者にとっては、R言語などの専門的なツールを急いで学ぶ必要がない場合もある。Excelは十分な機能を備えており、多くの統計・分析・可視化用のアドインが用意されている。 ただ、私たちが普段、情報を扱うときに多くの関数が、どうやって使えばいいのかわからない。
今回のエクセル上級学習は、データ分析によく使われるエクセルの関数と、エクセルを使って簡単な分析を共有する二つの部分に分かれている。
関数について
関数は、複雑な計算をプログラムに対処し、限り関数の形式によって、関連するパラメータを入力すると結果を得る。
例えば、あるエリアの合計を求めるには、SUM (A1: C100) という形式を直接使用することができる。
ですから、関数については、暗記する必要はなく、例えば「フィールドを選択し、Left/Right/Midを使う」関数と、どんなパラメータが必要のを知っていればよいです。複雑なものは全能のGoogleに任せよ～
関数の類別
▲関連・マッチング関数
▲データクリア・処理関数
▲論理演算関数
▲計算・統計関数
▲時間・序列関数
I. 関連・マッチング関数
必要なデータは、多くの場合、異なるエクセル・シートや同じエクセル・シートの異なるシートにある。 データが多すぎてコピーするのが面倒なので、どうやって統合するのか？
このタイプの関連マッチング関数は、複数のテーブルの相関や列を比較する場合で使用される。 そして、複雑なテーブルほどよく使われる。
関数HLOOKUPとVLOOKUPは、どちらもシートのデータを検索するのものだ。
1.VLOOKUP
機能：指定された範囲の1列目で特定の値を検索し、指定した列と同じ行にある値を返す
構文：=VLOOKUP（検索する値、値が検索されるエリア、返された値を含むエリア内の列の番号、完全一致または近似一致-0/FALSEまたは1/TRUEで指定）。
2.HLOOKUP
機能： 表や値の配列の最上段にある値を検索し、表や配列で指定された列の同じ列にある値を返す。
構文：=VLOOKUP（検索する値、値を検索するエリア、返された値を含むエリア内の行の番号、完全一致または近似一致-0/FALSEまたは1/TRUEで指定）。
その違いは、HLOOKUPは調べる値と同じ列の値を返すのに対し、VLOOKUPは調べる値と同じ行の値を返すことだ。
3.INDEX
機能: テーブルやエリア内の値を返したり、値を参照したりする。
構文：= INDEX（値を返すセルエリアまたは序列、列、行）
4.MATCH
機能：指定した範囲内で探したい数値や文字列がどの位置にあるかを列・行番号で返す
構文：= MATCH（値を返すセルエリアまたは配列、検索するエリア、検索方法）
5.RANK
機能：特定エリア内の値の集合におけて、ある値の順位を求める。
構文：=RANK（順位付けた値、順位付けた値のエリア、順位付け方法-0は降順、1は昇順-デフォルトは0）。
6.ROW
機能: セルが配置されている列を返す
7.Column
機能: セルが配置されているカラムを返す
8.Offset
機能: 指定したセルまたはセル範囲から指定された行数と列数だけ移動した位置にあるセル範囲を返す
構文：=Offset（指定セル、移動する列数、移動する行数、何列目に返す、何行目に返す。）
II.データクリア・処理関数
データが処理される前に、抽出されたデータは、文字列のスペースのクリア、セルのマージ、置換、文字列のインターセプト、文字列が発生する場所の発見など、最初にクリーンアップを実行する必要がある。
文字列のスペースを消す：Trim/Ltrim/Rtrimを使う
セルのマージ：concatenateを使う
文字列のインターセプト：Left/Right/Midを使う
セルの内容を置き換える：Replace/Substitute
セル内のテキストの位置を調べる：Find/Search
9.Trim
機能：文字列の両サイドのスペースを消去する
10.Ltrim
機能：セルの右側のスペースを消去する
11.Rtrim
機能：セルの左側のスペースを消去する
12.Concatenate
構文：=Concatenate (cell 1, cell 2 ......)
セルの内容を結合するもう一つの方法は「&」。 結合する内容が多すぎる場合は、「連結」の方が効率的だ。
13.Left
機能：左からの文字列をインターセプトする
構文：=Left(値のあるセル、インターセプトの長さ)
14.Right
機能：右からの文字列をインターセプトする
構文：= Right（値のあるセル、インターセプトの長さ）
15.Mid
機能: 文字列を中央からインターセプトする
構文：= Mid (指定された文字列、開始位置、インターセプトの長)
16.Replace
機能：セル内の文字列を置き換える
構文：=Replace（指定された文字列、置換を開始する位置、置換する文字数、置換後の文字列）
17.Substitute
Substituteとreplaceの機能が似ている。違いは、Replaceは位置を基準にしており、入れ替えの順番、数、新しいテキストが必要だ。それに対し、Substituteはテキスト内容を基準にしており、入れ替え用の古いテキストと新しいテキストを用意し、何番目古いテキストを書く必要がある。
つまり、「Replace」は固定位置のテキストの置き換えで、「Substitute」は固定としたテキストの置き換えを実行している。
18.Find
機能: テキストの位置の検索
構文: =Find (検索する文字、指定された文字列、何番目の文字列)
19.Search
機能：指定した検索文字列を他の文字列の中で検索し、その検索文字列が最初に現れる位置を左端から数えた位置を返す。
構文：=search（検索する文字、その文字が含まれるテキスト、何番目の文字から始まる）
Find関数とSearch関数は、文字の位置を検索するという点ではほぼ同じだが、Find関数は厳密で大文字小文字を区別するのに対し、Search関数はそれを区別しないという違いがある。
20.Len
機能: テキスト文字列の文字数
21.Lenb
機能: テキストに含まれる文字数を返す
III.論理演算関数
22.IF
機能：論理関数のIF関数を使用した場合、条件が真であれば値を返し、条件が偽であれば別の値を返す。
構文：=IF（条件、trueの時の戻り値、falseの時の戻り値）
23.AND
機能：論理的判断で、「と」に相当する。
構文：すべての引数がTrueの場合にTrueを返す。複数条件の判定によく使われる。
24.OR
機能: 論理的な判断で、「または」に相当する。
構文：引数の1つがTrueである限り、Tureを返す。複数条件の判定によく使われる。
IV.計算・統計関数
データを統計する場合、エクセルに付属する様々な計算式をよく使用する。 その重要性は言わずと知れた。
MIN関数：特定エリア内の最小値を求める
MAX関数：特定エリア内の最大値を求める
AVERAGE機能：特定エリアの平均値を計算する
COUNT機能：特定エリア内の数字を含むセルの数を計算する
COUNTIF機能：指定された条件を満たすエリアのセルを算出する機能
COUNTIFS機能：指定された条件で指定されたセルの数をカウントする機能
SUM関数：セルエリア内のすべての値の合計を計算する
SUMIF関数：条件を指定して数値を合計する
SUMIFS関数：指定された条件を満たすセルのセットを合計する
SUMPRODUCT関数：範囲または配列に対応する要素の積を合計した結果を返す
25.MIN
機能：あるエリアの最小値を求める
26.MAX
機能：あるエリアでの最大値を求める
27.AVERAGE
機能：あるアリアの平均値を算出する
28. COUNT
機能：ある数値を含むセルの数を算出する。
29.COUNTIF
機能：指定された条件を満たすエリア内のセル数を算出する
構文：= COUNTIF（セル1：セル2 、条件）
例 = COUNTIF (Table1! A1:Table1! C100, YES) A1～C100のエリアのセルでYESの値を持つセルの数を計算する。
30.COUNTIFS
機能：指定された条件で指定されたセルの数をカウントする
構文：=COUNTIFS（第1の条件エリア、第1の対応条件、第2の条件エリア、第2の対応条件、第Nの条件エリア、第Nの対応条件）
例： = COUNTIFS （Table1！ A1: Table1! A100, 「YES」,Table1! C1: Table1! C100，「NO」 ） Table1のエリアA1～A100の値が「YES」、エリアCの値が「NO」のセルの数を計算する
31.SUM
セル範囲に含まれる数値の合計値を求める
32.SUMIF
機能：条件を満たすセルの合計値を求める
構文：=SUMIF (セル1：セル2 , 条件, セル3：セル4)
32.SUMIFS
機能：指定された条件を満たすセルの集合を集計する
構文：=SUMIFS（実際の合計を求めるエリア、第1の条件エリア、対応する第1の合計を求める条件、第2の条件エリア、対応する第2の合計を求める条件、第Nの条件エリア、対応する第Nの合計を求める条件）。
例：=SUMIFS（Table1！ C1:Table1! C100，Table1! A1: Table1! A100, 「YES」 ,Table1! B1：Table1B100， 「NO」 ）
表1のC1〜C100のエリアで、対応する行のA列の値が「YES」、B列の値が「NO」のセルの合計を算出する。
33.SUMPRODUCT
機能： 対応する配列またはエリアの積の和を返す
構文：=SUMPRODUCT (セル1：セル2 , セル3：セル4)
例： =SUMPRODUCT (Table1! A1:Table1! A100, Table2! B1Table2! B100)
Table1のA1～A100とTable2のB1～B100の積の和、つまりA1*B1+A2*B2+A3*B3+...を計算する。
34.Stdev
標本に基づいて標準偏差の推定値を計算する統計型関数。
35.Substotal
構文：=Substotal (参照エリア、パラメータ)
平均値、カウント、乗算、標準偏差、総和、分散などをパラメータとする集計関数。言い換えれば、この関数を知っていれば、上記のすべてを捨てる。
36.Int/Round
丸め関数
Intは浮動小数点形式で最も近い整数に丸める。
roundは数値を四捨五入して指定された桁数にする。
round(3.1415,2)=3.14 ;
round(3.1415,1)=3.1
V. 時間・序列関数
時間のフォーマットを処理する
37.TODAY
ファイルを開いた日付が自動的に表示される。動的な関数。
38.NOW
現在の時刻を表示する動的な関数。
39.YEAR
機能：付の年を返す。
40.MONTH
機能：日付の月を返す。
41.DAY
機能: 付データから日の数値をもとめる
42.WEEKDAY
機能: 日付のシリアル値から対応する数値が戻されるが、その数値は引数の種類で指定する。フォルトでは、日数は1（日曜日）から7（土曜日）の範囲の整数です。
構文：=Weekday（指定時刻、パラメータ）
43.Datedif
機能：2つの日付の間の日数、月数、年数を計算する。
構文：=Datedif（開始日、終了日、パラメータ）
前のコンテンツ：
1.【データ分析手法と営業分析のモデルをご紹介】https://note.com/jolly_cosmos153/n/n60ed5b1d37a4
2.【面接準備って何すればいい？IT企業面接の必勝法】https://urlzs.com/7qoR6
3.【14種類の統計チャート・グラフ】
https://urlzs.com/fnJdK
データ分析/DX/機械学習についてもっと知りたい方は、FacebookでIT視界をフォローしてください！皆さんの交流を楽しみにしています。^ ^
この記事が気に入ったらサポートをしてみませんか？