Excel - 便利な関数

概要

Excelの特筆すべき点は、Excel 2003からExcel 2007へのバージョンアップで、過去最大ともいえる変更が加わった。
まず、Excel 2003は最大で6万5535行までのデータまでしか扱えませんでしたが、Excel 2007は一気に100万行以上のデータを扱えるようになった。
また、画面の外観も劇的に変化し、リボンUIが採用された。

Excel 2007以降は、大幅な操作性や機能の変更は無く、それほど困ることなく新しいバージョンのExcelを使用できる。
困らずに利用できてしまうが故に、新しく追加された機能の使用に消極的になることがある。

とはいえ、バージョンアップで追加された機能は、使用することで業務が効率的になることも多い。
特に関数は、バージョンアップのたびに新しいものが追加されている。
例えば、Excel 2007では10個以上、Excel 2010では20個以上の関数が追加された。

これだけ追加された関数があると、全ての関数の機能を覚えて使いこなすのはかなり大変である。
ただし、追加された関数の中には、統計関連の関数や、エンジニア向けの関数も数多く含まれているので、
ここでは、一般に使用頻度が高いと考えられる代表的な関数を下表に示す。

表. バージョンアップで追加された関数のうち代表的なもの

Excel	関数名	カテゴリ	代替可能な関数	使用用途
Excel 2007	COUNTIFS	統計	COUNTIF	複数の検索条件に一致するデータの個数を計算する。
	AVERAGEIF	統計	AVERAGE	指定した検索条件に一致するデータの平均を計算する。
	AVERAGEIFS	統計	AVERAGE	複数の検索条件に一致するデータの平均を計算する。
	SUMIFS	数学	SUMIF	複数の検索条件に一致するデータの合計を計算する。
	IFERROR	論理	ISERORR	条件式の結果がエラーとなる場合、指定の値を返す。
Excel 2010	AGGREGATE	数学	SUBTOTAL	指定した集計方法でデータを集計した値を返す。
Excel 2013	FORMULATEXT	検索・行列	なし	セルに記述された数式を文字列として表示する。
	IFNA	論理	ISNA	条件式の結果がエラー"#N/A"となる場合、指定の値を返す。
Excel 2016 Excel 2019	IFS	論理	IF	複数の条件式を順に評価し、評価の結果を指定した値で返す。
	SWITCH	論理	IF、CHOOSE	値を複数の条件で評価し、評価の結果を指定した値で返す。
	TEXTJOIN	文字列操作	CONCATENATE	区切り記号を入れながら複数の文字列を連結する。
	CONCAT	文字列操作	CONCATENATE	範囲を指定して文字列を連結する。

バージョンアップで追加された関数

上記の表を見ると、追加された関数はおおむね次のように分類できる。

• COUNT、AVERAGE、SUM、SUBTOTAL、AGGREGATEといったデータを集計する関数の機能を拡張したもの
• IFの機能を拡張したもの
• 文字列をつなげる関数を拡張したもの

上記の3項目それぞれどのような変更なのか、従来の関数とともに詳しく記載する。

COUNT系

• COUNT

  選択した範囲のセルの内、数値が含まれているセルの個数を集計する

• COUNTA

  選択した範囲のセルの内、空白を除いたセルの個数を集計する

• COUNTBLANK

  選択した範囲のセルの内、空白のセルの個数を集計する

• COUNTIF

  選択した範囲のセルの内、指定した検索条件に当てはまるセルの個数を集計する

• COUNTIFS(Excel 2007以降)

  選択した範囲のセルの内、指定した複数の検索条件に当てはまるセルの個数を集計する

Excel 2007でCOUNTIFS関数が追加され、複数の検索条件を同時に集計できるようになった。
例えば、社員データから"男性かつ45歳以上の社員数"を集計する場合、COUNTIFS関数であれば、以下のように記述すれば集計できる。

COUNTIFS（性別データ,"男性",年齢データ,">=45")

AVERAGE系

• AVERAGE

  選択した範囲のセルの内、数値が含まれているセルを対象として平均を計算する

• AVERAGEA

  選択した範囲のセルの内、数値以外の値のセルも対象として平均を計算する

• AVERAGEIF(Excel 2007以降)

  選択した範囲のセルの内、数値が含まれているセルを対象として、指定した検索条件に当てはまるセルの数値の平均を計算する

• AVERAGEIFS(Excel 2007以降)

  選択した範囲のセルの内、数値が含まれているセルを対象として、指定した複数の検索条件に当てはまるセルの数値の平均を計算する

Excel 2007でAVERAGEIF関数とAVERAGEIFS関数が追加され、検索条件を指定して平均を集計できるようになった。

SUM系

• SUM

  選択した範囲のセルの内、数値が含まれているセルを対象として合計を計算する

• SUMIF

  選択した範囲のセルの内、数値が含まれているセルを対象として、指定した検索条件に当てはまるセルの数値の合計を計算する

• SUMIFS（Excel 2007で追加）

  選択した範囲のセルの内、数値が含まれているセルを対象として、指定した複数の検索条件に当てはまるセルの数値の合計を計算する

Excel 2007でSUMIFS関数が追加され、複数の検索条件を同時に適用して集計できるようになった。

SUBTOTAL・AGGREGATE

• SUBTOTAL

  選択した範囲のセル内の数値を、指定した計算方法で計算する

  
  

  「SUBTOTAL(集計方法,データ範囲)」といった関数式で記述する。

  集計方法は1～11の番号で指定し、1は平均、2は個数のカウント、9は合計等となっている。

  データ範囲にSUBTOTAL関数があると、その値を除外して集計される。

  例えば、売り上げデータの集計時に部門や製品別の小計を入れる場合があるが、SUBTOTAL関数を使用すれば、小計の値を気にすることなく合計を計算できる。

• AGGREGATE(Excel 2010以降)

  選択した範囲のセル内の数値を、指定した計算方法と除外条件で計算する。

  
  

  SUBTOTAL関数の上位機能版ともいえる関数である。

  まず、集計方法がSUBTOTAL関数と比較して8種類ほど増えています。集計方法が増えただけでなく、集計対象からの除外条件を指定できるようにもなりました。利用中のバージョンがExcel 2010以降ならば、SUBTOTALを使用するような場面ではAGGRIGATEを使用した方がよいでしょう。

IF系

• IF

  条件式の結果に対して、指定した値や計算結果を返す。

• IFERROR(Excel 2007以降)

  条件式の結果がエラーになった場合に、指定した値や計算結果を返す。

• IFNA(Excel 2013以降)

  条件式の結果がN/Aエラーになった場合に、指定した値や計算結果を返す。

• IFS(Excel 2016 / 2019以降)

  複数の条件式の結果に対して指定した値や計算結果を返す。

• SWITCH（Excel 2016 / 2019以降）

  値を複数の条件で評価し、評価の結果を指定した値で返す。

IFERROR関数とIFNA関数はそれぞれ、ISERROR関数とISNA関数の上位機能版ともいえる関数である。
ISERROR関数とISNA関数は、セルの値が対応するエラーだった場合にTRUE、そうでない場合はFALSEを返す。
したがって、返した値に応じて、更にIF関数で表示する値を設定する必要がある。
IFERROR関数とIFNA関数は、この表示する値の設定まで実施可能なため、関数式を簡略化できる。

IFS関数とSWITCH関数は、IF関数では出来なかった複数の条件を1度に指定できる。IF関数をネストして実現していた複数の条件による分岐を簡略化できる。

文字列の連結

• CONCATENATE

  複数の文字列をつなげて1つの文字列にする

• TEXTJOIN（Excel 2016・2019で追加）

  区切り記号を入れながら、複数の文字列をつなげて1つの文字列にする

• CONCAT（Excel 2016・2019で追加）

  範囲を指定して文字列をつなげる

　「CONCAT」と「TEXTJOIN」は、文字列をつなげる「CONCATENATE」の機能を拡張した関数です。例えば文字列にカンマを挿入しながらつなげたい場合、CONCATENATEの場合は、つなげる文字としてカンマを文字列の間ごとに入れる必要がありました。TEXTJOINは、一度カンマを指定すれば自動的に文字列の間にカンマを挿入して文字列をつなげてくれます。

　一方CONCATは、つなげたい文字列をセルの範囲で指定することができます。例えばA1、B1、C1のセルをつなげたいとき、CONCATENATEは「CONCATENATE（A1,B1,C1）」と書きますが、CONCATは「CONCAT（A1:C1）」と記載できます。