外部データの取り込みや データベースクエリから
自参照でデータを集約しようとすると 各シートごとに設定を行う必要があります。SQLで直接 指示を入力すると これらの手間をなくすことができます。
で SQLとは何かというと「データベースを操作する為の言語」です。そうすると今後は「データベース」って何? という話になる訳ですが「規則正しく配列された表形式のデータ」と考えて下さい。Excelのデータも
入力の仕方に気をつけておけば SQLを使って操作することができます。
032.xls:
今回も「1月」「2月」「3月」シートを総合シートにまとめます。シートの数が増えてもやることは一緒なので 応用の範囲内かと思います。「032.xls」ファイルは
「C:」に直接保存されていることとします。A1セルに「日付」B1セルに「数値」と 項目だけは先に入れておきましょう。
・Excel2002以降の方の場合※(2007.03.17 修正)
[データ]→[外部データの取り込み]→[データの取り込み]
・Excel2000の場合
[データ]→[外部データの取り込み]→[新しいデータベースクエリ]
ここから Excelのバージョンによって若干設定が異なります。
★Excel2002・Excel2003の方の場合
[データ ファイルの選択]の[ファイルの場所]から移動して指定のブックを選択
(今回なら「C:\032.xls」)→[開く]
[表の選択]ではそのまま[OK]
項目行は既に入力したので 参照先は[既存のワークシート]で「=A2」とします。
→[プロパティ]→[フィールド名を含む]と[列の幅を調整する]のチェックを外して[OK]
→[クエリの編集]
[OLE DB クエリの編集]で[接続][コマンドの種類]はそのままにして
[コマンドの文字列]に
SELECT *
FROM [C:\032].['1月$']
WHERE (['1月$'].日付 Is Not Null)
UNION ALL
SELECT *
FROM [C:\032].['2月$']
WHERE (['2月$'].日付 Is Not Null)
UNION ALL
SELECT *
FROM [C:\032].['3月$']
WHERE (['3月$'].日付 Is Not Null)
ORDER BY ['1月$'].日付
と記入して[OK]→[OK]
※[コマンドの文字列]では 右クリックメニューは使えませんので コピーや
貼り付けがしたい時は ショートカットキーを利用して下さい。
「Ctrl + C(コピー)」「Ctrl + X(切り取り)」「Ctrl + V(貼り付け)」
以上です。
★Excel2000の場合
[データ ソースの選択]で[ExcelFiles]を選択して[OK]
[ブックの選択]で指定のファイルを選択して[OK]
[クエリ ウィザード − 列の選択]
適当なシート選んで (4)の「>」ボタンをクリック
→[次へ]→[次へ]→[次へ]→[完了]
もし「このデータソースには表示できるテーブルがありません」という注意書きが表示された場合は「OK」を押して下さい。おそらくテーブルに何も表示されないと思いますが
(1)[オプション]→(2)[システムテーブル]にチェック→(3)[OK]
を行えば 何らかの項目名が表示されるはずです。
[クエリ ウィザード − 完了列の選択]
[Microsoft Query でデータの表示またはクエリの編集を行う]
→[完了]※
※初めて利用する時は 追加インストールを求められますのでインストールして下さい。
[Microsoft Query]で[ファイル]→[SQL の実行]
ファイルの選択を求められるので 任意のファイルを選択して[OK]
[SQL ステートメント]に
SELECT *
FROM [C:\032].['1月$']
WHERE (['1月$'].日付 Is Not Null)
UNION ALL
SELECT *
FROM [C:\032].['2月$']
WHERE (['2月$'].日付 Is Not Null)
UNION ALL
SELECT *
FROM [C:\032].['3月$']
WHERE (['3月$'].日付 Is Not Null)
ORDER BY ['1月$'].日付
と記入して[OK]→[OK]
[ファイル]→[Microsoft Office Excel にデータを返す]
以上です。
「SELECT」や「FROM」についての説明は後でします。これでA2セル以降にデータが入力されると思います。因みに 頭に数字が付いている場合や
一部の文字列(「C」や「R」など)の場合 「['1月$']」のように「'」で囲む必要があります。「Sheet1」などの場合は「[Sheet1$]」でいいです。あと範囲を名前定義している場合は「[」や「]」も不要です。
表示形式は「標準」になっていると思うので 別途「日付」や「通貨」などに変更して下さい。
さて上で設定した SQLの書き方についてです。
■SELECT:選択するシートを設定します。必須
[シート名].1列目の項目名, [シート名].2列目の項目名……
項目名を指定しないでデータ全てを対照とする場合「*」を入力します
また 指定した範囲内で重複データを除くときは後ろに「DISTINCT」を付け
て下さい
例:SELECT ['1月$'].日付, ['1月$'].数値
例:SELECT DISTINCT *
■DISTINCT:重複データを除いて表示します。指定がなければ不要
■FROM:保存先と SELECTで指定した項目のあるシート名の指定。必須
[保存先のフルパス].[シート名]
例:FROM [C:\032].['1月$']
■WHERE:条件指定。指定がなければ不要
(07.03.10 修正)
([シート名].項目名 条件)
例:WHERE (['1月$'].日付=#2007-1-1 00:00:00#) AND (['1月$'].数値=1.0)
[条件]には下のようなものがあります
と等しい |
=? |
と等しくない |
<>? |
より大きい |
>? |
以上 |
>=? |
より小さい |
<? |
以下 |
<=? |
値の一部である |
In (?) |
値の一部ではない |
NOT In (?) |
値に挟まれる |
Between ? And ?
Between '' And '' |
値に挟まれない |
Not Between ? And ?
Not Between '' And '' |
値で始まる |
Like '文字列%' |
値で始まらない |
Not Like '文字列%' |
値で終わる |
Like '%文字列' |
値で終わらない |
Not Like '%文字列' |
値を含む |
Like '%文字列%' |
値を含まない |
Not Like '%文字列%' |
空白データ |
Is Null |
空白以外のデータ |
Is Not Null |
※「?」の部分に直接 数値を指定する(「1」「#2007-1-1 00:00:00#」など)
※条件にする値は そのデータ形式によって表記方法が異なります
文字列型 |
「"(ダブルクォーテーション)」か「'(シングルクォーテーション)」で値を挟む
例:WHERE 名前="あ" |
日付時刻型 |
「#(井桁)」で値を挟む
例:WHERE 日付=#2007/3/10# |
数値型 |
そのまま入力
例:WHERE 数値=1 |
■ORDER BY:データの並べ替えを指定。指定がなければ不要。構文にひとつ
昇順 → [シート名].項目名 ASC
降順 → [シート名].項目名 DESC
例:ORDER BY ['1月$'].数値 DESC
■UNION:違うデータを繋ぐ時に使用。
今回はこれが一番重要
データをまとめる際 全ての項目が一致するデータ(重複データ)は ひとつ
にまとめられます。それを避けるなら後ろに「ALL」を付けて下さい。
例:SELECT ['1月$'].日付 FROM [C:\032].['1月$'] UNION SELECT ['2月$'].日付 FROM
[C:\032].['2月$']
面倒そうに見えますが 指示通りに入力するだけなので 慣れればとても簡単です。更新については[外部データ]ツールバーの[すべて更新]ボタンを押すか
[データ範囲プロパティ]で[ファイルを開くときにデータを更新する]にチェックを入れておくといいと思います。
→参考ファイル(032.xls 保存場所を「C:」にしている設定です。
Excel2002以降のバージョンでないと 正常に機能しないと思います)
因みに シート名に名前を付ける方法もありますが その際
データ範囲を可変にすると クエリで設定できなくなります。もし名前定義をするなら範囲を多めにとるなどの対応をして下さい。ただし 範囲を列参照にすると とても重くなります。ご注意下さい。あと保存先が変わった時は
別途[クエリの編集]から保存先の変更を行う必要があります。
■接続
Provider=Microsoft.Jet.OLEDB.4.0;Password="";
User ID=Admin;Data Source=フルパスのファイル名(拡張子付き);
Jet OLEDB:Engine Type=35;
■コマンドの文字列
SELECT *
FROM [保存元のフルパス].[シート名1$]
UNION ALL
SELECT *
FROM [保存元のフルパス].[シート名2$]
ORDER BY [Sheet1$].種別
上の部分だけ記述すれば 基本的には機能すると思います(2007.10.23 追加)
最後に。このページを作るきっかけと アドバイスを下さった 図さんに感謝します。