Как изменить значания чисел в ячейках под необходимую сумму

Страница 1 из 1 1
Модератор форума: китин, _Boroda_, DrMini

Как изменить значания чисел в ячейках под необходимую сумму (Формулы/Formulas)

esculap

Дата: Суббота, 25.01.2020, 12:12 | Сообщение № 1

Группа: Пользователи

Ранг: Прохожий

Сообщений: 2

Репутация: 0 ±

Замечаний: 0% ±

Excel 2019

Автосумма чисел в этих ячейках составляет 32137. Возможно ли автоматически (скрипт, формула, и т.д.) пропорционально изменить значения чисел в указанных ячейках так, чтобы сумма их составляла 31500?

К сообщению приложен файл: 0412402.xlsx (9.5 Kb)

Ответить

Nic70y

Дата: Суббота, 25.01.2020, 12:24 | Сообщение № 2

Группа: Друзья

Ранг: Экселист

Сообщений: 9133

Репутация: 2416 ±

Замечаний: 0% ±

Excel 2010

Код

=ЕСЛИ(C3;ОКРУГЛ(D$10/C$10*C1;0);D$10-СУММ(D$1:ИНДЕКС(D:D;СТРОКА()-1)))

где D$10=31500

К сообщению приложен файл: 7129070.xlsx (9.7 Kb)

ЮMoney 41001841029809

Ответить

Светлый

Дата: Суббота, 25.01.2020, 13:08 | Сообщение № 3

Группа: Друзья

Ранг: Старожил

Сообщений: 1871

Репутация: 538 ±

Замечаний: 0% ±

Excel 2013, 2016

Упрощённо:

Код

=ОКРУГЛ(C$11/C$10*C1;)

и в последней:

Код

=C11-СУММ(D1:D8)

, где в C11 требуемая сумма.
По-хорошему:

Код

=ОТБР(C1*C$11/C$10)+(СУММПРОИЗВ(Ч(ОСТАТ(C1*C$11/C$10;1)/C1>=ОСТАТ(C$1:C$9*C$11/C$10;1)/C$1:C$9))<=C$11-СУММПРОИЗВ(ОТБР(C$1:C$9*C$11/C$10)))

*Но, если есть одинаковые значения, то лучше:

Код

=ОТБР(C1*C$11/C$10)+(СУММПРОИЗВ(Ч((ОСТАТ(C1*C$11/C$10;1)+СТРОКА()%%%)/C1>=(ОСТАТ(C$1:C$9*C$11/C$10;1)+СТРОКА($1:$9)%%%)/C$1:C$9))<=C$11-СУММПРОИЗВ(ОТБР(C$1:C$9*C$11/C$10)))

Программировать проще, чем писать стихи.

Сообщение отредактировал Светлый - Суббота, 25.01.2020, 13:18

Ответить

esculap

Дата: Суббота, 25.01.2020, 19:15 | Сообщение № 4

Группа: Пользователи

Ранг: Прохожий

Сообщений: 2

Репутация: 0 ±

Замечаний: 0% ±

Excel 2019

Светлый, скажите, пожалуйста, а как быть с таким примером?

К сообщению приложен файл: 2466633.xlsx (9.5 Kb)

Ответить

Pelena

Дата: Суббота, 25.01.2020, 19:51 | Сообщение № 5

Группа: Админы

Ранг: Местный житель

Сообщений: 19518

Репутация: 4632 ±

Замечаний: ±

Excel 365 & Mac Excel

Вариант через Подбор параметра (Данные -- Анализ "что если" -- Подбор параметра)

К сообщению приложен файл: 6828339.xlsx (26.3 Kb)

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816

Ответить

Светлый

Дата: Воскресенье, 26.01.2020, 00:25 | Сообщение № 6

Группа: Друзья

Ранг: Старожил

Сообщений: 1871

Репутация: 538 ±

Замечаний: 0% ±

Excel 2013, 2016

Легко.
*Файл перезалил.

К сообщению приложен файл: 2466633-1.xlsx (10.2 Kb)

Программировать проще, чем писать стихи.

Сообщение отредактировал Светлый - Воскресенье, 26.01.2020, 00:30

Ответить

Светлый

Дата: Вторник, 28.01.2020, 09:18 | Сообщение № 7

Группа: Друзья

Ранг: Старожил

Сообщений: 1871

Репутация: 538 ±

Замечаний: 0% ±

Excel 2013, 2016

Ошибся. Неправильно условие задал. Формула должна быть такой:

Код

=ОТБР(C1*C$11/C$10)+(СУММПРОИЗВ(Ч((ОСТАТ(C1*C$11/C$10;1)+СТРОКА()%%%)<=(ОСТАТ(C$1:C$9*C$11/C$10;1)+СТРОКА($1:$9)%%%)))<=C$11-СУММПРОИЗВ(ОТБР(C$1:C$9*C$11/C$10)))

*Добавил файл с несколькими вариантами формул и проверкой оптимальности результата.

К сообщению приложен файл: 0412402-2.xlsx (11.5 Kb)

Программировать проще, чем писать стихи.

Сообщение отредактировал Светлый - Вторник, 28.01.2020, 10:49

Ответить

Как изменить значания чисел в ячейках под необходимую сумму (Формулы/Formulas)

Страница 1 из 1
1