ESTRAZIONE RANDOM DA ELENCO

[realkaranka]

Ciao a tutti,

volevo porgervi un quesito!

Vorrei realizzare un file excel che operi in questo modo.

Ho 2 fogli. Uno dei 2 fogli contiene un elenco di nomi dislocati su una colonna. Quello che vorrei è che sull'altro foglio ci sia una cella in cui l'utente premendo invio estrarrà, in maniera casuale, un nome dell'elenco situato sul foglio precedente. Ovviamente ogni volta che un nome viene estratto non deve essere più presente nel foglio precedente, o meglio, ogni nome estratto viene messo in un'altra colonna in modo tale da essere discriminato dai nomi che ancora devono essere estratti.

Ho visto l'esempio tombola ma n ho avuto il tempo di analizzarlo. Qll che vorrei è una cosa molto simile alla tombola solo che in questo caso ci sono dei nomi e l'elenco non è composto da 90 righe ma in qst caso saranno circa 600.

Fatemi sapere se è fattibile...

Grazie mille anticipatamente[/u]

[Anthony47]

Si, si puo’ fare con una macro.
Segui queste istruzioni:
-sul foglio Sorgente dei nomi assegni in nome “Inizio” alla cella che contiene il primo nominativo
-determini quale e’ il numero di nominativi contenuti sul foglio sorgente; se l’ elenco e’ in col A puoi usare qualcosa come

Codice: Seleziona tutto

TotNomi = Range("A65536").End(xlUp).Row - Range("Inizio").Row + 1

-vai sul secondo foglio, dove in prima colonna con la funzione RND metti un numero random compreso tra 0 e il numero di nominativi calcolati sopra; prima di accettare il numero random calcolato, bisogna controllare se nell’ elenco quel numero esiste gia’; lo puoi fare con una sequenza tipo questa, che ti restituisce nella variabile NumRand un numero random unico:

Codice: Seleziona tutto

AncoRand:
NumRand = Int((TotNomi + 1) * Rnd)
If Application.WorksheetFunction.CountIf(Range("A1:A1000"), NumRand) > 0 Then GoTo AncoRand

(la formula presuppone max 1000 estrazioni; eventualmente amplia il range in formula)
-incolli questo numero nella prima cella libera di col A; puo’ andare bene

Codice: Seleziona tutto

Range("A65536").end(xlUp).offset(1,0).value = NumRand

-accanto a questo numero inserisci il nominativo che nel sorgente corrisponde a quel numero:

Codice: Seleziona tutto

Range("A65536").end(xlUp).offset(0,1).value = Range("Inizio").offset(NumRand,0).value

Se vuoi fare una estrazione alla volta ti fermi qui, altrimenti inserisci tutto in un ciclo For I=1 to NumEstrazioni /Next I.
Puoi farti un pulsante a cui poi associ la macro di estrazione. Per quanto riguarda l’ azzeramento inziale dell’ elenco su foglio2 suggerisco di farlo a mano, cosi’ e’ piu’ difficile farlo per errore e si puo’ sempre fare “Annulla”.

Sul foglio Sorgente, puoi colorare di rosso i nomi gia’ estratti usando la formattazione condizionale: sul secondo foglio selezioni tutta la colonna dei nominativi estratti (col. B) e assegni il nome “Estratti”; poi sul foglio sorgente selezioni tutta la riga A (ossia quella con tutti i nominativi), avvi la formattazione condizionale, clausola “la formula è”, come formula metti

Codice: Seleziona tutto

=CONTA.SE(Estratti;A1)>0

, poi imposti o il font o lo sfondo di colore rosso.

Se l’ approccio generale ti quadra, prova a lavorarci su; posta eventuali difficolta’.
Ciao.

[realkaranka]

Ti ringrazio tantissimo ma con Visual Basic non sono per niente bravo. Devo fare per forza una macro? Non si può fare con delle funzioni, utilizzando magari una singola macro.

p.s. Ho excel in inglese

Grazie per il tempo che t rubo

ciao

[Anthony47]

Puoi fare una cosa manuale:
-Foglio1 contiene l' elenco, da A2 in giu'; definisci "inizio" e formattazione condizionale come da messaggio precedente.
-sul foglio2, col A la lasci per i numeri estratti
- In F1 ti calcoli quanti sono i nominativi con

Codice: Seleziona tutto

=CONTA.VALORI(Foglio1!A:A)-1

-in F4 inserisci

Codice: Seleziona tutto

=INT(CASUALE()*F1+1)

-formattazione condizionale su F4: "la formula è", formula

Codice: Seleziona tutto

=CONTA.SE($A:$A;F4)>0

e sfondo rosso
-in col. B, in B1 metti

Codice: Seleziona tutto

=se(A1="";"";scarto(inizio;G3-1;0))

poi copi la formula verso il basso.

A questo punto:
-premi F9 per eseguire una estrazione
-copi a mano il numero in F4 nella prima cella libera di col. A
-se il numero in F4 viene rosso, significa che e' stato gia' estratto e va ripetuta l' estrazione (premere F9)
-man mano che popoli col A, in col B compaiono i nominativi.

Per la versione inglese:
SE=IF
CONTA.SE = COUNTIF
CONTA.VALORI = COUNTA
SCARTO = OFFSET
CASUALE = RAND
Probabilmente avrai anche il separatore di campi settato su "virgola".

Ciao, fai sapere.

[realkaranka]

-in col. B, in B1 metti

Codice: Seleziona tutto

=se(A1="";"";scarto(inizio;G3-1;0))

poi copi la formula verso il basso.

Ma in G3 cosa c'è???

Poi una domanda se ho un elenco di 600/700 nomi sarà molto probabile che arrivato circa a metà dell'estrazione, estrarrò sempre nomi già estratti (scusa per il gioco di parole)...

Grazie ancora

[Anthony47]

Ma in G3 cosa c'è???

Boh...
Infatti la formula in B1 doveva essere

Codice: Seleziona tutto

=se(A1="";"";scarto(inizio;A1-1;0))

Per quanto riguarda il rischio crescente di estrarre gli stessi numeri, lo gestisci col colore rosso dell' estratto e ripigiando F9 (cosa che la macro avrebbe fatto in automatico).
Una piccola automazione potrebbe derivare dall' uso di una macro che ti estrae in F4 un numero non ancora estratto, usando il codice che ti avevo gia' dato:

Codice: Seleziona tutto

Sub NuRand()
AncoRand:
NumRand = Int((Range("F1").value + 1) * Rnd)
If Application.WorksheetFunction.CountIf(Range("A1:A1000"), NumRand) > 0 Then GoTo AncoRand
Range("F4").value = NumRand
End Sub

Poi disegni un pulsante e ci associ la macro.

Ma tu, dall' elenco, quanti nomi devi estrarre, e come: tutti insieme, uno dopo l' altro, uno quando ce n' e' bisogno?? Per ogni bisogno c' e' un approccio diverso...


Ciao.

[realkaranka]

Uno dopo l'altro... Adesso provo con qsta macro. Ma devo fare un semplice copia e incolla?

[realkaranka]

Allora ti elenco qll che ho fatto...

Su Sheet1 nella colonna A:A ho l'elenco dei nomi.
Nello stesso foglio nella colonna B:B ho inserito la formula:

Codice: Seleziona tutto

 =IF(A1="";"";OFFSET(inizio;A1-1;0))

Su Sheet2 nella cella F1 ho inserito la formula:

[/code]=COUNTA(Sheet1!A:A)-1 [/code]

Poi nello stesso foglio (Sheet2) ho assegnato ad un pulsante la macro:

Codice: Seleziona tutto

 
Sub NuRand()
AncoRand:
NumRand = Int((Range("F1").value + 1) * Rnd)
If Application.WorksheetFunction.CountIf(Range("A1:A1000"), NumRand) > 0 Then GoTo AncoRand
Range("F4").value = NumRand
End Sub


Cosa manca? Non si può semplificare tutto con un'unica macro... I nomi verranno estratti tutti uno dopo l'altro dal primo all'ultimo.

Grazie ancora[/code]

[Anthony47]

Devi fare tutto quello che ti suggeri ieri:

Puoi fare una cosa manuale:
-FOGLIO1contiene l' elenco, da A2 in giu'; DEFINISCI "inizio" e formattazione condizionale come da messaggio precedente.
-sul FOGLIO2, col A la lasci per i numeri estratti
- In F1 ti calcoli quanti sono i nominativi con:

Codice: Seleziona tutto

=CONTA.VALORI(Foglio1!A:A)-1

-in F4 inserisci Codice:

Codice: Seleziona tutto

=INT(CASUALE()*F1+1)

-formattazione condizionale su F4: "la formula è", formula :

Codice: Seleziona tutto

=CONTA.SE($A:$A;F4)>0

e sfondo rosso
-in col. B, in B1 metti :
=se(A1="";"";scarto(inizio;A1-1;0))
poi copi la formula verso il basso.

Poi inserisci la macro e il pulsante, e invece di pigiare F9 per estrarre un numero pigerai il pulsante.

Per quanto riguarda la tua domanda "Non si può semplificare tutto con un'unica macro... I nomi verranno estratti tutti uno dopo l'altro dal primo all'ultimo", non la capisco, visto che non hai risposto alla mia domanda precedente ("Ma tu, dall' elenco, quanti nomi devi estrarre, ... ").

Ciao.

[realkaranka]

Devo estrarre circa 550 nomi. I nomi dovranno essere tutti estratti significa che l'estrazione non si fermerà ai primi N nomi ma continuerà fino all'estrazione del 550-esimo numero...

[Anthony47]

Beh.. allora andava bene la prima risposta che ti fornii, che diceva:

Se vuoi fare una estrazione alla volta ti fermi qui, altrimenti inserisci tutto in un ciclo For I=1 to NumEstrazioni /Next I

Quindi: "se l’ approccio generale ti quadra, prova a lavorarci su; posta eventuali difficoltà"

Ciao.

[realkaranka]

Scusami tanto ma mi sono confuso non poco. Potresti abbozzarlo su un foglio excel e inviarmelo via mail? In questo modo lo posso analizzare al meglio....

Grazie ancora... Ti sono grato

Ciao e buon fine settimana

[Anthony47]

Dai, un piccolo sforzo..
Hai impostato Foglio1 come da messaggio? Hai gia’ abbozzato la macro? Che cosa non sai dove mettere? Che cosa ti preoccupa?

Ciao.

[realkaranka]

Il problema è che non ho dimestichezza con le macro. Se mi passi il file me lo posso studiare meglio. Purtroppo non posso dedicarci molto tempo perchè lavoro tutto il giorno.

Cmq grazie per la pazienza

[Anthony47]

Non ti sei fatto piu’ sentire, non so se perche’ hai risolto con un po’ di “applicazione” o ti sei offeso per l’ invito a provarci. Opterei per la seconda...
Comunque, nell’ illusione che arrossivi quando scrivevi “Se mi passi il file me lo posso studiare meglio” e “non posso dedicarci molto tempo perchè lavoro tutto il giorno” (noi che siamo ricchi di famiglia invece...), provo a darti qualcosa di piu’.
La macro da usare e’ la seguente:

Codice: Seleziona tutto

Sub lavoratore()
FNomi = "Sorgente"      '<<<<< Foglio con i nominativi
FEstraz = "Estratti"    '<<<<< Foglio per le estrazioni

Sheets(FNomi).Select
TotNomi = Range("A65536").End(xlUp).Row - Range("Inizio").Row + 1
Sheets(FEstraz).Select
Range("A:B").ClearContents
For I = 1 To TotNomi
AncoRand:
NumRand = Int((TotNomi) * Rnd)
If Application.WorksheetFunction.CountIf(Range("A1:A1000"), NumRand) > 0 Then GoTo AncoRand
Range("A65536").End(xlUp).Offset(1, 0).Value = NumRand
Range("A65536").End(xlUp).Offset(0, 1).Value = Range("Inizio").Offset(NumRand, 0).Value
Next I
End Sub

Procedura: da excel apri il vba editor con Alt-F11, poi Menu /Insert /Modulo; copia il codice e incollalo nel frame bianco di dx del vba editor.
Devi modificare le due istruzioni marcate <<<< con i nomi dei fogli del tuo file su cui sono presenti i nominativi e il foglio su cui vuoi avere i dati estratti; tieni presente che la macro per prima cosa AZZERA LE COLONNE A e B del foglio che dichiari per le estrazioni, quindi non sbagliare, e comunque fai prima 2 copie di backup del file di origine.

Poi torna su excel, sul foglio con i nominativi o su quello con le estrazioni a tua scelta, disegna un pulsante (lo trovi nella barra degli strumenti “Moduli”) e associagli la macro (ti verra’ proposto nel ciclo di creazione del pulsante). In questo modo bastera’ premere il pulsante per procedere all’ estrazione.
Ricorda che devi definire sul foglio contenente i nominativi la cella “Inizio”: selezioni la prima cella con nominativo, Menu /Inserisci /Nome /Definisci; nell’ area “Nomi ..” scrivi Inizio e confermi con Ok.
Se vuoi puoi anche inserire la formattazione condizionata, ma siccome devi estrare tutti questo mi sembra superfluo.

Certo di averti fatto cosa gradita... e comunque se hai ancora dubbi siamo qua.
Ciao.


PS: se invece non eri arrossito. . . beh, non sarebbe un problema nostro :-))

[realkaranka]

Non sono arrosito... non mi fraintendere... semplicemente sono sommerso di lavoro, sto in un ufficio 10 ore al giorno. Ci provo nella pausa pranzo qnd non ci sono i miei capi. Cmq capisco che ognuno di noi ha i suoi problemi, quindi non preoccuparti. Qui siete davvero tanto disponibili e penso che essermi registrato su qst forum sia stata una delle cose migliori che ho fatto...

Grazie ancora per la tua disponibilità... qnd ho tempo mi farà sentire...

Ciao

[realkaranka]

Non ti sei fatto piu’ sentire, non so se perche’ hai risolto con un po’ di “applicazione” o ti sei offeso per l’ invito a provarci. Opterei per la seconda...
Comunque, nell’ illusione che arrossivi quando scrivevi “Se mi passi il file me lo posso studiare meglio” e “non posso dedicarci molto tempo perchè lavoro tutto il giorno” (noi che siamo ricchi di famiglia invece...), provo a darti qualcosa di piu’.
La macro da usare e’ la seguente:

Codice: Seleziona tutto

Sub lavoratore()
FNomi = "Sorgente"      '<<<<< Foglio con i nominativi
FEstraz = "Estratti"    '<<<<< Foglio per le estrazioni

Sheets(FNomi).Select
[b]TotNomi = Range("A65536").End(xlUp).Row - Range("Inizio").Row + 1[/b]
Sheets(FEstraz).Select
Range("A:B").ClearContents
For I = 1 To TotNomi
AncoRand:
NumRand = Int((TotNomi) * Rnd)
If Application.WorksheetFunction.CountIf(Range("A1:A1000"), NumRand) > 0 Then GoTo AncoRand
Range("A65536").End(xlUp).Offset(1, 0).Value = NumRand
Range("A65536").End(xlUp).Offset(0, 1).Value = Range("Inizio").Offset(NumRand, 0).Value
Next I
End Sub

Procedura: da excel apri il vba editor con Alt-F11, poi Menu /Insert /Modulo; copia il codice e incollalo nel frame bianco di dx del vba editor.
Devi modificare le due istruzioni marcate <<<< con i nomi dei fogli del tuo file su cui sono presenti i nominativi e il foglio su cui vuoi avere i dati estratti; tieni presente che la macro per prima cosa AZZERA LE COLONNE A e B del foglio che dichiari per le estrazioni, quindi non sbagliare, e comunque fai prima 2 copie di backup del file di origine.

Poi torna su excel, sul foglio con i nominativi o su quello con le estrazioni a tua scelta, disegna un pulsante (lo trovi nella barra degli strumenti “Moduli”) e associagli la macro (ti verra’ proposto nel ciclo di creazione del pulsante). In questo modo bastera’ premere il pulsante per procedere all’ estrazione.
Ricorda che devi definire sul foglio contenente i nominativi la cella “Inizio”: selezioni la prima cella con nominativo, Menu /Inserisci /Nome /Definisci; nell’ area “Nomi ..” scrivi Inizio e confermi con Ok.
Se vuoi puoi anche inserire la formattazione condizionata, ma siccome devi estrare tutti questo mi sembra superfluo.

Certo di averti fatto cosa gradita... e comunque se hai ancora dubbi siamo qua.
Ciao.


PS: se invece non eri arrossito. . . beh, non sarebbe un problema nostro :-))

Ho fatto come hai detto ma mi da un errore sulla riga in grassetto. L'errore è: "CAN'T EXECUTE CODE IN BREACK MODE"

Ti dico i passi che ho già fatto:
Un file con due fogli (uno si chiama SORGENTE, l'altro si chiama ESTRATTI)
Ho inserito la macro come hai detto tu enon ho modificato niente perchè ho chiamato i fogli per come tu li hai definiti.

Sul foglio SORGENTE, nella cella A1, ho scritto "Inizio" e poi ho fatto come hai detto tu: "Menu /Inserisci /Nome /Definisci; nell’ area “Nomi ..” scrivi Inizio e confermi con Ok."

Ho assegnato la macro ad un bottone... il risultato è l'errore di cui ti parlavo

Grazie ancora, ci aggiorniamo lunedì

[Anthony47]

NON SI VEDE LA RIGA IN GRASSETTO, devi scrivere l' istruzione.

Pero' il messaggio di errore mi fa pensare che sei entrato in debug (non so in quale modo, se dopo errore o volutamente), e poi col pulsante provi a rilanciare la macro che e' ferma per il debug in corso.
Se e' cosi', ferma il debug (dal vba editor, tasto "quadrato", stile stop sulla barra degli strumenti Standard) e rilancia la macro col pulsante.

Ciao.

[realkaranka]

Ho fatto come hai detto e il risultato è il seguente:

Intanto non ho più messaggi d'errore e qst è OTTIMO.

Nella colonna A del primo foglio (sorgente), dopo la cella A1 (dove c'è "Inizio") ho inserito tutti i nomi di cui voglio fare l'estrazione...

Nella colonna A del secondo foglio (estratti), dopo che premo il punsante (a cui è associata la macro), ho dei numeri assegnati in maniera random. Da notare che ciascun numero compare una volta soltanto, ciò mi fa pensare che la macro ha asegnato ad ogni nome del primo foglio, un numero random. Nella colonna B (sempre del foglio "estratti" ho alcune celle vuote e alcune con dei numeri che trovo anche nella colonna A.

Qll che non capisco è l'importanza di qst numeri (qll della colonna B). Penso che adesso bisognerebbe fare in modo di associare ad ogni nome del primo foglio, un numero del secondo foglio. Poi occorrerebbe una cella dove ma man che premo un altro pulsante esce un nome, magari secondo l'ordine cresente associato all'ordine random ottenuto su "estratti".

Attendo istruzioni

Ciao

[Anthony47]

Quello che descrivi non e’ quello che dovrebbe succedere. . .
In colonna A di Estratti dovresti avere un numero random compreso tra 0 e 550-1 (se 550 sono i nominativi), e in col B dovresti avere il nominativo di foglio Sorgente che corrisponde a quel numero.
Se in col. B di Estratti hai dei numeri o dei vuoti questo dovrebbe significare che anche su foglio Sorgente hai dei vuoti o dei numeri.

Dimmi in cosa quello che vedi e’ diverso da quello che dovrebbe succedere.

Rileggendo pero’ il tuoi post ti chiedo: che cosa e’ quel “Inizio” in cella A1?

A riguardo rileggi le istruzioni date il 21-11:
“sul foglio Sorgente dei nomi assegni in nome “Inizio” alla cella che contiene il primo nominativo”
e dettagliate poi il 30-11
“Ricorda che devi definire sul foglio contenente i nominativi la cella “Inizio”: selezioni la prima cella con nominativo, Menu /Inserisci /Nome /Definisci; nell’ area “Nomi ..” scrivi Inizio e confermi con Ok.”
Quindi come minimo hai inserito Inizio tra i nominativi da estrarre.

Poi, ricontrolla la macro, ad esempio non capisco la riga [b]TotNomi = Range("A65536").End.etc.etc che e’ diversa da quella che ti scrissi io.

Infine, per quanto riguarda la richiesta “Poi occorrerebbe una cella dove ma man che premo un altro pulsante esce un nome”, non capisco perche’ la fai, visto che questa richiesta corrisponde alla soluzione che ti proposi il 21-11 pomeriggio (con correzione/complemento il 22-11); che pero’ non ti andava bene e chiedesti di semplificare tutto con una unica macro: “Devo estrarre circa 550 nomi. I nomi dovranno essere tutti estratti significa che l'estrazione non si fermerà ai primi N nomi ma continuerà fino all'estrazione del 550-esimo numero...”

Ciao.