Разбиение числа на составляющие суммы

Question

Есть ли способ, которым я могу предоставить число, и Excel будет рассчитывать компоненты, которые составляют это число из ограниченного подмножества (без превышения предоставленного числа)

например, Подмножество: (1,25, 2,5, 5,10,20)

и, учитывая число (25), Excel набрал бы 20+5 (или 20,5)

17 -> 10+5+1,25 (ближайшая комбинация, не превышающая 17)

35 -> 20+10+5 = 35

36 -> 20+10+5 = 35

37 -> 20+10+5+1,25 = 36,25

38 -> 20+10+5+2,5 = 37,5

39 -> 20+10+5+2,5+1,25 = 38,75

и так далее

Answer 1

Это было весело!

Где-нибудь в вашей книге (я предлагаю другую вкладку) создайте таблицу компонентов, включая ноль, например: Просто начните со слова "Компоненты", затем добавьте свои числа ниже, затем выберите "Вставить таблицу" на ленте. Обязательно укажите ноль и измените имя таблицы, как выделено. Эта таблица компонентов должна быть в порядке возрастания, но создание таблицы означает, что вы получите эту удобную маленькую кнопку сортировки / фильтрации. :-)

Затем, где-нибудь еще в вашей книге, создайте основную таблицу. Добавьте только первый столбец и все заголовки столбцов, затем снова выберите «Вставить таблицу». Я отредактировал числовой формат стиля Normal на General;General; чтобы нулевые значения отображались как пустые.

Колонки:

Номер - ваш номер

C1 - =VLOOKUP([@Number],Components,1)

C2 - C10 - =VLOOKUP([@Number]-SUM(OFFSET([@C1],0,0,1,COLUMN()-COLUMN([@C1]))),Components,1)

CSum - =SUM([@[C1]:[C10]])

В таблице [@ColumnName] ссылается на ячейку "ColumnName" в текущей строке. Поэтому в моей версии формула COLUMN([@C1]) оценивается как 3, поскольку она находится в столбце C.

Итак, для выделенной ячейки на изображении выше, формула

=VLOOKUP([@Number]-SUM(OFFSET([@C1],0,0,1,COLUMN()-COLUMN([@C1]))),Components,1)

решает в

=VLOOKUP(**35**-SUM(OFFSET(C4,0,0,1,**6** - **3**)),Components,1)

решает в

=VLOOKUP(35-SUM(**C4:E4**),Components,1)

решает в

=VLOOKUP(35-**35**,Components,1)

И поэтому VLOOKUP ищет наибольшее число в компонентах, равное первому аргументу (0), и возвращает результат 0. Это отображается в соответствии с пользовательским форматированием Custom;Custom; так что ноль выглядит пустым.

(Ну, хренов «**», конечно, не является буквально частью того, к чему относится формула, но вместо этого является моей неудачной попыткой выделить измененные части жирным шрифтом. Если кто-нибудь знает, как получить эффект, за который я снимал, исправьте это.)