Note: The other languages of the website are Google-translated. Back to English
ログイン  \/ 
x
or
x
今すぐ登録  \/ 
x

or

Excelで背景色に基づいてセルをカウントして合計する方法は?

赤、緑、青など、背景色が異なるセルの範囲があるとしますが、その範囲内で特定の背景色を持つセルの数を数え、同じ特定の色の色付きセルを合計する必要があります。 。 Excelには、カラーセルの合計と数を計算する直接の数式はありません。ここでは、この問題を解決するいくつかの方法を紹介します。


フィルターとサブトータルで色付きセルをカウントして合計

以下のスクリーンショットに示すような果物の販売テーブルがあり、[金額]列の色付きのセルをカウントまたは合計するとします。 この状況では、[量]列を色でフィルタリングし、ExcelのSUBTOTAL関数でフィルタリングされた色付きセルを簡単にカウントまたは合計できます。

1。 空白のセルを選択して、SUBTOTAL機能に入ります。

  1. 同じ背景色のすべてのセルをカウントするには、数式を入力してください = SUBTOTAL(102、E2:E20);
  2. 同じ背景色のすべてのセルを合計するには、数式を入力してください = SUBTOTAL(109、E2:E20);


注意:どちらの数式でも、E2:E20は色付きのセルを含む[金額]列であり、必要に応じて変更できます。

2。 テーブルのヘッダーを選択し、をクリックします データ > フィルタ。 スクリーンショットを参照してください:

3。 フィルタアイコンをクリックします  [金額]列のヘッダーセルで、[ 色で絞り込みます そして、あなたが連続して数える指定された色。 スクリーンショットを参照してください:

フィルタリング後、両方のSUBTOTAL数式は、[量]列のすべてのフィルタリングされたカラーセルを自動的にカウントおよび合計します。 スクリーンショットを参照してください:

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

ワンクリックでExcelの色付きセルをカウント、合計、平均化

優秀な 色で数える の特徴 Kutools for Excel、Excelでワンクリックするだけで、指定した塗りつぶしの色またはフォントの色でセルをすばやくカウント、合計、平均化できます。 さらに、この機能は、塗りつぶしの色またはフォントの色によってセルの最大値と最小値も検出します。 フル機能の無料トライアル30日!
色別の広告数2

Kutools for Excel -Excel用の300以上の便利なツールが含まれています。 30日間のフル機能の無料トライアル、クレジットカードは必要ありません! 今すぐ入手

GET.CELL関数による色付きセルのカウントまたは合計

このメソッドでは、GET.CELL関数を使用して名前付き範囲を作成し、セルのカラーコードを取得してから、Excelでカラーコードによって簡単にカウントまたは合計します。 次のようにしてください。

1に設定します。 OK をクリックします。 > 名前を定義する。 スクリーンショットを参照してください:

2。 [新しい名前]ダイアログで、以下のスクリーンショットのようにしてください。
(1)[名前]ボックスに名前を入力します。
(2)式を入力します = GET.CELL(38、Sheet4!$ E2) [参照先]ボックス(注意:式では、 38 セルコードを返すことを意味し、 Sheet4!$ E2 テーブルデータに基づいて変更する必要がある列ヘッダーを除いて、[金額]列の最初のセルです。)
(3)をクリックします OK ボタン。

3。 次に、元のテーブルに新しいColor列を追加します。 次に、数式を入力します = NumColor 、およびオートフィルハンドルをドラッグして、[色]列の他のセルに数式を適用します。 スクリーンショットを参照してください:
注意:式では、 NumColor 最初の2つの手順で指定した名前付き範囲です。 設定した指定の名前に変更する必要があります。

これで、[金額]列の各セルのカラーコードが[色]列に戻ります。 スクリーンショットを参照してください:

4。 アクティブなワークシートの空白の範囲に塗りつぶしの色をコピーして一覧表示し、次のスクリーンショットのようにその横に数式を入力します。
A.細胞を色で数えるには、数式を入力してください = COUNTIF($ F $ 2:$ F $ 20、NumColor);
B.セルを色で合計するには、数式を入力してください = SUMIF($ F $ 2:$ F $ 20、NumColor、$ E $ 2:$ E $ 20).

注意:両方の式で、 $ F $ 2:$ F $ 20 色の列です。 NumColor 指定された名前付き範囲です。 $ E $ 2:$ E $ 20 は金額列であり、必要に応じて変更できます。

これで、[量]列のセルがカウントされ、塗りつぶしの色で合計されます。


ユーザー定義関数を使用して、特定の塗りつぶしの色に基づいてセルをカウントおよび合計します

下のスクリーンショットに示すように、色付きのセルが範囲内に散在しているとすると、上記の両方の方法では、色付きのセルをカウントまたは合計できません。 ここでは、このメソッドは問題を解決するためにVBAを導入します。

1。 を押し続けます Alt + F11 キー、そしてそれは開きます アプリケーション向け Microsoft Visual Basic 窓。

2に設定します。 OK をクリックします。 インセット > モジュール、モジュールウィンドウに次のコードを貼り付けます。

VBA:背景色に基づいてセルをカウントおよび合計します。

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult
lCol = rColor.Interior.ColorIndex
If SUM = True Then
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = WorksheetFunction.SUM(rCell, vResult)
End If
Next rCell
Else
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = 1 + vResult
End If
Next rCell
End If
ColorFunction = vResult
End Function

3。 次に、コードを保存し、次の式を適用します。
A.色付きのセルを数えます。 = colorfunction(A、B:C、FALSE)
B.色付きのセルを合計します。 = colorfunction(A、B:C、TRUE)

注:上記の式では、 A カウントと合計を計算する特定の背景色のセルであり、 紀元前 カウントと合計を計算するセル範囲です。

4。 たとえば、次のスクリーンショットを撮り、数式を入力します= colorfunction(A1、A1:D11、FALSE) 黄色い細胞を数えます。 そして、式を使用します = colorfunction(A1、A1:D11、TRUE) 黄色のセルを合計します。 スクリーンショットを参照してください:

5。 他の色付きのセルを数えて合計する場合は、手順4を繰り返します。そうすると、次の結果が得られます。


Kutools関数を使用して、特定の塗りつぶしの色に基づいてセルをカウントおよび合計します

Kutools for Excelは、Excelユーザーが特別な計算を行うのに役立ついくつかの便利な関数もサポートしています。たとえば、セルの背景色によるカウント、フォントの色による合計などです。

Kutools for Excel -Excel用の300以上の便利なツールが含まれています。 30日間のフル機能の無料トライアル、クレジットカードは必要ありません! 今すぐ無料トライアル!

1。 カウント結果を配置する空白のセルを選択し、をクリックします クツール > Kutools関数 > 統計と数学 > COUNTBYCELLCOLOR。 スクリーンショットを参照してください:

2。 [関数の引数]ダイアログで、色付きのセルをカウントする範囲を指定してください。 参照 ボックスで、指定された背景色で塗りつぶされているセルを選択します。 Color_index_nr ボックスをクリックし、 OK ボタン。 スクリーンショットを参照してください:

注意:
(1)指定したKutools関数を入力することもできます = COUNTBYCELLCOLOR($ A $ 1:$ E $ 20、G2)  空白のセルまたは数式バーに直接入力して、カウント結果を取得します。
(2)をクリック クツール > Kutools関数 > 統計と数学 > サバイセルカラー タイプ = SUMBYCELLCOLOR($ A $ 1:$ E $ 20、G2) 空白のセルに直接入力して、指定した背景色に基づいてセルを合計します。
適用 COUNTBYCELLCOLOR サバイセルカラー 背景色ごとに個別に機能し、以下のスクリーンショットのような結果が得られます。

Kutools関数 Excelユーザーが簡単に計算できるようにするための多数の組み込み関数が含まれています。 カウント/合計/平均可視セル, セルの色でカウント/合計, フォントの色でカウント/合計, 文字を数える, 太字のフォントで数えるなどです。 無料トライアルを!


Kutools for Excelを使用して、特定の塗りつぶしの色に基づいてセルをカウントおよび合計します

上記のユーザー定義関数では、数式をXNUMXつずつ入力する必要があります。さまざまな色がたくさんある場合、この方法は面倒で時間がかかります。 しかし、あなたが持っているなら Kutools for Excelさん 色で数える ユーティリティを使用すると、色付きのセルのレポートをすばやく生成できます。 色付きのセルを数えて合計するだけでなく、色付きの範囲の平均値、最大値、最小値を取得することもできます。

Kutools for Excel -Excel用の300以上の便利なツールが含まれています。 30日間のフル機能の無料トライアル、クレジットカードは必要ありません! 今すぐ無料トライアル!

1。 使用する範囲を選択し、をクリックします クツールズプラス > 色で数える、スクリーンショットを参照してください:

2。 とで 色で数える ダイアログボックス、以下のスクリーンショットのようにしてください:
(1)選択 標準フォーマット から カラー方式 ドロップダウンリスト;
(2)選択 背景 から カウントタイプ ドロップダウンリスト。
(3)[レポートの生成]ボタンをクリックします。

注意:特定の条件付き書式の色で色付きのセルをカウントして合計するには、を選択してください 条件付き書式 から カラー方式 上記のダイアログのドロップダウンリスト、または選択 標準および条件付き書式 ドロップダウンリストから、指定した色で塗りつぶされたすべてのセルをカウントします。

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

世界 色で数える 機能は、背景色またはフォントの色でセルを計算します(カウント、合計、平均、最大など)。 無料トライアルを!


関連記事:


デモ:背景、条件付き書式の色に基づいてセルをカウントおよび合計します。


Kutools for Excel Excel用の300以上の便利なツールが含まれており、30日以内に無制限に無料で試すことができます。 今すぐダウンロードして無料トライアル!

最高のオフィス生産性ツール

Kutools for Excelはほとんどの問題を解決し、生産性を80%向上させます

  • 再利用: すばやく挿入 複雑な数式、チャート および以前に使用したものすべて。 セルを暗号化する パスワード付き。 メーリングリストを作成する そしてメールを送る...
  • スーパーフォーミュラバー (複数行のテキストと数式を簡単に編集できます); 読書レイアウト (多数のセルを簡単に読み取って編集する); フィルター範囲に貼り付け...
  • セル/行/列をマージする データを失うことなく; 分割セルコンテンツ; 重複する行/列を組み合わせる...重複セルを防止します。 範囲を比較する...
  • [複製]または[一意]を選択します 行; 空白行を選択 (すべてのセルは空です); スーパーファインドとファジーファインド 多くのワークブックで; ランダム選択...
  • 正確なコピー 数式参照を変更せずに複数のセル。 参照の自動作成 複数のシートに; 箇条書きを挿入、チェックボックスなど...
  • テキストを抽出、テキストの追加、位置による削除、 スペースを削除する; ページング小計の作成と印刷。 セルの内容とコメントを変換する...
  • スーパーフィルター (フィルタースキームを保存して他のシートに適用します); 高度な並べ替え 月/週/日、頻度など。 特殊フィルター 太字、斜体...
  • ワークブックとワークシートを組み合わせる; キー列に基づいてテーブルをマージします。 データを複数のシートに分割; xls、xlsx、PDFをバッチ変換...
  • 300以上の強力な機能。 Office / Excel2007-2019および365をサポートします。すべての言語をサポートします。 企業や組織に簡単に導入できます。 全機能30日間の無料トライアル。 60日間の返金保証。
kteタブ201905

Officeタブは、タブ付きのインターフェイスをOfficeにもたらし、作​​業をはるかに簡単にします

  • Word、Excel、PowerPointでタブ付きの編集と読み取りを有効にする、パブリッシャー、アクセス、Visioおよびプロジェクト。
  • 新しいウィンドウではなく、同じウィンドウの新しいタブで複数のドキュメントを開いて作成します。
  • 生産性が50%向上し、毎日何百ものマウスクリックが減ります。
officetab下部
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.