Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel (архив)
Страницы: 1
RSS
Нужна формула расчета среднего знач. с учетом нулей
 
#1
27.10.2008 17:12:20
Расчитываю среднее значение несвязанного диапозона данных, в процессе попадаются нули - обнуления остатков на складе, которые обязательно нужно убирать из формулы, иначе получается грубая ошибка. Вручную - огромные затраты времени и сил, на автоматизацию пока не дали добро, и предстоит долго обходиться Экселем. Пересмотрела все, есть вариант, когда диапозон связанный - массив. Но у меня разбросанный... Мне нужна формула, чтобы считала среднее значение и учитывала нули - убирала их из расчета, например, (2+6+5+1+0+2)/5=3,2 а нужно: (2+6+5+1+2)/4=4. В маленьких числах разница небольшая, а в тысячах денег очень важная.  
Приму любую помощь, любое мнение.  
Спасибо)))
 
 
 
#2
27.10.2008 17:59:42
=AVERAGE(IF(C4:C16<>0,C4:C16))  
 
вводить с ctrl-alt-enter
 
 
 
#3
27.10.2008 18:25:33
нет, не получится, потому что у меня на массив данных(((  
а у функции ЕСЛИ только 7 агрументов. а в году 12 мес.  
я прикрепляю файл, может так будет понятнее.  
или проблема решается как-то по-другому? или я не вижу за деревьями леса?
 
 
 
#4
27.10.2008 19:43:20
С файла стоило начать - не получили бы ненужный ответ.  
 
Смотрим пример. Так?
Bite my shiny metal ass!      
 
 
 
#5
27.10.2008 20:15:54
{quote}{login=Veronika}{date=27.10.2008 05:12}{thema=Нужна формула расчета среднего знач. с учетом нулей}{post}Расчитываю среднее значение,.. например, (2+6+5+1+0+2)/5=3,2 а нужно: (2+6+5+1+2)/4=4.    
 
Спасибо))){/post}{/quote}  
 
1.Если я правильно понимаю среднее, то видимо, так "(2+6+5+1+0+2)/6=2,(6) а нужно: (2+6+5+1+2)/5=3,2"?.  
2. Для уточнения. Вам нужна формула для вписывания в ячейку (типа "=формула") или Вы работаете через макрос VBA?
 
 
 
#6
28.10.2008 10:19:27
=AVERAGE(IF(OFFSET(I5:BE5,-2,0)="Сумма",IF(OFFSET(I5:BE5,-3,-1)="Остатки",I5:BE5)))  
 
см ячейку с5
 
 
 
#7
28.10.2008 10:27:00
чтобы протянуть можно например в столбце IV  
сделать начиная с 5-й строки  
-2  
-3  
-4  
-5  
...  
 
Тогда формула такая:  
=AVERAGE(IF(OFFSET(I5:BE5,$IV5,0)="Сумма",IF(OFFSET(I5:BE5,$IV5-1,-1)="Остатки",I5:BE5)))
 
 
 
#8
29.10.2008 10:16:10
{quote}{login=Лузер™}{date=27.10.2008 07:43}{thema=}{post}С файла стоило начать - не получили бы ненужный ответ.  
 
Смотрим пример. Так?{/post}{/quote}  
 
не могу скачивать эти файлы((
 
 
 
#9
29.10.2008 10:24:00
{quote}{login=Prog}{date=28.10.2008 10:19}{thema=}{post}=AVERAGE(IF(OFFSET(I5:BE5,-2,0)="Сумма",IF(OFFSET(I5:BE5,-3,-1)="Остатки",I5:BE5)))  
 
см ячейку с5{/post}{/quote}  
 
не могу скачать файл(  
если можете, скиньте по почте:  
veronika_fom@mail.ru    
 
а формула не работает - пишет, что ошибка.
 
 
 
#10
29.10.2008 10:32:40
{quote}{login=xls_Green}{date=27.10.2008 08:15}{thema=Re: Нужна формула расчета среднего знач. с учетом нулей.  
1.Если я правильно понимаю среднее, то видимо, так "(2+6+5+1+0+2)/6=2,(6) а нужно: (2+6+5+1+2)/5=3,2"?.  
2. Для уточнения. Вам нужна формула для вписывания в ячейку (типа "=формула") или Вы работаете через макрос VBA?{/post}{/quote}  
 
 
да, тупой пример, согласна(  
 
мне абсолютно все равно, каким может быть решение, формула или макрос, я уже голову себе сломала. если бы это был один файл, то не трудно бы было вручную, но их около 30, и каждый месяц они меняются... у меня понимание, что можно что-то придумать, но VBA я не знаю и вообще Эксель у нас побочная программа, все основное - 1С, и такими расчетами я никогда не занималась.
 
 
 
#11
29.10.2008 10:35:57
{quote}{login=Veronika}{date=29.10.2008 10:24}{thema=Re: }{post}{quote}{login=Prog}{date=28.10.2008 10:19}{thema=}{post}=AVERAGE(IF(OFFSET(I5:BE5,-2,0)="Сумма",IF(OFFSET(I5:BE5,-3,-1)="Остатки",I5:BE5)))  
 
см ячейку с5{/post}{/quote}  
 
не могу скачать файл(  
если можете, скиньте по почте:  
veronika_fom@mail.ru    
 
а формула не работает - пишет, что ошибка.{/post}{/quote}Ждите письмо от ytk5kyky  
:)
Bite my shiny metal ass!      
 
 
 
#12
29.10.2008 10:57:46
большое спасибо, я получила:)))  
формула стра-а-ашная! как для неспециалиста.  
работает))  
 
я правильно понимаю: формула выбирает нужные данные из диапазона?    
теперь новый вопрос: что делать, когда будет добавляться новый месяц?    
добавлять в массив ячейки следующего месяца?
 
 
 
#13
29.10.2008 12:22:00
Работает как любое среднее: сумма отнесенная к количеству.  
как посчитать выборочно сумму или количество по нескольким критериям читаем здесь: Функция СУММПРОИЗВ - секретное оружие Excel  
http://www.planetaexcel.ru/tip.php?aid=94  
По новому месяцу есть три варианта:  
1. Каждый месяц изменять диапазон, затем протягивать формулу - неэффективно и можно ошибится.  
2. Задать сразу диапазон на все 12 месяцев, формула лишнего не посчитает. Возможно замедление работы и увеличение размера файла - тоже не наш метод.  
3. Задать динамические диапазоны, которые сами будут увеличиваться и уменьшаться в зависимости от данных на листе. Про них читаем тут: Использование динамических именованных диапазонов  
http://www.planetaexcel.ru/tip.php?aid=93  
Минус - сперва сложны в освоении, потом становится непонятно как обходился без них.
Bite my shiny metal ass!      
 
 
 
#14
29.10.2008 12:54:42
Я скорее всего выберу второй метод, потому что скоро конец октября, и вообще конец года, а в следующем месяце программисты обещают прописать эти расчеты в виде отчета в 1С.  
Почитала, ничего не поняла, к сожалению - слишком мало работаю в Экселе.  
Но все равно большое спасибо!  
 
*дикая наглость*  
как будет выглядеть формула на 12 месяцев?
 
 
 
#15
29.10.2008 14:03:38
Так у Вас там и есть еще три месяца в скрытых столбцах.    
Сделал и отправил на почту.  
Только надо погасить #ДЕЛ/0 в "Оборач-ть" и "Оборач-ть, дни"
Bite my shiny metal ass!      
 
 
 
#16
29.10.2008 14:59:51
Все, все получилось, красиво работает, красиво копирую в другие книги)))  
еще раз спасибо!
 
 
 
Страницы: 1
Читают тему
Наверх