[Excell] Macro per Analisi ABC (ABC Analysis)

[menate]

Buon giorno.

Vorrei sapere se qualcuno è a conoscenza di una macro per excell, in grado di effettuare in automatico una analisi ABC su una serie di dati.

Vi ringrazio per l'attenzione,
saluti

[ricky53]

Ciao,
prima di tutto benvenuto nel nostro forum e buona permanenza.

Immagino che tu manualmente lo faccia già questo calcolo e, quindi, potrai fornire un esempio effettivo sul quale lavorare insieme alla/alle formule che utilizzi.

In letteratura, molto probabilmente, si trova molto ma se vuoi una personalizzazione allora occorre avere dei dati sui quali operare ed insieme si costruirà il codice necessario.

[menate]

Ciao, grazie mille per l'accoglienza.

Non ho ancora inviato 3 post sul forum e quindi non posso ancora inserire un link.

Per chi vuole provare a rispondermi, potete trovare un esempio di come si fa l'analisi abc su excell, senza usare le macro, cercando su google: "ABC Analysis in Excel"

Il primo risultato è un file [doc] all'interno del quale ci sono le istruzioni.

Può bastare questo esempio o avete bisogno di qualcosa di diverso?

Grazie per l'attenzione

[Anthony47]

Ciao e benvenuto anche da parte mia.
Sei autorizzato a scrivere un post anche fasullo per arrivare a tre e poter allegare un link.

Per quello che ne so, questa analisi e' volta a separare in fasce un elenco di prodotti (fascia A, B e C, appunto), ma credo che i criteri di separazione siano assolutamente personali..
Uno degli esempi ottenuti dalla google serach porta a questo link http://www.docstoc.com/docs/5822121/ABC ... -in-Excel; qui e' spiegato mi pare in dettaglio un esempio di modo di procedere avendo scelto come criterio di scelta la valorizzazione del movimentato annuo.
Se i tuoi criteri sono simili allora puoi procedere in quel modo? Immagino che invece vorrai valutare anche altri criteri; in questo caso allora quell' esempio e' certamente un valido spunto.
Per altri ragionamenti siamo qui, pero' tieni presente che una analisi presuppone dei dati e dei criteri, su cui solo tu puoi essere piu' preciso.

Ciao

[menate]

ok, allora: post fasullo per poter inserire il link!

[menate]

Questo è il link di cui parlavo prima.

http://www.google.com/url?sa=t&rct=j&q= ... 7PHKQvHMqA

Anticipo che non ho mai fatto una macro e so a malapena come funzionano ma mi è stato richiesto di ricercare on line o da qualcuno che sapesse farle, una macro che consentisse di fare un'analisi ABC.

Per quanto riguarda i criteri ed i dati, proverei con quelli dell'esempio del link.

Grazie ancora per le gentili risposte,
Saluti

[ricky53]

Ciao,
cortesemente oltre al link dovresti scrivere nel forum come operi e quali dati e formule si utilizzano.
Questo a memoria futura, per altri utenti, quando il link non sarà più attivo.


Scusa perchè vuoi operare mediante una macro?

A mio avviso prima conviene provare l'esempio che hai riportato e costruirlo, con le formule referenziate nel documento word, operando su un foglio excel.

Ne convieni ?

[menate]

L'esempio l'ho ricostruito ed è possibile scaricarlo qui:

http://www.mediafire.com/?urh3qhc1klz89l3


Questo è il modo in cui ho operato:

Avendo a disposizione l'ID, la domanda attuale ed il prezzo unitario [colonne A,B e C]

- ho calcolato il Prezzo Annuale[colonna D] come (prezzo unitario * domanda annuale)
- ho calcolato il Prezzo Annuale TOT [D20], facendo la somma della colonna D.
- ho calcolato la Percentuale del Prezzo sul Totale [colonna E] con la formula (prezzo annuale/prezzo Annuale TOT).

- ho ordinato la tabella dal Prezzo Annuale più alto a quello più basso.
- ho calcolato la % Cumulativa copiando in F5 il valore che ho in E5, mentre in F6 la formula (E6+F5), copiandola poi nelle celle da F7 a F19.

- nella colonna G ho inserito: =+SE(F5<0,8; "A";SE(F5>0,95; "C"; "B" ))



Ciò che mi occorre è una macro che automatizzi questi passaggi.
E' possibile?

Grazie

[ricky53]

Ciao,
forse mi sono perso qualcosa ... con l'esempio non hai risolto essendo arrivato al risultato atteso?

Perchè vuoi fare le stesse operazioni con una macro

[Anthony47]

Direi che hai gia' fatto quasi tutto.
Il mio suggerimento:
Creati una tabella che ha le formule che hai detto fino a riga 1000, con queste piccole varianti:
1) scrivi in I1 e J1 le soglie per la fascia A e B (0,8 e 0,95, nel tuo esempio)
1) la formula che metti in G5 e sottostanti diventa

Codice: Seleziona tutto

=SE(A5<>"";SE(F5<$I$1; "A";SE(F5>$J$1; "C"; "B" ));"")

2) calcola la somma del "Prezzo Annuale" in E1 (non D20) con la formula

Codice: Seleziona tutto

=SOMMA(D1:D1000)

3) la formula in E5 e sottostanti diventa

Codice: Seleziona tutto

=D5/$E$1

Registra a questo punto una macro mentre esegui diligentemente queste operazioni:
-selezioni A5:E1000
-esegui un ordinamento per colonna E crescente
-selezioni A1
-interrompi la registrazione

Eventualmente disegna un pulsante (trovi questa forma nella barra degli strumenti Moduli) e gli associ la macro appena creata (ti verra’ chiesto in fase di creazione del pulsante).

In questo modo hai creato un "modello di lavoro": all' occorrenza inserisci da A5 verso il basso i prodotti, relativo volume annuale e prezzo, poi premi il pulsante e avrai l' elenco con le fasce di appartenenza. Modificando i valori di soglia in I1 e J1 puoi variare il modo di catalogare per fascia.

E se pensi di dover analizzare piu' di 1000 voci, basta aumentare l' area con le formule e ovviamente anche la formula della Somma in E1.

Ciao

[menate]

Grazie mille, gentilissimi!

Ho apportato le modifiche dettate da Anthony47 ed ho creato la mia prima macro!

Scusate ancora l'ignoranza ma fino a tre giorni fa non sapevo nemmeno l'esistenza delle macro.

Un'ulteriore domanda: non c'è la possibilità di avere il codice Visual Basic di questa procedura? Dal momento che ho registrato la macro, posso trovarlo (il codice) da qualche parte?

Grazie ancora!

[menate]

Ciao,
forse mi sono perso qualcosa ... con l'esempio non hai risolto essendo arrivato al risultato atteso?

Perchè vuoi fare le stesse operazioni con una macro

Mi hanno chiesto di trovare un modo per effettuare una analisi ABC tramite una macro Excell.
Esattamente non so per quale motivo!

[ricky53]

ciao,
non voglio entrare TROPPO nei meriti delle tue necessità ma quando un mio cliente mi chiede di fargli qualcosa (ramo informatico ovviamente) gli chiedo sempre cosa vuole ottenere, cosa ha a disposizione ecc, e poi come ottenerlo lo vediamo dopo e poi ... durante i lavori si cambia sempre il "cosa ottenere".

Premesso ciò per vedere il codice che hai registrato: Alt+F11
Il codice lo troverai in VBAProject nel "Modulo1" o altri moduli se opererai registrando più volte.

Puoi anche (valido per Excel 2003):
Barra dei menu
Strumenti
Macro
Visual Basic Editor
...

[Flash30005]

Benevenuto anche da parte mia

Il motivo di avere una macro rispetto alle formule credo sia per il fatto di poter ordinare le colonne anche dopo la disposizione e con le formule potrebbe avere problemi

Pertanto...
Inserisci un un modulo questa macro

Codice: Seleziona tutto

Sub AnalisiABC()
Worksheets("Foglio1").Select
UR = Range("A" & Rows.Count).End(xlUp).Row
Mtot = 0
For RR = 5 To UR
Range("D" & RR).Value = Range("B" & RR).Value * Range("C" & RR).Value
Range("D" & RR).NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
Mtot = Mtot + Range("D" & RR).Value
Next RR
Range("D" & UR + 1).Value = Mtot
Range("D" & UR + 1).NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
For RR = 5 To UR
Range("E" & RR).Value = Range("D" & RR).Value / Mtot
Range("E" & RR).NumberFormat = "0.00%"
Next RR
    Range("A4:G" & UR).Select
    Selection.Sort Key1:=Range("D5"), Order1:=xlDescending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Range("H23").Select

For RR = 5 To UR
    If RR = 5 Then
        MTF = Range("E" & RR).Value
    Else
        MTF = MTF + Range("E" & RR).Value
    End If
    Range("F" & RR).Value = MTF
    Range("F" & RR).NumberFormat = "0.00%"
    Number = Range("F" & RR).Value
Select Case Number
Case 0 To 0.8
 Range("G" & RR).Value = "A"
Case 0.8001 To 0.95
 Range("G" & RR).Value = "B"
Case Else
  Range("G" & RR).Value = "C"
End Select
Next RR
End Sub

Cancella la legenda A, B, C che hai sotto la tabella in colonna A
e avvia la macro

(allego questo file)

ciao

[Anthony47]

Ci sono messaggi di Flash e Ricky prima di questo.

Ho apportato le modifiche dettate da Anthony47 ed ho creato la mia prima macro!

Bravo! Sappi che ancora oggi la meta' del mio codice nasce da una macro registrata.
Un ulteriore metodo per vedere il codice della macro: da Excel, Alt-F8; ti presenta l' elenco delle macro disponibili; selezioni la tua, premi Modifica; ti aprira' l' editor delle macro e puoi vedere direttamente il codice registrato.

In genere si tratta di codice non ottimizzato; copialo tutto, incollalo nel prossimo messaggio e a scopo didattico lo comprimeremo per renderlo meno superfluo.

Ciao

[menate]

Vi ringrazio veramente tutti per la gentilezza e pazienza!

Come avrò qualche dubbio non esiterò a riscrivere su questo forum!

In genere si tratta di codice non ottimizzato; copialo tutto, incollalo nel prossimo messaggio e a scopo didattico lo comprimeremo per renderlo meno superfluo.

Codice: Seleziona tutto

Sub ABC_Analysis()
'
' ABC_Analysis Macro
' Esegue l'analisi Abc
'

'
    Application.Goto Reference:="R5C1:R1004C5"
    ActiveWorkbook.Worksheets("Foglio1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Foglio1").Sort.SortFields.Add Key:=Range( _
        "E5:E1004"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Foglio1").Sort
        .SetRange Range("A4:E1004")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveWindow.SmallScroll Down:=-15
    Range("A1").Select
End Sub


Fatto! Non è bello come quello di Flash30005, ma è pur sempre la mia prima volta!

[Anthony47]

Invece e' molto bella anche "la tua"...
Ed e' anche poco ridondante; le uniche modifiche che si potrebbero apportare e' l' eliminazione delle righe marcate <<< per i motivi scritti nella stessa riga:

Codice: Seleziona tutto

Sub ABC_Analysis()
'
' ABC_Analysis Macro
' Esegue l'analisi Abc
    Application.Goto Reference:="R5C1:R1004C5"   '<<< Inutile
    ActiveWorkbook.Worksheets("Foglio1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Foglio1").Sort.SortFields.Add Key:=Range( _
        "E5:E1004"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Foglio1").Sort
        .SetRange Range("A4:E1004")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin          '<<< E' il valore di dafault
        .Apply
    End With
    ActiveWindow.SmallScroll Down:=-15  '<<< inutile
    Range("A1").Select
End Sub

Continua a registrare macro per automatizzare piccoli task ripetitivi, vedrai che presto saprai anche modificarle o scriverle ex-novo per compiti sempre piu' articolati.

Per ogni esigenza sai dove chiedere.

Ciao

[menate]

Per ogni esigenza sai dove chiedere.

Ciao

Grazie ancora ed a presto!