Recherche MFC somme.si horizontal ou sommeprod ???

Kiriko

XLDnaute Occasionnel
Bonjour à tous,
J'essaye de trouver 2 MFC "particulières".
Le fichier joint est assez explicite je pense, mais voici ce que j'essaye de faire :
Présentation du fichier :
Nous avons des dates en entêtes de colonnes, de CH3 à QG3 (pour info les dates se suivent, jour après jour, sans trous et ne seront pas modifiées)
Ensuite, chaque ligne est individuelle à partir de la ligne 4 (1 ligne = 1personne)
- 1ère MFC recherchée : sachant que j’entends la semaine du lundi au dimanche, je voudrais mettre en surbrillance rouge tous les dépassements de 35 (ce sont des heures pour info) de la même semaine sur une même ligne (pour une même personne donc, de CH à QG donc)
- 2ème MFC recherchée : Je souhaite mettre en surbrillance dès que plus de 6 cases consécutives sont non vides sur la même ligne (toujours de CH à QG et à partir de la ligne 4).
J'ai fais quelques essais avec des somme.si, des sommeprod et autres mais sans succès....
Dans l'idéal, je souhaiterais faire fonctionner ceci sans avoir besoin de rajouter quoi que ce soit dans les cellules du fichier (comme par exemple les numéros de semaines en ligne 1) et donc que les MFC se suffisent à elles-mêmes avec le fichier en l'état...
Merci par avance à ceux qui voudront bien se pencher sur mon problème.
Également, j'ai eu du mal à trouver un titre à ce post, si vous avez une idée de comment le renommer afin de faciliter la recherche pour des utilisateurs futurs, n'hésitez pas....
J'ai bien trouvé quelques posts qui semblent traiter d'un sujet proche, mais je n'ai pas réussi à adapter...
 

Pièces jointes

  • Recherche 2 MFC.xlsx
    23.3 KB · Affichages: 39

Kiriko

XLDnaute Occasionnel
Re : Recherche MFC somme.si horizontal ou sommeprod ???

Bonjour chris, merci beaucoup, ça marche au poil...
J'ai à peine modifié :
=SOMME(DECALER(CH4;0;(JOURSEM(CH$3;2)*-1)+1;1;7))>35
Pour attaquer à partir de la colonne CH, et j'ai enlevé un $ pour prendre en compte toutes les lignes....
que j'applique à
Code:
=$CH$4:$QG$1048576

Pour la 2nde MFC, j'ai creusé autour de NBVAL.
Ce n'est pas exactement ce que je voulais faire, mais j'obtiens un résultat avec :
=NBVAL(CH4:CN4)>6
que j'applique à
=$CN$4:$QG$1048576
.
En effet, cette Mfc met bien le 7ème (et suivants) jour consécutif en rouge, ce qui peut convenir, mais l'idée de départ était de mettre toute la suite des journées consécutives en rouge....
Mais à y bien réfléchir, je pense que la MFC devrait être bien complexe en ce cas, et alourdir de beaucoup le fichier.... Auquel cas je préfère un fichier qui ne rame pas trop....
En tous les cas, encore merci chris !!!
 
Dernière édition:

chris

XLDnaute Barbatruc
Re : Recherche MFC somme.si horizontal ou sommeprod ???

Re

Effectivement j'avais focalisé sur le 1er lundi.

Par contre évite une MFC sur 1 millions de lignes : cela va ramer alors qu'il y a peu de chance que tu consommes ces lignes.

Tu peux mettre les lignes remplies, hors titres, sous forme de tableau, sans cocher lignes en tête puis tu masques la ligne d'en-tête (ligne ou option du tableau)
puis tu enlève la mise en forme tableau si elle ne te plait pas, tu appliques la MFC à la hauteur remplie (ligne 5 à 11) : toute nouvelles ligne en héritera et cela évite de prévoir trop ou pas assez.
 

Kiriko

XLDnaute Occasionnel
Re : Recherche MFC somme.si horizontal ou sommeprod ???

Encore merci chris,
Je ne suis pas très à l'aise avec les vrais "tableaux" excel, c'est vrai que j'ai pris l'habitude de fonctionner avec des plages de données...
Notamment sur les bêtises à ne pas faire pour ne pas "sortir" involontairement du tableau, la gestion des références à des cellules hors tableau (il m'arrive par exemple de devoir tester des conditions avec la ligne du dessous, ce qui me pose souvent des problèmes avec la dernière ligne du tableau justement). Mais bon, ceci est complètement hors sujet, charge à moi de trouver des réponses sur ce thème (si d'aventure tu ou d'autres avais un conseil de site approchant le sujet de façon un peu avancée, je ne trouve que "la base" dans mes recherches).
Encore merci chris !!

P.S. : J'ai encore modifié un peu la MFC, afin de ne mettre en surbrillance que les cellules non vides de la semaine qui dépasse les 35, et non pas la semaine entière cellules vides comprises. J'ai aussi rajouté des exclusions (des 3 1ères lignes) afin de pouvoir appliquer la MFC sur les colonnes entières (=$CH:$QG) mais ce point va à l'encontre de ton dernier conseil du coup :
Code:
=ET(CH1<>"";SOMME(DECALER(CH1;0;(JOURSEM(CH$3;2)*-1)+1;1;7))>35;LIGNE(CH1)<>1;LIGNE(CH1)<>2;LIGNE(CH1)<>3)
 

chris

XLDnaute Barbatruc
Re : Recherche MFC somme.si horizontal ou sommeprod ???

Bonjour

Il est souvent possible d'inverser le test et tester la ligne du dessus !

Quoi qu'il en soit je limiterais à 100 ou 1000 lignes, selon ce que tu risques d'utiliser, avec une ligne de couleur marquant bien la fin au cas où on y arrive. On peut alors modifier la plage d'application

Pour les tableaux : de même si on met des règles de validation sur les cellules cela fait partie de l'héritage.
La seule contrainte est d'avoir une approche base de données dont des formules, le cas échéant, qui comme dans une requête de BD, sont applicables à l'ensemble des lignes.
Petit inconvénient comme dans ton cas ou autres similaires, l'impossibilité d'avoir des titres non textuels.

Ici le seul avantage serait l'héritage des formats, MFC, validation... mais a priori rien d'autre.
 

Kiriko

XLDnaute Occasionnel
Re : Recherche MFC somme.si horizontal ou sommeprod ???

Je me répète mais merci encore chris pour ton temps et ta pédagogie...
Mon soucis principal est que mon (mes) fichiers sont destinés à d'autres, et que je ne sais pas comment empêcher les "mauvaises manip".
De simples "copier-coller" de lignes extérieures à l'onglet, et même à l’intérieur du même onglet, mettent le souk dans les MFC et dans les formules dans le cas de plages de données. J'ai eu testé des macro évènementielles qui annulaient les collages et les remplaçaient par des collages de valeurs, mais c'est du bidouillage et c'est surtout très lourd....
La solution idéale semble bien être l'utilisation de vrais "tableaux", mais là aussi j'ai eu des soucis lors de collages ou d'insertion de lignes, voire lors de "vidages" puis "remplissages" par macro de ces tableaux...
Bref, il faut que je m'y repemche....
 

Discussions similaires

Réponses
0
Affichages
325
Réponses
3
Affichages
388

Statistiques des forums

Discussions
311 733
Messages
2 082 019
Membres
101 872
dernier inscrit
Colin T