通常の関数ではセルに色がついているかどうか、判定ができないことはすでに述べました。
そこで
「4.0マクロ関数」というものを利用します。
「4.0マクロ関数」にはセルやワークブックの情報を把握する関数が複数用意されています。
「4.0マクロ関数」には次のような特徴があります。
- Excel5.0以前のマクロ用言語である。（現在は正式サポートしていない）
- セルに入力しても関数としては使用できない。
- 単体では再計算しない。
ワークシートに直接入力しても利用することは出来ませんが、
これに名前をつけることで間接的に利用することができます。
セルやフォントの「色番号」を求めるには4.0マクロ関数の
「GET.CELL関数」を使用します。
「A列のセル背景色の色番号」を求める「CELLCOLOR」という名前付き関数の設定手順を以下に紹介します。
次のようにA列のリストのうちいくつか色のついたセルがあり、
A列以外のセルへ色番号を求める場合を想定します。
任意の列の一行目のセルのいずれかを選択した状態で次の操作を行います。
| 1 |
メニューの
→
→
で「名前の定義」ダイアログボックスを開く。
|
| 2 |
「名前」テキストボックスにCELLCOLOR（この名前は任意）
「参照範囲」テキストボックスに=GET.CELL(63,!$A1)+NOW( )*0
追加(A)→OK
|
=GET.CELL(63,!$A1)+NOW( )*0の解説
- GET.CELL(63,!$A1)
- 「63」はセルの背景色を検査する場合のGET.CELL関数の引数です。
「!$A1」は検査を行う対象セルアドレスを指定します。
- +NOW( )*0
- 4.0マクロ関数単体で使用した場合、入力時点での計算しか行いません。
ワークシートの再計算時には再計算の対象になりません。
そこで再計算の対象となる関数NOWを組み合わせることで再計算を有効にします。
B列に
A列のセル背景色の色番号のリストを作成します。
| A | B |
C | D |
| 1 | 100 | =CELLCOLOR | | |
| 2 | 88 | | | |
| 3 | 95 | | | |
| 4 | 125 | | | |
B1に「=CELLCOLOR」と入力しコピー、B列に必要範囲へ貼り付け。
B列の各セルに
同じ行のA列のセル背景色の色番号が表示されます。
ただし、ウィンドウズの設定がデフォルトのままの場合、見かけの色は同じなので注意が必要です。
（「色なし」は「0」、「白」は「2」）
結果は下記の様になります。
| A | B |
C | D |
| 1 | 100 | 8 | | |
| 2 | 88 | 0 | | |
| 3 | 95 | 0 | | |
| 4 | 125 | 8 | | |
セルの背景色を基準にカウントや集計を行うには、背景色の色番号をもとめた列を基準として行います。
セルの背景色を表す数値をシート上に求めることが出来れば、
ワークシート関数でカウントや集計を容易に行うことが出来ます。
以下に良く使いそうなカウントや集計のパターンの例をご紹介します。
◆色のついたセルをカウントする
下記表B列には
◆4.0マクロ関数に名前をつけるで設定した
=CELLCOLORが入力されています。B列に表示されているのは同じ行のA列の背景色番号です。
C1へA列の色つきのセルをカウントする場合、C1セルへ次の数式を入力します。
| A | B |
C | D |
| 1 | 100 | 8 | =COUNTIF(B:B,">0") | |
| 2 | 88 | 3 | | |
| 3 | 95 | 0 | | |
| 4 | 125 | 8 | | |
A列にセル背景色が設定されたセルは、B列に「0」より大きい数値が返っています。
=COUNTIF(B:B,">0")で
B列の「0」より大きい数値のあるセルの数を計算します。
上記例ではC1セルに「3」が返ります。
ただし、C1セルの背景色を変更しただけではD1の数式は再計算されません。
C1のセルの色を書式設定で変更したあと、
F9 （再計算）キーを押すようにしてください。
◆特定の色のセルをカウントする
下記表B列には
◆4.0マクロ関数に名前をつけるで設定した
=CELLCOLORが入力されています。B列に表示されているのは同じ行のA列の背景色番号です。
C1へA列の背景色が「ライトブルー」であるセルをカウントする場合、C1セルへ次の数式を入力します。
| A | B |
C | D |
| 1 | 100 | 8 | =COUNTIF(B:B,B1) | |
| 2 | 88 | 3 | | |
| 3 | 95 | 0 | | |
| 4 | 125 | 8 | | |
A列にセル背景色が設定されたセルは、B列に「0」より大きい数値が返っています。
=COUNTIF(B:B,B1)でA列の背景色に「ライトブルー」が設定されたセル、
即ち、
B列の「ライトブルー」の色番号の返っているセルの数を計算します。
つまり、カウントしたい色のA列のセルと同じ行のB列のセルをCOUNTIF関数の検索条件とします。
色番号が明らかな場合は直接色番号を指定して
=COUNTIF(B:B,8)としてもかまいません。
上記例ではC1セルに「2」が返ります。
ただし、C1セルの背景色を変更しただけではD1の数式は再計算されません。
C1のセルの色を書式設定で変更したあと、
F9 （再計算）キーを押すようにしてください。
◆特定の色のセルを集計する
下記表B列には
◆4.0マクロ関数に名前をつけるで設定した
=CELLCOLORが入力されています。B列に表示されているのは同じ行のA列の背景色番号です。
C1へA列の背景色が「ライトブルー」であるセルを集計する場合、C1セルへ次の数式を入力します。
| A | B |
C | D |
| 1 | 100 | 8 | =SUMIF(B:B,B1,A:A) | |
| 2 | 88 | 3 | | |
| 3 | 95 | 0 | | |
| 4 | 125 | 8 | | |
A列にセル背景色が設定されたセルは、B列に「0」より大きい数値が返っています。
=SUMIF(B:B,B1,A:A)でA列の背景色に「ライトブルー」が設定されたセル、
即ち、
B列の「ライトブルー」の色番号の返っているセルと同じ行のA列のセルの数値を合計します。
つまり、集計したい色のA列のセルと同じ行のB列のセルをSUMIF関数の検索条件とします。
色番号が明らかな場合は直接色番号を指定して
=SUMIF(B:B,8,A:A)としてもかまいません。
上記例ではC1セルに「225」が返ります。
ただし、C1セルの背景色を変更しただけではD1の数式は再計算されません。
C1のセルの色を書式設定で変更したあと、
F9 （再計算）キーを押すようにしてください。
▲PAGETOP
