Accueil
Accueil Le Club Delphi Kylix C C++ Java J2EE DotNET & C# Visual Basic Access Pascal Dev Web PHP ASP XML UML SQLSGBD Windows Linux Autres
logo
Sommaire > Les formules
        Ou peut on trouver des exemples pour utiliser les fonctions du tableur Excel ?
        Comment remplacer une formule par son résultat ?
        Quelle sont les différences entre les références relatives, absolues et mixtes ?
        Comment verrouiller la ligne et la colonne (références) d'une cellule contenue dans une formule ?
        Comment afficher la boîte de dialogue 'Arguments de la fonction' pendant la saisie d'une formule ?
        Comment afficher les arguments d'une fonction dans la barre de formules ?
        Comment gérer les valeurs d'erreur dans le résultat des fonctions ?
        Pourquoi autant de propriétés 'Formula' différentes ?
        Peut on effacer les formules pour ne garder que les valeurs ?
        Ou trouver la traduction des fonctions Excel ?
        Comment vérifier rapidement l'orthographe des fonctions ?
        Comment identifier les cellules qui contiennent des formules ?
        A quoi sert la propriété WorksheetFunction ?
        Comment lire une formule par formule ?
        Comment Additionner des cellules en fonction de la couleur de fond, sans macro ?
        Comment extraire les mots d'une phrase ?
        Comment créer rapidement une table de multiplication ?
        Comment utiliser les fonctions de l'utilitaire d'analyse dans Excel2007 ?
        Comment fonctionne la formule SIERREUR dans Excel 2007 ?
        Comment utiliser la fonction SOMME.SI.ENS dans Excel 2007 ?

precedent    sommaire    suivant   


Comment remplacer une formule par son résultat ?
auteur : SilkyRoad
Pour remplacer la formule contenue dans une cellule par le résultat qu'elle renvoie:
Sélectionnez la cellule.
Cliquez sur la touche clavier F2
Puis sur la touche clavier F9.



Quelle sont les différences entre les références relatives, absolues et mixtes ?
auteur : SilkyRoad
Les références relatives:

Une référence relative (qui s'affiche sous la forme =A1+A2) est basée sur la position relative de la cellule qui contient la formule et les références de cellules spécifiées dans la formule. Si la position de la cellule qui contient la formule change, la référence est modifiée. Si vous copiez la formule dans d'autres lignes ou colonnes, la référence est automatiquement adaptée en conséquence.

Par exemple, si vous copiez une référence relative =A1+A2 contenue dans la cellule B2 vers la cellule B3, la formule collée est automatiquement transformée en =A2+A3.

Utilisez des références relatives si vous souhaitez adapter les références à chaque cellule de la plage sélectionnée.



Les références absolues (Utilisation du symbole $):

Une référence de cellule absolue (qui s'affiche sous la forme =$A$1+$A$2) spécifie des cellules se trouvant à un endroit fixe. Si la position de la cellule qui contient la formule change, la référence absolue reste inchangée.

Par exemple, si vous copiez une référence absolue =$A$1+$A$2 de la cellule B2 vers la cellule B3, la formule reste la même dans les deux cellules.
Les formules utilisent des références relatives par défaut. Il faut donc les transformer en références absolues manuellement en ajoutant les symboles $.



Les références mixtes:

Une référence mixte comprend soit une colonne absolue et une ligne relative ($A1,$B1,...), soit une ligne absolue et une colonne relative (A$1,B$1,...).

Par exemple, si vous copiez une référence mixte =A$1*2 de la cellule A2 vers la cellule B3, la formule est transformée en =B$1*2.



Comment verrouiller la ligne et la colonne (références) d'une cellule contenue dans une formule ?
auteur : Lou Pitchoun
L'utilité est la suivante: éviter la modification d'une formule de calcul lors d'une recopie (peut importe le sens : haut, bas, droite ou gauche).
Par exemple vous voulez sur une dizaine de ligne faire référence à la cellule D5.
Dans votre 1ère cellule (B6) vous tapez =D5. Si vous faites une recopie vers le bas sur 10 lignes, la cellule B15 aura pour formule =D14.

La solution pour avoir =D5 dans B15 :
Placer le curseur de la souris sur le nom de la cellule dans la barre de formule.
Une pression sur la touche F4 verrouille la colonne et la ligne.
Résultat : =$D$5
Une seconde pression verrouille la ligne.
Résultat : =D$5
Une troisième, verrouille la colonne.
Résultat : =$D5
Une quatrième déverouille la colonne.
Résultat : =D5



Comment afficher la boîte de dialogue "Arguments de la fonction" pendant la saisie d'une formule ?
auteur : SilkyRoad
Saisissez dans une cellule le début de la fonction, par exemple:
=NBVAL
Ensuite, appuyez sur la combinaison de touches Ctrl+A afin d'afficher la boîte de dialogue "Arguments de la fonction" associée à cette formule.



Comment afficher les arguments d'une fonction dans la barre de formules ?
auteur : SilkyRoad
Saisissez dans une cellule le début de votre formule, par exemple:
=SOMME.SI
Ensuite, appuyez sur la combinaison de touches Ctrl+Shift+A: Les arguments de la fonction s'affichent dans la barre de formule.



Pourquoi autant de propriétés 'Formula' différentes ?
auteurs : Bidou, Etienne Bar
Regardons ensemble. Un objet Range possède des propriétés Formula, FormulaArray, FormulaLocal, FormulaR1C1, FormulaLocalR1C1
La propriété FormulaArray sert à entrer des formules matricielles.
Par exemple

Vba

Range("E1:F11").FormulaArray = "=LINEST(R1C3:R20C3,R1C2:R20C2,TRUE,TRUE)"


Ce code renvoie la matrice de réponses de la formule DROITEREG d'Excel.
Notez que ce n'est pas DROITEREG mais LINEST qui est utilisé. Pourquoi ? Intrinsèquement, Excel utilise les formules internationales (donc anglaise). Mais il n'est pas toujours évident de connaître la syntaxe internationale. On peut donc toujours utiliser la syntaxe locale en appelant la propriété locale équivalente. Ainsi les deux formules suivantes sont équivalentes.

Vba
 
Range("E18").Formula = "=OFFSET(C1,MATCH(16,A1:A20,0)-1,0)"
Range("E18").FormulaLocal = "=DECALER(C1;EQUIV(16;A1:A20;0)-1;0)"


Je peux aussi entrer la formule avec une référence LC (RC international) et j'utilise alors la version R1C1 des propriétés

Vba

Range("E18").FormulaR1C1 = "=OFFSET(R1C3,MATCH(16,R1C1:R20C1,0)-1,0)"
Range("E18").FormulaR1C1Local = "=DECALER(L1C3;EQUIV(16;L1C1:L20C1;0)-1;0)"

Peut on effacer les formules pour ne garder que les valeurs ?
auteur : Bidou
Généralement lorsqu'on utilise Excel on utilise un collage spécial pour cela. Mais par le code il vaut mieux passer par une astuce toute simple.

Vba

Range("F1:H20").Value = Range("F1:H20").Value


Pourquoi cela fonctionne ? Car l'affectation explicite d'une valeur à la propriété value revient à supprimer la valeur de la propriété Formula. En effet on ne peut pas affecter une valeur à une cellule contenant une formule sans écraser celle-ci afin d'éviter un conflit entre la valeur calculée et la valeur affectée.



Ou trouver la traduction des fonctions Excel ?
auteur : SilkyRoad
Recherchez le fichier Excel nommé VBALIST.xls sur votre PC.
Il est généralement stocké à l'emplacement:
C:\Program Files\Microsoft Office\Office10\1036
Ce classeur contient une liste des Fonctions de feuille de calcul et leur traduction Français/Anglais.


La macro suivante permet aussi de traduire en anglais la formule que vous avez saisi dans la cellule A1:

Vba

MsgBox Range("A1").Formula

Comment vérifier rapidement l'orthographe des fonctions ?
auteur : SilkyRoad
Saisissez les noms de fonctions en minuscules.
Les noms de fonctions valides seront automatiquement transformées en majuscule lorsque vous appuierez sur la touche Entrée.



Comment identifier les cellules qui contiennent des formules ?
auteur : SilkyRoad
La macro boucle sur les cellules est applique la couleur jaune si elles contiennent une formule.

Vba

Sub IdentifierFormules() 
    Dim Cell As Range 
    
    'Boucle sur les cellules utilisées dans la Feuil1 et applique 
    'une couleur de fond jaune si elle contient une formule. 
    For Each Cell In Worksheets("Feuil1").UsedRange.Cells 
        If Cell.HasFormula Then Cell.Interior.ColorIndex = 6 
    Next Cell 
End Sub 



Une autre possibilité sans boucler sur les cellules:

Vba

'Renvoie une erreur si la feuille ne contient pas de formules 
Worksheets("Feuil1").Cells.SpecialCells(xlCellTypeFormulas).Interior.ColorIndex = 6

A quoi sert la propriété WorksheetFunction ?
auteur : Bidou
Elle renvoie la collection des fonctions intégrées d'Excel. Ceci est très pratique dans de nombreux cas.
Par exemple, dans Excel 97, la fonction VBA Replace n'existe pas. Mais Excel fournit sa fonction de feuille SUBSTITUTE qui est équivalente. On peut l'utiliser par le biais de WorksheetFunction.

Vba
 
Dim MaChaine As String
    
MaChaine = " est l'apostrophe"
MaChaine = Application.WorksheetFunction.Substitute(MaChaine, "'", "''")

Comment lire une formule par formule ?
auteur : SilkyRoad
Une solution en utilisant les anciennes macros Excel 4.

Si par exemple la formule à tester est dans la cellule C3:
Utilisez le Menu Insertion/Nom/Définir.
Dans le champ "Noms dans le classeur", saisissez NomFormule.
Dans le champ "Fait référence à:", saisissez =LIRE.CELLULE(41;$C$3)
Cliquez sur le bouton Ajouter, puis sur le bouton OK pour valider.
Saisissez la formule =NomFormule dans une cellule de la feuille.



Comment Additionner des cellules en fonction de la couleur de fond, sans macro ?
auteur : SilkyRoad
Par exemple, pour additionner les cellules dont la couleur de fond et jaune, dans la plage A1:A10:
Sélectionnez la plage B1:B10
Menu Insertion / Nom / Définir
Dans le champ "Nom dans le classeur", indiquez le mot "Test"
Dans le champ "Fait référence à" saisissez la formule:
=LIRE.CELLULE(63;Feuil1!A1)
Cliquez sur le bouton Ajouter , puis sur le bouton OK pour valider.

Il s'agit d'une utilisation détournée des anciennes fonctions XL4.
63 Renvoie la couleur de fond de la cellule.

Sélectionnez la cellule B1.
Saisissez la formule
=Test
Validez la formule.

Utilisez les poignées de recopie pour étirer la formule vers le bas.
Ensuite dans la cellule C1, vous pouvez utiliser une formule qui compte le nombre de fois qu'apparaît un code couleur (6= couleur jaune)
Par exemple:
=NB.SI(B1:B10;6)

Le seul inconvénient, il faut revalider la formule =Test lorsque vous modifiez la couleur d'une cellule car le recalcul automatique et le lancement du recalcul (F9) ne fonctionneront pas.
Pour effectuer la mise à jour du résultat, le plus rapide consiste à ressaisir la formule =Test en B1, et utiliser les poignées de recopie vers le bas.



Comment extraire les mots d'une phrase ?
auteur : SilkyRoad
1. Trouver le Nième mot dans une phrase.

La phrase est par exemple saisie en A1. En B1, saisissez la position du mot que vous souhaitez extraire.
Placez ensuite cette formule en A2:

Formule

=SI(B1>NBCAR(A1)-NBCAR(SUBSTITUE(A1;" ";""));DROITE(A1;NBCAR(A1)-TROUVE("^^";
SUBSTITUE(A1;" ";"^^";NBCAR(A1)-NBCAR(SUBSTITUE(A1;" ";"")))));SI(B1=1;STXT(A1;1;TROUVE("^^";
SUBSTITUE(A1;" ";"^^";1))-1);STXT(A1;TROUVE("^^";SUBSTITUE(A1;" ";"^^";B1-1))+1;TROUVE("^^";
SUBSTITUE(A1;" ";"^^";B1))-TROUVE("^^";SUBSTITUE(A1;" ";"^^";B1-1))-1)))




2. Sur le même principe, pour extraire tous les mots d'une phrase écrite en A1, il serait donc possible de saisir en B1:

Formule

=SI(NBCAR($A$1)-NBCAR(SUBSTITUE($A$1;" ";"")) + 2<COLONNE();"";
SI(COLONNE()-1>NBCAR($A$1)-NBCAR(SUBSTITUE($A$1;" ";""));DROITE($A$1;NBCAR($A$1)-
TROUVE("^^";SUBSTITUE($A$1;" ";"^^";NBCAR($A$1)-NBCAR(SUBSTITUE($A$1;" ";"")))));
SI(COLONNE()-1=1;STXT($A$1;1;TROUVE("^^";SUBSTITUE($A$1;" ";"^^";1))-1);STXT($A$1;
TROUVE("^^";SUBSTITUE($A$1;" ";"^^";COLONNE()-2))+1;TROUVE("^^";SUBSTITUE($A$1;" ";"^^";
COLONNE()-1))-TROUVE("^^";SUBSTITUE($A$1;" ";"^^";COLONNE()-2))-1))))


Et de recopier la même formule sur les autres colonnes de droite, afin d'extraire chaque mot.



3. Une autre solution, sans formule, consiste à utiliser l'asssistant de conversion:

Sélectionnez la cellule A1 qui contient la phrase complète.
Menu Données
Convertir
Sélectionnez l'option "Largeur Fixe".
Cliquez sur le bouton "Suivant".
La fenêtre suivante permet de pré-visualiser le résultat.
Bouton "Suivant".
L'assistant de conversion propose d'autres options pour personnaliser le résultat (le format des données en colonne, la cellule de destination …etc…).
Cliquez sur le bouton "Terminer".



Comment créer rapidement une table de multiplication ?
auteur : SilkyRoad
La première ligne et la première colonne de la feuille contiennent les éléments de multiplication.
Saisissez la formule suivante en B2:
=$A2*B$1

Puis étirez la formule.





Comment utiliser les fonctions de l'utilitaire d'analyse dans Excel2007 ?
auteur : SilkyRoad
Les fonctions de l'utilitaire d'Analyse (ATP) sont désormais intégrées dans l'application Excel et sont contenues dans la bibliothèque de fonctions natives.
Il n'est donc plus nécessaire d'activer le complément comme cela était le cas pour les anciennes versions d'Excel.

Attention:
Ces fonctions ayant été déplacées vers la bibliothèque native, certains résultats peuvent être différents entre Excel 2007 et les versions antérieures.

L'aide Microsoft Décrit en détail ces modifications:
Description des modifications apportées à l'Utilitaire d'Analyse (ATP) pour Microsoft Office Excel 2007.

Il est aussi possible que les fonctions ATP créées dans un classeur Excel2007 renvoient une erreur #Nom! lorsque le fichier est ouvert avec une version antérieure du tableur.
De la même manière, un classeur créé dans Excel2003 (ou antérieur), puis ouvert dans Excel2007, peut renvoyer une erreur #Nom! lorsque les cellules contiennent des formules issues de l'utilitaire d'analyse. Pour ce dernier cas, il suffit de rééditer et revalider la formule pour corriger l'erreur: Sélectionnez la cellule/F2/F9.


Vous noterez que l'activation du complément "Analysis ToolPack " est toujours nécessaire si vous souhaitez utiliser l'utilitaire d'analyse de données scientifiques et financières. Le complément ajoute un bouton d'accès dans le groupe "Analyse" de l'onglet "Données".



Comment fonctionne la formule SIERREUR dans Excel 2007 ?
auteur : SilkyRoad
SIERREUR est une nouveauté Excel2007. Cette fonction permet de vérifier et de gérer les erreurs.
=SIERREUR(formule;valeur_si_erreur)

Elle renvoie la valeur que vous avez spécifié (valeur_si_erreur) si la formule provoque une erreur. Dans le cas contraire, elle renvoie le résultat de la formule.

Par exemple, pour masquer les messages erreurs lorsque la division A1/A2 renvoie une erreur:
Jusqu'à Excel2003, la fonction ESTERREUR devait être associée à une condition SI.
Une telle formule devait s'écrire:
=SI(ESTERREUR(A1/A2);"";A1/A2)

Dans Excel2007, vous pouvez utiliser:
=SIERREUR(A1/A2;"")



Comment utiliser la fonction SOMME.SI.ENS dans Excel 2007 ?
auteurs : Microsoft, SilkyRoad
Cette fonction conditionnelle permet d'additionner les valeurs d'une plage, répondant à plusieurs critères.

Syntaxe
SOMME.SI.ENS(sum_range; criteria_range1;criteria1 ; criteria_range2;criteria2...)

Sum_range
représente la plage de cellules à additionner. Les cellules vides et textuelles sont ignorées. L'ordre des arguments est différent entre SOMME.SI.ENS et SOMME.SI. L'argument sum_range est le premier argument dans SOMME.SI.ENS, mais le troisième dans SOMME.SI.

Criteria_range1, criteria_range2,...
représentent 1 à 127 plages de cellules dans lesquelles les critères associés doivent être évalués.

Criteria1, criteria2,...
représentent 1 à 127 critères, sous forme de nombre, d'expression, de référence de cellule ou de texte qui déterminent les cellules à additionner. Ces arguments peuvent, par exemple, être exprimés sous l'une des formes suivantes: 32, "32", ">32", "pommes", B4 ou ">="&B4.

Remarques:
Chaque cellule d'un argument somme_plage est additionnée seulement si tous les critères correspondants spécifiés sont vrais pour la cellule.
Les cellules de l'argument somme_plage qui contiennent VRAI prennent la valeur 1 ; les cellules de l'argument somme_plage qui contiennent FAUX prennent la valeur 0 (zéro).
Contrairement aux arguments de critère et de plage de la fonction SOMME.SI, dans SOMME.SI.ENS, chaque plage_critères doit avoir la même taille et la même forme que plage_somme.
Vous pouvez utiliser les caractères génériques [le point d'interrogation (?) et l'astérisque (*)] dans l'argument critère. Le point d'interrogation correspond à un caractère quelconque et l'astérisque à une séquence de caractères. Si vous recherchez un point d'interrogation ou un astérisque, tapez un tilde (~) devant ce caractère.


Par exemple, additionner les valeurs de la plage D3:D36 si:
* La date en A3:A36 est inférieure ou égale à aujourd'hui et supérieure à aujourdhui() - 6 jours.
* Les données de la plage C3:C36 commencent par la chaîne de caractères "CB".

En utilisant la fonction SOMME.SI.ENS:
Formule

=SOMME.SI.ENS(D3:D36;A3:A36;"<="&AUJOURDHUI();A3:A36;">"&AUJOURDHUI()-6;C3:C36;"=CB*")


Nota:
La fonction équivalente en utilisant la fonction SOMMEPROD, pour être compatible avec toutes les versions d'Excel:
Formule

=SOMMEPROD((A3:A36>AUJOURDHUI()-6)*(A3:A36<=AUJOURDHUI())*(GAUCHE(C3:C36;2)="CB")*(D3:D36))




precedent    sommaire    suivant   

Consultez les autres F.A.Q's


Les sources présentés sur cette pages sont libre de droits, et vous pouvez les utiliser à votre convenance. Par contre cette page de présentation de ces sources constitue une oeuvre intellectuelle protégée par les droits d'auteurs. Copyright ©2008  Developpez LLC. Tout droits réservés Developpez LLC. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents et images sans l'autorisation expresse de Developpez LLC. Sinon vous encourez selon la loi jusqu'à 3 ans de prison et jusqu'à 300 000 E de dommages et intérets. Cette page est déposée à la SACD.

Vos questions techniques : forum d'entraide Accueil - Publiez vos articles, tutoriels, cours et rejoignez-nous dans l'équipe de rédaction du club d'entraide des développeurs francophones. Nous contacter - Copyright 2000..2005 www.developpez.com