Excel: Come effettuare statistiche degli acquisti escludendo il primo ordine
Problema
Abbiamo una serie di acuisti effettuati da varie aziende su cui volgiamo effettuare delle statistiche:
- Calcolare quanti ordini ogni azienda ha effettuato
- Individuare quali sono i primi ordini effettuati
- Calcolare quanti ordini ogni azienda ha effettuato che superano un certo importo, escludendo il primo ordine
Soluzione
Calcolo del Primo Ordine
Cominciamo con l'individuare i primi ordini effettuati da ogni singola azienda.
Per fare questo utilizzeremo innanzitutto la funzione CONFRONTA() che cerca un determinato elemento in un intervallo e restituisce la posizione relativa di tale elemento nell'intervallo. La cosa interessante di questa funzione che restituisca la prima posizione di dove si trova l'elemento. Quindi se cerchiamo AZIENDA 01, che è presente nelle righe 2, 7 ,,, in tutta la colonna A, ci restituirà sempre 2 (riga 2) dove è presente il primo elemento AZIENDA 01.
Inseriamo la funzione =CONFRONTA(A2;A:A;0) nella cella C2. In questo modo andiamo a cercare la prima posizione di "AZIENDA 01" in tutta la colonna A. Il Terzo parametro "0" significa che troverà il primo valore che corrisponde esattamente a valore "AZIENDA 01"
Trasciniamo la funzione su tutte le righe contenenti ordini.
Come possiamo notare, filtrando le righe che contengono solo "AZIENDA 01", il risultato della funzione in questo caso da sempre 2 cioè la prima riga, in tutta la colonna A dove appare "AZIENDA 01".
Ecco cosa succede filtrando per "AZIENDA 02".
Nella colonna D utilizziamo invece la funzione =RIF.RIGA() che restituisce il numero di riga dove si trova la cella indicata. Inserendo =RIF.RIGA(A2) nella cella D2 ci restituirà 2, cioè il numero di riga dove si trova la cella A2.
Trasciniamo in giù la formula e vediamo cosa succede filtrando le righe che contengono solo "AZIENDA 01".
Come si può vedere sol nella prima riga filtrata riga e posizione hanno lo stesso valore.
Andando un po a campione (AZIENDA 02, AZIENDA 03 e AZIENDA 15) vediamo che l'idea funziona
A questo punto nella colonna E possiamo evidenziare quali solo i primi ordini per ogni azienda.
Nella cella E2 inseriamo la funzione =SE(C2=D2;"po";"") . In pratica se C2 e D2 sono uguali, uscirà la scritta "po" (primo ordine) altrimenti nulla.
Ecco il risultato se trasciniamo la formula su tutte le righe.
Come si può notare "po" compare solo sulle righe che contengono il primo ordine dell'azienda.
Formula unica
Siccome la colonna C (riga) e la colonna D (Posizione) non danno informazioni utili per chi osserva i dati, possiamo rendere più complessa la formula in E2 e poi eliminare le colonne C e D.
Si tratta di sostituire nella formula =SE(C2=D2;"po";"") il contenuto delle celle
- C2 --> CONFRONTA(A2;A:A;0)
- D2 --> RIF.RIGA(A2)
Quindi la nostra formula diventa =SE(CONFRONTA(A2;A:A;0)=RIF.RIGA(A2);"po";"")
Una volta trascinata la formula su tutte le righe, possiamo eliminare le colonne C e D. Il risultato è il seguente:
Statistiche degli ordini per ogni azienda
volgiamo calcolare
- Il totale degli ordini per ogni azienda
- Il totale degli ordini per ogni azienda che superano un importo, escludendo però il primo ordine
Creiamo un nuovo foglio che chiamiamo "Statistica"
copiamo tutta la colonna A del foglio Acquisti
Lavoriamo con la colonna A del foglio Statistiche e eliminiamo i duplicati.
Dal menù Dati facciamo click sul pulsante Rimuovi i duplicati.
Controlliamo che le impostazioni siano corrette e diamo OK
Per terminare mettiamo anche i nomi in ordine alfabetico.
Totale degli ordini
Calcoliamo ora il Totale degli ordini effettuati per ogni azienda. Può essere molto comodo utilizzare le etichette.
Andando sul foglio Acquisti e selezioniamo le colonne A:C. Dal menù Formule facciamo click sul pulsante "crea da selezione".
Nella finestra che compare è importante lasciare attivo solo "Riga superiore"
Facendo click sul pulsante Gestione nomi, sarà possibile visualizzare le etichette create.
È il momento di creare la formula nel foglio Statistica nella cella B2.
La formula è =CONTA.SE(Azienda;A2)
In pratica andiamo a cercare all'interno della colonna A del foglio acquisti, che abbiamo nominato Azienda, tutte le volte che appare Azienda 01 (contenuto in A2 ) e lo conteggiamo.
Trascinando su tute le righe la formula otteniamo:
Calcolo del totale degli ordini per ogni azienda che superano un importo, escludendo però il primo ordine
Qui la cosa si fa interessante.
Inseriamo l'importo che va superato nella cella C1, ad esempio 10000 e diamo a questa cella il nome Delta. Una volta scritto il nome occorre confermare con Invio.
Questa volta usiamo la funzione =CONTA.PIÙ.SE() che permette di lavorare su più criteri.
Iniziamo a inserire la formula =CONTA.PIÙ.SE(Azienda;A2;Importo;">="&Delta). nella cella C2
Andremo a conteggiare tutte le righe che
- contendono "Azienda 01" nella colonna con l'etichetta Azienda
- contengono un importo maggiore o uguale a Delta. la dicitura ">="&Delta crea la stringa >=10000 che è esattamente il nostro criterio di selezione
Se diamo Invio otteniamo
In questo modo però non abbiamo escluso il primo ordine.
Miglioriamo la formula in C2 che diventa =CONTA.PIÙ.SE(Azienda;A2;Importo;">="ΔOrdine;"<>po"), dove con "<>po" chiediamo di escludere tutte le righe nella colonna etichettata Ordine che contendono la stringa "po".
Ecco il risultato finale, trascinando la formula su tutte le righe
Vediamo cosa è successo all'Azienda 03 tornando sul foglio Acquisti.
In effetti escludendo il primo ordine, il secondo non supera l'importo Delta che abbiamo impostato a 10.000
Calcolo della percentuale e rappresentazione grafica
A questo punto volgiamo sapere in percentuale quanti sono gli ordini che superano il nostro criterio ( >= Delta, e non primi ordini) rispetto al totale degli ordini per ogni azienda.
Nella cella D1 possiamo semplicemente scrivere =C2/B2, applicare il formato percentuale con due decimali e trascinare la formula su tutte le righe
Può essere comodo utilizzare la formattazione condizionale, che rende meglio visivamente la distribuzione della percentuale.
Andiamo in Home e scegliamo dal pulsante Formattazione Condizionale, Barre dei dati. A voi la scelta in base al gusto 🙂
ecco il risultato finale
Miglioramenti
Nella cella C1 possiamo mettere >=10000 e questo rende più leggibile il report. Modifichiamo la formula in C2 in =CONTA.PIÙ.SE(Azienda;A2;Importo;Delta;Ordine;"<>po").
Statistica per articoli
Introduciamo un elemento in più negli acquisti: gli articoli.
Nell'esempio iniziale che potete scaricare come allegato con nome excel-come-effettuare-statistiche-degli-acquisti-escludendo-il-primo-ordine-partenza.xlsx sono stati inseriti gli a rticoli
Allegati
- Il foglio Excel di partenza
- il foglio Excel finale