Форум программистов
 
Контакты: о проблемах с регистрацией, почтой и по другим вопросам пишите сюда - alarforum@yandex.ru, проверяйте папку спам! Обязательно пройдите активизацию e-mail.

Вернуться   Форум программистов > Microsoft Office и VBA > Microsoft Office Excel
Регистрация

Восстановить пароль
Повторная активизация e-mail


Донат для форума - использовать для поднятия настроения себе и модераторам

А ещё здесь можно купить рекламу за 15 тыс руб в месяц! ) пишите сюда - alarforum@yandex.ru

Ответ
 
Опции темы
Старый 10.03.2012, 21:21   #1
Buchhalter
Пользователь
 
Регистрация: 10.03.2012
Сообщений: 14
Репутация: 10
По умолчанию Создание формулы с вычислением среднего значения диапазона ячеек

Здравствуйте уважаемые программисты.
Помогите седому бухгалтеру разобраться с проблемой.

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

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

Очень прошу помочь. Я уже не молод и мне очень сложно подружится с такими программными монстрами как excel
Buchhalter вне форума   Ответить с цитированием
Старый 10.03.2012, 21:50   #2
MCH
Форумчанин
 
Регистрация: 21.11.2010
Сообщений: 325
Репутация: 152
По умолчанию

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

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

Для исключения 5 наименьших и нахождения среднего:
Код:
=(СУММ(A1:A250)-СУММ(НАИМЕНЬШИЙ(A1:A250;{1:2:3:4:5})))/245
PS: чем больше наименьших чисел исключаем, тем больше будет среднее значение
MCH вне форума   Ответить с цитированием
Старый 10.03.2012, 21:53   #3
MCH
Форумчанин
 
Регистрация: 21.11.2010
Сообщений: 325
Репутация: 152
По умолчанию

А еще можно использовать автофильтр, выбрать 245 наибольших значений и по ним уже считать среднее
MCH вне форума   Ответить с цитированием
Старый 10.03.2012, 22:00   #4
IgorGO
МегаМодератор
СуперМодератор
 
Аватар для IgorGO
 
Регистрация: 05.02.2008
Адрес: УКРАЇНА, Київ
Сообщений: 9,166
Репутация: 1811

icq: 7934250
skype: i2x0,5
По умолчанию

Цитата:
чем больше наименьших чисел исключаем, тем больше будет среднее значение
если исключить все наименьшие, то среднее будет равно максимальному
__________________
41001804815208 - Яндекс-деньги благодарности за удачные советы и решения можно отправлять прямо сюда)
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете
IgorGO на форуме   Ответить с цитированием
Старый 10.03.2012, 22:12   #5
MCH
Форумчанин
 
Регистрация: 21.11.2010
Сообщений: 325
Репутация: 152
По умолчанию

Цитата:
Сообщение от IgorGO Посмотреть сообщение
если исключить все наименьшие, то среднее будет равно максимальному
Ну и я о том же, нужно исключить 249 наименьших значений
MCH вне форума   Ответить с цитированием
Старый 11.03.2012, 19:04   #6
Buchhalter
Пользователь
 
Регистрация: 10.03.2012
Сообщений: 14
Репутация: 10
По умолчанию

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 тысяч таких листов.
Вложения
Тип файла: rar Нужна помощь.rar (19.2 Кб, 17 просмотров)

Последний раз редактировалось Buchhalter; 11.03.2012 в 19:23.
Buchhalter вне форума   Ответить с цитированием
Старый 11.03.2012, 19:39   #7
IgorGO
МегаМодератор
СуперМодератор
 
Аватар для IgorGO
 
Регистрация: 05.02.2008
Адрес: УКРАЇНА, Київ
Сообщений: 9,166
Репутация: 1811

icq: 7934250
skype: i2x0,5
По умолчанию

требуется уточнить условия и терминологию.
насколько я понял:
исключаются не наименьшие, а исключаются значения, идущие подряд.
т.е. например если надо исключить 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 шт., откидываая числа рядом стоящими парами.
и т.д. запоминаем максимальное среднее и что надо было выкинуть.

подтвердите правильно ли я все понял. это интересная задача для формулистов.
__________________
41001804815208 - Яндекс-деньги благодарности за удачные советы и решения можно отправлять прямо сюда)
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете
IgorGO на форуме   Ответить с цитированием
Старый 11.03.2012, 20:01   #8
Buchhalter
Пользователь
 
Регистрация: 10.03.2012
Сообщений: 14
Репутация: 10
По умолчанию

Цитата:
исключаются не наименьшие, а исключаются значения, идущие подряд.
т.е. например если надо исключить 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. Мне сейчас трудно сказать насколько эффективен способ исключения близлежащих значений. Необходима максимальная точность вычислений. Промежуточные данные возможно будут необходимы для построения технических графиков. Пока это под вопросом,но вероятность все же есть.

Спасибо Вам Игорь, за желание помочь, за то, что не бросаете старика в беде.
Buchhalter вне форума   Ответить с цитированием
Старый 11.03.2012, 21:56   #9
MCH
Форумчанин
 
Регистрация: 21.11.2010
Сообщений: 325
Репутация: 152
По умолчанию

Цитата:
Сообщение от IgorGO Посмотреть сообщение
это интересная задача для формулистов.
Не то чтобы уж очень интересная...
Вложения
Тип файла: rar Нужна помощь.rar (12.1 Кб, 30 просмотров)
MCH вне форума   Ответить с цитированием
Старый 11.03.2012, 22:42   #10
Buchhalter
Пользователь
 
Регистрация: 10.03.2012
Сообщений: 14
Репутация: 10
По умолчанию

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

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

Последний раз редактировалось Buchhalter; 11.03.2012 в 22:46.
Buchhalter вне форума   Ответить с цитированием
Ответ

Опции темы

Ваши права в разделе
Вы не можете создавать новые темы
Вы не можете отвечать в темах
Вы не можете прикреплять вложения
Вы не можете редактировать свои сообщения

BB коды Вкл.
Смайлы Вкл.
[IMG] код Вкл.
HTML код Выкл.

Быстрый переход

Похожие темы
Тема Автор Раздел Ответов Последнее сообщение
Дублирование ячеек, диапазона ячеек на новом листе 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 09:44
Подсчет среднего значения stas77 Microsoft Office Excel 6 27.11.2009 21:29


05:57.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.

Проекты отопления, пеллетные котлы, бойлеры, радиаторы
интернет магазин respective.ru