Hoang-Quang Dao

Donner une énergie plus positive : vouloir proposer, partager, créer du lien… mon blog.

Archive for the ‘Excel’ Category

Le tableur Microsoft Excel

Créer une liste déroulante dans Excel

Posted by hoangquangdao sur 4 janvier 2011

Introduction

Dans Access, on peut limiter les entrées utilisateur en l’obligeant de choisir une valeur dans une liste déroulante. On peut créer ce même type de liste déroulante dans une feuille Excel, mais le processus n’est pas intuitif. Cette fonctionnalité est « cachée » dans l’option de validation de données. Une fois qu’on sait que cette fonction existe, il est facile de la mettre en œuvre. On a seulement besoin de 2 choses : une liste et une cellule d’entrée de données. Cet article montre l’élaboration d’une liste déroulante simple dans une feuille Excel.

Méthode

Les utilisateurs cliquent sur la flèche vers le bas pour afficher une liste d’éléments de valeurs sur la colonne A. Si un utilisateur essaie d’entrer quelque chose qui n’est pas dans la liste, Excel rejette l’entrée. C’est le principe de la liste déroulante.

Pour ajouter cette liste déroulante dans la feuille Excel, voici comment procéder :

1. Créer la liste dans les cellules A1:A5, avec une entête ou pas (De même, vous pouvez entrer les éléments sur une seule rangée, comme A1:D4)

2. Sélectionnez la cellule E2. On peut choisir n’importe quelle position pour la liste déroulante et même plusieurs cellules à la fois)

3. Choisir Validation (Excel 2003) ou Validation des données (Excel 2010) dans le menu Données du ruban du groupe Outils de données dans Excel 2010.

LISTE01

4. Dans le menu de construction de la cellule validation de données, choisir le critère de validation par liste et sélectionner la zone A1:A5 de la liste déroulante comme source de la liste de validation de données. Sinon, on peut directement entrer la référence (=$A1:$A5). Il y également d’autres options de contrainte de saisie, de message d’erreur etc.

LISTE02

5. Valider sur OK.

LISTE03

On peut ajouter la liste déroulante dans plusieurs cellules. Il suffit de sélectionner la plage de cellules d’entrée de données (étape 2) au lieu d’une seule cellule. Il fonctionne même pour les cellules non contiguës en maintenant la touche Majuscule enfoncée tout en cliquant sur les cases appropriées.

Il est intéressant de noter que la flèche du menu déroulant est visible uniquement lorsque la cellule est active.

Publicités

Posted in Excel, Office | Tagué: , | Leave a Comment »

Créer des liens hypertexte dans Excel

Posted by hoangquangdao sur 23 décembre 2010

Introduction

Il y a plusieurs façons d’ajouter un lien hypertexte dans une feuille Excel. Cet article vous présente la manière traditionnelle et un raccourci facile.

Méthode de l’option hyperlien

La méthode traditionnelle requiert une plage nommée. Il faut tout d’abord attribuer un nom à la région de la feuille vers laquelle le lien redirigera Ensuite, il suffit de créer un lien hypertexte vers ce nom.

Dans ce premier exemple, nous allons insérer un lien hypertexte dans une feuille nommée TDM pour Table des matières. Le lien hypertexte redirigera vers une feuille nommée Données qui est une liste de contacts. Il suffit donc de créer deux feuilles.

LIEN01

Pour commencer, il faut ajouter une plage nommée (dans une cellule) à la feuille de Données comme suit :

1. Sélectionner la cellule B1 sur la feuille de Données. Cette cellule contient un titre pour la feuille.

2. Sélectionner la zone Nom (à gauche de la barre de formule).

3. Saisir le nom à attribuer à la cellule et valider. Dans l’exemple, attribuer le nom data à la cellule B1 sur la feuille de Données.

LIEN02

La création du nom était assez facile. Maintenant, pour créer un lien hypertexte vers des Données (la cellule nommée) il faut procéder ce qui suit :

1. Sélectionner la cellule où on souhaite insérer le lien hypertexte. Dans ce cas, c’est la cellule B3 sur la feuille de TDM (ci-dessus).

2. Dans Excel 2003, choisir un lien hypertexte dans le menu Insertion. Ou, un clic droit sur la cellule et choisir un lien hypertexte. Dans Excel 2007 et 2010, cliquez sur un lien hypertexte dans le groupe Liens dans le menu Insertion.

LIEN03

3. Cliquer sur la Emplacement dans ce document dans la zone à gauche, lier à.

4. Sélectionner la plage nommée dans la zone Ou sélectionner un emplacement dans ce document et valider.

LIEN04

Le résultat obtenu est un lien hypertexte qui redirige vers la cellule B1 sur la feuille de Données. Le lien hypertexte utilise le texte dans la cellule nommée.

Méthode du glisser-déposer

Il y a une autre façon qui pourra sembler plus facile. C’est la technique du glisser-déposer, il faut juste veiller à ce que les onglets des feuilles soient tous visibles pour que cette technique fonctionne. En outre, elle ne fonctionnera pas avec un nouveau fichier non enregistré préalablement alors il ne faut pas oublier d’enregistrer une première fois le fichier. (Cette technique ne nécessite pas une plage nommée.)

Pour créer le même lien hypertexte en utilisant le glisser-déposer, il faut procéder comme suit :

1. Sélectionner la cellule où on souhaite créer un lien (B1 sur la feuille de produits).

2. Passer la souris sur l’une des frontières de la cellule sélectionnée jusqu’à ce que le pointeur de la Croix à quatre flèches apparaisse.

3. Faire un clic droit sur la frontière (le clic gauche standard ne fonctionnera pas) et le pointeur de souris régulière remplacer le verso flèche à quatre.

4. Maintenir enfoncée la touche [Alt] en même temps que de glisser la souris à l’onglet approprié, dans le cas de l’exmple c’est la feuille TDM (table des matières).

5. Relâcher la touche [Alt] et sélectionnez la cellule où on souhaite placer le lien et relâcher la souris (dans la case B5).

6. Sélectionner Créer un lien hypertexte dans le menu contextuel qui en résulte.

LIEN05

Cela peut prendre nécessiter plusieurs tentatives avant de maîtriser la technique du glisser déposer. Fondamentalement, il ne s’agit que d’un glisser d’une cellule d’une feuille à une autre.

Posted in Excel, Office | Tagué: , | Leave a Comment »

Une formule de numérotation de lignes dynamique pour Excel

Posted by hoangquangdao sur 1 décembre 2010

Introduction

Un besoin rarement énoncé dans Excel telle que la numérotation des lignes Excel peut engendrer un petit problème. Bien qu’il ne s’agisse pas d’une tâche Excel primordiale, je présente dans cet article une réponse à ce problème en présentant par la même occasion la fonction NBVAL, la fonction SI et la concaténation de texte dans une formule.

La fonction SI

La fonction SI vérifie si la condition est respectée et renvoie une valeur si le résultat de cette condition spécifiée est VRAI, et une autre valeur si le résultat est FAUX. On utilisé jusqu’à 7 fonctions SI imbriquées. Elle prend 3 arguments :

  • Test_logique qui est n’importe quelle valeur ou expression dont le résultat peut-être VRAI ou FAUX seulement
  • Valeur_si_vrai représente la valeur renvoyée si Test_logique est VRAI. Si cette valeur est omise, la valeur « VRAI » est alors renvoyée
  • Valeur_si_faux représente la valeur renvoyée si Test_logique est FAUX. Si cette valeur est omise, la valeur « FAUX » est alors renvoyée

Ainsi un début de solution est donc de vérifier si la 1ère case de la ligne à compter n’est pas vide, alors on incrémente le comptage de la ligne en cours :

=SI(cell<>""; NBVAL(cell:cell);)

La fonction NBVAL

La fonction NBVAL détermine le nombre de cellules d’une plage qui ne sont pas vides et elle a comme argument(s), la ou les plages de cellules à comptabiliser.

NBVAL01

Formule simple

Comme vous pouvez le voir dans la figure ci-dessus, cette formule fonctionne bien. En insérant ou supprimant l’enregistrement du 5ème contact de la liste de contacts, les numéros des lignes est mise à jour en conséquence. Il y a un problème à la ligne 10; si la ligne est vide, la formule renvoie FAUX car il n’y a pas de précision en cas d’un Test_logique FAUX.

La valeur FAUX est facile à éliminer par la modification de la fonction SI :

=SI(cell<>""; NBVAL(cell:cell);"")
NBVAL02

Formule avec une valeur pour le cas FAUX

En ajoutant toute la ponctuation nécessaire à la SI () en concaténant la ponctuation au résultat par le symbole de concaténation Excel « & ». Excel affiche la ponctuation pour des lignes vides.

=SI(cell<>""; NBVAL(cell:cell);"") & "."
NBVAL03

Formule en ajoutant une concaténation

Enfin au lieu d’ajouter une simple ponctuation à la fonction SI () n ajoute cette ponctuation à la valeur du résultat VRAI comme suit:

=SI(cell<>""; NBVAL(cell:cell)& "." ;"")
NBVAL04

Formule finale

Bien que cette formule fonctionne, je ne suis pas convaincu que c’est la solution la plus efficace.

Posted in Excel, Microsoft | Tagué: , | Leave a Comment »

Additionner des valeurs dans une liste filtrée Excel

Posted by hoangquangdao sur 24 novembre 2010

Introduction

Le filtre est une fonctionnalité simple et puissante à utiliser. Si l’utilisation des filtres permet d’obtenir rapidement une gamme limitée des données, en obtenir un résultat d’enregistrement filtré est une autre affaire. Par exemple le calcul de la SOMME() d’une gamme de données filtrées. On peut avoir quelques surprises.

Voici un filtre d’un tableau de données qui représente une liste de contacts avec une colonne « nombre rdv » qui comptabilise le nombre de rendez-vous pris avec le contact. Les exemples montreront les différents résultats des sommes sur une liste filtrée. Le fonctionnement des filtres à proprement parler n’est pas abordé dans cet article, mais le un voici l’article pour en savoir plus sur ce sujet, lire « Utiliser les opérateurs ET et OU avec un filtre avancé Excel »

L’addition de valeurs filtrées

L’exemple ci-dessus montre ce qui se passe lorsque qu’on additionne les valeurs filtrées. On peut remarquer l’absence de lignes par la série de numéros de ligne tronquée causé par le filtre sélectionnant les lignes répondant au critère (ici le « code fonction 1 » de type « DG »).

SUMFILTER01

Somme-filtre-1

On constate facilement que le résultat n’est pas correct, la valeur est trop élevée, mais pourquoi? La fonction SOMME() est l’évaluation de toutes les valeurs de la gamme H9:H23, et pas seulement les valeurs filtrées, mais aussi les lignes cachées. La fonction SOMME() ne sait pas exclure des valeurs filtrées dans la gamme de référence.

La solution est beaucoup plus facile qu’on ne le pense ! Il suffit de cliquer ∑ et Excel entre automatiquement la fonction SOUS.TOTAL(), au lieu d’une fonction SOMME(). Cette fonction fait référence la liste entière, H9:H23, mais il évalue seulement les valeurs filtrées.

SUMFILTER02

Somme-filtre-2

A propos de la fonction SOUS.TOTAL()

Bien que la fonction SOUS.TOTAL() somme les références de la liste entière des valeurs dans la colonne H, il n’évalue pas seulement ceux de la liste filtrée. Le premier argument de la fonction, dans notre exemple la valeur 9, indique à Excel de faire la somme des valeurs de référence.

SUMFILTER03

Somme-filtre-3

Le tableau suivant répertorie les valeurs acceptables de cet argument :

Calcule les valeurs cachées Ignore les valeurs cachées Fonction
1 101 MOYENNE()
2 102 NB()
3 103 NBVAL()
4 104 MAX()
5 105 MIN()
6 106 PRODUIT()
7 107 ECARTYPE.STANDARD()
8 108 ECARTYPE.PEARSON()
9 109 SOMME()
10 110 VAR.S()
11 111 VAR.P.N()

On peut observer une bizarrerie pour la sous fonction SOMME de SOUS.TOTAL qui n’additionne que les valeurs filtrées que l’argument soit 9 ou 109. Il faut donc juste savoir que cela fonctionne comme ça pour la SOMME.

Posted in Excel, Office | Tagué: , , | Leave a Comment »

Utiliser les opérateurs ET et OU avec un filtre avancé Excel

Posted by hoangquangdao sur 23 novembre 2010

Définition

Un filtrage de données est un affichage d’un sous-ensemble de données. C’est une tâche de routine pour les nombreux utilisateurs d’Excel.
Un filtre automatique permet de limiter les données affichées, mais il est justement limité car il dépend de données réelles. Le filtrage avancé d’Excel est une fonction qui nécessite un peu de configuration, mais est plus souple et plus puissant qu’un simple filtre automatique. Non seulement on peut utiliser une expression pour apparier des enregistrements, mais aussi en combiner en utilisant les opérateurs ET et OU, c’est ce que cet article va vous montrer.

La fonctionnalité de filtre avancé est composée de trois éléments :

  • Une plage de données où appliquer le filtre
  • Une plage de critères, où on spécifier les critères comme une formule
  • Enfin l’extraction de la gamme de données qui satisfait aux critères

Le filtre simple automatique

Avant d’entrer dans un exemple plus complexe, voici un exemple simple de filtre automatique en utilisant un ensemble de données d’un fichier de contacts client.

FILTRE01

Liste de contacts

Pour appliquer un filtre automatique

1. Sélectionnez les en-têtes de la première à la dernière colonne des données à filtre, ici A1:H1

FILTRE02

Donnees-Filtrer

2. Choisir alors Filtre automatique dans le menu Données dans Excel 2003, dans Excel 2007 et 2010, cliquez sur le menu Données du bandeau, puis cliquez sur Filtrer dans le groupe Trier et filtrer.

FILTRE03

Filtre simple critère

FILTRE04

Filtre simple résultat

Excel affiche une flèche de liste déroulante pour chaque colonne dans la sélection. En utilisant cette fonctionnalité, on peut effectuer des tâches simples de filtrage, tels que les contacts qui n’ont pas de « code fonction 2 ». C’est rapide et facile, mais parfois insuffisant. (Pour supprimer un filtre, il suffit de choisir « tous » de la même liste ou dans Excel 2010, « effacer la liste des filtres »)

Le filtre avancé avec l’expression ET

Maintenant un exemple de filtre avancé avec une expression, en affichant tous les contacts ayant un « code fonction 1 » de type « DG » ET un « code fonction 2 » de type « Développement ». Cela nécessite un filtrage à deux exigences – deux critères – et qui doivent être satisfaite toutes les deux. Un filtre automatique peut encore se faire, mais essayons donc un filtre avancé.

La plage de critères, dans ce cas, ne nécessite que deux colonnes: le « code fonction 1 » et le « code fonction 2 ». Pour une question d’ergonomie et de lecture, il est recommandé de recopier les entêtes et d’ajouter autant de ligne que de critères pour former une zone de plage de critères au-dessus des données réelles. Ce placement est efficace et facilement accessible.

Ensuite, il faut indiquer critères de filtrage. Excel intègre l’interprétation des formules également. Dans ce cas, les deux expressions sont de simples comparaisons. Les deux critères sont dans la même ligne (ligne 2). En plaçant les deux expressions dans la même rangée, Excel sait va appliquer l’opérateur implicite de combinaison ET sur les expressions.

FILTRE06

Filtre avancé

La gamme d’extraction est facultative. Pour copier les données vers un autre emplacement, il faut spécifier les colonnes que l’on veut extraire. Tout ce qui reste à faire maintenant est d’appliquer le filtre comme suit:

1. Cliquez sur n’importe quelle cellule dans la plage de données.

2. Cliquez sur le menu Données, puis cliquez sur Filtre | Filtre avancé. Dans Excel 2007 et 2010, cliquez sur l’onglet Données, puis cliquez sur Filtre dans le groupe Trier et filtrer.

3. Garder le réglage par défaut, filtrer la liste en place.

4. Excel sélectionne automatiquement la plage de données à filtrer.

5. Il n’y a qu’à spécifiez la plage de critères, A1:H2. Vous avez seulement besoin d’identifier les entêtes de colonne et la rangée des critères ou des lignes.

FILTRE07

Filtre avancé ET plage criètres

6. Cliquer sur OK.

FILTRE08

Filtre avancé ET résultat

Trois contacts sont de « code fonction 1 » de type « DG » ET de « code fonction 2 » de type « Développement. Pour retirer le filtre, cliquez sur le menu Données, puis cliquez sur Filtre | Afficher tous les, dans Excel 2007 et 2010 cliquez sur le menu Données, puis cliquez sur Filtre | Effacer.

Le filtre avancé avec l’expression OU

Pour le filtre avancé avec une expression OU, il faut placer les critères dans des rangées séparées. Ainsi afficher tous les contacts ayant un « code fonction 1 » de type « DG » OU un « code fonction 2 » de type « Développement ».

Après avoir réglé la plage de critères en déplaçant l’une des expressions dans une autre rangée, on applique de nouveau le filtre comme suit:

1. Cliquez sur n’importe quelle cellule dans la plage de données.

2. Cliquez sur le menu Données, puis cliquez sur Filtre | Filtre avancé. Dans Excel 2007 et 2010, cliquez sur l’onglet Données, puis cliquez sur Filtre dans le groupe Trier et filtrer.

3. Garder le réglage par défaut, filtrer la liste en place.

4. Excel sélectionne automatiquement la plage de données à filtrer.

5. Il n’y a qu’à spécifiez la plage de critères, A1:H3. Vous avez seulement besoin d’identifier les entêtes de colonne et la rangée des critères c’est-à-dire les 2 lignes en plus.

FILTRE09

Filtre avancé OU plage critères

6. Cliquer sur OK.

FILTRE10

Filre avancé OU résultat

De plus nombreux contacts répondent désormais à l’un ou l’autre des critères.

On peut utiliser un filtre avancé en une seule formule, mais en utilisant les opérateurs implicite ET et OU, cela ouvre la porte à des filtres très complexes, mais puissants. Il suffit de faire attention à ce queles critères soient placer dans au bon endroit.

Posted in Excel, Office | Tagué: , | Leave a Comment »

La fonction RANG dans Excel

Posted by hoangquangdao sur 10 novembre 2010

La fonction RANG dans Excel retourne le classement d’une valeur par rapport à une liste de valeurs. Il s’agit d’une position relative de la valeur par rapport aux autres dans la liste. On pourrait tout simplement trier la liste pour obtenir le classement, mais cela n’est pas toujours pratique à faire, et de toute façon trier ne donne pas le rang, mais un ordre même si c’est évident de constater la plus grande ou la plus petite valeur.

Voici un exemple d’utilisation de la fonction RANG dans un fichier Excel d’un questionnaire de satisfaction client comportant quelques questions adressées aux clients qui donnent donc une note sur une échelle à 5 valeurs (+3, +1, 0, -1, -3). Dans notre exemple, il s’agit d’une consolidation de 3 questionnaires remplis.

Le tableau des totaux intègre une fonction SOMME.SI qui donne le total des valeurs des réponses de la colonne G (Score) de chaque question de la colonne A (Customer Survey)

La fonction RANG comporte 3 arguments : RANG (nombre, référence, [ordre])

  • nombre : la valeur qui doit être classée
  • référence : la liste des valeurs de comparaison
  • ordre : 0 ou 1 qui spécifie respectivement l’ordre ascendant ou descendant du classement

Dans notre exemple il s’agit de : RANG($J2;$J$2:$J$13;0)

Remarque : le nombre est « relatif » alors que la référence est absolue.

Le cas des égalités : on parle de rang d’égalité lorsqu’il y a au moins 2 rangs de même valeur. Le comportement de la fonction RANG est de sauter autant de fois les valeurs des rangs suivants qu’il y aura d’égalité de rang.

Dans notre exemple, il y a une égalité au 5ème rang, et le rang suivant est donc le 7ème. Pour éliminer les cas d’égalité, il faut alors élargir le calcul du rang à partir d’autres paramètres de classification.

Posted in Excel, Office | Tagué: , | Leave a Comment »

Les guides interactifs Office 2003 – Office 2007

Posted by hoangquangdao sur 8 novembre 2010

Guide Interactif de référence des commandes Office 2003 – Office 2007

Excel 2003 et Excel 2007

Word 2003 et Word 2007

Outlook 2003 et Outlook 2007

PowerPoint 2003 et PowerPoint 2007

Access 2003 et Access 2007

Posted in Excel, Microsoft, Office, Outlook, Word | Tagué: , , | Leave a Comment »