Practical Excel
応用:簡易検索

簡易検索

 ここでは関数を応用し簡単な「検索」を行い結果を表示します。「生徒を名前で検索する」「住所を指定し特定地域の生徒をピックアップする」などの応用が考えられます。この簡易検索は私のお気に入りの一つです。

 ここでは仕組みを知る事に重点を置くため、最もシンプルな形で作成していきます。
 ここで紹介する条件検索のサンプルファイルはダウンロード出来ます。


簡易検索の設計

 まずは、どのような形にするのかを考えます。それでは、ここで作るモノを簡単に説明をします。

◆概要
 ・氏名や住所といった「項目」をドロップダウンリストより選択し、セルに任意の文字列を入力すると、
  選択した「項目」の中で、その文字列が含まれるリストが表示される
 ・データ入力欄(氏名・性別・住所)は40人分作成
 ・表示リスト枠はデータ入力欄と同じ数(40人分)作成

◆使用シート
 「データ」シート・・・氏名・性別・住所を入力するシート。欄は40作成
 「検索」シート・・・検索用のシート。表示リスト枠は40作成。「項目」選択セルと「検索用セル」を作成

 データの誤入力を防ぐため「入力」シートと「検索」シートとは別に作成することにします。


「データ」シートの作成

 まず「データ」シートを作成し、そこに「氏名・性別・住所」欄を作っていきます。
 データ数把握のための「通し番号(No.)」列と、項目全てを対象とする「すべて」列、「作業列※」を付け加えます。

 ※作業列は検索に使用する番号の表示に使用します。

「通し番号」・・・A列。検索で使用
「氏名」・・・氏名を入力する欄
「性別」・・・性別を入力する欄
「住所」・・・住所を入力する欄
「すべて」・・・「氏名・性別・住所」を繋げた「すべて」の項目作成のための作業列
「作業列」・・・条件に合わせ番号を表示するための検索用作業列



 では次に、セル範囲に名前を定義します。

「項目」欄(セル範囲[$B$1:$E$1]):[Area_Name]
「氏名・性別・住所」欄(セル範囲[$B$2:$D$41])・・・[Data_Area]
「作業列」欄(セル範囲[$F$2:$F$41])・・・[Num_Data]



「検索」シートの作成

 次に「検索」シートを作成します。最初は「表示枠」を作ります。ここでは通し番号(No.)と、氏名、性別、住所の3つを表示できるように罫線で区切ってあります。
 そして、「項目」を選択するセル(薄黄部)と、検索用入力セル(薄緑部)を準備します。

 文字を枠内に収めるため、使用セル範囲の書式は「縮小して全体を表示する※」に設定します。

 ※セル範囲を「右クリック」→「セルの書式設定」→「配置」→「縮小して全体を表示する」にチェック



 では次に、セルに名前を定義します。

項目ナンバー表示セル(セル[B3]):[N_Data]
「項目」選択セル(セル[C3]):[Select]
検索用入力セル(セル[E3]):[T_Data]




 それでは、次に「項目」の選択をリストで行うため、セルに入力規則を設定します。

 「データ」→「入力規則」→「入力値の種類」にリストを指定→「元の値」に=Area_Nameを指定。



数式の挿入

 それでは数式を挿入していきます。座席表や委員会名簿と比べるとやや複雑になります。

◆「データ」シート



 ◇「すべて」列の役割

    「すべて」は、その行の「氏名・性別・住所」全ての文字列を検索対象とするための項目です。
   CONCATENATE関数で、「氏名・性別・住所」を結合し一つのセルにまとめます。

  【セル[E2]に挿入する数式】
   =CONCATENATE(B2,C2,D2)

 ◇「作業列」の役割

    「作業列」は、その行の該当項目に、検索値があるかを確認し、結果を表示する列です。
    1.セル[T_Data](検索用セル)に値が入力されたかを確認
    2.値が入力されたなら、その値が、その行の指定された「項目」内にあるかを確認
    3.確認できた場合、その行の「通番(No.)」を表示(確認できなければ「空白」)

  【セル[F2]に挿入する数式】
   =IF(T_Data="","",IF(ISERROR(FIND(T_Data,INDEX(B2:E2,1,N_Data))),"",A2))

◆「入力」シート



 ◇セル[N_Data]の役割

    セル[N_Data]は表示項目の番号を表示します。
    その番号は「データ」シートの作業列の、INDEX関数で使用されます。
    1.セル[Select](項目選択セル[C2])の値を調べる
    2.空欄だったら「4※」を表示し、空欄でない場合は、セル範囲[Area_Name]の中で
      その値の位置が何番目かを表示する(位置はMATCH関数で調べる)

    ※セル[Select]が空欄の場合は「すべて」の項目を選択した時と同じにするため。

  【セル[N_Data]に挿入する数式】
   =IF(Select="",4,MATCH(Select,Area_Name,0))

 ◇セル[D2]の役割

    セル[D2]は、セル[Select]が空欄でない場合に「に」を表示する。

  【セル[D2]に挿入する数式】
   =IF($C$2="","","に")

 ◇リスト表示部

   リスト表示部(セル[C5:E44])に挿入する数式の意味の説明です。
    1.「データ」シートの「作業列」セル範囲[Num_Data]中の、△番目に小さい値を調べる
    2.調べた値を基に、「データ」シートのセル範囲[Data_Area]から任意の値を表示

   ※SMALL関数がエラーの場合は空欄になります。

  【セル[C5]に数式を挿入の場合】
   =IF(ISERROR(SMALL(Num_Data,$B5)),"",INDEX(Data_Area,SMALL(Num_Data,$B5),1))

    ※「性別」「住所」に挿入する数式は、上記数式のINDEX関数の列指定値を変更します。
    数式の一番右の数字です。性別は2列目なので「2」、住所は3列目なので「3」を指定します。


簡易検索

 ここまで作業を進めたら完成になります。必要な場合はシートの保護をして下さい。

◆シート保護

  シート保護時でも値の変更を可能にするセル(ここでは入力欄)を選択し、 「右クリック」→「セルの書式設定」→「保護」→「ロック」のチェックボックスをOFFにします。

 その後、「ツール」→「保護」→「シートの保護」→(保護にパスワードをかけるなら「シートの保護を解除するためのパスワード」を入力)→「OK」

◆完成


サンプルファイル

 サンプルファイルを準備しました。先にも記述しました通り「仕組みの理解」に重点を置いているため実用的な作りにはなっていません。自分なりに改変しご使用下さい。尚、ファイルによる問題が生じないよう気をつけておりますが、万が一、本ファイルの使用によって損害等が生じたとしても、責任は取りませんので、悪しからずご了承下さい。

サンプルファイル「簡易検索」・・・[sample_kensaku.xls] 44kb

Excel Tips for Teachers

Copyright (C) 2009 坂江 保 All Rights Reserved.