Pivot-Tabellenformel in Excel (Inhaltsverzeichnis)
- Pivot-Tabellenformel in Excel
- Benutzerdefiniertes Feld zur Berechnung des Gewinnbetrags
- Erweiterte Formel im berechneten Feld
Pivot-Tabellenformel in Excel
Pivot Table ist ein Tool, mit dem wir große Datenbereiche analysieren können. Wir können viele andere Dinge analysieren, interpretieren und tun, ohne uns den Kopf und den Schweiß zu brechen. Es kann fast alles geben, was sich in den Quelldaten befindet.
Wenn einige der Informationen in den Quelldaten enthalten sind, müssen wir diese möglicherweise selbst berechnen. Wenn wir beispielsweise einen Gesamtumsatzbetrag und Gesamtkosten haben, müssen wir den Gesamtgewinn oder -verlust möglicherweise selbst berechnen.
Wir müssen dies nicht in der Quelle tun, sondern können dies innerhalb der Pivot-Tabelle selbst tun. Diese werden als berechnete Felder innerhalb der Pivot-Tabelle bezeichnet. Wir können benutzerdefinierte Formeln verwenden, damit die Daten mehr Geschichte aus den Daten erzählen. Mit Calculated Fields können wir eine neue berechnete Spalte erstellen, die in der tatsächlichen Datenquelle nicht vorhanden ist.
In diesem Artikel werden wir die Möglichkeiten demonstrieren, mit Pivot Table Calculated Fields neue Spalten basierend auf unserer Anforderung zu erstellen.
Benutzerdefiniertes Feld zur Berechnung des Gewinnbetrags
Sie können diese Excel-Vorlage für Pivot-Tabellenformeln hier herunterladen - Excel-Vorlage für Pivot-TabellenformelnDies ist das am häufigsten verwendete berechnete Feld in der Pivot-Tabelle. Sehen Sie sich die folgenden Daten an: Ich habe die Spalten Ländername, Produktname, Verkaufseinheiten, Stückpreis, Bruttoumsatz, COGS (Kosten der verkauften Waren), Datum und Jahr.
Lassen Sie mich die Pivot-Tabelle anwenden, um den Gesamtumsatz und die Gesamtkosten für jedes Land zu ermitteln. Unten ist die Pivot-Tabelle für die obigen Daten.
Das Problem ist, dass ich keine Gewinnspalte in den Quelldaten habe. Ich muss den Gewinn und den Gewinnprozentsatz für jedes Land herausfinden. Wir können diese beiden Spalten in der Pivot-Tabelle selbst hinzufügen.
Schritt 1: Wählen Sie eine Zelle in der Pivot-Tabelle aus. Wechseln Sie in der Multifunktionsleiste zur Registerkarte Analysieren und wählen Sie Felder, Elemente und Gruppen aus. Wählen Sie unter Berechnetes Feld.
Schritt 2: Geben Sie im folgenden Dialogfeld einen Namen für Ihr neu berechnetes Feld ein.
Schritt 3: Wenden Sie im Abschnitt Formel die Formel an, um den Gewinn zu ermitteln. Die Formel zur Ermittlung des Gewinns lautet Bruttoumsatz - COGS.
Gehen Sie in die Formelleiste> Wählen Sie Bruttoumsatz aus dem unteren Feld und doppelklicken Sie darauf, um ihn in der Formelleiste anzuzeigen.
Geben Sie nun das Minuszeichen (-) ein und wählen Sie COGS> Doppelklick.
Schritt 4: Klicken Sie auf HINZUFÜGEN und auf OK, um die Formel zu vervollständigen.
Schritt 5: Jetzt haben wir unsere TOTAL PROFIT-Spalte in der Pivot-Tabelle.
Dieses berechnete Feld ist flexibel und nicht nur auf die länderbezogene Analyse beschränkt, wir können es auch für alle Arten von Analysen verwenden. Wenn ich die Analyse länderspezifisch und produktspezifisch sehen möchte, muss ich nur die Produktspalte in das Feld ROW ziehen und dort ablegen. In diesem Feld wird die Gewinnaufteilung für jedes Produkt in den einzelnen Ländern angezeigt.
Schritt 6: Jetzt müssen wir den Gewinnprozentsatz berechnen. Die Formel zur Berechnung des Gewinnprozentsatzes lautet Gesamtgewinn / Bruttoumsatz.
Gehen Sie zu "Analysieren" und wählen Sie erneut "Berechnetes Feld" unter "Felder, Elemente und Sets" aus.
Schritt 7: Jetzt müssen wir das neu eingefügte berechnete Feld Total Profit in der Liste Fields sehen. Fügen Sie dieses Feld in die Formel ein.
Schritt 8: Geben Sie das Trennsymbol (/) ein und fügen Sie das Bruttoverkaufsfeld ein.
Schritt 9: Nennen Sie dieses berechnete Feld als Gewinnprozentsatz.
Schritt 10: Klicken Sie auf HINZUFÜGEN und auf OK, um die Formel zu vervollständigen. Wir haben Profit Percentage als neue Spalte.
Erweiterte Formel im berechneten Feld
Was ich jetzt gezeigt habe, ist das Grundmaterial von Calculated Field. In diesem Beispiel zeige ich Ihnen die erweiterten Formeln in den berechneten Feldern der Pivot-Tabelle. Jetzt möchte ich den Incentive-Betrag basierend auf dem Gewinnprozentsatz berechnen.
Wenn der Gewinn%> 15% ist, sollte der Anreiz 6% des Gesamtgewinns betragen.
Wenn der Gewinn%> 10% ist, sollte der Anreiz 5% des Gesamtgewinns betragen.
Wenn der Gewinn% <10% ist, sollte der Anreiz 3% des Gesamtgewinns betragen.
Schritt 1: Gehen Sie zu Berechnetes Feld und öffnen Sie das folgende Dialogfeld. Geben Sie den Namen als Anreizbetrag an.
Schritt 2: Jetzt werde ich die IF-Bedingung verwenden, um den Anreizbetrag zu berechnen. Wenden Sie die folgenden Formeln wie in der Abbildung gezeigt an.
= IF ('ProfitPercentage'> 15%, 'TotalProft' * 6%, IF ('ProfitPercentage'> 10%, 'Total Proft' * 5%, 'Total Proft' * 3%)
Schritt 3: Klicken Sie auf HINZUFÜGEN & OK, um den Vorgang abzuschließen. Jetzt haben wir eine Incentive Amount-Spalte.
Begrenzung des berechneten Feldes
Wir haben das Wunder der berechneten Felder gesehen, aber es hat auch einige Einschränkungen. Schauen Sie sich nun das folgende Bild an, wenn ich die Aufteilung des produktbezogenen Incentive-Betrags sehen möchte, haben wir einen falschen SUB TOTAL & GRAND TOTAL of INCENTIVE AMOUNT.
Seien Sie also vorsichtig, wenn Sie die Zwischensumme der berechneten Felder anzeigen. Es werden Ihnen die falschen Beträge angezeigt.
Liste aller berechneten Feldformeln abrufen
Wenn Sie nicht wissen, wie viele Formeln sich im berechneten Feld der Pivot-Tabelle befinden, können Sie die Zusammenfassung aller Formeln in einem separaten Arbeitsblatt abrufen.
Gehen Sie zu Analysieren> Felder, Elemente und Mengen -> Listenformeln.
Sie erhalten eine Zusammenfassung aller Formeln in einem neuen Arbeitsblatt.
Wichtige Informationen zur Pivot-Tabellenformel in Excel
- Wir können alle berechneten Felder löschen, ändern.
- In berechneten Feldern können keine Formeln wie VLOOKUP, SUMIF und viele andere mit dem Bereich verbundene Formeln verwendet werden, dh alle Formeln, die einen Bereich erfordern, können nicht verwendet werden.
Empfohlene Artikel
Dies war eine Anleitung zur Pivot-Tabellenformel in Excel. Hier haben wir die Schritte zur Verwendung der Formel der Pivot-Tabelle in Excel zusammen mit Beispielen und einer herunterladbaren Excel-Vorlage besprochen. Sie können sich diese nützlichen Funktionen auch in Excel ansehen -
- Tutorials zum Pivot Chart in Excel
- Pivot-Tabelle in Excel erstellen
- VLOOKUP Tutorial in Excel
- Excel Datenbank erstellen