Après avoir vu comment créer et formater une table, nous allons voir quelques particularités sur l'écriture des formules dans les tables.

Références dans les tables

Les tables ont un nom qui est unique dans le classeur. Par conséquent, il n'est pas utile de préciser le nom de la feuille sur laquelle la table est située.
Les tables sont en fait un objet (au sens programmatique du terme) excel de type plage de données (range) et il est stocké comme tel par excel. Mais il y a une particularité : la ligne d'entête et celle des totaux ne sont pas incluses dans le range défini par le nom !

Dans les options d'excel 2007, vous avez la possibilité de travailler avec les noms de tableaux dans les formules. Si ce n'est déjà fait, cochez cette option et la saisie semi-automatique de formule pour suivre ce tutoriel (options/formules/manipulation de formules)

Un des grands intérêts des tables c'est d'avoir un système de référence "interne" à la table, c'est à dire ne dépendant pas des numéros de lignes et de colonnes.
Reprenons l'exemple utilisé dans la page précédente, et ajoutons une colonne "total dépenses"
Au bout de la première ligne de données, tapez = somme( et sélectionnez les données de la première ligne).

Vous voyez deux choses particulières :
1) Au lieu du

=somme($B$2:$D$2) 

attendu, la formule est

=SOMME(data_famille[[#Cette ligne];[nb personnes]:[visites]])

Les références sont constituées du nom de la table (data_famille) puis entre crochets droits des références ligne;colonne définies par les entête de colonnes.
[#cette ligne] indique qu'on fait référence à la même ligne que celle sur laquelle se trouve la formule.

NB dans excel 2010 [#cette Ligne] est noté [@] ce qui est autrement plus lisible et raccourcit agréablement les formules !

2) quand vous validez la formule, en cliquant sur l'icone qui apparait puis sur le message, la formule se recopie automatiquement dans toute la colonne.

De façon notable, la formule est exactement la même dans toute la colonne, il n'y a pas de référence à des numéros de ligne et de colonne comme dans une formule classique.

Intérêt du mode de référence des tables

A première vue on se dit que c'est plus simple de taper (ou de lire)

=somme($B$2:$D$2) 

que

=SOMME(data_famille[[#Cette ligne];[nb personnes]:[visites]])

Cette notation présente l'avantage d'expliciter les références en termes de contenus plutôt que de position.
En fait ce type de notation est surtout intéressante (je trouve) lorsque les tables sont appellées à s'étendre par l'ajout de nouvelles lignes ou de nouvelles colonnes.
C'est particulièrement pratique quand on a plusieurs tables les unes en dessous des autres dans une feuille qui chacune peuvent accueillir de nouvelles données.
En effet, l'ajout de données dans celle du haut de la feuille décale les références (en numéros de lignes) de la seconde table.
Avec le mode de notation "tables", les références ne changent pas. Ainsi,

=data_famille[[#En-têtes];[Famille]]

fait référence à la première cellule de la table, quelle que soit sa position dans la feuille.
Cette stabilité des références est particulièrement utile pour manipuler les tables en VBA.

Vous pouvez cependant tout à fait utiliser les références classiques pour utiliser les données d'une table.
Si vous souhaitez désactiver de façon permanente ce mode de référence, décochez l'option correspondante dans les options d'excel.

Références absolues et semi relatives dans une table.

Une limite du mode de référence des tables c'est l'impossiblité de fixer une référence comme on le fait avec les $ dans une référence classique.
C'est parfois très gênant.
Disons que l'on veut calculer les dépenses de courses et de visites par personne.
Effaçons la colonne total dépenses et créons deux nouvelles colonnes avec comme intitulé courses_pax et visites_pax
En dessous de courses_pax, la formule

=data_famille[[#Cette ligne];[courses]]/data_famille[[#Cette ligne];[nb personnes]]

nous donne ce qu'on souhaite. Mais en tirant la formule vers la droite pour l'appliquer à la colonne visites_pax, il faudrait fixer la partie

data_famille[[#Cette ligne];[nb personnes]]

On peut le faire de deux façons :
On peut tout à fait faire un mélange de références de type tables et de références classiques:
=data_famille[[#Cette ligne];[courses]]/$B2
Ici on utilise des références semi-relatives, de façon à ne fixer que la référence de la colonne, pour que la formule s'adapte dans le reste de la colonne.

C'est pratique mais on perd l'avantage de s'affranchir complètement des références aux numéros de ligne et de colonne.
On peut le faire en utilisant la fonction INDIRECT qui transforme du texte en une référence. Quand on tire une formule contenant indirect(" ref de la cellule") le texte entre guillemets n'est pas modifié ce qui permet de fixer notre formule.
Un petit conseil, n'hésitez pas à modifier petit à petit vos formules si elles sont un peu complexes, et à ajouter des espaces entre les parenthèses et crochets pour ne pas vous y perdre !

=data_famille[[#Cette ligne];[courses]]/INDIRECT("data_famille[[#Cette ligne];[nb personnes]]")

Dans ce cas précis, [#cette ligne] fait que cette référence reste une référence semi-relative, ce que nous souhaitons.

Cette notation de type table fonctionne aussi pour faire dans une cellule hors d'une table des calculs sur celle-ci.

=SOMMEPROD((data_famille[nb personnes]=2)*(data_famille[courses]))

Renvoie le montant dépensé pour les courses par les familles de deux personnes.
Là encore, on peut utiliser indirect pour fixer une référence.

Utiliser les entêtes de colonnes comme valeurs dans une formule.

Les entêtes de colonne peuvent contenir des dates ou des valeurs que l'on souhaite utiliser dans une formule.
Il faut se souvenir que le fait de transformer une plage de données en formule transforme en même temps en texte les entêtes de colonne.
Pour utiliser cet entête comme une valeur il faut simplement transformer à nouveau le texte en valeur, par exemple en ajoutant 0 ou en multipliant par 1.

Utiliser la colonne d'une table comme source dans données/validation

Il ne semble pas que dans données/validation, on puisse restreindre le choix à une liste et indiquer directement = table2[colonne1] comme source de données, ce qui est ma foi fort dommage. ça ne marche pas non plus si la table n'a qu'une seule colonne.
On peut cependant ruser et dans le gestionnaire de nom définir un nouveau nom : masource=matable[macolonne]
Cette façon de définir une source pour la validation de données est intéressante car la source est dynamique (si on ajoute de nouvelles valeurs elles sont prises en compte), et on n'a pas besoin d'indiquer le nom de la feuille contenant les données.