Создание формулы с вычислением среднего значения диапазона ячеек

[MCH]

Что именно исключать очень хорошо видно на графиках.
В свою очередь формулы именно так и посчитали.

[Buchhalter]

2MCH
Еще раз огромное Вам спасибо за помощь. К сожалению вынужден снова к Вам обратится за помощью. Дело в том, что написанная Вами формула ведет себя не совсем так, как это определяется условиями моей задачи, а именно:
1. Она выдает максимальное среднее число, но выборку проводит не подряд, а просто исключает загаданное число наименьших значений.
2. Еще одна трудность в том, что не совсем понятно с какого именно значения формула проводит исключение.

ПС
Я очень долго и вдумчиво изучал то, что Вы написали, но даже когда скопировал всю помощь с сайта майкрософт по используемым функциям мне не удалось понять как действует формула в целом. Даже действие некоторых функций для меня остаются загадкой (например МУМНОЖ и ТРАНСП). С одной стороны как бы понятно, что там значения переворачиваются в горизонтальное или вертикальное положение, а с другой стороны для чего - это для меня тайна.

Если не сложно, просветите меня хоть немного в этом вопросе.

ППС
То что вдумчиво читал очень много раз

Сколько исключить – 0 (D1)
С какого исключать=ПОИСКПОЗ(D3;МУМНОЖ(--(ОСТАТ(ТРАНСП(СТРОКА($A$1:$A$250))-СТРОКА($A$1:$A$250);250)>=D$1);$A$1 :$A$250)/(250-D$1)
Макс. Среднее =МАКС(МУМНОЖ(--(ОСТАТ(ТРАНСП(СТРОКА($A$1:$A$250))-СТРОКА($A$1:$A$250);250)>=E$1);$A$1 :$A$250)/(250-E$1))
Возвращает относительное положение элемента массива, который соответствует указанному значению в указанном порядке. Функция ПОИСКПОЗ используется вместо одной из функций ПРОСМОТР, когда требуется позиция элемента в диапазоне, а не сам элемент. Синтаксис
ПОИСКПОЗ(искомое_значение;просматри ваемый_массив;тип_сопоставления)
Искомое_значение — значение, используемое при поиске значения в таблице.
Искомое_значение — это значение, которое сопоставляется со значениями в аргументе просматриваемый_массив. Например, при поиске номера в телефонной книге имя абонента указывается в качестве искомого значения, а требуемым значением будет номер телефона.
Искомое_значение может быть значением (числом, текстом или логическим значением) или ссылкой на ячейку, содержащую число, текст или логическое значение.
Возвращает произведение матриц (матрицы хранятся в массивах). Результатом является массив с таким же числом строк, как массив1 и с таким же числом столбцов, как массив2. Синтаксис
МУМНОЖ(массив1;массив2)
Массив1, массив2 — перемножаемые массивы.
Возвращает остаток от деления аргумента "число" на значение аргумента "делитель". Результат имеет тот же знак, что и делитель. Синтаксис: ОСТАТ(число, делитель)
Аргументы функции ОСТАТ описаны ниже.
Число Обязательный. Число, остаток от деления которого требуется определить.
Делитель Обязательный. Число, на которое нужно разделить (делитель).
Функция ТРАНСП возвращает вертикальный диапазон ячеек в виде горизонтального и наоборот. Функцию ТРАНСП необходимо вводить как формулу массива в диапазон, содержащий столько же строк и столбцов, что и аргумент массив. Функция ТРАНСП используется для изменения ориентации массива или диапазона на листе с вертикальной на горизонтальную и наоборот.
Синтаксис
ТРАНСП(массив)
Аргументы функции ТРАНСП. Массив. Обязательный аргумент. Массив (диапазон ячеек) на листе, который нужно транспонировать. Транспонирование массива заключается в том, что первая строка массива становится первым столбцом нового массива, вторая — вторым столбцом и т. д.
Возвращает номер строки, определяемой ссылкой.
Синтаксис СТРОКА(ссылка)
Ссылка — ячейка или интервал ячеек, для которых определяется номер строки. Если ссылка опущена, то предполагается, что это ссылка на ячейку, в которой находится сама функция СТРОКА.
Если ссылка является интервалом ячеек, а функция СТРОКА введена как вертикальный массив, то функция СТРОКА возвращает номера строк в аргументе ссылка в виде вертикального массива.Ссылка не может ссылаться на несколько областей.
Возвращает наибольшее значение из набора значений. Синтаксис МАКС(число1;число2; ...)
Число1, число2,... — от 1 до 30 чисел, среди которых требуется найти наибольшее.
Заметки
Можно задавать аргументы, которые являются числами, пустыми ячейками, логическими значениями или текстовыми представлениями чисел. Аргументы, которые являются значениями ошибки или текстами, не преобразуемыми в числа, вызывают значения ошибок.
Если аргумент является массивом или ссылкой, то в нем учитываются только числа. Пустые ячейки, логические значения или текст в массиве или ссылке игнорируются. Если логические значения или текст не должны игнорироваться, следует использовать функцию МАКСА.
Если аргументы не содержат чисел, то функция МАКС возвращает 0 (ноль).

[MCH]

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

Это такое впечатление создается для "Линия1" и "Линия 2А",
на графике очень хорошо видно, что наименьшие значения идут подряд, соответственно они подряд и исключаются для нахождения максимального среднего

привел свои данные на листе "Линия3В", где видно, что сначала исключаем значения из ячеек 161-174 а затем из ячеек 128-148

Еще одна трудность в том, что не совсем понятно с какого именно значения формула проводит исключение

Вначале в ячейках D3 (E3,F3 ...) мы находим максимальное среднее значение, которое получилось при исключении количества значений указанных в D1 (E1,F1 ...)
После чего по найденному максимальному значению с помощью функции ПОИСКПОЗ в ячейке D2 (E2, F2 ...) определяем с какой позиции произошло исключение

мне не удалось понять как действует формула в целом... Если не сложно, просветите меня хоть немного в этом вопросе

С помощью ОСТАТ и математических действий мы определяем какие ячейки исключаются и не участвуют в расчетах, при это получаем двумерный массив значений ЛОЖЬ/ИСТИНА (исключен/не исключен) преобразовав его в значения 0/1 с помощью "--"

Складывая значения с помощью МУМНОЖ, получаем линейный массив сумм при скользящем исключении заданного количества чисел (во сказанул, аж самому не понятно)
Затем делим на количество значений и определяем с помощью МАКС максимальное среднее значение

Новичку понять как работает формула очень трудно (практически невозможно), а объясняю я очень плохо, поэтому на листе "Пример" есть пошаговые вычисления с заменой промежуточных вычислений на вычисления одной формулой

[IgorGO]

см. вложение.
данные располагайте в колонке А. их количество может быть произвольным (не обязательно 250), располагаться данные должны подряд, начиная с ячейки А1.
в ячеку Д1 - пишете какое максимальное количество значений можно отбросить. после этого приведенные формулы посчитают:
1. сколько значений, идущих подряд, надо отбросить
2. указывают в какой позиции находится 1-е из отбрасываемых значения (если, например, оказалось что надо отбрасывать, начиная с предпоследнего 5 шт. - это значит отбрасываются: предпоследний, последний, 1, 2, 3. всего 5 шт.)
3. и наконец, каким будет значение среднего оставшихся значений.

правда вычисления происходят в обратном порядке:
максимальное среднее, сколько значений подряд отбросить, в какой позиции 1-е из отбрасываемых значений.

2MCH,
вот такой вариант расчета, я считал интересной задачкой.
спасибо, варианты возможных комбинаций я добросовестно содрал с твоей формулы. ОСТАТ(...;счетз(А:А)) - значительно сократил мой вариант формулы.

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

еще одна особенность этих формул:
если попадутся комбинации значений, когда одинаковое значение среднего можно получить отбрасывая разное количество значений, то формулы выберут вариант, где выбрасывается бОльшее количество значений.
для примера из 5 значений, на котором я тренировался, если указать макс. возможное число отброшенных 2, то максимальное среднее можно получить отбросив 1 (во второй позиции) (7+9+8+4)/4 = 7 или отбросив 7 и 1 (2 значения с первой позиции) (9+8+4)/3 тоже равно 7. формулы укажут на второй вариант - выбрасываем 2 значения с первой позиции.

[MCH]

2IgorGO
Игорь, попатался сам сделать универсальную формулу по твоему алгоритму, которая автоматически определяла сколько нужно отбрасывать
Формула получилась немного короче

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

[Buchhalter]

IgorGO и MCH
Великолепно. Ребята, считает все как и должно! У меня нет слов. Первый раз с этой задачей не придется мучатся часами. И даже настроение улучшилось.

Мне не удобно Вас напрягать, лишний раз, но похоже сам не смогу модифицировать данную формулу под еще одну параллельную задачу с подключениями.

Дело в том, что на каждой линии по 250 перекрестных подключений как можно модифицировать формулу для решения аналогичной задачи, а именно:
надо чтобы формула брала начиная со второй ячейки - A2 (в реальной таблице первая занята текстом) 60 первых значений и искала максимальный средний коэффициент при условии исключения определенного числа ячеек подряд. Потом чтобы брала следующие 60 смещаясь на один ряд вниз (начиная с А3) снова 60 и подсчитывала среднее значение с там же условием и так далее.

ПС
Не сочтите за наглость. Вы и так мне упростили жизнь, за что примного благодарен.

[Buchhalter]

Файл с примером тут
Помогите пожалуйста.