Excelで背景色に基づいてセルを数え、合計する方法は?
赤、緑、青など、背景色が異なるセルの範囲があるとします。ただし、その範囲のセルの中に特定の背景色があるものをいくつか数え、同じ色のセルを合計する必要があります。 。 Excelでは、カラーセルの合計とカウントを計算する直接的な公式はありません。ここで、この問題を解決するためのいくつかの方法を紹介します。
FilterとSUBTOTALによる特定の塗りつぶしの色に基づいてセルをカウントして合計する
GET.CELL関数による特定の塗りつぶし色に基づくセルの数と合計
ユーザー定義関数で特定の塗りつぶし色に基づいてセルを数え、合計する
Kutools for Excelを使用して、特定の塗りつぶし色(または条件付き書式設定色)に基づいてセルをカウントおよび合計する
Excelで色付きセルを数える、合計する、平均するためのワンクリック
優秀な カラーでカウント の特徴 Kutools for ExcelあなたはすぐにExcelでワンクリックで指定された塗りつぶしの色やフォントの色でセルを数える、合計する、平均することができます。 また、この機能は塗りつぶしの色やフォントの色によってセルの最大値と最小値も調べます。
全機能無料トライアル60日!
色付きセルをフィルタと小計でカウントして合計する
下のスクリーンショットに示すようなフルーツの売り上げ表があり、Amount列の色付きのセルを数えるか合計するとします。 このような場合は、[金額]列を色でフィルタ処理してから、フィルタ処理された色付きセルをSUBTOTAL関数でExcelで簡単にカウントまたは合計できます。
1。 空白のセルを選択してSUBTOTAL機能に入ります。
- 同じ背景色のすべてのセルを数えるには、式を入力してください = SUBTOTAL(102、E2:E20);
- 同じ背景色のすべてのセルを合計するには、式を入力してください = SUBTOTAL(109、E2:E20);
注注:どちらの式でも、E2:E20は色付きのセルを含むAmount列です。必要に応じて変更できます。
2。 テーブルのヘッダを選択して、をクリックします。 データ > フィルタ。 スクリーンショットを見る:
3。 フィルタアイコンをクリックします [金額]列のヘッダーセルにある 色で絞り込みます 指定された色はあなたが連続して数えるでしょう。 スクリーンショットを参照してください。
フィルタ処理後、両方のSUBTOTAL数式はAmount列のすべてのフィルタ処理済みカラーセルを自動的にカウントおよび合計します。 スクリーンショットを参照してください。
注注:この方法では、カウントまたは合計する色付きのセルが同じ列にある必要があります。
GET.CELL関数による色付きセルのカウントまたは合計
このメソッドでは、GET.CELL関数を使用して名前付き範囲を作成し、セルのカラーコードを取得してから、Excelでそのカラーコードで簡単にカウントまたは合計します。 次のようにしてください。
1に設定します。 OK をクリックします。 式 > 名前を定義する。 スクリーンショットを見る:
2。 New Nameダイアログで、以下のスクリーンショットのようにしてください。
(1)[名前]ボックスに名前を入力します。
(2)式を入力してください = GET.CELL(38、Sheet4!$ E2) 参照ボックスに(注意:式中、 38 セルコードを返すことを意味します。 Sheet4!$ E2 テーブルデータに基づいて変更する必要がある列ヘッダーを除く、Amount列の最初のセルです。
(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.色付きのセルを数える: =カラーファンクション(A、B:C、FALSE)
B.色付きのセルを合計します。 =カラーファンクション(A、B:C、TRUE)
注:上記の式では、 A カウントと合計を計算する特定の背景色のセルです。 紀元前 カウントと合計を計算するセル範囲です。
4。 たとえば、次のスクリーンショットを取って、数式を入力します=カラーファンクション(A1、A1:D11、FALSE) 黄色の細胞を数える。 そして、式を使う =カラーファンクション(A1、A1:D11、TRUE) 黄色のセルを合計する。 スクリーンショットを見る:
5。 他の色のセルを数えて合計したい場合は、ステップ4を繰り返してください。 次に、次の結果が得られます。
Kutools for Excelで特定の塗りつぶし色に基づいてセルを数え、合計する
上記のユーザー定義関数を使用すると、数式を1つずつ入力する必要があります。異なる色が多い場合、この方法は面倒で時間がかかります。 しかし、もしあなたが Kutools for Excelさん カラーでカウント ユーティリティを使用すると、色付きセルのレポートをすばやく生成できます。 あなたは色のついた細胞を数えて合計するだけでなく、色のついた範囲の平均値、最大値、最小値を得ることができます。
1。 使用する範囲を選択してをクリックします Kutools Plus > カラーでカウント、スクリーンショットを見る:
2。 とで カラーでカウント ダイアログボックスは、下のスクリーンショットのようにしてください。
(1)選択 標準フォーマット から カラー法 ドロップダウンリスト;
(2)選択 背景 から カウントタイプ ドロップダウンリスト。
(3)[レポート生成]ボタンをクリックします。
Kutools for Excel - 300以上の便利なExcelツールを含みます。 全機能無料トライアル60日、クレジットカードは不要です。 今それを入手!
注:色付きセルを特定の条件付き書式設定色で数えて合計するには、選択してください 条件付き書式 から カラー法 上のダイアログのドロップダウンリスト、または選択 標準および条件付き書式設定 指定された色で塗りつぶされたすべてのセルを数えるためにドロップダウンリストから。
これで、統計を含む新しいワークブックを入手できます。 スクリーンショットを参照してください。
関連記事:
デモ:背景、条件付き書式設定の色に基づいてセルを数え、合計します。
このビデオでは、 クツール タブと Kutools Plus タブが追加されます Kutools for Excel。 必要な場合はクリックしてください 詳細はこちら 制限なしに60日の無料試用をすることができます!
推奨生産性ツール
Office Tab
Chrome、Firefox、新しいInternet Explorerと同様に、Excelやその他のOfficeソフトウェアに便利なタブを持たせる。
Kutools for Excel
素晴らしい! 5分で生産性を向上させます。 特別なスキルは必要ありません。毎日2時間節約できます!
300 Excelの新機能は、Excelを非常に簡単かつ強力にする:
- データを失うことなくセル/行/列をマージします。
- 複数のシートとブックを結合して統合する。
- 範囲を比較し、複数の範囲をコピーし、テキストを日付、単位、通貨の変換に変換します。
- 色数、ページング小計、高度なソートとスーパーフィルター、
- 詳細選択/挿入/削除/テキスト/書式/リンク/コメント/ワークブック/ワークシートツール...
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
- ← Previous
- ...
- ...
- Next →