Formation Excel

Excel - Formation interne

Fonctionnalités les plus utilisées Sydel du Pays Coeur d'Hérault

Pour ceux qui le souhaite, voici un lien vers une présentation sur les bases d'excel :

Les bases d'Excel

Sommaires

1.

Créer des listes de validations

2.

Les tableaux croisés dynamiques

3.

Créer et mettre en forme des graphiques

4.

Mise en forme conditionnelle

5.

L’Assistant Conversion (pour les fichiers CSV)

6.

Fonctions les plus utilisées

Pour retrouver quelques astuces pour naviguer facilement sur Excel :

C'est par ici

Les astuces de navigation

Hissez haut !

Ctrl + clic gauche

Sélectionner des cellules non adjacentes : Exemple : Selectionner les informations de la colonne A et de la Colonne D sans sélection C & B

Atteindre l’extrémité de la région de données active dans une feuille de calcul.Exemple : Ctrl+Flèche du bas amène sur la dernière ligne qui contient des données

Ctrl+Touche de direction

Sélectionner une plage large (exemple pour sélectionner les cellules A2 à A5000, se positionner initialement sur A2, aller en A5000 et la sélectionner en laissant appuyer sur MAJ)

MAJ + clic gauche

Étendre la sélection à la dernière cellule non vide de la même ligne ou de la même colonne que la cellule active ou, si la cellule suivante est vide, à la prochaine cellule non vide.

Ctrl+Maj+Touche de direction

1.

Créer des listes de validation

Une liste ? pour quoi faire ?

L'utilisation de listes peut vous servir :

• A faciliter la saisie ;
• A garantir l'intégrité de vos données texte (un simple espace en plus sur une cellule de texte peut vous empêcher de l'utiliser dans une formule.

En vidéo :

1. Dans une feuille de calcul, tapez les entrées que doivent figurer dans votre liste déroulante 2. Sélectionnez la ou les cellule(s) de la feuille de calcul dans laquelle vous voulez créer la liste déroulante. 3. Dans le ruban, aller sur l'onglet "Données" puis cliquer sur Validation des données 4.Dans "Autoriser", choisir "Liste". Dans source, sélectionnez votre plage de liste. 5. L'onglet message saisie permet de personnaliser le message d’entrée lorsque la cellule est sélectionnée 6. Onglet alerte d'erreur: Si vous souhaitez qu’un message s’affiche quand quelqu’un entre quelque chose qui n’est pas dans votre liste, vérifiez Afficher l’alerte d’erreur après la saisie de données non valides

+info

Voir sur grand écran

2.

Les tableaux croisés dynamiques - Création

Un tableau croisé machin quoi ?

Le tableau croisé dynamique ! Il permet de calculer, de synthétiser et d’analyser des données rapidement. Nota : Les données doivent être organisées en colonnes avec une seule ligne d’en-tête.

En vidéo :

1. Pour créer le tableau, aller dans insertion > Croisé dynamique ou tableau croisés r<ecommandés pour avoir des propositions déjà configurées.

2. Sélectionner la plage du tableau d'origine 3. Choisissez l’emplacement où vous souhaitez placer le rapport de tableau croisé dynamique. Sélectionnez Nouvelle feuille de calcul pour placer le tableau croisé dynamique dans une nouvelle feuille de calcul ou une feuille de calcul existante

Voir sur grand écran

Voir l'épisode suivant :

2.

Les tableaux croisés dynamiques - Exemple de configuration

Lors de la création du tableau, l'interface permettant de configurer la structuration du tableau apparaît. Disons que je veux savoir quel est le revenu moyen ainsi que l'âge moyen par genre pour mes salariés sur un tableau anonymisé :

Ma liste de salariés :

On choisit d'abord les champs que l'on souhaite interroger :

Je veux mes résultats dans mes colonnes.

Optionnel : Ajouter la possibilité de trier à partir d'un champ spécifique (par exemple, je veux que les prénommés Gaston)

En cliquant sur la petite flèche à gauche je peux choisir si je veux faire la somme, la moyenne ou plein d'autres fonctionnalités sur les valeurs.

Voir le résultat

Je veux faire apparaître une ligne par genre

2.

Les tableaux croisés dynamiques - Résultats

Rien n'empêche la modification utlérieure du tableau

3.

Les graphiques

Plutôt camenbert ou donut ?

Ajouter des graphiques pour mettre des paillettes dans votre vie. Ils permettent de visualiser les données de façon plus sympathiques que des tableaux bruts.

En vidéo :

Pour insérer un graphique : 1. mettez les cellules concernées en surbrillance (astuce, si vos données ne sont pas côte à côte, utiliser la touche contrôle au moment de la sélection. 2 . aller dans insertion > Graphiques recommandés ou sélectionnez directement le type de graphique souhaité.

Voir sur grand écran

Voir l'épisode suivant :

Le graphique croisé dynamique offre plusieurs options et peut vous permettre de faire évoluer facilement vos graphiques.

3.

Les graphiques (S01E02) - Le graph. croisé dynamique

Lors de la création du graphique croisé dynamique, vous pouvez sélectionner l'ensemble de votre tableau.

Tout comme pour le tableau croisé dynamique, je peux sélectionner les champs que je veux interroger.

Ex : Je veux représenter la moyenne d'âge par genre et par nationalité. Dans l'espace sur les valeurs, je mets le champ "âge", je fais un clic droit et je paramètre le champ pour indiquer que je veux calculer la moyenne. En légénde, j'indique le "genre", et en axe, je mets le champ "nationalité".

4.

Mise en forme conditionnelle

A ne pas confondre avec la mise en forme au subjonctif présent

La mise en forme conditionnelle facilite la mise en évidence de certaines valeurs ou l’identification de cellules particulières. Elle modifie l’apparence d’une plage de cellules en fonction d’une condition (ou d’un critère). Très utile pour se mettre des alarmes !

1. Sélectionnez la plage de cellules, le tableau ou l’ensemble de la feuille où vous voulez appliquer une mise en forme conditionnelle.2. Dans l’onglet Accueil, cliquez sur Mise en forme conditionnelle.

Exemples :

1. Sélectionnez la plage de cellules, le tableau ou l’ensemble de la feuille où vous voulez appliquer une mise en forme conditionnelle.2. Dans l’onglet Accueil, cliquez sur Mise en forme conditionnelle.

Nuances de couleurs

Jeux d'icônes

Barre de données

1Permet l'ajout, la suppression et la gestion des règles

5.

L’Assistant Conversion (pour les fichiers CSV ou autres)

Un fichier CSV (valeurs séparées par des virgules) est un type de fichier spécial qu'il est possible de créer ou de modifier dans Excel. Plutôt que de stocker les informations en colonnes, les fichiers CSV les stockent en les séparant par des points-virgules. Quand on recoit un fichier au format CSV, il faut utiliser un outil de conversion pour retrouver un tableau exploitable.

En vidéo :

Les étapes pour convertir le document :

Voir sur grand écran

1. Sélectionnez la cellule ou la colonne contenant le texte à fractionner.
2. Sélectionnez Données > Convertir.
3. Dans l’Assistant Conversion, sélectionnez Délimité > Suivant.
4. Sélectionnez les Séparateurs de vos données. Par exemple, Virgule et Espace. Un aperçu de vos données apparaît dans la fenêtre Aperçu des données.
5. Sélectionnez Suivant.
6. Sélectionnez la Destination des fractions de données sur votre feuille de calcul.
7. Sélectionnez Terminer.

6.

Fonctions les plus utiles au quotidien Vol.1

Selon un sondage BVA

réalisé sur un échantillon de 1 personne

Valable sur office 365 uniquement

NB.SI

NB.SI.ENS

RechercheX Unique Transpose

SOMME.SI

SOMME.SI.ENS

SI

Principales fonctions sur les dates

RECHERCHEV

Tous les critères textuels doivent être placés entre guillemets ("). En revanche, les guillemets ne sont pas nécessaires pour les critères numériques

NB.SI

Ou comment différencier les choux et les carottes

Permet de compter le nombre de cellules qui répondent à un critère. Dans sa forme la plus simple, la fonction NB.SI se décompose ainsi : =NB.SI(où voulez-vous rechercher ? ; que voulez-vous rechercher ?) Ex : Toujours sur mon tableau de suivi de mes salariés, je veux savoir combien de salariés sont made in USA. Depuis un autre onglet, je colle ma colonne F et je supprime les doublons pour garder ma liste des données sur les nationalités en colonne A. En cellule B,2 je tape : ==NB.SI(Tableau!$F$2:$F$7;'Etat sur les nationalités'!A2) Sinon, je peux auss i taper n'importe ou : =NB.SI(F2:F7;"Américaine")

Résultat :

Retour à la liste des formules

NB.SI.ENS

Ou comment différencier le chou rouge du chou blanc

Permet de compter le nombre de cellules qui répondent à plusieurs critères. La syntaxe de la fonction NB.SI.ENS contient les arguments suivants :

• plage_critères 1 : La première plage dans laquelle évaluer les critères associés.
• critère 1 : Critères, sous forme de nombre, d’expression, de référence de cellule ou de texte, qui déterminent les cellules à compter.

Ex : Sur mon tableau de suivi de mes salariés (qui a un peu augmenté), je veux connaître le nombre de personne pour chaque nationalité en distinguant le genre. Je vais donc utiliser la fonction NB.SI.ENS.

1. Sur un nouvel onglet, je vais d'abord entrer les nationalités en colonne A et entrer le genre en Colonne B & C. 2. En cellule B2. je tape NB.SI.ENS : =NB.SI.ENS(Tableau!$F$2:$F$12;'Etat NB.SI.ENS'!$A2;Tableau!$A$2:$A$12;'Etat NB.SI.ENS'!B$1)

crit. 1

plage crit. 2

plage crit. 1

crit. 2

Ca fait peur mais c'est assez logique. Je sélectionne d'abord la plage ou je veux rechercher pour mon premier critère, puis la cellule du critère, et ainsi de suite. Nota : Attention à la position des $ pour pouvoir étirer la formule.

Résultat :

Retour à la liste des formules

SOMME.SI

C'est presque comme NB.SI, mais en pas pareil !

Permet de faire un total conditionné par un critère. La syntaxe de la fonction SOMME.SI contient les arguments suivants :Plage : Plage de cellules avec la liste des éléments pouvant servir de critère. Critère : Cellule qui contient le critère ; Plage de somme : Plage contenant les valeurs à sommer.

Exemple : Je veux faire la somme des salaires pour les personnes agés de + de 40 ans : Dans une cellule je vais taper : =SOMME.SI(D2:D9;">40";E2:E9); Je veux faire la somme des salaires des hommes : =SOMME.SI(A2:A9;"Homme";E2:E9)

Résultats :

Retour à la liste des formules

SOMME.SI.ENS

C'est presque comme NB.SI, mais en pas pareil !

Permet de faire un total conditionné par plusieurs critères. La syntaxe de la fonction SOMME.SI..ENS contient les arguments suivants :Plage de somme : Plage contenant les valeurs à sommer.Plage Crit 1 : Plage de cellules avec la liste des éléments du 1er critère Critère 1 : Cellule qui contient le 1er critère ; Plage crit 2. ; Crit 2 ; ...

Sur un nouvel onglet, je vais d'abord mettre le critère de genre souhaité en colonne A et le critère de la nationalité en B. En B2 je saisis : =SOMME.SI.ENS(Tableau!$E$2:$E$9;Tableau!$A2:$A$9;A2;Tableau!$F$2:$F$9;B$1)

Exemple : Je veux faire la somme des salaires pour les femmes de nationalités francaises sur ce tableau.

Résultats :

plage crit. 1

crit. 1

plage crit. 2

crit. 2

Plage a sommer.

Retour à la liste des formules

SI

Avec des si, on scierait

La fameuse fonction SI. : Elle permet d’établir des comparaisons logiques entre une valeur et le résultat attendu. Une instruction SI peut donc avoir deux résultats. Le premier résultat est appliqué si la comparaison est vérifiée, sinon le deuxième résultat est appliqué.

Exemple : Je veux faire indiqué le message "c'est cool" pour les salaires au dessus de 2000 €, ou "c'est pas cool" pour les valeurs en dessous.

En cellule G2, je vais tapers : =si(E2>2000;"C'est cool";"C'est pas cool")

La condition

Valeur si vraie

Valeur si faux

Résultats :

Pour les aventuriers, on peut imbriquer des fonctions SI ! (si si c'est vrai). Si je veux recruter une personne de profil confirmé (+ de 40 ans) mais que je veux pas payer trop cher ( - de 2 000 €). Ca donne ça : =SI(D2<40;"Pas le bon profil";SI(E2>2000;"Trop cher";"Bon profil"))

Résultats :

Retour à la liste des formules

Recherchev

V pour verticale

La recherche V permet de rechercher des données stockées dans une colonne d'un tableau en se référencant à un identifiant unique

Exemple : Je veux faire un état de recherche dans lequel, à partir d'un numéro de matricule (ajouter à mon tableau pour l'occasion) je veux retrouver le nom, le prénom et l'âge d'un salarié.

Sur une nouvelle feuille, en cellule B2, je créer une liste déroulante reprennant le N° de matricule (je peux aussi tout simplement taper le matricule mais c'est plus sûr)

En cellule B6, je tape : =RECHERCHEV(B2;'Tableau avec matricule'!$A$2:$I$9;3)

L'identifiant unique recherché*

L'ensemble du tableau ciblé

Le nom est stocké dans la 3e colonne du tableau

Pour le prénom en cellule B8, je tape : ==RECHERCHEV(B2;'Tableau avec matricule'!$A$2:$I$9;4)Le prénom étant stocké dans la 4e colonne.

* Nota : l'identifiant doit être stocké dans la première colonne de la zone ciblée

Retour à la liste des formules

Merci à ceux qui sont restés jusqu'au bout