Come creare un campo calcolato in Excel. Colonne calcolate in Transact-SQL Come creare una colonna calcolata

In questo articolo parleremo dei campi calcolati in Accedere alle query. Una query, come una tabella, può eseguire calcoli su valori numerici, stringhe o date per ciascun record utilizzando i dati di uno o più campi. Il risultato del calcolo forma un nuovo campo calcolato nella tabella delle query. A differenza dei campi della tabella calcolata, i campi calcolati nelle tabelle del database di origine non creano nuovi campi. Ogni volta che viene eseguita la query, i calcoli vengono effettuati in base ai valori del campo corrente.

Le espressioni di campo calcolato possono utilizzare costanti e funzioni oltre ai nomi di campo. Come risultato dell'elaborazione di un'espressione, è possibile ottenere un solo valore.

Compito 1. La tabella PRODOTTO contiene i campi PRICE e RATE_VAT Calcola il prezzo IVA inclusa e confrontalo con il prezzo ottenuto nel campo calcolato della tabella Prezzo IVA inclusa.

1. Creare una query di selezione in modalità progettazione per la tabella PRODUCT. Trascina i campi NAME_TOV, PRICE, RATE_VAT e Prezzo con IVA nel modulo di richiesta (Fig. 4.6).
2. Per calcolare il prezzo IVA inclusa, creare un campo calcolato scrivendo l'espressione [PRICE]+[PRICE]*[RATE_VAT] nella cella vuota della riga Campo.
3. Per selezionare i record con un valore maggiore di 5000 nel campo calcolato, inserisci > 5000 nella riga Criteri
4. Dopo aver inserito un'espressione, il sistema genera per default il nome del campo calcolato Espressione 1, che diventa l'intestazione della colonna nella tabella con i risultati della query. Questo nome verrà inserito prima dell'espressione [PRICE]+[PRICE]*[VAT_RATE]. Per ogni nuovo campo calcolato nella query, il numero dell'espressione aumenta di uno. Il nome del campo calcolato è separato dall'espressione da due punti. Per modificare il nome posizionare il cursore del mouse nel campo calcolato del modulo di richiesta e cliccare con il tasto destro del mouse. Dal menu sensibile al contesto, selezionare Proprietà(Proprietà) e nella riga Didascalia inserisci un nuovo nome di campo: Prezzo comprensivo di IVA1. Ora nella tabella con i risultati della query, questo nome verrà visualizzato nell'intestazione della colonna calcolata. Il nome del campo può anche essere corretto direttamente nel modulo di richiesta.
5. Per visualizzare il risultato della query, fare clic sul pulsante Eseguire(Correre) nel gruppo risultati(Risultati). Il campo calcolato della tabella e la query hanno gli stessi valori.
6. Modificare il prezzo del prodotto in uno dei record di richiesta. I valori in entrambi i campi calcolati verranno immediatamente ricalcolati.
7. Per formare un'espressione complessa in un campo calcolato o in una condizione di selezione, è consigliabile utilizzare il generatore di espressioni. Il builder consente di selezionare i nomi dei campi necessari nell'espressione da tabelle, query, segni di operazione e funzioni. Rimuovi l'espressione nel campo calcolato e utilizza il builder per generarla.
8. Richiamare il generatore di espressioni facendo clic sul pulsante Costruttore(Costruttore) nel gruppo Configurazione della query(Impostazione query) Progettazione della barra multifunzione o selezionando Costruire(Costruisci) nel menu sensibile al contesto. Il cursore del mouse deve essere precedentemente posizionato nella cella di input dell'espressione.
9. Sul lato sinistro della finestra Costruttore di espressioni(Costruttore di espressioni) (Fig. 4.7) selezionare la tabella PRODOTTO su cui si basa la query. Sulla destra verrà visualizzato un elenco dei relativi campi. Selezionare in sequenza i campi e gli operatori richiesti facendo doppio clic su di essi nell'espressione. L'espressione verrà formata nella parte superiore della finestra. Tieni presente che il builder ha indicato il nome della tabella a cui appartiene prima del nome del campo e lo ha separato dal nome del campo con un punto esclamativo.
10. Completare il processo di costruzione di un'espressione in un campo calcolato facendo clic sul pulsante OK.
11. Salvare la richiesta con il nome ― Prezzo con IVA e chiuderla.
12. Esegui quello salvato selezionandolo nell'area di navigazione e selezionando il comando Apri dal menu contestuale.

Compito 2.È possibile utilizzare funzioni integrate nei campi calcolati e nelle condizioni di filtro. Access ha oltre 150 funzioni definite.
Sia necessario selezionare tutte le fatture per le quali è stata effettuata la spedizione in un determinato mese. Nella FATTURA, la data di spedizione viene memorizzata nel campo DATE_OTG con il tipo di dati Data/Ora.

1. Creare una query di selezione in modalità progettazione per la tabella INVOICE. Trascina i campi NOM_NAKL e CODE_SK nel modulo (Fig. 4.8).
2. Crea un campo calcolato in una cella di riga vuota Campo(Campo) scrivendo una delle espressioni lì: Format([INVOICE]![DISPOSITION_DATE];"mmmm") - questa funzione restituirà il nome completo del mese
   o Formato([INBOOK]![DISPOSITION_DATE];"mm") - questa funzione restituirà il numero del mese.
3. Per selezionare le fatture emesse in un determinato mese, nel campo calcolato della riga Condizioni di selezione (Criteri), inserire il nome del mese, ad esempio marzo (Fig. 4.8), o il numero del mese, ad esempio 3 secondo la parametro nella funzione Formato.
4. Completa la tua richiesta cliccando il pulsante Eseguire(Correre) nel gruppo risultati(Risultati) nella scheda della barra multifunzione Lavorare con le richieste | Costruttore(Strumenti di query | Progettazione).
5. Scrivi la funzione Mese(INVOICE!DATE_OTG) nel campo calcolato e assicurati che questa funzione restituisca il numero del mese estratto dalla data.
6. Per selezionare tutte le righe relative al secondo trimestre, nella riga Criteri, inserire l'operatore Between 4 And 6, che determina se il valore dell'espressione rientra nell'intervallo specificato.
7. Scrivi l'espressione MonthName(Month(INVOICE!DATE_OTG)) nel campo calcolato e assicurati che la funzione MonthName converta il numero del mese nel suo nome completo.

Per rafforzarlo, guarda il video tutorial.

Quando analizzi i dati utilizzando le tabelle pivot, spesso devi includere nel report i valori risultanti da calcoli eseguiti al di fuori del set di dati originale. Programma Excel fornisce all'utente i mezzi per eseguire calcoli nelle tabelle pivot utilizzando campi calcolati ed elementi calcolati. Nella fig. La Figura 1 mostra come un campo calcolato può presentare i dati da una prospettiva diversa. La tabella pivot mostra il volume delle vendite e il tempo (in ore) impiegato nella vendita delle apparecchiature in ciascun mercato. Un campo calcolato che calcola il ricavo medio orario migliora la capacità di analizzare i dati in una tabella pivot.

Riso. 1. È necessario creare un campo calcolato in una tabella pivot esistente Entrate medie all'ora, che darà un nuovo significato ai dati analizzati

Scarica la nota in formato o, esempi in formato

Un campo calcolato è un campo dati creato come risultato di calcoli basati su campi di tabella pivot esistenti. Il campo calcolato viene aggiunto al set di dati come colonna virtuale. Questa colonna non è inclusa nei dati di origine, contiene valori determinati da una formula e interagisce con i dati della tabella pivot allo stesso modo degli altri campi della tabella pivot.

Con i campi calcolati, puoi inserire una formula in una tabella pivot, consentendoti di creare il tuo campo. I dati appena creati entreranno a far parte della tabella pivot, interagendo con i dati esistenti. Quando aggiorni le tabelle pivot, i campi calcolati vengono ricalcolati e la tabella pivot stessa viene riempita con informazioni che non erano presenti nel set di dati originale.

Ora guarda la fig. 5.1 e chiediti: perché hai dovuto aggiungere campi calcolati? Perché non utilizzare le normali formule di cella o eseguire i calcoli necessari direttamente sulla tabella di origine per ottenere le informazioni di cui hai bisogno?" Per rispondere a queste domande, esaminiamo i diversi metodi che è possibile utilizzare per creare il campo calcolato mostrato nella Figura 1.

Metodo 1: aggiungere un campo calcolato all'origine dati

Puoi aggiungere un campo calcolato all'origine dati, come mostrato nella figura. 2, consentendo alla tabella pivot di utilizzare questo campo come campo dati standard. Se la disposizione delle informazioni sul foglio della fonte dati consente ciò, quale potrebbe essere la soluzione migliore. A proposito, l'aggiunta di una nuova colonna richiederà non solo l'aggiornamento della tabella pivot, ma anche la modifica dell'ambito su cui si basa il pivot. Per fare ciò, fare clic su Riepilogo e scorrere il menu Analisi → Origine dati → Origine dati.

Provo a trasformare i dati originali in una tabella (operazione eseguita in Fig. 2 e nel file Excel allegato). In questo caso, in primo luogo, vengono evidenziate le intestazioni, in secondo luogo compaiono i pulsanti con i filtri, che spesso sono utili per l'esplorazione dei dati, in terzo luogo, le righe vengono colorate una alla volta e, infine, la cosa più importante, quando si aggiungono righe e colonne non è necessario modificare l'area dati per una tabella pivot. L'area specificata come tale non è un intervallo rettangolare, ma il nome della Tabella. Nel nostro caso - Fonte(Fig. 3).

Riso. 3. Formazione di un riepilogo basato su uno speciale strumento Excel – Tabella

Ma questo metodo presenta anche degli svantaggi: in primo luogo, non è sempre conveniente aggiungere una colonna ai dati di origine e, in secondo luogo, le possibilità sono limitate se la struttura dei dati di origine cambia (ad esempio, si esportano dati da 1C e la nuova esportazione ha una colonna in più; questa nuova colonna sovrascriverà la colonna di calcolo).

Metodo 2: utilizzare una formula esterna alla tabella pivot per creare un campo calcolato

Puoi aggiungere un campo calcolato accanto a una tabella pivot. Nella fig. 4 ogni cella in una colonna Entrate medie all'ora fornito con una formula che fa riferimento a una tabella pivot. A proposito, se, quando provi a fare riferimento a una cella della tabella pivot, nella tua formula appare la funzione GET.PIOTTABLE.DATA, non sarai in grado di "trascinare" la formula. Per superare questa difficoltà, leggere la nota.

Riso. 4. Quando inserisci una formula dopo aver creato una tabella pivot, stai essenzialmente aggiungendo un campo calcolato che cambia quando la tabella pivot stessa viene aggiornata

Sebbene questo metodo aggiunga un campo calcolato che viene aggiornato quando viene aggiornata la tabella pivot, qualsiasi modifica alla struttura della tabella pivot potrebbe rendere inutilizzabile la formula. Ad esempio, come risultato del trascinamento del campo Mercato delle vendite nell'area FILTRI dell'elenco dei campi in una tabella pivot, la struttura del report cambia, a seguito della quale appare un messaggio di errore nel campo calcolato (Fig 5). Se aggiungi un altro campo all'area COLONNE della tabella pivot, il campo column Entrate medie all'ora verrà sovrascritto.

Metodo 3: inserimento diretto di un campo calcolato nella tabella pivot

L'inserimento di un campo calcolato direttamente in una tabella pivot è spesso la soluzione migliore. Se utilizzi questo metodo, non dovrai modificare le formule. Sarà anche possibile adeguare automaticamente la tabella in base alla fonte dati modificata. Sarà raggiunto il necessario grado di flessibilità per garantire il ricalcolo automatico dei campi in caso di modifica delle unità di misura.

Un altro vantaggio di questo metodo è che se lo utilizzi, puoi modificare la struttura della tabella pivot e anche i campi dati per le diverse unità di misura utilizzate nei campi calcolati. Allo stesso tempo, puoi essere sicuro che non ci saranno errori nelle formule e che i riferimenti alle celle non andranno persi.

Il rapporto di tabella pivot mostrato nella figura 6 presenta una tabella riassuntiva, che è mostrata in Fig. 1, tranne che viene ricostruito per mostrare il ricavo medio orario per ciascun servizio e ciascun mercato.

Riso. 6. Il campo calcolato precedentemente creato rimane “valido” anche se viene modificata la struttura della tabella pivot, con conseguente visualizzazione del ricavo medio orario per ciascun servizio e ciascun mercato di vendita

Crea un campo calcolato

Prima di poter creare un campo calcolato, è necessario aprire o generare una tabella pivot. Il nostro esempio utilizza la tabella pivot mostrata in Fig. 1, ma senza colonna D. Per creare un campo calcolato, attivare la finestra di dialogo Inserimento di un campo calcolato. Per fare ciò, fai clic sulla tabella pivot per attivare il gruppo di schede contestuali Lavorare con le tabelle pivot. Vai alla scheda Analisi, al gruppo Calcoli, fare clic sul pulsante Campi, elementi e insiemi e selezionare dal menu il comando Campo calcolato (Fig. 7).

Sullo schermo verrà visualizzata una finestra di dialogo Inserimento di un campo calcolato(Fig. 8). Nella parte superiore della finestra di dialogo sono presenti due campi di testo: Nome E Formula. In questi campi è necessario specificare il nome del campo calcolato e creare una formula specificando i campi dati e gli operatori matematici richiesti. Al campo calcolato viene assegnato un nome descrittivo Entrate medie all'ora. Questo nome dovrebbe essere scelto per descrivere accuratamente il tipo di operazione matematica da eseguire. Campo di testo predefinito Formula la finestra di dialogo Inserimento di un campo calcolato contiene l'espressione = 0. È necessario rimuovere lo zero prima di immettere la formula. Seleziona uno dei campi della zona Campi; nel nostro caso - Volume delle vendite e fare clic Aggiungi un campo. Il nome del campo apparirà nella riga Formula. Inserisci il segno di divisione / e poi seleziona il secondo campo – Periodo di vendita (in ore). Clic Aggiungere, poi OK per attivare il nuovo campo calcolato.

Un nuovo campo calcolato verrà visualizzato nella tabella pivot Entrate medie all'ora(vedi Fig. 1). Un nuovo campo calcolato è appena stato aggiunto con successo a una tabella pivot. Ora puoi modificare le impostazioni per questo nuovo campo proprio come faresti per qualsiasi altro campo (ad esempio nome campo, formato numero o colore).

Ciò significa che hai aggiunto la colonna all'origine dati? NO. I campi calcolati sono come elementi totale parziale E importo totale Le funzioni di tabella pivot sono predefinite perché sono tutte funzioni matematiche che ricalcolano i dati quando la tabella pivot viene modificata o aggiornata. I campi calcolati imitano semplicemente i campi definiti esplicitamente nell'origine dati. Puoi trascinarli, modificare le impostazioni dei campi e utilizzarli insieme ad altri campi calcolati.

Si prega di notare che nella fig. 8 Il formato della formula inserita è simile a quello utilizzato nella riga standard Formule di Excel. La differenza principale è che invece di utilizzare rigidi riferimenti di cella o valori numerici, si fa riferimento ai campi dati della tabella pivot per assegnare valori agli argomenti utilizzati nei calcoli.

Creazione di elementi calcolati

Un elemento calcolato è essenzialmente una riga virtuale di dati che viene aggiunta come risultato di calcoli eseguiti su altre righe nello stesso campo. Molto spesso è possibile ottenere un risultato simile a quello ottenuto durante la creazione di un elemento calcolato raggruppando i dati. In molti casi, infatti, il raggruppamento fornisce un'ottima alternativa ai membri calcolati.

Nella tabella riassuntiva riportata in Fig. 9, è indicato il volume totale delle vendite per determinati periodi di riferimento (campo Periodo di riferimento). Immagina di voler confrontare il volume medio delle vendite degli ultimi sei periodi con il volume medio delle vendite dei sette periodi precedenti. Per essere più precisi, dobbiamo determinare la media dei periodi P01–P07 e confrontarla con la media dei periodi P08–P13.

Posiziona il puntatore in qualsiasi cella del campo Periodo di riferimento vai alla scheda contestuale della barra multifunzione Analisi al gruppo Informatica, e fare clic sul pulsante Campi, elementi e insiemi. Nel menu che si apre, seleziona il comando Oggetto calcolato(Fig. 10).

Si aprirà una finestra di dialogo (Fig. 11). Nota che nella parte superiore della finestra di dialogo indica con quale campo stai lavorando. In questo caso questo campo Periodo di riferimento. Inoltre, l'elenco contiene tutti gli elementi del campo Periodo di riferimento. Assegnare un nome all'elemento calcolato, quindi creare una formula con la combinazione necessaria di elementi di dati e operatori che produrrà il risultato corretto. La formula viene inserita nel campo Formula e gli elementi dati necessari utilizzati in esso vengono selezionati nell'elenco Elementi. Fare clic sul pulsante OK e l'elemento calcolato verrà aggiunto alla tabella pivot (Fig. 12).

Riso. 11. Finestra di dialogo Inserimento di un elemento calcolato

Allo stesso modo, crea un articolo calcolato che rappresenterà le vendite medie per i periodi P08–P13. Quindi nascondi i singoli periodi di reporting, lasciando solo due elementi calcolati. Con una piccola formattazione, gli elementi calcolati verranno utilizzati per confrontare le vendite medie dei sei mesi precedenti e successivi (Figura 13).

Se non si nascondono gli elementi dati utilizzati nel calcolo degli elementi calcolati, i totali parziali e generali potrebbero non essere corretti.

Vantaggi e svantaggi dei calcoli di tabella pivot

Esistono anche alcuni svantaggi legati all'utilizzo di campi ed elementi calcolati. È importante comprendere cosa accade quando si calcolano i dati nelle tabelle pivot, ma è ancora più importante comprendere le limitazioni dei campi calcolati e degli elementi calcolati per evitare errori durante l'analisi dei dati.

Priorità delle operazioni. Come nei fogli di calcolo, qualsiasi operatore può essere utilizzato nelle formule per gli elementi e i campi calcolati: +, –, *, /, %, ^, ecc. Inoltre, come nei fogli di calcolo, i calcoli nelle tabelle pivot vengono eseguiti in base alla precedenza degli operatori. Quando esegui un calcolo che combina più operatori, ad esempio (2+3) *4/50%, Excel valuta l'espressione ed esegue il calcolo in un ordine specifico. Conoscere questo ordine ti salverà da molti errori.

Pertanto, l'ordine delle operazioni in Excel è il seguente:

• elaborazione delle espressioni tra parentesi;
• elaborazione dell'intervallo(:);
• intersezioni (aree) di elaborazione;
• l'elaborazione si unisce (;);
• eseguire un'operazione di negazione;
• conversione di valori percentuali (ad esempio, 50% viene convertito in 0,50);
• esponenziazione (^);
• moltiplicazione (*) e divisione (/); tali operazioni hanno pari priorità;
• addizione (+) e sottrazione (–); tali operazioni hanno pari priorità;
• combinando dati di testo (&) questa operazione è anche chiamata concatenazione;
• eseguendo operazioni di confronto (=,<>, <=, >=).

Gli operatori equivalenti nella stessa espressione vengono sempre eseguiti in ordine (da sinistra a destra).

Diamo un'occhiata a un semplice esempio. Come sai, l'espressione (2+3)*4 restituisce il risultato 20. Se rimuovi le parentesi e lasci l'espressione 2+3*4, Excel calcolerà: 3*4 = 12 + 2 = 14.

Diamo un'occhiata a un altro esempio. Se inserisci l'espressione 10^2 come formula, ad es. Se specifichi di elevare al quadrato il numero 10, il programma restituirà il valore 100 come risposta. Se inserisci l'espressione –10^2, puoi aspettarti che il valore restituisca –100. Tuttavia, Excel restituirà nuovamente il valore 100. Il motivo è che il programma esegue l'operazione di negazione prima dell'elevamento a potenza, cioè il valore 10 viene prima convertito in -10 e il risultato di -10*-10 è in realtà 100. L'uso delle parentesi nella formula -(10^2) garantisce che il programma esegua prima l'elevamento a potenza e poi la negazione, restituendo il risultato atteso risultato - 100.

Riferimenti di celle e intervalli denominati. I riferimenti di cella o gli intervalli denominati non possono essere utilizzati durante la creazione di campi ed elementi calcolati perché quando crei oggetti calcolati in una tabella pivot, stai essenzialmente lavorando al di fuori dello spazio degli oggetti. Gli unici dati disponibili per gli utenti si trovano nella cache della tabella pivot. Poiché non puoi andare oltre la cache, non puoi fare riferimento a celle o intervalli denominati nella formula che crei.

Funzioni del foglio di lavoro.È possibile utilizzare qualsiasi funzione del foglio di lavoro che non utilizzi riferimenti di cella o oggetti denominati come argomenti. In effetti, puoi utilizzare qualsiasi funzione del foglio di lavoro che non richieda necessariamente riferimenti a celle o oggetti con nome. Funzioni come COUNT, AVERAGE, IF, AND, NO e OR rientrano in questa categoria.

Costanti.È possibile utilizzare qualsiasi costante nei calcoli eseguiti nelle tabelle pivot. Le costanti sono valori statici che non cambiano nel tempo. Ad esempio, nella formula seguente, il numero 5 è una costante: [Unità vendute] *5. Anche se il significato Unità vendute potrebbe cambiare in base ai dati disponibili, il numero 5 sarà sempre lo stesso.

Collegamenti ai totali. Le formule che eseguono calcoli non possono fare riferimento a totali parziali o totali di tabella pivot. In altre parole, non è possibile utilizzare il risultato di un calcolo parziale o totale come variabile o argomento in un campo calcolato.

Regole speciali per i campi calcolati. I calcoli nei campi calcolati vengono sempre eseguiti sui totali, non sui singoli elementi di dati. In poche parole, Excel calcola sempre i campi dati, i totali parziali e i totali prima di valutare un campo calcolato. Ciò significa che il campo calcolato viene sempre applicato ai totali dei dati di origine. L'esempio mostrato in Fig. 14 dimostra come ciò possa influenzare l’analisi dei dati.

Riso. 14. Sebbene un campo calcolato sia valido per singoli dati, non è matematicamente corretto utilizzarlo per i totali parziali

In ogni trimestre è necessario ottenere i volumi di vendita finali per ciascun prodotto moltiplicando il numero di unità vendute per il prezzo unitario di quel prodotto. Se guardi prima i dati del primo trimestre, identificherai immediatamente il problema. Invece di calcolare la somma di 220+150+220+594, ovvero 1184, la somma del numero di unità viene moltiplicata per la somma dei prezzi delle merci, ottenendo un valore errato. Sfortunatamente, questo problema non può essere risolto, ma può essere aggirato. Eliminare i totali parziali e parziali dalla tabella pivot, quindi calcolare un nuovo totale nella parte inferiore all'esterno della tabella pivot.

Regole speciali per l'utilizzo dei membri calcolati. Una tabella pivot non può utilizzare elementi calcolati che calcolano medie, deviazioni standard o varianze. Al contrario, non è possibile utilizzare medie, deviazioni standard o varianze in una tabella pivot che contiene un elemento calcolato. Non potrai utilizzare un campo pagina per creare un elemento calcolato oppure non potrai spostare gli elementi calcolati nell'area del filtro del report. Non è possibile aggiungere un elemento calcolato a un report che contiene un campo raggruppato e non è possibile raggruppare alcun campo in una tabella pivot che contenga un elemento calcolato. Quando crei una formula per un articolo calcolato, non puoi fare riferimento a un articolo da un campo esterno.

Gestire e supportare i calcoli nelle tabelle pivot

Quando si lavora con le tabelle pivot, spesso si verificano situazioni in cui non ha senso archiviare la tabella pivot più a lungo di quanto necessario per copiare i singoli valori. Tuttavia, ci sono spesso situazioni in cui è più vantaggioso conservare la tabella pivot e tutti i suoi elementi invariati. Se si mantiene e si gestisce una tabella pivot modificando i requisiti e aumentando il volume dei dati di origine, è necessario gestire i campi calcolati e i membri calcolati.

Modifica ed eliminazione di calcoli nelle tabelle pivot. Se si modificano le impostazioni di calcolo o non è più necessario un campo calcolato o un membro calcolato, è possibile aprire la finestra di dialogo corrispondente per modificare o eliminare il calcolo. Attiva la finestra di dialogo Inserimento di un campo calcolato O Inserimento di un elemento calcolato(vedi descrizione alle Fig. 7 e 10) e selezionare l'elenco a discesa al suo interno Nome(Fig. 15).

Modificare l'ordine in cui vengono eseguiti i calcoli nei campi calcolati. Se il valore di una cella in una tabella pivot dipende dal risultato del calcolo di più elementi calcolati, è possibile modificare l'ordine in cui vengono eseguite le operazioni nei campi calcolati. In altre parole, è possibile specificare l'ordine in cui verranno eseguiti i singoli calcoli. Per aprire la finestra di dialogo , posiziona il puntatore in un punto qualsiasi della tabella pivot e fai clic sull'icona Campi, elementi e insiemi. Nel menu a tendina selezionare il comando Ordine di calcolo (Fig. 16).

Riso. 16. Aprire la finestra di dialogo Ordine di calcolo

Nella finestra di dialogo Ordine di calcolo(Fig. 17), vengono visualizzati tutti gli elementi calcolati che vengono aggiunti alla tabella pivot. Selezionare qualsiasi elemento calcolato nell'elenco e utilizzare i pulsanti Su, Giù E Eliminare. L'ordine in cui vengono visualizzate le formule nell'elenco determina l'ordine esatto in cui vengono eseguiti i calcoli nella tabella pivot.

Documentazione delle formule. Excel fornisce uno strumento eccellente che visualizza i campi calcolati e gli elementi calcolati utilizzati in una tabella pivot, nonché come eseguire calcoli e applicare formule. Questo strumento è molto utile per analizzare una tabella pivot di terze parti o quando è necessario determinare rapidamente quali calcoli vengono utilizzati al suo interno e quali campi ed elementi influenzano. Per creare un report sui calcoli della tabella pivot, posiziona il puntatore in un punto qualsiasi della tabella pivot e fai clic su Campi, elementi e insiemi e seleziona il comando dal menu a tendina Derivare formule. Excel creerà un report sui campi e sugli elementi calcolati su un foglio separato (Fig. 18).

Riso. 18. Squadra Derivare formule ti consente di documentare rapidamente e facilmente i calcoli in una tabella pivot

La nota è stata scritta sulla base del libro di Jelen, Alexander. . Capitolo 5.

La parola Tabella è in maiuscolo perché non è solo una tabella, ma uno strumento Excel separato.

Il primo modo per creare un campo calcolato si basa sul fatto che il modulo si basa su una query che include colonne calcolate. Il modulo crea un campo di testo che specifica una colonna di query calcolata come origine dati. Il campo è bloccato in modo che l'utente non possa modificare i dati in esso contenuti. I dati vengono calcolati durante l'esecuzione della query. Per bloccare un campo in modalità progettista nella finestra Proprietà campo calcolato nella scheda Dati in una riga Blocco accedere SÌ.

Nel secondo caso il modulo è costruito sulla base di una tabella. Viene creato un nuovo campo nel modulo in cui viene specificata un'espressione come origine dati, ad esempio = [Prezzo unitario]*Quantità. Quando si fa riferimento ai campi nelle espressioni, è obbligatorio utilizzare le parentesi quadre se il nome del campo è costituito da più di una parola. È consentito immettere un'espressione direttamente in un controllo (campo) se è breve e la lunghezza del campo lo consente. Altrimenti l'espressione viene inserita nella cella delle proprietà Dati finestra Proprietà. A tale scopo è possibile utilizzare il generatore di espressioni. Se l'espressione non rientra nella cella delle proprietà, è necessario premere i tasti + e aprire la finestra Area di immissione. Se necessario, è necessario impostare il formato del campo quando viene visualizzato sullo schermo.

6. Creazione di un modulo pulsante

Seleziona il comando del menu Gestione moduli Servizi/Utilità/Pulsanti.

Nella finestra di dialogo che chiede se desideri creare un modulo pulsante, fai clic su SÌ.

Sullo schermo apparirà una finestra Gestione moduli pulsanti. Questa finestra contiene un elenco di pagine del modulo dei pulsanti. All'inizio del lavoro, la finestra è una pagina - Modulo del pulsante principale. Nella finestra, fare clic sul pulsante Modifica.

Si apre una finestra di dialogo Modifica della pagina del modulo di un pulsante. Modifica il nome del modulo.

Fare clic sul pulsante Creare. Viene visualizzata una finestra Modifica di un elemento del modulo pulsante.

Nel campo Testo Immettere il nome del pulsante da creare. Il nome del pulsante dovrà essere associato dall'utente agli oggetti con cui intende lavorare. Secondo campo Squadra consente di definire l'azione che verrà eseguita dopo aver fatto clic su questo pulsante. Seleziona il comando richiesto per la tua applicazione dall'elenco a discesa.

Aggiungi pulsanti per tutti i moduli e i report, strutturandoli per tipologia.

Aggiungi un pulsante per uscire dall'applicazione e un pulsante per accesso veloce A Gestore moduli pulsanti.

Configura l'applicazione su Modulo del pulsante principale aperto all'apertura del database. Per fare ciò, utilizzare il comando di menu Strumenti/Opzioni di avvio.

ESEMPIO:

Riso. 28. Modulo pulsante di livello superiore

Laboratorio 3 Creazione di query

Obiettivo del lavoro - acquisire competenze pratiche nello sviluppo di query di database.

Esercizio:

Testare lo schema del database utilizzando query tipiche. È necessario creare almeno una richiesta di ciascun tipo descritto di seguito. Nel report, le query dovrebbero essere presentate in linguaggio naturale, in SQL, e dovrebbero anche fornire un esempio del risultato dell'esecuzione della query.

Sequenza di lavoro

1.Crea una query di selezione di una tabella singola utilizzando il Generatore di query

IN finestra del database fare clic sul segnalibro Richieste.

Fare clic sul pulsante Creare.

Nella finestra Nuova richiesta Selezionare Costruttore e premere il pulsante OK.

Nella finestra Aggiunta di una tabella selezionare dalla finestra di elenco la tabella (o query) su cui si baserà la query e fare clic sul pulsante Aggiungere. Chiude la finestra corrente. Successivamente, apparirà una finestra sullo schermo Generatore di query.

Aggiungi un campo alla richiesta. A questo scopo in tabella di origine, situato nella parte superiore della finestra esempio di richiesta, selezionare il campo desiderato. Fare doppio clic con il pulsante sinistro del mouse sul campo evidenziato. Allo stesso tempo, nella parte inferiore della finestra Richieste nel modulo di richiesta apparirà una colonna corrispondente al campo selezionato.

È inoltre possibile aggiungere un campo a una richiesta nel modo seguente: fare clic pulsante di sinistra posizionare il mouse sul campo selezionato e, senza rilasciarlo, trascinare il campo nella posizione desiderata nel modulo di richiesta; Fare clic su un campo della cella nel modulo di richiesta per visualizzare un elenco a discesa di campi e selezionare da esso il campo richiesto; Fare clic sul campo della cella nel modulo di richiesta e inserire il nome del campo.

Per aggiungere tutti i campi della tabella a una query, selezionare il campo indicato da un asterisco (*). L'asterisco semplifica il lavoro di costruzione di una query, ma rende più complicati l'ordinamento e l'immissione delle condizioni per la selezione dei campi.

Per impostazione predefinita, tutti i campi della richiesta non sono ordinati. Se necessario, è possibile specificarlo nella riga Ordinamento modulo di query, come visualizzare i valori delle colonne: ascendente o discendente. Quando si specifica un ordinamento in più campi, i record vengono ordinati prima in base alla colonna più a sinistra, quindi in base a ciascuna colonna successiva da sinistra a destra.

In linea Uscita sul display seleziona le caselle che vuoi vedere nel recordset dopo aver eseguito la query. Per impostazione predefinita vengono visualizzati tutti i campi inclusi nel modulo di richiesta.

Nel modulo di richiesta in linea Condizione di selezione e nella linea O specificare le condizioni per il campionamento dal database. Le condizioni sono espressioni logiche.

Un'operazione logica viene eseguita sulle condizioni di selezione situate in una riga E. Diverse condizioni di selezione per un campo possono essere impostate in due modi: inserisci tutte le condizioni in una cella della riga Condizione di selezione, collegandoli con un operatore logico O oppure inserisci ciascuna condizione in una cella separata della riga O. Passa alla riga successiva dell'area della cella O per chiave<>. Le espressioni in una cella del modulo di richiesta vengono inserite dalla tastiera o utilizzate per crearle. Costruttore di espressioni, che viene avviato dal comando Costruire dal menu contestuale associato alla linea Condizione di selezione(oppure puoi premere il pulsante Costruire sulla barra degli strumenti Accesso).

Visualizza i risultati della query facendo clic sul pulsante Lancio(pulsante con un punto esclamativo) o pulsante Visualizzazione sulla barra degli strumenti.

Nelle situazioni in cui è necessario modificare la proprietà di una query, fare doppio clic sull'area vuota nella parte superiore della finestra Richiedi costruttore - si aprirà una finestra Richiedi proprietà.

Query create in modalità progettista E Maestri, generare comandi in linguaggio SQL. È possibile visualizzare questi comandi selezionandoli dal menu Visualizzazione opzione modalitàSQL.

ESEMPIO:

Seleziona le informazioni sui prodotti in quantità superiori a 100, ma inferiori a 300 e che costano meno di 100 rubli.

Riso. 10. Interrogare il costruttore con una condizione

Riso. 11. Risultato della query con condizione

2. Join interno su un campo

Seleziona una scheda Richieste finestra del database, fare clic sul pulsante Creare. Si aprirà una finestra di dialogo Nuova richiesta. Seleziona un'opzione Costruttore.

Nella finestra Aggiunta di una tabella selezionare in sequenza diverse tabelle correlate su cui verrà costruita la query. In cima Generatore di query vengono visualizzate le tabelle correlate.

Compila il modulo di richiesta.

Fare clic sul pulsante Lancio O Visualizzazione per visualizzare i risultati della query.

ESEMPIO:

Selezionare i nomi dei prodotti per la fornitura dei quali è stato concluso un contratto, indicando i nomi dei loro produttori e il volume della fornitura.

Riso. 12. Progettazione di query multitabella

Riso. 13. Risultato di una query su più tabelle

3. Connessioni esterne

Seguire i passaggi 1-3 della sezione Join interno su un campo.

Stabilire una relazione tra le tabelle se non è stata precedentemente registrata schema DB.

Fare clic sulla linea di unione dei campi per evidenziarla.

Seleziona i comandi dal menu Visualizzazione opzione Opzioni di fusione. Viene visualizzata una finestra di dialogo Unisci opzioni.

Selezionare il tipo di unione, fare clic sul pulsante OK. Dopo questa operazione sulla linea di unione apparirà una freccia che indica il tipo di connessione.

4. Includere una transazione di gruppo nel modulo di richiesta

Fare clic sul pulsante Operazioni di gruppo, cartello  sulla barra degli strumenti Generatore di query. Nel modulo di richiesta sopra la riga Ordinamento apparirà la linea Operazione di gruppo.

Sostituisci in linea Operazione di gruppo installazione Raggruppamento per il richiesto operazione di gruppo selezionandolo dalla casella combinata: Somma, Media, Contare, MIn, Massimo, StDev, Var, Primo, Scorso, Espressione(espressione),Dove(condizione),Gruppo di(raggruppamento).

Espressione ti consente di inserire un'espressione invece di un nome di campo in una cella , ad esempio, per calcolare l'intervallo di valori per una determinata colonna X:Massimo([ X]) – minimo([ X]).

Condizione indica che questo campo verrà utilizzato per impostare le condizioni per la selezione dei record. La condizione di selezione stessa viene scritta nella cella della riga Condizione di selezione.

3. Completa la richiesta.

ESEMPIO:

Seleziona il numero totale di prodotti di ciascun produttore, indicandone il nome.

Riso. 14. Costruttore di query con raggruppamento di record

Riso. 15. Risultato di una query con raggruppamento di record

5. Includere i campi calcolati in un modulo di richiesta

Generatore di query .

Fare clic sulla riga Campo secondo la colonna vuota del modulo di richiesta. Immettere il nome della colonna, seguito da due punti e da un'espressione. Per inserire un'espressione, fare clic sul pulsante Costruire, avvantaggiarsi Costruttore di espressioni.

In cima Costruttore di espressioni Nell'area di input, crea un'espressione.

In una colonna di campo calcolato, spostare il cursore sulla riga Operazione di gruppo e seleziona dall'elenco a discesa Espressione(se non selezioni l'opzione Espressione, una volta eseguita, la richiesta apre una finestra di dialogo Immettere il valore del parametro o restituisce un messaggio di errore).

Esegui la richiesta.

ESEMPIO:

Calcolare l'importo totale dell'ordine per ciascun contratto (definito come il prodotto della quantità di merce e del prezzo più i costi di trasporto).

Riso. 16. Costruttore di query con un campo calcolato

Riso. 17. Risultato di una query con un campo calcolato

6.Creazione di una query parametrica

Crea una query di selezione in modalità Costruttore di query.

Immettere le stringhe in una delle celle Condizione di selezione un parametro, non un valore. Ad esempio, se selezioni i record in base al cognome, allora interrogazione parametrica sul campo Cognome nelle condizioni di selezione la frase [inserire cognome] può essere scritta tra parentesi quadre.

Seleziona dal menu Richiesta opzione Opzioni. Nella finestra di dialogo Richiedi parametri nella colonna di sinistra, inserire tra parentesi quadre il parametro che corrisponde al parametro in modulo di richiesta Generatore di query(il parametro può essere copiato dal modulo di richiesta). Nella colonna di destra, specificare il tipo di dati: premere il tasto , seleziona un tipo di dati dall'elenco.

Esegui la richiesta di esecuzione. Accesso visualizzerà la finestra Immettere il valore del parametro, in cui inserire il valore del parametro desiderato e premere il pulsante OK.

5. Eseguire la richiesta di esecuzione.

ESEMPIO:

Seleziona un elenco di prodotti la cui data di scadenza supera il valore specificato.

Riso. 18. Generatore di query parametriche

Riso. 19. Inserimento della data di scadenza del prodotto

Riso. 20. Risultato di una query parametrica

7. Creazione di query incrociate

1. Crea con progettista nuova richiesta e includere le tabelle necessarie.

2. Seleziona i campi i cui valori verranno utilizzati per creare le righe e le colonne della tabella dinamica e trascinali nel modulo di query.

3. Selezionare un comando dal menu Interrogazione/Incrocio. Titolo progettista cambierà da Query 1: per campione SU Query 1: query incrociata . Il modulo di richiesta visualizzerà la riga Tavolo a croce .

4. Per i campi che saranno righe della tabella a campi incrociati, aprire l'elenco a discesa Riga Tavolo a croce selezionare l'opzione Intestazioni di riga. Ogni campo che costituisce un'intestazione di riga deve avere un'impostazione Operazione di gruppo nella riga Raggruppamento.

5. Per i campi che costituiranno colonne della tabella a campi incrociati, a seconda della query, sono possibili le seguenti azioni:

in linea Tavolo a croce selezionare l'opzione Intestazioni di colonna e in linea Operazione di gruppo installazione raggruppamento, o in linea Operazione di gruppo – Condizione, in linee Condizione di selezione E O scrivere espressione logica.

6. Impostare il campo in base al quale verrà creato il valore totale. In linea Tavolo a croce scegliere Senso. In linea Operazione di gruppo selezionare la funzione finale oppure Espressione. In quest'ultimo caso, nella cella Campo scrivere l'espressione.

7. Eseguire la richiesta di esecuzione.

ESEMPIO:

Seleziona le quantità totali di prodotti ordinati di ciascuna tipologia prodotti da ciascun produttore. Presenta il risultato sotto forma di tabella, dove le righe sono i nomi dei tipi di prodotto e le colonne sono i nomi dei produttori.

Riso. 21. Generatore di query incrociate

Riso. 22. Risultato della query incrociata

8.Interrogazione per creare una tabella

Per creare una tabella dai risultati di una query utilizzata per selezionare i record nelle tabelle correlate, attenersi alla seguente procedura:

Crea una copia della query per selezionare i record dalle tabelle (o creane una nuova).

Apri la richiesta creata in modalità progettista, evidenziandone il nome nell'elenco delle query nella finestra del database e facendo clic sul pulsante Costruttore.

Selezionare un comando dal menu Interroga/Crea tabella.Si aprirà una finestra di dialogo Creazione di una tabella. Inserisci il nome della nuova tabella, lascia il pulsante di opzione in posizione Nel database attuale.

Fare clic sul pulsante OK. La query di selezione viene convertita in una query di creazione di tabella.

Eseguire la query facendo doppio clic sul nome della query nella finestra del database. Prima di eseguire la query viene visualizzato un messaggio che avvisa che verranno apportate modifiche alla nuova tabella (anche se non è stata ancora creata).

Fare clic sul pulsante SÌ. Viene visualizzato un secondo messaggio di avviso relativo al numero di record che verranno inseriti nella nuova tabella come risultato della query. Fare clic sul pulsante SÌ.

Espandi la scheda Tabelle nella finestra DB; Nell'elenco dovrebbe essere visualizzata una nuova tabella.

9.Richiesta di aggiornamento dei record

Crea una nuova query e includi al suo interno la copia creata della tabella.

Selezionare un comando dal menu Interroga/Aggiorna. Nel modulo di richiesta della linea Ordinamento E Uscita sul display verrà sostituito con la stringa Aggiornamento.

In linea Aggiornamento specificare un'espressione che rappresenta il nuovo valore per il campo corrente.

Se le informazioni non vengono aggiornate in tutti i record della tabella, ma solo in parte di essa, inserire una condizione per la selezione dei record.

Completa la richiesta. Controlla che i record siano aggiornati correttamente.

ESEMPIO:

Aggiorna il costo dei prodotti la cui quantità supera le 500 unità, impostandolo al 90% del costo originale.

Riso. 23. Costruttore della richiesta di aggiornamento

10.Richiesta di eliminazione dei record

Creare una copia della tabella in cui si suppone che i record vengano aggiornati.

Crea una nuova query e includi al suo interno la tabella appropriata.

Trascina e rilascia i campi nel modulo di richiesta i cui valori verranno utilizzati per selezionare i campi. In linea Condizioni di selezione indicare i criteri di selezione.

Eseguire una query di selezione per visualizzare i record da eliminare.

Aprire finestra del database. Seleziona una scheda Tabelle.

Attiva la richiesta di selezione creata e vai alla modalità Richiedi costruttore.

Selezionare un comando dal menu Interroga/Elimina. Nel modulo di richiesta della linea Ordinamento E Uscita sul display verrà sostituito con la stringa Rimozione.

Fare clic sul pulsante Lancio barre degli strumenti. Viene visualizzata una finestra di messaggio che richiede conferma per eliminare le voci.

Eliminazione dei record nella tabella “ uno", per cui nella tabella “ molti" sono presenti record correlati, viola la regola di integrità dei dati. Pertanto, l'opzione di integrità dei dati impostata su una relazione tra tabelle potrebbe impedire l'eliminazione dei record. Dovrebbe essere applicata un'eliminazione a cascata.

ESEMPIO:

Riso. 24. Elimina il costruttore della richiesta

11. Richiedi di aggiungere record

Una query di accodamento viene spesso utilizzata per copiare dati da una tabella a un'altra.

Seleziona una scheda Richieste finestra del database, fare clic sul pulsante Creare. Si aprirà una finestra di dialogo Nuova richiesta. Seleziona un'opzione Costruttore.

Nella finestra Aggiunta di una tabella selezionare la tabella da cui verranno presi i dati.

Passa alla riga Campo modulo di richiesta per i campi i cui valori verranno inseriti in un'altra tabella.

Seleziona i comandi del menu Richiedi/Aggiungi. Apparirà una finestra Aggiunta, dove devi specificare il nome della tabella a cui dovresti aggiungere i dati e a quale database appartiene. Se il database non è quello corrente, ma un altro, allora dovrai specificare il nome completo del file contenente il database.

Nel modulo di richiesta dopo la riga Ordinamento la linea verrà inclusa Aggiunta, in cui è necessario indicare i campi della tabella in cui vengono aggiunti i dati. Se i nomi dei campi sono gli stessi UNConcessa verrà inserito automaticamente nella riga Aggiunta nomi dei campi.

Esegui la richiesta di esecuzione. Guarda come è cambiata la tabella con l'aggiunta di nuove righe.

Se in una richiesta di Aggiunta ci sono campi come Contatore, puoi lavorare con tali campi in due modi:

non includere campi di tipo Contatore nei campi che devono essere aggiunti; poi nella tabella in cui vengono aggiunti i dati, la numerazione della sequenza dei dati continuerà, cioè se c'è una tabella dei dipendenti con i numeri da 1 a 100 e aggiungila nuove voci, quindi la loro numerazione inizierà dal numero 101;

includere campi di tipo Contatore all'elenco delle richieste come campi aggiunti, quindi i valori che avevano nella tabella originale verranno salvati nei campi dei record aggiunti; se tali valori esistono già nei campi della tabella a cui si stanno aggiungendo i record, ciò potrebbe causare un errore. Un messaggio di errore viene visualizzato sullo schermo prima dell'esecuzione della richiesta. Se si verifica un errore, selezionare il pulsante Annulla e correggere l'errore.

Metodo 2: fare clic con il tasto destro sui valori del campo desiderato nell'area ΣValues ​​e selezionare:

Inoltre, per selezionare una funzione diversa dall'elenco suggerito, configurare il formato del campo e modificare il nome del campo.

Per sfruttare gli effetti. Oggi ti insegneremo. Segna le celle che vuoi disegnare. Lezione 3: Tabelle e grafici pivot. Diciamo che abbiamo un set da analizzare. Per utilizzare una tabella pivot, seleziona una cella qualsiasi nel foglio di lavoro e scegli Tabella pivot dal menu Inserisci.

Quindi puoi ottenere le informazioni. Quando crei i grafici, non importa se la tabella ha un bordo o altri elementi di formattazione. Introduzione a formule e funzioni L'esecuzione di calcoli, semplici o complessi, può essere noiosa e richiedere molto tempo.

 Calcoli aggiuntivi, quindi seleziona l'opzione di calcolo desiderata.

Riutilizzare un campo originale in un rapporto di tabella pivot

Puoi effettuare più calcoli utilizzando lo stesso campo di origine. Per fare ciò, è necessario aggiungere il campo originale dall'elenco dei campi nella tabella pivot all'intervallo di valori Σ Valori [Σ

Valori] più volte, quindi regolare i parametri del campo. Ciò ti consentirà di vedere contemporaneamente sia il calcolo stesso che il calcolo eseguito in base ad esso.

Aggiungi campi calcolati a una tabella pivot

Non puoi aggiungere nuove righe o colonne a un rapporto di tabella pivot per inserire formule o aggiungere ulteriori calcoli, ma puoi aggiungere un campo calcolato che ti consenta di inserire formule per calcolare nuovi dati in base ai dati esistenti.

Foglio di lavoro Database 1 Foglio di lavoro Database 1 Crea una cartella "Database" per archiviare tutti i dati del corso. Crea una query che visualizza titoli e descrizioni. Viene visualizzata la finestra di dialogo Gestisci database. Utilizzando questa finestra, l'utente gestisce il database e tutti i suoi elementi.

Cos'è un foglio di calcolo? Un foglio di calcolo è un programma per eseguire calcoli matematici. Con il suo aiuto possiamo anche effettuare calcoli leggibili nel modulo. Per usufruire di azioni specifiche. La parte più laboriosa è la preparazione dei dati. Problema 1 Gruppo di matematica 2 Segui questi passaggi in sequenza. Gestisci la tabella.

L'inserimento di un campo calcolato direttamente in una tabella pivot lo è soluzione migliore. Non è necessario gestire formule o preoccuparsi dell'estensibilità della tabella man mano che l'origine dati cresce o viene modificata e la tabella pivot rimane sufficientemente flessibile da modificare le definizioni dei campi.

Un altro enorme vantaggio di questo metodo è che puoi modificare la struttura della tabella pivot e persino calcolare altri campi dati per i campi calcolati senza preoccuparti di errori di formula o riferimenti di cella interrotti.

Università della Tecnologia della Pomerania Occidentale a Stettino Facoltà di Tecnologia Marina e dei Trasporti Dipartimento di Strutture, Meccanica e Tecnologia Nave in un laboratorio di ricerca operativa. Le tabelle pivot vengono utilizzate per analizzare rapidamente i dati. Per utilizzare le tabelle pivot, dobbiamo avere i dati adeguatamente preparati con le intestazioni.

Per evitare tali situazioni, dovresti formattare i nostri dati come una tabella. Per formattare i dati come tabella. Questo selezionerà tutte le intestazioni. . La scelta successiva è la posizione della tabella pivot e per mantenere la leggibilità dei dati dovresti lasciare l'opzione Nuovo foglio, che inserirà nuova foglia al tavolo.

Microsoft Excel 2010. Livello 2. Funzionalità avanzate

Pertanto, i vantaggi derivanti dall'aggiunta dei campi calcolati a una tabella pivot sono chiari:

 Elimina potenziali errori nelle formule e nei riferimenti di cella.

 La possibilità di aggiungere e rimuovere dati da una tabella pivot senza modificare i calcoli originali.

È presente un nuovo foglio di tabella pivot vuoto. Sul lato destro c'è un elenco di campi, che contiene tutte le intestazioni della nostra tabella, ed una pianta con le sue aree. Diciamo che vogliamo vedere chi è stato il best seller. In questo caso seleziona il campo Venditore e il campo Importo, perché dopo la vendita controlleremo chi è stato il migliore. Il rapporto sarà simile a questo.

L'impostazione predefinita è colonna. Le tabelle pivot possono essere facilmente modificate e se desideri verificare il paese più venduto, annulla semplicemente l'acquisto e contrassegnalo con il venditore. Immediatamente riceviamo un nuovo report dal quale potete leggere il risultato.

 La possibilità di ricalcolare automaticamente i dati quando si modifica o si aggiorna una tabella pivot.

 Fornisce flessibilità per modificare i calcoli quando cambiano le definizioni degli elementi.

 Capacità di gestire efficacemente i calcoli.

Crea un campo calcolato:

1. Seleziona una cella della tabella pivot;

Possiamo anche aggiungere una sottocategoria al Venditore selezionando questa casella. Il report sulle vendite riassume automaticamente i valori visualizzati, ma questi totali spesso rendono difficile l'analisi. Possiamo anche eliminare il totale e modificare il layout del report.

Per modificare rapidamente il rapporto e vedere come ha venduto Dobrovalsky ad altri paesi, è sufficiente modificare l'ordine delle condizioni nei campi catturando il Venditore e trascinandolo attraverso lo stato. A parte Irlanda e Germania, è chiaro che i risultati delle vendite non sono dei migliori. Quando fai clic su Vendi in Irlanda, vedrai un nuovo foglio con una tabella contenente tutte le transazioni di Dobrovalsky in Irlanda.

2. Nella scheda Opzioni, nel gruppo Calcoli, espandere l'elenco Campi.

elementi e insiemi e selezionare Campo calcolato.

3. Nella finestra Inserimento di un campo calcolato:

 Nel campo Nome, inserire il nome del nuovo campo.

 Nel campo Formula, creare una formula, iniziando con il segno = e selezionando

campi disponibili nell'elenco Campi (per inserirli è possibile fare doppio clic su un campo oppure selezionare un campo, quindi fare clic su Aggiungi campo).

Se vogliamo stampare i nostri dati, prenderemo il campo Venditore dall'area Righe e lo sposteremo nell'area Colonne. Ciò farà sì che la tabella venga distribuita su tutto il foglio. In questa finestra possiamo anche specificare il formato numerico visualizzato in tutte le celle del campo. Basta utilizzare il pulsante "Formato numero" e selezionare, ad esempio, la valuta appropriata.

Anche i filtri sono utili. Una volta aggiunti a quest'area, i campi fornitore vengono visualizzati nell'angolo in alto a sinistra di un elenco a discesa dal quale è possibile selezionare una o più opzioni. Vediamo quale mese è stata la migliore vendita. Di seguito selezioniamo la categoria in base alla quale vogliamo raggruppare i dati.

 Fare clic su Aggiungi.

L'impostazione dell'aspetto di un campo calcolato, come qualsiasi altro campo, avviene nella finestra

Parametri del campo valore.

Modifica di un campo calcolato

1. Nella scheda Opzioni, nel gruppo Calcoli, espandere l'elenco Campi.

elementi e insiemi, selezionare Campo calcolato.

Sappiamo tutti come utilizzare le tabelle pivot. Hanno molti vantaggi, ma ci sono anche molti svantaggi. Molte persone credono che il problema principale con le tabelle pivot sia che non c'è modo di fare calcoli: questo è un malinteso. Perché potrebbe essere necessario? Supponiamo che tu crei un budget utilizzando le tabelle pivot. I dati nella tabella originale sono presentati su base trimestrale, ai fini del budget sarà necessario aggiungere a questi dati un importo annuale.

Celebriamo i mesi e li abbracciamo. Riceveremo un report ordinato per mese. Puoi anche dividere i dati in diverse categorie. Ritornando al raggruppamento dei configuratori e selezionando “Trimestre e mese”, otteniamo l'aspetto del report. Il raggruppamento può essere utilizzato anche per i numeri.

Quando apri menù contestuale, il campo Righe e l'opzione Raggruppa mostrano una finestra diversa rispetto a quando si raggruppano le date. I numeri sono raggruppati in intervalli, che definiamo in base all'inizio, alla fine e al volume dell'intervallo. In questo caso lo impostiamo su 500, quindi il primo gruppo conterrà tutte le transazioni di valore compreso tra 0 e 500 zloty.

In effetti, esiste una tale possibilità e si realizza in tre modi.

Puoi rispolverare la tua conoscenza delle basi delle tabelle pivot.

Primo modo. Effettua i calcoli in una tabella con i dati originali. Il metodo, francamente, non è una fontana: nel posto sbagliato.

Secondo modo. Costruire calcoli accanto a una tabella pivot non è, ancora una volta, il metodo migliore. Se la tabella pivot cambia (e vale la pena utilizzare le tabelle pivot esattamente quando ci aspettiamo cambiamenti nella tabella).

Terza via. Incorpora i campi calcolati nella tabella pivot stessa. Ma questo è un buon modo. Questo viene fatto come segue. Quando la tabella pivot è già stata creata, posiziona il cursore sulla tabella pivot (sulla barra multifunzione appaiono due schede relative a questa tabella pivot) e fai clic sulla scheda visualizzata Opzioni, Poi Campi,Elementi, &Imposta dal blocco Calcoli, selezionare dal menu a tendina Calcolatocampo. In Office 2007, questo pulsante si trovava nella stessa scheda Opzioni, solo che il pulsante era separato.

Nella finestra che appare, nel campo Nome Immettere il nome del campo desiderato. Nel campo Formula inserisci la formula che ti serve inserendo i campi dall'elenco sottostante e facendo clic sul pulsante Inserirecampo.

Otterrai una tabella con un nuovo campo di calcolo. Il tavolo è pronto!

Il file Excel può essere scaricato da qui.

Fatto interessante. Quando si utilizza il terzo metodo, il programma esegue effettivamente il calcolo per ciascuna riga e quindi applica i parametri necessari: somma, media, ecc.

Ultime novità

• Excel avanzato: perché ho smesso di utilizzare la funzione V...

  Ho già scritto che la funzione CERCA.VERT (VLOOKUP) è probabilmente la funzione più utile dopo semplici operazioni aritmetiche...

• Come Excel ricalcola una cartella di lavoro e perché la volatilità dovrebbe essere evitata...

  Se lavori spesso con file di grandi dimensioni che eseguono un gran numero di calcoli che dipendono l'uno dall'altro, o...

• Come costruire rapidamente un grafico a cascata

  Excel non dispone di grafici a cascata standard. Pertanto, per creare questo tipo di diagrammi, di solito viene utilizzato il gis...

• Finestra di controllo per il monitoraggio dei risultati

  Accade spesso di lavorare con tabelle di grandi dimensioni che alla fine calcolano 1-2 indicatori, ma dipendono da...

• Grafico bullet per confrontare gli indicatori pianificati e quelli effettivi...

  Spesso è necessario confrontare i principali indicatori finanziari ed economici con quelli pianificati, ad esempio, allo scopo di...

• Tatsuo Horiuchi è un artista di 73 anni che disegna in Excel.

  "Non ho mai usato Excel al lavoro, ma ho visto altre persone creare dei diagrammi e dei grafici piuttosto carini...

• Come elevare un numero a potenza ed estrarne la radice

  Sapevi che per elevare un numero a potenza in Excel esiste carattere speciale^ (sul sei in inglese...

I campi calcolati sono progettati per visualizzare i valori delle espressioni in base ai dati di origine nei report. I campi calcolati nei report possono essere utilizzati per ottenere totali o eseguire calcoli speciali, ad esempio, per visualizzare il prezzo totale di un ordine calcolando il prodotto della quantità di un articolo e del prezzo di un articolo. Esaminiamo la procedura per la creazione dei campi calcolati utilizzando l'esempio del report "Articoli in magazzino" creato in precedenza.

Per aggiungere un campo calcolato "Importo" a un report:

1. Aprire il report Articoli in magazzino in modalità Progettazione.
2. Nella casella degli strumenti, fare clic sul pulsante Iscrizione(Etichetta) e posiziona il nuovo testo a destra di "Unità in magazzino" nella sezione dell'intestazione. Immettere la parola "Somma" come testo dell'etichetta.
3. Per creare un campo calcolato Somma, fare clic sul pulsante Campo(Casella di testo) nella casella degli strumenti e aggiungi una casella di testo a destra della casella di testo UnitsInStock nella sezione dati. Rimuovi l'etichetta del nuovo campo di testo.
4. Fare clic all'interno del campo di testo e immettere la seguente espressione come valore:

   = [Disponibile]* [Prezzo] (= * ).

   Apri la finestra delle proprietà campi facendo doppio clic su di esso e sulla scheda Disposizione(Formato) nella casella combinata delle proprietà Formato del campo(Formato) selezionare un valore Monetario(Valuta).

Commento

Esistono diversi modi per inserire un'espressione nel campo di testo: selezionarla e premere la combinazione di tasti +per accedere alla modalità di modifica del contenuto del campo oppure fare doppio clic sul campo per visualizzare una finestra di dialogo Proprietà(Proprietà) e quindi immettere l'espressione come valore della proprietà Dati(Fonte).

Riso. 6.27.

1. Ripeti i passaggi 3 e 4 per creare un campo di testo nella sezione commenti del gruppo Categoria, ma inserisci quanto segue come espressione del totale parziale: =Sum([InStock] * [Price]) (=Sum( * )). Fare clic sul pulsante Grassetto(Grassetto) nella barra del formato per impostare il testo nel nuovo campo in grassetto. Apri la finestra di dialogo Proprietà(Proprietà) e apri la scheda al suo interno Altro(Altro), sul campo Nome(Nome) Immettere un nome per la casella di testo Somma. Quindi espandere la scheda Disposizione(Formato) e nella casella combinata delle proprietà Formato del campo(Formato) selezionare un valore Monetario(Valuta).
2. Poiché la larghezza dell'area dati del nostro report è aumentata, riduci la larghezza dei margini della pagina in modo che tutti i campi rientrino in un'unica pagina. Per fare ciò, seleziona il comando File, Imposta pagina(File, Imposta pagina) e modificare la larghezza predefinita dei margini sinistro e destro su 10 mm (Vedere "Visualizzazione e stampa dei rapporti" in questo capitolo.)
3. Fare clic sul pulsante Visualizzazione(Visualizza) sulla barra degli strumenti Progettista di report(Report Design) per valutare i risultati del tuo lavoro. Il report sarà simile a quello mostrato in Fig. 6.27. Ai dati precedenti è stata aggiunta la colonna più a destra con i valori del costo di ciascun prodotto nella quantità disponibile in magazzino e i subtotali del costo della merce per ciascuna categoria.