Condividi:        

[Excel 10] Modificare file e foglio di formula CERCA.VERT

Vuoi potenziare i tuoi documenti Word? Non sai come si fa una macro in Excel? Devi creare una presentazione in PowerPoint?
Oppure sei passato a OpenOffice e non sei sicuro di come lavorare al meglio?

Moderatori: Anthony47, Flash30005

[Excel 10] Modificare file e foglio di formula CERCA.VERT

Postdi Sbarba » 22/07/15 15:10

Buongiorno a tutti,

ho provato a cercare tra le discussioni precedenti ma non ho trovato nulla in merito.

Mi chiedevo se fosse possibile modificare i riferimenti di una formula CERCA.VERT (ma varrebbe per tutte) inserendo tali valori in celle di testo.


Ad esempio, con una formula =CERCA.VERT(B5;'[File1.xlsx]Foglio1'!B2:D45;3;FALSO)

vorrei poter modificare il file (File1.xlsx, File2.xlsx ecc) e i fogli (Foglio1, Foglio2) ecc in cui far compiere la ricerca, inserendo i valori in una cella.
Cioè che al posto di [File1.xlsx] mi prenda il nome che c'è nella cella C5, ad esempio, e al posto di Foglio1! quello che c'è nella cella C6.

È una cosa fattibile? Spero di essermi spiegato decentemente


E vorrei imparare anche a costruire macro che mi permettano, ad esempio, di aprire due file, copiare i valori di uno nell'altro e salvarli entrambi. Dove potrei trovare dei tutorial o delle guide? Così proverei a farlo da solo prima di chiedere qui :)


Grazie in anticipo

Sbarba
Sbarba
Utente Junior
 
Post: 36
Iscritto il: 07/10/14 15:56

Sponsor
 

Re: [Excel 10] Modificare file e foglio di formula CERCA.VER

Postdi ricky53 » 23/07/15 00:20

Ciao,
prova in questo modo:
1. in "B3" scrivi il nome del file
2. in C3" scrivi il nome del foglio
3. utilizza la seguente formula
Codice: Seleziona tutto
=CERCA.VERT(B5; INDIRETTO("'[" & B3 & "]" & C3 & "'!B2:D45"); 3; FALSO)



Prima copia la formula che ti ho inviato (NON riscriverla) e se occorre cambiala in base allea tua situazione effettiva



Per l'altra tua questione: esegui i passi che ti occorrono utilizzando il registratore di macro, che va attivato prima di iniziare a fare i passi
Dice il vecchio saggio provare e riprovare è l'unica strada per imparare

Più chiara è la vostra spiegazione
Più immediata sarà la nostra soluzione


. . . . . . . . . .
S.O. W10; Office 2003-10-13-16-19
Avatar utente
ricky53
Utente Senior
 
Post: 4565
Iscritto il: 11/04/09 19:29
Località: Italia

Re: [Excel 10] Modificare file e foglio di formula CERCA.VER

Postdi Sbarba » 23/07/15 09:12

Ciao Ricky,

grazie mille per la risposta come prima cosa.

Ieri sera avevo poi trovato questa discussione viewtopic.php?t=81145 , che mi è stata d'aiuto.

Ho adattato la formula al mio caso e funziona perfettamente. L'unica cosa che vorrei modificare è la possibilità di inserire non solo il nome del file ma tutto il percorso, prevedendo file anche in altre cartelle.

La formula adattata al mio esempio risulta:
=CERCA.VERT(B43;INDIRETTO("'["&D34&"]"&D38&"'!B10:D45");2;FALSO)

dove in B43 c'è il valore da cercare, in D34 il nome del file (Prova2.xlsx) e in D38 il nome del foglio (Foglio2)

Ho provato a modificare la formula così:
=CERCA.VERT(B43;INDIRETTO(G36&D38&"'!B10:D45");2;FALSO)

oppure =CERCA.VERT(B43;INDIRETTO(" ' "&G36&D38&"'!B10:D45");2;FALSO)

in modo da mettere in G36 il percorso del file (K:\Cartella2\[Prova3.xlsx]) ma non funziona. Come devo modificarla?


E riguardo alla seconda richiesta sulle macro, chiedevo più che altro se vi fossero dei tutorial o delle guide dove poter imparare come fare, dato che non ne so praticamente nulla.

Grazie ancora per il tuo aiuto :)
Sbarba
Utente Junior
 
Post: 36
Iscritto il: 07/10/14 15:56

Re: [Excel 10] Modificare file e foglio di formula CERCA.VER

Postdi Sbarba » 23/07/15 10:10

Mi correggo: la formula funziona anche per i file in cartelle diverse. Avevo scritto male il nome del file :-?

La richiesta su dove poter imparare l'utilizzo delle macro invece rimane :D

Sbarba
Sbarba
Utente Junior
 
Post: 36
Iscritto il: 07/10/14 15:56

Re: [Excel 10] Modificare file e foglio di formula CERCA.VER

Postdi alfrimpa » 23/07/15 10:29

Ciao Sbarba provo a risponderti io.

Premesso che su Internet trovi l'universo per quanto attiene ad Excel ed al VBA ma in questo mare magnum è spesso difficile orientarsi soprattutto per un neofita.

A questo aggiungi che è veramente difficile trovare video tutoria sul VBA in italiano (prova a cercare su YouTube e te ne renderai conto).

Comunque voglio segnalarti due siti che come primo approccio credo possano andare:

http://forum.wintricks.it/showthread.php?t=155252

http://mikevba.altervista.org/cerca/cerca_full.php

Il primo è una sorta di corso sul VBA (non eccessivamente lungo; il secondo è invece una raccolta (piuttosto corposa) di argomenti riguardanti il VBA. Ti consiglio di approcciare inizialmente il primo e poi, quando sarai un po' più pratico, il secondo.

Comunque, al di la di quanto detto prima, ti consiglio di procurarti un buon manuale sul VBA (se ti fai un giro su Amazon o in qualche libreria ben fornita dovresti trovarne una buona scelta).

Ne trovi di tutti i tipi: dal pocket al manualone di molte centinaia di pagine; la scelta dipende da te.

Uno potrebbe essere questo

http://www.amazon.it/Excel-2010-VBA-Mac ... ba+e+macro (ma vedo che è disponibile solo in Kindle)

un altro questo http://www.amazon.it/Microsoft-Excel-20 ... ba+e+macro

oppure ancora http://www.amazon.it/Excel-2013-Power-P ... walkenbach

Quest'ultimo è in inglese ma è molto semplice da capire e da seguire ed è veramente completo.

Io ce li ho tutti e tre e devo dire che il primo ed il terzo sono veramente buoni; il secondo un po' meno (almeno dal mio punto di vista).

Spero di esserti stato utile.
Alfredo

Win7 + Office 2007
Avatar utente
alfrimpa
Utente Senior
 
Post: 1201
Iscritto il: 30/12/13 17:01
Località: Napoli

Re: [Excel 10] Modificare file e foglio di formula CERCA.VER

Postdi Sbarba » 23/07/15 10:35

Grazie mille Alfredo, molto gentile.

L'inglese non è un problema, però dato che è un argomento che conosco poco l'italiano è meglio :)

Mi guarderò allora libri e siti che mi hai consigliato :)

Buona giornata.
Sbarba
Utente Junior
 
Post: 36
Iscritto il: 07/10/14 15:56

Re: [Excel 10] Modificare file e foglio di formula CERCA.VER

Postdi alfrimpa » 23/07/15 10:40

Se per te l'inglese non è un problema allora ti consiglio vivamente quello di John Walkenbach (è definito Mr. Spreadsheet) che è fatto molto bene ed è veramente completo.

"Excel 2013 VBA e macro" non mi è piaciuto molto, ma poi vedi tu.

P.S. Ovviamente frequentare con costanza i forum che si occupano di Excel e del VBA è comunque una cosa da fare con costanza; si apprendono sempre tantissime cose.
Alfredo

Win7 + Office 2007
Avatar utente
alfrimpa
Utente Senior
 
Post: 1201
Iscritto il: 30/12/13 17:01
Località: Napoli

Re: [Excel 10] Modificare file e foglio di formula CERCA.VER

Postdi Sbarba » 23/07/15 11:44

Ottimo, grazie :)

Purtroppo o per fortuna ho un'infinità di cose da studiare e quindi non riesco a star dietro a tutto. Sicuramente l'utilizzo di Excel, comunque, è una delle mie priorità.



Ho un problema con la formula che ho creato: funziona solamente se i file a cui faccio riferimento sono aperti. Se la faccio girare con i file in questione chiusi, mi restituisce l'errore #RIF!. Come posso fare?
Sbarba
Utente Junior
 
Post: 36
Iscritto il: 07/10/14 15:56

Re: [Excel 10] Modificare file e foglio di formula CERCA.VER

Postdi alfrimpa » 23/07/15 13:03

Ciao Sbarba

Il cerca.vert funziona anche se la tabella di ricerca è sul file chiuso.

Ora non so se la funzione indiretto() possa in qualche modo dar fastidio.

Comunque attendiamo un intervento di Ricky53 sicuramente più esperto di me.
Alfredo

Win7 + Office 2007
Avatar utente
alfrimpa
Utente Senior
 
Post: 1201
Iscritto il: 30/12/13 17:01
Località: Napoli

Re: [Excel 10] Modificare file e foglio di formula CERCA.VER

Postdi alfrimpa » 23/07/15 14:29

Ciao Sbarba

Ho fatto una ricerca in rete e credo di poter dire che la funzione INDIRETTO() non sia utilizzabile su file chiusi.

A questo punto credo tu abbia due alternative:

1) con una macro all'apertura del file principale (quello con il CERCA.VERT) si aprano anche i file relativi
2) utilizzare il metodo ExecuteExcel4Macro (che riesce a leggere i dati su file chiusi) ma la strada si fa assai impervia.
Alfredo

Win7 + Office 2007
Avatar utente
alfrimpa
Utente Senior
 
Post: 1201
Iscritto il: 30/12/13 17:01
Località: Napoli

Re: [Excel 10] Modificare file e foglio di formula CERCA.VER

Postdi Sbarba » 23/07/15 14:53

Grazie Alfredo, molto gentile ;)

A meno che qualcuno abbia un'ulteriore alternativa proverò allora a studiarmi quel metodo (preferisco che i file rimangano chiusi in quanto scaricano dati).
Sbarba
Utente Junior
 
Post: 36
Iscritto il: 07/10/14 15:56

Re: [Excel 10] Modificare file e foglio di formula CERCA.VER

Postdi Anthony47 » 24/07/15 00:47

Come detto, non puoi usare INDIRETTO per indirizzare un file chiuso. Pero' puoi usare un file chiuso all'interno di una funzione...

Quindi una possibile soluzione e' creare una macro che quando modifichi i riferimenti di "indiretto" va a scrivere la formula nella cella interessata.
Ad esempio, ho in D1 ed E1 due troconi di informazioni che puntano alla directory, al file, al foglio e all'intervallo; nel mio caso
Codice: Seleziona tutto
D1= ''C:\Users\UTENTE\Downloads\             'NB: comincia con due "apice semplice", non un "virgolette"
E1= '[MULTI_B50624.xlsm]Foglio11'!$M:$P

Compilero' la formula usando questa macro:
Codice: Seleziona tutto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$1" Or Target.Address = "$E$1" Then
    Range("C7").Formula = "=VLOOKUP(C1," & Range("D1") & Range("E1") & ",2,0)"
End If
End Sub

La formula inserita in C7 sara'
Codice: Seleziona tutto
=CERCA.VERT(C1;'C:\Users\UTENTE\Downloads\[MULTI_B50624.xlsm]Foglio11'!$M:$P;2;0)


Adatta la macro al tuo caso.

Ciao
Avatar utente
Anthony47
Moderatore
 
Post: 19196
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: [Excel 10] Modificare file e foglio di formula CERCA.VER

Postdi Sbarba » 24/07/15 09:27

Grazie per la risposta, Anthony.

Non ho però capito il funzionamento di quello che mi hai suggerito.

Non conosco le macro e quindi non capisco cosa faccia il codice che mi hai suggerito, purtroppo.

La formula in C7 mi sembra un classico CERCA.VERT che funziona sempre e con i riferimenti fissi. Come posso fare per far prendere i riferimenti a foglio e file da altre caselle, superando il problema di INDIRETTO?
Sbarba
Utente Junior
 
Post: 36
Iscritto il: 07/10/14 15:56

Re: [Excel 10] Modificare file e foglio di formula CERCA.VER

Postdi Anthony47 » 24/07/15 23:48

Quella macro simula un Indiretto, in quanto modifica la formula secondo quanto indicato in D1 ed E1.
Si tratta di una macro che scatta in occasione di un Worksheet_Change, cioe' di una modifica manuale fatta sul foglio a cui quella macro viene applicata; se la modifica riguarda una delle celle D1 oppure E1 allora la formula viene ricalcolata e inserita nella cella C7 in modo che sia funzionante anche se la formula punta a un file chiuso.
Se il tuo problema e' simile all'esempio fatto allora probabilmente si riesce ad adattare la macro.
Puoi fare delle prove partendo da un file nuovo; per inserire la macro:
-tasto dx sul tab col nome del foglio su cui devi lavorare, scegli Visualzza codice; si apre l'editor delle macro nella posizione "giusta"; copia il codice e incollalo nel frame vuoto di destra.
Poi torna su excel, inserisci un D1 ed E1 quanto serve a puntare al tuo file che rimane chiuso e vedi se riesci a far inserire in C7 (o in altra cella che scegli tu) una formula che restituisca il valore atteso.

In alternativa alla macro, se il numero di file che la formula deve indirizzare e' abbastanza contenuto, potresti immaginare di costruire una formula basata su SE; del tipo
Codice: Seleziona tutto
=SE(E(D1="File1";E1="Foglio1");CERCA.VERT(C1;'C:\Users\UTENTE\Downloads\[File1.xlsx]Foglio1'!$M:$P;2;0);SE(E(D1="File2";E1="Foglio2");CERCA.VERT(C1;'C:\Users\UTENTE\Downloads\[File2.xlsx]Foglio2'!$M:$P;2;0);Etc Etc))


Ciao
Avatar utente
Anthony47
Moderatore
 
Post: 19196
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: [Excel 10] Modificare file e foglio di formula CERCA.VER

Postdi Sbarba » 27/07/15 08:51

Buongiorno Anthony,

alla seconda formula che mi suggerisci ci avevo pensato anche io, però il numero di file/fogli può essere piuttosto elevato e quindi è da scartare.

Impostando su un file nuovo la macro, invece, sembra funzionare esattamente nel modo in cui mi serve.

Faccio altre prove e cerco di adattarla al mio esempio per capire se c'è qualcosa da sistemare

grazie mille :)
Sbarba
Utente Junior
 
Post: 36
Iscritto il: 07/10/14 15:56

Re: [Excel 10] Modificare file e foglio di formula CERCA.VER

Postdi Sbarba » 28/07/15 09:25

Ho fatto alcune prove e sono riuscito ad adattare la situazione al mio caso. Avrei però altre due domande:

-È normale che quando cambi la cartella in cui cercare il file il risultato della formula non si aggiorni in automatico, ma debba dargli l'invio dalla cella contenente il foglio o la cartella?

-Per un'unica formula non ho problemi, ma se volessi costruire una matrice del tipo:


-------------------A------------------B--------C--------E
Riga 16: Oggetto da cercare - Cartella - File - Risultato

come posso fare?

la macro per un'unica cella è

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$16" Or Target.Address = "$C$16" Then
Range("E16").Formula = "=VLOOKUP(A16," & Range("B16") & Range("C16") & ",2,0)"
End If
End Sub

ho provato a modificare il modo in cui vengono bloccate B16 e C16 ma non riesco ad ottenere risultati. Non vorrei, chiaramente, dover copiare il codice per ogni riga che devo cercare.

Grazie nuovamente per l'aiuto :)
Sbarba
Utente Junior
 
Post: 36
Iscritto il: 07/10/14 15:56

Re: [Excel 10] Modificare file e foglio di formula CERCA.VER

Postdi Anthony47 » 30/07/15 01:59

È normale che quando cambi la cartella in cui cercare il file il risultato della formula non si aggiorni in automatico, ma debba dargli l'invio dalla cella contenente il foglio o la cartella?
Se "tu cambi" la cartella in cui cercare il file allora il tutto dovrebbe essere generato automaticamente, quindi "No, non e' normale"
Se invece la cartella cambia nella cella per effetto di una formula allora l'evento non si avviera' e la formula non verra' cambiata automaticamente.
Quale e' il tuo caso?
Per usare lo stesso approccio su N righe preferisco prima avere la risposta alla domanda precedente.

Ciao
Avatar utente
Anthony47
Moderatore
 
Post: 19196
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: [Excel 10] Modificare file e foglio di formula CERCA.VER

Postdi Sbarba » 30/07/15 09:00

Ciao Anthony, grazie per la risposta. In effetti non mi sono spiegato molto bene.

Intendo dire che se prima faccio cercare il file A nella cartella B e poi faccio cercare il file C nella cartella D (incollando. nelle celle collegate alla formula, le stringhe di testo riferite ai nuovi riferimenti, es 'K:\CartellaD\ e [File C]'Foglio...) la formula non si aggiorna automaticamente.

Una volta che clicco (o con F2) nella cella con la formula o in quella con il riferimento alla cartella/file e do invio, la formula si aggiorna correttamente. Se invece seleziono i riferimenti a cartella e foglio con un menù a tendina da convalida dati, non ho di questo problema.

Era più che altro una questione di comodità e, in parte, di curiosità, la formula funziona correttamente. La seconda parte, in realtà, era la domanda vera e propria :)
Sbarba
Utente Junior
 
Post: 36
Iscritto il: 07/10/14 15:56

Re: [Excel 10] Modificare file e foglio di formula CERCA.VER

Postdi Anthony47 » 01/08/15 23:06

La macro che ti ho dato lavora su una cella (anzi una coppia di celle); se tu hai un intervallo di coppie allora vanno cambiate alcune cose su vari fronti...
Modifica la macro in questo modo:
Codice: Seleziona tutto
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRan As String, myCell As Range
'
myRan = "B16:C30"           '<<< L'intervallo in cui saranno scritti i parametri
If Not Application.Intersect(Range(myRan), Target) Is Nothing Then
    For Each myCell In Target
        Cells(myCell.Row, "E").Formula = "=VLOOKUP(A" & myCell.Row & "," & Cells(myCell.Row, "B") & Cells(myCell.Row, "C") & ",2,0)"
    Next myCell
End If
End Sub
La riga marcata <<< va personalizzata come da commento.

Ciao
Avatar utente
Anthony47
Moderatore
 
Post: 19196
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: [Excel 10] Modificare file e foglio di formula CERCA.VER

Postdi Sbarba » 03/08/15 09:29

funziona alla perfezione, grazie mille :)
Sbarba
Utente Junior
 
Post: 36
Iscritto il: 07/10/14 15:56

Prossimo

Torna a Applicazioni Office Windows


Topic correlati a "[Excel 10] Modificare file e foglio di formula CERCA.VERT":


Chi c’è in linea

Visitano il forum: Gianca532011 e 87 ospiti