仕事がススむ関数(7) SUBTOTALの進化形、AGGREGATE関数では、エラーレコードを無視できます

仕事がススむ関数(7) SUBTOTALの進化形、AGGREGATE関数では、エラーレコードを無視できます

仕事がススむ関数(7) SUBTOTALの進化形

AGGREGATE関数では、エラーレコードを無視できます

Excelは、西暦年数が更新(Excel 2019 → 2021)される大改訂以外にも、ふだんからさまざまなバージョンアップがくりかえされています。

それにともない、関数でも、「進化形」と呼ばれる派生種が、いろいろ誕生しています。

最近の代表的な例でいえば、データベース関数代表格「VLOOKUP」の進化形「XLOOKUP」や、自作関数も作れてしまう「LAMBDA」(ラムダ)関数などがそれにあたるでしょう。

Excel関数は、あまりにも種類が多いので、すべてを覚えることはむずかしく、その必要もありません。あくまでも、自分の仕事に直結するものだけ、何度も使いこなして、業務効率化へつなげていけば良いだけです。

ただし、時には「進化形」情報をキャッチしておくと、置きかえて、従来の仕事がより効率化できる可能性もうまれます。

この記事では、その事例として、以前ご紹介したSUBTOTAL関数の進化形である、AGGREGATE関数の活用事例を見てみましょう。

AGGREGATE関数が便利なのは、エラーレコードを無視できるから

AGGREGATEは、Excel 2010以降のバージョンに実装されている関数で、SUBTOTALの進化形です。復習のため、SUBTOTALの構文と比較してみましょう。

SUBTOTALは、

=SUBTOTAL(集計方法, 参照範囲1,[参照範囲2]...)

という構文で、「集計方法」に1-111までの数字を指定することで、1=平均値を求める、2=数値の個数を求める、3=データの個数を求める、というように、求める結果を変化させられるものでした。

インボイス制度の適格請求書テンプレート(消費税率8%・10%品目明示)をサンプルとして、中間計に使われているSUBTOTAL関数の値をスキップした合計値を算出する方法をご説明しました。

今回ご紹介するAGGREGATEの構文は、

=AGGREGATE(集計方法, オプション, 参照範囲1,[参照範囲2]...)

となり、SUBTOTALとの最大のちがいは「オプション」という引数を設定できるようになったことです(ただし省略可)。 この「オプション」という引数に指定する数値(コード)の一覧は以下の通りとなります。

仕事がススむ関数(7) SUBTOTALの進化形、AGGREGATE関数では、エラーレコードを無視できます
Microsoft support解説ページより転載

この引数の「エラー値」について、くわしくみてみましょう。

請求書内にエラーレコードがあると、そのセルを無視して集計できます

まず、ためしに請求書の数量(個数)欄に、演算できない漢数字(壱, 弐, 参, 肆, 伍)を入れてみましょう。かけ算ができませんので、小計欄も合計欄も、いずれも計算結果はエラーとなります。

<SUBTOTAL請求書>

仕事がススむ関数(7) SUBTOTALの進化形、AGGREGATE関数では、エラーレコードを無視できます

実務上、数量(個数)をわざわざ漢数字で表示することは少ないと予想されますが、たとえば、”(発注/納品予定)品目は決まっているが、数量未決でエラーとなる明細表”などを操作する可能性は、けっして少なくないはずです。

エラーセルを集計からはずす方法としては、他にIFERROR関数(ISERROR関数)などをネスト(入れ子)にし、エラー値をゼロ値もしくは空白に置きかえておく方法もあります。

しかし、わざわざ関数をネスト(入れ子)にして構文を複雑にしなくても、AGGREGATEであれば「オプション」引数に「3」を指定しておくだけで、自動的にエラー値と非表示行をスキップし、集計結果を返す請求書テンプレートになります。

上図のエラーがでた適格請求書テンプレートについて、小計/合計集計欄のSUBTOTAL関数をAGGREGATE関数(オプション引数「3」指定)に置きかえてみましょう。

<AGGREGATE請求書1>

仕事がススむ関数(7) SUBTOTALの進化形、AGGREGATE関数では、エラーレコードを無視できます

明細品目のエラーレコードをすべて無視して、計算できるセルだけを集計し、小計/合計金額を返しています。

エラーレコードを無視する前提なら、ワークシートクラッシャーに要注意!!

さて、ここで間違いさがしゲームです。

<AGGREGATE請求書1>の合計金額は13,325円ですが、以下に図示する<AGGREGATE請求書2>の合計金額は13,217円です。なぜ集計結果が変わってしまったのでしょうか?

<AGGREGATE請求書2>

仕事がススむ関数(7) SUBTOTALの進化形、AGGREGATE関数では、エラーレコードを無視できます

わかりますよね。

仕事がススむ関数(7) SUBTOTALの進化形、AGGREGATE関数では、エラーレコードを無視できます

明細の1行目、1(個)✖️100(円)=(本体価格)100(円), (消費税額)8(円)と計算しなければいけないところ、本体価格と消費税額がエラーになってしまっています。

元のテンプレート自体、数量フィールドに、数値とは別セルで単位「個」を明示してしまっていることが、とても不親切な作り方だともいえます。

しかし、Excelにふなれな、事情を知らない別担当者が勝手にセル結合をしてしまったら?

「1個」というのは、もはや数値ではなく文字列で、演算(かけ算)には使えないレコードとなり、最終的には誤った集計結果がはじき出されてしまうわけです。

まとめ:進化した集計関数AGGREGATEなら、エラーレコードをスキップして集計できます。ただし、気をつけないと、集計結果を間違える恐れもあります。

請求書にかぎらず、実務で必要となる表計算書類には、現実のギャップから発生する数多くのエラー値が含まれることでしょう。そのようなエラーをうまくスキップして、簡単に合計、平均、個数カウントできるAGGREGATE関数のオプション機能は、とても便利です。

これまでSUBTOTAL関数とIFERROR関数を併用していた人などは、この機会にAGGREGATE関数に置きかえてみると、構文がスッキリして、第三者にも理解しやすい資料に変わるでしょう。

ただし、複数の担当者などでブックを共有する場合は、要注意です。Excel技能には差があり、「無視できるエラー」と、「無視できないエラー」の判別がつかない人もいるかもしれません。

とくに、請求書の金額をまちがえるのは、考えただけでゾッとしますね。そのような事故を防ぐためにも、「エラースキップ」するのであれば、かならず別の方法で、データ信ぴょう性を確認できる手段を確保してください。

ダブルチェックにかける時間と、エラーチェックもれで、クライアントへおわびに飛んでいく時間と、どちらが短くすみそうでしょうか?

Excel関数と同様、おたがい、働き方も進化形を目指しましょうね。

小さなお悩みでも、
お気軽にご相談ください!

お急ぎの方はお電話にてお問い合わせください

050-6867-2130
せるワザロゴ

セルワザでは、役に立つEXCELワザをご紹介しております!