Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel (архив)
Страницы: 1
RSS
Расчет среднего значения без учета экстремум
 
#1
25.04.2012 12:52:32
Всем добрый день.    
Не силен в Эселе, но работать надо )  
Суть: есть ряд чисел примерно в одном диапазоне, но в этот ряд попадают значения значительно большие или меньшие остальных.    
Вопрос - Как сделать так чтобы эти значения из расчета исключались?    
Пример во вложении  
Понимаю, что человек логически при взгляде на числовой ряд может выделить эти значения, а может ли нечто подобное сделать Эксель?
 
 
 
#2
25.04.2012 12:59:44
можно так (без максимального и минимального):  
=(СУММ(B4:H4)-МАКС(B4:H4)-МИН(B4:H4))/(СЧЁТ(B4:H4)-2)
 
 
 
#3
25.04.2012 13:09:43
не совсем удачный пример получается привел.    
может получиться так, что минимальное значение будет вполне адекватным, но будет 2 слишком больших. т.е. если в примере 3, заменить на 23, то расчет с помощью этой формулы будет не верным.
 
 
 
#4
25.04.2012 13:23:27
По какому критерию исключать значения которые больше или меньше остальных?  
 
Могу предложить вариант:    
1. определить текущее среднее значение,    
2. определить среднее квадратичное отклонение  
3. определить числа которые попадают в интервал    
Средн. +/- СКО или Средн. +/- 2*СКО или Средн. +/- 3*СКО  
 
Формула массива (вводится нажатием ctrl+shift+enter):  
=СРЗНАЧ(ЕСЛИ((B4:H4>=СРЗНАЧ(B4:H4)-ДИСПР(B4:H4)^0,5)*(B4:H4<=СРЗНАЧ(B4:H4)+ДИСПР(B4:H4)^0,5);B4:H4))
 
 
 
#5
25.04.2012 13:26:43
Может тогда нормализовать данные?  
взять Ln(value2/value1) - и отсеивать те которые больше некоторого пограничного значения.  
 
#22280#
 
 
 
#6
25.04.2012 13:28:40
.
 
 
 
#7
25.04.2012 14:09:23
{=СРЗНАЧ(ЕСЛИ(ABS(R4C2:R4C8-СРЗНАЧ(R4C2:R4C8))<4;R4C2:R4C8))}
Живи и дай жить..
 
 
 
#8
25.04.2012 14:12:57
R4C2:R4C8 - это ваши данные, можно и именем обозначить  
=55522=
Живи и дай жить..
 
 
 
#9
25.04.2012 17:31:02
Может быть - "=УРЕЗСРЕДНЕЕ($B$4:$H$4;0.2)", далее по выбору-желанию - 0,25, 0,3, 0,33, 0,35.... ;) -16346-
 
 
 
#10
26.04.2012 07:00:21
Большое спасибо всем ответившим! Честно говоря, не ожидал такого кол-ва вариантов ответов  
to Z  
насколько я понял функцию, она обрезает с обоих сторон по одинаковому кол-ву максимальных и минимальных значений. не совсем то, т.к. экстремум м.б. быть только один  
to СЛЭН  
скопированная в файл формула не работает :(  
смысл уловить не смог :(  
to Noname  
тоже не понял. но судя по всему нужно будет вводить эти пограничные значения, а это доп работа, которая не нужна  
to MCH  
огромное спасибо за подробное объяснение и вложенный файл. попробовал немного покрутить формулу, вроде подходит ) буду тестировать на большем кол-ве примеров. думаю, если что, можно будет поиграться с уменьшением или увеличением значения СКО
 
 
 
#11
26.04.2012 07:06:26
{quote}{login=Дмитрий}{date=26.04.2012 07:00}{thema=}{post}to СЛЭН  
скопированная в файл формула не работает/{/post}{/quote}  
Как вводить формулу массива:  
После введения формулы в ячейку, ввод завершается нажатием не просто Enter, а Ctrl+Shift+Enter. Формула при этом будет заключена в фигурные скобки - {}.
 
 
 
#12
26.04.2012 12:28:20
файл
Живи и дай жить..
 
 
 
Страницы: 1
Читают тему
Наверх