Formuła ogólna
=INT((amount-SUMPRODUCT(denoms,counts))/currentdenom)
Podsumowanie
Aby obliczyć wymagane nominały walut dla określonej kwoty, możesz utworzyć tabelę obliczeń walut, jak pokazano w przykładzie. To rozwiązanie wykorzystuje funkcje INT i SUMPRODUCT.
W przedstawionym przykładzie formuła w D5 to:
=INT(($B5-SUMPRODUCT($C$4:C$4,$C5:C5))/D$4)
Wyjaśnienie
Na początek formuła w C5 to:
=INT($B5/C$4)
Ta formuła dzieli kwotę w kolumnie B przez nominał w C4 (100) i odrzuca resztę za pomocą funkcji LICZBA. Formuły w kolumnie C są prostsze niż te w kilku następnych kolumnach, ponieważ jest to pierwszy nominał - nie musimy martwić się o poprzednie liczenia.
Następnie w D5 najpierw ustalamy, z czym liczy się wartość istniejącego nominału:
SUMPRODUCT($C$4:C$4,$C5:C5)
Tutaj SUMPRODUCT jest skonfigurowany z dwiema macierzami, obie starannie skonfigurowane.
Tablica1 składa się z nominałów z wiersza 4. Ten zakres jest starannie skonstruowany tak, aby można go było „rozszerzać” po skopiowaniu przez tabelę po prawej stronie. Pierwsze odwołanie jest bezwzględne (4 $ C $), a drugie "mieszane" (C $ 4) - wiersz jest zablokowany, ale kolumna się zmieni, powodując rozszerzenie zakresu.
Array2 składa się z istniejących liczników nominałów z wiersza 5, przy zastosowaniu takiego samego podejścia jak powyżej. Zakres rozszerzy się podczas kopiowania w prawo.
Wynikiem tej operacji SUMPRODUCT jest całkowita wartość istniejących dotychczas zliczeń nominałów w tabeli. Jest to odejmowane od pierwotnej wartości w kolumnie B, a następnie dzielone przez „bieżący” nominał z wiersza 4. Tak jak poprzednio, używamy INT, aby usunąć resztę.
Ponieważ formuły z kolumny C są kopiowane w całej tabeli, obliczane są prawidłowe liczby dla każdego nominału.
Sprawdzanie wyniku
Jeśli chcesz sprawdzić wyniki, dodaj kolumnę na końcu tabeli z formułą taką:
=SUMPRODUCT(C$4:H$4,C5:H5)
W każdym wierszu SUMPRODUCT mnoży wszystkie liczenia przez wszystkie nominały i zwraca wynik, który powinien odpowiadać oryginalnym wartościom w kolumnie B.
Funkcja FLOOR
W przykładzie na tej stronie użyto funkcji INT, ale zamiast tego można użyć funkcji FLOOR, aby zaokrąglić w dół według mianownika.
