Tableaux et formules
Formule unique
Une caractéristique très pratique des tableaux : une formule unique peut s'appliquer à l'ensemble d'une colonne. Il suffit de créer la formule dans une des cellules de la colonne pour que la colonne soit dupliquée à toute la colonne. La modification d'une cellule (par "F2" ou par la barre de formule modifie les formules de toute la colonne.Pratique... à condition de bien maîtriser, car....
Le remplacement d'une formule dans une cellule, ne modifie que la formule de la cellule et pas la colonne dans son semble. La formule non cohérente est marquée par le minuscule indicateur en triangle vert dans le coin haut-gauche de la cellule.
Cette duplication automatique des formules à l'ensemble du tableau présente tout de même de sérieux avantages pour les tableaux de saisie. Le formules rendues nécessaires par l'adjonction de lignes de créent automatiquement, au fur et à mesure du besoin, sans qu'il soit nécessaire d'en faire une duplication manuelle ou de remplir un tableau vide de formules inutilisées dans l'attente de saisies à venir.
Système de nommage propre aux tableaux
Les tableaux bénéficient d'un système de nommage propre :- Le tableau lui-même est identifié par un nom ; par défaut "tableau"+ un incrément, mais modifiable en fonction des besoins pour une bonne lisibilité des formules ;
- Les colonnes sont nommées par le contenu de la cellule d'entête de la colonne.
exemple : la colonne des montants est nommée [Montant] ; - La dénomination [@Montant] renvoie la valeur dans l'intersection ligne en cours / colonne Montant ;
La dénomination [Montant] renvoie la référence à la colonne Montant toute entière - La dénomination [#Totaux] identifie la ligne de totaux ; =+ventes[[#Totaux];[Facture]] renverra la valeur de la cellule à l'intersection de la ligne de totalisation et de la colonne Facture, sans préjuger du type de calcul ou de traitement réalisé dans cette cellule.
 |
| Excel - tableau - utilisation du nommage dans les formules |
Tableaux et requêtes
Les requêtes SQL ou Power Query permettent d'alimenter un modèle Excel de façon efficace. Les données sont importées sous forme d'un tableau, correspondant au résultat de la requête.
Un tableau issu d'une requête est très flexible dans ses dimensions : il s'adapte automatiquement lors des mises à jour, en nombre de lignes et en colonnes, si la requête est modifiée.
Il est possible d'ajouter manuellement des colonnes supplémentaires :
- Pour compléter les informations provenant de la requête ;
- Pour faire des calculs complémentaires, plus aisés que par le langage SQL (c'est beaucoup moins vrai si l'on utilise des requêtes Power Query, très puissantes) ;
- Pour faire interagir les données importées avec d'autres données du modèle Excel, et non disponibles dans la base source de la requête.
Il est toutefois préférable de ne pas mélanger calculs et importation de données, et de réserver cette adjonction de colonne à des opérations de "mise en forme" des données :
- calcul d'un mois ou d'un trimestre à partir d'une date,
- changement de signe d'une donnée comptable,
- récupération d'une partie d'une zone texte,
- adjonction d'un libellé à partir d'une table...
L'utilisation des requêtes SQL et Power Query fera l'objet de longs développements à venir...
Totalisations
La ligne de totaux est à la fois pratique et très flexible puisque elle permet d'associer à chaque colonne une fonction de calcul (pas seulement un total arithmétique) s'appliquant soit à l'ensemble du tableau (lignes affichées et lignes masquées) soit aux seules lignes affichées.
Inconvénient, la ligne total est toujours placée en bas du tableau, immédiatement après les lignes de données, position pas toujours pratique dès que la liste comporte un grand nombre d'enregistrements (il faut faire défiler le tableau pour lire les totaux). On peut donc regretter l'absence d'une option permettant de positionner la ligne de totaux immédiatement avant les lignes de données, sous les entêtes de colonne.
Si l'on tient à conserver les totaux à proximité des entêtes, on peut, au choix :
- Positionner manuellement des totalisations au dessus les lignes d'entête ;
- Reconstituer à un autre emplacement, entêtes et totaux...
 |
| Totaux reportés en haut du tableau |
Excel propose par défaut la fonction "SOUS.TOTAL" (syntaxe : =SOUS.TOTAL(num;plage) dont les caractéristiques sont :
- De s'appliquer aux seules lignes affichées (dans le cas d'un tableau) ; dans une utilisation générale, la fonction permet de ne pas totaliser les totaux intermédiaires présents dans la plage ;
- De permettre un éventail de calculs différents, sélectionné par le premier paramètre de la fonction.
Le paramètre "num" est un numéro de fonction, de 1 à 11 et de 101 à 111.
| n° de fonction comprend les valeurs masquées | n° de fonction ignore les valeurs masquées | Fonction |
| 1 | 101 | MOYENNE |
| 2 | 102 | NB |
| 3 | 103 | NBVAL |
| 4 | 104 | MAX |
| 5 | 105 | MIN |
| 6 | 106 | PRODUIT |
| 7 | 107 | ECARTYPE |
| 8 | 108 | ECARTYPEP |
| 9 | 109 | SOMME |
| 10 | 110 | VAR |
| 11 | 111 | VAR.P |
La plage proposée est par défaut la colonne active. Mais rien n'empêche de modifier la formule pour réaliser un traitement sur une autre colonne du tableau, ou tout autre calcul qui serait nécessaire.
