Минимизация значений столбцов в Microsoft Excel к оптимальному

Question

На рисунке ниже показан график из 144 measurements в Column A показанном на графике.

Я решил, что хочу сделать все значения ближе к 4300 который находится посередине, чтобы я мог показать эффект оптимизации до и после. Теперь единственная цель - показать наглядный пример, а не математический. Но затем я решил, что должен сделать это численно, используя Solver optimization в Microsoft Excel, и вот как я это сделал: в column B я предположил, что это =ABS(SUM(B1;-4300)) примененный ко всему столбцу Column B а в D2 общая сумма всех значений в Column B Теперь моей задачей оптимизации в качестве первого испытания было минимизировать D2 до определенного значения, например 15000 где Солвер будет изменять значения в Column A для достижения этой цели, а также где я получу новые значения в Column A , которые ближе к 4300 .

На второй картинке вы можете увидеть результат.

Моя проблема здесь: хотя мне удалось минимизировать мои данные до определенного значения, мне действительно нужно исправить тот факт, что есть некоторые значения "дальше", чем другие, поэтому они не "заслуживают" быть такими же ближе, чем ближе значения к 4300 . Мы можем рассматривать его как более высокий штрафной коэффициент для дальних значений и более низкий штраф для более близких значений. Как и где я должен ввести этот штрафной фактор?

Answer 1

Отношение линейного отображения перемещает среднее значение данных, но не влияет на их дисперсию (или стандартное отклонение).

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

Например, вы можете использовать линейное преобразование: новое значение = новое среднее значение + (старое значение - старое среднее значение)* коэффициент. Когда коэффициент равен 1,0, данные перемещаются без изменения дисперсии. Когда Коэффициент меньше 1 (но больше 0), данные перемещаются ближе к среднему, но данные, которые изначально были дальше от среднего, перемещаются больше.

Если данные характеризуются нормальным распределением (я не могу сказать, потому что вы не опубликовали рабочую книгу), то вы могли бы сначала определить статистику Z, которая измеряет, сколько стандартных отклонений является базовым значением от среднего значения: Z = ( Старое значение - старое среднее)/ старое стандартное отклонение

Затем вы можете использовать эту статистику Z, чтобы определить новое значение (все еще следующее за нормальным распределением), которое имеет новое среднее значение и новое стандартное отклонение. Чтобы выполнить это переназначение, вы должны использовать обратную формулу предыдущей формулы:= Новое среднее значение + Z * Новое стандартное отклонение

При использовании Z-статистики и обратной зависимости, описанной выше, новые данные сохраняют свое нормальное распределение, только с новым средним и стандартным отклонением. Также сохраняется ссылка на исходные данные.

Я использовал статистику Z и отношение обратного отображения для преобразования набора неприемлемых данных, показанных на скриншоте ниже.

Преобразованные данные, показанные на скриншоте ниже, моделируют, как они могут выглядеть при поднятом среднем значении и уменьшенном стандартном отклонении, поэтому большинство точек попадают в пределы спецификации.