■ Si je commande jusqu’à 500 unités d’un produit, je paie 3,00 $ l’unité. Si je commande de 501 à 1 200 unités, je paie 2,70 $ l’unité. Si je commande de 1 201 à 2 000 unités, je paie 2,30 $ l’unité. Si je commande plus de 2 000 unités, je paie 2,00 $ l’unité. Comment puis-je écrire une formule qui exprime le coût d’achat en fonction du nombre d’unités achetées?

■ Je viens d’acheter 100 actions au prix de 55 $ l’action. Pour couvrir le risque de baisse de valeur de l’action, j’ai acheté 60 options de vente européennes à six mois. Chaque option a un prix d’exercice de 45 $ et coûte 5 $. Comment puis-je développer une feuille de calcul qui indique le pourcentage de rendement sur six mois de mon portefeuille pour une variété de prix futurs possibles?

■ De nombreux analystes boursiers estiment que les règles de négociation à moyenne mobile peuvent surperformer le marché. Une règle de négociation sur la moyenne mobile couramment suggérée consiste à acheter un titre lorsque le cours du titre dépasse la moyenne des 15 derniers mois et à vendre un titre lorsque le cours du titre passe au-dessous de la moyenne des 15 derniers mois. Comment cette règle de négociation aurait-elle fonctionné par rapport à l’indice boursier Standard & Poor’s 500 (S&P)?

■ Dans le jeu de craps, deux dés sont lancés. Si le total des dés du premier lancer est de 2, 3 ou 12, vous perdez. Si le total des dés du premier lancer est de 7 ou 11, vous gagnez. Sinon, le jeu continue. Comment puis-je écrire une formule pour déterminer l’état du jeu après le premier lancer?

■ Dans la plupart des états financiers pro forma, la trésorerie est utilisée comme prise pour équilibrer l’actif et le passif. Je sais que l’utilisation de la dette comme prise serait plus réaliste. Comment puis-je créer un relevé pro forma ayant la dette comme plugin?

■ Lorsque je copie une formule RECHERCHEV pour déterminer les salaires de chaque employé, j’obtiens beaucoup d’erreurs #NA. Ensuite, lorsque je fais la moyenne des salaires des employés, je ne peux pas obtenir de réponse numérique en raison des erreurs #NA. Puis-je facilement remplacer les erreurs #NA par un espace vide afin de pouvoir calculer le salaire moyen?

■ Ma feuille de calcul contient les revenus trimestriels de Wal-Mart. Puis-je calculer facilement les revenus de chaque année et les placer dans la ligne contenant les ventes du premier trimestre de cette année?

■ Les instructions SI peuvent devenir assez volumineuses.

Combien d’instructions SI puis-je imbriquer dans une cellule? Quel est le nombre maximal de caractères autorisé dans une formule Excel?

Si je commande jusqu’à 500 unités d’un produit, je paie 3,00 $ l’unité. Si je commande de 501 à 1 200 unités, je paie 2,70 $ l’unité. Si je commande de 1 201 à 2 000 unités, je paie 2,30 $ l’unité. Si je commande plus de 2 000 unités, je paie 2,00 $ l’unité. Comment puis-je écrire une formule qui exprime le coût d’achat en fonction du nombre d’unités achetées?

Vous pouvez trouver la solution à cette question sur la feuille de calcul Remise sur quantité dans le fichier. La feuille de calcul est illustrée à la figure1 ci-dessous.

FIGURE 1 : Vous pouvez utiliser une formule SI pour modéliser les remises sur quantité.

Supposons que la cellule A9 contienne votre quantité de commande. Vous pouvez calculer le coût d’une commande en fonction de la quantité commandée en implémentant la logique suivante:

■ Si A9 est inférieur ou égal à 500, le coût est 3 * A9.

■ Si A9 est compris entre 501 et 1 200, le coût est de 2,70 * A9.

■ Si A9 est compris entre 1 201 et 2 000, le coût est de 2,30 * A9.

■ Si A9 est supérieur à 2 000, le coût est de 2 * A9.

Commencez par lier les noms de plage dans A2: A4 aux cellules B2: B4 et reliez les noms de plage dans les cellules D2: D5 aux cellules C2: C5. Ensuite, vous pouvez implémenter cette logique dans la cellule B9 avec la formule suivante:

SI(A9 <= _ couper1, prix1 * A9, SI (A9 <= _ coupe2, prix2 * A9, SI (A9 <= _ couper3, prix3 * A9, prix4 * A9)))

Pour comprendre comment Excel calcule une valeur à partir de cette formule, rappelez-vous que les instructions SI sont évaluées de gauche à droite. Si la quantité commandée est inférieure ou égale à 500 (coupe1), le coût est donné par le prix 1 * A9. Si la quantité de commande n’est pas moins que  n ou égal à 500, la formule vérifie si la quantité commandée est inférieure ou égale à 1 200. Si tel est le cas, la quantité commandée est comprise entre 501 et 1 200 et la formule calcule un coût du prix 2 * A9. Ensuite, la formule vérifie si la quantité de commande est inférieure ou égale à 2 000. Si cela est vrai, la quantité commandée est de 1 201 à 2 000, et la formule calcule un coût du prix 3 * A9. Enfin, si le coût de la commande n’a pas encore été commis, la formule par défaut au prix de valeur 4 * A9. Dans chaque cas, la formule IF renvoie le coût de commande correct. Notez que trois autres quantités de commande sont entrées dans les cellules A10: A12 et la formule de coût est copiée dans B10: B12. Pour chaque quantité de commande, la formule renvoie le coût total correct.

Une formule SI contenant plusieurs instructions SI est appelée formule SI imbriquée.

Je viens d’acheter 100 actions au prix de 55 $ l’action. Pour couvrir le risque de baisse de valeur de l’action, j’ai acheté 60 options de vente européennes à six mois. Chaque option a un prix d’exercice de 45 $ et coûte 5 $. Comment puis-je développer une feuille de calcul qui indique le pourcentage de rendement sur six mois de mon portefeuille pour une variété de prix futurs possibles?

Avant d’aborder ce problème, je veux passer en revue quelques concepts de base du monde de la finance. Une option de vente européenne vous permet de vendre une part d’une action à un moment donné dans le futur (dans ce cas, six mois) pour le prix d’exercice (dans ce cas, 45 $). Si le prix de l’action en six mois est de 45 $ ou plus, l’option n’a aucune valeur. Supposons cependant que le prix du stock en 6 mois soit inférieur à 45 $. Ensuite, vous pouvez gagner de l’argent en achetant une action et en revendant immédiatement le stock pour 45 $. Par exemple, si en 6 mois le titre se vend 37 $, vous pouvez réaliser un bénéfice de 45 $ – 37 $, ou 8 $ par action, en achetant une action pour 37 $ puis en utilisant le put pour revendre l’action pour 45 $. Vous pouvez voir que les options de vente vous protègent contre les mouvements à la baisse du cours d’une action. Dans ce cas, chaque fois que le prix de l’action en six mois est inférieur à 45 $, les put commencent à gagner de la valeur. Cela protège un portefeuille contre une baisse de la valeur des actions qu’il détient. Notez également que le pourcentage de rendement d’un portefeuille (en supposant que les actions du portefeuille ne versent pas de dividendes) est calculé en divisant le nombre de variation de la valeur du portefeuille (valeur finale du portefeuille-valeur initiale du portefeuille) par la valeur initiale du portefeuille.

Dans ce contexte, regardez comment le pourcentage de rendement sur six mois de ce portefeuille, composé de 60 options de vente et de 100 actions, varie, car le cours de l’action varie entre 20 $ et 65 $. Vous pouvez trouver cette solution dans la feuille de calcul Hedging dans le fichier. La feuille de calcul est illustrée à la figure 2.

Les étiquettes dans A2: A7 sont liées aux cellules B2: B7. La valeur initiale du portefeuille est égale à 100 (55 $) +60 (5 $) = 5 800 $, indiquée dans la cellule B7. En copiant la formule SI(A9 <exprice, exprice-A9,0) * Nputs de B9 à B10: B18, vous pouvez calculer la valeur finale des options de vente. Si le prix à six mois est inférieur au  prix d’exercice, vous pouvez évaluer chaque vente comme un prix d’exercice sur six mois. Sinon, chaque put aura une valeur de 0 $ en six mois. Copiez la formule Nshares * A9 de C9 à C10: C18 et calculez la valeur finale des actions. Copie de la formule ((C9 + B9) -startvalue) / startvalue) de D9 à D10: D18 calcule le pourcentage de rendement du portefeuille couvert. Copie de la formule (C9-Nshares * pricenow) / (Nshares * pricenow) de E9 à E10: E18 calcule le pourcentage de rendement du portefeuille si vous n’êtes pas couvert (c’est-à-dire que vous n’avez aucun but).

FIGURE 2 : Il s’agit d’un exemple de couverture qui utilise des instructions SI.

Dans la figure ci-dessus, vous pouvez voir que si le cours de l’action tombe en dessous de 45 $, le portefeuille couvert a un rendement attendu plus élevé que le portefeuille non couvert. Notez également que si le cours de l’action ne diminue pas, le portefeuille non couvert a un rendement attendu plus important. C’est pourquoi l’achat de put est souvent appelé assurance de portefeuille.

De nombreux analystes boursiers pensent que les règles de négociation à moyenne mobile peuvent surperformer le marché. Une règle de négociation sur la moyenne mobile généralement suggérée consiste à acheter un titre lorsque le cours du titre dépasse la moyenne des 15 mois précédents et à vendre un titre lorsque le cours du titre passe au-dessous de la moyenne du cours des 15 derniers mois. Comment cette règle de trading aurait-elle fonctionné contre le Standard & Po   ou 500 Stock Index (S&P)?

Dans cet exemple, comparez les performances de la règle de négociation à moyenne mobile (en l’absence de coûts de transaction pour l’achat et la vente d’actions) à une stratégie d’achat et de conservation. La force d’une règle de négociation à moyenne mobile est qu’elle vous aide à suivre les tendances du marché. Une règle de négociation à moyenne mobile vous permet de monter avec un marché haussier et de vendre avant qu’un marché baissier ne vous détruise.

L’ensemble de données contient la valeur mensuelle de l’indice S&P 500 pour la période de janvier 1871 à octobre 2002. Pour suivre les performances de la stratégie de négociation à moyenne mobile, vous devez suivre les informations suivantes chaque mois:

■ Quelle est la moyenne de l’indice S&P 500 au cours des 15 derniers mois?

■ Est-ce que je possède des actions au début de chaque mois?

■ Dois-je acheter des actions au cours du mois?

■ Est-ce que je vends des actions au cours du mois?

■ Quel est le flux de trésorerie du mois (positif si je vends des actions, négatif si j’achète des actions et 0 sinon)?

La feuille de calcul pour cette situation vous oblige à faire défiler vers le bas de nombreuses lignes. Cela aide à garder les colonnes A et B, ainsi que les en-têtes de la ligne 8, visibles lorsque vous faites défiler vers le bas. Pour ce faire, dans le fichier, déplacez le curseur vers la cellule C9, choisissez Affichage sur le ruban, puis sélectionnez Figer les volets dans le groupe Windows. Cela présente les choix illustrés à la figure  3 ci-dessous.

FIGURE 3 : Choisissez parmi les options Figer les volets.

Ici, vous choisissez Figer les volets, qui garde les colonnes A et B et les lignes 6-8 visibles lorsque vous faites défiler la feuille de calcul. Figer la ligne supérieure conserve uniquement la ligne supérieure visible lors du défilement dans le reste de la feuille de calcul. Par exemple, si la ligne supérieure visible est la ligne 6, vous voyez la ligne 6, quelle que soit la distance de défilement. Si vous choisissez Geler la première colonne, vous voyez toujours la colonne la plus à gauche lorsque vous faites défiler la feuille de calcul. La sélection de Dégeler les volets dans le menu vous ramène à la vue de feuille de calcul normale.

Le fichier, illustré à la figure 4, comprend les formules nécessaires pour suivre l’efficacité d’une stratégie à moyenne mobile. La résolution de ce problème nécessite plusieurs formules SI et certaines des formules SI nécessitent un opérateur ET. Par exemple, vous achetez le stock pendant un mois si et seulement si vous ne possédez pas le stock au début du mois, et que le prix du mois en cours est supérieur à la moyenne mobile sur 15 mois du prix du stock. Le premier mois pour lequel vous pouvez calculer une moyenne mobile de 15 mois est avril 1872, alors commencez vos calculs à la ligne 24.

Supposons que vous possédiez le stock pour la première fois en avril 1872, entrez donc Oui dans la cellule C24.

■ En copiant la formule MOYENNE (B9: B23) de D24 à D25: D1590, vous calculez la moyenne mobile de 15 mois pour chaque mois.

■ En copiant la formule SI (ET (C24 = “Non”, B24> D24), “oui”, “non”) de E24 à E25: E1590, vous déterminez pour chaque mois si la part S&P est achetée au cours du mois. N’oubliez pas que vous n’achetez l’action que si vous ne possédiez pas le titre au début du mois et que la valeur actuelle du S&P dépasse sa moyenne mobile de 15 mois. Remarquez la partie ET de la formule. Il contient deux conditions (plus de deux sont autorisées) séparées par une virgule. Si les deux conditions sont remplies, la formule renvoie Oui; sinon, elle renvoie Non. Pour qu’une formule IF reconnaisse le texte, placez des guillemets (“”) autour du texte.

■ En copiant la formule SI (ET (C24 = “Oui”, B24 <D24), “oui”, “non”) de F24 à F25: F1590, vous déterminez pour chaque mois si l’action S&P est vendue. Le titre est vendu si et seulement si vous déteniez l’action S&P au début du mois et que la valeur actuelle de l’action S&P est inférieure à la moyenne mobile sur 15 mois. Avril 1873 est le premier mois au cours duquel vous vendez vos actions S&P.

■ Au cours d’un mois précédant octobre 2002, si vous achetez une action de S&P au cours du mois, le flux de trésorerie est négatif à la valeur de l’action S&P que vous avez achetée. Si vous vendez une part du S&P au cours du mois, le flux de trésorerie est égal à la valeur du S&P. Sinon, le flux de trésorerie est égal à 0. En octobre 2002, vous vendez toute action S&P que vous détenez pour obtenir un crédit pour sa valeur. Par conséquent, en copiant la formule SI (E24 = “oui”, – B24, IF (F24 = “oui”, B24,0)) de G24 à G25: G1589, vous enregistrez  le flux de trésorerie pour tous les mois avant octobre 2002. La saisie de la formule SI (C1590 = “oui”, B1590,0) dans la cellule G1590 vous donne un crédit pour la vente des actions que vous possédez au début du mois dernier.

• • Dans la cellule G6, calculez le bénéfice total de la stratégie de négociation à moyenne mobile avec la formule SOMME (G24: G1590). Vous pouvez voir que la stratégie de moyenne mobile sur 15 mois génère un bénéfice de 1 319,75 $.
• • Le bénéfice de l’achat et de la détention d’actions correspond simplement à la valeur S&P d’octobre 2002 moins la valeur S&P d’avril 1872. Calculez le profit de la stratégie d’achat et de conservation dans la cellule G7 avec la formule B1590-B24.

Comme vous pouvez le voir, le profit d’achat et de détention de 849,45 $ est bien pire que le profit de la règle de négociation à moyenne mobile. Bien sûr, cette solution ne tient pas compte des coûts de transaction encourus pour acheter et vendre des actions. Si les coûts de transaction sont élevés, cela pourrait effacer les bénéfices excédentaires de la moyenne mobile    la stratégie commerciale gagne.

Dans le jeu de craps, deux dés sont lancés. Si le total des dés du premier lancer est de 2, 3 ou 12, vous perdez. Si le total des dés du premier lancer est de 7 ou 11, vous gagnez. Sinon, le jeu continue. Comment puis-je écrire une formule pour déterminer l’état du jeu après le premier lancer?

Le fait que vous perdiez au craps si vous lancez un 2, 3 ou 12 peut être facilement modélisé en plaçant une formule OU dans une formule IF. Dans la cellule B5 de la feuille de calcul Craps du fichier Ifstatement.xlsx, illustrée à la figure 11-5, entrez l’SI (OU (A5 = 2, A5 = 3, A5 = 12), “perdre”, SI (OU (A5 = 7, A5 = 11), ” gagner “,” continuer “)) formule. Cette formule est ensuite copiée de B5 vers B6: B7. La formule affiche perdre si un 2, 3 ou 12 est entré dans la cellule A5. Il affiche victoire si un 7 ou 11 est entré, et il affiche continuer pour toute autre valeur.

FIGURE 5 Utilisez les instructions SI pour modéliser le premier lancer dans le craps.

Dans la plupart des états financiers pro forma, la trésorerie est utilisée comme prise pour équilibrer l’actif et le passif. Je sais que l’utilisation de la dette comme prise serait plus réaliste. Comment puis-je créer un relevé pro forma ayant la dette comme plugin?

Une déclaration pro forma est fondamentalement une prédiction de l’avenir financier d’une entreprise. Un pro forma nécessite la construction des futurs bilans et comptes de résultats d’une entreprise. Le bilan fournit à tout moment un instantané des actifs et des passifs de la société; un compte de résultat vous indique comment la situation financière de l’entreprise évolue à tout moment. Les déclarations pro forma peuvent aider une entreprise à déterminer ses besoins futurs de dette; ce sont aussi des éléments clés des modèles que les analystes de stock utilisent pour déterminer si un stock est correctement évalué. Dans le fichier, les flux de trésorerie disponibles (FCF) ont été générés pour une entreprise pour les quatre prochaines années. La figure 6 montre le bilan et la figure 7 le compte de résultat.

FIGURE 6 Il s’agit d’un énoncé d’hypothèses et d’un bilan proforma.

FIGURE 7 Il s’agit d’un compte de résultat pro forma.

La colonne D contient des informations sur le statut de l’entreprise (au cours de l’année 0). Les hypothèses de base sont les suivantes:

■ La croissance des ventes (SG) est de 2% par an.

■ Les ventes initiales sont de 1 000 $.

■ Le taux d’intérêt sur la dette est de 10%.

■ Le versement de dividendes représente 5% du revenu net.

■ Le taux d’imposition est de 53%.

■ Le coût des marchandises vendues (COGS) représente 75% des ventes.

■ L’amortissement représente 10% des immobilisations brutes.

■ Les liquidités gagnent 9%.

■ Les actifs courants représentent 15% des ventes.

■ Les passifs courants représentent 7% des ventes.

Attribuez les noms de la plage de cellules C3: C10 aux cellules de la plage D3: D10. Ensuite, au cours de chaque année t, la finance et la comptabilité de base impliquent les relations suivantes, qui sont ensuite mises en œuvre dans une série de formules:

■ Formule 1 Année t + 1 ventes = (Année t ventes) * (1 + SG). Vous pouvez calculer les ventes au cours de chaque année en copiant la formule D28 * (1 + SG) de E28 à F28: H28.

■ Formule 2 Année t COGS = COGS * (Ventes année t). Le COGS de chaque année est calculé en copiant la formule COGS * E28 de E29 à F29: H29.

■ Formule 3 Si les actifs de l’année t> les passifs de l’année t, la dette de l’année t doit être égale à l’actif total de l’année t – Les passifs courants de l’année t – Les capitaux propres de l’année t. Sinon, la dette de l’année t est égale à 0. Vous pouvez calculer la dette de chaque année dans E21: H21 avec la formule IF (E18> E20 + E24, E18-E20-E24,0). Si l’actif total de l’année t est supérieur au passif total de l’année t, cette formule définit la dette de l’année t sur l’actif total de l’année t – Oui     ar t passifs courants – Capitaux propres de l’année t. Cela égalise ou équilibre les actifs et les passifs. Sinon, vous définissez la dette de l’année t sur 0. Dans ce cas, la trésorerie et les titres négociables de l’année t sont utilisés pour équilibrer les actifs et les passifs.

■ Formule 4 Passif courant de l’année t = (CL / Ratio des ventes) * (Ventes de l’année t). Dans E20: H20, utilisez la formule $ H $ 4 * E28 pour calculer les passifs courants pour chaque année (en copiant cette formule de E20 à F20: H20).

■ Formule 5 Capitaux propres année t = Actions t année + Bénéfices non distribués année t. Dans E24: H24, vous pouvez calculer l’équité en copiant la formule SUM (E22: E23) de E24 vers F24: H24.

■ Formule 6 Si la dette de l’année t est supérieure à 0, la trésorerie et les titres négociables de l’année t sont égaux à 0. Sinon, la trésorerie et les titres négociables de l’année t sont égaux à MAX (0, passif total de l’année t – actif courant de l’année t – actif fixe net de l’année t) . Dans E13: H13, calculez la trésorerie et les valeurs mobilières de placement pour chaque année en copiant la formule SI (E21> 0,0, MAX (0, E25-E14-E17)) de E13 à F13: H13.

Si la dette de l’année t est supérieure à 0, vous n’avez pas besoin d’utiliser les liquidités et les titres négociables de l’année t pour équilibrer les actifs et les passifs. Dans ce cas, définissez la trésorerie et les titres négociables de l’année t sur 0. Sinon, définissez la trésorerie et les titres négociables de l’année t sur l’actif total de l’année t – les passifs courants de l’année t – les capitaux propres de l’année t. Cela équilibre les actifs et les passifs si les actifs de l’année t (sans espèces et titres négociables) sont inférieurs aux passifs de l’année t. Si la dette n’équilibre pas les actifs et les passifs, cela crée des actifs liquides comme le bouchon qui équilibre les actifs et les passifs.

■ Formule 7 Charges d’intérêts pour l’année t = (dette pour l’année t) * IRD. Dans E33, calculez les intérêts débiteurs en utilisant la formule IRD * E21, en recopiant cette formule dans F33: H33.

■ Formule 8 Revenu d’intérêts sur l’année t = (espèces et titres négociables sur l’année t) * TAUX. Dans E32: H32, calculez le revenu d’intérêts en copiant la formule LAIR * E13 de E32 à F32: H32.

■ Formule 9 Résultat d’exploitation de l’année t = Ventes de l’année t – Année t COGS – Amortissement de l’année t. Dans E31: H31, le résultat d’exploitation est calculé en copiant la formule E28-E29-E30 de E31 à F31: H31.

■ Formule 10 Dividendes de l’année t = (Revenu net de l’année t) * DIV. Dans E39: H39, copiez la formule E36 * DIV de E39 à F39: H39 pour calculer les dividendes pour chaque année.

■ Formule 11 Bénéfice non réparti de l’année t + 1 = Bénéfice non réparti de l’année t. Calculez les bénéfices non répartis de début chaque année en F38: H38, en copiant la formule E40 de F38 à G38: H38.

■ Formule 12 Bénéfices non distribués de fin d’année = Bénéfices non répartis de début t + Revenu net de l’année t – Dividendes de l’année t. Dans E40: H40, calculez les gains retenus de fin d’année en copiant la formule E38 + E36-E39 de E40 à F40: H40.

■ Formule 13 Bénéfice de l’année t avant impôts = Bénéfice d’exploitation de l’année t – Charges d’intérêts de l’année t + Revenu de trésorerie de l’année t. Calculez le revenu avant impôts en copiant la formule E31-E33 + E32 de E34 à F34: H34.

■ Formule 14 Impôts sur l’année t = (Revenu sur l’année t avant impôts) * TR. Calculez les taxes de chaque année en E35: H35 en copiant la formule TR * E34 de E35 à F35: H35.

■ Formule 15 Revenu net après impôts de l’année t = (Revenu avant impôts de l’année t) – (Taxes de l’année t).

Dans E36: ​​H36, calculez le revenu net de chaque année en copiant la formule E34-E35 de E36 à F36: H36.

■ Formule 16 Année t immobilisations brutes = Année t immobilisations nettes + Année t dépréciation cumulée. Dans les cellules E15: H15, calculez les immobilisations brutes pour chaque année en copiant la formule E17 + E16.

■ Formule 17 Amortissement année t = (Immobilisations nettes année t) * DEP. Pour chaque année, vous pouvez utiliser la formule DEP * E15 pour calculer l’amortissement, en copiant la formule de E30 à F30: H30.

■ Formule 18 Amortissement cumulé année t = Amortissement cumulé année t – 1 + Amortissement année t. Pour chaque année, utilisez la formule D16 + E30 pour calculer la dépréciation accumulée en copiant la formule de E16 à F16: H16.

■ Formule 19 Année t immobilisations nettes = Année t immobilisations brutes – Année t Amortissement cumulé. À la ligne 17, pour calculer les immobilisations nettes, copiez la formule E15-E16 de E17 à F17: H17.

■ Formule 20 Actif total de l’année t = Actifs liquides de l’année t + Immobilisations nettes de l’année t + Trésorerie et titres négociables de l’année t. En ajoutant des actifs liquides, des actifs courants et des actifs fixes nets, vous pouvez calculer le total des actifs en copiant la formule SUM (E13, E14, E17) de E18 à F18: H18.

■ Formule 21 Total des engagements de l’année t = Engagements courants de l’année t + Dette de l’année t + Capitaux propres de l’année t. En copiant la formule SUM (E20, E21, E24) de E25 à F25: H25, vous pouvez calculer le total des engagements pour chaque période. Chaque année s’équilibrera en raison de vos relevés de dette et de vos liquidités.

Les formules 3 et 6 nécessitent l’utilisation d’instructions IF. Cette feuille de travail contiendra également des références circulaires. (Pour plus d’informations sur la résolution des références circulaires, reportez-vous à l’article 10, «Références circulaires».) Par exemple, les relations suivantes créent une référence circulaire:

■ L’encaisse de l’année t affecte l’actif total de l’année t.

■ L’actif total de l’année t affecte la dette de l’année t.

■ La dette de l’année t affecte la trésorerie de l’année t.

Parce que la feuille de calcul con     contient des références circulaires, vous devez cliquer sur l’onglet Fichier, choisir Options, puis Formules, et cocher la case Activer les calculs itératifs. Comme expliqué au chapitre 10, cela permet à Excel de résoudre les références circulaires. Notez que pour chaque année t, le total des actifs de la ligne 18 est égal au total des passifs de la ligne 25. Cela montre la puissance des formules SI combinées à des références circulaires.

Lorsque je copie une formule RECHERCHEV pour déterminer les salaires des employés individuels, j’obtiens beaucoup d’erreurs # N / A. Ensuite, lorsque je fais la moyenne des salaires des employés, je ne peux pas obtenir de réponse numérique en raison des erreurs # N / A. Puis-je facilement remplacer les erreurs # N / A par un espace vide afin de pouvoir calculer le salaire moyen?

Le fichier (voir la figure 8) contient les salaires et les noms de cinq employés dans la plage de cellules D3: E7. Dans D11: D15 est une liste de cinq personnes; calculer leur salaire en copiant la formule = RECHERCHEV (D11, $ D3: $ E 7,2 $, Faux) de E11 à E12: E15. Malheureusement, dans les cellules E13 et E14, Excel affiche une erreur # N / A. NA est l’acronyme de «non disponible». Excel renvoie une valeur d’erreur # N / A lorsqu’une formule ne peut pas renvoyer un résultat approprié. Étant donné que JR et Josh n’ont aucun salaire répertorié, RECHERCHEV ne peut pas retourner une valeur salariale pour eux. Ainsi, lorsque vous calculez le salaire moyen en E16 avec la formule = MOYENNE (E11: E15), vous obtenez une erreur # N / A. De nombreuses personnes remplacent manuellement les erreurs # N / A par des espaces afin que leur formule moyenne se calcule correctement (en ignorant les espaces). La fonction SIERREUR facilite le remplacement des erreurs par un caractère (tel qu’un espace ou un 0). La syntaxe de SIERREUR est SIERREUR (valeur, valeur si erreur).

Le premier argument est la formule que vous souhaitez calculer et le deuxième argument est la valeur à insérer dans la cellule si votre formule renvoie une valeur d’erreur. (Les autres valeurs d’erreur courantes sont # DIV / 0, #NOM, #NOMBRE, #REF, #VALEUR; plus d’informations à ce sujet plus loin dans la section.) Par conséquent, copier l’IFERROR (RECHERCHEV (D11, $ D $ 3: $ E3: $ E7,2, Faux), “”) formule de F11 à F12: F15 calcule correctement le salaire pour chaque employé réel et saisit un espace vide pour les personnes qui ne sont pas des employés réels. La formule = MOYENNE (F11: F15) calcule désormais correctement le salaire moyen pour tous les employés répertoriés.

FIGURE 8 Voici un exemple de la façon dont les formules de capture d’erreur piègent les formules.

Excel 2010 et Excel 2013 fournissent une nouvelle fonction, la fonction AGRÉGER, qui vous permet d’effectuer des calculs et d’ignorer les lignes contenant des erreurs. La syntaxe de la fonction AGRERER est AGREGER(numéro_fonction, option, tableau). Le numéro de fonction est un code entre 1 et 19 qui donne la fonction impliquée dans vos calculs. Par exemple, 1 = MOYENNE et 9 = SOMME. Consultez la rubrique d’aide sur cette fonction (en cliquant sur Aide sur cette fonction dans l’assistant de fonction) pour une liste complète des fonctions disponibles. L’option donne le type de cellules à ignorer dans vos calculs. (Voir Tableau 1.) L’option 6 ignore les valeurs d’erreur. Le tableau est simplement la plage de cellules utilisée dans vos calculs. Comme indiqué dans la cellule E18, la formule AGRÉGAT (1,6, E11: E15) calcule correctement la moyenne (74,6667) des salaires répertoriés.

TABLEAU 1 Types de cellules à ignorer

Comportement des options

Le fichier , illustré à la figure 9, contient des exemples d’autres valeurs d’erreur courantes.

FIGURE 9 Ce sont des exemples de valeurs d’erreur Excel.

■ Dans la cellule D3, la formule = C3 / B3 donne un # DIV / 0! parce que vous ne pouvez pas diviser par 0.

■ Dans la cellule D6, la formule = C6 + D6 donne une #VALEUR! erreur car Jack n’est pas le type de données approprié pour la formule entrée. (Jack est un texte.)

■ Dans la cellule D7, la formule = SOMME (ventes) renvoie un #NOM? erreur, indiquant que le nom de la plage de ventes mentionné dans la formule n’est pas défini.

■ Dans la cellule D8, la formule = SQRT (-1) donne un #NUM! erreur, qui se produit lorsque vous entrez un argument inacceptable dans une fonction. Parce que les nombres négatifs n’ont pas de racines carrées, Excel affiche le #NOMBRE! Erreur.

■ Dans la cellule C9, la saisie de la formule SOMME (A1: A3), puis la suppression de la colonne A entraîne un #REF! erreur car les cellules auxquelles il est fait référence dans la formule (cellules A1: A3) ne figurent plus dans la feuille de calcul.

Vous pouvez utiliser la fonction SIERREUR pour remplacer l’une de ces valeurs d’erreur par un nombre ou une chaîne de texte.

Ma feuille de calcul contient les revenus trimestriels de Wal-Mart. Puis-je calculer facilement t at-il des revenus pour chaque année et les placer dans la ligne contenant les ventes du premier trimestre pour cette année?

Le fichier contient les revenus trimestriels (en millions de dollars) de Wal-Mart. (Voir figure 10.) Les lignes 6, 10, 14, etc. contiennent les revenus du premier trimestre de chaque année. Dans chacune de ces lignes, vous calculerez le total des revenus pour l’année dans la colonne E. Dans les autres lignes, la colonne E doit être vide. Vous pouvez entrer la formule SUM (D6: D9) dans la cellule E6 et copier cette formule dans E10, E14, E18, etc., mais il doit y avoir une meilleure solution. L’utilisation d’une instruction SI avec deux fonctions Excel intéressantes (ROW et MOD) vous permet de saisir facilement la formule une fois, puis de la copier. La fonction ROW (référence de cellule) donne la ligne de référence. La fonction = LIGNE(A6) donne un 6; si vous êtes dans la ligne 6, la fonction = ROW () donnerait également un 6. La fonction MOD (nombre, diviseur) donne le reste lorsque le nombre est divisé par diviseur. Par exemple, MOD (9,4) donne 1, tandis que MOD (6,3) donne 0. Vous voulez que votre formule fonctionne uniquement dans les lignes qui laissent un reste de 2 lorsqu’elle est divisée par 4. Par conséquent, la copie de = SI (MOD (LIGNE (), 4) = 2, SUM (D6: D9), “”) formule de E6 à E7: E57 garantit que vous additionnez les revenus pour l’année en cours uniquement dans les lignes qui laissent un reste de 2 lorsqu’il est divisé par 4. Cela signifie que vous calculez les revenus annuels uniquement au premier trimestre de chaque année, ce qui est l’objectif.

FIGURE 10 Voici un résumé des revenus annuels de Wal-Mart.

Les instructions SI peuvent devenir assez volumineuses.

Combien d’instructions SI puis-je imbriquer dans une cellule? Quel est le nombre maximal de caractères autorisé dans une formule Excel?

Dans Excel , vous pouvez imbriquer jusqu’à 64 instructions SI dans une cellule. Dans les versions précédentes d’Excel, vous pouviez imbriquer un maximum de sept instructions SI. Dans Excel 2010 (et Excel 2007), une cellule peut contenir jusqu’à 32 000 caractères.