|
|
Регистрация Восстановить пароль |
Повторная активизация e-mail |
Регистрация | Задать вопрос |
Заплачу за решение |
Новые сообщения |
Сообщения за день |
Расширенный поиск |
Правила |
Всё прочитано |
|
|
Опции темы | Поиск в этой теме |
10.03.2012, 21:21 | #1 |
Пользователь
Регистрация: 10.03.2012
Сообщений: 14
|
Создание формулы с вычислением среднего значения диапазона ячеек
Здравствуйте уважаемые программисты.
Помогите седому бухгалтеру разобраться с проблемой. Дело вот в чем. Есть столбец 250 ячеек с конкретными значениями. Надо 1. чтобы формула искала среднее значение данного диапазона (у меня получилось что-то такое =СРЗНАЧ(A1:A250) ) 2. дальше - запоминала его 3. обращалась к ячейке с количеством возможных исключений (оно у меня в отдельной ячейке и может быть равно любому числу от 0 до 249). ну это наверное понятно, если значение 0, то ничего исключать нельзя, если например 5, то можно исключить 1 или 2 или 3 или... 5 значений. 4. дальше формула должна начать исключать по очереди сначала одно значение и просчитывать среднее значение, если оно больше, чем то, что формула запомнила ранее, то она должна запомнить новое значение и как-то отображать или записать в отдельную ячейку адрес ячейки с исключенным значением. 5. дальше формула должна исключить 2 наименьших значения и опять все пересчитать и запомнить и так далее, пока не исчерпает количество возможных исключений (см. пункт 3 выше). В результате должно получится максимальное среднее значение в конкретной ячейке и (в соседней ячейке) адреса тех ячеек, которые исключила формула в процессе подсчета, чтобы получить искомое максимальное среднее значение. Очень прошу помочь. Я уже не молод и мне очень сложно подружится с такими программными монстрами как excel |
10.03.2012, 21:50 | #2 | |
Форумчанин
Регистрация: 21.11.2010
Сообщений: 326
|
Цитата:
так например, чтобы исключать любые 5 чисел из 250 нужно перебрать 7 817 031 300 комбинаций (250!/245!/5!) Самое простое, если нужно максимизировать среднее значение, то нужно исключать наименьшие числа, функции НАИМЕНЬШИЙ и НАИБОЛЬШИЙ Вам помогут в этом Без Вашего примера не получится показать (самому пример рисовать не хочется) Для исключения 5 наименьших и нахождения среднего: Код:
|
|
10.03.2012, 21:53 | #3 |
Форумчанин
Регистрация: 21.11.2010
Сообщений: 326
|
А еще можно использовать автофильтр, выбрать 245 наибольших значений и по ним уже считать среднее
|
10.03.2012, 22:00 | #4 | |
Новичок
СтарожилДжуниор
Регистрация: 05.02.2008
Сообщений: 9,487
|
Цитата:
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете
|
|
10.03.2012, 22:12 | #5 |
Форумчанин
Регистрация: 21.11.2010
Сообщений: 326
|
|
11.03.2012, 19:04 | #6 |
Пользователь
Регистрация: 10.03.2012
Сообщений: 14
|
MCH и IgorGO
Во-первых, извините, что не появлялся сразу. Что-то у меня давление плавает, совсем себя плохо чувствую. Спасибо большое. Да, конечно если исключать самые маленькие значения, то действительно можно воспользоваться формулой простейшего вида Код:
Дополнительные условия к сожалению делают непригодной простую формулу. Дело в том, что надо исключать не самые маленькие значения, а только подряд и во всех вариантах. Например, сначала просто 1 значение во всех вариантах, потом первое и второе подряд во всех вариантах (включая вариант исключения первого и последнего, т.к. эти значения сначала и с конца тоже считаются такими, что идут подряд), дальше надо исключать три подряд и так далее. Долго думал и читал всю ночь. Старался понять как это сделать. Придумал использовать логическую функцию ЕСЛИ. У меня получилась примерно такая формула Код:
По видимому - нужен другой путь. Может можно както оптимизировать процесс. Очень рассчитываю на Вашу помощь, дорогие Ребята. Добавил свои файл с моими данными. Там 3 листа, но это только начало. У меня еще около 2 тысяч таких листов. Последний раз редактировалось Buchhalter; 11.03.2012 в 19:23. |
11.03.2012, 19:39 | #7 |
Новичок
СтарожилДжуниор
Регистрация: 05.02.2008
Сообщений: 9,487
|
требуется уточнить условия и терминологию.
насколько я понял: исключаются не наименьшие, а исключаются значения, идущие подряд. т.е. например если надо исключить 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 шт., откидываая числа рядом стоящими парами. и т.д. запоминаем максимальное среднее и что надо было выкинуть. подтвердите правильно ли я все понял. это интересная задача для формулистов.
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете
|
11.03.2012, 20:01 | #8 | |
Пользователь
Регистрация: 10.03.2012
Сообщений: 14
|
Цитата:
но последняя серия исключений будет не последовательность 246, 247, 248, 249, 250 , а 250, 1, 2, 3, 4. Все это по тому, что первые и последние данные в ячейках как бы "закольцованы". 1. Да, сначала надо найти среднее число из всех 250. Как я понял Код:
Очень важно то, что в идеале надо иметь возможность устанавливать максимально возможное число исключения для конкретной отдельно взятой линии (последовательность от 1 до 250 в столбик). 3. Мне сейчас трудно сказать насколько эффективен способ исключения близлежащих значений. Необходима максимальная точность вычислений. Промежуточные данные возможно будут необходимы для построения технических графиков. Пока это под вопросом,но вероятность все же есть. Спасибо Вам Игорь, за желание помочь, за то, что не бросаете старика в беде. |
|
11.03.2012, 21:56 | #9 |
Форумчанин
Регистрация: 21.11.2010
Сообщений: 326
|
Не то чтобы уж очень интересная...
|
11.03.2012, 22:42 | #10 |
Пользователь
Регистрация: 10.03.2012
Сообщений: 14
|
Спасибо огромное! Сейчас постараюсь разобраться. Если возникнут вопросы, боюсь Вас снова придется потревожить.
ПС Жизнь показывает, что люди все более безразличны. Очень радует, что остаются те, кто может и хочет помочь. Последний раз редактировалось Buchhalter; 11.03.2012 в 22:46. |
|
Похожие темы | ||||
Тема | Автор | Раздел | Ответов | Последнее сообщение |
Дублирование ячеек, диапазона ячеек на новом листе | ukrman | Microsoft Office Excel | 2 | 03.02.2012 01:06 |
Вычисление среднего значения | roxy7 | Microsoft Office Excel | 2 | 13.12.2011 19:41 |
Поиск среднего значения повторяющихся ячеек | Pavel777 | Microsoft Office Excel | 6 | 15.07.2011 20:19 |
Выбор значения из диапазона ячеек и получение ссылки на него | Otando | Microsoft Office Excel | 2 | 12.12.2009 08:44 |
Подсчет среднего значения | stas77 | Microsoft Office Excel | 6 | 27.11.2009 20:29 |