Excel - 便利な関数

提供：MochiuWiki - SUSE, Electronic Circuit, PCB
ナビゲーションに移動 検索に移動

概要

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関数では、1度だけカンマを指定すれば自動的に文字列の間にカンマを挿入して文字列が連結される。

一方、CONCAT関数は、連結する文字列をセルの範囲で指定することができる。
例えば、A1、B1、C1のセルを連結する場合、CONCATENATE関数ではCONCATENATE(A1,B1,C1)と記述するが、CONCAT関数ではCONCAT(A1:C1)と記述できる。


「https://mochiuwiki.one/index.php?title=Excel_-_便利な関数&oldid=10004」から取得