Qu'est-ce qu'une formule : c'est une suite de valeurs, de références de cellule, de noms, de fonctions ou opérateurs dans une cellule permettant de générer une nouvelle valeur à partir des valeurs existantes. Une formule commence toujours par le signe égal (=).

Qu'est-ce qu'une fonction [de feuille de calcul] : c'est une formule pré-écrite qui prend une ou plusieurs valeurs, effectue une opération, puis renvoie une ou plusieurs valeurs. L'objectif est ainsi de simplifier les calculs et raccourcir des formules sur une feuille de calcul, notamment celles qui effectuent des calculs longs et complexes.

Comment Excel interprète une formule : Excel effectue le calcul de la gauche vers la droite mais il respecte les règles élémentaires associées aux opérateurs de calcul. Ainsi,

• =5+2*3 => donne 11
• =(5+2)*3 => donne 21
• plus d'informations sur les calculs, chercher "Calcul des valeurs par les formules" & "Opérateurs de calcul dans les formules" dans l'aide

Comment saisir une formule : cliquez sur une cellule, puis cliquez dans la barre de formules en-dessous des barres d'outils

Aide à la création de formules : saisissez le signe + et la fonction que vous voulez utilisez comme - =trouve - puis appuyez sur :

CTRL + SHIFT + A pour afficher les arguments de la fonction à la suite de celle-ci :

CTRL+ A pour afficher la boîte d'assistance à la rédaction de formules : =trouve(texte_cherché;texte;no_départ).

Cette boîte est constitué d'un premier groupe d'informations, chapeauté par le nom de la fonction éditée. Ces informations sont les paramètres nécessaires à la fonction, vous renseignez leur valeur dans le champ texte qui leur est associé, ce champ texte est de type refedit.
Un champ refedit peut contenir des valeurs numériques ou des valeurs textes (texte entre guillemets), et surtout, il vous autorise à sélectionner la plage de calcul (une ou plusieurs cellules) qui contient la ou les valeurs dont vous voulez vous servir.
A droite de ce champ s'affiche la valeur prise par le paramètre.

Dessous ce premier groupe d'informations, Excel décrit l'objectif de la fonction utilisée.

Il apporte ensuite une explication du paramètre que vous êtes entrain d'éditer (celui où le curseur est positionné).
Et vous indique le résultat de votre calcul.

• Excel possède bien entendu une documentation sur chacune de ses fonctions , que vous pourrez obtenir en cliquant sur le bouton d'aide.
• Quelques informations pour savoir comment saisir rapidement et retourner à la ligne à la fin de la saisie : ici sur le web excellabo.net

Références circulaires

L'aide d'excel possède un chapitre "Recherche de cellules responsables d'une référence circulaire".
Lorsque ce problème survient, Excel affiche la première référence qui s'avère circulaire dans la barre de statuts et il affiche la barre d'outils "Audit" pour aider à identifier les cellules antécédentes (et dépendantes). Vous pouvez alors naviguer dans ces antécédents.
Il y aussi la macro "FindCirc" de Stephen Bullen http://www.BMSLtd.co.uk/Excel/SBXLPage.asp#VBA.
Dans les pages edition.html & xl_cellule.htm vous trouverez de quoi détecter les dépendances & antécédences.

Aide à la visualisation des formules

• afficher les formules : Ctrl 3 ou Menu Outils>Options>Onglet Général> Case à cocher Formules
• afficher une formule :
  1. utiliser une fonction personnalisée :

     'copier cette fonction dans un module puis vous pouvez utiliser la fonction dans une feuille de calcul.
     function FORMUL(cel as range)
     FORMUL = cel.formulalocal
     end function
     'exemple d'utilisation = "voici la formule de la cellule A1" & FORMUL(A1)
     

  2. mettre un apostrophe devant le signe = : => '=maformule
     
     voici d'ailleurs, une petite macro pour effacer ce caractère, considérée comme un caractère de préfixe par Excel.
     ci-contre le message à son origine : news
     
     Sub ZapPrefixes()

     For Each cell In ActiveSheet.UsedRange
     If cell.PrefixCharacter <> "" Then
     cell.Formula = cell.Formula
     End If
     Next cell
     
     End Sub

  3. les imprimer dans un fichier en vba
     
     la macro ci-dessous imprime les formules de la plage sélectionnée dans un fichier

Sub FormulaListing()
Dim c As Range
Open "C:\XLS Formula Listing.txt" For Output As #1
For Each c In Selection.Cells
Print #1, c.Address(, , xlR1C1), c.FormulaR1C1
Next c
Close #1
End Sub

Quelques explications sur certaines choses pas très connues

Fonction volatile : news

Formule matricielle :

explication de LL + page xl_denombrement.htm + classeur exemples sur Excel Downloads + http://www.emailoffice.com/excel/arrays-bobumlas.html

Différence entre (...) et [ ...] : news, news

Diviser, multiplier à une plage de cellules : edition.html

Conversion d'une "formule relative" en "formule absolue":

ActiveCell.Formula=Application.ConvertFormula(formula:=MaFormule,fromReferenceStyle:=xlR1C1,toReference Style:=xlA1)

Format & Format$ :

La seule différence entre Format$ et Format, c'est que la première fonction renvoie directement une chaîne de caractères, alors que la deuxième renvoie un Variant contenant une chaîne de caractères. Pour en savoir plus sur ce type de fonctions, regardez "Renvoi de chaînes à partir de fonctions" dans l'aide en ligne.

Fonctions personnalisées

Pour créer votre propre fonction, inscrivez le code de celle-ci dans un module VBA d'Excel - (exemple ci-dessus).
Pour l'utiliser, vous trouverez cette fonction dans le menu des formules à "Fonctions personnalisées", elle s'utilise comme une autre formule.

Par contre, dans le code d'une fonction personnalisée, vous ne pouvez pas sélectionner/modifier des cellules/feuilles autres, vous pouvez juste calculer une valeur pour la cellule en cours. "Excel verrouille toute possibilité de modifier quoi que ce soit dans le classeur quand il est en cours de calcul, pour éviter que les modifications perturbent l'ordre de recalcul entre les cellules", dixit Laurent.

Structure d'une fonction

function Nomdelafonction ( Argument1 as type de l'argument, Argument2 as type de l'argument....) as type de la donnée retournée
' Votre code ci-dessous
Nomdelafonction = valeur à renvoyer
end function.

Exemple : multiplier par deux

function TimesTwo(Num as double) as double
TimesTwo = Num * 2
end function

=TimesTwo(25) ou =TimesTwo(A1)

Autre exemple : Retourner un résultat en toutes lettres : news
Lister les procédures et/ou fonctions personnalisées : news
Appeler une macro à partir d'une fonction employée dans une formule : news

Quelques exemples de fonctions personnalisées utiles

Une cellule donnée contient-elle une formule ?

function Isformula(Rng As range) As Boolean
Isformula = Rng(1).Hasformula
end function
=> écrire ceci dans une cellule =Isformula(A1)

Somme des valeurs positives

Public Function AddSpecificPositive(aRng As Range)
Dim aCell as Range
Dim aNum As Double aNum = 0#
For Each aCell In aRng
If aCell.Value2 > 0 Then
aNum = aNum + aCell.Value2
End If
Next aCell
AddSpecificPositive = aNum
End Function

Fonction Cellformula

Cellformula est une fonction personnalisée, elle permet de voir la formule contenue dans une cellule,
=Cellformula(A1)
function Cellformula(c) as String
Cellformula = c.formula
end function
Vous pouvez afficher les formules à la place des valeurs en cochant une option dans menu outils/ options/ onglet général

Calcul de la TVA

function CalculerTTC(HorsTaxe As double, TVA As double) As double
        CalculerTTC = HorsTaxe * (1 + (TVA / 100))
end function

Ecrire la tva comme suit 369 F/HT, utiliser le format suivant : ### ##0" F/HT"


Pour info, pour connaître un montant HT à partir d'un montant TTC, faites l'opération suiante :
Montant TTC / (1+ Taux TVA en %)
Ainsi, pour une TVA à 19,6% autrement écrit : 0.196, on a donc :
Montant HT = Montant TTC : 1,196

Se référer à la nième feuille précédente ou suivante : news

Fonctions Excel basiques

Si

La fonction Si permet de tester quelquechose et d'agir selon que le test s'avère vrai ou faux, c'est un syllogisme en quelque sorte : =Si ( A1 = 2 ; ALORS ; SINON)

Les fonctions Si sont imbriquables, mais une formule ne peut contenir au maximum que 7 Si imbriqués.
Vous pouvez incorporer cette fonction au sein d'autres formules ou utiliser d'autres formules en son sein.

exemples

=si (A1=2;A1;0) ' si A1 est égal à 2, alors nous affichons la valeur de A1, sinon 0.
=si (A1=2;A1;"") ' si A1 est égal à 2, alors nous affichons la valeur de A1, sinon nous n'affichons rien.
=si (A1=2;B1+B2;B1-B2) 'si A1 est égal à 2, alors nous additionnons B1 & B2, sinon nous les soustrayons.

=si (nbval(A:A)=10;somme(A:A);"") ' si le nombre de valeurs contenues dans la colonne A est de 10, nous en faisons la somme et l'affichons, , sinon nous n'affichons rien. Pour d'autres exemples de nbval, cf xl_denombrement.htm.

="Bonjour " & si(A1="Stéphane";"Stéphane";"") & ","

comment surpasser le chiffre de 7 Si imbriquables

Pour pallier à cette restriction, vous pouvez disposer certaines conditions SI :
- soit dans d'autres cellules (nommées ou non),
- soit dans des plages nommées définies via le menu insertion (choisir Noms, puis Définir)
vous imbriquerez ensuite ces conditions dans la formule principale en faisant référence aux cellules où figurent les autres conditions
Exemple:     =    si  (    A1<>""    ;    Si2    ;    Si3)

autre exemple tarabiscoté

dont l'objectif est d'exporter des données d'une colonne A vers une colonne B sur le modèle suivant :
A1 vers B1
A2 vers B3
A3 vers B5
A4 vers B7
A5 vers B9
=SI(MOD(LIGNE();2) = 0;0;DECALER($A$1;(1+LIGNE()/2)-1;0))
'solution produite par Eric J. le samedi 1 avril 2000 dans le groupe de discussion microsoft.public.fr.excel en réponse à une requête de Zazie.

Et & Ou

la fonction ET retourne VRAI si tous les tests qu'elle contient s'avèrent vrai, FAUX sinon.
la fonction OU retourne VRAI si au moins un des tests qu'elle contient s'avère vrai, FAUX sinon.

ces fonctions sont imbriquables.
ces fonctions sont utilsables au sein d'autres fonctions.

exemples

=et(A1>4;A2<=19) => retourne vrai si la valeur de A1 est strictement supérieure à 4 et A2 est inférieure ou égale à 19.
=ou(A1>1;A2=4) => retourne vrai si A1 est égal à 1 ou A2 est égal à4

=si(et(A1=B1;A1<>"";B1<>"");"OK";"KO")
en d'autres termes,
soit A1 & B1 sont égales & différents de 0 ou bien, le texte affiché est OK,
soit elles ne sont pas égales ou l'une d'elles ne contient rien, le texte affiché est KO.

Les tests A1<>"" & B1<>"" peuvent être remplacés par l'emploi de la fonction Estvide.

Fonction Estvide

=si(estvide(A1);"c'est vide";B1)
=si(isblank(B4),"It's Empty",E6)

Somme & Sum

Arrondir les nombres

Fonction arrondi.au.multiple
pour obtenir cette fonction il faut activer Outils/Macros complémentaires/Utilitaires d'analyse

=arrondi.au.multiple(A1;0,05)

Fonction Type, Estnum

connaître le type de données dans une cellule : =TYPE(A1)

Il y a différents types :
1 NUMBER
2 TEXT
4 LOGICAL value
8 FORMULA
16 ERROR value
64 ARRAY

le type est-il numérique : = ESTNUM(A1)

Ci-contre un exemple compliqué : news

Le besoin était de contrôler dans quel répertoire est enregistré un fichier - le répertoire "relance" ou le répertoire "offre" - et d'afficher le cas échéant "rel" ou "off". Le contrôle consiste à tester la présence - à l'aide de la fonction CHERCHE des chaînes "relance" ou "offre" dans le chemin d'accès au fichier retourné par la formule CELLULE("fichier").
Si la fonction CHERCHE trouve , alors elle retourne un NOMBRE, donc :
on se sert de ESTNUM pour finaliser le contrôle.

Fonction CNum

Pour convertir une valeur en numérique et pouvoir l'utiliser dans un calcul, vous pouvez utiliser la fonction CNUM.

Lorsque l'on manipule des valeurs textes, Excel tient à disposition des fonctions de feuilles de calcul utiles pour traiter ces valeurs.

Exemple : ci-dessous un tableau avec en colonne A des valeurs (supposées texte) correspondant un nombre de kilogrammes par jour. Quelle formule ?

Colonne A	Colonne B
10 kg/j	=CNUM(SUBSTITUE(GAUCHE(A1;NBCAR(A1)-5);".";","))	10
15 kg/j	'formule à copier sur les lignes suivantes	15
100 kg/j		100
30 kg/j		30
Total		155

Nous avons en fait identifier les 5 dernières caractères de la chaîne en partant de la gauche avec la fonction GAUCHE et nous avons à l'aide de la fonction SUBSTITUE remplacer cette chapine par rien du tout.
L'ensemble peut alors être converti en numérique.

 

Gestion des erreurs

Cette partie concerne les valeurs d'erreurs des feuilles de calcul.

Les valeurs d'erreur

Des fonctions existent pour tester si le résultat d'une fonction retourne une erreur :

Esterreur, estvide, estna, ....

=si(esterreur(A2/A1);A2/A1;"")
= si(a1=0;"";A2/A1)
=si(estvide(A1);0;A2/A1)

=SOMME.SI(A1:A10;"<>#VALEUR!") ' [Objet: Re: erreur : #VALEUR, date : jeudi 23 mars 2000 21:19]
=SI(ESTNA(RECHERCHEV(B6;Feuil2!A1:C10;2;FAUX));0;RECHERCHEV(B6;Feuil2!A1:C10 ;2;FAUX))

NAN est une constante usuelle de nombreux langages et logiciels signifiant un "non-nombre" ("Not A Number").
La valeur NAN a une valeur binaire déterminée. Elle est renvoyée par des calculs sans résultat numérique, tels que log(0) etc.
C'est donc pour Excel l'équivalent du code d'erreur #NOMBRE

A l'aide de VBA, vous pouvez aussi identifier les cellules avec des valeurs d'erreurs.
Vous aurez besoin des fonctions IsError & CVErr.

La procédure ci-dessous donne la valeur 0 aux cellules contenant la valeur d'erreur #REF

Sub Replace()
For Each oCell In Selection
If IsError(oCell) Then
If oCell = CVErr(xlErrRef) Then
oCell.Value = 0
End If
End If
Next oCell
End Sub

La procédure ci-dessous sélectionne les cellules sans valeurs d'erreur de la sélection courante.
'proposé par Gianluca sur la lsite de diffusion excel-l@peeach.ease.lsoft.com, conversation "Re: Excluding Cells w/ Error Values" le mardi 22 mai 2001 à 06h29

Sub MacroNoSelError()
Dim Rango1 As String
For Each celda In Selection
If Not IsError(celda) Then
If Rango1 = "" Then
Rango1 = celda.Address(False, False)
Else
Rango1 = Rango1 & "," & celda.Address(False, False)
End If
End If
Next celda
Range(Rango1).Select
End Sub

J'obtiens le message "type incompatible" puis le bon résultat lorsque je tape dans une cellule la formule =A1,
(réponse de L.L le samedi 13 novembre 1999 00:59)

Ce message d'erreur est affiché en permanence dès que l'on utilise un nom de la bibliothèque d'objets de VBA-Excel dans une formule de feuille de calcul, si le classeur contient du code VBA ou même simplement un module vide.

Par exemple, dans un nouveau classeur, insérer un module VBA et taper dans n'importe quelle cellule la formule =WorkBooks, =Application ou =range. Le message d'erreur "Type incompatible" sera affiché dès qu'on fait une nouvelle saisie dans n'importe quelle cellule.

La solution : supprimer ou modifier la ou les formules fautives, enregistrer le classeur, le fermer et le réouvrir.

 

Erreurs survenant pendant l'exécution d'une macro

Article : "Q186063 - INFO: Translating Automation Errors for VB/VBA". Il explique notamment comment récupérer le contenu d'un message d'erreur.

Autres macros d'intérêt

Identifier & colorier des cellules contenant une fonction de feuille de calcul particulières dans leur formule

'le code ci-dessous est à insérer dans un nouveau module vba
'il boucle sur les cellules de la plage en cours de sélection, récupère leur formule
' et contrôle si elle contient le mot "vlook" à l'aide du mot clé "like".
' vlook étant pour vlookup, recherchev
Option Compare Text
Option Explicit
Sub colorchange1()
Dim s As String
Dim cell As Range
Application.ScreenUpdating = False
With Selection
For Each cell In Selection
s = cell.Formula
If s Like "*vlook*" Then
cell.Interior.ColorIndex = 36 'formats interior as yellow color
End If
Next cell
End With
Application.ScreenUpdating = True
End Sub

Fonctions statistiques

Explication par Christian H. dans le groupe de discussion microsoft.public.fr.excel le dimanche 30 janvier 2000 à 17h59 conversation "Re: Fonctions stats PREVISION et TENDANCE"

PREVISION()

elle sert à extrapoler à partir d'un jeu de données existantes.
exemple : en colonne A (matrice X), tu as les 12 derniers mois, en colonne B (matrice Y) 12 nombres représentants un chiffre d'affaire mensuel.
=prevision(B1:B12;A1:A12) renvoie le chiffre d'affaire prévu pour le mois suivant.

TENDANCE()

Pour mieux comprendre, fais un graphique de type "nuage de points" à partir des données précédentes; insére une courbe de tendance (clic droit sur un point du nuage)
la fonction =tendance(B1:B12;A1:A12) renvoie le coefficient de pente de la droite insérée sur le nuage.

DROITEREG()

Dans sa forme la plus simple (2 cellules voisines sur la même ligne) renvoie le coeff de pente et l'ordonnée à l'origine.
Si tu sélectionnes d'abord une matrice de 5Lx2C, tu obtiens un tableau d'analyse avec tous les paramétres de la régression. (c'est un fonction matricielle donc valider avec Ctrl Shift Entrée)
Ces 3 fonctions sont des variantes issues de la régression linéaire (éventuellement curvilinéaire).