Microsoft Excel Fonctions Avancées

La référence d’une cellule est utilisée dans Excel afin d’identifier la cellule. Cette référence est composée d’une lettre et d’un nombre  (ex : A234). Ce type de référence est nommé : Référence relative.

Dans certains cas vous serez amené (notamment pour les calculs de %, de RechercheX…) à utiliser des références absolues ou mixtes. Ce type de références se caractérisent par l’utilisation de $ au niveau de l’écriture de la référence. Elles se présentent sous la forme suivante :

• Références Absolues: $A$234
• Références mixtes en ligne  : A$234
• Références mixtes en colonne: $A234

La référence d’une cellule est utilisée dans Excel afin d’identifier la cellule. Cette référence est composée d’une lettre et d’un nombre  (ex : A234). Ce type de référence est nommé : Référence relative.

Dans certains cas vous serez amené (notamment pour les calculs de %, de RechercheV, RechercheX…) à utiliser des références absolues ou mixtes. Ce type de références se caractérisent par l’utilisation de $ au niveau de l’écriture de la référence. Elles se présentent sous la forme suivante :

Il existe encore deux autres types de références basées sur l’utilisation des dollars ($) :

• $B5 : le blocage de la colonne lors de la recopie et pas de la ligne.
• B$5 : le blocage de la ligne lors de la recopie et pas de la colonne.

Cette vidéo vous permettre de revoir cette notion de référence mixte dans l'utilisation des $.

Cette partie est une révision de premier module sur l'utilisation des $.

Les formules de calculs utilisent classiquement des plages de cellules (ex : somme(A2 :A45)). Imaginons que vous ayez une base de produits. Vous réalisez différents types de calculs ou d’analyse avec des outils du type filtres avancés, tableaux croisés dynamiques... sur ces lignes.

Vous pouvez créer vos outils en utilisant les références de cellules, mais vous pouvez aussi opter pour des plages nommées. L’intérêt de cette technique d’une part est que vous pourrez donner des noms explicites à vos cellules ou plages de cellules et donc les identifier très clairement. D’autre part vous pourrez utiliser le même nom dans l’ensemble de vos outils ou fonctions. Cette technique simplifie les mises à jour car vous actualiserez la plage au niveau du nom et non au niveau de chacun des outils ou formules

Les formules de calculs utilisent classiquement des plages de cellules (ex : somme(A2 :A45)). Imaginons que vous ayez une base de produits. Vous réalisez différents types de calculs ou d’analyse avec des outils du type filtres avancés, tableaux croisés dynamiques... sur ces lignes.

Vous pouvez créer vos outils en utilisant les références de cellules, mais vous pouvez aussi opter pour des plages nommées. L’intérêt de cette technique d’une part est que vous pourrez donner des noms explicites à vos cellules ou plages de cellules et donc les identifier très clairement. D’autre part vous pourrez utiliser le même nom dans l’ensemble de vos outils ou fonctions. Cette technique simplifie les mises à jour car vous actualiserez la plage au niveau du nom et non au niveau de chacun des outils ou formules.

La fonction Si est la fonction la plus répandue dans les classeurs Excel. Elle permet effectivement d’automatiser des calculs du type :

• Calculs de primes
• Vérification de calculs
• Affectation de coefficient selon une grille de référence
• Retraitement d’erreurs de saisies
• …

La fonction Si est la fonction la plus répandue dans les classeurs Excel. Elle permet effectivement d’automatiser des calculs du type :

• Calculs de primes
• Vérification de calculs
• Affectation de coefficient selon une grille de référence
• Retraitement d’erreurs de saisies
• …

Mise en pratique

Afin de vous exercer à la construction des fonctions Si Simples, nous vous proposons de réaliser les différents exercices du classeur :

La fonction Si Simple permet donc de réaliser un test sur le contenu d’une cellule et affecter un des deux résultats possibles Valeur_Si_Vrai ou Valeur_Si_Faux dans cette cellule. Cependant certains cas de figures entraînent plusieurs situations possibles et de ce fait les deux alternatives ne suffisent plus. Vous serez donc amené à imbriquer des fonctions SI afin de répondre à votre problématique. La fonction de base sera toujours la même mais vous allez cumuler les fonctions SI dans la même formule. Vous avez droit d’imbriquer jusqu’à 64 fonctions dans une formule.

La fonction Si Simple permet donc de réaliser un test sur le contenu d’une cellule et affecter un des deux résultats possibles Valeur_Si_Vrai ou Valeur_Si_Faux dans cette cellule. Cependant certains cas de figures entraînent plusieurs situations possibles et de ce fait les deux alternatives ne suffisent plus. Vous serez donc amené à imbriquer des fonctions SI afin de répondre à votre problématique. La fonction de base sera toujours la même mais vous allez cumuler les fonctions SI dans la même formule. Vous avez droit d’imbriquer jusqu’à 64 fonctions dans une formule.

Mise en pratique

Afin de vous exercer à la construction des fonctions Si Imbriquées, nous vous proposons de réaliser les différents exercices du classeur :

Dans certaines situations, vous avez besoin de réaliser deux tests simultanément (voire plus) afin de valider une hypothèse. Vous allez donc apprendre (ou revoir) comment intégrer la fonction ET au niveau du test logique de vos fonctions SI. Les valeurs logiques représentent nos différents tests. Vous pouvez utiliser tous les opérateurs classiques vues dans la partie « construction des fonctions classiques : < , > ,=, <=,>=,<>.

Cette fonction vous permet d’intégrer jusqu’à 255 conditions afin de valider votre problème.

Mise en pratique

Afin de vous exercer à la construction des fonctions ET , nous vous proposons de réaliser les différents exercices du classeur

Dans certaines situations vous avez peut-être plusieurs hypothèses correspondant à la même situation finale. Au lieu d’imbriquer des fonctions SI, nous allons voir comment centraliser toutes les hypothèses de réaliser afin de simplifier la fonction de calcul. Vous allez donc apprendre à intégrer la fonction OU au niveau du test logique de vos fonctions SI.

Dans certaines situations vous avez peut-être plusieurs hypothèses correspondant à la même situation finale. Au lieu d’imbriquer des fonctions SI, nous allons voir comment centraliser toutes les hypothèses de réaliser afin de simplifier la fonction de calcul. Vous allez donc apprendre à intégrer la fonction OU au niveau du test logique de vos fonctions SI.

Ces fonctions vous permettront de retraiter les chaînes textuelles inclus dans vos cellules. Vous pourrez ainsi découper une saisie, extraire une partie des informations des cellules, supprimer les espaces avant et après une chaîne textuelle,…

Ces fonctions vous permettront de retraiter les chaînes textuelles inclus dans vos cellules. Vous pourrez ainsi découper une saisie, extraire une partie des informations des cellules, supprimer les espaces avant et après une chaîne textuelle,…

Mise en pratique

Afin de vous exercer à la construction des fonctions TEXTES, nous vous proposons de réaliser les différents exercices du classeur

Ces fonctions vous permettront de retraiter les dates . Vous pourrez ainsi calculer des échéances, des délais, des âges, des nombres de jours ouvrés,…

Ces fonctions vous permettront de retraiter les dates . Vous pourrez ainsi calculer des échéances, des délais, des âges, des nombres de jours ouvrés,…

Lors de l’apparition de la version 2007, Excel se voit doter d’un nouvel outil : le tableau.  
Cet outil permet de gérer plus facilement des bases de données et surtout permet d’utiliser 
des fonctionnalités avancées et automatisées d’Excel. 

Lors de l’apparition de la version 2007, Excel se voit doter d’un nouvel outil : le tableau.  
Cet outil permet de gérer plus facilement des bases de données et surtout permet d’utiliser 
des fonctionnalités avancées et automatisées d’Excel. 

Un tableau croisé dynamique est un tableau interactif permettant de synthétiser rapidement les données contenues dans ce tableau. Un tableau croisé est appelé dynamique car il est évolutif : une fois créé, il permet de faire pivoter les titres de colonnes et de lignes pour obtenir 
différentes vues des données source. Il peut être constitué à partir d’une liste d’Excel, d’un fichier ou d’une table de données créés dans une autre application qu’Excel ou de données d’un tableau croisé dynamique existant. Le tableau de données permettant la création d’un tableau croisé dynamique doit avoir impérativement les caractéristiques suivantes : 

• La 1ère ligne du tableau doit être une ligne composée de noms de champs 
• La 2ème ligne du tableau et les suivantes sont des lignes d’enregistrements comportant au moins une donnée saisie sur chacune de ces lignes 

Ces fichiers vous permettront de vous entrainer sur la construction de tableaux croisés dynamiques.