Formule pour incrémenter des chiffres avec conditions

Lou · Août 26, 2018, 11:06

Bonjour à vous,

Je m’adresse de nouveau à vous car j’ai un nouveau problème de formule sur mon fichier.

En F5 et G5, je saisis manuellement le chiffre et ensuite, je souhaite que l’incrémentation se fasse automatiquement grâce à des formules.

L’incrémentation de la colonne F se fait correctement grâce à la formule de Nono.

=SI(A6<>"";SI(OU(A6=“D”;A6=“S”);G5;SI((G5+1)>20;1;(G5+1)));"")

Par contre, le problème se pose sur la colonne G.
En fait, le chiffre 17 qui se trouve en F5 ne doit pas être reporté en colonne G12 car le chiffre tombe sur un jour férié donc, il faudrait que sur mon fichier en G12, ce soit le chiffre 18 qui apparaissent. En tenant compte de la formule ci-dessus.

Et je ne sais pas comment rajouter à la formule existante que si C5=Joursfériés ; alors, on affiche F6 mais, l’incrémentation ne devra pas dépasser 20 et reprendre à 01 tout en tenant compte des week-end où l’incrémentation devra être le chiffre du vendredi.

Merci d’avance pour votre aide
test2.xlsx (22,0 Ko)

Mimimathy · Août 26, 2018, 11:24

Bonjour,

J’avoue de mon côté ne rien comprendre à la finalité de ta demande
Nono (que je salue) t’as fourni une bonne formule dans l’autre post (que tu aurais du garder vu que c’est une continuité)
Mais si tu fournissais des infos et un classeur avec tes doléances en finition, le problème serait moins compliqué à comprendre, car là

Si toi tu te comprends, fais pour que cela soit compris par des autres

En fait, le chiffre 17 qui se trouve en F5 ne doit pas être reporté en colonne G12 car le chiffre tombe sur un jour férié donc, il faudrait que sur mon fichier en G12, ce soit le chiffre 18 qui apparaissent. En tenant compte de la formule ci-dessus.

Lou · Août 26, 2018, 12:30

bonjour Mimimathy,

Le problème est complexe c’est pour cette raison que je n’arrive pas à trouver la formule.

Nono avait trouvé la bonne formule pour la colonne F ; mais, on ne tenait pas compte des jours fériés et la condition était : si le chiffre en F6 tombait un samedi ou un dimanche on prenait le chiffre du dessus ; sinon on ajoutait +1 et que l’incrémention une fois arrivée au chiffre 20 devait repartir à 01.
En G6, il me faudrait la même formule qu’en F6 ; mais, en ajoutant une condition pour tenir compte des jours fériés et ne pas incrémenter le chiffre qui se trouve sur la ligne d’un jours fériés en colonne F (en vert sur mon fichier) sur la colonne G (le chiffre en jaune doit sauter et passer au suivant (en G12, 17 doit sauter et c’est 18 qui doit apparaître au lieu de 17)

L’explication en image sera peut-être plus facile à comprendre ?

Je vous joint le fichier avec les explications en face des chiffres qui ne doivent pas être pris en compte dans l’incrémentation.
test2.xlsx (24,5 Ko)

Merci pour votre aide.

Bonne journée

Nono · Août 26, 2018, 1:49

Salut @Lou

C’est vrai que c’est compliqué à appréhender comme le souligne @Mimimathy (que je salue aussi et chapeau pour le vba )

Le problème me parraissait simple de prime abord (une fois compris).
Incrémenter +1 à chaque jour férié et recalculer le tout. Sauf que voilà, j’ai besoin de ton retour:

La demande va créer des boucles: si on ajoute + 1 on va nécessairement retomber sur le même résultat plus loin.
Je m’explique: dans ton exemple (ouais je suis passé au « tu » comme ça sans prévenir ), donc dans ton exemple, le 20 juin a donc une valeur 06.
On va donc la décaler avec +1 et donc repartir sur une valeur de jour férié
Donc il faudrait que l’on ajoute +2, mais il faudrait aussi vérifier que 8 ne tombe pas un jour férié.
Du coup, plusieurs méthodes s’offrent à nous, créer plusieurs colonnes avec des conditions de recalcul (il faut anticiper toutes les possibilités, mais ça devrait aller, y’ a pas non plus beaucoup de jours fériés qui se suivent), mais ça risque de te faire bcp de colonnes (genre 2 dans Feuill1 et 4 dans « Jours fériés »)
Soit de passer par vba pour « boucler » (et là je pense que les comptétences de @Mimimathy seront plus qu’utiles)

Je te laisse voir ce que tu préfères:

plusieurs colonnes masquées avec des formules (classique, simple, mais un peu lourd)
passer par vba (utra efficace, anticipe tous les résultats, fichier en xlsm, code complexe à appréhender)

A plus!

Lou · Août 26, 2018, 3:16

Bonjour Nono,

Je confirme que ma demande est compliquée ! Désolé !

Pas de problème pour le “tu”

Je me doutais que ça serait compliqué ! Car je souhaitais tout automatiser.

J’aurais préféré des formules au VBA car en VBA, je ne suis pas une experte.

Pour info : en colonne H, j’ai mis manuellement les chiffres (en espérant n’avoir pas fait d’erreur) qui devraient normalement se trouver en colonne G. C’est à s’arrager les cheveux pour trouver une formule, en plus de comprendre ! mdr

Voici le fichier
test3.xlsx (25,1 Ko)

Si vous trouvez cela trop complexe ! ce n’est pas grave ! je ferais cela manuellement.

Merci tout de même pour l’aide.

Bonne soirée

Mimimathy · Août 26, 2018, 3:25

Re,

Ce qu’il faudrait comprendre, c’est pourquoi tu pars avec le chiffre 17 en F5 et avec le chiffre 12 en G5.
Si l’on pouvait comprendre le pourquoi, les chances de résoudre serait plus facile
D’ou ma demande, savoir la finalité de ces chiffres

Lou · Août 26, 2018, 3:37

Re MimiMathy,

Je ne pensais pas que ces chiffres avaient autant d’importance dans la compréhension du fichier car ces chiffres varient selon les mois de l’année.

En F5, le chiffre saisi correspond à un numéro de tournée d’un représentant et en G5, le chiffre saisi correspond à un numéro de tournée de livraison. Il y a 20 tournées correspondant soit au passage du représentant chez le client ou au jour de livraison.

Etant donné que c’est un calendrier lorsque le mois est inférieur à 31 jours, il ne faudra pas que les chiffres s’affichent dans les cellules.

Merci d’avance.

Bonne soirée

Nono · Août 26, 2018, 4:59

Ahhhhhhhhhhhhhhhhhhhh (#Illumination)

Mais en fait tu veux juste que le jour férié ne soit pas pris en compte!
Moi je croyais qu’il fallait que les jours fériés ce soit +1 et tout recalculer à partir de là, mais en fait pas du tout si je comprends bien

Ca devrait être beaucoup plus simple!

Lou · Août 27, 2018, 2:29

Re Nono,

Effectivement, le jour férié ne doit pas être pris en compte (enfin, pour être clair, le chiffre surligner en vert en colonne F qui se trouve devant un jour férié ne doit pas être pris en compte sur la colonne G).

Tu me rassures si tu penses que cela sera plus simple

Et à la fin du mois, les chiffres de la colonne G doivent être masqués si le mois est inférieur à 31 jours

Merci d’avance.

Nono · Août 28, 2018, 9:04

Salut Lou,

En fait j’ai bien relu toutes tes conditions et…

ben en fait non… C’est complexe!
Mais j’ai fait le gros du boulot à 80% environ.

Je pense que je vais pouvoir te transmettre un truc d’ici jeudi
(Et je ne t’oublie pas @Formaconsult, je suis aussi sur ton fichier )

A bientôt!

Lou · Août 29, 2018, 3:30

Bonjour Nono,

Je suis vraiment désolé pour la complexité de mon fichier !

Et je te remercie de ton aide ! C’est vraiment très gentil de ta part d’essayer de trouver la bonne formule.

à bientôt !

Nono · Août 30, 2018, 1:51

Salut @Lou,

Et voilà, je pense que c’est bon pour une alpha Suite.xlsx (30,6 Ko)

Alors quelques prérequis d’utilisation:

Tu peux saisir tes chiffres en G5 et H5. A partir de là, les suites vont se générer automatiquement.
ATTENTION: certaines suites ne sont pas compatibles: par exemple commencer avec les mêmes chiffres des deux cotés, alors que le premier jour du mois est férié (d’autres suites ne fonctionnent pas entre elles non plus pour le moment, il faut que je continue à bosser un peu) EDIT : Problème corrigé en principe
Tu trouveras un nouvel onglet “Matrice”, c’est ici que tout se joue.

Quelques explications
Avant toute chose, ma méthode N’EST PAS la bonne.
(Je suis parti sur deux principes opposés et je n’y ai travaillé que par ci par là, alors ça se répète et ça tourne en rond)

EDIT : SUPPRESSION DES EXPLICATIONS (VOIR EN BAS DE POST)

Je te laisse tester le fichier, qui, je tiens à le souligner encore:

1: n’est pas fini
2: a été fait par bout de truc et doit donc être optimisé de A à X (ouais pas jusqu’à Z quand même )

A bientôt !

EDIT : je me suis dit que quand même, le but du forum était le partage et la transmission de savoir donc voici (tant bien que mal) une explication de la construction des formules du fichier plutôt que des explications générales
Je n’en mets qu’une (la plus compliquée) mais elles sont toutes basées sur le même modèle

C’est la formule la plus compliquée du fichier (mais elle est plus simple qu’il n’y paraît)

C’est une formule si avec:

Test logique

	SI((
		SIERREUR( -on renvoie "" si une erreur est trouvée
			INDEX($F$2:$F$32; -la plage

Le coeur de la formule:
Teste le résultat de la matrice renvoyée,
si ce résultat est VRAI, on retient l’élément correspondant dans la matrice LIGNE()

            MIN( MIN va renvoyer la valeur minimum du test suivant
	Si la plage est différente de vide;Si y'a pas de doublon; alors renvoi du n° de ligne;
            9^9 génère un très grand chiffre (ou une erreur) qui permet un renvoi de la valeur inf
	SI(($F$2:$F$32<>"");SI(NB.SI($G$1:G1;$F$2:$F$32)=0;LIGNE($F$2:$F$32)-1;9^9))));""))

	>20 si tout ça est supérieur à 20 alors

	(INDIRECT( Permet de faire référence à une cellule de façon dynamique (=INDIRECT("A1") va lire le contenu de A1 et le renvoyer)
		ADRESSE( comme on ne veut pas taper "A1" dans notre formule puisqu’elle doit s’adapter, on utilise ADRESSE donc avec une n° de ligne et de colonne
			Ici la ligne est la ligne sur laquelle on est positionnée - nbr de cellules entre 0 et 21 (en gros)
			ATTENTION AU 8, c'est le n° de colonne (il aurait mieux valu mettre COLONNE(G2))
			LIGNE()-(NB.SI.ENS(G:G;">0";G:G;"<21"));8)))
	SINON:
	
On reprend la valeur du début ;)

Voilà, j’espère que c’est à peu prês clair

Nono · Août 30, 2018, 3:02

Je suis pas sûr d’avoir bien compris mais dans la MFC:
=JOUR(DATE(ANNEE($D$1);MOIS($C$5)+1;0))<31
couleur-> blanc

Lou · Août 30, 2018, 5:02

Salut Nono,
Merci pour ton retour et les explications et le partage de transmission ! Je suis impressionnée par l’énorme travail que tu as effectué. Tu es un champion d’Excel, il n’y a aucun doute !

Je constate que tu as décalé la colonne F en G ; ce n’est pas un problème ; mais, j’ai eu peur d’avoir fait une erreur de colonne pour mes explications ! Ouf ! En fait, tu as juste ajouté une colonne.

Nono a dit : « ATTENTION: certaines suites ne sont pas compatibles: par exemple commencer avec les mêmes chiffres des deux cotés, alors que le premier jour du mois est férié … »

Pour info : il n’y aura jamais les mêmes chiffres l’un à côté de l’autre, les chiffres sont toujours différents.

J’avais récupéré ton 1er fichier que tu as supprimé avec les suites qui ne fonctionnaient pas sur certains mois ; donc, je viens de tester à nouveau avec ton dernier fichier.

J’ai testé ton fichier en testant mois par mois en modifiant le mois dans ma formule en C5. Et en saisissant, les chiffres en G5 et H5.

Résultat du test :

Pour la colonne G : l’incrémentation se fait parfaitement du mois de Janvier à Décembre.

Pour la colonne H :

Mois de Janvier, Mai, Novembre : En H34, il y a un souci car le chiffre reprend le chiffre en H33 (chiffres en doublon)

Mois de Février, Mars, Juin, Juillet, Août, septembre, Octobre : c’est parfait !

Mois d’Avril : en G5, je saisi le chiffre 1 et en H5, je saisi le chiffre 17. J’ai un problème d’incrémentation, en H10 : ça me donne 3 et je devrais avoir 1. La suite de l’incrémentation est donc fausse à partir de H10 jusqu’à la fin. En H30, je n’ai pas de chiffre, celui-ci est sur la ligne de dessous ? Hors, le mois est de 30 jours.

Mois de Novembre : Un chiffre apparait en H35. Et H34 en doublon avec H33.

Mois de Décembre : en G5, je saisi 16 et en H5, je saisi 12. Problème en H7, le chiffre 14 apparait au lieu du chiffre 13.

Merci encore pour ton travail sur le fichier et toutes ces formules, je n’aurai jamais réussi à faire ça. J’ai vu bons nombres du tutoriel sur les fonctions : index, equiv, indirect, rechercheV ! Lorsque c’est simple, j’arrive à le mettre en application ; mais, avec un fichier comme celui-ci c’était trop compliqué pour moi.
Du coup, je vais regarder toute la procédure de tes formules pour bien comprendre l’acheminement de la formule miracle qui aboutit au fichier désiré.

Nono a dit : Je suis pas sûr d’avoir bien compris mais dans la MFC:
=JOUR(DATE(ANNEE($D$1);MOIS($C$5)+1;0))<31
couleur-> blanc

Si tu as mis dans la MFC la formule c’est nickel ! car moi, j’utilisais la fonction =si(A35<>« »;« ») imbriqué avec les autres formules. Mais, c’est mieux avec la MFC

Pour l’impression, le calendrier sera de Janvier à Juin en recto et de Juillet à décembre en verso. Penses-tu qu’il serait préférable de faire deux fichiers : 1er semestre et 2eme semestre ou bien deux onglets différents !

A bientôt et merci encore !

Nono · Août 30, 2018, 6:25

Merci Lou pour ton retour ! C’est très apprécié !

Je pense qu’on approche de la finalité

Tellement bon !

J’ai identifié le problème, c’est la « classique » de la rechercheV qui bloque sur la première valeur, ca devrait se corriger sans trop de souci avec un petit NB.SI et un CONCATENER

Là je pense que c’est un problème de compréhension:

vu que le 1 et le 2 avril sont fériés, ils sont exclus de la suite non ?

Et la suite se poursuit correctement en suivant cette logique

Toujours cette rechercheV qui nous *****

C’est un problème de délimiteur, faut que je corrige le séparateur (colonne P de Matrice si ça tu veux jeter un oeil )

Là encore, c’est comme pour avril, le 25 dec donnant la valeur 13, elle est exclue.

Alors pour le coup, c’est vrai que cette problématique est quand même prise de tête (et je ne suis vraiment pas persuadé que mes formules soient très indiquées) mais pour conseil, commence par « jouer » avec une seule formule.
Comme ça, les miennes ont l’air monstrueuses mais en fait, c’est comme un puzzle, il suffit de les imbriquer
La base, c’est fonction SI, NB.SI, rechercheV, si tu les maîtrises alors tu auras fait 80% du boulot

Surtour n’hésite pas à demander pour apprendre, c’est toujours beaucoup plus simple quand on a des explications.

Je vais poursuivre sur le fichier dès que je peux

A très vite

Lou · Août 30, 2018, 8:37

De rien Nono pour le retour ! Tu fais un travail de dingue sur ce fichier c’est impressionnant ! Je suis épatée par ta facilité à trouver les formules et à les imbriquées avec autant de facilité !

Oui, on approche de la finalité ! Je ne pensais pas que cela serait possible !

Pour le mois d’avril :

En fait, c’est en testant le fichier que je me suis aperçue qu’il y avait un soucis par rapport au jour férié qui tombe un week-end sur la cellule où je dois saisir le chiffre (uniquement sur la colonne H)!
Mais, le fichier est tellement prise de tête que je n’ai pas pensé à cela et je m’en excuse car ça fait une prise de tête en plus. Et je ne sais pas s’il sera possible de trouver une formule pour cette exception.

Là, c’est l’exception, le 01 en G5 tombe en face d’un jour férié ; mais ce jour férié tombe un dimanche et non pas un jour de semaine ; donc, il doit être reporté en H10.

Pour le 02 en G6, il tombe en face d’un jour férié ; mais, un jour de semaine ; donc, il ne doit pas être pris en compte.

Je te fais la capture d’écran pour mieux comprendre.

Pour le mois de décembre :

Lou:
Mois de Décembre : en G5, je saisi 16 et en H5, je saisi 12. Problème en H7, le chiffre 14 apparait au lieu du chiffre 13.

En fait, non ! Ici, cela ne concerne pas le 25 décembre, il n’y a pas de jour férié en face ce chiffre en début de mois. Voir la capture d’écran.

Merci encore pour tout ! Je me répète mais c’est mérité !

C’est vrai que tes formules ont l’air monstrueuses ! Il va falloir que je m’entraîne ! j’ai encore du boulot pour arriver à ton niveau ! Promis, je n’hésiterai pas à demander pour continuer à m’améliorer.

Pas de soucis pour le fichier, tu le fais à ton temps.

à bientôt !

system · Septembre 29, 2018, 8:37

Ce sujet a été automatiquement fermé après 30 jours. Aucune réponse n’est permise dorénavant.