Tabellenverknüpfung - Excel Hands on - Blog - HSC-Consulting, Controlling, Excel & Coaching

Direkt zum Seiteninhalt

Hauptmenü:

Excel, MS Query als Alternative zum SVERWEIS

Herausgegeben von in Excel Standard ·
Tags: MSQueryExcelSVEREISTabellenverknüpfungRelation
 
MS Query – eine Alternative zum SVERWEIS?
 
Verknüpfen Sie Daten anstelle mit dem SVEWEIS über MS Query und sehen Sie was für Vorteile das bietet.
 
 
Der SVERWEIS ist neben Index und Vergleich der Klassiker Daten miteinander zu verknüpfen. In den neueren Excel-Versionen bietet sich das Datenmodell und PowerPivot als Lösung für anspruchsvolle Aufgaben an.
 
Mit MS Query können Sie verschiedene Excel-Tabellen zu einer zusammenführen und Sie erzeugen dabei noch automatisch eine strukturierte Tabelle mit einem eigenen Namen. Diese Benennung entfaltet ihre Stärken insbesondere bei Pivot-Auswertungen oder auch Auswertungen mit Datenbankfunktionen.
 
Die Daten enthalten Informationen über die Essenausgabe in der Kantine. Da jeder Mitarbeiter nur Anspruch auf einen täglich einmaligen Zuschuss hat, muss dies im Controlling überprüft werden. Der Verbindungsschlüssel zwischen den Kantinendaten und dem Personalstammsatz ist die Cardnumber (Card Number).

 
Abbildung 1: Schematische Lösung der Datenverknüpfung und Auswertung in einer PivotTable

 

Die Daten per Query zusammenführen
 
Öffnen Sie die Datei QueryKantine_UEB_001.xlsx. Sie finden dort die beiden Tabellenblätter Basisdaten und Ergaenzung. Wechseln Sie in ein neues, leeres Tabellenblatt und beginnen mit der Verknüpfung.
  • Wählen Sie In Excel den Befehl DATEN/aus anderen Quellen/Aus Microsoft Query
  • Folgendes Dialogfeld erscheint:
 
 
Abbildung 2: Das Dialogfeld in dem Sie den Query erstellen

 
  • Wählen Sie den Listeneintrag Excel Files und bestätigen OK
  • Im erscheinenden Dialogfeld wählen Sie den Ort und die betreffende Datei aus
 
 
Abbildung 3: Wählen Sie die benötigte Arbeitsmappe aus


  • Auf Grund der Fehlermeldung Keine auswählbare Tabelle gefunden öffnen Sie die Tabellenoptionen und aktivieren das Kontrollkästchen um die Systemtabellen anzuzeigen

 
Abbildung 4: Kontrollkästchen aktivieren um die Systemtabellen anzuzeigen


 
  • Bestätigen Sie OK, daraufhin werden die Tabellen angezeigt.
  • Übernehmen Sie alle Felder in die Abfrage

 
 
Abbildung 5: Alle relevanten Felder aus den beiden Tabellen werden in die Abfrage übernommen


 
  • Klicken Sie jetzt auf Abbrechen und gelangen daraufhin nach einer Abfrage ob Sie den Query weiter bearbeiten wollen im Query-Assistenten

 
Abbildung 6:  Bearbeitung der Abfrage im Query-Assistenen (Ausschnitt)


 
Es wäre nicht notwendig alle Felder in die Abfrage zu übernehmen. Wenn Sie alle Felder übernehmen stehen Ihnen alle Auswertemöglichkeiten offen.
 
  • Verbinden Sie die beiden Tabellen, in dem Sie das Schlüsselfeld CardNumber aus der Tabelle Basisdaten auf das gleichnamige Feld in der Tabelle Ergaenzungen ziehen. Eine Verbindung (Relation) entsteht.
 
Im obigen Beispiel wurden durch die Markierung * alle Felder in die Anzeige übernommen.
 
Wenn keine Felder in die Abfrage übernommen wurden, fügen Sie per Doppelklick auf die Feldnamen die Felder in die Abfrage ein, die Sie unbedingt benötigen.
 
Falls die Daten noch nicht angezeigt werden, klicken Sie auf die Befehlsschaltfläche mit dem Ausrufezeichen (!).
 
  • Zum Abschluss wählen Sie den Befehl Daten/Daten an Excel zurück geben (oder das entsprechende Befehlssymbol)
  • Im erscheinenden Dialogfeld Daten importieren wählen Sie die Option PivotTable-Bericht und Neues Arbeitsblatt und bestätigen OK
 
 
Abbildung 7: Auswahl der Darstellungsoptionen im Dialogfeld Daten importieren


 
  • Die leere PivotTable wird erstellt und zeigt Ihnen in der PivotTable-Felderliste alle Felder der Tabellen an.
  • Wählen Sie die Felder aus die zur Auswertung benötigt werden (vgl. Abbildung 8)

 
 
Abbildung 8: Die PivotTable-Felderliste mit der Anordnung der Felder und die Auswertung in einer PivotTable (Ausschnitt)


 
Hinweis:
Das Feld CardNumber wurde in den Filterbereich als auch in den Layoutbereich Werte gezogen. Um die Anzahl der Mitarbeiter zu berechnen stellen Sie in den Wertfeldeinstellungen den Berechnungsmodus von Summe auf Anzahl um (vgl. Abbildung 9)
 
Abbildung 9: Berechnung der Anzahl der in Anspruch genommenen Vergütung


 
  • Formatieren Sie das Berichtslayout im Tabellenformat und schalten Sie Teilergebnisse für die Zeilenanordnung aus. Zusätzlich wurde das Wertfeld Anzahl von Card Number umbenannt in .Card Number.

 
Rückgabe der Query-Daten in ein Tabellenblatt
 
Im vorausgehenden Beispiel habe wir die Daten direkt bei der Rückgabe in einer PivotTable aufbereitet (vgl. Abbildung 8)
 
In einem anderen Fall können wir die Daten auch als Tabelle in Excel zurück geben und auf diese Tabelle aufsetzend die PivotTable erstellen.
 
Abbildung 10: Rückgabe der Daten als strukturierte Tabelle mit eigenem Namen


 
Im ersten Fall greifen wir mit der PivotTable direkt auf den Query zu und erstellen die PivotTable.
 
Im anderen Fall, wenn wir die Daten als Tabelle zurückgeben, können sie für unterschiedliche Auswertungen als Basis dienen. Bei einer Tabellenrückgabe erstellt Excel automatisch eine  strukturierte Tabelle die mit einem speziellen (eigenen) Namen versehen wird. Beim Erstellen der PivotTable wird automatisch auf diesen Namen der strukturierten Tabelle zugegriffen. Der Vorteil, die strukturierte Tabelle passt sich immer automatisch an die Datenmenge an. Daher entfällt die händische Überwachung der verarbeiteten Datenmenge. Siehe dazu auch den Blogbeitrag strukturierte Tabelle.
 
 
Die vergleichbare Auswertung mit SVERWEIS finden Sie in meinem Buch:
 
Excel 2010 (2013) Pivot-Tabellen Das Praxisbuch im Verlag von Microsoft Press / OReilly
______
HSC




 








Zurück zum Seiteninhalt | Zurück zum Hauptmenü