メインコンテンツへスキップ
見出し画像

【Excel】地味だけど絶対にあなどってはいけない!!「データベース」の概念とテーブル化★

    こんにちは、HARUです!

    今回はExcel上にデータベースを作る方法をご紹介します。

    Excelに集めるデータは、情報の検索や加工が簡単に行える形式で蓄積しておくことが理想です。

    画像

    ある特定の条件に当てはまる複数のデータを集約して、検索や加工といった管理がしやすいように整理されたデータの集合体を、「データベース(Database)」と呼びます。

    少し難しそうな印象を受けますが、情報の検索や編集が容易にできる形であれば、住所録や●●ランキングのような表も立派なデータベースです。

    Excelは行と列により実現する特有のマトリックス構造から、縦横の軸で情報を並べることができ、データベースの概念にマッチしています。

    さらに、拠点別の販売実績や顧客の属性情報といったデータが正しく整理されていると、それらが持っている値を計算したり抽出したりするシーンでも大いに役立ちます。

    そこでこの記事では、データベースを構築する際に気を付けるべきことと、情報の蓄積・整形作業に絶大な効力を発揮してくれる「テーブル化」について解説します。

    Excelで表計算やデータ管理を行う際になによりも先におさえておきたいテーマです。
    ぜひご一読ください!


    データベース化の要件


    単刀直入に結論から言ってしまうと、Excelに集約した情報をデータベースとして活用するためには、以下7つの点に気を付けます。

    画像

    ①空白行/空白列に囲まれて独立している

    データベースの対象とする範囲は該当のワークシートに独立して存在していることが理想です。無関係のデータが対象範囲に接触していると、後に設定するテーブルの領域にも含まれてしまいます。
    もし近傍に日付やメモデータを残す必要がある場合は、隣接させずに離れた箇所に入力しましょう。

    ②見出しに重複がない

    検索関数などで特定のデータを抽出する際、見出しをkeyにしてアプローチすることがあります。同じ見出しが複数存在すると、求めている情報を正しく取り出せない可能性が生じるのです。
    たとえば同じ「氏名」でも、"氏名①"/"氏名②"/"氏名③"だったり"氏名"/"カナ氏名"/"英字氏名"などと、それぞれがユニークな文字列になるように設定しましょう。

    ③1行に1件の情報

    たとえば上図のサンプルの場合、A株式会社の一部の情報が離れた箇所に入力されていたり、B株式会社の行にC株式会社の情報が混在したりしていると、上記②と同じく正しい情報を取り出せなくなります。
    ②で設定した見出しに該当する要素を各列ごとに入力し、1件の情報を1行に集結・集約させるようにしましょう。

    ④1つのセルに1つのデータ

    たとえば上図のサンプルの場合、一部の取引先名のセルに関係会社の情報が併記されていたり、所在地に続けて電話番号が入力されていたり、売上金額の欄に文字列が含まれていたりすると、見出しに合致しない情報が入り混じることになります。
    他のデータとの統一性が担保されなくなり、文字列操作関数などを使っても同じ条件でデータを取り出せない、値を正しく演算または参照できないといった問題が発生する可能性があります。
    こんなときは補足情報を入力する備考欄の列を別に用意したり、所在地に加えて新たに電話番号の列を追加したりすることで、1つのセルには1つのデータのみを配置するようにしましょう。

    ⑤結合セルを含まない

    セルが結合されていると、数式によるデータの参照やフィルターによる絞り込みの他、範囲選択やコピー&ペーストといった基本操作も正しく機能しなくなります。
    データベース化する範囲には結合されたセルを含まないようにしましょう。

    ⑥改行を含まない

    値や文字列をセル内で改行すると、目には見えない「改行コード」がデータに加わります。こちらも検索や参照、文字列操作による整形作業に支障を来す可能性がありますので、セルに入力したデータは改行しないようにしましょう。

    ⑦空白行/空白列を含まない

    対象範囲をまとめて選択したり、テーブル化したりする際、空白列や空白行が存在していると、アクティブセル領域がその手前までと認識され正しく操作できなくなります。
    一括範囲選択や数式の参照元指定を効率的に行うために、対象範囲のデータは途切れることなく連続的に入力されていることが理想です。
    ※「アクティブセル領域」:選択中のセルまたはセル範囲から空白行と 空白列の手前までの領域のことだ。



    テーブル化


    これらデータベース形式の留意事項をおさえた上で、表の整形作業に欠かせない「テーブル」機能について解説します。

    テーブルを設定しておくと、見やすいしま模様のデザインに変換されてフィルターが適用されるだけでなく、新しいデータが追加されても自動で数式や書式が複製される構造になります。

    画像


    テーブルの設定

    表の中のいずれかのセルがアクティブな状態で、[Ctrl]+[T]を押します。
    テーブルに変換するデータ範囲が正しく指定されていることと、「先頭行をテーブルの見出しとして使用する」にチェックが入っていることを確認したら、[Enter]で実行します。

    画像

    これによって、表にテーブルが適用されます。

    画像


    テーブルデザインの変更

    テーブルが設定された表にあるいずれかのセルがアクティブな状態で[Alt]→[J]→[T]→[S]と順に押し、方向キーでお好みのデザインを選択して、[Enter]で決定します。

    画像


    テーブルの解除(範囲に変換)

    テーブルが設定された表にあるいずれかのセルがアクティブな状態で[Alt]→[J]→[T]→[G]と順に押します。
    テーブルを標準の範囲に変換するかを確認するポップアップが出てきたら、[Enter]で決定します。

    画像

    これによってテーブルが解除されます。
    ※書式は残ったままとなりますが、フィルターがなくなっていればテーブルが適用されていない目印です。

    画像


    テーブル化のメリット


    表にテーブルを設定することで、次のようなメリットがあります。

    数式や書式が自動で適用される

    テーブルを設定していない表に、新たに"I株式会社"の情報を追記するとします。

    画像

    入力した数値から売上単価や利益率を求める数式、値の表示形式や罫線といった書式を、1から設定し直す必要があります。

    画像

    これに対してテーブルを設定していれば、数式や書式が自動で反映されます。

    画像

    必要なデータを入力するだけで、作業が素早く完了します。

    画像



    数式の参照元が自動拡張される

    欄外で販売実績の合計を求めたとします。

    画像

    テーブルを設定していないと、"I株式会社"の情報を新たに追加したときに販売実績は計算対象範囲から外れます。

    画像

    テーブルを設定していれば、テーブル内のデータが数式の参照元となっている場合に参照範囲が自動拡張されます。

    画像


    集計方法をアレンジできる

    テーブル範囲にあるセルをアクティブして、「テーブルデザイン」タブ→「テーブルスタイルのオプション」グループにある「集計行」にチェックすると、最下段に「集計行」が追加されます。

    画像

    ※この操作はテーブル内にアクティブセルがある状態で[Ctrl]+[Shift]+[T]を同時に押すことでも実行できます。

    画像

    集計行では、合計だけでなくデータの平均や個数、最大値といった分析が行えます。
    ※"その他の関数"を選択すると、「関数の挿入」ダイアログボックスが表示されます。

    画像

    社内の基幹システムから入手したCSVファイルなどは、ここまで解説してきたデータベースファーストの構造になっていることがほとんどです。

    テーブル化し、集計行を追加することで、簡易的な分析作業が可能になります。非常に便利ですよね!





    いかがでしたか?

    今回は、Excelの表をデータベース化する際に留意すべきことと、情報の蓄積・整形作業に絶大な効力を発揮してくれるテーブル機能について解説しました。

    地味ではありますが、表計算やデータ管理に欠かせない最重要テーマですので、積極的に実践していきましょう!



    ↓↓記事の内容を動画で解説しています↓↓
    ※本記事の委細が動画収録当時のバージョン・解説内容と異なる場合があります。

    ↓↓Excel操作をとにかく高速化したい方へ↓↓

     
     
    元東証上場企業の事業企画職|サラリーマン稼業の傍ら、20万人以上登録のITスキルメディアを個人運営|書籍「Excel超時短メソッド」発売即重版→ http://amzn.to/38Nq5Qx|可愛い2児のパパ🍼|Noteでは有益スキル情報を文字と図解で徹底解説📝

    あなたへのおすすめ