Erstellen Sie berechnete Spalten, ohne Formeln zu schreiben

In der Vergangenheit haben wir berechnete Spalten durch Schreiben von Formeln erstellt. In diesem Beitrag werden wir sehen, dass in einigen Fällen das Klicken auf einige Schaltflächen in Power Query eine gute Alternative sein kann.

Zielsetzung

Bevor wir uns mit der Mechanik befassen, wollen wir unser Ziel bestätigen. Nehmen wir an, wir haben eine Tabelle voller Transaktionen wie diese:

Wir möchten einige berechnete Spalten rechts von der SKU-Spalte erstellen. Beispielsweise möchten wir möglicherweise die Monatsnummer der Transaktion, die Summe der Spalten Betrag und Versand, die Anzahl der Verkaufstransaktionen (Typ = S), die Anzahl der Rückerstattungen (Typ = R) und die Produktgruppe ( der in der SKU enthaltene mittlere Code). Etwas wie das:

Wir können alle diese berechneten Spalten hinzufügen, ohne herkömmliche Excel-Formeln zu schreiben. Wir können sie erstellen, indem wir in Power Query auf einige Schaltflächen klicken. Lassen Sie uns die Details untersuchen.

Einzelheiten

Wir werden den Prozess mit den folgenden Schritten durchlaufen:

  • Importieren Sie die Daten in Power Query
  • Erstellen Sie die berechneten Spalten
  • Senden Sie die Ergebnisse an Excel

Lasst uns anfangen.

Importieren Sie die Daten in Power Query

Zuerst müssen wir die Transaktionstabelle in Power Query übernehmen. Da sich unsere Tabelle bereits in Excel befindet, wählen wir die Datentabelle aus und klicken auf Daten> Aus Tabelle / Bereich Befehl. Excel öffnet den Power Query-Editor und zeigt eine Vorschau der Daten an:

Hinweis: In der Praxis können Sie mithilfe der entsprechenden Daten eine Verbindung zu anderen Datenquellen herstellen Daten> Daten abrufen Befehl.

Nachdem sich die Daten in Power Query befinden, können wir auf einige Schaltflächen klicken, um die gewünschten berechneten Spalten zu erstellen.

Erstellen Sie die berechneten Spalten

Wir können die Befehle auf dem verwenden Spalte hinzufügen Registerkarte, um berechnete Spalten in Power Query zu erstellen. Lassen Sie uns die Schritte für jede der berechneten Spalten durchgehen, die wir erstellen möchten.

Monat

Um die Monatsspalte zu erstellen, wählen Sie zunächst die aktuelle Datumsspalte wie folgt aus:

Dann klicken wir auf Spalte hinzufügen> Datum> Monat> Monat Befehl. Bam, wir haben eine neue Monatsspalte wie diese:

Hinweis: Es gibt viele Möglichkeiten, die Sie erkunden können Spalte hinzufügen> Datum Befehl, einschließlich Jahr, Jahresanfang, Jahresende, Monat, Name des Monats, Monatsanfang, Monatsende und mehr.

Gesamt

Erstellen wir nun die Spalte „Gesamt“. Wir möchten die Spalten Betrag und Versand hinzufügen. Der erste Schritt besteht darin, die Spalten Betrag und Versand wie folgt auszuwählen (halten Sie die Umschalt- oder Strg-Taste gedrückt, um mehrere Spalten auszuwählen):

Klicken Sie dann auf Spalte hinzufügen> Standard> Hinzufügen Befehl. Bam… unsere neue Kolumne erscheint:

Hinweis: Es gibt viele Optionen in der Spalte hinzufügen> Standard Befehl, also überprüfen Sie sie auf jeden Fall, da sie nützlich sein können.

Sie können die neue Spalte umbenennen, indem Sie auf die aktuelle Bezeichnung (Addition) doppelklicken und den neuen Namen wie gewünscht eingeben, z Gesamt::

Verkauf

Wir möchten eine neue Spalte erstellen, in der der Gesamtbetrag für Verkaufstransaktionen angezeigt wird. Die Art der Transaktion (Verkauf / Rückerstattung) ist in der Art Säule:

Wenn der Spaltenwert Typ S ist, handelt es sich um eine Verkaufstransaktion, und der Gesamtbetrag soll in unserer neuen Spalte Verkauf angezeigt werden.

Um diese Spalte zu erstellen, verwenden wir die Spalte hinzufügen> Bedingte Spalte Befehl. Im resultierenden Dialog geben wir die gewünschte Logik ein. In unserem Fall lautet die Logik: Wenn der Wert in der Spalte Typ gleich S ist, soll in der neuen Spalte der Wert in der Spalte Gesamt angezeigt werden, andernfalls wird 0 angezeigt:

Hinweis: Mit den Schaltflächen Wert, Ausgabe und Sonst können Sie zwischen der Bezugnahme auf einen bestimmten Wert (z. B. S oder 0) und der Bezugnahme auf eine Spalte (z. B. Gesamt) umschalten.

Wir drücken OK und bam:

Rückerstattung

Wir verwenden grundsätzlich die Spalte hinzufügen> Bedingte Spalte Befehl erneut, um die Spalte Rückerstattung zu erstellen. Wenn der Typwert gleich R ist, möchten wir den Wert in der Spalte Gesamt, andernfalls 0. Bam:

Gruppe

In der letzten Spalte wird der Produktgruppencode angezeigt. Die Produktgruppe ist der mittlere Code in der Spalte SKU. Werfen wir einen Blick auf die Spalte SKU:

Wenn wir uns die Werte in der Spalte SKU ansehen, stellen wir fest, dass jede SKU drei Teile hat, die durch einen Bindestrich getrennt sind. Da jedes SKU-Segment eine variable Anzahl von Zeichen zu enthalten scheint, extrahieren wir den Gruppencode basierend auf den Bindestrich-Trennzeichen.

Nach Auswahl der Spalte SKU verwenden wir die Spalte hinzufügen> Extrahieren> Text zwischen Trennzeichen Befehl.

Beachten Sie das Spalte hinzufügen> Extrahieren Der Befehl enthält viele Optionen. Sie sollten sie daher unbedingt ausprobieren, da sie je nach Ihren Daten nützlich sein können.

Im resultierenden Dialog geben wir die Bindestrich-Trennzeichen ein:

Hinweis: Sie können auf Erweiterte Optionen klicken, um viele zusätzliche Einstellungen anzuzeigen, die möglicherweise nützlich sind.

Wir klicken auf OK und bam:

Wir können auf die Spaltenbezeichnung doppelklicken und unseren gewünschten Spaltennamen eingeben: Gruppe:

Mit unseren berechneten Spalten ist es Zeit, die Ergebnisse an Excel zu senden.

Senden Sie die Ergebnisse an Excel

Um die Ergebnisse an Excel zu senden, verwenden wir die Home> Schließen und laden in Befehl.

Im resultierenden Dialogfeld senden wir die Ergebnisse an eine Tabelle in einem neuen Arbeitsblatt… und bam:

Yay … wir haben es geschafft!

Fazit

Wir haben mehrere berechnete Spalten erstellt, ohne herkömmliche Excel-Formeln zu schreiben. Wir hätten sie mit traditionellen Excel-Formeln erstellen und Funktionen wie MONTH, SUM, IF, MID, FIND, LEN usw. verwenden können. Das Klicken auf Schaltflächen in Power Query ist jedoch eine bequeme Alternative.

Das wirklich Coole an dieser Lösung ist außerdem, dass wir im nächsten Zeitraum, wenn wir eine neue oder aktualisierte Transaktionstabelle haben, nur mit der rechten Maustaste auf unsere Ergebnistabelle klicken und auf Aktualisieren klicken müssen. Power Query verarbeitet die Spalten erneut und aktualisiert die Ergebnistabelle entsprechend.

Wenn Sie andere Optionen oder coole Power Query-Techniken haben, teilen Sie diese bitte mit, indem Sie unten einen Kommentar veröffentlichen… danke!

Beispieldatei

Source link

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.