[Excel] calcolo voti fantacalcio+sostituzioni automatiche

[pulpit123]

Buongiorno a tutti,
nonostante le numerose descrizioni sulla argomento, non riesco a scaricare le soluzioni, quindi ho deciso di aprirne una nuova.

Innanzitutto, per essere più chiaro nella spiegazione, vi allego il link del mio file excel:
https://www.dropbox.com/s/mf4c8dzmvvt51 ... M2014.xlsx

1)nelle colonne D,H,L,P,T,X del foglio1 ho inserito la funzione cerca.vert che dovrebbe restituire i voti dal foglio2. Tuttavia se provo a scrivere come valore direttamente la cella (es:=CERCA.VERT(B2;Foglio2!$A$2:$F$669;6;0)) restituisce errore e sono stato costretto a inserire i nomi a mano (es:=CERCA.VERT("buffon";Foglio2!$A$2:$F$669;6;0)). Vorrei sapere dove sbaglio.

2)vorrei sapere come impostare la sostituzione di un giocatore titolare (dall'1 all'11) che prende un voto uguale a - oppure 0, come una riserva (12-23) dello stesso ruolo che ha preso voto. (fino ad un massimo di 3 sostituzioni)
esempio nel mio file:
vorrei che automaticamente sostituisca nella somma Buffon - con sirigu 6,5 e schweinsteiger - con dembele 5.

*attualmente ho cancellato dal file le formule per i voti dei panchinari per calcolare le somme.

Vi ringrazio anticipatamente!

[ricky53]

Ciao,
la tua formula

Codice: Seleziona tutto

=CERCA.VERT(B2;Foglio2!$A$2:$F$669;6;0)

è corretta.
Che errore ottenevi prima delle operazioni manuali ???

Probabilmente prendevi "#N/D" controlla in "B2" se ci sono degli spazi iniziali o finali.
Questo capita spesso se scarichi i dati da qualche sito come immagino tu abbia fatto.

[pulpit123]

si mi da "#N/D" però ho controllato e non ci sono spazi!! sono solo scritti in maiuscolo ma ho letto che non cambia niente...

[pulpit123]

trovato....spazi finali in alcune celle....ok il primo punto è risolto! adesso rimane il più complicato eheheheh

[ricky53]

Ciao,
come sospettavo: copiando dai siti le celle contengono spazi che creano problemi.

Per il punto "2" dovresti spiegarlo meglio perchè non ho capito cosa vuoi ottenere.

Per esempio: cosa intendi con

vorrei che automaticamente sostituisca nella somma Buffon - con sirigu 6,5 e schweinsteiger - con dembele 5.

Dovresti spiegare tutto il processo che hai in mente

[pulpit123]

ok, vorrei che in fondo alle colonne D,H,L,eccecc...di ogni squadra mi sommi i voti degli 11 titolari e, in caso uno o più di essi abbiano un voto uguale a - oppure a 0, sommi il voto delle riserve dello stesso ruolo fino ad un massimo di 3 giocatori, così da non fare i conti a mano ogni volta.
Quindi nell'esempio di prima sommerà i voti degli 11 titolari + i voti di Sirigu e Dembele al posto di Buffon e Schweinsteiger che hanno preso "-"
Così è più chiaro? In caso chiedi ancora! grazie

[Anthony47]

Ma come si riconoscono i titolari dalle riserve, nel file che hai pubblicato? E poi, visto che Sirigu e' senza voto, e idem Dembele, che sostituzioni sono? Inoltre ricordo (per risposte date in altre discussioni) che le regole per le sostituzioni sono piu' complesse che non "si sostituisce chi non ha preso voti"; dovresti spiegarle tutte (oppure devi aspettare che ti risponda uno che sa tutto di fantacalcio).
NB: spero che non pensi di usare i bordi cella come separatori di ruolo...

Inoltre vedo che le tue formule Cerca.Vert sono del tipo =CERCA.VERT("buffon";Foglio2!$A$2:$F$669;6;0) cioe' col nominativo da cercare scritto nella formula... E' un buon modo per complicarsi la vita... E' piu' conveniente usare

Codice: Seleziona tutto

=CERCA.VERT(ANNULLA.SPAZI(B2);Foglio2!$A$2:$F$669;6;0)

Oltre a non richiedere di scrivere i nominativi, detta formula e' pronta per essere copiata nelle altre celle dove serve; ho usato "Annulla.Spazi" per ignorare gli abbondanti spazi che ci sono in coda ai nominativi di Foglio1 (mentre su Foglio2 la pulizia la devi fare con altri sistemi).

Ciao

[pulpit123]

Mi spiego meglio:
allego il file organizzato meglio: https://www.dropbox.com/s/k88qxlooh1iyn ... M2014.xlsx
le regole del fantacalcio sono 2:
-si può sostituire un giocatore titolare (i titolari sono quelli che nelle colonne A,G,M,eccecc hanno i numeri dall'1 all'11) che non ha preso voto, SOLO con una riserva dello stesso ruolo (ho inserito una colonna con i ruoli: P = portiere, D = difensore, C = centrocampista, A = attaccante);
-si possono fare solo 3 sostituzioni.

Nel documento aggiornato ho inserito anche una colonna di SE (colonne F,L,R,eccecc) che restituisce 1 se il giocatore è titolare e prende voto. Rimane il problema delle riserve che subentrano ai titolari senza voto e qui non sono contate.
Attendo risposte, grazie del vostro tempo!

Saluti

[Anthony47]

Penso di adattare la funzione ScorePanc che avevo usato qui: viewtopic.php?f=26&t=92851&p=551842#p551915
Penso di postare qualcosa in "tarda serata".

Ciao

[pulpit123]

grazie, davvero molto gentile! a dopo

[Anthony47]

E' stata piu' incasinata di quanto mi aspettavo...
Usa questo codice vba:

Codice: Seleziona tutto

Function myScorex(ByRef wArea As Range) As Variant
'by Anthony47 2014
'Restituisce una matrice con i punteggi di tutta la formazione
'Layout della wArea:
'   col 1=Numero di gara        col 2=Ruolo (P, D, C, A)
'   col 3, ignorato             col 4, ignorato
'   col 5=Punteggio
'Esempio di Formula
'   =myScorex(A2:E23)  Confermare con Contr-Maiusc-Enter
'
Dim myRoles, I As Long, J As Long, K As Long, cRole As String, Need As Long
Dim OArr(), wArr(), Gia As Long
'
ReDim wArr(1 To wArea.Rows.Count, 1 To wArea.Columns.Count)
ReDim OArr(1 To wArea.Rows.Count)
wArr = wArea.Value
myRoles = Array("P", "D", "C", "A")
For K = LBound(myRoles, 1) To UBound(myRoles, 1)
    cRole = myRoles(K): Need = 0
    For J = LBound(wArr, 1) To UBound(wArr, 1)
        If wArr(J, 5) = "-" Then wArr(J, 5) = 0
        If wArr(J, 5) = 0 And wArr(J, 2) = cRole And wArr(J, 1) < 12 Then
            Need = Need + 1
        Else
            If wArr(J, 1) < 12 Then OArr(J) = wArr(J, 5)
        End If
    Next J
'    If Need > 0 Then
    For I = 1 To Need
        For J = LBound(wArr, 1) To UBound(wArr, 1)
            If wArr(J, 1) >= 12 And wArr(J, 2) = cRole And wArr(J, 5) > 0 And Gia < 3 Then
                OArr(J) = wArr(J, 5)
                wArr(J, 5) = 0
                Gia = Gia + 1
                Exit For
            End If
        Next J
    Next I
'    End If
Next K
myScorex = Application.WorksheetFunction.Transpose(OArr)
End Function


Da excel:
-Alt-F11 per aprire l' editor delle macro
-Menu /Inserisci /Modulo
-copia il codice e incollalo nel frame vuoto di dx
-torna a Excel

Cancella le formule che hai inserito in F2 e sottostanti; cancella anche la formula in E24.
In F2 inserisci la formula

Codice: Seleziona tutto

=myscorex(A2:E23)

Seleziona da F2 fino a F23; premi il tasto "F2" (edit formula), conferma la formula con Contr-Maiusc-Enter

La teoria e' che in colonna F dovresti ora trovare i punti che effettivamente vanno calcolati per la squadra, con le eventuali sostituzioni per ruolo.
Calcolerai quindi il puntaggio totale come somma di questa colonna.

Ripeti l' operazione sulle altre squadre (puoi copiare F2:F23 e fare Incolla speciale /Formula in L2, R2, etc

Ciao, fai sapere.

[pulpit123]

Allora, avendo io un mac sto impazzendo...ho trovato il visual basic editor, ho inserito la tua formula in foglio1 (la copio tutta o tralascio le prime righe di spiegazione?), ho cancellato i dati dalla colonna F, ho inserito =myscorex(A2:E23), l'ho editato con fn+F2, ma adesso non mi conferma la formula....ne con crtl+maiusc+enter, ne con cmd+return come ho letto in giro....
Scusami per l'ignoranza ma non ho mai inserito delle formule.
Graziee

Ciao

[Anthony47]

https://www.dropbox.com/s/ctg2cy3wv172x ... 40623.xlsm
Ho caricato su dropbox il file che contiene il codice e le formule relative alle squadre Bosnia e Argentina.

Le celle col testo in Rosso hanno dei punteggi alterati (rispetto alle formule tue standard) per poter collaudare il comportamento della macro.

Ciao

[pulpit123]

Perfetto! Grazie mille!
Solo una cosa, forse non l'ho specificato io, riusciresti a modificarla per far entrare prima le riserve,sempre dello stesso ruolo, ma con il numero più basso? cioè le riserve sono ordinate dal 12 al 23 e nell'esempio che hai messo luis gustavo che ha il numero 19 entra prima di honda con il numero 18.
Ti ringrazio molto davvero!

Ultimissima domanda: come mai in qualsiasi formato io metta il file se lo allego via mail e lo apro con un iphone le righe vengono deformate? (all'inizio larghissime poi più piccole)

Ciao

[Anthony47]

Per il problema della sequenza, direi che la cosa piu' semplice e' di mettere i componenti in ordine di numero (e cosi' avrebbe funzionato anche la funzione ScorePanc che avevo pubblicato in passato).

Per quanto riguarda il comportamento sull' iphone non ho nessuna idea, penso dovresti chiedere su un forum dei melafonini.

Ciao

[pulpit123]

eh ma il problema diventa che ogni volta che si fanno le formazioni, quindi ogni settimana, devo modificare tutti i nomi invece che modificare solo i numeri a fianco al nome. Non c'è un modo di sistemare quest'ultima sottigliezza?

[Anthony47]

Eh, per queste sottigliezze in Microsoft hanno sviluppato il comando Ordina...

Comunque mi interessava ampliare l' esperimento della Udf matriciale, e sono arrivato a questo codice:

Codice: Seleziona tutto

Function myScoreZ(ByRef wArea As Range) As Variant
'Restituisce una matrice con i punteggi di tutta la formazione
'Layout della wArea:
'   col 1=Numero di gara        col 2=Ruolo (P, D, C, A)
'   col 3, ignorato             col 4, ignorato
'   col 5=Punteggio             col 6=Index to OArr
'Esempio di Formula
'   =myScoreZ(A2:E23)  Confermare con Contr-Maiusc-Enter
'
Dim myRoles, I As Long, J As Long, K As Long, cRole As String, Need As Long
Dim OArr(), wArr(), Gia As Long, myMatc, bArr As Range
'
ReDim wArr(1 To wArea.Rows.Count, 1 To 6)
ReDim OArr(1 To wArea.Rows.Count, 1 To 1)

Set bArr = Application.WorksheetFunction.Index(wArea, 0, 1)    'colonna Numerazione
'
'
For I = 1 To 30         'scan max 30 players
    myMatc = Application.Match(I, bArr, 0)
    If Not IsError(myMatc) Then
        J = J + 1
        For K = 1 To 5
            wArr(J, K) = wArea.Cells(myMatc, K)
        Next K
        wArr(J, 6) = myMatc     'wArr(J, 6) e' il crossindex tra wArr e OArr
    End If
Next I
'wArr = wArea.Value
myRoles = Array("P", "D", "C", "A")
For K = LBound(myRoles, 1) To UBound(myRoles, 1)
    cRole = myRoles(K): Need = 0
    For J = LBound(wArr, 1) To UBound(wArr, 1)
        If wArr(J, 5) = "-" Then wArr(J, 5) = 0
        If wArr(J, 5) = 0 And wArr(J, 2) = cRole And wArr(J, 1) < 12 Then
            Need = Need + 1
        Else
            If wArr(J, 1) < 12 Then OArr(wArr(J, 6), 1) = wArr(J, 5)
        End If
    Next J
    For I = 1 To Need
        For J = LBound(wArr, 1) To UBound(wArr, 1)
            If wArr(J, 1) >= 12 And wArr(J, 2) = cRole And wArr(J, 5) > 0 And Gia < 3 Then
                OArr(wArr(J, 6), 1) = wArr(J, 5)
                wArr(J, 5) = 0
                Gia = Gia + 1
                Exit For
            End If
        Next J
    Next I
Next K
'
myScoreZ = OArr 'Application.WorksheetFunction.Transpose(OArr)
End Function


La funzione si chiama myScoreZ, e si usa nello stesso modo della myScoreX.

Ciao

[pulpit123]

eh già....ordina...ahahahahahah
Scusa la domanda stupida...
Grazie di tutto! Molto gentile!

[Anthony47]

Per evitare equivoci, la funzione myScoreZ ti risparmia il gravoso compito di ordinare

[pulpit123]

Ciao a tutti,
nuovo anno di fantacalcio, stesso problema...Ho ripreso il file che mi ha fatto Anthony47 e l'ho modificato con i nuovi calciatori (ho aggiunto anche 2 calciatori in più per ogni squadra: righe 25-26 e 52-53) solo che non riesco a replicare la funzione e non funziona più come dovrebbe.
In particolare, ripeto il tutto per essere più chiaro, vorrei che la funzione nelle colonne: F,L,R,X,AD restituisse i valori della colonna alla sua sinistra solo se in A,G,M,S,Y ci sono numeri dall'1 al 11. E soprattutto, qualora per i calciatori dall'1 all'11, nella colonna a fianco alla funzione ci siano valori uguali a "-" dovrebbe provvedere e restituire il voto di uno dei calciatori in panchina(dal 12 al 18) seguendo i vincoli di ruolo (P con P, D con D, C con C e A con A) e fino ad un massimo di 3 sostituzioni.
ES: nella squadra cagliari, la funzione dovrebbe sostituire Morata (attaccante) con Dybala (attaccante) e non dovrebbe restituire i valori degli altri giocatori della panchina e della tribuna.
Naturalmente allego il file: https://www.dropbox.com/s/cv5situ92fs5s ... .xlsm?dl=0
E spero che qualcuno risolva il mio problema.
Vi ringrazio anticipatamente,
Saluti!