Macro o matrice

Hai un problema con Excel?

Nuova discussione

Macro o matrice
di fada2ma (utente non iscritto) data: 14/05/2014 18:31:27
Buonasera a tutto il forum. Vorrei sottoporvi un problema di cui non riesco, per dichiarata incapacità e ignoranza in materia, a trovare soluzione. Ho una base id dati in una cartella excel che importo dal programma di contabilità in as400. Questa base di dati può variare per numero di righe. Nella fattispecie ho le seguenti colonne: Colonna A 'Azienda', Colonna B 'Centro di costo', Colonna C 'Descrizione Centro di costo', Colonna D 'Conto di contabilità', Colonna E 'Nome conto di contabilità', Colonna F 'Elemento di costo', Colonna G 'Nome Elemento di costo', Colonna H e Colonna I rispettivamente Valori Anno corrente e Valori anno precedente.
In un altra cartella di excel ho un foglio chiamato PDC che riporta in se le informazioni presenti nelle Colonne D, E, F, G della base dati. In più ha nella Colonna E 'Nome voce di Riclassificato' e nella Colonna F 'Codice voce di Riclassificato' .
Il mio problema è il seguente: vorrei creare un controllo che, quando la base dati CONTIENE conti di contabilità o elementi di costo in più rispetto a quelli previsti dal foglio PDC presente nell'altra cartella, li prenda, attraverso un comando o una formula, e li scriva nel foglio PDC.
Spero di essere stato chiaro...
In alternativa ditemi cosa non lo è e cercherò di spiegarmi meglio..
Confido nel vostro aiuto.
Grazie.
di lepat (utente non iscritto) data: 14/05/2014 18:38:47
la migliore spiegazione è allegare un file di esempio con anche il risultato desiderato
di Textomb data: 14/05/2014 18:42:14
ciao
La domanda è chiara. Esposta bene ed è stata scritta in italiano.
Le soluzioni possono essere più di una.
Puoi usare una macro (che fa tutto in automatico). Oppure delle formule da inserire nel foglio PDC.
Se vuoi una mano puoi allegare un file di esempio nel quale inserisci la base dati ed il foglio PDC senza dati sensibili.
Files allegati
di fada2ma (utente non iscritto) data: 15/05/2014 07:40:20
Buongiorno a tutto il forum e in particolare a Textomb e Iepat. Ho provveduto ad inserire due file (base dati e Riclassificato). Nella seconda cartella (Riclassificato) ho anche scritto qualche righe per agevolarvi nella comprensione del mio problema. Vi sono grato sin d'ora per il vostro aiuto e per quello di tutto il forum.
Vi auguro una buona giornata.
di Textomb data: 15/05/2014 18:22:06
Ho visto il tuo file.
Tu scrivi che il Leasing avendo come codice di conto nelle prime due cifre 45 dovrà andare nel foglio riclassificato tra i COSTI OPERATIVI. E da dove si evince?
Quello che manca è una tabella da cui si possa evincere che quel determinato conto vada agganciato ad una determinata voce di riclassificato.
Stando così le cose puoi solo importare le voci del foglio Base dati che mancano nel Riclassificato, ma potrai solo completare le colonne del foglio riclassificato A, B, C, D ma i dati riportati nelle colonne E ed F resteranno vuote perchè manca questo collegamento...
Altrimenti spiega meglio perchè potrei non aver capito.
di fada2ma data: 16/05/2014 09:14:14
Ciao Textomb, hai perfettamente ragione, la tabella ancora non era creata in quel file. Tra qualche minuto allego il file con la tabella creata. Ho fatto comunque delle prove, guardando il piano dei conti con cui avrei a che fare, e mi sono accorto che sarebbe meglio considerare non solo le prime due cifre ma anche le successive due (nell'esempio del Leasing 4515). Ciò perchè nel piano dei conti (vero) ci sono dei conti di contabilità che appartengono allo stesso capomastro ma il cui mastro è differente e che, in virtù di ciò, rientrano in raggruppamenti di riclassificato differenti. Un esempio: le spese per servizi, nel piano dei conti vero, hanno tutte il capomastro 60; dal mastro si distinguono poi in 3 sottovoci (35 servizi amministrativi, 30 servizi commerciali, 15 servizi industriali). Nel riclassificato le 3 sottovoci rappresentano 3 raggruppamenti differenti.
di fada2ma data: 16/05/2014 09:41:33
Ho provveduto a caricare il file con la tabellina che indica il legame. Per qualsiasi ulteriore chiarimento rimango a disposizione di chiunque voglia aiutarmi.
Grazie e buona giornata.
di Textomb data: 16/05/2014 09:56:59
non vedo nessuna tabellina... dov'è?
di fada2ma data: 16/05/2014 12:52:54
Si trova nel file Riclassificato1 che è all'esterno della cartelletta Esempio... io ho scaricato lo zip che avevo precedentemente caricato e c'è.
di Textomb (utente non iscritto) data: 16/05/2014 18:57:12
ancora non va.
Allora la tabellilna che proponi lega un codice a sei cifre con un codice di riclassificato. E la voce di Riclassificato come si lega?
quindi nella tabellina hai riportato per es.:
451510 - CO
Perciò a questo punto saprò che tutti i conti del PdC che hanno una struttura con 4515 (prime 4 cifre) andranno a finire nel codice di riclassificato CO. Giusto? Ma allora perchè la tabellina mi propone le sei cifre e non solo 4?
Inoltre, come si lega il codice di Riclassificato con la Voce di riclassificato? In questo caso dove sta il legame tra COSTI OPERATIVI e CO?
Il meccanismo che vuoi fare tu per gli umani è abbastanza semplice. Ma per le macchine no! Gli dobbiamo dare tutti gli elementi altrimenti l'operazione di costruzione della tabella del riclassificato è impossibile.

Riepiloghiamo altrimenti a Natale siamo ancora qui
Vediamo se ho capito.
Dunque tu hai una tabella di PdC su cui ci sono mille (più o meno) conti.
Questa tabella però è dinamica. Cioè si aggiorna continuamente con nuovi conti.
Tu vuoi, per sviluppare il Bilancio riclassificato, estrapolare l'elenco univoco di tutti i conti del PdC e inquadrarli correttamente in una tabella che ha la forma descritta nel file Riclassificato1.
In questo file ci sono 6 colonne di cui:
le colonne A, B, C, D le prendo precise prescise dal PdC e precisamente le estrapolo dalle colonne D, E, F, G di quest'ultimo.
La colonna F del Riclassificato la ricavo estrapolando le prime 4 cifre del conto (colonna D del PdC) e confrontandola con una tabellina (che dovrà essere archiviata sempre in un luogo ben preciso) che ha la forma descritta nel tuo ultimo file con sei cifre nella prima colonna e il codice di Riclassificato nella colonna accanto... Ovviamente in questa tabellina non ci possono essere conti duplicati.
I miei dubbi:
La colonna E del riclassificato, da dove la tiro fuori?
Perchè la tabellina che lega il conto con il codice di riclassificato non ha 4 cifre che sono quelle che servono?
di Textomb data: 17/05/2014 10:08:16
senza andare troppo sul sottile ti allego il tuo stesso file di esempio rinominato "Riclassificato Textomb" in cui ho inserito una formula speciale matriciale (ctrl-shift-invio) che riporta sulla tabella dei conti Riclassificati solo i codici univoci della tabella del PdC.
Per comodità ho riportato entrambe le tabelle nello stesso file in due fogli diversi. Ma è la stessa cosa.
Può essere che così ti è sufficiente. Altrimenti si dovrà pensare a qualche routine più complessa in VBA.
ciao.
di fada2ma data: 20/05/2014 09:26:22
Salve a tutto il forum e buongiorno a Textomb. Guarda ti allego i file su cui sto lavorando e che contengono già del codice vba che però mi da qualche problema. Nel file Riclassificati troverai una piccola spiegazione di quelli che sono gli errori e i problemi. Chiedo se gentilmente tu o qualcunaltro del forum può aiutarmi a risolverli.
Nella fattispecie il file Riclassificati contiene due pulsanti cui è assegnato del codice. Con il primo pulsante viene fatta girare una macro che legge il file CDC per verificare se ci sono conti di contabilità nuovi e se ci sono aggiorna il foglio PDC del file Riclassificati. Qui nasce poi il problema per cui ho iniziato questo post. La macro scrive soltanto i conti e i relativi elementi di costo ma non mi permette di abbinare le voci di riclassificato in modo automatico. Quindi al momento il lavoro lo devo fare manualmente. Tra l'altro, se non ho visto male, al momento questa macro (che si chiama Aggiorna Dati) scrive a prescindere se i conti siano presenti o meno nel foglio PDC del file Riclassificati.
Con il secondo pulsante viene fatta girare una macro che cancella il contenuto del foglio Db del file Riclassificati e scrive tutto il contenuto del file CDC e in più crea due chiavi di lettura (Key1, Ke2) che serviranno per rintracciare le voci da inserire nel riclassificato (foglio P&L) attraverso l'abbinamento dei valori contenuti nelle colonne E ed L . Solo che la macro va in errore e il risultato del foglio è quello che allego nel file zip.
Per qualsiasi ulteriore chiarimento sono a vostra disposione nella speranza che mi possiate aiutare....
di Textomb data: 20/05/2014 10:05:45
ho visto i tuoi file.
La questione è un pò incasinata. Il codice inserito, ad un primo esame, appare ridondante e prolisso (e, a quanto pare, anche poco efficace...)
Comunque, oggi non credo di avere tempo ma, per fortuna tua, l'ultimo lavoro che ho sviluppato è stato riclassificare un bilancio per conto di un'azienda prelevando i dati dal db di contabilità... Quindi diciamo che la problematica mi è un pizzico familiare anche se però non sono nè ragioniere e neanche commercialista.
di fada2ma data: 20/05/2014 10:29:37
Dai veramenteeeee??? Fantastico Textomb.. spero proprio tu mi possa aiutare.. Per qualsiasi ulteriore chiarimento sono a tua disposizione. I prg di contabilità che girano su AS400 non sono particolarmente chiari dal punto di vista grafico. Quindi il mio intento è di dare un aspetto più consono a dati che debbano essere letti anche sotto altri aspetti e non quelli meramente economici/finanziari.. tipo produttività di reparti o negozi ecc.
Comunque sono nelle tue mani e in quelle del forum...
di Textomb data: 20/05/2014 15:24:44
Allora.
Se non mi chiarisci una volta per tutte questo dubbio io mi fermo qui. Te l'avevo già chiesto ma ancora non ho capito.
tu scrivi che sei costretto a scrivere manualmente la voce ed il cod della tabella del Riclassificato.
Intendi le colonne E ed F della foglio PDC.
Ma questi elementi da dove si tirano fuori?
dovrebbe, a rigore, esistere una tabella (che nel tuo file non ho visto!) che associa ad ogni conto della tabella del Cdc la voce ed il cod del Riclassificato.
Quindi in estrema sintesi ci vorrebbe una tabella che ha 3 colonne
in una il codice del Cdc e nelle altre due la VOCE ed il cod del Riclassificato.
perciò a 551010000010 corrisponderà RICAVI DA VENDITA, R
stessa cosa anche per i codici 551010000020, 551010000500... E così via su TUTTI i codici del Cdc. Altrimenti la procedura automatica da dove dovrebbe prelevare queste informazioni per completare la tabella del PDC?
di fada2ma data: 20/05/2014 18:18:21
Si Textomb, hai capito alla perfezione. Al momento non c'è questa tabellina. Ma provvedo subito e la inserisco nel prossimo file che invio.
di Textomb data: 20/05/2014 20:08:45
ho proseguito nell'analisi del tuo file e devo aggiungere altre cose a dir poco discutibili.
Intanto ci saranno almeno un milione di potenziali rischi di errore. Certamente causati dalla versione allegata al forum che non mi fa capire bene. Per dirne una, per es, trovo le formule nel foglio P&L che si riferiscono all'intervallo Db!$A$2:Db!$A$1691 mentre il foglio Db conta al suo interno 2151 righe. Quindi in quella formula molte righe non le considera e non esiste, tra le routine che ho analizzato, nessuna istruzione che le aggiorni...
Il foglio Cover gioca un ruolo fondamentale per l'esposizione dei valori sul foglio P&L in quanto i risultati in quest'ultimo variano in base alle scelte operate nel foglio Cover nelle celle D5 e D8. Ma perchè non mi piace per niente? Alla fine tutto il casino è fatto per costruire quelle due chiavi Key1 e Key2 che si trovano nelle colonne A e B del foglio Db. Il foglio Pdc non entra mai in gioco nel foglio P&L se non fosse per la costruzione di quelle due chiavi poco chiare e molto oscure e tenebrose riportate nel foglio Db.
In ultimo, ma il foglio Cdc che si trova nel file Riclassificati che cos'è e soprattutto chi lo aggiorna???
Scusa se sono stato un pò prolisso ma negli ultimi due anni ho fatto solo analisi di bilanci in tutte le salse!
Se posso darti una mia opinione personale... Andrebbe riscritto tutto! Forse ci vorrà meno tempo e certamente con i risultati dovuti.
di fada2ma data: 21/05/2014 09:34:31
Buongiorno Textomb, ti ringrazio intanto per il tempo che mi stai dedicando. Il file CDC è un'estrapolazione di dati dalla contabilità. Il file Riclassificati dovrebbe importarsi i dati quindi creare delle chiavi da richiamare nel foglio P&L (per chiavi intendo un codice univoco che definisca un raggruppamento di conti di contabilità/elementi di costo; es. la Voce di riclassificato Ricavi di vendita è riferita all'elemento di costo Ricavi di vendita che raggruppa i conti dei ricavi quindi il conto corrispettivi, merci c/vendite ecc.). Il codice che è stato scritto, tranne la prima parte (quella del cerca e scrivi nuovi conti), scaturisce dalla registrazione di macro, ecco perchè probabilmente non è tanto chiaro. Rimango in attesa di suggerimenti e se ritieni che sia meglio riscrivere tutto per me possiamo iniziare... solo che non vorrei risultare un approfittatore... In ogni caso ti ringrazio per l'aiuto che fin qui mi hai dedicato.
di Textomb data: 21/05/2014 10:36:38
cit. Rimango in attesa di suggerimenti e se ritieni che sia meglio riscrivere tutto per me possiamo iniziare...
Il fatto è che non si può fare diversamente. Quello che mi hai mandato, se è tutto lì, va riscritto. Non avrai mai un risultato decente se prosegui così.
Da parte mia posso mandarti una bozza di come ritengo sia meglio sviluppare il tutto. Ma ovviamente mi mancano mille elementi che non mi permettono di fare un'analisi corretta.
Per esempio non condivido la costruzione delle due chiavi nelle colonne A e B del foglio Db ma non ho elementi sufficienti per proporti una valida alternativa... Dovrei capire meglio e certamente questo Forum non è il canale adatto in quanto ci addentriamo nella consulenza specifica.
di fada2ma data: 21/05/2014 11:21:11
Capisco, beh allora mi tocca proseguire per la strada intrapresa... anche perchè la consulenza specifica per quanto sia la scelta più logica per un'azienda, non lo è per un dipendente che già di valore aggiunto all'azienda ne porta abbastanza. Quindi se riesco, anche se con forme "arcaiche" a raggiungere l'obiettivo bene altrimenti come si dice dalle mie parti... dove arrivo metto punto... In ogni caso se tu vuoi mandarmi una bozza di come lo intenderesti tu il lavoro te ne sarei grato come te ne sono tutt'ora per tutto il tempo che hai dedicato alla mia "causa".
di Textomb data: 21/05/2014 13:11:38
vista la mia precedente esperienza in materia di riclassificazione di bilanci e considerato che ormai il tuo file l'ho snocciolato e compreso non mi ci vorrà molto a buttarti una bozza ed allegarti un file (funzionante sulla base di quello da te inviato) con le mie osservazioni.
Considera però che avrà le limitazioni di una bozza. Quindi potrai utilizzarlo per testarlo ma sono certo che dovrai apportare le modifiche e le integrazioni necessarie quando lo utilizzerai per il tuo lavoro...
di fada2ma data: 22/05/2014 12:42:57
Buongiorno Textomb, ieri ho proseguito lo sviluppo del mio file e sono riuscito a sistemare alcune parti di codice. Nella fattispecie ad eliminare degli errori dovuti al tipo di formato del file origine e del file di arrivo (cdc e riclassificati). Adesso le macro, anche se come dici tu un po' complesse e probabilmente strutturate male, eseguono i compiti in maniera corretta. L'unica cosa che manca realmente, in base al progetto che avevo io, è l'automatismo di aggancio tra il codice della voce di riclassificato e i conti di contabilità. Probabilmente non è il massimo lavoro che poteva venire fuori ma intanto fa il suo dovere.. Accetto comunque qualsiasi suggerimento tuo o del forum.

Se interessa allego l'ultima versione del file...
di Textomb data: 22/05/2014 13:07:13
certo allega pure l'ultima versione del tuo file, così faccio una verifica anch'io.
Nel frattempo sto sviluppando qualcosa in bozza per te che ti allegherò non appena possibile. Potrai senz'altro attingere qualora riscontrassi qualcosa di tuo interesse.
di fada2ma data: 22/05/2014 14:43:46
Ho inserito l'ultima versione. Se riesci potresti risolvermi l'unico problema che rimane e che non riesco a sistemare. La macro aggiorna conti del file Riclassificati quando la mando in esecuzione non riesce a copiare un conto che si trova alla riga 468 del file cdc. E' l'unico #N/D della colonna Key1 del foglio db che non riesco a risolvere.
di Textomb data: 22/05/2014 16:48:05
al momento non sono in grado di aprire il file... Ma conoscendo il tuo lavoro ti dico:
vista la natura dell'errore che ti da sul foglio Db nella colonna Key1 vuol dire che non ha trovato la corrispondenza con il codice di conto. Molto probabilmente non trova la corrispondenza nel foglio Cdc oppure non la trova nel foglio Pdc o in entrambi... Verifica se il codice su quella riga lo trovi nei due rispettivi fogli. Certamente è per questo motivo.
Comunque spero stasera di potergli dare un'occhiata...
di Textomb (utente non iscritto) data: 22/05/2014 23:43:43
Ti dico cosa non funziona nel tuo file.
Intanto per fare un'analogia tu per arrivare da roma a milano hai preso prima per Napoli, poi Bari, Pescara San Marino, Venezia e finalmente sei arrivato a Milano. Insomma secondo me, prima o poi ti esplode il pc...
Tranquillo scherzo. Non ti esplode però, caspita, che giro che fai. Considera che il mio pc quando ho fatto girare la tua macro CERCA_e_SCRIVE_Nuovi_Conti() ha impiegato un bel pò per completare...
Andiamo agli errori.
1) nella suddetta macro ad un tratto hai questa istruzione (riporto fedelmente)
If Uguali = "NO" Then
Trovato = Trovato + 1
UR2 = UR2
SH1.Range("D" & I & ":G" & I).Copy Destination:=SH2.Range("A" & UR2)
SH2.Range("G" & UR2) = SH2.Range("C" & UR2) & "-" & SH2.Range("A" & UR2)
End If
Il tuo intento (immagino) è aggiungere il codice nuovo che hai trovato in coda nel foglio Pdc. Allora devi correggere UR2=UR2+1 altrimenti non aggiunge nulla ma sovrascrive sempre nell'ultima riga...

2) Nella tabella Pdc i codici univoci sono costituiti dall'unione di due campi e cioè (ECOS-Conto). Ma allora allo stesso modo nella Sub Aggancia_Cdc() devi cercare la corrispondenza con gli stessi due codici e non con uno solo di questi. ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],Cdc!R1C1:R500C3,3,FALSE). Per farti capire meglio ti dico che, per es, nella tabella Cdc esiste il codice 601510000065 che è agganciato a due codici ECOS 000120 e 000145. La funzione CERCA.VERT in questo caso trova la prima corrispondenza e ti aggancia il relativo cod del Riclassificato ma non farà sempre l'assegnazione corretta perchè in realtà nella tabella Pdc dovrebbero essercene due. Poi in realtà ne trovi uno solo per il problema di cui al punto precedente...

3) Nonostante la costruzione della tabella Pdc è fatta sul presupposto di avere un elenco di codici univoci costituiti dall'unione di ECOS-CONTO, mi ritrovo dei codici univoci (ECOS-CONTO) ripetuti. Nella logica è un errore! Per es. il codice 000090-601055000010, me lo ritrovo due volte. Una volta con il Cod Riclassificato Pr e un'altra con il cod Riclassificato Sml (colonna F). Anche in questo caso quando cercherai di assegnare nella tabella Db il cod del riclassificato la procedura (appena aggiusterai l'errore di cui al punto precedente) ti andrà a prendere il primo codice che trova. Ma siccome non è univoco non farà sempre l'assegnazione corretta.

4) Infine, prova a rimodulare un pò il tutto perchè fai troppi giri nella Sub CERCA_e_SCRIVE_Nuovi_Conti().
Personalmente avrei usato l'istruzione Find. Diventa tutto molto più leggibile e veloce.

Comunque, non ti scoraggiare. Continua così che va bene lo stesso a patto però che aggiusti questi errori perchè un bilancio riclassificato non può contenerne neanche uno!
di fada2ma data: 26/05/2014 09:34:53
Buongiorno Textomb, ho riflettuto sul tuo ultimo post, controllato il file e fatto delle modifiche che adesso ti posto nel nuovo file.
Adesso però vorrei modificare il "principio" del foglio cover con un altro sistema che mi permetta di selezionare i negozi o i reparti sempre mantenendo le formule del foglio P&L. Mi puoi dare qualche suggerimento e/o aiuto in questo senso??
di Textomb data: 26/05/2014 11:40:11
ciao
ho visto il tuo nuovo file. Probabilmente non ho visto bene ma, rispetto ai miei suggerimenti, ad una rapida lettura del codice hai fatto solo l'intervento indicato al punto n.1 e cioè UR=UR+1.
Il resto è rimasto così com'era. Sbaglio? Altrimenti potresti indirizzarmi tu sulle modifiche che hai fatto e come le hai fatte?
Riguardo il "principio" del foglio cover con un altro sistema, puoi adottare molte scelte. Dipende da te e da cosa vorresti migliorare rispetto alla situazione attuale.
Per esempio io andrei con una maschera (userform) che si apre direttamente dal foglio P&L e da lì operi le tue scelte come se fossi nel foglio Cover. Ma sono dettagli. L'importante è che funzioni...
di fada2ma data: 29/05/2014 11:55:26
Ciao Textomb. Allego il file con gli ultimi cambiamenti sia al codice che agli altri fogli. Adesso però vorrei ulteriormente svilupparlo magari, come dicevi tu, utilizzando un sistema migliore di quello fatto da me con il foglio cover. Io pensavo ad una userform in cui scegliendo il centro di profitto da interrogare mi uscissero tutti dati nel modello di riclassificato che si trova nel foglio P&L. Solo che non so da dove partire...
A proposito mi incuriosisce un argomento trovato su internet ma che nel forum non ho riscontrato... mi sai dire qualcosa a proposito di master detail? E' un metodo applicabile al mio lavoro?
di Textomb data: 29/05/2014 13:34:28
ciao
innanzi tutto non vedo alcun file allegato... Te lo sei scordato.
Per quanto riguarda il master detail avresti bisogno di un accesso autorizzato al database SQL o altro.
di fada2ma data: 29/05/2014 15:00:59
Ho inserito il file...

Vuoi Approfondire?