エクセルでは色付きセルのみ表示するなど特定の書式で取り出したい場合もあるかもしれません。特に、エクセルで色付きだけ表示できるとデータの処理などで活用できそうですよね。エクセルで色付きセルの抽出を関数で行うには、Excelで色フィルターを複数つかって組み合わせることがポイントです。今回は、エクセルで色のついたセルを数えるあるいは抽出を関数などを使ってする方法をご紹介します。
◎条件付き書式で色を付けたセルを数えることや色フィルターはできない？
Excelで色でのフィルターを条件付き書式でしたいと思うこともあるかもしれません。条件付き書式で色を付けたセルを数えることができれば、エクセルで該当する項目を色別で取り出したりカウントを簡単にしたりできそうですよね。しかしながら、条件付き書式には色でのフィルターはできない仕様になっています。条件付き書式は値の条件によって色などの書式を決めるものだからです。つまり、条件付き書式で色を付けたセルを数える仕組みはありません。そのため、色を付けたセルを抽出・カウントしたい場合は、条件付き書式以外の方法を使う必要があります。
◎Excel/エクセルで色付きセルのみ選択、複数する方法も
Excelで色付きセルのみ選択する1つ目のやり方は、特定の値を基準にする方法です。たとえば、以下の例のように文字色とセルの背景色を使い分けて1～10の数字を入力したとしましょう。
エクセルで色付きセルのみ選択したいときは、複数を希望する場合も含めて、まず取り出したい基準のセルを選びます。そのうえで右クリック⇒『フィルター(E)』を選びましょう。すると、4つが抽出条件として表示されます。『選択したセルの値でフィルター(V)』『選択したセルの色でフィルター(C)』『選択したセルのフォント色でフィルター(F)』『選択したアイコンでフィルター(I)』です。
Excelで色のついたセルを抽出したい場合には『選択したセルの色でフィルター(C)』をクリックしましょう。これにより、最初に選んだセルの背景と同じ色のセルのみが表示されます。また、『選択したセルのフォント色でフィルター(F)』を選ぶと、文字色が同じもののみに絞り込むことも可能です。
●エクセルで色なしセルを選択するには？色付きセルを削除して残す方法も
エクセルで色なしセルを選択する方法は、まず背景色が付いていないセルを基準にすることです。たとえば今回の例では「1」のセルを選び、右クリック→『フィルター(E)』→『選択したセルの色でフィルター(C)』と進めます。すると、エクセル内の色なしセルのみを選択することが可能です。
また、エクセルの色付きセルを削除して色なしセルを残す方法もあります。その場合は『選択したセルの色でフィルター(C)』を使ってエクセルに色付きセルのみ表示し、右クリック→『行の削除』を選びましょう。セルの色の種類ごとに繰り返し削除していけば、最終的に色のないセルだけが残ります。
◎Excel/エクセルの色フィルターができない理由は？正しい設定・除外の方法
Excelで色のついたセルを抽出する2つ目のやり方は、色フィルターを利用する方法です。Excelにある絞り込み機能の一種で、セルの色を基準にして色付きセルのみ選択することができます。エクセルで色フィルターを使って設定する方法は、まず抽出したいセル範囲全体またはその列で項目が書かれているところよりも上のセルの1つを選択しましょう。次に、エクセルの上側にある『ホーム』→「編集」にある『並べ替えとフィルター』→漏斗のマークの『フィルター(F)』を順番にクリックします。
範囲の一番上のセルの右側に出てくる▼を押してみましょう。この中の項目で、範囲内の値を並べ替えたり絞り込みをしたりすることが可能です。ここで『色フィルター』をクリックすると、エクセルに書かれている項目で使われている文字色と背景色が一覧で表示されます。好みのものを選ぶことで、先ほどと同様に選んだ色と同じセルのみが表示されました。
ただし、条件付き書式で付けたものは色フィルターでは認識できない仕様となっています。正しく設定するには、セルに直接色を塗る操作が必要です。さらに、エクセルの色フィルターで特定の色を「除外」したい場合は、その色以外にチェックを入れることで、希望するデータだけを残すことができます。色フィルターを解除したい場合は、『ホーム』→「編集」にある『並べ替えとフィルター』→漏斗のマークの『フィルター(F)』を順番にクリックすると、フィルターそのものが解除されるため、元の状態に戻せます。
●Excelの色フィルターで除外などに使える複数条件の設定を行うには？
Excelの色フィルターでは複数条件で絞り込みをしたいと思ったことがあるかもしれません。特に除外したい場合には不要なものをまとめて消せると便利でしょう。しかしながら、エクセルの色フィルターは複数条件での適用はできません。ただし、以下のように別のセルに違う色があり両方のセルで当てはまるものを選ぶことはできます。式を使った方法はこの後の項目で記載しますので、あわせて参考にしていただければ幸いです。
Excelの色フィルターで不要なものを除外したい場合は、消したいものを色フィルターで抽出し、出てきたエクセル内の色付きセルのすべての行を選択して右クリック→『行の削除』を行います。セルの色の種類と同じ回数繰り返して、最後に残ったものが色のないセルのみになれば完了です。
●エクセル2019/Excelで色フィルターが出てこない/できない理由：共有など
Excel/エクセルで色フィルターが出てこない場合、まず考えられる原因の一つが他のユーザーとブックを共有していることです。
①『校閲』→「保護」→『ブックの共有を解除』をクリック
②(エクセル2016以前で色フィルターができない場合）『校閲』→「変更」→『ブックの共有』のダイアログで「複数のユーザーによる同時編集と、ブックの結合を許可する(A)」のチェックを外す
でExcelの色フィルターができない要因の一つである共有が解除できるため、試してみましょう。
エクセル2019など比較的に新しいもので色フィルターができない原因として考えられるのが、絞り込む範囲に空白セルが存在することです。Excelではフィルターを適用する際に、選択されたデータから順番に自動的に下の行の値を探し、空白セルの手前までを範囲とみなします。空白セルがある場合は、削除して間を詰めてから再度フィルターを試しましょう。なお、結合されているセルが原因と思われることもありますが、実際には結合セルがあっても色フィルターは正しく適用されます。
◎エクセルで色付きセルのカウントを関数でする方法(フィルターなし)
エクセルで色付きセルのカウントはフィルターなしで関数で集計する方法があります。エクセルで色のついたセルを数える関数は基本的にSUBTOTAL関数を使うと便利です。SUBTOTAL関数は、データベースやリストの集計値を返す機能で、書式は『=SUBTOTAL(集計方法,参照1,[参照2]…)』となります。
[集計方法]には今回は3または103を使用しましょう。ともにCOUNTAですが、数字の違いは非表示セルを含めるかどうかです。[参照1]には集計対象のセル範囲を入力し、必要に応じて複数の範囲を列挙します。
この状態で色フィルターを適用すると、色付きセルのみをカウント可能です。フィルターを解除すると総数に戻るため、必要に応じて別のセルにコピーして記録しておきましょう。
補足として、COUNT関数やCOUNTIF関数も利用できます。COUNT関数は単純に数値を数え、COUNTIF関数は条件に合うセルをカウントします。SUBTOTAL関数は、フィルターで非表示になっているセルを無視するなど、フィルターとの連動に優れている点が特徴です。
◎Excel/エクセルで色付きセル/文字色の抽出を関数でする方法(複数列や別シート対応)
エクセルで文字色などの抽出を関数でしたい場合もあるかもしれません。Excelで色付きセルの抽出はVBAでもより自由にできますが、マクロを使わず関数でも行えます。まず、Excel/エクセルの色フィルターを複数つかうために、抽出条件ごとに右隣の列を準備し、条件に合致するセルに〇を入力します。図の例では、1列右に黄色背景・2列右に青背景・3列右に赤文字が付いたセルです。そのうえで、さらに隣の列に〇が付いている行の値のみを抽出する式を入力しましょう。
例：『=IF(AND(OR(E4<>””,F4<>””),G4<>””),C4,””)』
背景色がありかつ文字にも色が付いている場合は抽出するというものです。このように、エクセルでの色付きセルの抽出を複数列に条件をわけることで、なじみのあるIF関数で取り出すことができます。
●エクセルで色付きセルの抽出を別シートにするには？
エクセルで色付きセルの抽出を別シートにするには、IF関数の条件式で出てくるセル参照に元データのシート名を加えることで可能です。たとえば、今回の式であれば別シートに『=IF(AND(OR(色付きセル!E4<>””,色付きセル!F4<>””),色付きセル!G4<>””),色付きセル!C4,””)』などと入力することで、他のシートの値を基準に色フィルターで選んだ値のみを取り出せます。特に別シートに抽出する場合は、空白セルを除外しやすいのもメリットです。取り出した値が書かれている列を選択し、『データ』⇒「データツール」にある『重複の削除』をクリックします。基準になる列を選んでOKを押すと空白セルがまとめて消えるため、抽出データのみを整理することが可能です。
さらに、[Excel 365]や[エクセル 2021]以降ではFilter関数を使うことで、色付きセルの取り出しのうちIF＋ORの操作に限り簡略化できます。例えば、抽出条件に合致するセルを別列にまとめて表示したい場合は、=FILTER(A2:A100, (B2:B100=”〇”)+(C2:C100=”〇”)+(D2:D100=”〇”)) のように入力しましょう。この式では、色付きセルを表すB～D列に付いた〇のいずれかに該当する行のA列の値を一括で取り出せます。
◎まとめ
今回は、色の付いているセルを抽出する方法と、カウントしたり条件によってまとめたりする方法をご紹介しました。条件付き書式で色を付けたセルを数えることはできないため、色フィルターなどを使って絞り込みを行います。エクセル2019などで色フィルターができないあるいはかからない場合は、共有の問題があるか空白セルがないかを確かめましょう。関数と組み合わせることで、カウントしたりまとめるのを簡単にするのもおすすめです。最後までお読みいただきありがとうございました。
コメント