データを扱う時のいろいろな操作 2
| ■ Excel豆知識41 |  |
|---|
[図04101]のようなデータから[図04102]のような担当者ごとのシートを作成し、元データに 追加変更があったらそれをいつでも反映させたい、というような場合、「外部データの取り込 み」を使うと結構便利ですのでご紹介してみようと思います。 そして、今回は「外部データ」として自ブックのシートを利用します。 （データを自ブックに置くことを推奨しているわけではありません。あくまでも例としてです。）
[図04101]
[図04102]
なお、以下の操作をするには「Microsoftクエリ」が必要ですが、標準ではインストールされていないよう
ですので「この機能は現在インストールされていません。インストールしますか？」というメッセージ
が出たら、追加インストールをしてください。（OfficeのCDが必要になると思います。）
外部データの取り込みは元データのパスが変わると「ファイルが見つかりません」というようなエラー
になりますので、ダウンロード用のブックには作成例を置いてありません。皆さんのPC上で作業をなさ
ってみてください。
41-1 外部データの取り込み（パラメータークエリの利用）
<外部データの取り込み>
まず「ここへ作成」シートのA2セルに入力規則のリストで担当者名を選択できるように設定しま
す。「売上データ」シートのB列から担当者を重複なしで抽出し、そのリスト範囲に名前をつけ、
それを入力規則のリストの「元の値」として設定します。
重複しない抽出については Excel豆知識29-1:エクセル:重複しない抽出
入力規則のリストについては Excel豆知識12-2:エクセル:入力規則（リスト）
を参照してください。
[図04103]
別にこのように設定しなければならないということではありません。 こうしておくと便利なのでやってみました。
（１）抽出データを表示させるシートを選択して、「データ」-「外部データの取り込み」-「新 しいデータベース クエリ」をクリック。（２）「データソースの選択」で「Excel Files*」を選択し、「クエリウィザードを使ってクエ リを作成/編集する」にチェックを入れ「OK」。
[図04104]（３）データのあるブック（今回は自ブック）を選択して「OK」。
[図04105]ここで、「このデータ ソースには、表示できるテーブルはありません。」というエラーが出る場合は、 サイト内FAQ13のやり方で試してみてください。その後このページの（８）へ繋がります。 （４）データシートを選択して、「>」をクリックしてクエリの列にデータシートの項目を全部 入れます。
[図04106]もし不要な項目があれば選択して「<」で戻せます。ここでは全項目を取り込んで「次へ」。
[図04107]（５）ここはこのまま「次へ」。
[図04108]（６）ここもこのまま「次へ」。
[図04109]（７）「Microsoft Query でデータの表示またはクエリの編集を行う」にチェックを入れ「完了」。
[図04110]（８）クエリの編集の画面が出ます。
[図04111]（９）「条件」-「抽出条件の追加」をクリック。
[図04112]（１０）「抽出条件の追加」で「集計」は空欄のまま、「フィールド」で「担当者」を選択。 演算子は「=」のままで、「値」に「[」と「]」（半角の角括弧）を並べて入力（ [] となりま す）。そして「追加」。
[図04113]（１１）「パラメータ値の入力」ダイアログボックスが出ますから、これは何も入力しないまま で「OK」。（このパラメータを後でExcelのセルの値で設定するようになります。）
[図04114]（１２）「抽出条件の追加」に戻りますので「閉じる」。 （複数の抽出条件を設定したい場合は、ここで別のフィールドを同様に設定します。）
[図04115]（１３）クエリの画面の「ファイル」-「Microsoft Excelにデータを返す」をクリック。
[図04116]（１４）「データのインポート」ダイアログボックスの「パラメータ」をクリック。
[図04117]（１５）「パラメータ１」が選択された状態で、「パラメータ値の取得先」の「次のセルから 値を取得する」にチェックを入れ、右の小さな四角をクリックして、
[図04118]「担当者」をリストで選べるように設定した「ここへ作成」シートのA2セルを選択して、「閉じ る」。
[図04119]「セルの値が変わるときに自動的に更新する」にチェックが入った状態で「OK」。 （ここにチェックを入れない場合は、セルの値を変更したあと「データの更新」をクリックして更新を反映 させます。）
[図04120]（１６）「データのインポート」ダイアログボックスに戻るので、「データを返す先」の「既 存のワークシート」にチェックを入れ（初期設定で入っていますが）、データを表示させたい先 頭セルを選択して「OK」。
[図04121]この時「プロパティ」をクリックすると、下図のダイアログボックスが出ます。 ここで更新のタイミングなどを設定することが出来ます。 今回はここはこのままにします。
[図04122]（１７）すると、下図のように項目だけが表示されます。
[図04123]（１８）ここでA2セルで担当者名を選択。
[図04124]（１９）すると、その担当者のデータだけが表示されます。
[図04125]（２０）ここでA列のセルの書式を、データと同じ「何月何日」という形式にします。 日付の書式については Excel豆知識3:エクセル:セルの書式設定（日付の書式） を参照してください。
[図04126]（２１）別の担当者を選択すれば、その担当者のデータだけが表示されます。
[図04127]これで操作は完了です。 <データの更新> では、「売上データ」シートにデータを追加して動作を確認してみましょう。 （１）とりあえず９月分のデータをコピーして10月分の下に貼り付け、日付の部分を置換で 「/9」を「/11」にすると、[図04129]のようになります。 （コピー貼り付けした日付の部分を下図のように範囲選択して、「編集」-「置換」で 「検索する文字列」に「/9」を「置換後の文字列」に「/11」を入れ、「すべて置換」とします。）
[図04128]（２）「外部データの取り込み」で表示されているデータの中のセルを選択した状態で「デー タ」-「データの更新」をクリックします。
[図04129]（３）すると、下図のように追加した１１月分のデータが反映されます。
[図04130]（４）A2セルで「担当者」を変えながら抽出するように作成しましたが、このシートをコピー して[図04132]のように「篠木」「鈴木」など担当者ごとのシートを作成すれば、いつでもそ の担当者のデータを見ることができます。
[図04131]A2セルに「シート名を表示させる数式」を入力しておけば、シートをコピーしてシート名を変更 すればシート名の担当者のデータが表示されます。 シート名を表示させる数式については Excel豆知識10-1:エクセル:シート名を表示させる を参照してください。
[図04132]（５）「外部データの取り込み」で表示されているデータの中のセルを選択した状態で「デー タ」-「外部データの取り込み」-「データ範囲のプロパティ」をクリックすると、
[図04133][図04135]のダイアログボックスが出ます。 この「コントロールの更新」の「定期的に更新する」にチェックを入れ、「１分ごと」とすると、 １分ごとに新しいデータを反映させることが出来ます。１分が一番短い単位です。 元データを 変更して、１分待っていると自動的にデータが更新されるのを確認できると思います。（１分ご とにしておくと、ちょっとうるさい感じがしますので、これは必要に応じて・・ということで。） また、「ファイルを開くときにデータを更新する」のチェックを入れないでおくと、手動で「デ ータの更新」をするまでは前回の抽出結果が表示されることになります。
[図04134]（６）この外部データの取り込みを設定してあるブックを開くと、下記のようなメッセージが 出ます。状況に応じてどちらかを選択してください。「自動更新を無効にする」とした場合は、 手動で「データの更新」をした時のみ更新が行われます。
[図04135]この機能は工夫次第でいろいろ便利に使うことができるものだと思います。 いろいろ試してみてください。 なお、Excelのバージョンの違いやそれぞれのPCの設定の違いで、ここでご紹介した画像とは 違うことも多いかと思います。その場合は「ご意見箱」のページからお問い合わせをいただき たいと思います。
[図04136]