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

[Buchhalter]

Здравствуйте уважаемые программисты.
Помогите седому бухгалтеру разобраться с проблемой.
Дело вот в чем.
Есть столбец 250 ячеек с конкретными значениями.
Надо
1. чтобы формула искала среднее значение данного диапазона (у меня получилось что-то такое =СРЗНАЧ(A1:A250) )
2. дальше - запоминала его
3. обращалась к ячейке с количеством возможных исключений (оно у меня в отдельной ячейке и может быть равно любому числу от 0 до 249). ну это наверное понятно, если значение 0, то ничего исключать нельзя, если например 5, то можно исключить 1 или 2 или 3 или... 5 значений.
4. дальше формула должна начать исключать по очереди сначала одно значение и просчитывать среднее значение, если оно больше, чем то, что формула запомнила ранее, то она должна запомнить новое значение и как-то отображать или записать в отдельную ячейку адрес ячейки с исключенным значением.
5. дальше формула должна исключить 2 наименьших значения и опять все пересчитать и запомнить и так далее, пока не исчерпает количество возможных исключений (см. пункт 3 выше).

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

Очень прошу помочь. Я уже не молод и мне очень сложно подружится с такими программными монстрами как excel

[MCH]

формула должна начать исключать по очереди

Если исключать по очереди все возможные варианты, то устаним их перебирать
так например, чтобы исключать любые 5 чисел из 250 нужно перебрать 7 817 031 300 комбинаций (250!/245!/5!)

Самое простое, если нужно максимизировать среднее значение, то нужно исключать наименьшие числа, функции НАИМЕНЬШИЙ и НАИБОЛЬШИЙ Вам помогут в этом
Без Вашего примера не получится показать (самому пример рисовать не хочется)

Для исключения 5 наименьших и нахождения среднего:

=(СУММ(A1:A250)-СУММ(НАИМЕНЬШИЙ(A1:A250;{1:2:3:4:5})))/245

PS: чем больше наименьших чисел исключаем, тем больше будет среднее значение

[MCH]

А еще можно использовать автофильтр, выбрать 245 наибольших значений и по ним уже считать среднее

[IgorGO]

чем больше наименьших чисел исключаем, тем больше будет среднее значение

если исключить все наименьшие, то среднее будет равно максимальному

[MCH]

Сообщение от IgorGO

если исключить все наименьшие, то среднее будет равно максимальному

Ну и я о том же, нужно исключить 249 наименьших значений

[Buchhalter]

MCH и IgorGO
Во-первых, извините, что не появлялся сразу. Что-то у меня давление плавает, совсем себя плохо чувствую.
Спасибо большое. Да, конечно если исключать самые маленькие значения, то действительно можно воспользоваться формулой простейшего вида

=(СУММ(A1:A250)-СУММ(НАИМЕНЬШИЙ(A1:A250;{1:2:3:4:5})))/245

Собственно, я задавал этот вопрос, чтобы понять как это вообще работает. Не хотел грузить дополнительными условиями. Думал, может если еще постараюсь, то не придется Вас опять тревожить. Но к сожалению, мне не удалось разобраться и вся надежда на Вас, дорогие ребята.

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

Долго думал и читал всю ночь. Старался понять как это сделать. Придумал использовать логическую функцию ЕСЛИ.
У меня получилась примерно такая формула

=ЕСЛИ((СРЗНАЧ(A1:A250))>=(СРЗНАЧ(A2:A250));(СРЗНАЧ(A1:A250));(СРЗНАЧ(A2:A250)))

Дальше можно продолжать усложнять формулу, но скоро стало понятно, что усложнять формулу можно до тех пор, пока она будет умещаться в 32 767 знаков. Но исходя из того, что она будет расти как на дрожжах, стает понятно, что на это ограничение экселя я наткнусь очень быстро.
По видимому - нужен другой путь.
Может можно както оптимизировать процесс.

Очень рассчитываю на Вашу помощь, дорогие Ребята.

Добавил свои файл с моими данными. Там 3 листа, но это только начало. У меня еще около 2 тысяч таких листов.

[IgorGO]

требуется уточнить условия и терминологию.
насколько я понял:
исключаются не наименьшие, а исключаются значения, идущие подряд.
т.е. например если надо исключить 3 значения, то это 1,2,3; 2,3,4 ... 7,8,9 ... 100,101,102 ... 250,1,2. т.е. за 250-м список не заканчивается, а продолжается снова с первого, так?

если задано 5 исключений:
1.находим средене из исходных (250 шт)
2.находим среднее из 249 шт (понятно надо откинуть минимальное и понятно, что среднее из 249 шт будет больше чем предыдущее)
3.находим среднее из 248 шт., откидываая числа рядом стоящими парами.
и т.д. запоминаем максимальное среднее и что надо было выкинуть.

подтвердите правильно ли я все понял. это интересная задача для формулистов.

[Buchhalter]

исключаются не наименьшие, а исключаются значения, идущие подряд.
т.е. например если надо исключить 3 значения, то это 1,2,3; 2,3,4 ... 7,8,9 ... 100,101,102 ... 250,1,2. т.е. за 250-м список не заканчивается, а продолжается снова с первого, так?

Совершенно верно. Только на всякий случай уточню, что если например задано как Вы говорите 5 исключений, то надо действовать именно так, как Вы и написали,
но последняя серия исключений будет не последовательность 246, 247, 248, 249, 250 , а 250, 1, 2, 3, 4.
Все это по тому, что первые и последние данные в ячейках как бы "закольцованы".

1. Да, сначала надо найти среднее число из всех 250. Как я понял

=СРЗНАЧ(A1:A250)

2. Не совсем (хотя этот способ и даст правильный результат). Но нужно провести анализ всех возможных значений и вывести результат, т.е. то самое большое среднее число + информацию о том, сколько значений и какие именно в процессе исключения были отброшены.
Очень важно то, что в идеале надо иметь возможность устанавливать максимально возможное число исключения для конкретной отдельно взятой линии (последовательность от 1 до 250 в столбик).
3. Мне сейчас трудно сказать насколько эффективен способ исключения близлежащих значений. Необходима максимальная точность вычислений. Промежуточные данные возможно будут необходимы для построения технических графиков. Пока это под вопросом,но вероятность все же есть.

Спасибо Вам Игорь, за желание помочь, за то, что не бросаете старика в беде.

[MCH]

Сообщение от IgorGO

это интересная задача для формулистов.

Не то чтобы уж очень интересная...

[Buchhalter]

Сообщение от MCH

Не то чтобы уж очень интересная...

Спасибо огромное! Сейчас постараюсь разобраться. Если возникнут вопросы, боюсь Вас снова придется потревожить.

ПС
Жизнь показывает, что люди все более безразличны. Очень радует, что остаются те, кто может и хочет помочь.