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

Вход

Регистрация

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

 

= Мир MS Excel/Среднеарифметическое значение при заданном условии - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Среднеарифметическое значение при заданном условии (Формулы/Formulas)
Среднеарифметическое значение при заданном условии
powerware Дата: Суббота, 11.03.2023, 13:30 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 3
Репутация: 0 ±
Замечаний: 0% ±

Доброго дня!
Нужна формула для расчета среднеарифметической цены на основании табличных данных. Таблицу прилагаю.
Требование: к расчету не допускается цена поставщика, которая отклоняется (как вверх, так и вниз) от среднеарифметической цены всех коммерческих предложений (далее - КП) более чем на 20%.
Как я это делаю вручную:
выводится среднеарифметическая цифра/цена по всем КП, далее рядом с каждой ценой КП по формуле определяется наличие отклонения от среднеарифметической цены более чем на 20 %. (Истина отклонение отсутствует, Ложь отклонение присутствует) и там где Ложь вручную начиная с максимальной цены ставлю к цене знак * (например 58188,34*) чтобы исключить из расчета и так прохожу по всем ценам в сторону уменьшения с отклонением Ложь, оставляя Истина.
Собственно вопрос: как то через формулу или макрос можно автоматизировать этот трудоемкий ручной процесс, чтобы на выходе формировалась среднеарифметическая цена с учетом вышеуказанных требований (не включать в расчет цены с отклонением более 20%) и не плодить столбцы с отклонением Истина, Ложь для каждой цены КП? Т.е. по факту в файле нужны цены КП и среднерыночная цена и все.
К сообщению приложен файл: raschet_ceny_dubl_2.xlsx (18.3 Kb)
 
Ответить
СообщениеДоброго дня!
Нужна формула для расчета среднеарифметической цены на основании табличных данных. Таблицу прилагаю.
Требование: к расчету не допускается цена поставщика, которая отклоняется (как вверх, так и вниз) от среднеарифметической цены всех коммерческих предложений (далее - КП) более чем на 20%.
Как я это делаю вручную:
выводится среднеарифметическая цифра/цена по всем КП, далее рядом с каждой ценой КП по формуле определяется наличие отклонения от среднеарифметической цены более чем на 20 %. (Истина отклонение отсутствует, Ложь отклонение присутствует) и там где Ложь вручную начиная с максимальной цены ставлю к цене знак * (например 58188,34*) чтобы исключить из расчета и так прохожу по всем ценам в сторону уменьшения с отклонением Ложь, оставляя Истина.
Собственно вопрос: как то через формулу или макрос можно автоматизировать этот трудоемкий ручной процесс, чтобы на выходе формировалась среднеарифметическая цена с учетом вышеуказанных требований (не включать в расчет цены с отклонением более 20%) и не плодить столбцы с отклонением Истина, Ложь для каждой цены КП? Т.е. по факту в файле нужны цены КП и среднерыночная цена и все.

Автор - powerware
Дата добавления - 11.03.2023 в 13:30
elovkov Дата: Суббота, 11.03.2023, 14:09 | Сообщение № 2
Группа: Друзья
Ранг: Обитатель
Сообщений: 364
Репутация: 50 ±
Замечаний: 0% ±

Excel 2013
Как-то хитро, как только Вы ставите "*" и исключаете из расчета значение, меняется среднеарифметическое и вся оценка по строке


Умное лицо это еще не признак ума. Все глупости на земле делаются именно с этим выражением лица
 
Ответить
СообщениеКак-то хитро, как только Вы ставите "*" и исключаете из расчета значение, меняется среднеарифметическое и вся оценка по строке

Автор - elovkov
Дата добавления - 11.03.2023 в 14:09
bmv98rus Дата: Суббота, 11.03.2023, 14:41 | Сообщение № 3
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4099
Репутация: 766 ±
Замечаний: 0% ±

Excel 2013/2016


Замечательный Временно просто медведь , процентов на 20.
 
Ответить
Сообщениекросс

Автор - bmv98rus
Дата добавления - 11.03.2023 в 14:41
elovkov Дата: Суббота, 11.03.2023, 15:18 | Сообщение № 4
Группа: Друзья
Ранг: Обитатель
Сообщений: 364
Репутация: 50 ±
Замечаний: 0% ±

Excel 2013
Мне кажется постепенное исключение неподходящих значений ломает объективность оценки, надо сразу задать границы от рассчитанного общего среднеарифметического и выводить оттуда в отдельную таблицу уже только те значения, которые в эти границы входят.
Смотри вложение
формула протягивается, в УФ задал для проходных зеленый шрифт
это явно проще чем вручную звездочки проставлять, и ИМХО, гораздо объективнее
К сообщению приложен файл: raschet_ceny.xlsx (17.3 Kb)


Умное лицо это еще не признак ума. Все глупости на земле делаются именно с этим выражением лица

Сообщение отредактировал elovkov - Суббота, 11.03.2023, 15:22
 
Ответить
СообщениеМне кажется постепенное исключение неподходящих значений ломает объективность оценки, надо сразу задать границы от рассчитанного общего среднеарифметического и выводить оттуда в отдельную таблицу уже только те значения, которые в эти границы входят.
Смотри вложение
формула протягивается, в УФ задал для проходных зеленый шрифт
это явно проще чем вручную звездочки проставлять, и ИМХО, гораздо объективнее

Автор - elovkov
Дата добавления - 11.03.2023 в 15:18
elovkov Дата: Суббота, 11.03.2023, 15:50 | Сообщение № 5
Группа: Друзья
Ранг: Обитатель
Сообщений: 364
Репутация: 50 ±
Замечаний: 0% ±

Excel 2013
Еще допилил слегка, если эти цифры для справки вывести но не учитывать, и через УФ подкрасил красным)))
К сообщению приложен файл: 0285057.xlsx (17.6 Kb)


Умное лицо это еще не признак ума. Все глупости на земле делаются именно с этим выражением лица
 
Ответить
СообщениеЕще допилил слегка, если эти цифры для справки вывести но не учитывать, и через УФ подкрасил красным)))

Автор - elovkov
Дата добавления - 11.03.2023 в 15:50
bigorq Дата: Суббота, 11.03.2023, 15:55 | Сообщение № 6
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 197
Репутация: 47 ±
Замечаний: 0% ±

Нет
elovkov, у меня тоже такое среднее получилось
Код
=AVERAGEIFS($E3:$L3;$E3:$L3;"<"&1.2*$N3;$E3:$L3;">"&$N3/1.2)
К сообщению приложен файл: raschet_ceny_dubl_2_2.xls (15.5 Kb)


Сообщение отредактировал bigorq - Суббота, 11.03.2023, 15:56
 
Ответить
Сообщениеelovkov, у меня тоже такое среднее получилось
Код
=AVERAGEIFS($E3:$L3;$E3:$L3;"<"&1.2*$N3;$E3:$L3;">"&$N3/1.2)

Автор - bigorq
Дата добавления - 11.03.2023 в 15:55
Gustav Дата: Суббота, 11.03.2023, 16:13 | Сообщение № 7
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2698
Репутация: 1123 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
И у меня такое же, если звёздочки почистить:
Код
=СРЗНАЧЕСЛИМН(E3:T3; E3:T3; "<="&ОКРУГЛ(СРЗНАЧ(E3:T3);2)*1,2; E3:T3; ">="&ОКРУГЛ(СРЗНАЧ(E3:T3);2)/1,2)

Как у меня возникло это представление, так никакое обсуждение ни здесь, ни там пока не убедило в чём-то другом. Естественно, согласен с теми участниками, которые тоже придерживаются этого подхода.

[p.s.]А вообще, основываясь на своем совсем небольшом, но всё же опыте,[/p.s.] я бы вместо первого среднего (которое по всем значениям) ориентировался на медиану:
Код
=СРЗНАЧЕСЛИМН(E3:T3; E3:T3; "<="&ОКРУГЛ(МЕДИАНА(E3:T3);2)*1,2; E3:T3; ">="&ОКРУГЛ(МЕДИАНА(E3:T3);2)/1,2)


МОИ: Ник, Tip box: 41001663842605

Сообщение отредактировал Gustav - Суббота, 11.03.2023, 16:31
 
Ответить
СообщениеИ у меня такое же, если звёздочки почистить:
Код
=СРЗНАЧЕСЛИМН(E3:T3; E3:T3; "<="&ОКРУГЛ(СРЗНАЧ(E3:T3);2)*1,2; E3:T3; ">="&ОКРУГЛ(СРЗНАЧ(E3:T3);2)/1,2)

Как у меня возникло это представление, так никакое обсуждение ни здесь, ни там пока не убедило в чём-то другом. Естественно, согласен с теми участниками, которые тоже придерживаются этого подхода.

[p.s.]А вообще, основываясь на своем совсем небольшом, но всё же опыте,[/p.s.] я бы вместо первого среднего (которое по всем значениям) ориентировался на медиану:
Код
=СРЗНАЧЕСЛИМН(E3:T3; E3:T3; "<="&ОКРУГЛ(МЕДИАНА(E3:T3);2)*1,2; E3:T3; ">="&ОКРУГЛ(МЕДИАНА(E3:T3);2)/1,2)

Автор - Gustav
Дата добавления - 11.03.2023 в 16:13
powerware Дата: Суббота, 11.03.2023, 17:11 | Сообщение № 8
Группа: Пользователи
Ранг: Прохожий
Сообщений: 3
Репутация: 0 ±
Замечаний: 0% ±

Ребят, огромное спасибо elovkov, bigorq, Gustav, что помогли. Решение от Gustav, подходит идеально для моих задач. Спасибо!!!
 
Ответить
СообщениеРебят, огромное спасибо elovkov, bigorq, Gustav, что помогли. Решение от Gustav, подходит идеально для моих задач. Спасибо!!!

Автор - powerware
Дата добавления - 11.03.2023 в 17:11
bmv98rus Дата: Суббота, 11.03.2023, 18:40 | Сообщение № 9
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4099
Репутация: 766 ±
Замечаний: 0% ±

Excel 2013/2016
powerware, чисто гипотетически рассмотрите вариант такого набора 5,6,7,10,13,14,15 методика покажет что нужно выбрать 10, но логика показывает что нудно выбрать 6 или 7 . понятно что это надуманный случай, но для оценки которую вы хотите получить надо искать максимальную плотность при в диапазоне наименьшей стоимости и на это ориентироваться.


Замечательный Временно просто медведь , процентов на 20.
 
Ответить
Сообщениеpowerware, чисто гипотетически рассмотрите вариант такого набора 5,6,7,10,13,14,15 методика покажет что нужно выбрать 10, но логика показывает что нудно выбрать 6 или 7 . понятно что это надуманный случай, но для оценки которую вы хотите получить надо искать максимальную плотность при в диапазоне наименьшей стоимости и на это ориентироваться.

Автор - bmv98rus
Дата добавления - 11.03.2023 в 18:40
powerware Дата: Воскресенье, 12.03.2023, 10:29 | Сообщение № 10
Группа: Пользователи
Ранг: Прохожий
Сообщений: 3
Репутация: 0 ±
Замечаний: 0% ±

bmv98rus, я полностью с Вами согласен, что в гипотетическом варианте есть две точки максимальной плотности, но вышеуказанная методика выбирает 10. Как тогда в расчетах получить результат максимальной плотности в диапазоне наименьшей стоимости?
 
Ответить
Сообщениеbmv98rus, я полностью с Вами согласен, что в гипотетическом варианте есть две точки максимальной плотности, но вышеуказанная методика выбирает 10. Как тогда в расчетах получить результат максимальной плотности в диапазоне наименьшей стоимости?

Автор - powerware
Дата добавления - 12.03.2023 в 10:29
AlienSphinx Дата: Воскресенье, 12.03.2023, 11:12 | Сообщение № 11
Группа: Пользователи
Ранг: Новичок
Сообщений: 24
Репутация: 6 ±
Замечаний: 0% ±

powerware, если "в лоб", то я бы построил ряд чисел от мин до макс цены с малым приращением на ваше усмотрение (условно, 1 копейка) и запустил итерационный процесс, чтобы посчитать кол-во цен, которые попадают в какой-то заданный интервал (например, +/- 20%) от каждого такого числа. Затем можно задать какое-то кол-во (10%, 20% или сколько хотите от общего кол-ва цен) и отфильтровать наши вычисления по этому параметру. Наименьшее число, которое будет удовлетворять этому условию и будет искомой величиной.
upd или же отправить результаты отработки алгоритма в какой то численный метод определения экстремумов.
Но на вашей выборке в несколько чисел - стоит ли этим заниматься?


Сообщение отредактировал AlienSphinx - Воскресенье, 12.03.2023, 23:11
 
Ответить
Сообщениеpowerware, если "в лоб", то я бы построил ряд чисел от мин до макс цены с малым приращением на ваше усмотрение (условно, 1 копейка) и запустил итерационный процесс, чтобы посчитать кол-во цен, которые попадают в какой-то заданный интервал (например, +/- 20%) от каждого такого числа. Затем можно задать какое-то кол-во (10%, 20% или сколько хотите от общего кол-ва цен) и отфильтровать наши вычисления по этому параметру. Наименьшее число, которое будет удовлетворять этому условию и будет искомой величиной.
upd или же отправить результаты отработки алгоритма в какой то численный метод определения экстремумов.
Но на вашей выборке в несколько чисел - стоит ли этим заниматься?

Автор - AlienSphinx
Дата добавления - 12.03.2023 в 11:12
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Среднеарифметическое значение при заданном условии (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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