Risolto Fantacalcio: somma dei primi 11 giocatori su 25 in base al modulo implementando il modificatore

pellegrino

Nuovo utente
22 Agosto 2021
13
1
office365
0
Ciao a tutti, non mi sembra di aver trovato una soluzione al problema che sto per esporre, nel caso non dovesse essere così, per favore reindirizzatemi.
Scusate la richiesta un po' lunga da descrivere ma vorrei essere il più chiaro possibile.
Parliamo di Fantacalcio,
nella fase di calcolo della formazione scesa in campo vorrei rendere automatizzato il calcolo dei soli 11 titolari che hanno preso voto. In un elenco di 25 giocatori massimo 24 giocatori prendono voto (numero tra 1 e 10, escluso bonus). Nella formula che sto cercando (cioè che faccia la somma di 11 giocatori) vorrei che in base alla colonna RUOLO definita e alle tre celle che definiscono il MODULO venissero presi in considerazione i primi 11 giocatori che abbiano ricevuto un voto valido, riportando comunque il voto di tutti i giocatori.


PS: Faccio notare che gli undici giocatori potrebbe rientrare anche l'11esimo giocatore che non ha preso voto, in quanto sia i giocatori titolari che quelli di riserva facenti parte dello stesso RUOLO a loro volta non hanno preso voto e che per definizione di regola prende come voto 3 in automatico.
(esempio Portiere_1 voto 0 , Portiere_2 voto 0 , Portiere_3 voto 0, in automatico viene assegnato al Portiere_1 voto 3)


Nel Modulo_1 quello che riporto è più una rappresentazione che la formazione-tipo, serve per far capire che per ogni ruolo bisogna prendere in considerazione le prime
posizioni in base al modulo scelto, cioè se il modulo è il 4-4-2, si dovranno prendere in considerazione in primi 4 difensori, i primi 4 centrocampisti e i primi
2 attaccanti aventi avuto voto. Il modulo_1 si trova solo nell'allegato.
Nel Modulo_2 riporto lo schema che attualmente vorrei riuscire a realizzare automaticamente ma che attualmente faccio manualmente.
Nel Modulo_3 riporto lo schema che attualmente utilizzo automaticamente ma devo necessariamente spostare i giocatori aventi avuto voto nelle prime undici posizioni.
Nel Modulo_3 è implementato il Modulo_4 che riporto successivamente per non confondere
FORMULA_1

Definizione di foglio excel:
GIOCATORE ..................... = Colonna A, F, K
RUOLO.............................. = Colonna B, G, L
VOTO ............................... = Colonna C, H, M
POSIZIONE ...................... = Colonna D, I, N
modulo_2 (Riga Colonna) = G1 (Difesa) H1 (Centrocampo) I1 (Attacco)

modulo_3 (Riga Colonna) = L1 (Difesa) M1 (Centrocampo) N1 (Attacco)

trattini .............................. = non occupano nessuno spazio come righe ma servono solo per evitare confusione


.................................D ............. C ............ A ..................................... .....................D ............ C ........... A
modulo_2 ............... 3 ............. 4 ............ 3 ........................ modulo_3 ................ 5 ............. 4 ........... 1
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Colonna_F ................Col._G .... Col._H .... Col._I ................. Colonna_K ..............Col._L .... Col._M ... Col._N
GIOCATORE ............ RUOLO ... VOTO .... POSIZIONE ......... GIOCATORE .......... RUOLO ... VOTO ..... POSIZIONE
Portiere_1 ................ P ........... 10 .......... 1° ........................ Portiere_1 .............. P ............ 4,5 .......... 1°
Difensore_1 ............. D ............ 9 ............ 2° ...................... Difensore_1 ........... D ............. 3,5 ..........2°
Difensore_2 ............. D ............ 9 ............ 3° ...................... Difensore_2 ........... D ............. 3,5 ......... 3°
Difensore_3 ............. D ........... 0 ....................................... Difensore_3 ............ D ............. 3,5 ........ 4°
Centrocampista_1 .. C ........... 8 ............ 5° ...................... Difensore_6 ............ D ............. 3,5 ........ 5°
Centrocampista_2 .. C ........... 8 ............ 6° ...................... Difensore_8 ............ D ............. 3,5 ........ 6°
Centrocampista_3 .. C ........... 0 ....................................... Centrocampista_1 .. C ............. 2,5 ........ 7°
Centrocampista_4 .. C ........... 0 ....................................... Centrocampista_2 .. C .............. 2,5 ...... 8°
Attaccante_1 ........... A ........... 7 ............. 9° .................... Centrocampista_3 .. C ............... 2,5 ...... 9°
Attaccante_2 ........... A ........... 7 ............ 10° ................... Centrocampista_7 .. C ............... 2,5 ..... 10°
Attaccante_3 ........... A ............ 0 ...................................... Attaccante_1 ........... A ............... 1,5 ..... 11°
Portiere_2 ................ P ............ 0 ...................................... Portiere_2 ................ P ................ 0
Portiere_3 ................ P ............ 0 ...................................... Portiere_3 ................ P ................ 0
Attaccante_4 ........... A ............ 7 ............ 11° .................. Attaccante_2 ........... A ................ 1,5
Attaccante_5 ........... A ............ 0 ...................................... Attaccante_3 ............ A ................ 1,5
Attaccante_6 ............A ............ 0 ...................................... Attaccante_4 ............ A ................ 0
Centrocampista_5 ... C ............ 8 ............. 7° ................... Attaccante_5 ............ A ................ 0
Centrocampista_6 ... C ............ 0 ..................................... Attaccante_6 ............. A ................ 0
Centrocampista_7 ... C ............ 0 ..................................... Centrocampista_5 ... C .................. 0
Centrocampista_8 ... C ............. 8 ............. 8° .................. Centrocampista_6 ... C ................... 0
Difensore_4 .............. D ............ 9 .............. 4° .................. Centrocampista_4 ... C ................... 0
Difensore_5 .............. D ............ 0 ..................................... Centrocampista_8 ... C ................... 2,5
Difensore_6 .............. D ............ 0 ..................................... Difensore_4 ............... D .................. 0
Difensore_7 .............. D ............ 0 ..................................... Difensore_5 ............... D .................. 0
Difensore_8 .............. D ............ 0 ..................................... Difensore_7 ............... D .................. 0
----------------------------------------------------------------------------------------------------------------------------------------------------------------
TOTALE 11 GIOCATORI.............. 90 .................................... TOTALE 11 GIOCATORI ................... 33,5

#Formule Applicate:

TOTALE 92 = SOMMA(H3;H4;H5;H23;H7;H8;H19;H22;H11;H12;H16)
TOTALE 33,5 = SOMMA(M3:M13)

==============================================================================================================================



Superato questo primo scoglio dovrei integrare il Modificatore della difesa che ho realizzato nel seguente Modulo_4.

Il Modificatore della difesa vuol essere un bonus che si attiva a seconda di determinate condizioni.
Se si usano negli undici titolari:
- 3 difensori che raggiungono la somma voto (per la sola difesa) di 20 punti
- 4 difensori che raggiungono la somma voto (per la sola difesa) di 24 punti
- 5 difensori che raggiungono la somma voto (per la sola difesa) di 28 punti
la squadra avrà un bonus ulteriore di 1 punto

Avendo usato un altro foglio riporto Righe e Colonne per praticità

Colonna_B ...... Colonna_C Colonna_D Colonna_E Colonna_F
Modulo_4 (Riga_4) 3 4 3
--------------------------------------------------------------------------------------------------------------------------------------------
Colonna_B .... Colonna_C ............. Colonna_D ...... Colonna_E ..... Colonna_F
POSZIONE ... GIOCATORE ............. RUOLO ........... VOTO ............ BONUS/MALUS
1 (Riga_7) ..... Portiere_1.................. P ..................... 9,5 ..................... -1
2 .................... Difensore_1 .............. D ..................... 8,5 ................... +3
3 .................... Difensore_2............... D ..................... 8,5
4 .................... Difensore_3 .............. D ..................... 8,5
5 .................... Centrocampista_1 ... C ..................... 7,5 ..................... -0,5
6 .................... Centrocampista_2 ... C ..................... 7,5 ..................... -1
7 .................... Centrocampista_3 ... C ..................... 7,5 ..................... +2
8 .................... Centrocampista_4 ... C ..................... 7,5
9 .................... Attaccante_1 ............ A ..................... 6,5 ..................... +6
10 ................... Attaccante_2 ........... A ..................... 6,5
11 (Riga_17) .. Attaccante_3 ........... A ..................... 6,5
12 ................... Portiere_2 ................. P
13 ................... Portiere_3 ................. P
14 ................... Attaccante_4 ............ A
15 ................... Attaccante_5 ............ A
16 ................... Attaccante_6 ............ A
17 ................... Centrocampista_5 .... C
18 ................... Centrocampista_6 .... C
19 ................... Centrocampista_7 ... C
20 ................... Centrocampista_8 ... C
21 ................... Difensore_4 .............. D
22 ................... Difensore_5 .............. D
23 ................... Difensore_6 ............. D
24 ................... Difensore_7 ............. D
25 (Riga_31) .. Difensore_8 ............. D
-----------------------------------------------------------------------------------------------------------------------
TOTALE VOTO & BONUS/MALUS .......................... 84,5 ..................8,5
TOTALE SOLO DIFESA ........................................... 25,5
MODIFICATORE DELLA DIFESA ...................................................... +1

#Formule Applicate:

TOTALE VOTO ................ =SOMMA(E6:E31)

TOTALE BONUS/MALUS =SOMMA(F6:F31)

TOTALE SOLO DIFESA =SOMMA(INDIRETTO("R"&AGGREGA(15;6;RIF.RIGA(INDIRETTO("D"&CONFRONTA(D3;D3:D17;0)+7&":D"&CONFRONTA(D3;D3:D17;0)+11))/((SCARTO(INDIRETTO("D"&CONFRONTA(D3;D3:D17;0)+7);;;5)=D3)+(SCARTO(INDIRETTO("D"&CONFRONTA(D3;D3:D17;0)+7);;1;5)=D3));1)&"C"&5&":R"&AGGREGA(14;6;RIF.RIGA(INDIRETTO("D"&CONFRONTA(D3;D3:D17;0)+7&":D"&CONFRONTA(D3;D3:D17;0)+11))/((SCARTO(INDIRETTO("D"&CONFRONTA(D3;D3:D17;0)+7);;;5)=D3)+(SCARTO(INDIRETTO("D"&CONFRONTA(D3;D3:D17;0)+7);;1;5)=D3));1)&"C"&5;0))

MODIFICATORE DELLA DIFESA =PIÙ.SE(D4=3; SE(SOMMA(INDIRETTO("R"&AGGREGA(15;6;RIF.RIGA(INDIRETTO("D"&CONFRONTA(D3;D3:D17;0)+7&":D"&CONFRONTA(D3;D3:D17;0)+11))/((SCARTO(INDIRETTO("D"&CONFRONTA(D3;D3:D17;0)+7);;;5)=D3)+(SCARTO(INDIRETTO("D"&CONFRONTA(D3;D3:D17;0)+7);;1;5)=D3));1)&"C"&5&":R"&AGGREGA(14;6;RIF.RIGA(INDIRETTO("D"&CONFRONTA(D3;D3:D17;0)+7&":D"&CONFRONTA(D3;D3:D17;0)+11))/((SCARTO(INDIRETTO("D"&CONFRONTA(D3;D3:D17;0)+7);;;5)=D3)+(SCARTO(INDIRETTO("D"&CONFRONTA(D3;D3:D17;0)+7);;1;5)=D3));1)&"C"&5;0))>=20; 1; 0); D4=4; SE(SOMMA(INDIRETTO("R"&AGGREGA(15;6;RIF.RIGA(INDIRETTO("D"&CONFRONTA(D3;D3:D17;0)+7&":D"&CONFRONTA(D3;D3:D17;0)+11))/((SCARTO(INDIRETTO("D"&CONFRONTA(D3;D3:D17;0)+7);;;5)=D3)+(SCARTO(INDIRETTO("D"&CONFRONTA(D3;D3:D17;0)+7);;1;5)=D3));1)&"C"&5&":R"&AGGREGA(14;6;RIF.RIGA(INDIRETTO("D"&CONFRONTA(D3;D3:D17;0)+7&":D"&CONFRONTA(D3;D3:D17;0)+11))/((SCARTO(INDIRETTO("D"&CONFRONTA(D3;D3:D17;0)+7);;;5)=D3)+(SCARTO(INDIRETTO("D"&CONFRONTA(D3;D3:D17;0)+7);;1;5)=D3));1)&"C"&5;0))>=24; 1; 0); D4=5; SE(SOMMA(INDIRETTO("R"&AGGREGA(15;6;RIF.RIGA(INDIRETTO("D"&CONFRONTA(D3;D3:D17;0)+7&":D"&CONFRONTA(D3;D3:D17;0)+11))/((SCARTO(INDIRETTO("D"&CONFRONTA(D3;D3:D17;0)+7);;;5)=D3)+(SCARTO(INDIRETTO("D"&CONFRONTA(D3;D3:D17;0)+7);;1;5)=D3));1)&"C"&5&":R"&AGGREGA(14;6;RIF.RIGA(INDIRETTO("D"&CONFRONTA(D3;D3:D17;0)+7&":D"&CONFRONTA(D3;D3:D17;0)+11))/((SCARTO(INDIRETTO("D"&CONFRONTA(D3;D3:D17;0)+7);;;5)=D3)+(SCARTO(INDIRETTO("D"&CONFRONTA(D3;D3:D17;0)+7);;1;5)=D3));1)&"C"&5;0))>=28; 1; 0))
 

Allegati

Ultima modifica:

pellegrino

Nuovo utente
22 Agosto 2021
13
1
office365
0
Ciao,
si mi sono accorto che c'era incongruenza tra anteprima e realtà. Ho modificato e allegato, spero sia più chiaro ora
 

pellegrino

Nuovo utente
22 Agosto 2021
13
1
office365
0
Ricomincio
Ciao a tutti, parliamo di Fantacalcio,
nella fase di calcolo della formazione scesa in campo (Squadra A verde - cella C5) vorrei rendere automatizzato il calcolo
dei soli 11 titolari che hanno preso voto (celle sfondo verde).
Il voto valido è un numero diverso da 0.
Nella formula che sto cercando (cioè che faccia la somma di 11 voti) vorrei che in base
- alle tre celle che definiscono il MODULO (celle D4-E4-F4);
- al modificatore della difesa (cella F33) che condiziona il ruolo dalla cella D8 alla cella D17;
- alla colonna RUOLO (Colonna "D");
venissero presi in considerazione i primi 11 giocatori che abbiano ricevuto un voto valido (diverso da 0),
riportando comunque il voto di tutti i giocatori.

NB: Se un giocatore non è sceso in campo prende voto 0.

Attualmente mi trovo costretto ad usare il modello in cui i voti devono essere per forza nelle prime 11 posizioni (Squadra B arancione - cella I5)

Superato questo primo scoglio dovrei integrare il Modificatore della difesa che ho realizzato (vedere cella F33)
che assegna un bonus extra ma che si attiva a seconda di determinate condizioni,
cioè se i giocatori definiti nel ruolo D (=Difensori) siano nel totale di:
- 3 difensori che raggiungono la somma voto (per la sola difesa) di 20 punti
- 4 difensori che raggiungono la somma voto (per la sola difesa) di 24 punti
- 5 difensori che raggiungono la somma voto (per la sola difesa) di 28 punti
la squadra avrà un bonus ulteriore di 1 punto

Ulteriore difficoltà nasce però nel momento in cui nei primi 11 giocatori potrebbe rientrare quel/quei giocatore/i che non ha/hanno preso voto,
in quanto sia i primi 11 giocatori che i restanti 14 facenti parte dello stesso RUOLO a loro volta non hanno preso voto e
che per definizione di regola prende come voto 3 in automatico.
(esempio Portiere_1 voto 0 , Portiere_2 voto 0 , Portiere_3 voto 0, in automatico viene assegnato al Portiere_1 voto 3 - cella E7)
 

Allegati

pkrome59

Utente assiduo
Expert
26 Settembre 2015
2.604
145
61
Reggio Calabria
Office 2019
178
Ciao, per quanto riguarda la somma degli 11 titolari:

In E32:
=SOMMA(MAX((D$7:D$31="P")*(E$7:E$31));AGGREGA(14;6;E$7:E$31/(D$7:D$31=D3);RIF.RIGA(INDIRETTO("A1:A"&D4)));AGGREGA(14;6;E$7:E$31/(D$7:D$31=E3);RIF.RIGA(INDIRETTO("A1:A"&E4)));AGGREGA(14;6;E$7:E$31/(D$7:D$31=F3);RIF.RIGA(INDIRETTO("A1:A"&F4))))
formula matrice da attivare con i tasti ctrl+maiuscolo+invio.

La formula va copiata e incollata in K32 (si adatta automaticamente) e così via.
Per il resto spiega meglio con il risultato desiderato da esempi sritti a mano.
Ciao.
 

pkrome59

Utente assiduo
Expert
26 Settembre 2015
2.604
145
61
Reggio Calabria
Office 2019
178
Ciao, forse ho capito:

In F33:
=O(SOMMA(AGGREGA(14;6;E$7:E$31/(D$7:D$31=D3);RIF.RIGA($A$1:$A$3)))>=20;SOMMA(AGGREGA(14;6;E$7:E$31/(D$7:D$31=D3);RIF.RIGA($A$1:$A$4)))>=24;SOMMA(AGGREGA(14;6;E$7:E$31/(D$7:D$31=D3);RIF.RIGA(A1:A5)))>=28)*1
formula matrice da attivare con i tasti ctrl+maiuscolo+invio.
La formula va copiata e incollata in K32 (si adatta automaticamente) e così via.

In E34:
=SE(F34<66;0;SE(F34<72;1;SE(F34<78;2;SE(F34<84;3;SE(F34<90;4;SE(F34<96;5;6))))))
La formula va copiata e incollata in K34 (si adatta automaticamente) e così via.
Ciao.
 

pkrome59

Utente assiduo
Expert
26 Settembre 2015
2.604
145
61
Reggio Calabria
Office 2019
178
Ciao, rileggendo, per quanto riguarda la somma degli 11 titolari va integrata un'ulteriore condizione riguardo i tre portieri che conseguono contemporaneamente punti 0, per cui:

In E32:
=SOMMA(SE(SOMMA((D$7:D$31="P")*(E$7:E$31))=0;3;MAX((D$7:D$31="P")*(E$7:E$31)));AGGREGA(14;6;E$7:E$31/(D$7:D$31=D3);RIF.RIGA(INDIRETTO("A1:A"&D4)));AGGREGA(14;6;E$7:E$31/(D$7:D$31=E3);RIF.RIGA(INDIRETTO("A1:A"&E4)));AGGREGA(14;6;E$7:E$31/(D$7:D$31=F3);RIF.RIGA(INDIRETTO("A1:A"&F4))))
formula matrice da attivare con i tasti ctrl+maiuscolo+invio.
La formula va copiata e incollata in K32 (si adatta automaticamente) ecc...
 

pkrome59

Utente assiduo
Expert
26 Settembre 2015
2.604
145
61
Reggio Calabria
Office 2019
178
Ciao, se il discorso di assegnare un minimo di tre punti ai giocatori che rientrano nella somma del punteggio dei primi 11 che hanno conseguito voto 0, allora la formula in E32 va ulteriormente modificata, per cui:

In E32:
=SOMMA(SE(SOMMA((D$7:D$31="P")*(E$7:E$31))=0;3;MAX((D$7:D$31="P")*(E$7:E$31)));SE(AGGREGA(14;6;E$7:E$31/(D$7:D$31=D3);RIF.RIGA(INDIRETTO("A1:A"&D4)))=0;RIF.RIGA(INDIRETTO("A1:A"&D4))^0*3;AGGREGA(14;6;E$7:E$31/(D$7:D$31=D3);RIF.RIGA(INDIRETTO("A1:A"&D4))));SE(AGGREGA(14;6;E$7:E$31/(D$7:D$31=E3);RIF.RIGA(INDIRETTO("A1:A"&E4)))=0;RIF.RIGA(INDIRETTO("A1:A"&E4))^0*3;AGGREGA(14;6;E$7:E$31/(D$7:D$31=E3);RIF.RIGA(INDIRETTO("A1:A"&E4))));SE(AGGREGA(14;6;E$7:E$31/(D$7:D$31=F3);RIF.RIGA(INDIRETTO("A1:A"&F4)))=0;RIF.RIGA(INDIRETTO("A1:A"&F4))^0*3;AGGREGA(14;6;E$7:E$31/(D$7:D$31=F3);RIF.RIGA(INDIRETTO("A1:A"&F4)))))
formula matrice da attivare con i tasti ctrl+maiuscolo+invio.
La formula va copiata e incollata in K32 (si adatta automaticamente) ecc...
 

Sostieni ForumExcel

Aiutaci a sostenere le spese e a mantenere online la community attraverso una libera donazione!