メインコンテンツへスキップ

Excel のヒント: 色別のセルの数/合計 (背景、フォント、条件付き書式)

日常業務では、重要なデータを素早く区別して強調表示するためのカラーマーキングが一般的な方法です。 しかし、特定の色 (塗りつぶしの色、フォントの色、条件付き書式) に基づいてセル データをカウントまたは合計するにはどうすればよいでしょうか? デフォルトでは、Excel には色ごとにカウントまたは合計する直接機能はありません。 それでも、いくつかのトリックと間接的な方法を使えば、これを達成することができます。 この記事では、色ごとにデータをカウントまたは合計する方法について説明します。

背景色に基づいてセルをカウントおよび合計

フォントの色に基づいてセルを数えて合計する

条件付き書式の色に基づいてセルをカウントおよび合計する


ビデオ: 色に基づいてセルを数えて合計する


背景色に基づいてセルをカウントおよび合計

たとえば、以下のスクリーンショットに示すように、値が異なる背景色で塗りつぶされているデータ範囲があるとします。 特定の色に基づいてセルを数えたり合計したりするために、Excel には背景色に基づいてセルを数えたり合計したりする直接的な機能がありません。 ただし、少しの創意工夫といくつかの便利なテクニックを使えば、このタスクを達成できます。 このセクションでは、いくつかの便利な方法を見てみましょう。


ユーザー定義関数を使用して背景色ごとにセルをカウントおよび合計する

ここでは、Excel でこのタスクを解決するためのユーザー定義関数を作成して使用する方法を説明します。 次の手順に従ってください。

手順 1: VBA モジュール エディターを開いてコードをコピーする

  1. イベント Altキー+ F11 キーを押して アプリケーション向け Microsoft Visual Basic 窓。
  2. 開いたウィンドウで、 をクリックします。 インセット > モジュール 新しい空のモジュールを作成します。
  3. 次に、以下のコードをコピーして空のモジュールに貼り付けます。
    VBA コード: 背景色に基づいてセルを数えて合計する
    Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean = False) As Variant
    'Updateby Extendoffice
        Dim rCell As Range
        Dim lCol As Long
        Dim vResult As Double
        lCol = rColor.Interior.ColorIndex
        vResult = 0
        If SUM Then
            For Each rCell In rRange
                If rCell.Interior.ColorIndex = lCol Then
                    vResult = vResult + rCell.Value
                End If
            Next rCell
        Else
            For Each rCell In rRange
                If rCell.Interior.ColorIndex = lCol Then
                    vResult = vResult + 1
                End If
            Next rCell
        End If
        ColorFunction = vResult
    End Function
    

ステップ 2: 背景色ごとにセルを数えて合計する数式を作成する

上記のコードを貼り付けた後、モジュール ウィンドウを閉じて、次の数式を適用します。

  • 特定の背景色に基づいてセルをカウントします。
    以下に示す数式をコピーするか、結果を取得するセルに入力します。 次に、塗りつぶしハンドルを下にドラッグして、他の結果を取得します。 スクリーンショットを参照してください:
    =colorfunction(G2,$B$2:$E$12,FALSE) 
    Note:この式では、 G2 一致させたい特定の背景色の参照セルです。 $B$2:$E$12 G2 の色のセルの数を数えたい範囲です。 間違った情報 一致する色のセルを数えるために使用されます。
  • 特定の背景色に基づいてセルを合計します。
    以下に示す数式をコピーするか、結果を取得するセルに入力します。 次に、塗りつぶしハンドルを下にドラッグして、他の結果を取得します。 スクリーンショットを参照してください:
    =colorfunction(G2,$B$2:$E$12,TRUE)  
    Note:この式では、 G2 一致させたい特定の背景色の参照セルです。 $B$2:$E$12 G2 の色のセルの数を数えたい範囲です。 TRUE 一致する色のセルを合計するために使用されます。

強力な機能を使用して、背景色ごとにセルをカウントおよび合計します。

プログラミングに慣れていない人にとって、VBA は非常に複雑に見えるかもしれません。 ここで、強力なツールを紹介します。 Excel用のKutool、 その 色で数える この機能を使用すると、数回クリックするだけで背景色に基づいて簡単に計算 (カウント、合計、平均など) できます。 印象深いのは、 色で数える この機能は単なる背景色にとどまらず、フォントの色や条件付き書式に基づいて区別したり計算したりすることもできます。

Kutools for Excelのダウンロードとインストール, まず、特定の背景色に基づいてセルをカウントまたは合計するデータ範囲を選択します。 次に、次の場所に移動します クツールズプラス 選択して 色で数える.

色で数える ダイアログ ボックスで、操作を指定してください:

  1. 選択 標準フォーマット カラー方式 ドロップダウンリスト;
  2. 指定 経歴 カウントタイプ ドロップダウン リストを使用すると、ダイアログ ボックスで各背景色の統計結果をプレビューできます。
  3. 最後に、 レポートを生成する 計算結果を新しいワークブックにエクスポートします。

結果:

これで、統計を含む新しいワークブックが得られます。 スクリーンショットを参照してください:

ヒント:
  1.   色で数える この機能は、標準のフォントの色、条件付き書式の背景またはフォントの色、塗りつぶしと条件付き書式の色の組み合わせに基づいてセルのカウントと合計もサポートします。
  2. この機能に興味がありましたら、 クリックしてダウンロードし、30 日間の無料試用版を入手してください.

Filter および SUBTOTAL 関数を使用して、背景色ごとにセルをカウントおよび合計します。

以下のスクリーンショットに示すような果物の売上表があり、その中の色付きのセルをカウントまたは合計するとします。 単位 コラム。

ステップ 1: SUBTOTAL 関数を適用する

SUBTOTAL 関数を入力するには、空白のセルを選択します。

  • 同じ背景色のすべてのセルを数えるには、次の数式を入力してください。
    =SUBTOTAL(102, F2:F16)
  • 同じ背景色のすべてのセルを合計するには、数式を入力してください。
    =SUBTOTAL(109, F2:F16)
  • Note: 上の式では、 102 非表示のセルを除外しながら、フィルターされたリスト内の数値をカウントすることを表します。 109 非表示のセルを除いたフィルタリングされたリスト内の値を合計することを表します。 F2:F16 カウントまたは合計が計算される範囲です。

ステップ 2: 特定の色に基づいてセルをフィルタリングする

  1. テーブルのヘッダーを選択し、 且つ > フィルタ。 スクリーンショットを参照してください:
  2. フィルタ アイコンを押します。  のヘッダーセルに、 単位 列をクリックし、 色で絞り込みます そして、あなたが連続して数える指定された色。 スクリーンショットを参照してください:

結果:

フィルタリング後、SUBTOTAL 式は自動的に色付きのセルを数えて合計します。 単位 カラム。 スクリーンショットを参照してください:

Note:この方法では、カウントまたは合計する色付きのセルが同じ列にある必要があります。

フォントの色に基づいてセルを数えて合計する

Excel でフォントの色に基づいてセルを数えたり合計したりしたいですか? 指定されたスクリーンショットのような、赤、青、オレンジ、黒の色のテキストを含むセルを含むデータがあるとします。 Excel では、デフォルトではこれを簡単に行うことができません。 でも心配しないでください。 このセクションでは、それを行うための簡単なトリックをいくつか紹介します。


ユーザー定義関数を使用してフォントの色に基づいてセルをカウントおよび合計する

特定のフォント色のセルを数えて合計するには、次のユーザー定義関数がこのタスクの解決に役立つ場合があります。 次の手順に従ってください。

手順 1: VBA モジュール エディターを開いてコードをコピーする

  1. イベント Altキー+ F11 キーを押して アプリケーション向け Microsoft Visual Basic 窓。
  2. 開いたウィンドウで、 をクリックします。 インセット > モジュール 新しい空のモジュールを作成します。
  3. 次に、以下のコードをコピーして空のモジュールに貼り付けます。
    VBA コード: フォントの色に基づいてセルを数えて合計する
    Function ProcessByFontColor(pRange1 As Range, pRange2 As Range, FunctionType As String) As Double
    'Updateby Extendoffice
        Application.Volatile
        Dim rng As Range
        Dim xTotal As Double
        Dim xCount As Double
        xTotal = 0
        xCount = 0
        For Each rng In pRange1
            If rng.Font.Color = pRange2.Font.Color Then
                If UCase(FunctionType) = "SUM" Then
                    xTotal = xTotal + rng.Value
                ElseIf UCase(FunctionType) = "COUNT" Then
                    xCount = xCount + 1
                End If
            End If
        Next
        If UCase(FunctionType) = "SUM" Then
            ProcessByFontColor = xTotal
        ElseIf UCase(FunctionType) = "COUNT" Then
            ProcessByFontColor = xCount
        Else
            ProcessByFontColor = CVErr(xlErrValue)
        End If
    End Function
    

ステップ 2: フォントの色ごとにセルを数えて合計する数式を作成する

上記のコードを貼り付けた後、モジュール ウィンドウを閉じて、次の数式を適用します。

  • 特定のフォントの色に基づいてセルをカウントします。
    以下に示す数式をコピーするか、結果を取得するセルに入力します。 次に、塗りつぶしハンドルを下にドラッグして、他の結果を取得します。 スクリーンショットを参照してください:
    =ProcessByFontColor($B$2:$E$12,G2, "COUNT")
    Note:この式では、 G2 は、一致させたい特定のフォントの色を持つ参照セルです。 $B$2:$E$12 は、G2 の色のセルの数を数えたい範囲です。
  • 特定のフォントの色に基づいてセルを合計します。
    以下に示す数式をコピーするか、結果を取得するセルに入力します。 次に、塗りつぶしハンドルを下にドラッグして、他の結果を取得します。 スクリーンショットを参照してください:
    =ProcessByFontColor($B$2:$E$12,G2, "SUM")  
    Note:この式では、 G2 は、一致させたい特定のフォントの色を持つ参照セルです。 $B$2:$E$12 は、G2 の色のセルの数を数えたい範囲です。

簡単な機能でフォントの色に基づいてセルを数えて合計する

Excel でフォントの色に基づいてセルの値を簡単にカウントまたは合計したいと考えていますか? 飛び込む Kutools for Excel's 色で数える 特徴! このスマートなツールを使用すると、特定のフォントの色ごとにセルを数えたり合計したりすることが簡単になります。 その方法を発見する クツール Excel エクスペリエンスを変えることができます。

Kutools for Excelのダウンロードとインストール, まず、特定のフォントの色に基づいてセルをカウントまたは合計するデータ範囲を選択します。 次に、クリックします クツールズプラス > 色で数える を開く 色で数える ダイアログボックス。

色で数える ダイアログ ボックスで、操作を指定してください:

  1. 選択 標準フォーマット カラー方式 ドロップダウンリスト;
  2. 指定 フォント カウントタイプ ドロップダウン リストを使用すると、ダイアログ ボックスで各フォントの色の統計結果をプレビューできます。
  3. 最後に、 レポートを生成する 計算結果を新しいワークブックにエクスポートします。

結果:

これで、フォントの色に基づいて詳細な統計を表示する新しいワークブックが作成されました。 スクリーンショットを参照してください:


条件付き書式の色に基づいてセルをカウントおよび合計する

Excel では、一般的に条件付き書式設定を使用して、特定の条件を満たすセルに特定の色を適用し、データの視覚化を直感的に行うことができます。 しかし、特別にフォーマットされたセルを数えたり合計したりする必要がある場合はどうすればよいでしょうか? Excel ではこれを直接行う方法は提供されていませんが、この制限を回避する方法を次に示します。


VBA コードを使用して条件付きで書式設定されたセルをカウントおよび合計する

Excel で条件付き書式設定されたセルを数えたり合計したりすることは、組み込み関数を使用するのは簡単ではありません。 ただし、このタスクは VBA コードを使用して実行できます。 これに VBA を使用する方法を見てみましょう。

手順 1: VBA モジュール エディターを開いてコードをコピーする

  1. イベント Altキー+ F11 キーを押して アプリケーション向け Microsoft Visual Basic 窓。
  2. 開いたウィンドウで、 をクリックします。 インセット > モジュール 新しい空のモジュールを作成します。
  3. 次に、以下のコードをコピーして空のモジュールに貼り付けます。
    VBA コード: 条件付き書式の色に基づいてセルをカウントおよび合計する
    Sub SumCountByConditionalFormat()
    'Updateby Extendoffice
        Dim sampleColor As Range
        Dim selectedRange As Range
        Dim cell As Range
        Dim countByColor As Long
        Dim sumByColor As Double
        Dim refColor As Long
        Set selectedRange = Application.InputBox("Select a range to evaluate:", _
                                                 "Kutools for Excel", _
                                                 Type:=8)
        If selectedRange Is Nothing Then Exit Sub
        Set sampleColor = Application.InputBox("Select a conditional formatting color:", _
                                               "Kutools for Excel", _
                                               Type:=8)
        If Not sampleColor Is Nothing Then
            refColor = sampleColor.Cells(1, 1).DisplayFormat.Interior.color
            For Each cell In selectedRange
                If cell.DisplayFormat.Interior.color = refColor Then
                    countByColor = countByColor + 1
                    sumByColor = sumByColor + cell.Value
                End If
            Next cell
            MsgBox "Count: " & countByColor & vbCrLf & _
                   "Sum: " & sumByColor, _
                   vbInformation, "Results based on Conditional Format Color"
        End If
    End Sub
    

ステップ 2: この VBA コードを実行する

  1. コードを貼り付けたら、を押します F5 キーを押してこのコードを実行すると、プロンプト ボックスが表示されます。条件付き書式に基づいてセルをカウントおよび合計するデータ範囲を選択してください。 次に、クリックします OK、スクリーンショットを参照してください:
  2. 別のプロンプト ボックスで、カウントして合計する特定の条件付き書式の色を選択し、 OK ボタン、スクリーンショットを参照してください:

結果:

これで、指定した条件付き書式の色を持つセルの数と合計の両方を含む結果がポップアウト ボックスに表示されます。 スクリーンショットを参照してください:


スマート機能を使用して、条件付きで書式設定されたセルをカウントおよび合計する

条件付きで書式設定されたセルを数えて合計する他の迅速かつ簡単な方法をお探しの場合は、 Kutools for Excel 頼りになるソリューションです。 その 色で数える この機能を使用すると、数回クリックするだけでこのタスクを解決できます。 Kutools がワークフローにもたらす効率と精度を発見してください。

Kutools for Excelのダウンロードとインストール, まず、特定の条件付き書式設定の色に基づいてセルをカウントまたは合計するデータ範囲を選択します。 次に、クリックします クツールズプラス > 色で数える を開く 色で数える ダイアログボックス。

色で数える ダイアログ ボックスで、操作を指定してください:

  1. 選択 条件付き書式 カラー方式 ドロップダウンリスト;
  2. 指定 経歴 カウントタイプ ドロップダウン リストを使用すると、ダイアログ ボックスで各条件と書式設定の色の統計結果をプレビューできます。
  3. 最後に、 レポートを生成する 計算結果を新しいワークブックにエクスポートします。

結果:

これで、条件付き書式の色に基づいて詳細な統計を表示する新しいワークブックが作成されました。 スクリーンショットを参照してください:


関連記事:

  • フォントの色が赤の場合は、特定のテキストを返します
  • 下のスクリーンショットに示すように、別のセルでフォントの色が赤の場合、特定のテキストを返すにはどうすればよいですか? この記事では、Excel で赤いフォント テキストに基づいていくつかの操作を行うためのいくつかのトリックを紹介します。
  • 複数の色でデータをフィルタリングする
  • 通常、Excelでは、XNUMX色のみの行をすばやくフィルタリングできますが、同時に複数の色の行をフィルタリングすることを検討したことがありますか? この記事では、この問題に対処するための簡単なトリックについて説明します。
  • ドロップダウンリストに色を追加
  • Excel では、ドロップダウン リストを作成すると非常に役立ちますが、場合によっては、対応する選択内容に応じてドロップダウン リストの値を色分けする必要があります。 たとえば、果物名のドロップダウン リストを作成しました。Apple を選択すると、セルが自動的に赤で色付けされ、オレンジを選択すると、セルがオレンジ色で色付けされます。
  • 結合されたセルの行ごとに色を付ける
  • データをスキャンするために、大きなデータで別の色で行を XNUMX 行おきに書式設定すると非常に役立ちますが、データ内に結合されたセルが存在する場合があります。 下のスクリーンショットに示すように、結合されたセルの行を別の色で交互に強調表示するには、Excel でこの問題をどのように解決できますか?