Etirer une formule décaler avec mise à jour de la plage

Bonjour,

Si vous voulez la somme d'une seule ligne, décalée de 4 à chaque fois :

=SOMME(DECALER($D$59:$CE$59;(ligne($CL10)-ligne($CL$10))*4;))

Cdlt,

Edit : j'ai trouvé pour supprimer.
Merci :)

Oui, il faut que vous éditiez votre code, repassiez sur la pièce et appuyez sur la touche de suppression.

Cdlt,

Cela semble fonctionner à merveille !
Est-il possible de m'expliquer la formule?

=SOMME(DECALER($D$59:$CE$59;(ligne($CL10)-ligne($CL$10))*4;))
Je ne comprends pas bien le :
(ligne($CL10)-ligne($CL$10))*4
Merci.

Ici $CL10 est la cellule en ligne 10 et en colonne CL (j'ai pris CL mais j'aurais pu faire autrement...).

Le dollar de CL permet de bloquer la colonne lors de la propagation de la formule. L'absence de $ devant 10 laisse la ligne libre. Dans les formules des lignes suivantes, cette référence deviendra $CL11, puis $CL12 ...

Pour l'autre $CL$10, on bloque bien la ligne (et la colonne). Donc Ligne($CL10)-LIGNE($CL$10) donne 0 (10-10), puis 1 (11-10), puis 2 (12-10)...

On multiplie par 4 et on obtient le décalage (0, 4, 8, ...) de lignes.

Cdlt,

Bonjour, je vois un peu mieux.
Je vais essayer de faire le même pour étirer la formule :
=SOMMEPROD((D52:CE52)*(D59:CE59<>"AJ")*(D59:CE59<>"NE")*(D59:CE59<>""))
je reviendrais si je n'y arrive pas.
Merci de votre aide en tout cas :)

J'ai essayé, mais je ne suis pas parvenu à faire ce que je voulais.
Je dois additionner la plage D52:CE52 si D59:CE59 ne contient pas "AJ" (et 3 autres critères), mais gardons juste AJ pour l'exemple.

cela fonctionne avec :
=SOMMEPROD((D52:CE52)*(D59:CE59<>"AJ")*(D59:CE59<>"NE")*(D59:CE59<>"")*(D59:CE59<>"NC"))
formule tirée du forum (et j'en remercie encore l'auteur).
Seulement, je ne peux pas la tirer vers le bas car, comme pour l'autre problème, la plage de comparaison suivante est D63:CE63 et si je tire la formule vers le bas, le D59 devient D60.
J'ai essayé en m'inspirant de la formule d'avant, mais cela me donne une erreur de valeur.

Voici ce que j'ai essayé :
=SOMMEPROD(($D$52:$CE$52)*(DECALER($D$59:$CE$59;(LIGNE($CL10)-LIGNE($CL$10))*4;))<>"AJ")*(D59:CE59<>"NE")*(D59:CE59<>"")*(D59:CE59<>"NC")

j'ai remplacé (D59:CE59<>"AJ") par (DECALER($D$59:$CE$59;(LIGNE($CL10)-LIGNE($CL$10))*4;))<>"AJ")
en espérant que cela donne le même effet que tout à l'heure, mais non.
Avez-vous une idée?

Je remets le fichier, la formule se trouve en CM10

Je vous remercie de votre aide précieuse.

Bonjour nico,

Honnêtement, la présentation est vraiment pas idéale, je n'ai pas compris grand-chose. Je pense que tu y gagnerais peut-être à repenser la structure de manière à avoir des tableaux sans colonnes répétées quitte à rajouter des colonnes servant de clés... Il faut à tout prix éviter les cellules fusionnées également, c'est une source d'e**erdes :).

En tout cas, je te propose une formule (le DECALER posait problème en fait), tu me diras si tu obtiens les résultats espérés :

=SOMMEPROD(($D$52:$CE$52)*(--ESTNUM(DECALER($D$59:$CE$59;TRANSPOSE((LIGNE($CL10)-LIGNE($CL$10))*4);0))))

Cdlt,

Bonsoir,

Je vais regarder votre formule :)
Niveau présentation, la seule chose dont je pouvais me passer, c'est le tableau en dessous.
Mais je pensais que ça serait plus simple pour la dernière somme avec les conditions si les nombres étaient les uns à côtés des autres.
Visiblement non, car j'ai du venir ici :( .

Maintenant, si vous savez comment faire pour le faire directement, comme ceci :

Avec les conditions pour le jaune, je suis preneur.
Pour le rouge, qui peut contenir AJ ANJ NE NC NR, la somme compte le texte comme +0.

J'avais regroupé les valeurs rouges les une à côtés des autres (le tableau en dessous) pour utiliser une plage pour la formule décaler,

Pour la fusion, j'essaye d'éviter mais quand je dois diviser un contrôle en 4 parties (S/SF/SA/T) je dois bien fusionner au dessus.
J'essaye cependant de ne pas prendre de cellules fusionnées dans une formule.

-----------------------

Pour la formule, si je comprends bien, elle vérifie simplement s'il y a un nombre ou pas.
Le problème, c'est qu'il faut faire la somme si c'est un nombre OU alors ANJ ou NR (mais pas AJ, NC NE).

Un peu comme le fait :
=SOMMEPROD((D52:CE52)*(D59:CE59<>"AJ")*(D59:CE59<>"NE")*(D59:CE59<>"")*(D59:CE59<>"NC"))

Elle additionne si c'est pas AJ, NE ou NC (et j'ai limité le choix d'écriture à un nombre ou à un texte prédéfini !!> AJ, NE etc...)

--------------------------

Pour éclaircir les choses, c'est un carnet de côtes.
Chaque contrôle est divisé en 4 parties (théorie, exercices et dépassement + total).
j'entre chaque partie du contrôle et ça me permet d'avoir les points du bulletin à la fin (après la ligne noire), mais aussi en détail ce qui pose problème : la théorie, les exercices ou le dépassement en ayant par exemple :
théorie (S) : 15/20
exercice (SF): 14/20
Dépassement (SA) : 13/20
total bulletin 14/20

L'histoire des lettres maintenant.

Exemple avec un AJ (absence justifiée)
Si un élève est absent, je mets AJ dans S (du 1ere contrôle). Donc, le AJ ne doit pas compter dans sa note car c'est justifié.
dans le résumé (partie S) x sur y , le total S du contrôle ne peut être compté dans le nombre y.
Par contre, s'il est anj (non justifiée), il faut bien prendre en compte le total S du contrôle dans le y afin de pénaliser l'élève et compter comme un 0.

Imaginons deux contrôles avec les 3 parties :
C1 S = 4 / 5
c1 SF = 9/10
c1 SA = 2/5
c1 T = 15 /20

c2 S = 3/10
c2 SF = 7/10
c2 SA = 4/5
c2 T = 14 /20

Total bulletin
Total S = 7/10
total SF = 16/20
total SA = 6/10

Si on rajoute un contrôle.
C3 S = ANJ ... (max /5)
Il a séché le cours ! Donc au total, il doit avoir pour total S 7/15 (10 + 5) le C3 S est pris en compte
si C'est AJ, il aura au total 7/10 (le 5 du C3 S ne sera pas pris en compte car il est justifié)

Résultat au bulletin en savoir, il aura
S'il a séché le contrôle 3 : 7/15
S'il justifie son absence au contrôle 3 : 7/10
D'où l'importance des conditions dans la somme des points maximums

J'espère être clair.

Merci de votre aide:)

Ce sera peut-être mieux avec le fichier en retour (qui ne tient pas compte de certains éléments de ta réponse)

Au lieu de procéder avec des <>"NE", dans le sommeprod, on peut sommer les conditions :

((--estnum(ref)) + (ref="ANJ") + (ref="NR"))

Ca renverra 1 si l'une des conditions est vraie.

Sinon, il y a l'option SOMMEPROD(SOMME.SI.ENS(plagesomme;plagecritere;{">0";"ANJ";"NR"})) qui fait également la somme pour chacun des critères (avec le ">0" pratique pour les nombres car j'ai cru comprendre qu'il n'y avait que des nombres positifs).

Cdlt,

La première formule ne fonctionne pas vraiment.
Elle semble multiplié la côte par le maximum (1/10 = + 10 dans la somme, 2/10 = +20 dans la somme)
la deuxième fonctionne, mais sans tenir compte des conditions de lettres.
En ayant de sommer les conditions avec le + ref, j'obtiens des erreurs de valeurs ####

Le problème en soit n'est pas spécialement la formulation, car la sommeprod donnée tantôt fonctionnait. C'est simplement le fait de pouvoir l'étirer sur 3 x 40 cellules (S/SF/SA), j'ai pas spécialement envie de changer 3/4 plages dans 120 cellules manuellement.

et oui, les nombres sont >=0 et <= au max (bloqué dans la saisie d'informations)

Merci

Visiblement :
=SOMMEPROD($D$9:$CE$9*($D$8:$CE$8=$CK$8)*(--ESTNUM(D12:CE12)+ESTREF("AJ")+ESTREF("NE")+ESTREF("nc")))
ne donne plus d'erreur.
Seulement j'ai l'impression que le estnum prend le pas sur tout le reste.
En gros, je peux rentrer ce que je veux comme texte (aj, anj ..) c'est considéré comme sans nombre et donc pas pris en compte.
Cependant, ça ne doit pas être pris en compte que dans certains cas.

Merci

Oui mais j'ai commencé à répondre avant de voir la fin de ton commentaire (je crois que tu l'as édité ?).

Et donc je n'ai pas pris en compte ces paramètres, ne sachant pas ce que représentaient les valeurs en ligne 9.

Voici un nouvel essai avec le somme.si.ens abordé (mais que j'ai correctement formulé cette fois). Je pense que ça devrait être bon maintenant.

J'ai juste modifié la formule donc il ne faut plus tenir compte de ce que j'avais pu commenter précédemment.

Oui, j'ai édité, désolé.

Dingue, ça semble fonctionner.

J'ai mis un peu de temps à comprendre, mais je pense avoir compris.
En gros, il s'agit d'une double condition?
Pour ajouter D9 à la somme, il faut que D10 = nombre ou lettre spécifique ET que D8 soit un S?
Je suppose que c'est ça, car si je tire le S, ça ne s'ajoute plus.
Donc en gros, il parcourt les trois lignes en même temps et analyse colonne par colonne si les 2 cellules valident l'acceptation de la troisième (celle du milieu) dans la somme?

Visiblement, plus besoin du gros bazar en dessous de tout ça.
Merci

Edit : sans oublier la cellule S que vous avez repiqué pour l'utiliser comme condition si j'ai bien compris.

Exactement, je crois que tu as tout compris !

En fait, il y a un critère sur la ligne 8 (ici, il faut que ce soit un "S", qu'on mentionne par référence CK8).

Puis il y a 3 critères additionnés sur la ligne 10 : valeur >0, chaine = "ANJ", chaine "NR". On pourrait les obtenir par référence également en se créant une petite liste en A1:A3 par exemple.

Le sommeprod permet de réaliser en fait la somme des 3 somme.si.ens pour chacun de ces critères (le critère en ligne 8 étant quant à lui valable pour chaque somme.si.ens). Attention cependant, un critère supplémentaire comme "<20" poserait problème car il partagerait des valeurs avec l'ensemble ">0". Il y aurait toutefois une solution qui consisterait à rajouter un ensemble de critères pour affiner l'intersection et annuler l'effet de la double prise en compte des valeurs entre 0 et 20.

Donc, pour résumer, on a l'intersection du critère en ligne 8 avec l'union des 3 critères en ligne 10.

Je ne connaissais pas vraiment ces formules avant, pourtant ça fait des années que je m'amuse avec excel.
La dernière fois que j'ai fait ça, j'avais 10-15 IF pour mon cahier avec une seule colonne par contrôle ^^.
petit extrait pour le plaisir des yeux d'un pro :
=SI(ESTERREUR(AI9);"erreur";SI(ET(NB.VIDE(D9:U9)=18;AI9=0);"";SI(ESTNUM(AI9);(SI(OU(D9="AJ";D9="");0;$D$8)+SI(OU(E9="AJ";E9="");0;$E$8)+SI(OU(F9="AJ";F9="");0;$F$8)+SI(OU(G9="AJ";G9="");0;$G$8)+SI(OU(H9="AJ";H9="");0;$H$8)+SI(OU(I9="AJ";I9="");0;$I$8)+SI(OU(J9="AJ";J9="");0;$J$8)+SI(OU(K9="AJ";K9="");0;$K$8)+SI(OU(L9="AJ";L9="");0;$L$8)+SI(OU(M9="AJ";M9="");0;$M$8)+SI(OU(N9="AJ";N9="");0;$N$8)+SI(OU(O9="AJ";O9="");0;$O$8)+SI(OU(P9="AJ";P9="");0;$P$8)+SI(OU(Q9="AJ";Q9="");0;$Q$8)+SI(OU(R9="AJ";R9="");0;$R$8)+SI(OU(S9="AJ";S9="");0;$S$8)+SI(OU(T9="AJ";T9="");0;$T$8)+SI(OU(U9="AJ";U9="");0;$U$8));"erreur")))

Je vais mettre tout ça en forme demain, j'espère ne plus revenir pour ça.
Mais je garderai mon post sous la main au cas où .

Un très grand merci pour ta patience et pour les explications plus que claires et précises.
C'était un plaisir de travailler, mais également de te lire.

Je t'en prie et te remercie pour ces compliments, je craignais ne pas avoir été très clair...

En tout cas, je te conseille (encore) de t'intéresser tout particulièrement à SOMMEPROD qui offre de belles possibilités (et qui évite les sommes interminables de SI ).

Au sein d'une formule (dans un SOMMEPROD par exemple) tu peux sélectionner une matrice (A1:A10="NR") et utiliser la touche F9 pour inspecter les valeurs renvoyées. Ca permet de mieux comprendre les étapes de calcul...

A bientôt peut-être,

Cdlt,

Merci pour l'astuce.
Je vais effectivement aller me documenter sur SOMMEPROD
Encore merci et joyeuses fêtes de fin d'année :)

Merci ! Je te souhaite de passer d'excellentes fêtes et une bonne fin d'année également !