Excel-Kreuztabellenextraktion, Microsoft Query KO alle Funktionen

Die erste Reaktion vieler Partner beim tabellenübergreifenden Extrahieren von Daten sind Funktionen wie VLOOKUP oder die INDEX+SMALL+IF-Formel. Wenn Sie mehrere Datenspalten extrahieren, ist Microsoft Query, das von vielen Leuten lange Zeit in der Ecke gelassen wurde, tatsächlich der König! Es ist nicht nur einfach zu bedienen und löst „Eins-zu-viele“-Probleme, sondern die generierte Ergebnistabelle kann auch eine dynamische Verknüpfung mit der Datenquelle herstellen. Wenn sich die Datenquelle ändert, werden die Ergebnisse dynamisch aktualisiert!

Heute möchte ich Ihnen eine Funktion vorstellen, die selten verwendet wird, aber wundersame Auswirkungen hat – Microsoft Query, um Ihnen bei der Lösung der „Eins-zu-viele“-Datenextraktion aus zwei Tabellen zu helfen oder das Problem zu lösen Verwenden einer Tabelle zum Abgleichen mit einer anderen Die Praxis, bestimmte Daten aus einer Tabelle zu generieren.

Wie im Bild unten gezeigt, gibt es in derselben Arbeitsmappe zwei Arbeitsblätter. Die „Abteilungspersonalinformationstabelle“ listet die Namen der Mitarbeiter in jeder Abteilung und die entsprechenden Vorgesetzten auf, und die „Provinzverkaufsdatentabelle“ listet die Verantwortlichkeiten auf Jeder Mitarbeiter mehrerer Provinzen und dreimonatige Verkaufsdaten der entsprechenden Provinzen. Nun ist es erforderlich, die beiden Tabellen anhand der Namensspalte zu einer Tabelle zusammenzufassen.

Originaluhr

Erforderliche Ergebnisse

Wie verwende ich Microsoft Query?

SCHRITT 01 Aktivieren Sie Microsoft Query und laden Sie Daten

(1) Erstellen Sie eine neue Arbeitsmappe, klicken Sie auf das Dropdown-Menü „Aus anderen Quellen“ in der Gruppe „Externe Daten abrufen“ unter [Daten ]-Registerkarte von Microsoft Abfrage“.

Klicken Sie im Fenster [Datenquelle auswählen] unter der Option „Datenbank“ auf „Excel-Dateien“, aktivieren Sie unten „[Abfrageassistent] zum Erstellen/Bearbeiten von Abfragen verwenden“ und klicken Sie auf „OK“.

Suchen Sie den Speicherort der Datenquelle im Verzeichnis auf der rechten Seite des Fensters [Arbeitsmappe auswählen], suchen Sie die Datei im Datenbanknamen auf der linken Seite und klicken Sie auf OK

(2) Manchmal wird das System dies tun Daraufhin wird das folgende Fenster angezeigt: „Die Datenquelle ist nicht enthalten. „Sichtbare Tabellen“, machen Sie sich darüber keine Sorgen, klicken Sie auf „OK“. Klicken Sie unten auf die Schaltfläche, öffnen Sie das Fenster [Tabellenoptionen] auf der rechten Seite und aktivieren Sie „Systemtabellen“. Klicken Sie auf „OK“.

Das Arbeitsblatt in der Datenquelle wird im Fenster [Abfrageassistent] angezeigt. Dies liegt daran, dass Excel aufruft

Wählen Sie als Nächstes die beiden Arbeitsblätter aus und klicken Sie auf die Schaltfläche „>“, um die „Verfügbare Tabellen und Spalten“ hinzuzufügen Klicken Sie rechts auf „Spalten in den Abfrageergebnissen“ und klicken Sie auf „Schritt“. Es erscheint ein weiteres Fenster mit der Meldung „Der Abfrageassistent kann nicht fortfahren, da das Formular nicht mit Ihrer Abfrage verknüpft werden kann.“ Sie müssen bei Microsoft sein Ziehen Sie Felder zwischen Tabellen in Query, um sie manuell zu verknüpfen. „Machen Sie sich darüber keine Sorgen, klicken Sie auf OK.

SCHRITT 02

Daten entsprechend den erforderlichen Elementen abgleichen

Zu diesem Zeitpunkt gelangen wir in das Microsoft Query-Fenster mit einer Excel-ähnlichen Menüleiste oben und ein Tabellenbereich in der Mitte, der die beiden aktuell hinzugefügten Tabellen und die entsprechenden Felder zeigt. Der Datenbereich unten ist das Ergebnis der Zusammenführung der beiden Tabellen. Das Ergebnis des Datenbereichs ist derzeit unübersichtlich Geben Sie keine Tabellenadditionsbeziehung an. Die beiden Tabellen stehen über die Namensspalte in einer Eins-zu-Eins-Korrespondenz.

(1) Wählen Sie mit der Maus den „Namen“ in der „Personalinformationstabelle der Abteilung“ auf der linken Seite aus, ziehen Sie ihn auf den „Namen“ in der „Provinzverkaufsdatentabelle“ in der rechten Tabelle und lassen Sie dann die Maustaste los . Zu diesem Zeitpunkt erscheint zwischen den „Namen“-Feldern der beiden Tabellen eine Verbindungslinie mit kleinen Knoten an beiden Enden. Der Datenbereich unten wird sofort aktualisiert.

(2) Da es zwei Spalten mit demselben Namen gibt, wählen wir eine der Spalten aus und klicken in der Menüleiste unter [Datensatz] auf „Spalte löschen“.

SCHRITT 03 Zurückgeben der Ergebnisdaten an das Excel-Arbeitsblatt

Als letztes müssen Sie die Ergebnisse an EXCEL zurückgeben.

(1) Klicken Sie auf die Schaltfläche links neben „SQL“ in der Menüleiste, um die Daten an Excel zurückzugeben.

(2) Das Fenster [Daten importieren] erscheint in Excel. Wir wählen, es als „Tabelle“ anzuzeigen und in das vorhandene Arbeitsblatt einzufügen.

Das zurückgegebene Ergebnis lautet wie folgt:

In diesen einfachen 3 Schritten haben wir den erforderlichen Datenabgleich abgeschlossen und eine neue Datentabelle generiert.

Extra Freude

Wir haben festgestellt, dass die von Microsoft Query generierten Daten eine super Tabelle sind und Sie auch direkt eine Pivot-Tabelle oder ein Pivot-Diagramm erstellen können.

Gleichzeitig ist diese Tabelle dynamisch mit der Datenquelle verknüpft. Wenn wir beispielsweise die Originaldaten ändern, klicken Sie zum Schließen auf Speichern.

Klicken Sie mit der rechten Maustaste auf die zurückgegebenen Ergebnisse, um sie zu aktualisieren.

Auf diese Weise werden die Daten synchronisiert.

Anwendungsbedingungen

Es ist zu beachten, dass bei der Verwendung dieser Methode die Standardisierung der Datenquelle gewährleistet sein muss. Es ist erforderlich, dass das Arbeitsblatt keine Daten enthalten darf, die keinen Bezug zur Datenquelle haben, und die erste Zeile der Tabelle muss aus Spaltenüberschriften bestehen. Wenn Sie eine dynamische Verknüpfung implementieren möchten, können die Namen und Speicherorte der Arbeitsmappe und des Arbeitsblatts nicht geändert werden.

Wie ist es, hast du es gelernt? Ist es einfacher als PQ oder einfacher als eine Funktion?

Verwandte Lernempfehlungen: Excel-Tutorial

Das obige ist der detaillierte Inhalt vonExcel-Kreuztabellenextraktion, Microsoft Query KO alle Funktionen. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!