Sommaire
Pour convertir une dépense dans une unité de temps (c.-à-d. Quotidienne, hebdomadaire, mensuelle, etc.) en d'autres unités de temps, vous pouvez utiliser une formule INDEX et MATCH bidirectionnelle. Dans l'exemple illustré, la formule en E5 (copiée en travers et en bas) est:
=$C5*INDEX(data,MATCH($D5,vunits,0),MATCH(F$4,hunits,0))
où data (O5: S9), vunits (N5: N9) et hunits (O4: S4) sont des plages nommées, comme expliqué ci-dessous.
Explication
Pour convertir une dépense dans une unité de temps (c.-à-d. Quotidienne, hebdomadaire, mensuelle, etc.) en d'autres unités de temps, vous pouvez utiliser une formule INDEX et MATCH bidirectionnelle. Dans l'exemple illustré, la formule en E5 (copiée en travers et en bas) est:
=$C5*INDEX(data,MATCH($D5,vunits,0),MATCH(F$4,hunits,0))
Cette formule utilise une table de recherche avec des plages nommées comme indiqué ci-dessous:
Plages nommées: data (O5: S9), vunits (N5: N9) et hunits (O4: S4).
introduction
Le but est de convertir une dépense dans une unité de temps, en une dépense équivalente dans d'autres unités de temps. Par exemple, si nous avons une dépense mensuelle de 30 $, nous voulons calculer une dépense annuelle de 360 $, une dépense hebdomadaire de 7,50 $, etc.
Comme tant de défis dans Excel, tout dépend de la façon dont vous abordez le problème. Vous pourriez d'abord être tenté d'envisager une chaîne de formules IF imbriquées. Cela peut être fait, mais vous vous retrouverez avec une formule longue et compliquée.
Une approche plus claire consiste à créer une table de recherche contenant les facteurs de conversion pour toutes les conversions possibles, puis à utiliser une formule INDEX et MATCH bidirectionnelle pour récupérer la valeur requise pour une conversion donnée. Une fois que vous avez la valeur, vous pouvez simplement multiplier par le montant initial.
La table de conversion
Le tableau de conversion a les mêmes valeurs pour les étiquettes verticales et horizontales: quotidienne, hebdomadaire, bihebdomadaire, mensuelle et annuelle. Les unités «de» sont répertoriées verticalement et les unités «à» sont répertoriées horizontalement. Pour les besoins de cet exemple, nous voulons d'abord faire correspondre la ligne, puis la colonne. Ainsi, si nous voulons convertir une dépense mensuelle en dépense annuelle, nous faisons correspondre la ligne «mensuelle» et la colonne «annuelle», et retournons 12.
Pour remplir la table elle-même, nous utilisons un mélange de formules simples et de constantes:
Remarque: personnalisez les valeurs de conversion pour répondre à vos besoins spécifiques. La saisie d'une valeur = 1/7 est un moyen simple d'éviter de saisir de longues valeurs décimales.
La formule de recherche
Puisque nous devons localiser une valeur de conversion basée sur deux entrées, une unité de temps «de» et une unité de temps «à», nous avons besoin d'une formule de recherche bidirectionnelle. INDEX and MATCH fournit une solution intéressante. Dans l'exemple illustré, la formule en E5 est:
=$C5*INDEX(data,MATCH($D5,vunits,0),MATCH(F$4,hunits,0))
En travaillant de l'intérieur vers l'extérieur, la première fonction MATCH localise la bonne ligne:
MATCH($D5,vunits,0) // find row, returns 4
Nous tirons l'unité de temps d'origine "de" de la colonne D, que nous utilisons pour trouver la bonne ligne dans la plage nommée vunits (N5: N9). Remarque $ D5 est une référence mixte avec la colonne verrouillée, de sorte que la formule peut être copiée.
La deuxième fonction MATCH localise la colonne:
MATCH(F$4,hunits,0) // find column, returns 5
Ici, nous obtenons la valeur de recherche de l'en-tête de colonne de la ligne 4, et l'utilisons pour trouver la bonne colonne «à» dans la plage nommée hunits (O4: S4). Encore une fois, notez que F $ 4 est une référence mixte avec la ligne verrouillée, de sorte que la formule peut être copiée.
Une fois que les deux formules MATCH ont renvoyé les résultats à INDEX, nous avons:
=$C5*INDEX(data,4,5)
Le tableau fourni à INDEX est la plage de données nommée (O5: S9). Avec une ligne de 4 et une colonne de 5, INDEX renvoie 12, donc nous obtenons un résultat final de 12000 comme ceci:
=$C5*INDEX(data,4,5) =1000*12 =12000
