=SUM() をデフォルトにするのはやめましょう: Excel の合計行は思っているよりも強力です

素朴な =SUM() 式は数十年にわたってスプレッドシートに使用されてきましたが、Excel の Total Rows は多くの現実のシナリオで静かに強力になってきました。フィルター対応の計算、組み込みの統計、自動拡張により、あらゆるテーブルが、非常に破壊が困難な動的な自己更新レポートに変わります。

Excel ユーザーの多くは、今でも何も考えずに =SUM(B2:B5000) のような数式を書いています。ただし、範囲外の行を追加すると数式が壊れます。データをフィルターすると、非表示の値が合計されます。小計を入力すると 2 倍にカウントされます。また、複数の列の平均、個数、最大値が必要な場合は、さらに多くの数式を維持する必要があります。ただし、Total Row ははるかに直感的です。

最初に Total Row を開始します (その後、良い点に取り掛かります)

Excel テーブルをまだ使用していない場合、合計行は使用できません。データセット内の任意の場所をクリックし、Ctrl+T を押して、テーブルにヘッダーがあることを確認します。

接続済み

Excel でテーブルの名前を変更しない場合は、今日から始めましょう。

名前には何が入っているのでしょうか？まあ、かなりの数です。

次に、「テーブルデザイン」タブに移動し、「合計行数」を確認します（またはCtrl+Shift+Tを押します）。

Excel ではテーブルの下部に新しい行が追加され、各列には集計関数を選択できるドロップダウン メニューが表示されます。合計や平均などのおなじみのオプションに加えて、カウント、最小、最大、標準偏差、分散、さらに多くの関数への入り口が表示されます。

それがセットアップです。この機能が、日常のレポートで最初に表示されるよりもはるかに強力である理由を説明します。

=SUM() よりも合計行の信頼性が高いのはなぜですか

ドロップダウン メニューから[合計]を選択した場合、Excel では標準の SUM 式は挿入されません。代わりに、以下を使用します。

=SUBTOTAL(109,[Revenue])

SUBTOTAL 関数はフィルターされたテーブル内でインテリジェントに動作するように設計されているため、この違いは重要です。 109 コードは、表示されている行のみをカウントし、フィルターされた行や手動で非表示になっている行は無視するように Excel に指示します。また、データセット内に他の小計式が存在する場合の二重カウントも防止します。

レポートを月、地域、製品、またはチームごとに定期的にフィルタリングする場合、従来の =SUM() は、表示されるかどうかに関係なく、範囲内のすべてを合計し続けます。一方、合計行は、画面上の内容を反映して更新されます。これだけでも、現実世界のレポートの信頼性が高まります。

接続済み

Microsoft Excelの小計と集計

より柔軟に小計を生成します。

ほとんどの人は Total Rows を使用して追加したりそこで停止したりしますが、これが本当に役立つ、より微妙なオプションがいくつかあります。

たとえば、列を「カウント」(空白以外のセルを含む) と「カウント数」(数値のみを含む) の間で切り替えます。これら 2 つの結果が一致しない場合は、列のどこかに数値がテキストとして保存されている可能性があります。

データ整合性エラーを探したり、診断式を作成したりする代わりに、Total Row は問題にすぐにフラグを立てることができます。これは、テーブルにすでに組み込まれている監査ツールです。

基本的な統計に限定されない

[合計行数]ドロップダウン メニューには制限がありません。[その他の関数]をクリックすると、Excel の関数ライブラリ全体が開きます。

データセットに過剰な値がある場合は、以下を追加します。

=MEDIAN([Revenue])

平均値よりも「典型的な」値のより現実的な感覚を得ることができます。異常に大規模な売上が発生すると平均が歪む可能性がありますが、平均は安定したままです。

数式は合計行内に存在するため、テーブルに添付されたままになり、自動的に展開され、追加のメンテナンスを行わなくてもライブ概要の一部として残ります。その結果、単にメトリクスを計算するのではなく、構造化されたレポートを作成することになります。

合計線は、単なる合計ではなく、一貫性を明らかにします。

ドロップダウン メニューの StdDev (標準偏差) および Var (分散) オプションを使用すると、多くのスプレッドシートが無視しているもの、つまりデータの定常性を分析できます。これは、チーム間または期間間のパフォーマンスを比較する場合に特に役立ちます。

この例では、東部門と西部門の両方で 4,000 ドルの収益が発生しました。

しかし、合計からは分からないのは、ある部門が毎月一貫した成果を上げている一方で、別の部門は浮き沈みが激しいということです。ここで StdDev が威力を発揮します。通常、個々の値が平均からどの程度乖離しているかを測定します。数値が低いほど結果が一貫していることを示し、数値が高いほど不安定であることを示します。

この場合、東の StdDev は 0.00 ですが、西では 983.19 です。

どちらの部門も同じ総収益を生み出しますが、一方は完全に安定しており、もう一方は大きく変動しています。

その違いが重要なのです。予測可能な数値は計画を立てるのが簡単ですが、不安定な数値にはリスクが伴います。したがって、StdDev を Total 行に追加すると、単純な合計を超えて、表示品質の評価を開始できます。

フィルタリングされた合計と実際の合計を一緒に表示できます

デフォルトでは、TOTAL 行は SUBTOTAL を使用するため、フィルターを尊重します。通常、これが必要なものです。

ただし、次のように手動で入力することもできます。

=SUM([Revenue])

フィルターを無視した合計行が必要な場合は、[合計行]セルに入力します。 Excel では、構造化されたコンテキストを維持しながら、このオーバーライドが可能です。

これにより、柔軟性が得られます。1 つの列にはフィルタリングされた結果が反映され、別の列には全体の合計が表示されます。このタイプの並べて比較をスタンドアロンの式できれいに管理するのは困難です。

トラブルシューティング: 合計行が「間違っている」ように見える場合

標準の SUM 式とは動作が異なるため、合計行は、意図されたとおりに実際に実行しているときに、正しく表示されないことがあります。何かが間違っているように見える場合、通常、問題は予測可能であり、修正するのは簡単です。

では、=SUM() は死んだのでしょうか?完全にはそうではありません。これは今でもその役割を果たしており、特に関数が値をまたがったり値を返したりできる最新の Excel では、その動作を理解することは役に立ちます。ただし、構造化され、フィルター処理されたデータの場合は、通常、合計行が統計を要約するための最もスマートで柔軟な方法です。

OS

Windows、macOS、iPhone、iPad、Android

無料トライアル

1ヶ月

Microsoft 365 には、最大 5 台のデバイスで Word、Excel、PowerPoint などの Office アプリ、1 TB の OneDrive ストレージなどへのアクセスが含まれています。