Confronto/Estrapolazione dati Excel
Ciao a tutti,
La mia richiesta è questa:
Ho un foglio Excel della mia azienda, contenente i dati di vari prodotti: lo dovrei confrontare con quello di un fornitore (confronto il campo "codice prodotto") ed estrapolare il prezzo dal file del fornitore e sostituirlo a quello nel nostro. Dopodichè, se il prezzo è cambiato colorare la cella "prezzo" di rosso, se non lo è lasciarla così, se il prodotto è assente nel nostro database il campo diventa verde.
Secondo voi è fattibile? E con quale operazione? Bastano le formule "base" di Excel o serve scrivere codice in VBA? Aggiungo che saranno almeno 10 anni che non tocco questo linguaggio; quindi se poteste spiegarmi la cosa in maniera molto basica ve ne sarei grato.
Grazie e un saluto.
P.S.: e' davvero questione di vita o morte, buona parte delle mie possibilità di essere assunto passano da qui!
Re: Confronto/Estrapolazione dati Excel
Citazione:
Originariamente Scritto da
stealth_ema
Ciao a tutti,
La mia richiesta è questa:
Ho un foglio Excel della mia azienda, contenente i dati di vari prodotti: lo dovrei confrontare con quello di un fornitore (confronto il campo "codice prodotto") ed estrapolare il prezzo dal file del fornitore e sostituirlo a quello nel nostro. Dopodichè, se il prezzo è cambiato colorare la cella "prezzo" di rosso, se non lo è lasciarla così, se il prodotto è assente nel nostro database il campo diventa verde.
Secondo voi è fattibile? E con quale operazione? Bastano le formule "base" di Excel o serve scrivere codice in VBA? Aggiungo che saranno almeno 10 anni che non tocco questo linguaggio; quindi se poteste spiegarmi la cosa in maniera molto basica ve ne sarei grato.
Grazie e un saluto.
P.S.: e' davvero questione di vita o morte, buona parte delle mie possibilità di essere assunto passano da qui!
Via formula potrebbe essere fattibile se cambi un po' il tipo di risultato ottenuto (invece che sovrascrivere direttamente le celle, aggiungere in due nuove colonne differenti il prezzo modificato e la casellina colorata).
Vedi ad esempio questo articolo e quelli correlati:
http://www.mbaexcel.com/excel/how-to-use-vlookup/
Non ho esperienza diretta di questo perchè non essendo un esperto di Excel ma sapendo invece programmare, per queste cose passo diretto a VBA anche quando magari ci sarebbero sistemi senza macro :look:
In VBA, per l'appunto, è comunque relativamente semplice; ti butto giù un esempio proprio "alla maledetta", non è particolarmente efficente su grandi quantità di dati ma dovrebbe funzionare:
Codice:
Option Explicit
Sub Confronta()
'Le colonne che contengono i due dati di riferimento (codice/importo)
Const COLONNA_CODICE_FORNITORE As String = "A"
Const COLONNA_IMPORTO_FORNITORE As String = "D"
Dim bookFornitore As Workbook
Dim sheetFornitore As Worksheet
Dim rangeFornitore As Range
Dim codFornitore As String
Dim valoreAzienda As String 'o Double/Currency o quello che e', dipende dal tipo di cella...
Dim row As Long
Set bookFornitore = Workbooks.Open("C:\fornitore.xlsx")
Set sheetFornitore = bookFornitore.Worksheets(1) 'o l'indice corretto del foglio che ti interessa, se non è il primo
'Qui metti codice di controllo su eventuali errori di apertura ecc... ecc..
'ciclo su tutto il foglio fintanto che la colonna col codice non è vuota
'nota bene: se il foglio ha dei "buchi" nelle righe, allora devi trovare un'altro sistema per capire quando interrompere
row = 1
Do
codFornitore = sheetFornitore.Range(COLONNA_CODICE_FORNITORE & row).Value
Set rangeFornitore = sheetFornitore.Range(COLONNA_IMPORTO_FORNITORE & row)
valoreAzienda = Lookup_Codice(codFornitore)
If Len(valoreAzienda) > 0 Then
'trovato
rangeFornitore.Value = valoreAzienda
rangeFornitore.Interior.Color = RGB(255, 0, 0)
Else
'non trovato
rangeFornitore.Interior.Color = RGB(0, 255, 0)
End If
row = row + 1
Loop Until Len(codFornitore) = 0
Set sheetFornitore = Nothing
bookFornitore.Close
Set bookFornitore = Nothing
MsgBox "Confronto completato", vbInformation
End Sub
Function Lookup_Codice(codFornitore As String) As String 'o Double/Currency o quello che e', dipende dal tipo di cella...
'Le colonne che contengono i due dati di riferimento (codice/importo) del TUO foglio
Const COLONNA_CODICE_AZIENDA As String = "D"
Const COLONNA_IMPORTO_AZIENDA As String = "E"
Dim sheetAzienda As Worksheet
Dim codAzienda As String
Dim row As Long
Set sheetAzienda = Workbooks(1).Worksheets(1) ' o l'indice corretto del foglio che ti interessa, se non è il primo
'Vedi commenti all'altro ciclo
row = 1
Do
codAzienda = sheetAzienda.Range(COLONNA_CODICE_AZIENDA & row).Value
'se i due codici corrispondono, restituiamo il contenuto della seconda colonna e usciamo dal ciclo
If StrComp(codAzienda, codFornitore, vbTextCompare) = 0 Then
Lookup_Codice = sheetAzienda.Range(COLONNA_IMPORTO_AZIENDA & row).Value
Exit Do
End If
row = row + 1
Loop Until Len(codAzienda) = 0
'Se arrivi qui, il codice non e' stato trovato, usciamo con valore vuoto
End Function
Re: Confronto/Estrapolazione dati Excel
Ciao,
Grazie per il suggerimento!
Ho riadattato in base ai miei dati le tue istruzioni e la procedura sembra girare, ma entra in una specie di loop continuo.
Credo di aver sbagliato qualcosa... Tu come la vedi?
Option Explicit
Sub Aggiorna()
Const Colonna_Codice_Produttore As String = "F"
Const Colonna_Prezzo_MEPA As String = "H"
Dim FORdata As Workbook
Dim Foglio2 As Worksheet
Dim RangeFornitore As Range
Dim CodProduttore As String
Dim Prezzo_old As String
Dim row As Long
Set FORdata = Workbooks.Open("C:\XXX\Agg_prezzi_XXX_17_09_2013_2 .xls")
Set Foglio2 = FORdata.Worksheets(1)
row = 2
Do
CodProduttore = Foglio2.Range(Colonna_Codice_Produttore & row).Value
Set RangeFornitore = Foglio2.Range(Colonna_Prezzo_MEPA & row)
Prezzo_old = Lookup_Codice(CodProduttore)
If Len(Prezzo_old) > 0 Then
'trovato
RangeFornitore.Value = Prezzo_old
RangeFornitore.Interior.Color = RGB(0, 255, 0)
Else
'non trovato
RangeFornitore.Interior.Color = RGB(255, 0, 0)
End If
row = row + 1
Loop Until Len(CodProduttore) = 0
Set Foglio2 = Nothing
FORdata.Close
Set FORdata = Nothing
End Sub
Function Lookup_Codice(CodProduttore As String) As String
Const COLONNA_SKU_INFOBIT As String = "F"
Const COLONNA_PREZZO_INFOBIT As String = "N"
Dim NSdata As Workbook
Dim Foglio1 As Worksheet
Dim SKU As String
Dim row As Long
Set NSdata = Workbooks.Open("C:\XXX\Cartel1_PC.xls")
Set Foglio1 = NSdata.Worksheets(1)
row = 2
Do
SKU = Foglio1.Range(COLONNA_SKU_INFOBIT & row).Value
If StrComp(SKU, CodProduttore, vbTextCompare) = 0 Then
Lookup_Codice = Foglio1.Range(COLONNA_PREZZO_INFOBIT & row).Value
Exit Do
End If
row = row + 1
Loop Until Len(SKU) = 0
End Function
Fammi sapere e grazie ancora!
Ema.
Re: Confronto/Estrapolazione dati Excel
Citazione:
Originariamente Scritto da
stealth_ema
Ciao,
Grazie per il suggerimento!
Ho riadattato in base ai miei dati le tue istruzioni e la procedura sembra girare, ma entra in una specie di loop continuo.
Credo di aver sbagliato qualcosa... Tu come la vedi?
A occhio, non vedo niente di particolare nel codice... tieni conto che i due cicli davano per assunto che, prima o poi, la colonna con il codice fornitore fosse completamente vuota per stabilire quando uscire dal loop; se questo non si verifica con i tuoi file c'è qualcosa da sistemare.
Metti un breakpoint su entrambe le istruzioni 'Loop Until' ed esegui passo-passo... quando arrivi alla riga dove, secondo te, dopvrebbe finire la lista, controlla cosa contengono le variabili incriminate
Re: Confronto/Estrapolazione dati Excel
Ciao,
Il programma funziona perfettamente; semplicemente impiega circa 3 minuti di orologio per elaborare (gira su una vecchia macchina con XP...).
Grazie ancora per l'aiuto, ma mi chiedono se si possono aggiungere due modifiche "minori".
1) Tutta la procedura dovrebbe avere come condizione iniziale che nella colonna "descrizione" il prodotto sia indicato come "personal computer", in caso contrario saltare tutte le operazioni e segnalare il codice prodotto in rosso.
2) Fare anche una ricerca "inversa", ovvero: al momento viene controllato che il "codice prodotto" del file "Agg_prezzi" sia contenuto nel file "Cartel1", mentre vorremmo fare il contrario ed evidenziare in rosso, ma nel file Cartel1, i codici mancanti.
Grazie ancora!
Ema.
EDIT: Il punto 1 l'ho risolto io, ho qualche difficoltà a realizzare il punto 2...
Re: Confronto/Estrapolazione dati Excel
Citazione:
Originariamente Scritto da
stealth_ema
Ciao,
Il programma funziona perfettamente; semplicemente impiega circa 3 minuti di orologio per elaborare (gira su una vecchia macchina con XP...).
L'esempio che ti ho postato faceva un lookup semplice con un ciclo "ignorante", ma si può ottimizzare un po' utilizzando il metodo 'Find' integrato; cambia la funzione interna così, dovrebbe essere più efficente se il file contiene molte righe:
(i nomi variabili sono rimasti quelli del mio esempio originale, modifica con i tuoi, se devi)
Codice:
Function Lookup_Codice(codFornitore As String) As String 'o Double/Currency o quello che e', dipende dal tipo di cella...
'Le colonne che contengono i due dati di riferimento (codice/importo) del TUO foglio
Const COLONNA_CODICE_AZIENDA As String = "A"
Const COLONNA_IMPORTO_AZIENDA As String = "B"
Dim sheetAzienda As Worksheet
Dim rangeRicerca As Range
Dim rangeRisultato As Range
Set sheetAzienda = Workbooks(1).Worksheets(1) ' o l'indice corretto del foglio che ti interessa, se non e' il primo
Set rangeRicerca = sheetAzienda.Columns(COLONNA_CODICE_AZIENDA & ":" & COLONNA_CODICE_AZIENDA)
Set rangeRisultato = rangeRicerca.Find(What:=codFornitore, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If rangeRisultato Is Nothing Then
'codice non trovato
Lookup_Codice = ""
Else
'codice trovato, prendiamo il valore nell'altra colonna, stessa riga
Lookup_Codice = sheetAzienda.Range(COLONNA_IMPORTO_AZIENDA & rangeRisultato.row)
End If
End Function
Citazione:
Originariamente Scritto da
stealth_ema
Grazie ancora per l'aiuto, ma mi chiedono se si possono aggiungere due modifiche "minori".
1) Tutta la procedura dovrebbe avere come condizione iniziale che nella colonna "descrizione" il prodotto sia indicato come "personal computer", in caso contrario saltare tutte le operazioni e segnalare il codice prodotto in rosso.
2) Fare anche una ricerca "inversa", ovvero: ora controlla che il codice prodotto del file Cartel1 sia contenuto nel file "Agg_prezzi", mentre vorremmo fare il contrario e evidenziare in rosso, nel file Cartel1 i codici mancanti.
Beh, l'impostazione generale è sempre quella, hai già un canovaccio per tutto quello che serve:
Il punto 1) è banale: nel ciclo principale, prima di chiamare 'Lookup_Codice', aggiungi una semplice IF su un .Range(<colonna che vuoi><riga 1>).Value per vedere che contenga il testo desiderato.
Il punto 2) fai semplicemente una nuova macro speculare a questa esistente che inverta i riferimenti di conseguenza
Re: Confronto/Estrapolazione dati Excel
Citazione:
Originariamente Scritto da
LarsenB
Il punto 2) fai semplicemente una nuova macro speculare a questa esistente che inverta i riferimenti di conseguenza
Non c'è modo di fare tutto in una sola macro? Vorrei semplificare al massimo gli interventi "umani"...
Re: Confronto/Estrapolazione dati Excel
Citazione:
Originariamente Scritto da
stealth_ema
Non c'è modo di fare tutto in una sola macro? Vorrei semplificare al massimo gli interventi "umani"...
Riformulo la frase:
"fai semplicemente una nuova funzione speculare a questa esistente che inverta i riferimenti di conseguenza"
A questo punto puoi fare una macro principale (che sempre una funzione è, ma tanto per capirci) che, in due semplici righe, chiamerà prima la versione A e poi quella B...
Codice:
Sub AggiornaTutto()
Aggiorna_VersioneA()
Aggiorna_VersioneB()
End Sub
l'utente farà riferimento solo a quest'unico 'pulsante/menù/quello che sarà', dove metterai il tuo link.
Chiaro che volendo puoi fare anche tutto in una sola funzione integrando prima un ciclo poi l'altro nel medesimo blocco di codice; personalmente però, in particolare finchè non sono sicuro che torna tutto, preferisco mantenere il codice isolato, così una volta che ho fatto debug e confermato che la versione A funziona, posso spippolare sulla versione B senza rischiare di incasinare il codice precedente.
Inoltre è più semplice riutilizzare il tutto la volta che, per qualche motivo, devi eseguire solo uno dei due pezzi.
Re: Confronto/Estrapolazione dati Excel
Rieccomi...
Sto scrivendo la "seconda parte" della macro. Le istruzioni girano, ma mi da sempre il campo SKU (il codice del prodotto presente nel nostro database) evidenziato in rosso, come se fosse sempre assente nel database del fornitore.
Eseguendo le istruzioni passo-passo (e visualizzando i valori delle variabili) vedo che il campo "CodProduttore" (con cui faccio il confronto con SKU) rimane sempre vuoto, ma non capisco perchè...
Il codice per ora è questo:
Option Explicit
Sub AggiornaB()
Const COLONNA_SKU As String = "F"
Dim FORdata As Workbook
Dim Foglio2 As Worksheet
Dim NSdata As Workbook
Dim Foglio1 As Worksheet
Dim RangeSKU As Range
Dim SKU As String
Dim Test As Boolean
Dim row As Long
Set FORdata = Workbooks.Open("C:\XXX\Agg_prezzi_17_09_2013_2.xls ")
Set Foglio2 = FORdata.Worksheets(1)
Set NSdata = Workbooks.Open("C:\XXX\Cartel1_PC.xls")
Set Foglio1 = NSdata.Worksheets(1)
row = 2
Do
Set RangeSKU = Foglio1.Range(COLONNA_SKU & row)
SKU = Foglio1.Range(COLONNA_SKU & row).Value
Test = Lookup_SKU(SKU)
If Test = False Then
'SKU non trovato
RangeSKU.Interior.Color = RGB(255, 0, 0)
End If
row = row + 1
Loop Until Len(SKU) = 0
Set Foglio1 = Nothing
NSdata.Close
Set NSdata = Nothing
End Sub
Function Lookup_SKU(SKU As String) As Boolean
Const Colonna_Codice_Produttore As String = "F"
Dim CodProduttore As String
Dim row As Long
row = 2
Do
CodProduttore = Foglio2.Range(Colonna_Codice_Produttore & row).Value
If StrComp(CodProduttore, SKU, vbTextCompare) = 0 Then
Lookup_SKU = True
Exit Do
End If
row = row + 1
Loop Until Len(CodProduttore) = 0
End Function
Qualche idea?
Ema.
Edit: Risolto. Per quanto riguarda il volerle inserire in una macro unica: Ho fatto una terza macro che le richiama entrambe, ma mi dice che l'oggetto non è definito...
Edit 2: Come non detto... Se lancio la macro da un file diverso non gira, o meglio non produce risultati nei file. In particolare, la riga
"CodProduttore = Foglio2.Range(Colonna_Codice_Produttore & row).Value"
restituisce sempre un valore vuoto, come se perdesse il riferimento al foglio e alla cella dal quale pescare il dato...
Edit 3: Capito l'arcano, avevo "saltato" un'istruzione... La domanda, a questo punto, è: all'interno della funzione LOOKUP c'è modo di modificare questa riga
CodProduttore = Foglio2.Range(Colonna_Codice_Produttore & row).Value
in modo da puntare un file in particolare, e non quello attivo?
Grazie.
Re: Confronto/Estrapolazione dati Excel
Ciao, volevo ringraziare pubblicamente LarsenB perchè grazie a lui sono riuscito nell'impresa (titanica per le mie conoscenze ultra-scarse) di far girare questa macro. Grazie ancora per l'aiuto disinteressato!
Un saluto,
Ema.
Re: Confronto/Estrapolazione dati Excel
Citazione:
Originariamente Scritto da
stealth_ema
Ciao, volevo ringraziare pubblicamente LarsenB perchè grazie a lui sono riuscito nell'impresa (titanica per le mie conoscenze ultra-scarse) di far girare questa macro. Grazie ancora per l'aiuto disinteressato!
Un saluto,
Ema.
Prego... non ho potuto guardare l'ultima questione, ero un po' preso con i miei lavori, ma vedo che te la sei cavata lo stesso.
