Verbinden Sie Excel mit SQL Server: 3 einfache Schritte

In einem früheren Artikel haben wir darüber gesprochen, warum Sie sowohl Excel als auch SQL in Ihren Datenverwaltungslösungen benötigen, aber wie verbinden Sie Excel mit SQL Server? In diesem Artikel zeigen wir Ihnen die Schritte und heben einen Hauptvorteil der Verwendung eines Excel-Add-Ins wie SQL Anywhere hervor.

Aber zuerst werden wir die Vorteile der Verbindung von Excel mit Daten in SQL Server erneut beschreiben.

Vorteile der Verbindung von Excel mit SQL Server

Um die Vorteile der Verbindung von Excel mit SQL Server zu demonstrieren, schauen wir uns ein Beispiel an.

Diese Tabelle wird von Vertriebsmitarbeitern eines produzierenden Unternehmens verwendet, um Angebote für Kunden vorzubereiten. Jeder Vertriebsmitarbeiter verfügt über eine Kopie der Tabelle, die eine Liste der Inventargegenstände und deren Preise enthält. Die Liste der Inventargegenstände und Preise wird von einem Administrator aus dem Finanzsystem extrahiert. Das Angebotsblatt verwendet einige Nachschlageformeln zur Datenvalidierung, um die Preise abzurufen, wenn ein Vertriebsmitarbeiter einen Artikel aus der Dropdown-Liste auswählt.

Dies funktioniert gut, bis die Organisation die Preise aktualisieren muss. In diesem Fall müsste jeder Benutzer die neuen Preise kopieren und in die Tabelle einfügen (oder eine neue Kopie der Tabelle verwenden). Wenn der Vertriebsmitarbeiter das Angebot erstellt, weiß er auch nicht, ob die Artikel tatsächlich auf Lager sind.

Eine bessere Lösung wäre, die Excel-Tabelle mit der Inventartabelle in der Datenbank des Finanzsystems zu verbinden. Jedes Mal, wenn der Vertriebsmitarbeiter die Angebotstabelle öffnet, aktualisiert er die Preisdaten aus der Live-Datenbank. Wir können noch einen Schritt weiter gehen und die Verbindung zur Datenbank herstellen, um auch die aktuellen Lagerbestände wiederherzustellen, sodass Angebote für tatsächlich auf Lager befindliche Artikel erstellt werden können.

Zusammenfassend sind die Vorteile der Verbindung von Excel mit SQL Server:

• Wir haben Zugriff und können aktuelle Informationen anzeigen
• Wir verwenden eine einzige vertrauenswürdige Datenquelle
• Wir können die Quelldaten mithilfe einfacher Excel-Funktionen wie Pivot-Tabellen im laufenden Betrieb analysieren oder aggregieren

Verbinden Sie Excel mit einer SQL Server-Datenbank: 3 einfache Schritte

Der einfachste Weg, Excel mit einer SQL Server-Datenbank zu verbinden, ist mit SQL Server. SQL Spreads ist ein einfaches Excel-Add-In.

Der SQL Server-Download kann hier gefunden werden, und der Installationsprozess wird hier beschrieben.

Schritt 1: Sammeln Sie die Datenbankverbindungsdetails

Sie benötigen die folgenden Informationen, um Excel mit SQL Server zu verbinden, also holen Sie sich die Details als ersten Schritt:

• SQL Servername
• Datenbankname
• Tabelle oder Ansicht, die die benötigten Informationen enthält
• Anmeldedaten (wenn keine Windows-Authentifizierung verwendet wird)

Die Form des SQL Server-Namens hängt davon ab, ob SQL Server lokal, in einem Netzwerk oder an einem Remotestandort installiert ist (beachten Sie, dass dies derselbe Name ist wie bei der Verbindung mit SQL Server in SQL Server Management Studio).

Typischer Servername	Groß- und Kleinschreibung
Localhost	Wenn SQL Server lokal installiert ist
localhost\sqlexpress	Wenn SQL Server Express lokal installiert ist
Domain\Servername	Wenn SQL Server in einem Unternehmensnetzwerk installiert ist
IP-Adresse	Wenn SQL Server an einem Remotestandort installiert ist

Schritt 2: Verbindung mit SQL Server herstellen

Sobald SQL Server installiert ist, wird es als neue Registerkarte im Menüband angezeigt.

Klicken Sie im Bereich SQL Spreads Designer auf der rechten Seite auf die Schaltfläche Bearbeiten, um das Dialogfeld SQL Server-Verbindung zu öffnen.

Geben Sie den Namen Ihres SQL Servers in das Feld SQL Servername ein:

Wählen Sie aus, ob Sie eine Verbindung über Ihre Windows-Anmeldung (Windows-Authentifizierung) herstellen oder einen Benutzernamen und ein Kennwort eingeben sollen (SQL Server-Authentifizierung). Die Windows-Authentifizierung ist die sicherere der beiden Optionen (weitere Informationen finden Sie hier).

Klicken Sie auf OK. SQL Server versucht, eine Verbindung zur Datenbank herzustellen. Wenn die Verbindung erfolgreich ist, werden Ihre Datenbanken im SQL Server Designer-Bereich angezeigt.

Schritt 3: Wählen Sie die entsprechende Tabelle in SQL Server

Nachdem wir die Verbindung von Excel zu SQL Server hergestellt haben, können wir auswählen, welche Datentabelle wir in Excel verwenden möchten.

Klicken Sie im SQL Server Designer auf die Datenbank und wählen Sie dann Ihre Tabelle aus.

Sobald Sie eine Tabelle auswählen, werden die Daten in der Tabelle in der Excel-Tabelle ausgefüllt.

Sie können jetzt alle Daten in Ihrer SQL Server-Tabelle anzeigen und in Ihrer Excel-Arbeitsmappe verwenden. Die wahre Stärke von SQL Server ist die Möglichkeit, die Daten in der Tabelle in SQL Server direkt aus Excel zu aktualisieren. Zum Beispiel kann ich die Preise in der Produkttabelle direkt in Excel aktualisieren und die Änderungen wieder in SQL Server speichern!

Beispiel: Ein einfaches Angebotsformular in Excel, das mit SQL Server verbunden ist

Kehren wir zu unserem früheren Beispiel des Angebotsformulars und der Inventarliste zurück. Wir werden dies ändern, damit das Angebotsformular seine Daten aus der Tabelle im SQL Server anstelle einer statischen Liste in der Tabelle abruft.

Wenn Sie diesem Beispiel folgen möchten, benötigen Sie Zugriff auf die AdventureWorks-Beispieldatenbank, die Sie hier abrufen können.

Zuerst erstellen wir ein neues Blatt in der quote_sample–Arbeitsmappe und belassen es mit dem Standardnamen Sheet2 – die Daten von SQL Server werden hier ausgefüllt.

Als nächstes stellen wir mithilfe der oben beschriebenen Schritte eine Verbindung zur AdventureWorks-Datenbank her.

Schritt (1) : Verbindungsdetails

Hier sind die Verbindungsdetails, die ich verwenden werde, um eine Verbindung zu meiner SQL Server-Datenbank herzustellen:

• SQL Servername: Andy \SQLExpress
• Datenbankname: AdventureWorks
• Tabelle oder Ansicht: Produktion.Produkt
• Anmeldedaten: Windows-Authentifizierung

Schritt (2): Verbindung zu SQL Server herstellen

Wir möchten eine Verbindung zur Produkttabelle in SQL Server herstellen und das Blatt ‚Produkt‘ im Zitat-Arbeitsblatt ausfüllen. Dazu klicken wir auf SQL Server > Entwurfsmodus und geben die Verbindungsdetails ein und erweitern die richtige Datenbank (in unserem Fall AdventureWorks), damit wir die relevante Tabelle auswählen können (in unserem Fall Production.Produkt).

Schritt (3): Wählen Sie die Produktion.Produkttabelle in SQL Server

Klicken Sie auf die Produktion.Produkttabelle in der Liste und die Daten werden automatisch in das Produktblatt gefüllt.

Wir können diese Daten jetzt als Quelle für unsere Suche in der Angebotsvorlage verwenden, aber bevor wir dies tun, werden wir uns ansehen, wie die von SQL zurückgegebenen Daten gefiltert werden, sodass nur Artikel auf Lager angezeigt werden.

Klicken Sie im SQL Server Designer auf die Registerkarte Filter. Unter der Option Filtertyp können wir ‚Spalte‘ oder ‚Benutzerdefiniertes SQL‘ auswählen. Wir werden ‚Benutzerdefiniertes SQL‘ auswählen, da wir eine SQL-Abfrage ausführen müssen, die nur Artikel auf Lager zurückgibt, und das bedeutet, dass wir die Produkttabelle mit der ProductInventory-Tabelle verknüpfen müssen.

Wenn Sie auf die Option ‚Benutzerdefiniertes SQL‘ klicken, wird ein Textfeld angezeigt, in das Sie die SQL-Abfrage eingeben können. Für unseren Anwendungsfall möchten wir eine Liste von Produkten über eine Join-Abfrage zurückgeben. Also müssen wir Folgendes einfügen:

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)

im Feld Benutzerdefiniertes SQL:

Wenn Sie auf ‚Speichern‘ klicken, werden die Daten in der Produkttabelle auf die gefilterten Datensätze reduziert. Schließlich werden wir den benannten Bereich aktualisieren, der von den Datenvalidierungssuchen im Angebotsblatt verwendet wurde:

Und wir müssen auch die XLOOKUP–Funktion aktualisieren, die das Angebotsblatt verwendet, um den Preis zu erhalten, wenn ein Benutzer einen Artikel auswählt – es muss auf die neue Tabelle 2 verweisen, die die Daten von SQL Server enthält.

Das war’s! Unsere Vertriebsmitarbeiter können jetzt Angebote basierend auf aktuellen Preisen für Artikel erstellen, die derzeit auf Lager sind. Jedes Mal, wenn der Vertriebsmitarbeiter die Tabelle öffnet, werden die Daten von SQL Server aktualisiert.

Bonus-Funktion! Preise in Excel aktualisieren und in SQL Server speichern

Wir haben Excel mit SQL Server verbunden, damit unsere Vertriebsmitarbeiter Angebote basierend auf aktuellen Preisen und Verfügbarkeiten erstellen können.

Wir können noch einen Schritt weiter gehen und einem bestimmten Benutzer (z. B. einem Administrator) die Möglichkeit geben, die Verkaufspreise der Artikel direkt in Excel zu aktualisieren und die Aktualisierungen wieder in der Produktion zu speichern.Produkt‘ Tabelle in SQL Server.

Zum Beispiel, wenn ich den Preis des ersten Artikels in der Liste auf $ 39 ändern wollte.99, ich kann die ListPrice Zelle eingeben und auf ‚In Datenbank speichern‘ klicken – so einfach ist das! Natürlich möchten wir, dass diese Funktion nur für bestimmte Benutzer gesperrt wird, und dies ist einfach über SQL Anywhere möglich.

Zusammenfassung

In diesem Artikel haben wir gezeigt, wie einfach es ist, Excel mit dem SQL Server-Add-In mit SQL Server zu verbinden. Wir haben auch ein Beispiel durchgesehen, um zu zeigen, wie eine einfache Angebotstabelle aktuelle Verkaufspreisdaten aus einer SQL-Datenbank verwenden kann und wie die Preise in der Datenbank auch direkt aus Excel aktualisiert werden können.

Laden Sie die Testversion von SQL Server herunter und verbinden Sie Excel noch heute mit SQL Server.