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

Вход

Регистрация

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

 

= Мир MS Excel/Среднее значение без учета ячеек с нулем - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Среднее значение без учета ячеек с нулем (Формулы/Formulas)
Среднее значение без учета ячеек с нулем
AleksFin Дата: Среда, 07.06.2017, 11:27 | Сообщение № 1
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 117
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Здравствуйте!

Собираю отчет, столкнулся с такой задачкой, когда нужно чтобы среднее значение в ячейке выводилось без учета ячеек с нулями.

Если есть 3 ячейки и в 2-х из них стоят нули, то эксель делит на 3, а нужно чтобы не делил на 3, а делил только на заполненные больше нуля цифры.

Пример не прилагаю, думаю он не нужен. Если нужен, приложу.
 
Ответить
СообщениеЗдравствуйте!

Собираю отчет, столкнулся с такой задачкой, когда нужно чтобы среднее значение в ячейке выводилось без учета ячеек с нулями.

Если есть 3 ячейки и в 2-х из них стоят нули, то эксель делит на 3, а нужно чтобы не делил на 3, а делил только на заполненные больше нуля цифры.

Пример не прилагаю, думаю он не нужен. Если нужен, приложу.
Автор - AleksFin
Дата добавления - 07.06.2017 в 11:27
Pelena Дата: Среда, 07.06.2017, 11:31 | Сообщение № 2
Группа: Админы
Ранг: Местный житель
Сообщений: 19399
Репутация: 4549 ±
Замечаний: ±

Excel 365 & Mac Excel
Здравствуйте.
Функция СРЗНАЧЕСЛИ() должна помочь

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеЗдравствуйте.
Функция СРЗНАЧЕСЛИ() должна помочь
Автор - Pelena
Дата добавления - 07.06.2017 в 11:31
_Boroda_ Дата: Среда, 07.06.2017, 11:31 | Сообщение № 3
Группа: Админы
Ранг: Местный житель
Сообщений: 16699
Репутация: 6495 ±
Замечаний: ±

2003; 2007; 2010; 2013 RUS
СРЗНАЧЕСЛИ
СУММ/СЧЁТ

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеСРЗНАЧЕСЛИ
СУММ/СЧЁТ
Автор - _Boroda_
Дата добавления - 07.06.2017 в 11:31
amfor Дата: Среда, 07.06.2017, 11:32 | Сообщение № 4
Группа: Проверенные
Ранг: Обитатель
Сообщений: 250
Репутация: 75 ±
Замечаний: 0% ±

Excel 2016
Здравствуйте.
= СРЗНАЧЕСЛИ

Семь бед - один RESET.
 
Ответить
СообщениеЗдравствуйте.
= СРЗНАЧЕСЛИ
Автор - amfor
Дата добавления - 07.06.2017 в 11:32
AleksFin Дата: Среда, 07.06.2017, 11:39 | Сообщение № 5
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 117
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Спасибо за ответы, наверно прикладываю файл, возможно я не правильно вопрос построил, посмотрите пжл файл.
К сообщению приложен файл: 333.xlsx (8.4 Kb)
 
Ответить
СообщениеСпасибо за ответы, наверно прикладываю файл, возможно я не правильно вопрос построил, посмотрите пжл файл.
Автор - AleksFin
Дата добавления - 07.06.2017 в 11:39
_Boroda_ Дата: Среда, 07.06.2017, 11:43 | Сообщение № 6
Группа: Админы
Ранг: Местный житель
Сообщений: 16699
Репутация: 6495 ±
Замечаний: ±

2003; 2007; 2010; 2013 RUS
Код
=СРЗНАЧЕСЛИ(B11:B13;"<>0")

Или формула массива
Код
=СУММ(B11:B13)/СЧЁТ(1/B11:B13)
К сообщению приложен файл: 333_1.xlsx (8.7 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение
Код
=СРЗНАЧЕСЛИ(B11:B13;"<>0")

Или формула массива
Код
=СУММ(B11:B13)/СЧЁТ(1/B11:B13)
Автор - _Boroda_
Дата добавления - 07.06.2017 в 11:43
buchlotnik Дата: Среда, 07.06.2017, 11:45 | Сообщение № 7
Группа: Заблокированные
Ранг: Участник клуба
Сообщений: 3442
Репутация: 929 ±
Замечаний: 20% ±

2010, 2013, 2016 RUS / ENG
Код
=СРЗНАЧЕСЛИ(B11:B13;">0")
К сообщению приложен файл: 333-1-.xlsx (8.9 Kb)
 
Ответить
Сообщение
Код
=СРЗНАЧЕСЛИ(B11:B13;">0")
Автор - buchlotnik
Дата добавления - 07.06.2017 в 11:45
and_evg Дата: Среда, 07.06.2017, 11:47 | Сообщение № 8
Группа: Проверенные
Ранг: Обитатель
Сообщений: 460
Репутация: 77 ±
Замечаний: 0% ±

Excel 2007
AleksFin, так вам ведь говорили:
Код
= СРЗНАЧЕСЛИ(B11:B13;"<>0";B11:B13)


Сообщение отредактировал and_evg - Среда, 07.06.2017, 11:48
 
Ответить
СообщениеAleksFin, так вам ведь говорили:
Код
= СРЗНАЧЕСЛИ(B11:B13;"<>0";B11:B13)
Автор - and_evg
Дата добавления - 07.06.2017 в 11:47
AleksFin Дата: Среда, 07.06.2017, 12:20 | Сообщение № 9
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 117
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Спасибо за ответы.

В оригинальную форму когда вставляю, выдает "слишком много аргументов".

Зажимаю ctrl и подбираю по одному нужные мне ячейки, это можно обойти как-нибудь?
 
Ответить
СообщениеСпасибо за ответы.

В оригинальную форму когда вставляю, выдает "слишком много аргументов".

Зажимаю ctrl и подбираю по одному нужные мне ячейки, это можно обойти как-нибудь?
Автор - AleksFin
Дата добавления - 07.06.2017 в 12:20
_Boroda_ Дата: Среда, 07.06.2017, 12:31 | Сообщение № 10
Группа: Админы
Ранг: Местный житель
Сообщений: 16699
Репутация: 6495 ±
Замечаний: ±

2003; 2007; 2010; 2013 RUS
Можно. Для этого нужно показать пример оригинальной формы

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеМожно. Для этого нужно показать пример оригинальной формы
Автор - _Boroda_
Дата добавления - 07.06.2017 в 12:31
AleksFin Дата: Среда, 07.06.2017, 12:35 | Сообщение № 11
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 117
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Ок
К сообщению приложен файл: 0222546.xlsx (8.6 Kb)
 
Ответить
СообщениеОк
Автор - AleksFin
Дата добавления - 07.06.2017 в 12:35
_Boroda_ Дата: Среда, 07.06.2017, 12:46 | Сообщение № 12
Группа: Админы
Ранг: Местный житель
Сообщений: 16699
Репутация: 6495 ±
Замечаний: ±

2003; 2007; 2010; 2013 RUS
Не, не так. Нужно понимать, почему Вы выбрали именно ячейки из 11,13,13,17 и 20 строк. Не нужно придумывать примеры, просто отрубите кусок своего файла, уберите конф. информацию, положите сюда и объясните как-то примерно так: "Если в столбце А находятся значения ХХХ, то по таким строкам нужно вычислить среднее значение по столбцу В, но не учитывая нулевые значения"
Предположу, что Вам нужно что-то примерно вот такое
Код
=СРЗНАЧЕСЛИМН(B11:B21;B11:B21;"<>0";A11:A21;"ХХХ")

Или, если нужно для любого заполнения столбца А, то
Код
=СРЗНАЧЕСЛИМН(B11:B21;B11:B21;"<>0";A11:A21;"*")

=========
А вот это
Код
=СРЗНАЧЕСЛИ(B11;B13;B15;B17;B20;">0")
работать не будет. Почитайте справку по функции СРЗНАЧЕСЛИ и все сами поймете

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеНе, не так. Нужно понимать, почему Вы выбрали именно ячейки из 11,13,13,17 и 20 строк. Не нужно придумывать примеры, просто отрубите кусок своего файла, уберите конф. информацию, положите сюда и объясните как-то примерно так: "Если в столбце А находятся значения ХХХ, то по таким строкам нужно вычислить среднее значение по столбцу В, но не учитывая нулевые значения"
Предположу, что Вам нужно что-то примерно вот такое
Код
=СРЗНАЧЕСЛИМН(B11:B21;B11:B21;"<>0";A11:A21;"ХХХ")

Или, если нужно для любого заполнения столбца А, то
Код
=СРЗНАЧЕСЛИМН(B11:B21;B11:B21;"<>0";A11:A21;"*")

=========
А вот это
Код
=СРЗНАЧЕСЛИ(B11;B13;B15;B17;B20;">0")
работать не будет. Почитайте справку по функции СРЗНАЧЕСЛИ и все сами поймете
Автор - _Boroda_
Дата добавления - 07.06.2017 в 12:46
Sancho Дата: Среда, 07.06.2017, 12:58 | Сообщение № 13
Группа: Проверенные
Ранг: Обитатель
Сообщений: 279
Репутация: 19 ±
Замечаний: 0% ±

2007, 2010, 2013
AleksFin, не понятно, из несвязанного диапазона надо выдернуть среднее значение среди ненулевых значений? тогда
Код
=СУММ(B11;B13;B15;B17;B20)/СУММПРОИЗВ((B11>0)+(B13>0)+(B15>0)+(B17>0)+(B20>0))
 
Ответить
СообщениеAleksFin, не понятно, из несвязанного диапазона надо выдернуть среднее значение среди ненулевых значений? тогда
Код
=СУММ(B11;B13;B15;B17;B20)/СУММПРОИЗВ((B11>0)+(B13>0)+(B15>0)+(B17>0)+(B20>0))
Автор - Sancho
Дата добавления - 07.06.2017 в 12:58
AleksFin Дата: Среда, 07.06.2017, 13:13 | Сообщение № 14
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 117
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Выкладываю файлик, посмотрите пжл. Описание внутри.
К сообщению приложен файл: 1103380.xlsx (10.3 Kb)
 
Ответить
СообщениеВыкладываю файлик, посмотрите пжл. Описание внутри.
Автор - AleksFin
Дата добавления - 07.06.2017 в 13:13
_Boroda_ Дата: Среда, 07.06.2017, 13:18 | Сообщение № 15
Группа: Админы
Ранг: Местный житель
Сообщений: 16699
Репутация: 6495 ±
Замечаний: ±

2003; 2007; 2010; 2013 RUS
Так?
Код
=СРЗНАЧЕСЛИМН(Данные!F9:F18;Данные!F9:F18;"<>0";Данные!D9:D18;B8&"*")

На случай, если все нули
Код
=ЕСЛИОШИБКА(СРЗНАЧЕСЛИМН(Данные!F9:F18;Данные!F9:F18;"<>0";Данные!D9:D18;B8&"*");)

=
Вот теперь файл хороший и правильный.
В следующий раз тоже примерно так и делайте, тогда решение сразу получите, а не через 2 часа, как сейчас
=
И да, для цены <> не обязательно, достаточно просто >, как у Михаила
К сообщению приложен файл: 1103380_1.xlsx (10.6 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеТак?
Код
=СРЗНАЧЕСЛИМН(Данные!F9:F18;Данные!F9:F18;"<>0";Данные!D9:D18;B8&"*")

На случай, если все нули
Код
=ЕСЛИОШИБКА(СРЗНАЧЕСЛИМН(Данные!F9:F18;Данные!F9:F18;"<>0";Данные!D9:D18;B8&"*");)

=
Вот теперь файл хороший и правильный.
В следующий раз тоже примерно так и делайте, тогда решение сразу получите, а не через 2 часа, как сейчас
=
И да, для цены <> не обязательно, достаточно просто >, как у Михаила
Автор - _Boroda_
Дата добавления - 07.06.2017 в 13:18
buchlotnik Дата: Среда, 07.06.2017, 13:20 | Сообщение № 16
Группа: Заблокированные
Ранг: Участник клуба
Сообщений: 3442
Репутация: 929 ±
Замечаний: 20% ±

2010, 2013, 2016 RUS / ENG
как-то так, наверное
Код
=СРЗНАЧЕСЛИМН(Данные!F9:F18;Данные!D9:D18;B8&"*";Данные!F9:F18;">0")
К сообщению приложен файл: 1168525.xlsx (10.7 Kb)
 
Ответить
Сообщениекак-то так, наверное
Код
=СРЗНАЧЕСЛИМН(Данные!F9:F18;Данные!D9:D18;B8&"*";Данные!F9:F18;">0")
Автор - buchlotnik
Дата добавления - 07.06.2017 в 13:20
Sancho Дата: Среда, 07.06.2017, 13:40 | Сообщение № 17
Группа: Проверенные
Ранг: Обитатель
Сообщений: 279
Репутация: 19 ±
Замечаний: 0% ±

2007, 2010, 2013
AleksFin, то ли пример неудачный, то ли я чего то не понимаю? как может быть цена на изделие рассчитываться как среднее значение. в вашем варианте да, изделие из одной детали, а если из двух? одна деталь 15 другая 1, Средняя цена 8? че за бред какой то? если сравнить односоставное изделие со средней ценой 15 и двухсоставное изделие с ценой 8... в чем "соль"? какой вывод из этого следует?


Сообщение отредактировал Sancho - Среда, 07.06.2017, 13:43
 
Ответить
СообщениеAleksFin, то ли пример неудачный, то ли я чего то не понимаю? как может быть цена на изделие рассчитываться как среднее значение. в вашем варианте да, изделие из одной детали, а если из двух? одна деталь 15 другая 1, Средняя цена 8? че за бред какой то? если сравнить односоставное изделие со средней ценой 15 и двухсоставное изделие с ценой 8... в чем "соль"? какой вывод из этого следует?
Автор - Sancho
Дата добавления - 07.06.2017 в 13:40
AleksFin Дата: Среда, 07.06.2017, 14:18 | Сообщение № 18
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 117
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Всем спасибо, очередной вопрос как всегда без сомнений решен. hands
 
Ответить
СообщениеВсем спасибо, очередной вопрос как всегда без сомнений решен. hands
Автор - AleksFin
Дата добавления - 07.06.2017 в 14:18
AleksFin Дата: Среда, 07.06.2017, 14:39 | Сообщение № 19
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 117
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Ребята, извините, я не полностью задачу обрисовал. :'(

Гляньте во вложении, я вторую строку добавил
К сообщению приложен файл: 7091850.xlsx (10.8 Kb)
 
Ответить
СообщениеРебята, извините, я не полностью задачу обрисовал. :'(

Гляньте во вложении, я вторую строку добавил
Автор - AleksFin
Дата добавления - 07.06.2017 в 14:39
Sancho Дата: Четверг, 08.06.2017, 12:55 | Сообщение № 20
Группа: Проверенные
Ранг: Обитатель
Сообщений: 279
Репутация: 19 ±
Замечаний: 0% ±

2007, 2010, 2013
AleksFin, вы ищете машину, которой на листе "данные" нет.
 
Ответить
СообщениеAleksFin, вы ищете машину, которой на листе "данные" нет.
Автор - Sancho
Дата добавления - 08.06.2017 в 12:55
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Среднее значение без учета ячеек с нулем (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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