Erstellen von berechneten Spalten und Zeilen mit Excel-Formeln

Mit Excel-Formeln können Sie berechnete Spalten und Zeilensummen hinzufügen, die im ursprünglichen QlikView Dokument oder der ursprünglichen Qlik Sense App nicht vorhanden sind.

Ihre Aufgaben

Sie führen folgende Schritte aus:

1. Sie fügen Zeilensummen mit der SUM-Funktion hinzu.
2. Sie fügen berechnete Spalten hinzu. Dafür stehen Ihnen zwei verschiedene Möglichkeiten zur Verfügung:

1. Excel-Tabellen: Sie erstellen eine Tabelle mithilfe der Excel-Tabellenwerkzeuge. Diese Methode führt zu einer besseren Leistung bei der Berichterstellung, erfordert jedoch fundiertere Excel-Kenntnisse. Wir empfehlen Ihnen diesen Ansatz.

2. Ebenen: Sie fügen Feld-Tags in eine Ebene ein und fügen anschließend Excel-Formeln hinzu. Das lässt sich leicht umsetzen; der Einsatz von Ebenen kann sich jedoch auf die Leistung bei der Berichterstellung auswirken.

Auf dieser Seite wird gezeigt, wie Sie Spalten mithilfe beider Ansätze berechnen können. Zur Demonstration wird eine dritte Spalte berechnet, indem die Werte in einer Spalte von den Werten in einer anderen Spalte subtrahiert werden. Die abgeleiteten Werte der dritten Spalte werden daraufhin durch die entsprechenden Werte aus einer der beiden ursprünglichen Spalten geteilt.

In diesem Tutorial werden QlikView Daten verwendet, die Sie in Beispieldateien finden. Sie können auch Ihre eigenen QlikView oder Qlik Sense Daten verwenden.

Info zu Berechnungen

Wenn Sie die Berichterstellungszeit minimieren möchten, können Sie gewünschte Berechnungen zum QlikView oder Qlik Sense Ladeskript hinzufügen.

In diesem Beispiel werden einfache Excel-Formeln verwendet, aber Sie können bei Bedarf auch komplexere Formeln einfügen. Sie können auch Qlik-Formeln einfügen. Weitere Informationen dazu finden Sie unter: Qlik Sense und QlikView Formeln.

Hinzufügen einer Excel-Tabelle mit berechneten Spalten und Summen

Dies ist die erste Möglichkeit, berechnete Spalten zu erstellen. Sie fügen das QlikView-Quellenobjekt zum Tabellen-Knoten hinzu und erstellen anschließend eine Excel-Tabelle.

Hinzufügen der Tabelle

Gehen Sie folgendermaßen vor:

1. Erstellen Sie einen neuen Excel-Bericht oder öffnen Sie einen bestehenden.

2. Klicken Sie mit der rechten Maustaste auf den Knoten Tabellen und wählen Sie Objekte hinzufügen aus.

3. Wählen Sie in der Liste ein Diagramm aus. Fügen Sie beispielsweise Top 10 Products hinzu. Klicken Sie auf OK.

   

4. Klicken Sie links auf +, um den Tabellenknoten CH184_Table - Top 10 Products zu erweitern.
5. Wählen Sie nacheinander die einzelnen Spalten unter CH184_Table - Top 10 Products aus und deaktivieren Sie Keep Source Formats.

6. Halten Sie die Strg-Taste gedrückt und wählen Sie die Tabellenfelder aus, die Sie zum Arbeitsblatt hinzufügen möchten.

   Beispiel: Product Name, Total Sales und Gross Profit. Ziehen Sie sie in eine leere Zeile mit Zellen.

   

7. Wählen Sie die Zellen mit der Überschrift und den Feld-Tags und eine weitere darunter liegende leere Zeile aus.
8. Öffnen Sie die Registerkarte Einfügen in der Excel-Menübandleiste und klicken Sie anschließend auf Tabelle.

9. Achten Sie darauf, dass die Markierung Tabelle hat Überschriften aktiviert ist, und klicken Sie anschließend auf OK.

   

10. Klicken Sie im linken Fenster auf Extras. Ziehen Sie ein deleterow-Tag in eine Zelle der leeren Zeile unter der Tabelle.

    

    Dadurch wird die leere Zeile aus dem letztlich erstellten Bericht entfernt.

Hinzufügen der berechneten Spalte

Gehen Sie folgendermaßen vor:

1. Wählen Sie die erste leere Zelle rechts von den Feld-Tags aus.

2. Fügen Sie die Formel =[@[Total Sales]]-[@[Gross Profit]] ein.

   Excel fügt diese neue Spalte automatisch zur Tabelle hinzu.

   

3. Geben Sie eine Spaltenüberschrift ein. Beispiel: Costs.

4. Verwenden Sie dann das Excel-Tabellenformatierungswerkzeug, um Formatierung anzuwenden.

Hinzufügen von Summen

Gehen Sie folgendermaßen vor:

1. Wählen Sie eine leere Zelle unterhalb der Tabelle aus. Beispiel: D8.

2. Geben Sie eine SUM-Formel in die Zelle ein. Der Bereich der SUM-Funktion muss Folgendes umfassen:

   • Die Adresse der Zelle in der Zeile, die das Starttag der Ebene enthält.
   • Die Zelle mit dem Feld-Tag.
   • Die Adresse von mindestens einer leeren Zelle unmittelbar darunter.

   In diesem Beispiel lautet die Funktion: =SUM(D4:7)

3. Klicken Sie auf diese Formel und ziehen Sie sie zu E8 und F8.

4. Wenden Sie Excel-Formatierungswerkzeuge auf die Elemente der Zeile an.

   Beispiel: Die Summen Total Sales und Gross Profit können das Format Währung aufweisen.

   

Vorschau für den Bericht

Gehen Sie folgendermaßen vor:

1. Klicken Sie auf Vorschau.

   Excel wird gestartet und zeigt Ihren Bericht an.

2. Sie sehen eine Tabelle mit der einen Spalte, die Ihre berechneten Ergebnisse enthält. Die untere Zeile enthält die Spaltensummen.

   

3. Klicken Sie auf Speichern und schließen, um die Vorlage zu speichern, und schließen Sie den Vorlageneditor.

Verwenden von Ebenen zum Erstellen einer Tabelle mit berechneten Spalten und Summen

Hierbei handelt es sich um die zweite Möglichkeit, berechnete Spalten zu erstellen, bei der Sie Ebenen verwenden. Weitere Informationen zu Ebenen finden Sie unter: Excel-Berichte mit Ebenen.

Hinzufügen der Ebene

Gehen Sie folgendermaßen vor:

1. Erstellen Sie einen neuen Excel-Bericht oder öffnen Sie einen bestehenden.
2. Klicken Sie mit der rechten Maustaste auf den Knoten Ebenen und wählen Sie Ebenen hinzufügen aus.

3. Wählen Sie in der Liste ein Diagramm aus. Fügen Sie beispielsweise Top 10 Products hinzu. Klicken Sie auf OK.

   

4. Klicken Sie auf den Knoten CH184_Level - Top 10 Products und ziehen Sie ihn in drei leere, vertikal ausgerichtete Zellen.

   

5. Klicken Sie links auf +, um den Ebenenknoten CH184_Level - Top 10 Products zu erweitern.
6. Wählen Sie nacheinander die einzelnen Spalten unter CH184_Level - Top 10 Products aus und deaktivieren Sie Keep Source Formats.

7. Halten Sie die Strg-Taste gedrückt und wählen Sie die Ebenenfelder aus, die Sie zum Arbeitsblatt hinzufügen möchten.

   Beispiel: Product Name, Total Sales und Gross Profit. Ziehen Sie sie in eine leere Zeile mit Zellen zwischen den Ebenen-Tags.

   

8. Formatieren Sie diese Felder nach Ihren Wünschen. Formatieren Sie beispielsweise Total Sales als Währung.

Hinzufügen von Formeln und Überschriften

1. Wählen Sie die erste leere Zelle rechts von den Feld-Tags aus. In diesem Beispiel ist dies F4.
2. Klicken Sie auf die Zelle, geben Sie = ein und klicken Sie anschließend auf die Zelle Total Sales.

3. Geben Sie - ein, klicken Sie auf die Zelle Gross Profit und drücken Sie die Eingabetaste. Damit werden die Kosten berechnet.

   Wenn Sie eine Formel zwischen Ebenen-Tags einfügen, kopiert Qlik NPrinting sie in jede Zeile der endgültigen Tabelle.

   Sie können die Anzeige von error #VALUE! in der Vorlage ignorieren. Der Fehler tritt auf, weil die Excel-Formel Zellen referenziert, die Feld-Tags enthalten.

4. Geben Sie in Zelle G4 den Wert =F5/D5 ein. Damit werden die Kosten durch den Umsatz dividiert.

5. Formatieren Sie die Formelzellen nach Wunsch mithilfe der Excel-Formatierung.

   Beispiel: Formatieren Sie Kosten als Währung und Kosten/Umsatz als Prozentsatz.

6. Geben Sie Überschriften für jede Spalte in einer Zeile ein, die ein oder zwei Zeilen oberhalb der Zeile mit dem Starttag der Ebene Top 10 Products liegt.

   Formatieren Sie diese Überschriften wie gewünscht.

   

7. Wählen Sie eine Zelle unter dem Endtag der Ebene </Top 10 Products_Level> so aus, dass mindestens eine leere Zelle dazwischen liegt.

8. Geben Sie in Zelle D8 eine Excel-SUM-Formel ein, um eine Zeile mit Spaltensummen zu erstellen. Der Summenbereich sollte Folgendes umfassen:

   1. Die Adresse der Feld-Tag-Zelle.
   2. Die Adresse der leeren Zelle darunter.

   In diesem Beispiel lautet die Funktion: =SUM(D4:D6)

9. Klicken Sie auf diese Formel und ziehen Sie sie zu E8 und F8.

10. Wenden Sie Excel-Formatierungswerkzeuge auf die Elemente der Zeile an.

    Beispiel: Die Summen Total Sales und Gross Profit können als Währung formatiert werden.

    

Vorschau für den Bericht

Gehen Sie folgendermaßen vor:

1. Klicken Sie auf Vorschau.

   Excel wird gestartet und zeigt Ihren Bericht an.

2. Sie sehen eine Tabelle mit zwei neuen Spalten, die Ihre berechneten Ergebnisse enthalten. Die untere Zeile enthält die Spaltensummen.

   

3. Klicken Sie auf Speichern und schließen, um die Vorlage zu speichern, und schließen Sie den Vorlageneditor.