Menu

Like a virgin


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

Où insérer les données ?

Tableau de départ

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.

Insertion ligne entre les 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.

Insertion ligne au milieu d'un tableau Microsoft Excel.

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.

Tableau exemple avant insertion de lignes.

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.

Comportement des formules avec insertion dans la plage.

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.

Insertion ligne au milieu d'un tableau Microsoft Excel.

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

Oui mais voilà...

Utilisateur en core sous influence du papier/crayon.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.

Insertion ligne en fin d'un tableau Microsoft Excel.

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.

Comportement des formules avec insertion en fin du tableau Excel.

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.

Insertion ligne en fin d'un tableau Microsoft Excel.

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

Des solutions limites perverses

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

Grosse colère lors de l'insertion de lignes.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 !

Développeur menotté.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

Le fou du village

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.

Insertion ligne à l'avance d'un tableau Microsoft Excel.

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

Les calculs inclus les adresses de ces lignes vierges.

Lignes créées à l'avance dans un tableau Microsoft Excel.

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

Icône "Mettre sous forme de tableau" Microsoft Excel.Internet et les experts suggèrent de mettre sous forme de tableau.

Tableau créé à l'aide de l'outil de Microsoft Excel.

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...

Tableau dont les données sont bien présentées comme le reste du tableau.

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.

Tableau avec les formules qui sont mises à jour.

Figure 14 : Les formules suivent.

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

Formules produites à l'aide de l'outil de Excel.

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.

Insérer en fin avec une ligne.

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.

Insertion ligne à l'aide de celle prévue à cet effet.

Figure 17 : Ligne formatée comme les autres.

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

Tableau dont les données sont bien présentées comme le reste du tableau.

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

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

Tableau avec les formules qui sont mises à jour.

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).

Comportement des formules avec une ligne d'insertion.

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 !

Restez informé

Inscrivez-vous à la newsletter et recevez l'actualité du blog dans votre boite aux lettres.

Laisser un commentaire


Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Prouvez que vous êtes humain. * Time limit is exhausted. Please reload the CAPTCHA.