Like a virgin

Tableau hideux ! Z'appréciez le jeu de mot, j'espère ?

Où insérer les données ?

Figure 1 : Tableau exemple dans lequel, il faut rajouter des lignes.

À vrai dire Muchmuch ne s'est jamais posé la question. Il lui semble naturel de le faire à la suite des autres lignes.
Le collègue fortiche en informatique et miss Sessy font bien comme cela depuis toujours, de plus pour une fois Buzzbee cela ne le dérange pas.

• Pourtant il existe plusieurs possibilités.

Insérer en haut

La solution choisie par les Anglo-saxons, serait d'insérer les données entre la ligne des titres de colonnes et la première ligne de données.

Figure 2 : Insérer une ligne en haut du tableau.

Que celui qui choisi cette solution, s'exile immédiatement au Texas ou autres états où l'on compte encore en tête de bétail.

Cette solution est inepte et ne sert qu'à détruire votre réputation auprès de vos collègues.

Insérer au milieu

C'est une solution élégante et à conseillé aux débutants, pourquoi ?

Nous insérons dans le tableau, donc la nouvelle ligne est déjà formatée comme le reste du tableau, pas de mise en forme à refaire.

Figure 3 : Les mises en forme sont conservées.

De manière identique, les calculs qui font référence à une colonne du tableau sont eux aussi mis à jour.
Ainsi dans le tableau d'origine, les calculs des colonnes vont de la ligne 11 à la ligne 19.

Figure 4 : Les totaux, font référence aux colonnes du tableau.

Tout naturellement, comme l'insertion se fait dans les coordonnées des formules (entre les lignes 9 et 11), Excel ajuste les adresses dans les formules pour prendre en compte la nouvelle ligne.
C'est le comportement de Excel quand les données sont insérées dans les coordonnées des formules de calcul.

Figure 5 : Les totaux, font référence aux nouvelles adresses du tableau.

Excel est bien passé à la ligne 20 dans les formules.

Les calculs sont ajustés.

Figure 6 : Les résultats des calculs sont mis à jour.

Oui mais voilà...

Ce n'est pas par hasard si Muchmuch and Co. insère au bas des tableaux.

• Vous voyez un préhistorique graver sur la pierre une nouvelle ligne entre deux autres quand il note le résultat de la chasse ?
• Imaginez-vous un comptable au 18° siècle, déchirer au couteau son tableau en deux pour insérer une nouvelle information ?

Il y a des habitudes qui ont la vie rude !

Donc de façon naturelle l'homme ajoute à la fin.
Il n'y a avec l'informatique aucune justification à ça.

• Retrouver l'ordre naturel de saisie ? À quoi ça sert ? De toute façon au premier tri cet ordre est perdu.
  Après deux ans d'utilisation du classeur, je vous met au défi de m'assurer que les données sont bien triées dans l'ordre de la saisie.
• Vous avez besoin d'un ordre précis dans l'organisation des données ? Alors vous avez mis une colonne numérotée (ou une date ou une information, toujours unique) pour trier dessus.

Une ligne en plus, une case en moins

Il y a quand même des raisons pour insérer à la fin.

Le jour où l'on souhaite automatiser l'ajout de données au tableau (et oui, il faut savoir prévoir aujourd'hui pour demain en informatique).
L'automatisme passe par des traitements toujours identiques pour ne pas faire des usines à gaz.
Donc insérer toujours au même endroit.

Le respect du fonctionnement de l'utilisateur. Il insère compulsivement à la fin, le tableau doit s'adapter à son fonctionnement.

Insérer en fin de tableau

Si vous avez bien suivi c'est la solution choisie par une majorité d'utilisateurs.

Mais c'est aussi la pire (à égalité avec l'insertion en haut du tableau).

Les mises en forme sont perdues puisque la ligne d'insertion n'est pas formatée comme le reste du tableau.

Figure 7 : Les mises en forme sont à refaire.

Les formules de calcul ne suivent pas car nous insérons en dehors des coordonnées référencées dans la formule.

L'ajout, se fait en ligne 20, mais les formules référencent comme ligne de fin la 19.

Figure 8 : Les totaux, font référence aux anciennes adresses du tableau.

Forcément Excel ne met rien à jour et il faut modifier manuellement les adresses des formules.

Figure 9 : Les formules de calcul ne sont pas à jour.

Des solutions limites perverses

Muchmuch corrige à la main – d'où les deux jours.

Il vit la situation de la majorité des utilisateurs. Refaire et recommencer à longueur de temps. Il peste, râle, gémit.

Et heureusement qu'il ne sait pas qu'une formule d'un classeur peut utiliser une plage d'un autre classeur !
Je vous fiche mon billet que tôt ou tard, il oublierait d'aller modifier les coordonnées dans le classeur concerné.

C'est une des erreurs les plus fréquentes sur Excel, des formules qui ne font plus référence à la bonne plage.

Le service informatique pond une solution efficace

Combien de questions passent sur les forums pour évoquer les mises en forme lors de l'ajout d'informations !

Les fous furieux du service informatique ont adaptés une petite macro-commandes toute mignonne et légère trouvée sur une réponse à cette question existentielle.
Je leur laisse l'entière responsabilité de leur irresponsabilité.

Muchmuch se souvient encore de la maladie des yeux-qui-deviennent-globuleux-comme-le-caméleon quand il a vue la solution proposée.

With .Range(.Range("A" & Nl), .Range("G" & Nl))
.Borders(xlDiagonalDown)
.LineStyle = xlNone
.Borders(xlDiagonalUp)
.LineStyle = xlNone
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
.Borders(xlInsideVertical).LineStyle = xlNone
.Borders(xlInsideHorizontal).LineStyle = xlNone
End With

De plus cela ne fonctionne que sur ce classeur précis, pas ailleurs. Moi je dis 4 ans d'attente pour ça, c'est un peu exagéré non ?

Le collègue fortiche propose une formule délirante

Comme d'habitude, le collègue fortiche est à l'Ouest (m'étonnerait pas qu'il soit Américain lui) avec sa solution ingénieuse mais comme toujours hors de propos.

{=SI(ESTERREUR(INDEX($B$3:$B$7;PETITE.VALEUR(SI(
EQUIV($B$3:$B$7;$B$3:$B$7;0)=LIGNE(INDIRECT("1:"&
LIGNES($B$3:$B$7)));EQUIV(B3:B7;B3:B7;0);"");LIGNE(
INDIRECT("1:"&LIGNES($B$3:$B$7))))));"";INDEX($B$3:$B$7;
PETITE.VALEUR(SI(EQUIV($B$3:$B$7;$B$3:$B$7;0)=LIGNE(
INDIRECT("1:"&LIGNES($B$3:$B$7)));EQUIV(B3:B7;B3:B7;0);"");
LIGNE(INDIRECT("1:"&LIGNES($B$3:$B$7))))))}

Étaler sa science, c'est étaler au yeux de tous son incompétence (ce que je n'arrête de faire sur ce blog, d'ailleurs, lol !).

Note aux inconscients

Certain règlent le problème pour les formules en faisant référence à toute la colonne

=Somme(A:A)

Soit, c'est comme prendre un chariot pour acheter un paquet de chewing-gum et cela ne règle en aucun cas le problème des mises en forme.

Prévoir l'imprévisible

Certains ingénieux pas forcément ingénieurs, me la jouent fine. Ils préparent un tableau avec un certains nombre de lignes vierges.

Figure 10 : Les mises en forme sont à conservées.

Les calculs inclus les adresses de ces lignes vierges.

Figure 11 : Les résultats des calculs sont mises à jour.

C'est une solution aussi efficace que d'insérer dans les données, cas évoqué plus haut et admissible pour des débutants.

Elle soulève deux questions.

• Comment justifier le nombre de lignes excédentaires ?
  C'est au hasard, basé sur des informations antérieures (expérience) ?
• Et si on a vue trop juste ? Retour à la case départ : Où insérer les données ?

Tableau Microsoft

Internet et les experts suggèrent de mettre sous forme de tableau.

Figure 12 : Tableau présenté à l'aide de la commande "Mettre sous forme de tableaux".

Au première abord c'est plutôt séduisant. Il est même possible de faire des mises en forme plutôt "chiadées" !

Mais les mises en forme ne sont pas toujours souples. Il faut créer un modèle de tableau et tralali et tralala...

Figure 13 : Les mises en forme sont à conservées.

Les formules de calcul sont gérées par Microsoft Excel. Je n'ai pas encore testé comment il se comporte dans des cas complexes.

Figure 14 : Les formules suivent.

Les formules classiques se transforment en signes cabalistiques que seul un druide est capable de lire.

Figure 15 : Fonctionne mais (sic...)

Pour réaliser votre calcul, vous devez faire une incantation à l'arobase et deux pour le crochet ouvrant, n'oubliez pas de louer comme il ce doit notre grand maître le point.

Imaginez la tête à Muchmuch si le collègue fortiche lui déboule avec un tableau pareil !

Tiens en bas, la somme a une tête normale.

Prêche à la ligne

L'idée de dimensionner le tableau à l'avance n'est pas si naïve qu'il n'y parait. Elle ne va simplement pas au bout de la logique.

Plutôt que de mettre un nombre de lignes plus ou moins aléatoire, il suffit d'en mettre une et d'insérer à son niveau.

Je créé une ligne d'insertion, formatée comme les autres lignes du tableau, elle est aussi incluse dans les adresses de formules.

Figure 16 : Tableau avec une ligne d'insertion.

Elle se comporte donc comme si j'insérais n'importe où dans le tableau, mais j’insère toujours à la fin.

Figure 17 : Ligne formatée comme les autres.

Je reste dans mon tableau, donc les mises en forme sont conservées.

Figure 18 : Les mises en forme sont à conservées.

Pour la même raison, les formules de Excel se mettent à jour automatiquement.

Figure 19 : Les formules suivent.

Mes formules ont bien prises en compte la nouvelle ligne. Remarquez que les formules s'arrêtent bien à la ligne vierge (ligne 21).

Figure 20 : Les totaux, font référence aux nouvelles adresses du tableau.

Elle a aussi l'avantage d'éviter la tentation d'écrire les sommes de colonnes immédiatement sous les données cas que je ne manquerais pas d'évoquer prochainement tant chez Total ils ont rigolé.

De simples précautions

Cette ligne d'insertion doit rester vierge. Pas de formules qui n'afficheraient rien, ni même un espace ne doit se trouver dans les cellules de cette plage.

Utiliser cette ligne

Lorsque vous sélectionnez une colonne du tableau dans un calcul, pour une mise en forme et mise en forme conditionnelle, pour définir des noms de cellules, validations de données, etc., incluez toujours cette ligne vierge.

Ainsi tout se mettra à jour tout seul lors d'insertion d'informations dans le tableau.

Adieu gourous, solutions fantaisistes et barbares.

Like a virgin !