Changement de serveur

Bonjour,

Dorénavant, mes nouveaux billets seront publiés sur le site http://www.ballajack.com/ dans la rubrique Excel du Menu Tutos et Astuces.

Formats et formules étendus

Quand on active les formules et les formats étendus (Menu Outils / Options / Modification / Etendre les formules et formats de plage de données), Excel met automatiquement en forme les nouvelles données que vous saisissez à la fin d'une liste en accord avec les lignes précédentes, et copie automatiquement les formules qui se répètent sur chaque ligne.

Par exemple le tableau ci-dessous :

On a dans les deux premières colonnes de ce tableau deux séries de nombres. Dans la troisième colonne C, on fait une simple multiplication des nombres trouvés en colonnes A et B.

Si on saisit des nouveaux nombres à partir de la ligne 13, Excel formate automatiquement les nombres comme ceux trouvés sur les lignes précédentes et copie la formule qui se trouve en C.

Attention : Cela ne fonctionne que si l'option est activée (voir plus haut) et pour pouvoir être étendus, les formats et les formules doivent apparaître dans au moins trois des cinq lignes de la liste qui précède la nouvelle ligne.

Dupliquer une formule vers le bas par double-clic

Sur un tableau contenant une série de valeurs et de formules organisées en colonnes et en lignes, il est possible de gagner du temps en dupliquant rapidement les formules.

Il suffit de sélectionner l'angle en bas et à droite de la cellule à dupliquer afin d'obtenir un curseur en forme de croix. En double-cliquant sur ce curseur, la cellule est automatiquement copiée vers le bas, jusqu'à la première cellule vide de la colonne adjacente (ici la colonne C).

Si votre colonne ne contient pas de cellule vide, comme c'est le cas dans notre exemple, la cellule D2 sera automatiquement copiées jusqu'à la fin du tableau, ici la ligne 6.

C'est bien entendu la formule qui se trouve en D2 qui est copiée automatiquement vers le bas du tableau.

Si votre tableau contient une ligne vide, la copie automatique s'arrêtera au niveau de cette ligne vide.

Recherche avec Excel

A partir des dernières versions d’Excel, l’outil de recherche, accessible par la combinaison CTRL F (F pour find qui veut dire rechercher en Anglais) permet plusieurs options intéressantes :

Pour y accéder, cliquez sur le bouton Options>>.

Vous pourrez alors recherchez sur la feuille, mais aussi sur l’ensemble du classeur (Menu déroulant Dans :).
 

Vous pouvez aussi choisir de respecter la casse (majuscule ou minuscule).
 

Vous pouvez chercher la totalité du contenu de la cellule.

Une des options les plus intéressantes est le bouton Rechercher tout qui permet d’afficher l’ensemble des endroits où se trouvent ce qu’on recherche. On peut ainsi naviguer très rapidement dans le classeur à la recherche de la bonne formule. Il suffit pour cela de cliquer sur un des résultats de la recherche.

Masquer une feuille

Lorsque l'on élabore des classeurs Excel un peu complexes, on a souvent besoin de cacher un certain nombre d'informations aux personnes qui vont les manipuler. Le plus souvent, il s'agit de paramètres ou de tables de recherche utiles au fonctionnement de votre modèle.  Evidemment, le fait de modifier ces paramètres compromet tout le mécanisme du classeur.

On peut facilement masquer une ou plusieurs feuilles d'un classeur Excel. Pour cela, il suffit de sélectionner la ou les feuilles que l'on désire dissimuler aux yeux des utilisateurs, de cliquer sur le Menu Format, de cliquer sur Feuille et de choisir Masquer.

Dans l'exemple ci-dessus, on dispose d'un classeur Excel composé de trois feuilles : A, Pas touche et C. La feuille Pas touche comprend des paramètres qu'on souhaite cacher aux utilisateurs qui risqueraient de compromettre le fonctionnement du classeur si par mégarde ils venaient y apporter des modifications...

Dans ce cas de figure, on peut donc masquer cette feuille.

Pour afficher une ou des feuilles masquées, cliquer sur le Menu Format puis sur Feuille et choisir Afficher...

Choisir la ou les feuilles à afficher et cliquer sur OK.

La grande majorité des utilisateurs ignorent tout de la possibilité de masquer des feuilles mais ne sous estimez pas le temps libre dont ils disposent pour découvrir ce genre de fonctions...

Ni à gauche, ni à droite, au centre avec la fonction =STXT()

Il est facile avec les fonctions =GAUCHE() et =DROITE() d'extraire un certain nombre de caractères d'une chaîne à partir de la gauche ou de la droite.

Si l'on veut extraire une partie de cette chaîne (une sous-chaîne) à partir d'une position donnée et en prenant seulement en compte un certain nombre de caractères, ces deux fonctions ne nous seront d'aucune utilité car elles commencent leur extraction de caractères soit à l'extrême gauche, soit à l'extrême droite. Nous avons besoin d'une fonction plus "centriste".

Par exemple, on veut extraire du mot TOULOUSE, 4 caractères en commençant à partir du 2ème, soit OULO.

La fonction =STXT() permet de réaliser cette extraction.
 

Sa syntaxe exige trois arguments : =STXT(texte ; no_départ ; no_car) : une chaîne ; une position de départ ; un nombre de caractères à extraire. Les arguments sont séparés par un ; (point virgule).

 =STXT("TOULOUSE" ; 2 ; 4) renvoie par conséquent le mot OULO.

On commence l'extraction à partir de la position 2 (la lettre O) et on extrait les 4 caractères qui suivent, on obtient donc OULO comme résultat.

Ci-dessous un exemple complet démontrant cette fonction.

A bâbord ou à tribord - Les fonctions GAUCHE() et DROITE()

Rien de plus facile dans Excel de récupérer la partie gauche ou la partie droite d'une valeur ou d'un texte d'une cellule.

Excel dispose de deux fonctions pour faire cela.

=GAUCHE() permet de récupérer la partie gauche d'une chaîne.

La fonction demande deux arguments séparés par un ; (point virgule). Le texte (ou la valeur) sur lequel on extrait un certain nombre de caractères à partir de la gauche et le nombre de caractères à extraire.

Par exemple =GAUCHE("TOULOUSE";3) renvoie TOU. On a extrait les trois premiers caractères (donc ceux de gauche) du mot TOULOUSE et la fonction =GAUCHE() nous donne donc TOU comme résultat.

Remarquer le signe ; qui sépare les deux arguments.

= DROITE() permet de récupérer la partie droite d'une chaîne.

Elle fonctionne de la même façon que la fonction =GAUCHE().

Par exemple =DROITE("TOULOUSE";4) renvoie OUSE. On a extrait les quatre derniers caractères (donc ceux de droite) du mot TOULOUSE et la fonction =DROITE() nous donne donc OUSE comme résultat.

Ci-dessous un exemple complet sur l'utilisation de ces deux fonctions.

Navigation dans un classeur Excel

• Un classeur peut contenir un grand nombre de feuilles de calculs. Pour travailler sur l'une d'entre-elles, il suffit de cliquer sur son nom en bas d'écran. Cela est pratique quand le classeur ne contient pas trop de feuilles.

• Si le classeur contient un grand nombre de feuilles, il faut alors utiliser la barre de navigation qui se trouve en bas à gauche de l'écran pour faire défiler les différentes feuilles du classeur.

• Un clic-droit sur cette barre de navigation permet d'avoir une liste des quinze premières feuilles du classeur et de choisir une feuille directement sur cette liste.

• En cliquant sur Plus de feuilles..., on affiche alors une boîte de dialogue dans laquelle on peut sélectionner une feuille de son choix.

La calculatrice d'Excel

• Excel dispose d'une calculatrice intégrée très pratique pour obtenir rapidement le résultat d'un certain nombre d'opérations prédéfinies.

• Pour l'utiliser, sélectionner une plage de cellules et regarder en bas à droite dans la barre d'état d'Excel. La somme des cellules sélectionnées est affichée.

• Il est possible de modifier l'opération à effectuer en faisant un clic-droit sur cette zone.

• Dans les versions récentes d'Excel (supérieures à 2003), tous les résultats des opérations sont affichés en bas de l'écran.

Des dollars dans Excel

• Pour ajouter des signes de dollars ($) aux adresses afin de les rendre absolues, cliquez sur celles-ci dans la barre de formule et utilisez la touche F4.

• Le premier F4 rend la colonne et la ligne absolues, le deuxième rend seulement la ligne absolue, le troisième rend seulement la colonne absolue alors que le quatrième ramène l'adresse relative.

• Vous pouvez sélectionner l'ensemble de la formule ou plusieurs adresses en même temps dans la formule avant de cliquer sur la touche F4.

Saisir la date du jour dans Excel

• Pour saisir rapidement, la date du jour dans une cellule Excel, il suffit d'utiliser le raccourci clavier CTRL ;
• Pour saisir rapidement l'heure, utiliser le raccourci clavier CTRL :
• Pour que la date du jour apparaisse et s'actualise chaque fois que vous ouvrez un classeur, utilisez la formule =AUJOURDHUI().
• Pour l'heure utilisez =MAINTENANT().
• Pour avoir la date et l'heure utilisez aussi =MAINTENANT() et choisissez le format de cellule "date/heure".

Comptons les caractères - La fonction =NBCAR()

• Un programme très léger pour aujourd'hui, il s'agit d'utiliser la fonction =NBCAR().

• Très simplement, cette fonction permet de compter le nombre caractères qu'elle reçoit comme argument.

• Par exemple =NBCAR("toto") renvoie la valeur 4 (il y a quatre caractères dans le mot 'toto').

• On peut aussi demander de compter le nombre de caractères d'une valeur numérique.

• Par exemple =NBCAR(48) renvoie la valeur 2.

• Bien entendu, on peut aussi passer l'adresse d'une cellule comme argument.

• Si la cellule A1 contient le texte 'bonjour', la formule =NBCAR(A1) renvoie la valeur 7.

• Si la cellule A1 contient le texte 'bonjour' et la cellule A2 le texte 'salut', la formule =NBCAR(A1)+NBCAR(A2) renvoie la valeur 12 ce qui est la même chose que =NBCAR(A1&A2).

• Rien de bien compliqué dans tout cela me direz-vous. On verra que compter le nombre de caractères d'une cellule va nous être utile lorsque l'on élaborera des formules permettant de manipuler du texte comme par exemple, remplacer un caractère par un autre, compter le nombre de fois où un caractère apparaît dans une cellule, etc.

Consolidation

• Souvent, on se retrouve avec une série de données pour laquelle on souhaite effectuer une consolidation : une somme, une moyenne...

• Par exemple, une liste d'articles, une liste de codes postaux, une liste de noms... et des données associées.

• Évidemment, cette liste contient de nombreuses saisies multiples : un même article apparaît à plusieurs reprises, un même code postal est sur plusieurs lignes, etc.

• Prenons un exemple simplifié. Voici une liste d'articles que l'on a inventoriés. Elle comporte des codes articles et des quantités relevées. Les codes articles peuvent apparaître plusieurs fois (ils ont été inventoriés à divers endroits, ils sont ici coloriés d'une couleur différente). Le but va être de produire une somme des quantités pour chacun des codes article.

• On souhaite consolider les données sur la même feuille.

• Cliquer sur la cellule où vous voulez consolider les données.

• Cliquer sur le Menu Données et cliquer sur Consolider...

• En haut de la boîte de dialogue Consolider, choisir la fonction de consolidation, par exemple Somme. On peut choisir d'autres fonctions de consolidation : Moyenne, Nombre, Max, Min...

 

• Dans Référence, sélectionner la zone contenant les données à consolider, dans notre exemple A2:B11

• Dans la zone Etiquettes, cocher la case Colonne de gauche. Cela indique à Excel la colonne sur laquelle on va faire la consolidation, ici il s'agit de la colonne Code Art.

• A la finale, on obtient le résultat suivant :

• Les codes articles ont été regroupés et une somme a été calculée pour chacun d'entre-eux. Sur le même principe, on a dénombré le nombre de fois où chaque code article apparaît dans la liste. On utilise pour cela la fonction de consolidation Nombre.