Catégorie : Excel intermédiaire

Naviguer dans Excel

Si la plupart d’entre vous maîtrisent sans souci les fonctions de déplacement dans Word, force est de constater que naviguer dans Excel pose d’avantage de soucis à nombre d’entre vous. Une petite piqûre de rappel s’impose donc…

Sélectionner des tableaux

Pour sélectionner rapidement un tableau :

  • positionnez votre curseur sur une cellule de votre tableau ;
  • maintenez les touches Ctrl * enfoncées ou, si vous le préférez, Ctrl A.

Si votre feuille de calcul contient plusieurs petits tableaux disséminés sur votre feuille et que vous souhaitiez les sélectionner tous (pour définir, par exemple, une zone d’impression) :

  • positionnez-vous en A1 ou sur la première cellule de votre tableau ;
  • maintenez les touches Ctrl Maj Fin enfoncées !

 

zone_nomAtteindre rapidement une cellule

Pour atteindre rapidement une cellule, il vous suffit simplement de taper son adresse (ou son nom) dans la zone « Nom » puis de valider en appuyant sur la touche Entrée.

 

 

Se déplacer dans un tableau

L’utilisation des curseurs vous permettra de vous déplacer très rapidement à l’intérieur d’un tableau ou d’un long listing.

Ainsi, la combinaison Ctrl droite (ou bas2haut2gauche) vous permet de vous déplacer suivant les cas :

Cellule de départ Cellule d’arrivée
Cellule vide  -> Première cellule non vide
Cellule non vide  –> Dernière cellule non vide

Autre alternative, communiquée par Céline Brien des Laurentides (Canada) : il suffit de double-cliquer sur les bordures d’une cellule pour se déplacer selon le même principe ! Pour vous déplacer vers la droite, faites un double-clic sur la bordure droite, pour vous déplacer vers le bas, faites un double-clic sur la bordure du bas, etc. Simple. Efficace ! Merci Céline.

Se déplacer de feuille en feuille

Pour se déplacer d’une feuille à une autre, rien de plus simple : il vous suffit d’utiliser la combinaison Ctrl baspour passer à la feuille suivante et Ctrl haut pour revenir à la feuille précédente.

Accéder rapidement à un onglet donné

Il n’est pas toujours évident de faire défiler les onglets lorsque ceux-ci sont particulièrement nombreux.

Aussi, pour accéder rapidement à une feuille de calculé donnée, faites un clic droit de la souris à gauche du premier onglet. Cette manipulation affiche une boîte de dialogue contenant l’intégralité des feuilles présentes dans le classeur. Sélectionnez alors la feuille désirée pour y accéder.

naviguer

Excel

Les fonctions dates et heures

Saisir une date sous Excel ? Rien de plus facile me répondrez-vous. Et vous aurez raison. Mais savez-vous additionner des heures ? Utiliser la fonction fin.de.mois() ? Non, peut-être pas… Je vous invite donc à vous intéresser de plus près à la gestion des dates et heures sous Excel.

Les jours

Saisir une date

Rien de plus facile que de saisir une date sous Excel.
Il suffit d’entrer votre date sous la forme 01/01 et de valider. Excel traduit alors, à défaut de plus de précision, que cette date concerne l’année en cours.

Saisir une plage de dates/de mois grâce à la poignée de recopie

Plus fort, il est possible de saisir les mois, les jours, les trimestres…. sans se fatiguer, sans risque d’erreur grâce à la poignée de recopie.

Concrètement, il vous suffit de saisir le nom du premier mois (Janvier voire Janv), le jour de la semaine (lundi), du trimestre (trimestre 1)… puis de valider.

Positionnez-vous sur la cellule que vous venez de compléter et placez votre curseur dans le bas inférieur droit (cf. illustration ci-dessous). Votre curseur prend la forme d’un +. Cliquez et maintenez le bouton gauche de la souris enfoncé. Tirez l’ensemble vers la droite : au fur et à mesure, les mois, jours, trimestres… défilent. Relâchez simplement le bouton de la souris quand vous le souhaitez.

recopie

Pour utiliser cette fonction avec les dates (1, 2… 31), vous devez saisir manuellement le 1 puis le 2 et sélectionner les deux cellules avant de tirer la poignée de recopie.

dateFormater une date

Excel propose de nombreux formats de date accessibles depuis l’onglet Accueil – Nombre. Les formats par défaut proposés par Excel sont peu nombreux.

Pour créer un format plus personnalisé, vous pouvez, au choix, cliquer sur la liste déroulante et choisir l’option Autres formats numériques ou cliquer sur la petite case située à droite de Nombre (plus rapide).

Sélectionnez ensuite la Catégorie Personnalisée. Pour formater vos dates, vous pouvez utiliser les formats suivants :

j jour (le zéro n’est pas représenté).
jj jour (le zéro est représenté)
jjj jour en abrégé (lun, mar, mer…)
jjj jour en entier
m mois (le zéro n’est pas représenté)
mm mois (le zéro est représenté)
mmm mois abrégé (janv, fev…)
mmmm mois entier (janvier, février…)
aa année codée sur deux chiffres
aaaa année codée sur 4 chiffres

Ainsi, le 1/1/05 formaté à l’aide du format personnalisé jjjj j mmmm aaaa donnera comme résultat samedi 1 janvier 2005.

Insérer la date du jour : aujourdhui ou maintenant ?

  • =aujourdhui() insère la date du jour ;
  • =maintenant() insère,  la date du jour et l’heure courante.

Ajouter des jours, des mois ou des années à une date : la fonction Date()

ajouter des jours à une dateRien de plus facile que d’ajouter des jours à une date : il suffit de saisir la date de départ dans une cellule.  Dans une autre, saisissez = puis cliquez sur la cellule de date puis sur + et ajouter le nombre de jours que vous souhaitez. Validez.
Ainsi, si vous ajoutez 20 jours au 15/01/05, vous obtiendrez le 5 février 2005.

Les choses se corsent lorsque l’on souhaite ajouter des mois ou des années.
La solution passe alors par la fonction =Date(annee(madate) ; mois(madate) ; jour(madate)) où madate correspond à l’adresse de la cellule contenant la date de départ ou la date elle-même encadrée de «  ».
Le principe est le suivant : on ajoute (ou on soustrait) à chaque membre de cette fonction le nombre voulu :
ex : pour ajouter 128 jours au 15/01/05 contenu en B5, il faut écrire =date(annee(B5); mois(B5);jour(B5)+128).
.

Calculer le nombre de jours, de mois ou d’années écoulés entre deux dates – la fonction DateDif(date début ; date de fin ; cadence)

Voici une fonction intéressante qui existe depuis plus d’une décennie sous Excel mais que Microsoft s’obstine à ne pas vouloir documenter : DateDif(date début ; date de fin ; cadence) permet de répondre à toute question du type « Combien de jours entre le 21/03/24 et aujourd’hui ? » et de suivre ainsi des échéances.

Voici tout d’abord les valeurs acceptées pour Cadence :

  • « d » : pour obtenir le nombre de jours écoulés ;
  • « m »: pour obtenir le nombre de mois entiers écoulés ;
  • « b » : pour obtenir le nombre d’années entières écoulées ;
  • « yd » : pour obtenir le nombre de jours écoulés, sans tenir compte des années ;
  • « ym » : pour obtenir le nombre de mois écoulés, sans tenir compte des années ;
  • « md » : pour obtenir le nombre de jours écoulés, sans tenir compte des années et des mois.

Pour calculer le nombre de jours écoulés entre la date d’une facture située en B5 et la date du jour, il suffit d’utiliser la syntaxe suivante :

=dateDif(B5;maintenant(); »d »)

Si B5 contient le 14/1/05 et que nous sommes aujourd’hui le 3 février 2005, nous obtiendrons 20 jours.

Autres fonctions intéressantes

Excel propose d’autres fonctions intéressantes parmi lesquelles jour(), mois(), joursem() qui renvoient respectivement, le numéro du jour d’une date, le mois de ladite date et le jour de la semaine (1 pour lundi, 2 pour mardi…).

Exemples :

  • =jour(« 17/04/1992 ») renverra 17 ;
  • =mois(« 17/04/1992 ») renverra 4 ;
  • =joursem(« 17/04/1992 »;1) renverra 6. Le « 1 » indique à Excel que la semaine débute un lundi. Si vous appliquez à la cellule de résultat le format « jjjj », vous verrez que le 6 correspond à un vendredi…

Bien évidemment ces fonctions prennent tout leur sens combinées à d’autres.

Calculer des échéances type xx jours fin de mois : la fonction FIN.MOIS

Votre entreprise accorde un délai de règlement à 45 jours fin de mois. En cellule B5 figure la date d’émission de votre facture (11/01/14).
Dans ce cas de figure, la formule sera donc la suivante : =FIN.MOIS(B5 ; 0) + 45

NB : si vous préférez utiliser la date elle-même dans votre syntaxe plutôt que la référence à sa cellule, placez alors votre date entre guillemets.

Connaître le numéro de semaine correspondant à une date : NO.SEMAINE

Cette fonction prend 2 arguments : la date (ou la référence de la cellule contenant cette dernière) et la méthode, c’est-à-dire le chiffre 2 pour la France où la semaine début un lundi.
La syntaxe sera donc =NO.SEMAINE(B5 ; 2)

Tenir compte des jours ouvrés : nb.jours.ouvres() et serie.jour.ouvre()

Vous avez peut-être l’occasion d’élaborer des plannings pour la réalisation de certaines prestations. Si une tâche nécessite 10 jours entiers de travail, vous devez penser à exclure les week-ends du calcul de votre date d’échéance. nb.jours.ouvres() se charge de ce calcul.

Cette fonction prend les arguments suivants : nb.jours.ouvres(date début; date fin; jours fériés) où jours fériés est une plage de votre feuille de calcul sur laquelle vous aurez pris la précaution de lister les jours fériés au format date. Mieux encore, à cette liste, pour être totalement exhaustive, vous pouvez rajouter les liste, les jours de fermeture spécifiques à votre entreprise…

Dans le même ordre d’idée, si vous souhaitez ajouter un nombre de dates en jours ouvrés, utilisez la fonction serie.jour.ouvre(date début, nombre de jours en plus ou en moins ; plage de dates fériés).

Ainsi, nous sommes le 2 mai 1et vous planifiez une tâche de 10 jours. Si vous n’adoptiez pas la bonne syntaxe, en l’occurence =serie.jour.ouvre(« 2/05/05 »;12;mesdates), vous obtiendriez, bien évidemment le 12 mai au lieu du 17 mai (le 8 mai est férié et cette plage de dates inclut un week-end…).

Aller plus loin : www.admexcel.com/trucdate.htm#form005

Les heures

Saisir une heure ou des minutes

Pour saisir une heure, vous devez impérativement utiliser la syntaxe « hh:mm ».
Ainsi, pour entrer « 11h00 » dans une cellule, vous devrez taper 11:00.
Pour saisir des minutes, par exemple 15 mn, saisissez 0:15.

Additionner des heures

Lorsque l’on souhaite additionner des heures, il convient de prendre la précaution d’appliquer le format personnalisé [hh:mm] pour additionner des heures cumulées au-delà de 24h, faute de quoi vous n’obtiendriez pas le résultat escompté, mais le nombre de minutes écoulées après minuit.

heureVoici un exemple simple :
Le tableau ci-contre montre le résultat obtenu si l’on ajoute 11 minutes à 23h50.
Dans le premier cas de figure, nous obtenons une minute après minuit et, dans le second cas de figure le nombre d’heures cumulé.

Travailler en centième d’heure

Il est parfois utile de raisonner en centième d’heure plutôt qu’en heures entières.

  • pour convertir une heure en centième, multipliez-la par 24 et appliquez à votre cellule un format numérique ;
  • pour convertir des centièmes d’heures en heures, divisez-les par 24 et appliquez à votre cellule un format heure.

Utiliser des heures négatives

Lorsque vous devez, dans vos calculs, utiliser des heures négatives (par exemple, dans le cas d’un temps passé réel sur un projet versus le temps facturé à un client), le plus simple consiste à passer en calendrier 1904 :

  • activez le menu OutilsOptions,
  • dans l’onglet Calcul, cochez la case Calendrier depuis 1904.

Attention ! Si votre classeur comprend également des dates, celles-ci s’incrémenteront automatiquement de 1 462 jours…

Télécharger le fichier d’exercice exos dates

Utiliser des listes déroulantes dans une feuille Excel

La fonction Validation des données permet d’accélérer et contrôler la saisie en permettant à l’utilisateur d’utiliser des listes déroulantes dans une feuille Excel. Ceci permet, non seulement un gain de temps, mais aussi d’éviter des erreurs de saisie.

Prenons un exemple concret et téléchargez le fichier exemple. Le fichier joint récapitule les factures émises et, pour chacune, le délai de règlement accordé. L’utilisateur a le choix entre 4 options. Excel calcule ensuite la date d’échéance correspondant au choix de l’utilisateur.

Pour créer une liste déroulante :

  • choixDans une zone de votre feuille de calcul, tapez les valeurs de votre liste les unes au-dessous des autres. Cette liste peut être placée sur une autre feuille de calcul. Vous pouvez également affecter un nom aux éléments de la liste et l’utiliser comme référence.

  • Sélectionnez les lignes de votre tableau devant contenir la liste déroulante.

  • Activez l’onglet Données et sélectionnez Validation des données.

liste deroulante

  • Cliquez sur l’onglet Options, puis dans le champ Autoriser, sélectionnez Liste. Le champ Source est maintenant accessible. Cliquez dans cette zone puis sélectionnez sur votre feuille de calcul les éléments de votre liste.

Guider l’utilisateur : ajouter un message de saisie

listeSi vous souhaitez ajouter un message à destination de l’utilisateur, activez l’onglet Message de saisie et renseignez les zones comme ci-dessous. Ce message apparaîtra automatiquement lorsque l’utilisateur placera son curseur sur une cellule contenant la liste déroulante :

  • Titre est le texte qui s’affiche en gras, sur la première ligne (dans l’exemple ci-contre Délai de règlement).
  • Message de saisie est le texte d’explication destiné à guider l’utilisateur.

Restreindre plus ou moins les saisies : l’alerte d’erreur

Cet onglet permet d’afficher un message d’erreur à destination de l’utilisateur s’il saisit dans la cellule un élément ne figurant pas dans la liste déroulante.

3 options sont disponibles :

  • Arrêt : l’utilisateur n’a d’autre choix que d’opter pour un élément de la liste.
  • Avertissement : prévient l’utilisateur que le texte saisit n’appartient pas à la liste et lui offre le choix de continuer (la saisie de l’utilisateur est alors validée) ou non. Dans ce cas, il convient de cliquer sur Annuler, ce qui vous évite d’avoir à supprimer le texte saisi avant d’activer à nouveau la liste déroulante (cas si on opte pour Non).
  • Information : similaire à Avertissement mais ne donne que deux choix :
    • Ok pour forcer la saisie
    • Annuler pour revenir à la liste déroulante.

Dans la zone Titre, saisissez un titre pour votre boîte de dialogue (ex : « Erreur » ou « Donnée non valide »).

Dans la zone Message d’erreur, saisissez le texte destiné à aider l’utilisateur.

alerte

Gagner du temps grâce aux fonctions avancées de la recopie

Un sujet sur la recopie ? Cela peut paraître de prime abord étonnant. Ctrl C, Ctrl V, tout le monde connaît. Mais savez-vous qu’il est possible de gagner du temps  grâce aux fonctions avancées de la recopie ?

Recopier une formule simple

Sans doute utilisez-vous la commande Ctrl C Ctrl V pour recopier une formule sur une plage de calcul.

exo3Mais saviez-vous que vous pouviez accomplir cette même action à la souris ? Il suffit pour cela de placer votre curseur dans l’angle inférieur droit de votre cellule (votre curseur prend alors la forme d’une croix). Il vous suffit ensuite d’étirer cette sélection sur la plage de cellules où la formule doit être recopiée.

Il existe bien sûr d’autres solutions plus optimales pour un résultat identique, par exemple, vous pouviez tout à fait sélectionner votre ligne ou colonne de total et cliquer sur ∑, ce qui est encore bien plus rapide !

Ma poignée de recopie a disparu ! Pas de panique. Pour réactiver votre poignée de recopie, activez le menu FichierOptions, Options avancées, et, dans la zone Option d’édition, tout en haut de la fenêtre, cochez la case Activer la poignée de recopie et le glisser-déplacer des cellules.

recopie3
Compléter des séries simples

Saviez-vous que vous pouviez compléter très rapidement un tableau mensuel… en ne tapant que le premier mois ?

Là encore, c’est la fonction recopie qui est mise en oeuvre et elle fonctionne comme la recopie d’une formule.

  • Saisissez votre premier mois (janvier) dans une cellule.
  • Positionnez-vous dans l’angle inférieur droit de cette cellule : votre curseur doit prendre la forme d’une croix.
  • Tirez cette poignée vers la droite : le mois suivant s’affiche sous votre souris.

recopie

  • Continuez à tirer cette poignée jusqu’à ce que vous atteigniez le mois désiré et relâchez : votre série est automatiquement complétée.

Mieux encore : cette astuce fonctionne avec tout type de texte :

  • les mois : il suffit de saisir janvier ou janv
  • les jours : saisissez lundi ou lun
  • mais aussi Cas 1, Semaine 1… bref, tout texte suivi (ou précédé) d’un nombre !

Compléter des séries complexes (recopie incrémentée)

Telle que nous venons de l’étudier, la recopie constitue déjà une aide précieuse à la saisie. Mais cette fonction réserve encore bien d’autres surprises.

Ainsi, si vous souhaitez compléter un tableau qui ne contient qu’un mois sur deux, là encore la recopie incrémentée sera un allié bien utile.

Il suffit de compléter deux cellules avec les valeurs voulues (en l’occurrence Janvier et Mars) pour qu’Excel identifie lesaut (le « pas » dans son jargon) :

  • Tapez janvier dans la première cellule
  • Saisissez mars dans la seconde
  • Sélectionnez ces deux valeurs et tirez la poignée de recopie vers la droite.

recopie2

Cela fonctionne bien évidemment avec les séries mathématiques (par exemple, pour compléter un tableau avec de nombres variant de 5 en 5), les dates (tous les 15 jours), ou tout autre texte, le principe étant de toujours saisir les 2 premiers éléments.

Exemples : pour obtenir une suite « 15, 20, 25 » on saisit « 5, 10 » et on tire la poignée de sélection après avoir mis en surbrillance nos deux premières cellules.

Mieux encore !

Rien ne vous empêche de vous limiter à deux valeurs. Vous pouvez créer des séries « logiques » plus complexes.

Ainsi, saisissez 5 dans une cellule.

  • Dans la 2e multipliez la première cellule par 2
  • Dans la 3e déduisez 2 de la seconde cellule.
  • Sélectionnez les 2 dernières cellules (celles contenant 10 et 8) et tirez la poignée de recopie…

recopie3Pourquoi ne pas sélectionner aussi la 1ère cellule, me direz-vous. Le schéma ci-dessous vous montre ce que l’on obtient si l’on ne sélectionne dans un cas que les cellules orangées et sur la ligne inférieure les 3 première
s cellules.

Dans le premier cas de figure, le contenu de la 4e colonne respecte bien la suite logique. Dans le second, la 4e cellule commence au chiffre suivant 5, soit 6. Nous aurions ensuite 7, 14, 12…

Tous ces exemples vous donnent une idée de la puissance de la recopie incrémentée !

Copier ou déplacer très rapidement une sélection à la souris

Vous connaissez peut-être l’astuce pour déplacer une série de cellules à la souris : il suffit de sélectionner les cellules souhaitées puis de se positionner sur la bordure de la sélection et de tirer cette sélection, à la souris, vers le nouvel emplacement.

… A condition toutefois, que vous ayez activé la recopie incrémentée comme indiqué plus haut !

Mais saviez-vous qu’il est possible de déplacer cette même sélection sur un autre onglet ? Il suffit pour cela, après avoir sélectionné les cellules, de positionner son curseur sur la bordure inférieure de la sélection puis de maintenir la touche Alt enfoncée puis de tirer votre sélection vers l’onglet en question.

Mieux encore. En combinant cette manipulation avec la touche Ctrl, vous dupliquez vos cellules, soit sur la feuille actuelle (Ctrl + sélection), soit vers un autre onglet (Ctrl +Alt + sélection).

i4

Pour récapituler :

  • Alt + sélection : déplacer la sélection vers un autre onglet
  • Ctrl + sélection : copier la sélection sur la feuille
  • Ctrl + Alt + sélection : copier la sélection vers un autre onglet

Ces manipulations étant bien plus rapides que l’emploi des raccourcis clavier, je vous les recommande vivement.