Windows 7 & Office 2010 Excel 2010

色付きセルの合計・個数などの集計(テーブルを使用しない方法も解説)

投稿日:2017年1月9日 更新日:

Excelの表でセルに色を付ける方は多いと思います。

この色付きのセルのみを合計したいという場合、Excel2007以降のバージョンであれば簡単に集計することができます。

テーブルに変換すると簡単に集計できますが、テーブルを使用しなくても集計することができます。

ここでは、Excel2010で解説します。

下のような表があります。

セルに色を付けた表

テーブルに変換

まず、この表をテーブルに変換します。

表内のセルを1つ選択して、[挿入]タブの[テーブル]グループにある[テーブル]をクリックします。

[テーブル]のポップヒントには以下のように記されています。

テーブル(Ctrl+T)

関連データの管理および分析を行うテーブルを作成します。

テーブルにすると、シート内のデータの並べ替え、フィルター、書式設定が簡単に行えます。

[挿入]タブの[テーブル]グループの[テーブル]

[テーブルの作成]ダイアログボックスが表示されます。

データ範囲を確認して、[先頭行をテーブルの見出しとして使用する]のチェックはオンにするか、オフにするかを決定して[OK]ボタンをクリックします。

参考[テーブルの作成]ダイアログボックスを表示するショートカットキーは[Ctrl]+[T]です。

[テーブルの作成]ダイアログボックス

下のようにテーブルになり、先頭行に自動的にフィルターボタン(▼ボタン)が付きます。

テーブルの完成

参考テーブルを解除して通常の表にする方法については、以下の記事で解説しています。

テーブルのサイズ変更と解除(自動拡張の設定はオートコレクトで)

Excelでテーブルを作成した後、テーブルのサイズを変更する方法です。 また、テーブルを通常の表に変換する方法と、自動拡張の設定についても解 ...

続きを見る

テーブルのスタイルを[なし]に設定

テーブルにすると、自動的にスタイルが設定されます。

スタイルが設定されていると元のセルの色が分かりづらくなるので、スタイルは[なし]に設定します。

テーブル内のセルを選択していると、[テーブルツール]タブが表示されていると思います。

[デザイン]タブをクリックします。

[デザイン]タブの[テーブルスタイル]グループにある[その他]ボタンをクリックすると、スタイルの一覧が表示されます。

テーブルスタイルの[その他]ボタン

既定のテーブルのスタイルは、[テーブルスタイル(中間)2]です。

[テーブルのスタイル]から[なし]を選択します。

[スタイル]の一覧から[なし]を選択

テーブルに集計行を追加

[デザイン]タブの[テーブルスタイルのオプション]グループにある[集計行]のチェックボックスをオンにします。

テーブルスタイルのオプションの[集計行]

テーブルの最終行に[集計]行が追加されます。

B列とC列の最終セルをアクティブにして、プルダウンから[合計]を選択します。

集計のプルダウンから[合計]を選択

集計行のセルには[SUBTOTAL]関数の数式が入ります。

これで準備OKです。

数式SUBTOTAL

選択したセルの色でフィルター

色付きのセルで右クリックします。

ショートカットメニューの[フィルター]から[選択したセルの色でフィルター]をクリックします。

ショートカットメニューの[フィルター]-[選択したセルの色でフィルター]

または、先頭行のフィルターボタン(▼ボタン)をクリックして、メニューを表示します。

そして、[色フィルター]のサブメニューにある[セルの色でフィルター]から、目的の色をクリックします。

ドロップダウンボタンから[色フィルター]を選択

集計行に合計値が表示されます。

色付きセルのみの合計を求めることができます。

色付きセルの合計

参考SUBTOTAL関数は、オートフィルターを使用した時に、表示されているセルのみを集計することができます。

SUM関数では、フィルターの結果に含まれていない行も集計されます。

SUBTOTAL 関数では、集計方法の値にかかわらず、フィルターの結果に含まれていない行はすべて無視されます。

SUBTOTAL 関数 - Office サポートより

色付きセルの個数を求める

また、色付きセルのデータの個数を求めるには、集計行のセルを選択してプルダウンから[数値の個数]に切り替えると、色付きセルの個数を求めることができます。

テーブルの場合は、集計方法を簡単に変更することができます。

集計のプルダウンから[数値の個数]

色フィルターの解除

フィルターを解除する場合は、フィルターボタンをクリックして、メニューを表示します。

["〇〇"からフィルターをクリア]をクリックします。

フィルターをクリア

テーブルを使用しない場合の集計

フィルターを設定してデータを抽出している時に、[ホーム]タブの[編集]グループにある[オートSUM]ボタンをクリックすると、自動的に[SUBTOTAL]が入力されます。

[ホーム]タブの[編集]グループにある[オートSUM]ボタン

これについては、以下の記事で解説しています。

オートSUMボタンをクリックするとSUM関数ではなくSUBTOTAL関数が挿入される

テーブルは使用したくないという場合は、セルの色でフィルターを実行した後に、すぐ下のセルを選択して[オートSUM]ボタンをクリックします。

[Enter]キーで確定します。または、[オートSUM]ボタンをダブルクリックします。

自動でセル参照が正しく選択されていないこともあります。その時は、範囲をドラッグして指定してください。

オートSUMボタン

SUBTOTAL関数の集計方法

テーブルを使用していない場合は、SUBTOTAL関数の集計方法の引数は[9]になります。

テーブルの場合は、[109]です。

SUBTOTAL関数の集計方法の引数が[9]、または[109]のどちらの場合であってもフィルター処理された値は含まれません。

この引数の違いは、手動で非表示にした行の値を含めるかどうかです。

[9]の場合は、非表示にした行の値は含まれ、[109]の場合は非表示の値は無視されます。

詳細は、Microsoftのページを参照してください。

SUBTOTAL 関数 - Office サポート

関連以下の記事では、セルの色でフィルターする方法を紹介しています。

選択したセルの色でデータをフィルター(抽出)するには

Excelで色分けした表を作成して、セルの色で絞り込む方法です。 Excel2007からの機能です。 便利なので、ぜひ活用してみてください。 ...

続きを見る

また、セルの色で並べ替えを行うこともできます。

セルの色で並べ替え(条件を追加して複数キーによる並べ替えもできる)

Excel2007からセルの塗りつぶしの色で並べ替えや絞り込みができるようになりました。 ここでは、Excel2010でセルの塗りつぶしの色 ...

続きを見る

[並べ替え]の[レベルの追加]で複数の基準でデータを並べ替え

Excelのデータでセルに塗りつぶしを設定して色分けした表を作成している場合、セルの塗りつぶしの色で並べ替えることができます。 [並べ替え] ...

続きを見る

テーブルの便利機能については、以下の記事にまとめています。

表をテーブルに変換してデータベース管理などの作業効率をアップ

Excelのテーブルはとても便利なものです。 通常の表をテーブルに変換することによって、データベースとしての機能が自動的に設定され、管理や分 ...

続きを見る

検索

ほかの記事も検索してみませんか?

Access / Excel / PowerPoint / Word

Profile

執筆者/はま

-Windows 7 & Office 2010 Excel 2010

Copyright © 2001-2024 初心者のためのOffice講座 All Rights Reserved.

Copyright© 初心者のためのOffice講座 , 2024 AllRights Reserved Powered by AFFINGER4.