Share
Excel est excellent, mais essayer de comprendre comment utiliser Excel par vous-même ne peut vous mener à rien, car il n’est pas intuitif. Mais si vous utilisez les techniques et les astuces de ce didacticiel, vous pourrez faire votre travail plus rapidement et sans trop de stress..
Apprenez à appliquer les formules Excel, les calculs, le filtrage, la manipulation de données, l'efficacité du flux de travail, etc. Voici une douzaine de techniques et de fonctionnalités Excel à connaître.
Avant de commencer, téléchargez la feuille de calcul gratuite sur fichier Excel. Le fichier contient plusieurs feuilles, une permettant de s'exercer à chacune des douze techniques Excel. Regardez le didacticiel complet ci-dessus ou suivez la version écrite détaillée ci-dessous..
Chaque formule doit commencez par un signe égal. Si vous n'avez pas le même signe, Excel ne fera pas le calcul. il va simplement afficher ce que vous avez tapé. C'est la manière d'Excel de dire: "Hé, calcule ça." Pensez au résultat de votre formule comme ce qui devrait être à gauche du signe égal si vous utilisiez du papier et un crayon..
Et où écrivez-vous une formule? Ecrivez-le dans la cellule où vous voulez que la réponse apparaisse. Reportez-vous à la fiche "simple" du fichier Excel gratuit pour cet exercice..
Exemple: ajouter les valeurs de B2, B3 et B4 et mettre le total en B6
Dans B6, entrez cette formule:
= B2 + B3 + B4
Formule simplePuis appuyez Entrer pour obtenir le résultat. Si vous n'appuyez pas sur Entrer (ou cliquez sur le petit coche juste au dessus colonne A), rien ne va se passer.
Formule simple entréeSoustraction, multiplication, division et exposants fonctionnent de la même manière. Pour la plupart, utilisez les symboles que vous utiliseriez normalement ou référez-vous à ce tableau:
| Opération | symbole |
|---|---|
| Une addition | + |
| Soustraction | - |
| Multiplication | * |
| Division | / |
| Exposant | ^ (exemple: B5 ^ 2 est la valeur de B5 au carré) |
| Plus grand que | > |
| Moins que | < |
| Plus grand ou égal à | > = |
| Inférieur ou égal à | <= |
| Joindre | Et |
Si vous souhaitez effectuer un calcul un peu plus complexe qu'une simple formule, insérez une fonction dans votre formule. Excel en a environ 400, vous en trouverez probablement quelques-uns dont vous avez besoin. Il existe des fonctions pour les entreprises, les statistiques, les finances, la date et l'heure, les textes, les informations sur les documents, etc..
La syntaxe de toutes les fonctions est la suivante:
= NOM DE LA FONCTION (…)
Vous commencez toujours par un signe égal si la fonction est au début d'une formule, puis le nom de la fonction, puis un ensemble de parenthèses. Il y a généralement quelque chose entre les parenthèses, mais pas toujours.
Exemple: Trouvez le total et la moyenne d'une colonne de nombres. Reportez-vous à la fiche "fonctions" du fichier Excel gratuit pour cet exercice..
Dans B10, entrez la fonction:
= SOMME (B2: B8)
Dans B11, entrez la fonction:
= MOYENNE (B2: B8)
Somme et moyennepresse Entrer pour obtenir les résultats.
Somme et résultat moyenSupposons que vous écrivez une formule en haut d'une colonne et que vous souhaitiez réutiliser la formule dans la colonne (ou que vous ayez une formule au début d'une ligne et que vous souhaitiez réutiliser la formule dans la ligne). Au lieu de copier et coller manuellement, utilisez le Remplissage automatique fonction pour en faire un travail rapide et que la formule s’ajuste automatiquement. Vous pouvez aussi utiliser Remplissage automatique pour entrer automatiquement les mois ou les jours de la semaine.
Utiliser Remplissage automatique, recherchez le petit point dans le coin inférieur droit de la cellule actuelle. (Si plusieurs cellules sont sélectionnées, elles partagent un point commun.) Lorsque vous placez le pointeur de la souris sur le point, le pointeur se transforme en croix. Ensuite, faites glisser le réticule dans la colonne ou à travers la ligne.
Exemple: Faites une multiplication simple dans D2, puis Remplissage automatique jusqu'à D8. Reportez-vous à la feuille de saisie automatique du fichier Excel gratuit pour cet exercice..
Écrivez la première formule comme vous le feriez normalement.
Formule de remplissage automatiqueEntrez la formule. Si nécessaire, cliquez à nouveau sur D2. Faites glisser le pointeur de la souris sur le point lourd dans le coin inférieur droit de la cellule et le pointeur de la souris deviendra un réticule.
La souris devient un réticuleSoit tirer le réticule vers le bas colonne D, ou juste double-cliquez. La colonne aura les formules correctes et les résultats corrects dans la colonne.
Double-cliquez pour sélectionner la colonneVous pouvez utiliser Excel comme une petite base de données, en triant des lignes de données par ordre alphabétique ou numérique et en filtrant uniquement les données souhaitées. Cela fonctionne mieux lorsque les colonnes ont des en-têtes, comme Prénom, Nom, etc..
La chose la plus importante à retenir est ne pas sélectionnez une colonne avant de trier. Cela triera la colonne indépendamment du reste des données, et ce n'est probablement pas ce que vous voulez. Lorsque vous cliquez dans une colonne, Excel est suffisamment intelligent pour savoir ce que vous faites..
En matière de filtrage, Excel dispose d'excellents outils automatiques.
Exemple: Triez une liste par nom de famille, ville ou autre colonne, par ordre croissant ou décroissant. Reportez-vous à la feuille "Filtrer et trier" du fichier Excel gratuit pour cet exercice..
Tri simple dans ExcelCliquez sur n'importe quelle cellule du Nom de famille colonne. Sur le Accueil onglet du Ruban, Cliquez sur Trier et filtrer, Alors choisi De A à Z ou Z à A. Essayez ceci avec l'une des autres colonnes.
Vous avez peut-être remarqué que vous pouvez filtrer à partir du même menu déroulant..
Exemple: Filtrez maintenant la liste pour n’afficher que les adresses. Alaska et Californie.
Cliquez n'importe où dans la zone de données et à partir du Trier et filtrer menu, choisissez Filtre.
Filtre simpleDès que vous le ferez, vous remarquerez que les en-têtes de colonnes ont des flèches déroulantes. À présent:
Boîte à filtresLa liste affiche maintenant uniquement les lignes pour ces deux états. Si vous le souhaitez, triez les états de A à Z. Cela regroupera toutes les lignes de l’Alaska au-dessus des lignes de la Californie (en surbrillance dans la capture d'écran ci-dessous).
Si vous voulez effacer le filtre, cliquez sur la flèche déroulante sur le Etat colonne à nouveau et choisissez Effacer le filtre.
Effacer le filtreVous pouvez supprimer les flèches déroulantes de la même manière que vous les avez obtenues: cliquez sur le bouton Trier et filtrer menu et choisir Filtre, encore.
Si vous avez beaucoup de données, en particulier si elles sont importées à partir d'un formulaire Web, par exemple, vous pourriez avoir des lignes en double. Excel supprime les doublons et vous permet de décider si des lignes similaires doivent être pour que ces doublons soient considérés..
Exemple: Utilisez les informations d'adresse pour déterminer les lignes en double. Reportez-vous à la feuille «Supprimer les doublons» du fichier Excel gratuit pour cet exercice..
Lorsque nous faisons défiler cet exemple de fichier, nous voyons qu'il existe trois lignes identiques dont le nom est Samantha Carter: les données de toutes les colonnes sont identiques, nous savons donc qu'il s'agit de la même personne répertoriée trois fois. Il y a aussi deux rangées où le nom est Sharon Valeri, mais toutes les autres informations sont différentes, nous savons donc qu'il s'agit de deux personnes portant le même nom..
Sélectionnez à la fois Sam et Sharon Assurez-vous de cliquer quelque part dans la zone de données. Puis dans le Les données onglet du ruban, Cliquez sur Supprimer les doublons.
Supprimer les doublonsLaissez toutes les cases à cocher sélectionnées. De cette façon, Excel considérera les lignes comme des doublons uniquement si toutes leurs colonnes sont identiques..
Cliquez sur D'accord. Cela nous indique que deux doublons ont été supprimés, ce à quoi nous nous attendions. Lorsque nous faisons défiler l'écran, nous voyons deux des trois disques de Samantha Carter supprimés et les deux disques de Sharon Valeri sont conservés..
Doublons enlevésSi vous avez une colonne de données que vous devez fractionner en plusieurs colonnes, utilisez le Texte en colonnes commander. Sachez simplement qu'il n'est pas parfait car il ne peut pas lire dans vos pensées, vous devrez donc peut-être effectuer un nettoyage manuel..
Exemple: divisez une colonne contenant les noms complets en colonnes séparées pour le prénom et le nom. Reportez-vous à la feuille de fractionnement du fichier Excel gratuit pour cet exercice..
Ici, nous avons des données similaires à celles des exemples précédents. Notez que trois personnes dans la liste ont trois noms, nous aurons donc besoin de deux colonnes vides.
Tout d'abord, insérez deux colonnes vierges à droite de colonne A:
Placer le pointeur de la souris sur l'en-tête de colonne B, alors ça devient un flèche vers le bas. Glisser vers la droite, donc des colonnes B et C sont choisi.
Sélection des colonnes ExcelClic-droit et sélectionnez Insérer dans le menu contextuel. Puisque vous avez sélectionné deux colonnes, vous en avez maintenant deux vierges..
Sélectionner tous les noms dans colonne A. (Moyen rapide: cliquez sur le premier dans A3, puis appuyez Ctrl + Maj + Flèche bas. ensuite faire défiler vers le haut.)
Sur le Les données onglet, sélectionnez Texte en colonnes. Choisir Délimité, puis clique Suivant.
Colonnes Texte en OngletsChoisir Espace comme seul délimiteur. Traiter les délimiteurs consécutifs comme un seul sera sélectionné par défaut, alors laissez cela. Faites défiler la liste pour voir les noms qui vont s'étendre sur trois colonnes.
Texte des onglets sur les colonnesCliquez sur Suivant, puis clique terminer. Colonne A aura maintenant que des prénoms, colonne B aura des noms de famille, et colonne C aura trois noms qui ont débordé.
Noms divisésVous pouvez modifier manuellement ces trois noms, puis supprimer la colonne C, qui devrait être vide.
Excel fait également du bon travail en sens inverse: si vous avez plusieurs colonnes et que vous voulez les joindre en une seule. Il y a plusieurs façons de faire cela, mais je vais vous montrer la plus simple, qui consiste à utiliser une formule. Reportez-vous à la fiche "rejoindre" dans le fichier Excel gratuit pour cet exercice..
Comme ci-dessus, insérez une nouvelle colonne après colonne B. Positionner le pointeur de la souris sur le entête de colonne C alors le pointeur de la souris devient un flèche pointant vers le bas…
Insérer une nouvelle colonne… Puis sélectionnez Insérer dans le menu contextuel. Donnez à la nouvelle colonne le titre de Nom complet.
Cliquez dans la cellule C3 en haut de la nouvelle colonne pour faire la première formule. Pour le rendre plus compréhensible, nous le ferons avec une petite erreur, puis le réparer.
Puisque nous voulons joindre le contenu de la cellule, et non ajouter des numéros, l'opérateur à utiliser est une esperluette. Alors faites la formule:
= A3 et B3
Formule de jonctionMais lorsque vous y entrez, le prénom et le nom de famille sont écrasés ensemble, sans espace entre eux. Alors supprimez-le. Réécrivez la formule à nouveau, mais avec un autre élément inclus:
= A3 & "" & B3
Mettre un espace entre les guillemets doubles signifie que la formule doit littéralement inclure un espace.
Formule de jonction avec espaceEntrer ça et Remplissage automatique jusqu'au fond.
Remplissage automatiqueVoici quelques moyens rapides de formater des nombres (dollars, pourcent, etc.) et des feuilles de calcul (couleurs, polices).
Reportez-vous à la fiche de formatage dans le fichier Excel gratuit pour cet exercice..
Les nombres dans cette feuille de travail doivent comporter des virgules pour séparer des milliers, mais ne nécessitent pas de décimales. Vous voudrez peut-être appliquer des signes dollar à la première rangée, le Ligne totale et le Colonne totale. En outre, la dernière colonne est mise en forme en décimales mais doit ressembler à des pourcentages..
Sélectionnez la rangée supérieure de nombres à l'aide de la Colonne totale.
Maintenez la Touche Ctrl et sélectionnez à travers le Total rangée du bas (les rangées du haut et du bas devraient maintenant être sélectionnées).
Sélection du formatageSur le ruban, cliquez sur le bouton Signe dollar bouton ou appuyez sur Ctrl + Shift + $. Cela s’applique aux signes dollar, aux virgules et aux décimales. (Nous allons supprimer les décimales dans une minute.)
Maintenant, sélectionnez tous les nombres entre les deux et cliquez sur le bouton Bouton de style virgule ou appuyez sur Ctrl + Maj + !. Cela s'applique aux virgules et aux décimales.
Pour supprimer toutes les décimales, sélectionnez tous les nombres de la feuille sauf le Colonne de pourcentage à la fin et cliquez sur le Diminuer décimal bouton deux fois.
Mise en forme Excel - diminution des décimalesAppliquons maintenant les pourcentages.
Sélectionnez tous les numéros du bas % Du total colonne, puis cliquez sur le % signer ou appuyer Ctrl + Maj +%.
Si vous voulez, cliquez sur le bouton Augmenter décimal bouton une ou deux fois.
Appliquer des pourcentagesPour formater la feuille, il y a trois façons de le faire:
Pour la vitesse, voici les deuxième et troisième méthodes.
Sélectionnez tous les en-têtes de colonne, puis sur le ruban, cliquez sur Styles de cellules. Choisissez-en un.
Styles de cellules de mise en forme de colonnes ExcelFaites la même chose pour les en-têtes de lignes.
Styles de cellules de formatage d'en-tête ExcelAnnuler plusieurs fois.
Maintenant, cliquez Format comme tableau et choisissez un design.
Cliquez sur D'accord dans la boîte de dialogue qui apparaît. Notez que les colonnes ont des flèches déroulantes afin que vous puissiez les filtrer.
Si une colonne est trop étroite, vous pouvez avoir du texte coupé ou des chiffres affichés sous forme de dièse. Si une ligne est trop courte, vous ne pourrez peut-être pas la lire. Reportez-vous à la feuille "Lignes et colonnes" du fichier Excel gratuit pour cet exercice..
Le réglage de la largeur de la colonne et de la hauteur de la ligne fonctionne essentiellement de la même manière: placez le pointeur de la souris sur le bord droit ou inférieur de la ligne ou de la colonne de façon à ce que le pointeur de la souris devienne une flèche à deux pointes, puis faites glisser jusqu'à la taille souhaitée pour la ligne ou la colonne. . Ou tu peux double-cliquez pour obtenir la taille de la ligne ou de la colonne automatiquement. (Cela fonctionne de la même manière dans Explorateur de fichiers Windows dans la vue détaillée.)
Explorateur de fichiers Windows Largeur de colonne Hauteur de ligneIl y a deux façons de créer des sauts de page:
Pour insérer un saut de page manuellement, sélectionnez l'en-tête de la ligne ou de la colonne à l'endroit où vous voulez que la page se sépare, puis sélectionnez Onglet Mise en page du ruban, cliquez sur le bouton Brise la liste déroulante et sélectionnez Insérer un saut de page.
Insérer un saut de pagePour ajuster les sauts de page, utilisez Aperçu de saut de page.
Clique le Aperçu de saut de page icône dans le coin inférieur droit, puis faites glisser les lignes. Les lignes en pointillé sont des sauts de page automatiques. Si vous insérez des sauts de page, ces lignes seront pleines..
Ajuster les sauts de pagePour insérer, supprimer, déplacer ou renommer des feuilles de calcul, utilisez les onglets situés dans le coin inférieur gauche de l'écran..
Ce cahier a une seule feuille, appelée Feuille1. Pour le renommer:
Travailler avec des drapsPour insérer une nouvelle feuille vierge, Cliquez sur la ronde Signe plus à côté de l'onglet.
Pour dupliquer la feuille:
Cliquez sur le triangleRelâchez la souris en premier. Vous verrez une nouvelle feuille avec le même nom et un “(2)" après ça.
Votre nouvelle fiche crééeVous pouvez faire glisser les feuilles dans la position de votre choix. Les feuilles sont déposées à l'endroit où vous voyez le triangle pointant vers le bas.
Lorsque vous avez une longue feuille, il est facile de faire défiler un en-tête de colonne, mais une fonction permet d'éviter cela. Lorsque vous imprimez une longue feuille, vous voulez vous assurer que les en-têtes de colonne apparaissent en haut de chaque page et ne le faites pas en le copiant-collant. Cela deviendra un cauchemar.
Reportez-vous à la fiche "en-têtes" du fichier Excel gratuit pour cet exercice..
Dans cette fiche, cliquez sur la cellule A3, qui est juste en dessous de la ligne des en-têtes de colonnes. Puis dans le Vue onglet du ruban, cliquez sur Geler les vitres. Et sélectionnez Geler les vitres de la liste déroulante.
Sélectionnez Figer les voletsMaintenant, vous pouvez faire défiler la feuille de haut en bas et toujours voir les en-têtes.
Vous pouvez dégeler les volets de la même manière: cliquez sur Geler les vitres, puis sélectionnez Dégeler les vitres de la liste déroulante.
Toutefois, cela n’a aucun effet sur l’impression. Pour imprimer des en-têtes de colonne sur chaque page:
Sélectionnez les en-têtesPour voir que cela fonctionne, allez à Aperçu avant impression: Fichier> Imprimer ou appuyez sur Ctrl + P.
Cliquez sur les onglets de page en bas pour faire défiler les pages et voir les en-têtes..
Faites défiler les en-têtesExcel a plusieurs fonctions qui peuvent décider de la valeur à afficher. Celles-ci sont toutes basées sur la fonction IF. Une fois que vous vous y serez familiarisé, il vous sera plus facile d’utiliser les plus complexes..
La fonction IF a trois arguments:
Lorsqu'une fonction a plusieurs arguments, vous les séparez par des virgules. Donc, voici la syntaxe:
= IF (condition à tester, quoi renvoyer si vrai, quoi renvoyer si faux)
Reportez-vous à la fiche "fonctions" du fichier Excel gratuit pour cet exercice..
Exemple: nous avons une liste de commandes passées par des personnes de différents états. Si une commande provient du New Jersey, appliquez une taxe de vente de 7%. Si une commande provient de n'importe où ailleurs, n'appliquez pas de taxe.
Au sommet de la Colonne d'impôt (H3), entrez cette formule:
= SI (C3 = "nj", G3 * .07, "")
Ça signifie:
Résultats de la fonction IFRemplissage automatique vers le bas. La plupart des cellules de la colonne Taxes seront vides et celles qui ne le sont pas ont une taxe de vente car cette ligne indique New Jersey dans le Etat colonne, comme indiqué ci-dessous.
Semblables à la fonction IF, les fonctions SUMIF et AVERAGEIF trouveront la somme et la moyenne d'une plage de cellules contenant une certaine valeur ou condition..
Les fonctions ont trois arguments:
La syntaxe des deux fonctions est similaire, mais les mêmes arguments:
= SUMIF (plage à regarder, valeur à choisir, plage à ajouter)
= AVERAGEIF(plage à regarder, valeur à choisir, plage à ajouter)
Référez-vous à la feuille 'SUMIF' dans le fichier Excel gratuit pour cet exercice.
Exemple: recherchez le total de toutes les commandes en Californie et la moyenne des commandes de Kona..
Au bas de la Vente colonne, dans G203, entrez cette formule pour trouver le total des commandes en Californie:
= sumif (C3: C201, "ca", G3: G201)
Ça signifie:
Ajouter la valeur de la colonneMaintenant, utilisez la même technique pour trouver la moyenne de toutes les ventes de Kona.
Entrer dans G204 et entrez cette formule:
= moyenne (D3: D201, "kona", G3: G201)
Ça signifie:
Moyenne de la colonneCela devrait être le résultat:
Résultat finalApprendre à utiliser Excel en vaut la chandelle. Avec ces techniques et astuces, vous pouvez utiliser davantage de fonctionnalités puissantes d'Excel, qui vous feront gagner du temps, à utiliser dans vos propres feuilles de calcul. Pratiquez les leçons ci-dessus, puis travaillez sur l'intégration de ces techniques dans votre flux de travail..
Accentsconagua