[EXCEL] Confronto tra dati in colonne (array dinamici)

[mikecodadilupo]

Buongiorno a tutti,
ho il seguente problema.
Nelle colonne A,B,C,D,E,F,G e poi X e Y ho dei numeri; le righe sono in numero imprecisato.

Ad esempio:
A B C D E F G X Y
1 2,3 3,1 9 7,4 6 8,7 1 1
1,2 2 4 6 5,8 8 5,2 1 0

e così via.

Ciò di cui avrei bisogno è un pulsante di comando che mi conti il numero di righe che soddisfano le tre seguenti simultanee condizioni:
a) il numero nella colonna A è maggiore sia di B che di C;
b) il numero nella colonna D è minore del numero nella colonna E;
c) il numero nella colonna F è maggiore del numero nella colonna G.

Inoltre, una volta noto il numero di righe che soddisfano la precedente condizione, vorrei poi sapere:
1. in quante di esse i numeri in X e Y sono entrambi pari a 1;
2. in quante di esse i numeri in X e Y sono entrambi pari a 0.

Le righe sono in continua evoluzione nel senso che due calcoli consecutivi non hanno lo stesso numero di righe.

A dire il vero il problema l'ho risolto in maniera molto grossolana con un ciclo fino alla LastRow e poi con una serie interminabile di IF...ELSE variamente nidificati.
Di funzionare diciamo che funziona, solo che è molto pesante e lento, anche perchè si tratta di migliaia di righe.
Usando una metafora è come se usassi un cannone per sparare ad un uccellino: di prenderlo lo prendo, ma c'era bisogno di disturbare l'artiglieria pesante per andare a caccia??

Ho letto da qualche parte che la funzione ARRAY è assegnabile dinamicamente. Posso allora creare tanti array dinamici quante sono le colonne e quindi paragonare semplicemente gli array A,B e C con degli operatori logici all'array D,E, all'array F,G ed all'array X,Y??? Se si, in che modo??

Oppure quale altra strada alternativa potrei seguire per velocizzare i calcoli??
Grazie...

[Flash30005]

Vedi se ti va bene questa macro da abbinare ad un pulsante

Codice: Seleziona tutto

Sub ContaRighe()
RigheA = Worksheets("Foglio1").Range("A" & Rows.Count).End(xlUp).Row
RigheB = Worksheets("Foglio1").Range("B" & Rows.Count).End(xlUp).Row
RigheC = Worksheets("Foglio1").Range("C" & Rows.Count).End(xlUp).Row
RigheD = Worksheets("Foglio1").Range("D" & Rows.Count).End(xlUp).Row
RigheE = Worksheets("Foglio1").Range("E" & Rows.Count).End(xlUp).Row
RigheF = Worksheets("Foglio1").Range("F" & Rows.Count).End(xlUp).Row
RigheG = Worksheets("Foglio1").Range("G" & Rows.Count).End(xlUp).Row

If RigheA > RigheB And RigheA > RigheC And RigheD < RigheE And RigheF > RigheG Then
Val1 = 0
Val0 = 0
For I = 1 To Worksheets("Foglio1").Range("X" & Rows.Count).End(xlUp).Row
    If Range("X" & I).Value = 1 And Range("Y" & I).Value = 1 Then Val1 = Val1 + 1
    If Range("X" & I).Value = 0 And Range("Y" & I).Value = 0 Then Val0 = Val0 + 1
Next I
MsgBox "Valori 1 = " & Val1 & " e Valori 0 = " & Val0
End If

End Sub


Ciao

[Anthony47]

Pero' io lo farei con sole formule:
-in J2 (o altra colonna libera)

Codice: Seleziona tutto

=(A2>B2)*(A2>C2)*(D2<E2)*(F2>G2)

-in K2 (idem)

Codice: Seleziona tutto

=J2*(X2=1)*(Y2=1)

-in L2 (idem)

Codice: Seleziona tutto

=J2*(X2=0)*(Y2=0)

Poi copi queste formule in basso quanto vuoi, e infine in 3 celle libere calcoli quello che cerchi:

Codice: Seleziona tutto

=somma(J:J)
=somma(K:K)
=somma(L:L)

Puo' andare?
Se non hai 3 colonne libere useremo invece Matr.Somma.Prodotto per ottenere direttamente i 3 risultati.
Rispettivamente:

Codice: Seleziona tutto

=MATR.SOMMA.PRODOTTO((A2:A10000>B2:B10000)*(A2:A10000>C2:C10000)*(D2:D10000<E2:E10000)*(F2:F10000>G2:G10000))
=MATR.SOMMA.PRODOTTO((A2:A10000>B2:B10000)*(A2:A10000>C2:C10000)*(D2:D10000<E2:E10000)*(F2:F10000>G2:G10000);(X2:X10000=1)*(Y2:Y10000=1))
=MATR.SOMMA.PRODOTTO((A2:A10000>B2:B10000)*(A2:A10000>C2:C10000)*(D2:D10000<E2:E10000)*(F2:F10000>G2:G10000);(X2:X10000=0)*(Y2:Y10000=0))


(la formula copre fino a 10000 righe; adatta se necessario)

Ciao

[mikecodadilupo]

Scusate, rileggendo mi sono accorto di essere stato ambiguo.

@Flash
Ho dei numeri su 7 colonne (che poi diventano 9 colonne con X e Y), e la lunghezza di queste colonne è identica. Una matrice 7*1000 ad esempio.
Di queste mille righe io vorrei contare solo quelle in cui accade contemporaneamente che:
a) l'elemento a(i) è maggiore di b(i) e di c(i);
b) l'elemento d(i) è minore di f(i);
c) l'elemento f(i) è maggiore di g(i).

Una riga del tipo: 3,4 - 2 - 0,1- 6 - 9 - 10 - 8 mi viene pertanto conteggiata, mentre una riga del tipo: 3,4 - 2 - 0,1- 6 - 9 - 8 - 10 non mi viene conteggiata in quanto l'elemento nella colonna f è minore di quello nella colonna g.

Spero di essere stato adesso più chiaro.


@Antony47
Si Antony, diciamo che la tua soluzione è quasi quanto cerco.
Vorrei porti una domanda:
è possibile evitare di dover copiare manualmente la formula che proponi nella colonna J (che ho libera) a mano (non lo dico per pigrizia ma perchè si tratta di file enormi)?? Cioè, si può usare il contatore di righe di cui parla Flash per creare una funzione ed un ciclo che mi ricopia le formule dalla prima riga fino all'ultima contenente i numeri??
Purtroppo non vado molto d'accordo con l'assegnazione di formule in VBA.

[Flash30005]

Vedi ora

Codice: Seleziona tutto

Sub ContaValori()
Val1 = 0
Val0 = 0
RigheSi = 0
For I = 1 To Worksheets("Foglio1").Range("A" & Rows.Count).End(xlUp).Row
ValA = Range("A" & I).Value
ValB = Range("B" & I).Value
ValC = Range("C" & I).Value
ValD = Range("D" & I).Value
ValE = Range("E" & I).Value
ValF = Range("F" & I).Value
ValG = Range("G" & I).Value
If ValA > ValB And ValA > ValC And ValD < ValE And ValF > ValG Then
RigheSi = RigheSi + 1
End If
    If Range("X" & I).Value = 1 And Range("Y" & I).Value = 1 Then Val1 = Val1 + 1
    If Range("X" & I).Value = 0 And Range("Y" & I).Value = 0 Then Val0 = Val0 + 1

Next I
MsgBox "N. Righe Ok = " & RigheSi & " Valori 1 = " & Val1 & " e Valori 0 = " & Val0

End Sub

Non so se il risultato lo vuoi come messaggio oppure inserirlo in un'altra parte del foglio ma avendo le variabili ore le puoi sistemare dove vuoi
Ciao

[mikecodadilupo]

Si, grazie tante!
L'unica cosa da correggere è mettere gli ultimi due IF all'interno dell'IF principale, dato che la seconda scelta (X e Y) va fatta non su tutte le righe, ma solo su quelle già filtrate dalla prima condizione.
Ancora grazie

[Anthony47]

Per quanto riguarda l' uso delle formule:
-le colonne libere necessarie sono 3, con la prima soluzione
-le formule le metti una volta e poi non le tocchi piu' (basta metterle su una lunghezza sufficientemente esagerata; anche su un foglio diverso da quello dei tuoi dati, anche su un file diverso...)
-se usi Matr.Somma.Prodotto non devi ricopiare niente; anche qui basta impostare un range sufficientemente esagerato.
-se invece vuoi usare Matr.Somma.Prodotto solo sulle righe che hanno un valore, allora potresti:
--mettere in una cella libera la formula =CONTA.VALORI(A:A) per contare quanti dati hai in colonna A; io ho usato N1
--poi userai rispettivamente:

Codice: Seleziona tutto

=MATR.SOMMA.PRODOTTO((SCARTO(A2;0;0;N1;1)>SCARTO(B2;0;0;N1;1))*(SCARTO(A2;0;0;N1;1)>SCARTO(C2;0;0;N1;1))*(SCARTO(D2;0;0;N1;1)<SCARTO(E2;0;0;N1;1))*(SCARTO(F2;0;0;N1;1)>SCARTO(G2;0;0;N1;1)))

=MATR.SOMMA.PRODOTTO((SCARTO(A2;0;0;N1;1)>SCARTO(B2;0;0;N1;1))*(SCARTO(A2;0;0;N1;1)>SCARTO(C2;0;0;N1;1))*(SCARTO(D2;0;0;N1;1)<SCARTO(E2;0;0;N1;1))*(SCARTO(F2;0;0;N1;1)>SCARTO(G2;0;0;N1;1));(SCARTO(X2;0;0;N1;1)=1)*(SCARTO(Y2;0;0;N1;1)=1))

=MATR.SOMMA.PRODOTTO((SCARTO(A2;0;0;N1;1)>SCARTO(B2;0;0;N1;1))*(SCARTO(A2;0;0;N1;1)>SCARTO(C2;0;0;N1;1))*(SCARTO(D2;0;0;N1;1)<SCARTO(E2;0;0;N1;1))*(SCARTO(F2;0;0;N1;1)>SCARTO(G2;0;0;N1;1));(SCARTO(X2;0;0;N1;1)=0)*(SCARTO(Y2;0;0;N1;1)=0))


Ciao

[mikecodadilupo]

Si, funziona alla perfezione.
Un altro modo ulteriore che ho trovato riflettendoci su è stato quello di creare 3 nuove colonne:
1. la prima mi da 1 nelle righe in cui la prima condizione è vera e 0 nelle righe in cui la prima condizione è falsa;
2. la seconda mi da 1 nelle righe in cui la seconda condizione è vera e 0 nelle righe in cui la seconda condizione è falsa;
3. la terza mi da 1 nelle righe in cui la terza condizione è vera e 0 nelle righe in cui la terza condizione è vera.

A quel punto, da un AND tra quelle tre colonne trovo tutte e sole le righe che soddisfano le mie condizioni.

Ho creato infatti una userform con checkbutton e optionbutton in cui scelgo di volta in volta quali debbano essere le condizioni di maggiore e minore tra i tre gruppi (in tal modo posso vedere le righe in cui a(i)<b(i), a(i)>c(i), e cosi via) con una semplice selezione di pulsanti.
Leggere questo forum è sempre un piacere..ciao!

[mikecodadilupo]

Ri-salve a tutti,
sempre su questo tema ho un problemone insormontabile.

Supponiamo nel foglio 1 che in A ho il nome delle sezioni di una scuola (a,b,c,ecc...), in B i nomi e cognomi dei ragazzi, e in C,D,E le loro medie al primo, secondo quadrimestre e finali.

Passiamo al foglio2. Qui ho una cella dove inserisco la votazione minima, una cella dove inserisco la votazione massima ed un pulsante di comando.
Ogni qualvolta inserisco ad esempio 6,3 nella prima cella e 7,8 nella seconda cella, mi viene restituito il numero di ragazzi di tutte le sezioni la cui votazione è compresa tra quei valori.
Fin qui funziona tutto...

Il problema nasce allorchè inserisco una casella combinata da dove poter selezionare, anzichè tutti gli studenti della scuola, solo quelli di una data sezione.
Il codice funziona, non vi sono errori...solo che, una volta attivata la casella ad una generica sezione, il numero di studenti che viene restituito (studenti di una specifica sezione e con uno specifico range di votazione) non è più quello esatto....
Mi ritrovo cioè come risposta dei valori sballati....

Il codice che ho utilizzato è il seguente:

Private Sub ComboBox1_Change()

studenti_mio_interesse = 0


quotazione_minima = Worksheets("foglio2").Range("C5").Value 'Qui inserisco il voto minimo del mio criterio di ricerca
quotazione_massima = Worksheets("foglio2").Range("D5").Value ' Qui inserisco il voto massimo del mio criterio

sezione = Worksheets("foglio2").Range("g4").Value 'A G4 è collegata la combo collegata alla colonna A del foglio1


lastrow = Worksheets("Foglio1").Range("A" & Rows.Count).End(xlUp).Row


For i = 4 To lastrow ' Parte da 4 perche vi sono intestazioni nelle prime 3 righe

a = Worksheets("Foglio1").Range("C" & i).Value 'Voto minimo dell'i-esimo studente
b = Worksheets("Foglio1").Range("D" & i).Value 'Voto massimo dell'i-esimo studente


val_sezione = Worksheets("Foglio1").Range("A" & i).Value

If a >= quotazione_minima And b <= quotazione_massima And val_sezione = sezione Then
studenti_mio_interesse = studenti_mio_interesse + 1
End If



Next

Worksheets("Foglio2").Range("C6").Value = studenti_mio_interesse

End Sub



Questo è il codice. Tutto funziona fintanto che filtro solo in base ai numeri...non appena inserisco la terza condizione nell'IF, quella relativa alla sezione, se gli studenti in una data sezione sono ad esempio 22, lui mi restitusice un valore inammissibile, tipo 33.

Dove sbaglio??

ps. nella casella combinata con quale proprietà disabilito i doppioni??

[Anthony47]

Sono troppo pigro per crearmi un ambiente di prova, potresti allegare un file di esempio gia' fatto?
Ciao.

[mikecodadilupo]

Buongiorno Antony...

Allora, il problema l'ho risolto per tentativi, anche se non ci ho capito molto. Infatti mi è bastato disabilitare il filtro automatico nel foglio 1 (che avevo messo per verificare i risultati ottenuti nel foglio 2 con quelli "visivi") che tutto procede adesso regolarmente...

L'unica cosa che non riesco ad impostare è la non ripetibilità degli elementi A,B,C,ecc... delle sezioni nella casella combinata. Supponendo che vi siano 1000 studenti e solo 4 sezioni, la casella combinata mi da 1000 righe tutte doppione di A,B,C e D, anzichè darmi solo 4 righe.

Questo problema potrei forse risolverlo ricorrendo ad un range supplementare dove scrivo solo (A,B,C,D) e poi collego la casella combinata a questo range anzichè alla colonna A del foglio1.

Ma mi sembra una cosa ridondante....sicuramente c'è qualche proprietà (che non vedo) che permette di togliere i doppioni da una lista...

[Anthony47]

Non conosco una proprieta' per indicare "solo valori unici" (ma questo non significa che non esiste); lo puoi risolvere creando in una colonna libera una lista di "unici" tramite filtro avanzato (spunta Copia in altra posizione, spunta Copia univoca dei record), definendo poi un range dinamico che comprende questi dati), e usando infine il nome di questo range come valore per la proprieta' ListFillRange del combobox.
La generazione e l' aggiornamento della tabella "unici" la puoi anche fare con una macro simile alla seguente:

Codice: Seleziona tutto

Private Sub ComboBox1_GotFocus()
    Range("A:A").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
        "BF1"), Unique:=True
End Sub


Questa lavora sui dati presenti in col A e crea l' elenco in colonna BF, e aggiorna l' elenco tutte le volte che clicchi sulla combobox.
Il range dinamico lo definisci tramite Menu /Inserisci /Nome /definisci e impostando "Riferito a" con questa formula:

Codice: Seleziona tutto

=SCARTO(Foglio1!$BF$2;0;0;CONTA.VALORI(Foglio1!$BF:$BF)-1;1)


(se crei la lista in altro foglio o colonna, adatterai ovviamente macro e formula, rispettando la sintassi data)

Ciao, fai sapere.