更新日2015/09/08 この記事は約 7 分で読めます。

Excelで詳細抽出・集計・データ取り込み!エクセルデータベース計画

前回は「並べ替え」「フィルター」「テーブル」といった機能について、基本的な部分を解説してまいりました。

今回はこれらの機能を使った、より高度な「Excel・データベース」の分析・運用について解説していきたいと思います。

詳細フィルター01

「フィルター」、「テーブル」で使ったデータを引き続き使用していきます。

フィルター~詳細条件を指定して抽出する~

特定の取引先のみ、指定した納品日のもののみ、など指定した条件にあったものだけを抽出して表示する「フィルター」の基本機能について前回解説いたしました。

「フィルター」には詳細な条件を指定することで、より広範囲にデータの精査を行うオプションが用意されています。

例えば、ある一定範囲内の売上金額をあげられた製品がどれくらいあるか調べます。

ここでは55,000円から100,000円までの間で売り上げられた製品をあげてみます。

詳細フィルター02

まず、「金額」の プルダウンボタン をクリックし、現れた「数値フィルター」の ▶ をクリックします。

詳細フィルター03

条件が羅列されますので、「指定の範囲内」を選びます。

詳細フィルター04

「オートフィルターオプション」が表示されます。

詳細フィルター05

空欄に「55,000」「100,000」をそれぞれ直接入力するか

詳細フィルター06

データとして収納されている「金額」がプルダウンで表示されるので、そこから範囲を選ぶか、します。

入力もしくは選択が終わったら「OK」ボタンをクリックします。

詳細フィルター07

55,000円から1,000,000円の範囲内の金額を持つレコードを抽出・表示します。

採算ラインをあらかじめ設定しておけば、今売れている製品はどのようなものかが一目瞭然でわかるようになります。

また、売上の中の「上位5件」や「上位5%以内」といった、ランク付け機能もあります。

詳細フィルター08

「数値フィルター」を選んだあと、先ほどの「指定の範囲内」のすぐ下「トップテン」を選びます。

詳細フィルター09

「トップテンオートフィルター」のダイアログボックスが表示されます。

「トップテン」となっていますが、別に10件というわけではなく500件まで選択可能です。

上位(もしくは下位)●●件か上位(もしくは下位)●●パーセントかを選び、「OK」ボタンをクリックします。

詳細フィルター10

売上金額上位5件を選んでみました。

同じ要領で売上金額5%以内を選ぶこともできます。

詳細フィルター11

「金額」はよく使われる条件ですが、「日付」も負けず劣らず頻出の条件です。

受注日が2月16日から3月15日までの製品を抽出してみましょう。

「受注日」の プルダウンボタン をクリックし、「日付フィルター」を選びます。

詳細フィルター11.50

「オートフィルターオプション」が表示されます。

空欄に日付を入れて、その範囲を指定しますが、

詳細フィルター12

プルダウンで「受注日」の日付を表示させて選択するか

詳細フィルター13

比較的直近の日付なら、 カレンダーマーク をクリックすると「カレンダー」が表示されますので、該当する日付をクリック。

詳細フィルター13.5

もしくは直接日付を入力するか、して「OK」をクリックします。

詳細フィルター14

2月16日から3月15日までに受注した11件が表示されました。

「金額」や「日付」といった数字のほか、「テキスト」による抽出も可能です。

数ある仕事のうちで「キャンペーン」絡みのものがどれだけあったか?なんていう時に便利です。

詳細フィルター15

製品内容のなかに「キャンペーン」を冠したものがどれだけあるか、で調べます。

「製品内容」の プルダウンボタン をクリック、「テキストフィルター」から「指定の値を含む」を選びます。

詳細フィルター16

「オートフィルターオプション」が表示されますので、「製品内容」の欄に「キャンペーン」と入力し、「OK」をクリックします。

詳細フィルター17

「キャンペーン」関連の製品4件が表示されました。

今回は直接入力して抽出しましたが、これ以外にもプルダウンで選択して行うこともできます。

詳細フィルター18

「製品内容」の列に入力されているものから選ぶので、この機能を使って「二重入力」などのミスを見つけることもできます。

このように、「フィルター」による抽出機能が分析や作業の精査などに役立つことがおわかりいただけたかと思います。

 

「集計」をしてみる

データを分析するときに欠かせない要素の一つに「集計」があります。

Excelの場合、特に「テーブル」化しておくと非常に集計がやりやすくなります。

これでもいいのですが、もう少しわかりやすくするために多少手を加えておきます。

「納品日」の列を切り取って「金額」の列が最後尾になるように入れ替えます。

集計01

右クリックで切り取り

集計02

「金額」の列を選択し、「切り取ったセルの挿入」をクリック

集計03

「金額」の列を最後尾にします。

この内容で、売上金額を集計してみます。

集計04

「テーブル」内の任意のセルをクリックします。

すると、「デザイン」タブ及びその上に「デザインツール」と表示されます。

「デザインツール」をクリックします。

集計05

「デザイン」タブの内容が表示されますので、「データスタイルのオプション」→「集計行」のチェックボックスに✓をいれます。

すると、最後尾に「集計行」が追加され、さらに最後尾の列である「金額」の合計が計算されて表示されます。

先ほど、「納品日」の列と入れ替えたのはこのためで、仮に「納品日」の列がここにあったとしたら、そのデータの個数が表示されていました。

集計07

このように、「合計」以外にも「平均」や「データの個数」、「最大値」「最小値」などをプルダウンで選んで表示することができます。

「集計行」が追加された後にあらためて選び直すこともできます。

集計08

さらに他の列についても、「集計」をすることができます。

 

データの再利用~「書き出し」と「取り込み」について~

Excelデータベースの内容について、専用データベースアプリケーションといった、他のアプリケーションに取り込んで使用することがよくあります。

その際にExcelデータそのもので渡すこともありますが、互換性という観点からも、一度オールマイティーに使える形式、「テキスト形式」に変えて渡すことが一般的です。

テキスト形式にもいくつかありますが、Excelのような表計算・リスト系のものでは、CSV(しーえすぶい)形式が多く使われています。

ここでは、これまで使ってきたデータを他でも使えるように、テキスト形式(CSV)に書き出し、また、それをあらためて取り込む場合にはどうするか、をご紹介します。

GW-00049

「ファイル」をクリックすると

データ書き出し02

「Backstageビュー」といわれる上記のような画面になりますので、「名前をつけて保存」をクリックします。

続けて保存場所を指定します。

ここでは「参照」ボタンをクリックし、「データベース」フォルダを指定し、選択します。

データ書き出し02.5

「ファイルの種類」を「CSV(カンマ区切り)」にします。

データ書き出し03

ファイル名を「営業4課売上一覧」とし、「保存」ボタンをクリックします。

データ書き出し04

「CSV(カンマ区切り)として保存する場合、ブックの一部の機能が失われる可能性があります。この形式でブックを保存しますか?」と聞いてきますが、ここでは「はい」をクリックします。

データ書き出し05

このようにCSV形式で保存されました。

データ書き出し07

これをワードパッドで開いてみるとこのように「,」で区切られた形のデータになっていることがわかります。

もしExcelを持っていなかったり、何らかの原因で使用できないお客様や状況のときに、このテキスト形式で保存したものがあれば、ワードパッドやメモ帳などテキストエディター系のアプリケーションで対処することができて、非常に便利です。

では、このようなタイプのデータをお客様などから支給されたとしたら、どう使えばよいでしょう?

いきなりExcelで開くことも可能ではありますが、ここでは「テキストデータ」の取り込みをご紹介しておきます。

データ取り込み01

まず空のブックを用意します。

そこで、「データ」タブから「外部データの取り込み」→「テキストファイル」を選びます。

取り込むデータを指示します。

データ取り込み02

「テキストファイルのインポート」が立ち上がりますので、指定のデータを選択し、「インポート」ボタンをクリックします。

データ取り込み03

次に「テキストファイルウィザード」が起動します。

まずファイル形式で「カンマやタブなどの区切り文字によってフィールドごとに区切られたデータ」をチェック。

それと「先頭行をデータの見出しとして使用する」にもチェックを入れます。

取り込み開始行については「1」のままでよいでしょう。

もしタイトルなどが入っていていらない場合などは2行目以降、という形で指定できます。

ここまで行ったら「次へ」をクリックします。

データの取り込み04

ウィザードが第二段階に入ります。

「カンマ」にチェックを入れると、下のプレビューに区切りが入ります。

この区切りがExcelで開いていた時のデータと同じものか確認してください。

「引用符」が「”」になっていれば、「次へ」をクリックします。

データの取り込み05

いよいよ最終段階です。

ここでは、各列のデータ形式を設定することができますが、いらない列の削除もすることができます。

最初のNo.列を試しに削除してみましょう。

「データのプレビュー」で「No.」列が選択され色が反転していることを確認したら、「削除」にチェックを入れます。

後は「完了」をクリックするだけです。

ちなみに他の列を削除したい場合は、その列をクリックして色を反転させればOKです。

データの取り込み06

データの取り込み先(データを返す先)を尋ねてきますので、「既存のワークシート」にチェックが入っていることを確認し、「OK」をクリックします。

今回は先に空のブックを立ち上げていたので、そこが取り込み先になります。

データの取り込み07

このようにデータが取り込まれました。

先ほど「No.」列は削除するよう設定したので取り込まれていません。

これでお客様などからデータをいただいても使えることができますね!

 

Excelを使ったデータベースの運用についてみてきました。

他にもいろいろなテクニックがありますので、また別の機会に他の機能のと合わせてご紹介していきたいと思います。

Excelで詳細抽出・集計・データ取り込み!エクセルデータベース計画へコメントをする

※メールアドレスは公開されません。