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

Старая форма входа

Страница 1 из 1 1
Модератор форума: китин, _Boroda_

Мир 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

Группа: Друзья

Ранг: Старожил

Сообщений: 1829

Репутация: 510 ±

Замечаний: 0% ±

Excel 2013, 2016

Добрый вечер!
Не стал использовать {=ЕСЛИОШИБКА(ИНДЕКС.....)}. В зелёных ячейках формула расчёта максимального вылета с учётом ранее выброшенных. Массивная (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

Ответить

Страница 1 из 1
1