Microsoft 365 Problème au Niveau de l'icrémentation des formules dans un tableau

[Daher Ali]

Bonjour à tous,
J’ai un formulaire de saisi et cela fonctionne très bien, le problème que je vais poser n’a rien avoir avec le formulaire, j’explique :

Mon tableau comporte 6 colonne (4 première colonne remplie par le formulaire et les 2 autre comporte des formules)

Les formules de la colonne 5 et 6 sont :

=SIERREUR(SOMMEPROD(SI.CONDITIONS($B$5:B5="SALAIRE";+$D$55;$B$5:B5="AVANCE";-$D$55;$B$5:B5="SOLDE SALAIRE";-$D$55;$B$5:B5="REMBOURSEMENT";-$D$55;$B$5:B5="CONGE";+$D$55;$B$5:B5="GRATIFICATION";+$D$55;$B$5:B5="PRET";0));"")

=SIERREUR(SOMMEPROD(SI.CONDITIONS($B$5:B5="SALAIRE";0;$B$5:B5="AVANCE";0;$B$5:B5="SOLDE SALAIRE";0;$B$5:B5="REMBOURSEMENT";-$D$55;$B$5:B5="CONGE";0;$B$5:B5="GRATIFICATION";0;$B$5:B5="PRET";+$D$55));"")

A la saisi de la première ligne pas de problème, mais lorsque je saisi la deuxième ligne le tableau prend une nouvelle forme mais les FORMULES NE S’INCREMENTENT PAS COMME IL LE FAUT, c’est-à-dire au moment que le tableau ajoute une nouvelle ligne automatiquement les formules doivent aussi s’incrementer selon la première ligne, mais dans mon cas par exemple au lieu de $B$5:B6 je trouve $B$5:B7

Mais lorsque j’agrandi mon tableau manuellement et j’écris ces formules dans les 2 colonnes et que je tire jusqu’en bas cela met les bonnes formules. Mais avec la sélection de la première ligne si je veux agrandir le tableau que je tire le résultat change, je ne sais pas pourque cette différence.
Joint un exemple du fichier que j'ai ( dans feuille : Sarah et Ali )

Merci d'avance pour votre aide.

 

[chris]

Bonjour

C'est un problème connu sur les tableaux

Erreur de formule avec des données en tableaux dans Excel

Le CFO masqué vous explique comment résoudre une erreur de formule dans vos tableaux Excel afin d'éviter toute problématique éventuelle.

www.lecfomasque.com

Dans certains cas on peut, comme expliqué sur le site, utiliser l'en-tête, dans d'autres se servir de DECALER

Avec une formule aussi longue, je conseille DECALER en formule nommée en espérant que tu n'as pas trop de personnes

Voir ci joint où j'ai renommé les tableaux et créé 2 formules nommées pour Sarah (et effacé celles en erreurs dan les noms)

 

[Daher Ali]

Bonjour Chris et merci pour ton retour. Je vérifie des que possible et je te reviens.

 

[Daher Ali]

C'est TOP Chris,
j'ai juste modifier légèrement la formule de la colonne E et tout fonctionne à merveille, joint le fichier.
je te remercie pour le beau travail que tu ma fait.
Merci infiniment

 

[Daher Ali]

Une dernière question!
j'ai vu le travail que tu fait dans le gestionnaire de noms, lorsque je clic sur :
MONTANT_S en suite sur Fait Référence à : elle sélectionne juste les 2 première ligne saisie et les autres ligne saisie elle ne fait rien
pareille pour la RUBRIQUE_S

je ne sais pas si c'est normal

autre chose j'aimerais savoir si je duplique la feuille Sarah pour faire pareille pour les autres feuilles, j'aurai beaucoup de Plage dans le Gestionnaire de Noms vu que je duplique. y'a t'il pas un moyen de généralisé dans le gestionnaire de noms pour c'est 2 plages que tu à crées le même travaille pour toute les autres feuilles vu que sa sera le même modèles de feuilles.

Merci

 

[chris]

Bonjour

Déjà ne pas créer de lignes d'avance dans les tableaux structurés : elles se créent dès qu'on saisie sous la dernière ligne du tableau

Les formules nommées surtout si elles contiennent des références relatives doivent être créées ou éditées depuis la cellule où elles s'appliquent : par exemple si tu regardes RUBRIQUE_S à partir de E5 cela affiche $B5 mais si tu regardes à partir de E12 cela affiche $B12.

MONTANT_S est défini en parallèle à RUBRIQUE_S

Si on duplique une feuille, le nom est dupliqué avec une porté feuille et non plus classeur mais cela créant une ambiguïté, pour ma part je préfère redéfinir les noms pour chaque feuille, ce qui oblige à adapter les formules.

C'est pourquoi je disais "espérant que tu n'as pas trop de personnes"

Il existe aussi une méthode pour que le calcul considère la feuille active (donc en utilisant des adresses classique et non tableaux) mais le recalcul nécessite, soit une action sur la feuille (saisie), soit de forcer le calcul car la simple activation de l'onglet de le provoque pas, soit à ajouter du VBA pour déclencher le calcul à l'activation.
Voir ci-joint

Ce calcul cumulé par onglet est-il utile si tu n'as besoin que des soldes sur l'onglet SOLDE ?

Je pense que tu devrais réfléchir à une autre option, plutôt qu'un tableau par personne, une table commune et un calcul du solde à l'instant T pour une personne par une requête PowerQuery (intégré à Excel) comme dans un vraie base de données.

 

[Daher Ali]

Super merci beaucoup Chris pour cette explication et d'avoir résolu mon problème.
Vous êtes formidable dans ce Forum

 

[Daher Ali]

Salut Chris, Salut le Forum,

Tu as raison Chirs, si je duplique la feuille et que je fait une saisi dans le formulaire dans l'une des feuilles pour ajouter la saisie à la feuille active automatiquement la saisi est dupliquée dans les autre feuilles dupliquées aussi d'où il y'a problème avec cette formule :
=DECALER(!$B$4;1;;NBVAL(!$B$4:$B4)-1;1) dans le Gestionnaire de Noms.
Je serai donc obligé d'utiliser ta première proposition :
=DECALER(Sarah[[#En-têtes];[RUBRIQUE]];1;;NBVAL(Sarah[[#En-têtes];[RUBRIQUE]]:Sarah!$B4)-1;1)

tu m'as aussi proposer cela :
" Je pense que tu devrais réfléchir à une autre option, plutôt qu'un tableau par personne, une table commune et un calcul du solde à l'instant T pour une personne par une requête PowerQuery (intégré à Excel) comme dans un vraie base de données. "
Mais je ne maitrise pas

 

[Daher Ali]

Avec du VBA est ce que c'est faisable ?

Merci encore

 

[chris]

RE

VBA peut faire beaucoup de chose si on maîtrise sans pour autant être toujours la meilleure solution

Ta question porte sur son utilisation en ne modifiant pas la structure actuelle ou bien dans le cadre de ma proposition PowerQuery ?

Sur ce dernier cas , il est à mon avis plus simple d'utiliser PowerQuery sinon coder un filtre avancé...

 

[Daher Ali]

RE

VBA peut faire beaucoup de chose si on maîtrise sans pour autant être toujours la meilleure solution

Ta question porte sur son utilisation en ne modifiant pas la structure actuelle ou bien dans le cadre de ma proposition PowerQuery ?

Sur ce dernier cas , il est à mon avis plus simple d'utiliser PowerQuery sinon coder un filtre avancé...

Bonjour Chris, Bonjour le FORUM,

Je préfère pour le moment utiliser mon fichier, et j'aime bien la formule que tu ma donnée dans le Gestionnaire de Nom, tout marche à merveille et même lorsque je duplique cette feuille elle fonctionne aussi, puisque la formule dans le Gestionnaire de Noms prend en compte le Nom et Tableau de la nouvelle feuille ce qui est parfait pour moi.
Mais le problème lorsque j'utilise une macro VBA pour dupliquer une feuille et renommer, les formules dans le Gestionnaire de Noms reste pareille que la feuille de depart, c'est à dire :

1- Feuille nommé : Sarah
=DECALER(Tsarah[[#En-têtes];[RUBRIQUE]];1;;NBVAL(Tsarah[[#En-têtes];[RUBRIQUE]]:Sarah!$B4)-1;1)
=DECALER(Sarah!RUBRIQUE_S;;2)

2- Feuille Sarah dupliquée renommer ex : Sarah-2
En VBA dans le Gestionnaire de Noms, les formules en 1 reste pareille
Sans VBA pas de problème

J'aimerais vraiment trouver une solution en VBA si possible.

Merci encore.

 

[chris]

RE

Dans ce cas il est préférable

• d'avoir un modèle sans nom et sans formules
• que le VBA
  1. renomme le tableau du nouvel onglet
  2. crée 2 nouveaux noms, par exemple RUBRIQUE_A et MONTANT_A pour Ali,
  3. place les formules dans la ligne unique de la copie du modèle (comme déjà dit un tableau structuré ne doit pas contenir de ligne vide donc seule la ligne initiale obtenu à la création doit figurer sous l'en-tête),

car sinon il va falloir après les étapes 1 et 2, modifier les formules, supprimer les noms de portée feuille créés par la copie

 

[Daher Ali]

RE

Dans ce cas il est préférable

• d'avoir un modèle sans nom et sans formules
• que le VBA
  1. renomme le tableau du nouvel onglet
  2. crée 2 nouveaux noms, par exemple RUBRIQUE_A et MONTANT_A pour Ali,
  3. place les formules dans la ligne unique de la copie du modèle (comme déjà dit un tableau structuré ne doit pas contenir de ligne vide donc seule la ligne initiale obtenu à la création doit figurer sous l'en-tête),

car sinon il va falloir après les étapes 1 et 2, modifier les formules, supprimer les noms de portée feuille créés par la copie

Je te remercie Chris pour ton retour, la vérité je suis débutant et il y'a des terme que je ne maitrise pas vraiment selon ce que tu ma d'écrit.
Joint 2 fichier une sans VBA et une avec macro VBA ajouteFeuille
Merci de voir ces 2 fichiers stp. je suis bloquer et je compte vrai sur Vous

Merci encore à vous

 

[Daher Ali]

Salut Chris,

Merci de m'avoir aidée, j'ai trouver mon problème en fin. il ce situe au niveau de la macro, au lieu de dupliquer la feuille, je fessais nouvelle feuille et ensuite je copy et colle les informations de la feuille vers la nouvelle feuille ce qui était mon erreur.
Merci infiniment pour ta formule qui a vraiment aidée

 

[chris]

RE

Pas eu le temps ce matin

Bon cela semble fonctionner correctement avec copie de l'onglet donc c bon mais pense à renommer les tableaux sur la copie sinon cela n'aura aucun sens et compliquera la maintenance

Evite le goto

Et ajoute des contrôles car tout n'est pas accepté comme nom d'onglet
Il me parait essentiel d'avoir une liste globale des salariés qui pourrait servir à contrôler l'ajout des onglets. La formule de récupération du nom-prénom du salarié à partir du nom de l'onglet ne me parait pas fiable...

Exemple de code pour éviter le goto et renommer le tableau

Sub ajout_feuille()
Dim Nom As String, i As Byte, Verif As Boolean
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
 
Do
    Nom = InputBox("Définissez le nom de votre nouvelle feuille", "Ajout nouvelle feuille")
    Nom = UCase(Nom)

    If Nom = "" Then Exit Sub

    For i = 1 To Sheets.Count
        If Sheets(i).Name = Nom Then Verif = True
    Next
 
    If Verif = True Then
        MsgBox "la feuille " & Nom & " existe déjà, veuillez choisir un autre nom"
    End If

Loop While Verif = True

ThisWorkbook.Worksheets("EXEMPLAIRE").Copy after:=Sheets(Sheets.Count)
ActiveSheet.Name = Nom
ActiveSheet.ListObjects(1).Name = Trim(Replace(Nom, " ", "_"))

Application.ScreenUpdating = True
End Sub