XL 2013 aide création d'un tableau

[stef12345]

Bonjour,

je suis en train de faire un tableau pour des stats que je joins pour que cela soit plus clair. dans les colonnes C-D il y a le calcul pour la totalités des colonnes représentants les mesures (a partir des colonnes I:J), lorsque la mesure est finie c'est a dire que la case fin est remplie, une mise en forme conditionnelle met la colonne en rouge, j'aimerai si cela est possible que les colonne AB compte uniquement les mesure en cours donc celle qui ne sont pas en rouge pour avoir au final les stat pour l'ensemble (mesure ouverte et fermées) mais aussi les stat uniquement pour les mesure en cours,
j'espère avoir été suffisamment clair pour que vous puissiez m'aider.
merci

 

[AtTheOne]

Bonne nuit à tous, bonne nuit @stef12345

Essaie cette formule (à valider par CTRL+MAJ+ENTRÉE) dans une cellule non fusionnée. La ligne contenant le mot "Mesure" sur 2 colonnes étant la ligne 4 :

=SOMME(--(NB.SI(DECALER(INDIRECT(ADRESSE(4;(COLONNE(DECALER($A$1;0;0;1;NB.SI($4:$4;"Mesure")))-1)*2+9;;;"Feuil1"));0;0;8;2);"")=4))

Je l'ai placée en A16 et j'ai refusionné les cellules A16 et B16 après
Cela compte les plages de Mesure pour lesquelles il manque une donnée (supposée être la date de fin). Les plages concernées comptent alors 4 cellules vides (sans tenir compte des fusions).

• NB.SI($4:$4;"Mesure") compte le nombre de plages de mesure
• INDIRECT(ADRESSE(4;(COLONNE(DECALER($A$1;0;0;1;NB.SI($4:$4;"Mesure")))-1)*2+9;;;"Feuil1")) renvoie les cellules qui contiennent "Mesure" (la 1ère des 2 cellules fusionnées)
• DECALER(...;0;0;8;2) renvoie les plages "Mesure" (8 lignes, 2 colonnes)
• NB.SI(...;"") compte le nombre de cellules vides de ces plages
• --NB.SI(...)=4 renvoie 1 pour les plages comptant 4 cellules vides, 0 pour les autres
• SOMME(...) fait le total.

Amicalement
Alain

 

[stef12345]

Bonne nuit à tous, bonne nuit @stef12345

Essaie cette formule (à valider par CTRL+MAJ+ENTRÉE) dans une cellule non fusionnée. La ligne contenant le mot "Mesure" sur 2 colonnes étant la ligne 4 :

=SOMME(--(NB.SI(DECALER(INDIRECT(ADRESSE(4;(COLONNE(DECALER($A$1;0;0;1;NB.SI($4:$4;"Mesure")))-1)*2+9;;;"Feuil1"));0;0;8;2);"")=4))

Je l'ai placée en A16 et j'ai refusionné les cellules A16 et B16 après
Cela compte les plages de Mesure pour lesquelles il manque une donnée (supposée être la date de fin). Les plages concernées comptent alors 4 cellules vides (sans tenir compte des fusions).

• NB.SI($4:$4;"Mesure") compte le nombre de plages de mesure
• INDIRECT(ADRESSE(4;(COLONNE(DECALER($A$1;0;0;1;NB.SI($4:$4;"Mesure")))-1)*2+9;;;"Feuil1")) renvoie les cellules qui contiennent "Mesure" (la 1ère des 2 cellules fusionnées)
• DECALER(...;0;0;8;2) renvoie les plages "Mesure" (8 lignes, 2 colonnes)
• NB.SI(...;"") compte le nombre de cellules vides de ces plages
• --NB.SI(...)=4 renvoie 1 pour les plages comptant 4 cellules vides, 0 pour les autres
• SOMME(...) fait le total.

Amicalement
Alain

Bonjour Alain,
merci de ton aide mais le fichier joins ne semble pas marché ou je n'ai pas compris la procédure.

 

[AtTheOne]

Bonjour à tous, bonjour @stef12345
La formule matricielle (validée par CTRL MAJ ENTRÉE) :

=SOMME(--(NB.SI(DECALER(INDIRECT(ADRESSE(4;(COLONNE(DECALER($A$1;0;0;1;NB.SI($4:$4;"Mesure")))-1)*2+9;;;"Feuil1"));0;0;8;2);"")=4))

compte les plages de cellules de 8 lignes et 2 colonnes "Mesure" pour lesquelles il manque 1 information :

Dans l'exemple ci-dessus il y a 2 plages concernées

La cellule A16 renvoie 2 :

Peut-être est-ce moi qui n'ai pas bien compris ton besoin ?
Reprécise le s'il te plais.

Je t'ai remis le fichier en PJ, il fonctionne avec Office 2007 et 2021.

Amicalement
Alain

 

[stef12345]

Bonjour à tous, bonjour @stef12345
La formule matricielle (validée par CTRL MAJ ENTRÉE) :

=SOMME(--(NB.SI(DECALER(INDIRECT(ADRESSE(4;(COLONNE(DECALER($A$1;0;0;1;NB.SI($4:$4;"Mesure")))-1)*2+9;;;"Feuil1"));0;0;8;2);"")=4))

Regarde la pièce jointe 1132052

compte les plages de cellules de 8 lignes et 2 colonnes "Mesure" pour lesquelles il manque 1 information :
Regarde la pièce jointe 1132053
Dans l'exemple ci-dessus il y a 2 plages concernées

La cellule A16 renvoie 2 :
Regarde la pièce jointe 1132055

Peut-être est-ce moi qui n'ai pas bien compris ton besoin ?
Reprécise le s'il te plais.

Je t'ai remis le fichier en PJ, il fonctionne avec Office 2007 et 2021.

Amicalement
Alain

bonjour Alain,
je ne suis pas assez clair dans mes explications, en fait l'effectif au 31/12 est mal nommé il ne comptabilise pas le nombre de mesure mais le nombre de fois où les lignes sont cochées pour les mesures qui ne sont pas finies, dans la nouvelle pièce jointe j'ai rempli la colonne A de manière manuelle mais j'aimerais que cela se fasse automatiquement comme pour les colonne CD
merci

 

[AtTheOne]

Bonsoir à tous, bonsoir @stef12345,

Ok je pense avoir compris.

Du coup la formule devient (j'utilise SOMMEPROD pour éviter la validation de formule matricielle) :

=SOMMEPROD(
     (NB.SI(DECALER(INDIRECT(ADRESSE(11;(COLONNE(DECALER($A$1;0;0;1;NB.SI($4:$4;"Mesure")))-1)*2+9));0;1;1;1);"")=1)
    *(NB.SI(DECALER(INDIRECT(ADRESSE(LIGNE();(COLONNE(DECALER($A$1;0;0;1;NB.SI($4:$4;"Mesure")))-1)*2+9));0;0;1;2);"<>")=1)
          )

• DECALER(INDIRECT(ADRESSE(11;(COLONNE(DECALER($A$1;0;0;1;NB.SI($4:$4;"Mesure")))-1)*2+9));0;1;1;1) renvoie les cellules qui contiennent la date de fin
• DECALER(INDIRECT(ADRESSE(LIGNE();(COLONNE(DECALER($A$1;0;0;1;NB.SI($4:$4;"Mesure")))-1)*2+9));0;0;1;2) renvoie les cellules fusionnées de la ligne en cours qui contiennent ou non un X

Voir le fichier joint
Bonne nuit à tous
Amicalement
Alain

 

[stef12345]

Bonsoir à tous, bonsoir @stef12345,

Ok je pense avoir compris.

Du coup la formule devient (j'utilise SOMMEPROD pour éviter la validation de formule matricielle) :

=SOMMEPROD(
     (NB.SI(DECALER(INDIRECT(ADRESSE(11;(COLONNE(DECALER($A$1;0;0;1;NB.SI($4:$4;"Mesure")))-1)*2+9));0;1;1;1);"")=1)
    *(NB.SI(DECALER(INDIRECT(ADRESSE(LIGNE();(COLONNE(DECALER($A$1;0;0;1;NB.SI($4:$4;"Mesure")))-1)*2+9));0;0;1;2);"<>")=1)
          )

• DECALER(INDIRECT(ADRESSE(11;(COLONNE(DECALER($A$1;0;0;1;NB.SI($4:$4;"Mesure")))-1)*2+9));0;1;1;1) renvoie les cellules qui contiennent la date de fin
• DECALER(INDIRECT(ADRESSE(LIGNE();(COLONNE(DECALER($A$1;0;0;1;NB.SI($4:$4;"Mesure")))-1)*2+9));0;0;1;2) renvoie les cellules fusionnées de la ligne en cours qui contiennent ou non un X

Voir le fichier joint
Bonne nuit à tous
Amicalement
Alain

bonjour Alain et un grand merci c'est exactement ce qu'il me fallait cela va grandement me faciliter les choses, merci et bonne jurnée