Utiliser des colonnes calculées dans un tableau Excel

Les colonnes calculées dans les tableaux Excel sont un outil fantastique pour entrer efficacement des formules. Ils vous permettent d’entrer une formule unique dans une cellule, puis cette formule est automatiquement étendue au reste de la colonne par elle-même. Il n’est pas nécessaire d’utiliser les commandes Fill ou Copy. Cela peut être incroyablement gagner du temps, surtout si vous avez beaucoup de lignes. Et la même chose se produit quand vous modifiez une formule ; la modification s’étend également au reste de la colonne calculée.

Remarque : Les captures d’écran illustrant cet article ont été prises dans Excel 2016. Si vous avez une autre version, votre affichage peut être légèrement différent mais, sauf indication contraire, la fonctionnalité est la même.

Créer une colonne calculée

Créer un tableau Si vous n’êtes pas familiarisé avec les tableaux Excel, vous pouvez en savoir plus sur : Vue d’ensemble des tableaux Excel.
Insérez une nouvelle colonne dans la table. Pour ce faire, vous pouvez taper dans la colonne immédiatement à droite du tableau, et Excel étend automatiquement le tableau pour vous. Dans cet exemple, nous avons créé une colonne en tapant « Total général » dans la cellule D1.
Conseils :
- Vous pouvez également ajouter une colonne de table à partir de l’onglet Accueil . Cliquez simplement sur la flèche Insérer > Insérer des colonnes de table à gauche.
Tapez la formule que vous souhaitez utiliser, puis appuyez sur Entrée.

Dans ce cas, nous avons entré =sum(, puis sélectionné les colonnes Qtr 1 et Qtr 2 . Par conséquent, Excel a généré la formule : =SOMME(Table1[@[Qtr 1] :[Qtr 2]])). C’est ce qu’on appelle une formule de référence structurée , qui est propre aux tableaux Excel. Le format de référence structuré permet à la table d’utiliser la même formule pour chaque ligne. Une formule Excel normale pour cela serait =SOMME(B2 :C2), que vous devrez ensuite copier ou remplir dans le reste des cellules de votre colonne

Pour en savoir plus sur les références structurées, consultez : Utilisation de références structurées avec des tableaux Excel.
Lorsque vous appuyez sur Entrée, la formule est automatiquement remplie dans toutes les cellules de la colonne, au-dessus et en dessous de la cellule dans laquelle vous avez entré la formule. La formule est la même pour chaque ligne, mais comme il s’agit d’une référence structurée, Excel sait en interne quelle ligne est laquelle.

Remarques :

Il est également possible de créer une colonne calculée en remplissant ou en copiant une formule dans toutes les cellules d’une colonne de tableau vide.
Si vous tapez ou déplacez une formule dans une colonne de table qui contient déjà des données, une colonne calculée n’est pas créée automatiquement. Toutefois, le bouton Options de correction automatique s’affiche pour vous permettre de remplacer les données afin qu’une colonne calculée puisse être créée.
Si vous entrez une nouvelle formule différente des formules existantes dans une colonne calculée, la colonne est automatiquement mise à jour avec la nouvelle formule. Vous pouvez choisir d’annuler la mise à jour et de conserver uniquement la nouvelle formule à partir du bouton Options de correction automatique. Toutefois, cela n’est généralement pas recommandé, car il peut empêcher votre colonne de se mettre à jour automatiquement à l’avenir, car il ne sait pas quelle formule étendre lorsque de nouvelles lignes sont ajoutées.
Si vous avez tapé ou copié une formule dans une cellule d’une colonne vide et que vous ne souhaitez pas conserver la nouvelle colonne calculée, cliquez sur Annuler deux fois. Vous pouvez également appuyer deux fois sur Ctrl+ Z

Une colonne calculée peut inclure une cellule qui a une formule différente du reste. Cela crée une exception qui sera clairement marquée dans la table. De cette façon, les incohérences par inadvertance peuvent facilement être détectées et résolues.

Notification d’erreur de formule incohérente dans un tableau Excel

Remarque : Des exceptions de colonne calculée sont automatiquement créées lorsque vous effectuez l’une des opérations suivantes :

tapez des données autres qu’une formule dans une cellule de colonne calculée ;
Tapez une formule dans une cellule de colonne calculée, puis cliquez sur Annuler dans la barre d’outils Accès rapide.
tapez une nouvelle formule dans une colonne calculée qui contient déjà au moins une exception ;
copiez des données dans la colonne calculée qui ne correspond pas à la formule de colonne calculée.

Remarque : Si les données copiées contiennent une formule, cette formule remplace les données dans la colonne calculée.
Supprimer une formule d’une ou plusieurs cellules dans la colonne calculée.

Remarque : Cette exception n’est pas marquée.
déplacez ou supprimez une cellule dans une autre zone de la feuille de calcul, qui est référencée par l’une des lignes d’une colonne calculée.

La notification d’erreur s’affiche uniquement si l’option de vérification des erreurs en arrière-plan est activée. Si vous ne voyez pas l’erreur, accédez à Options de > fichier > Formules > vérifiez que la case Activer l’erreur en arrière-plan est cochée.

Si vous utilisez Excel 2007, cliquez sur le bouton Office , puis sur Options Excel > Formules.
Si vous utilisez un Mac, accédez à Excel dans la barre de menus, puis cliquez sur Préférences > Formules & Listes > Vérification des erreurs.

L’option permettant de remplir automatiquement des formules pour créer des colonnes calculées dans un tableau Excel est activée par défaut. Si vous ne souhaitez pas qu’Excel crée des colonnes calculées lorsque vous entrez des formules dans des colonnes de tableau, vous pouvez désactiver l’option de remplissage des formules. Si vous ne souhaitez pas désactiver l’option, mais que vous ne souhaitez pas toujours créer des colonnes calculées pendant que vous travaillez dans une table, vous pouvez arrêter la création automatique des colonnes calculées.

Activer ou désactiver les colonnes calculées
1. Sous l’onglet Fichier, cliquez sur Options.
  
  Si vous utilisez Excel 2007, cliquez sur le bouton Office , puis sur Options Excel.
2. Cliquez sur Vérification.
3. Sous Options de correction automatique, cliquez sur Options de correction automatique.
4. Cliquez sur l’onglet Lors de la frappe.
5. Sous Automatiquement pendant que vous travaillez, activez ou désactivez la case à cocher Remplir les formules dans les tables pour créer des colonnes calculées pour activer ou désactiver cette option.
  
  Conseil : Vous pouvez également cliquer sur le bouton Options de correction automatique qui s’affiche dans la colonne du tableau après avoir entré une formule. Cliquez sur Contrôler les options de correction automatique, puis désactivez la case à cocher Remplir les formules dans les tables pour créer des colonnes calculées pour désactiver cette option.
Si vous utilisez un Mac, accédez à Excel dans le menu principal, puis Préférences > Formules et listes > Tableaux & Filtres > Remplir automatiquement les formules.
Arrêter de créer automatiquement des colonnes calculées

Après avoir entré la première formule dans une colonne de table, cliquez sur le bouton Options de correction automatique qui s’affiche, puis cliquez sur Arrêter la création automatique de colonnes calculées.

Vous pouvez également créer des champs calculés personnalisés avec des tableaux croisés dynamiques, où vous créez une formule et Excel l’applique ensuite à une colonne entière. En savoir plus sur le calcul des valeurs dans un tableau croisé dynamique.