データベース
Excelでは、データを並び替えたり、条件に合うデータのみを表示したりといった、データベースとしての利用が可能です。
今回は、以下のような表を作りながら解説します。
用語
-
リスト
ワークシート上の1つの表データの範囲をリストと言います。
リストは空白の列や行で、他のデータから独立させておく必要があります。 -
レコード
1件のひとまとまりのデータ（リストの行）をレコードと言います。 -
フィールド
列ごとに入力される同種類のデータ（リストの列）のことです。 -
列見出し（フィールド名）
各フィールドの一番上に入力される項目名です。 -
キー
並び替え等をする場合に基準になるデータのことです。 -
昇順と降順
キー項目の値が小さい順にレコードを配置するのを昇順といい、その逆は降順と言います。
連続データの入力・検索と置換
下表を見ると、「番号」、「氏名」、「担当曜日」のフィールドはデータに規則性があることがわかります。
Excelには連続データの入力や置換といった便利な機能があり、下表のようにすべてのデータを入力せず、未入力のセルには後でExcelの機能を利用してデータをセットする方法を解説します。
連続データの入力
「番号」フィールドは、数値が10ずつ増えています。
フィルハンドルを下にドラッグすると、データのコピーとなりますが、あらかじめ数値を含む、隣り合うデータを2つ選択しておき、フィルハンドルをドラッグすると、その数値の差分を追加する形でデータがコピーされていきます。
最後の行までドラッグすれば、No.10～No.200まで入力できます。
しかし、コピー元のセルの下側には罫線がないので、セルA22の下の太罫線も消えてしまいます。
そこで、スマートタグを利用し、［書式なしコピー］を選択しておきます。
「担当曜日」は数値ではありませんが、Mon,Tue,Wed,・・・と規則的に変化するデータです。
このような場合、Monだけ入力しておき、フィルハンドルをドラッグすると、自動的に連続データが入力されます。
曜日以外にも、Jan,Feb,Mar,・・・や睦月、如月、弥生、・・・等が連続データとして扱われます。
どのようなデータが連続データとなっているかは、［ファイル］-［オプション］-［詳細設定］-［ユーザー設定リストの編集］より確認することができます。
検索と置換
「氏名」のフィールドは「東一郎」～「東五郎」の5名を範囲選択し、フィルハンドルで下にコピーしておきます。
B8～B12の「東」を「西」に変更します。
B8：B12を範囲選択し、［ホーム］タブの［編集］グループより［検索と選択］-［置換］をクリックします。
すると、［検索と置換］画面が表示されます。
［検索する文字列］に「東」を、［置換後の文字列］に「西」を入力し、［すべて置換］ボタンをクリックします。
同様に次の5名を「南」、最後の5名を「北」に変更します。
文字列を検索する場合は、［検索］タブを利用します。
なお、あらかじめ範囲を選択していないと、すべての範囲が検索や置換の対象範囲になりますので、注意しましょう。
並べ替え
キーが1つの並べ替え
担当曜日について昇順に並べ替えます。
まず、アクティブセルをC3にします。
なお、リスト内のC列であればどこでも大丈夫です。
［データ］タブの［並び替えとフィルター］グループより［昇順］ボタンをクリックします。
［ホーム］タブの［編集］グループより［昇順］ボタンをクリックしても同様です。
リストのデータが担当曜日の昇順に並び替わります。
降順に並べ替えるには［降順］ボタンをクリックします。
［降順］ボタンは［昇順］ボタンの下にあります。
キーが2つ以上の並べ替え
担当曜日が同じ場合、体重が重い順にレコードを並べ替えます。
並び替える項目が複数になる場合は、並べ替えダイアログボックスを使用します。
あらかじめ、並べ替えをする表内のセルを選択しておきます。
［データ］タブの［並び替えとフィルター］グループより［並べ替え］ボタンをクリックします。
［ホーム］タブの［編集］グループより［並び替えとフィルター］-［ユーザー設定の並び替え］をクリックしても同様です。
並び替えダイアログボックスが表示されます。
指定したリストの先頭行を並び替えの対象とするかしないかを、［先頭行をデータの見出しとして使用する］で指定します。
今回のデータは一番上の行は見出しですので、チェックを入れておきます。
［最優先されるキー］で「担当曜日」、［セルの値］、［昇順］を選択します。
担当曜日が同じだった場合に、次の並べ替えのキーを設定する為、［レベルの追加］をクリックし、［次に優先されるキー］で「体重(kg)」、「セルの値」、「小さい順」を選択します。
［OK］をクリックします。
上記の並び替えの結果です。
担当曜日はFri→Mon→Sat→・・・の順に並びます。
データが数値でなく文字の場合、並び替えの順番は文字コードの順になります。
ただし、［オプション］で［ふりがなを使う］にチェックが入っていると、漢字のふりがなのコード順となるので注意しましょう。
抽出
オートフィルター
条件に合うレコードのみを表示する作業を抽出と言います。
Excelには「オートフィルター」という、ワークシートに作成されたリストからデータを抽出する機能があります。
まずは、オートフィルターを使い、担当曜日がFriのレコードを抽出してみましょう。
フィルタの設定
あらかじめ、リスト内のセルを選択しておき、［データ］タブの［並び替えとフィルター］グループより［フィルター］ボタンをクリックします。
［ホーム］タブの［編集］グループより［並び替えとフィルター］-［フィルター］をクリックしても同様です。
領域A2：E2の各セル（フィールド名）の右側に▼がつきます。
セルC2［担当曜日］の▼をクリックします。
最初、［テキストフィルター］の（すべて選択）にチェックが入っています。
これは、データがすべて表示されていることを意味しています。
［テキストフィルター］で「Fri」のみチェックを入れます。
最初に（すべて選択）のチェックを外し、その後、「Fri」にチェックを入れましょう。
［OK］をクリックします。
担当曜日がFriのレコードのみが抽出され、ステータスバーには抽出されたレコード数が表示されます。
元に戻すには、再度、（すべて選択）にチェックを入れるか、［”担当曜日”からフィルターをクリア］を選択します。
抽出条件の指定
オートフィルターオプションを利用すると、より複雑な抽出条件を指定することが可能です。
例1：氏名が「東」で始まるレコードの抽出
セルB2［氏名］の▼をクリックし、［テキストフィルター］-［ユーザー設定フィルター］を選択します。
［カスタムオートフィルター］のダイアログボックスに、以下のように入力します。
今回の場合、左右どちらの方法でも構いません。
右側はワイルドカードを利用して条件を指定した例です。
ワイルドカードとは、抽出したい文字を検索するときに使う文字列のことを言います。
ワイルドカードは、抽出したい文字の前か後、もしくは前後両方に付けることで、対象の文字列を含んだ文字が検索対象になります。
?は任意の1文字、*は任意の文字列を意味します。
通常は左側のように指定します。
ただし、「2文字目に三を含む」というような条件の場合、ワイルドカードを利用して「?三*」のように設定しないと指定できません。
例2：体重が60kg台のレコードの抽出
この場合、「体重が60以上」かつ「体重が70未満」なので、「体重 ≧ 60」and「体重＜ 70」と考えます。
両方の条件を満たす意味で、ANDを指定します。
フィルターオプションの使用
フィルターオプションを使用することにより、より複雑な抽出をすることが可能です。
例：（担当曜日が「Sat」 で 体重が60未満）または（担当曜日が「Mon」 で 身長が160台）のレコードを25行目以降に抽出する
まずは、抽出条件を別のセルに記載します。
1行目をフィールド名とし、2行目以降に指定する条件を記述していきます。
抽出条件範囲のフィールド名はリストのフィールド名と全く同じである必要があります。
間違いを避ける為、直接入力せずに、リストよりコピーしましょう。
検索条件範囲で、1つの行に記載した内容はAND、別の行はORで結合されます。
上記の例では、
（担当曜日が「Sat」 で 体重<60）または（担当曜日が「Mon」 で 160≦身長＜170）
となります。
なお、条件にはワイルドカードをし指定することもできます。
別の場所に抽出する場合は、抽出したい項目のフィールド名を記載します。
抽出条件と同様、間違いを避ける為、直接入力せずに、リストよりコピーしましょう。
下記の例では、抽出結果が25行目以降に抽出されます。
［データ］タブの［並び替えとフィルター］グループより［詳細設定］ボタンをクリックします。
［リスト範囲］、［検索条件範囲］、［抽出範囲］の各欄をクリックし、上記範囲を指定します。
今回の場合は、
- リスト範囲：$A$2:$E$22
- 検索条件範囲：$G$2:$J$4
- 抽出範囲：$A$24:$E$24
です。
［OK］をクリックすると、抽出範囲の下に、抽出結果が表示されます。
