Excel- Cerca.Verticale... e con riferimenti uguali?

[Flash30005]

Se ho due colonne A Col con i riferimenti (purtroppo uguali) e B il valore che devo ricavarmi es.
A B
14 1
32 2
2 3
75 4
61 5
32 6
8 7
30 8
Ora in C3 metterò =GRANDE($A1:$A30;1), in D3 metterò =GRANDE($A1:$A30;2), in E3 metterò =GRANDE($A1:$A30;3)
ottenendo rispettivamente 75, 32, 32 (i 3 valori di A più grandi) ma ora se volessi saper a quali valori di B appartengono con il Cerca.verticale in C4, D4, E4 ottengo in C4 il valore 75 in D4 2 e in E4 2 (in quanto è il primo valore della matrice) quello che vorrei ottenere invece è 75, 2 e 6 (il secondo 32 della matrice).
C'è da tenere presente che i valori in B corrispondono anche ai numeri riga cioè il 14 si trova nella riga 1, il 32 nella riga 2 ,il 2 riga 3, il 75 sulla riga 4, etc ma con Rif.Riga come posso condizionarlo alla formula di Grande(...)?

[Anthony47]

La mia proposta e' che sostituisci l' attuale colonna B con questa formula: =A1-RIF.RIGA()/10000
Poi userai

=CONFRONTA(GRANDE(B$1:B$30;1);B$1:B$30;0)

per cercare la posizione del primo e dei successivi (previa sostituzione del k=1 nella formula Grande).

Ciao.

[Flash30005]

La mia proposta e' che sostituisci l' attuale colonna B con questa formula: =A1-RIF.RIGA()/10000
Poi userai

=CONFRONTA(GRANDE(B$1:B$30;1);B$1:B$30;0)

per cercare la posizione del primo e dei successivi (previa sostituzione del k=1 nella formula Grande).

Ciao.

In attesa del tuo consiglio avevo risolto utilizzando la colonna C dove ho messo =A1*100+B1 (i numeri in B sono 90 quindi le prime due cifre a partire da destra compongono il numero che mi occorre) e ho utilizzato, poi, questa formula

Codice: Seleziona tutto

=VALORE(DESTRA(GRANDE($C1:$C90;1);2))

Domani proverò la tua formula

Ciao e grazie

[martin]

scusate se leggendo magari non ho capito..
io ho lo stesso problema o similare
DATA codice
14/10/2008 2
15/10/2008 3
15/10/2008 4
17/10/2008 25
18/10/2008 26
19/10/2008 27
19/10/2008 28
19/10/2008 29
21/10/2008 30
23/10/2008 31

vorrei nella ricerca avere tutti i risultati nelle data richiesta ...SE(VAL.NON.DISP(CERCA.VERT(A1;scarico!A2:G500;2;FALSO))=VERO;"no";CERCA.VERT(A1;scarico!A2:G500;2;FALSO)) con questa formula mi trova solo 1 data
se ricopio scivolando la ricerca di un rigo mi da doppioni .
come posso fare? grazie

[Anthony47]

Ciao martin e benvenuto nel forum.
Ma non puoi usare il filtro automatico applicato sulla colonna A?
Puoi anche automatizzarlo, tramite una macro come questa che associ poi a un pulsante:

Codice: Seleziona tutto

Sub Filtra_A1()
'
    Range("A2:A1000").Select
    Selection.AutoFilter Field:=1, Criteria1:=Range("A1").Value
    Range("A1").Select
End Sub

(filtra le righe del range A2:A1000 che in col A hanno un valore pari a quanto indicato in A1).

Probabilmente puoi elaborare i risultati gia' in questo modo, usando formule della famiglia "Subtotale"; se invece ti interessa copiarle in altro range, si puo' aggiungere anche la fase di copia e incolla.
Se non ti basta lo spunto, posta ancora con l' indicazione di quali colonne vuoi copiare e dove.

L' uso del metodo adottato nel mio suggerimento o nella formula di Flash dipende dalle regole rispettate dai dati in col B, se cioe' sono sempre diversi riga per riga o invece ci sono di doppioni.

Ciao.

[martin]

ciao antony grazie per il benvenuto e per avermi risposto.
mi sa comunque che mi sono espresso male nell'esempio cerco di spiegarmi meglio.:

in un foglio DI NOME SCARICO ho dei dati con :

colonna A COLONNA B COLONNA C
DATA
21/05/2008 41 BIANCO
22/05/2008 3 NERO
22/05/2008 4 GIALLO
24/05/2008 5 VIOLA


IN UN ALTRO FOGLIO:


COLONNA A COLONNA B

CERCA DATA : 22/05/2008 (ESEMPIO)




RISULTATO ( FORMULA DEL MIO PRECEDENTE MESSAGGIO...)

MI DA SOLO PRIMA DATA ,E AL RIGO SUCCESSIVO SE SCENDO NELLA RICERCA DI UN RIGO MI RIPASSA SUL PRECEDENTE DANDOMI UN DOPPIOE. MENTRE MI SERVONO TUTTE LE DATE.

MI SERVE INSERIRE UNA FORMULA CHE RIPETUTA IN VARI RIGHI MI DIA TUTTI I RISULTATI DI QUELLA DATA ESCLUDENDO QUELLI GIA SCRITTI AL RIGO PRECEDENTE
O TROVARE UN MODO CHE NELLA SECONDA RICERCA VADA AVANTI DI UN RIGO ALLA PRIMA TROVATA.
NON SO SE SONO CHIARO.
PREFERIREI COMUNQUE DELLE FORMULE DI RICERCA PERCHè LE MACRO NON LI SO PROGRAMMARE.
NOTA : SONO RIUSCITO CON LA FORMULA CONFRONTA A TROVARE IL PRIMO RIGO E L'ULTIMO... BENE E POI CHE FACCIO ?

GRAZIE.

[Anthony47]

Una preghiera: non scrivere frasi in maiuscolo (nel linguaggio dei forum significa prepotenza o peggio).

Poi, avendo tu l' obiettivo di ottenere tutte le righe che contengono una data uguale a quella che scegli, non hai detto perche' il filtro non e' idoneo.

Comunque, se lo vuoi fare con formule allora puoi partire da queste:
Per ottenere la data:

Codice: Seleziona tutto

=SCARTO($A$1;GRANDE((RIF.RIGA($2:$1100)*($A$2:$A$1000=$A$1));RIF.RIGA())-1;0)

Per ottenere la colonna adiacente:

Codice: Seleziona tutto

=SCARTO($A$1;GRANDE((RIF.RIGA($2:$1100)*($A$2:$A$1000=$A$1));RIF.RIGA())-1;1)

La terza colonna:

Codice: Seleziona tutto

=SCARTO($A$1;GRANDE((RIF.RIGA($2:$1100)*($A$2:$A$1000=$A$1));RIF.RIGA())-1;2)

Metterai queste formule obbligatoriamente in riga1 di tre colonne libere; confermerai ogni formula NON con Enter MA con Contr-Maiusc-Enter.
Copierai poi queste tre formule nelle righe successive, per quante righe vuoi predisporti a ricevere; cioe' se pensi di voler cercare fino a 10 date uguali, dovrai copiare le formule da riga-1 fino a riga-10.

Ciao, fai sapere.

[martin]

ciao antony scusa per il maiuscolo

provate formule ma niente . non va

cmunque se mi spieghi come fare anche in altro modo io faccio

data rif colore
28/11/2008 41 nero
28/11/2008 3 bianco
30/11/2008 52 rosso
30/11/2008 21 verde

ricerca?

grazie per avermi risposto

[Anthony47]

provate formule ma niente . non va

Eh no, non te la cavi cosi!
Approfondiamo:
-la prima formula che ti ho passato, in che cella l' hai inserita?
-se selezioni quella cella, che cosa vedi nella barra della formula?
-che risultato ti da?
-in che cella hai messo la data che vuoi estrarre?
-in quali celle sono contenute tutte le tue date?
Vai sull' ultima data nel tuo elenco che e' uguale alla tua data impostata:
-in che cella si trova?
-se formatti la cella come numero con due decimali, che cosa leggi in cella?

Per l' altra proposta, quella del filtro: prova ad applicare il filtro e vedi se quello che ottieni e' buono per quello che cerchi di fare; se e' buono approfondiremo il discorso della macro che ti ho proposto, ma dovresti rispondere alla domanda "Se non ti basta lo spunto, posta ancora con l' indicazione di quali colonne vuoi copiare e dove"
Ma e' comunque, anche per questo metodo, necessario che risponda alle varie domande poste a proposito della soluzione "formule".

Ciao.

[martin]

ciao

allora ti dico ciò che ho fatto:
a b c g h i
28/11/2008 41 nero #N/D #N/D #N/D
28/11/2008 3 bianco #N/D #N/D #N/D
30/11/2008 52 rosso #N/D #N/D #N/D
30/11/2008 21 verde #N/D #N/D #N/D

=SCARTO($A$1;GRANDE((RIF.RIGA($2:$1100)*($A$2:$A$1000=$A$1));RIF.RIGA())-1;0)

quello che leggo se seleziono h1 e via per le righe accanto finali ( -1;1) ... (-1;2)
confermati con ctrl -maiu-invio

la data che voglio estrarre ho provata a metterla dovunque ma a essere sincero non ho capito dove tu l'hai predisposta

questo è il risultato.

non ho capito niente suppongo... dirai aiuta allora questo povero martin........
ciao grazie

[Anthony47]

1) Mi accorgo che le formule che ti ho dato contengono un errore in questa parte: RIF.RIGA($2:$1100)*($A$2:$A$1000=$A$1): trattandosi di una formula "matriciale" le due matrici devono avere la stessa lunghezza, ambedue 2:1100 oppure 2:1000; quindi correggi per favore mettendo su tutte "($2:$1000)" (senza apici) come parametro di Rif.Riga.

2) Detto questo:
-non mi hai confermato che vedi le formule, nella barra delle formule, racchiuse da parentesi graffe; se non ci sono le graffe allora seleziona la cella, fai F2, conferma con Contr-Maiusc-Enter
-la formula lavora con la data da cercare in A1 e le altre informazioni in A2:A1000 e colonne adiacenti; ho dedotto queste informazioni dalle formule del tuo primo post.

Se con la prima correzione e con queste altre informazioni non funziona ancora, allora dovresti rispondere a queste domande:
Vai sull' ultima data nel tuo elenco che e' uguale alla tua data impostata:
-in che cella si trova?
-se formatti la cella come numero con due decimali, che cosa leggi in cella?

Ciao.

[martin]

bingo...................
ok era quello l'errore...

grazie.

però non ho capito perche quando ho riconfermato le formule mi ha dato errore "memoria insufficiente l'applicazzione
verra chiusa". ho annullato e poi tutto ok .(nb. vista? c'entra?)
sei stato gentilissimo se ho altri problemi penso posso ancora contare su di te...

[Anthony47]

Bene!
Sui miracoli del sistema operativo che mangia tutta la memoria disponibile sono un emerito ignorante, quindi non ho commenti.

Prendo lo spunto da un altro thread in corso per dire che se vuoi mettere le formule su una riga diversa dalla 1 e' sufficiente che nella prima riga di formule (quella che poi copierai verso il basso) modifichi questa parte ";RIF.RIGA())-1;" in ";RIF.RIGA(A1))-1;" (ovviamente senza apici).

Ciao.

[martin]

ciao antony
grazie ancora per avermi risposto.

ok registrato anche consiglio per cambiare cella.


volevo chiederti:
nella realtà operativa tornando all'esempio che ti avevo chiesto
l'elenco dei dati si trova in un foglio chiamato scarico.
la cella di ricerca vorrei inserirla in altro foglio chiamato ricerca.
è possibile con le formule che mi hai dato effettuare questa operazione?(
che modifica devo apportare alla formula per leggere l'elenco da altro foglio?

nb: in pratica ci sono riuscito inserendo le formule di ricerca sul foglio scarica e poi dall'altro foglio ricerca dando le cellle uguali ma il giro e doppio e poi mi ritrovo la ricerca nello stesso foglio dello scarico.

grazie martin

[martin]

dimenticavo

qual'è la differenza nel confermare con ctrl-maiu-enter?

[Anthony47]

La cosa piu' semplice e' che dopo che hai fatto funzionare il tutto sullo stesso foglio sposti una a una le celle che ti servono dal foglio Storico al foglio Ricerca: seleziona la cella, Contr-x, attivi nuovo foglio, selezioni cella di destinazione, Enter. Excel si prendera' cura di modificare i riferimenti delle celle.
Per le formule bastera' spostare solo la prima riga, poi le copi verso il basso nel nuovo foglio.

Per il significato di Contr-Maiusc-Enter, guarda l' help on line, tab Ricerca libera, alla voce "formule a matrice".

Ciao.

[martin]

allora...
per conoscenza
fatto spostamenti per i due fogli... funziona solo la prima colonna quelle adiacenti non li trova o meglio li trova ma da 0
ma se lascio a1 nel foglio storico e lo rendo uguale a a1 del foglio di ricerca va ok.
quindi l'ho lasciato cosi.
ho provato anche cambiare rigo come mi avevi detto con rif.riga(a1)-1;.... e va ok pure.
niente a questo punto tranne come ti dicevo sopra tutto ok.

ciao grazie ancora

martin

[Anthony47]

Ho capito che quindi funziona con il trucco di lasciare nella formula il riferimento a Storico!$A$1, salvo che quell' A1 ricopia il valore di Ricerca!"A1.
Per curiosita', se hai voglia di provare ancora, quale formula ti restituiva "0"?

Ciao.

[martin]

FOGLIO 4
A B C
28/11/2008 41 nero
28/11/2008 3 bianco
30/11/2008 52 rosso
30/11/2008 21 verde
01/12/2008 27 nero
01/12/2008 25 bianco
03/12/2008 24 rosso
03/12/2008 23 verde
03/12/2008 22 nero
06/12/2008 21 bianco
07/12/2008 20 rosso
08/12/2008 19 verde

FOGLIO 5
A G H I
30/11/2008(cella cerca data) 30/11/2008 0 0
30/11/2008 0 0
#RIF! #RIF! #RIF!
#RIF! #RIF! #RIF!
#RIF! #RIF! #RIF!
#RIF! #RIF! #RIF!
#RIF! #RIF! #RIF!

come vedi funziona solo colonna G ... h e i danno zero.
g=SCARTO(Foglio4!$A$1;GRANDE((RIF.RIGA(Foglio4!$1:$1)*(Foglio5!$A$2:$A$1000=Foglio4!$A$1));RIF.RIGA(Foglio4!A1))-1;0)
h=SCARTO(Foglio4!$A$1;GRANDE((RIF.RIGA(Foglio4!$1:$1)*(Foglio5!$A$2:$A$1000=Foglio4!$A$1));RIF.RIGA(Foglio4!A1))-1;1)
i=SCARTO(Foglio4!$A$1;GRANDE((RIF.RIGA(Foglio4!$1:$1)*(Foglio5!$A$2:$A$1000=Foglio4!$A$1));RIF.RIGA(Foglio4!A1))-1;2)
(tutto su parentesi graffa) ctrl-maiusc-invio
considera che la prima volta si è modificato da solo il punto (foglio4!$1:$1)
quando ho riprovato per darti l'esempio l'ho dovuto fare io manualmente perche non lo dava e se lascio 1:1000 non funziona ho provato idem per le colonne adiacenti ma negativo.
invece se lascio la cella di ricerca sul foglio 4 in A1 funziona.
allora "gli ho detto che la cella del foglio 4 è uguale alla cella a1 del foglio 5 "dove io scrivo la data che mi serve.
e cosi funziona. spero di essere stato chiaro ,acc mi va si di studiarci ancora grazie
ciao martin

[martin]

dimenticavo con la soluzione ottimale della cella di ricerca le formule rimangono quelle che mi hai dato tu

meglio il riferimento riga rimane 2:1000 per tutte le colonne:
=SCARTO(scarico!$A$1;GRANDE((RIF.RIGA(scarico!$2:$1000)*(scarico!$A$2:$A$1000=scarico!$A$1));RIF.RIGA(A1))-1;0)