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:

  1. Calcolare quanti ordini ogni azienda ha effettuato
  2. Individuare quali sono i primi ordini effettuati
  3. Calcolare quanti ordini ogni azienda ha effettuato che superano un certo importo, escludendo il primo ordine

Ordini delle azinende

 

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"

Funzione confronta

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".

Filtro su "AZIENDA 01" - statistiche acquisti

Ecco cosa succede filtrando per "AZIENDA 02".

Filtro 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.

Statistiche acquisti

Trasciniamo in giù la formula e vediamo cosa succede filtrando le righe che contengono solo "AZIENDA 01".

Statistiche primo ordine

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

trovare la prima riga per ogni azienda

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.

Individuazione del primo ordine

Ecco il risultato se trasciniamo la formula su tutte le righe.

visualizzazione delle righe che contengono il primo ordine

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";"")

Creazione della formula complessa

Una volta trascinata la formula su tutte le righe, possiamo eliminare le colonne C e D. Il risultato è il seguente:

formula unica trascinata

 

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"

Creazione del foglio Statistica

copiamo tutta la colonna A del foglio Acquisti

Copia della colonna A con le aziende sul foglio statistiche

Lavoriamo con la colonna A del foglio Statistiche e eliminiamo i duplicati.

Dal menù Dati facciamo click sul pulsante Rimuovi i duplicati.

Rimozione dei duplicati

Controlliamo che le impostazioni siano corrette e diamo OK

Rimozione dei duplicati

Per terminare mettiamo anche i nomi in ordine alfabetico.

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".

Creazione delle etichette

Nella finestra che compare è importante lasciare attivo solo "Riga superiore"

Crea da Selezione le etichette usando solo la riga superiore

Facendo click sul pulsante Gestione nomi, sarà possibile visualizzare le etichette create.

Gestione nomi visualizza 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.

Conteggio degli ordini per Azienda 01

Trascinando su tute le righe la formula otteniamo:

Conteggio di tutti gli ordini

 

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.

Creazione dell'etichetta

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

Inserimento delle condizioni

Se diamo Invio otteniamo

Calcolo degli ordini che superano un certo importo

In questo modo però non abbiamo escluso il primo ordine.

Miglioriamo la formula in C2 che diventa =CONTA.PIÙ.SE(Azienda;A2;Importo;">="&Delta;Ordine;"<>po"), dove con "<>po" chiediamo di escludere tutte le righe nella colonna etichettata Ordine che contendono la stringa "po".

Inserimento del terzo criterio

Ecco il risultato finale, trascinando la formula su tutte le righe

Trascinamento della formula su tutte le righe

Vediamo cosa è successo all'Azienda 03 tornando sul foglio Acquisti.

Acquisti Azienda 03

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

Calcolo della percentuale deglio ordini

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 🙂

Formattazione condizionale

ecco il risultato finale

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").

Formula modificata

 

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