Note: The other languages of the website are Google-translated. Back to English

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でワンクリックするだけで、指定した塗りつぶしの色またはフォントの色でセルをすばやくカウント、合計、平均化できます。 さらに、この機能は、塗りつぶしの色またはフォントの色によってセルの最大値と最小値も検出します。


色別の広告数2

Kutools for Excel - Excel 用の 300 以上の便利なツールが含まれています。 全機能無料トライアル 30-day、クレジットカード不要! 今すぐ入手

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

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

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

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

3。 次に、元のテーブルに新しいColor列を追加します。 次に、数式を入力します = 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 色の列です。 色数 指定された名前付き範囲です。 $ 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-day、クレジットカード不要! 今すぐ無料トライアル!

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-day、クレジットカード不要! 今すぐ無料トライアル!

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

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

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

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

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


関連記事:


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


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

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

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

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

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

  • Word、Excel、PowerPointでタブ付きの編集と読み取りを有効にする、パブリッシャー、アクセス、Visioおよびプロジェクト。
  • 新しいウィンドウではなく、同じウィンドウの新しいタブで複数のドキュメントを開いて作成します。
  • 生産性を 50% 向上させ、毎日何百回もマウス クリックを減らすことができます!
officetab下部
コメントを並べ替える
コメント (235)
まだ評価はありません。 最初に評価してください!
このコメントは、サイトのモデレーターによって最小化されました
とても便利なツール、ありがとうございました
このコメントは、サイトのモデレーターによって最小化されました
関数を挿入した後、値を変更すると、合計は自動的に更新されません、任意の提案
このコメントは、サイトのモデレーターによって最小化されました
Alt-Ctrl-F9は再計算します
このコメントは、サイトのモデレーターによって最小化されました
他の誰かがファイルを使用していて、Alt-Ctrl-F9について知らない場合は、ボタンを追加して更新できます。 このコード行をマクロ「Application.CalculateFull」に追加するだけです。 これは、ボタンにプログラムされたAlt-Ctrl-F9と同じです。
このコメントは、サイトのモデレーターによって最小化されました
En que parte agrego la linea para crear el boton
このコメントは、サイトのモデレーターによって最小化されました
最初の行のすぐ下に、Application.volitileというXNUMX番目の行を追加します。これにより、何かが更新された後に再計算されます。
このコメントは、サイトのモデレーターによって最小化されました
入れてみました Application.Volatile 最初の行を元に戻すと何も起こりません:(
このコメントは、サイトのモデレーターによって最小化されました
[quote]最初の行のすぐ下に、Application.volitileというXNUMX番目の行を追加します。これにより、何かが更新された後に再計算されます。12345678998765432154ことで[/ quote] Application.Volatileは、Application.volitileではなく正しい関数です。
このコメントは、サイトのモデレーターによって最小化されました
application.volatileという行が表示されません。 どこにありますか?
このコメントは、サイトのモデレーターによって最小化されました
ColorFunction数式でセルをダブルクリックし、Enterキーを押しました。 更新しました。
このコメントは、サイトのモデレーターによって最小化されました
非常に便利。 ありがとうございました
このコメントは、サイトのモデレーターによって最小化されました
私も式を試しました。 最初の行で機能し、XNUMX番目とXNUMX番目では機能しません。 ????
このコメントは、サイトのモデレーターによって最小化されました
Jarodに同意します。 私の関数の最初の9行は、次のように述べています。Function ColorFunction(rColor As Range、rRange As Range、Optional SUM As Boolean)Application.Volatile問題は、Alt-Ctrl-FXNUMXのみが再計算されることです。 より自動化されたソリューションを探しています。 アイデア?
このコメントは、サイトのモデレーターによって最小化されました
次のようにしてみてください:1。説明されているように関数が保存されていることを確認します2.例の使用を許可します*数百行のデータがあります*より多くのデータで列AからABを使用します*問題が発生したときはいつでもデータ、黄色でハイライトします*このクールな式を使用して、各行のハイライトの数を「カウント」します。3.行ごとのハイライトの数をカウントする方法a。 形 セルの範囲カウント(または合計)したいハイライトが含まれている可能性があります*私にとっては、各行(私の範囲)でフラグが立てられたハイライトの量をカウントしたいと思いますb。 カウント(または合計)を報告するセルを選択します*私にとっては、データの右端に配置しました...列AEc。 項目b(上記)で選択したセルに次の数式を挿入します= colorfunction(AE3、A3:AB3、FALSE)*私にとっては、この数式をセルAE3(行の終わり)に配置し、同じものを強調表示しましたフォーミュラセルイエローd。 次に、この式をすべてのデータ行4にコピーしました。 カウントは行われませんでした。 (悪い)5ここでの重要な問題:a。 CTL + ALT+F9を押しました。 PRESTO !!! できます。! このバンターがお役に立てば幸いです。 乾杯はこれを行います:ABCD 1 2 3 4
このコメントは、サイトのモデレーターによって最小化されました
とても良いです、どうもありがとう!!!
このコメントは、サイトのモデレーターによって最小化されました
とても便利な記事、どうもありがとう
このコメントは、サイトのモデレーターによって最小化されました
素晴らしい!!! どうもありがとう!!!
このコメントは、サイトのモデレーターによって最小化されました
すごい、どうもありがとう
このコメントは、サイトのモデレーターによって最小化されました
これは素晴らしいツールです。共有してくれてありがとう! 質問がXNUMXつあります。セルの色をある色から別の色に変更しても、マクロ数式が自動的に更新されないことに気付きました。 セルの色が変わったときに自動的に更新する方法はありますか? 加算式のあるセルをダブルクリックしてEnterキーを押すと更新されますが、これらのセルの多くを含むシートがあり、毎回手動で更新する必要はありません。
このコメントは、サイトのモデレーターによって最小化されました
返信ありがとうございます。申し訳ありませんが、現在の状況では、VBAコードでは指摘された質問を解決できません。手動で更新する必要があります。
このコメントは、サイトのモデレーターによって最小化されました
また、フォーマットペインタを使用してセルの色を更新すると、数式が期待どおりに機能することもわかりました。 まだ完璧ではありません。
このコメントは、サイトのモデレーターによって最小化されました
こんにちは、私は試しました。 ワークシートには、どのセルに関係なく「Enter」が必要です。 したがって、セルに色を付けたら、色の付いたセルにメモを入力して「入力」するだけです。
このコメントは、サイトのモデレーターによって最小化されました
CTL + ALT + f9を試してください。これにより、すべてのセルが追加の数式で一度に更新されます。 完全に自動ではありませんが、少なくとも、加算式を使用して各セルをクリックするよりも優れています。
このコメントは、サイトのモデレーターによって最小化されました
これは私には何もしませんでした:(
このコメントは、サイトのモデレーターによって最小化されました
こんにちは、エラーが発生します。 エラーを遵守し、あいまいなエラーが発生します。
このコメントは、サイトのモデレーターによって最小化されました
数式を適用しましたが、セルにNAMESと表示されていますか? 修正する必要があるもの
このコメントは、サイトのモデレーターによって最小化されました
こんにちはRAMON、多分あなたは上記のコードをモジュールにコピーしなかったでしょう。 最初にコードをモジュールにコピーして保存してから、数式を適用する必要があります。 あなたはそれを試すことができます。 それでも問題が解決しない場合は、お知らせください。
このコメントは、サイトのモデレーターによって最小化されました
こんにちは、初めて動作しましたが、今、合計にセルを追加する必要があるとき...コードを再度導入し、保存して、数式をもう一度記述してみました...「名前?」と表示されます。
このコメントは、サイトのモデレーターによって最小化されました
私は同じ問題を抱えています-最初はうまく機能しましたが、別のセルを追加した後にNAMEエラーが発生しました。 モジュールを削除して再度追加しようとしましたが、関数を再入力してください...喜びはありません。
このコメントは、サイトのモデレーターによって最小化されました
マルコスを再度有効にする必要があるかもしれません。 Excelのバージョンに応じて、画面の上部で点滅するはずです。 :)
このコメントは、サイトのモデレーターによって最小化されました
Skyyang、私があなたに文書を電子メールで送るならば、あなたはそれで私を助けることができますか? マイク
このコメントは、サイトのモデレーターによって最小化されました
どうもありがとう! これは天才です、助けてくれてありがとう!
このコメントは、サイトのモデレーターによって最小化されました
どうもありがとうございました、私は成功した結果を一日中探していました、そして今私たちはXNUMXつを持っています:)
このコメントは、サイトのモデレーターによって最小化されました
ありがとう。 説明したようにそれは私のために働いた。 ただし、次にファイルを再度開いた後、この数式を含むすべてのセルにエラーが表示されていました。 コーディングを再度コピーする必要がありました。 足りないものはありますか? ファイルを他の人と共有する必要がありますが、他の人は問題を「修正」できません。 ありがとう、カー。
このコメントは、サイトのモデレーターによって最小化されました
あなたはそれをExcelマクロワークブックとして保存する必要があります
このコメントは、サイトのモデレーターによって最小化されました
よくできていますが、条件付き書式のセルの色でどのように機能するか???
このコメントは、サイトのモデレーターによって最小化されました
これはすごい、ありがとう!
このコメントは、サイトのモデレーターによって最小化されました
それはうまくいきました!!!
このコメントは、サイトのモデレーターによって最小化されました
親愛なる先生、実際には素晴らしいコードですが、セルの色を変更してもセルを計算し続けるという問題に直面しました
このコメントは、サイトのモデレーターによって最小化されました
ありがとうサー、これは素晴らしい仕事であり、私を大いに助けてくれます。
このコメントは、サイトのモデレーターによって最小化されました
色付きのセルがたくさんあるExcelファイルがあります。 これらの色をデータに変換する方法はありますか? したがって、赤いセルにはデータとして「赤」が含まれ、青い色のセルには「青」が含まれます。 ジェームズ
このコメントは、サイトのモデレーターによって最小化されました
探しているターゲットをヒットするようなタイムリーで適切な記事:)ありがとう
このコメントは、サイトのモデレーターによって最小化されました
これを機能させることができないようです。 手動で色付けされたセルに対してのみ機能しますか? 条件付き書式で色付けされたセルを数える必要がありますが、現時点ではボールを再生していません。
このコメントは、サイトのモデレーターによって最小化されました
Ianと同じ問題が発生しています。条件付き書式で設定されたカラールールに基づいて数値を合計しようとしていますが、これではうまくいかないようです。 助言がありますか?
このコメントは、サイトのモデレーターによって最小化されました
今後のバージョンでそれを強化しようとします。 :-)
このコメントは、サイトのモデレーターによって最小化されました
一種の回避策として機能するPowershellスクリプトをスローしました: "#setup Excel $ excelApp = New-Object -comobject Excel.Application $ excelApp.Quit()$ excelApp.Visible = $ True $ workbook = $ excelApp.Workbooks .Open( "H:\ Desktop \ test.xlsx")#これをExcelファイルアドレスに変更します。$worksheet = $ workbook.Worksheets.Item( "Sheet1")#シートが「Sheet1」と呼ばれない場合はこれを変更します#static変数$row= 1 $ column = 1#この変数を検索する列に変更$ totalRow = $ worksheet.UsedRange.Rows.Count do {$ currentCell = $ worksheet.cells.item($ row、$ column )if($ currentCell.text -eq "SEARCH_FOR_THIS"){$ worksheet.cells.item($ row、$ column).Interior.ColorIndex = 44#この番号を変更して、新しいセルの色を変更します "$ row、$ column =空白。 色付け"}$ row ++ $ row} while($ row -lt $ totalRow)"ScriptComplete。""SAVING ... "$ excelApp.Save $ excelApp.Quit()[System.Runtime.Interopservices.Marshal] :: ReleaseComObject ($ excelApp) "このスクリプトはスプレッドシートを検索し、" SEARCH_FOR_THIS "を含むセルが見つかった場合(おそらくスクリプトのその部分を変更する必要があります)、そのセルをスクリプト内の任意の色番号に変換します(現在44に設定すると、奇妙に見えるオレンジ/黄褐色になります)。 スクリプトが終了すると、保存しようとしていることをExcelに通知しますが、Excelはあまり信頼できるプログラムではないため、[保存]をクリックするように求められます。 それを行うと、それは閉じます。 次に、ファイルをバックアップして開き、このページにリストされているすべてのビジネスを実行します。スクリプトが検索する色が、このページにリストされているスクリプトが検索する色と同じであることを確認します。 お役に立てれば!
このコメントは、サイトのモデレーターによって最小化されました
伝説! おかげで魅力が働いた
ここにはまだコメントが投稿されていません
もっと読む
あなたのコメントを残す
ゲストとして投稿
×
この投稿を評価:
0   文字
推奨される場所

フォローする

著作権©の2009 - WWW。extendoffice.com。 | | 全著作権所有。 搭載 ExtendOffice。 | サイトマップ
MicrosoftおよびOfficeのロゴは、米国MicrosoftCorporationの米国およびその他の国における商標または登録商標です。
SectigoSSLで保護