Как определить максимальную скидку, которую получил пороговый процент населения

Я пытаюсь выяснить, какая формула поможет мне найти максимальную скидку, которую получил пороговый процент населения.

Например, в таблице ниже я хочу узнать максимальную скидку, которую получают не менее 30% населения.

Population   Discount
400          25%
3000         24%
2000         23%
1000         22%
850          20%
400          19%
350          18%
350          15%
1500         13%
450          12%
700          11%
3000         5%
7000         3%
6000         2%

Это достаточно просто, если данные отсортированы, как указано выше, по сумме скидки в порядке убывания. Достаточно просто спуститься вниз по совокупному проценту населения сверху до тех пор, пока не будет достигнуто не менее 30%, а затем проверить процент скидки.

В приведенной выше таблице не менее 30 % населения достигается в строке 8 (30,93 % от общей численности населения), поэтому максимальная скидка, полученная как минимум для 30 % населения, составляет 15 %.

Однако что, если данные не в таком порядке? Как я мог получить тот же ответ из таблицы ниже, не имея возможности полагаться на сортировку в первую очередь по наибольшей скидке?

People   Discount
700      11%
3000     24%
3000     5%
1000     22%
6000     2%
400      25%
350      18%
350      15%
1500     13%
450      12%
850      20%
400      19%
7000     3%
2000     23%

excel excel-formula
person DLR    schedule 21.12.2017    source источник
comment
Я настоятельно рекомендую сортировать данные, это оооочень упрощает жизнь. Если для вас важно сохранить текущий порядок, добавьте пронумерованный вспомогательный столбец, чтобы сортировка по этому столбцу вернула ваши данные в исходный формат.   -  person Luuklag    schedule 21.12.2017
comment
Спасибо, Люклаг. Моя проблема заключается в том, что фактическая исходная таблица сложнее, чем в приведенном выше примере, и отсортирована по другому столбцу, что упрощает использование данных конечным пользователем. К сожалению, таких таблиц несколько, и каждый раз, когда меняются скидки и численность населения, мне приходится переделывать эту задачу, сначала изменяя порядок сортировки по самой высокой скидке, а затем находя максимальную скидку для X% населения. Я надеюсь найти способ устранить монотонность этой задачи.   -  person DLR    schedule 21.12.2017
comment
Что ж, это вполне возможно, если вы готовы написать для этого код VBA.   -  person Luuklag    schedule 21.12.2017
comment
Power Query может быть еще одним вариантом для сортировки.   -  person Alexis Olson    schedule 21.12.2017

Ответы (1)


arrow_upward
0
arrow_downward

Я смог сделать это с помощью вспомогательной колонки.

Определите вспомогательный столбец, который показывает, сколько людей получили эту скидку или выше:

=SUMIFS($A$2:$A$15,$B$2:$B$15,">="&B2)

(при условии, что ваши данные находятся в формате A1: B15). Это дает вам

People   Discount  Helper
700      11%       11000
3000     24%       3400
3000     5%        14000
1000     22%       6400
6000     2%        27000
400      25%       400
350      18%       8000
350      15%       8350
1500     13%       9850
450      12%       10300
850      20%       7250
400      19%       7650
7000     3%        21000
2000     23%       5400

Теперь вам нужно найти максимальную скидку в столбце B так, чтобы значение в столбце C составляло не менее 30% от общего числа людей.

=MAXIFS(B2:B15,C2:C15,">="&30%*SUM(A2:A15))

Вы можете, если хотите, сделать эти жестко запрограммированные 30% ссылкой на ячейку, содержащую этот параметр.

person Alexis Olson    schedule 21.12.2017
comment
MAXIFS доступен в Excel 2016 и более поздних версиях. Для предыдущих версий Excel может потребоваться другой подход. - person shrivallabha.redij; 21.12.2017