[Excel] Ordinare dati in ordine crescente con VBA

[mikecodadilupo]

salve a tutti.....ho un problema...

ho una matrice di numeri interi da A1 a G7.

Mi servirebbe poter ordinare i primi 5 numeri più grandi di tale matrice nelle celle B10:B14.

Se non usassi VBA potrei usare la formula =GRANDE("A1:G7";k), ma poichè voglio fare partire l'ordinamento con un pulsante di comando ecco che in VBA la corrispondente funzione di GRANDE (che credo sia LARGE) mi da errore....

come posso risolvere il problema??

ps non uso la funzione GRANDE perchè in un passaggio successivo mi servirebbe ordinare ben 25 elementi e quindi sarebbe più comodi un ciclo FOR NEXT

[Alexsandra]

Ho fatto questo,ma non ho capito bene qual'è il tuo problema che accenni nell'ultima riga prova questo codice

Codice: Seleziona tutto

Private Sub Worksheet_Activate()
Set area = ActiveSheet.UsedRange
X = area.Columns.Count
For Colonna = 1 To X
 Range(Cells(1, Colonna), Cells(1, Colonna).End(xlDown)).Select
Selection.Sort Key1:=Cells(1, Colonna), Order1:=xlAscending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom
Next
End Sub

per provarlo l'ho messo in activate nel foglio 1 e ti ordina i tuoi dati,se hai bisogni particolari che non ho recepito spiegameli meglio.

[mikecodadilupo]

grazie tante per il suggerimento...anche questo mi gioverà parecchio...ma non era quello che chiedevo....
proverò a spiegare meglio il mio problema...
dunque....ho degli elementi numerici di una matrice [A1:C3].....che so....
3 5 1
2 6 3
1 8 9
io vorrei adesso, ad esempio, che nelle celle D3,D4,D5 vengano riportati rispettivamente il valore più grande di questa matrice, il secondo valore più grande e il terzo valore più grande....in modo tale da ottenere
9 [in D3]
8 [in D4]
6 [in D5]
ovviamente per una matrice 3*3 potrei usare la funzione GRANDE in ogni cella.....ma per matrici 20*20 di cui mi serve ordinare 100 valori mi occorre di sicuro VBA.....non posso mettermi a scrivere per ogni cella la relativa funzione....o almeno credo che esista un metodo più veloce...
ma il fatto è che con VBA non riesco a farlo partire......
spero di essere stato piu chiaro...grazie anticipatamente

[archimede]

per matrici 20*20 di cui mi serve ordinare 100 valori mi occorre di sicuro VBA.....non posso mettermi a scrivere per ogni cella la relativa funzione....o almeno credo che esista un metodo più veloce...

Se ad esempio la tua matrice è A1:Z20 e vuoi i primi 100 valori nelle celle AA1:AA100, scrivi in AA1 la formula =GRANDE($A$1:$Z$20;RIF.RIGA()) e poi la copi fino a AA100.

HTH.

Alessandro

[cassioli]

per matrici 20*20 di cui mi serve ordinare 100 valori mi occorre di sicuro VBA.....non posso mettermi a scrivere per ogni cella la relativa funzione....o almeno credo che esista un metodo più veloce...

Se ad esempio la tua matrice è A1:Z20 e vuoi i primi 100 valori nelle celle AA1:AA100, scrivi in AA1 la formula =GRANDE($A$1:$Z$20;RIF.RIGA()) e poi la copi fino a AA100.

Forte...

E se invece ho la matrice qui sotto, e voglio riordinarne una copia in base al punteggio?
Germania 6
Polonia 7
Ecuador 1
Costa Rica 2

Si puo' fare senza VBA?

[mikecodadilupo]

grazie tante

mi sfuggiva il passaggio di Rif.Riga() che in VBA diventa Row().
Adesso tutto funziona.....ho creato un pulsante di comando e tutto fila....

se ad esempio ho valori in A1:F10 e voglio i primi /Z/ valori ordinati nelle prime /Z/ celle della colonna n il codice VBA è:

Codice: Seleziona tutto

Private Sub CommandButton1_Click()
  Range("n1").Select
    ActiveCell.Formula = "=LARGE(A1:F10,ROW())"
    Range("n1:n/Z/").Select
    Selection.FormulaArray = "=LARGE(A1:F10,ROW())"
End Sub

e tutto va senza problemi

[mikecodadilupo]

vorrei porre un altro quesito.....

io ho nelle celle A1:L1, venti numeri che so....A1=1000, B1=480, C1=660, ecc...
io vorrei adesso che nelle celle della riga 2 vengano riportati i valori delle corrispettive celle superiori divisi per due.....cioè A2=500, B2=240, C2=330, ec...
vorrei che nella riga 3 ci fossero i valori delle celle della prima riga ma divise per tre, cioè A3=333,33 B3=160, C3=220 ecc
e cosi di seguito un divisore per 4 nella riga 4, per 5 nella riga 5, fino alla riga 10.
vorrei inoltre che dalla prima riga alla decima riga vengano selezionati in Rosso i venti numeri più grandi di tutta la matrice A1:L10.
come posso procedere senza scomodare VBA???

grazie

[cassioli]

per matrici 20*20 di cui mi serve ordinare 100 valori mi occorre di sicuro VBA.....non posso mettermi a scrivere per ogni cella la relativa funzione....o almeno credo che esista un metodo più veloce...

Se ad esempio la tua matrice è A1:Z20 e vuoi i primi 100 valori nelle celle AA1:AA100, scrivi in AA1 la formula =GRANDE($A$1:$Z$20;RIF.RIGA()) e poi la copi fino a AA100.

Forte...

E se invece ho la matrice qui sotto, e voglio riordinarne una copia in base al punteggio?
Germania 6
Polonia 7
Ecuador 1
Costa Rica 2

Si puo' fare senza VBA?

Ah, trovato... ma che cribbio di formula!!!

Codice: Seleziona tutto

=SCEGLI(CONFRONTA(GRANDE($H$5:$H$8;1);$H$5:$H$8;0);$B$5;$B$6;$B$7;$B$8)
=SCEGLI(CONFRONTA(GRANDE($H$5:$H$8;2);$H$5:$H$8;0);$B$5;$B$6;$B$7;$B$8)
=SCEGLI(CONFRONTA(GRANDE($H$5:$H$8;3);$H$5:$H$8;0);$B$5;$B$6;$B$7;$B$8)
=SCEGLI(CONFRONTA(GRANDE($H$5:$H$8;4);$H$5:$H$8;0);$B$5;$B$6;$B$7;$B$8)


I nomi sono in b5-b8 e i punti in h5-h8.

[archimede]

io ho nelle celle A1:L1, venti numeri che so....A1=1000, B1=480, C1=660, ecc...

io vorrei adesso che nelle celle della riga 2 vengano riportati i valori delle corrispettive celle superiori divisi per due.....cioè A2=500, B2=240, C2=330, ec...

vorrei che nella riga 3 ci fossero i valori delle celle della prima riga ma divise per tre, cioè A3=333,33 B3=160, C3=220 ecc

=A$1/RIF.RIGA()

HTH.

Alessandro

[mikecodadilupo]

dunque.....ho risolto il problema con VBA....
ho costruito una riga di numeri da A1 a J1 ed un pulsante col seguente codice che mi restituisce i 20 numeri più grandi della matrice che si viene a creare

Codice: Seleziona tutto

Private Sub CommandButton1_Click()
Set area = ActiveSheet.UsedRange  'Definisci il numero di colonne della matrice
    X = area.Columns.Count 'Definisci il numero di righe della matrice
    Y = area.Rows.Count
Dim massimo_valore As Single  'Effettua i vari rapporti per ogni riga
  For colonna = 0 To X - 1
    Range("A1").Offset(1, colonna).Select
    ActiveCell.FormulaR1C1 = "=R[-1]C/ROW()"
    Range("A2:A10").Offset(1, colonna).Select
    Selection.FormulaArray = "=R[-1]C/ROW()"
  Next
'Assegna il 20° valore più grande ad una cella
Range("A20").Value = "=large(A1:J11,20)"
massimo_valore = Range("A20").Value
Range("a1").Select
'Colora di rosso le celle conteneti i valori dal 1° al 20°
 For riga = 0 To Y - 8
    For colonna = 0 To X - 1
          If ActiveCell.Value >= massimo_valore Then
             ActiveCell.Interior.Color = vbRed
          Else
             ActiveCell.Interior.Color = vbWhite
          End If
       ActiveCell.Offset(0, 1).Select
   Next
        Cells(ActiveCell.Row + 1, 1).Select
 Next
End Sub

tutto funziona alla perfezione...!! ci sono secondo me però due difetti nel codice:
1. nella parte di codice

Codice: Seleziona tutto

   Range("A20").Value = "=large(A1:J11,20)"
   massimo_valore = Range("A20").Value

come posso assegnare il valore della funzione direttamente a massimo_valore senza dover passare per la cella A20 che non mi interessa?? (in modo da evitare anche quel brutto FOR riga=0 to Y-8)

2. sempre nella funzione large....come posso fare per non definire io le dimensioni della matrice (nel mio caso sono costretto a dire [A1:J11]) ma sia direttamente VBA a capirla dai valori X e Y definiti all'inizio del codice???

[archimede]

vorrei inoltre che dalla prima riga alla decima riga vengano selezionati in Rosso i venti numeri più grandi di tutta la matrice A1:L10.

Dai il focus alla cella A1, seleziona tutta la matrice quindi Formato --> Formattazione Condizionale --> La formula è --> =RANGO(A1;$A$1:$J$11)<=20

HTH.

Alessandro

[mikecodadilupo]

grazie tante...con la funzione RANGO tutto si semplifica alla grande...mi sorge un ultimo dubbio....

se in A1 ho 1000 e in A2,A3,A4,ecc... volgio la sua metà, il suo terzo, il suo quarto....uso tranquillamente

=A1/rif.riga()

e lo copio su tutte le celle che mi interessano...

ma se il mio numero di partenza lo avessi ad esempio nella cella C8....posso usare ancora rif.riga() in C9 per ottenere la metà di C8???? o in C10 per ottenere 1/3 di C8???

e se si...in che modo???

[cassioli]

grazie tante...con la funzione RANGO tutto si semplifica alla grande...mi sorge un ultimo dubbio....

se in A1 ho 1000 e in A2,A3,A4,ecc... volgio la sua metà, il suo terzo, il suo quarto....uso tranquillamente

=A1/rif.riga()

e lo copio su tutte le celle che mi interessano...

ma se il mio numero di partenza lo avessi ad esempio nella cella C8....posso usare ancora rif.riga() in C9 per ottenere la metà di C8???? o in C10 per ottenere 1/3 di C8???

e se si...in che modo???

usa rif.riga()-x , con x=quellochetipare

[archimede]

usa (rif.riga()-x) , con x=quellochetipare

;)

Alessandro

[cassioli]

usa (rif.riga()-x) , con x=quellochetipare

;)

Alessandro

uff... ti avevo quasi battuto...

[mikecodadilupo]

grazie tante.....

per adesso ogni mio dubbio è sperduto.....ma domani me ne verranno di più profondi

[mikecodadilupo]

Forte...

E se invece ho la matrice qui sotto, e voglio riordinarne una copia in base al punteggio?
Germania 6
Polonia 7
Ecuador 1
Costa Rica 2

Si puo' fare senza VBA?

Ah, trovato... ma che cribbio di formula!!!

Codice: Seleziona tutto

=SCEGLI(CONFRONTA(GRANDE($H$5:$H$8;1);$H$5:$H$8;0);$B$5;$B$6;$B$7;$B$8)
=SCEGLI(CONFRONTA(GRANDE($H$5:$H$8;2);$H$5:$H$8;0);$B$5;$B$6;$B$7;$B$8)
=SCEGLI(CONFRONTA(GRANDE($H$5:$H$8;3);$H$5:$H$8;0);$B$5;$B$6;$B$7;$B$8)
=SCEGLI(CONFRONTA(GRANDE($H$5:$H$8;4);$H$5:$H$8;0);$B$5;$B$6;$B$7;$B$8)


I nomi sono in b5-b8 e i punti in h5-h8


ma perchè usare questa gran formula e non Dati->Ordina dal menù di Excel??? Qual'è la differenza???

[Alexsandra]

E se invece ho la matrice qui sotto, e voglio riordinarne una copia in base al punteggio?

Con quella formulistica riordini la matrice in base al punteggio ,cioè fai un'estrazione di dati in base ad un criterio,come dici tu la riordini e basta.
Non riesci a stabilire una classifica in base al punteggio assegnato.

[mikecodadilupo]

vorrei particolarizzare il problema precedente.

supponiamo che io abbia una matrice A1:M50 di interi.

finora abbiamo riordinato dei dati relativamente a tutta la matrice.

il problema adesso è:

come posso riordinare (oppure prenderne gli X più grandi) i dati non di tutta la matrice ma di una sottomatrice (ad esempio C3:G8) dove gli estremi (C3 e G8 nel nostro caso) vengano richiesti all'utente con due MsgBox del tipo

"Inserisci prima cella della sottomatrice" e l'utente mette C3
"Inserisci ultima cella della matrice" e l'utente mette G8

?????????????

[-Io-]

per matrici 20*20 di cui mi serve ordinare 100 valori mi occorre di sicuro VBA.....non posso mettermi a scrivere per ogni cella la relativa funzione....o almeno credo che esista un metodo più veloce...

Se ad esempio la tua matrice è A1:Z20 e vuoi i primi 100 valori nelle celle AA1:AA100, scrivi in AA1 la formula =GRANDE($A$1:$Z$20;RIF.RIGA()) e poi la copi fino a AA100.

Forte...

E se invece ho la matrice qui sotto, e voglio riordinarne una copia in base al punteggio?
Germania 6
Polonia 7
Ecuador 1
Costa Rica 2

Si puo' fare senza VBA?

Ah, trovato... ma che cribbio di formula!!!

Codice: Seleziona tutto

=SCEGLI(CONFRONTA(GRANDE($H$5:$H$8;1);$H$5:$H$8;0);$B$5;$B$6;$B$7;$B$8)
=SCEGLI(CONFRONTA(GRANDE($H$5:$H$8;2);$H$5:$H$8;0);$B$5;$B$6;$B$7;$B$8)
=SCEGLI(CONFRONTA(GRANDE($H$5:$H$8;3);$H$5:$H$8;0);$B$5;$B$6;$B$7;$B$8)
=SCEGLI(CONFRONTA(GRANDE($H$5:$H$8;4);$H$5:$H$8;0);$B$5;$B$6;$B$7;$B$8)


I nomi sono in b5-b8 e i punti in h5-h8.

E se volessi far comparire accanto ai nomi anche i rispettivi valori?