Astuces EXCEL

 

Mettre à jour la taille de la population observée dans le titre d'un graphique dont les données varies :
La cellule A1 contient la taille de la population, =somme(C2:D2)

La cellule A2 va contenir le titre du graphique :
="Titre
N="&A1

Nota : pour aller à ligue dans la cellule A2, il faut appuyer sur les touches "Alt + Entr"

Créer un graphique en écrivant un titre quelconque.

Dans le graphique, cliquer sur le titre, puis cliquer dans la zone fx (fonction) , puis sélectionner la cellule A1 (=Feuil1!$A$2), puis appuyer sur Entrer pour valider la formule.

 

Mettre à jour la conclusion de l'interprétation d'une donnée :
La cellule A1 contient la variation d'une donnée dans le temps.

Dans la cellule A2 écrivez ="La donnée "&SI(A1<0;"baisse";"augmente")&"" de "&ABS(A1)&" points"

Dans le cellule A2 s'affiche : "La donnée baisse de X points" si la donnée varie négativement
                                           "La donnée augmente de Y points" si la donnée varie positivement

 

Afficher / masquer les formules de calculs :
Méthode 1 : touche [Ctrl) + touche [3”#]
Méthode 2 : menu Outils/Options, onglet Affichage, case à cocher Formules

 

Formule expresse :
Sélectionner les cellules désirées, cliquer droit sur la barre d'état (la barre du bas), choisiser la formule voulue parmi la liste qui apparait, le résultat apparait aussitot dans la barre d'état.

 

Transformer une formule en nombre (Copier/Collage spécial valeurs) expresse :
Sélectionner la cellule avec la formule, cliquer dans la barre de formule, appuyer sur la touche F9, puis valider. En cas d'erreur, seul la fonction Annuler peut vous sauver.

 

Protéger un classeur par un mot de passe / protéger l'écriture d'un classeur :
Appeler le menu Enregister sous
Cliquer sur le menu Outils et cliquer sur Options Générales
Entrer un mot de passe :
     - dans la case du haut pour protéger le classeur.
     - dans la case du bas pour protéger en écriture le classeur.
Confirmer votre mot de passe.
Remplisser la boite Enregistrer sous et valider.

 

Fixer les en-tetes dans un tableau (figer les volets)
Si la ligne 1 est un en-tete, sélectionner une cellule sur la ligne 2.
Si la colonne A est un en-tete, sélectionner une cellule de la colonne B.
Si la ligne 1 et la colonne A sont des en-tetes, sélectionner la cellule B2.
Appeler le menu Fenetre et cliquer sur Figer les volets.
Pour annuler, appeler le menu Fenetre et cliquer sur Libérer les volets.

 

Protéger une feuille en laissant quelques cellules dévérouillées
Par défaut toutes les cellules sont vérouillées.
Sélectionner les cellules à dévérouillés.
Appeler le menu Format/Cellule..., onglet Protection, case à cocher Vérouillée décocher.
Appeler le menu Outils/Protection/Proteger la feuille.
Entrer votre mot de passe et confirmer le.

 

Forcer le retour à la ligne dans une cellule
Appuyer sur les touches Alt + Entr

 

Ecrire dans plusieurs cellules la même chose en une seule fois
Sélectionner les cellules devant recevoir le texte (enfoncer la touche Ctrl et sélectionner une à une les cellules).
Ecriver votre texte dans la cellule active.
Valider en enfonçant les touches Ctrl + Entr.

 

Entrer plus rapidement des chiffres qui possèdent tous le même nombre de chiffre après la virgule
Vous devez saisir : 123,45 : Vous écrivez 12345 et Excel transforme automatiquement en 123,45
Appeler le menu Outils/Options, onglet Modification, case à cocher Décimale fixe cocher.
Dans la zone Place indiquer le nombre de chiffre après la virgule (2 correspond aux 2 derniers chiffres après la virgule).
Cliquer sur le bouton OK.

Remarque : Vous devez saisir : 12300 : Vous écrivez 123 et Excel transforme automatiquement en 12300
Dans la zone Place vous indiquez -1, -2, -3, etc pour que le chiffre saissit soit multiplier par 10, 100, 1000, ...

 

Entrer une fraction dans une cellule
Lorsque vous tapez dans une cellule 1/2, Excel ne vous affiche pas votre chiffre sous la forme 1/2 mais en plus il le transforme en date. Pour éviter cela, taper un 0 (zéro) suivi d'un espace et enfin votre fraction.

 

Annuler l'incrémentation
Vous voulez dupliquer des cellules (nombres, dates, ...), mais sans incrémenter leurs valeurs. Enfoncer la touche Ctrl, puis dupliquer les cellules.

 

Traduire du texte en nombres
Lors d'importation de donnée dans Excel depuis un autre tableur, les nombres sont parfois reconnus comme du texte.
Dans une cellule voisine, écriver =CNUM(cellulecible).
Le texte sera reconnu comme un nombre.
Utiliser l'astuce "transformer une formule en nombre (Copier/Collage spécial valeurs) expresse" pour finir.

 
Traduire des nombres en textes
Dans une cellule voisine, écriver =TEXTE(cellulecible;0). Le nombre sera reconnu comme du texte. Utiliser l'astuce "transformer une formule en nombre (Copier/Collage spécial valeurs) expresse" pour finir.
Remarque : pour que le texte s'affiche avec "N°" devant la référence et que la référence ait le séparateur des milliers, écriver =TEXTE(cellulecible;"N° # ##0")
 

MOYENNE.SI
Les fonctions NB.SI, SOMME.SI existent mais pas MOYENNE.SI, ni ECARTYPE.SI, ni .....
Pour les réaliser, écriver =MOYENNE(SI(plage="critère";somme_plage)) et valider la formule en appuyant surr les touches Ctrl + Maj + Entr. Remplacer MOYENNE par la fonction désirée.

 

Donner un nom aux lignes et aux colonnes d'un tableau
Sélectionner le tableau. Appeler le menu Insertion / Nom / Créer.
Valider les propositions d'Excel.
Les lignes et les colonnes sont nommées.

 

Bouton pour fusionner les cellules
Appeler menu Outils/Personnaliser..., onglet Commandes, catégorie Format, commandes Fusionner.
Déplacer la commande dans la barre outils.

 

Remplacer les barres d'un histogramme par une image
Copier une image dans le presse papier. Cliquer sur une série de donnée. Coller l'image du presse papier.

Remarque : par défaut, l'image est étirée, si vous préférez qu'elle soit empilée : cliquer sur une série de donnée, appeler le menu Format/Séries de données sélectionnée..., onglet Motifs, bouton Motifs et textures, onglet Image, format Empilée.

 

Pour se déplacer plus vite dans la feuille, dans le classeur, pour faire un lien entre un graphique et des cellules, ... (lien hypertexte)
Le texte se trouvant dans une cellule, une case de texte (dessiner), un rectangle (dessiner) peuvent servir de lien hypertexte. Sélectionner l'élément désiré, cliquer droit, menu contextuel Hypertexte.

 

Imprimer deux tableaux très dissemblables l'un de l'autre quant aux dimensions de leurs colonnes
Réaliser vos deux tableaux sur des feuilles différentes (dans des classeurs différents) (l'un à droite de l'autre).
Appeler menu Outils/Personnaliser..., onglet Commandes, catégorie Outils, commandes Photo.
Déplacer la commande dans la barre outils.
Sélectioner le deuxième tableau et cliquer sur le bouton Photo.
Sélectionner une cellule en dessous du premier tableau (la photo du deuxième tableau apparait).
Appuyer dessus avec le bouton gauche de votre souris et glissez pour le positionner comme vous le désirez.

 

Filtrer les lignes automatiquement
Sélectioner la ligne d'en-tête du tableau.
Appeler le menu Données / Filtres /Filtre automatique.
Des flèches apparaissent sur la ligne d'en-tête.
Cliquer sur la flèche de votre choix et choisissez votre critère.

 

Mettre en valeurs les sous-totaux d'un tableau
Sélectionner le tableau avec ses sous-totaux.
Appeler le menu Données / Grouper et créer un plan / Options...
Cocher la case à cocher Styles automatiques.
Cliquer sur le bouton "Apliqquer les styles".
Appeler menu Format / Style...
Modifier le Motifs des sous-totaux.

 

Calcul d'une fonction uniquement sur les lignes visibles (après filtrage)
En bas de votre colonne écriver =SOUS.TOTAL(N°fonction;plage)
Le calcul de la fonction ne se fait que sur les lignes visibles.

N° fonction Fonction
1 MOYENNE
2 NB
3 NBVAL
4 MAX
5 MIN
6 PRODUIT
7 ECARTYPE
8 ECARTYPEP
9 SOMME
10 VAR
11 VAR.P
 

Calculer une fonction (SOMME, MOYENNE,...) dans un tableau qui s'agrandit par le bas
Placer votre fonction sur une autre feuille et sélectionner comme plage de données la colonne (vous pouvez aussi donner des noms aux colonnes).

 

Calculer des heures négatives
Placer le temps théorique en A1 et le temps effectué en B1.
En C1 écrivez : =SI(A1>=B1;A1-B1;CONCATENER("- ";HEURE(B1-A1);":";MINUTE(B1-A1)))
Appliquez un format d'heure en C1 pour avoir dans tous les cas un affichage de type 0:00.

Calculer des heures négatives au-delà de 24 heures
Placer le temps théorique en A2 et le temps effectué en B2.
En C2écrivez : =SI(A2>=B2;TEXTE (A2-B2;"[h]:mm");TEXTE(B2-A2;"-[h]:mm"))
Appliquez un format [h]:mm aux cellules en A2, B2, C2 pour avoir dans tous les cas un affichage de type 0:00.

 

Désactiver la saisie semi-automatique
Appeler le menu Outils / Option / Modification.
Décocher la case Saisie semi-automatique des valeurs de cellule.

 

Numéroter les lignes non vide
Placer la numérotation dans la colonne A et les informations à numéroter dans la colonne B.
En A1 écrivez : =SI(B1<>"";NBVAL($B$1:B1);"")
Dupliquer la formule jusqu'en bas de votre tableau.

 
A
B
1
1
Pierre
2
2
Paul
3
 
 
4
3
Jacques
5
 
 
6
4
Henri