Общая формула
=INT((amount-SUMPRODUCT(denoms,counts))/currentdenom)
Резюме
Чтобы рассчитать требуемые деноминации валюты с учетом конкретной суммы, вы можете построить таблицу расчета валюты, как показано в примере. Это решение использует функции INT и SUMPRODUCT.
В показанном примере формула в D5:
=INT(($B5-SUMPRODUCT($C$4:C$4,$C5:C5))/D$4)
Объяснение
Для начала, формула C5:
=INT($B5/C$4)
Эта формула делит сумму в столбце B на номинал в C4 (100) и отбрасывает остаток с помощью функции INT. Формулы в столбце C проще, чем в следующих нескольких столбцах, потому что это первое наименование - нам не нужно беспокоиться о предыдущих подсчетах.
Затем в D5 мы сначала выясняем, что учитывается стоимость существующего номинала:
SUMPRODUCT($C$4:C$4,$C5:C5)
Здесь SUMPRODUCT сконфигурирован с двумя массивами, оба тщательно сконфигурированы.
Array1 состоит из номиналов из строки 4. Этот диапазон тщательно сконструирован так, чтобы его можно было «расширить» при копировании по таблице вправо. Первая ссылка является абсолютной ($ C $ 4), а вторая ссылка является «смешанной» (C $ 4) - строка заблокирована, но столбец изменится, что приведет к расширению диапазона.
Array2 состоит из существующих счетчиков достоинств из строки 5 с использованием того же подхода, что и выше. Диапазон будет расширяться по мере того, как он копируется вправо.
Результатом этой операции SUMPRODUCT является общее значение существующих на данный момент счетчиков достоинств в таблице. Это вычитается из исходного значения в столбце B, затем делится на «текущее» значение из строки 4. Как и раньше, мы используем INT для удаления остатка.
Поскольку формулы в столбце C копируются по таблице, вычисляются правильные значения для каждого номинала.
Проверка результата
Если вы хотите проверить свои результаты, добавьте столбец в конец таблицы с такой формулой:
=SUMPRODUCT(C$4:H$4,C5:H5)
В каждой строке СУММПРОИЗВ умножает все значения на все номиналы и возвращает результат, который должен соответствовать исходным значениям в столбце B.
Функция ПОЛ
В примере на этой странице используется INT, но вы также можете использовать функцию FLOOR для округления до знаменателя.
