Excelのヒント: 色(背景、フォント、条件付き書式)でセルをカウント/合計する
日常のタスクにおいて、色によるマーク付けは重要なデータを迅速に区別し強調するための一般的な方法です。しかし、特定の色(塗りつぶしの色、フォントの色、条件付き書式)に基づいてセルのデータをカウントしたり合計したりするにはどうすればよいでしょうか?デフォルトでは、Excelは色でカウントや合計を行う直接的な機能を提供していません。それでも、いくつかの工夫と間接的な方法を使えば、この目的を達成することができます。この記事では、色に基づいてデータをカウントまたは合計する方法を探ります。
背景色に基づいてセルをカウントおよび合計する
例えば、下のスクリーンショットに示すように、異なる背景色で塗りつぶされたデータ範囲があるとします。特定の色に基づいてセルをカウントまたは合計したい場合、Excelは背景色に基づいてセルをカウントまたは合計する直接的な機能を提供していません。しかし、少しの工夫と便利なテクニックを使えば、このタスクを実行できます。このセクションでは、いくつかの有用な方法を探ってみましょう。
ユーザー定義関数を使用して背景色に基づいてセルをカウントおよび合計する
ここでは、このようなユーザー定義関数を作成して使用し、Excelでのこのタスクを解決する方法をご紹介します。以下の手順に従ってください:
ステップ 1: VBAモジュールエディタを開き、コードをコピーする
- Alt + F11 キーを押して Microsoft Visual Basic for Applications ウィンドウを開きます。
- 開いたウィンドウで、 挿入 > モジュール をクリックして新しい空白のモジュールを作成します。
- 次に、以下のコードを空白のモジュールにコピーして貼り付けます。
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)
注意: この数式では、G2 は一致させたい特定の背景色を持つ参照セルです; $B$2:$E$12 は G2 の色を持つセルの数をカウントしたい範囲です; FALSE は色が一致するセルをカウントするために使用されます。 - 特定の背景色に基づいてセルを合計する:
結果を得たいセルに以下に示す数式をコピーまたは入力してください。その後、フィルハンドルを下にドラッグして他の結果を得ます。スクリーンショットをご覧ください:
=colorfunction(G2,$B$2:$E$12,TRUE)
注意: この数式では、G2 は一致させたい特定の背景色を持つ参照セルです; $B$2:$E$12 は G2 の色を持つセルの数をカウントしたい範囲です; TRUE は色が一致するセルを合計するために使用されます。
強力な機能を使用して背景色に基づいてセルをカウントおよび合計する
プログラミングに不慣れな方にとって、VBAはかなり複雑に見えることがあります。ここで、強力なツールであるKutools for Excelを紹介します。その「色でカウント」機能により、数回のクリックで背景色に基づいて計算(カウント、合計、平均など)を行うことができます。印象的なことに、「色でカウント」機能は背景色に限定されず、フォントの色や条件付き書式の色にも対応し、さらに塗りつぶしと条件付き書式の色の組み合わせにも対応しています。
Kutools for Excelをダウンロードしてインストールした後、まず特定の背景色に基づいてカウントまたは合計したいデータ範囲を選択します。次に、Kutools Plusに移動して「色でカウント」を選択します。
「色でカウント」ダイアログボックスで、操作を指定してください:
- 「色の方法」ドロップダウンリストから「標準の書式設定」を選択します;
- 背景をカウントの種類ドロップダウンリストから選択し、ダイアログボックスで各背景色の統計結果をプレビューできます;
- 最後に、「レポートの生成」をクリックして計算結果を新しいワークブックにエクスポートします。
結果:
これで、統計情報が記載された新しいワークブックが作成されます。スクリーンショットをご覧ください:
- この 色でカウント 機能は、標準のフォントの色、背景色、条件付き書式のフォントの色、そして塗りつぶしと条件付き書式の色の組み合わせに基づいてセルをカウントおよび合計することもサポートしています。
- この機能に興味がある方は、30日間の無料体験版をダウンロードするにはこちらをクリックしてください。
フィルターとSUBTOTAL関数を使用して背景色に基づいてセルをカウントおよび合計する
下のスクリーンショットに示すように、果物の売上表があると仮定します。そして、私たちは「金額」列にある色付きのセルをカウントまたは合計します。 金額 列。
ステップ 1: SUBTOTAL関数を適用する
SUBTOTAL関数を入力するための空白セルを選択します。
- 同じ背景色を持つすべてのセルをカウントするには、次の数式を入力してください:
=SUBTOTAL(102, F2:F16)
- 同じ背景色を持つすべてのセルを合計するには、次の数式を入力してください;
=SUBTOTAL(109, F2:F16)
- 注意: 上記の数式では、102 はフィルタリングされたリスト内の数値をカウントし、非表示のセルを除外することを表します;109 は非表示のセルを除外してフィルタリングされたリスト内の値を合計することを表します;F2:F16 はカウントまたは合計が計算される範囲です。
ステップ 2: 特定の色に基づいてセルをフィルタリングする
- テーブルのヘッダーを選択し、 データ > フィルターをクリックします。スクリーンショットをご覧ください:
- クリック フィルター アイコン
「金額」列のヘッダーセル内にある 金額 列のヘッダーセル内にある「フィルター」アイコンをクリックし、 色でフィルター をクリックし、順番にカウントしたい指定の色を選択します。スクリーンショットをご覧ください:
結果:
フィルタリング後、SUBTOTAL数式は自動的に「金額」列の色付きセルをカウントおよび合計します。 金額 列。スクリーンショットをご覧ください:
フォントの色に基づいてセルをカウントおよび合計する
Excelでフォントの色に基づいてセルをカウントまたは合計したいですか?例えば、与えられたスクリーンショットのように、赤、青、オレンジ、黒の文字が含まれるセルのデータがあるとします。Excelはこれをデフォルトで簡単にはできません。しかし、心配しないでください!このセクションでは、それを実現するための簡単なトリックをいくつかお見せします。
ユーザー定義関数を使用してフォントの色に基づいてセルをカウントおよび合計する
特定のフォントの色を持つセルをカウントおよび合計するには、次のユーザー定義関数がこのタスクを解決するのに役立つかもしれません。以下の手順に従ってください:
ステップ 1: VBAモジュールエディタを開き、コードをコピーする
- Alt + F11 キーを押して Microsoft Visual Basic for Applications ウィンドウを開きます。
- 開いたウィンドウで、 挿入 > モジュール をクリックして新しい空白のモジュールを作成します。
- 次に、以下のコードを空白のモジュールにコピーして貼り付けます。
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")
注意: この数式では、G2 は一致させたい特定のフォントの色を持つ参照セルです; $B$2:$E$12 は G2 の色を持つセルの数をカウントしたい範囲です。 - 特定のフォントの色に基づいてセルを合計する:
結果を得たいセルに以下に示す数式をコピーまたは入力してください。その後、フィルハンドルを下にドラッグして他の結果を得ます。スクリーンショットをご覧ください:
=ProcessByFontColor($B$2:$E$12,G2, "SUM")
注意: この数式では、G2 は一致させたい特定のフォントの色を持つ参照セルです; $B$2:$E$12 は G2 の色を持つセルの数をカウントしたい範囲です。
簡単な機能を使用してフォントの色に基づいてセルをカウントおよび合計する
Excelでフォントの色に基づいてセルの値を簡単にカウントまたは合計したいですか?Kutools for Excelの「色でカウント」機能に飛び込んでみてください!このスマートなツールを使えば、特定のフォントの色に基づいてセルをカウントおよび合計することが非常に簡単になります。KutoolsがどのようにあなたのExcel体験を変えるかを発見してください。
Kutools for Excelをダウンロードしてインストールした後、まず特定のフォントの色に基づいてカウントまたは合計したいデータ範囲を選択します。次に、Kutools Plus > 「色でカウント」をクリックして「色でカウント」ダイアログボックスを開きます。
「色でカウント」ダイアログボックスで、操作を指定してください:
- 「色の方法」ドロップダウンリストから「標準の書式設定」を選択します;
- 「カウントの種類」ドロップダウンリストから「フォント」を選択し、ダイアログボックスで各フォントの色の統計結果をプレビューできます;
- 最後に、「レポートの生成」をクリックして計算結果を新しいワークブックにエクスポートします。
結果:
これで、フォントの色に基づく詳細な統計情報を表示する新しいワークブックが作成されました。スクリーンショットをご覧ください:
条件付き書式の色に基づいてセルをカウントおよび合計する
Excelでは、条件付き書式を使用して特定の基準を満たすセルに特定の色を適用することがよくあります。これにより、データの視覚化が直感的になります。しかし、これらの特別に書式設定されたセルをカウントまたは合計する必要がある場合はどうすればよいでしょうか?Excelはこれを直接行う方法を提供していませんが、この制限を回避する方法があります。
VBAコードを使用して条件付き書式のセルをカウントおよび合計する
Excelで条件付き書式のセルをカウントおよび合計することは、組み込み関数を使用しても簡単ではありません。しかし、VBAコードを使用することでこのタスクを達成できます。VBAを使用する方法を見てみましょう:
ステップ 1: VBAモジュールエディタを開き、コードをコピーする
- Alt + F11 キーを押して Microsoft Visual Basic for Applications ウィンドウを開きます。
- 開いたウィンドウで、 挿入 > モジュール をクリックして新しい空白のモジュールを作成します。
- 次に、以下のコードを空白のモジュールにコピーして貼り付けます。
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コードを実行する
- コードを貼り付けた後、 F5 キーを押してこのコードを実行すると、プロンプトボックスが表示されますので、条件付き書式に基づいてカウントおよび合計したいデータ範囲を選択してください。次に、「OK」をクリックします。スクリーンショットをご覧ください: OK, スクリーンショットをご覧ください:
- 別のプロンプトボックスで、カウントおよび合計したい特定の条件付き書式の色を選択し、「OK」をクリックします。スクリーンショットをご覧ください: OK ボタンをクリックします。スクリーンショットをご覧ください:
結果:
これで、指定された条件付き書式の色を持つセルのカウントと合計の両方を含む結果がポップアップボックスに表示されます。スクリーンショットをご覧ください:
スマートな機能を使用して条件付き書式のセルをカウントおよび合計する
条件付き書式のセルをカウントおよび合計するための他の簡単で迅速な方法をお探しの場合、Kutools for Excelが最適なソリューションです。「色でカウント」機能を使用すれば、数回のクリックでこのタスクを解決できます。Kutoolsがワークフローにもたらす効率と精度を発見してください。
Kutools for Excelをダウンロードしてインストールした後、まず特定の条件付き書式の色に基づいてカウントまたは合計したいデータ範囲を選択します。次に、Kutools Plus > 「色でカウント」をクリックして「色でカウント」ダイアログボックスを開きます。
「色でカウント」ダイアログボックスで、操作を指定してください:
- 「色の方法」ドロップダウンリストから「条件付き書式 」を選択します;
- 「カウントの種類」ドロップダウンリストから「背景」を選択し、ダイアログボックスで各条件付き書式の色の統計結果をプレビューできます;
- 最後に、「レポートの生成」をクリックして計算結果を新しいワークブックにエクスポートします。
結果:
これで、条件付き書式の色に基づく詳細な統計情報を表示する新しいワークブックが作成されました。スクリーンショットをご覧ください:
関連記事:
- フォントの色が赤の場合、特定のテキストを返す
- 下のスクリーンショットに示すように、別のセルのフォントの色が赤の場合、特定のテキストを返すにはどうすればよいでしょうか?この記事では、Excelでの赤いフォントのテキストに基づいていくつかの操作を行うためのトリックをご紹介します。
- 複数の色でデータをフィルタリングする
- 通常、Excelでは1つの色を持つ行を素早くフィルタリングできますが、複数の色を持つ行を同時にフィルタリングすることを考えたことはありますか?この記事では、この問題に対処するための簡単なトリックについてお話します。
- ドロップダウンリストに色を追加する
- Excelでは、ドロップダウンリストを作成することで多くの助けになります。また、選択された値に応じてドロップダウンリストの値に色を付ける必要がある場合もあります。例えば、リンゴ、オレンジなどの果物名のドロップダウンリストを作成したとします。リンゴを選択すると、セルが自動的に赤くなり、オレンジを選択するとセルがオレンジ色になるようにしたい場合があります。
- 結合されたセルの交互に行に色を付ける
- 大量のデータで交互に行に異なる色を付けることは、データをスキャンする際に非常に役立ちますが、時にはデータ内に結合されたセルがある場合があります。下のスクリーンショットに示すように、結合されたセルに対して交互に行に異なる色を付けて強調表示するには、Excelでこの問題をどのように解決すればよいでしょうか?
最高のオフィス生産性ツール
🤖 | Kutools AI アシスタント: データ分析を革命化する: インテリジェント実行 | コード生成 | カスタム数式の作成 | データを分析しグラフを生成 | 拡張機能を呼び出す… |
人気の機能: 重複を見つけてハイライトまたはマーキング | 空白行を削除 | データを失わずに列またはセルを統合 | 丸める ... | |
スーパーLOOKUP: 複数条件のVLookup | 複数値のVLookup | 複数シートの検索 | ファジーマッチ .... | |
高度なドロップダウンリスト: ドロップダウンリストを迅速に作成 | 依存ドロップダウンリスト | マルチセレクトドロップダウンリスト .... | |
列管理: 特定の数の列を追加 | 列を移動 | 非表示列の可視ステータスを切り替え | 範囲と列を比較 ... | |
注目の機能: グリッドフォーカス | デザインビュー | 強化された数式バー | ワークブック & ワークシート管理 | オートテキスト ライブラリ (Auto Text) | 日付ピッカー | データの統合 | セルの暗号化/復号化 | リストで電子メールを送信 | スーパーフィルター | 特殊フィルタ (太字/斜体/取り消し線をフィルタリング...) ... | |
トップ15のツールセット: 12 のテキストツール (テキストの追加, 特定の文字を削除, ...) | 50以上 のグラフ の種類 (ガントチャート, ...) | 40以上の実用的な 数式 (誕生日に基づいて年齢を計算する, ...) | 19 の挿入ツール (QRコードの挿入, パスから画像を挿入, ...) | 12 の変換ツール (単語に変換する, 通貨変換, ...) | 7 の統合 & セルの分割ツール (高度な行のマージ, セルの分割, ...) | ...さらに多く |
Kutools for ExcelでExcelスキルを強化し、これまでにない効率を体験してください。 Kutools for Excelは、300以上の高度な機能を提供し、生産性を向上させ、保存時間を節約します。 最も必要な機能を入手するにはここをクリック...
Office TabはOfficeにタブインターフェイスをもたらし、作業を非常に簡単にします
- Word、Excel、PowerPoint、Publisher、Access、Visio、Projectでタブ付きの編集と読み取りを有効にします。
- 新しいウィンドウではなく、同じウィンドウの新しいタブで複数のドキュメントを開いて作成します。
- 生産性を50%向上させ、毎日数百回のマウスクリックを減らします!