Formule générique
=INT((amount-SUMPRODUCT(denoms,counts))/currentdenom)
Sommaire
Pour calculer les dénominations de devises requises, compte tenu d'un montant spécifique, vous pouvez créer une table de calcul des devises comme indiqué dans l'exemple. Cette solution utilise les fonctions INT et SUMPRODUCT.
Dans l'exemple d'exposition, la formule en D5 est:
=INT(($B5-SUMPRODUCT($C$4:C$4,$C5:C5))/D$4)
Explication
Pour commencer, la formule en C5 est:
=INT($B5/C$4)
Cette formule divise le montant de la colonne B par la dénomination en C4 (100) et supprime le reste en utilisant la fonction INT. Les formules de la colonne C sont plus simples que celles des colonnes suivantes car il s'agit de la première dénomination - nous n'avons pas à nous soucier des décomptes précédents.
Ensuite, dans D5, nous déterminons d'abord avec quoi la valeur de la dénomination existante compte:
SUMPRODUCT($C$4:C$4,$C5:C5)
Ici, SUMPRODUCT est configuré avec deux tableaux, tous deux configurés avec soin.
Array1 se compose des dénominations de la ligne 4. Cette plage est soigneusement construite pour être "élargie" lorsqu'elle est copiée à travers le tableau vers la droite. La première référence est absolue ($ C $ 4) et la deuxième référence est "mixte" (C $ 4) - la ligne est verrouillée mais la colonne changera, provoquant l'extension de la plage.
Array2 se compose des dénominations existantes de la ligne 5, avec la même approche que ci-dessus. La plage s'élargira à mesure qu'elle sera copiée vers la droite.
Le résultat de cette opération SUMPRODUCT est la valeur totale des dénominations existantes jusqu'à présent dans le tableau. Ceci est soustrait de la valeur d'origine dans la colonne B, puis divisé par la dénomination «actuelle» de la ligne 4. Comme précédemment, nous utilisons INT pour supprimer tout reste.
Au fur et à mesure que les formules de la colonne C sont copiées dans le tableau, les comptes corrects pour chaque dénomination sont calculés.
Vérification du résultat
Si vous souhaitez vérifier vos résultats, ajoutez une colonne à la fin du tableau avec une formule comme celle-ci:
=SUMPRODUCT(C$4:H$4,C5:H5)
Dans chaque ligne, SUMPRODUCT multiplie tous les décomptes par toutes les dénominations et renvoie un résultat qui doit correspondre aux valeurs d'origine de la colonne B.
Fonction FLOOR
L'exemple de cette page utilise INT, mais vous pouvez également utiliser la fonction FLOOR pour arrondir par dénominateur à la place.
