Moltiplicazione complicata:)

[Spartacus85]

Ragazzi ho un problema con questa tabella (e solo esplicativa, il vero file ha migliaia di righe e puoi avere piu livelli rispetto quelli mostrati qui).
L'obiettivo e quello di ottenere nella colonna gialla la reale quantita..
Come vedete nella colonna sequence ci sono una serie di numeri che indicano una sequenza, per spiegarvi il componente 100.1 si trova nel componente 100, il componente 103.1.3 si trova nel componente 103.1 che a sua volta si trova nel componente 103.

Quello che vorrei ottenere e ad esempio per 103.1.3 la moltiplicazione tra il corrispoettivo di 103.1.3 nella colonna 1 (ossia 1), per il corrispettivo di 103.1 (ossia 1) per il corrispettivo di 103 (ossia 2). Totale 2.

come vedete solo il primo livello e univoco.

Spero sia stato chiaro nella spiegazione. Il problema puo essere risolto sia con Macro che con formule.

Grazie a tutti per la preziosa collaborazione

[ricky53]

Ciao,
Uhm, Uhm !!!

Qualche altro esempio dove i dati non siano tutti "1".

Come hai ottenuto "4" per il 103.1.2 ?

Come hai ottenuto "4" per il 103.1.3.1 ?

Perchè il 147 e il 170 non hanno il livello 1 presente?
Invece il 100, 103, 105 ci sono.

[Spartacus85]

103.1.2 (OSSIA 4) moltiplicazione tra il corrispettivo di 103.1.2 nella colonna 1 (ossia 2), per il corrispettivo di 103.1 (ossia 1) per il corrispettivo di 103 (ossia 2). Totale 4.

103.1.3.1 (OSSIA 4) moltiplicazione tra il corrispettivo di 103.1.3.1 nella colonna 1 (ossia 2), per il corrispettivo di 103.1.3 (ossia 1) per il corrispettivo di 103.1 (ossia 1) per il corrispettivo di 103 (ossia 2). Totale 4.

Per quanto riguarda la tua giusta osservazione su 147 e il 170 e sul fatto che il livello 1 non e presente, e dovuto al fatto che nel file ci possono essere casi come questo per cui puo accadere che la numerazione parta direttamente da170.7 piuttosto che da 170.1.

[Spartacus85]

Non sono un esperto di macro ma credo che la soluzione migliore sia usarne una. A logica farei una cosa del genere:

Ws3="Foglio1"
UR3= Ws3.Range("B" & Rows.Count).End(xlUp).Row
For i = 2 To UR3

Conto numero punti nella cella B-i (k=numero punti)
Creo un array con k+1 celle e in ogni cella memorizzo i numeri divisi dai punti. Ad esempio 103.1.3.1 diventa (103|.1|.3|.1)
Farei un altro ciclo for con J=1 fino a "k=Numero punti"+1

Codice=concatena(Codice, Array_J)
Cerco "Codice" nella colonna B e restituisco il valore "Quantita" della colonna A
Reale Quantita=Quantita*Reale Quantita
Next J

Memorizzo nella cella X_i "Reale Quantita",

Inizializzo "Reale Quantita"=1
Inizializzo Codice =""

Next i


Penso il codice a logica possa essere questo pero non so come scriverlo senza errori.

Qualcuno se e daccordo con la mia soluzione puo aiutarmi?

[ricky53]

Ciao,
però mancano anche il 147 e il 170 senza punti !

Non mi è ancora chiaro come fare le operazioni: sto cercando di generalizzare la regola e tradurla in formule per poi applicarla al codice da scrivere.

Puoi inviare altri esempi ?

[Spartacus85]

Ricky in questo caso per il 147.385 la quantita finale sara 1. Se ci fosse stato il 147 allora avrei dovuto moltiplicare la quantita corrispondente a 147 con quella corrispondente a 147.385 (ossia 1).

Se leggi il mio post precedente forse puoi chiarirti per quanto rigurarda la logica da usare.

Grazie x l'aiuto

[wallace&gromit]

ciao, mi intrometto anche io.
Con una formula, premesso che
- la colonna B contiene Sequence
- i quantity, ch fin'ora erano in colonna A possano essere spostati in colonna J
- tutti i numeri inseriti sono in forma di testo
- il lavoro sia sempre completo (ogni x.y.z ha sempre un x.y che lo precede)

in riga 3 si potrebbe fare qualcosa di questo tipo:

Codice: Seleziona tutto

=SE(F3<>"";CERCA.VERT(C3&"."&D3&"."&E3&"."&F3;B$3:J$7;9;0);1)*SE(E3<>"";CERCA.VERT(C3&"."&D3&"."&E3;B$3:J$7;9;0);1)*SE(D3<>"";CERCA.VERT(C3&"."&D3;B$3:J$7;9;0);1)*CERCA.VERT(TESTO(C3;0);B$3:J$7;9;0)

questa calcola fino al livello 4, per aggiungere livelli si procede aggiungendo progressivamente le celle successive.

[Spartacus85]

Grazie Wallace la provo subito.

Io nel frattempo stavo lavorando ad una macro e ho partorito questo:

Codice: Seleziona tutto

Sub Sequenza()

Dim UC1, k, j, RealQuantity As Integer
Dim Punti As Long
Dim MyArray As Variant
Dim Code As String
Dim Quantity As Variant

Set Ws1 = Worksheets("Foglio1")
UC1 = Ws1.Range("A" & Rows.Count).End(xlUp).Row

For k = 3 To UC1
   Punti = Len(Ws1.Range("A" & k) - Len(Replace(Ws1.Range("A" & k), ".", "")))
   MyArray = Split(Ws1.Range("A" & k), ".")
   RealQuantity = 1
   Code = ""
   
   For j = 1 To Punti + 1
     Code = Code & "." & MyArray(j)
     Quantity = Application.VLookup(arg1, Ws1.Range("A3" & "A" & UC1), 7, False)
     RealQuantity = Quantity * RealQuantity
   Next j
   
   Ws1.Range("X" & k).Value = RealQuantity
 
Next k

End Sub

Mi da un problema al seguente codice

Codice: Seleziona tutto

Code = Code & "." & MyArray(j)

run-time9 intervallo non incluso

[wallace&gromit]

Mi da un problema al seguente codice

Codice: Seleziona tutto

Code = Code & "." & MyArray(j)

run-time9 intervallo non incluso

idem, ma non mi sorprende, pur non capendoci granché di macro vedo che qui si costruisce un testo che iniza con un punto (non so bene dove porti)

[Anthony47]

Split restituisce un array con base 0; visto che usi For j = 1 To Punti + 1 andra' in errore sull' ultimo valore che cerchi di riprendere da MyArray (mentre hai saltato il primo, contenuto in MyArray(0)).
Inoltre non hai previsto che Application.VLookup talvolta restituira' errore.


Io lavorerei su una userfunction in grado di fare questo specifico calcolo.
-supponiamo di avere i dati Quantita' e Sequenza in A2:B16 (colonna A e B, rispettivamente)
-in un "Modulo standard", es Modulo1 inserisci questo codice:

Codice: Seleziona tutto

Function myQty(ByRef CSeq As Range, ByRef SeqSerie As Range) As Long
'vedi http://www.pc-facile.com/forum/viewtopic.php?f=26&t=99933
'
Dim UTN As Long, RMatch, CVSeq As String, Seq As Range
'
CVSeq = CSeq.Value
Set Seq = Application.WorksheetFunction.Index(SeqSerie, 0, 2)
'
Do
    RMatch = Application.Match(CVSeq, Seq, 0)
    If Not IsError(RMatch) Then UTN = UTN + Seq.Cells(1, 1).Offset(RMatch - 1, -1).Value
    If (Len(CVSeq) > Len(Replace(CVSeq, ".", ""))) Then
        CVSeq = Left(CVSeq, InStrRev(CVSeq, ".", , 1) - 1)
    Else
        myQty = UTN
        Exit Function
    End If
    DoEvents
Loop
End Function


-Poi nella cella in cui vuoi calcolare la "quantita' reale" inserisci una formula come questa:

Codice: Seleziona tutto

=myQty(B2;$A$2:$B$16)

-copi verso il basso, per quante Sequence sono presenti
Ad esempio:


Uploaded with ImageShack.us

Ciao

[wallace&gromit]

rimango sempre allibito da come poche righe di codice di Anthony fanno il lavoro!
l'unico problema è che l'operazione eseguita è la somma invece della moltiplicazione.
Per ovviare basta inserire UTN = 1 prima dell'istruzione "Do"
e sostituire + con *

[Spartacus85]

Grazie Anthony x la soluzione.. c'e pero una cosa che non va. Questa e la tabella dei risultati, in giallo quello che vorrei ottenere ed in Arancio cio che ottengo con la tua funzione:


Credo che l'inghippo sia qui

Codice: Seleziona tutto

If (Len(CVSeq) > Len(Replace(CVSeq, ".", ""))) Then
        CVSeq = Left(CVSeq, InStrRev(CVSeq, ".", , 1) - 1)
    Else


In questa funzione credo che quello che faccia e prendere il codice di sinistra eliminando la parte di destra dopo il punto.
Il problema che il codice e formato da piu codici padre (ogni codice padre va letto da destra verso sinistra togliendo man mano la parte di codice compresa tra 2 punti).

Infatti ad esempio per il codice 103.1.3.1 (riga 10) la quantita corretta e 4 perche deve moltiplicare il valore di 103.1.3.1 (ossia 2) per il valore di 103.1.3 (ossia 1) per il valore di 103.1 (ossia 1) per il valore di 103 (ossia 2).

Quindi dato un codice "figlio", moltiplico la quantita relativa a quel codice per tutte le quantita riferite ai codici "padre", e questo lo faccio togliendo man mano da destra verso sinistra la parte di codice tra 2 punti.
Una nota: come si vede nella tabella, ad esempio per il 147.354 (riga 13) non esiste il codice "padre" 147 per cui in questo caso devo moltiplicare la quantita di 147.354 (ossia 1) per una quantita fittizia e neutra relativa al codice "padre" (ossia 1)

[wallace&gromit]

penso che il tuo problema sia solo che certi numeri con la virgola sono considerati numeri e non testo. Prova a mettere un apostrofo davanti a uno che non funziona e vedi se risolvi

[Spartacus85]

Stavo continuando a lavorare sulla Macro anche in base ai vostri suggerimenti:

Codice: Seleziona tutto

Sub BOM()

Dim UC1, k, j, RealQuantity, Quantity, Match, Punti As Integer
Dim MyArray() As String
Dim Codice As Variant

Application.ScreenUpdating = False

Set Ws1 = Worksheets("ALC.43000.00")
UC1 = Ws1.Range("A" & Rows.Count).End(xlUp).Row

'For k = 3 To UC1
For k = 30 To 30
   'Punti = Len(Ws1.Range("A" & k) - Len(Replace(Ws1.Range("A" & k), ".", "")))
   Punti = CharCount(Ws1.Range("A" & k), ".")
   MyArray = Split(Ws1.Range("A" & k), ".")
   RealQuantity = 1
   Codice = ""
   
   For j = 0 To Punti
     Codice = Codice & "." & MyArray(j)
     If Left(Codice, 1) = "." Then Codice = Mid(Codice, 2, Len(Codice) - 1)
     'MsgBox Application.CountIf(Ws1.Range("A3:A" & UC1), Codice)
     Match = Application.VLookup(Codice, Ws1.Range("A3:A" & UC1), 7, False)
       If Not IsError(Match) Then
       Quantity = Match
       RealQuantity = Quantity * RealQuantity
     End If
   Next j
   
   Ws1.Range("X" & k).value = RealQuantity
   
Next k

End Sub

La Macro funziona fintanto che arriva al punto

Codice: Seleziona tutto

   For j = 0 To Punti
     Codice = Codice & "." & MyArray(j)
     If Left(Codice, 1) = "." Then Codice = Mid(Codice, 2, Len(Codice) - 1)
     'MsgBox Application.CountIf(Ws1.Range("A3:A" & UC1), Codice)
     Match = Application.VLookup(Codice, Ws1.Range("A3:A" & UC1), 7, False)
       If Not IsError(Match) Then
       Quantity = Match
       RealQuantity = Quantity * RealQuantity
     End If
   Next j


Ed esattamente al punto

Codice: Seleziona tutto

Match = Application.VLookup(Codice, Ws1.Range("A3:A" & UC1), 7, False)


Credo che non faccia bene il VLookup e quindi poi non vada mai al passaggio successivo.

Avete qualche suggerimento?
Grazie

[Spartacus85]

Il rpoblema era il range non corretto!..

Codice: Seleziona tutto

Match = Application.VLookup(Codice, Ws1.Range("A3:G" & UC1), 7, False)

[wallace&gromit]

mah, capisco che sei fiero di averlo cucinato da te, ma preferisco il filettino di Anthony al tuo ... polpettone

[Spartacus85]

Anch'io pero non funziona come vorrei, nemmeno col tuo suggerimento.Aspetto Anthony che mi dica cosa va modificato nella sua e poi la riprovo.

Comunque alla fine la Macro che ho fatto funziona , solo che dovendo farla per un centinaio di fogli e in ogni voglio per 3000righe ci mette un'eternita per la risoluzione. Sicuramente con quella di Anthony ci metterei molto meno.
Se Anthony puo rivedere la sua ne sarei contento.

Grazie per l'aiuto a tutti!

[wallace&gromit]

Anch'io pero non funziona come vorrei, nemmeno col tuo suggerimento.

ti riporto la funzione di Anthony, con le mie piccole modifiche e ricordandoti l'avvertenza che i valori iniziali devono essere testo (puoi anche selezionare tutta la colonna e scegliere "formato celle/ testo". A me funziona perfettamente come da te desiderato.

Codice: Seleziona tutto

Function myQty(ByRef CSeq As Range, ByRef SeqSerie As Range) As Long
'vedi http://www.pc-facile.com/forum/viewtopic.php?f=26&t=99933
'
Dim UTN As Long, RMatch, CVSeq As String, Seq As Range
'
CVSeq = CSeq.Value
Set Seq = Application.WorksheetFunction.Index(SeqSerie, 0, 2)
'
    UTN = 1
Do

    RMatch = Application.Match(CVSeq, Seq, 0)
    If Not IsError(RMatch) Then UTN = UTN * Seq.Cells(1, 1).Offset(RMatch - 1, -1).Value
    If (Len(CVSeq) > Len(Replace(CVSeq, ".", ""))) Then
        CVSeq = Left(CVSeq, InStrRev(CVSeq, ".", , 1) - 1)
    Else
        myQty = UTN
        Exit Function
    End If
    DoEvents
Loop
End Function


[Anthony47]

Ho seguito la discusione sempre di fretta, anche perche' ho visto che w&g ha corretto bene il codice affinche' calcolasse il prodotto delle Quantity parziali (e non la somma!) e poi ha fatto una diagnosi che condivido sul perche' nelle prove di Spartacus i risultati fossero errati (valori tipo "100" inseriti come numero e non come testo).
Leggendo il codice della Sub BOM() ho pero' l' impressione che tale macro faccia riferimento a un layout dati diverso da quello pubblicato (es Sequence in col A invece che B e il riferimento a una colonna G che contiene qualcosa di importante ma non descritto); non escludo che quindi altre cose si siano perse per strada.
C' e' anche da notare che usare per il collaudo valori tipo 1 e' un altro ottimo modo per non capire se il calcolo e' corretto o meno e, in caso di palese inesattezza, non avere nessuna informazione su che cosa puo' essere andato storto.
Negli ultimi messaggi hai parlato di centinaia di fogli e migliaia di righe: sono informazioni che forse avresti dovuto dare prima per poter impostare una soluzione pensata proprio per elaborazioni massive e pronte per essere replicate su tanti fogli. Interessa anche sapere se i dati sono sempre in una sequenza crescente (cioe' un "padre" e' sempre in una riga precedente a un "figlio"), nel qual caso l' algoritmo di calcolo puo' essere ottimizzato nell' ottica di centinaia di fogli con migliaia di righe, o se invece e' possibile trovare, mettiamo, un 100.1 dopo un 100.1.1.
Volendo costruire qualcosa di ottimizzato sara' infine necessario conoscere il layout preciso dei dati e del workbook che li contiene.
A problema risolto potremo poi parlare di alcune discrepanze e disottimizzazioni nel codice della Sub BOM, probabilmente legate alla fase di apprendimento del vba ma che conviene conoscere per non costruire su basi confuse.

In conclusione ti chiederei di verificare se la Function MyQty, nella versione pubblicata da w&g (vedi sopra), produce risultati corretti. Se No allora dovresti condividere un file di prova per scoprire l' arcano.
Dando per scontato che myQty (almeno nella formulazione corrente) non sia molto efficiente quando applicata a un file con tanti fogli e tante righe ti chiederei di rispondere ai miei interrogativi posti prima (sequenza dei dati, layout preciso dei dati sul foglio e dei fogli nel workbook) per poter cogitare qualcosa di piu' veloce.

Ciao a tutti.