リンクユニット

Windows7 & Office2010 Excel2010

色付きセルの集計はテーブルで

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

Excelの表でセルに色を付ける方は多いと思います。この色付きのセルのみを合計したいという場合、Excel2007以降のバージョンであれば簡単に集計することができます。ここでは、Excel2010で解説します。下のような表があります。
表

テーブルに変換

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

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

関連データの管理および分析を行うテーブルを作成します。
テーブルにすると、シート内のデータの並べ替え、フィルター、書式設定が簡単に行えます。

テーブル

[テーブルの作成]ダイアログボックスが表示されます。
データ範囲を確認して、[先頭行をテーブルの見出しとして使用する]のチェックはオンにするか、オフにするかを決定して[OK]ボタンをクリックします。

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

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

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

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

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

既定のテーブルのスタイルは、[テーブルスタイル(中間)2]です。[テーブルのスタイル]から[なし]を選択します。
スタイルなし

テーブルに集計行を追加

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

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

集計行のセルには[SUBTOTAL]関数の数式が入ります。これで準備OKです。
数式SUBTOTAL

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

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

または、先頭行のフィルターコントロールボタン(▼ボタン)をクリックして、メニューを表示します。そして、[色フィルター]のサブメニューにある[セルの色でフィルター]から、目的の色をクリックします。
ドロップダウンボタンから選択

集計行に合計値が表示されます。色付きセルのみの合計を求めることができます。
色付きセルの合計

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

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

テーブルを使用したくない場合

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

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

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

テーブルは使用したくないという場合は、フィルター実行後に[オートSUM]ボタンをクリックするといいですね。[Enter]キーで確定します。
オートSUMボタン

参考ただし、上記の場合は、SUBTOTAL関数の集計方法の引数は[9]になります。テーブルの場合は、[109]です。

SUBTOTAL関数の集計方法の引数が[9]、または[109]のどちらの場合であってもフィルター処理された値は含まれません。この引数の違いは、手動で非表示にした行の値を含めるかどうかです。[9]の場合は、非表示にした行の値は含まれ、[109]の場合は非表示の値は無視されます。

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

SUBTOTAL 関数 - Office サポート

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

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

色フィルターの解除

フィルターを解除する場合は、フィルターコントロールボタンをクリックして、メニューを表示します。["〇〇"からフィルターをクリア]をクリックします。
フィルターをクリア

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

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

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

続きを見る

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

セルの色で並べ替え

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

続きを見る

【並べ替え】のレベルの追加(複数のセルの色を並べ替え)

Excelで色分けした表を作成している場合、セルの色で並べ替えることができます。表内に複数の色がある場合も、条件を追加して複数キーによる並べ ...

続きを見る

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

テーブルの便利機能(テーブルに変換して効率アップ)

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

続きを見る

参考Officeのバージョンによって操作が異なる場合があります。以下の記事で、お使いのOfficeのバージョンを確認する方法を解説しています。

Office(Word、Excel、PowerPointなど)のバージョンを確認する方法

Microsoft Officeも時を得て、大きく様変わりしてきました。トラブルを解決しようとするとき、あるいは、何かの操作を探すときなど、 ...

続きを見る

管理人一押しのテキストです。業務改善、時短のための一冊です。関数を学び直したい人はぜひ!

おすすめ記事と広告

記事を更新しました

ペイントのトリミング

Windows7では、アクセサリーの中にあるペイントでトリミングが簡単に行えるようになりました。 これは嬉しいですね。 参考Windows ...

ブックを閉じても使用中となる

Windows7とExcel2010の環境で、PowerPivotを頻繁に使用するようになってからだと思うのですが、 Excelのブックを終 ...

データベースにパスワードを設定

Access2010でデータベースにパスワードを設定する方法です。 排他モードで開く パスワードを設定したいデータベースを[排他モード]で開 ...

ピボットテーブルの追加フィールド

Accessのクエリでピボットテーブルを作成した後でも、演算によるフィールドを追加することができます。 Accessのピボットテーブルの確認 ...

定義XMLによるスキーマの確認が失敗しました

Access2010で、Excelのワークシートからインポートしようと、[外部データ]タブの[Excel]をクリックして、ウィザードに従って ...

-Windows7 & Office2010 Excel2010

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

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