Выберите элементы в массиве, которые удовлетворяют критериям

Question

Цель: вырезка каппа-сигмы в Excel
Я хочу реализовать селективную формулу линейной регрессии в Excel (избегая VBA). У меня есть выборка данных, к которой мне нужно применить линейную регрессию. Но мои данные имеют некоторые структурные отклонения. Я хочу отфильтровать их, используя вырезку из каппа-сигмы. По сути, это просто делает линейную регрессию, вычисляет ошибку для каждого элемента данных и, если точка данных имела большую ошибку (некоторая постоянная [каппа] умноженная на стандартное отклонение всех ошибок [сигма]), она будет отклонена из следующей итерации. Затем линейное изменение снова применяется к отсеченному набору данных.

Что у меня пока
У меня есть лист Excel с фиктивными данными (у = 3 * х + некоторые ошибки). Три точки данных (x = 6, x = 10 и x = 16) имеют структурную ошибку (измерение). Поэтому я подгоняю данные (A25:C29), вычисляю ошибку (столбец D) и проверяю, больше ли ошибка, чем каппа (1), умноженная на стандартное отклонение ошибки (B31). Результаты приведены в столбце E. Вы можете видеть, что три точки данных со структурной ошибкой аккуратно определены.

Где я застрял
Я хочу сделать линейную регрессию снова по x и y без точек данных, которые должны быть обрезаны. В наиболее идеальном случае я хотел бы ввести условие (D2:D22<$B$31*$B$32) непосредственно в формулу LINEST , поэтому я получаю результаты без использования промежуточных столбцов (теперь в E:G). Я почти уверен, что это можно сделать с помощью некоторых условий и функций массива, но я не могу заставить его работать.

Answer 1

АГА! Вот ваша формула массива монстров:

{=LINEST(N(OFFSET(B2:B22, SMALL(IF(ABS(D2:D22)<=B31, ROW(B2:B22)-ROW(B2)), ROW(INDIRECT("1:"&COUNTIFS(D2:D22,"<="&B31,D2:D22,">=-"&B31)))), 0, 1)), N(OFFSET(A2:A22, SMALL(IF(ABS(D2:D22)<=B31, ROW(B2:B22)-ROW(B2)), ROW(INDIRECT("1:"&COUNTIFS(D2:D22,"<="&B31,D2:D22,">=-"&B31)))), 0, 1)))}

Похоже, что некоторые из них в комментариях, но мне пришлось немного подправить. Главное, чтобы комментарии использовали ISNUMBER чтобы определить, хотим ли мы использовать значение, и COUNT чтобы определить, сколько всего мы хотим. Я изменил их, чтобы оценить поле error по сравнению со значением Std Dev, используя IF(D2:D22<=B31 а затем подсчитать их на основе тех же критериев, используя COUNTIF(D2:D22,"<="&B31) .

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

2.798424149 = правильное значение (на основе ваших ограниченных диапазонов)
2.798424149 = Значение по моей формуле
2.825623377 = Значение, заданное формулой, которая не игнорирует те, которые имеют высокую ошибку

Между прочим, причина, по которой вы пытались получить 0.329988513 том, что у вас ссылки X и Y поменялись местами. Я предполагаю, что где бы вы ни копировали формулу, их листы были настроены в порядке, обратном вашему.

---

Бонусная мысль: если вы действительно хотите, вы можете оставить поля a*x+b и error в дополнение к остальным, и эту формулу можно настроить, чтобы она продолжала работать. Возможно, вы захотите сохранить эти поля по другим причинам, но в этом случае формула как есть, будет в порядке.

Если вы хотите удалить поле error :

{=LINEST(N(OFFSET(B2:B22, SMALL(IF(ABS(B2:B22-C2:C22)<=B31, ROW(B2:B22)-ROW(B2)), ROW(INDIRECT("1:"&SUMPRODUCT(1*((B2:B22-C2:C22)<=B31)*((B2:B22-C2:C22)>=-B31))))), 0, 1)), N(OFFSET(A2:A22, SMALL(IF(ABS(B2:B22-C2:C22)<=B31, ROW(B2:B22)-ROW(B2)), ROW(INDIRECT("1:"&SUMPRODUCT(1*((B2:B22-C2:C22)<=B31)*((B2:B22-C2:C22)>=-B31))))), 0, 1)))}

Если вы хотите удалить оба поля a*x+b и error :

{=LINEST(N(OFFSET(B2:B22, SMALL(IF(ABS(B2:B22-(B24*A2:A22+C24))<=B31, ROW(B2:B22)-ROW(B2)), ROW(INDIRECT("1:"&SUMPRODUCT(1*((B2:B22-(B24*A2:A22+C24))<=B31)*((B2:B22-(B24*A2:A22+C24))>=-B31))))), 0, 1)), N(OFFSET(A2:A22, SMALL(IF(ABS(B2:B22-(B24*A2:A22+C24))<=B31, ROW(B2:B22)-ROW(B2)), ROW(INDIRECT("1:"&SUMPRODUCT(1*((B2:B22-(B24*A2:A22+C24))<=B31)*((B2:B22-(B24*A2:A22+C24))>=-B31))))), 0, 1)))}

Вы чувствуете это сейчас, мистер Крабс?

Answer 2

Я рекомендую добавить еще два столбца справа, чтобы извлечь нужные значения в компактной форме без пробелов в конце. Используя ваш пример, формула для x & y будет иметь вид:

=IFERROR(SMALL($F$2:$F$22,ROW()-1),NA())
=INDEX($G$2:$G$22,MATCH($H2,$F$2:$F$22,0))

При этом извлекаются все необрезанные значения x & y без пробелов между ними, а дополнительные ячейки внизу показывают ошибки. Затем создайте два именованных диапазона, используя следующую формулу для x & y:

=OFFSET(Sheet1!$H$1,1,0,MATCH(MAX(Sheet1!$F$2:$F$22),Sheet1!$H$2:$H$22,0))
=OFFSET(Sheet1!$I$1,1,0,MATCH(MAX(Sheet1!$F$2:$F$22),Sheet1!$H$2:$H$22,0))

Это выберет все записи в каждом из этих двух компактных разделов, но не ячейки с ошибками внизу. Затем вы можете использовать эти два именованных диапазона для LINEST и все, что вам нравится.

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