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

Вход

Регистрация

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

 

= Мир MS Excel/Исключение выбросов по критерию через ИНДЕКС и массив - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
  • Страница 1 из 1
  • 1
Модератор форума: _Boroda_, Manyasha, SLAVICK, китин  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Исключение выбросов по критерию через ИНДЕКС и массив (Формулы/Formulas)
Исключение выбросов по критерию через ИНДЕКС и массив
Niamato Дата: Вторник, 20.08.2019, 18:18 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 1
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Доброго времени суток!
Во вложении к посту пример файла с моей попыткой создания таблицы для исключения выбросов по G-критерию Граббса(Груббса).
Метод Граббса заключается в получении G-критерия для наименьшего и наибольшего значений выборки и сравнении с табличными величинами. Если G-критерий конкретной величины больше порогового значения - то он определяется как выброс, исключается из выборки и данные пересчитываются вновь, пока не G-критерии не будут соответствовать условию. Подробнее - П.6 ГОСТ Р 8.736-2011.
Исходные данные вносятся в столбцы В и С, затем, через копировать-вставить переносятся в столбец J и обрабатываются Excel'ем.
Обычно я данные из столбца J удаляю вручную, в таблице указаны для примера 45 значений, но может быть и 200, но меньше 1000 обычно. Выбросов так же может быть достаточно.
Не могу сам прописать формулу через {=ЕСЛИОШИБКА(ИНДЕКС.....)}, постоянно вываливается в ошибку.
В связи с этим у меня вопрос - можно ли вообще прописать такое условие, чтобы в случае, если G-критерий наибольшего значения из отсортированного диапазона превышает допустимое, то значение исключалось бы для проведения расчётов и считалось снова? Так же эти значения, желательно, переносить в соответствующую таблицу (выделено желтым), но не обязательно.
Или мне надо начинать изучение макросов для этого?
В таблице есть 3 листа с данными - исходные, после исключения минимального, после исключения максимального, по шагам, но понятно, что данные так выводиться не будут, просто для наглядности показал.
Если что - сильно не кидайте тапками.. :shy:
К сообщению приложен файл: 8613058.xlsx(85.4 Kb)
 
Ответить
СообщениеДоброго времени суток!
Во вложении к посту пример файла с моей попыткой создания таблицы для исключения выбросов по G-критерию Граббса(Груббса).
Метод Граббса заключается в получении G-критерия для наименьшего и наибольшего значений выборки и сравнении с табличными величинами. Если G-критерий конкретной величины больше порогового значения - то он определяется как выброс, исключается из выборки и данные пересчитываются вновь, пока не G-критерии не будут соответствовать условию. Подробнее - П.6 ГОСТ Р 8.736-2011.
Исходные данные вносятся в столбцы В и С, затем, через копировать-вставить переносятся в столбец J и обрабатываются Excel'ем.
Обычно я данные из столбца J удаляю вручную, в таблице указаны для примера 45 значений, но может быть и 200, но меньше 1000 обычно. Выбросов так же может быть достаточно.
Не могу сам прописать формулу через {=ЕСЛИОШИБКА(ИНДЕКС.....)}, постоянно вываливается в ошибку.
В связи с этим у меня вопрос - можно ли вообще прописать такое условие, чтобы в случае, если G-критерий наибольшего значения из отсортированного диапазона превышает допустимое, то значение исключалось бы для проведения расчётов и считалось снова? Так же эти значения, желательно, переносить в соответствующую таблицу (выделено желтым), но не обязательно.
Или мне надо начинать изучение макросов для этого?
В таблице есть 3 листа с данными - исходные, после исключения минимального, после исключения максимального, по шагам, но понятно, что данные так выводиться не будут, просто для наглядности показал.
Если что - сильно не кидайте тапками.. :shy:

Автор - Niamato
Дата добавления - 20.08.2019 в 18:18
Светлый Дата: Вторник, 20.08.2019, 19:59 | Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 1272
Репутация: 322 ±
Замечаний: 0% ±

Excel 2010
Добрый вечер!
Не стал использовать {=ЕСЛИОШИБКА(ИНДЕКС.....)}. В зелёных ячейках формула расчёта максимального вылета с учётом ранее выброшенных. Массивная (Ctrl+Shift+Enter):
Код
=МАКС((ЕСЛИ(($I$3:$I$999<>"")*(СЧЁТЕСЛИ($P$14:$P14;$I$3:$I$999)=0);ABS(СРЗНАЧ(ЕСЛИ(($I$3:$I$999<>"")*(СЧЁТЕСЛИ($P$14:$P14;$I$3:$I$999)=0);$J$3:$J$999))-$J$3:$J$999)/СТАНДОТКЛОН(ЕСЛИ(($I$3:$I$999<>"")*(СЧЁТЕСЛИ($P$14:$P14;$I$3:$I$999)=0);$J$3:$J$999)))))
На её основе в синих ячейках рассчитывается номер, который надо будет выбросить.
По величине вылетов можете ограничить количество отбрасываемых.
Остальные листы не нужны.
*Формулу подкорректировал, файл перезалил.
Код
=МАКС((ЕСЛИ(СЧЁТЕСЛИ(P$13:P14;I$3:I$999)=0;ABS(СРЗНАЧ(ЕСЛИ(СЧЁТЕСЛИ(P$13:P14;I$3:I$999)=0;J$3:J$999))-J$3:J$999)/СТАНДОТКЛОН(ЕСЛИ(СЧЁТЕСЛИ(P$13:P14;I$3:I$999)=0;J$3:J$999)))))
**Слагаемые с %% в синей формуле можно выбросить. Всё равно отбрасывание идёт по порядковому номеру и одинаковость значений не влияет.
К сообщению приложен файл: 8613058-1.xlsx(82.9 Kb)


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

Сообщение отредактировал Светлый - Среда, 21.08.2019, 13:11
 
Ответить
СообщениеДобрый вечер!
Не стал использовать {=ЕСЛИОШИБКА(ИНДЕКС.....)}. В зелёных ячейках формула расчёта максимального вылета с учётом ранее выброшенных. Массивная (Ctrl+Shift+Enter):
Код
=МАКС((ЕСЛИ(($I$3:$I$999<>"")*(СЧЁТЕСЛИ($P$14:$P14;$I$3:$I$999)=0);ABS(СРЗНАЧ(ЕСЛИ(($I$3:$I$999<>"")*(СЧЁТЕСЛИ($P$14:$P14;$I$3:$I$999)=0);$J$3:$J$999))-$J$3:$J$999)/СТАНДОТКЛОН(ЕСЛИ(($I$3:$I$999<>"")*(СЧЁТЕСЛИ($P$14:$P14;$I$3:$I$999)=0);$J$3:$J$999)))))
На её основе в синих ячейках рассчитывается номер, который надо будет выбросить.
По величине вылетов можете ограничить количество отбрасываемых.
Остальные листы не нужны.
*Формулу подкорректировал, файл перезалил.
Код
=МАКС((ЕСЛИ(СЧЁТЕСЛИ(P$13:P14;I$3:I$999)=0;ABS(СРЗНАЧ(ЕСЛИ(СЧЁТЕСЛИ(P$13:P14;I$3:I$999)=0;J$3:J$999))-J$3:J$999)/СТАНДОТКЛОН(ЕСЛИ(СЧЁТЕСЛИ(P$13:P14;I$3:I$999)=0;J$3:J$999)))))
**Слагаемые с %% в синей формуле можно выбросить. Всё равно отбрасывание идёт по порядковому номеру и одинаковость значений не влияет.

Автор - Светлый
Дата добавления - 20.08.2019 в 19:59
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Исключение выбросов по критерию через ИНДЕКС и массив (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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