Excelでは色を基準にフィルター(絞り込み抽出)をしたい場合もあるかもしれません。複数条件ならどうすれば良いのか、色を付けたセルを数えるあるいはまとめるのを効率よくできる方法も押さえておきたいですよね。Excelでの色を基準にしたフィルターや抽出のやり方にはいくつか考えられ、条件付き書式など色でのフィルターがうまくできない場合があるのも特徴です。今回は、エクセルで文字の色などによって抽出する方法と関数との便利な組み合わせをご紹介します。
Excelで色でのフィルターを条件付き書式でしたいと思ったこともあるかもしれません。条件付き書式で色を付けたセルを数えることができれば、エクセルで該当する項目を色別で取り出したりカウントを簡単にしたりできそうですよね。しかしながら、条件付き書式には色でのフィルターはできない仕様になっています。条件付き書式は値の条件によって色などの書式を決めるものだからです。試しに、Excelで条件付き書式を開いてみると色を基準としたフィルターは出てこないことがわかるでしょう。条件付き書式のルールがいずれも値を基準にしています。Excelで色を基準にフィルターあるいは値を抽出する場合に条件付き書式ではないやり方はどうすれば良いのかを見ていきましょう。
Excelで色のついたセルを抽出する1つ目のやり方は、エクセルの値のどれかを基準にして同じもののみを絞り込む方法です。今回は、以下の画像のように、文字色とセルの色(背景色)が異なる1～10の数字を例に考えてみましょう。
エクセルで色付きセルのみ選択したい場合はまず、取り出したい色のついたセルを選択します。次に、右クリックして出てくる項目から『フィルター(E)』を選びましょう。ここで出てくる『選択したセルの値でフィルター(V)』『選択したセルの色でフィルター(C)』『選択したセルのフォント色でフィルター(F)』『選択したアイコンでフィルター(I)』の4つが抽出する条件です。
Excelで色のついたセルを抽出したい場合には『選択したセルの色でフィルター(C)』をクリックしましょう。これにより、最初に選んだセルの背景と同じ色のセルのみが表示されます。また、『選択したセルのフォント色でフィルター(F)』を選ぶと、文字色が同じもののみに絞り込むことも可能です。
エクセルで色なしセルを選択するには、最初に選ぶセルを背景色が付いていないものにすれば良いでしょう。今回の例であれば、”1″などを選んでから右クリック→『フィルター(E)』→『選択したセルの色でフィルター(C)』と進むことで、エクセル内の色なしセルのみを選択することができます。
また、エクセルの色付きセルを削除することによって、色の付いていないセルを残す方法も考えられるでしょう。その場合は、色の付いているセルを『選択したセルの色でフィルター(C)』で抽出し、選択して右クリック→『行の削除』を行います。セルの色の種類と同じ回数繰り返して、最後に残ったものが色のないセルのみになれば完了です。
Excelで色のついたセルを抽出する2つ目のやり方は、色フィルターを利用する方法です。エクセルにある絞り込み機能の一種で、セルの色を基準にして色付きセルのみ選択することができます。エクセルで色フィルターを使って設定する方法は、まず抽出したいセル範囲全体またはその列で項目が書かれているところよりも上のセルの1つを選択しましょう。次に、エクセルの上側にある『ホーム』→「編集」にある『並べ替えとフィルター』→漏斗のマークの『フィルター(F)』を順番にクリックします。
範囲の一番上のセルの右側に出てくる▼を押してみましょう。この中の項目で、範囲内の値を並べ替えたり絞り込みをしたりすることが可能です。ここで『色フィルター』をクリックすると、エクセルに書かれている項目で使われている文字色と背景色が一覧で表示されます。好みのものを選ぶことで、先ほどと同様に選んだ色と同じセルのみが表示されました。
色フィルターを解除したい場合は、『ホーム』→「編集」にある『並べ替えとフィルター』→漏斗のマークの『フィルター(F)』を順番にクリックすると、フィルターそのものが解除されるため、元の状態に戻せます。
Excelの色フィルターでは複数条件で絞り込みをしたいと思ったことがあるかもしれません。特に除外したい場合には不要なものをまとめて消せると便利でしょう。しかしながら、エクセルの色フィルターは複数条件での適用はできません。ただし、以下のように別のセルに違う色があり両方のセルで当てはまるものを選ぶことはできます。式を使った方法はこの後の項目で記載しますので、あわせて参考にしていただければ幸いです。
Excelの色フィルターで不要なものを除外したい場合は、消したいものを色フィルターで抽出し、出てきたエクセル内の色付きセルのすべての行を選択して右クリック→『行の削除』を行います。セルの色の種類と同じ回数繰り返して、最後に残ったものが色のないセルのみになれば完了です。
Excelで色フィルターが出てこないこともあるかもしれません。Excelで色フィルターができない原因としてまず考えられるのが他のユーザーと共有していることです。もし誰かと共有しているのであれば、『校閲』⇒「保護」の中にある『ブックの共有を解除』のボタンをクリックして変化があるかを確かめてみましょう。また、エクセル2016以前で色フィルターができない場合は『校閲』⇒「変更」の中にある『ブックの共有』をクリックして出てくるダイアログボックスを変更すると同じことです。ここでエクセルでフィルターがかからない場合は「複数のユーザーによる同時編集と、ブックの結合を許可する(A)』が適用されている可能性があるため、☑を外しましょう。
エクセル2019など比較的に新しいもので色フィルターができない原因として考えられるのが、絞り込む範囲に空白セルが存在することです。Excelではフィルターを適用する際に、選択されたデータから順番に自動的に下の行の値を探し、空白セルの手前までを範囲とみなします。そのため、先ほどのように一番上のセルのみをクリックしても問題なかった一方で、空白セルがあると不具合の原因になってしまうのです。もし空欄があれば、削除して間を詰めてからもう一度フィルターを試しましょう。
エクセルでフィルターがかからない事例としては結合されているセルが存在することも原因だと思われるかもしれません。しかし、実際に結合セルが存在するエクセルで色フィルターを適用すると、問題なく表示されることがわかります。
エクセルで色のついたセルを抽出した際に関数でカウントしたいこともあるかもしれません。データの個数を数える場合はCOUNT関数やCOUNTIF関数が思い浮かぶかもしれませんが、今回ご紹介するのはSUBTOTAL関数を使う方法です。SUBTOTAL関数とは、データベースやリストの集計値を返す機能があります。SUBTOTAL関数の基本的な書式は、『=SUBTOTAL (集計方法,参照1, [参照2] …) 』です。
一つ目の引数の「集計方法」はどのようにデータを取り出すかであり、色を付けたセルをカウントする場合は3または103を入力しましょう。ここでの数字の違いは、非表示の値を含めるかどうかです。ここで、『103』の方を選択することで、値が絞り込まれるとそれに応じて表示されているもののみを数えられます。二つ目の引数はどの値を集計するかです。ここではセル範囲を入力し、セル範囲が別の場所にもある(途中のセルを飛ばす)場合は参照2以降で羅列させましょう。
この状態で色フィルターを適用することで、色を付けたセルをカウントできます。元に戻すと数値も総数になるため、別のセルなどにメモする形で記録すると良いでしょう。
エクセルで文字色などでの抽出を関数でしたい場合もあるかもしれません。セルの取り出しはVBAなどを利用する場合もありますが、今回はマクロは使わずに関数や式を使うことで取り出す方法を考えてみましょう。単独の場合は、先ほどの作業にひと手間加えるだけです。まず、色フィルターを使いエクセルで抽出したい条件を選びましょう。該当するもののみが表示されるのでそれらをコピーします。色フィルターを解除してから、最後に任意のセルに貼り付けると問題なく取り出せるでしょう。
エクセルで色付きセルの抽出を複数条件で行うには、隣の列などのセルにマークを付けるのもおすすめです。フィルターによってデータが絞り込まれている場合、あるセルに付けたマークをコピーしてもその時点で表示されているもののみに適用され、右下の■をスクロールすることで素早く入力できます。それぞれの条件ごとに列を決めて該当するものをマークし、それらの列でさらに絞り込みをするか、IF関数を使って両方に当てはまるものを求めましょう。IF関数を使う場合は特にまたは(OR)の条件で抽出するのに有効で、色の付いているセル(複数種類あり)かつ文字にも色が付いているなどのときに当てはまるもののみを簡単に絞り込むことができます。
エクセルで色付きセルの抽出を別シートにするには、IF関数の条件式で出てくるセル参照に元データのシート名を加えることで可能です。たとえば、今回の式であれば別シートに『=IF(AND(OR (色付きセル!E4<>””,色付きセル!F4<>””),色付きセル!G4<>””),色付きセル!C4,””) 』などと入力することで、他のシートの値を基準に色フィルターで選んだ値のみを取り出せます。特に別シートに抽出する場合は、空白セルを除外しやすいのもメリットです。取り出した値が書かれている列を選択し、『データ』⇒「データツール」にある『重複の削除』をクリックします。基準になる列を選んでOKを押すと空白セルがまとめて消える(仕組みとしては、複数ある空白列が重複しているものとみなされて除去される)ため、抽出データのみをまとめることが可能です。
今回は、色の付いているセルを抽出する方法と、カウントしたり条件によってまとめたりする方法をご紹介しました。条件付き書式で色を付けたセルを数えることはできないため、色フィルターなどを使って絞り込みを行います。エクセル2019などで色フィルターができないあるいはかからない場合は、共有の問題があるか空白セルがないかを確かめましょう。関数と組み合わせることで、カウントしたりまとめるのを簡単にするのもおすすめです。最後までお読みいただきありがとうございました。