Condividi:        

cerca.vert altrimenti dammi un altro valore

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 altrimenti dammi un altro valore

Postdi paloma » 15/09/09 22:35

uffa che 2 maroni, ogni volta provo ad utilizzare cerca vert impazzisco e devo ricorrere al vs aiuto!

Problema:
Ho una tabella con un elenco di nomi (nella prima colonna) e un valore ("S" o "N") associato a ciascuno nella seconda colonna.
In un altro foglio di lavoro ho un altro elenco di nomi che potrebbe anche differire dal primo. Nel caso in cui in tale elenco vi siano dei nomi presenti nella prima tabella vorrei vedere a fianco (e quindi nella seconda colonna) il valore ("S" o "N") associato. Altrimenti vorrei vedere quanto presente in A1 di un altro foglio di lavoro. Nel caso in cui anche in A1 non vi fosse nessun valore vorrei riportare allora quanto presente in B2.

Sper esser stato chiaro!...c'è qlc che ha qualche idea??
:-? :-?
grazie mille!
paloma
Utente Junior
 
Post: 64
Iscritto il: 29/10/07 09:38

Sponsor
 

Re: cerca.vert altrimenti dammi un altro valore

Postdi Anthony47 » 16/09/09 01:17

Si fa con un paio di SE e un Val.Errore; se non ti basta lo spunto e servono delle formule dacci nome dei fogli e coordinate dei tuoi dati.

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

Re: cerca.vert altrimenti dammi un altro valore

Postdi paloma » 18/09/09 09:07

Grande Anthony!
dopo un paio di giorni sono finalmente riuscito a realizzare un cerca.vert con anche val.errore solo che penso di essere arrivato ad un punto morto! nel senso che per fare quello di cui ho bisogno penso dovrei costruire una formula con decine di se!
Ne approfitterei quindi della tua incredibile disponibilità allegandoti il file allo stato in cui sono arrivato con le indicazioni dell'ultimo obiettivo da raggiungere che mi sembra insormontabile sulla base delle mie conoscenze.
Grazie mille per quanto potrai fare!!!
:) :)
oh ops, ma come si fa ad allegare un file xls di 124kb??!!
paloma
Utente Junior
 
Post: 64
Iscritto il: 29/10/07 09:38

Re: cerca.vert altrimenti dammi un altro valore

Postdi paloma » 18/09/09 09:27

paloma
Utente Junior
 
Post: 64
Iscritto il: 29/10/07 09:38

Re: cerca.vert altrimenti dammi un altro valore

Postdi paloma » 18/09/09 15:56

ps: se può essere utile potrei facilmente fare in modo che anche le transazioni da 8 caratteri (es:ABA02FCS) abbiano come suffisso gli altri due caratteri che caratterizzano la famiglia.
In questo modo l'elenco potrebbe facilmente diventare così fatto:
ZF
ZF04
ZF04AB
ZF04ABA02FCS
ZF04ABA26FCS
...
:undecided: :eeh:
paloma
Utente Junior
 
Post: 64
Iscritto il: 29/10/07 09:38

Re: cerca.vert altrimenti dammi un altro valore

Postdi Anthony47 » 20/09/09 18:20

Da come l' ho capita, puoi semplificarti la vita usando tre colonne di appoggio (io ho usato F, G e H), in cui su riga 2 metti rispettivamente queste formule:
Codice: Seleziona tutto
=SE(LUNGHEZZA(A2)=2;D2;F1)
=SE(LUNGHEZZA(A2)=4;SE(D2="";F2;D2);G1)
=SE(LUNGHEZZA(A2)=6;SE(D2="";G2;D2);H1)
Poi copi F2:H2 verso il basso.
Quindi in D2 userai la formula
Codice: Seleziona tutto
=SE(LUNGHEZZA(B2&C2)>0;B2&C2;SCARTO(F2;0;LUNGHEZZA(A2)/2-2))

Che copierai poi verso il basso.

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

Re: cerca.vert altrimenti dammi un altro valore

Postdi paloma » 21/09/09 11:24

Caro Anthony47 sei un GRANDE!!!

Posso chiederti quanto tempo ci hai messo?

Io penso di aver impiegato 20’ solo per capirla anche perché non avevo mai utilizzato la funzione SCARTO!
A questo punto sarei tentato di provare a chiederti se è possibile introdurre un ulteriore miglioramento:
Relativamente al foglio query1 la sequenza dei record che vanno dalla riga 2 (“ABA26FCS”) alla riga 14 (“AND06FCS”) appartengono tutti ad una classe composta da tre lettere (es.”CAR”) che si trova sempre in una colonna a sinistra dei valori (diciamo che nel file avrebbe dovuto occupare la prima colonna) e che io per semplicità avevo omesso.
La stessa cosa per quanto riguarda la query2 che, nella versione completa, presenta nella cella a sinistra di ciascun valore, la classe di appartenenza.
Quello che vorrei costruire alla fine è una matrice che riporta nella prima colonna il nome della transazione e poi nelle successive colonne il valore “S” o “N” che risulta dalla colonna D che mi hai appena preparato per ciascuna classe.
Alla fine dovrebbe venir fuori una tabella tipo:


Transazione Classe CAR Classe CED Classe CON …
ZF S S N
ZF04 S N N
ZF04AB N S S


Grazie mille ancora!!!
:) :) :)
paloma
Utente Junior
 
Post: 64
Iscritto il: 29/10/07 09:38

Re: cerca.vert altrimenti dammi un altro valore

Postdi Flash30005 » 21/09/09 12:26

Ciao Paloma, ciao Anthony

Stavo seguendo il quesito postato e ho dedicato anche io un po' di tempo alla risoluzione seguendo la strada delle macro, non utilizzando nessuna formula, quindi, colonna B e C vuote e la colonna D compilata dalla macro.

Magari potrà essere utile per altri scopi sempreché risponda all'esigenza del primo quesito.

Codice: Seleziona tutto
Public URF As Integer

Sub Compila1()
URF = Worksheets("Foglio2").Cells(Rows.Count, 1).End(xlUp).Row
UR1 = Worksheets("Query1").Cells(Rows.Count, 1).End(xlUp).Row
UR2 = Worksheets("Query2").Cells(Rows.Count, 1).End(xlUp).Row
For RF = 2 To URF
    Trovata = 0
    StrF = Worksheets("Foglio2").Range("A" & RF).Value
        For R1 = 2 To UR1
            STR1 = Worksheets("Query1").Range("A" & R1).Value
            If StrF = STR1 Then
                Worksheets("Foglio2").Range("D" & RF).Value = Worksheets("Query1").Range("B" & R1).Value
                Trovata = 1
                End If
        Next R1
        If Trovata = 0 Then
            For R2 = 2 To UR2
                STR2 = Worksheets("Query2").Range("A" & R2).Value
                If StrF = STR2 Then Worksheets("Foglio2").Range("D" & RF).Value = Worksheets("Query2").Range("B" & R2).Value
            Next R2
        End If
Next RF
Call Compila2
End Sub
Sub Compila2()
URF = Worksheets("Foglio2").Cells(Rows.Count, 1).End(xlUp).Row
For RF = URF To 3 Step -1
    If Worksheets("Foglio2").Range("D" & RF).Value = "" Then
        StrF = Worksheets("Foglio2").Range("A" & RF).Value
        LungS = Len(StrF)
        If LungS > 2 Then
            Cicli = Int(Len(StrF) / 2) - 1
            For CR = 1 To Cicli
                For RD = RF - 1 To 2 Step -1
                    If Worksheets("Foglio2").Range("D" & RD).Value <> "" Then
                        StrFD = Worksheets("Foglio2").Range("A" & RD).Value
                        If Len(StrFD) = LungS - CR * 2 Then
                            Worksheets("Foglio2").Range("D" & RF).Value = Worksheets("Foglio2").Range("D" & RD).Value
                            GoTo esci
                        End If
                    End If
                Next RD
            Next CR
        End If
    End If
esci:
Next RF
End Sub


Il secondo quesito non ho capito molto bene, ad es.: come mai ZF abbia S S N (rispettivamente CAR, CED, CON) se su query1 (CAR) ZF non esiste come fa ad assumere S? :roll:
(così anche per ZF04 e ZF04AB)

Ciao
Flash
Win10 + Office 2010 Ita
"Fotografica" al servizio dell'immagine
Avatar utente
Flash30005
Moderatore
 
Post: 8517
Iscritto il: 27/09/07 11:44
Località: Roma +o-

Re: cerca.vert altrimenti dammi un altro valore

Postdi paloma » 21/09/09 14:09

WOW!
Grazie Flash! confesso che poter far girare il tutto semplicemente con un click mi attirerebbe moltissimo!
Premesso che purtroppo per me è arabo quello che hai scritto sono riuscito...penso :oops: ... a copiare il testo e forse a creare una macro...solo che il risultato inizia ad un certo punto a non restituire più valori corretti.
Facendo girare il tutto in colonna D mi sembra che verso la riga 281 inizi a non girare più bene: la transazione KAT10FCS per esempio mi restituisce il valore "S" anche se sua "madre" (la transazione TA28KI) riporta il valore "N". :?:

In merito al secondo quesito chiedo venia: ZF effettivamente (e così anche gli altri record) sono valori della query2!
Diciamo che entrambe le due query hanno, nel file originario, una colonna in più (la prima) che riporta la classe di appartenenza di ciascuna transazione:
Con riferimento alla query 2 la query di partenza è siffatta:
CAR ZF04AB N
CAR ZF04AN N
CAR ZF S
CAR ZF40TH S
CAR ZF52 S
CAR ZF82 N
CAR AU S
CAR TA S
CAR TA28 N
e così via... dopo qualche migliaio di record si inizia con la nuova classe che può trovare gli stessi record e/o anche di diversi:
CED ZF04AN S
CED ZF40TH S
CED ZF08CI N
CED ZF82 N
CED TA S
...
riuscire a mettere tutto in una matrice sarebbe fantastico! :)
paloma
Utente Junior
 
Post: 64
Iscritto il: 29/10/07 09:38

Re: cerca.vert altrimenti dammi un altro valore

Postdi Flash30005 » 21/09/09 16:05

paloma ha scritto:KAT10FCS per esempio mi restituisce il valore "S" anche se sua "madre" (la transazione TA28KI) riporta il valore "N".

Non mi risulta che TA28KI avesse N (era vuoto) quando vengono processate le stringhe di lungheza 8 (prima delle altre)
Tue specifiche:
2) i valori delle celle vuote relativi a record lunghi 8 caratteri (es. D8, D9, D11…) sono uguali al valore del primo record superiore lungo 6 caratteri. Se anche quest'ultimo è assente si fa riferimento al valore del primo record superiore lungo 4 caratteri. Se anche quest'ultimo è assente si fa riferimento infine al valore del primo record superiore lungo 2 caratteri. Analogamente, i valori delle celle vuote relativi a record lunghi 6 caratteri (ad esempio D4) devono essere uguali al valore del primo record superiore lungo 4 caratteri. Idem per le celle vuote relative a record lunghi 4 caratteri che ereditano il valore del primo record superiore lungo 2 caratteri.

Quindi ho pensato di partire dal basso e risalire: è la maniera più ragionevole per trovare il precedente,
ma se così non va bene è sufficiente modificare la macro invertendo il percorso, ma non rispetta più la specifiche perché si andranno a compilare prima le stringe (mancanti) di lunghezza 2 poi quelle di 4, di 6 e infine quelle di 8 caratteri.


Ciao

P.s. il pulsante è la cosa più semplice da fare per avviare il processo ma non vorrei interferire con l'ottima soluzione di Anthony né tantomeno attivare un altro "processo cerebrale": che si "pensasse male". :roll:
Flash
Win10 + Office 2010 Ita
"Fotografica" al servizio dell'immagine
Avatar utente
Flash30005
Moderatore
 
Post: 8517
Iscritto il: 27/09/07 11:44
Località: Roma +o-

Re: cerca.vert altrimenti dammi un altro valore

Postdi paloma » 22/09/09 08:09

TA28KI era vuoto ed avrebbe infatti dovuto ereditare la classe direttamente dalla madre superiore di 4 caratteri (TA28) che aveva una "N" e quindi non la "S"-
Non capisco proprio come mai il risultato sia corretto quasi sempre tranne per quelle transazioni... :!: :?: :eeh:

Un bel bottoncino che possa automatizzare il tutto rimane sempre molto interessante anche per facilitarne l'utilizzo visto che potrei divulgare il tutto ad una decina di persone, la maggior parte delle quali non si sforzerebbe sicuramente a ripetere tutti i passaggi con formule di vario tipo.
Resterebbe inoltre il problema di come fare per creare agevolmente la matrice con tutte le classi incolonnate supposto che l'elenco dei record è costituito da circa 7.900 transazioni diverse e le classi possono essere dalle 20 alle 40!!!

grazie di tutto ancora! :) :)
paloma
Utente Junior
 
Post: 64
Iscritto il: 29/10/07 09:38

Re: cerca.vert altrimenti dammi un altro valore

Postdi Flash30005 » 22/09/09 08:53

E' solo una questione di procedura e per capirla devi commentare la chiamata alla macro compila2 che si trova alla fine della macro compila1
Codice: Seleziona tutto
Sub Compila1()
...
...
Next RF
'Call Compila2   '<<<<<< da commentare con un'apice così
End Sub

Poi ripuuisci la colonna D e avvii la macro compila1
Avrai solo le sigle che si troveranno nel foglio Query1 e Query2
adesso vai alla riga 281 del foglio
cod KAT10FCS Vuoto
riga 280 - TA28KI vuoto
riga 279 - TA28 con N
riga 278 TA con S

ma KAT10FCS non prenderà nessuno di questi valori ma solo il primo lungo 6 che "incontra" retrocedendo e
che trovi alla riga 79 - ZF40TH con S
E' corretta questa procedura oppure bisogna agire diversamente?

Fai sapere
Ciao
Flash
Win10 + Office 2010 Ita
"Fotografica" al servizio dell'immagine
Avatar utente
Flash30005
Moderatore
 
Post: 8517
Iscritto il: 27/09/07 11:44
Località: Roma +o-

Re: cerca.vert altrimenti dammi un altro valore

Postdi paloma » 22/09/09 09:29

Ciao Flash!
incredibilmente riesco a far girare il codice che mi fornisci senza troppi intoppi anche se non capendoci un tubo penso di avere il...ops la fortuna del principiante!

Comunque effettivamente bisogna agire diversamente:

KAT10FCS (record lungo 8 caratteri di riga 281), qualora non valorizzato (come in questo caso), deve prendere il valore del record di 6 caratteri subito precedente (TA28KI riga 280) e non il primo valore di un record di 6 caratteri avvalorato.
Siccome però il record di 6 caratteri precedente (TA28KI riga 280) non è avvalorato bisogna allora assegnarli un valore. Il valore da assegnare sarà quello di sua "madre" ovvero deve prendere il valore del record precedente lungo 4 caratteri (TA28 riga 279) che in questo caso riporta "N".
In questo modo quindi:
TA28KI dovrà avere N
e tutte le transazioni da 8 caratteri sottostanti KAT10FCS, KAT20FCS...dovranno avere N.

Nel caso in cui anche la transazione TA28 non avesse avuto alcun valore corrispondente avrebbe ereditato il valore della transazione superiore lunga 2 caratteri (TA riga 278).

Se poi volessimo esagerare si potrebbe dire che ciascuna transazione da 2 caratteri, qualora non avvalorata, dovrebbe automaticamente prevedere il valore "N".
paloma
Utente Junior
 
Post: 64
Iscritto il: 29/10/07 09:38

Re: cerca.vert altrimenti dammi un altro valore

Postdi Flash30005 » 22/09/09 09:43

Da quanto dici allora prima si devono avvalorare (compilare con il "metodo del precedente") le stringhe con 2 poi con 4, 6 e infine 8
confermi?

Ciao
Flash
Win10 + Office 2010 Ita
"Fotografica" al servizio dell'immagine
Avatar utente
Flash30005
Moderatore
 
Post: 8517
Iscritto il: 27/09/07 11:44
Località: Roma +o-

Re: cerca.vert altrimenti dammi un altro valore

Postdi paloma » 22/09/09 09:51

SI!
sempre dopo aver riportato però i valori delle query
paloma
Utente Junior
 
Post: 64
Iscritto il: 29/10/07 09:38

Re: cerca.vert altrimenti dammi un altro valore

Postdi paloma » 22/09/09 09:57

o meglio:
prima o dopo decidilo tu comunque sempre facendo attenzione che i valori delle query siano presenti!
:)
paloma
Utente Junior
 
Post: 64
Iscritto il: 29/10/07 09:38

Re: cerca.vert altrimenti dammi un altro valore

Postdi Flash30005 » 22/09/09 10:33

Con questa modifica della macro compila2, la procedura dovrebbe essere corretta
sostituisci la macro compila2 con questa (compila1 lasciala com'è ma togli il commento che richiama questa seconda macro)
Codice: Seleziona tutto
Sub Compila2()
URF = Worksheets("Foglio2").Cells(Rows.Count, 1).End(xlUp).Row
For CR1 = 2 To 8 Step 2
    For RF = URF To 3 Step -1
        If Len(Worksheets("Foglio2").Range("A" & RF).Value) = CR1 Then
            If CR1 = 2 And Worksheets("Foglio2").Range("D" & RF).Value = "" Then
                Worksheets("Foglio2").Range("D" & RF).Value = "N"
                GoTo esci
            End If
            If Worksheets("Foglio2").Range("D" & RF).Value = "" Then
                StrF = Worksheets("Foglio2").Range("A" & RF).Value
                LungS = Len(StrF)
                    Cicli = Int(Len(StrF) / 2) - 1
                    For CR = 1 To Cicli
                        For RD = RF - 1 To 2 Step -1
                            If Worksheets("Foglio2").Range("D" & RD).Value <> "" Then
                                StrFD = Worksheets("Foglio2").Range("A" & RD).Value
                                If Len(StrFD) = LungS - CR * 2 Then
                                    Worksheets("Foglio2").Range("D" & RF).Value = Worksheets("Foglio2").Range("D" & RD).Value
                                    GoTo esci
                                End If
                            End If
                        Next RD
                    Next CR
            End If
        End If
esci:
  Next RF
Next CR1
End Sub

Per il pulsante:
Esistono appositi pulsanti nella barra degli strumenti ma personalmente preferisco crearmi la forma:
Vai sulla barra del disegno in basso Forme>Forme>Rettangolo arrotondato (si presta bene) porti il cursore del mouse sul foglio e lo disegni nella grandezza voluta,
tasto destro mouse Formato forme... scegli Colore > Tendina > Effetti di riempimento puoi scegliere due colori e applicare il gradiente (sfumatura tra i due scelti).
Puoi aggiungere un testo alla forma, tasto destro mouse Aggiungi testo... (puoi Inserire "START")
Sistemata l'estetica devi assegnare la macro "Compila1" alla forma
seleziona il rettangolo, tasto destro mouse > assegna macro.. Selezioni "Compila1"
Clicchi fuori dal rettangolo e da questo momento è un pulsante di comando che avvia la macro.

Fai sapere
Ciao
Flash
Win10 + Office 2010 Ita
"Fotografica" al servizio dell'immagine
Avatar utente
Flash30005
Moderatore
 
Post: 8517
Iscritto il: 27/09/07 11:44
Località: Roma +o-

Re: cerca.vert altrimenti dammi un altro valore

Postdi paloma » 22/09/09 11:33

FANTASTICO FUNZIONA!!! :) :) :)

Confesso che provo una buona dose di invidia!

ps: ho fatto un bottoncino che è la fine del mondo sfruttando l'effetto gradiente: una FIGATA!

Perdonami mi sento un po' in colpa non vorrei approfittarne e quindi valuta tu disponibilità di tempo e di voglia...ma secondo te è anche possibile costruire una matrice riportando i dati in colonna di ciascuna classe come descritto in precedenza??
Intanto CONGRATULATIONS! e GRAZIEEEE!!!!
paloma
Utente Junior
 
Post: 64
Iscritto il: 29/10/07 09:38

Re: cerca.vert altrimenti dammi un altro valore

Postdi Flash30005 » 22/09/09 12:31

E' difficile immaginare cosa non possono fare applicativi come Excel (purché ci siano i dati)

All'inizio io stesso usavo le formule standard (semplici) poi vedendo cosa riesce a fare Anthony (senza togliere alcun merito per l'importante apporto degli utenti attraverso il loro contributo) "miscelando" 5 o 6 funzioni in una unica formula sono rimasto sbalordito sia per la grande "inventiva" e bravura di Anthony, anche dalla versatilità di excel con i codici nativi molto più veloci e immediati delle macro ma che, a parere mio, se occupano un numero notevole di celle aumentano il peso al file stesso cosa che non fa una macro che inserisce direttamente il dato.

La mia "infarinatura" proviene dall'utilizzo Access e dal suo VBA realizzavo macro con i quali ottenevo i dati che mi occorrevano per poi trasferirli su excel per la realizzazione di grafici (quelli di access non sono molto estestici).
Quindi, visto che i linguaggi sono quasi identici, quando posso e quando non è necessario usare un Db realizzo le macro direttamente in Excel.

Dopo questo preambolo e venendo al tuo problema mi dovresti chiarire meglio cosa vuoi ottenere, magari inviando un foglio esplicativo.
A tal proposito (non è un rimprovero) scopriamo che c'è una colonna in più sulla sinistra che comporta la modifica della/le macro (per fortuna di poche righe e di facile realizzazione) cosa che avremmo evitato se fossimo venuti a conoscenza fin dall'inizio dell'effetiva struttura del/i fogli.
Questa è una raccomandazione per tutti coloro che pensano di esporre il quesito "minimizzandolo" e "semplificandolo" al punto da non informare come stanno realmente le cose, eviteremo inutili perdite di tempo. e... il tempo che passa non torna più...

Attendo tue specifiche esaustive per far sì che con un click tu ottenga (come nel libro delle favole :lol: ) quello che desideri ;)

Ciao
Flash
Win10 + Office 2010 Ita
"Fotografica" al servizio dell'immagine
Avatar utente
Flash30005
Moderatore
 
Post: 8517
Iscritto il: 27/09/07 11:44
Località: Roma +o-

Re: cerca.vert altrimenti dammi un altro valore

Postdi paloma » 22/09/09 13:51

EVVAIIIIMACHISSSIETEBABBONATALEEEE???? :D :D
Splendido allora più tardi appena ho un attimo provo a sintetizzare con file di partenza e spero non mi manderete a quel paese... :oops:
paloma
Utente Junior
 
Post: 64
Iscritto il: 29/10/07 09:38

Prossimo

Torna a Applicazioni Office Windows


Topic correlati a "cerca.vert altrimenti dammi un altro valore":

BTp Valore
Autore: MarioLombardi
Forum: Forum off-topic
Risposte: 2

Chi c’è in linea

Visitano il forum: Nessuno e 80 ospiti