コラム

コラム 詳細

2021年06月27日 (日)コラム

人事担当のためのEXCELマメ知識②

こんにちは、HRプラス社会保険労務士法人の武田素子です。

人事担当のためのEXCELマメ知識、第2回目のテーマは、「数式エラーがある場合の合計の出し方」です。
VLOOKUPでデータをひっぱってきたけれど、エラー(#N/A)がある。そうすると、普通に合計をSUM関数で出そうと思っても、合計値もやはりエラー(#N/A)になってしまいます。
そんな時に使うと便利な3つの関数について解説します。

 

ある果物店の、1日の売り上げを従業員別に表した表が図1です。

【図1】売上表

これを従業員別、くだもの別に集計しなおした表が図2です。

【図2】売上集計

 

従業員名とくだもの名を検索キーに指定してVLOOKUPでデータを引っ張ってきていますが、従業員によっては売り上げがなかった商品があります。たとえば、佐藤すみれさんのキウイの売り上げはなかったため、該当するデータなし(#N/A)のエラーが出てしまっています。
そのため、合計の関数もエラーになってしまっています。
こうした場合に合計を正しく算出するための関数を3つご紹介しましょう。

 

1.エラー表示を出さないようにする  ~IFERROR関数~

エラーが出るのは想定内で、エラーの場合は0にしてしまいたい。そんな時はVLOOKUP関数とIFERROR関数を組み合わせます。

IFERROR関数は、左側の式(上記の例で言えばVLOOKUP)の結果がエラーになるのであれば0を表示させる、という関数です。

実際に、図2でVLOOKUPを使用していたすべてのセルにIFERROR関数を組み込んでみると、図3のようにエラー(#N/A)が消え、0で表示されるため、合計値も算出出来るようになります。

【図3】

 

2.フィルタをかけたものだけ合計する  ~SUBTOTAL関数~

エラーであることを認識するために、エラー表示はそのままにしたい。そのうえで合計値を出したい。
そんな時は、SUM関数ではなく、SUBTOTAL関数を使用することが考えられます。

SUBTOTAL関数は、フィルタをかけて表示しているものだけを集計する効果があります。
最初の引数の“9”は、合計(SUM)を出す引数です。他に“1”(AVERAGE)や”3“(COUNTA)などもあります。
また、“109”はフィルタで非表示にしたものだけでなく、セルを非表示にしたものも除外する引数です。

実際の例をみてみましょう。
図4は、図2でSUM関数を使っていた合計欄の式を、SUBTOTAL関数に変えたものです。

【図4】

これに対して、B2セルのフィルタで#N/Aを表示しない設定をします。

【図5】

すると、B7セルの表示が11に変わり、正しく合計が表示されていることがわかります。
なお、C7セルも表示されている数字だけの合計に変わっているので要注意です。

SUBTOTALは非常に便利で、フィルタを「色のついたセルを表示する」「一定以上の数値のものを表示する」というように変化させていけば、合計が変わります。
たとえば、図1の表に合計行を作り、SUBTOTALを使用すれば、フィルタの掛け方によって「さくらんぼの合計」「金子みずきさんの合計」を出すことができます。

 

3.エラーは集計しないようにする  ~AGGRIGATE関数

AGGREGATE関数は、SUBTOTAL関数同様、集計する範囲を指定できる関数です。
SUBTOTAL関数はフィルタによって集計する範囲を変えるものですが、AGGREGATE関数は引数でその対象を指定します。

1番目の引数“9”は、SUBTOTAL関数と同様、合計(SUM)を出す引数です。
2番目の引数“6”は、「エラーを無視」を意味する引数です。

図6は、SUM関数の代わりにAGGREGATE関数を使っています。

【図6】

エラーを無視して集計できていることが分かります。
フィルタで都度指定するのではなく、最初からエラーを除外して合計値が出せるのが特色です。

 

 

今回のテーマ、「数式エラーがある場合の合計の出し方」とは趣旨とはずれてしまいますが、AGGGREGATE関数を使用すると非常に便利な場面をご紹介します。

図1の表に、従業員ごとの小計をつけてみたのが図7です。小計行にはSUBTOTAL関数を使用しています。

【図7】

この表に合計行をつける場合、小計行をひとつひとつ拾って足し込んでいませんか?
ここでAGGREGATE関数を使うことで、そのような手間を削減することができます。

 

2番目の引数“0”は、「入れ子になっているSUBTOTAL関数及びAGGREGATE関数を無視」を意味する引数です。

実際に使用してみたのが図8です。

【図8】

合計が正しく算出されているのがわかります。
小計行にSUBTOTAL関数を使うのがミソで、SUM関数では除外しないので注意してください。

実を言うと、私はこのオプションをたった今まで知らなくて、このコラムを書くために引数の中身を精査して気づきました。

知っていれば工数が大幅に削減できる関数は他にもたくさんあるはずです。コラムを通じて、もっともっと発掘していければと思います。

それでは、次回もお楽しみに!

ページトップへ戻る