Fonctions diverses

Accueil


Fonctions Est
EstErreur
EstLogique
EstNa
EstTexte
EstRef
EstVide

Fonctions Statistiques
Somme
Moyenne
Max
Min
Nbval
NB
GrandeValeur
Rang
Mode
Frequence

Fonctions Maths
Arrondi

Fonctions Matricielles
Transpose
DroiteReg
Tendance
Adresse

Informations
Cellule
Info

Base de données

BdSomme
Tri Filtre Unique
Recherchex
Si multiple
Si conditions
Minimum Si

Fonctions Est

ESTERR(expression ou cellule)
ESTERREUR(expression ou cellule)

La fonction ESTERR(expression) renvoie une valeur logique vraie. Si le calcul de expression
provoque une erreur (sauf pour #N/A).
La fonction ESTERREUR(expression) renvoie une valeur
logique vraie si le calcul de expression provoque une erreur.

Messages d'erreurs

#DIV/0! 

Division par zéro.

#NOM?  

Le nom de champ spécifié dans la formule n'existe pas

#N/A 

Il n'y a pas de valeur attribuée.

#NUL! 

La formule référence un champ qui n'existe pas.

#NUM! 

La fonction ne reçoit pas une valeur numérique valide

#REF!

Des cellules adressées ont étés supprimées ou des noms de cellules ont étés supprimés.

 #VALEUR! 

L'opération ne fonctionne pas avec les arguments donnés  Exemple: 123*"X".

Sur cet exemple,nous effectuons la multiplication de A1 par A2.Si A1 ou A2 contient un libellé,
il n'y a pas de message d'erreur affiché.

ESTLOGIQUE(cellule)

La fonction ESTLOGIQUE() retourne la valeur VRAI si la cellule spécifiée contient une valeur logique VRAI ou FAUX.

  =ESTLOGIQUE(A1) retourne VRAI si A1 contient FAUX ou VRAI.

ESTNA(cellule)

Renvoie la valeur VRAI si la cellule spécifiée contient #N/A.

ESTTEXTE(cellule)

Renvoie la valeur VRAI si la cellule spécifiée contient du texte.

ESTNUM(expression ou cellule)

Renvoie la valeur VRAI si la cellule spécifiée est un nombre.

ESTREF(cellule)

Renvoie la valeur VRAI si la cellule spécifiée contient #REF.

ESTVIDE(cellule)

Renvoie la valeur VRAI si la cellule spécifié est vide.

Fonctions statistiques

SOMME (champ)

  Donne la somme des nombres d'un champ.

MOYENNE (champ1;champ2;...)

 Donne la moyenne des nombres d'un champ.

MAX (champ)

  Donne le plus grand des nombres d'un champ.

MIN (champ)

Donne le plus petit des nombres d'un champ.

NBVAL (champ)

Donne le nombre de cellules occupées d'un champ.

NB (champ)

La fonction NB(champ) donne le nombre de cellules numériques d'un champ.

GRANDE.VALEUR(champ;n)

Donne la neme valeur la plus grande d'un champ

=GRANDE.VALEUR(A2:A7;1) donne la plus grande valeur du champ A2:A7

=GRANDE.VALEUR(A2:A7;2) donne la 2eme plus grande valeur du champ A2:A7

Moyenne des 10 plus grandes valeurs

=MOYENNE(GRANDE.VALEUR(A2:C14;LIGNE(INDIRECT("1:10"))))
Valider avec Maj+ctrl+entrée

RANG(nombre;champ;ordre)

Donne le rang d'un nombre dans un champ

Sur champ discontinu

Ne tient pas compte des doublons

Il y a 2 fois 12. Le nombre 10 est est considéré en 3e position

Par catégorie

MODE(champ)

Donne la valeur numérique le plus fréquente d'un champ

Fonction mode conditionnelle

=MODE(SI($A$2:$A$30=F2;$C$2:$C$30))
Valider avec maj+ctrl+entrée

Mot le plus fréquent dans un champ

=INDEX(champ;MODE(SI(ESTNUM(EQUIV(champ;champ;0));EQUIV(champ;champ;0);FAUX)))

FREQUENCE(ChampDonnées;ChampTranche)

Donne la fréquence d'apparition de valeurs numériques dans chaque intervalle de tranches.

Fréquence

Fonctions Maths

ARRONDI (nombre ;nombre_décimales)

Arrondit un nombre

ENT(nombre)

Donne la partie entière d’un nombre.

Fonctions Matricielles

TRANSPOSE(Champ)

Fait pivoter un champ

DROITEREG(y_connus;x_connus)

Donne les coéfficients d'une droite de régression.

TENDANCE(yConnus;xConnus;Xnouveaux;constante)

-Sélectionner C2:C9
=TENDANCE(B2:B9;A2:A9)

-Valider avec Maj+ctrl+entrée

-Sélectionner C10:C13
=TENDANCE(B2:B9;A2:A9;A10:A13)
-Valider avec Maj+ctrl+entrée

ADRESSE(NoLigne;NoColonne;ModeAdresse;R1C1;Feuille)

NoLigne : numéro de la ligne.
NoColonne: le numéro de la colonne.
ModeAdr : type d'adressage

ModeAdresse
1 Absolu
2 Ligne absolue, colonne relative
3 Ligne relative, colonne absolue
4 Relative

R1C1 spécifie le type de référence (A1 ou R1C1).
Feuille spécifie le nom de la feuille de calcul.

=ADRESSE(2,3)

$C$2

=ADRESSE(2;3;2)

C$2

=ADRESSE(2;3;2;FAUX)

R2C[3]

=ADRESSE(2;3;1;FAUX,"[Classeur1]Feuil1")

[Classeur1]Feuil1!R2C3

=ADRESSE(2;3;1;FAUX,"FEUILLE EXCEL ")

'FEUILLE EXCEL'!R2C3

=SUBSTITUE(ADRESSE(1;2;4);"1";"")

B

Informations

CELLULE(TypeInfo;référence)

TypeInfo

 

adresse

=CELLULE("adresse";B10) donne $B$10

col

=CELLULE("col";B10) donne 2

format

=CELLULE("format";A1) donne F2 pour 2 décimales

ligne

=CELLULE("ligne";B10) donne 10

protege

=CELLULE("protege";A1) donne 0 ou 1

type

=CELLULE("type";A1) -> i,l,v pour vide,libellé,numérique


CELLULE("nomfichier ou filename";A1)

La fonction CELLULE("filename ou nomfichier ";A1) donne répertoire+nom_fichier+nom_onglet:

Nom de l'onglet:
=STXT(CELLULE("filename";A1);TROUVE("]";CELLULE("filename";A1))+1;99)

Chemin+nomFichier:
=GAUCHE(CELLULE("filename";A1);TROUVE("]";CELLULE("filename";A1)))
-->C:\mesdoc\excelmacronouveau\1001exemples\[Classeur1.xls]

=SUBSTITUE(SUBSTITUE(GAUCHE(CELLULE("filename";A1);TROUVE("]";CELLULE("filename";A1)));"[";"");"]";"")
-->C:\mesdoc\excelmacronouveau\1001exemples\Classeur1.xls

Nom du fichier:
=STXT(CELLULE("filename";A1);TROUVE("[";CELLULE("filename";A1))+1;TROUVE("]";
CELLULE("filename";A6))-TROUVE("[";CELLULE("filename";A1))-1)

INFO(TypeInfo)

TypeInfo

 

cellule

 

memdispo

mémoire disponible

recalcul

le mode de recalcul en cours:Automatique,Manuel

répertoire

le chemin d'accès au répertoire ou au dossier en cours.

systexpl

nom de l'environnement d'exploitation : pcdos ou mac

version

version excel ,9,10,..

versionse

version du système d'exploitation

 

BDSomme(base;ChampSomme;Critère)

Les fonctions sur la base de données BDSomme(), BDMoyenne(), BDNbVal() nécessitent un champ critère . Plus contraignantes que Sommeprod(), ces fonctions ont l'avantage d'être plus rapides.

On veut la somme des factures non réglées antérieures au 25/12/2008.
Le critère de sélection est défini en F4:G5.

=BDSOMME(A1:D30;"montant";F4:G5)
=BDNBVAL(A1:D30;"facture";F4:G5)

Avec Sommeprod()
=SOMMEPROD((B2:B30="N")*(D2:D30<=DATEVAL("25/12/2008"))*C2:C30)

BDSomme

Le critère peut être une formule en F4: =(D2<=$F$2)*(B2=$H$2)

Donées/Table

Ancêtre des TCD, Données/Table permet d'obtenir des statistiques.
Sur cet exemple, on obtient l'effectif et le salaire moyen par service.

- Ecrire les formules:
     G6:=BDNBVAL(A1:D22;"nom";F1:F2)
     H6:=BDMOYENNE(A1:D22;"salaire";F1:F2)
- Sélectionner F6:H9
- Données/Table
- Celllule entrée colonne: F2

Si la BD est modifiée, la Maj du tableau est automatique (contrairement aux TCD).

Exemples

BDLire(base;ChampRetour;Critère)

On récupère la ville pour le nom et le prénom choisis.

=BDLIRE(A1:D10;"ville";F1:G2)

BdLire

Fonction Filtre(champ_données;champ_critère;critère) (Excel 2019)

Fonction Filtre

=FILTRE(Tableau1;Tableau1[Ville]=E2)

Fonction Tri(champ_données;colonneTri) (Excel 2019)

On peut combiner Filtre() et Tri()

=TRI(FILTRE(Tableau2;Tableau2[Ville]=D2))

Fonction Unique(champ_colonne) (Excel 2019)

=TRI(UNIQUE(A2:A16))

Fonctions personalisées

Fonction tri 1 colonne
Fonction tri une BD
Fonction Filtre une BD avec 2 critères

Recherchex(valeurCherchée;tableRecherche;tableRésultat;[messageErreur];[match];[modeRecherche])

match
0 égalité
-1 égalité ou prochain plus petit
1 égalité ou prochain plus grand
2 avec joker * /?

modeRecherche
1 premier dernier
-1 dernier premier
2 premier dernier/recherche dichotomique(trié)
-2 dernier premier/recherche dichotomique(trié)

Le reproche qui est souvent fait à la fonction Recherchev() est que la recherche se fait uniquement dans la première colonne, d'où l'utilisation de Equiv()/Index().

Recherchex permet de rechercher comme Equiv/Index.

Avec la recherche dichotomique (table triée), le temps de recherche est X fois inférieur.

Fonction recherchex

Fonction Joindre.texte(séparateur;vide;champ) (Excel 2019)

Fonction Joindre.Texte

Fonction Sequence(début;fin) (Excel 2019)

Fonction Séquence

Ecrit Janvier 20 à décembre 20

=TEXTE(DATE(ANNEE(AUJOURDHUI());SEQUENCE(1;12);1);"mmm aa")

C'est l'équivalent de

=TEXTE(DATE(ANNEE(AUJOURDHUI());{1.2.3.4.5.6.7.8.9.10.11.12};1);"mmmm aa ") en matriciel

Si.Multiple(test;1;cas1;2;cas2;3;cas3;aucunCas)

Fonction Si.Multiple

Si.Conditions(test1;cas1;test2;cas2;......)

Fonction Si.conditions

MIN.SI.ENS(plage_min; plage_critère1; critère1; [plage_critère2; critère2]; ...)

Fonction Min.Si

 

 

 

 

 

Exemples

Fonctions diverses