Questo episodio mostra tre modi per eseguire i totali parziali.
Un totale parziale è, per un elenco di valori numerici, una somma dei valori dalla prima riga alla riga del totale parziale. Gli usi comuni di un totale parziale sono in un registro del libretto degli assegni o in un foglio contabile. Esistono molti modi per creare un totale parziale, due dei quali sono descritti di seguito.
La tecnica più semplice consiste nel sommare, su ogni riga, il totale parziale dalla riga sopra al valore nella riga. Quindi la prima formula nella riga 2 è:
=SUM(D1,C2)
Il motivo per cui utilizziamo la funzione SOMMA è perché, nella prima riga, stiamo guardando l'intestazione nella riga sopra. Se usiamo la formula più semplice e più intuitiva, =D1+C2verrà generato un errore perché il valore dell'intestazione è testo anziché numerico. La magia è che la funzione SOMMA ignora i valori di testo, che vengono aggiunti come valori zero. Quando la formula viene copiata su tutte le righe in cui si desidera un totale parziale, i riferimenti di cella vengono regolati di conseguenza:
Anche l'altra tecnica utilizza la funzione SOMMA, ma ogni formula somma tutti i valori dalla prima riga alla riga che mostra il totale parziale. In questo caso usiamo un segno di dollaro ($) per rendere la prima cella nel riferimento un riferimento assoluto, il che significa che non viene regolato quando viene copiato:
Entrambe le tecniche non sono influenzate dall'ordinamento e dall'eliminazione delle righe ma, quando si inseriscono le righe, la formula deve essere copiata nelle nuove righe.
Excel 2007 ha introdotto la tabella che è una reimplementazione dell'elenco in Excel 2003. Le tabelle hanno introdotto una serie di funzioni molto utili per le tabelle di dati come formattazione, ordinamento e filtro. Con l'introduzione delle tabelle ci è stato anche fornito un nuovo modo di fare riferimento alle parti di una tabella. Questo nuovo stile di riferimento è chiamato riferimento strutturato.
Per convertire l'esempio precedente in una tabella, selezioniamo i dati che vogliamo includere nella tabella e premiamo Ctrl + T.Dopo aver visualizzato un prompt che ci chiede di confermare l'intervallo della tabella e se ci sono o meno intestazioni esistenti, Excel converte i dati in una tabella formattata:
Nota che le formule che abbiamo inserito in precedenza rimangono le stesse.
Una delle funzionalità utili offerte da Tabelle è la formattazione automatica e la manutenzione delle formule quando le righe vengono aggiunte, rimosse, ordinate e filtrate. È in particolare il mantenimento della formula su cui ci concentreremo e che può essere problematico. Per mantenere le tabelle funzionanti mentre vengono manipolate, Excel utilizza colonne calcolate che sono colonne con formule come la colonna D nell'esempio precedente. Quando vengono inserite nuove righe in fondo, Excel popola automaticamente le nuove righe con la formula "predefinita" per quella colonna. Il problema con l'esempio precedente è che Excel viene confuso con le formule standard e non le gestisce sempre correttamente. Ciò è reso evidente quando vengono aggiunte nuove righe in fondo alla tabella (selezionando la cella in basso a destra nella tabella e premendo TAB):
Questa carenza viene risolta utilizzando la referenziazione strutturata più recente. Il riferimento strutturato elimina la necessità di fare riferimento a celle specifiche utilizzando lo stile di riferimento A1 o R1C1 e utilizza invece nomi di colonna e altre parole chiave per identificare e fare riferimento alle parti di una tabella. Ad esempio, per creare la stessa formula del totale parziale usata sopra ma utilizzando riferimenti strutturati abbiamo:
=SUM(INDEX((Sales),1):(@Sales))
In questo esempio abbiamo un riferimento al nome della colonna, "Vendite", insieme al segno (@) per fare riferimento alla riga nella colonna in cui si trova la formula, nota anche come riga corrente.
Per implementare il primo esempio sopra in cui abbiamo aggiunto il valore del totale parziale nella riga precedente all'importo delle vendite nella riga corrente, puoi utilizzare la funzione OFFSET:
=SUM(OFFSET((@(Running Total)),-1,0),(@Sales))
Se gli importi utilizzati per calcolare il totale parziale sono in due colonne, ad esempio una per "Debiti" e una per "Crediti", la formula è:
=SUM(INDEX( (Credit),1):(@Credit))- SUM(INDEX( (Debit),1):(@Debit))
Qui stiamo utilizzando la funzione INDICE per individuare le celle di credito e di debito della prima riga e sommare l'intera colonna fino a includere i valori della riga corrente. Il totale parziale è la somma di tutti gli accrediti fino alla riga corrente inclusa meno la somma di tutti gli addebiti fino alla riga corrente inclusa.
Per ulteriori informazioni sui riferimenti strutturati in particolare e sulle tabelle in generale, consigliamo il libro Tabelle Excel: una guida completa per la creazione, l'utilizzo e l'automazione di elenchi e tabelle di Zack Barresse e Kevin Jones.
Quando ho chiesto ai lettori di votare per i loro suggerimenti preferiti, i tavoli erano popolari. Grazie a Peter Albert, Snorre Eikeland, Nancy Federice, Colin Michael, James E. Moede, Keyur Patel e Paul Peton per aver suggerito questa funzione. Peter Albert ha scritto il suggerimento bonus per i riferimenti leggibili. Zack Barresse ha scritto il suggerimento bonus Running Totals. Quattro lettori hanno suggerito di utilizzare OFFSET per creare intervalli in espansione per i grafici dinamici: Charley Baak, Don Knowles, Francis Logan e Cecelia Rieb. Le tabelle ora fanno la stessa cosa nella maggior parte dei casi.
Guarda un video
- Questo episodio mostra tre modi per eseguire i totali parziali
- Il primo metodo ha una formula diversa nella riga 2 rispetto a tutte le altre righe
- Il primo metodo è = Sinistra nella riga 2 e = Sinistra + Su nelle righe dalla 3 alla N
- Se provi a utilizzare la stessa formula, ottieni un errore #Value con = Total + Number
- Il metodo 2 utilizza
=SUM(Up,Left)o=SUM(Previous Total,This Row Amount) - SOMMA ignora il testo in modo da non ottenere un errore VALUE
- Il metodo 3 utilizza un intervallo in espansione:
=SUM(B$2:B2) - Le gamme in espansione sono interessanti ma lente
- Leggi il white paper di Charles Williams su Excel Formula Speed
- Il terzo metodo è un problema quando usi Ctrl + T e aggiungi nuove righe
- Excel non riesce a capire come scrivere la formula
- Le soluzioni alternative richiedono una certa conoscenza dei riferimenti strutturati nelle tabelle
- La soluzione 1 è il lento
=SUM(INDEX((Qty),1):(@Qty)) - La soluzione 2 è il volatile
=SUM(OFFSET((@Total),-1,0),(@Qty)) - (@Qty) si riferisce a Qty su questa riga
- (Qtà) si riferisce a tutti i valori Qtà
Trascrizione del video
Impara Excel per Podcast, episodio 2004 - Totali correnti
Metterò in podcast l'intero libro. Fare clic su I nell'angolo in alto a destra per iscriversi.
Ehi bentornato al netcast di mystic cell. Sono Bill Jelen. Ora questo argomento nel libro, sono stato contribuito dal mio amico Zach Parise. Parliamo di tabelle Excel, Zach è l'esperto mondiale di tabelle Excel. Ha scritto un libro sulle tabelle di Excel, ma prima parliamo dei totali correnti non nelle tabelle.
Quindi, quando penso ai totali parziali, ci sono tre modi diversi per fare i totali parziali, e il modo in cui ho sempre iniziato è nella prima riga che dici semplicemente, porta il valore. Così uguale qualunque cosa sia alla mia sinistra. Va bene, quindi questo formato qui è solo = B2. Questi sono tutti testi di formule qui nell'angolo a destra in modo da vedere cosa stiamo usando, e da lì in poi, è una semplice piccola formula uguale al valore precedente, più il valore corrente a destra e copialo in basso , ma sai ora, abbiamo questo problema che richiedeva due formule diverse e sai che in una situazione perfetta hai la stessa identica formula fino in fondo, e il motivo per cui dobbiamo avere una formula diversa lì nella prima riga è che quando provi ad aggiungere uguale a 7 più la parola totale è un errore di valore,ma il lavoro interessante qui fuori, non è solo usare sinistra più su, ma usare = (SUM) del valore precedente più la quantità in questa riga, e vedere che alcuni sono abbastanza lontani da ignorare i testi. Giusto in modo che consenta la stessa formula. fino in fondo.
Va bene, allora è stato quando stavo iniziando a utilizzare Excel, lo stavo usando e poi ho scoperto l'intervallo in espansione, l'intervallo in espansione dice che faremo L $ 2: L2 e quello che succede è che inizia sempre dalla riga 2, ma poi scende alla riga corrente. Quindi, quando guardi come funziona quando viene copiato, abbiamo sempre iniziato la riga 2, ma scendiamo alla riga corrente e questo è diventato il mio metodo preferito. Ero tipo, oh, questo è molto più sofisticato e quando andiamo in Opzioni di Excel, vai alla scheda Formule e scegli R1C1 in Stile di riferimento. Va bene vedi, R1C1, tutte queste formule sono esattamente le stesse fino in fondo. Non so se capisci R1C1, è solo bello sapere che abbiamo formule R1C1 identiche fino in fondo.
Torniamo indietro. Quindi questo metodo qui è il metodo che mi è piaciuto, fino a quando Charles Williams, un MBP di Excel dall'Inghilterra, che ha un documento straordinario sulla velocità delle formule, sulla velocità delle formule di Excel, ha completamente sfatato questo metodo. Questo metodo, diciamo che hai 10.000 righe questo, ogni singola formula sta guardando due riferimenti. Quindi stai guardando 20.000 riferimenti, ma questo, questo sta guardando due, questo sta guardando tre, questo sta guardando quattro, questo sta guardando cinque e l'ultimo sta guardando 10.000 riferimenti, ed è orribilmente più lento e così ho smesso di usare questo metodo.
Poi vado a leggere Zack nel libro di Kevin Jones sulle tabelle Excel e scopro ancora un altro problema con questo metodo. Quindi una delle funzioni utili offerte dalle tabelle è "la formattazione automatica e le righe di manutenzione delle formule vengono aggiunte, rimosse, ordinate e filtrate". Va bene, questa è una citazione dal suo libro. E per aggiungere una riga a una tabella basta andare all'ultima cella della tabella e premere tab. Quindi tutto funziona qui. Siamo scesi a 70, giusto, è fantastico e poi A104 e io metterò un 100 qui. Va bene, quindi 70 dovrebbe cambiare a 170 e lo fa, ma questo 70 non dovrebbe essere cambiato affatto. Va bene 68 + 2 non è un 170. Lo farò di nuovo. A 104 e mettere un altro cento nell'ultimo è giusto. Questi due non hanno ragione. Va bene, quindi abbiamo una situazione strana che se tu 'stai usando questa formula e ti converti in tabella inizi ad aggiungere righe, il totale parziale non funzionerà. Quanto è grave?
Bene, quindi Zack offre due soluzioni ed entrambe richiedono un po 'di conoscenza su come funzionano i riferimenti alla struttura. Avremo solo una nuova colonna qui e se volessi fare quantità, uguale quantità, giusto, in modo che = (@ Qty) dica la quantità in questa riga. Oh, bene, c'è un altro tipo di riferimento in cui usiamo la quantità senza @. Controllalo. Quindi = SUM (INDEX ((Qty), 1: (@ Qty)) significa tutte le quantità e diremo che vogliamo SUM dalla prima quantità, quindi (INDEX ((Qty), 1 dice primo valore qui, fino alla quantità di riga corrente, e questa utilizza una versione davvero speciale di index, quando index è seguito da due punti, in realtà si trasforma in un riferimento di cella. Va bene, questa soluzione purtroppo viola la regola di Charles Williams di, noi 'Dovrai guardare ogni singolo riferimento, quindi quando ottieni 10.000 righe di questo sarà molto, molto lento.
Zach ha un'altra soluzione alternativa che non viola il problema di Charles Williams, ma utilizza il temuto OFFSET. OFFSET è una funzione volatile, quindi ogni volta che si calcola qualcosa, OFFSET verrà ricalcolato e tutto ciò a valle dell'offset verrà ricalcolato. È solo un ottimo modo per rovinare completamente e completamente le tue formule, e quello che sta facendo, sta dicendo, stiamo prendendo il totale da questa riga, salendo di una riga, su zero colonne e quindi quello che sta facendo sta dicendo: prendiamo il totale dalla riga precedente e poi aggiungiamo ad esso la quantità da questa riga. Va bene, quindi, ora guarda tutti due riferimenti ogni volta, ma sfortunatamente OFFSET sta introducendo funzioni volatili.
Bene, ecco qua, più di quanto avresti mai voluto sapere sui totali parziali. Immagino che la mia opinione finale qui sia quella di utilizzare questo metodo, perché sembra solo due. Stessa formula fino in fondo ei riferimenti alla tabella strutturata funzioneranno.
Per questa esplorazione e altri 39 ottimi consigli, dai un'occhiata a questo libro XL, i 40 migliori consigli di Excel di tutti i tempi.
Ricapitolando per questo episodio abbiamo parlato di tre modi per fare i totali parziali. Il primo metodo ha una formula diversa, riga 2, rispetto a tutte le altre righe. È uguale a sinistra nella riga 2 e quindi uguale a sinistra più in alto nelle righe da 3 a N, ma se provi a usare la stessa formula, uguale a sinistra più in alto, fino in fondo, come otterrai un errore #Value . Quindi = SUM (Up, Left), che è il totale precedente, più questa roadmap, che funziona alla grande, nessun errore di valore e quindi l'intervallo in espansione che uso per amare. Sono fantastici, ma fino a quando non ho letto il white paper di Charles Williams sulla forma di velocità di Excel. Poi ho iniziato a odiare questi riferimenti in espansione. Ha anche un problema quando usi CTRL T e aggiungi nuove righe. Excel non riesce a capire come espandere quella formula, come aggiungere nuove righe. Adoro questo suggerimento, vai all'ultima cella della tabella e premi Tab,questo aggiungerà una nuova riga e poi abbiamo parlato di alcuni riferimenti strutturati, dove stiamo usando la quantità in questa riga e poi tutte le quantità. = SOMMA (OFFSET ((@ Totale), - 1,00, (@ Qtà)).
Ok, voglio ringraziare Zach per aver contribuito con questo suggerimento. Voglio ringraziarti per essere passato. Ci vediamo la prossima volta per un altro netcast da.
Download file
Scarica il file di esempio qui: Podcast2004.xlsx
