Formules : SI - ET - OU (panachage)

[chris63]

Bonjour à tous, bonjour le forum,
Depuis quelques jours j'essaye sans succès de compléter un fichier excel composé de 3 tableaux. Je n'arrive pas à trouver la formule idéale qui me permettrait de renseigner automatiquement les cellules des tableaux.
En abscisse, j'ai plusieurs colonnes (24) avec des heures de 00:00 à 23:59. En ordonnée, j'ai plusieurs lignes avec des heures H1 et H2 (H1 < H2).
Je souhaite pouvoir renseigner les tableaux de la manière suivante :
- tableau 1 --> marquer l'ensemble de la plage horaire comprise entre H1 et H2;
- tableau 2 --> marquer la plage horaire qui correspond à H1;
- tableau 3 --> marquer la plage horaire qui correspond à H2.
J'ai essayé avec les formules SI, ET et OU sans trouver la solution idéale. Pour un amateur le "mélange" des conditions n'est pas facile à réaliser.
Je joint le fichier excel qui me sert de test.
Merci de votre aide.
Bon WE à tous.

 

[wilfried_42]

Re : Formules : SI - ET - OU (panachage)

bonjour chris

ton fichier en retour, si j'ai vien compris ce que tu demandes

 

[wilfried_42]

Re : Formules : SI - ET - OU (panachage)

re:

voici la suite : le fichier etant trop important, pour le tableau 2 et 3, je n'ai fait que la 1ere ligne, il te suffit de glisser vers le bas

 

[Gorfael]

Re : Formules : SI - ET - OU (panachage)

Salut chris63, wilfried_42 et le forum

GRRRR😡 wilfried_42 m'a encore grillé, mais comme je l'ai fait, autant s'en servir 😛
Colonne L > colonne J : c'est logique, mais encore faut-il l'expliquer à Excel
J = 22:00 et L = 00:00 => erreur donc, il faut :
- L = 24:00

Edit : la vrai formule serait =TEMPS(24+1;0;0) ou =TEMPS(1;0;0) +1 pour ceux qui finissent à 1:00 le lendemain, ce qui revient à écrire 25:00:00, mais Excel le transforme en date et ça peut être problèmatique pour détecter une erreur
A+

 

[wilfried_42]

Re : Formules : SI - ET - OU (panachage)

bonjor Gorfael

Meme si tu saisis dans la colonne L, 24:00 ou 25:00, le critere N2 reste à 0:00 donc ne rentrera pas dans la selection, il faut aussi ajouter 24 aux criteres de selection N2, O2..................................

 

[chris63]

Re : Formules : SI - ET - OU (panachage)

Bonjour à vous 2,
Tout d'abord un grand merci pour l'aide que vous m'avez apporté et pour la célérité de votre réponse.
Il est certain que j'aurai pu y passer tout le WE sans y arriver. Je peux même vous avouer que je n'ai pas encore bien compris la construction de la formule.
J'ai un petit souci avec le tableau 1 qui ne marque pas la valeur 1 de début de plage. Ce doit être au niveau de la formule qui ne compare pas la bonne plage.
Encore merci de votre aide et bon WE.

 

[Gorfael]

Re : Formules : SI - ET - OU (panachage)

bonjor Gorfael

Meme si tu saisis dans la colonne L, 24:00 ou 25:00, le critere N2 reste à 0:00 donc ne rentrera pas dans la selection, il faut aussi ajouter 24 aux criteres de selection N2, O2..................................

Salut à tous
wilfried_42, je n'ai pas compris la même chose que toi 🙄
J'avoue ne pas avoir regardé les planning 2 et 3, (je vais les regarder)
Un planning journalier, pour moi, ne peut contenir 2 jours
Si tu veux savoir le nombre de personne en poste à chaque heure, par exemple. Il faut bien différencier sur le planning, celui qui a commencé la veille et qui finit à 1:00 et celui qui commence à 23:00 pour finir le lendemain.

Mais c'est ma vision du problème, et pas forcément celle de chris63
c'est l'avantage des forum 😛 , il y a toujours plus de manière d'aborder le problème posé que quand on est seul, face à excel 😀
A+

 

[wilfried_42]

Re : Formules : SI - ET - OU (panachage)

je ne comprendspas trop ce que tu dis, as tu regardé mon fichier ?

le tableau 1 est complet du moin, je le crois

 

[wilfried_42]

Re : Formules : SI - ET - OU (panachage)

Salut à tous
wilfried_42, je n'ai pas compris la même chose que toi 🙄
J'avoue ne pas avoir regardé les planning 2 et 3, (je vais les regarder)
Un planning journalier, pour moi, ne peut contenir 2 jours
Si tu veux savoir le nombre de personne en poste à chaque heure, par exemple. Il faut bien différencier sur le planning, celui qui a commencé la veille et qui finit à 1:00 et celui qui commence à 23:00 pour finir le lendemain.

Mais c'est ma vision du problème, et pas forcément celle de chris63
c'est l'avantage des forum 😛 , il y a toujours plus de manière d'aborder le problème posé que quand on est seul, face à excel 😀
A+

tu sais quand j'ai pris le lasseur, je pensais comme toi, c'est quand jai vu le resultat recherché, que je me suis tiré les cheveux lol

 

[Gorfael]

Re : Formules : SI - ET - OU (panachage)

tu sais quand j'ai pris le lasseur, je pensais comme toi, c'est quand jai vu le resultat recherché, que je me suis tiré les cheveux lol

Salut
Moi, je peux plus 😀 (enfin presque)

Autre proposition sur les 4 premières lignes, mais recopiables
On ne renseigne que la feuille Jour. Les feuilles J+1 et J-1 sont mises à jour par formules
Si H1>H2 on considère que H1 commence à J-1
Si H2 > 24:00 on considère que H2 finit à J+1
Pour avoir une confirmation visuelle, si les heures H1 ou H2 ne font pas parties du jour, elles passent en gras par MFC

Les plages horaires des jours J-1 et J+1 on été limitées à 12 heures. ça permet de savoir où on est.

Je ne me suis occupé que de J, L et du tableau. mais pour J-1 et J+1, tous les renseignements devraient venir de J avec une recopie simple par formule

une fois le fichier construit, il serait bon de mettre une protection sur les cellule ne devant pas être saisie manuelleement (sans mot de passe, c'est qu'une protection fonctionnelle)

À mon sens, le planning devrait être renseigné en fonction de dates, mais comme dans le fichier d'essais, on n'aborde pas ce problème, je ne vois pas de solution à proposer
A+

 

[chris63]

Re : Formules : SI - ET - OU (panachage)

Bonjour à vous 2,
Effectivement il y a un probème sûrement au niveau de la formule car dans le tableau 1, le début de la plage n'est pas intégré dans le résultat : il y a 0 dans des cellules où il devrait y avoir 1.
J'ai joint le fichier avec les cellules qui sont erronées (en vert) sur le tableau 1. Précision, pour moi, la cellule 00:00 correspond à la plage horaire 00:00 à 00:59.
Pour les tableaux 2 et 3, pour moi c'est OK à première vue.
Ce n'est pas un planning ... ... mais ça y ressemble.
Merci encore et bonne journée.
A+

 

[wilfried_42]

Re : Formules : SI - ET - OU (panachage)

re:

merci pour le retour du fichier, ca aide, mais j'ai mal à a tete lol

verifie tout stp

 

[chris63]

Re : Formules : SI - ET - OU (panachage)

Wilfried ........ ................. c'est trop top !!!!
J'ai pas tout compris dans la formule (est-ce une formule matricielle ... ?!), mais c'est exactement ce que je cherchais.
Encore un grand merci à vous 2 pour cette aide précieuse.
Bon WE.
A+

 

[wilfried_42]

Re : Formules : SI - ET - OU (panachage)

re:

desolé, ne connaissant rien aux formules matricielles, ne connaissant meme pas le principe, ce sont des formule tout ce qu'il y a de classique

 

[Gorfael]

Re : Formules : SI - ET - OU (panachage)

Bonjour à vous 2,
Tout d'abord un grand merci pour l'aide que vous m'avez apporté et pour la célérité de votre réponse.
Il est certain que j'aurai pu y passer tout le WE sans y arriver. Je peux même vous avouer que je n'ai pas encore bien compris la construction de la formule.
J'ai un petit souci avec le tableau 1 qui ne marque pas la valeur 1 de début de plage. Ce doit être au niveau de la formule qui ne compare pas la bonne plage.
Encore merci de votre aide et bon WE.

Salut
N3 : =SI($L3>$J3;1 * (N$2>=$J3)*(N$2<=$L3); 1 *((N$2+$AM$3)>=$J3)* ((N$2+($AM$3*(N$2<$L3)))<=($L3+$AM$3)))

N$2 :
recopiée vers la droite => O$2, P$2, Q$2...
recopiée vers le bas reste N$2

$J3 :
recopiée vers la droite reste $J3
recopiée vers le bas => $J4, $J5, $J6...

$AM$3 : reste $AM$3 quelque soit la recopie

SI($L3>$J3; A ; B)
L3 > J3 : réponse A
L3 <= J3 : Response B

1 * (N$2>=$J3)*(N$2<=$L3)
(N$2>=$J3) : si N2<J3 alors on à faux (0 pour Excel), dans tous les autres cas, on a VRAI (1)
(N$2<=$L3) : si N2>L3 alors on à faux (0 pour Excel), dans tous les autres cas, on a VRAI (1)
1 : ne sert ici qu'à s'assurer qu'Excel ne retourne pas une valeur logique (VRAI/FAUX), mais une valeur numérique
La formule correspond à : Si(Et(($J3<=N$2);(N$2<=$L3));1;0)

1 *((N$2+$AM$3)>=$J3)* ((N$2+($AM$3*(N$2<$L3)))<=($L3+$AM$3))
(N$2+$AM$3)>=$J3 si J3<=(N2+ 1jour) laors 1 sinon 0
((N$2+($AM$3*(N$2<$L3)))<=($L3+$AM$3))
N$2+($AM$3*(N$2<$L3)) si L3>N2 alors N2 + 1 jour sinon N2
si (N2<L3;N2<L3+1jour; N2+1jour<L3+1jour) ce qui est égal à :
N2<L3 : On applique : (N2+1jour)<=(L3+1jour) : toujours vrai = 1
N2>=L3 : applique N2<=(L3+1jour) : toujours vrai puisque N2<1jour
Pas tout saisi, moi 😱 Où est l'erreur ? 😕

A+