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

458
Sarah22

Есть ли способ, которым я могу предоставить число, и 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

и так далее

0
Вы хотите сделать это, используя формулы ячейки, VBA, или и то, и другое? Robyn 7 лет назад 0
@Robyn хорошо, если бы вы могли сделать это с формулой ячейки, это, безусловно, ответит на вопрос и будет очень впечатляющим barlop 7 лет назад 1
Вероятно, потребуется VBA, и на этом сайте им не очень нравится делать целые упражнения для людей. так как это VBA, это скорее проблема программирования, но опять же сайт-брат или старший брат, stackoverflow, не любит делать упражнения для людей. Так что было бы лучше спросить на другом форуме, как https://www.excelforum.com/excel-programming-vba-macros/ barlop 7 лет назад 0
Также стоит подумать о том, почему вы задаете этот вопрос. Откуда возникает вопрос. Если вы пытаетесь научиться превосходить ... возможно, VBA, то я полагаю, что для этого может потребоваться цикл - итерация ... А если вы пытаетесь научиться делать это, то есть более простые способы ... так что более прогрессивное упражнение что наращивает ваши знания, имело бы смысл. Как макрос VBA для отображения номеров 1-5. barlop 7 лет назад 0
Здесь много неясностей. Могут ли факторы повториться? Ваше подмножество - все факторы друг друга. Есть много способов их использования. Есть ли другое правило, как наименьшее количество возможных факторов? Как должно быть выражено 60? fixer1234 7 лет назад 0

1 ответ на вопрос

2
JSmart523

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

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

Затем, где-нибудь еще в вашей книге, создайте основную таблицу. Main Table with an input number column, 10 component columns, and a component sum column Добавьте только первый столбец и все заголовки столбцов, затем снова выберите «Вставить таблицу». Я отредактировал числовой формат стиля Normal, General;General;чтобы значения нуля отображались как пустые.

Колонки:

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

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

С2 по С10 - =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;так что ноль отображается пустым.

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

Похожие вопросы