【Excel分析】「フィルターかけると合計が合わない」を解決！見えないデータを除外する関数3選

【Excel分析】「フィルターかけると合計が合わない」を解決！見えないデータを除外する関数3選

会議中、「A支店のデータだけに絞って見てみよう」とフィルターをかけたのに、合計欄が変わらず焦った経験はありませんか。通常のSUM関数は、行が非表示になっても計算結果を変えません。しかし、表示されているデータだけを正しく集計し、瞬時に分析結果を返す関数が存在します。動的なレポート作成に欠かせない、集計・分析のための賢い関数3選をご紹介します（最新のExcel（Microsoft 365 / 2021以降）の場合）。

1：表示データだけ集計「SUBTOTAL」

リストまたはデータベースの集計値を返すのがSUBTOTAL関数です。この関数の最大の特徴は、オートフィルターで非表示にされた行を計算から除外できる点です。第一引数に集計方法（9ならSUM、1ならAVERAGEなど）を指定することで、合計だけでなく平均や個数カウントも可能です。

◇9：フィルターで隠れた行は無視しますが、手動で「非表示」にした行は計算に含みます。
◇109：フィルターも手動非表示も、どちらも無視して計算します。実務で「パッと見て見えている数字だけ」を合計したいなら、迷わず 109 を使うのが正解です。

フィルター操作と連動して数値が変化するため、インタラクティブな表作成に必須です。

2：エラーも無視して集計「AGGREGATE」

リストまたはデータベースの集計値を返しますが、SUBTOTALよりさらに高機能なのがAGGREGATE関数です。非表示の行だけでなく、「エラー値」も無視して集計することができます。これまでは範囲内に一つでもエラーがあると合計もエラーになっていましたが、AGGREGATEならエラーをスキップして計算できるため、データの不備に強い堅牢な集計表が作れます。

3：順位を一瞬で判定「RANK.EQ」

数値のリストの中で、指定した数値の序列（順位）を返すのがRANK.EQ関数です。売上ランキングや成績順位をつける際、いちいち並べ替えを行わなくても、この関数を入れるだけで「1位」「2位」といった順位が表示されます。同じ数値があった場合は同じ順位（1位が2人いれば次は3位）となります。大量のデータからトップ層を把握したい分析業務で非常に役立ちます。 

「見えているものだけを計算する」技術は、分析の精度とスピードを両立させます。動的な集計関数を使いこなし、ライブ感のあるデータ分析を実現してください。

＜出典＞

Excel 関数 (機能別)（Microsoft）

【Excel裏ワザ】ショートカット特集！行の先頭へ瞬時にジャンプも！Homeキーを使い倒す方法

イチオシ編集部　ガジェット部

この記事を読む

※記事内における情報は原稿執筆時のものです。