Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Как распределить пропорции, чтобы мин.значение было "1" - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Как распределить пропорции, чтобы мин.значение было "1" (Формулы/Formulas)
Как распределить пропорции, чтобы мин.значение было "1"
AlexPsy Дата: Четверг, 07.11.2019, 07:44 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 6
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Помогите подобрать формулу, чтобы при распределении пропорции, мин. значение всегда было равно "1" (столбец "N")
К сообщению приложен файл: 8412569.xlsx (19.1 Kb)
 
Ответить
СообщениеПомогите подобрать формулу, чтобы при распределении пропорции, мин. значение всегда было равно "1" (столбец "N")

Автор - AlexPsy
Дата добавления - 07.11.2019 в 07:44
K-SerJC Дата: Четверг, 07.11.2019, 08:43 | Сообщение № 2
Группа: Проверенные
Ранг: Обитатель
Сообщений: 487
Репутация: 86 ±
Замечаний: 0% ±

Excel 2013
мин. значение всегда было равно "1"

так?
Код
=ЕСЛИ($O$16/(СУММ($M$5:$M$15))*M5<0,01;0,01;$O$16/(СУММ($M$5:$M$15))*M5)
К сообщению приложен файл: AlexPsy.xlsx (18.4 Kb)


Благими намерениями выстелена дорога в АД.

Сообщение отредактировал K-SerJC - Четверг, 07.11.2019, 08:44
 
Ответить
Сообщение
мин. значение всегда было равно "1"

так?
Код
=ЕСЛИ($O$16/(СУММ($M$5:$M$15))*M5<0,01;0,01;$O$16/(СУММ($M$5:$M$15))*M5)

Автор - K-SerJC
Дата добавления - 07.11.2019 в 08:43
AlexM Дата: Четверг, 07.11.2019, 09:29 | Сообщение № 3
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4448
Репутация: 1094 ±
Замечаний: 0% ±

Excel 2003
Код
=МАКС(1%;$O$16/СУММ($M$5:$M$15)*M5)



Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
Сообщение
Код
=МАКС(1%;$O$16/СУММ($M$5:$M$15)*M5)

Автор - AlexM
Дата добавления - 07.11.2019 в 09:29
AlexPsy Дата: Четверг, 07.11.2019, 10:35 | Сообщение № 4
Группа: Пользователи
Ранг: Прохожий
Сообщений: 6
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
нет, нужно чтобы общая сумма распределения при этом, не превышала величину части для которой расчитывается...
 
Ответить
Сообщениенет, нужно чтобы общая сумма распределения при этом, не превышала величину части для которой расчитывается...

Автор - AlexPsy
Дата добавления - 07.11.2019 в 10:35
AlexM Дата: Четверг, 07.11.2019, 11:13 | Сообщение № 5
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4448
Репутация: 1094 ±
Замечаний: 0% ±

Excel 2003
а если так?
Код
=$O$16/СУММ($D$5:$D$15)*D5
или
Код
=D5



Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.


Сообщение отредактировал AlexM - Четверг, 07.11.2019, 11:15
 
Ответить
Сообщениеа если так?
Код
=$O$16/СУММ($D$5:$D$15)*D5
или
Код
=D5

Автор - AlexM
Дата добавления - 07.11.2019 в 11:13
AlexPsy Дата: Четверг, 07.11.2019, 11:31 | Сообщение № 6
Группа: Пользователи
Ранг: Прохожий
Сообщений: 6
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
нет, столбец "D" пробит вручную. я ищу формулу чтобы избавится от столбца "D".
в конечном варианте таблицы будет вручную выделена доля, для данной группы позиций.
так же вручную будет прописыватся доходность за период по каждой позиции "M", и нужно пропорционально доходности, распределить "доли позиций" внутри "групповой доли", чтобы при этом у каждой позиции была хотя бы минимальная доля "1".
то есть как то усредняя верхние и средние значения в пользу меньших.


Сообщение отредактировал AlexPsy - Четверг, 07.11.2019, 11:36
 
Ответить
Сообщениенет, столбец "D" пробит вручную. я ищу формулу чтобы избавится от столбца "D".
в конечном варианте таблицы будет вручную выделена доля, для данной группы позиций.
так же вручную будет прописыватся доходность за период по каждой позиции "M", и нужно пропорционально доходности, распределить "доли позиций" внутри "групповой доли", чтобы при этом у каждой позиции была хотя бы минимальная доля "1".
то есть как то усредняя верхние и средние значения в пользу меньших.

Автор - AlexPsy
Дата добавления - 07.11.2019 в 11:31
Светлый Дата: Четверг, 07.11.2019, 12:42 | Сообщение № 7
Группа: Друзья
Ранг: Старожил
Сообщений: 1823
Репутация: 507 ±
Замечаний: 0% ±

Excel 2013, 2016
А как быть с отрицательными значениями?


Программировать проще, чем писать стихи.
 
Ответить
СообщениеА как быть с отрицательными значениями?

Автор - Светлый
Дата добавления - 07.11.2019 в 12:42
AlexPsy Дата: Четверг, 07.11.2019, 12:51 | Сообщение № 8
Группа: Пользователи
Ранг: Прохожий
Сообщений: 6
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
так этот вопрос и пытаюсь решить, ведь временное падение привлекательности по отдельным позициям не должно вести к их обнулению, просто выделение им минимально возможной доли в группе.
на практике в столбце "G" возможно выставление минусового значения при переизбытке в столбце "H", что указывает на продажу избытка.


Сообщение отредактировал AlexPsy - Четверг, 07.11.2019, 12:56
 
Ответить
Сообщениетак этот вопрос и пытаюсь решить, ведь временное падение привлекательности по отдельным позициям не должно вести к их обнулению, просто выделение им минимально возможной доли в группе.
на практике в столбце "G" возможно выставление минусового значения при переизбытке в столбце "H", что указывает на продажу избытка.

Автор - AlexPsy
Дата добавления - 07.11.2019 в 12:51
AlexPsy Дата: Четверг, 07.11.2019, 15:49 | Сообщение № 9
Группа: Пользователи
Ранг: Прохожий
Сообщений: 6
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
формула
Код
=МАКС(1%;$O$16/СУММ($M$5:$M$15)*M5)
хороша, вот только суммарный процент выходит больше изначального значения, и если вручную приписать вычитание лишнего процента от формулы, то получается то что надо.
вот только на автомат поставить невозможно, ругается на цикличность формулы...


Сообщение отредактировал AlexPsy - Четверг, 07.11.2019, 16:03
 
Ответить
Сообщениеформула
Код
=МАКС(1%;$O$16/СУММ($M$5:$M$15)*M5)
хороша, вот только суммарный процент выходит больше изначального значения, и если вручную приписать вычитание лишнего процента от формулы, то получается то что надо.
вот только на автомат поставить невозможно, ругается на цикличность формулы...

Автор - AlexPsy
Дата добавления - 07.11.2019 в 15:49
Светлый Дата: Четверг, 07.11.2019, 17:57 | Сообщение № 10
Группа: Друзья
Ранг: Старожил
Сообщений: 1823
Репутация: 507 ±
Замечаний: 0% ±

Excel 2013, 2016
Сам уже запутался. Возможно, где-то ещё есть ошибка, но с имеющимися данными согласуется.
Формула массива:
Код
=ТЕКСТ($O$16/(СУММ($M$5:$M$15))*M5;"[>0,01]0%;1%;1%")-ЕСЛИ(ЕНД(ПОИСКПОЗ(МУМНОЖ(-ТЕКСТ($O$16/(СУММ($M$5:$M$15))*M5;"[>0,01]"&{0;"0,00"}&"%;1%;1%");{-1:1})/M5;НАИБОЛЬШИЙ(МУМНОЖ(-ТЕКСТ($O$16/(СУММ($M$5:$M$15))*$M$5:$M$15;"[>0,01]"&{0;"0,00"}&"%;1%;1%");{-1:1})/$M$5:$M$15;СТРОКА(ДВССЫЛ("1:"&ОКРУГЛ((СУММ(--ТЕКСТ($O$16/(СУММ($M$5:$M$15))*$M$5:$M$15;"[>0,01]0%;1%;1%"))-$O$16)*100;))));));;1%)
*Немного причесал:
Код
=ТЕКСТ(O$16/(СУММ(M$5:M$15))*M5;"[>,01]0%;1%;1%")-ЕСЛИ(ЕНД(ПОИСКПОЗ(МУМНОЖ(-ТЕКСТ(O$16/(СУММ(M$5:M$15))*M5;"[>,01]"&{0;"0,00"}&"%;1;1");{-1:1});НАИБОЛЬШИЙ(МУМНОЖ(-ТЕКСТ(O$16/(СУММ(M$5:M$15))*M$5:M$15;"[>,01]"&{0;"0,00"}&"%;1;1");{-1:1});СТРОКА(ДВССЫЛ("1:"&ОКРУГЛ((СУММ(--ТЕКСТ(O$16/(СУММ(M$5:M$15))*M$5:M$15;"[>,01]0%;1%;1%"))-O$16)*100;))));));;1%)
**Довёл до ума:
Код
=ТЕКСТ(O$16/(СУММ(M$5:M$15))*M5;"[>,01]0%;1%;1%")-ЕСЛИ(ЕНД(ПОИСКПОЗ(СТРОКА(M5)%%%+ABS(МУМНОЖ(-ТЕКСТ(O$16/(СУММ(M$5:M$15))*M5;"[>,01]"&{0;",00"}&"%;1;1");{-1:1}));НАИБОЛЬШИЙ(СТРОКА(M5)%%%+ABS(МУМНОЖ(-ТЕКСТ(O$16/(СУММ(M$5:M$15))*M$5:M$15;"[>,01]"&{0;"0,00"}&"%;1;1");{-1:1}));ЕСЛИОШИБКА(СТРОКА(ДВССЫЛ("1:"&ОКРУГЛ((СУММ(--ТЕКСТ(O$16/(СУММ(M$5:M$15))*M$5:M$15;"[>,01]0%;1%;1%"))-O$16)*100;)));1));));;1%)


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

Сообщение отредактировал Светлый - Четверг, 07.11.2019, 19:19
 
Ответить
СообщениеСам уже запутался. Возможно, где-то ещё есть ошибка, но с имеющимися данными согласуется.
Формула массива:
Код
=ТЕКСТ($O$16/(СУММ($M$5:$M$15))*M5;"[>0,01]0%;1%;1%")-ЕСЛИ(ЕНД(ПОИСКПОЗ(МУМНОЖ(-ТЕКСТ($O$16/(СУММ($M$5:$M$15))*M5;"[>0,01]"&{0;"0,00"}&"%;1%;1%");{-1:1})/M5;НАИБОЛЬШИЙ(МУМНОЖ(-ТЕКСТ($O$16/(СУММ($M$5:$M$15))*$M$5:$M$15;"[>0,01]"&{0;"0,00"}&"%;1%;1%");{-1:1})/$M$5:$M$15;СТРОКА(ДВССЫЛ("1:"&ОКРУГЛ((СУММ(--ТЕКСТ($O$16/(СУММ($M$5:$M$15))*$M$5:$M$15;"[>0,01]0%;1%;1%"))-$O$16)*100;))));));;1%)
*Немного причесал:
Код
=ТЕКСТ(O$16/(СУММ(M$5:M$15))*M5;"[>,01]0%;1%;1%")-ЕСЛИ(ЕНД(ПОИСКПОЗ(МУМНОЖ(-ТЕКСТ(O$16/(СУММ(M$5:M$15))*M5;"[>,01]"&{0;"0,00"}&"%;1;1");{-1:1});НАИБОЛЬШИЙ(МУМНОЖ(-ТЕКСТ(O$16/(СУММ(M$5:M$15))*M$5:M$15;"[>,01]"&{0;"0,00"}&"%;1;1");{-1:1});СТРОКА(ДВССЫЛ("1:"&ОКРУГЛ((СУММ(--ТЕКСТ(O$16/(СУММ(M$5:M$15))*M$5:M$15;"[>,01]0%;1%;1%"))-O$16)*100;))));));;1%)
**Довёл до ума:
Код
=ТЕКСТ(O$16/(СУММ(M$5:M$15))*M5;"[>,01]0%;1%;1%")-ЕСЛИ(ЕНД(ПОИСКПОЗ(СТРОКА(M5)%%%+ABS(МУМНОЖ(-ТЕКСТ(O$16/(СУММ(M$5:M$15))*M5;"[>,01]"&{0;",00"}&"%;1;1");{-1:1}));НАИБОЛЬШИЙ(СТРОКА(M5)%%%+ABS(МУМНОЖ(-ТЕКСТ(O$16/(СУММ(M$5:M$15))*M$5:M$15;"[>,01]"&{0;"0,00"}&"%;1;1");{-1:1}));ЕСЛИОШИБКА(СТРОКА(ДВССЫЛ("1:"&ОКРУГЛ((СУММ(--ТЕКСТ(O$16/(СУММ(M$5:M$15))*M$5:M$15;"[>,01]0%;1%;1%"))-O$16)*100;)));1));));;1%)

Автор - Светлый
Дата добавления - 07.11.2019 в 17:57
Светлый Дата: Пятница, 08.11.2019, 10:12 | Сообщение № 11
Группа: Друзья
Ранг: Старожил
Сообщений: 1823
Репутация: 507 ±
Замечаний: 0% ±

Excel 2013, 2016
Учёл негативное влияние пустого значения 22 строки:
Код
=ЕСЛИ(M5="";"";ТЕКСТ(O$16/СУММ(M$5:M$15)*M5;"[>,01]0%;1%;1%")-ЕСЛИ(ЕНД(ПОИСКПОЗ(СТРОКА(M5)%%%+ABS(МУМНОЖ(-ТЕКСТ(O$16/СУММ(M$5:M$15)*M5;"[>,01]"&{0;",00"}&"%;1;1");{-1:1}));НАИБОЛЬШИЙ(СТРОКА(M5)%%%+ABS(МУМНОЖ(-ТЕКСТ(O$16/СУММ(M$5:M$15)*M$5:M$15;"[>,01]"&{0;"0,00"}&"%;1;1");{-1:1}));ЕСЛИОШИБКА(СТРОКА(ДВССЫЛ("1:"&ОКРУГЛ((СУММ(ЕСЛИ(M$5:M$15="";;--ТЕКСТ(O$16/СУММ(M$5:M$15)*M$5:M$15;"[>,01]0%;1%;1%")))-O$16)*100;)));1));));;1%))
*Иначе делаю выбор уменьшаемых:
Код
=ЕСЛИ(M5="";"";ТЕКСТ(O$16/СУММ(M$5:M$15)*M5;"[>,01]0%;1%;1%")-ЕСЛИ(ЕНД(ПОИСКПОЗ(ЕСЛИ(M5>1%;СТРОКА(M5)%%%+M5);НАИБОЛЬШИЙ(ЕСЛИ(M$5:M$15>1%;СТРОКА(M$5:M$15)%%%+M$5:M$15);ЕСЛИОШИБКА(СТРОКА(ДВССЫЛ("1:"&ОКРУГЛ((СУММ(ЕСЛИ(M$5:M$15="";;--ТЕКСТ(O$16/СУММ(M$5:M$15)*M$5:M$15;"[>,01]0%;1%;1%")))-O$16)*100;)));1));));;1%))
Файл перезалил.
**Формулу исправил, файл перезалил ещё раз.
К сообщению приложен файл: 8412569-1.xlsx (20.7 Kb)


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

Сообщение отредактировал Светлый - Пятница, 08.11.2019, 11:09
 
Ответить
СообщениеУчёл негативное влияние пустого значения 22 строки:
Код
=ЕСЛИ(M5="";"";ТЕКСТ(O$16/СУММ(M$5:M$15)*M5;"[>,01]0%;1%;1%")-ЕСЛИ(ЕНД(ПОИСКПОЗ(СТРОКА(M5)%%%+ABS(МУМНОЖ(-ТЕКСТ(O$16/СУММ(M$5:M$15)*M5;"[>,01]"&{0;",00"}&"%;1;1");{-1:1}));НАИБОЛЬШИЙ(СТРОКА(M5)%%%+ABS(МУМНОЖ(-ТЕКСТ(O$16/СУММ(M$5:M$15)*M$5:M$15;"[>,01]"&{0;"0,00"}&"%;1;1");{-1:1}));ЕСЛИОШИБКА(СТРОКА(ДВССЫЛ("1:"&ОКРУГЛ((СУММ(ЕСЛИ(M$5:M$15="";;--ТЕКСТ(O$16/СУММ(M$5:M$15)*M$5:M$15;"[>,01]0%;1%;1%")))-O$16)*100;)));1));));;1%))
*Иначе делаю выбор уменьшаемых:
Код
=ЕСЛИ(M5="";"";ТЕКСТ(O$16/СУММ(M$5:M$15)*M5;"[>,01]0%;1%;1%")-ЕСЛИ(ЕНД(ПОИСКПОЗ(ЕСЛИ(M5>1%;СТРОКА(M5)%%%+M5);НАИБОЛЬШИЙ(ЕСЛИ(M$5:M$15>1%;СТРОКА(M$5:M$15)%%%+M$5:M$15);ЕСЛИОШИБКА(СТРОКА(ДВССЫЛ("1:"&ОКРУГЛ((СУММ(ЕСЛИ(M$5:M$15="";;--ТЕКСТ(O$16/СУММ(M$5:M$15)*M$5:M$15;"[>,01]0%;1%;1%")))-O$16)*100;)));1));));;1%))
Файл перезалил.
**Формулу исправил, файл перезалил ещё раз.

Автор - Светлый
Дата добавления - 08.11.2019 в 10:12
AlexPsy Дата: Воскресенье, 17.11.2019, 15:37 | Сообщение № 12
Группа: Пользователи
Ранг: Прохожий
Сообщений: 6
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Спасибо.
Приладил первую формулу, но появился вопрос. Если мне потребуется изменить в формуле нижний порог с 1% например до 5%, что нужно скорректировать в формуле? или если хочу брать такое значение из какой то ячейки, то как вписаться?

Или как точнее объяснить... если мне нужно чтобы все позиции не превышающие 5% приравнивались к 1% доле.. так наверное правильнее...


Сообщение отредактировал AlexPsy - Воскресенье, 17.11.2019, 15:49
 
Ответить
СообщениеСпасибо.
Приладил первую формулу, но появился вопрос. Если мне потребуется изменить в формуле нижний порог с 1% например до 5%, что нужно скорректировать в формуле? или если хочу брать такое значение из какой то ячейки, то как вписаться?

Или как точнее объяснить... если мне нужно чтобы все позиции не превышающие 5% приравнивались к 1% доле.. так наверное правильнее...

Автор - AlexPsy
Дата добавления - 17.11.2019 в 15:37
Светлый Дата: Воскресенье, 17.11.2019, 19:21 | Сообщение № 13
Группа: Друзья
Ранг: Старожил
Сообщений: 1823
Репутация: 507 ±
Замечаний: 0% ±

Excel 2013, 2016
Если мне потребуется изменить в формуле нижний порог с 1% например до 5%, что нужно скорректировать в формуле?
Заменить фрагменты "[>,01]0%;1%;1%" на "[>,05]0%;5%;5%" и остальные 1% на 5%. (Не проверял) Но с таким минимумом может не сложиться общая сумма.
если мне нужно
Создавайте новую тему. Формулируйте правильный вопрос. И Вам, скорее всего, ответят.


Программировать проще, чем писать стихи.
 
Ответить
Сообщение
Если мне потребуется изменить в формуле нижний порог с 1% например до 5%, что нужно скорректировать в формуле?
Заменить фрагменты "[>,01]0%;1%;1%" на "[>,05]0%;5%;5%" и остальные 1% на 5%. (Не проверял) Но с таким минимумом может не сложиться общая сумма.
если мне нужно
Создавайте новую тему. Формулируйте правильный вопрос. И Вам, скорее всего, ответят.

Автор - Светлый
Дата добавления - 17.11.2019 в 19:21
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Как распределить пропорции, чтобы мин.значение было "1" (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!