Valutazione 4.87/ 5 (100.00%) 5838 voti

Condividi:        

Formula con CONFRONTA?

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

Formula con CONFRONTA?

Postdi papiriof » 18/06/20 18:02

Posso solo immaginare che ci voglia la funzione Confronta , ho visto gli esempi ma non ho capito come "accroccare" la formula.
Ho questa situazione due variabili in A1 var "A" in B1 var "B" dove metterò rispettivamente 3 e 56
in A2 una data e di seguito 25 numeri 5matrici di 5 numeri così :
01/01/2020 3 66 68 34 67 81 54 56 22 55 20 83 85 51 84 5 68 70 36 69 87 60 62 28 61
vorrei che la fotmula mi restituisse il numero di riga nel caso soprra esposto perchè i 2 numeri si tovano in posizione diverse
in quanto se avessi messo 3 e 81 o 3 e 83 o 66 e 54 insomma stesse posizioni la formula mi dovrebbe restituire 0 .
Spero di essermi spiegato , aggiungo che che se nel caso i due numeri si trovassero nella stessa matrice tipo 3 e 34 (nella prima matrice) anche in questo caso la fformula mi restituisce il numero di ririga.
Win 7+Office 2010
papiriof
Utente Senior
 
Post: 270
Iscritto il: 16/02/10 13:23

Sponsor
 

Re: Formula con CONFRONTA?

Postdi papiriof » 18/06/20 18:18

ci sono 2 discussioni con lo stesso titolo questa è quella giusta senza gli errori della precedente che ho chiesto di cancellare agli amministratori
Win 7+Office 2010
papiriof
Utente Senior
 
Post: 270
Iscritto il: 16/02/10 13:23

Re: Formula con CONFRONTA?

Postdi D@nilo » 18/06/20 20:22

Buonasera
condividi un file con un pò di dati e il risultato da ottenere scritto a mano
Avatar utente
D@nilo
Utente Junior
 
Post: 34
Iscritto il: 12/05/18 13:15
Località: ROMA

Re: Formula con CONFRONTA?

Postdi papiriof » 18/06/20 22:46

http://www.filedropper.com/forum_3
un piccolo file con i risultati attesi della formula
Win 7+Office 2010
papiriof
Utente Senior
 
Post: 270
Iscritto il: 16/02/10 13:23

Re: Formula con CONFRONTA?

Postdi D@nilo » 20/06/20 06:27

....post errato...
Avatar utente
D@nilo
Utente Junior
 
Post: 34
Iscritto il: 12/05/18 13:15
Località: ROMA

Re: Formula con CONFRONTA?

Postdi papiriof » 20/06/20 10:24

D@nilo ha scritto:....post errato...
NO, NO e quello giusto adesso mando un ulteriore file dove ho messo qualcosa in più per farmi comprender..... almeno spero :oops:
http://www.filedropper.com/forum_4
Win 7+Office 2010
papiriof
Utente Senior
 
Post: 270
Iscritto il: 16/02/10 13:23

Re: Formula con CONFRONTA?

Postdi D@nilo » 20/06/20 15:28

Ciao
il post errato era il mio...avevo scritto una formula sbagliata....prova cosi in AH2 da trascinare in basso


Codice: Seleziona tutto
=SE($A$1<>$B$1;SE(MATR.SOMMA.PRODOTTO(((CONTA.SE($D2:$AG2;$A$1)>0)+(CONTA.SE($D2:$AG2;$B$1)>0)))>1;A2+1;"");SE(CONTA.SE($D2:$AG2;$A$1)>=2;A2+1;""))
Avatar utente
D@nilo
Utente Junior
 
Post: 34
Iscritto il: 12/05/18 13:15
Località: ROMA

Re: Formula con CONFRONTA?

Postdi papiriof » 20/06/20 17:56

Codice: Seleziona tutto
=SE($A$1<>$B$1;SE(MATR.SOMMA.PRODOTTO(((CONTA.SE($[color=#FF0000]D2[/color]:$AG2;$A$1)>0)+(CONTA.SE($[color=#FF0000]D2[/color]:$AG2;$B$1)>0)))>1;A2+1;"");SE(CONTA.SE($[color=#FF0000]D2[/color]:$AG2;$A$1)>=2;A2+1;""))
[/quote]
Gentile sig Danilo grazie per l'nteressamento ho messo la sua formula ma da subito mi sono accorto che lei fa incominciare lo scandagliamento dalla colonna "D2" mentre la rilevazione incomincia dalla col " I2" e finisce alla colonna "AG2"
perciò ho corretto semplicemente D2 in ----> I2 cosi:
=SE($A$1<>$B$1;SE(MATR.SOMMA.PRODOTTO(((CONTA.SE($I2:$AG2;$A$1)>0)+(CONTA.SE($I2:$AG2;$B$1)>0)))>1;A2+1;"");SE(CONTA.SE($I2:$AG2;$A$1)>=2;A2+1;""))
Ora sempre in relazione all'ultimo foglio che ho mandato si provi a mettere in A1 1 e in B 1 85 e quest'ultima formula in AH2 e ricopiata in basso si noterà che in AH2 la formula ha messo 2 (a tal proposito cambiare la colonna A dalla A2 da 1 fino a 121) ma in questo caso doveva mettere NIENTE "" in quanto sia 85 che 1 si trovano tutti e due nelle rispettive cinquine al primo posto
rispettivamente 1° nella seconda cinquina e 1° nella terza cinquina.
Seconda osservazione nella cella AH 84 troviamo " " invece di 84 in quanto ho detto che se si fossero presentati uno dei 2 numeri immessi in a1 o b1 2 volte ma in posizioni diverse nelle rispettive cinquine mi avrebbe dato non " " ma il numero.
Win 7+Office 2010
papiriof
Utente Senior
 
Post: 270
Iscritto il: 16/02/10 13:23

Re: Formula con CONFRONTA?

Postdi Anthony47 » 21/06/20 00:28

Ho provato a risolverlo con le funzioni standard di excel e mi si stavano aggrovigliando i neuroni.
Ho optato quindi per una funzione personalizzata, corrispondente a questo codice:
Codice: Seleziona tutto
Function PapirAxB(ByVal aaVal As Long, ByVal bbVal As Long, ByRef myRan As Range) As Variant
'Vedi http://www.pc-facile.com/forum/viewtopic.php?f=26&t=111409
Dim oArr(), haArr(), hbArr(), I As Long, J As Long
'
ReDim oArr(1 To myRan.Rows.Count)
For I = 1 To myRan.Rows.Count
ReDim haArr(0 To myRan.Columns.Count / 5)
ReDim hbArr(0 To myRan.Columns.Count / 5)
    For J = 1 To myRan.Columns.Count
        If myRan.Cells(I, J) = aaVal Or myRan.Cells(I, J) = bbVal Then
            If hbArr(J Mod 5) = 0 Then haArr(J Mod 5) = 1
        End If
        If myRan.Cells(I, J) = bbVal Then
            If haArr(J Mod 5) = 0 Then hbArr(J Mod 5) = 1
        End If
    Next J
    If (Application.WorksheetFunction.Sum(haArr) > 0 And Application.WorksheetFunction.Sum(hbArr) > 0) Or _
    Application.WorksheetFunction.Sum(haArr) > 1 Or Application.WorksheetFunction.Sum(hbArr) > 1 Then
        oArr(I) = I + myRan.Cells(1, 1).Row - 1
    End If
Next I
PapirAxB = Application.WorksheetFunction.Transpose(oArr)
End Function

Il codice va messo in un modulo standard del vba, poi su foglio di lavoro puoi usare una formula come questa:
Codice: Seleziona tutto
=PapirAxB(aa;bb;I2:AG121)

La formula va inserita in forma matriciale nell'intervallo AH2-AH121:
-selezionare AH2:AH121
-scrivere la formula nella "barra della formula"
-confermare con Contr-Maiusc-Enter, non col solo Enter

Fai sapere...
Avatar utente
Anthony47
Moderatore
 
Post: 17445
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: Formula con CONFRONTA?

Postdi papiriof » 21/06/20 07:34

"Ho provato a risolverlo con le funzioni standard di excel e mi si stavano aggrovigliando i neuroni."
E figurati a me, povero mortale !!!!!!
Certo che va!!! era chiaro che alla base si dovesse individuare la posizione di aa e bb e mi ero immaginato l'uso di confronta e avevo pensato trovo il RESTO di (CONFRONTA aa+CONFRONTAaa x 10)/5 una cosa del genere ma mi perdevo.
Che dire ? troppo bravo!!!!!!!
Win 7+Office 2010
papiriof
Utente Senior
 
Post: 270
Iscritto il: 16/02/10 13:23

Re: Formula con CONFRONTA?

Postdi papiriof » 21/06/20 08:19

Una curiosità , vicino ad A1 e B1 c'è il bottone che mi permette di variare il numero posto in A1 per avere poi diversi scenari di ritardo con altri ambi ebbene perchè per fare una semplice operazione ci mette 4 ,5 secondi per aggiornarsi?
Win 7+Office 2010
papiriof
Utente Senior
 
Post: 270
Iscritto il: 16/02/10 13:23

Re: Formula con CONFRONTA?

Postdi Anthony47 » 21/06/20 20:27

Col file che hai pubblicato la funzione a me impiega 0.1-0.2 sec per completarsi; con una tabella da 1000 righe impiega 0.4-0.5 sec
Se hai molte piu' righe si puo' pensare di modificare per velocizzare di 5-10 volte, ma se la lentezza dipendesse da altre formule presenti sul foglio allora il gioco forse non vale la candela
Avatar utente
Anthony47
Moderatore
 
Post: 17445
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: Formula con CONFRONTA?

Postdi papiriof » 22/06/20 08:09

Come al solito hai ragione ... scrivevi :
La formula va inserita in forma matriciale nell'intervallo AH2-AH121:
-selezionare AH2:AH121
-scrivere la formula nella "barra della formula"
-confermare con Contr-Maiusc-Enter, non col solo Enter

Ebbene io facevo :selezionavo AH2 scrivevo la formula la confermavo con Contr-Maiusc-Enter quindi la strisciavo in basso-
Questa mattina ho cancellato la colonna AH ho seleionato AH2:AH121 e confermato con Contr-Maiusc-Enter, non col solo Enter
Adesso va bene!!!!! come è possibile? le due operazioni sono in sostanza simili ma evidentemente non uguali :?: :?:
Win 7+Office 2010
papiriof
Utente Senior
 
Post: 270
Iscritto il: 16/02/10 13:23

Re: Formula con CONFRONTA?

Postdi Anthony47 » 22/06/20 11:51

come è possibile? le due operazioni sono in sostanza simili ma evidentemente non uguali
Purtroppo l'idea della formula "a matrice" e' fortemente alterata dal frequente uso di formule internamente a matrice ma fatte per restituire un unico valore.
La mia Function invece restituisce i risultati di tutte le 100 righe; ripetere la formula su 100 righe vuol dire calcolare 100 volte gli stessi risultati e prenderne poi solo il primo.
Le conseguenze le misuri col cronometro...
Avatar utente
Anthony47
Moderatore
 
Post: 17445
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: Formula con CONFRONTA?

Postdi papiriof » 01/07/20 12:06

Buongiorno a tutti ho provato ad adattare la formula di Anthony volendo ripetere gli stessi criteri ad una lotteria con 6 numeri per ogni estrazione l'intervallo da sottoporre alla formula è composto non da " 5ruote" percon 5estratti ciascuna (tot 25 numeri) ma da 2ruote di 6numeri ciascuna (tot 12 numeri) . Ho armeggiato ma il massimo che ho potuto fare è visibile nel file allegato.Sarei veramente grato di capire il senso della funzione con qualche commento della stessa


http://www.filedropper.com/slimsvizzambovirt
Win 7+Office 2010
papiriof
Utente Senior
 
Post: 270
Iscritto il: 16/02/10 13:23

Re: Formula con CONFRONTA?

Postdi Anthony47 » 01/07/20 14:43

La Function PapirAxB restituiva il numero di riga se i dati del quesito iniziale rispettavano le clausole date nel quesito iniziale; per fare il calcolo usavo due vettori (haArr e hbArr) di lunghezza pari al numero degli estratti, in cui "spuntavo" la posizione degli estratti che rispettavano le condizioni; alla fine controllavo quante spunte fossero presenti e su questa base scrivevo il valore di uscita di quella riga.

La nuova funzione che vuoi creare immagino debba verificare le stesse condizioni quindi, partendo dal codice della PapirAxB ho elaborato questa variante:
Codice: Seleziona tutto
Function PapirAnB(ByVal aaVal As Long, ByVal bbVal As Long, ByRef myRan As Range, ByVal Estraz As Long) As Variant
'Vedi http://www.pc-facile.com/forum/viewtopic.php?f=26&t=111409
Dim oArr(), haArr(), hbArr(), I As Long, J As Long
Dim wArr, nEstr As Long
'
nEstr = myRan.Columns.Count / Estraz            'Quante estrazioni nella tabella
'
Mytim = Timer
ReDim oArr(1 To myRan.Rows.Count)               'Crea Output Array con abbastanza celle
wArr = myRan.Value                              'Copia l'intervallo dati
For I = 1 To myRan.Rows.Count
ReDim haArr(0 To myRan.Columns.Count / Estraz)       'Erase haArr
ReDim hbArr(0 To myRan.Columns.Count / Estraz)       'Erase hbArr
    For J = 1 To myRan.Columns.Count            'Scansiona le colonne
        If wArr(I, J) = aaVal Or wArr(I, J) = bbVal Then
            If hbArr(J Mod nEstr) = 0 Then haArr(J Mod nEstr) = 1       'Spunta haArr sulla colonna dell'estratto (1°,2°,..)
        End If
        If wArr(I, J) = bbVal Then
            If haArr(J Mod nEstr) = 0 Then hbArr(J Mod nEstr) = 1       'Spunta hbArr sulla colonna dell'estratto
        End If
    Next J
    'Verifica quante spunte ci sono
    If (Application.WorksheetFunction.Sum(haArr) > 0 And Application.WorksheetFunction.Sum(hbArr) > 0) Or _
    Application.WorksheetFunction.Sum(haArr) > 1 Or Application.WorksheetFunction.Sum(hbArr) > 1 Then
        'Se ci sono le spunte giuste, compila array di output
        oArr(I) = I + myRan.Cells(1, 1).Row - 1
    End If
Next I
'Restituisce l'array di output
PapirAnB = Application.WorksheetFunction.Transpose(oArr)
''Debug.Print "B", Format(Timer - Mytim, "0.00")
End Function

Noterai qualche commento in piu' per indicare sinteticamente lo scopo delle istrzuzioni.
Rispetto alla versione precedente, e' necessario indicare nella formula quante estrazioni sono presenti nella tabella (2, secondo l'ultimo tuo messaggio); da questo parametro il codice calcolera' quanti sono gli estratti per ogni estrazione (6, in questo esempio; 5 nell'esempio iniziale) prima di procedere con il calcolo dei risultati.
Da quanto detto, la formula che potrai usare sara':
Codice: Seleziona tutto
=PapirAnB(xx;zz;I4:T153;2)


Puoi usare la stessa PapirAnB nel file che aveva 5 estratti e 5 estrazioni; in questo caso la formula da usare sara'
Codice: Seleziona tutto
=PapirAnB(aa;bb;I2:AG121;5)

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

Re: Formula con CONFRONTA?

Postdi papiriof » 01/07/20 16:13

Inutile dire che è perfetta , ma il regalo più bello sono i commenti spero di riuscire a capirli -
Ancora grazie!!!! :D
Win 7+Office 2010
papiriof
Utente Senior
 
Post: 270
Iscritto il: 16/02/10 13:23


Torna a Applicazioni Office Windows


Topic correlati a "Formula con CONFRONTA?":


Chi c’è in linea

Visitano il forum: Nessuno e 27 ospiti