Consolidare i dati in più fogli di lavoro

Per riepilogare e creare report dei risultati da fogli di lavoro separati, è possibile consolidare i dati di ogni foglio in un foglio di lavoro master. I fogli possono trovarsi nella stessa cartella di lavoro del foglio di lavoro master o in altre cartelle di lavoro. Quando si consolidano i dati, si assemblano i dati in modo da aggiornarsi e aggregare più facilmente se necessario.

Ad esempio, se si ha un foglio di lavoro delle spese per ognuno degli uffici locali, è possibile usare il consolidamento per mettere insieme tali cifre in un foglio di lavoro master delle spese aziendali. Questo foglio di lavoro master potrebbe anche contenere dati sulle vendite totali e medie, sugli attuali livelli di scorte e sui prodotti più venduti per l'intera organizzazione.

Suggerimento: Se si consolidano spesso i dati, potrebbe essere utile creare nuovi fogli di lavoro da un modello di foglio di lavoro che usa un layout coerente. Per altre informazioni sui modelli, vedere: Creare un modello. È anche il momento ideale per impostare il modello con le tabelle di Excel.

Esistono due modi per consolidare i dati, in base alla posizione o alla categoria.

Consolidamento in base alla posizione: i dati nelle aree di origine hanno lo stesso ordine e usano le stesse etichette. Usare questo metodo per consolidare i dati da una serie di fogli di lavoro, ad esempio i fogli di lavoro del budget di reparto che sono stati creati in base allo stesso modello.

Consolidamento in base alla categoria: Quando i dati nelle aree di origine non vengono disposti nello stesso ordine ma usano le stesse etichette. Usare questo metodo per consolidare i dati da una serie di fogli di lavoro con layout differenti, ma con le stesse etichette dati.

Il consolidamento in base alla categoria è simile alla creazione di una tabella pivot. Con una tabella pivot, è possibile tuttavia riorganizzare facilmente le categorie. Se è necessario un consolidamento più flessibile in base alla categoria, è consigliabile creare una tabella pivot .

Nota: Gli esempi di questo articolo sono stati creati con Excel 2016. Anche se la visualizzazione potrebbe essere diversa se si usa un'altra versione di Excel, la procedura è la stessa.

Seguire questa procedura per consolidare diversi fogli di lavoro in un foglio di lavoro master:

Se non è già stato fatto, configurare i dati in ogni foglio costituente eseguendo le operazioni seguenti:
- Verificare che ogni intervallo di dati sia in formato elenco. Ogni colonna deve avere un'etichetta (intestazione) nella prima riga e contenere dati simili. Nell'elenco non devono essere presenti righe o colonne vuote.
- Inserire ogni intervallo in un foglio di lavoro distinto, ma non immettere nulla nel foglio di lavoro master in cui si prevede di consolidare i dati. Excel eseguirà questa operazione automaticamente.
- Verificare che ogni intervallo abbia lo stesso layout.
Nel foglio di lavoro master fare clic sulla cella superiore sinistra dell'area in cui si desidera visualizzare i dati consolidati.

Nota: Per evitare di sovrascrivere i dati esistenti nel foglio di lavoro master, assicurarsi di lasciare un numero sufficiente di celle a destra e sotto questa cella per i dati consolidati.
Fare clic su Dati>Consolida nel gruppo Strumenti dati .
Nella casella Funzione selezionare la funzione di riepilogo che si vuole usare per il consolidamento dei dati. La funzione predefinita è SOMMA.

Ecco un esempio in cui sono stati scelti tre intervalli di fogli di lavoro:
Selezionare i dati.

Quindi, nella casella Riferimento fare clic sul pulsante Comprimi per ridurre il pannello e selezionare i dati nel foglio di lavoro.

Fare clic sul foglio di lavoro che contiene i dati da consolidare, selezionare i dati e quindi fare clic sul pulsante Espandi finestra a destra per tornare alla finestra di dialogo Consolida.Se un foglio di lavoro contenente dati da consolidare si trova in un'altra cartella di lavoro, fare clic su Sfoglia per individuarla. Dopo aver individuato e fatto clic su OK, Excel immette il percorso del file nella casella Riferimento e aggiunge un punto esclamativo a tale percorso. È quindi possibile continuare a selezionare altri dati.

Ecco un esempio in cui sono stati selezionati tre intervalli di fogli di lavoro:
Nella finestra popup Consolida fare clic su Aggiungi. Ripetere questa operazione per aggiungere tutti gli intervalli da consolidare.
Confronto tra aggiornamenti automatici e manuali: Se si vuole che excel aggiorni automaticamente la tabella di consolidamento quando i dati di origine cambiano, basta selezionare la casella Crea collegamenti con i dati di origine . Se questa casella rimane deselezionata, è possibile aggiornare manualmente il consolidamento.
Note:
- Quando le aree di origine e destinazione sono nello stesso foglio, è possibile creare collegamenti.
- Se è necessario modificare l'estensione di un intervallo o sostituire un intervallo, fare clic sull'intervallo nel popup Consolida e aggiornarlo seguendo la procedura precedente. Verrà creato un nuovo riferimento di intervallo, quindi è necessario eliminare quello precedente prima di consolidare nuovamente. È sufficiente scegliere il riferimento precedente e premere CANC.
Fare clic su OK per generare automaticamente il consolidamento. Facoltativamente, è possibile applicare la formattazione. È necessario formattarlo una sola volta, a meno che non si eservi nuovamente il consolidamento.
- Le etichette che non corrispondono alle etichette presenti nelle altre aree di origine verranno posizionate in righe o colonne distinte nel consolidamento.
- Verificare che le categorie che non si desidera consolidare abbiano etichette univoche visualizzate in un solo intervallo di origine.

Se i dati da consolidare si trova in celle diverse in fogli di lavoro diversi:

Immettere una formula con riferimenti di cella ad altri fogli di lavoro, uno per ogni foglio di lavoro distinto. Per consolidare, ad esempio, i dati di fogli di lavoro denominati Vendite (nella cella B4), Personale (nella cella F5) e Marketing (nella cella B9), nella cella A2 del foglio di lavoro master, è necessario immettere quanto segue:

Riferimento della formula in più fogli di Excel

Suggerimento: Per immettere un riferimento di cella, ad esempio Vendite! B4: in una formula senza digitare, digitare la formula fino al punto in cui è necessario il riferimento, fare clic sulla scheda del foglio di lavoro e quindi fare clic sulla cella. Excel completerà automaticamente il nome del foglio e l'indirizzo della cella. NOTA: le formule in questi casi possono essere soggette a errori, poiché è molto facile selezionare accidentalmente la cella errata. Può anche essere difficile individuare un errore dopo aver immesso una formula complessa.

Se i dati da consolidare si trova nelle stesse celle in fogli di lavoro diversi:

Immettere una formula con un riferimento 3D che utilizza un riferimento a un intervallo di nomi di fogli di lavoro. Ad esempio, per consolidare i dati nelle celle A2 da Vendite fino a Marketing incluso, nella cella E5 del foglio di lavoro master è necessario immettere quanto segue:

Formula di riferimento foglio 3D di Excel