Valutazione 4.87/ 5 (100.00%) 5838 voti

Condividi:        

FUnzione Utente o altro?

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

FUnzione Utente o altro?

Postdi papiriof » 27/02/14 11:43

Ho due colonne (come da immagine) la prima contiene dei codici la seconda dei numeri. Nella prima colonna ogni codice può essere
ripetuto ma, se ripetuto,nella seconda colonna corrispondono numeri diversi;viceversa nella seconda colonna ci possono essere
numeri uguali ma a numeri uguali corrispondono codici diversi.
La richiesta , magari può sembrare semplice ma non ci riesco, vorrei trovare una correlazione tra due codici .
Esempio :in una ipotetica funzione utente chiamata "CorrelazioneEsito" ( qui gli argomenti necessari,tra cui i due codici da confrontare)
se metto a confronto il codice "1BA01" e "1BA10" la funzione dovrebbe restituirmi 3 in quanto i due codici hanno 3 numeri in comune
se invece metto a confronto il codice "1BA01" e "1BA05" la funzione dovrebbe restituirmi 1 in quanto i due codici hanno solo 1 numero in comune
come si vede anche dall'immagine.
Avevo pensato a MATR.SOMMA.PRODOTTO ma non riesco a formularla ,forse con una funz utente ?!
PosizNum RigaEsito 1BA04 8684
1BA01 8690 1BA01 8690 1BA04 8714
1BA01 8702 1BA01 8702 1BA04 8717
1BA01 8715 1BA01 8715 1BA04 8719
1BA01 8685 1BA01 8685 1BA04 8722
1BA02 8717 1BA02 8717 1BA04 8736
1BA02 8719 1BA02 8719 1BA04 8743
1BA02 8743 1BA02 8743
1BA03 8690
1BA03 8714 1BA03 8690
1BA03 8714 1BA03 8714 1BA05 8690
1BA03 8739 1BA03 8717 1BA05 8727
1BA03 8755 1BA03 8739 1BA05 8758
1BA04 8684 1BA03 8755
1BA04 8714
1BA04 8717
1BA04 8719
1BA04 8722
1BA04 8736 1BA06 8697
1BA04 8743 1BA06 8774
1BA05 8690 1BA06 8788
1BA05 8727
1BA05 8758 1BA09 8776
1BA06 8697 1BA09 8797
1BA06 8774
1BA06 8788 1BA10 8690
1BA07 8690 1BA10 8702
1BA07 8769 1BA10 8715
1BA09 8776 1BA10 8731
1BA09 8797 1BA10 8741
1BA10 8690
1BA10 8702
1BA10 8715
1BA10 8731
1BA10 8741
Win 7+Office 2007... ma preferisco convertire in Office 2003
papiriof
Utente Senior
 
Post: 152
Iscritto il: 16/02/10 13:23

Sponsor
 

Re: FUnzione Utente o altro?

Postdi papiriof » 27/02/14 13:25

scusate per la mancanza dell'immagine le prime due colonne(PosizNum RigaEsito ) rispettivamente con sotto i codici e i numeri le altre 4 col (impaginate male) rappresentano una specifica per meglio evidenziare quanto volevo esporre.
Win 7+Office 2007... ma preferisco convertire in Office 2003
papiriof
Utente Senior
 
Post: 152
Iscritto il: 16/02/10 13:23

Re: FUnzione Utente o altro?

Postdi CANAPONE » 27/02/14 13:51

Ciao

in H1=1BA01
in H2=1BA10

per contare quanti sono i numeri distinti in comune

Codice: Seleziona tutto
=SOMMA(--(VAL.NUMERO(CONFRONTA(SE($A$1:$A$34=H1;B1:B34);($A$1:$A$34=H2)*$B$1:$B$34;0))))


da confermare con control+maisc+invio.


Saluti
---------------
Excel 2004 su Mac
Excel 2010 su Windows 8
CANAPONE
Utente Senior
 
Post: 425
Iscritto il: 22/11/10 15:43
Località: Firenze

Re: FUnzione Utente o altro?

Postdi Zer0Kelvin » 27/02/14 15:01

Ciao.
In attesa del riscontro alla risposta di Canapone, posto una probabile UDF.
Visto che non hai specificato cosa dovrebbe fare la UDF in caso di cifre duplicate non ho approfondito.
La UDF conta le cifre di var1 che sono anche presenti in var2, anche se ripetute; per es il confronto fra 102232 e 659528 (oppure "102232 " e "659528") restituisce 3.
Codice: Seleziona tutto
Public Function CountEqualDigit(var1 As Variant, var2 As Variant) As Long
' conta le cifre di var1 che sono presenti in var2
Dim L1 As Long, L2 As Long, S1 As String, S2 As String, c1 As Long, c2 As Long, digit1 As Byte, digit2 As Byte
CountEqualDigit = -1 '(valore di errore; restituito in caso di parametri errati o vuoti
    If IsNumeric(var1) And IsNumeric(var2) Then
        S1 = Trim(CStr(var1))
        S2 = Trim(CStr(var2))
        L1 = Len(S1)
        L2 = Len(S2)
        If L1 > 0 And L2 > 0 Then
            CountEqualDigit = 0
            For c1 = 1 To L1
                digit1 = Val(Mid(S1, c1, 1))
                For c2 = 1 To L2
                    digit2 = Val(Mid(S2, c2, 1))
                    If digit1 = digit2 Then
                        CountEqualDigit = CountEqualDigit + 1
                        Exit For
                    End If
                Next c2
            Next c1
        End If
    End If
End Function
[Win7,Office2010]
Condividere la conoscenza aumenta la ricchezza di tutti(Z0°K)
Dai ad un uomo un pesce e lo avrai sfamato per un giorno;insegnagli a pescare e lo avrai sfamato per sempre(Confucio)
Il sonno della ragione genera mostri(Francisco Goya)
Avatar utente
Zer0Kelvin
Utente Senior
 
Post: 303
Iscritto il: 08/04/12 11:23

Re: FUnzione Utente o altro?

Postdi papiriof » 27/02/14 15:33

Grazie CANAPONE funziona!!!
Ti chiedo troppo di commentare come funziona questo uso combinato di dierse formule (SOMMA,VAL.NUMERO,CONFRONTA e SE) soprattutto i due "- -" che funzione hanno ? ancora grazie p.
Win 7+Office 2007... ma preferisco convertire in Office 2003
papiriof
Utente Senior
 
Post: 152
Iscritto il: 16/02/10 13:23

Re: FUnzione Utente o altro?

Postdi CANAPONE » 27/02/14 16:04

Ciao,

il segmento

Codice: Seleziona tutto
=SE($A$1:$A$34=H1;B1:B34)


seleziona i numeri corrispondenti ad H1

CONFRONTA cerca questi numeri nella matrice di numeri prodotta nel secondo argomento

Codice: Seleziona tutto
($A$1:$A$34=H2)*$B$1:$B$34


che produce 34 numeri; tanti zero più i numeri corrispondendi ad H2.

Se CONFRONTA trova i primi numeri -prodotti da SE($A$1:$A$34=H1;B1:B34)- fra questo secondo gruppo di numeri, restituisce la posizione: un numero, non mi interessa quale.

Se non li trova restituisce messaggio d'errore.

VAL.NUMERO che contiene tutta la formula restituisce VERO, se CONFRONTA ha restituito la posizione (è un numero, quindi VERO)
FALSO se non trova nulla (il messaggio d'errore n/d produce FALSO).

Con il -- davanti VERO diventa 1, FALSO divento zero.


SOMMA addiziona questi 1.
---------------
Excel 2004 su Mac
Excel 2010 su Windows 8
CANAPONE
Utente Senior
 
Post: 425
Iscritto il: 22/11/10 15:43
Località: Firenze

Re: FUnzione Utente o altro?

Postdi papiriof » 27/02/14 16:19

CANAPONE ha scritto:Ciao,

il segmento

Codice: Seleziona tutto
=SE($A$1:$A$34=H1;B1:B34)


seleziona i numeri corrispondenti ad H1

CONFRONTA cerca questi numeri nella matrice di numeri prodotta nel secondo argomento

Codice: Seleziona tutto
($A$1:$A$34=H2)*$B$1:$B$34


che produce 34 numeri; tanti zero più i numeri corrispondendi ad H2.

Se CONFRONTA trova i primi numeri -prodotti da SE($A$1:$A$34=H1;B1:B34)- fra questo secondo gruppo di numeri, restituisce la posizione: un numero, non mi interessa quale.

Se non li trova restituisce messaggio d'errore.

VAL.NUMERO che contiene tutta la formula restituisce VERO, se CONFRONTA ha restituito la posizione (è un numero, quindi VERO)
FALSO se non trova nulla (il messaggio d'errore n/d produce FALSO).

Con il -- davanti VERO diventa 1, FALSO divento zero.


SOMMA addiziona questi 1.

Ottima Spiegazione!! GRAZIE CANAPONE
Ringrazio per l'interessamento anche Zer0Kelvin
Win 7+Office 2007... ma preferisco convertire in Office 2003
papiriof
Utente Senior
 
Post: 152
Iscritto il: 16/02/10 13:23

Re: FUnzione Utente o altro?

Postdi CANAPONE » 27/02/14 16:39

Ciao,

grazie a te del generoso riscontro: non mi sembrava molto chiaro il tentativo di spiegare la formula.

Metti alla prova la formula: credo che se ci sono dei numeri uguali in corrispondenza di h1 la formula non restituisca il risultato atteso: ed è molto probabile che il Vba sia di maggiore aiuto.

Se questa casistica non esiste nel caso che stai gestendo ( per esempio due o più 8690 in corrispondenza di due o più 1BA01), la formula potrebbe andare.

Saluti
---------------
Excel 2004 su Mac
Excel 2010 su Windows 8
CANAPONE
Utente Senior
 
Post: 425
Iscritto il: 22/11/10 15:43
Località: Firenze

Re: FUnzione Utente o altro?

Postdi CANAPONE » 27/02/14 16:57

Ciao,

così eviti i doppi conteggi


Codice: Seleziona tutto
=SOMMA(--(VAL.NUMERO(CONFRONTA(RIF.RIGA(1:100000);CONFRONTA(SE($A$1:$A$34=H1;B1:B34);($A$1:$A$34=H2)*$B$1:$B$34;0);0))))


Sempre control+maiusc+invio.

In atteso di soluzioni migliori.

Saluti
---------------
Excel 2004 su Mac
Excel 2010 su Windows 8
CANAPONE
Utente Senior
 
Post: 425
Iscritto il: 22/11/10 15:43
Località: Firenze

Re: FUnzione Utente o altro?

Postdi papiriof » 27/02/14 17:27

CANAPONE ha scritto:Ciao,


Se questa casistica non esiste nel caso che stai gestendo ( per esempio due o più 8690 in corrispondenza di due o più 1BA01), la formula potrebbe andare.

Saluti

No, non possono esserci doppioni,quindi la formula va più che bene! piuttosto ... se posso approfittare :oops: ho la necessità di fare diversi confronti fra due codici diversi perchè il fine è quello di trovare due codici che hanno più numeri in comune.
Quindi devo cambiare più velocemente possibile i codici a questo scopo nelle caselle dove ci sono i due codici ho messo un convalida ma è lo stesso abbastanza lento, ci vorebbe che a far muovere i codici una casella di controllo ma queste agiscono su dei numeri non su codici alfanumerici....qualche idea?
Win 7+Office 2007... ma preferisco convertire in Office 2003
papiriof
Utente Senior
 
Post: 152
Iscritto il: 16/02/10 13:23

Re: FUnzione Utente o altro?

Postdi CANAPONE » 27/02/14 17:47

Ciao, porterei le serie di due codici da confrontare (H1 E H2) uno accanto all'altro (H1 ed I1): in H1:I50 -esempio scrivi tutte le coppie di codici e la stessa formula

=SOMMA(--(VAL.NUMERO(CONFRONTA(SE($A$1:$A$34=H1;B1:B34);($A$1:$A$34=I1)*$B$1:$B$34;0))))

Poi usi un semplice MAX , o un INDICE(..CONFRONTA(MAX... sui risultati che ottieni dalle formule matrice.

Forse si può fare tutto con una formula: no saprei dirti come, sicuramente perderesti parecchia sensibilità sui numeri da controllare.

Sempre che abbia capito
---------------
Excel 2004 su Mac
Excel 2010 su Windows 8
CANAPONE
Utente Senior
 
Post: 425
Iscritto il: 22/11/10 15:43
Località: Firenze

Re: FUnzione Utente o altro?

Postdi papiriof » 11/03/14 08:57

CANAPONE ha scritto:Ciao, porterei le serie di due codici da confrontare (H1 E H2) uno accanto all'altro (H1 ed I1): in H1:I50 -esempio scrivi tutte le coppie di codici e la stessa formula

=SOMMA(--(VAL.NUMERO(CONFRONTA(SE($A$1:$A$34=H1;B1:B34);($A$1:$A$34=I1)*$B$1:$B$34;0))))

Poi usi un semplice MAX , o un INDICE(..CONFRONTA(MAX... sui risultati che ottieni dalle formule matrice.

Forse si può fare tutto con una formula: no saprei dirti come, sicuramente perderesti parecchia sensibilità sui numeri da controllare.

Sempre che abbia capito

Buongiorno , chiedo scusa aCANAPONE per il riscontro alla presente ma causa influenza ho fatto delle prove in modo saltuario; Sostanzialmente la soluzione proposta funziona ma in pratica è inapplicabile perchè i confronti li ho fatti con un codice fisso contro tutti gli altri solo che " tutti gli altri " sono proprio tanti..... (fatto una prova con 6800 codici contro uno fisso ed è subbentrato "calcola" eper finire ha impiegato più di mezz'ora!!!!) ma, una curiosità si può ovviare a calcola??
Win 7+Office 2007... ma preferisco convertire in Office 2003
papiriof
Utente Senior
 
Post: 152
Iscritto il: 16/02/10 13:23

Re: FUnzione Utente o altro?

Postdi Anthony47 » 11/03/14 23:59

Non sono in grado di fare un collaudo spinto, per carenza di file esemplificativo, ma direi che questa macro potrebbe produrre i confronti richiesti (in sostituzione delle formule):
Codice: Seleziona tutto
Sub pappr()
Dim VArr1, I As Long, LastA As Long, Dest As String, V As Long, H As Long, rDim As Long
Dim RArr(), cPos As Variant, cRig As Long, myComm As Long
'
Dest = "K2"     '<<< L' area ove sara' creata la tabella esiti
'
LastA = Cells(Rows.Count, 1).End(xlUp).Row
VArr1 = Range("A2:B" & LastA).Value
'
Range(Dest).Resize(100, 100).ClearContents     ' AZZERA Area di creazione risultati
Range("A1:A" & LastA).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
        Dest), Unique:=True
rDim = Range(Dest, Range(Dest).End(xlDown)).Count - 1
Range(Dest).Offset(1, 0).Resize(rDim, 1).Copy
Range(Dest).Offset(0, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
Application.CutCopyMode = False
r3d1 = Application.WorksheetFunction.Min(Range("B2:B" & LastA).Value)
r3d2 = Application.WorksheetFunction.Max(Range("B2:B" & LastA).Value)
ReDim RArr(1 To rDim, r3d1 To r3d2)
'riposiziona:
For I = LBound(VArr1, 1) To UBound(VArr1, 1)
    cPos = Application.Match(VArr1(I, 1), Range(Dest).Offset(0, 1).Resize(1, rDim), 0)
    RArr(cPos, VArr1(I, 2)) = 1
Next I
'Calcola:
For V = 1 To rDim - 1
    For H = V + 1 To rDim
        myComm = 0
        For I = LBound(RArr, 2) To UBound(RArr, 2)
            If RArr(V, I) <> "" Then
                If RArr(H, I) = RArr(V, I) Then
                    myComm = myComm + 1
                End If
            End If
        Next I
        Range(Dest).Offset(V, H).Value = myComm
    Next H
Next V
End Sub

Inseriscila in un modulo standard (es Modulo1), personalizza l' istruzione marcata <<< poi mandala in esecuzione.
La macro assume che il codice sia in colonna A e il numero in colonna B; verra' creata una tabella all' indirizzo specificato (K2, nel mio codice), con in verticale e in orizzontale i vari Codici, e all' incrocio il "numero di numeri" in comune.
Attenzione: non sapendo quanto sara' grande la tabella di destinazione la macro AZZERA un' area di 100 righe * 100 Colonne a partire dall' indirizzo specificato (K2 nel mio codice); tienilo in conto quando decidi dove creare la tabella degli esiti.

Ciao, fai sapere.
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: 13894
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: FUnzione Utente o altro?

Postdi papiriof » 12/03/14 20:06

Anthony47 ha scritto:Non sono in grado di fare un collaudo spinto, per carenza di file esemplificativo, ma direi che questa macro potrebbe produrre i confronti richiesti (in sostituzione delle formule):
Codice: Seleziona tutto
Sub pappr()
Dim VArr1, I As Long, LastA As Long, Dest As String, V As Long, H As Long, rDim As Long
Dim RArr(), cPos As Variant, cRig As Long, myComm As Long
'
Dest = "K2"     '<<< L' area ove sara' creata la tabella esiti
'
LastA = Cells(Rows.Count, 1).End(xlUp).Row
VArr1 = Range("A2:B" & LastA).Value
'
Range(Dest).Resize(100, 100).ClearContents     ' AZZERA Area di creazione risultati
Range("A1:A" & LastA).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
        Dest), Unique:=True
rDim = Range(Dest, Range(Dest).End(xlDown)).Count - 1
Range(Dest).Offset(1, 0).Resize(rDim, 1).Copy
Range(Dest).Offset(0, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
Application.CutCopyMode = False
r3d1 = Application.WorksheetFunction.Min(Range("B2:B" & LastA).Value)
r3d2 = Application.WorksheetFunction.Max(Range("B2:B" & LastA).Value)
ReDim RArr(1 To rDim, r3d1 To r3d2)
'riposiziona:
For I = LBound(VArr1, 1) To UBound(VArr1, 1)
    cPos = Application.Match(VArr1(I, 1), Range(Dest).Offset(0, 1).Resize(1, rDim), 0)
    RArr(cPos, VArr1(I, 2)) = 1
Next I
'Calcola:
For V = 1 To rDim - 1
    For H = V + 1 To rDim
        myComm = 0
        For I = LBound(RArr, 2) To UBound(RArr, 2)
            If RArr(V, I) <> "" Then
                If RArr(H, I) = RArr(V, I) Then
                    myComm = myComm + 1
                End If
            End If
        Next I
        Range(Dest).Offset(V, H).Value = myComm
    Next H
Next V
End Sub

Inseriscila in un modulo standard (es Modulo1), personalizza l' istruzione marcata <<< poi mandala in esecuzione.
La macro assume che il codice sia in colonna A e il numero in colonna B; verra' creata una tabella all' indirizzo specificato (K2, nel mio codice), con in verticale e in orizzontale i vari Codici, e all' incrocio il "numero di numeri" in comune.
Attenzione: non sapendo quanto sara' grande la tabella di destinazione la macro AZZERA un' area di 100 righe * 100 Colonne a partire dall' indirizzo specificato (K2 nel mio codice); tienilo in conto quando decidi dove creare la tabella degli esiti.

Ciao, fai sapere.

Grazie per l'interessamento Anthony , ho fatto come da te detto ossia :nella colonna A (da A1) ci sono 6800 codici alcuni di essi in diversamente ripetuti a fianco dei quali(quindi a cominciare da B1 ) i 6800 numeri anch'essi diversamente ripetuti, ho messo un bottone per laciare la macro (che non ho cambiato) ed il risultato è stato che mi si è popolata la col K a partire da K2 fino a K1351
ovvero tutti i codici SENZA I DOPPIONI come giustamente dovrebbe essere ma curiosamente ripetuto il primo codice "1BA01".
Il codice si blocca a questo punto :
Range(Dest).Offset(0, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True

ovviamente c'è solo questo vettore ma non la tabella , ho sbagliato qualcosa?
Win 7+Office 2007... ma preferisco convertire in Office 2003
papiriof
Utente Senior
 
Post: 152
Iscritto il: 16/02/10 13:23

Re: FUnzione Utente o altro?

Postdi Anthony47 » 13/03/14 01:08

Quindi hai 1500 codici diversi...
La macro cerca di creare una tabella di 1500 righe per 1500 colonne; se non ci riesce e' perche' stai lavorando su XL2007 in compatibility mode, che offre solo 256 colonne.
Ti conviene passare a modalita' 2007...

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: 13894
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: FUnzione Utente o altro?

Postdi papiriof » 13/03/14 07:51

Grazie Anthony ho seguito il consiglio sono passato a 2007 e adesso funziona perfettamente , ci mette un pochino ma niente a paragone con il precedente!!!!
Win 7+Office 2007... ma preferisco convertire in Office 2003
papiriof
Utente Senior
 
Post: 152
Iscritto il: 16/02/10 13:23

Re: FUnzione Utente o altro?

Postdi Anthony47 » 13/03/14 12:49

La macro calcola 1.125.000 correlazioni, direi che qualche decina di decina di secondi gli servono tutti...
Alla prossima.
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: 13894
Iscritto il: 21/03/06 16:03
Località: Ivrea


Torna a Applicazioni Office Windows


Topic correlati a "FUnzione Utente o altro?":


Chi c’è in linea

Visitano il forum: Nessuno e 18 ospiti