Archives de catégorie : Excel

Excel – Créer une pyramide des âges hommes/femmes

Contexte

Vous êtes étudiant(e) en sciences criminelles et vous devez présenter des données de centaines d’individus sous forme de pyramide des âges. Vous disposez dans votre classeur Excel d’une colonne avec les âges, une colonne du nombre d’hommes et une colonne du nombre de femmes.

ages

Étant donné qu’Excel ne propose pas de pyramide dans ses modèles de graphes, il va falloir bricoler un peu.

Vous trouverez sur le Web différentes procédures pour créer des pyramides ; personnellement, j’ai une préférence pour celles qui n’ont pas d’impact sur les données du tableau. Le bricolage proposé ci-dessous ne porte donc que sur le graphe; vos données resteront intactes.

Solution

  1. Sélectionnez votre tableau > Onglet Accueil > Bouton Mettre sous forme de tableau > choisissez un modèle et validez
  2. Dans l’onglet Tableau, cochez l’option Ligne des totaux
  3. Dans la liste déroulante de la ligne des totaux, choisissez la formule MAX (pour les hommes et pour les femmes); cette information nous sera utile plus bas (au point 12) pour mettre en forme le graphique; imaginons que nous avons obtenu le chiffre maximum de 23
  4. max
  5. Cliquez sur votre tableau et allez dans l’onglet Insertion > groupe Graphiques > Barres > Barres groupées ; vous obtenez un graphe qui ne correspond pas du tout à ce que vous souhaitez…
  6. Augmentez la taille du cadre de votre graphe pour y voir plus clair
  7. Cliquez sur une des barres du graphe de la série des femmes (couleur orange) > allez dans l’onglet Mise en forme > bouton Volet Format > bouton Options des séries
  8. Activez l’option Axe secondaire
  9. Indiquez une Superposition de séries à 100 % et une largeur de l’intervalle à 0 %
  10. Tout à gauche du ruban de l’onglet Mise en forme, déroulez la liste des éléments constituant votre graphe et choisissez Axe Horizontal (Valeur); ce qui a pour effet de sélectionner l’axe au bas du graphe
  11. Dans le Volet Format, cliquez sur le bouton Options d’axe
  12. Dans la zone Limite Minimum tapez la valeur suivante : – 25 (cette valeur représente un chouia plus que le chiffre maximum de 23 que nous avions obtenu au point 4 grâce à la formule =MAX
  13. Dans la zone Limite Maximum tapez la valeur suivante : 25 ; nous avons donc saisi la même valeur sauf que une est en négatif et l’autre en positif
  14. Dans le même volet, cochez l’option Valeurs en ordre inverse
  15. Dans le même volet, cliquez sur la catégorie Nombre
  16. Dans la zone Code de format, tapez Standard;Standard et cliquez sur Ajouter (le format personnalisé ainsi créé permet de supprimer les valeurs en négatif affiché pour cet axe)
  17. Tout à gauche du ruban de l’onglet Mise en forme, déroulez la liste des éléments constituant votre graphe et choisissez Axe secondaire Horizontal (Valeur); ce qui a pour effet de sélectionner l’axe au haut du graphe
  18. Faites les mêmes choix que sous les points 12, 13, 15 et 16 (mais pas 14 !)
  19. Cliquez sur une des barres du graphe de la série des hommes (couleur bleue) > allez dans l’onglet Mise en forme > bouton Volet Format > bouton Options des séries
  20. Si nécessaire, activez l’option Axe principal
  21. Indiquez une Superposition de séries à 100 % et une largeur de l’intervalle à 0 % (ça commence à ressembler à quelque chose…)
  22. Tout à gauche du ruban de l’onglet Mise en forme, déroulez la liste des éléments constituant votre graphe et choisissez Axe Vertical (Catégorie)
  23. Dans le Volet Format, cliquez sur le bouton Options d’axe > Étiquettes > tapez une valeur pour l’intervalle, par ex. 2
  24. Profitez de cette sélection pour mettre les âges en gras
  25. Saisissez un titre pour votre graphique
  26. Si vous trouvez que celui-ci manque de punch, cliquez sur la série de données des hommes (bleue) et attribuez une couleur de contour orange (onglet Mise en forme > bouton Contour ; faites l’inverse pour la série de données des femmes (orange) en attribuant une couleur de contour bleue
  27. Sélectionnez votre graphique et allez dans l’onglet Création de graphique > bouton Déplacer le graphique > Nouvelle feuille

pyramide

Excel – Contourner la recherche de caractères génériques (point d’interrogation)

Contexte

Dans plusieurs cellules d’un immense tableau Excel, vous avez saisi des points d’interrogation ? (parce que vous ignoriez les valeurs au moment de la création du tableau et vous vous étiez dit que des ? feraient l’affaire…). Vous souhaitez maintenant pouvoir retrouver facilement les ? afin de compléter les données. Bien entendu, vous avez saisi un ? dans la zone de recherche. Excel considérant ce caractère comme étant générique, il trouve toutes les cellules dans lesquelles vous avez saisi des données 🙁 .

Solution

  • Tapez un tilde avant le ? dans la zone de recherche (~?) ; Excel ne trouvera que les cellules contenant réellement un ?

PS : le tilde s’obtient à l’aide des touches Alt + n sur Mac (Alt-Gr + ^ sur PC)

Excel – Faire une somme selon plusieurs critères (SOMME.SI.ENS)

Contexte

Vous disposez d’un tableau (par exemple un tableau des scores). Les participants sont des hommes et des femmes rattachés à deux équipes différentes. Vous aimeriez faire la somme des points seulement s’il s’agit des femmes de l’équipe1, puis une somme des points obtenus par les hommes de l’équipe1 et la même chose pour l’équipe2. Bref, il s’agit de faire une somme si deux critères sont remplis (genre et équipe) afin d’obtenir ceci :

SommeSiEns

Solution : utilisez la fonction SOMME.SI.ENS

Pour la formule devant faire la somme des points obtenus par les hommes du groupe CE :

  • Placez-vous dans la cellule devant contenir la formule (dans mon exemple E30)
  • Insérez la fonction =SOMME.SI.ENS()
  • Indiquez quelle est la plage devant être additionnée (D3:D26), tapez un point-virgule
  • Indiquez quelle est la plage contenant le 1er critère (B3:B26), tapez un point-virgule
  • Indiquez entre guillemets quel est le 1er critère, tapez un point-virgule
  • Indiquez quelle est la plage contenant le 2ème critère (C3:C26), tapez un point-virgule
  • Indiquez entre guillemets quel est le 2ème critère et validez la formule, laquelle doit ressembler à ceci :

formuleSommeSiEns

  • Faites la même chose pour les autres scores à additionner (ou copiez la formule et adaptez-la)

Constat suite à cet exercice : il y a trop peu de femmes travaillant au Ci… 🙁

Excel – Masquer des données et protéger une feuille

Contexte

Vous avez créé une feuille Excel dans laquelle certaines données sont confidentielles (la colonne des salaires, des évaluations ou autre). Vous avez pensé à la la commande Masquer disponible dans le menu contextuel de la colonne mais cette méthode manque d’efficacité. En effet, à l’affichage on devine rapidement qu’une colonne a été masquée. N’importe quel utilisateur parvient, en deux clics de souris, à afficher la colonne masquée. De plus, le masquage de colonne perturbe la mise en page et l’impression de votre tableau devenu de ce fait plus petit.

Solution : utilisez le formatage masqué puis protégez la feuille

  • Sélectionnez la plage de données à masquer
  • Allez dans le menu Mise en forme > Cellules > onglet Nombre
  • Cliquez sur la catégorie Personnalisée et, dans la zone type, tapez 3 points-virgules comme ceci ;;; et validez
  • Les données des cellules sont devenues invisibles; toutefois si un utilisateur clique sur une des cellules dont le contenu est masqué, il peut malheureusement prendre connaissance de son contenu dans la barre d’adresse…

invisibleechec

… rendez donc impossible la sélection de la plage de cellules dont le contenu est masqué, pour cela :

  • Sélectionnez toute la feuille > allez dans l’onglet Accueil > bouton Mise en forme > désactivez l’option Verrouiller les cellules (active par défaut)
  • Sélectionnez ensuite la plage de cellules dont vous souhaitez empêcher la sélection > allez dans l’onglet Accueil > bouton Mise en forme > activez l’option Verrouiller les cellules
  • Puis allez dans l’onglet Accueil > bouton Mise en forme > activez l’option Protéger la feuille et faites les choix suivants :

protection

  • Saisissez bien entendu un mot de passe empêchant d’ôter la protection de la feuille
  • Si, par la suite, vous souhaitez faire réapparaître les données, supprimez simplement les ;;; du formatage personnalisé

Excel – Extraire des valeurs uniques d’une colonne

Contexte

Vous disposez d’une colonne contenant par exemple les âges des collaborateurs (les âges peuvent avoir été saisis manuellement ou être le fruit d’un calcul). Vous aimeriez pouvoir extraire les âges de ces personnes mais sous forme de valeurs uniques (certains d’entre eux ont bien entendu le même âge). Vous pensez utiliser une formule de calcul pour faire cela mais vous ne savez pas laquelle ferait l’affaire.

Solution : Laissez tomber l’idée de la formule de calcul et faites un filtrage avancé…

  • Positionnez-vous sur la cellule contenant l’en-tête de la colonne Âge
  • Allez dans l’onglet Données > cliquez sur Options avancées de filtre
  • Indiquez que vous souhaitez extraire et copier les données à un autre emplacement
  • Vérifiez que la plage de cellules à filtrer est correcte
  • Choisissez la cellule dans laquelle vous souhaitez effectuer la copie
  • Cochez l’option Extraction sans doublon et validez
  • Terminez éventuellement en faisant un tri Du plus petit au plus grand

filtre avance

 

Enregistrer

Enregistrer

Excel – Utiliser NB.SI dans une mise en forme conditionnelle

Contexte

Vous disposez d’un fichier Excel faisant l’inventaire d’articles. Les libellés de ces articles contiennent des noms (de marques, de fournisseurs…) par exemple « Citron vert » ou « Sucre brun » ou « Rhum ambré ». Vous aimeriez attribuer une mise en forme particulière à une cellule en fonction du contenu des libellés afin de repérer facilement les données, par exemple « Si le libellé en A3 contient le mot « Rhum ambré » alors la cellule B3 des quantités doit apparaître avec une trame brune sinon le texte doit s’écrire de manière standard ». Vous avez essayé la fonction « SI » mais ça ne marche pas…

Solution

  • Cliquez sur la cellule pour laquelle vous souhaitez une mise en forme conditionnelle (dans mon exemple B3)
  • Allez dans l’onglet Accueil > bouton Mise en forme conditionnelle > Nouvelle règle
  • Choisissez un style Classique > Utiliser une formule pour déterminer…
  • Tapez la formule suivante :
    NB.SI($A3;“*sucre brun*“) autrement dit « si la cellule A3 contient (et non pas « est égal ») l’expression « sucre brun » la cellule B3 doit être jaune (remarquez dans la formule que la colonne A est absolue et que la ligne 3 est relative car nous aurons besoin que le numéro de la ligne s’adapte lors de la recopie vers le bas) :

nb.si

  • Faites de même pour les autres conditions, afin d’obtenir ceci :

nb.si2

  • Validez cette boîte de dialogue et utilisez la poignée de recopie de la cellule B3 pour recopier cette mise en forme conditionnelle vers le bas :

nb.si3

Excel – Créer un planning avec mise en forme conditionnelle

Contexte

Vous souhaitez créer un tableau mensuel avec les absences (congé, cours, …) des membres de votre équipe. Chaque début de mois, vous aimeriez pouvoir rapidement créer un nouveau mois et souhaiteriez que les week-ends apparaissent automatiquement en couleur :

planning

Remarquez dans mon exemple que la cellule B3 contient une date saisie 01.04.2016 mais affiche un format personnalisé de type Ven 1 (jjjj j).

Solution : utiliser la mise en forme conditionnelle

  • Dans l’exemple ci-dessus, placez-vous sur la cellule B4 (sur la 1ère case en face de la première personne)
  • Cliquez sur le bouton Mise en forme conditionnelle de l’onglet Accueil > Nouvelle règle
  • Choisissez le style Classique > option Utiliser une formule pour déterminer à quelles cellules la mise en forme sera appliquée
  • Tapez la formule ci-dessous :

=OU(JOURSEM(B$3;2)=6;JOURSEM(B$3;2)=7)

Qu’il faut comprendre ainsi :

JOURSEM = formule qui renvoie un chiffre entre 1 et 7 désignant le jour de la semaine

B$3 = adresse de la cellule qui contient la 1ère date (Ven 1). La colonne n’est pas rendue absolue puisque nous souhaitons pouvoir recopier cette formule sur les autres jours du mois; lors de la recopie sur la droite les adresses des colonnes doivent donc s’adapter. Par contre, le numéro de la ligne est rendu absolu puisque les dates du mois sont situées sur la ligne 3 (et jamais sur la 4, la 5…); les adresses des lignes ne doivent donc pas s’adapter

;2 = cet argument permet d’indiquer à Excel que la semaine chez nous commence un lundi (les américains considèrent que le premier jour de la semaine est dimanche). 1 = dimanche, 2 = lundi…

=6 et =7 : indiquent que le 6ème jour de la semaine devront être mis en forme, le 6ème étant le samedi, le 7ème étant le dimanche… chez nous

OU en début de formule indique que les 6ème et 7ème jours de la semaine doivent être mis en forme de manière conditionnelle (en langage courant nous disons ET mais pour Excel il s’agit bien d’un OU)

  • Choisissez ensuite la mise en forme souhaitée et validez
  • Recopiez ensuite la cellule B4 avec la poignée de recopie en glissant sur la droite jusqu’au dernier jour du mois
  • Recopiez ensuite cette plage de cellules sur le bas jusqu’au dernier collaborateur

Beaucoup de travail pour pas grand chose me direz-vous ? Certes, mais si maintenant vous souhaitez créer en 5 secondes chrono le planning du mois prochain :

  • En B3, saisissez la date du 1er jour du mois (01.05.2016) et recopiez cette information jusqu’au dernier jour du mois et Tadaaaannnn ! les week-end de votre nouveau planning se sont mis à jour.

Excel – Importer des données provenant d’une page Web

Contexte

Vous utilisez Excel sur Mac (ça ajoute du piment à cet article…) et vous souhaitez importer des données provenant d’une page Web dans une feuille Excel. Vous aimeriez que ces données se mettent à jour régulièrement dans votre feuille Excel. Vous avez fait une tentative sur Excel 2011 qui s’est soldée par un échec. Vous êtes étonné car l’onglet Données contient bel et bien un bouton Actualiser permettant d’actualiser les données provenant d’une source externe.

Solution

  • Faites-vous d’abord un ami ayant un PC et prêt à vous rendre service 😉
  • Une fois cette première et délicate étape franchie, ouvrez Excel 2013 sur le PC
  • Allez dans l’onglet Données > bouton Web
  • Dans la boîte de dialogue Nouvelle requête sur le Web, tapez l’URL dont vous souhaitez importer les données
  • Cochez la petite case verte en face de la page Web (ou en face du cadre de la page)

actu unil

  • Validez en cliquant sur le bouton Importer
  • Choisissez la cellule à partir de laquelle vous souhaitez faire l’importation (A1 dans mon exemple)
  • Sauvegardez le classeur et ouvrez-le sur votre Mac (Excel 2011)
  • Cliquez sur le bouton Activer le contenu dans le bandeau jaune de sécurité
  • Allez dans l’onglet Données et cliquez sur le bouton Actualiser

Il existe une autre possibilité sur PC qui consiste à atteindre une page Web à l’aide d’Internet Explorer, de cliquer avec le bouton droit de la souris sur la page et de choisir Exporter vers Microsoft Excel. Excel s’ouvre et la boîte de dialogue Nouvelle requête sur le Web permet de choisir la zone de la page Web à importer.

Excel – Créer une liste déroulante – Méthode N° 2

Contexte

Vous avez créé un document Excel qui va être utilisé par plusieurs utilisateurs. Vous souhaitez insérer, dans une cellule, une liste déroulante contenant des valeurs à choix. Les valeurs en question sont situées dans une plage d’un autre onglet de votre classeur.

Solution

  • Allez dans l’onglet contenant les valeurs et sélectionnez la plage
  • Donnez un nom à cette plage de cellules par ex. type (en cliquant dans la zone située tout à gauche de la barre de formule)
  • Allez ensuite dans l’onglet où se trouve la cellule dans laquelle vous souhaitez insérez la liste déroulante et cliquez sur la cellule en question
  • Allez dans l’onglet Données > Bouton Validation des données
  • Dans l’onglet Paramètres, faites les choix suivants; veillez à taper le signe égal avant de saisir le nom de la plage

liste

Les utilisateurs qui cliqueront sur la cellule se verront proposer la liste que vous avez définie. Bien entendu, vous pouvez recopier vers le bas les paramètres de la cellule à l’aide de la poignée de recopie.

Remarquez que la boîte de dialogue Validation des données permet également d’ajouter un message de saisie ainsi qu’un message d’erreur personnalisés.

Excel – Comparer le contenu de deux plages de cellules

Contexte

Vous devez comparer, dans une feuille Excel, les contenus de deux colonnes (A et B) dans lesquelles ont été saisis par exemple des noms de personnes. Vous devez trouver si des noms ont été saisis dans les deux colonnes, sachant qu’ils n’ont pas été saisis dans le même ordre (par ex. mon nom a été saisi en A4 dans la colonne A et en B2 dans la colonne B). Si Excel trouve une correspondance de noms entre la colonne A et la colonne B, vous aimeriez qu’il vous indique, dans la colonne C, le nom de la personne trouvée à double.

EquivExcel

Solution

  • Entrez la formule suivante dans une cellule vide (dans mon exemple en C1)
    =SI(ESTERREUR(EQUIV(A1;$B$1:$B$42;0)); ″pas trouvé″; A1)
  • A l’aide de la poignée de recopie, recopiez cette formule jusqu’en C42.

Excel va vérifier, dans la plage allant de B1 à B42, si il trouve le même contenu que dans la cellule A1. S’il y a équivalence entre le contenu de la cellule A1 et la plage de cellules B1:B42, Excel va indiquer le contenu (autrement dit le prénom de la personne) qu’il a trouvé en A1. S’il n’a pas trouvé d’équivalence, Excel écrit le texte « pas trouvé ».
Le 0 figurant dans la formule a son importance car il indique à la fonction EQUIV que la recherche doit se faire n’importe où dans la plage allant de B1:B42.
Les dollars $ ont également leur importance car vous allez recopier cette formule jusqu’en C42. Si vous omettez les $, les adresses seront relatives (elles vont donc s’adapter lors de la recopie) alors qu’il est absolument nécessaire que les adresses de la plage soient absolues (car les adresses B1 à B42 doivent rester fixes).

Remarquez qu’Excel ne tient pas compte de la casse dans la recherche d’équivalent.

Remarquez aussi que cette formule fonctionnerait dans le cas où le texte recherché est lui-même le fruit d’une concaténation de prénom et de nom effectuée à l’aide de la formule suivante : =CONCATENER(F1;″ ″;G1).