Formula tabella pivot in Excel (tabella dei contenuti)
- Formula tabella pivot in Excel
- Campo personalizzato per calcolare l'importo del profitto
- Formula avanzata in campo calcolato
Formula tabella pivot in Excel
La tabella pivot è uno strumento che ci consente di analizzare ampi intervalli di dati. Possiamo analizzare, interpretare e fare molte altre cose senza rompere gran parte della nostra testa e del sudore. Può dare quasi tutto ciò che è presente nei dati di origine.
Se alcune delle informazioni sono presenti nei dati di origine, potrebbe essere necessario calcolarle da soli. Ad esempio, se disponiamo di un importo di vendita totale e di un costo totale, potrebbe essere necessario calcolare da soli l'utile o la perdita totale.
Non è necessario che ciò avvenga nell'origine ma possiamo farlo all'interno della tabella pivot stessa, questi sono chiamati campi calcolati all'interno della tabella pivot. Possiamo usare formule personalizzate per rendere i dati per raccontare più storie dai dati. Campi calcolati ci consente di creare una nuova colonna calcolata che non esiste nell'origine dati effettiva.
In questo articolo, dimostreremo come utilizzare i campi calcolati della tabella pivot per creare nuove colonne in base alle nostre esigenze.
Campo personalizzato per calcolare l'importo del profitto
Puoi scaricare questo modello Excel formula tabella pivot qui - Modello Excel formula tabella pivotQuesto è il campo calcolato più utilizzato nella tabella pivot. Dai un'occhiata ai dati seguenti, ho colonna Nome paese, Nome prodotto, Unità vendute, Prezzo unitario, Vendite lorde, COGS (Costo delle merci vendute), Data e Anno.
Consentitemi di applicare la tabella pivot per trovare le vendite totali e il costo totale per ciascun paese. Di seguito è riportata la tabella pivot per i dati sopra.
Il problema è che non ho una colonna di profitto nei dati di origine. Devo scoprire la percentuale di profitti e profitti per ciascun paese. Possiamo aggiungere queste due colonne nella stessa tabella pivot.
Passaggio 1: selezionare una cella nella tabella pivot. Vai alla scheda Analizza nella barra multifunzione e seleziona Campi, elementi e set. Sotto questo selezionare Campo calcolato.
Passaggio 2: nella finestra di dialogo in basso, dai un nome al tuo nuovo campo calcolato.
Passaggio 3: nella sezione Formula applica la formula per trovare il profitto. La formula per trovare il profitto è Vendite lorde - COGS.
Entra nella barra della formula> Seleziona Vendite lorde dal campo in basso e fai doppio clic per visualizzarlo nella barra della formula.
Ora digita il simbolo meno (-) e seleziona COGS> Fai doppio clic.
Passaggio 4: fare clic su AGGIUNGI e OK per completare la formula.
Passaggio 5: ora abbiamo la nostra colonna TOTAL PROFIT nella tabella pivot.
Questo campo calcolato è flessibile, non si limita solo all'analisi per paese, ma possiamo utilizzarlo per qualsiasi tipo di analisi. Se voglio vedere l'analisi dal punto di vista del paese e del prodotto, devo solo trascinare e rilasciare la colonna del prodotto nel campo ROW per mostrare la suddivisione del profitto per ciascun prodotto in ciascun paese.
Passaggio 6: ora è necessario calcolare la percentuale di profitto. La formula per calcolare la percentuale di profitto è Profitto totale / Vendite lorde.
Vai su Analizza e seleziona nuovamente Campo calcolato in Campi, Articoli e Set.
Passo 7: Ora dobbiamo vedere il campo calcolato appena inserito Profitto totale nell'elenco Campi. Inserisci questo campo nella formula.
Passaggio 8: digitare il simbolo del divisore (/) e inserire il campo Vendite lorde.
Passaggio 9: nominare questo campo calcolato come percentuale di profitto.
Passaggio 10: fare clic su AGGIUNGI e OK per completare la formula. Abbiamo la percentuale di profitto come nuova colonna.
Formula avanzata in campo calcolato
Qualunque cosa abbia mostrato ora è la roba base del Campo calcolato. In questo esempio, ti mostrerò le formule avanzate nei campi calcolati della tabella pivot. Ora voglio calcolare l'importo dell'incentivo in base alla percentuale di profitto.
Se l'utile% è> 15% l'incentivo dovrebbe essere il 6% dell'utile totale.
Se la percentuale di profitto è> 10% l'incentivo dovrebbe essere il 5% del profitto totale.
Se l'utile% è <10% l'incentivo dovrebbe essere il 3% dell'utile totale.
Passaggio 1: vai al campo calcolato e apri la finestra di dialogo in basso. Assegna il nome come importo dell'incentivo.
Step 2: Ora userò la condizione IF per calcolare l'importo dell'incentivo. Applica le seguenti formule come mostrato nell'immagine.
= IF ('ProfitPercentage'> 15%, 'TotalProft' * 6%, IF ('ProfitPercentage'> 10%, 'Total Proft' * 5%, 'Total Proft' * 3%))
Passaggio 3: fare clic su Aggiungi e OK per completare. Ora abbiamo una colonna Importo dell'incentivo.
Limitazione del campo calcolato
Abbiamo visto la meraviglia dei campi calcolati ma ha anche alcuni dei limiti. Ora guarda l'immagine qui sotto, se voglio vedere la suddivisione dell'importo dell'incentivo in termini di prodotto avremo sbagliato SUB TOTAL e GRAND TOTAL di INCENTIVE AMOUNT.
Quindi fai attenzione mentre mostri il totale parziale dei campi calcolati. Ti mostrerà gli importi sbagliati.
Ottieni l'elenco di tutte le formule dei campi calcolati
Se non sai quante formule ci sono nel campo calcolato della tabella pivot puoi ottenere il riepilogo di tutte queste in un foglio di lavoro separato.
Vai su Analizza> Campi, elementi e set -> Formule elenco.
Ti fornirà un riepilogo di tutte le formule in un nuovo foglio di lavoro.
Cose da ricordare sulla formula della tabella pivot in Excel
- Possiamo eliminare, modificare tutti i campi calcolati.
- Non possiamo usare formule come VLOOKUP, SUMIF e molte altre gamme coinvolte formule nei campi calcolati, cioè tutte le formule che richiedono l'intervallo non possono essere utilizzate.
Articoli consigliati
Questa è stata una guida alla formula della tabella pivot in Excel. Qui abbiamo discusso i passaggi per utilizzare la formula della tabella pivot in Excel insieme ad esempi e modello Excel scaricabile. Puoi anche guardare queste utili funzioni in Excel:
- Tutorial sul grafico pivot in Excel
- Creazione della tabella pivot in Excel
- VLOOKUP Tutorial in Excel
- Excel Crea database