Mettre à jour une plage dans les calcules en fonction d'une cellule

Le 08/12/2020 à 00:06

Bonjour,

Encore un casse tête

J'ai un fichier calendrier dans lequel je fais des annotations que je compte au bout du calendrier

avec des formules du type JR10 : =SI(JQ10="ok";NB.VIDE(IA10:JO10)+NB.SI(IA10:JO10;"r")+NB.SI(IA10:JO10;"i")+NB.SI(IA10:JO10;"F")+NB.SI(IA10:JO10;"CH")+NB.SI(IA10:JO10;"De")+NB.SI(IA10:JO10;"Av")+NB.SI(IA10:JO10;"De");"")

JZ10=SI(JQ10="ok";NB.SI(IA10:JO10;"R")*7,2;"")

Mon calendrier est sur la plage N8:JO8 ce sont les jours de l'année du lundi au vendredi

Je note une date dans un autre onglet par exemple 16/11/2020 et j'ai une formule qui me dit que le 16/11/2020 c'est la colonne IH de mon calendrier.

De là je vais dans toutes mes formules (6 au total) et je modifie manuellement ma plage (ici je remplace IA par IH) puis je descend mes formules jusqu'a la ligne 100

Y a t-il un moyen de modifier les formules automatiquement en fonction de la date que j'écris dans l'autre onglé ? J'ai déjà essayé avec la fonction recherche et "remplacer dans formule". mais ca ne me conviens pas. il y a déjà eu des erreurs et tout a été remplacé dans l'onglet. Aussi je peux avoir des IA dans d'autres formules que je souhaite garder. J'ai essayé également les formules de recherches mais c'était pitoyable

Le 08/12/2020 à 06:58

Salut Katarité,

et depuis le temps que tu postes, tu n'as toujours pas compris que nous ne sommes pas devins ?
- un fichier, stp ;
- des explications quant aux formules qui doivent évoluer ou non.


A+

Le 08/12/2020 à 11:43

Salut curulis57

Si bien sur, mais j'étais persuadé l'avoir fait >< ca m'apprendra à poster tard le soir lol.

Les formules sont parfaite pas besoin de les changer. Enfin le fichier fonctionne comme cela mais la mise à jour des plages de cellule entraine des erreurs et surtout le but est de simplifier la manip pour la déléguer sans qu'il y ai d'erreur. J'ai cherché à changer les formules mais je n'ai pas trouvé d'autres solutions et comme ca fonctionne j'ai laissé

Les formules ou je modifie manuellement la plage sont en jaune

Merci pour l'attention

13maj-formules.zip (176.26 Ko)
Le 08/12/2020 à 17:11

Salut Katarité,

ta feuille 'Date' ne sert qu'à spécifier une nouvelle date de calcul ?
Pas un peu dommage ? Tu fais ce que tu veux, évidemment mais pourquoi ne pas le faire dans 'Cal' puisque cette date y est de toute façon reproduite ?


A+

Le 08/12/2020 à 19:31

Re coucou

L'onglet date est en réalité une source de donnée à date et donc avec cet onglet je met a jour mes formules pour calculer à partir de la date de mise a jour les élément du calendrier.

Il y a des fonctions recherche (de JU a JX) qui cherche les éléments dans cet onglet date. Je les ai supprimés dans cette version car pas d'intérêt pour le pb.

Apres cette date je peux effectivement la mettre dans l'onglet cal, ce n'est pas un soucis mais est ce que ca va faire avancer le pb ?

Le 08/12/2020 à 20:37

Salut Katarité,

la recherche de la date se fait maintenant directement dans 'cal' [JU8], comme suggéré.
Question : il n'y a pas de jours de WE dans ton calendrier. Si la date encodée en [JU8] tombe sur un WE, veux-tu que je change automatiquement cette date pour le lundi qui suit ou le vendredi avant, par exemple ?
! Vérifie les formules ! : certaines ([KC] entre autres, je pense) ne présentaient pas de [IA]..

Private Sub Worksheet_Change(ByVal Target As Range)
'
Dim sCol$
'
If Not Intersect(Target, Range("JU8")) Is Nothing Then
    If CDate([JU8]) >= CDate([N9]) And CDate([JU8]) <= CDate([JO9]) Then
        If Weekday(CDate([JU8]), vbMonday) < 6 Then
            Application.ScreenUpdating = False
            Set rCel = Rows(9).Find(what:=CDate([JU8]), lookat:=xlWhole, LookIn:=xlFormulas)
            sCol = fctCol(rCel.Column)
            Range("JR10").FormulaLocal = "=SI(JQ10=""OK"";NB.VIDE(" & sCol & "10:JO10)+NB.SI(" & sCol & "10:JO10;""A"")+NB.SI(" & sCol & "10:JO10;""I"")+NB.SI(" & sCol & "10:JO10;""F"")+NB.SI(" & sCol & "10:JO10;""CH"")+NB.SI(" & sCol & "10:JO10;""De"")+NB.SI(" & sCol & "10:JO10;""Av"")+NB.SI(" & sCol & "10:JO10;""De"");"""")"
            Range("JR10").Select
            Range("JR10:JR100").FillDown
            Range("JZ10").FormulaLocal = "=SI(JQ10=""OK"";NB.SI(" & sCol & "10:JO10;""R"")*7,2;"""")"
            Range("JZ10").Select
            Range("JZ10:JZ100").FillDown
            Range("KA10").FormulaLocal = "=SI(JQ10=""OK"";NB.SI(" & sCol & "10:JO10;""R"")*5;"""")"
            Range("KA10").Select
            Range("KA10:KA100").FillDown
            Range("KB10").FormulaLocal = "=NB.SI.ENS(" & sCol & "10:JO10;""C"")"
            Range("KB10").Select
            Range("KB10:KB100").FillDown
            Range("KC10").FormulaLocal = "=SI(JQ10=""OK"";NB.SI.ENS(" & sCol & "10:JO10;""C"");"""")"
            Range("KC10").Select
            Range("KC10:KC100").FillDown
            Range("KI10").FormulaLocal = "=SI(JQ10=""OK"";JV10-NB.SI(" & sCol & "10:JO10;""R"");"""")"
            Range("KI10").Select
            Range("KI10:KI100").FillDown
            Application.ScreenUpdating = True
        End If
    End If
End If
'
End Sub
7katarite.zip (191.26 Ko)

A+

Le 08/12/2020 à 23:11

Salut curulis57,

Merci pour ta proposition, au premier abord elle à l'aire top. Je vais prendre le temps de creuser un peu plus.

Ce qui me fait peur c'est que c'est du VBA, il ne faudrait pas que je plante tout le fichier (car je ne suis pas du tout une pro du VBA). Eventuellement est il possible de placer le code dans un autre fichier et de l'activer à l'ouverture des deux fichier ?

La date en JU8 tombe quasi tout le temps un weekend mais comme c'est moi qui la saisie j'écris ce que je veux :P.

Ca serait pas mal de la mettre à j+1 si c'est un dimanche et a j+2 si c'est un samedi mais c'est de la pinaille la car ca ne prend pas longtemps regarder à quel jour tombe la date (j'ai un calendrier devant mon nez).

Bien vue pour l'erreur, je pense que pour le coup j'ai un peu trop anonymisé le fichier et j'en ai fait des bourdes.

Par contre j'ai oublié un détail qui je pense doit avoir une importance... => la formule KB=KC sauf que

KB c'est du 01/01 au 31/05 => 01/01 c'est ma variable en fonction de la date de mise a jour (JU8)

KC c'est du 01/06 au 31/12 => 01/06 la variable qui change en fonction de JU8

Une seule formule est utile alors je noirci l'autre quand on passe au 01/06 ou au 01/01.

Sinon pour l'application du code ça parait réalisable, il faut juste que je vérifie mes capacités à changer le code si je rajoute un argument dans ma formule (en JR)

car celle ci évolue en fonction de ce qui est complété dans le calendrier. Je suppose que dans ce cas je dois modifier la formule et le code

Le 09/12/2020 à 07:00

Salut Katarité,

on va prendre le temps qu'il faut pour régler tous ces petits ajustements à ton avantage.

- faut pas avoir peur de VBA : c'est un bon gros matou qui ronronne quand il a toutes ses croquettes ;
- "à l'ouverture des deux fichiers" ?? Quel est ce 2e fichier et comment vois-tu la chose ?
- un WE qui tombera le lundi qui suit : pas de souci !
- la formule en [KB] s'actualise donc si la date en [JU8] est antérieure au 31/5 (ici, [DQ]) ? Ex : du 2/3 au 31/5 tandis que [KC] = vide ?
- [KC] fait l'inverse du 1/6 ([DR] variable selon [JU8]) -> [JO9] avec [KB] = vide ?
- pour la formule en [JR] : plutôt que rajouter des arguments, ne pourrait-on pas plutôt simplement exclure ce qui ne doit pas être additionné ? J'ai vu que "L", par exemple, n'éatit pas pris en compte...

À te lire.
Bonne journée.


A+

Le 09/12/2020 à 11:19

Salut Karité,

nouvelle version :
- tient compte des dates tombant le WE : un WE devient le lundi suivant sauf fin décembre ou la date passe au vendredi si le lundi passe l'année suivante

        If Weekday(CDate([JU8]), vbMonday) > 5 Then
            If DateAdd("d", IIf(Weekday(CDate([JU8]), vbMonday) = 7, 1, 2), CDate([JU8])) <= DateValue("31/12/" & CStr(Year(CDate([N9])))) Then
                [JU8] = DateAdd("d", IIf(Weekday(CDate([JU8]), vbMonday) = 7, 1, 2), CDate([JU8]))
            Else
                [JU8] = DateAdd("d", IIf(Weekday(CDate([JU8]), vbMonday) = 7, -2, -1), CDate([JU8]))
            End If
        End If

- il y avait une erreur dans la formule [JR] où apparaissait 2 x NB.SI(" & sCol & "10:JO10;""De"") ;
- calcule les 31/5/ et 1/6 en fonction des jours de WE : le 31/5 WE passe au vendredi, le 1/6 WE passe au lundi ;
* si j'ai bien compris, une date en [JU8] <= au 31/5 = formule en [KB] de [JU8] au 31/5 et [KC] effacée ;
* une date en [JU8] >= 1/6 = formule en [KC] de [JU8] au 31/12 et [KB] effacée.

Pour tes critères en [JR], je vois en [A1:E5] les critères en question : peut-on imaginer que tu coches les critères souhaités pour construire la formule ?

À tester...

10katarite.zip (185.23 Ko)


A+

Le 09/12/2020 à 12:17

Bonjour curulis57,

- A l'ouverture des deux fichiers, peut importe en appuyant sur un bouton ou en saisissant la date dans le fichier original si le deuxième fichier est ouvert.

Pour alléger le fichier car l'original est déjà assez gros et en réseau donc il ne faudrait pas s'il plante car il est très important.

- oui c'est sa si KB s'actualise alors KC peut être vide et inversement si la date est entre le 01/06 et le 31/12 KC est actif KB = vide

- les critères en [A1:E5] sont des critères ajouté par les utilisateurs on peut imaginer les cocher mais je pense que ca va compliquer ? alourdir le fichier ? Pour cette formule en JR, étant donné que les utilisateurs ajoute les critères comme ils veulent c'est à moi de faire le tri de ce qui est pris en compte ou non. Je ne pense pas qu'il y ai grand changement à ajouter ou exclure les critères car dans les deux cas il y a un choix à faire.

J'ai fais des test hier soir, en faite j'ai juste à changer les formules dans le code ?! les formules dans l'onglet sont tout bidon lol. Ce qui est intéressant c'est que si quelqu'un touche aux formules ça sera corrigé à l'actualisation de la date JU.

Le nouveau fichier en jette ! j'adore quand on arrive a faire apparaitre et disparaitre des formules dans Excel. C'est vraiment magique

Le 09/12/2020 à 12:58

Salut Katarité,

toujours pas compris comment fonctionne le deuxième fichier et son rapport avec cette macro...
- les formules dans l'onglet sont tout bidon ! Ça, ce n'est pas malin !
- j'ai juste à changer les formules dans le code ! Ça, ce n'est pas conseillé : faire joujou avec les variables et autres guillemets ne va aller tout seul, surtout si tu n'as pas l'habitude (et même quand on l'a, d'ailleurs !) de VBA !

Avant de te faire un projet avec les critères en [A1:E5], j'aimerais :
- les vraies formules de toutes les colonnes avec leurs spécificités en fonction, peut-être ?, des dates encodées en [JU8] ;
- l'ensemble, si possible, des critères à cocher et les colonnes (formules) concernées par ces critères.

VBA peut t'aider : profite-en !


A+

Le 09/12/2020 à 13:44

Pour tes critères, quelque chose comme ça, ce ne serait pas pratique ?

image
Le 09/12/2020 à 14:25

Re curulis57,

Quand je disais "tout bidon" c'est parce que j'ai supprimé les données en JR10 et en changeant la date la formule c'est quand même mise via le VBA. La je l'ai fait volontairement mais ca peut être accidentel. Les formules sont bonnes, j'ai juste modifié des chiffres par d'autre par (exemple *5 en *7)

Les utilisateurs ont besoin que les arguments restent en A1E5 car ils copient les cellules pour compléter le calendrier.

Le fichier à beaucoup évolué en 1 an, je pense qu'il évoluera encore donc c'est important que je sois capable de modifier le code VBA si besoin

intérêt du deuxième fichier c'est surtout pour ne pas alourdir le fichier original mais je ne voie pas non plus comment le faire fonctionner. J'ai essayer également de déplacer le code dans un autre fichier mais je n'y arrive pas

Le 09/12/2020 à 22:55

Salut Katarité,

essayons d'y voir clair pour ce fichier déjà...
- les arguments en [A1:E5] : tu veux dire que les opérateurs copient (ctrl C) un argument ou l'autre pour aller le coller dans l'horaire ?
Pratique, tiens ! Et, à la place, une liste de validation créée à la demande par VBA quand on clique sur une cellule ne serait pas plus simple et exempte d'erreur de frappe ?
- de tes explications, je devine vaguement que ces arguments sont choisis en fonction d'un résultat attendu pour "reformuler" la colonne [JR] ?
Je peux te faire un système où tu cliques sur un argument pour le sélectionner pour figurer ou non dans la formule en [JR].

Si j'ai raison sur ces deux points, tu n'auras pas à modifier le code : VBA peut s'en charger ! Suffit juste de donner clairement et complètement les règles de fonctionnement des formules dans l'un ou l'autre cas...


A+

Le 09/12/2020 à 23:24

Bonsoir curulis57,

Les arguments [A1:E5] sont bien copié (ctrl +c) pour les coller (ctrl+v) dans la zone calendrier, c'est à dire quelque part en N10:JO100. Ses arguments représentent soit des absences soit des positionnements en poste. Donc soit ils cumulent des heures soit non. La colonne JR compte les jours travaillés (donc les vides + certains arguments).

Je ne visualise pas du tout ton idée. Pour moi ça me conviens comme cela. Pourquoi je ne pourrais pas toucher au code ? J'imagine bien que pour par exemple ce que tu as fait ci dessus :

Range("JR10").FormulaLocal = "=SI(JQ10=""OK"";NB.VIDE(" & sCol & "10:JO10)+NB.SI(" & sCol & "10:JO10;""A"")+NB.SI(" & sCol & "10:JO10;""I"")+NB.SI(" & sCol & "10:JO10;""F"")+NB.SI(" & sCol & "10:JO10;""CH"")+NB.SI(" & sCol & "10:JO10;""D"")+NB.SI(" & sCol & "10:JO10;""Av"")+NB.SI(" & sCol & "10:JO10;""De"");"""")"

Je copie scrupuleusement la partie jaune que je double (copie/colle) en modifiant le """A""" par """X""" par exemple. Ainsi, mon argument est ajouté sans difficultés. Par contre, tu as raison, une faute et je plante tout... Mais la motif du code devrait être autour de 1 fois tous les 12 à 24 mois. Si j'enregistre une copie avant la manip je pense que c'est viable.

Je pense que j'ai donné toutes règles complètements. Pour la clarté, je fais au max.

Le 10/12/2020 à 09:51

Hello, pour le fun, voici une version avec formules, sans VBA.

La formule est longue car pas trop de cellules intermédiaires, mais on pourrait alléger ;)
Tu me rediras si ça va ou pas. Je n'ai fait que les 4 premières colonnes.

J'ai mis les dates charnières en JS1 et JU1

6copie-de-maj-formules.zip (172.49 Ko)

pour info, la tronche de la formule : (Curulis a raison, faudrait simplifier un peu ...)

=SI(JQ10="ok";NB.VIDE(DECALER(M10;;EQUIV($JS$1;$N$9:$JO$9;0);;EQUIV($JU$1;$N$9:$JO$9;0)-EQUIV($JS$1;$N$9:$JO$9;0)+1))+NB.SI(DECALER(M10;;EQUIV($JS$1;$N$9:$JO$9;0);;EQUIV($JU$1;$N$9:$JO$9;0)-EQUIV($JS$1;$N$9:$JO$9;0)+1);"A")+NB.SI(DECALER(M10;;EQUIV($JS$1;$N$9:$JO$9;0);;EQUIV($JU$1;$N$9:$JO$9;0)-EQUIV($JS$1;$N$9:$JO$9;0)+1);"i")+NB.SI(DECALER(M10;;EQUIV($JS$1;$N$9:$JO$9;0);;EQUIV($JU$1;$N$9:$JO$9;0)-EQUIV($JS$1;$N$9:$JO$9;0)+1);"F")+NB.SI(DECALER(M10;;EQUIV($JS$1;$N$9:$JO$9;0);;EQUIV($JU$1;$N$9:$JO$9;0)-EQUIV($JS$1;$N$9:$JO$9;0)+1);"CH")+NB.SI(DECALER(M10;;EQUIV($JS$1;$N$9:$JO$9;0);;EQUIV($JU$1;$N$9:$JO$9;0)-EQUIV($JS$1;$N$9:$JO$9;0)+1);"De")+NB.SI(DECALER(M10;;EQUIV($JS$1;$N$9:$JO$9;0);;EQUIV($JU$1;$N$9:$JO$9;0)-EQUIV($JS$1;$N$9:$JO$9;0)+1);"Av")+NB.SI(DECALER(M10;;EQUIV($JS$1;$N$9:$JO$9;0);;EQUIV($JU$1;$N$9:$JO$9;0)-EQUIV($JS$1;$N$9:$JO$9;0)+1);"De");"")
Le 10/12/2020 à 11:55

Bonjour Joyeux Noel,

Merci pour ta proposition :)

La première formule fonctionne parfaitement (je ne suis pas choqué de la taille, j'ai déjà fait pire lol).

Je n'ai pas encore pris le temps de regarder les autres ni de comprendre le fonctionnement. Je ne fait dès que possible.

Pour les dates charnières seul celle en JS1 va être modifié. Pour la colonne KB on peu imaginer ajouter une date charnière (31/05)car comme dit mardi a 23h11

=> la formule KB=KC sauf que

KB c'est du 01/01 au 31/05 => 01/01 c'est la variable en fonction de la date de mise a jour (en JS1 pour ta proposition)

KC c'est du 01/06 au 31/12 => 01/06 la variable qui change en fonction de JS1pour ta proposition

(Une seule formule est utile alors je noirci l'autre quand on passe au 01/06 ou au 01/01)

Je regarde ca de plus près

merci

Katarité123

Le 10/12/2020 à 14:21

En fait, j'ai juste rajouté ça :

DECALER(M10;;EQUIV($JS$1;$N$9:$JO$9;0);;EQUIV($JU$1;$N$9:$JO$9;0)-EQUIV($JS$1;$N$9:$JO$9;0)+1)

On part de la cellule M10.

On la décale d'autant de cellules vers la droite que l'équivalent de la date voulue dans ta ligne 9 (celle avec tous les jours de l'année). En gros, on part de M10 et on se décale sur le jour défini.

Puis on créé une plage aussi large que la différence entre la fin d'année et ton jour charnière.

En gros, ça fait automatiquement exactement ce que tu faisais à la main. Du coup si tout le reste allait, tu peux allègrement te passer de VBA.

Ou alors, tu profites de tous les précieux conseils de l'ami Curulis et tu auras un fichier tip top.

Le 11/12/2020 à 00:48

Bonsoir Joyeux Noël,

Merci pour l'explication

J'ai fais les autres formules

Par contre j'ai décelé une erreur que je n'arrive pas à corriger.. J'ai eu du mal à trouver la cause mais je pense l'avoir trouvé : Si la date de départ est non vide et que l'argument n'est pas concerné par la formule (autre que A, i, F, CH, De, Av et D) il y a un écart de 1 dans la formule en JS. Je pense qu'il faut ajouter un argument si la case de départ est non vide mais je sèche.

J'ajoute le doc modifié j'ai mis une différence entre les deux formules en JV

9proposition-joyeuxnoel.zip (192.43 Ko)
Le 11/12/2020 à 12:12

Bonjour Joyeux Noël et curulis57,

Je vous remercie infiniment pour votre aide. ce casse tête était plus compliqué que les autres. Vous m'avez beaucoup aidée. Si je peux valider deux réponses je le fait.

Je pense que dans l'immédiat je vais plutôt utilisé la solution de Joyeux Noël. Par contre, si le fichier n'évolue pas sur 2021, dans ce cas en 2022 je passe en VBA pour avoir un fichier tip top. Pour le moment j'ai sortie du carton mon livre de VBA j'aimerais vraiment m'y mettre car c'est trop magique lol.

La solution de Joyeux Noel est déjà top merci encore

Joyeux Noël, j'ai corrigé l'erreur ! elle venais de moi, je démarré en N au lieu de M dsl..

A+

Katarité123

Rechercher des sujets similaires à "mettre jour plage calcules fonction"