Archives de catégorie : Excel Débutant

Somme.Si : Faire une somme avec une condition sous Excel 2007

L’intérêt de la fonction Somme.Si est de pouvoir calculer une somme seulement si une condition, un critère est respecté.

Prenons pour exemple, le tableau suivant :

Le client X a reçu le devis et souhaite savoir au fur et à mesure qu’il valide les produits, le montant réel de sa commande.

Pour cela, on va donc utiliser la fonction Somme.Si. On va demander à Excel de nous calculer la somme seulement si nous avons l’accord « Oui » dans la colonne E de notre tableau.

 

Photo 1

 

 

 

 

 

Regardons de plus près la construction de cette formule :

Comme toutes les formules Excel, nous avons le signe = qui va indiquer à Excel que nous souhaitons faire un calcul.

Nous avons ensuite SOMME.SI, ce qui va indiquer à Excel, que nous souhaitons faire la Somme de nos données seulement Si le critère que nous allons lui indiqué est respecté.

La parenthèse permet par la suite de cerner notre formule. La parenthèse servant à définir l’ordre dans lequel Excel doit effectuer les opérations.

La plage va être l’ensemble des données dans lesquelles Excel doit aller chercher l’information, ici notre critère, à savoir dans notre exemple, de la cellule E4 à la cellule E11, ce qui nous donnera la plage E4:E11

(les  : servant de référence à Excel pour prendre en compte toutes les données comprises entre ces 2 cellules).

Photo 2

 

 

 

 

 

Ne pas oublier les points virgules ; qui servent à séparer les arguments de notre fonction.

Nous devons par la suite indiquer notre critère. Dans notre exemple, nous allons utiliser le terme Oui qui serait à mettre entre guillemet comme il s’agit de texte.

Ce qui nous donne pour le moment :

Photo 3

 

 

 

Excel nous demande par la suite la somme_plage, c’est-à-dire la plage de données où il doit effectuer les calculs si le critère que nous lui avons défini est respecté, à savoir dans notre exemple, dans notre colonne « Prix total » de la cellule D4 à la cellule D11, traduit par D4:D11

Photo 4

 

 

 

 

 

 

Il ne nous reste plus qu’à valider notre formule (sans oublier de fermer notre formule par une parenthèse).

Par la suite, à partir du moment où j’indique Oui dans ma colonne Accord, Excel me calcule la somme de mes produits validés.

On peut voir ci-après qu’Excel ne m’a calculé que la somme des articles Chaussure, Gant, Pull, Veste de Pantalon car ils contiennent bien le critère Oui dans ma colonne Accord et n’a pas pris en compte les articles Écharpe, Bonnet et Manteau car le critère n’est pas respecté.

Photo 5

 

 

 

 

 

 

Comment utiliser la fonction NBval sur Excel.

Utilisation de la fonction NBVAL sous Excel 2007 ou excel 2010

Nous allons voir ici comment nous pouvons compter un nombre de cellules contenant des données que ce soit du texte ou des nombres à l’aide de la fonction NBVAL sous Excel 2007 (procédure également valable sur la version 2010 et 2014).

Prenons pour exemple, un tableau Excel où nous suivons au jour le jour nos équipes de formateurs.

excel nbval

Dans notre tableau nous voulons savoir le nombre de personnes pour chaque catégorie et pour cela nous allons donc utiliser la fonction NBVAL qui va nous permettre en fait de calculer le nombre de cellules non vides.

Nous allons donc dans un premier temps, nous positionner dans la cellule B17, cellule dans laquelle nous voulons que s’affiche notre résultat pour la catégorie « Formateurs Excel – Niveau 1 ».

Comme vous le savez, toute formule doit commencer par le signe = auquel nous ajouterons la fonction NBVAL ; Dans la barre de formule, notre formule débutera donc ainsi.

nbval

En effet, en double cliquant sur cette fonction, nous voyons qu’automatiquement la parenthèse s’est affichée et qu’Excel nous apporte des indications sur les données que nous devons entrer pour que notre formule fonctionne correctement :

detail formule nbval

Ce que nous voulons, ce sont le nombre de données contenues dans notre colonne B intitulée « Formateurs Excel – Niveau 1 » et nous allons sélectionner, à l’aide de la souris, la plage de données de la cellule B5, où débute les noms de nos formateurs, à la cellule B16, où elle se termine.

exemple formule nombre valeur

On n’oublie pas de fermer la parenthèse et on valide notre formule à l’aide de la touche Entrée de notre clavier, ce qui nous donne =NBVAL(B5 :B16) ; Notre résultat s’affiche bien dans la cellule que nous avions sélectionné, à savoir B17 dans notre exemple.

Pour rappel, les : permette à Excel de comprendre qu’il doit prendre en compte toutes les données contenues entre la cellule B5 et la cellule B16

resultat formule excel

Il ne nous reste plus qu’à l’aide de la souris à étirer notre formule vers la droite pour la recopier et nos résultats s’afficheront pour chacune des catégories.

etirement formule excel

copier coller automatique formule

A présent, vous pouvez à ajouter ou supprimer un nom dans n’importe laquelle de vos colonnes, vos résultats s’agrémenteront automatiquement.

La fonction NB.SI également possible pour calculer le nombre de cellules pleines dans la plage sélectionnée.

A titre indicatif et un peu plus compliqué, pour cet exemple qui ne contient que des données textes, vous auriez également pu utiliser la fonction NB.SI qui vous calcule le nombre de cellules si la condition est respectée.

formule nb si

On demande ici à Excel de nous calculer le nombre de cellule entre la plage de données B5 et B16 si la cellule contient du texte, représentée par * que nous mettons entre guillemets.

calcul nombre de cellules

Comme vous pouvez le constater par vous-même, la première formule en utilisant la fonction NBVAL est bien plus simple à utiliser et nous arrivons au même résultat.

Impression d’un tableau trop large sur Excel 2010 et 2013

Première chose à faire, c’est de cliquer sur l’onglet « Mise en page » qui laissera place à un carré de mise en page (qui va de marges à imprimer les titres).

Ensuite dans l’ordre, on va définir la zone d’impression. Au préalable, il faut sélectionner la zone qui nous intéresse, puis on clic « définir la zone d’impression »

Ensuite on prend le temps de définir l’orientation du document.  Pour cela, allez dans  l’onglet mise en page > Orientation > Portrait ou Paysage. Cette étape ne résoudra pas votre problème mais permettra d’avoir une impression plus confortable.

onglet-mise-en-pageEnsuite cliquez en bas à droite de l’onglet pour développer toutes les options de la mise en page de votre feuille excel. (en jaune sur l’image)

Une fenêtre s’ouvre alors et vous permet de détailler beaucoup plus votre mise en page, avec des chiffres qui vous permettront d’être le plus précis possible dans votre présentation.

fenetre mise en page excel

Les quatre onglets sont identiques sur Excel 2007, 2010 et 2013 pour vous rassurer. Le plus important alors pour adapter votre document, ce sera de choisir l’orientation, mais surtout de déterminer l’échelle.
Soit:

  • vous tâtonnez en modifiant le pourcentage de la taille normale.
  • vous ajustez en fonction du nombre de pages, en largeur et en hauteur.

La première solution est idéale lorsque votre document dépasse très largement et qu’il n’est pas énorme, en revanche pour une classeur excel très long, la deuxième solution est plus facile à gérer.

Il vous suffira ensuite de valider, puis de lancer l’impression et votre document sortira en une seule feuille, en deux, ou encore en trois feuilles… à vous de choisir.

N’oubliez pas que vouloir à tout prix sortir une seule feuille n’est pas le mieux si on ne peut rien lire. Sans vouloir les aligner et les scotcher comme on peut voir parfois, choisissez une feuille de large pour 3 ou 4 de long, cela ne gênera personne!

Si vous bloquez sur ce point, laissez nous un commentaire, nous vous proposerons la bonne procédure en fonction de la taille  et du type de votre document.

 

Modifier le format date sous Excel 2007

Excel vous présente différents formats de dates qui vous permettent de les présenter comme bon vous semble.

Par défaut, sous Excel, si vous saisissez une date, celle-ci s’affichera sous la forme « 01-janv » alors que vous l’avez saisi à l’aide du clavier numérique « 01/01 ».

Pour que votre date  s’affiche de manière complète, vous devez modifier le format de votre cellule.

  • Pour cela, sélectionnez la plage de données contenant vos dates ou la colonne entière si cette dernière n’est destinée qu’à ce type de données.
  • Dans l’onglet « Accueil » du ruban, dans la rubrique « Nombre », vous avez un menu déroulant qui par défaut, affiche « Standard ».

Il vous suffit alors de cliquer sur la petite flèche qui déroule les différentes options et de sélectionner :

  • Soit « Date courte » pour que votre date s’affiche sous le format « 01/01/2013 »,
  • Soit « Date longue » si vous souhaitez la formulation complète « mercredi 01 janvier 2014 ».

Autre façon de changer les formats :

Une fois vos données sélectionnées, vous pouvez utilisez le clic droit de votre souris. Vous retrouverez alors l’accès au « Format de cellule » et aux options de « Date » où de nombreuses possibilités vous seront offertes.

Insérer des commentaires à des cellules sous Excel 2010

Il peut être utile d’insérer des notes à vos cellules pour expliquer la donnée qui s’y trouve ou encore, pour vous faire un rappel. Par exemple, vérifier une donnée avant l’envoi ou l’impression de votre fichier ou encore lorsque vous partagez un fichier avec l’un de vos collègues et que vous souhaitez commenter un chiffre ou l’orienter dans le remplissage de sa feuille excel.

commentaires excel

Cela fonctionne un peu comme les mémos que vous collez un peu partout pour ne rien oublier mais sous format informatique.

Pour insérer un commentaire, il suffit de :

  • positionner votre curseur sur la cellule voulue
  • aller dans l’onglet « Révision »
  • cliquer sur « Nouveau commentaire ».

Une petite fenêtre apparaît alors à droite de votre cellule et vous pouvez saisir votre texte librement.

Pour valider votre commentaire, vous devez simplement cliquer hors de la zone de saisie.

Vous pouvez remarquer alors qu’un petit triangle rouge est apparu dans le coin à droite de votre cellule et cette petite icône  vous indique que cette cellule comporte une note. Il vous suffira de survoler la cellule avec ce petit triangle rouge pour lire le commentaire.

Pour effectuer cette action, je vous donne un petit raccourci qui vous fera gagner du temps :

Sélectionner la cellule dans laquelle vous souhaitez mettre votre commentaire, un clic droit avec votre souris et vous pouvez voir apparaître dans la liste la fonction « Insérer un commentaire ».

Si vous souhaitez modifier, afficher, masquer ou encore supprimer un ou plusieurs commentaires, vous trouverez tous les boutons utiles dans l’onglet « Révision ». Vous avez simplement à cliquer sur le bouton correspondant à l’action que vous souhaitez faire.

Pour modifier le format de vos notes si vous voulez, par exemple, changer la taille et la police du texte, mettre en gras, modifier la couleur du texte, etc, vous devez pour commencer Afficher votre commentaire (petit rappel, tout se passe dans l’onglet « Révision » !).

Il ne vous reste plus qu’à sélectionner votre texte, de faire un clic droit avec votre souris et vous verrez alors la fonction « Format de commentaire » où vous pouvez faire toutes vos modifications.

Afficher les formules sur Excel pour vérifier le contenu de votre cellule.

Même si la formule est longue, si le résultat est correcte, on ne voit que ce dernier apparaître. Par conséquent, il est bien utile de pouvoir de temps en temps voir correctement sa formule, et directement dans la cellule de sa feuille.

c’est un bon moyen de voir les éventuelles erreurs et un excellent indicateur qui permet de comprendre une formule. Nous l’utilisons donc très régulièrement durant nos formations d’initiation sur Excel, puisque la structuration d’une formule est le préalable à maîtriser pour être par la suite un peu plus tranquille.

Voici donc la procédure pour afficher votre formule sur excel:

  • Sélectionner sa cellule.
  • Tapez simultanément sur la touche CTRL +  » : cela affichera les formules.
  • Retapez sur les mêmes touches pour produire l’effet inverse, vous retrouverez votre résultat.

Il s’agit de la méthode facile, mais pour accéder à la fonction par le menu, vous pourrez passer par Fichier -> Options puis, Options avancées, et ensuite, afficher les options pour cette feuille. Il faudra alors cocher « Formules dans les cellules ». En décochant vous retrouverez votre résultat.

Dupliquer une donnée pour l’intégrer dans toutes les feuilles d’un classeur

Si vous avez besoin d’intégrer une nouvelle colonne ou un nouvel élément dans toutes les feuilles de votre classeur, Excel vous permet de le faire automatiquement.

Pour cela, il faut aller en bas de votre première feuille > faire un clic droit > sélectionner toutes les feuilles > puis il faut faire votre action, qui peut être l’ajout d’un nombre, l’ajout d’une colonne ou la mise en place d’un calcul.feuilles-excel

Ensuite, votre « action » sera présente sur toutes vos feuilles, vous économisant ainsi un temps précieux!

Ainsi dans l’exemple de l’image, il y a des tableaux pour répertorier les différents vins, permettant ensuite de définir des coûts de transport.
Si par exemple vous avez besoin d’une colonne de caractéristique supplémentaire qui s’applique à toutes les sortes de vins, vous aurez alors ce qu’il faut pour

 

Faire un tri par les couleurs avec Excel 2007 et 2010

Le tri de couleur a pour objectif de mettre en avant des éléments de votre tableau sans passer par un tri des données.

Depuis la version d’excel 2007, ces couleurs utilisées ne sont plus, juste à titre indicatif pour mettre en valeur des éléments. L’application d’une couleur permet également de créer des catégories, pour faire des tris par la suite, chose qui nous intéresse dans l’exemple de cet article.

L’application de ce tutoriel fonctionne pour les versions 2007, 2010 et bien évidement pour la toute dernière version 2013.
En utilisant le tri de couleur, vous pourrez ainsi mettre en avant certains éléments. Prenons l’exemple du commercial qui pose en vert ses contacts validés et en rouge les refus qu’il a essuyé.tableau sans tri de couleur

En fin de journée pour faire le tri de ses couleurs, rien de plus pratique que de le faire automatiquement, permettant alors de laisser passer que certaines données.

Le tri par couleur sur excel vous permettra de gagner un temps précieux d’observation sur Excel.

Si nous souhaitons mettre en avant les résultats en bleu, il faudra juste, depuis les dernières versions Excel 2007 et 2010, aller dans Données>Filtres, en ayant au préalable sélectionne le tableau des données.

filtre-excel

Vous aurez alors des flèches qui se positionnent sur votre cellule, permettant d’accéder au tri automatique par couleur.tri automatique sur excel

Ensuite il vous suffira de cliquer sur la flèche, pour aller ensuite dans : Filtre par couleur > couleur choisie

après filtre excel

A partir de là, seul les jaunes (dans notre exemple) apparaîtront sur votre feuille excel.

Bien imprimer sur Excel 2010, procédure.

Imprimer sur Excel: Quelque soit la version d’Excel, c’est toujours un casse tête pour imprimer correctement.

Rien ne vaut donc une procédure pour être capable de le faire une bonne fois pour toute et comprendre que Excel n’est pas un éditeur de texte et qu’il lui faut par conséquent quelques indications, indications précieuses qui vous permettront d’imprimer sur Excel.

Si nous prenons l’exemple d’Excel 2010 (pour prendre la version Office la plus utilisée) vous pourrez également l’appliquer à Excel 2007 et à Excel 2013 puisque la dernière version a très peu de changement.

Voici donc les 5 étapes à suivre pour que tout se passe correctement à la sortie de votre imprimante!

  1. Sélectionner à l’aide de votre souris, la partie à imprimer, c’est à dire la zone d’impression.impression-excel
  2. Cliquer sur le menu « mise en page ».
  3. Sélectionner « Zone d’impression » dans le carré mise en page affilié au menu du même nom.
  4. Cliquer sur « Définir ».
  5. Puis aller dans le menu et sélectionner « Impression ».

L’impression est désormais configurée, il ne vous reste plus qu’à mettre le papier! En cas de problème ou pour ajuster un document, vous pouvez également une fois l’opération faîte, changer vos marges, grâce aux options d’impression.
La gestion des marges sur Excel est prédéfinie, il faut donc en profiter. Si vous avez des détails plus précis à demander, il faudra alors les personnaliser.

Le vocabulaire de base

Vous avez pu le voir durant notre formation, le vocabulaire d’Excel est très simple.

Il faut faire le lien avec la vie « réelle », celle du papier de tous les jours.
En gros rappeler vous d’un classeur avec vos tarifs commerciaux par exemple.

Dans un classeur, il y a des feuilles puis des éléments. Excel s’organise de la même façon.

Pour excel c’est pareil, le document que l’on ouvre comme un « nouveau document », c’est le classeur, puis en dessous, ce sont les feuilles

  • classeur-excel-2007Voilà ci dessous quelques captures d’écran  avec le classeur, qui prend donc naturellement le titre de votre page (qui pourra changer par la suite avec un « enregistrer-sous » comme nous le verrons plus tard).
  • feuille-excel-2007Voici en dessous la capture d’écran pour une feuille : La feuille, comme la version papier, vous permettra d’en avoir plusieurs et donc de jongler sur des informations différentes, pourtant dans le même document, ou devrais je dire le même classeur (pour la nommer de façon différente, faîtes un clic droit puis sélectionner « renommer » puis taper votre texte et valider en appuyant sur la touche entrée).
  • Pour le reste, il s’agit d’une cellule et pour des groupes de cellules, nous appelons cela la plage