Googleスプレッドシートでセルの色に基づいてセルをカウントまたは合計するにはどうすればよいですか？
日常的なスプレッドシート作業では、特定のセル背景色に応じてセルの値をカウントしたり合計したりする必要がある場合があります。以下のスクリーンショットに示すように、たとえば、特定の色で強調表示されたセルのみをカウントまたは合計して、カテゴリ別やステータス別にデータを迅速に分析したい場合があります。このガイドでは、Googleスプレッドシート（このような色ベースの計算には組み込みサポートがない）だけでなく、Microsoft Excel（組み込み機能から高度なユーティリティまで、さまざまなアプローチを提供）での解決方法について説明します。
色ベースのデータ分析を扱う方法を理解することで、特に色がステータス、優先度、またはカテゴリを示すために使用される場合に、作業効率を向上させることができます。また、異なるソリューションを議論し、それらの使用状況を比較し、実用的な操作ヒントやエラーに関する注意点を提供して、タスクがスムーズに進むようにします。
- Googleスプレッドシートでスクリプトを使用してセルの色に基づいてセルの値をカウントする
- Googleスプレッドシートでスクリプトを使用してセルの色に基づいてセルの値を合計する
- Microsoft ExcelでKutools for Excelを使用してセルの色に基づいてセルの値をカウントまたは合計する
Googleスプレッドシートでスクリプトを使用してセルの色に基づいてセルの値をカウントする
Googleスプレッドシートには、背景色に基づいてセルをカウントする直接的なオプションはありません。ただし、カスタムのApps Scriptを使用することでこれを実現できます。このスクリプトはユーザー定義関数として機能し、数式のように参照することができます。設定および使用方法は次のとおりです：
1. スクリプトエディターを開くには、[ツール] > [スクリプトエディター] をクリックします。スクリーンショットをご覧ください：
2. プロジェクトウィンドウで、[ファイル] > [新規] > [スクリプトファイル] を選択して新しいコードモジュールを開きます。以下の画像をご覧ください：
3. プロンプトが表示されたら、新しいスクリプトコードの名前を入力して確認します。後で目的を識別しやすくするために、スクリプトに意味のある名前を付けてください。
4. 「OK」をクリックして、次にモジュール内のすべてのサンプルコードを次のコードに置き換えます。正確にコピーして貼り付けることを確認してください。
function countColoredCells(countRange,colorRef) {
var activeRg = SpreadsheetApp.getActiveRange();
var activeSht = SpreadsheetApp.getActiveSheet();
var activeformula = activeRg.getFormula();
var countRangeAddress = activeformula.match(/\((.*)\,/).pop().trim();
var backGrounds = activeSht.getRange(countRangeAddress).getBackgrounds();
var colorRefAddress = activeformula.match(/\,(.*)\)/).pop().trim();
var BackGround = activeSht.getRange(colorRefAddress).getBackground();
var countCells = 0;
for (var i = 0; i < backGrounds.length; i++)
for (var k = 0; k < backGrounds[i].length; k++)
if ( backGrounds[i][k] == BackGround )
countCells = countCells + 1;
return countCells;
};
5. このスクリプトファイルを保存し、シートに戻り、通常のGoogleスプレッドシートの数式と同じように新しい関数を使用します。空白のセルに =countcoloredcells(A1:E11,A1) と入力して、範囲 A1:E11 の中でA1の色と一致するセルをカウントします。Enterキーを押して結果を得ます。許可を求められた場合は、スクリプトをシートで実行することを承認してください。
注: A1:E11 はデータ範囲であり、A1 はカウントする色の基準セルです。信頼性を高めるために、結合されたセルは避けてください。
6. 他の色をカウントするには、必要な色の基準セルを変更して数式を繰り返します。範囲が変わった場合は、それに応じて数式内の範囲を調整してください。
エラーや予期しない結果が表示された場合は、スクリプトが保存されていること、正しい色の基準を使用していることを再度確認してください。Apps Scriptベースの関数は、関数またはその引数が変更された場合にのみ再計算されます。後でセルの色を変更した場合、数式を再度入力するか、Enterキーを押して更新してください。
Googleスプレッドシートでスクリプトを使用してセルの色に基づいてセルの値を合計する
Googleスプレッドシートで定義されたセルの色に基づいてセルの値を合計するには、Apps Scriptを使用する同様のアプローチが必要です。これは、財務表、ステータスログ、または色が数値データを持つカテゴリを示すあらゆるシナリオで特に有用です。
1. Googleスプレッドシートで、[ツール] > [スクリプトエディター] を開きます。プロジェクトウィンドウで、[ファイル] > [新規] > [スクリプトファイル] を選択して新しいコードモジュールを追加します。目的を追跡しやすいように「SumColoredCells」などのユニークな名前を指定し、確認してモジュールを作成します。
2. 「OK」をクリックし、新しいコードモジュールウィンドウで、提供されたスクリプトを貼り付けて既存のデフォルトコードを置き換えます。構文エラーを防ぐため、すべてのコードが正しくコピーされていることを確認してください。
function sumColoredCells(sumRange,colorRef) {
var activeRg = SpreadsheetApp.getActiveRange();
var activeSht = SpreadsheetApp.getActiveSheet();
var activeformula = activeRg.getFormula();
var countRangeAddress = activeformula.match(/\((.*)\,/).pop().trim();
var backGrounds = activeSht.getRange(countRangeAddress).getBackgrounds();
var sumValues = activeSht.getRange(countRangeAddress).getValues();
var colorRefAddress = activeformula.match(/\,(.*)\)/).pop().trim();
var BackGround = activeSht.getRange(colorRefAddress).getBackground();
var totalValue = 0;
for (var i = 0; i < backGrounds.length; i++)
for (var k = 0; k < backGrounds[i].length; k++)
if ( backGrounds[i][k] == BackGround )
if ((typeof sumValues[i][k]) == 'number')
totalValue = totalValue + (sumValues[i][k]);
return totalValue;
};
3. スクリプトを保存した後、シートに戻り、空白のセルに =sumcoloredcells(A1:E11,A1) と入力してEnterキーを押します。この数式は、背景色がA1と一致するA1:E11の値を合計します。この関数を使用する際は、対象となる合計セルが数値であることを確認してください。非数値文字は無視されます。
注: A1:E11 はデータ範囲を表し、A1 は色の基準を提供します。この数式は、表示可能な数値のみを合計します。結合されたセルや範囲内のエラーが合計に影響を与えないようにしてください。
4. 数式内の色の基準セルを変更して、異なる色カテゴリーの値を合計するプロセスを繰り返すことができます。データが更新された場合や背景色を変更した場合は、数式を更新して最新の結果を得ることを忘れないでください。
合計がゼロまたはエラー値を返す場合、範囲内に数値が含まれており、色の一致が正確であることを確認してください。また、セルの色だけが変更された場合、自動的に再計算されません。更新を強制するには、数式セルを編集してください。
Microsoft ExcelでKutools for Excelを使用してセルの色に基づいてセルの値をカウントまたは合計する
Microsoft Excelで作業する場合、プロジェクト管理、在庫、品質管理レポート内でセルを色に基づいてカウントまたは合計することがよく求められます。Kutools for Excelには専用の「Count by Color」ユーティリティがあり、これにより背景色またはフォントの色に基づいてカウントや合計を簡単に取得できます。これは、特に大規模なデータ範囲や、迅速かつ繰り返し可能な結果が必要な場合に役立ちます。
Kutools for Excelがインストールされたら、次の手順に従ってください：
1. 色に基づいてカウントまたは合計したい範囲を選択し、[Kutools Plus] > [Count by Color] をクリックします。以下のスクリーンショットを参考にしてください：
2. 「Count by Color」ダイアログボックスが表示されます。「Color method」で「Standard formatting」を設定し、「Count type」で「Background」を選択します。プレビューとオプションを慎重に確認してください：
3. 「Generate report」をクリックして、範囲内にある各色のカウントと合計の内訳を含む新しいワークシートを作成します。このレポートには、色付きセルの数と合計が含まれ、参照やさらなる分析が容易になります。
注: この機能は、条件付き書式設定またはフォントの色に基づいて値を計算することもできます。動的な分析には条件付き書式設定のルールを使用してください。それ以外の場合、静的な色塗りつぶしに最適です。元のセルの色に変更があった場合、「Count by Color」ユーティリティを再実行して、最新の結果を得る必要があります。問題が発生した場合は、Kutoolsが有効で最新バージョンであることを確認してください。
今すぐKutools for Excelをダウンロードして無料で試用！
最高のオフィス業務効率化ツール
| 🤖 | Kutools AI Aide：データ分析を革新します。主な機能：Intelligent Execution｜コード生成｜カスタム数式の作成｜データの分析とグラフの生成｜Kutools Functionsの呼び出し…… |
| 人気の機能：重複の検索・ハイライト・重複をマーキング｜空白行を削除｜データを失わずに列またはセルを統合｜丸める…… | |
| スーパーLOOKUP：複数条件でのVLookup｜複数値でのVLookup｜複数シートの検索｜ファジーマッチ…… | |
| 高度なドロップダウンリスト：ドロップダウンリストを素早く作成｜連動ドロップダウンリスト｜複数選択ドロップダウンリスト…… | |
| 列マネージャー：指定した数の列を追加 ｜列の移動 ｜非表示列の表示/非表示の切替｜ 範囲＆列の比較…… | |
| 注目の機能：グリッドフォーカス｜デザインビュー｜強化された数式バー｜ワークブック＆ワークシートの管理｜オートテキスト ライブラリ｜日付ピッカー｜データの統合 ｜セルの暗号化/復号化｜リストで電子メールを送信｜スーパーフィルター｜特殊フィルタ（太字/斜体/取り消し線などをフィルター）…… | |
| トップ15ツールセット：12 種類のテキストツール（テキストの追加、特定の文字を削除など）｜50種類以上のグラフ（ガントチャートなど）｜40種類以上の便利な数式（誕生日に基づいて年齢を計算するなど）｜19 種類の挿入ツール（QRコードの挿入、パスから画像の挿入など）｜12 種類の変換ツール（単語に変換する、通貨変換など）｜7種の統合＆分割ツール（高度な行のマージ、セルの分割など）｜… その他多数 |
Kutools for ExcelでExcelスキルを強化し、これまでにない効率を体感しましょう。 Kutools for Excelは300以上の高度な機能で生産性向上と保存時間を実現します。最も必要な機能はこちらをクリック...
Office TabでOfficeにタブインターフェースを追加し、作業をもっと簡単に
- Word、Excel、PowerPointでタブによる編集・閲覧を実現。
- 新しいウィンドウを開かず、同じウィンドウの新しいタブで複数のドキュメントを開いたり作成できます。
- 生産性が50%向上し、毎日のマウスクリック数を何百回も削減！
全てのKutoolsアドインを一つのインストーラーで
Kutools for Officeスイートは、Excel、Word、Outlook、PowerPoint用アドインとOffice Tab Proをまとめて提供。Officeアプリを横断して働くチームに最適です。
- オールインワンスイート — Excel、Word、Outlook、PowerPoint用アドインとOffice Tab Proが含まれます
- 1つのインストーラー・1つのライセンス —— 数分でセットアップ完了（MSI対応）
- 一括管理でより効率的 —— Officeアプリ間で快適な生産性を発揮
- 30日間フル機能お試し —— 登録やクレジットカード不要
- コストパフォーマンス最適 —— 個別購入よりお得