文字列操作  実用編

重複データの判定（ Sheet：重複チェック(1) ）

Excelには表やグラフを利用するほかに、データベースとしての使い方があります。顧客住所録、商品一覧表、売上一覧を作成し、その情報を活用できるようにデータを蓄積していきます。日々更新されるデータなどは、表記ゆれやデータの重複入力があると、データベースとして正しく利用できなくなります。

関数で重複データを回避するには、IF関数とCOUNTIF関数を組み合わせる方法があります。
セル【C3】には、次の数式が入力されていて、セル【C16】までコピーされています。

=IF(COUNTIF(A:A,A3)>1,"重複あり","")

A列に同じ名前の会社名が２つ以上あれば、「重複チェック」のD列に「重複あり」と表示される関数の組み合わせです。
ただし、会社名データに“表記ゆれ”があると、正しく判定できません。会社名の中に空白を入れていたり、カタカナや英字が半角、全角とまちまちであったりすると、同じ会社であっても異なる会社として判定してしまいます。

【表記ゆれの例】

  ・株式会社ナカムラ　→　株式会社　ナカムラ
  ・RAXTデザイン　→　ＲＡＸＴﾃﾞｻﾞｲﾝ

文字列操作で表記ゆれに対応する（ Sheet：重複チェック(2) ）

重複を調べる前に、文字列操作の関数を使って、同じ入力条件に合わせましょう。
まず、ＪＩＳ関数で半角の文字列を全角にします。そして、SUBSUTITUTE関数でスペースがあれば、スペースを削除します。これで、表記ゆれを解消できます。

セル【C3】に次の数式を入力し、数式をセル【C16】までコピーしましょう。

=SUBSTITUTE(JIS(A3),"　","")

会社名の文字列をすべて全角にし、スペースがあればそれを削除する数式にしています。
セル【D3】には、重複チェックする数式を入力しましょう。

=IF(COUNTIF(C:C,C3)>1,"重複あり","")

このように、入力する人によって入力の仕方が異なる場合、文字列の統一をすることで、重複をチェックできます。

2つの表から自動検索（Sheet:請求書）

VLOOKUP関数は、入力したデータから、該当する一覧表を参照して自動的に内容を取り出しますが、参照先の表が2つに分かれている場合は利用できません。
Sheet商品一覧のように分類ごとに商品台帳が分かれているような場合、複数の商品台帳を自動で切り替えて商品番号を検索し、該当する商品情報を取り出すことができます。
この場合、VLOOKUP関数とINDIRECT関数の組み合わせを使います。
さらに、セル範囲に「名前」を付けるテクニックも必要となります。

◆ 文字列として指定された参照先に切り替える（INDIRECT）

まず、参照先としたい表に名前を付けます。商品一覧シートのセル範囲【A3】：【C8】までを選択し、名前ボックスに「PC」と名前を付けます。

同様に、セル範囲【E3】：【G8】までを選択し、名前ボックスに「付属」と名前をつけましょう。

請求書シートのセル【C4】に次の数式を入力しましょう。

=VLOOKUP(B4,INDIRECT(A4),2,0)

また、セル【E4】には、次の数式を入力します。

=VLOOKUP(B4,INDIRECT(A4),3,0)

それぞれの数式を製品NOが記述されているセルまでコピーします。

INDIRECT関数は、セルの文字を範囲名に変換する関数なので、VLOOKUP関数と組み合わせることで、参照先の表を切り替えられます。複数の表から参照する場合は、とても便利な組み合わせです。

部分検索で複数候補を表示（Sheet：検索）

実務ではExcelを使って顧客管理や売上管理などを一覧表にし、データベース機能を活用することも多くあります。大量のデータから必要なデータだけを取り出して（抽出）、そのデータを加工、編集、更新して業務に活用します。
キーワードとして適当な検索語を入力するだけで、該当する情報を表示させる処理を関数を使って実行してみましょう。
一覧表の余白を『作業セル』として使い、検索語に一致する値があるかどうかを一行ずつ調べます。

「顧客名簿」シートの一覧表から一部の検索語を入力して、「検索」シートの結果欄に表示させてみます。
まず、「顧客名簿」シートの一覧表の右側を『作業セル』とします。COUNTIF関数の引数「範囲」に名簿一件分のデータを、「検索条件」に検索語を指定すれば、名前、電話、住所のいずれかのセルに一致する値があるときに「1」と表示できます。
次の数式を「顧客名簿」シートのセル【D3】に入力して、セル【D51】までコピーしましょう。

=COUNTIF(A3:C3,"＊"&検索!$D$5&"＊")

検索」シートの検索結果に、該当する複数の候補を一覧表示させます。部分一致で探すので、検索語の前後に半角の「＊」記号を結合して条件に指定します。
これで該当データに「1」と印を付けることができます。

（※「3」と表示されるのは、検索語が空白であるからです。）

「検索」シートの検索結果に該当データを上から順番に抽出して並べて表示するには、ROW関数とSMALL関数を使います。
「顧客名簿」シートのE列を『作業セル』として使い、該当データの行番号を表示します。

次の数式をセル【E3】に入力し、セル【E51】までコピーしましょう。

=IF(D3>=1,ROW(),"")

（※該当セルに「1」と表示されている、行番号のみ表示されます。）

◆ 指定したセルの「行番号」を調べる（ROW）

次にこの中から一番小さい行番号を取り出して「検索」シートの該当行に表示させます。SMALL関数を利用します。

◆ データの中で、指定した範囲の順位の番目に小さい値を返す（SMALL）

次の数式を「検索」シートのセル【F9】に入力し、セル【F11】までコピーしましょう。

=SMALL(顧客名簿!E:E,A9)

一番目の候補の行番号が取り出せたら、INDEX関数でその行のデータを取り出します。
次の数式をセル【B9】に入力し、セル【B11】までコピーしましょう。

=IF($D$5="","",INDEX(顧客名簿!A:A,F9))

同様に、セル【C9】【D9】に次の数式を入力して下のセルまでコピーしましょう。

=IF($D$5="","",INDEX(顧客名簿!C:C,F9))

=IF($D$5="","",INDEX(顧客名簿!D:D,F9))

検索語を入力してみましょう。
セル【D5】に「中田」と入力してみましょう。

最大3件までとしていますが、検索件数を増やすのであれば、各数式を下のセルにコピーすれば該当データを多く表示できます。ただし、該当データが少ない場合はエラー表示されます。

◆ エラー表示の回避

エラー表示を回避するために、条件付き書式を利用します。
結果表示されるセル内にエラーが表示されると文字色を「白」になるように設定しましょう。
セル範囲【B8】：【D11】まで選択し、[書式]メニューから「条件付き書式」をクリックします。
条件1で「数式が」を選択し、右の欄に「=ISERROR（B9）」と入力し、書式からフォントの色を「白」を選び、OKボタンをクリックします。

これで、エラー表示は文字色が白になって、見えなくなります。
エラー表示の回避は、関数でも実現できますが、「条件付き書式の設定」で行うと簡単に設定できます。