Abbiamo già esaminato come con la funzione del quadratino di riempimento automatico automatico, si possono ricopiare con il metodo del trascinamento , formule in celle adiacenti sia per le righe che per le colonne…..

se te lo sei perso…puoi rileggerlo qui….

In alcuni casi, però questa funzione sembra non funzionare e ci restituisce un codice di errore “#DiV/0!”……

analizziamo perchè accade e come poterlo risolvere……

Supponiamo di avere questa Tabella che rappresenta le vendite mensili di una serie di rappresentanti. A noi serve calcolarci il Totale delle vendite in un anno, il totale delle vendite generale, e la percentuale di vendita di ciascun rappresentante.01

Per calcolarmi la somma mi posiziono nella cella N2 e digito la formula della Somma che è :

=SOMMA(B2:M2) ….lui mi restituirà il totale (208)

02

Ora per copiare facilmente la formula a tutte le celle adiacenti , devo usare il quadratino di riempimento automatico….e quindi mi posiziono in basso a destra alla cella in cui ho inserito la prima formula, (dove c’è un quadratino nero)…quando compare una “+” nera allora tengo premuto il tasto sinistro del mouse e senza lasciare trascino fino alla cella corrispondente all’ultimo rappresentante…..poi lascio il tasto sinistro e mi ritrovo tutte le somma fatte.

03

Io nelle celle vedo il totale, ma per capire che funzione svolge “il riempimento automatico” vado a dare uno sguardo in alto nella barra della formula….

Se mi posiziono sulla cella N3… mi accorgo che nella barra della formula in alto….la funzione del riempimento automatico, ha scritto per me la formula che è : =SOMMA(B3:M3)…….se vado in N4 …troverò la formula : =SOMMA(B4:M4)………Quindi fino alla fine mi troverò tutte le formule giuste che slittano di una riga…..B5, B6,B7,B8…….

Cioè io avevo scritto in N2 : =SOMMA(B2:M2)

Quando vado a trascinare in basso , excel mi ricopia la formula, però mi fa slittare sempre di una riga le due celle che ho inserito nella mia formula

  • =SOMMA(B2:M2)
  • =SOMMA(B3:M3)
  • =SOMMA(B4:M4)
  • =SOMMA(B5:M5)
  • =SOMMA(B6:M6)

e così via

04

Capito questo passaggio…mi vado a calcolare il totale generale delle vendite annuali semplicemente inserendo una funzione somma nella cella N23  che è :

= SOMMA(N2:N21)

05

Ora passiamo alla Percentuale delle vendite annuali di ogni rappresentante……

MI posiziono nella cella O2…e la formatto per contenere una % premendo il tasto apposito

06

Questo tasto oltre ad inserire il simbolo della percentuale mi esegue anche una moltiplicazione per 100 al contenuto della cella.

Così la formula originaria per calcolarmi la percentuale sarebbe :

Somma individuale / Somma totale * 100  quindi in questo caso (208/3375* 100 )  e visto in celle (N2/N23*100)

Se premo quel tasto allora posso risparmiare di moltiplicare per 100 perchè lo fa lui per me e quindi la mia formula sarà solamente N2/N23 …..

infatti vado nella cella O2 e la scrivo…….ed otterrò il risultato….6%

07

è opportuno per una maggiore precisione aumentare i decimali con l’apposito pulsante sulla barra degli strumenti.

Ora se vado a ricopiarmi la formula come abbiamo fatto in maniera giusta per la somma poco fa…ecco cosa accade….

08

Ecco l’errore che vi ho accennato in precedenza. Vediamo perchè accade e cerchiamo di correggerlo.

Ci posizioniamo nella cella O3 ed andiamo a leggere nella barra della formula il riempimento automatico che formula ha scritto per me…..

Senza titolo-11

Mi Accorgo che la formula in questione è: =N3/N24……e cioè N3 il totale del rappresentante 2 ed è giusto…..ma N24 è una cella vuota e non è il totale che è sempre N23. Pertanto eseguendo una divisione per una cella vuota (e quindi zero) mi da quell’errore #Div/0!, che significa appunto stai dividendo per zero un numero , cosa che è impossibile in matematica. La formula corretta avrebbe dovuto essere = N3/N23……

La stessa cosa accade alle celle adiacenti in basso e quindi troverò:

  • =N3/N24
  • =N4/N25
  • =N5/N26
  • =N6/N27
  • e così via…

quindi il riempimento automatico  si comporta come per la somma ….parte dalla mia prima formula e mi slitta di una riga le due celle contenute nella formula……In questo caso però non va bene come per la somma, e se mi va bene che slitta la colonna delle somme individuali (N2, N3, N4…..) non va assolutamente bene che mi slitta il totale generale N23….poichè tutte le celle al di sotto sono tutte vuote (N24, N25, N26…..)

Quindi la formula si corregge dicendo ad excel che mi deve bloccare quella cella in caso di uso del riempimento automatico. 

Questa funzione è il riferimento e si indica con il simbolo “$” 

Nel nostro caso dovendo bloccare solo le la cella N23 per il trascinamento in verticale ….allora si parla di riferimento misto, relativo di colonna ed assoluto di riga si utilizza in questo modo : “N$23″ 

Pertanto la mia formula iniziale nella cella O2 deve essere modificata in :

=N2/N$23……ed adesso posso ricopiare la formula usando il riempimento automatico………il risultato sarà esatto….Senza titolo-12

se vado ad analizzare adesso la barra della formula in corrispondenza della cella O3 ….

13

Vedo che mi ha slittato di una riga N2 facendolo diventare N3…..ma mi ha bloccato la cella N23 poichè ho utilizzato il riferimento ($). Questa è la procedura corretta……

Per completezza si parla di riferimento assoluto di riga e di colonna se devo bloccare la cella per il trascinamento sia in orizzontale che in verticale e quindi sia per le righe che per le colonne si scrive in questo modo :

$N$23 

Nel caso invece trovo questa espressione:

$N23

allora si tratta di un riferimento misto di colonna assoluto e di riga relativo e si usa quando devo ricopiare una formula trascinando non in verticale come il nostro esempio, ma in orizzontale e ciò per le colonne……

 

 

 

 

LASCIA UN COMMENTO