Valutazione 4.87/ 5 (100.00%) 5838 voti

Condividi:        

Excel - CERCA.VERT "dinamico"

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 - CERCA.VERT "dinamico"

Postdi gaetano73 » 17/04/13 13:57

Ciao a tutti. Uso Excel 2003.

Perché
Codice: Seleziona tutto
=CERCA.VERT($B7;'\\dati5\Febbraio 2013\[Dipendenti.xls]OreOrdinarie'!$D$5:$G$88;2;FALSO)

funziona, mentre
Codice: Seleziona tutto
AN7 ==> '\\dati5\Febbraio 2013\[Dipendenti.xls]OreOrdinarie'!$D$5:$G$88
=CERCA.VERT($B7;AN7;2;FALSO)

non funziona?

Grazie


Gaetano
gaetano73
Utente Senior
 
Post: 247
Iscritto il: 03/09/02 18:36

Sponsor
 

Re: Excel - CERCA.VERT "dinamico"

Postdi ricky53 » 17/04/13 14:15

Ciao,
tu fai riferimento al contenuto della cella $AN$7 e non all'intervallo il cui valore è contenuto nella AN7 ... chiaro NO !!!

Correggi in questo modo
Codice: Seleziona tutto
=CERCA.VERT($B7; INDIRETTO($AN$7); 2; FALSO)
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. W7; Office 2003-10-13-16
Avatar utente
ricky53
Utente Senior
 
Post: 4223
Iscritto il: 11/04/09 19:29
Località: Italia

Re: Excel - CERCA.VERT "dinamico"

Postdi Anthony47 » 17/04/13 14:40

Bisogna tener anche presente che usando Indiretto la cartella di lavoro indirizzata deve risultare aperta, "In caso contrario verrà restituito il valore di errore #RIF!"

Ciao
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 13904
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: Excel - CERCA.VERT "dinamico"

Postdi gaetano73 » 17/04/13 14:47

@ ricky53:
... chiaro NO !!!

Chiaro.

@ Anthony47:
Infatti, perché restituisce #RIF

Non c'è un metodo alternativo?
Quello suggerito da ricky mi costringe ad aprire decine di cartelle... :cry:
gaetano73
Utente Senior
 
Post: 247
Iscritto il: 03/09/02 18:36

Re: Excel - CERCA.VERT "dinamico"

Postdi ricky53 » 17/04/13 14:54

Ciao,
solo per precisione:
io ho fatto in modo che la tua formula funzionasse, NON sono entrato nel merito.

Inoltre tu non avevi detto che avevi tante formule con tanti riferimenti a cartelle diverse.
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. W7; Office 2003-10-13-16
Avatar utente
ricky53
Utente Senior
 
Post: 4223
Iscritto il: 11/04/09 19:29
Località: Italia

Re: Excel - CERCA.VERT "dinamico"

Postdi gaetano73 » 17/04/13 15:42

io ho fatto in modo che la tua formula funzionasse

Infatti funziona benissimo e per questo ti ringrazio, è ovvio ;)

NON sono entrato nel merito

È stata colpa mia anzi, della mia non prolissità. Pardon...

Inoltre tu non avevi detto che avevi tante formule con tanti riferimenti a cartelle diverse.

È sempre colpa della non prolissità :)
gaetano73
Utente Senior
 
Post: 247
Iscritto il: 03/09/02 18:36

Re: Excel - CERCA.VERT "dinamico"

Postdi ricky53 » 17/04/13 16:13

Ciao,
ma in pratica quante formule hai e a quanti file fanno riferimento?
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. W7; Office 2003-10-13-16
Avatar utente
ricky53
Utente Senior
 
Post: 4223
Iscritto il: 11/04/09 19:29
Località: Italia

Re: Excel - CERCA.VERT "dinamico"

Postdi gaetano73 » 17/04/13 17:20

Ho tanti CERCA.VERT quanti sono i giorni del mese quindi al più 31.

In effetti ho una situazione del genere:
=CERCA.VERT($B7;'\\dati5\Febbraio 2013\[Dipendenti_01_02_2013.xls]OreOrdinarie'!$D$5:$G$88;2;FALSO)
=CERCA.VERT($B7;'\\dati5\Febbraio 2013\[Dipendenti_02_02_2013.xls]OreOrdinarie'!$D$5:$G$88;2;FALSO)
=CERCA.VERT($B7;'\\dati5\Febbraio 2013\[Dipendenti_03_02_2013.xls]OreOrdinarie'!$D$5:$G$88;2;FALSO)
e cosi via.

In pratica tra una formula e l'altra cambia solo il nome del file sul quale c'è la tabella sulla quale fare il CERCA.VERT().
Avevo pensato di scomporre '\\dati5\Febbraio 2013\[Dipendenti_03_02_2013.xls]OreOrdinarie'!$D$5:$G$88;2 in tre pezzi:
- in una cella "'\\dati5\Febbraio 2013\[Dipendenti_" (che resta costante);
- in una colonna i valori 01, 02, 03... fino a 31;
- in una cella "_02_2013.xls]OreOrdinarie'!$D$5:$G$88";
Poi con un CONCATENA ottenere la stringa intera. Il risultato del concatena viene inserito nel CERCA.VERT
gaetano73
Utente Senior
 
Post: 247
Iscritto il: 03/09/02 18:36

Re: Excel - CERCA.VERT "dinamico"

Postdi ricky53 » 17/04/13 19:31

Ciao,
il concatena ti consentirebbe di gestire il cambio del mese e del giorno.
Ti consiglio di spezzare anche il mese e metterlo in un'altra colonna (anzi due: una per il mese in lettere ed un'altra per il mese in cifre) e poi concatenare il tutto con "&".
Mi riferisco a questi dati
- in una cella "'\\dati5\Febbraio 2013\[Dipendenti_" (che resta costante);

In una cella metti "\\dati5\" in un'altra "Febbraio", "Marzo", ecc., in un'altra "2013", "2014", ecc. e nell'ultima "\Dipendenti"

- in una cella "_02_2013.xls]OreOrdinarie'!$D$5:$G$88";

in una cella metti "_", in un'altra "02", "03', ecc ma in formato stringa e via discorrendo.

Però poi dovrai utilizzare comunque "INDIRETTO" (con i vincoli che comporta) a meno che qualche altro utente non ti suggerisca un'altra soluzione ... attendiamo
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. W7; Office 2003-10-13-16
Avatar utente
ricky53
Utente Senior
 
Post: 4223
Iscritto il: 11/04/09 19:29
Località: Italia

Re: Excel - CERCA.VERT "dinamico"

Postdi gaetano73 » 17/04/13 21:28

OK...
gaetano73
Utente Senior
 
Post: 247
Iscritto il: 03/09/02 18:36

Re: Excel - CERCA.VERT "dinamico"

Postdi Anthony47 » 17/04/13 22:43

Oggi non sono in grado di approfondire; indipendentemente dalla formula io giocherei poi con WorksheetChange per aprire quel file, perche' senza file aperto non si conclude niente.

Ciao
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 13904
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: Excel - CERCA.VERT "dinamico"

Postdi gaetano73 » 19/04/13 14:48

E se provassi a popolare le celle mediante una macro?

Ad esempio inserendo in un ciclo For l'istruzione
Codice: Seleziona tutto
Sheets("Foglio1").Cells(indice_riga, indice_colonna).Value = CERCA_VERTicale_dinamico

Potrebbe funzionare?
gaetano73
Utente Senior
 
Post: 247
Iscritto il: 03/09/02 18:36

Re: Excel - CERCA.VERT "dinamico"

Postdi Anthony47 » 20/04/13 17:17

Mi limito a riprendere l' idea di usare l' evento WorksheetChange per aprire i file di cui in un' area nota si segnano directory e nome.
Partiamo ad esempio da una situazione come da figura:
Immagine

Uploaded with ImageShack.us
L' area in giallo (variabile in quanto a posizione, altezza e larghezza) descrive i file che saranno poi usati nelle formule con Indiretto; le due colonne adiacenti (G e H, nell' esempio) saranno usate dalla macro per indicare il "nome pieno" (directory e nome file) di ogni riga di dati e lo stato di questo file.
Si noti che in queste celle deve essere riportato quanto serve per ricreare il nome pieno del file (es C:\Users\Anthony\Documents\cartel1234.xls), mentre la stringa che sara' usata con Indiretto sara' costruita separatamente (es C:\Users\Anthony\Documents\[cartel1234.xls]NomeFoglio!$A$1:$Z$100) sara' costruita altrove usando come sorgente le stesse celle.
Le celle devono contenere tutti i caratteri necessari a ricreare il nome completo, compreso i "\".

La seguente macro va posizionata nel modulo di codice del foglio in cui e' presente la tabelle:
Codice: Seleziona tutto
Private Sub Worksheet_Change(ByVal Target As Range)
Dim IndirArea As String, NomeF As String, myArea As Range, FileCol As Long
Dim I As Long, J As Long, myWBN As String, OkWB, StayOpen As Boolean, TryOp
'
IndirArea = "$C$1:$F$3"     '<< L' area dove e' composto Dir + Nome file
FileCol = 3                 '<< la colonna di IndirArea ove si trova Nome file
OkWB = Array("Personal.xls", "Personal.xlsm", "cartel4") '<<< Elenco file che possono rimanere aperti
'
If Application.Intersect(Target, Range(IndirArea)) Is Nothing Or Target.Count <> 1 Then Exit Sub
For J = 1 To Range(IndirArea).Rows.Count
NomeF = ""
    For I = 1 To FileCol
        NomeF = NomeF & Range(IndirArea).Cells(J, I).Value
    Next I
    Range(IndirArea).Cells(J, 2 + Range(IndirArea).Columns.Count).Value = NomeF
ReReady:
    If CheckIfOpen(Range(IndirArea).Cells(J, FileCol).Value) Then
        Range(IndirArea).Cells(J, 1 + Range(IndirArea).Columns.Count).Value = "Pronto"
    Else
        TryOp = myWbOpen(Range(IndirArea).Cells(J, 2 + Range(IndirArea).Columns.Count).Value)
        If TryOp Then
            Range(IndirArea).Cells(J, 1 + Range(IndirArea).Columns.Count).Value = "Pronto"
        Else
            Range(IndirArea).Cells(J, 1 + Range(IndirArea).Columns.Count).Value = "Non pronto"
        End If
    End If
Next J
'Controlla quale dei file aperti deve rimanere aperto
For I = Workbooks.Count To 1 Step -1
    myWBN = Workbooks(I).Name
    StayOpen = False
    If myWBN <> ThisWorkbook.Name And IsError(Application.Match(myWBN, OkWB, 0)) Then
        For J = 1 To Range(IndirArea).Rows.Count
            If Len(Range(IndirArea).Cells(J, 2 + Range(IndirArea).Columns.Count).Value) <> _
                Len(Replace(UCase(Range(IndirArea).Cells(J, 2 + Range(IndirArea).Columns.Count).Value), UCase(myWBN), "")) Then
                    StayOpen = True
            End If
        Next J
        If StayOpen = False Then Workbooks(I).Close 'savechanges:=True
    End If
Next I
End Sub
Il seguente codice andrebbe invece posizionato in un Modulo standard:
Codice: Seleziona tutto
Function CheckIfOpen(ByVal myWb As String) As Boolean
Dim PipWb
On Error Resume Next
Set PipWb = Workbooks(myWb)
If PipWb Is Nothing Then
    CheckIfOpen = False
Else
    CheckIfOpen = True
End If
On Error GoTo 0
End Function

Function myWbOpen(ByVal myWbFull As String) As Boolean
Dim mySplit
On Error Resume Next
Workbooks.Open myWbFull, 1, True
On Error GoTo 0
ThisWorkbook.Activate
mySplit = Split(" " & myWbFull, "\")
If CheckIfOpen(mySplit(UBound(mySplit))) Then myWbOpen = True
End Function

Le righe marcate << vanno personalizzate
In particolare
1) la variabile FileCol indichera' quale delle colonne contiene in nome del file da aprire (e' cioe' possibile che alcune colonne dell' area monitorata non siano destinate a contenere elementi del nome pieno del file; ad esempio per contenere il nome del foglio che va usato).
2) OkWB conterra' l' elenco dei file che vanno tenuti aperti, in aggiunta allo stesso file che contiene la macro; tutti gli altri verranno chiusi dalla macro.
L' eventuale salvataggio di dati alla chiusura dei file va gestita dall' utente; tuttavia e' possibile modificare l' istruzione If StayOpen = False Then Workbooks(I).Close per aggiungere l' informazione savechanges:=True oppure savechanges:=False

Uso:
Modificando le celle dell' area definita nella riga IndirArea = .... la macro:
-calcola il nome completo del file, che viene scritto nella colonna H
-il file viene aperto, e se l' operazione ha successo si scrive Pronto nella colonna G
-i file inutili (quelli diversi da quanto definito in OkWB) vengono chiusi (come gia' descritto prima)

Essendo i file aperti sara' possibile usare le formule con Indiretto da cui siamo partiti.

Ciao
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 13904
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: Excel - CERCA.VERT "dinamico"

Postdi gaetano73 » 22/04/13 11:45

È perfetta ma non la posso usare... Il motivo risiede nel fatto che, per policy aziendale, non posso aprire i file a cui puntano.
Avevo provato ad implementare una macro del genere:
Codice: Seleziona tutto
Sub Prova()
Dim contenuto_cella As String
  Sheets("Percorsi").Select
   parte_1 = Range("C2").Value
   parte_2 = Range("C3").Value
   parte_3 = Range("C4").Value
   parte_4 = Range("C5").Value
   parte_5 = Range("C6").Value
   parte_6 = Range("C7").Value
   parte_7 = Range("C8").Value
   parte_8 = Range("C9").Value
   parte_9 = Range("C10").Value
   parte_10 = Range("C11").Value
   parte_11 = Range("C12").Value
   parte_12 = Range("C13").Value
   parte_13 = Range("C14").Value

contenuto_cella = parte_1 & parte_2 & parte_3 & parte_4 & parte_5 & parte_6 & parte_7 & parte_8 & parte_9 & parte_10 & parte_11 & parte_12 & parte_13

Sheets("Destinazione").Select
Range("A10").Select
ActiveCell.FormulaR1C1 = contenuto_cella

End Sub

Sul foglio "Percorsi" ci sono i percorsi suddivisi come suggerito da ricky53. Sul foglio "Destinazione", al momento, provo ad incollare in una cella a caso (A10) il contenuto di "contenuto_cella" per vedere cosa salta fuori.
Detta macro non funziona. Mi restituisce il seguente errore:
"Errore di run-time '1004'
Errore definito dall'applicazione o dall'oggetto"
Dove sbaglio?
Grazie ancora

Gaetano
gaetano73
Utente Senior
 
Post: 247
Iscritto il: 03/09/02 18:36

Re: Excel - CERCA.VERT "dinamico"

Postdi Anthony47 » 22/04/13 23:28

Scusa, se non puoi aprire i file che ti servono come fanno a chiederti di raccogliere dei dati che sono archviati in quei file?

Immagino poi che la macro che hai pubblicato verte su come ricostruire la stringa con l' indirizzo del server a cui puntare; quindi anche se funzionasse, immagino che da qualche parte avrai una formula che usa Indiretto(A10), che come noto ti restituira' #RIF se il file puntato non e' aperto; insomma saremmo sempre allo stesso punto.
Prova a chiedere se sei autorizzato, "senza fare File /Apri", a leggere il contenuto di quei file; se Si allora ti aiuteremo a creare su un tuo foglio una replica di quei dati (che, sia chiaro, mi sembra molto peggio che non aprire il file, estrarne qualche dato e richiuderlo).
Per debuggare il run time error che hai segnalato dovresti indicare quale riga te lo riporta.

Ciao
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 13904
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: Excel - CERCA.VERT "dinamico"

Postdi gaetano73 » 23/04/13 08:57

Scusa, se non puoi aprire i file che ti servono come fanno a chiederti di raccogliere dei dati che sono archviati in quei file?

Bella domanda. Il fatto è che il CERCA.VERT va a raccogliere dati dal foglio "OreOrdinarie"; la cartella però contiene altri fogli in cui sono contenuti dati sensibili dei dipendenti. Inoltre, prossimamente cancelleranno completamente i file dal percorso ed io dovrò consegnare il mio file "alla cieca" ma dovrò essere sicuro che funzioni. Ora tutte le prove "tecniche" le sto facendo su due file di prova.

... immagino che da qualche parte avrai una formula che usa Indiretto(A10)...

No. Ho spezzettato il "=CERCA.VERT($B7;'\\dati5\Febbraio 2013\[Dipendenti_01_02_2013.xls]OreOrdinarie'!$D$5:$G$88;2;FALSO)" in questo modo:
- Parte1: =CERCA.VERT($B7;'\\dati5\
- Parte2: Febbraio 2013\[Dipendenti
- Parte3: _
- Parte4: 01
- Parte5: _
- Parte6: 02
Etc...
Poi ho fatto la macro che ho postato prima.

Per debuggare il run time error che hai segnalato dovresti indicare quale riga te lo riporta.

La riga è
Codice: Seleziona tutto
ActiveCell.FormulaR1C1 = contenuto_cella
gaetano73
Utente Senior
 
Post: 247
Iscritto il: 03/09/02 18:36

Re: Excel - CERCA.VERT "dinamico"

Postdi ricky53 » 23/04/13 14:57

Ciao,
puoi chiedere di farti un file ridotto che non abbia dati riservati?

In questo modo potresti utilizzare i suggerimenti ricevuti (da Anthony in particolare)
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. W7; Office 2003-10-13-16
Avatar utente
ricky53
Utente Senior
 
Post: 4223
Iscritto il: 11/04/09 19:29
Località: Italia

Re: Excel - CERCA.VERT "dinamico"

Postdi gaetano73 » 23/04/13 15:18

puoi chiedere di farti un file ridotto che non abbia dati riservati?

No. Non hanno tempo (=voglia) di aprire i file e tagliare le parti che non posso visualizzare... :(
gaetano73
Utente Senior
 
Post: 247
Iscritto il: 03/09/02 18:36

Re: Excel - CERCA.VERT "dinamico"

Postdi ricky53 » 23/04/13 16:14

Caio,
e quando il file non ci sarà più il file excel non funzionerà!
Come farai?
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. W7; Office 2003-10-13-16
Avatar utente
ricky53
Utente Senior
 
Post: 4223
Iscritto il: 11/04/09 19:29
Località: Italia

Re: Excel - CERCA.VERT "dinamico"

Postdi Anthony47 » 23/04/13 16:17

Vedo che hai calcolato la stringa intera della formula. Bene.
Devi usare FormulaLocal:
Codice: Seleziona tutto
ActiveCell. FormulaLocal = contenuto_cella

Quando poi il file non sara' piu' accessibile ti faremo un generatore casuale di dati verosimili.

Ciao
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 13904
Iscritto il: 21/03/06 16:03
Località: Ivrea

Prossimo

Torna a Applicazioni Office Windows


Topic correlati a "Excel - CERCA.VERT "dinamico"":


Chi c’è in linea

Visitano il forum: patel e 14 ospiti