Editions ENI Livres | Vidéos | e-Formations
Manipuler les formules
Objectifs
Dans ce chapitre, vous allez découvrir les différentes façons d’écrire des formules dans les cellules.
Un rappel sur l’onglet Formules dans Excel sera effectué avant de passer aux formules en VBA puis de poursuivre avec la classe WorksheetFunction.
Vous terminerez ce chapitre avec des exercices pour valider vos nouveaux acquis.
Formules sous Excel
Il existe des centaines de formules disponibles sous Excel et même un utilisateur très avancé n’en utilisera qu’une infime proportion. Sans avoir besoin d’être un expert en formules et fonctions Excel, voici pour débuter un bref rappel de l’onglet Formules dans le ruban Excel et quelques erreurs en sortie de formules auxquelles vous aurez à faire en VBA.
Onglet Formules
Dans le ruban Excel, les formules ont leur propre onglet dédié.
Le ruban comporte un groupe Bibliothèque de fonctions où les formules sont regroupées par catégories.
Vous pouvez saisir vous-même vos formules directement dans les cellules ou dans la barre de formules.
Les formules commencent par le signe = et elles utilisent aussi bien des fonctions Excel que des cellules ou des valeurs écrites « en dur ».
=A1+B2
=ANNEE(C3)
=TEXTE(AUJOURDHUI();"AAAA-MM") Exemple 1 : différents types de formules Excel
Chacune de ces formules fournit en sortie une valeur, de différent type de données selon les valeurs et les fonctions utilisées (texte, valeur numérique, date, booléen, etc.).
Erreurs en sortie de formules
Lorsque vous effectuez des formules qui retournent des erreurs, par exemple avec une recherche ne trouvant aucun résultat (avec RECHERCHEV), les cellules affichent des erreurs comme...
Formules en VBA
Comme vous allez le découvrir dans les sections suivantes, il y a plusieurs manières de rédiger vos formules en VBA. Selon votre aisance avec la langue de Shakespeare ou encore selon vos contraintes d’entreprise, vous serez amené à utiliser l’une ou l’autre des méthodes.
Utiliser l’Enregistreur de macros pour commencer
Pour commencer, utilisez l’Enregistreur de macros pour voir quel code est généré par défaut lorsque vous ajoutez des formules dans les cellules.
Manipulations à effectuer
Voici les cinq étapes à réaliser :
1 - Remplir les cellules
À partir d’un nouveau classeur ou d’un classeur existant, saisissez dans les cellules A1 à C3 les données suivantes :
2 - Lancer l’Enregistreur de macros
Comme vu dans le chapitre L’Enregistreur de macros, lancez l’enregistreur de macros et nommez la macro Macro1.
3 - Ajouter les formules
Dans la cellule D1, ajoutez la formule suivante : =RECHERCHEV(1;A:C;3;FAUX)
Dans la cellule E1, ajoutez la formule suivante : =RECHERCHEV("Martin Dupont";B:C;2;FAUX)
4 - Arrêter l’Enregistreur de macros
Comme vu dans le chapitre L’Enregistreur de macros, arrêtez l’Enregistreur de macros.
5 - Analyser le code généré automatiquement
Accédez à l’interface VBE ; voici le code que vous devriez voir dans le module nouvellement créé :
Sub Macro1()
Range("D1").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(1,C[-3]:C[-1],3,FALSE)"
Range("E1").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(""Martin Dupont"",
C[-3]:C[-2],2,FALSE)"
End Sub Exemple 2 : code généré par l’Enregistreur de macros
Analyse du code généré par l’Enregistreur de macros
La première ligne de la macro consiste à sélectionner la cellule D1, vous la connaissez maintenant.
Range("D1").Select La seconde ligne utilise la cellule active (D1) pour y affecter une formule en passant par la propriété FormulaR1C1, que vous verrez plus en détail dans le paragraphe suivant....
Utiliser ses propres fonctions dans les formules Excel
Une autre possibilité très pratique avec VBA est de pouvoir utiliser vos propres fonctions directement dans les cellules Excel.
Pour y arriver, c’est très simple. Les fonctions que vous souhaitez utiliser dans des formules devront être rédigées dans des modules (et non dans ThisWorkbook, ni dans les feuilles ou encore dans des modules de classe). La portée des fonctions devra également être Public.
Voici un exemple de formule qui retourne la valeur 1.
Public Function Retourne1() As Integer
Retourne1 = 1
End Function Exemple 17 : rendre une fonction personnalisée disponible dans les formules
Lorsque vous avez rédigé ces quelques lignes, allez dans une cellule Excel et tapez le nom de la fonction. Elle apparaît alors dans la liste des fonctions disponibles, en respectant la casse de nommage.
Validez la formule. La valeur de retour (ici 1) s’affiche directement dans la cellule.
Cela fonctionne également si vous avez une fonction qui nécessite des paramètres.
'Fonction retournant le carré de la valeur passée en paramètre
Public Function RetourneLeCarre(iValeur As Integer) As Integer
RetourneLeCarre = iValeur * iValeur
End Function Exemple 18 : fonction personnalisée nécessitant...
Utiliser les formules Excel directement en VBA - WorksheetFunction
Vous pouvez utiliser directement les fonctions en VBA sans passer par des formules Excel, grâce à la propriété WorksheetFunction de l’objet Application.
La propriété WorksheetFunction
La propriété WorksheetFunction regroupe l’ensemble des fonctions Excel disponibles dans l’interface Excel, directement au format VBA.
Tout comme il existe des centaines de fonctions Excel pour les formules, il existe des centaines de fonctions membres de la propriété WorkSheetFunction.
En voici quelques-unes pour vous permettre d’en comprendre le principe.
Membres de la propriété WorksheetFunction
Les points communs des fonctions dans Excel et dans VBA sont leur nom en anglais et l’ordre des paramètres que les fonctions attendent en entrée. Il vous est également possible de passer soit des cellules, soit des valeurs fixes.
La syntaxe générale de cette propriété est la suivante :
Application.WorksheetFunction.NomFonction(Arg1, [Arg2],….) Exemple 19 : syntaxe générale de la propriété WorksheetFunction
Le nom de la fonction NomFonction dépend de la fonction Excel qui doit être utilisée, le nombre de paramètres est variable selon la fonction utilisée.
Voici quelques exemples de fonctions...
Les options de calcul et le calcul sur une feuille ou un classeur
Les options de calculs en VBA : Calculation
Vous pouvez vous reporter à la section dédiée du chapitre Manipuler l’application Excel pour les propriétés Calculation.
Calculer sur une feuille, calculer tout le classeur : Calculate
Vous pouvez déclencher un calcul sur une feuille ou un classeur au moyen de la méthode Calculate.
La syntaxe sera la suivante :
Worksheets("Feuille").Calculate Exemple 23 : utilisation de la méthode Calculate
Formules Python dans Excel
Disponible pour les utilisateurs disposant d’abonnement Microsoft 365 Business et Enterprise depuis septembre 2024, Microsoft met à votre disposition la puissance du langage Python directement dans les formules Excel.
Dans l’onglet Formules un groupe Python est disponible.
Bien que cet ouvrage soit consacré à la programmation VBA, voici quelques éléments que vous pouvez apprendre sur Python dans Excel.
Si vous souhaitez aller plus loin sur la programmation en Python, plusieurs ouvrages aux Éditions ENI sont disponibles à cette adresse : https://www.editions-eni.fr/developpement-d-applications/langages/python
Exemple de syntaxe de Python
Pour insérer du code Python dans une cellule, cliquez sur le bouton Insérer Python. La barre de formule s’ouvre, avec un identifiant PY (pour Python, bien évidemment).
Dans la capture suivante, la formule Python additionne les cellules B1 et C1, chacune des adresses de cellules étant incluses dans une formule xl.
Il est possible de sélectionner les cellules des feuilles Excel avec un code Python généré dynamiquement. Les capacités de calculs avec Python permettent d’effectuer des opérations matricielles comme des opérations trigonométriques, au même titre que les formules Excel.
Identification des cellules incluant un objet Python
Afin d’identifier...
Exercices
Avant de réaliser les exercices suivants, créez une feuille Exercices_Chap_17_Formules.
Dans cette feuille, remplissez la plage de cellules E1 à F7 comme sur l’image suivante :
Les exercices auront pour objectif d’utiliser des formules se basant sur ces données.
Écriture de formules en français
Moyenne des notes
Créez une macro FormuleMoyenneNotes.
Cette macro écrira dans la cellule F8 une formule MOYENNE des notes. Vous devrez utiliser la formule en français (FormulaLocal).
Exécutez la macro FormuleMoyenneNotes. Le total affiché dans la cellule F8 devra être de 12,5.
Meilleure note
Créez une macro FormuleMaximumNotes.
Cette macro écrira dans la cellule F9 une formule MAX sur les notes des étudiants. Vous utiliserez de nouveau la formule en français (FormulaLocal).
Exécutez la macro FormuleMaximumNotes. Le total affiché dans la cellule F9 devra être de 17.
Écriture de formules en anglais
Nombre de rattrapage
Créez une macro FormuleRattrapage.
Cette macro écrira dans la cellule F10 une formule indiquant le nombre total d’étudiants ayant une note strictement inférieure à 12 (NB.SI en français, COUNT IF en anglais). Vous utiliserez cette fois-ci une formule en anglais...
