@Buchhalter

Здравствуйте уважаемые Форумчане. Помогите пожалуйста в решении следующей задачи.

В результате нужно создать формулу, которая берет из столбца значений некоторый (иногда разный и по этому надо иметь возможность устанавливать его вручную) диапазон ячеек по вертикали (подряд). При этом надо чтобы формула искала максимальное среднее значение этого диапазона. И самое интересное в том, что у нас есть возможность исключить некоторые (не выгодные, проще говоря маленькие) значения и провести расчеты среднего значения без них. Количество исключаемых значений может быть какое угодно и оно должно браться с определенной ячейки вверху листа. Очень важная деталь: исключаемые значения ОБЯЗАТЕЛЬНО должны идти подряд.

В написании формулы уже есть некоторые успехи и с этим мне помогли отличные ребята с другого форума.

Ту формулу, которую они предложили нужно избавить от недостатков, которых всего 2:
1. Надо чтобы формула не исключала круговым способом, т.е. чтобы она не могла исключить первые и последние значения (считая их такими, что идут подряд).
2. Чтобы можно было самостоятельно задавать необходимые диапазоны ячеек для выборки (например I8:I36).

Очень рассчитываю на Вашу посильную помощь.

В прилагаемом файле чуть более детально о проблемах.

Если есть другой способ решения данной задачи - пожалуйста предлагайте. Буду очень признателен!

Вложения

КнигаR802c.rar (10.3 Кб, 52 просмотров)

0

@KoGG

Сделан модуль с функциями, определенными пользователем, на VBA.

КнигаR802c_нов2.zip

1

@KoGG

... поправил конечное значение.
В предыдущей версии последняя ячейка не могла быть отброшена.

Вложения

КнигаR802c_нов2.zip (17.8 Кб, 46 просмотров)

1

@Buchhalter

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

0

@Buchhalter

Здравствуйте еще раз уважаемый KoGG!

Все работает замечательно. Остался лишь одна проблема - эта функция почему то не воспринимает сложных диапазонов. Например работает в таком виде =НаибольшееСреднее(A4:A404)
Но не работает, если диапазон прерывается =НаибольшееСреднее(A4:A40;A51:A574)

Пожалуйста скажите что не так?

С уважением,
Виктор Сергеевич

0

@KoGG

Записи
недопустимы, так как второй аргумент функции может быть только- единственным числом и у функции только два аргумента.
Я изменил функцию на три аргумента, на второй позиции второй необязательный дипазон данных можно оставлять пустым (писать два символа ';' подряд и заполнять третий аргумент).
Однако второй диапазон обязательно надо брать в кавычки
При протягивании и копировании формул строка в кавычках не будет меняться.
Можно было бы обойтись без кавычек, но тогда пришлось бы второй диапазон заполнять всегда, или любой непрерывный диапазон вводить как два смежных.
Второй диапазон обязательно должен иметь номера строк больше, чем номер строки конца первого диапазона. Первый и второй диапазоны не должны пересекаться, должны быть в одном столбце.
Промежуток между диапазонами не участвует в подсчете количества отбрасываемых строк.

Вложения

КнигаR802c_нов3.zip (19.9 Кб, 18 просмотров)

1

@KoGG

Исправил третью версию, в ней неверно считалось количество исключаемых строк в части ситуаций.

Вложения

МаксСреднееСИсключением_верс4.rar (18.3 Кб, 28 просмотров)

1

@Buchhalter

KoGG, целый день тестировал все возможности, но формула к сожалению не выдает оптимальный результат. Не пойму в чем причина, но один из диапазонов она игнорирует + не верно указывает исключаемые значения. Может возможности эксель не позволяют это сделать и вообще невозможно, чтобы все правильно считалось?

0

@DV68

Buchhalter,
сильно не вникал, но возможно все это можно сделать через автофильтр и функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ?

0

@Buchhalter

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

0

@KoGG

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

1

@KoGG

Как выяснилось, ошибка была в неправилном применении - допускалась только одна функция на книгу.
Новая версия разрешает большое количество вызовов функции и определяет НаибольшееИзНаибольших по книге в целом.
Ячейка, содержащее функцию НаибольшееСреднее, значение которой = НаибольшееИзНаибольших, помечается желтым цветом фона.
Кроме функций, находящихся
в модуле, в книгу встроен обработчик событий, для поиска НаибольшегоИзНаибольших.

Вложения

МаксСреднееСИсключением_верс5.zip (39.9 Кб, 18 просмотров)

1

@KoGG

Иправлена ошибка типа.

Вложения

МаксСреднееСИсключением_верс6.zip (33.9 Кб, 23 просмотров)

0