Sur Excel, l’utilisation des formules particulièrement peu explicites du type : (A1*B5)/C3 peut être avantageusement remplacée par l’utilisation de nom de zones, voire par les en-têtes de lignes ou de colonnes (nouveauté 97)

Toutefois, attention à l’insertion de nouvelles colonnes, l’insertion d'une nouvelle colonne devant la colonne " TOTAL " ne provoquera pas de recalcul, mais placé entre la colonne d’en-tête et la colonne " Avril " , le recalcul s’effectuera normalement. Toutefois, ne pas oublier également les grandes potentialités du bouton somme automatique.

A une cellule ou à une plage de cellule, il est possible d’attribuer un nom qui va rendre plus explicite l’utilisation des formules et sera disponible dans tout le classeur . Plus formaliste que précédemment puisque nous sommes obligés de passer par la création préalable du nom, cette possibilité offre plus de puissance (disponibilité dans le classeur) et plus de souplesse (possibilité de modification et de gestion des noms, type de données : cellules, formules, constantes…). L’intérêt principal de cette technique reste la clarté des formules : dans une feuille de paye =BRUT*RDS est plus compréhensible que =E6*B24. D'autre part,  les différents taux peuvent être stockés dans une feuille de constantes dans le classeur.

Le nom peut concerner une cellule

par le menu INSERTION NOM DEFINIR

• Le nom peut concerner une constante - Exemple : par Insertion-Nom-Définir, créer une constante appelée TVA2 qui égale 20.6% : =20.6%. Les calculs devenant B20*TVA2

• Le nom peut concerner une fonction - Exemple : Pour avoir le montant TTC, : par Insertion-Nom-Définir, créer une formule appelée TTC qui égale: =1+tva2. Les calculs devenant B20*TTC

• Pour les formules (un peu plus compliquées) de type recherche (RECHERCHEV, INDEX…) l’utilisation de nom de plages permet une plus grande clarté des formules ex =MontantHt*RECHERCHEV(PRODUIT;CODE_TVA;2;FAUX) au lieu de =B12*RECHERCHEV(A12;Cst!A2:B6;2;FAUX) –dans l’exemple des taux de tva multiples- Exemple : Dans la feuille " Cst ", nous avons des taux de TVA différents suivant les articles. Grâce à la fonction RECHERCHEV , nous allons appliquer le bon taux au bon produit.

Ce sont des formules renvoyant à une plage rectangulaire de valeurs et non à une seule cellule. Leur intérêt est de s'appliquer à l'ensemble d'une plage de cellules ou "matrice" (somme conditionnelle à multiples critères...) Pour les amateurs, visitez le site passionnant d'un "pro" : http://longre.free.fr , là vous verrez ce que Connaître Excel veut dire, votre humble serviteur lui doit beaucoup.

Il convient de sélectionner la zone de destination, de taper la formule dans la cellule active et de valider par ctrl+maj+entrée. Exemple {=MontantHt*tva1} ce qui garde une grande lisibilité tout en diminuant (un peu) la taille du fichier 1 formule au lieu de 6. Cependant, le temps de recalcul est plus long qu’avec des formules standard.

A noter, toutefois, l’utilisation de formules matricielles dans une seule cellule quand l’apparition des résultats intermédiaires ne présente pas d’intérêt.

Ce système est également très pratique pour les constantes : nom, adresse… réunis dans un classeur menu lancé à chaque démarrage car les informations sont mises à jour à chaque lancement pour tous les classeurs et peuvent être personnalisés par chaque service (ex menu.xls et be.xls).  N’oubliez pas les nouvelles possibilités 97, d’insérer un lien hypertexte (bouton dans la barre d’outil standard) qui charge un fichier à un endroit précis.

La consolidation de données a évolué sous Excel 97 en incorporant le mode plan et en permettant un recalcul par rapport aux données sources.

Néanmoins, la mise en œuvre reste encore assez lourde et peu pratique par rapport au tableau croisé dynamique

Bien souvent, la méconnaissance des fonctions d’Excel entraîne la création de fonctions très longues à base de SI imbriqués avec des opérandes (+,-,/,*) dans tous les sens ou de calculs intermédiaires. L’assistant fonction permet de visualiser rapidement le type de catégories et de trouver la fonction adéquate.

Nous pourrons faire un descriptif de chaque fonction. Nous insisterons plus particulièrement sur quelques catégories :

La règle de base à connaître pour éviter des problèmes demeure que les dates et heures sont des valeurs numériques sur lesquelles sont appliquées un format particulier. La valeur 1 correspond au 1 Janvier 1900. Le 1000^ème jour après le 01/01/19000 correspond au 26/09/1902 . Le numéro de série du 01/01/2000 est 36526. Ainsi Excel, peut effectuer des calculs sur les dates en renvoyant une valeur numérique.Les heures, les minutes et les secondes correspondent aux chiffres après la virgule.

La seconde règle est la connaissance des formats de cellule date :  exemple le 14/07/2000. Pour accèder à ces format, faire un clic droit sur une date , Format Cellule, et dans l'onglet Nombre, regarder la catégorie Personalisé, vous pourrez alors modifier le format à loisir !

Parmi les fonctions Date les  plus utilisées : inscription automatique de la date du jour automatiquement actualisée  : préférez AUJOURDUI() à MAINTENANT(). Car le premier ne renvoie que la date du jour en coupant heures minutes secondes. Utilisez également le raccourci clavier "CTRL ; " qui inscrit la date en tant que valeur (ne se recalcule pas). "CTRL : " inscrit l'heure active.

En outre :  La fonction DATE(Année ;Mois ; jour) permet de verrouiller tout ou partie de l’affichage de la date. Les fonctions d’extraction ANNEE, MOIS, JOUR permettent de travailler sur des parties de la date. Les Fonctions FIN.MOIS , MOIS.DECALER offrent des raccourcis rapides (macro complémentaire : Utilitaires d'analyse à installer si besoin).

Les calculs entre 2 dates sont simples, mais ne renvoie qu’un nombre de jours intransposable sous la forme AA MM JJ, le seul format acceptable est un format nombre ou du type 0 ou # ##0,00. Et n’oubliez pas que sur les PC, le 1/1/1900 égale 1 , sur les Mac 1 est égal au 01/01/1904 et que pour un calcul entre 2 dates, la date du type 15/09/99 est suivie de 00 :00 pour les heures et minutes  .

Pour les calculs sur les horaires, n’oubliez pas que la journée n’a pas plus de 24 heures. Seul, le format particulier [h] :mm peut permettre de dépasser cette limite (les crochets sont à taper directement dans la zone Type-Catégorie personnalisée de l'onglet Nombre du Format cellule)

Pour des calculs spéciaux sur les jours ouvrés, il existe des fonctions puissante : NB.JOUR.OUVRES, SERIE.JOUR.OUVRES

Il existe une autre possibilité qui fait apparaître un message si une condition est vrai ou fausse. La condition est énoncée dans une cellule sous la forme =si(condition ; AfficheVrai,AfficheFaux). Cette cellule peut être ensuite masquée. Un cadre texte sans bordure ni motif peut être dessiné et dans la zone de formule (en haut), on indique les références de la condition.

En C17 nous trouvons la formules =SI(C15>9.5;"Good";)

C’est l’équivalent de la fonction SOMME.SI en plus puissant, permettant le calcul selon 1 à PLUSIEURS paramètres. La mise en œuvre de la validation est moins évidente puisqu’elle doit porter sur les conditions préalablement choisies.

La première étape est identique : définition de la zone. La seconde permet de choisir le champ à totaliser soit avec la fonction SOMME pour les champs numériques soit avec NBVAL pour les champs textes. De plus, les conditions seront définies :
Les autres étapes sont identiques à celle de l’assistant Recherche. La formule est de type matricielle. Exemple :={SOMME(SI(journal!$D$2:$D$104=A7;SI(journal!$J$2:$J$104<>B7;
SI(journal!$G$2:$G$104>=C7;journal!$I$2:$I$104;0);0);0))}

La validation à l’intérieur d’une liste peut s’effectuer mais il faudra indiquer en source les valeurs possibles (exemple : A ;B ;C ;D ou liste avec zone de nom particulière.

En revanche, la formule peut être recopiée et d’autres conditions peuvent être ajoutées .

Les étiquettes de lignes et de colonnes serviront de critères de recherche :
La deuxième étape vous propose de trouver ces étiquettes de lignes et de colonnes. Si vous souhaitez choisir après les critères de recherches, appuyer sur le bouton suivant.

La troisième étape vous permet de choisir entre la copie du résultat ou des paramètres de recherche et du résultat.

Les dernières étapes vous permettront de disposer les différents éléments à l’endroit que vous souhaitez.

La formule obtenue utilise la fonction EQUIV pour retrouver la position du critère de ligne et de la colonne à rechercher, la fonction INDEX récupère à partir de ces positions dans la base les informations. Exemple ={INDEX(Base4!$B$1:$H$23; EQUIV(A25;Base4!$B$1:$B$23;); EQUIV(B25;Base4!$B$1:$H$1;))}
Une fois le résultat obtenu, pour modifier les paramètres de recherche, il convient au préalable de nommer les étiquettes de lignes et de colonnes. Puis l’étiquette de ligne étant sélectionnée, on appelle le menu Données Validation Autoriser Liste =NomEntête. On pratique de même avec la 1^ère colonne. Et la base de donnée est interrogeable dans tous les sens.

Ils peuvent être présents soit dans une feuille de calcul classique via la barre d’outil Formulaire ou via la création de boites de dialogues dites UserForm ou Dialogsheet dans le module Visual Basic Editor

A chaque fois, ils renvoient des valeurs et peuvent servir à exécuter des macros. Ils constituent en fait des points de décisions ou des embranchements.

Dans le module Visual Basic :

Dans la barre d’outils formulaire :

La zone de groupe sert à réunir des contrôles d’un même type : Case à cocher, bouton d’option

Très souvent les informations existent déjà quelque part sur le réseau ou sur un autre micro, il n’est guère rationnel de les retaper à l’occasion d’un tableau de synthèse qui peut avoir une durée de vie courte. Excel permet des importations de fichiers et une exploitation en directe ou en différé particulièrement souple et efficace notamment grâce au tableau croisé dynamique

Pour les bases volumineuses, il convient de paramétrer son écran afin de garder la première ligne et première colonne toujours à la vue. Positionné sur cellule (B2), par le menu FENETRE/Figer les volets, nous verrouillerons les colonnes à gauche et les lignes au-dessus (ici, 1ère ligne et 1ère colonne).
L’exploitation classique consiste en des tris (attention aux risques de modifier la base)
Règle : être sur une et une seule cellule avant de lancer un tri soit par DONNEES/Trier (à noter OPTION suivant des listes personnalisées) soit par les boutons.

Mais aussi et surtout dans des extraits ou filtres :
Pour cela, positionné sur une cellule de la 1^ère ligne, on appelle le menu DONNEES/FILTRE/FILTRE AUTOMATIQUE. Grâce aux flèches, on peut faire des extraits à l’écran suivant les valeurs possibles dans chaque colonne ce qui permettra de faire des totalisations.

Sur les taux de bourses (colonne O) nous allons demander à afficher les non-vides puis lancer le bouton Somme automatique. C’est la fonction SOUS.TOTAL qui apparaît (car, sur base filtrée), il convient de vérifier les points de départ et d’arrivée (de O2 jusqu’à O530). Le 9 correspond à la fonction somme, car nous avons à faire à des données numériques. Pour les autres paramètres , cf fonctions mathématiques. L’inconvénient principal demeure d’avoir à rajouter une ligne pour les calculs et par là-même à modifier la base.

Outil d’une grande puissance, le Tableau Croisé Dynamique permet de synthétiser les données de la liste en réalisant un tableau externe à la liste. Lié dynamique, ne mettant en oeuvre aucune formule , le TCD permet de synthétiser un ou plusieurs champs placés en lignes ou en colonnes. Les opérateurs possibles sont ceux qui existent avec la fonction sous-total (somme, moyenne, dénombrement, mini, maxi…). La présentation peut dépasser le simple calcul brut pour arriver aux pourcentages par ligne…

Si la liste de données Excel est la source la plus souvent utilisée, le TCD peut prendre des sources de données externes (Dbase, Access…), une ou plusieurs plages de calcul ou encore un TCD déjà réalisé.

Il est possible toutefois d’utiliser le clic droit à l’intérieur du tableau pour retrouver ces possibilités.

Actualiser : Si la base source évolue, le TCD ne sera pas automatiquement recalculé, le bouton actualisation permet de rafraîchir les tables permettant la mise à jour du tableau
( A NE PAS OUBLIER)

La sélection : par défaut, le TCD est en sélection normale, pour sélectionner lignes ou colonnes , il est nécessaire d’activer la sélection (clic droit, sélection)

Les options : possibilité de changer certains paramètres : nom, totaux, garder la mise en forme…

Le champ Page : il se comporte comme un champ filtré : selon l’option choisie, le tableau est immédiatement  mis à jour.

Afficher les Pages provoque la création de nouvelle feuilles

avec un TCD pour chaque élément des champs pages

Modifier un champ (commande Champ) la boite de dialogue permet de modifier différents paramètres : Nom, Emplacement, Type de sous totaux, masquer certains éléments, gérer les vides, ainsi que le format de nombre.

Si l’appel de la commande Champ sur la zone données, on n’accède qu’aux fonctions de totalisation. Mais par le bouton options, l’affichage des données peut être modifié.

Différent par rapport : affiche toutes les données sous forme de leur différence par rapport au champ de base et à l’élément de base qui est sélectionné.
% de : Affiche toute les données en % par rapport au champ de base et à l’élément de base qui est sélectionné.
Différence en % par rapport : même technique que Différence par rapport mais en %.
Résultat cumulé par : Affiche les données correspondant aux éléments successifs sous la forme d’un total cumulé. Vous devez sélectionner le champ des éléments que vous voulez voir apparaître dans le total cumulé.
% par ligne : Affiche le total de chaque ligne en indiquant le % qu’elle représente par rapport au total de la ligne.
% par colonne : Affiche le total de chaque ligne en indiquant le % qu’elle représente par rapport au total de la colonne.
% du total : Affiche le total de chaque ligne en indiquant le % qu’elle représente par rapport au total du TCD.
Index : Affiche les données en utilisant l’algorithme : (valeur dans la cellule*total général)/(total général de la ligne * total de la colonne).

Champ (ligne, colonne, page ) Modification avancée : Tri sur un champ, affiche le classement des n premières ou dernières valeurs sur un champ particulier.

Masquer Afficher :Le résultat sera différent selon que la sélection se trouve dans la zone de données, dans les éléments ou dans les champs du TCD.

Grouper Dissocier :

La commande Grouper n’agit que sur les éléments du TCD. Dissocier annule le groupe.

Si les éléments sont du texte, le groupe ne pourra être constitué que par une sélection préalable du nouvel ensemble (le nom groupe 1 pourra être modifié et les éléments constitutifs pourront être masqués ou supprimés)

Si les éléments sont des nombres : Positionné sur une cellule, l’appel à Grouper proposer la valeur minimale et maximale (modifiables) avec un pas de 10 (modifiable)

Si les éléments sont des dates : Le groupement possible va des secondes aux années, le pas est paramètrable et cumulable (mois et année).

Ajouter un champ calculé :

Il est possible de calculer de nouveaux résultats à partir des éléments du TCD (exemple : disponible avec recettes – dépenses), ou TTC =ht*tva2 ). Pour créer, un nouveau champ calculé , appeler la commande Formules – Champ calculé). On peut travailler au niveau du champ ou d’un élément d’un champ. La liste des formules récapitule les formules créées.

La mise en forme peut être préservée sous Excel 97 et les formats automatiques ou personnalisés peuvent être utilisés.

C – Graphique lié au tableau. La condition indispensable demeure la suppression des totalisations verticale et horizontale. Le graphique est alors bâti en ne sélectionnant au préalable qu’une cellule du TCD . Les étapes suivantes sont identiques à la démarche habituelle des graphiques. Néanmoins, si des lignes ou colonnes apparaissent lors d’une actualisation du tableau, le graphique les répercutera.