フィルターオプション 配列 MSQuery ADO を使って 表からデータを抽出する 2 Rev070820 こうすればできる研究所 目次 1. データ - 並べ替え で表のデータを並べ替えする... 3 2. オートフィルターを使ってデータを取得する... 4 3. フィルターオプションでデータシートと同じシートにデータを取得する... 5 4. データと同じ範囲に取得後にデータ範囲を元に戻す... 8 5. 集計機能を使う... 9 6. フィルターオプションでデータシートとは別シートにデータを取得する...11 7. データを抽出する ( フィルターオプション )... 12 8. 検索条件と検索結果を別のシートに分離する... 14 9. 複数条件で検索しデータを取得する... 15 10. 配列を使って表からデータを抽出する説明... 18 11. 表の準備 ( 配列 )... 19 12. 合計値を計算する ( 配列 )... 21 13. 条件を加えて計算する ( 配列 )... 22 14. 配列を使って度数分布を求める... 23 15. MSQuery を使ってデータを抽出する... 25 16. SQL 文を編集してデータを取得する また計算する... 34 17. SQL 文を編集して購入先が東北の場合の売上高を集計する... 35 18. SQL を編集して期間データ ( 開始月日 ~ 終了月日 ) を抽出する... 36 19. SQL の保存... 37 20. アクセスデータに ADO 接続してデータを取得する例... 38 21. 別のエクセル Book に ADO 接続して売上表データを取得する例... 39 2/39
1. データ - 並べ替え で表のデータを並べ替えする 1.1. フィルターオプション 2000Rev070820.xls のシート 売上表 2000_ 並べ替え を選択します 表中のセルを選択しておいて操作します データ - 並べ替え の順でクリックします 1.2. 並べ替えの項目 昇順 ( 降順 ) を指定します 通常は 3 つのキーを設定できます OK をクリック 3/39
2. オートフィルターを使ってデータを取得する 2.1. 表中のセルを選択しておきます データ - オートフィルター の順でクリックします 2.2. 任意の マークをクリックし取得したいデータを選択します 2.3. 結果が表示されます 4/39
3. フィルターオプションでデータシートと同じシートにデータを取得する 3.1. データシートの空いたスペースに検索条件を用意しておく 検索条件のフィールド名はデータのフィールド名をコピー貼り付けすると間違いが少なくなる 表中のセルを選択しておいて データ - フィルター - フィルターオプションの設定 の順にクリックする 3.2. 検索条件が一つの場合 検索条件のフィールド名の下に検索条件を入力する 下図の場合は品名 = さつま という条件を表している 品名 <> さつま は さつま 以外の品名のデータを取得したいとき 表示されるダイアログの 検索条件範囲 に検索条件の範囲を選択して入力する ( またはキーボードから入力する ) データの範囲は リスト範囲 に自動的に入力されている リスト範囲 検索条件範囲 が正しいことを確かめて OK をクリックする 5/39
3.3. 検索条件が複数の場合 (and 条件 ) 検索条件範囲の同じ列に条件を入力すると and 条件 になる 下図の場合は品名 = さつま 単価 <=90 を同時に満足するデータを取得したいとき and 条件 3.4. データ取得結果を下図に示す 6/39
3.5. 検索条件が複数の場合 (or 条件 ) 検索条件範囲の下行に入力すると or 条件になる 空行はすべてとなる 下図の検索条件は品名 = さつま で単価 <=90 のデータ (and 条件 ) または品名 = だいこん のデータ (or 条件 ) のデータを取得したいときの設定例 or 条件 3.6. 上の図の検索結果は下図となる 7/39
4. データと同じ範囲に取得後にデータ範囲を元に戻す 4.1. データ範囲と同じ範囲にデータを取得するとデータ範囲の表示が折り畳んで表示されます 取 得したデータを使った後にデータ範囲を下に戻すには下図のように操作します 4.2. 補足 : 取得したデータ範囲が大きい場合には下図のように 編集 - ジャンプ を使い表示されたダイアログで アクティブセル領域 をチェックし OK すると楽に取得したデータ範囲を選択操作できます 8/39
5. 集計機能を使う 5.1. 表のセルをクリックしておいて 品名 で並べ替えておく データ - 集計 をクリックする 5.2. グループ基準を 品名 以下下図のように設定する OK をクリックする 5.3. 品名ごとの集計が計算される 9/39
5.4. 明細を表示したくないときは下図の様に操作する 2 をクリック 5.5. 総計のみを表示するときは下図のように操作する 1 をクリックする 5.6. 集計を解除するときは下図のように操作する 集計を解除するときここをクリック 10/39
6. フィルターオプションでデータシートとは別シートにデータを取得する 6.1. 下図のような表を用意します シート名は売上表 2002 です 売上高列には単価 * 数量の式を設定しています表には売上表 2002 という名前を設定しています ( 挿入 名前 定義 ) 6.2. - シート売上計算に検索列範囲とデータ出力列を設定します 数値の大小 日付で検索条件を設定する場合は不等号 等号を <= 数値 >= 数値などのよう に数値 日付の左側に配置します また不等号は最左側に配置します 範囲名 売上出力 2002 範囲名売上検索 2002 AND 条件は同じ行に OR 条件は別の行に空白行は作らない ( すべてなので ) 11/39
7. データを抽出する ( フィルターオプション ) 7.1. シート 売上計算 を選択 データ - フィルター - フィルターオプション をクリックして下図の様に入力します ( カーソルを置いておいて 挿入 名前 貼付 名前の貼付 ダイアログで貼り付ける名前を選択して OK すると簡単に設定できます ) こちらを選択す 7.2. 下図のようなダイアログが表示される場合もありますが はい をクリックします 7.3. データが抽出されました 計算式はなくなって値になっている 12/39
7.4. 抽出範囲を下図のように設定して OK する 抽出範囲のフィールド名は順序は入れ替えても連続していることが条件です またフィールド名は元の表と正確に同じにするためコピーします 7.5. 下図のように抽出されます 13/39
8. 検索条件と検索結果を別のシートに分離する 8.1. 検索条件シートを用意し 検索条件を下図のように設定します 8.2. 検索結果シートを用意し下図のように設定します 2 行目にはダミーのデータを入力しておきます ( 下図では単価列に 1) 8.3. 検索結果シートを選択しておいて データ - フィルターオプション フィルターオプショ ンの設定 をクリック 下図のように設定します 8.4. データが検索結果シートに抽出されました 14/39
9. 複数条件で検索しデータを取得する 9.1. 複数条件で検索する方法は同じ行は AND 条件 行を異なれば OR 条件です 例として 2002/7/1~2002/7/31 の期間データを取得する場合は AND 条件を設定します ここでは別の方法にトライしてみます シート複数条件 選択 例として単価 <200 単価 >=100 を同時に満足するデータを取得します (AND 条件 ) 1 検索条件の表題を入力 2 検索条件式を入力 4 条件値 検索条件式 3 条件値 9.2. 条件式は =AND($K$2> 売上表 2002!D2, 売上表 2002!D2>=$J$2) と入力します $K$2 は条件値 200 のセル番号です 絶対参照にすることがポイントです $J$2 は条件値 100 のセル番号です 絶対参照にすることがポイントです 売上表 2002!D2 は売上表 2002 の単価列の一番上のデータのセル番号です 条件値をセル番号でなく数値または文字で設定したい場合は 数値の場合はそのままの数値をセル番号の位置に設定します 文字場合は で文字列を囲んでセル番号の位置に設定します ( 例 ) さつま 月日データは Date 関数を使って Date(2002,7,1) とするか DateValue 関数を使って DateValue( 2002/7/1 ) のようにします 15/39
9.3. シート複数検索条件 を選択し データ - フィルターオプションの設定 でフィルターオ プション設定ダイアログに設定値を設定して OK する 9.4. 検索条件に合ったデータを取得できました 16/39
9.5. 200> 単価 >=100 または 売上高 >5000 のように AND 条件と OR 条件の場合も同様にします 計算式は =OR(AND($K$10> 売上表 2002!D2, 売上表 2002!D2>=$J$10), 売上表 2002!F2>$L$10) です 17/39
10. 配列を使って表からデータを抽出する説明 10.1. 配列を使ったデータ抽出について簡単に説明します配列計算とは計算する範囲を選択しておいて数式を入力し Shift + Ctrl + Enter キーで確定することです ( Shift と Ctrl を押しておいて Enter を押す) 入力されたセルには式 {= 関数 ()} が入ります 配列計算によって途中計算の列を省くことなどが出来ます 10.2. 配列計算に使用できる関数は SUM MAX { } で囲まれ LARGE るのが特徴 SMALL AVERAGE COUNT MODE STDEV など 18/39
11. 表の準備 ( 配列 ) 11.1. 下図のような表を用意します シート名は売上表 2002 です フィルターオプションで使った表と同じ ( 売上高列には単価 * 数量の式を設定しています ) 範囲名 : 売上表 2002 11.2. 配列計算の例図のように入力して Shift+Ctrl+Enter します 19/39
11.3. 11.4. 配列の一部クリアなどをしようとすると図のようなダイアログが表示され操作が拒否されます そのときの対応は 1 配列範囲の中にカーソルを置き Ctrl+/ する ( 範囲が選択状態になる ) 2 数式バーの数式の中にカーソルを置く 3 Ctrl+Enter する ( データは見えているが通常の状態になる ) 4 クリアするまたは 1 Ctrl+/ する 2 Backspace 3 Shift+Ctrl+Enter する 20/39
12. 合計値を計算する ( 配列 ) 12.1. 集計値を出力するセルに下図のように式を入力し Shift+Ctrl+Enter します 12.2. 計算結果 21/39
13. 条件を加えて計算する ( 配列 ) 13.1. 購入先が東北の合計値を計算する 売上高 = 単価 * 数量で計算する この式を入力して Shift+Ctrl+Enter する 13.2. 計算結果 13.3. IF を使わない式に変更する 13.4. 購入先が東北 九州の合計値を計算する 13.5. 数量が 100 以上の売上合計 22/39
14. 配列を使って度数分布を求める 14.1. 配列の応用として売上表 2002 の単価列の度数分布を求めてみます 単価の区間配列を下図のように 20 刻みで設定してみました ( 昇順で設定します ) 度数の範囲を区間配列よりひとつ多く選択します ( これがコツです ) 14.2. =Frequency() を度数範囲に設定します 区間配列で設定する範囲 23/39
14.3. 14.4. Ctrl + Shift +[Enter] します 24/39
15. MSQuery を使ってデータを抽出する 15.1. MSQuery の操作の再実行は以前に実行したパソコンで再現できます 異なるパソコンに練習ファイル ( ここではMSQueryData 表示用.xls ) をコピーして実行しようとして もパスが認識できず正常に動作しません 練習ファイルの該当するデータをいったん消去して改めて実行してください 15.2. エクセルメニューの ツール - アドイン で MSQuery アドインをインストールしておきます アドインは標準ではインストールされませんので OFFICE -CDROM から追加インストールしま す インストールしたら下図のように MSQuery アドインと (ODBC アドイン ) にチェックしておきま す 15.3. データが抽出される Book にはあらかじめ名前を定義しておきます ( ここではすでに定義してある名前を使います ) ( 挿入 - 名前 - 定義 ) 25/39
15.4. データを抽出することに使うエクセル Book は抽出されるデータのある Book とは同じ Book でも別の新しい Book でも OK です ( この説明では MSQueryData 表示用.xls とします ) SQL 文を覚えれば EXCEL ファイルだけでなく ACCESS や MySQL などのデータも扱えます Excel データを扱うには データー - 外部データの取り込み - 新しいデータベースのクエリー - Excel Files * を選択します 次に表に範囲名を設定してあるファイル ( この説明ではフィルタオプション配列 2000.xls) を選択し OK します ( ファイルの場所をドライブ フォルダー データベース名の順に指定します ) 同様に ACCESS データを扱う場合は MS Access Database* を選択します 15.5. > をクリックしたら 次へ をクリック ここをクリック 26/39
15.6. 次へ をクリック 15.7. 次へ をクリック 15.8. クエリの保存 をクリック 27/39
15.9. 名前をつけたら 保存 をクリック 15.10. Microsoft Excel にデータを返す にして 完了 をクリック 15.11. データが抽出されました 28/39
15.12. 表示不要のデータがある場合は下図のようにする 2 ここをクリック 1 表示したくないデータを選択して 15.13. ここで抽出条件を設定してもよい 15.14. 抽出条件として 購入先 = 東北 とした場合の例 29/39
15.15. MicrosoftQuery でデータの表示またはクエリの編集を行う にチェックし 完了 をクリッ ク 15.16. 購入先 を東北に絞った場合の SQL 文例 ( SQL アイコンをクリックすると表示できます ) SELECT MSQuerry 売上表 2002. 月日, MSQuerry 売上表 2002. 品名, MSQuerry 売上表 2002. 購入先, MSQuerry 売上表 2002. 単価, MSQuerry 売上表 2002. 数量, MSQuerry 売上表 2002. 売上高, MSQuerry 売上表 2002. 経費 FROM MSQuerry 売上表 2002 MSQuerry 売上表 2002 WHERE (MSQuerry 売上表 2002. 購入先 =' 東北 ') ここでのポイントは WHERE 句です 購入先 = 東北 と東北が で囲まれていることが重要 です 15.17. ここで並べ替えを設定してもよい 30/39
15.18. 単価 を昇順に並べ替えたい場合の例 15.19. クエリの編集を行うを選択して完了 15.20. データを表示します ここに表示しているのは 抽出条件 並べ替えは設定していない例です 31/39
15.21. SQL 文を表示します 15.22. SQL 文が表示されました 15.23. エクセルシートにデータを返す 32/39
15.24. データを返すアドレスを指定します ( 最左上端の位置 ) 15.25. 15.26. この SQL 文に名前をつけて保存しておけば繰り返し使用できます MSQuery の ファイル - 名前をつけて保存 15.27. 保存した MSQuery を利用するには [ データ ]- データの取り込み をクリックして保存してある MSQuery を選択して 開く をクリックします 33/39
16. SQL 文を編集してデータを取得する また計算する 16.1. 編集するためには MSQuery を起動します 1 データを取得してある範囲のひとつのセルをクリックしておいて データ - 外部データの取り込み - クエリーの編集 で MSQuery を起動し SQL 文を表示して行います この方法でうまく起動できない場合は クエリーの編集 で キャンセル をクリックし 続けて表示される図の OK をクリックすると MSQuery が起動しますので MSQuery の ファイル - 開く から目的の DQY ファイルを開きます クリック 2 MSQuery を単独で起動する単独で起動するには エクスプローラー または ファイル名を指定して実行 で Program Files Microsoft Office Office MSQRY32.exe を開きます 単独で開いた場合は ファイル - Excel にデータを返す メニューが表示されませんのでいったん名前を付けて MSQuery を終了し 改めて新規の場所にデータを返します 34/39
17. SQL 文を編集して購入先が東北の場合の売上高を集計する 17.1. SQL 文を編集して購入先が東北の売上高合計を計算します 東北 としているのに注 意 この後 東北売上高計算 としま した 17.2. 結果を入れるセルを指定してクリックします 17.3. 35/39
18. SQL を編集して期間データ ( 開始月日 ~ 終了月日 ) を抽出する 18.1. 下図の例は 2002/7/1~2002/7/31 の期間データを抽出する SQL 文です Where 句は月日 >=#2002/07/01# としています 18.2. 18.3. 再度編集して Query を実行しようとすると下図のような表示が出ることがあります 下図でいえば AND <=#2002/07/31# の 部分が保存されていなかったためです AND MsQuer y 売上表. 月日 <=#2002/07/31# として実行すれば動作します 複数の条件を設定した場合はこのような現象が起きるようです このときは クエリーの編集 で表示される図で キャンセル をクリックし 続けて表示さ れる図の はい OK をクリックすると MSQuery が起動しますので MSQuery の ファイ ル - 開く から目的の DQY ファイルを開き 編集します 36/39
19. SQL の保存 19.1. 再利用のために名前をつけて保存します 通常は マイドキュメント - MyData Sources (2002) ApplicationData-Microsoft-Queries(2000) フォルダーに入ります 再利用するときは データ - 外部データの取り込み - データの取り込み (2002) 保存したクエリーの実行(2000) をクリックすると MSQUERY が起動されます 37/39
20. アクセスデータに ADO 接続してデータを取得する例 20.1. エクセルからアクセスデータファイルに ADO 接続してデータを取得する例を示します ここの示した例はデータの取得ですがデータの追加 削除 更新も出来ます ADO 接続するには VBE を起動し ( ツールーマクロー VisualBasicEditor),VBE の ツール ー 参照設定 で Microsoft AcitiveX DataObjects 2.5 Libraly にチェックを入れておく必要があります アクセスデータファイル (MSQueryUriage2002.mdb) はデータを取得するエクセルファイル ( フィルターオプション配列 2000.xls.xls) と同じフォルダーにあるものとします 20.2. シート AcData 取得 の下図のコマンドボタンをクリックすると対応したデータを取得することができます それぞれのコードは MSQueryData 表示用.xls で VBE を起動 してご覧ください 20.3. Access 全データ表示 をクリックした場合 38/39
21. 別のエクセル Book に ADO 接続して売上表データを取得する例 21.1. 名前定義した ( 売上表 2002) エクセルデータファイル (MSQuery 売上表 2002.xls) はデータを取得するエクセルファイル ( フィルターオプション配列 2000.xls.xls) と同じフォルダーにあるものとします ADO 接続するには VBE を起動し ( ツールーマクロー VisualBasicEditor),VBE の ツール ー 参照設定 で Microsoft AcitiveX DataObjects 2.5 Libraly にチェックを入れておく必要があります エクセル Bookに ADO 接続してデータを取得する例は発表事例が少ないのでマイクロソフトの説明を元に作成してみました アクセスでのコードと対比できるようにアクセスでのコードをコメントアウトして記述しました 詳しくはマイクロソフトオンラインサポート http://support.microsoft.com/default.aspx?scid=kb;ja;257819#xslth413212112212012112 0120 をご覧ください 21.2. フィルターオプション配列 2000.xls のシート xldata 取得 を操作して確かめてください また使用しているコードは ツール - マクロ - VisualBasicEditer を起動してご覧ください 39/39