この記事では、データがリストにあるかどうかを確認・〇の表示・色付け・抽出する方法について解説していきます。
リストに「あれば〇」「なければ×」を表示する方法や、リストにあれば抽出する方法や、リストにあるものに「色付け」する方法についてもご紹介します。
主に
2. VLOOKUP関数とISERROR関数とIF関数
3. VLOOKUP関数とIFERROR関数
4. 条件付き書式
を使用します。
とある単語がリストにあるかを検索するには、単純に検索機能やフィルタでのテキスト検索を使用すれば済む話です。
しかしたくさんのデータをチェックしたり、該当データがあるかどうかを表示させたりするためには、これから紹介する方法を使うとわかりやすいです。
エクセルにてリストにあるかどうかを確認し、「あれば〇」「なければ×」と表示する方法1【COUNTIF関数とIF関数】
リストに「あれば〇」「なければ×」と表示するには、IF関数とCOUNTIF関数を組み合わせて使用します。
今回は下記のようなリストを用意しました。
右の表の単語が、左の表にあるかどうかをチェックしていきます。
右側の「チェック」の列に、「あれば〇」「なければ×」と表示されるようにします。
今回使用する数式は、以下の通りです。
|
=IF(COUNTIF(検索したい範囲,検索したい単語)>0,”○”,”×”) |
具体的に見ていきましょう。
上記の表のE2を選択し、以下の関数を入力します。
この場合の「検索したい範囲」は、左のリストの「B2:B7」になります。
そして「検索したい単語」は「D2」セルの「CCC」です。
「B2:B7」の中に「CCC」があるため、「○」と表示されています。
同じように「ZZZ」も調べてみましょう。
「検索したい単語」に「D3」セルを指定します。
「ZZZ」は左のリストにないため、「×」と表示されます。
数式の詳細を見ていきます。
今回はIF関数の中にCOUNTIF関数を入れ子にして組み合わせています。
まずCOUNTIF関数は、条件に合うもののカウントを行う関数です。
使い方は以下の通りになります。
|
=COUNTIF(範囲,検索条件) |
IF関数は、論理式が真か偽かによって任意の処理を指定できる関数です。
使い方は以下の通りになります。
これらを組み合わせると、以下のようになります。
具体的に解説します。
今回はリストの中に検索したい単語があった場合＝真の場合には「○」、単語がない場合＝偽の場合には「×」を表示するようにしたいので、IF関数を使用して以下のようにします。
|
=IF(検索したい単語の数を数える条件式(COUNTIF関数)>0, “○”,”×”) |
「検索したい単語の数」が0より上であること（一致するものがあること）を示すために、「>0」を指定しています。
○や×は文字列のため、“”で囲みます。
“○”を“あり”、“דを“なし”に変更すれば、表示される結果は「あり」か「なし」になります。
例えば単語がない場合を空欄にしたい場合、［偽の場合］の指定を「“”」とすれば空欄になります。
これで条件式が真の場合(単語の数が1つ以上)では「○」、偽の場合(単語の数が0)では「×」を表示することができます。
上記の「検索したい単語がある場合を示す式」にCOUNTIF関数を用います。
特定の範囲の中に検索ワードと同じものがいくつあるか数えます。
今回のリストでは、「検索したい範囲」が「B2:B7」です。
「検索したい単語」が「D2」の場合、COUNTIF(B2:B7,D2)と指定します。
フィルやコピペした際に検索範囲がずれないように、範囲を指定するセルには「$」を付けます。
|
=COUNTIF($B$2:$B$7,D2) |
「$」を付ける場合には、付けたい引数(今回の場合はB2:B7)にカーソルを持っていった状態で「F4キー」を押すと楽です。
逆に検索したい単語には「$」を付けません。
これらを組み合わせた式が、最初に示したこの数式になります。
|
=IF(COUNTIF(検索したい範囲,検索したい単語)>0,”○”,”×”) |
以上、エクセルでIF関数とCOUNTIF関数を用いてリストに「あれば〇」「なければ×」を表示する方法を紹介しました。
エクセルにてリストにあるかどうかを確認し、「あれば〇」「なければ×」とする方法2【VLOOKUP関数とISERROR関数とIF関数】
リストに「あれば〇」「なければ×」と表示する別の方法として、IF関数とISERROR関数とVLOOKUP関数を組み合わせたものがあります。
今回は下記のようなリストで試してみます。
左のリストの「分類」の中に一致するものがあるかどうかをチェックしていきます。
今回使用する数式は、以下の通りです。
|
=IF(ISERROR(VLOOKUP(検索したい単語,検索範囲,1,0)),”×”,”〇”) |
上記の場合はF2を選択し、以下のような数式を入力します。
「検索したい単語」は「E2」セルです。$で固定せずに下の行にコピーしていくと、E3、E4…と指定することになります。
「検索範囲」は、左のリストの「B2:B6」になります。こちらはずれないように$で固定しましょう。
この結果、「果物」と「野菜」は○になり、リストに存在しない「魚」は×となります。
数式の詳細を見ていきます。
今回はISERROR関数の中にVLOOKUP関数を入れ子にし、さらにその式をIF関数の中に入れ子にして組み合わせています。
VLOOKUP関数は、指定の表の検索値に対応する列番号のセルの内容を返す関数です。
|
=VLOOKUP(検索値、範囲、列番号、［検索方法］) |
VLOOKUP関数はXLOOKUP関数でも代用できますので、使用可能なバージョンのExcelがある方はそちらも可能です。
ISERROR関数は、指定した対象がエラーかを判定する関数です。エラーであればTRUE、正しい値であればFALSEを返します。
|
=ISERROR(テストの対象) |
エラーかどうかを判定したいセルを引数に指定して使用します。
ISERROR関数は、エラーの種類の内「#N/Aエラー」(参照の対象が見つからない)なのかどうかを判定するISNA関数でも代用可能です。
先程も使用したIF関数は、論理式が真か偽かによって任意の処理を指定できる関数です。
|
=IF(条件式,［値が真の場合の処理］,［値が偽の場合の処理］) |
これらを組み合わせると、以下のようになります。
|
=IF(ISERROR(VLOOKUP(検索したい単語,検索範囲,1,0)),”×”,”〇”) |
具体的に解説します。
VLOOKUP関数は、特定の範囲の中に検索ワードと同じものがあれば抽出します。
|
= VLOOKUP(検索したい単語,検索範囲,列番号,［検索方法］) |
「検索したい単語」はE列のセル、「検索範囲」は左のリストの「B2:B6」を指定します($で固定も忘れずに)。
「列番号」では、「範囲」の中でどの列を抽出したいかを指定します。今回はB2:B6の1列のみを範囲としていますが、B列が「1列目」となります。今回は「1」と入力します。
もしB～C列を範囲に指定している場合、「2」と入力すればC列を指定したことになるため、例えば果物の欄の結果は「りんご」と表示されます。
「検索方法」に関しては、検索ワードと完全一致で検索するかどうかを指定します。
「1」または「TRUE」を入力すると近似一致、「0」または「FALSE」を入力すると完全一致を意味します。TRUEでは半端な結果しか出ないため、基本は「FALSE」で完全一致の検索を行います。
|
=VLOOKUP(E2,$B$2:$B$6,1,0) |
上の表の場合はこのように指定します。
「魚」は該当するものがないので、VLOOKUP関数ではエラーとなります。
ISERROR関数は、条件式に一致しない場合の処理を指定できます。
しかし今回は処理を指定するのではなく、VLOOKUP関数の真偽の結果を返すために使用します。
VLOOKUP関数の結果がエラー(＝検索値が見つからない場合)は「TRUE」、エラーにならなかった場合(＝検索値が見つかった場合)は「FALSE」を返します。
上記の表の場合、見つからなかった「魚」の項目のみ「TRUE」(エラー)となります。
VLOOKUP関数単独では真偽の値を返さないため、このようにISERROR関数をかませる必要があります。
上記の状態だけでもデータがリストにあるかどうかは判断できます。
しかし「ある」＝「FALSE」で「ない」＝「TRUE」だと意味が逆のように感じられますね。
そこでIF関数を使用し、1つ目の例のように〇か×で表示させるようにしてみます。
IF関数の使い方は1つ目の例とほぼ同様です。
|
=IF(ISERROR(VLOOKUP(検索したい単語,検索範囲,1,0)),”×”,”〇”) |
気をつけなくてはならないのは、1つ目の例とは“×”と“〇”の指定が逆になるということです。
ISERROR関数の結果が「TRUE」の場合は「ない」ということなので、真の場合の返り値に「×」を指定します。逆に「FALSE」の場合には「○」を表示させるようにします。
以上、エクセルでIF関数とISERROR関数とVLOOKUP関数を用いてリストに「あれば〇」「なければ×」を表示する方法を紹介しました。
エクセルにてリストにあるかどうかを確認し、「あれば抽出」「なければ×」とする方法【VLOOKUP関数とIFERROR関数】
続いては、○×等でデータの有無を表示させるだけではなく、データが「あれば抽出」「なければ×」とする方法についても解説します。
単にデータを抽出するだけであれば、VLOOKUP関数を使用します。
VLOOKUP関数の使い方は以下の通りです。
|
=VLOOKUP(検索値、範囲、列番号、［検索方法］) |
下記の表は日毎の売上額を表にした例です。
日付を「検索値」として、C列目の売上額をF列に反映させます。
F2セルに入力する数式はこのようになります。
|
=VLOOKUP(E2,B:C,2,0) |
調べたい日付と売上表の日付が一致する行の、2列目の値を返します。
すると、一致する日付がない1/7や1/8などはエラーになりました。
このエラーの際、つまり「リストにデータがない」時に「×」を表示するようにする方法を解説します。
使用するのはIFERROR関数です。
IFERROR関数は、条件式に一致するかしないかによって処理を変えるものです。
|
=IFERROR(値、エラーの場合の値) |
「値」には、上記のVLOOKUP関数の数式をそのまま入れ子にします。
「エラーの場合の値」（エラーが出た場合の表示）には、今回は「×」を表示させたいので、“×”と入力します。
ISERROR関数と似ていますが、ISERROR関数がエラーかどうかの結果を返すだけだったのに対し、こちらはエラーだった場合の処理まで指定できます。
また、IF関数とも似ていますが、IF関数では真の場合と偽の場合の結果を両方指定したのに対し、こちらは偽の場合のみしか指定しません。
これは、真の場合には条件式をそのまま適用する仕様になっているからです。こうすることで、IF関数では「条件式」と「真の場合」で同じ内容を記述しなければならないところを、省略できます。
条件式と真の場合の処理を別々に指定したい時はIF関数、処理が同じであればIFERROR関数、と使い分けます。
上記の表の場合、このような数式になります。
|
=IFERROR(VLOOKUP(E2,B:C,2,0),”×”) |
これを他のセルまでコピーすると、このようになりました。
左の表でデータがなかった日付に関しては、「×」が表示されています。
ただしこの表ですと、通貨の書式になっている売上額は文字が右寄せで×は左寄せという形になり、見栄えがよくありません。
この場合、エラーの際の表示を空白にするとスッキリしそうです。
|
=IFERROR(VLOOKUP(E2,B:C,2,0),””) |
このように、偽の場合の処理(エラーの場合の表示)を“”と指定します。
#N/Aエラー(値が見つからない場合のエラー)を防ぐためにも、VLOOKUP関数にはIFERROR関数をセットで使用するようにするとよいです。
以上、VLOOKUP関数とIFERROR関数を用いて、リストにデータが「あれば抽出」「なければ×」とする方法を紹介しました。
エクセルにてリストにあるものに色付けする方法【条件付き書式】
リストにあるものに色付けをしてわかりやすくする方法があります。
使用するのは「条件付き書式」です。
上記のリストの中から、「野菜」に分類される商品のセルに色付けしてみましょう。
まず、色付けしたいセルの範囲の中から一番上の列のセルを選択します。
今回の範囲は「C2:C6」です。その一番上は「C2」になります。
この状態で、「ホーム」タブの「条件付き書式」から「セルの協調表示ルール」→「その他のルール」を選択します。
「新しい書式ルール」が表示されます。
「数式を使用して、書式設定するセルを決定」を選択します。
数式を記入する欄に、以下の数式を入力します。
|
=B2=“野菜” |
この数式は「B2の値が”野菜”の場合の書式を設定する」という意味です。
B列の分類を元に色付けするため、B列のセルを指定します。
そして先程選択しておいたセルと同じ行番号になるように設定する必要があります。
C2に条件付き書式を設定するため、B2を指定しています。
「書式」の欄から書式の種類を選択します。バージョンによってはプリセットされたいくつかの書式パターンから選択できる場合もありますが、今回は自分で設定するやり方で説明します。
まずは右側の「書式」ボタンをクリックします。
「セルの書式設定」ダイアログが表示されます。
「フォント」タブから、テキストのスタイルや色が変更できます。
今回は太字を選択し、色も野菜のイメージに合わせて緑にしてみました。
合わせて、背景色も設定してみます。
「塗りつぶし」タブから色を選びます。
「OK」を押すと、「新しい書式ルール」に戻ります。
更に「OK」を押します。
しかしこの時点では、表の状態は何も変わらないはずです。先程「条件付き書式」で指定したB2セルは「野菜」ではないからです。
この条件付き書式を、「C2:C6」の他の行にも適用していきます。
C2セルが選択された状態で、先程同様に「ホーム」タブの「条件付き書式」から「ルールの管理」をクリックします。
先程作成した条件付き書式が表示されるはずです。
その中から「適用先」の欄を変更します。
現在は「=$C$2」となっているはずです。その右端にある矢印のアイコンをクリックします。
適用先を指定するモードになりました。
この状態でカーソルをドラッグして、適用したい範囲である「C2:C6」を選択します。
右端の矢印のアイコンを押すと、ルールの管理の画面に戻ります。
「OK」を押すと、条件付き書式が「C2:C6」に適用されました。
分類が野菜に該当する「にんじん」のみが緑になっています。
適用先の変更ではなく、「書式のみコピー」の機能を使用する方法もあります。
C2セルを選択した状態で、「ホーム」タブの左側にあるペンキの刷毛(はけ)のようなアイコンをクリックします。
そのまま書式設定を適用したい範囲(今回の場合はC2からC6)を選択します。
するとC2に設定した条件付き書式がC3からC6にも適用されます。
ただし書式のコピーを使用する方法だと、上記の「ルールの管理」の欄にコピー元とコピー先の2つ分のルールが存在することになります。
同様にコピペを繰り返すとルールがどんどん増えていくため、コピーではなく適用先を変更する方法をオススメします。
しかし条件付き書式を使用するリストがシート内に1つしかない場合などは、書式のコピペをした方が早いため、臨機応変に使い分けていきましょう。
以上、条件付き書式を用いてリストにあるものに色付けする方法を紹介しました。
まとめ
この記事では、エクセルで特定のデータがリストにあるかどうかを確認・チェック・検索し、「あれば〇」「なければ×」を表示する方法や、リストにあれば抽出する方法、リストにあるものに「色付け」する方法について解説しました。
いずれの方法にしても、大文字と小文字は区別されませんが、全角と半角は区別されるため、注意しましょう。
売上管理や商品管理をエクセルで行う際など幅広く活用できる方法なので、ぜひ覚えておきましょう。
コメント