Un esempio pratico per sommare e contare i valori nelle celle in base al colore del testo, impiegando anche il Filtro Avanzato per eliminare i valori doppi ed un comando rapido da tastiera per copiare una formula evitando la progressione dei parametri.
Nel precedente tutorial, abbiamo visto come ricavare l’informazione colore sfruttando una funzione Macro, senza usare VBA.
Il metodo ci ha permesso di memorizzare, in una colonna, il font color del primo carattere del testo espresso come numero da 1 a 56, dove 1 è il colore Nero (Automatico).
Supponiamo di dover gestire una catena di negozi con diverse sedi di cui si vogliono sommare, in automatico, gli importi delle fatture e sapere quante ne emettono.
Nel foglio di Excel aggiungiamo le descrizioni: Negozio in D1, Fatturato in E1, Negozio in G1, Somma Fattur in H1, Conteggio Fatt in I1.
Per comodità copiamo i colori della colonna A del precedente Tutorial, ma potete usare i colori che preferite.
Selezioniamo, tenendo premuto tasto sinistro mouse, celle da A3 a A12, CMD + C per copiare.
Selezioniamo, tenendo premuto tasto sinistro mouse, da D3 a E12, schiacciamo CMD + V per incollare.
In D3 inseriamo Sede in Nero (Colore Automatico), D4 Negozio 1 in rosso, D5 Sede, D6 Negozio 2 in viola, D7 Negozio 3 in ciano, D8 Sede, D9 Sede, D10 Negozio 1 in rosso, D11 Negozio 4 in verde, D12 Negozio 3 in ciano.
Nella colonna, E, inseriamo importi fatture con gli stessi colori dei negozi.
Nella colonna G vogliamo la lista dei negozi senza valori ripetuti. Nell’esempio sono pochi, ma avessimo diverse righe è più veloce automatizzare la selezione dei valori unici usando il Filtro Avanzato di Excel.
Andiamo nella scheda Dati.
Evidenziamo con il mouse la colonna con i valori da filtrare, nel nostro caso D, partendo dalla riga d’intestazione perché il filtro l’assume come condizione.
Clicchiamo su Filtro e su Avanzato.
Flag su Copia in un’altra posizione.
L’intervallo elenco compare in automatico avendo già selezionato da D1 a D12.
Rimane da definire la destinazione che scegliamo sia la colonna G.
Andiamo In Copia in e nel foglio Excel selezioniamo intervallo celle, da G1 a G12.
Flag su Copia solo record univoci
Click su OK.
Abbiamo ottenuto l’elenco dei negozi senza i doppioni.
Non ci resta che fare somme e calcoli relativi al fatturato dei singoli commerci.
Selezioniamo H3 ed andiamo con il cursore nella barra delle funzioni.
Digitiamo:
=SOMMA.SE(B3:B99;1;E3:E99)
Spiegazione: se nell’intervallo delle celle da B3 a B99 è presente il valore 1 (pari al colore Nero Automatico), la formula somma i valori delle celle del fatturato da E3 a E99.
Diamo Invio/Enter.
In H3 compare la somma delle fatture della Sede inserita con Nero Automatico.
Copiamo la formula in H3 nelle rimanenti celle da H4 a H7.
Se trasciniamo la maniglia di riempimento i valori degli intervalli progrediscono.
Per copiare esattamente la formula della cella superiore, ci posizioniamo nella cella vuota inferiore e premiamo contemporaneamente i tasti Ctrl+’
A questo punto non ci resta che cambiare (spostandoci con il cursore e dando enter) il valore di riferimento del colore che abbiamo ricavato nella colonna B, ossia:
3 per rosso e quindi in H4 il Negozio 1 avrà:
=SOMMA.SE(B3:B99;3;E3:E99)
29 per viola e quindi in H5 il Negozio 2 avrà:
=SOMMA.SE(B3:B99;29;E3:E99)
33 per ciano e quindi in H6 il Negozio 3 avrà:
=SOMMA.SE(B3:B99;33;E3:E99)
50 per verde e quindi in H7 il Negozio 4 avrà:
=SOMMA.SE(B3:B99;50;E3:E99)
Nella Colonna I inseriamo il conteggio delle fatture suddivise per i colori attribuiti ai vari negozi.
Selezioniamo I3 ed andiamo nella barra delle funzioni:
Per la Sede scritta in Nero Automatico, con Idcoloretesto pari a 1, digitiamo:
=CONTA.SE(B3:B99;1)
Copiamo la formula nelle rimanenti righe con Ctrl+’ andando a modificare il numero di riferimento del Font Color ossia 3 per Negozio 1 scritto in rosso, 29 per Negozio 2 in viola, 33 per Negozio 3 in ciano, 50 per Negozio 4 in verde, dando Invio/Enter per avere il risultato.
=CONTA.SE(B3:B99;3)
=CONTA.SE(B3:B99;29)
=CONTA.SE(B3:B99;33)
=CONTA.SE(B3:B99;50)
Se sommiamo i valori delle fatture ed il conteggio delle colonne con le sedi raggruppate confrontandole con i totali della colonna E, otteniamo gli stessi valori.
Potete visualizzare i vari passaggi nel video sul canale YouTube di ShowTechies. Se vi piacciono i nostri tutorial, iscrivetevi per rimanere aggiornati ed aiutarci a crescere. Grazie!
RIPRODUZIONE RISERVATA – © 2024 SHOWTECHIES – Quando la Tecnologia è spettacolo™
ShowTechies non è responsabile di eventuali danni causati dall’errata applicazione delle formule.
Immagini e Video: Simona Braga – Vietata la riproduzione dell’articolo e del video senza autorizzazione scritta.
UTILISSIMO!!!!!!!!!!!
Fenomeno
Avete spiegato ogni passaggio molto bene. Anche il video è stato d’aiuto come il Ctrl che non conoscevo. Potete fare un tutorial per i vari copia? Grazie.
I appreciate your contribution.
Complimenti avevo fatto la domanda su un forum di excel e mi avevano scritto di infocella ma dandomi la funzione sbagliata e senza um esempio. Molto ma molto utile. Grazie.