Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Среднее значение без учета нулей...как записать формулу? - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
Страница 1 из 11
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK 
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Среднее значение без учета нулей...как записать формулу? (Формулы/Formulas)
Среднее значение без учета нулей...как записать формулу?
excel2010 Дата: Воскресенье, 06.03.2016, 12:33 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация: 0 ±
Замечаний: 40% ±

Excel 2010
Ексель 2010 → как вывести среднее значение по нескольким ячейкам, при это чтоб не учитовало нули? (см. пример)

В файле есть лист 1, лист 2, лист 3.
На листе 4 - записано среднее по первым трем листам, но при вычитании среднего учитывает 0, который записан на листе 2.
Функция записана так =СРЗНАЧ ('Лист 1:Лист 3'!A1)

Вот как дописать имеющеюся функцию на листе 4, чтоб не учитывать нули?

Добавлен файл с элементарно простецким примером)
К сообщению приложен файл: 7205712.xlsx(10Kb)
 
Ответить
СообщениеЕксель 2010 → как вывести среднее значение по нескольким ячейкам, при это чтоб не учитовало нули? (см. пример)

В файле есть лист 1, лист 2, лист 3.
На листе 4 - записано среднее по первым трем листам, но при вычитании среднего учитывает 0, который записан на листе 2.
Функция записана так =СРЗНАЧ ('Лист 1:Лист 3'!A1)

Вот как дописать имеющеюся функцию на листе 4, чтоб не учитывать нули?

Добавлен файл с элементарно простецким примером)

Автор - excel2010
Дата добавления - 06.03.2016 в 12:33
Udik Дата: Воскресенье, 06.03.2016, 13:57 | Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 1219
Репутация: 153 ±
Замечаний: 0% ±

Excel 2013
из-за трехмерных ссылок вот такое чудовище
Код
=СУММ('Лист 1:Лист 3'!A1)/СУММПРОИЗВ(--(--Ч(ДВССЫЛ("'Лист "&СТРОКА(ДВССЫЛ(H1&":"&I1))&"'!A1"))>0))


условие поставил - больше ноля, если могут быть отрицательные, на не равно изменить.
К сообщению приложен файл: 0t.xlsx(10Kb)


вот вам барабан
яд 41001231307558 wm R419131876897
udik1968@gmail.com


Сообщение отредактировал Udik - Воскресенье, 06.03.2016, 14:00
 
Ответить
Сообщениеиз-за трехмерных ссылок вот такое чудовище
Код
=СУММ('Лист 1:Лист 3'!A1)/СУММПРОИЗВ(--(--Ч(ДВССЫЛ("'Лист "&СТРОКА(ДВССЫЛ(H1&":"&I1))&"'!A1"))>0))


условие поставил - больше ноля, если могут быть отрицательные, на не равно изменить.

Автор - Udik
Дата добавления - 06.03.2016 в 13:57
excel2010 Дата: Воскресенье, 06.03.2016, 14:55 | Сообщение № 3
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация: 0 ±
Замечаний: 40% ±

Excel 2010
вот такое чудовище

ого) нечего себе) хочу сразу сказать - я ексель немного понимаю, но на начально - среднем уровне, поэтому прошу не гнобить)
ДВССЫЛ я так понимаю возвращает значения. Но если мне нужно будет мою ячейку с формулой протянуть на всю таблицу, мне прийдеца для ДВССЫЛ вводить много каких-то дополнительных ячеек? тут во я не совсем понял.
И можно формулу как то проще прописать:) с использованием например формулы ЕСЛИ?...и спасибо, что помогаете!
 
Ответить
Сообщение
вот такое чудовище

ого) нечего себе) хочу сразу сказать - я ексель немного понимаю, но на начально - среднем уровне, поэтому прошу не гнобить)
ДВССЫЛ я так понимаю возвращает значения. Но если мне нужно будет мою ячейку с формулой протянуть на всю таблицу, мне прийдеца для ДВССЫЛ вводить много каких-то дополнительных ячеек? тут во я не совсем понял.
И можно формулу как то проще прописать:) с использованием например формулы ЕСЛИ?...и спасибо, что помогаете!

Автор - excel2010
Дата добавления - 06.03.2016 в 14:55
Udik Дата: Воскресенье, 06.03.2016, 15:22 | Сообщение № 4
Группа: Друзья
Ранг: Старожил
Сообщений: 1219
Репутация: 153 ±
Замечаний: 0% ±

Excel 2013
если мне нужно будет мою ячейку с формулой протянуть на всю таблицу

просто ссылки замените на абсолютные или смешанные, или вычисляемые (примеры надо сразу нормальные приводить).
с использованием например формулы ЕСЛИ?

можно, если ручками каждый лист вписывать. Я же уже написал всё упирается в трёхмерные ссылки. Ещё можно макросом обработать B) .


вот вам барабан
яд 41001231307558 wm R419131876897
udik1968@gmail.com


Сообщение отредактировал Udik - Воскресенье, 06.03.2016, 15:24
 
Ответить
Сообщение
если мне нужно будет мою ячейку с формулой протянуть на всю таблицу

просто ссылки замените на абсолютные или смешанные, или вычисляемые (примеры надо сразу нормальные приводить).
с использованием например формулы ЕСЛИ?

можно, если ручками каждый лист вписывать. Я же уже написал всё упирается в трёхмерные ссылки. Ещё можно макросом обработать B) .

Автор - Udik
Дата добавления - 06.03.2016 в 15:22
Pelena Дата: Воскресенье, 06.03.2016, 16:24 | Сообщение № 5
Группа: Модераторы
Ранг: Экселист
Сообщений: 9890
Репутация: 2269 ±
Замечаний: 0% ±

Excel 2010 & Mac Excel 2011
excel2010, исходных листов всегда три?
Если да, то может не усложнять
Код
=СУММ('Лист 1:Лист 3'!A1)/(('Лист 1'!A1<>0)+('Лист 2'!A1<>0)+('Лист 3'!A1<>0))

Ещё такая формула массива придумалась
Код
=СУММ('Лист 1:Лист 3'!A1)/СУММ(--(ВЫБОР({1;2;3};'Лист 1'!A1;'Лист 2'!A1;'Лист 3'!A1)<>0))


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
Сообщениеexcel2010, исходных листов всегда три?
Если да, то может не усложнять
Код
=СУММ('Лист 1:Лист 3'!A1)/(('Лист 1'!A1<>0)+('Лист 2'!A1<>0)+('Лист 3'!A1<>0))

Ещё такая формула массива придумалась
Код
=СУММ('Лист 1:Лист 3'!A1)/СУММ(--(ВЫБОР({1;2;3};'Лист 1'!A1;'Лист 2'!A1;'Лист 3'!A1)<>0))

Автор - Pelena
Дата добавления - 06.03.2016 в 16:24
excel2010 Дата: Воскресенье, 06.03.2016, 20:33 | Сообщение № 6
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация: 0 ±
Замечаний: 40% ±

Excel 2010
исходных листов всегда три?

нет не всегда:( это так для примера, а так их больше (ну например штук 15).

У меня два вопроса:
1) не по теме - а как вы реально Udik и Pelena научились ссылки писать? ну правда... учились на это или сами как-то ?:) сколько времени у вас ушло на приличный уровень ексель? поделитесь если можете в двух словах, вот реально интересно!
2) по теме - я вам благодарен за помощь и если чесно с моим уровнем до меня дошла только вот эта формула

=СУММ('Лист 1:Лист 3'!A1)/(('Лист 1'!A1<>0)+('Лист 2'!A1<>0)+('Лист 3'!A1<>0))

с остальными наприяг:( тоесть я знаю что такое например смешанные и абсолютные ссылки, но сами масштабы формул меня пока что, если чесно приводят в смятение...
Если у вам будет время и вас не затруднит и так сказать захочеться тряхонуть стариной - я выложу свой файл, можете глянуть и возможно предложить свои варианты, там впринципи я в нем вам все указал и описал, так чтоб было понятно. Но вобще, я буду стараться сделать сам по вашим ответам и еще раз спасибо за помощь!


Сообщение отредактировал excel2010 - Воскресенье, 06.03.2016, 20:34
 
Ответить
Сообщение
исходных листов всегда три?

нет не всегда:( это так для примера, а так их больше (ну например штук 15).

У меня два вопроса:
1) не по теме - а как вы реально Udik и Pelena научились ссылки писать? ну правда... учились на это или сами как-то ?:) сколько времени у вас ушло на приличный уровень ексель? поделитесь если можете в двух словах, вот реально интересно!
2) по теме - я вам благодарен за помощь и если чесно с моим уровнем до меня дошла только вот эта формула

=СУММ('Лист 1:Лист 3'!A1)/(('Лист 1'!A1<>0)+('Лист 2'!A1<>0)+('Лист 3'!A1<>0))

с остальными наприяг:( тоесть я знаю что такое например смешанные и абсолютные ссылки, но сами масштабы формул меня пока что, если чесно приводят в смятение...
Если у вам будет время и вас не затруднит и так сказать захочеться тряхонуть стариной - я выложу свой файл, можете глянуть и возможно предложить свои варианты, там впринципи я в нем вам все указал и описал, так чтоб было понятно. Но вобще, я буду стараться сделать сам по вашим ответам и еще раз спасибо за помощь!

Автор - excel2010
Дата добавления - 06.03.2016 в 20:33
Udik Дата: Воскресенье, 06.03.2016, 20:43 | Сообщение № 7
Группа: Друзья
Ранг: Старожил
Сообщений: 1219
Репутация: 153 ±
Замечаний: 0% ±

Excel 2013
Если коротко, то каждый раз когда не знал как сделать задачу, начинал искать по инету :) Можно в принципе и с книг начинать, тот же Уокеннбах вполне подробно расписывает.


вот вам барабан
яд 41001231307558 wm R419131876897
udik1968@gmail.com
 
Ответить
СообщениеЕсли коротко, то каждый раз когда не знал как сделать задачу, начинал искать по инету :) Можно в принципе и с книг начинать, тот же Уокеннбах вполне подробно расписывает.

Автор - Udik
Дата добавления - 06.03.2016 в 20:43
excel2010 Дата: Воскресенье, 06.03.2016, 20:52 | Сообщение № 8
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация: 0 ±
Замечаний: 40% ±

Excel 2010
по инету :)

круто, значит в норме мозги и встроенный фильтр, ибо шлака в интернете ну прост оч много. Вот вы пока мне не подсказали, я не мог по сути найти, тоесть если не знаеш формул, выходит и не знаеш в какую точку конкретно быть..ну тобишь что искать, так как описание формул это одно, а приминение на практике воообще другое:)

из-за трехмерных ссылок вот такое чудовище

=СУММ('Лист 1:Лист 3'!A1)/СУММПРОИЗВ(--(--Ч(ДВССЫЛ("'Лист "&СТРОКА(ДВССЫЛ(H1&":"&I1))&"'!A1"))>0))

если не затруднит, как-то подсказать принцип (алгоритм или ее аргументы, хз как правильно) действия этой формулы - тут несколько спарених формул и я понимаю, что СУММ - сумирует, ДВССЫЛ - чет как-то значения возвращеет, но сама ее общая работа немного недопонятна...
Выйдет подсказать или для неуча (меня) это слишком сложно?:)
 
Ответить
Сообщение
по инету :)

круто, значит в норме мозги и встроенный фильтр, ибо шлака в интернете ну прост оч много. Вот вы пока мне не подсказали, я не мог по сути найти, тоесть если не знаеш формул, выходит и не знаеш в какую точку конкретно быть..ну тобишь что искать, так как описание формул это одно, а приминение на практике воообще другое:)

из-за трехмерных ссылок вот такое чудовище

=СУММ('Лист 1:Лист 3'!A1)/СУММПРОИЗВ(--(--Ч(ДВССЫЛ("'Лист "&СТРОКА(ДВССЫЛ(H1&":"&I1))&"'!A1"))>0))

если не затруднит, как-то подсказать принцип (алгоритм или ее аргументы, хз как правильно) действия этой формулы - тут несколько спарених формул и я понимаю, что СУММ - сумирует, ДВССЫЛ - чет как-то значения возвращеет, но сама ее общая работа немного недопонятна...
Выйдет подсказать или для неуча (меня) это слишком сложно?:)

Автор - excel2010
Дата добавления - 06.03.2016 в 20:52
excel2010 Дата: Воскресенье, 06.03.2016, 21:54 | Сообщение № 9
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация: 0 ±
Замечаний: 40% ±

Excel 2010
=СУММ('Лист 1:Лист 3'!A1)/(('Лист 1'!A1<>0)+('Лист 2'!A1<>0)+('Лист 3'!A1<>0))

Вопросик по этой формуле - а если есть пустые ячейки (я так понимаю все ячейки на всех листах), то эта формула выдает ошибку ДЕЛО.
Как можно обойти этот ньюанс? может как-то вставить функцию ЕСЛИОШИБКА или не выйдет дописать?

Может у вас есть варианты? :o

спасибо, уже разобрался. притом сам оО, для меня это успех)


Сообщение отредактировал excel2010 - Воскресенье, 06.03.2016, 22:02
 
Ответить
Сообщение
=СУММ('Лист 1:Лист 3'!A1)/(('Лист 1'!A1<>0)+('Лист 2'!A1<>0)+('Лист 3'!A1<>0))

Вопросик по этой формуле - а если есть пустые ячейки (я так понимаю все ячейки на всех листах), то эта формула выдает ошибку ДЕЛО.
Как можно обойти этот ньюанс? может как-то вставить функцию ЕСЛИОШИБКА или не выйдет дописать?

Может у вас есть варианты? :o

спасибо, уже разобрался. притом сам оО, для меня это успех)

Автор - excel2010
Дата добавления - 06.03.2016 в 21:54
Udik Дата: Воскресенье, 06.03.2016, 22:08 | Сообщение № 10
Группа: Друзья
Ранг: Старожил
Сообщений: 1219
Репутация: 153 ±
Замечаний: 0% ±

Excel 2013
СУММПРОИЗВ(--(--Ч(ДВССЫЛ("'Лист "&СТРОКА(ДВССЫЛ(H1&":"&I1))&"'!A1"))>0))


если упрощённо, то СУММПРОИЗВ(арг>0) считает количество аргументов больше ноля, а всё то что внутри - наворочено с целью получить диапазон трехмерных ссылок. B) .

если есть пустые ячейки (я так понимаю все ячейки на всех листах), то эта формула выдает ошибку ДЕЛО.

если на всех листах пусто, то в знаменателе будет ноль, естественно эксель нецензурно ругается. Можно и ЕСЛИОШИБКА впендюрить, это Вы уж сами определяйте что под ваши задачи подходит.


вот вам барабан
яд 41001231307558 wm R419131876897
udik1968@gmail.com


Сообщение отредактировал Udik - Воскресенье, 06.03.2016, 22:08
 
Ответить
Сообщение
СУММПРОИЗВ(--(--Ч(ДВССЫЛ("'Лист "&СТРОКА(ДВССЫЛ(H1&":"&I1))&"'!A1"))>0))


если упрощённо, то СУММПРОИЗВ(арг>0) считает количество аргументов больше ноля, а всё то что внутри - наворочено с целью получить диапазон трехмерных ссылок. B) .

если есть пустые ячейки (я так понимаю все ячейки на всех листах), то эта формула выдает ошибку ДЕЛО.

если на всех листах пусто, то в знаменателе будет ноль, естественно эксель нецензурно ругается. Можно и ЕСЛИОШИБКА впендюрить, это Вы уж сами определяйте что под ваши задачи подходит.

Автор - Udik
Дата добавления - 06.03.2016 в 22:08
excel2010 Дата: Понедельник, 07.03.2016, 15:15 | Сообщение № 11
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация: 0 ±
Замечаний: 40% ±

Excel 2010
=СУММ('Лист 1:Лист 3'!A1)/(('Лист 1'!A1<>0)+('Лист 2'!A1<>0)+('Лист 3'!A1<>0))

эта формула чет некоректно работает на все ячеки:
Пример в файле (там ячейки выделена огромним размером):
Код
=ЕСЛИОШИБКА(СУММ('Итого по ДП Донбас:ДП Умань'!N22)/(('Итого по ДП Донбас'!N22<>0)+('ДП Запорожье'!N22<>0)+('ДП Умань'!N22<>0));"")

так вот эти ячейки должны давать результат - 0,7
а формула выдает 0,35 - хоть такого просто ну не может быть.
Принцип формули видать местами бито прописан или алгоритм где-то мож хромает.

ФАЙЛ - Удалено. Нарушение Правил форума
[moder]Не нужен весь файл, оставьте проблемные строки, остальное удалите и выкладывайте сюда. А ещё лучше сочините пример, чтобы не выкладывать реальную информацию[/moder]
[moder]И оформляйте формулы тегами (кнопка fx)[/moder]


Сообщение отредактировал Pelena - Понедельник, 07.03.2016, 15:35
 
Ответить
Сообщение
=СУММ('Лист 1:Лист 3'!A1)/(('Лист 1'!A1<>0)+('Лист 2'!A1<>0)+('Лист 3'!A1<>0))

эта формула чет некоректно работает на все ячеки:
Пример в файле (там ячейки выделена огромним размером):
Код
=ЕСЛИОШИБКА(СУММ('Итого по ДП Донбас:ДП Умань'!N22)/(('Итого по ДП Донбас'!N22<>0)+('ДП Запорожье'!N22<>0)+('ДП Умань'!N22<>0));"")

так вот эти ячейки должны давать результат - 0,7
а формула выдает 0,35 - хоть такого просто ну не может быть.
Принцип формули видать местами бито прописан или алгоритм где-то мож хромает.

ФАЙЛ - Удалено. Нарушение Правил форума
[moder]Не нужен весь файл, оставьте проблемные строки, остальное удалите и выкладывайте сюда. А ещё лучше сочините пример, чтобы не выкладывать реальную информацию[/moder]
[moder]И оформляйте формулы тегами (кнопка fx)[/moder]

Автор - excel2010
Дата добавления - 07.03.2016 в 15:15
excel2010 Дата: Понедельник, 07.03.2016, 16:06 | Сообщение № 12
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация: 0 ±
Замечаний: 40% ±

Excel 2010
hands


Сообщение отредактировал excel2010 - Понедельник, 07.03.2016, 16:22
 
Ответить
Сообщениеhands

Автор - excel2010
Дата добавления - 07.03.2016 в 16:06
excel2010 Дата: Понедельник, 07.03.2016, 16:12 | Сообщение № 13
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация: 0 ±
Замечаний: 40% ±

Excel 2010
А ещё лучше сочините пример, чтобы не выкладывать реальную информацию

1) Ну ин-фа как-бы уже неактуальна и там упущено множество информации
2) я бы с радостью оформил, но тут то и ньюанс - что в части моего задания - формула работает, а в части - нет. И вот как раз актуально разобрать почему же одна и та формула, но ведет себя по разному.
 
Ответить
Сообщение
А ещё лучше сочините пример, чтобы не выкладывать реальную информацию

1) Ну ин-фа как-бы уже неактуальна и там упущено множество информации
2) я бы с радостью оформил, но тут то и ньюанс - что в части моего задания - формула работает, а в части - нет. И вот как раз актуально разобрать почему же одна и та формула, но ведет себя по разному.

Автор - excel2010
Дата добавления - 07.03.2016 в 16:12
Pelena Дата: Понедельник, 07.03.2016, 16:18 | Сообщение № 14
Группа: Модераторы
Ранг: Экселист
Сообщений: 9890
Репутация: 2269 ±
Замечаний: 0% ±

Excel 2010 & Mac Excel 2011
Формула сравнивает ячейку с нулем, а Вы на листе Итого по ДП Донбас ставите в неё пустую строку ""
Это не одно и то же.
Замените в формуле "" на 0


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеФормула сравнивает ячейку с нулем, а Вы на листе Итого по ДП Донбас ставите в неё пустую строку ""
Это не одно и то же.
Замените в формуле "" на 0

Автор - Pelena
Дата добавления - 07.03.2016 в 16:18
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Среднее значение без учета нулей...как записать формулу? (Формулы/Formulas)
Страница 1 из 11
Поиск:

Яндекс цитирования
© 2010-2016 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!