creare un riassunto" lista art venduti a diversi clienti

[marcus69]

ciao a tutti,

ho un foglio excel, con la lista degli articoli venduti, articolo 1... articolo n, DIVISA PER CLIENTE quindi l'articolo x possono averlo acquistato più clienti.

Ho bisogno di creare un foglio riepilogativo che ignori i clienti ma che mi faccia il subototale di ogni singolo articolo

e con un cerca.vert o matrice.somma.prodotto credevo di aver risolto, ma il problema è: quando ho una nuova lista vendite generata con i nuove righe di vendita che NON ha le stesse dimensioni di quella originale, ricevo errore.

il file excel in questione articolo/cliente/quantità/data ( lista_vendita_cliente ) è estratto da un gestionale che era vecchio ai tempi di Noè ed è in mano a dinosauri che non collaborano in nessun modo ad un progresso.

quindi mi trovo ogni settimana a dover estrarre la lista vendita per cliente, RISOLVERE il problema di cui al titolo del 3d, generare il nuovo foglio con i subtotali e "copiarlo" con l'inventario di magazzino ( ho il file inventario ) per verificare le scorte e pensare ai riordini del materiale.

Essendo la lista variabile, nel tempo devo fare in modo che il subtotale legga sempre tutte le righe del file lista vendita per cliente.
Poco importa se al venerdì quando faccio il controllo, tutte le volte avrò una lista sempre più grande.
La cartella che ho preparato contiene i fogli

1) inventario con le colonne: articolo giacenza attuale, vendite, giacenza rimanente
2) lista riassuntiva vendita per articolo ( che pesca i dati dal foglio 3) : colonne: articolo quantità
3) lista vendita per cliente colonne : articolo - cliente - quantità- data vendita


allego un semplice file di esempio:

http://www.pcdata.it/upload18/riassunto_articoli_vendita.xlsx

[Anthony47]

Beh, ma le tue formule di foglio Articoli_Venduti possono benissimo indirizzare diciamo 1000 righe (o piu'; fino a 5000 non dovresti avere problemi di rallentamento) del foglio Lista_Vendite_Per_Cliente, in questo modo puoi avere i risultati aggiornati man mano che aggiungi nuove righe; tipo, in B2:

Codice: Seleziona tutto

=MATR.SOMMA.PRODOTTO(('lista_vendite_per cliente'!$A$2:$A$1000=A2)*'lista_vendite_per cliente'!$C$2:$C$1000)

La formula non ha bisogno di essere introdotta con Contr-Maiusc-enter, basta il solo Enter

Pero' questo e' un lavoro da "Tabella pivot":
-selezioni le colonne complete A:D del foglio Lista_Vendite_Per_Cliente
-digiti Alt-d-b per avviare il wizard della tabella pivot
-confermi con Avanti le scelte di default
-indichi come $A1:$D1000 l'intervallo dei dati di origine (o anche piu' lungo; non lasciare $A:$D perche' potrebbe in realta' limitarsi alle righe attuali, escludendo cosiì righe che potresti aggiungere "domani")
-scegliere NuovoFoglio come sede della tabella pivot e premere Fine
-Trascina ora il campo Articolo nell'area Righe
-trascina il campo Quantita' nell'area Valori; accertarsi che compaia come "Somma di quantità" (e non, ad esempio "Conteggio di quantità")

Avrai cosi' la tabella che ti serve; per aggiornarla con un nuovo elenco su foglio Lista_Vendite_Per_Cliente ti bastera' fare Tsto dx su un campo della tabella e scegliere Aggiorna.

Ciao

[marcus69]

Aspetta, io son qui che sto impazzendo con le formule e tu mi dici che la PIVOT ( che ho scartato inizialmente ) può invece fare tutto il lavoro? IO TI ADORO
Ho scartato la tabella Pivot inizialmente proprio perché temevo che tutte le volte che faro l'esportazione dei dati dal gestionale e "incollerò" i dati esportati nel mio file EXCEL , non si calibrasse coerentemente

Perché questo è l'inizio del lavoro, io alla fine devo arrivare ad avere un foglio con queste colonne:

FOGLIO FINALE DI ANALISI ( oggi foglio inventario nel mio file )

colonne
articolo / giacenza / in ordine / in ordine a 1 settimana / a 2 set/ a 3 set / a 4set / rimanenza in magazzino / previsto ingresso da fornitore / media venduto ultimi 30 giorni / media venduto ultimi 90 giorni / media venduto mensile ultimi 12 mesi / media venduto mensile anno 2019 /

Ma dalla tabella Pivot, poi, posso fare una lettura dei dati e portarla nel mio "inventario"?
Perché il mio timore è, se modifico la Pivot, si modificano le posizioni delle celle e i riferimenti e si sballano le letture.

Il motivo per cui avevo scartato la PIVOT è quello

( ho aggiornato il file )
http://www.pcdata.it/upload18/riassunto ... ndita.xlsx

[Anthony47]

E' corretta la considerazione che la pivot non dara' sempre la stessa lista di prodotti.

Quindi se tu devi "importare" le info in arrivo dal gestionale in un tuo inventario, che evidentemente deve comprendere tutti i possibili prodotti, allora l'ipotesi formule (con l'intervallo esteso) puo' essere preferibile.
In alternativa, con la tabella pivot, devi "pescare" dalla pivot le movimentazioni tramite un Se.Errore (forza "0" se prodotto non in tabella) + Cerca.Vert (cerca il codice e restituisce la movimentazione).

Devi anche pensare a come "congelare" le variazioni all'interno del tuo inventario, sia che le variazioni siano calcolate con formule che con tabella pivot. Ovviamente il mio suggerimento e' che copi le movimentazioni e poi fai un Incolla speciale /Valori sulla colonna delle giacenze con l'opzione "Sottrai". Se registri una macro mentre esegui queste operazioni allora potrai usare quella macro per ripetere, giorno dopo giorno, la stessa operazione.
Se procedi con la macro autoregistrata e pubblichi il codice che ottieni allora potremmo suggerire qualcosa altro per rendere piu' solida la procedura.

Oppure, e in effetti e' preferibile, copi la movimentazione e poi fai Incolla speciale /Valori in una nuova colonna libera accanto all'elenco prodotti, in modo da creare un tabellone con le movimentazioni dei singoli giorni e con queste creare poi la somma dei consumi da utilizzare peri calcoli del tuo inventario. Anche in questo caso una macro autoregistrata puo' fare il 90% del lavoro, poi (se pubblichi il codice ottenuto) il 10% rimanente lo facciamo qui sul forum.

Per i primi suggerimenti su come registrare una macro, vedi viewtopic.php?f=26&t=103893&p=622593#p622593

Ciao

[marcus69]

ok, il tuo ragionamento fila, ed era un opzione che avevo considerato, mi ero fermato per il timore macro, ma se mi scrivi che una autoregistrata mi risolve parte del mal di testa vado sicuro in quella direzione meglio sbattersi, affrontare il timore e vivere meglio dopo.

domani quando rientro in ufficio, faccio un test con dati "reali", e nomi di campi reali, buttando giu un flusso di procedura cui riferirmi.

nel complesso hai centrato il punto:

ho un A inventario ( oggi) che nel 2020 diventa la mia giacenza di lavoro che varia nel tempo,
campi ( colonne ) : 1 articolo - 2descrizione - 3quantità in magazzino - 4quantità in ordine - 5quantità da fornitore IN ATTESA DI ESSERE RICEVUTA NON NEL MIO MAGAZZINO

Ho un B foglio ordini che varia "ogni giorno ":
campi ( colonne ) : articolo - descrizione - cliente - data di consegna

devo avere in foglio riepilogo ogni volta che lo interrogo :
campi ( colonne ):
1-articolo -
2-descrizione -
3-quantità in magazzino -
4-quantità in ordine -

5-qta ordine per la settimana di interrogazione -
6- qta in ordine per la settimana presente più una ( a due settimane ) -
7- qta in ordine per la settimana presente piu due ( a tre settimane ) -
8- qta ordine 30 giorni - -

9-rimanenza in magazzino dopo che ho ordinato il TOTALE ( campo quantità in ordine ) -
10- rimaneva in magazzino TEORICA DOPO CHE AL CAMPO 9 AGGIUNGE il campo 5 dell'inventario ( teorica perché quel materiale non lo ho in magazzino )
11-media venduto mese precedente -
12- media trimestre precedente -
13- media anno solare precedente ( nel 2019 per mese )

e poi metterò un campo finale 14 e 15 con la quantità da ordine e il fornitore a cui ordinarla e da li partirà un altro cinema ( tutto questo analizzare mi serve per decidere questi due dati, quanto e a chi ! )

Il foglio inventario e il foglio ordini ( A & B ) li devo estrarre manualmente dal gestionale, in verità ho un progetto di automatizzare anche quello, in un secondo momento o con una chiamata da EXCEL al database - ho visto che è stato fatto in altri reparti per altre cose - o con un modulo Python che dalle prime ricerche sembra essere ottimo per le automazioni del sistema, ma questa sarà storia per più avanti.

Ora devo tenere sotto controllo il magazzino e i 250 prodotti che vi si trovano dentro.

[marcus69]

dunque, sto proseguendo, ho ottenuto un ottimo risultato la chiave sta tutta qui

=((CERCA.VERT(D2;magazzino!$A$2:$F$985;4;FALSO()))-(SE(G2=$I$1;MATR.SOMMA.PRODOTTO(($D$2:$D$389=D2)*($G$2:$G$389=$I$1)*$H$2:$H$389);"0")))

ho ridotto a due fogli.

magazzino e ordini

ho creato l mie colonne e sembrerebbe funzionare tutto quasi perfettamente, sto rifinendo.

Ora rimane un problema di comodità le tre aree che uso per il cerca verticale, e per la ricerca dati nella matrice, che ho colorato li sopra non sono fisse. Mai.
Nel foglio ordini : ogni volta che degli ordini vengono evasi, le righe diminuiscono. Ogni volta che entrano nuovi ordini le righe aumentano.
Nel foglio magazzino: ogni volta che tolgo o metto un nuovo articolo ad inventario ho una variazione delle righe della lista.

Le colonne sono sempre quelle, ma variano in numero di righe. Ho pensato di " nominarle " in modo che vado a cambiare l'ultimo numero nel nome assegnato , e mi risparmio un bel po di tempo. Ma mi chiedo se con VBA/Macro, sia possibile fare una cosa ancora più semplice.
In pratica importo giacenza, e ordini dal database, copio/incollo, entrambi nel mio foglio, e LANCIO QUESTA Ipotetica MACRO/VBA che "adatta" le nuove colonne di riferimento in funzione della nuova dimensione.

Ritenete sia possibile?

[marcus69]

ecco il file di esempio

http://www.filedropper.com/test2_46

[marcus69]

ho fatto questa

Sub Macro5()
' Macro5 Macro
'
x = Range("a2").End(xlDown).Row ---- ( codice recuperato)
y = Range("a2").Column ---- ( codice recuperato)
Range(Cells(2, 1), Cells(x, y)).Select ---- ( codice recuperato)
' Range("A2:A8").Select ---- ( codice registrato e messo come commento che toglierò)
ActiveWorkbook.Names.Add Name:="uno", RefersToR1C1:="=uno!R2C1:R6C1" ---- ( seleziona la lista precedentemente creata e la sostituisce con quella nuova presa dal nuovo intervallo di celle)
End Sub

che ne pensate?

[marcus69]

no, la macro non va, purtroppo mi serve aiuto di qualche esperto VBA

l'area la seleziona, ma poi quando assegna il nome all'area fa un riferimento assoluto e non cambia il riferimento di celle.

x = Range("b2").End(xlDown).Row
y = Range("b2").Column
Range(Cells(2, 2), Cells(x, y)).Select
'ActiveWorkbook.Names.Add Name:="uno", RefersToR1C1:="=uno!R2C1:R6C1"

la mia area 1, per qualche motivo, viene dettata con R2C1:R6C1 e non "aumenta ( o diminuisce ) " i numero di riga considerati.

[marcus69]

sono un pirla: non si fanno alle 21 questi lavori.... risolto, mancava il riferimento a tutta la griglia



=((CERCA.VERT(D2;magazzino!$A$2:$F$985;4;FALSO()))-(SE(G2=$I$1;MATR.SOMMA.PRODOTTO(($D$2:$D$389=D2)*($G$2:$G$389=$I$1)*$H$2:$H$389);"0")))

tra l'altro ho un problema,
ho creato le 4 aree e le tre che sono nello stesso foglio funzionano, quella nell'altro foglio pur essendo correttamente selezionata non va.

[marcus69]

dunque, ho realizzato questa

Sub Macro1()
'
x = Range("a2").End(xlDown).Row
y = Range("a2").Column
Range(Cells(2, 1), Cells(x, y)).Select
ActiveWorkbook.Names.Add Name:="ciao", RefersTo:="Range(Cells(2, 1), Cells(x, y)).Select"
End Sub

ma non va, non "sente "i numeri " e da errore

[Anthony47]

Non so a che punto sei arrivato, quindi vado a braccia.

Per le formule, puoi benissimo inserire riferimenti lunghi quanto basta per stare tranquilli in tutte le situazioni; tipo:

Codice: Seleziona tutto

=((CERCA.VERT(D2;magazzino!$A$2:$F$3000;4;FALSO()))-(SE(G2=$I$1;MATR.SOMMA.PRODOTTO(($D$2:$D$1000=D2)*($G$2:$G$1000=$I$1)*$H$2:$H$1000);0)))

Ho immaginato che in "magazzino" non arriverai mai a 3000 righe mentre nel "movimentato" non arriverai mai a 1000; se serve puoi allungare, ma senza esagerare.

Anzi, invece di Matr.Somma.Prodotto, se hai almeno XL2007, potresti usare Somma.Più.Se che e' abbastanza piu' veloce:

Codice: Seleziona tutto

=(CERCA.VERT(D2;magazzino!$A$2:$F$3000;4;FALSO()))-(SE(G2=$L$1;SOMMA.PIÙ.SE($H$2:$H$1000;$G$2:$G$1000;$L$1;$D$2:$D$1000;D2); 0))

Mi pare che le tue macro dovevano servire a creare dei "Nomi" che includessero tutte le righe dei fogli; se risolvi allungando i riferimenti nelle formule allora diventano tutte inutili.

Ciao

[marcus69]

SEI UN GRANDE !!!!
il somma.piu.se HA risolto tutto TUTTO.

La mia formula con la matrice, nonostante allungassi l'intervallo, mi restituiva l'errore.
Quella in magazzino, nessun problema, il cerca verticale lo fa anche con un area più ampia.
MA la matrice .somma.prodotto, quando andavo oltre le righe con valore, nulla dava errore,
Con questa modifica funziona benissimo
Proseguo nello sviluppo Grazie per ora.
Apro un nuovo 3d con il nuovo problema