Archives de catégorie : astuce excel

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

 

 

 

 

 

 

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.

Mettre en forme son fichier pour l’impression sous Excel 2007

Préparer un document permettant l’impression d’un tableau sur Excel 2007

Par défaut, votre page Excel est affichée en mode « Normal » et en portrait, c’est-à-dire dans le sens vertical de la feuille.

Pour améliorer votre présentation et modifier la mise en page avant l’impression,  nous pouvons y accéder de deux manières :

  • En sélectionnant l’onglet Mise en page directement sur votre barre d’outils
  • En cliquant sur le bouton Office puis ImprimerAperçu avant impression et Mise en page

Si vous souhaitez procéder à des modifications,  vous retrouverez les 4 fonctions essentielles pour la mise en forme : Page – Marges – En-tête/Pied de page – Zone d’impression

Nous allons donc détailler ici les fonctionnalités de chacun.

L’onglet  Page

Vous retrouverez ici les options de présentation de votre classeur sur papier.

Vous pouvez alors modifier l’orientation des pages à imprimer et définir votre échelle d’impression ; Ce qui est important lorsque vous avez de nombreuses colonnes et que vous souhaitez qu’elles apparaissent toutes sur la même page et non sur la suivante.

Pour modifier l’échelle, vous pouvez soit le faire manuellement en utilisant la fonction Réduire/Agrandir à :  ou vous pouvez laisser Excel choisir automatiquement la meilleure échelle en fonction de votre classeur en sélectionnant la fonction Ajuster

Dans cet onglet, vous pourrez également changer le format du papier en fonction du document que vous souhaitez imprimer (A3, A4, A5, etc), la qualité de l’impression

L’onglet  Marge

Les marges correspondent à l’espace entre les bords de votre feuille qui sera imprimée et les données de votre feuille de calcul.

Par défaut, Excel a prédéfini ces marges en mode normal mais vous pouvez également opter pour des  marges larges, étroites ou encore les personnaliser vous-même.

Dans cet onglet, vous pourrez également centrer votre feuille de calcul horizontalement ou verticalement pour un rendu parfait.

L’onglet En-tête/Pied de page

Par cet onglet, vous pourrez rapidement ajouter et modifier des en-têtes et des pieds de pages afin d’apporter des informations utiles à vos feuilles de calcul lors de l’impression.

Vous pourrez également insérer d’autres éléments pratiques tels que date, l’heure, le nom de votre fichier, des numéros de pages, etc

L’onglet  Feuille

Par défaut, Excel vous imprimera la totalité de votre feuille de calcul. En utilisant l’onglet  Feuille, vous pourrez définir votre zone d’impression si vous ne souhaitez imprimer qu’une partie vos données mais également faire répéter sur chaque feuille d’impression le ou les lignes en haut et la ou les colonnes à gauche.

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.

La référence relative, la référence absolue et le symbole $ sous Excel

A quoi servent les $ sur Excel et comment les mettre en place efficacement, sans se tromper.

utilisation-$-excelPour mesurer l’intérêt du symbole $ sous Excel, vous devez avant tout comprendre les différences entre ce qu’on appelle la référence relative et la référence absolue.

La référence relative est la référence standard d’Excel. Il s’agit de la position de la cellule dans une formule de calcul.

Par exemple, si vous souhaitez faire la somme des valeurs contenues dans vos cellules A1, B1 et C1, votre formule de calcul en D1 sera donc =SOMME(A1+B1+C1) ou plus simplement =SOMME(A1:C1)

Les : permettant de sélectionner toutes les valeurs entre la première cellule choisie, A1 et la dernière, C1

Si vous recopiez cette formule vers le bas, votre formule se modifiera et s’adaptera  automatiquement. La formule de calcul dans la cellule D2 deviendra donc =Somme(A2:C2) et ainsi de suite tant que vous recopierez cette formule.

La référence absolue est une valeur contenue dans une cellule qui ne sera pas incrémentée et qui aura donc toujours la même valeur dans votre formule de calcul.

Celle-ci s’obtient en ajoutant le symbole $ à votre formule.

Prenons l’exemple de la formule ci-après que nous créons dans la cellule C1 : =(A1*$B$1)
Si vous recopiez cette formule vers le bas, votre formule dans la cellule C2 deviendra : =(A2*$B$1).
Ce qui signifie qu’Excel fera le calcul de multiplication en allant chercher la valeur contenue en A2 et la valeur contenue en B1.

Vous pouvez étirer cette formule sur des dizaines de lignes, votre formule s’adaptera aux valeurs contenues dans votre première colonne mais prendra toujours la valeur contenue dans votre seconde colonne.

Vous avez compris, le symbole $ vous servira à figer une valeur précise contenue dans une cellule dans le cas où vous recopiez une formule dans votre feuille Excel.

Sur votre clavier, pour obtenir le $, vous pouvez soit utiliser la touche sur laquelle est représentée le symbole, soit utiliser la touche F4 en sélectionnant le nom de votre cellule dans votre formule de calcul.

Astuces : Renvoi à la ligne dans une cellule sur Excel 2007.

Il ne s’agit pas d’une révélation, mais nous nous engageons à produire des astuces comme des tutoriaux techniques. C’est pourquoi vous pourrez, tout spécialement dans cet article, vous rendre compte à quel point il est facile d’utiliser excel depuis la version 2007.

Dès qu’excel est utilisé avec du texte, on est toujours moins à l’aise qu’avec Word. Par conséquent, voici pour cet article, comment faire un renvoi à la ligne dans une cellule excel.

Figure 1 : Texte sans renvoi

Figure 1 : Texte sans renvoi

Dans la première image, vous pourrez voir que le texte contenu dans la cellule déborde. Vous pourrez alors jouer avec la largeur des colonnes, mais ce n’est pas forcément ce qu’il y a de plus pratique lorsque vous souhaitez travailler sur une seule page et que vous avez déjà une idée de ce que donnera votre impression. Ceci n’a pas d’intérêt pour des nombres, mais pour des classements de bases de données de noms par exemple, la présentation sera plus agréable et plus uniforme avec le renvoi à la ligne.

Figure 2 : Bouton de renvoi à la ligne

Figure 2 : Bouton de renvoi à la ligne

Depuis 2007, la fonction est directement visible et il vous suffit de:

  1. Cliquez sur la cellule, afin de la sélectionner.
  2. Cliquez ensuite sur le bouton se trouvant dans la section “alignement” : “Renvoyez à la ligne automatiquement”. Ce renvoi sera effectif sur la cellule sélectionné. Pour annuler votre renvoi, il suffit de recliquer sur le même bouton, ou alors d’annuler grâce à l’action CTRL + Z.
    Si vous souhaitez étendre cette fonction, il faut soit, sélectionner toute la feuille, soit sélectionner la colonne ou la ligne qui vous intéresse.

 

Astuce pour changer de feuille dans un même classeur excel.

Voilà un astuce rapide qui vous permettra de faire un changement de feuille rapide. Rien d’extraordinaire  mais cette astuce à l’avantage de proposer un gain de temps.

Excel est la référence du tableur pour être rapide et efficace. En aucun cas, excel ne doit être plus complexe et un tableau doit être organiser pour gagner du temps afin d’éviter de faire des tâches répétitives.

C’est pourquoi pour un même sujet, il est préférable de travailler avec des feuilles dans un même classeur lorsqu’on souhaite travailler sur des informations d’évolutions annuelles ou des chiffres qui reprennent les mêmes bases.

La plupart des gens préfèrent plusieurs classeurs pour une organisation qu’ils jugent plus faciles. Ce qui est faut donc pour nous, et c’est pourquoi je vous dis juste comment passer d’une feuille à l’autre sur excel avec votre clavier.

Raccourcis pour faire le changement de feuille dans un même classeur.

CTRLPour cela, vous appuyer sur la touche CTRL et en même temps, vous appuyer sur la flèche du bas, vous permettant ainsi de basculer sur une autre feuille excel. En réalisant successivement l’opération, vous pourrez alors vous balader tranquillement d’une feuille à l’autre.

fleche-bas-haut-excel

En réalisant l’opération inverse, vous pourrez revenir en arrière et ainsi basculer dans la feuille excel supérieur, c’est à dire celle d’avant. Pour cela, il faut appuyer simultanément sur la touche CTRL et la flèche du Haut.

Pour les débutants, je vous conseil d’utiliser ces raccourcis. S’il ne s’agit pas d’un réflexe au premier abord, ce sont les successions de ces utilisations qui fera que vous serez un bon utilisateur. Excel, powerpoint ou autre logiciel, la plupart de bons utilisateurs se bloquent et se forcent à utiliser un raccourcis précis pour ensuite en faire une habitude.

Petit à petit, l’utilisation se met dans votre quotidien et certains raccourcis sur Excel ont une impact sur d’autres logiciels ou sur votre windows tout simplement. s’agissant de liens en pc et mac, l’équivalent sur Excel mac se fait dans les conditions presque identiques, avec pour remplacer la touche CTRL, l’utilisation de la touche CMD, l’ancienne pomme à gauche et droite de votre clavier.

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