Aide sur Excel IV
Imprimer la pageCalculer la différence entre deux dates avec DATEDIF (fonction)
Normalement, un délai se calcule par soustraction :
Date – Date = Délai (en jours)
Il peut cependant être très utile, pour présenter les résultats en années, mois, jours, de connaître la fonction cachée et non documentée : =DATEDIF ( Date1 ; Date2 ; Intervalle )
dans laquelle Date1 >= Date2
et dans laquelle l’argument intervalle peut être :
"y" : différence en années
"m" : différence en mois
"d" : différence en jours
"ym" : différence en mois, une fois les années soustraites
"yd" : différence en jours, une fois les années soustraites
"md" : différence en jours, une fois les années et les mois soustraits
Exemple : Âge en années simples :
=DATEDIF(A1 ;B1;"y") & SI(DATEDIF(A1 ;B1;"y")>1 ;" ans" ;" an")
Âge en années et mois :
=DATEDIF(A1 ;B1;"y") & SI(DATEDIF(A1 ;B1;"y")>1 ;" ans, " ;" an, ") & DATEDIF(A1 ;B1;"ym") & " mois"
Extraire le jour d’une date (saisie ou calculée) : le chiffre du jour (pas son nom), le quantième du jour (fonction)
JOUR(date)
Extraire le mois d’une date (saisie ou calculée) : le chiffre du mois (pas son nom), le quantième du mois (fonction)
MOIS(date)
Extraire l’année d’une date (saisie ou calculée) (fonction)
ANNEE(date)
Recomposer une date valide avec des fragments (constants ou calculés)
DATE(année ;mois;jour)
Calculer une date décalée de x mois par rapport à une date de référence
MOIS.DECALER( date_réf ; nb_mois_de_décalage )
On peut spécifier un nb de mois négatif pour revenir dans le temps... Nota : si cette fonction rend un message d’erreur #NOM ?, installer et charger la macro complémentaire Utilitaire d’analyse.
Calculer la date correspondant à la fin d’un mois
FIN.MOIS( date_réf ; nb_mois_de_décalage )
On peut spécifier un nb de mois négatif pour revenir dans le temps...
Nota : si cette fonction rend un message d’erreur #NOM ?, installer et charger la macro complémentaire Utilitaire d’analyse.
Extraire d’une date un élément et recomposer une date de toutes pièces (fonction)
JOUR(datespécifiée) extrait le chiffre du jour d’une date spécifiée
MOIS(datespécifiée) en extrait le chiffre du mois
ANNEE(datespécifiée) en extrait l’année
DATE(annéespécifiée ; moisspécifié ; jourspécifié ) recompose une date par morceaux ;
Exemple, pour calculer la prochaine date anniversaire d’une date donnée (le premier du mois en cours, mais l’an prochain) :
=DATE( ANNEE(datedonnée) +1 ; MOIS(datedonnée) ; 1 )
Repérer les jours ouvrés (ou pas) dans une suite de dates (calendrier ou planning…) (fonction) Mettre en rouge les dimanches et jours de fêtes par exemple…
Prévoir une ligne ou une colonne supplémentaire le long de la série des dates, et saisir une formule qui teste chaque date, grâce à la fonction NB.JOURS.OUVRES(date_début ;date_fin ;jours_fériés) en spécifiant la même date en début et en fin…
De cette façon, la formule rend 1 si la date est ouvrée, et rend 0 si le jour est férié...
Penser à saisir les jours fériés de la période dans une petite table à part, nommer cette table et indiquer ce nom en 3ème argument de la fonction...
Nota : cette fonction peut être utilisée dans la commande Format, Mise en forme conditionnelle, en appliquant la fonction à la cellule en cours pour mettre en rouge les dimanches et les jours de fêtes, par exemple. Puis recopier ce format sur toutes les dates...
Nota : si cette fonction rend un message d’erreur #NOM ?, installer et charger la macro complémentaire Utilitaire d’analyse.
Créer rapidement une suite numérique : 1 2 3 4… (procédure)
Si le pas est 1, taper le premier chiffre, puis CTRL + glisser sur la poignée de recopie (carré noir en bas à droite de la cellule, carré sur lequel le pointeur de la souris devient une croix noire).
Si le pas est différent (pour avancer de 10 en 10, par exemple, ou de 7 jours en 7 jours), taper les deux premières valeurs (10 et 20, par exemple, ou un lundi et le lundi suivant), sélectionner ces deux valeurs (avec le pointeur grosse croix blanche), puis glisser sur la poignée de recopie (carré noir en bas à droite de la cellule) Cette technique fonctionne très bien sur les dates : construire un calendrier avec Excel est enfantin !
Créer rapidement une suite de type texte : lundi mardi mercredi… janvier février mars… Région1, Région2… (procédure)
Taper une des valeurs de la liste (mardi, par exemple, ou avril, ou Région 1), puis glisser sur la poignée de recopie incrémentée (carré noir en bas à droite de la sélection).
Créer une série personnalisée pour permettre ensuite la saisie rapide de cette série par recopie incrémentée. (procédure)
Taper les valeurs de la série dans un groupe de cellules, sélectionner ces cellules, et lancer la commande Outils, Options, onglet Liste pers., bouton Importer…
Convertir une formule en valeur figée (résultat) (clavier)
Sélectionner la formule, puis F2 pour entrer en mode Correction dans la formule,
puis F9 pour Évaluer la formule, puis ENTRÉE pour valider la transformation
(ou ÉCHAP, Esc pour abandonner la correction et ne pas valider la transformation).
Convertir une plage de formules en valeurs figées (résultats) (menu)
Sélectionner la plage de cellules (il peut y avoir des cellules vides ou non calculées dans la plage, bien sûr), copier cette sélection au presse-papiers avec CTRL + C, puis lancer la commande Edition, Collage spécial, Coller Valeurs…
N’additionner que les cellules qui correspondent à une condition (fonction de somme conditionnelle)
SOMME.SI(zone à tester ; test ; zone à sommer)
Il est très commode de pouvoir tester sur une colonne et sommer sur une autre, La somme n’a lieu que si la condition (test) est réalisée sur la première colonne.
• Il faut désigner deux groupes qui correspondent bien l’un à l’autre (pas forcément dans la même feuille !) : même longueur et correspondance parfaite en fonction de l’ordre d’apparition. Les constantes doivent figurer entre guillemets.
Exemple : =SOMME.SI(B :B ;">10" ;D :D) pour tester la colonne B (quantités livrées) et sommer les cellules correspondantes de la colonne D (montant HT).
• On peut sommer la colonne que l’on teste : on n’utilise alors que deux arguments, le troisième est simplement omis. Par exemple, pour sommer tous les montants (colonne D) qui dépassent 1000, on créera une cellule qui contient =SOMME.SI(D :D ;">1000") sans utiliser le troisième argument.
• Sauf astuce, on ne peut décrire (deuxième argument) qu’une seule condition à la fois.
• Si la condition est une simple égalité, on peut se dispenser du signe égal. Par exemple, pour totaliser le CA du client Dugenou, on peut prévoir la formule
=SOMME.SI(A :A ;"Dugenou" ;D :D) (la colonne A contient le nom des clients et la colonne D les CA). En fait, souvent, on ne tapera pas la constante "Dugenou" mais plutôt l’adresse (sans guillemets) de la cellule qui peut contenir "Dugenou", mais qui peut aussi contenir n’importe quel autre nom de client : =SOMME.SI(A :A ;T3;D :D)
• Si l’on veut comparer à ce que contient une cellule de référence, il faut utiliser une esperluette & pour assembler une partie constante (l’opérateur de comparaison) et une adresse de cellule qui contient la partie variable.
Par exemple : =SOMME.SI(A :A ;">="&$A$1 ;D :D)
Voir aussi NB.SI.
N’additionner ou ne dénombrer que les cellules qui correspondent à plusieurs conditions (fonction de somme et de dénombrement conditionnelle)
SOMME.SI(zone à tester ; test ; zone à sommer) On peut sommer la colonne que l’on teste : on n’utilise alors que deux arguments, le troisième est simplement omis.
On ne peut décrire (deuxième argument) qu’une seule condition à la fois. C’est quelquefois très pénalisant, mais on peut utiliser une astuce pour éviter cet écueil : pour calculer le CA de Davolio au mois de mars (3), par exemple, on ajoute une colonne qui concatène (avec une esperluette &) les deux colonnes à tester, et c’est cette colonne qu’on teste : exemple : SOMME.SI ( colonneconcaténée ; "Davolio3" ; colonneCA )... en remplaçant "Davolio3", bien sûr, par une formule qui, elle aussi par concaténation, fabrique cette expression : $A2&D$1 (si la colonne 1 contient les noms des vendeurs et si la ligne 1 contient le numéro du mois. On peut aussi ensuite masquer la colonne concaténée pour qu’elle ne perturbe personne. Voir aussi NB.SI.
Ne dénombrer que les cellules qui… (condition) (fonction)
NB.SI(zone à tester et à dénombrer ; condition)
Malheureusement, on ne peut pas, comme on peut le faire avec SOMME.SI, tester une colonne et compter les valeurs dans une autre : on dénombre forcément la colonne même qu’on teste.
De plus, on ne peut décrire (deuxième argument) qu’une seule condition à la fois. C’est quelquefois pénalisant, mais on peut utiliser une astuce pour éviter cet écueil : pour dénombrer les ventes de Davolio au mois de mars (3), par exemple, on ajoute une colonne qui concatène (avec &) les deux colonnes à tester, et c’est cette colonne qu’on teste :
Exemple : NB.SI ( colonneconcaténée ; "Davolio3" ) ...en remplaçant "Davolio3", bien sûr, par une formule qui, elle aussi par concaténation, fabrique cette expression. (on peut aussi ensuite masquer la colonne concaténée pour qu’elle ne perturbe personne). Voir aussi SOMME.SI.
Enfin, quand la fonction NB.SI doit comparer ce qu’elle dénombre à une cellule de référence, il faut mettre le comparateur entre guillemets, mais pas l’adresse de la cellule de référence, et assembler (concaténer) les deux informations avec une esperluette &.
Par exemple, si on veut dénombrer les cellules d’une plage A2 :A200 qui soient supérieures à la valeur de la cellule B8, il faudra prévoir
=NB.SI(A2 :A200 ;">"&B8)
Compter les cellules vides d’un zone
NB.VIDE(plage)
Ne dénombrer que les cellules d’une certaine couleur de fond
Problème : Excel ne sait pas compter les cellules colorées.
Idée : Mais il sait compter les valeurs (les 1, les 2, les 3…)
Astuce : si on arrive à saisir des valeurs et à les afficher sous forme de couleurs, c’est gagné ! (Voir illustrations et explications détaillées dans le manuel Excel.)
1. Sélectionnez la zone des bandeaux de couleur puis lancez la commande Données Validation pour contrôler la saisie : n’autorisez que la Liste 1 ;2;3 : tapez cette liste avec des points virgules dans le champ Source. Pas besoin de spécifier le cas cellule vide, toujours possible. Prévoyez un message clair en cas d’erreur de saisie.
2. Donnez un format conditionnel à toute la zone des bandeaux : Format, Mise en forme conditionnelle : si la cellule vaut 1, alors le format de la police est rouge sur motif rouge, ton sur ton… le 1 devient donc invisible dans une cellule toute rouge !
« Ajoutez » le cas où la valeur 2 donne le format bleu sur fond bleu, et le 3 donne jaune sur fond jaune… Testez quelques saisies.
3. Testez la technique astucieuse de saisie groupée :
sélectionnez plusieurs cellules, tapez le chiffre 2, puis tapez Ctrl Entrée.
Recommencez sur la même sélection avec 3.
Videz avec Suppr.
4. Utilisez la fonction NB.SI pour compter les 1, les 2, les 3…
La syntaxe est (sans espace) : =NB.SI(zone à tester et à dénombrer ; test à effectuer)
Exemple du nb de congés pris, affiché en O4 : il faut compter le nombre de 2 dans la colonne O, en commençant à la ligne 8. O4 =NB.SI(O$8 :O$272 ;2).
Associer des conditions : ET OU (fonction)
ET(condition1 ; condition2 ; conditions 3…)
OU(condition1 ; condition2 ; conditions 3…)
Dénombrer les cellules d’une plage comprises entre deux bornes (mini et maxi)
La difficulté de dénombrer en fonction de deux valeurs vient de ce que la fonction NB.SI n’accepte qu’une seule condition (un seul test). Voici quelques moyens de sauter l’obstacle. Pour compter, par exemple, les cellules d’une plage (A2 :A200) comprises entre 60 et 100 :
• première technique (simple et astucieuse) : soustraire le nombre de cellules inférieures à la borne basse du nombre de cellules inférieures à la borne haute : =NB.SI(A2:200 ;"<100")-NB.SI(A2:200 ;"<60)
• Deuxième technique (astucieuse également) : créer une colonne (éventuellement cachée par la suite) qui contienne pour chaque ligne une série de tests (plusieurs conditions assemblées) qui rendent soit 1, soit 0 : Z2=SI(ET(A2<100 ;A2>=60) ;1;0), formule recopiée jusqu’en Z200. Puis simplement prévoir une somme sur cette colonne =SOMME(Z2 :Z200), qui fonctionne même si la colonne Z est cachée. • troisième astuce (terriblement astucieuse !) : utiliser une fonction SOMME dans une formule matricielle (validée avec Ctrl Maj Entrée) et multiplier les différentes conditions (la multiplication simule une fonction ET, interdite dans les formules matricielles) : =SOMME((A2 :A200>=60)*(A2 :A200<100))
Ne pas taper les accolades : valider la formule avec Ctrl Maj Entrée au lieu de Entrée... Pour comprendre l’astuce : l’expression (A2>=60) sous-entend l’utilisation d’une fonction SI une telle fonction vaut VRAI (1 en cas de calcul) si la condition se réalise, et FAUX (zéro si on l’utilise dans un calcul, ici la multiplication) sinon, ce qui permet, en sommant les 1, de dénombrer les occurrences.
Pour des explications infinies sur ces points, voir l’excellent site de Laurent Longres : http://longre.free.fr.
Afficher la valeur de la dernière cellule non vide d’une zone
Parfois, on a besoin d’isoler la dernière valeur en date dans une colonne. Laurent Longre (http://longre.free.fr) est vraiment une mine inépuisable d’astuces :
Soit une colonne A partiellement saisie, sans jamais dépasser la ligne 1000.
• Si des cellules vides peuvent s’intercaler entre les valeurs saisies de la colonne, il faut utiliser cette formule :
=INDEX(A1 :A1000 ;MAX(SI(A1:1000<>"" ;LIGNE(A1 :A1000))))
... formule matricielle, à valider avec CTRL+MAJ+ENTRÉE.
• Si aucune cellule vide n’est possible dans la plage saisie, c’est plus simple :
=INDEX(A :A ;NBVAL(A :A))
Assembler des textes et des chiffres (calculés ou pas) (fonction)
Utiliser l’opérateur & (esperluette) pour assembler des textes (textes constants entre guillemets ou adresses de cellules, ou formules de calculs.
Exemple : =CodePostal & " " & Ville (CodePostal et Ville étant des noms donnés à des colonnes)
Autre exemple : ="PLANNING DES CONGÉS " & ANNEE(P$8) & " – " & ANNEE(P$8) + 1
On a souvent besoin de titres "intelligents", des étiquettes qui s’adaptent toutes seules à une situation variable, sans avoir besoin d’y toucher : plus d’oublis, fiabilité, gain de temps, simplicité d’emploi…
La concaténation est un assemblage de textes constants (qui ne changent pas) avec des mentions variables. Le titre est alors, non plus un simple texte saisi, inerte, mais une formule de calcul, dynamique donc, qui affiche bout à bout des fragments divers.
La concaténation fait appel à l’opérateur & nommé "esperluette" dont la touche est placée sur le clavier au-dessus du A.
La commande Format, elle aussi, permet d’ajouter des textes constants autour de la valeur de la cellule (voir AMI aux mots "titres format personnalisé").
• Le premier avantage de la concaténation sur le Format est la possibilité d’assembler plusieurs valeurs calculées avec des textes constants.
• L’autre avantage de la concaténation sur le Format est la possibilité de prévoir dans le titre des sauts à la ligne, avec ALT + Entrée, ce que ne permet pas de faire Format.
Utiliser les fonctions financières les plus courantes : VA, VC, VPM, NPM, TAUX (fonction)
VA = valeur actuelle
VC = valeur future
VPM = montant des remboursements
NPM = nombre des remboursements
TAUX = taux de l’emprunt
TAUX ( NPM ; VPM ; VA ; VC ; type ; estimation ) Quel est le taux d’un emprunt ?
VA ( TAUX ; NPM ; VPM ; VC ; type ) Quelle est la valeur actuelle d’un investissement ?
VC ( TAUX ; NPM ; VPM ; VA ; type ) Combien vaudra mon épargne ? VPM ( TAUX ; NPM ; VA ; VC ; type ) Combien faudra-t-il rembourser à chaque période ?
NPM ( TAUX ; VPM ; VA ; VC ; type ) Combien faut-il prévoir de temps pour rembourser ?
Calculer le taux équivalent à partir du taux effectif
Le taux mensuel utilisé par les banquiers, appelé taux "effectif", consiste à simplement diviser le taux annuel par 12, ce qui avantage sensiblement le prêteur (le banquier). Un taux annuel de 12% donne ainsi un taux mensuel de 1%. Or si on ajoute ces 12 fois 1%, en capitalisant les intérêts, on obtient un coût du crédit supérieur à 12% sur l’année. C’est pour ça que les banquiers préfèrent cette méthode, ce n’est pas pour nous simplifier les calculs... :o)
Le véritable taux mensuel, celui qui permet de retomber sur un taux annuel en capitalisant les intérêts, s’appelle le taux "équivalent".
Avec Excel, on le calcule ainsi : =TAUX.NOMINAL(taux annuel ;12)
Si cette fonction n’est pas disponible, exécutez le programme d’installation pour installer la macro complémentaire Utilitaire d’analyse, puis activez cette dernière à l’aide de la commande Macros complémentaires du menu Outils.
Éviter les messages d’erreur (fonction)
Pour éviter les messages d’erreur, il faut repérer que la formule "vaut" une erreur AVANT de l’afficher : on utilise les fonctions SI(condition ; valeur si vraie ; valeur si fausse) et ESTERREUR(formule à tester).
On glisse la fonction ESTERREUR(formule) à l’intérieur d’un SI pour repérer l’erreur et réagir.
Exemple :
=SI(ESTERREUR(RECHERCHEV(Code ;Table;2 ;FAUX)) ;"" ;RECHERCHEV(Code ;Table;2 ;FAUX)) On remarque qu’on doit donc taper deux fois la même formule : une fois pour vérifier qu’elle ne rend pas d’erreur, et une autre fois pour donner sa valeur à la cellule en cours. Mais avec le presse-papiers (CTRL+C, CTRL+V), c’est une formalité, bien sûr...
Donner une place (un rang, un mérite) à une cellule parmi un groupe de cellules de même nature (fonction)
RANG(cellule à classer ; groupe de cellules de référence ; ordre)
Si l’ordre n’est pas spécifié (ou égale 0), le rang 1 est donné au plus grand. Pour donner le rang 1 au plus petit, il faut spécifier une valeur non nulle en troisième argument, 1 par exemple).
La principale difficulté, ici, est de penser à figer (avec des dollars $, touche F4) l’adresse du groupe de référence, pour que la recopie ne détruise pas la formule…
Exemple, pour classer les étudiants en fonction de leur note sur 20 (rang 1 = note la plus grande) : D5 =RANG(C5 ; C$5 :C$25)
Si on recopie vers le bas, on n’a pas besoin de figer la colonne (C) qui ne changera pas, de toutes façons, mais il faut bien penser à figer les lignes (5 et 25) qui permettent de définir le groupe de notes de référence.
Voir aussi MIN et MAXpour isoler le plus petit et le plus grand d’un groupe,
Voir aussi PETITE.VALEUR(plage ;rang) et GRANDE.VALEUR(plage ;rang) pour repérer le Nième élément dans une liste.
Trouver la Nième valeur d’une plage
PETITE.VALEUR(plage ;rang) et GRANDE.VALEUR(plage ;rang) permettent de localiser le Nième élément d’une liste, même désordonnée.
C’est idéal pour calculer un hit parade, best of, ou autre top ten…
Si on veut les trois premiers d’un ensemble, il faut prévoir trois cellules, trois formules : une qui spécifie 1 pour l’argument Rang, l’autre qui utilise 2 et la troisième qui utilise 3. Attention à prévoir des dollars dans l’adresse de la plage de référence si vous voulez recopier la formule.
Exemple astucieux d’application : utiliser une fonction PETITE.VALEUR dans un format conditionnel permet de mettre automatiquement en bleu foncé le premier, en bleu vif le deuxième et en bleu clair le troisième : sélectionnez la plage entière comprenant la colonne à tester (par exemple, sélectionnez A1 :D100), lancez Format Mise en forme conditionnelle, précisez "La formule est" puis tapez =$C1=grande.valeur($C$1 :$C$100 ;1) (si c’est la colonne C qui doit servir à mettre toute la ligne en couleur quand la colonne contient le plus grand nombre. Le dollar devant le C sert à figer ce C pour que TOUTES les cellules, et pas seulement A1 qui est la cellule active (blanche), se réfèrent à cette colonne. Par contre, il n’y a pas de dollar devant le 1 pour que la cellule comparée soit celle de la ligne en cours (C1, C2, C3...). Pensez à changer le format quand la condition se réalise : motif (couleur de fond de la cellule) bleu foncé par exemple.
Puis ajouter la condition =$C1=grande.valeur($C$1 :$C$100 ;2) et le format bleu clair, etc. Testez ensuite en tapant des valeurs dans la colonne C.
Remarque : vous pouvez donner de tels formats conditionnels complexes à des colonnes entières sans ralentir le classeur, Excel est vraiment malin et rapide.
Extraire des fragments de texte à partir de cellules (fonction)
Les principales fonctions texte et leur syntaxe sont :
DROITE ( texte ; nbcar )
GAUCHE ( texte ; nbcar )
NBCAR ( texte )
STXT ( texte ; numdépart ; nbcar )
TEXTE ( nombre ; format entre guillemets )
CHERCHE ( texte_cherché ; texte ; no_départ ) renvoie le numéro du car où le texte a été trouvé
MINUSCULE ( texte )
MAJUSCULE ( texte )
NOMPROPRE ( texte )
Une astuce permet de ventiler, séparer, répartir, des valeurs (séparées par des espaces ou un autre signe séparateur) sur plusieurs cellules. Par exemple, si chaque cellule d’une colonne contient à la fois un prénom, un espace, un nom, un espace et une date de naissance, et que l’on veut décomposer cette colonne "agrégée" en trois colonnes "élémentaires". L’astuce consiste à enregistrer la feuille au format TXT, à la fermer et à ouvrir avec Excel le fichier TXT : cela lance un Assistant qui sait bien répartir les données sur plusieurs cellules.
Chercher et trouver dans des colonnes éloignées (éventuellement dans des feuilles différentes !), même en désordre : INDEX et EQUIV (fonction)
En gros, EQUIV retrouve une valeur connue dans un vecteur (ligne ou colonne) et rend un rang (un numéro dans la série de cellules alignées)… alors que INDEX est capable d’utiliser ce rang pour trouver la cellule équivalente (de même rang) dans un autre vecteur (ligne ou colonne organisée de la même façon que le 1er vecteur). Cette combinaison permet de chercher dans une feuille et de trouver dans une autre feuille, même dans un autre classeur si on veut ! On n’a plus la contrainte de la table unique, avec sa 1ère colonne qui est la seule colonne de recherche possible !
EQUIV ( valeur_cherchée ; zone_de_recherche ; type )
permet de localiser (par un rang) une valeur dans une ligne ou une colonne.
Si type=0, EQUIV s’arrête sur la première occurrence trouvée : le vecteur peut être en désordre.
Si type = 1, il faut que le vecteur soit en ordre croissant. (Type = -1, vecteur trié décroissant).
INDEX ( zone_d’extraction ; indice_Ligne ; indice_Colonne ) permet de piocher dans un tableau grâce à 2 indices numériques (un seul si le tableau n’a qu’une dimension).
La syntaxe pour associer ces deux précieuses fonctions est :
INDEX ( vecteur_valeur_à_rendre ;
EQUIV ( valeur_connue ; vecteur_de_recherche ) )
Exemple : INDEX ( SalairesNets ;
EQUIV ( NomSalariéSaisi ; NomsSalariés ; 0 ) )
On cherche et on trouve dans des colonnes placées où l’on veut : pas de contrainte « Table et 1ère colonne »)…
Dans une colonne de table, créer un identifiant de ligne par concaténation de plusieurs colonnes, pour ensuite bien repérer la ligne avec EQUIV et récupérer telle ou telle colonne avec INDEX
Ajouter une colonne à la table, créer une formule de concaténation avec l’opérateur & (esperluette).
Ce résultat, si les colonnes concaténées (assemblées) ont été bien choisies, pourra servir à EQUIV à localiser la ligne à coup sûr dans la table et rendre son rang. Ce bloc EQUIV rendant un rang pourra donc être placé dans une fonction INDEX pour extraire une valeur de la même ligne mais sur une autre colonne...
Exemple : soit une table de relevé des compteurs, table unique pour plusieurs compteurs et plusieurs années. Une ligne par relevé. Une colonne A "Date du relevé", une colonne B "Code compteur", une colonne C "Nombre relevé".
Pour calculer la consommation depuis le dernier relevé (du compteur en cours), il faut localiser ce dernier relevé, en ignorant les autres compteurs... Pas facile...
L’astuce consiste à créer une colonne D (éventuellement cachée) qui calcule pour chaque relevé, un identifiant (sans doublons) en associant le compteur à un rang calculé par le nombre d’apparition du compteur depuis le début.
D2 =B2&NB.SI(B$2 :B2 ;B2)
Notez le dollar devant le 2 de la seule adresse de début de plage. Cette première bonne
astuce permet, en recopiant la formule dans toute la colonne, de dénombrer uniquement vers le haut, sans être pollué par les occurrences apparues plus tard (plus bas) !
Le fait que l’identifiant soit calculable permet à une fonction EQUIV de le calculer et de le rechercher, même si la colonne de recherche (les identifiants, col. D) est en désordre :
E2 =EQUIV(B2&NB.SI(B$2 :B2 ;B2)-1 ;D :D ;0)
Notez le "moins 1" qui isole "le précédent".
Le 0 sert à trouver même dans un vecteur en désordre.
Un fonction INDEX peut alors utiliser le rang rendu par EQUIV pour isoler le nombre relevé ce jour-là : E2=INDEX(C :C ;EQUIV(B2&NB.SI(B$2 :B2 ;B2)-1 ;D :D ;0))
Et par soustraction (Relevé de ce mois moins Relevé précédent), on peut déduire la consommation (et le prix à facturer, etc.).
Notez que cette formule ne fonctionne pas pour la première ligne du compteur car elle n’a pas de "relevé précédent" : message #NA ! à faire disparaître avec une fonction SI.
Créer une formule utilisant les valeurs d’autres feuilles ou classeurs (fonction)
Pendant la frappe de la formule (commençant par le signe égal =), cliquer directement sur une autre feuille, ou utiliser le menu Fenêtre pour changer de classeur... Excel va placer le nom de la source externe entre apostrophes et particulièrement le nom éventuel d’un classeur entre crochets (ALTGR parenthèses).
Il va aussi séparer la feuille de la cellule par un point d’exclamation ! Exemples :
=’Tables’ !$H$586
=’[Références]Tables’ !$B$6
Créer des formules 3D (à travers de nombreuses feuilles) (fonction)
Une feuille est présentée en deux dimensions (2D) : lignes et colonnes.
En utilisant plusieurs feuilles, on accède à une troisième dimension, à condition de savoir rédiger des formules qui utilisent les données d’autres feuilles.
Quand une même formule doit additionner des valeurs de nombreuses feuilles, on peut ne désigner explicitement que les feuilles extrêmes en les reliant avec un opérateur jusqu’à (signe deux-points : ).
Exemple : =SOMME(Janvier :Décembre !C3)
Cette formule est ensuite recopiable vers le bas, puis vers la droite, si c’est toute une zone qu’il faut consolider.
Donner un NOM isolé à une ou plusieurs cellules (menu)
Sélection des cellules à nommer, puis Insertion, Nom, Définir… frappe du nom (pas d’espace !) et Entrée.
Donner un NOM isolé à une ou plusieurs cellules (souris)
• Sélection des cellules à nommer,
• puis un seul clic sur la zone Nom
(en haut et à gauche de la barre de formule),
• puis, frappe du nom (pas d’espace !) et Entrée…
Pensez à bien RELIRE les noms juste après les avoir créés !
C’est essentiel !
Donner rapidement des NOMS correspondant aux titres des colonnes d’un tableau
D’abord, bien prendre garde à ce que les titres des colonnes soient TOUS dans une seule cellule (verticalement) : pas de titre sur plusieurs lignes !!!
Sélection du tableau, en incluant dans la sélection la ligne (unique) des titres, puis Insertion, Nom, Créer… Vérifier que "Ligne du haut" est bien cochée.
Contrôler un nom (relire) : vérifier la plage de cellules qu’il désigne (menu)
Insertion, Nom, Définir… sélection du nom à contrôler et étude de la plage spécifiée dans le champ Fait référence à…
Contrôler un nom (relire) : vérifier la plage de cellules qu’il désigne (souris)
Afficher la liste des noms (clic sur la flèche bas située à droite de la zone Nom, en haut et à gauche de la barre de formule), éventuellement taper une ou plusieurs initiales pour s’approcher rapidement du nom voulu, puis simplement cliquer sur le nom à contrôler : Excel met en surbrillance les cellules nommées. Pour les examiner sans perdre cette sélection, il vaut mieux se déplacer avec les ascenseurs...
Supprimer un nom de cellule
Insertion, Nom, Définir… sélection du nom à supprimer avec la main droite (clic souris), et ALT + S avec la main gauche (utilisation du clavier pour cliquer sur le bouton Supprimer)… Cette répartition des tâches permet d’en supprimer beaucoup très vite !
Modifier le nom porté par une plage de cellules
Insertion, Nom, Définir… sélection du nom voulu, correction du nom, bouton Ajouter (le nouveau nom est créé)…
Il faut penser ensuite (dans la même commande) à supprimer le vieux nom erroné : sélection de l’ancien nom, et clic sur le bouton Supprimer…
Modifier la plage de cellules référencée par un nom
Insertion, Nom, Définir… sélection du nom voulu et correction de la plage spécifiée dans le champ Fait référence à… puis bouton Ajouter...