■ Comment puis-je créer une référence à une plage de cellules correspondant à un nombre spécifié de lignes et de colonnes d’une cellule ou d’une autre plage de cellules?

■ Comment puis-je effectuer une opération de recherche basée sur la colonne la plus à droite dans une plage de tableaux au lieu de la colonne la plus à gauche?

■ Je télécharge souvent les informations de vente de produits logiciels répertoriées par pays. J’ai besoin de suivre les revenus de l’Iran ainsi que les coûts et les unités vendues, mais les données sur l’Iran ne sont pas toujours au même endroit dans la feuille de calcul. Puis-je créer une formule qui retiendra toujours les revenus, les coûts et les unités vendues de l’Iran?

■ Chaque médicament développé par mon entreprise passe par trois étapes de développement. J’ai une liste des coûts par mois pour chaque médicament et je connais la durée en mois de chaque stade de développement. Puis-je créer des formules qui calculent le coût total encouru pour chaque médicament à chaque stade de développement?

■ Je gère un petit magasin de vidéos. Dans une feuille de calcul, mon comptable a répertorié le nom de chaque film et le nombre de copies en stock. Malheureusement, il a combiné ces informations dans une cellule pour chaque film. Comment puis-je extraire le nombre de copies de chaque film en stock dans une cellule distincte?

■ Comment fonctionne la fonction Excel Évaluer la formule?

■ Comment puis-je écrire une formule qui renvoie toujours le dernier nombre d’une colonne?

■ Comment puis-je configurer un nom de plage qui inclut automatiquement de nouvelles données?

■ Je représente les ventes mensuelles unitaires de mon entreprise. Chaque mois, je télécharge les ventes unitaires du mois le plus récent. Je souhaite que mon graphique soit mis à jour automatiquement. Y a-t-il un moyen facile d’accomplir cela?

Utilisez la fonction DECALER pour créer une référence à une plage qui est un nombre spécifié de lignes et de colonnes loin d’une cellule ou d’une plage de cellules. Fondamentalement, pour créer une référence à une plage de cellules, vous devez d’abord spécifier une cellule de référence. Vous indiquez ensuite le nombre de lignes et de colonnes loin de la cellule de référence que vous souhaitez créer votre plage. Par exemple, en utilisant la fonction DECALER, je peux créer une référence à une plage de cellules qui contient deux lignes et trois colonnes et commence deux colonnes à droite et une ligne au-dessus de la cellule actuelle. Vous pouvez calculer le nombre spécifié de lignes et de colonnes à déplacer à partir d’une cellule de référence à l’aide d’autres fonctions Microsoft Excel.

La syntaxe de la fonction DECALER est  DECALER (référence, rangées_moved, colonnes_moved, hauteur, largeur).

■ La référence est une cellule ou une plage de cellules à partir de laquelle le décalage commence. Si vous spécifiez une plage de cellules, les cellules doivent être adjacentes les unes aux autres.

■ Lignes déplacées est le nombre de lignes éloignées de la cellule ou de la plage de référence que vous souhaitez que la référence de plage démarre (la cellule supérieure gauche de la plage de décalage). Un nombre négatif de lignes vous éloigne de la référence; un nombre positif de lignes vous fait descendre. Par exemple, si la référence est égale à C5 et que les lignes déplacées sont égales à -1, vous passez à la ligne 4. Si les lignes déplacées sont égales à +1, vous passez à la ligne 6. Si les lignes déplacées sont égales à 0, vous restez à la ligne 5.

■ Colonnes déplacées correspond au nombre de colonnes éloignées de la cellule ou de la plage de référence que vous souhaitez que la référence de plage démarre. Un nombre négatif de colonnes vous déplace à gauche de la référence; un nombre positif de colonnes vous déplace vers la droite. Par exemple, si la référence est égale à C5 et les colonnes déplacées égales à -1, vous passez à la colonne B. Si les colonnes déplacées sont égales à +1, vous passez à la colonne D. Si les colonnes déplacées sont égales à 0, vous restez à la colonne C.

■ La hauteur et la largeur sont des arguments facultatifs qui donnent le nombre de lignes et de colonnes dans la plage de décalage. Si la hauteur ou la largeur est omise, la fonction DECALER crée une plage pour laquelle la valeur de la hauteur ou de la largeur est égale à la hauteur ou la largeur de la cellule ou de la plage de référence.

Réponses aux questions de cet article

Cette section fournit les réponses aux questions répertoriées au début de  cet article.

Comment créer une référence à une plage de cellules correspondant à un nombre spécifié de lignes et de colonnes d’une cellule ou d’une autre plage de cellules?

Le fichier , illustré à la figure 1, fournit quelques exemples de la fonction DECALER en action.

FIGURE 1 : Cette figure montre l’utilisation de la fonction DECALER en combinaison avec la fonction SOMME.

Par exemple, dans la cellule B10, entrez la formule indiquée dans la cellule A10: SOMME (DECALER (B7, -1,1,2,1)). Cette formule commence dans la cellule B7. Il déplace une ligne vers le haut et une colonne vers la droite, ce qui vous amène à la cellule C6. La formule sélectionne désormais une plage composée de deux lignes et d’une colonne, ce qui donne la plage C6: C7.

La fonction SOMME ajoute les nombres dans cette plage, ce qui donne 2 + 6 = 8. Les deux autres exemples illustrés dans la figure  ci-dessus fonctionnent de la même manière. Les sections suivantes vous montrent comment appliquer la fonction DECALER pour résoudre certains problèmes des principales sociétés américaines.

Comment puis-je effectuer une opération de recherche basée sur la colonne la plus à droite dans une plage de table au lieu de la colonne la plus à gauche?

Dans la figure 2 (voir le classeur ) sont listés les membres de l’équipe de basket-ball Dallas Mavericks NBA 2002-2003 et leurs pourcentages de buts sur le terrain. Si vous êtes invité à trouver le joueur avec un pourcentage d’objectif de terrain spécifique, vous pouvez facilement résoudre ce problème en utilisant une fonction RECHERCHEV. Mais ce que vous voulez vraiment faire, c’est une recherche sur le côté gauche, ce qui implique de trouver le pourcentage d’objectif de terrain pour un joueur en utilisant son nom. Une fonction RECHERCHEV ne peut pas effectuer une recherche du côté gauche, mais une recherche du côté gauche est simple si vous combinez les fonctions EQUIV et DECALER.

FIGURE 2 Vous pouvez effectuer une recherche sur le côté gauche en utilisant les fonctions EQUIV et DECALER.

Tout d’abord, entrez le nom du joueur dans la cellule D7, puis utilisez une cellule de référence de B7 (l’en-tête de colonne de pourcentage de l’objectif de champ) avec la fonction DECALER. Pour trouver le pourcentage de buts sur le terrain du joueur, descendez jusqu’à la ligne sous la ligne 7, où le nom du joueur apparaît. Il s’agit d’un travail pour la fonction EQUIV. La partie fonction EQUIV de la formule DECALER (B7, EQUIV (D7, $ C $ 8: $ C $ 22,0), 0) descend jusqu’à la ligne contenant le nom du joueur spécifié, puis se déplace sur 0 colonnes. Étant donné que la référence se compose d’une cellule, l’omission des arguments de hauteur et de largeur de la fonction DECALER garantit que la plage renvoyée par cette formule est également une cellule. Ainsi, vous récupérez le pourcentage d’objectif de terrain du joueur.

Je télécharge souvent des informations de vente de produits logiciels répertoriées par pays. J’ai besoin de suivre les revenus de l’Iran ainsi que les coûts et les unités vendues, mais les données sur l’Iran ne sont pas toujours au même endroit dans la feuille de calcul. Puis-je créer une formule qui retiendra toujours les revenus, les coûts et les unités vendues de l’Iran?

Le fichier (voir figure ci-dessous) contient des données sur les unités vendues, le chiffre d’affaires et le coût variable des logiciels vendus dans plusieurs pays d’Asie et du Moyen-Orient. Chaque mois, lorsque vous téléchargez les rapports financiers mensuels, l’emplacement de chaque pays dans la feuille de calcul change, vous avez donc besoin de formules qui renvoient toujours (pour un pays donné) les bonnes unités vendues, les revenus et le coût variable.

FIGURE 3: Vous pouvez utiliser la fonction DECALER dans les calculs lorsque vous travaillez avec des données qui ne sont pas toujours au même emplacement dans une feuille de calcul.

En copiant la formule DECALER ($ C $ 6, EQUIV ($ C21, $ C $ 7: $ C $ 17,0), D20) de D21 à E21: F21, vous pouvez calculer le résultat souhaité. Cette formule définit une référence égale à la cellule C6 (qui contient les mots Pays / Région), puis se déplace sur une colonne (vers la cellule D20) pour rechercher les unités vendues et jusqu’à la ligne contenant le pays répertorié dans C21. Dans la cellule E21, la référence à D20 fait désormais référence à E20 et devient un 2, vous passez donc sur deux colonnes à droite de la colonne C pour trouver des revenus. Dans la cellule E21, la référence à D20 fait désormais référence à F20 et devient un 3, vous déplacez donc trois colonnes à droite de la colonne C pour trouver le coût variable.

Chaque médicament développé par ma société passe par trois étapes de développement. J’ai une liste des coûts par mois pour chaque médicament et je sais combien de mois chaque étape de développement a pris pour chaque médicament.

Puis-je créer des formules qui calculent le coût total encouru pour chaque médicament pendant chaque stade de développement?

Le fichier  contient les coûts mensuels engagés pour développer cinq médicaments et, pour chaque médicament, le nombre de mois requis pour terminer chaque phase. Un sous-ensemble des données est illustré à la figure ci-dessous.

L’objectif est de déterminer pour chaque médicament le coût total encouru lors de chaque phase de développement. Dans les cellules D4: D6, vous calculez les coûts de développement totaux pour les phases 1 à 3 pour le médicament 1. Calculez les coûts de la phase 1 pour le médicament 1 en utilisant une référence de cellule D10, avec des lignes déplacées et des colonnes déplacées égales à 0. Définition d’une hauteur égale à 0 le nombre de mois dans la phase 1 et une largeur égale à 1 capture tous les coûts de la phase 1. Calculez les coûts de la phase 1 pour le médicament 1 dans la cellule D4 avec la formule SOMME(DECALER (D10,0,0, D1,1)). Ensuite, dans la cellule D5, calculez les coûts totaux de la phase 2 pour le médicament 1 en utilisant la formule SOMME (DECALER (D10, D1,0, D2,1)). Commencez avec une référence de cellule de D10 (le premier mois de coûts) et descendez le nombre de lignes equal à la longueur de la phase 1. Cela vous amène au début de la phase 2. Réglage de la hauteur égale à la valeur dans la cellule D2 garantit que vous incluez tous les coûts de la phase 2. Enfin, dans la cellule D6, trouvez les coûts de développement de la phase 3 pour le médicament 1 en utilisant la formule SOMME (DECALER (D10, D1 + D2,0, D3,1)). Dans cette formule, vous commencez à partir du premier mois de vente et descendez le nombre de lignes égal au temps total nécessaire pour les phases 1 et 2. Cela vous amène au début de la phase 3 où, dans la cellule D3, vous totalisez le nombre de lignes pour capturer les coûts de la phase 3. Ensuite, en copiant les formules de D4: D6 à E4: H6, vous pouvez calculer les coûts totaux pour la phase 1 à la phase 3 pour les médicaments 2 à 5. Par exemple, pour le médicament 2, les coûts totaux de la phase 1 sont égaux à 313 $, la phase totale 2 les coûts sont égaux à 789 $ et les coûts totaux de la phase 3 à 876 $.

FIGURE 4 :Utilisez la fonction DECALER pour calculer les coûts de développement des phases 1 à 3.

Je gère un petit magasin de vidéos. Dans une feuille de calcul, mon comptable a répertorié le nom de chaque film et le nombre de copies en stock. Malheureusement, il a combiné ces informations dans une cellule pour chaque film. Comment puis-je extraire le nombre de copies de chaque film en stock dans une cellule distincte?

Le fichier contient le nom de chaque film et le nombre de copies en stock dans la gamme C2: C12.

FIGURE 5: Utilisez la fonction DECALER pour afficher cet exemple de film.

Comme indiqué dans la colonne B de la figure  ci-dessus (voir la feuille de calcul Films du classeur ), vous souhaitez extraire le nombre de copies appartenant à chaque film dans une cellule distincte. Si le nombre de copies était répertorié à gauche du titre d’un film, ce problème serait facile; vous pouvez utiliser la fonction FIND pour localiser le premier espace, puis utiliser la fonction GAUCHE pour renvoyer toutes les données à gauche du premier espace. (Voir l’ article  5, “Fonctions texte”, pour une discussion sur l’utilisation des fonctions GAUCHE et TROUVE ainsi que d’autres fonctions que vous pouvez utiliser pour travailler avec du texte.) Malheureusement, cette technique ne fonctionne pas lorsque le nombre de copies est répertorié à droite du titre du film. Par exemple, pour un titre de film d’un mot, le nombre de copies se trouve à droite du premier espace, mais pour un titre de film de quatre mots, le nombre de copies se trouve à droite du quatrième espace.

Une façon de résoudre ce problème consiste à cliquer sur l’onglet Données sur le ruban et, dans le groupe Outils de données, cliquez sur Texte vers colonnes pour placer chaque mot dans un titre et le nombre de copies dans des colonnes distinctes. Vous pouvez utiliser la fonction NBVAL pour compter le nombre total de mots dans un titre, y compris le nombre d’éléments en tant que mot, pour chaque film. Vous pouvez ensuite utiliser la fonction DECALER pour localiser le nombre d’éléments.

Pour commencer, insérez suffisamment de colonnes à droite des données pour permettre à chaque mot dans les titres des films et le nombre d’éléments à extraire dans une colonne distincte. Vous pouvez utiliser six colonnes (Raiders of the Lost Ark nécessite six colonnes), comme vous pouvez le voir sur la figure ci-dessus. Vous pouvez ensuite sélectionner la plage de cellules C2: C12 et choisir Text To Columns dans l’onglet Data. Sélectionnez Délimité dans l’Assistant Conversion de texte en colonnes et utilisez le caractère espace comme caractère de délimitation. Après avoir sélectionné la cellule D2 comme cellule de destination, les résultats s’affichent dans les colonnes D à I de la figure ci-dessus.

Maintenant, comptez le nombre de mots dans la cellule de chaque film (en comptant le nombre d’éléments comme un mot) en copiant la formule NBVAL(D2: I2) de A2 à A3: A12. Les résultats sont présentés dans la figure ci-dessus.

Enfin, en copiant la formule DECALER (C2,0, A2) de B2 à B3: B12, vous pouvez localiser le nombre de copies de chaque film en stock. Cette formule commence à la cellule de référence contenant le titre du film et se déplace sur le nombre de colonnes égal au nombre de mots dans la cellule de titre. Parce que la cellule de référence ne contient qu’une seule cellule, vous pouvez omettre l’heure huit et arguments de largeur de la fonction DECALER de sorte que la fonction utilise uniquement la cellule contenant le dernier mot (le nombre de copies) de la cellule de titre.

Bien sûr, comme le montre la figure ci-dessous et la feuille de calcul, vous pouvez utiliser Flash Fill pour extraire le nombre de copies de chaque film. Entrez 40 dans la cellule D2 et 12 dans la cellule D3; appuyez sur Entrée, puis appuyez sur Ctrl + E.

FIGURE 6 Utilisez Flash Fill pour extraire le nombre d’articles en stock.

Comment fonctionne la fonction Excel Évaluer la formule?

Si vous sélectionnez une partie d’une formule de cellule, puis appuyez sur F9, Excel affiche la valeur créée par cette partie de la formule. Lorsque vous avez terminé de revoir la valeur évaluée, vous devez appuyer sur Échap, sinon la valeur remplacera la formule. Cette astuce facilite le débogage et la compréhension des formules complexes. Ainsi, il pourrait être plus facile de comprendre ce que fait la partie DECALER de la formule si vous appliquez cette astuce à l’une des formules de ce chapitre. Par exemple, dans le fichier Offsetcost.xlsx, la cellule E4 génère le coût total de la phase 1 avec la formule = SOMME (DECALER (E10,0,0, E1,1)). Si vous déplacez le curseur sur DECALER (E10,0,0, E1,1), mettez en surbrillance cette partie de la formule, puis sélectionnez F9, vous voyez = SOMME ({135,120,58}), ce qui indique que la partie DECALER de la formule dans la cellule D4 utilise les cellules correctes (D10: D12). Assurez-vous d’appuyer sur Echap pour quitter cette procédure, sinon vous modifierez la formule!

Une autre façon de voir comment fonctionne une formule complexe consiste à utiliser la commande Évaluer la formule. Déplacez le curseur sur E4 et cliquez sur l’onglet Formules du ruban. Dans le groupe Audit de formule, choisissez Évaluer la formule. (Voir figure ci-dessous.) Cliquez sur le bouton Évaluer (il ressemble à une loupe) et Excel simplifie la formule étape par étape jusqu’à ce que vous voyiez le résultat de la formule. Après avoir cliqué sur Évaluer deux fois, la formule apparaît sous la forme = Somme ($ E $ 10: E $ 12), vous savez donc que dans la cellule E4, vous avez sélectionné les cellules de phase 1 pour le médicament 2, ce que vous vouliez.

FIGURE 7:Il s’agit de la boîte de dialogue Évaluer la formule.

Comment puis-je écrire une formule qui renvoie toujours le dernier nombre d’une colonne?

Vous téléchargez souvent de nouvelles données dans une feuille de calcul. Pouvez-vous écrire une formule qui renvoie toujours des ventes au cours du mois le plus récent? (Voir le fichier et la figureci-dessous)

FIGURE 8 : Trouvez le dernier chiffre d’une colonne.

Entrez la formule DECALER (B6, NB(B: B), 0,1,1) dans la cellule D4.

Cette formule commence dans la cellule B6 et descend le nombre de lignes égal au nombre d’entrées numériques dans la colonne B. Cela vous amène au mois de ventes le plus récent, qui est sélectionné car 1,1 ne renvoie qu’une seule cellule.

Comment puis-je configurer un nom de plage qui inclut automatiquement de nouvelles données?

Les personnes qui utilisent Excel ajoutent souvent des lignes ou des colonnes de données à une plage de données qu’elles utilisent pour créer un tableau croisé dynamique ou pour effectuer un autre type d’analyse. Habituellement, ils mettent simplement à jour la plage de cellules mentionnée dans leur formule, puis réexécutent l’analyse. Cependant, vous pouvez utiliser des noms de plage dynamique et ne jamais avoir à mettre à jour la plage de données référencée dans une formule ou un tableau croisé dynamique. La gamme se met à jour automatiquement. Voici un exemple.

Le fichier affiche les entrées d’une base de données des ressources humaines (HR). (Voir figure ci-dessous.)

FIGURE 9 : Cette figure montre un exemple de plage dynamique.

Actuellement, neuf lignes et quatre colonnes contiennent des données. Ne serait-il pas agréable de créer un nom de plage qui inclurait automatiquement plus de lignes et de colonnes lorsque vous ajoutez des personnes ou des champs d’informations à la base de données?

Pour créer une plage dynamique, cliquez sur l’onglet Formules et, dans le groupe Noms définis, choisissez Gestionnaire de noms. Cliquez sur Nouveau et définissez une plage, comme le montre la figure .

FIGURE 10: Créez une plage dynamique.

La plage commence dans le coin supérieur gauche: cellule A1. Parce que vous voulez que la cellule A1 soit le coin supérieur gauche de la plage nommée, vous choisissez des lignes déplacées et des colonnes déplacées à 0. La plage sélectionnée a un nombre de lignes = nombre d’entrées non vides dans la colonne A et nombre de colonnes = nombre des entrées non vides dans la ligne 1. Ainsi, si vous ajoutez des personnes ou des champs de données, la formule se développe automatiquement pour les inclure. Les signes dollar ($) sont nécessaires pour que la plage définie ne change pas si vous vous déplacez dans la feuille de calcul.

Pour l’essayer, entrez la formule = SUM (données) dans la cellule G11. À l’heure actuelle, cette formule totalise tous les nombres dans la fourchette A1: D10 et donne 448 278 $.

Ajoutez maintenant le nom Meredith à la ligne 11, entrez un salaire de 10 000 $ en B11, en    ter une variable pour les erreurs (ajoutez le mot erreurs) dans E1 et entrez 1 000 dans E11. La formule = SUM (données) inclut désormais les 10 000 et 1 000 et se met automatiquement à jour à 459 278 $.

Je représente les ventes mensuelles unitaires de mon entreprise. Chaque mois, je télécharge les ventes unitaires du mois le plus récent. Je souhaite que mon graphique soit mis à jour automatiquement. Y a-t-il un moyen facile d’accomplir cela?

Le classeur (voir la figure  ci-dessous) contient les unités vendues du produit de votre entreprise. Comme vous pouvez le voir, les unités vendues ont été cartographiées à l’aide d’un graphique XY (scatter).

FIGURE 11: Vous pouvez utiliser la fonction DECALER pour mettre à jour ce graphique dynamiquement.

À partir de la ligne 19, vous téléchargez de nouvelles données de vente. Existe-t-il un moyen simple de s’assurer que le graphique inclut automatiquement les nouvelles données?

La clé de la mise à jour du graphique consiste à utiliser la fonction DECALER pour créer des noms de plage dynamique pour la colonne Mois et la colonne Unités vendues. Lorsque de nouvelles données sont entrées, la plage dynamique des ventes unitaires inclut automatiquement toutes les données de vente et la plage dynamique des mois inclut chaque numéro de mois. Après avoir créé ces plages, vous pouvez modifier le graphique en remplaçant les plages de données utilisées dans le graphique par les plages dynamiques. Le graphique est désormais mis à jour à mesure que de nouvelles données sont entrées.

Pour commencer, cliquez sur Définir des noms dans l’onglet Formules du ruban pour ouvrir la boîte de dialogue Nouveau nom. Créez une plage nommée Unités en remplissant la boîte de dialogue comme le montre la figure ci-dessous.

FIGURE 12 : Créez un nom de plage dynamique pour les unités vendues.

La saisie = DECALER(‘plage dynamique’! $ C $ 3,0,0, NB (! $ C: $ C), 1) dans la zone Fait référence à de la boîte de dialogue crée une plage d’une colonne de large commençant dans la cellule C3, qui contient le premier point de données de vente unitaire. La plage contiendra autant de nombres qu’il y en a dans la colonne C, qui est dérivée par la partie de la formule qui lit NB («plage dynamique»! $ C: $ C). Lorsque de nouvelles données sont entrées dans la colonne C, les données sont automatiquement incluses dans la plage nommée Unités.

Ensuite, créez une plage dynamique nommée Mois pour les mois entrés dans la colonne B. La formu le est illustrée à la figure.

FIGURE 13 : Il s’agit de la formule qui définit une plage dynamique nommée Mois.

Cliquez maintenant sur n’importe quel point du graphique. Dans la zone de formule, vous voyez la formule SERIES (‘plage dynamique’! $ C $ 2, ‘plage dynamique’! $ B $ 3: $ B $ 18, ‘plage dynamique’! $ C $ 3: $ C $ 18,1). Cette formule est la façon dont Excel récupère les données nécessaires pour tracer le graphique. Remplacez les plages $ B $ 3: $ B $ 18 et $ C $ 3: $ C18 par les noms de plage dynamique comme suit:

SÉRIE («plage dynamique»! $ C $ 2, Chartdynamicrange.x1sx! Mois,Units, 1)

Si un espace vide est répertorié au-dessus de nouvelles données, cette méthode ne fonctionnera pas. Entrez de nouvelles données et vous verrez qu’elles sont incluses dans le graphique.

Remarques

La fonction de tableau Excel facilite la configuration afin que les graphiques et les formules intègrent automatiquement de nouvelles données. Voir l’ article 25, «Tableaux», pour une discussion sur cette fonction.