ExcelをSQL Serverに接続する:3つの簡単な手順

以前の記事では、データ管理ソリューションにExcelとSQLの両方が必 この記事では、手順を示し、SQLスプレッドのようなExcelアドインを使用することの主な利点を強調します。

しかし、まず、ExcelをSQL Serverのデータに接続することの利点を再キャップします。

ExcelをSQL Serverに接続する利点

ExcelをSQL Serverに接続する利点を示すために、例を見てみましょう。

このスプレッドシートは、製造会社の営業担当者が顧客の見積もりを準備するために使用されます。 各営業担当者には、在庫品目とその価格のリストを含むスプレッドシートのコピーがあります。 在庫品目と価格のリストは、管理者によって財務システムから抽出されます。 見積シートでは、営業担当がドロップダウンから品目を選択したときに、いくつかのデータ検証検索式を使用して価格を取得します。

見積サンプル見積シート

これは、組織が価格を更新する必要があるまでうまく機能します。 その場合、各ユーザーは新しい価格設定をコピーしてスプレッドシートに貼り付ける必要があります（またはスプレッドシートの新しいコピーを使用します）。 また、営業担当者が見積を作成するときに、品目が実際に在庫されているかどうかもわかりません。

より良い解決策は、excelスプレッドシートをfinance systemデータベースのinventoryテーブルに接続することです。 営業担当が見積スプレッドシートを開くたびに、ライブデータベースから価格データが更新されます。 さらに一歩進んで、データベースへの接続も現在の在庫レベルを取り戻すことができるので、実際に在庫があるアイテムの見積もりを準備できます。

だから、要約すると、ExcelをSQL Serverに接続することの利点は次のとおりです:

  • 私たちは、アクセス権を持っており、最新の情報を表示することができます
  • 私たちは、単一の、信頼できるデータソースを使用しています
  • 私たちは、ピボットテーブルのような単純なExcel関数を使用して、ソースデータのオンザフライ分析や集計を実行することができます

SQL ServerデータベースにExcelを接続します:3簡単な手順

excelをsql serverデータベースに接続する最も簡単な方法は、sqlスプレッドを使用することです。 SQLスプレッドは、単純なExcelアドインです。

SQLスプレッドのダウンロードはここで見つけることができ、インストールプロセスはここで説明されています。

ステップ1: データベース接続の詳細を収集する

ExcelをSQL Serverに接続するには、次の情報が必要なので、最初のステップとして詳細を取得します:

  • SQL Server名
  • データベース名
  • 必要な情報を含むテーブルまたはビュー
  • ログインの詳細(Windows認証を使用していない場合))

SQL Server名の形式は、SQL Serverがローカル、ネットワーク、またはリモートの場所にインストールされているかどうかによって異なります(これは、SQL Server Management StudioでSQL Serverに接続

典型的なサーバー名 ケース
Sql Serverがローカルにインストールされている場合、Localhost
ローカルホスト\sqlexpress SQL Server Expressがローカルにインストールされている場合
Sql Serverが企業ネットワークにインストールされている場合、Domain\Servername
SQL Serverがリモートの場所にインストールされている場合のIPアドレス

ステップ2: SQL Serverに接続する

SQLスプレッドがインストールされると、新しいリボンタブとして表示されます。

ExcelでSQLスプレッドデザイナーを開きます

右側のSQLスプレッドデザイナーパネルで、編集ボタンをクリックしてSQL Server接続ダイアログを開きます。

SQL Server接続ダイアログを開きます

SQL Server名フィールドにSQL Serverの名前を入力します:

SQL Serverへの接続ダイアログ

Windowsログイン(Windows認証)を使用して接続するか、ユーザー名とパスワードを入力(SQL Server認証)するかを選択します。 Windows認証は、2つのオプションのうち、より安全です（詳細については、こちらを参照してください）。

SQL Server認証方法を選択

OKをクリックします。 SQLスプレッドはデータベースへの接続を試みます。 接続が成功すると、データベースがSQL Spread Designerパネルに表示されます。

SQL Spread Designerデータベースリスト

ステップ3: SQL Serverで関連するテーブルを選択します

ExcelからSQL Serverへの接続を作成したので、Excelで使用するデータテーブルを選択できます。

SQLスプレッドデザイナーで、データベースをクリックし、テーブルを選択します。

SQLはDesigner AWテーブルリストをスプレッドします

テーブルを選択するとすぐに、テーブル内のデータがExcelシートに入力されます。

SQL Serverテーブル内のすべてのデータを表示し、Excelブックで使用できるようになりました。 SQLスプレッドの本当の力は、Excelから直接SQL Serverのテーブル内のデータを更新する機能です。 たとえば、excelでproductテーブルの価格を直接更新し、変更をSQL Serverに保存できます。

見積の例データベースに保存

例:Sql Serverに接続されたExcelの単純な見積フォーム

前の見積フォームと在庫リストの例に戻りましょう。 これを変更して、見積もりフォームがスプレッドシートの静的リストではなくSQL serverのテーブルからデータを取得するようにします。

この例に従うには、ここから入手できるAdventureWorksサンプルデータベースにアクセスする必要があります。

まず、quote_sampleブックに新しいシートを作成し、Sheet2の既定の名前のままにします–SQL Serverからのデータがここに入力されます。

引用例新しいシートの追加

次に、上記の手順を使用してAdventureWorksデータベースに接続します。

ステップ(1) : 接続の詳細

SQL Serverデータベースへの接続に使用する接続の詳細は次のとおりです:

  • SQL Server名:Andy\SQLExpress
  • データベース名:AdventureWorks
  • テーブルまたはビュー:Production。ステップ（2）：SQL Serverに接続します。

    SQL ServerのProductテーブルに接続し、見積もりワークシートに「Product」シートを入力します。 これを行うには、SQLスプレッド>デザインモードをクリックし、接続の詳細を入力し、適切なデータベース（この場合はAdventureWorks）を展開して、関連するテーブル（この場合は実製品）。

    ステップ(3):生産を選択します。SQL Serverの製品テーブル

    生産をクリックします。リスト内の製品テーブルとデータが自動的に製品シートに入力されます。

    Quote example product table

    このデータをQuoteテンプレートでの検索のソースとして使用できるようになりましたが、その前に、SQLから返されたデータをフィルター処理して、在庫品のみが表示されるようにする方法を見ていきます。

    SQLスプレッドデザイナーのフィルタータブをクリックします。 フィルタタイプオプションの下で、’列’または’カスタムSQL’を選択できます。 在庫のあるアイテムのみを返すSQLクエリを実行する必要があるため、ProductテーブルをProductInventoryテーブルに結合する必要があるため、「カスタムSQL」を選択します。

    Quote example custom filter

    ‘Custom SQL’オプションをクリックすると、SQLクエリを入力できるテキストボックスが表示されます。 私たちのユースケースでは、結合クエリを介して製品のリストを返したいので、サブクエリを使用する必要があります。 そのため、次のものを貼り付ける必要があります:

    Name in (SELECT P.Name AS ProductFROM AdventureWorks.Production.Product AS PJOIN AdventureWorks.Production.ProductInventory AS PI ON P.ProductID = PI.ProductIDJOIN AdventureWorks.Production.Location AS L ON PI.LocationID = L.LocationIDwhere L.Name = 'Finished Goods Storage' AND PI.Quantity > 0GROUP BY P.Name, P.ListPrice)

    カスタムSQLボックスで:

    Quote example custom filter query

    ‘Save’をクリックすると、Productテーブルのデータがフィルターされたレコードに縮小されます。 最後に、見積シートのデータ検証ルックアップで使用された名前付き範囲を更新します:

    Quote example update named range

    また、ユーザーがアイテムを選択したときに見積もりシートが価格を取得するために使用するXLOOKUP関数を更新する必要があります。

    引用例更新ルックアップ関数

    それだけです！ 当社の営業担当者は、現在在庫がある商品の最新の価格に基づいて見積を作成できるようになりました。 営業担当がスプレッドシートを開くたびに、データはSQL Serverから更新されます。

    ボーナス特集！ Excelで価格を更新してSQL Serverに保存

    営業担当が最新の価格と可用性に基づいて見積もりを作成できるように、ExcelをSQL Serverに接続しました。

    さらに一歩進んで、指定されたユーザー（管理者など）がExcelでアイテムの販売価格を直接更新し、更新を”生産”に保存する機能を提供できます。Sql ServerのProduct’テーブル。たとえば、リストの最初のアイテムの価格を$39に変更したい場合。99、ListPriceセルを入力して「データベースに保存」をクリックすることができます–それは簡単です！ もちろん、この機能を特定のユーザーのみにロックダウンしたいと考えており、これはSQLスプレッドを介して簡単に行われます。

    見積もりの例更新価格

    概要

    この記事では、SQLスプレッドアドインを使用してExcelをSQL Serverに接続するのがいかに簡単かを示しました。 また、単純なquotesスプレッドシートでSQLデータベースの最新の販売価格データを使用する方法と、データベース内の価格をExcelから直接更新する方法も示しました。

    SQLスプレッドの試用版をダウンロードし、今日のSQL ServerにExcelを接続します。

    Andy

    Andy McDonald

    Andyは、Sql Server、Excel、Power Query、Power BIなどのツールを使用したデータ分析とプレゼンテーションで、エンジニアリング、財務、IT部門で20年以上働いています。

    は、ExcelとSQL ServerについてのSQLスプレッドと、それら二つを結びつける方法を書いています。

Write a Comment

メールアドレスが公開されることはありません。