Formules et références

Les formules sont au cœur de l'utilisation des tableurs comme Microsoft Excel et LibreOffice Calc. Elles permettent de réaliser des calculs, de manipuler des données et d'automatiser des tâches. Une compréhension claire de la manière d'écrire des formules et de l'utilisation des références de cellules est essentielle pour exploiter pleinement les capacités de ces outils.

Écrire des formules#

Pour écrire une formule dans Excel ou Calc, commencez par saisir le signe égal =. Après ce signe, vous pouvez entrer des opérations mathématiques, utiliser des valeurs de cellules, des fonctions intégrées, et plus encore.

=3 + 2

Résultat : 5

Insérer une image montrant une cellule avec une formule simple.

Dans Excel comme dans Calc, les formules peuvent référencer des cellules pour utiliser leurs valeurs dans les calculs :

=A1 + A2

Si A1 contient 3 et A2 contient 2, le résultat sera 5.

Insérer une image illustrant une formule faisant référence à des cellules.

Les opérations mathématiques de base#

Les tableurs comme Microsoft Excel et LibreOffice Calc permettent d'effectuer des opérations mathématiques de base directement dans les cellules en utilisant des formules. Les quatre opérations mathématiques fondamentales que vous pouvez réaliser sont l'addition, la soustraction, la multiplication et la division.

Addition#

Pour additionner des valeurs, utilisez le signe plus +.

=3 + 2

Résultat : 5

Insérer une image montrant une cellule avec une formule d'addition.

Soustraction#

La soustraction utilise le signe moins -.

=5 - 2

Résultat : 3

Insérer une image montrant une cellule avec une formule de soustraction.

Multiplication#

La multiplication est réalisée avec l'astérisque *.

=3 * 2

Résultat : 6

Insérer une image montrant une cellule avec une formule de multiplication.

Division#

La division s'effectue avec la barre oblique /.

=6 / 2

Résultat : 3

Insérer une image montrant une cellule avec une formule de division.

Utilisation des parenthèses#

Les parenthèses () sont utilisées pour contrôler l'ordre des opérations dans une formule. Les opérations entre parenthèses sont exécutées en premier. En l’absence de parenthèses, Microsoft Excel et LibreOffice Calc suivent les règles de priorité des opérations en mathématique. Cela est particulièrement utile lorsque vous avez des formules complexes où l'ordre des opérations est important pour obtenir le résultat correct.

=(2 + 3) * 4

Ici, 2 + 3 est calculé en premier, donnant 5, et le résultat est ensuite multiplié par 4, donnant un résultat final de 20.

Insérer une image montrant une cellule avec une formule utilisant des parenthèses.

Sans parenthèses, la multiplication serait effectuée avant l'addition (en suivant les règles de priorité des opérations), ce qui donnerait un résultat différent :

=2 + 3 * 4

Résultat : 14

Ici, 3 * 4 est calculé en premier pour donner 12, et ensuite 2 est ajouté, ce qui n'est pas le même résultat que lorsque les parenthèses sont utilisées pour modifier l'ordre des opérations.

Insérer une image montrant une cellule avec une formule sans parenthèses pour illustrer la différence.

L'utilisation correcte des parenthèses est essentielle pour garantir que vos formules produisent les résultats attendus.

Références de cellules#

Il existe trois types principaux de références de cellules dans les formules : relatives, absolues et mixtes.

Références relatives#

Par défaut, les références dans les formules sont relatives. Cela signifie que lorsqu'une formule est copiée d'une cellule à une autre, ses références sont ajustées relativement à la position de la nouvelle cellule.

=A1 + 1

Si vous copiez cette formule de la cellule B1 vers B2, la formule copiée deviendra =A2 + 1.

Insérer une image montrant l'effet de la copie d'une formule avec une référence relative.

Références absolues#

Pour garder une référence constante à une cellule spécifique, utilisez des références absolues en ajoutant un signe dollar $ devant la lettre de la colonne et/ou le numéro de la ligne.

=$A$1 + 1

Peu importe où vous copiez cette formule, elle fera toujours référence à la cellule A1.

Insérer une image montrant l'effet de la copie d'une formule avec une référence absolue.

Références mixtes#

Les références mixtes combinent les références absolues et relatives. Vous pouvez verrouiller soit la colonne, soit la ligne.

=$A1 + 1  // La colonne A est absolue, la ligne est relative

Si vous copiez cette formule vers le bas, la référence à la colonne A restera, mais la ligne s'ajustera relativement.

Insérer une image montrant l'effet de la copie d'une formule avec une référence mixte.

Raccourci clavier pour changer le type de référence#

Pour alterner rapidement entre les références relatives, absolues et mixtes dans Microsoft Excel et LibreOffice Calc, vous pouvez utiliser un raccourci clavier pratique. Lorsque vous êtes en train d'éditer une formule et que le curseur est sur une référence de cellule, appuyez sur F4 pour faire défiler les différents types de références.

• La première pression sur F4 transforme une référence relative en absolue (A1 devient $A$1).
• La deuxième pression rend la ligne relative et la colonne absolue ($A$1 devient A$1).
• La troisième pression rend la colonne relative et la ligne absolue (A$1 devient $A1).
• La quatrième pression revient à une référence relative ($A1 redevient A1).

Plages de données#

Dans Microsoft Excel et LibreOffice Calc, une plage de données se réfère à une sélection de deux ou plusieurs cellules sur une feuille de calcul. Ces plages sont essentielles pour effectuer des opérations sur de multiples valeurs simultanément et pour utiliser des fonctions qui traitent des séries de données.

Référencement d'une plage de données#

Pour référencer une plage de données dans une formule, vous indiquez la cellule de départ et la cellule de fin de la plage, séparées par un deux-points :. Par exemple, A1:A4 représente une plage verticale qui inclut les cellules A1, A2, A3, et A4.

=SOMME(A1:A4)

Cette formule additionne toutes les valeurs des cellules de A1 à A4.

Insérer une image montrant une cellule avec une formule utilisant une plage de données.

Références absolues et relatives#

Comme pour les cellules individuelles, les plages de données peuvent être référencées en utilisant des références relatives, absolues ou mixtes, ce qui est particulièrement utile lors de la copie de formules sur de multiples cellules.

=SOMME($A$1:A4)

Cette formule, même lorsqu'elle est copiée ailleurs sur la feuille de calcul, continuera à référencer une plage démarrant en A1 , mais dont la fin changera de A4 vers d’autres valeurs, étendant progressivement la plage.

Insérer une image montrant l'effet de la copie d'une formule avec une plage de données à référence absolue et relative.

Références nommées#

Les références nommées, également connues sous le nom de noms définis ou de noms de cellules, sont une fonctionnalité puissante dans Microsoft Excel et LibreOffice Calc qui permet d'attribuer des noms significatifs à des cellules, des plages de cellules, des formules ou des constantes. Ces noms peuvent ensuite être utilisés dans les formules, rendant les feuilles de calcul plus lisibles et plus faciles à gérer.

Création de références nommées#

Pour créer une référence nommée, vous devez sélectionner la cellule ou la plage de cellules à nommer, puis attribuer un nom via la boîte de dialogue "Définir un nom" ou "Gestionnaire de noms" dans Excel, ou "Gestionnaire de noms" dans Calc.

Insérer une image montrant la boîte de dialogue pour créer une référence nommée dans Excel et Calc.

Options de création de plages nommées#

Voici les options principales lors de la création d'une plage nommée :

• Nom : Vous devez donner un nom unique à la plage que vous souhaitez définir. Ce nom est utilisé dans les formules et ne doit pas contenir d'espaces ni commencer par un chiffre.

• Portée : Vous pouvez définir la portée d'un nom à l'ensemble du classeur ou à une feuille de calcul spécifique. Un nom avec une portée de classeur peut être utilisé dans toutes les feuilles de calcul du classeur, tandis qu'un nom avec une portée de feuille est uniquement accessible dans la feuille spécifiée.

• Réfère à : C'est ici que vous indiquez la plage de cellules que le nom représentera. Vous pouvez entrer la référence manuellement ou sélectionner la plage directement dans la feuille de calcul.

• Commentaire : Microsoft Excel vous permet d'ajouter un commentaire pour décrire le nom ou la plage nommée, ce qui peut être utile pour fournir un contexte supplémentaire aux autres utilisateurs de la feuille de calcul.

• Appliquer et Fermer : Une fois que vous avez entré toutes les informations nécessaires, vous pouvez utiliser ces options pour sauvegarder le nouveau nom défini. "Appliquer" enregistre le nom sans fermer la boîte de dialogue, tandis que "Fermer" enregistre et ferme la boîte de dialogue.

Insérer une image montrant la boîte de dialogue de création d'une plage nommée avec les différentes options.

Utilisation des références nommées dans les formules#

Une fois qu'une référence nommée est créée, vous pouvez l'utiliser dans une formule tout comme vous le feriez avec une référence de cellule classique. Cela rend les formules beaucoup plus intuitives. Par exemple, au lieu d'utiliser =SOMME(B2:B10), vous pouvez nommer la plage B2:B10 comme VentesTrimestrielles et écrire la formule =SOMME(VentesTrimestrielles).

=SOMME(VentesTrimestrielles)

Insérer une image montrant une cellule avec une formule utilisant une référence nommée.

Avantages des références nommées#

L'utilisation de références nommées présente plusieurs avantages :

• Clarté : Les formules sont plus faciles à comprendre quand elles utilisent des noms descriptifs plutôt que des références de cellules cryptiques.
• Maintenance : Si vous devez modifier la plage de données référencée, vous pouvez simplement mettre à jour la définition du nom sans avoir à modifier toutes les formules qui l'utilisent.
• Réutilisation : Les noms définis peuvent être utilisés dans toute la feuille de calcul, et même dans d'autres feuilles de calcul du même classeur.

Bonnes pratiques pour nommer les références#

Lorsque vous nommez des références, suivez ces bonnes pratiques pour éviter la confusion :

• Utilisez des noms descriptifs et significatifs.
• Évitez les espaces et les caractères spéciaux. Utilisez des caractères de soulignement _ pour séparer les mots si nécessaire.
• Ne commencez pas un nom par un chiffre ou un caractère qui pourrait être confondu avec une référence de cellule (par exemple, "C2" pourrait être interprété comme une référence à la cellule C2).

En maîtrisant l'utilisation des références nommées, vous pouvez grandement améliorer l'efficacité et la lisibilité de vos feuilles de calcul dans Excel et Calc.

Références vers une autre feuille de calcul et vers un autre classeur#

Dans Microsoft Excel et LibreOffice Calc, il est possible de créer des formules qui font référence à des cellules situées dans une autre feuille de calcul ou même dans un autre classeur. Cette fonctionnalité est très utile pour organiser des données complexes et pour consolider des informations provenant de multiples sources.

Références à une autre feuille de calcul#

Pour faire référence à une cellule ou une plage de cellules dans une autre feuille du même classeur, vous devez précéder la référence de la cellule par le nom de la feuille suivi d'un point d'exclamation !.

=Feuille2!A1

Cette formule fait référence à la cellule A1 de la feuille nommée Feuille2. Si le nom de la feuille contient des espaces, vous devez l'entourer de simples guillemets '.

='Feuille de calcul 2'!A1

Insérer une image montrant une formule qui fait référence à une cellule dans une autre feuille.

Références à un autre classeur#

Pour faire référence à des cellules dans un autre classeur, la syntaxe est un peu plus complexe. Vous devez inclure le chemin du fichier, le nom du classeur entre crochets [], le nom de la feuille et la référence de la cellule, le tout séparé par des points d'exclamation !.

=[Budget.xlsx]Janvier!C10

Cette formule fait référence à la cellule C10 de la feuille Janvier dans le classeur Budget.xlsx. Si le classeur est ouvert, vous n'avez pas besoin d'inclure le chemin du fichier.

Insérer une image montrant une formule qui fait référence à une cellule dans un autre classeur.

Points importants à considérer#

• Mise à jour des références : Lorsque vous faites référence à des cellules dans d'autres classeurs, si le fichier source est déplacé ou renommé, les références doivent être mises à jour manuellement pour refléter le nouveau chemin ou nom de fichier.

• Ouverture des classeurs référencés : Pour que les formules qui font référence à d'autres classeurs fonctionnent correctement, les classeurs référencés doivent être ouverts. Sinon, les formules ne pourront pas afficher les valeurs mises à jour.

• Liens entre les classeurs : L'utilisation de références à d'autres classeurs crée des liens entre les fichiers, ce qui peut rendre la gestion des données plus complexe, notamment en termes de partage et de sécurité des données.

Utilisation des caractères ' et & dans une formule#

Les caractères spéciaux tels que l'apostrophe (') et le symbole et commercial (&) jouent des rôles uniques dans les formules de Microsoft Excel et LibreOffice Calc. Leur compréhension est essentielle pour formuler correctement les expressions et éviter les erreurs.

Caractère Apostrophe (')#

L'apostrophe est utilisée dans les feuilles de calcul pour plusieurs raisons, notamment :

1. Pour indiquer que ce qui suit doit être traité comme du texte, même s'il ressemble à un nombre ou à une date.
2. Pour ignorer un format de cellule lors de la saisie de données.

Dans les formules#

Lorsqu'on utilise des formules, l'apostrophe peut être nécessaire si vous faites référence à une feuille de calcul dont le nom contient des espaces ou des caractères spéciaux.

Microsoft Excel#

='Feuille de données'!A1

Cette formule fait référence à la cellule A1 dans une feuille nommée "Feuille de données".

LibreOffice Calc#

='Feuille de données'.A1

La syntaxe de Calc est légèrement différente, utilisant un point au lieu d'un point d'exclamation.

Symbole Et Commercial (&)#

Le symbole & est utilisé pour concaténer, ou combiner, deux chaînes de texte ou plus dans une cellule.

Concaténation de texte#

Le symbole & est très utile pour assembler du texte provenant de différentes cellules ou pour ajouter du texte autour des valeurs de cellules.

Microsoft Excel et LibreOffice Calc#

=A1 & " et " & B1

Si A1 contient "Pomme" et B1 contient "Banane", le résultat sera "Pomme et Banane".

Inclure des apostrophes dans le texte#

Pour inclure un apostrophe dans le texte concaténé, vous devez utiliser une paire d'apostrophes pour représenter un seul apostrophe dans le résultat final.

Microsoft Excel et LibreOffice Calc#

="L'ordinateur" & A1 & " est allumé."

Si A1 est vide, le résultat sera "L'ordinateur est allumé." Notez que l’apostrophe est dans une chaîne de caractère entre guillemets.

Insérer une image illustrant l'utilisation des caractères ' et & dans une formule pour Excel et une autre pour Calc.

La maîtrise de ces caractères spéciaux est cruciale pour la création de formules précises et pour la manipulation de textes dans les feuilles de calcul. Que ce soit pour formater des références de cellule ou pour concaténer des chaînes de texte, l'utilisation appropriée de l'apostrophe et du symbole et commercial rend les formules à la fois fonctionnelles et lisibles.

Différences entre Excel et Calc#

Bien que la syntaxe de base pour écrire des formules et des références de cellules soit très similaire entre Excel et Calc, il peut y avoir des différences dans certaines fonctions et caractéristiques avancées. Par exemple, les noms de certaines fonctions peuvent varier légèrement. Il est donc important de consulter la documentation spécifique du programme que vous utilisez pour des détails précis sur les fonctions avancées.