LOC – 13/05/2018 – 2

Plan de la fiche

1. Avant de commencer

2. Fonctions courantes

2.1 Calculs

Abs(), Exp(), Fact(), Mod()

Produit(), Puissance(), Quotient(), Racine()

Signe()

Somme()

2.2 Troncatures et arrondis

Arrondi(), Arrondi_precision(), Arrondi.au.multiple(), Arrondi.inf(), Arrondi.sup()

Ent(), Tronque()

Impair(), Pair()

Iso.plafond(), Plafond(), Plafond.math(), Plafond.precis()

Plancher(), Plancher.math(), Plancher.precis()

2.3 Autres

Alea()

Alea.entre.borne()

Pgcd()

Ppcm()

3. Fonctions orientées base de données et statistiques

Somme.si()

Somme.si.ens()

Grande.valeur(), Petite.valeur()

Max(), Maxa(), Max.si()

Min(), Mina(), Min.si()

Mode(), Mode.multiple(), Mode.simple()

Moyenne(), Moyennea(), Moyenne.si(), Moyenne.si.ens()

Nb(), Nb.si(), Nb.si.ens()

Nb.vide(), Nbval()

Rang()

4. Fonctions exotiques

Couleur()

Euroconvert()

Présentation

Les fonctions mathématiques sont nombreuses. J’ignore ici les fonctions trigonométriques, statistiques et probabilités, binaires (bit à bit – logique informatique) et financières. Ces fonctions sont spécialisées et largement parlantes pour ceux qui veulent les utiliser.

Les fonctions courantes sont regroupées en trois paquets : calculs, troncatures et arrondis, autres.

Un paragraphe est consacré aux fonctions orientées base de données et statistiques, et un autre aux fonctions que je qualifie d’exotiques, amusantes à découvrir.

1. Avant de commencer

Les formules mathématiques n’ont pas nécessairement recours aux fonctions. Il est possible de construire des formules en utilisant des constantes ou des variables, et les opérateurs + – * / ^. Avec ou sans séparateurs (parenthèses).

2. Détail des fonctions courantes

2.1 Calculs

Abs(Nombre)

Retourne la valeur absolue d’un nombre, c’est-à-dire pour un nombre négatif, sa valeur positive.

Exp(Exposant)

Retourne la valeur e^Exposant

Fact(Nombre)

Retourne la factorielle de Nombre.

Mod(Dividende; Diviseur)

Retourne le reste de la division entière de Dividende par Diviseur.

Identique à Quotient().

Produit(Liste de nombres)

Retourne le produit des Nombres de la Liste. 30 arguments sont possibles. Une zone de cellules (références extrêmes en diagonale séparée par “:”) peut être posée en argument, ainsi qu’une zone nommée.

Comparable à Somme().

Puissance(Base; Exposant)

Équivalent à Base^Exposant.

Voir aussi Exp().

Quotient(Dividende; Diviseur)

Retourne le reste de la division entière de Dividende par Diviseur.

Identique à Mod().

Racine(Nombre)

Retourne la racine carrée de Nombre.

Voir Racine.pi().

Signe(Nombre)

Renvoie le signe de Nombre : -1, si négatif; 1, si positif; 0 si Nombre est nul.

Somme(Liste de nombres)

Retourne la somme des Nombres de la liste. 30 arguments sont possibles. Une zone de cellules (références extrêmes en diagonale séparée par “:”) peut être posée en argument, ainsi qu’une zone nommée.

Ressemble à Produit() ; voir aussi Somme.si().

2.2 Troncatures et arrondis

Arrondi(Base [, Nombre de décimales])

Renvoie Base en ne gardant que le Nombre de décimales arrondies au plus près.

Si Nombre de décimales est omis, il est pris pour 0.

Voir aussi : Arrondi_precision(), Arrondi.au.multiple, Arrondi.inf(), Arrondi.sup().

Arrondi_precision(Base; Niveau)

Niveau est le nième chiffre de Base, en partant de la gauche, qui supporte l’arrondi, décimales comprises (sans compter le séparateur).

Fonction intéressante pour avoir des nombres ronds quand le nombre de chiffres devant la virgule est connu.

Voir aussi : Ent(), Arrondi(), Arrondi.au.multiple, Arrondi.inf(), Arrondi.sup().

Arrondi.au.multiple(Base; Arrondi)

Retourne Base arrondie au plus près avec Arrondi.

L’intitulé de la fonction, dans les deux premiers exemples, pourrait être Arrondi au cent près.

Comparée à Arrondi_precision(), cette fonction permet un arrondi identique dans une liste de nombres quel que soit le nombre de chiffres devant la virgule de chacun.

Voir aussi Arrondi(), Arrondi_precision(), Arrondi.inf(), Arrondi.sup().

Arrondi.inf(Base; Nombre de décimales)

Retourne Base avec Nombre de décimales. La dernière décimale n’est pas modifiée. Arrondi dit « par défaut », « vers le bas » ou « vers zéro ».

Voir aussi : Arrondi(), Arrondi_precision(), Arrondi.au.multiple, Arrondi.sup().

Arrondi.sup(Base; Nombres de décimales)

Retourne Base avec Nombre de décimales arrondi par excès.

Voir aussi : Arrondi(), Arrondi_precision(), Arrondi.au.multiple, Arrondi.inf().

Ent(Nombre)

Transforme Nombre en entier arrondi par défaut.

Identique à Arrondi(Nombre; 0).

Impair(Nombre)

Si Nombre est positif, retourne le premier entier impair plus grand que  Nombre (ou égal à Nombre si celui-ci est un entier impair positif).

Si Nombre est négatif, retourne le premier entier impair négatif plus petit que Nombre (ou égal à Nombre si celui-ci est un entier impair négatif).

Comparable à Pair().

Iso.plafond(Nombre; Multiple)

Renvoie un multiple de Multiple immédiatement supérieur à Nombre, quel que soit le signe de Nombre.

Pair(Nombre)

Si Nombre est positif, retourne le premier entier pair supérieur à Nombre (ou égal à Nombre si celui-ci est un entier pair positif).

Si Nombre est négatif, retourne le premier entier pair négatif inférieur à  Nombre (ou égal à Nombre si celui-ci est un entier pair négatif).

Comparable à Impair().

Plafond(Nombre; Multiple [; Mode])

Retourne le Multiple immédiatement supérieur à Nombre suivant le Mode indiqué (Multiple par excès).

Nombre et Multiple doivent être de même signe (positifs ou négatifs tous les deux ; sinon, une erreur #502 est retournée).

Mode est une valeur logique (FAUX, 0 ou omis ; VRAI, 1 ou toute autre valeur positive) qui n’est prise en compte que si Nombre et Multiple sont négatifs. FAUX retourne alors le multiple le plus proche de zéro (plus grand que Nombre) ; VRAI retourne le multiple le plus loin de zéro (plus petit que Nombre).

Voir Plafond.math(), Plafond.precis(), Plancher(), Plancher.math, Plancher.precis.

Plafond.math(Nombre; Multiple [;Mode])

Fonction identique à Plafond().

Voir Plafond(), Plafond.precis(), Plancher(), Plancher.math(), Plancher.precis().

Plafond.precis(Nombre; Multiple)

Fonction comparable à Plafond() avec cette nuance que Nombre et Multiple peuvent être de signe différent, et que Multiple est toujours interprété comme étant positif.

Voir Plafond(), Plafond.math(), Plancher(), Plancher.math(), Plancher.precis().

Plancher(Nombre; Multiple; Mode)

Fonction analogue à Plafond() avec cette nuance que le résultat est le Multiple le plus près de zéro (Multiple par défaut).

Voir Plafond(), Plafond.math(), Plafond.precis(), Plancher.math(), Plancher.precis().

Plancher.math(Nombre; Multiple; Mode)

Fonction identique à Plancher().

Voir Plafond(), Plafond.math(), Plafond.precis(), Plancher(), Plancher.precis().

Plancher.precis(Nombre; Multiple)

Fonction analogue à Plancher() avec cette nuance que Nombre et Multiple peuvent être de signe différent, et que Multiple est toujours interprété comme étant positif.

Voir Plafond(), Plafond.math(), Plafond.precis(), Plancher(), Plancher.math().

Tronque(Base; Nombre de décimales)

Transforme Base en ne laissant que le Nombre de décimales, sans arrondi.

Les chiffres non significatifs ne sont pas ajoutés aux décimales.

2.3 Autres

Alea()

Renvoie un nombre aléatoire entre 0 et 1. Il suffit de le multiplier par une puissance de 10 pour avoir un nombre à plusieurs chiffres, et d’arrondir le résultat.

Voir aussi : Alea.entre.bornes().

Alea.entre.bornes(Minimum; Maximum)

Renvoie un nombre aléatoire entre et y compris Minimum et Maximum.

Minimum et Maximum sont deux nombres quelconque.

Alea.entre.bornes (0; 1) est identique à Alea().

Pgcd(Liste de nombres)

Retourne le plus grand commun diviseur des Nombres de la Liste. 30 nombres maximum peuvent être mis en paramètres.

Comparable à Produit(), Somme().

Voir aussi Ppcm().

Ppcm(Liste de nombres)

Retourne le plus petit commun multiple des Nombres de la Liste. 30 nombres maximum peuvent être mis en paramètres.

Comparable à Produit(), Somme().

Voir aussi Pgcd().

3. Fonctions orientées base de données et statistiques

Calc classe (dans l’assistant fonction et dans l’aide) les fonctions Somme.si() et Somme.si.ens() dans les fonctions mathématiques, et les autres fonctions décrites dans ce paragraphe dans les fonctions statistiques.

Somme.si (Plage de recherche; Valeur recherchée; Valeur additionnée)

Voilà une fonction particulièrement puissante.

Plage de recherche est une colonne servant de de référence dans un tableau quelconque.

Valeur recherchée est une valeur unique qui va être cherchée dans la Plage de recherche. Ce peut être une variable ou une constante. La constante peut utiliser les caractères génériques (voir ici : 3.3 Composer une formule, §3.3 Utiliser les caractères génériques ou les expressions régulières).

Valeur additionnée est une colonne du tableau quelconque dans laquelle se trouve les éléments à additionner.

La fonction balaie toute la colonne Plage de recherche. Quand elle trouve une ligne portant la Valeur recherchée, elle additionne la valeur de la colonne Valeur additionnée présente sur la même ligne. Et continue la recherche jusqu’à la fin de la Plage de recherche.

Un bon exemple de l’utilisation de cette fonction est dans la colonne Stock de la première feuille de MaCave. Le stock est calculé en ajoutant les entrées et en soustrayant les sorties à partir d’une concordance des lignes sur la référence du lot (première colonne dans les trois tableaux).

Cette fonction est classée par Calc dans les fonctions mathématiques

Voir aussi : Somme.si.ens().

Somme.si.ens(Zone à totaliser; Zone à tester 1; Test 1; Zone à tester 2; Test 2; …; Zone à tester 127; Test 127)

Voilà une extension de la fonction Somme.si().

127 couples Zone à tester, Test sont possibles.

Zone à totaliser est une colonne dans laquelle se trouve des montants que la fonction va totaliser en fonction des critères qui suivent. Il faut que tous les tests de la fonction soient VRAI pour que la somme soit effectuée. Ce qui revient à dire que la fonction cesse ses explorations dès qu’un test sort FAUX.

Les zones Zone à totaliser, Zones à tester doivent être de dimensions identiques, sinon l’erreur #502 est retournée. Elles peuvent être en colonnes ou en lignes.

Test x peut être une référence de cellule : dans ce cas, si la valeur de cette cellule est trouvée dans la Zone à tester x à la position y, le test retourne VRAI et la somme située à la position y de Zone à totaliser est ajouté au résultat de la fonction.

La cellule référencée peut contenir une expression conditionnelle analogue à celle du Test x.

Test x peut contenir une expression conditionnelle (constante de texte ou concaténation de plusieurs expressions de type Texte). Cette expression est de la forme : opérateur de comparaison (en constante de texte entre guillemets doubles) suivi du critère de comparaison (constante avec ou sans fonctions retournant du texte). La constante peut intégrer des caractères génériques. Voir ici : 3.3 Composer une formule, §3.3 Utiliser les caractères génériques ou les expressions régulières).

Voici un exemple dans une application de trésorerie domestique. La cellule qui contient la formule ci-dessous récupère toutes les dépenses d’une catégorie donnée ($A2) antérieures ou égales à une date donnée (TBB_date_fin).

La formule contient trois parties : la colonne dans laquelle se trouvent les montants à totaliser sous conditions (CPT1_liste_Dep) et deux ensembles zone à tester et test : sur les catégories (CPT1_liste_Categ; $A2) et sur les dates (CPT1_liste_Dates; « <= »&TBB_date_fin).

Concernant la zone des montants à totaliser sous conditions, je note qu’elle se situe sur une autre feuille que ma formule (CPT1). Il en sera de même pour les zones à tester.

Concernant le test sur les catégories, je note que la catégorie à tester ($A2) est sur la même feuille que la formule. En fait, cette formule est dans un tableau (CALC) qui récupère les montants réels par catégories pour les comparer aux budgets.

Concernant le test sur les dates, je note que la condition exploite une zone nommée (TBB_date_fin) récupérée dans le tableau de bord (TBB) qui rassemble les paramètres de l’analyse budgétaire.

Comment fonctionne la formule ?

Le système commence par mettre à zéro le cumul calculé. Il balaie ensuite le tableau CPT1 dans la colonne Catégorie. Quand il trouve une catégorie égale à celle présente sur la même ligne que la formule (je note que la référence ligne (2 dans A2) est relative), il s’arrête sur la ligne et passe au test suivant. Je note cette ligne : ligne Y.

Dans la zone CPT1_Listes_Dates, le système teste la date de la ligne Y en regard de TBB_fin_mois. Si la date est effectivement inférieure ou égale, il prend le montant dans la zone CPT1_Liste_Dep, à la ligne Y et le cumule dans le total de la formule. Si la date est supérieure, il ne fait rien et revient au balayage du tableau CPT1 dans la colonne Catégorie et passe à la ligne suivante (Y+1).

En fin de colonne, il renvoie le cumul fait comme résultat de la formule.

De la sorte, et quelle que soit la position de la Catégorie dans le tableau CPT1, les sommes correspondantes sont rassemblées en un montant si les dates d’opération sont inférieures ou égales à celle calculée en argument. Et ce montant est le résultat de la formule.

Voir aussi Somme.si().

Grande.valeur (Plage de nombres; Rang)

Renvoie la plus grande valeur de rang Rang (la première plus grande, la deuxième plus grande, etc.) trouvée dans Plage de nombres, .

Comparable à Petite.valeur().

Max (Nombre1; Nombre2; …; Nombre30)

Renvoie le NombreX le plus grand, ou 0 si les NombreX appellent des cellules de type Texte. Si un des arguments est une chaîne de caractères, renvoie l’erreur 504.

Trente arguments sont possibles, et un argument peut être une zone de cellules extrêmes séparées par “:”.

Comparable à Min(), Moyenne(), Produit(), Somme().

Voir aussi Maxa(), Max.si().

Maxa (Argument 1; Argument 2; …; Argument 30)

Retourne l’argument maximum.

Si un argument est de type Texte, il est pris pour 0.

Si aucun argument n’est de type Texte, identique à Max().

Max.si (Zone à maximaliser; Zone à tester 1; Test 1; Zone à tester 2; Test 2; …; Zone à tester 127; Test 127)

Comparable à Somme.si.ens().

Les Zones (à maximaliser, à tester X) doivent être de mêmes dimensions sinon Err.502 est retournée.

Test X accepte, en fonction des paramétrages système, les caractères génériques et les expressions régulières.

Les valeurs de type Texte sont prises pour 0.

Comparable à Min.si(), Moyenne.si().

Min (Nombre1; Nombre2; …; Nombre30)

Comparable à Max().

Mina (Argument 1; Argument 2; …; Argument 30)

Comparable à Maxa().

Min.si (Zone à minimaliser; Zone à tester 1; Test 1; Zone à tester 2; Test 2; …; Zone à tester 127; Test 127)

Comparable à Max.si().

Mode (Argument 1; Argument 2; …; Argument 30)

Renvoie l’Argument X le plus fréquent de la liste des arguments.

Si tous les arguments sont différents, renvoie l’erreur #Valeur! Voir Sierreur() pour s’en sortir.

Si plusieurs arguments ont la même fréquence, renvoie le plus petit.

Voir Mode.multiple(), Mode.simple().

Mode.multiple (Argument 1; Argument 2; …; Argument 30)

Fonction de matrice. Préférer Mode.simple().

Mode.simple (Argument 1; Argument 2; …; Argument 30)

Identique à Mode().

Moyenne (Nombre1; Nombre2; …; Nombre30)

Comparable à Min(), Max(), Moyenne, Produit(), Somme().

Si Nombre X contient une chaîne de caractères, il est ignoré de la liste (dans la somme et le nombre).

Voir aussi Moyennea().

Moyennea (Nombre1; Nombre2; …; Nombre30)

Par rapport à Moyenne() : si un Nombre X contient du texte, il est pris pour 0 et compté dans le dénominateur.

Comparable à Mina(), Maxa().

Moyenne.si (Plage de recherche; Valeur recherchée[; Valeur additionnée])

Comparable à Somme.si().

Si Valeur additionnée est omise, la Plage de recherche est prise comme zone de Valeurs additionnée.

Si une cellule dans la zone Valeur additionnée est vide ou contient du texte, elle est ignorée (dans la somme et le nombre).

Si Valeur recherchée n’est pas trouvée, renvoie l’erreur #Div/0!.

Voir aussi Moyenne.si.ens().

Moyenne.si.ens (Zone à totaliser; Zone à tester 1; Test 1; Zone à tester 2; Test 2; etc.)

Comparable à Somme.si.ens().

Si une cellule de Zone à totaliser est vide ou contient du texte, elle est ignorée.

Si une cellule de Zone à totaliser contient la valeur VRAI, elle est prise pour 1; si elle contient la valeur FAUX, elle est prise pour 0.

Si les zones sont de dimensions différentes, une erreur Err. 502 est retournée. 

Nb (Argument 1; Argument 2; …; Argument 30)

Renvoie le nombre d’arguments de type non-texte.

Voir aussi Nb.si(), Nb.si.ens().

Nb.si (Plage de recherche; Valeur recherchée)

Ressemble à Somme.si().

Renvoie le nombre d’éléments de la Plage de recherche satisfaisant au critère Valeur recherchée.

Valeur recherchée admet les expressions régulières.

Nb.si.ens (Zone à tester 1; Test 1; Zone à tester 2; Test 2; …; Zone à tester 127; Test 127)

Ressemble à Somme.si.ens().

Nb.vide (Plage de recherche)

Renvoie le nombre de cellules vides de la Plage de recherche.

Une cellule vide est une cellule sans information. Une cellule nulle contient l’information 0.

Voir aussi Nbval().

Nbval (Plage de recherche)

Renvoie le nombre de cellules non vides de la Plage de recherche.

Voir aussi Nb.vide().

Petite.valeur (Plage de nombres; Rang)

Comparable à Grande.valeur().

Rang (Valeur cherchée; Zone de recherche [; Tri])

Renvoie le rang dans Zone de recherche, de Valeur recherchée. Si Tri = 1, la zone de recherche est parcouru naturellement du premier au dernier élément. Si Tri =0 (valeur par défaut), la Zone de recherche est parcouru du dernier élément au premier.

La fonction retourne le rang du premier élément trouvé.

Si Valeur recherchée n’existe pas dans Zone de recherche, #Valeur! est retourné. Voir Sierreur() pour s’en sortir au mieux.

4. Fonctions exotiques

Couleur(Rouge; Vert; Bleu [; Alpha])

Retourne un nombre correspondant aux couleurs entrées. Ce nombre en base décimale peut ensuite être traduit en base hexadécimale pour correspondre aux couleurs HTML.

Pour les accros des présentations colorées, permet de faire des tests sur les couleurs.

Euroconvert(Valeur; De monnaie; Vers monnaie; Précision)

Cette fonction permet de convertir Valeur exprimée en De monnaie dans la monnaie Vers monnaie. De monnaie et Vers Monnaie doivent correspondre aux monnaies remplacées par l’Euro au moment de sa mise en place. Elles sont exprimées avec leur code ISO 4217 (EUR, FRF, DEM, etc.). Précision indique si la transformation comporte toutes les décimales ou seulement celles de Vers monnaie. Précision omis est pris pour 0.

Utile en son temps, cette fonction est devenue quasiment obsolète.

Informations complémentaires

Liens

3.3 Composer une formule, §3.3 Utiliser les caractères génériques ou les expressions régulières