ご存知のように Excel はデータ処理ツールとして非常に優秀 で、おそらくオフィスワークをする人の大半が使っていると思います。
一方で、データを保管、管理するツール として、Excel も使えなくも無いですが、昔から専用のツールとして データベース が利用されています。
データベース は Excel と比較して以下の優位性があります。
- データが 一元的に管理 されるので誤って古いデータを使う心配が無い
- 多数のアクセスを前提に作られているので、複数人のチームで共通で使う のに適している
- データ量が大規模 になってもパフォーマンスの問題が起きにくく、スケーラビリティに優れる
- ユーザごとのアクセス管理が細かく設定でき、セキュリティが高い
- システマチックな バックアップと復元 が可能で、データを安心して保管することができる
データ保管と管理には データベース 、データ処理には Excel を利用する体制を組むことでそれぞれの利点を最大限活かし、これまでできなかった 高度な仕組みを構築することが可能 です。
そこで、この記事ではプロも使用する Microsoft の データベース SQL Server の 無料版 である Express エディションを Excel と連携して使う方法をご紹介します。
無料版とは言え、生データの容量上限 10GB と、プロ並みに使わなければ使いきれない容量ですので、使わない手はありませんね。
では早速行ってみましょう。
SQL Server Express のセットアップ
ダウンロード
この記事の執筆時点では最新の SQL Server Express は、2019版ですので、以下のリンクからダウンロードして下さい。
検索で「SQL Server Express」をキーワードで検索すれば、最新版のリンクが出てくると思います。
インストール
インストーラをダウンロードしたら早速実行します。悩みそうなところは下記のようにして進めて下さい。
- インストールは 「基本」 を選んで問題ありません。
- インスタンスの構成では 「既定のインスタンス」 を選択
- データベースエンジンの構成の「認証モード」は 「通常モード（SQL Server認証とWindows認証）」 を選択
- システム管理者ID（sa: System Administrator の略）の パスワードを設定 （注意: このパスワードは後で使いますので忘れないようどこかにメモしておきましょう！）
SQL Server のリモート接続設定
SQL Server はデフォルトでは Excel などからネットワーク経由でアクセスできない状態になっています。
このためリモート接続できるよう設定します。次の２つの設定をするだけでOKです。
SQL Server の TCP/IP を有効にする
SQL Server のインストールが終わるとスタートメニューの 「Microsoft SQL Server 2019」のメニューの中に、SQL Server 2019 構成マネージャ が登録されているので、これを起動します。
構成マネージャのウィンドウで図のように操作して TCP/IPを有効化 をして下さい。
SQL Server Browser のサービスを開始する
続いて同じ構成マネージャで Browser のサービスを開始します。これも下の図のように操作して下さい。
以上で SQL Server Express版のセットアップは完了です。続いて管理ツールである SQL Server Management Studio （通称 SSMS）をセットアップします。
管理ツール SQL Server Management Studio （SSMS） のセットアップ
ダウンロード
こちらもこの記事の執筆時点で最新版は 2019版 なので以下のリンク先ページの下の方に 日本語版のリンク があるのでここからファイルをダウンロードして下さい。
「SQL Server Management Studio」をキーワードに検索すれば最新版のリンクが出てくると思います。
SQL Server Management Studio (SSMS) のダウンロード
インストール
インストーラをダウンロード、実行してインストールします。デフォルト設定のまま進めて下さい。
SQL Server に接続
インストールが終わったら早速起動して SQL Server に接続してみましょう。起動すると接続用のウィンドウが出てきますので、図のように先に設定した ユーザーIDの sa と、パスワード 入力して「接続」ボタンを押すと無事接続が完了するはずです。
サンプルデータで接続テストをする
ここからは必須の手順ではありませんが、ここまでの作業で本当に Excel から SQL Server に接続できるようになったのか確認してみます。
実際にデータを SQL Server に登録し、 Excel から SQL Server に接続してデータをダウンロードします。
実際にご自分でシステムを構築される際にも同様な作業が必要ですので、参考になると思います。
データベースを作成
まず、SQL Server 上に DMS というデータベースを作成します。
SQL Server Management Studio で SQL Server に接続した状態で、図のように操作します。
テーブルを作成
続いてデータを保管するテーブルを作成します。
SQL Server Management Studio で図のように操作します。
以上で、データベースの作成と、その中にテーブルを作成する処理の完了です。
サンプルデータを登録
続いてサンプルデータを登録します。以下のリンクからサンプルデータをダウンロードして下さい。
中身はタブ区切りプレーンテキストのシンプルなデータになっています。
これを C:¥temp のフォルダに保管して下さい。
続いて SQL Server Management Studio で、「新しいクエリ」ボタンを押して次のようにコマンドを入力します。
ハイフン以降のコメントは入力不要です。
USE [DMS]
BULK INSERT TEST_TABLE
FROM 'C:\temp\Sample_Data.txt'
WITH (
FIELDTERMINATOR = '\t', -- ファイル内のフィールドの区切り文字
ROWTERMINATOR = '\n', -- 行の終わりを示す文字
FIRSTROW = 2, -- ヘッダー行を無視するための指定 (1行目はヘッダーならば2行目からデータと見なす)
TABLOCK -- バルクインサートでテーブルロックを行う
);コマンド入力が面倒な方は、以下のリンクからコマンドファイルをダウンロードして、先ほどと同じようにファイルをダブルクリックしてSQL Server Management Studio の画面が開いたら F5キー を押してクエリを実行します。
エラー無く実行できたらサンプルデータが作成したテーブルに登録されているはずです。
データが登録されているか確認してみます。図のようにテーブルのアイコンを右クリック、「1000行確認」をクリックするとデータが表示されます。
以上でデータ登録が無事完了しました。
アクセスユーザーの設定
Excelから SQL Server にアクセスするのに管理者のアカウントを使うのはあまり望ましくありません。
そこでデータアクセス用のユーザーを設定します。
SQL Server のアクセス権限には次の2つの種類があります。この記事では簡単のために SQL Server 認証 でのユーザーアカウントを作成します。
SQL Server 認証
一つ目は SQL Server 上で独自のユーザー、パスワードを設定し、これで外部からアクセスする方法です。これは SQL Server 認証と呼ばれています。
この方法は単純で簡単な反面、SQL Server側で独自でユーザー管理をするため管理の手間がかかります。
Windows認証
二つ目は会社などのユーザー管理に連携した Windows認証です。これは会社などの組織で管理するユーザー認証をそのまま使うので、SQL Server 側ではユーザーのパスワード管理などが不用なので便利です。
しかし、組織でユーザーを管理するアクティブディレクトリについて多少の知識が必要など、人によってはハードルが高い可能性があります。
SQL Server 上でユーザーを管理する SQL Server 認証のユーザーを作成します。図のようにログインの作成をします。
ユーザー名とパスワードを設定し、更にユーザーにアクセスするデータベースを設定します。
これで SQL Server上にユーザーが作成されましたので、Excel からこのユーザーでアクセスします。
Excel Power Query で SQL Server に接続
いよいよ Excel で SQL Server に接続します。図のように Excel のメニューから操作します。
接続ダイアログが出ますので、図のように接続設定を入力します。
続いてアクセス権設定のダイアログが出てきたら先ほど作成したユーザー （read_user）とパスワードを入力します。
続いて接続するテーブルの選択画面になるので、作成したテーブルを選択し、読み込み先をクリックします。
読み込み先の設定ダイアログが出るので、図のように設定します。
設定が完了すると Excel に Power Query によって作成されたテーブルが表示されます。
このテーブルは図のように更新を実行するだけで SQL Server に自動で再接続して最新データをダウンロードして表示してくれます。
以上、Excel から SQL Server への接続ができるようになりました。これで何時でも SQL Server 上の最新データを Excel で取得できます。
前述のように、SQL Server Express版の主な制限として、１データベースのサイズが10GB ということになっていますが、この具体的な確認方法をご紹介します。
サイズ制限はデータベースのファイルである MDFファイル のサイズを指しています。では、この MDFファイル はどこにあるのでしょうか？
SQL Server Management Studio を開いて図のように調べたいデータベースの名前の部分を右クリックしてプロパティを開きます。この場合、「TEST」という名前のデータベースについて調べています。
開いたプロパティの「File」を選択すると、MDFファイルの保管されているフォルダのパスが表示されますので、これを Ctrl＋C でコピーします。
最後にエクスプローラーを開いて先ほどコピーしたパスをアドレス欄にペーストしてフォルダを開きます。すると図のように MDFファイルの容量を確認することができます。
この MDFファイルが 10GB になると追加でデータを登録できなくなります。
まとめ
この記事では SQL Server をインストール、設定して Excel から接続して活用できるよう環境構築する方法をご紹介しました。
これを使えば、大量なデータの管理や、多数のユーザーからのアクセスを SQL Server でこなしつつ、そのデータは使い勝手の良い Excel から活用するという最強なシステムを構築することが可能です。
最初はスモールスタートで小さなデータ活用の仕組みを作るところから始めると良いと思います。
SQL Server は本格データベースですから、小さな仕組みを大きく広げられるポテンシャルがあります。そうすると一挙に大人数の作業の効率化が出来、効果は非常に大きくなると思います。
こんな仕組みを無料で構築できるのですから、活用しない手はないですね。
皆さんも是非この記事で得た知識で SQL Server と Excel をフル活用してみて下さい！
