Condividi:        

CERCA.VERT con riferimenti esterni dinamici

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

CERCA.VERT con riferimenti esterni dinamici

Postdi sceriffopeschiera » 27/12/13 02:05

Buongiorno a tutti,
ho un problema con la funzione CERCA.VERT che proprio non riesco a risolvere. Apparentemente, sembrerebbe un problema semplice, ma non riesco a venirne a capo.
Il problema è questo, il CERCA.VERT dovrebbe “pescare” dati da diversi file Excel, ovvero da una fonte esterna "variabile" .
Più nel dettaglio, ho diversi file strutturati esattamente nella stessa maniera, uno per ogni mese (2013_08, 2013_09, 2013_10, 2013_11, etc…). Il file nel quale andrò ad inserire il CERCA.VERT conterrà anche una cella che indicherà in quale dei diversi file mensili, il CERCA.VERT dovrà pescare i dati.
Il valore da cercare (primo argomento del CERCA.VERT) e la colonna Indice (terzo argomento) non variano.
Il problema sta nel secondo argomento della funzione (argomento Matrice_Tabella). Non riesco a creare un riferimento dinamico che venga identificato come tale dalla funzione CERCA.VERT.
Supponendo quanto segue:
1. i file riferiti a ciascun periodo (2013_08, 2013_09, 2013_10, 2013_11, etc…) si trovano sul desktop in una cartella che possiamo chiamare “PIPPO” per comodità
2. il valore da cercare è in cella A1
3. l’indice (terzo argomento della funzione) è fisso, come dicevo, e supponiamo sia 3.
Nella cella B1 ho il riferimento del file excel dal quale va estratto il dato (2013_08, 2013_09, 2013_10, 2013_11, etc…)

Come dovrebbe essere la formula? CERCA.VERT($A$1;????????;3;falso)

Spero di aver spiegato in maniera esaustiva il problema. Ringrazio anticipatamente per il suppporto
sceriffopeschiera
Newbie
 
Post: 9
Iscritto il: 02/08/13 13:20

Sponsor
 

Re: CERCA.VERT con riferimenti esterni dinamici

Postdi peppo55 » 27/12/13 16:33

Ciao sceriffopeschiera

credo che non si possa fare.
In B1 hai il percorso del file. Esempio: 'C:\pippo\AAA.xlsx'!AAA
quindi nella matrice_tabella sarà : CERCA.VERT(A1;B1;3;falso)
però B1 non viene interpretato come percorso del file.
In una analoga situazione, avevo risolto con un' unica formula con i SE( )
Naturalmente ha dei limiti per quanto riguarda il numero di files da gestire
e dalla versione di Excel che usi.
peppo

Excel 2010
peppo55
Utente Senior
 
Post: 167
Iscritto il: 30/09/12 13:51

Re: CERCA.VERT con riferimenti esterni dinamici

Postdi wallace&gromit » 27/12/13 17:20

quello che puoi fare è creare un foglio di appoggio nel file in cui esegui il cerca.vert.
In cella A1 c'è il riferimento al nome del file, per es. "2013_08.xls"
in A2 inserisci la formula:
Codice: Seleziona tutto
=INDIRETTO(INDIRIZZO(RIF.RIGA();RIF.COLONNA();;;"C:\pippo\["&A1&"]Foglio1"))

copi e incolli nelle altre celle.
Poi su un'altra pagina esegui il cerca.vert

A rigor di logica questa formula dovrebbe funzionare anche inserita direttamente nella formula cerca.vert (ma per qualche arcano motivo che magari qualcuno saprà spiegare mi funziona solo quando il file cui faccio riferimento è anch'esso aperto).
Office2016 + 2019 su win11
Avatar utente
wallace&gromit
Utente Senior
 
Post: 2174
Iscritto il: 16/01/12 14:21

Re: CERCA.VERT con riferimenti esterni dinamici

Postdi wallace&gromit » 27/12/13 17:37

EDIT: Ops, per lo stesso arcano motivo non funziona neanche questa formula, mi sembrava di averla testata correttamente, invece chiudendo il file il riferimento salta!
Office2016 + 2019 su win11
Avatar utente
wallace&gromit
Utente Senior
 
Post: 2174
Iscritto il: 16/01/12 14:21

Re: CERCA.VERT con riferimenti esterni dinamici

Postdi Anthony47 » 27/12/13 18:47

La sintassi del Cerca.Vert per un file diverso dal corrente e' del tipo
Codice: Seleziona tutto
=CERCA.VERT(A1;'D:\Directory\[NomeFile.xls]NomeFoglio'!Matrice;3;0)


Pero' non si puo' usare la funzione INDIRETTO (per calcolare nome file /Foglio / matrice di lavoro) se il file corrispondente non e' aperto (vale anche per w&g); devi quindi ripiegare su una macro.
Usando l' evento WorksheetChange potrai elaborare la formula e inserirla nella cella dove vuoi leggere il risultato; ad esempio:
Codice: Seleziona tutto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$1" And Target.Count = 1 Then   '<< La cella col nome file
Dim myPath As String, myFile As String, myForm As String
    myPath = "D:\PROVA\"   '<< Il percorso dei file, con lo "\" finale
    myFile = Range("B1").Value  '<< La cella che contiene il nome file
    myForm = "=CERCA.VERT(A1;'" & myPath & "[" & myFile & "]Giocatori'!$A$1:$C$1000;2;0)"   '<< NomeFoglio e Intervallo
    Range("E1").FormulaLocal = myForm
End If
End Sub
Tasto dx sul tab col nome del foglio su cui lavori, scegli Visualizza codice: ti si aprira' l' editor delle macro. Copia il codice e incollalo el frame di dx; personalizza le istruzioni marcate << rispettando rigorosamente la sintassi indicata.

Poi torna sul foglio excel, prova a modificare la cella col nome file e controlla che nella cella desiderata sia presente la formula e il risultato desiderato.

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

Re: CERCA.VERT con riferimenti esterni dinamici

Postdi sceriffopeschiera » 29/12/13 03:03

Grazie davvero a tutti per le vostre indicazioni e suggerimenti. La soluzione di Anthony è perfetta, produce esattamente il risultato che volevo, GRAZIE MILLE!!! :D

A beneficio degli altri utenti che incappino nello stesso mio problema, specifico che il nome del file contenuto nella cella B1 deve comprenderne l'estensione (.xls, .xlsx). Per intenderci, tornando al mio esempio, nella cella B1 dovremo avere 2013_08.xlsx, 2013_09.xlsx, 2013_10.xlsx, 2013_11.xlsx, etc…
sceriffopeschiera
Newbie
 
Post: 9
Iscritto il: 02/08/13 13:20

Re: CERCA.VERT con riferimenti esterni dinamici

Postdi mirko876 » 29/03/16 09:38

Ciao,

Io ho un problema molto simile che si dovrebbe risolvere con una macro:
Ho le colonne B C D dalla riga 3 alla riga 10000 in cui ho dei cerca vert con indirizzi che vorrei rendere dinamici. Nella colonna E ho il nome del file Excel da cui devo andare a leggere l'indirizzo.

La formula della cella B3 dovrebbe diventare la seguente: Cerca.vert(A3;E3&"A1:B100";2;FALSO).
Per C3:Cerca.vert(A3;E3&"A1:C100";3;FALSO).
Dove in E3 ho il file Excel di riferimento il foglio.

Qualcuno mi saprebbe indicare cosa devo inserire nella macro?

Grazie in anticipo
mirko876
Newbie
 
Post: 1
Iscritto il: 29/03/16 09:29

Re: CERCA.VERT con riferimenti esterni dinamici

Postdi Anthony47 » 29/03/16 22:42

Non ho capito se ogni riga ha in colonna E il file (+ il foglio) a cui fare riferimento o se il file (+ il foglio) e' sempre lo stesso (e allora dove sitrova?).
Ciao
Avatar utente
Anthony47
Moderatore
 
Post: 19196
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: CERCA.VERT con riferimenti esterni dinamici

Postdi kikkigellu » 26/05/17 22:24

Ciao a tutti,

avrei bisogno di qualcosa di analogo a quanto scritto sopra. Il mio caso è leggermente diverso:

la formula che vorrei ottenere è la seguente:

=CERCA.VERT(A7;'PERCORSO DEL FILE\[NOME DEL FILE.xlsx]FOGLIO DEL FILE'!$A$1:$T$15000;MATR.SOMMA.PRODOTTO(('PERCORSO DEL FILE\[NOME DEL FILE.xlsx]FOGLIO DEL FILE'!$1:$1="riferimento della colonna in valori")*RIF.COLONNA('PERCORSO DEL FILE\[NOME DEL FILE.xlsx]FOGLIO DEL FILE'!$1:$1));FALSO)

il nome del FOGLIO DEL FILE sarebbe il mese precedente ad oggi, e quindi ottenuto con la formula =SE(MESE(OGGI())=1;"DICEMBRE";SCEGLI(MESE(OGGI())-1;"GENNAIO";"FEBBRAIO";"MARZO";"APRILE";"MAGGIO";"GIUGNO";"LUGLIO";"AGOSTO";"SETTEMBRE";"OTTOBRE";"NOVEMBRE";"DICEMBRE"))

avevo provato con un semplice CONCATENA ma senza risultato, poi sono riuscito modificando la formula con INDIRETTO ma avevo riscontrato la stessa anomalia di collegamento a file esterni.

ho così provato a modificare quanto già scritto in questo modo:

Codice: Seleziona tutto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "non ho capito cosa devo selezionare" And Target.Count = 1 Then   '<< La cella col nome file
Dim myPath As String, myFile As String, mySheet As String, myIndex As String, myForm As String
    myPath = "PERCORSO DEL FILE"   '<< Il percorso dei file, con lo "\" finale
    myFile = "NOME DEL FILE.xlsx"  '<< il nome file
    mySheet = Range("$O$1").Value 'la cella che contiene la variabile del foglio - in O1 c'è la formula che restituisce mese precedente
    myIndex = "MATR.SOMMA.PRODOTTO(('" & myPath & "[" & myFile & "]" & mySheet & "'!$1:$1= ""riferimento"")*RIF.COLONNA('" & myPath & "[" & myFile & "]" & mySheet & "'!$1:$1))"
    myForm = "=CERCA.VERT(A7;'" & myPath & "[" & myFile & "]" & mySheet & "'!$A$1:$T$15000;" & myIndex & ";0)"   '<< NomeFoglio e Intervallo 'aggiungere myIndex   'in A7 c'è il primo nome del cerca vert
    Range("F7").Value = myForm     'in F7 è la prima cella dove dovrei utilizzare la formula
End If
End Sub


Sicuramente mi sto perdendo qualcosa (non sono molto pratico).. riuscite ad aiutarmi?

grazie mille
kikkigellu
Newbie
 
Post: 2
Iscritto il: 21/09/06 22:36

Re: CERCA.VERT con riferimenti esterni dinamici

Postdi Anthony47 » 28/05/17 23:05

Per ottenere il mese precedente puoi usare la formula
Codice: Seleziona tutto
=TESTO(DATA.MESE(OGGI();-1);"mmmm")

Per il resto la cosa piu' semplice e' che tieni aperto il secondo file cosi' non hai problemi a usare indiretto. C'e' un motivo che ostacola questa soluzione?

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

Re: CERCA.VERT con riferimenti esterni dinamici

Postdi kikkigellu » 29/05/17 09:34

Sono file condivisi, non sarà possibile tenerli aperti (e altrimenti non avrebbe senso la richiesta)
kikkigellu
Newbie
 
Post: 2
Iscritto il: 21/09/06 22:36

Re: CERCA.VERT con riferimenti esterni dinamici

Postdi Anthony47 » 29/05/17 17:00

Non posso ricreare il tuo ambiente di prova; quindi dovresti allegare esemplari dei file di prova (non ho capito se sono 2 o 3), con gia' inserita e funzionante la formula che invece dobbiamo rendere dinamica.
Per le istruzioni su come allegare un file:
viewtopic.php?f=26&t=103893&p=605487#p605487

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


Torna a Applicazioni Office Windows


Topic correlati a "CERCA.VERT con riferimenti esterni dinamici":


Chi c’è in linea

Visitano il forum: Nessuno e 46 ospiti