confronta colonne
gino
mi servirebbe un aiuto per confrontare 2 colonne
ho un file di excel con 2 fogli (originari, attuali) contenenti dei numeri
nella colonna A ed altri dati nelle colonne adiacenti
ora mi servirebbe confrontare le 2 colonne A dei due fogli ed estrapolare in
un terzo foglio solo i valori che differiscono (molti dei valori, che sono
numeri possono essere uguali ed io vohglio estrapolare solo quelli che non
sono presenti in entrambe le colonne)
esempio
Colonna A Colonna A
1 1
2 2
3 4
5 5
4 7
8 9
nel 3 foglio mi dovrebbe riportare il 3,7, 8, 9
poi avrei bisogno che mi segnalasse (magari con un colore diverso i numeri
che sono nel primo foglio e non nel secondo e viceversa)
infine se mi potesse, nell'estrapolazione nel 3 foglio, portare dietro anche
i valori delle celle adiacenti la colonna A (valori delle colonne b, c, d,
e, ecc ecc ) sarebbe proprio il massimo
Me lo date un aiuto? (sono convinto che per dei guru come voi � una
sciocchezza ma per un estraneo ad excel come me, pare una impresa
impossibile)
Grazie
Gino
Me lo date un aiuto? (sono convinto che per dei guru come voi è una
elby
Premesso che l'operazione che chiedi sarenne preferibiel farla in una
applicazione di database con query appropiata, in excel le alternative
girano su una serie di formule con colonne di appoggio ( non mi sono
cimentato per la ricerca di una unica formula ) o ricorrendo a VBA ( e
qui pure sono posibili varie soluzioni: sub ricorrendo ad ADO; sub
ricorrendo a array; funzione definita dall'utente di tipo
matriciale ).
Qui ti propongo una soluzione triviale con formule matriciali:
Per quanto non lo espliciti si assume che i 2 elenchi da confrontare
hanno un numero diverso di righe ma uguale di colonne; che nell'ambito
di ogni elenco il valore del primo campo sia univoco ( cioè senza
ripetizioni nell'elenco ).
Se per esempio nel primo foglio l'elenco occupa l'intervallo A1:F6 e
nel secondo l'intervallo A1:F7
In foglio3:
In A1:A6:
=SE(VAL.NON.DISP(CONFRONTA(Foglio1!A1:A6;Foglio2!A1:A7;0))=FALSO;
0;Foglio1!A1:A6)
da inserire come matriciale con Ctrl + Maisc + Invio
In B1:B7:
=SE(VAL.NON.DISP(CONFRONTA(Foglio2!A1:A7;Foglio1!A1:A6;0))=FALSO;
0;Foglio2!A1:A7)
da inserire come matriciale con Ctrl + Maisc + Invio
In C1:C14 ( cioè un intervallo in righe doppio del più lungo dei 2
elenchi ):
=GRANDE(A1:B7;RIF.RIGA(INDIRETTO("A1:B"& RIF.RIGA(A7)*2)))
da inserire come matriciale con Ctrl + Maisc + Invio
In E1:I1:
=SE(C1<>0;SE(VAL.NON.DISP(CERCA.VERT(C1;Foglio1!$A$1:$F$6;
{1;2;3;4;5};FALSO))=FALSO;CERCA.VERT(C1;Foglio1!$A$1:$F$6;
{1;2;3;4;5};FALSO);CERCA.VERT(C1;Foglio2!$A$1:$F$7;
{1;2;3;4;5};FALSO)))
da inserire come matriciale con Ctrl + Maisc + Invio
Trascinare le formule E1:C1 in basso fino all'ultimo dato
significativo
Fai saper se hai risolto
Ciao Elio
> Me lo date un aiuto? (sono convinto che per dei guru come voi è una
paoloard
"gino" <gi...@discussion.microsoft.com> ha scritto nel messaggio
news:uPSqKQuu...@TK2MSFTNGP02.phx.gbl...
> ciao a tutti
>
> mi servirebbe un aiuto per confrontare 2 colonne
>
> ho un file di excel con 2 fogli (originari, attuali) contenenti dei numeri
> nella colonna A ed altri dati nelle colonne adiacenti
>
> ora mi servirebbe confrontare le 2 colonne A dei due fogli ed estrapolare
> in un terzo foglio solo i valori che differiscono (molti dei valori, che
> sono numeri possono essere uguali ed io vohglio estrapolare solo quelli
> che non sono presenti in entrambe le colonne)
>
> esempio
> Colonna A Colonna A
> 1 1
> 2 2
> 3 4
> 5 5
> 4 7
> 8 9
>
> nel 3 foglio mi dovrebbe riportare il 3,7, 8, 9
>
ciao gino, una soluzione che far� inorridire chi so io:
solo per il primo quesito:
nel presupposto che la prima riga sia occupata dai titoli, quindi tutti i
valori inizino dalla seconda riga, indipendentemente dalla lunghezza delle
colonne, con una colonna d'appoggio che, in pratica compatta le due colonne
in una sola:
in foglio3
in A2:
=SE(RIF.RIGA($A1)>CONTA.NUMERI(Foglio1!$A$1:$A$100)+CONTA.NUMERI(Foglio2!$A$1:$A$100);"#";SCARTO(INDIRETTO("Foglio"&ARROTONDA.PER.ECC(RIF.RIGA(A1)/CONTA.NUMERI(Foglio1!$A$1:$A$100);)&"!$A$1");(RESTO(RIF.RIGA()-2;6)+1);0))
copia in basso fino al risultato #.
poi estrae i valori non ripetuti:
in B2:
=INDICE(INDIRETTO("$A$2:$A$"&(CONTA.NUMERI($A$2:$A$100)+1));PICCOLO(SE(FREQUENZA(CONFRONTA(INDIRETTO("$A$2:$A$"&(CONTA.NUMERI($A$2:$A$100)+1));INDIRETTO("$A$2:$A$"&(CONTA.NUMERI($A$2:$A$100)+1));0);CONFRONTA(INDIRETTO("$A$2:$A$"&(CONTA.NUMERI($A$2:$A$100)+1));INDIRETTO("$A$2:$A$"&(CONTA.NUMERI($A$2:$A$100)+1));0))=1;RIF.RIGA(INDIRETTO("$A$2:$A$"&(CONTA.NUMERI($A$2:$A$100)+1)))-1;"");RIF.RIGA(A1)))
matriciale da inserire con Ctrl+Maiusc+Invio e copiare in basso fino al
valore di errore #NUM!.
lo so! rischio il linciaggio.... abbiate piet�! e non chiedete di ripetere
perch� non saprei pi� da dove cominciare. 8-P
Per gli altri quesiti lasciatemi riprendere fiato. :-(
--
Fai sapere se e come hai risolto grazie.
ciao paoloard
http://riolab.org
Nur
>
> mi servirebbe un aiuto per confrontare 2 colonne
> Colonna A Colonna A
> 1 1
> 2 2
> 3 4
> 5 5
> 4 7
> 8 9
>
> nel 3 foglio mi dovrebbe riportare il 3,7, 8, 9
>
Ciao,
ci provo anch'io :-)
Chiamerò per praticità rng1 un range del primo foglio e rng2 un range
del secondo foglio, di righe *uguali* e almeno sufficienti a contenere
il più grande dei due.
(tipo Foglio1!$A$1:$A$100 e Foglio2!$A$1:$A$100)
(definirli da: menù > inserisci > nome > definisci)
Nell'altro foglio inserisci:
=PICCOLO(SE(SE({1;0};SE(FREQUENZA(rng1;1*rng1);CONTA.SE(rng2;rng1)=0);SE(FREQUENZA(rng2;1*rng2);CONTA.SE(rng1;rng2)=0));SE({1;0};rng1;rng2));RIF.RIGA(A1))
matriciale da trascinare in basso
Da provare, non l'ho testata molto.
Se si potesse escludere a priori la possibilità di valori ripetuti
all'interno dei singoli range, forse si potrebbe togliere qualche
pezzo.
Ciao, Nur
paoloard
"Nur" <news...@gmail.com> ha scritto nel messaggio
news:77482baa-cbae-4929...@q23g2000yqd.googlegroups.com...
On 3 Mar, 16:13, "gino" <g...@discussion.microsoft.com> wrote:
> ciao a tutti
>
> mi servirebbe un aiuto per confrontare 2 colonne
> esempio
> Colonna A Colonna A
> 1 1
> 2 2
> 3 4
> 5 5
> 4 7
> 8 9
>
> nel 3 foglio mi dovrebbe riportare il 3,7, 8, 9
>
Ciao,
ci provo anch'io :-)
Chiamer� per praticit� rng1 un range del primo foglio e rng2 un range
del secondo foglio, di righe *uguali* e almeno sufficienti a contenere
il pi� grande dei due.
(tipo Foglio1!$A$1:$A$100 e Foglio2!$A$1:$A$100)
(definirli da: men� > inserisci > nome > definisci)
Nell'altro foglio inserisci:
=PICCOLO(SE(SE({1;0};SE(FREQUENZA(rng1;1*rng1);CONTA.SE(rng2;rng1)=0);SE(FREQUENZA(rng2;1*rng2);CONTA.SE(rng1;rng2)=0));SE({1;0};rng1;rng2));RIF.RIGA(A1))
matriciale da trascinare in basso
Da provare, non l'ho testata molto.
Se si potesse escludere a priori la possibilit� di valori ripetuti
all'interno dei singoli range, forse si potrebbe togliere qualche
pezzo.
Ciao, Nur
*********************************************
Ciao Nur.. va va altroch�,
adesso mi vergogno tanto :-(
--
ciao paoloard
http://riolab.org
elby
rng1 e rng2 sono nomi infelici in Excel 2007 o più semplicemente
invalidi in quanto entrano in conflitto con le celle RNG1 e RNG2
Inoltre, sembra limitativo il fatto che i 2 range denominati debbano
avere dimensioni uguali, anche se indubbiamente la formula ha il
vantaggio di essere unica senza colonna di appoggio.
E allora propongo, tanto per giocare, visto che il postante non si è
fatto più vivo, una UDF matriciale
Inserire in un numero di celle in colonna pari alla somma delle righe
dei 2 range
=MancateCorrispondenze2VettoriLong(Range1;Range2)
dove Range1 e Range2 sono 2 colonne anche di un numero diverse di
celle
La UDF in un modulo standard
Public Function MancateCorrispondenze2VettoriLong(rng1 As Range, rng2
As Range) As Variant
Dim arr1 As Variant
Dim arr2 As Variant
Dim arrNoMatch() As Long
Dim r1 As Long
Dim r2 As Long
Dim x As Long
Dim y As Long
On Error GoTo ErrHandler
arr1 = rng1
arr2 = rng2
r1 = UBound(arr1)
r2 = UBound(arr2)
For x = 1 To r1
If WorksheetFunction.CountIf(rng2, arr1(x, 1)) = 0 Then
y = y + 1
ReDim Preserve arrNoMatch(1 To 1, 1 To y)
arrNoMatch(1, y) = arr1(x, 1)
End If
Next x
For x = 1 To r2
If WorksheetFunction.CountIf(rng1, arr2(x, 1)) = 0 Then
y = y + 1
ReDim Preserve arrNoMatch(1 To 1, 1 To y)
arrNoMatch(1, y) = arr2(x, 1)
End If
Next x
MancateCorrispondenze2VettoriLong =
WorksheetFunction.Transpose(arrNoMatch)
ExitProcedure:
Exit Function
ErrHandler:
'Debug.Print Err.Number & " " & Err.Description
Resume ExitProcedure
End Function
Ciao a tutti
Elio
24 Mar, 19:22, "paoloard" <xxp...@alice.it> wrote:
> "Nur" <news....@gmail.com> ha scritto nel messaggionews:77482baa-cbae-4929...@q23g2000yqd.googlegroups.com...
> On 3 Mar, 16:13, "gino" <g...@discussion.microsoft.com> wrote:
>
> > ciao a tutti
>
> > mi servirebbe un aiuto per confrontare 2 colonne
> > esempio
> > Colonna A Colonna A
> > 1 1
> > 2 2
> > 3 4
> > 5 5
> > 4 7
> > 8 9
>
> > nel 3 foglio mi dovrebbe riportare il 3,7, 8, 9
>
> Ciao,
> ci provo anch'io :-)
>
> Chiamerò per praticità rng1 un range del primo foglio e rng2 un range
> del secondo foglio, di righe *uguali* e almeno sufficienti a contenere
> il più grande dei due.
> (tipo Foglio1!$A$1:$A$100 e Foglio2!$A$1:$A$100)
>
> (definirli da: menù > inserisci > nome > definisci)
>
> Nell'altro foglio inserisci:
> =PICCOLO(SE(SE({1;0};SE(FREQUENZA(rng1;1*rng1);CONTA.SE(rng2;rng1)=0);SE(FREQUENZA(rng2;1*rng2);CONTA.SE(rng1;rng2)=0));SE({1;0};rng1;rng2));RIF.RIGA(A1))
> matriciale da trascinare in basso
>
> Da provare, non l'ho testata molto.
>
> Se si potesse escludere a priori la possibilità di valori ripetuti
> all'interno dei singoli range, forse si potrebbe togliere qualche
> pezzo.
>
> Ciao, Nur
> *********************************************
>
> Ciao Nur.. va va altroché,
paoloard
"elby" <elib...@tin.it> ha scritto nel messaggio
news:cae4a997-3af9-427b...@z4g2000yqa.googlegroups.com...
Ciao
rng1 e rng2 sono nomi infelici in Excel 2007 o pi� semplicemente
invalidi in quanto entrano in conflitto con le celle RNG1 e RNG2
************************************
ciao elby, in effetti quando ho affermato che la formula funzionava bene non
ho specificato che avevo modificato i nomi in range1 e range2.
Nur
On 4 Mar, 23:35, elby <elibu...@tin.it> wrote:
> Ciao
> rng1 e rng2 sono nomi infelici in Excel 2007 o più semplicemente
> invalidi in quanto entrano in conflitto con le celle RNG1 e RNG2
Avete ragione, scusatemi.
La formula l'avevo scritta in Excel 2003 e provata (come spesso
faccio) anche col 2007. Ma senza utilizzare i nomi di zona.
Poi, incollandola nel post, mi era sembrata poco leggibile e ho
preferito sostituire gli intervalli con i nomi.
Scegliendoli, però, in modo 'infelice'.
> Inoltre, sembra limitativo il fatto che i 2 range denominati debbano
> avere dimensioni uguali, anche se indubbiamente la formula ha il
> vantaggio di essere unica senza colonna di appoggio.
Preciso una cosa (non per te, Elio), perché rileggendomi mi sono
accorta di non essere stata molto chiara sul discorso dei due range.
Non è necessario che il numero di dati del primo e del secondo foglio
siano uguali.
Sono gli intervalli che utilizzo in quella formula che devono avere lo
stesso numero di righe. Quante righe? Almeno il numero di righe del
range che contiene più dati.
Se abbiamo dati in Foglio1!A1:A5 e dati in Foglio2!A1:A10, i range da
utilizzare nella formula saranno:
Foglio1!A1:A10
Foglio2!A1:A10
In ogni caso, concordo con il 'limite' segnalato da Elio che suppongo
si riferisse all'inutile 'sovradimensionamento' del più piccolo dei
due range.
Anche perché, per evitarlo, bastava poco, ma ieri l'avevo davvero
scritta di fretta.
La posto anch'io più che altro per gioco. Anche perché, ricordo, che
formule come questa, se i dati fossero 'molti', sarebbe assolutamente
da evitare.
Comunque, ...così rng_1 e rng_2 possono essere di righe diverse:
=PICCOLO(SE(NON(VAL.NON.DISP(SE({1;0};SCARTO(rng_1;;;RIGHE(rng_1)+1);SCARTO(rng_2;;;RIGHE(rng_2)+1))));SE(SE({1;0};SE(FREQUENZA(rng_1;1*rng_1);CONTA.SE(rng_2;rng_1)=0);SE(FREQUENZA(rng_2;1*rng_2);CONTA.SE(rng_1;rng_2)=0));SE({1;0};rng_1;rng_2)));RIF.RIGA(A1))
matriciale da trascinare in basso
> E allora propongo, tanto per giocare, visto che il postante non si è
> fatto più vivo, una UDF matriciale
> >
> =MancateCorrispondenze2VettoriLong(Range1;Range2)
> dove Range1 e Range2 sono 2 colonne anche di un numero diverse di
> celle
>
Leggendo la tua UDF, ho notato che abbiamo gestito in modo diverso la
presenza di zeri, celle vuote ed eventuali numeri ripetuti.
Con la formula ho scelto di considerare i numeri nei singoli range una
sola volta.
Ma non è detto, in effetti, che la mia sia l'interpretazione corretta.
Quindi, nel caso:
in foglio1: 1 1 3
in foglio2: 2 3 2
Io ho voluto ottenere:
1 2
Tu invece:
1 1 2 2
Altra differenza:
Nella tua udf le celle vuote vengono considerate zeri. Nella mia
formula, sia per esigenza (nella prima versione), che per scelta, le
celle vuote vengono ignorate. Gli zeri, invece, se presenti, vengono
trattati come gli altri numeri.
Un'altra:
Se non dichiari arrNoMatch() come long, la tua udf potresti usarla
anche con valori di testo.
La mia formula *assolutamente* no.
Ciao, Nur
r
ciao a tutti,
ci provo ... per gioco ... anch'io:
Function only_r(ParamArray S()) As Variant
'non considera le celle vuote
Dim x As Variant, y As Variant
Dim dic
'riferimenti a Microsoft Scripting Runtime
'Set dic = New Dictionary
'senza riferimenti
Set dic = CreateObject("Scripting.dictionary")
For Each x In S
If TypeOf x Is Range Or IsArray(x) Then
For Each y In x
If dic.Exists(CStr(y)) = False And Not IsEmpty(y) Then
dic.Add CStr(y), y
End If
Next
Else
If dic.Exists(CStr(x)) = False Then
dic.Add CStr(x), x
End If
End If
Next x
only_r = dic.Keys
End Function
la udf considera qualsiasi valore anche la stringa vuota, escludendo
invece le celle vuote
Il risultato è sempre un vettore ...
saluti
r
r
>
> - Mostra testo citato -
ops ... però la domanda era un'altra :-)
scusate ma non l'avevo letta con attenzione
saluti
r
r
vediamo se ho capito:
Function only_r(ParamArray S()) As Variant
'non considera le celle vuote
Dim x As Variant, y As Variant
Dim dic, dic2
'riferimenti a Microsoft Scripting Runtime
'Set dic = New Dictionary
'Set dic2 = New Dictionary
'senza riferimenti
Set dic = CreateObject("Scripting.dictionary")
Set dic2 = CreateObject("Scripting.dictionary")
For Each x In S
If TypeOf x Is Range Or IsArray(x) Then
For Each y In x
If IsEmpty(y) Then
ElseIf dic.exists(CStr(y)) = False Then
dic.Add CStr(y), y
Else
If dic2.exists(CStr(y)) = False Then
dic2.Add CStr(y), y
End If
End If
Next
Else
If dic.exists(CStr(x)) = False Then
dic.Add CStr(x), x
Else
If dic2.exists(CStr(x)) = False Then
dic2.Add CStr(x), x
End If
End If
End If
Next x
For Each x In dic2
If dic.exists(x) Then
dic.Remove x
End If
Next
only_r = dic.Keys
End Function
saluti
r
elby
> only_r = dic.Keys
proporrei per poter essere utilizzata direttamente in celle in colonna
only_r = WorksheetFunction.Transpose(dic.Keys)
perchè la prima obbligherebbe alla nidificazione per l'utilizzo in
colonna sul foglio di lavoro; per es
=MATR.TRASPOSTA(only_r(A1:A6;C4:C10))
Comunque, poichè il tutto nasce per testare le mancate corrispondenze
tra 2 vettori di valori, sembra innaturale che un valore presente nel
primo vettore sia escluso ancorchè non presente nel secondo vettore
per il solo fatto che sia duplicato nel primo.
Comunque, a dirla tutta, esasperiamo una soluzione innaturale per il
tipo di problema postato in origine. In una gestione corretta di dati,
soprattutto se numerosi, la soluzione è una query complessa
Ciao Elio