Omettre certaines plages

[Calvus]

Bonjour,

Voici un fichier que j'ai réalisé il y quelque temps avec l'aide de David, dont l'occasion m'est donnée de le remercier à nouveau.

Voulant indexer les tâches contenues dans un planning, je me heurte à un problème de plages de cellules horaires que je ne souhaite pas prendre en compte.
Pour celà, j'ai utilisé une autre formule inspirée par le même David84, mais malheureusement, je vois toutes les valeurs apparaître,même celle contenant des heures.

Voici la formule :
SI(LIGNES($1:1)<=NBVAL(zone_2);INDIRECT(ADRESSE(MOD(PETITE.VALEUR(SI(zone_2<>"";COLONNE(zone_2)*10^5+LIGNE(zone_2));LIGNES($1:1));10^5);PETITE.VALEUR(SI(zone_2<>"";COLONNE(zone_2));LIGNES($1:1))));"")

J'ai essayé en ajoutant si( esttexte(), mais le résultat est le même.

Une solution consisterait à utiliser 7 zones différentes, mais je n'arrive pas à les regrouper en une seule, comme par exemple Zone=zone1+zone2+zone3..etc.

La plage de formules concernée par cette demande est celle sur fond jaune de S1 à S14.

Eventuellement, si cette première étape est résolue, quelle serait la modification de la formule pour éviter les doublons ? ( je vais continuer à chercher de ce coté là)

Merci.

 

[Monique]

Re : Omettre certaines plages

Bonjour,

En ajoutant ce critère : SI(ESTTEXTE(zone_2)

En nommant « Date » la plage B2:O2 et en modifiant la condition en début de formule : SOMME((zone_2<>"")*(Date<>""))
=SI(LIGNES($1:1)<=SOMME((zone_2<>"")*(Date<>""))

[FONT=Verdana][FONT=Verdana]=SI(LIGNES($1:1)<=[COLOR=blue]SOMME((zone_2<>"")*(Date<>""));[/COLOR]INDIRECT(ADRESSE(MOD(PETITE.VALEUR(SI(zone_2<>"";[B][COLOR=blue]SI(ESTTEXTE(zone_2);[/COLOR][/B]COLONNE(zone_2)*10^5+LIGNE(zone_2)));LIGNES($1:1));10^5);PETITE.VALEUR(SI(zone_2<>"";[COLOR=blue][B]SI(ESTTEXTE(zone_2);[/B][/COLOR]COLONNE(zone_2)));LIGNES($1:1))));"")[/FONT]
[/FONT]

 

[Calvus]

Re : Omettre certaines plages

Bonjour Monique,

Merci beaucoup, c'est impeccable.

une idée pour éviter les doublons ?

Merci

 

[david84]

Re : Omettre certaines plages

Bonjour Calvus, monique,
pour éviter les doublons, une formule de Monique.
A+

 

[Calvus]

Re : Omettre certaines plages

Re, bonjour David,

Merci.

Ca ne marche pas encore sur mon fichier original, je ne comprend pas pourquoi !!
Je dois m'en aller, et verrai ça ce soir à mon retour.
Je te tiendrai au courant dans tous les cas.

Merci à tous les deux.

A+

 

[Monique]

Re : Omettre certaines plages

Re,

Par ordre alphabétique (tant qu'on y est)
Une formule de David84

 

[Calvus]

Re : Omettre certaines plages

Bonsoir le fil,
Bonsoir Monique, bonsoir David,

Encore merci à vous deux. J'ai pu bien avancer.

David, voulant automatiser la liste d'activités, ce que j'ai pu faire grâce à vous, je me vois maintenant confronté à un nouveau problème.

Je ne peux pas exploiter directement les données si je n'ai pas une heure de début ET une heure de fin. Ca pose des problèmes pour les évènements uniques tels que les trajets.

J'ai cherché à adapter la formule pour exclure certains mots, mais en vain.

Je joins la nouvelle mouture de mon fichier, avec une feuille test, fonctionnant correctement mais où les données sont entrées manuellement de A33 à A42, et une feuille Problème, avec les mêmes données automatiques, mais posant des problèmes....

En l'occurrence, "trajet" n'étant pas renseigné, donne 107:20h alors qu'on devrait avoir 0.

J'ai placé les différents tableaux de calcul à partir de la ligne 45.

Concernant ces tableaux, j'ai entré les formules manuellement. Je pense qu'on doit pouvoir trouver une formule pour automatiser ça avec Decaler, mais là aussi j'ai séché !

Peux tu jeter un coup d'oeil à tout ça, et me dire s'il te manque des précisions ?

Merci d'avance.

Cordialement.

 

[david84]

Re : Omettre certaines plages

Re
désolé Calvus mais là je m'y perds. Indépendamment du fait que cela fait quelque temps que j'avais traité ce fichier et que je ne l'ai plus en tête il y a des choses que je ne comprends pas :
feuille test :
que veux tu obtenir en A33:A42 ? D'où sort l'activité Piano non présente dans le planning ?
De plus dans la feuille Problème, j'ai l'impression qu'il y a des formules qui font doublon.
Veux-tu conserver tous les tableaux de ta feuille ou les as tu par obligation ?
Le plus simple serait que tu marques manuellement sur ta feuille test le résultat final attendu et non verrons ce qu'il convient de faire ( mais en ce moment pas trop le temps donc si je le passe à tenter de comprendre ce que tu veux, cela ne va pas te faire avancer).
A+

 

[Calvus]

Re : Omettre certaines plages

Re,

que veux tu obtenir en A33:A42 ? D'où sort l'activité Piano non présente dans le planning ?

Je voudrais en effet obtenir la liste des activités. Piano a été simplement effacé dans le planning, j'aurais du l'effacer en A33. Mais si c'est manuellement, du fait que c'est une activité récurrente, elle pourrait aussi bien y rester, mais c'est un autre problème.

De plus dans la feuille Problème, j'ai l'impression qu'il y a des formules qui font doublon.

Si tu parles des formules en col S et T, c'est parce que Monique m'a renvoyé le fichier (voir plus haut) avec une possibilité d'indexer les activités soit par ordre d'apparition dans la semaine, ou alphabétique. Ne nous attachons qu'à la 1ere solution.

Autrement, je ne vois pas où il y aurait des doublons.

Veux-tu conserver tous les tableaux de ta feuille ou les as tu par obligation ?

Parles tu des tableaux à partir de la ligne 45 ? Ceux sont ceux qui permettent le calcul des heures, et de ton fait , ils sont donc nécessaires. Je pourrais au pire les raccourcir un peu.

Le plus simple serait que tu marques manuellement sur ta feuille test le résultat final attendu

Les résultats attendus sont bien ceux de la feuille test.

Dernière chose, j'aurai une feuille par semaine, ce qui semble d'ores et déjà poser des problèmes pour les formules nommées. Mais nous verrons celà plus tard.

Je te remercie David

 

[david84]

Re : Omettre certaines plages

Re
en gros, tu veux que la formule placée en A33:A42 ne prenne pas en compte "projet" et "trajet", c'est à dire ne prenne en compte que les activités qui ont des plages horaires renseignées ?
Si oui, les plages horaires rentrées dans zone_2 auront tjrs un début et une fin ?
Les cellules fusionnées sont elles indispensables (cela peut poser problème dans les formules) ?
A te lire

 

[Calvus]

Re : Omettre certaines plages

Re David,

Idéalement, j'aimerais retrouver ma liste automatiquement à partir de A33.
Mais comme tu peux le constater, la ligne A34:N34 trajet pose problème.
Idem pour projet , exemple :si on renseigne la cellule I11 en mettant 1:00, on obtient en I34 12H pour trajet, et 1H pour projet, ce qui est évidemment tout à fait erroné.

Ces évènements sont inférieurs à 30 mn et je pourrai calculer leur somme avec une fonction somme.si. S'ils sont supérieurs à 30mn, ils auront alors une heure de début et une heure de fin.
Il peut y en avoir d'autres différents.

Je renvoie le fichier, avec 3 feuilles, exemple1, exemple2 et exemple3.

En exemple 1 ,j'ai mis manuellement, en rouge et gras les résultats escomptés dans le tableau inférieur B33:N42, celui des calculs, et même MF dans le planning lui même B4:O32.
Je n'ai volontairement pas renseigné E38, afin que tu puisses voir le problème se posant.
Certaines cellules correspondant à trajet ou projet (B4:O32) peuvent ne pas être renseignées. Ex C26 ou E26 ou K8
On trouve enfin de A33 à A41 la liste identique et dans le même ordre que celle générée de S2à S10, et qui sert de vérification pour le moment.
Le tableau inférieur est limité à 10 activités pour le moment, mais je pense que je dois pouvoir insérer de nouvelles lignes. Il me faudra seulement rajouter des tableaux de calcul pour les nouvelles heures.
Ca me parait être le mieux à faire, mais est ce possible ?

En exemple 2 j'ai renseigné quelques plages pour trajet et projet.
J'ai laissé telle qu'elle la liste à partir de A33. On peut voir qu'elle n'est pas complète. Je pense que c'est dû à la définition du nom de zone_2 qui fait référence à la feuille précédente. J'ai contourné ce problème sur mon fichier original en remplaçant le nom de zone par $B$4:$O$32. ( y a t'il d'ailleurs un moyen pour que la zone_2 concerne à chaque fois une nouvelle feuille ? )
Il manque donc plusieurs activités.

Enfin, en exemple 3, j'ai ajouté manuellement un tableau sur la droite qui pourrait venir en complément s'il est trop compliqué de tout avoir en B33:O42.
C'est une idée, évidemment la moins bonne...

Je récapitule donc:

1/ Idéalement, avoir une feuille avec toutes les activités listées, que les plages horaires soient renseignées ou pas.

2/ Pour les activités inférieures à 30mn, et ne nécessitant donc pas d'heure de fin, faire la somme des cellules concernées, de façon journalière. Cf exemple1 E34. Il s'agit en fait de durées et non pas d'heures de début...

3/ Insérer automatiquement des lignes à partir de A42 si plus de 10 activités.

4/ Enfin, comme tu as pu le constater, le temps de calcul devient très important vu le nombre de matricielles, est-il possible de générer automatiquement la taille des tableaux de calculs. Par exemple, en feuille1 A45:O55 n'utilise qu'une ligne.

J'espère que c'est plus précis maintenant; par contre ça n'en n'est pas moins compliqué !

Merci de ta patience.

A bientôt.

PS: les cellules fusionnées ne sont pas indispensables, seulement plus esthétique (visuel !) et plus pratique pour faire des copier coller

 

[david84]

Re : Omettre certaines plages

Re
Bon, avant tout, je pense que tu devrais plutôt t'orienter vers une solution VBA parce que c'est lent, vus les matricielles et le nombre de tableaux.
Ceci dit, commençons par voir si tes demandes 1 et 2 sont réglées.

1/ Idéalement, avoir une feuille avec toutes les activités listées, que les plages horaires soient renseignées ou pas.

2/ Pour les activités inférieures à 30mn, et ne nécessitant donc pas d'heure de fin, faire la somme des cellules concernées, de façon journalière. Cf exemple1 E34. Il s'agit en fait de durées et non pas d'heures de début...

Ci-joint fichier où je n'ai gardé qu'un onglet (test) pour l'alléger.
La formule proposée n'est pas matricielle (c'est toujours ça !). Je me suis servi des tableaux individuels puisque tu avais tenté de le faire toi-même.
Teste et dis-nous ce qu'il en est.
A+

 

[Calvus]

Re : Omettre certaines plages

Re,

Bon sang de bonsoir ! David, comment fais tu ça !!

Après quelques tests, ça a l'air de fonctionner tout à fait correctement.
J'ai appliqué la formule d'automatisation de la liste et je n'ai pas eu de bog pour le moment.

J'ai pensé qu'il serait peut être possible d'adapter la formule afin de réduire les tableaux individuels. En effet, les activités en dehors du trajet se répètent rarement plus de 3 fois, voire même 2 dans la même journée, ce qui permettrait de réduire les tableaux à 3 lignes sans grand risque d'erreur. Il faudrait juste isoler un tableau à part pour le calcul des trajets. Je vais regarder ça, ce qui permettraot de ne pas trop ralentir le tout.

Je ferai des tests plus poussés ce soir sur plusieurs feuilles, en fait sur mon fichier original et te tiendrai au courant.
Il me semble tout de même qu'on (enfin que tu...) arrive au but.

Merci infiniment.

 

[Calvus]

Re : Omettre certaines plages

Bonjour le fil,
Bonjour David,

Comme dit plus haut, je te donne des nouvelles sur l'avancement de mon fichier.

Ci-joint la dernière mouture.

J'ai réduit le nombre de lignes des tableaux de calculs à 3, sauf pour projet et trajet, afin d'alléger le fichier et moins( ) ralentir les calculs.
J'ai donc adapté ta dernière formule en conséquence.

Ca me pose juste un problème si je dois ajouter des activités, et donc des lignes.
Suivant l'ordre d'insertion des lignes ( et je ne me souviens plus lequel est le bon), j'ai des valeurs erronées.
On ne peut pas insérer une ligne en A40 par exemple, et faire un copier coller de 110-114 à 115-119.
Il faut faire plusieurs manips, corriger, effacer, etc !
Bref, mais à part ça,ça fonctionne au poil.
Je te remercie donc.

A bientôt.

PS: je ne pense pas que ce soit possible par formule, mais on ne sait jamais...
Penses tu qu'il soit possible d'incrémenter automatiquement 4 nouvelles lignes pour le tableau, si une activité est ajoutée à partir de 10 ?