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

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 でこの問題をどのように解決できますか?
Comments (237)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
嗨~版主好,我用了VBA 模塊,但是完全沒有動靜,沒有出現顏色儲存格的統計數量,Count欄一片空白~~請問是為什麼呢?跟office版本有關嗎?謝謝
This comment was minimized by the moderator on the site
嗨,MINA,
文章中的VBA代碼,微軟office版本基本上都可以適用,我這代碼可以正常使用。 如果你那邊還用不了,可以上傳你的文件,我們可以幫忙看看哪裡的問題,謝謝!
This comment was minimized by the moderator on the site
I am using =IF(D272>F272,D272-F272,if(F272>D272,F272-D272,"")) formula for subtraction, and I want it will coloured also??
This comment was minimized by the moderator on the site
Hi namrata,
Do you want to fill color for the result of your formula?
So, if the result is D272-F272, you want it, say, red; If the result is F272-D272, you want it, say, green; If blank, blank?
Amanda
This comment was minimized by the moderator on the site
I ran into problems when trying to run the function. Macro errors telling me: No RETURN() or HALT() function found on macro sheet. perhaps somebody could assist here. ThanksPaul
This comment was minimized by the moderator on the site
Hi, How to make a diagram based on the colors in the table? For example, I want to count all the red, green and yellow colors in the cells in a table and make a diagram. How to do this? Please
This comment was minimized by the moderator on the site
Is there a way to count different color backgrounds from conditional formatting? The current code as of 7/14/2020 counts them all as default yellow or not at all.
This comment was minimized by the moderator on the site
Hi Dusty,
You can try the Count by Color feature of Kutools for Excel. This feature will help you quickly calculate (count, sum, average, etc.) cells by cell background color or font color, no matter they are formatted by conditional formatting or solidly format.
This comment was minimized by the moderator on the site
Did anyone find a solution to auto-refresh? I have to manually refresh for it to update. Otherwise, it works great!
This comment was minimized by the moderator on the site
Hi Dennis,
By default, formulas are calculated in Excel until you are turning off the Automatic Formula Calculation. You can enable it by clicking Formulas > Calculation Options > Automatic.
This comment was minimized by the moderator on the site
Anyone have tips on a max by color VBA?
This comment was minimized by the moderator on the site
Hi Natasha,VBA is good but hard to apply. But below methods may solve your work easily too.
Method 1: Use Find & Replace feature to select and statistic the color cells(1) Press Ctrl + H keys to open the Find and Replace dialog, and then enable the Find tab.
(2) In the dialog, click Options to show advanced find options.
(3) Then click Format > Choose Format From Cell, and select one of the specified color cells.
(4) Click Find All. Now all cells with the same fill color are found out and listed at the bottom of the dialog.
(5) Select one of found cells, and press Ctrl + A to select all found cells, so that these cells are selected in the worksheet.
(6) Now you can get the count, average, sum, min, max, etc. of these cells in the task bar.
Note: If a certain statistic result cannot be found one the taskbar, you can right click the task bar, and then tick the specified item to show it.

Method 2: Kutools for Excel
Kutools for Excel supports 30-day free trial. Therefore, you can download it and try its Count by color feature to solve your problem with several clicks only.
This comment was minimized by the moderator on the site
awesome fix! count by color over an entire sheet was just what i was looking for and your VBA code was tighter than others that i have looked at. Works like a charm. Thank you, and again, well done.
This comment was minimized by the moderator on the site
I copied and paste but calculation result is "0" why?? I am using Office 2016.
This comment was minimized by the moderator on the site
I try the same you this command =COUNTBYCELLCOLOR is counting only fill color but is not count by condition formating. Please help to improve code thx.
This comment was minimized by the moderator on the site
I've copied and pasted as stated and have used this formula for over a year but recently saved the workbook to a new name and now the function doesn't work! I can't figure out what the deal is and I'm losing hair and sleep over it! Loading the original workbook, the formula works like a charm but going back to the new one, it doesn't! I've loaded VBA and tried re-creating the function but it doesn't work. Using Office 2019 - any help appreciated.
This comment was minimized by the moderator on the site
#NAME clearly indicates some keyword is not used in 2019, say, Interior.ColorIndex. Try to figure out changing the code from minimum lines to the full by adding one by one or search for the keywords in Excel/VBA in 2019
This comment was minimized by the moderator on the site
I should probably have also noted that I get a #NAME? error in the cell in which I try to use the function.
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations