All posts in excel-pratique

Dans la formation vidéo sur Office 2016, vous avez appris à nommer des plages de cellules. Cela peut être très utile pour accéder rapidement aux plages nommées ou pour faciliter l’écriture de formules. Ici, nous allons aller un peu plus loin en définissant plusieurs plages nommées en une seule opération.

Nous allons partir de cette feuille de calcul :

Comment définiriez-vous la plage Total ?

Certainement en sélectionnant les cellules A2 à A8 et en définissant le nom de la plage sélectionnée dans la barre de formule.

Supposons maintenant que vous vouliez nommer les plages Quantité et Prix unitaire. Pour cela, vous devriez recommencer la même technique. Il y a beaucoup plus simple !

Sélectionnez la plage A2 à C8. Basculez sur l’onglet Formules du ruban puis cliquez sur l’icône Depuis sélection dans le groupe Noms définis. La boîte de dialogue Créer des noms à partir de la sélection s’affiche. Indiquez où se trouvent les cellules dont le contenu sera utilisé pour nommer les plages. Ici, sur la ligne du haut :

Validez en cliquant sur OK. Vous venez de créer trois plages nommées. Pour vous assurer que tout a bien fonctionné, cliquez sur l’icône Gestionnaire de noms dans le groupe Noms définis, toujours sous l’onglet Formules du ruban. Les trois plages nommées ont bien été créées :

Dans Excel, il est fréquent de créer des feuilles de vente mensuelles comme celle-ci :

Cet article va vous montrer comment obtenir un récapitulatif des ventes trimestrielles en quelques clics souris. C’est une technique complémentaire à la consolidation, qui a été abordée dans la formation à Excel 2016.

Maintenez la touche Contrôle du clavier enfoncée et glissez-déposez l’onglet Mars un peu plus loin vers la droite. Ceci a pour effet de dupliquer la feuille Mars. Renommez-la en Trimestre 1 :

La feuille Trimestre 1 étant active, cliquez sur la cellule B3 et entrez la formule

=SOMME(

Basculez sur l’onglet Janvier et cliquez sur la cellule B3. Maintenez la touche Majuscule du clavier enfoncée et cliquez sur l’onglet Mars. La barre de formule devrait maintenant contenir la formule suivante :

=SOMME(Janvier:Mars!B3

Fermez la parenthèse et appuyez sur la touche Entrée du clavier.

Dans la feuille Trimestre 1, cliquez sur la cellule B3 et étirez la sélection pour recopier la formule jusqu’à la cellule D3. Lorsque vous relâchez le bouton gauche de la souris, la plage B3:D3 reste sélectionnée. Etirez la sélection jusqu’à la cellule D9 :

Ça y est, la feuille Trimestre 1 contient le récapitulatif trimestriel des ventes :

Il est fréquent d’utiliser dans Excel des données issues de différentes applications. Ces données ne sont pas toujours utilisables telles quelles …

A titre d’exemple, ces données démographiques ont été copiées-collées dans Word à partir d’une page Web :

Si vous les copiez-collez dans Excel, le résultat est bien décevant :

Si la répartition sur plusieurs lignes est bien respectée, les données de chaque ligne restent cantonnées à une seule colonne. Pour améliorer les choses, vous pouvez agir sur le document Word ou sur les données collées dans Excel.

Dans Word

Pour que les données d’une ligne se répartissent sur plusieurs colonnes dans Excel, vous devez les séparer entre-elles par des tabulations.

Sous l’onglet Accueil, dans le groupe Edition, cliquez sur l’icône Remplacer. La boîte de dialogue Rechercher et remplacer s’affiche. Tapez , dans la zone de texte Rechercher et ^t dans la zone de texte Remplacer par, puis cliquez sur Remplacer tout :

Le texte est désormais prêt à être copié-collé dans Excel :

Appuyez sur Contrôle + A, puis sur Contrôle + C. Basculez dans Excel. Cliquez sur la première cellule et appuyez sur Contrôle + V. Cette fois-ci, les données sont bien réparties sur plusieurs lignes et plusieurs colonnes :

Dans Excel

Les données importées peuvent également être réparties sur plusieurs colonnes sans repasser par Word. Sélectionnez les cellules concernées, basculez sur l’onglet Données du ruban et cliquez sur l’icône Convertir dans le groupe Outils de données :

La boîte de dialogue Assistant Conversion s’affiche. Sélectionnez l’option Délimité puis cliquez sur Suivant :

Cochez la case Virgule. La zone d’aperçu donne un avant-goût du résultat final. Cliquez sur Suivant :

La troisième boîte de dialogue permet de définir le format de données de chaque colonne. Ici, les colonnes ne contiennent que des données entières. Cette étape n’est donc pas utile. Cliquez sur Terminer :

Les données sont maintenant réparties sur plusieurs colonnes, ce qui est bien l’effet recherché :

Saviez-vous qu’il est possible de définir une liste déroulante dans une cellule pour limiter les possibilités de saisie et faciliter la vie de l’utilisateur ? Et cela sans aucune ligne de code ?

Supposons que vous vouliez limiter la saisie dans une cellule aux prénoms des commerciaux de la société. Supposons que ces prénoms soient les suivants : Eric, Kévin, Sandrine, Christel, Isabelle, Stéphane et Christophe.

Tapez ces noms dans différentes cellules d’une feuille. Si vous voulez qu’ils restent hors de la vue des utilisateurs, placez-les dans des cellules éloignées :

Cliquez sur la cellule dans laquelle vous voulez afficher la liste déroulante. Basculez sur l’onglet Données du ruban. Cliquez sur l’icône Validation des données dans le groupe Options de données. La boîte de dialogue Validation des données s’affiche :

Choisissez l’entrée Liste dans la liste déroulante Autoriser (1). Cliquez dans la zone de texte Source et désignez les cellules dans lesquelles se trouvent les prénoms (2). Et enfin, assurez-vous que la case Liste déroulante dans la cellule est cochée (3). Cliquez sur OK pour insérer la liste déroulante dans la cellule :

Voici l’aspect de la liste déroulante lorsqu’elle est repliée :

Et lorsqu’elle est dépliée :

Si vous essayez de saisir une autre valeur que celles proposées dans la liste, un message d’erreur s’affiche lorsque vous appuyez sur la touche Entrée ou Tabulation ou que vous cliquez sur l’icône Entrée, à gauche de la barre de formule :

Remarque

Si vous ne voulez pas que les éléments de la liste se trouvent dans le classeur, vous pouvez définir une liste personnalisée, comme indiqué dans la formation Excel 2016.

Saviez-vous qu’il est possible d’incorporer une feuille Excel dans un document Word ? Si la feuille Excel change, le document Word sera mis à jour en conséquence.

Dans Excel, basculez sur la feuille que vous voulez incorporer dans le document Word.

Dans Word, basculez sur l’onglet Insertion puis cliquez sur l’icône Objet dans le groupe d’options Texte. La boîte de dialogue Insérer un objet s’affiche. Sélectionnez l’onglet Créer à partir du fichier, cliquez sur Parcourir et désignez le classeur qui contient la feuille à incorporer. Cochez la case Lier au fichier et validez en cliquant sur OK :

La feuille est incorporée dans le document :

Revenez à Excel. Ajoutez une ligne pour calculer la moyenne des notes :

Sauvegardez le classeur. Retournez dans Word. Cliquez du bouton droit sur la feuille incorporée et sélectionnez Mettre à jour les liaisons dans le menu contextuel. La feuille est mise à jour :

Vous savez certainement filtrer vos données en utilisant l’icône Filtrer de l’onglet Données du ruban. Ici, nous allons voir une méthode alternative qui vous permettra d’aller plus loin.

Nous allons partir de ces données :

Dupliquez les cellules de titre A3 à D3 et insérez-les un peu plus à droite. Par exemple en F3.

A titre d’exemple, nous allons afficher uniquement les personnes dont le champ Titre vaut Sales Representative. Pour cela, il suffit d’inscrire Sales Representative sous Title :

Basculez sur l’onglet Données du ruban. Dans le groupe d’options Trier et filtrer, cliquez sur Avancé. Cette action déclenche l’affichage de la boîte de dialogue Filtre avancé. Sélectionnez l’option Copier vers un autre emplacement. Indiquez la plage qui contient les données, la zone de critères (ici les cellules F3 à I3) et l’emplacement où afficher le résultat du filtre (ici, à partir de la cellule F7) :

Cliquez sur OK pour afficher le résultat :

Supposons maintenant que vous vouliez obtenir la liste des personnes dont le champ Title vaut Sales Representative ou dont le champ Employee ID est inférieur à 3. Pour cela, il vous suffit d’ajouter une ligne dans la zone de critères :

Cliquez sur l’icône Avancé dans le groupe d’options Trier et filtrer, sous l’onglet Données du ruban. La boîte de dialogue Filtre avancé s’affiche. Sélectionnez l’option Copier vers un autre emplacement, cliquez dans la zone de critères et désignez la zone de critères qui comporte désormais une ligne complémentaire. Validez en cliquant sur OK. Les personnes sélectionnées ont soit un champ Title égal à Sales Representative, soit un champ Employee ID inférieur à 3 :

Supposons maintenant que vous vouliez obtenir la liste des personnes dont le champ Title vaut Sales Representative et dont le champ Employee ID est supérieur à 4. Définissez les deux critères sur la même ligne dans la zone de critères :

Cliquez sur l’icône Avancé dans le groupe d’options Trier et filtrer, sous l’onglet Données du ruban. La boîte de dialogue Filtre avancé s’affiche. Sélectionnez l’option Copier vers un autre emplacement, cliquez dans la zone de critères et désignez la zone de critères (ici, les cellules F3 à I4). Validez en cliquant sur OK. Les personnes sélectionnées ont un champ Title égal à Sales Representative et un champ Employee ID supérieur à 4 :

Vous savez certainement faire des recherches dans une feuille de calcul en utilisant l’icône Rechercher et sélectionner du ruban ou le raccourci clavier Contrôle + F. Mais savez-vous que vous pouvez modifier toutes les occurrences de la recherche en une seule fois ?

Dans le groupe Edition, sous l’onglet Accueil du ruban, cliquez sur l’icône Rechercher et sélectionner, puis cliquez sur Rechercher. La boîte de dialogue Rechercher et remplacer s’affiche. Entrez le terme recherché puis cliquez sur Rechercher tout. La partie inférieure de la boîte de dialogue liste les lignes correspondantes :

Appuyez sur Contrôle + A pour sélectionner toutes les cellules qui contiennent le terme recherché puis fermez la boîte de dialogue en cliquant sur Fermer. Vous pouvez maintenant appliquer une mise en forme globale aux cellules sélectionnées, voire même modifier leur contenu en agissant sur la barre de formule.

Appuyez simultanément sur les touches Contrôle et Entrée du clavier pour appliquer la modification dans toutes les cellules.