Доброго дня! Нужна формула для расчета среднеарифметической цены на основании табличных данных. Таблицу прилагаю. Требование: к расчету не допускается цена поставщика, которая отклоняется (как вверх, так и вниз) от среднеарифметической цены всех коммерческих предложений (далее - КП) более чем на 20%. Как я это делаю вручную: выводится среднеарифметическая цифра/цена по всем КП, далее рядом с каждой ценой КП по формуле определяется наличие отклонения от среднеарифметической цены более чем на 20 %. (Истина отклонение отсутствует, Ложь отклонение присутствует) и там где Ложь вручную начиная с максимальной цены ставлю к цене знак * (например 58188,34*) чтобы исключить из расчета и так прохожу по всем ценам в сторону уменьшения с отклонением Ложь, оставляя Истина. Собственно вопрос: как то через формулу или макрос можно автоматизировать этот трудоемкий ручной процесс, чтобы на выходе формировалась среднеарифметическая цена с учетом вышеуказанных требований (не включать в расчет цены с отклонением более 20%) и не плодить столбцы с отклонением Истина, Ложь для каждой цены КП? Т.е. по факту в файле нужны цены КП и среднерыночная цена и все.
Доброго дня! Нужна формула для расчета среднеарифметической цены на основании табличных данных. Таблицу прилагаю. Требование: к расчету не допускается цена поставщика, которая отклоняется (как вверх, так и вниз) от среднеарифметической цены всех коммерческих предложений (далее - КП) более чем на 20%. Как я это делаю вручную: выводится среднеарифметическая цифра/цена по всем КП, далее рядом с каждой ценой КП по формуле определяется наличие отклонения от среднеарифметической цены более чем на 20 %. (Истина отклонение отсутствует, Ложь отклонение присутствует) и там где Ложь вручную начиная с максимальной цены ставлю к цене знак * (например 58188,34*) чтобы исключить из расчета и так прохожу по всем ценам в сторону уменьшения с отклонением Ложь, оставляя Истина. Собственно вопрос: как то через формулу или макрос можно автоматизировать этот трудоемкий ручной процесс, чтобы на выходе формировалась среднеарифметическая цена с учетом вышеуказанных требований (не включать в расчет цены с отклонением более 20%) и не плодить столбцы с отклонением Истина, Ложь для каждой цены КП? Т.е. по факту в файле нужны цены КП и среднерыночная цена и все.powerware
Мне кажется постепенное исключение неподходящих значений ломает объективность оценки, надо сразу задать границы от рассчитанного общего среднеарифметического и выводить оттуда в отдельную таблицу уже только те значения, которые в эти границы входят. Смотри вложение формула протягивается, в УФ задал для проходных зеленый шрифт это явно проще чем вручную звездочки проставлять, и ИМХО, гораздо объективнее
Мне кажется постепенное исключение неподходящих значений ломает объективность оценки, надо сразу задать границы от рассчитанного общего среднеарифметического и выводить оттуда в отдельную таблицу уже только те значения, которые в эти границы входят. Смотри вложение формула протягивается, в УФ задал для проходных зеленый шрифт это явно проще чем вручную звездочки проставлять, и ИМХО, гораздо объективнееelovkov
Как у меня возникло это представление, так никакое обсуждение ни здесь, ни там пока не убедило в чём-то другом. Естественно, согласен с теми участниками, которые тоже придерживаются этого подхода.
[p.s.]А вообще, основываясь на своем совсем небольшом, но всё же опыте,[/p.s.] я бы вместо первого среднего (которое по всем значениям) ориентировался на медиану:
Как у меня возникло это представление, так никакое обсуждение ни здесь, ни там пока не убедило в чём-то другом. Естественно, согласен с теми участниками, которые тоже придерживаются этого подхода.
[p.s.]А вообще, основываясь на своем совсем небольшом, но всё же опыте,[/p.s.] я бы вместо первого среднего (которое по всем значениям) ориентировался на медиану:
powerware, чисто гипотетически рассмотрите вариант такого набора 5,6,7,10,13,14,15 методика покажет что нужно выбрать 10, но логика показывает что нудно выбрать 6 или 7 . понятно что это надуманный случай, но для оценки которую вы хотите получить надо искать максимальную плотность при в диапазоне наименьшей стоимости и на это ориентироваться.
powerware, чисто гипотетически рассмотрите вариант такого набора 5,6,7,10,13,14,15 методика покажет что нужно выбрать 10, но логика показывает что нудно выбрать 6 или 7 . понятно что это надуманный случай, но для оценки которую вы хотите получить надо искать максимальную плотность при в диапазоне наименьшей стоимости и на это ориентироваться.bmv98rus
Замечательный Временно просто медведь , процентов на 20.
bmv98rus, я полностью с Вами согласен, что в гипотетическом варианте есть две точки максимальной плотности, но вышеуказанная методика выбирает 10. Как тогда в расчетах получить результат максимальной плотности в диапазоне наименьшей стоимости?
bmv98rus, я полностью с Вами согласен, что в гипотетическом варианте есть две точки максимальной плотности, но вышеуказанная методика выбирает 10. Как тогда в расчетах получить результат максимальной плотности в диапазоне наименьшей стоимости?powerware
powerware, если "в лоб", то я бы построил ряд чисел от мин до макс цены с малым приращением на ваше усмотрение (условно, 1 копейка) и запустил итерационный процесс, чтобы посчитать кол-во цен, которые попадают в какой-то заданный интервал (например, +/- 20%) от каждого такого числа. Затем можно задать какое-то кол-во (10%, 20% или сколько хотите от общего кол-ва цен) и отфильтровать наши вычисления по этому параметру. Наименьшее число, которое будет удовлетворять этому условию и будет искомой величиной. upd или же отправить результаты отработки алгоритма в какой то численный метод определения экстремумов. Но на вашей выборке в несколько чисел - стоит ли этим заниматься?
powerware, если "в лоб", то я бы построил ряд чисел от мин до макс цены с малым приращением на ваше усмотрение (условно, 1 копейка) и запустил итерационный процесс, чтобы посчитать кол-во цен, которые попадают в какой-то заданный интервал (например, +/- 20%) от каждого такого числа. Затем можно задать какое-то кол-во (10%, 20% или сколько хотите от общего кол-ва цен) и отфильтровать наши вычисления по этому параметру. Наименьшее число, которое будет удовлетворять этому условию и будет искомой величиной. upd или же отправить результаты отработки алгоритма в какой то численный метод определения экстремумов. Но на вашей выборке в несколько чисел - стоит ли этим заниматься?AlienSphinx
Сообщение отредактировал AlienSphinx - Воскресенье, 12.03.2023, 23:11
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
