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との最大のちがいは「オプション」という引数を設定できるようになったことです(ただし省略可)。 この「オプション」という引数に指定する数値(コード)の一覧は以下の通りとなります。
この引数の「エラー値」について、くわしくみてみましょう。
請求書内にエラーレコードがあると、そのセルを無視して集計できます
まず、ためしに請求書の数量(個数)欄に、演算できない漢数字(壱, 弐, 参, 肆, 伍)を入れてみましょう。かけ算ができませんので、小計欄も合計欄も、いずれも計算結果はエラーとなります。
<SUBTOTAL請求書>
実務上、数量(個数)をわざわざ漢数字で表示することは少ないと予想されますが、たとえば、”(発注/納品予定)品目は決まっているが、数量未決でエラーとなる明細表”などを操作する可能性は、けっして少なくないはずです。
エラーセルを集計からはずす方法としては、他にIFERROR関数(ISERROR関数)などをネスト(入れ子)にし、エラー値をゼロ値もしくは空白に置きかえておく方法もあります。
しかし、わざわざ関数をネスト(入れ子)にして構文を複雑にしなくても、AGGREGATEであれば「オプション」引数に「3」を指定しておくだけで、自動的にエラー値と非表示行をスキップし、集計結果を返す請求書テンプレートになります。
上図のエラーがでた適格請求書テンプレートについて、小計/合計集計欄のSUBTOTAL関数をAGGREGATE関数(オプション引数「3」指定)に置きかえてみましょう。
<AGGREGATE請求書1>
明細品目のエラーレコードをすべて無視して、計算できるセルだけを集計し、小計/合計金額を返しています。
エラーレコードを無視する前提なら、ワークシートクラッシャーに要注意!!
さて、ここで間違いさがしゲームです。
<AGGREGATE請求書1>の合計金額は13,325円ですが、以下に図示する<AGGREGATE請求書2>の合計金額は13,217円です。なぜ集計結果が変わってしまったのでしょうか?
<AGGREGATE請求書2>
わかりますよね。
明細の1行目、1(個)✖️100(円)=(本体価格)100(円), (消費税額)8(円)と計算しなければいけないところ、本体価格と消費税額がエラーになってしまっています。
元のテンプレート自体、数量フィールドに、数値とは別セルで単位「個」を明示してしまっていることが、とても不親切な作り方だともいえます。
しかし、Excelにふなれな、事情を知らない別担当者が勝手にセル結合をしてしまったら?
「1個」というのは、もはや数値ではなく文字列で、演算(かけ算)には使えないレコードとなり、最終的には誤った集計結果がはじき出されてしまうわけです。
まとめ
進化した集計関数AGGREGATEなら、エラーレコードをスキップして集計できます。ただし、気をつけないと、集計結果を間違える恐れもあります。
請求書にかぎらず、実務で必要となる表計算書類には、現実のギャップから発生する数多くのエラー値が含まれることでしょう。そのようなエラーをうまくスキップして、簡単に合計、平均、個数カウントできるAGGREGATE関数のオプション機能は、とても便利です。
これまでSUBTOTAL関数とIFERROR関数を併用していた人などは、この機会にAGGREGATE関数に置きかえてみると、構文がスッキリして、第三者にも理解しやすい資料に変わるでしょう。
ただし、複数の担当者などでブックを共有する場合は、要注意です。Excel技能には差があり、「無視できるエラー」と、「無視できないエラー」の判別がつかない人もいるかもしれません。
とくに、請求書の金額をまちがえるのは、考えただけでゾッとしますね。そのような事故を防ぐためにも、「エラースキップ」するのであれば、かならず別の方法で、データ信ぴょう性を確認できる手段を確保してください。
ダブルチェックにかける時間と、エラーチェックもれで、クライアントへおわびに飛んでいく時間と、どちらが短くすみそうでしょうか?
Excel関数と同様、おたがい、働き方も進化形を目指しましょうね。
