前回の記事でプルダウンでリストに無い項目を入力できる方法をご紹介しました。
しかしVLOOKUP関数やINDEX/MATCH関数を使って集計しようとすると完全一致していないとうまく集計ができません。
そのような場合に後でどの項目が手入力した項目なのか、そのセルだけ色を変えて一目でわかるようにすることができますので、今回はその方法をご紹介いたします。
プルダウンに関してはこちらも参照してみてください。
条件付き書式を設定する
まず今回「条件付き書式設定」をしようとしているセルの一つを選択します。
次に「ホーム」タブの「条件付き書式」をクリックします。
表示されたメニューの「新しいルール」をクリックします。
ルールの種類の中で「数式を使用して、書式設定するセルを決定」をクリックします。
下に表示されたスペースに下記の関数を入力します。
=ISNA(MATCH(今回選択したセル番地, ドロップダウンの参照元範囲, 0))
今回の例では下記の通りです。
=ISNA(MATCH(D3, マスタ!$C$4:$C$10, 0))関数の入力ができたら、「書式」をクリックします。
表示されたメニューの「塗りつぶし」タブからお好きな色を選択して「OK」ボタンをクリックします。
もう一度「OK」ボタンをクリックします。
これで最初に選択していた「D3」セルの書式設定が完了しました。
書式設定をコピーする
次に残りのセルに関数をコピーします。
「D3」セルを選択した状態で[Ctrl]ボタン+[C]ボタンを押してコピーし、「D4」セルから「D8」セルを選択した状態で右クリックします。
表示されたメニューの「貼り付けのオプション」から「書式設定」のボタンをクリックします。
これで残りのセルにも書式設定がコピーされました。
空欄はMATCH関数で引っ張ってこれないようですので、ご覧のようにすべて黄色になってしまいます。
この表示が気になるようであれば、「空欄」の場合は「白」にする条件を追加する必要があります。
空白の場合は色を付けない書式設定
もう一度「条件付き書式設定」をしようとしているセルの一つを選択します。
次に「ホーム」タブの「条件付き書式」をクリックします。
表示されたメニューの「新しいルール」をクリックします。
ルールの種類の中で今度は「指定の値を含むセルだけを書式設定」をクリックし、下の項目で「セルの値」を「空白」に切り替えます。
空白に切り替えたら「書式」ボタンをクリックします。
書式設定のメニューが出ると、初めから「色なし」が選択された状態になっているかと思いますが、それでももう一度「色なし」をクリックし、「OK」ボタンをクリックします。
あとはもう一度先ほどと同様に書式設定をしたセルを残りのセルに「書式」をコピーします。
以上で書式設定は完了です。下図を見てわかる通り、リストにある項目と空白の項目は「白」になっており、リストに無い「その他」を入力したセルのみ「黄色」に塗りつぶされました。
まとめ
前述しましたようにVLOOKUP関数やINDEX/MATCH関数を使って集計しようとすると完全一致していないとうまく集計ができません。そのため必ずプルダウンから入力値を選択してもらいたいのですが、それでは入力時に選択肢がないと困るケースが出てきます。
そんな時はリストに無い値も入力できるように設定しておいて今回のように網掛けさせておいて後から修正をする方がスムーズかもしれません。仕事が早くなる方法をご選択ください。
プルダウンの設定方法はこちらの記事を参照してください。
リストに無い項目をプルダウンで入力できるようにするにはこちらの記事を参照してください。
コメント