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

Вход

Регистрация

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

 

= Мир MS Excel/Определение среднего значения не смежных ячеек по условию - Мир MS Excel

Старая форма входа
  • Страница 1 из 2
  • 1
  • 2
  • »
Модератор форума: китин, _Boroda_, DrMini  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Определение среднего значения не смежных ячеек по условию (Формулы/Formulas)
Определение среднего значения не смежных ячеек по условию
light26 Дата: Вторник, 23.09.2014, 13:43 | Сообщение № 1
Группа: Друзья
Ранг: Старожил
Сообщений: 1353
Репутация: 91 ±
Замечаний: 0% ±

2007, 2010, 2013
Всем привет, снова понадобилась помощь.
Необходимо определить среднее значение не смежных ячеек с условием если значение больше "0". Грубо говоря, если в указанном диапазоне ячеек есть ячейки с нулевым значением, то эта ячейка не учитывается при расчете.
Эксперименты со
Код
срзначесли
и
Код
срзначеслимн
не принесли успеха.
Пример прилагается.
В рабочем файле нулевые значения могут быть получены формулой, поэтому функция при расчете будет учитывать и ячейку с нулевым значением
К сообщению приложен файл: 7325123.xlsx (9.6 Kb)

Я не волшебник. Я только учусь

Сообщение отредактировал light26 - Вторник, 23.09.2014, 13:46
 
Ответить
СообщениеВсем привет, снова понадобилась помощь.
Необходимо определить среднее значение не смежных ячеек с условием если значение больше "0". Грубо говоря, если в указанном диапазоне ячеек есть ячейки с нулевым значением, то эта ячейка не учитывается при расчете.
Эксперименты со
Код
срзначесли
и
Код
срзначеслимн
не принесли успеха.
Пример прилагается.
В рабочем файле нулевые значения могут быть получены формулой, поэтому функция при расчете будет учитывать и ячейку с нулевым значением
Автор - light26
Дата добавления - 23.09.2014 в 13:43
Nic70y Дата: Вторник, 23.09.2014, 13:47 | Сообщение № 2
Группа: Друзья
Ранг: Экселист
Сообщений: 9126
Репутация: 2415 ±
Замечаний: 0% ±

Excel 2010
Код
=(C2+СУММ(C4:C5)+C7)/(СЧЁТЕСЛИ(C2;">0")+СЧЁТЕСЛИ(C4:C5;">0")+СЧЁТЕСЛИ(C7;">0"))
не смежные как определяются? вручную?

ЮMoney 41001841029809
 
Ответить
Сообщение
Код
=(C2+СУММ(C4:C5)+C7)/(СЧЁТЕСЛИ(C2;">0")+СЧЁТЕСЛИ(C4:C5;">0")+СЧЁТЕСЛИ(C7;">0"))
не смежные как определяются? вручную?
Автор - Nic70y
Дата добавления - 23.09.2014 в 13:47
Rioran Дата: Вторник, 23.09.2014, 13:52 | Сообщение № 3
Группа: Авторы
Ранг: Ветеран
Сообщений: 903
Репутация: 290 ±
Замечаний: 0% ±

Excel 2013
light26, здравствуйте.

Пардон, поспешил.

Роман, Москва, voronov_rv@mail.ru
Яндекс-Деньги: 41001312674279

Сообщение отредактировал Rioran - Вторник, 23.09.2014, 13:56
 
Ответить
Сообщениеlight26, здравствуйте.

Пардон, поспешил.
Автор - Rioran
Дата добавления - 23.09.2014 в 13:52
light26 Дата: Вторник, 23.09.2014, 13:52 | Сообщение № 4
Группа: Друзья
Ранг: Старожил
Сообщений: 1353
Репутация: 91 ±
Замечаний: 0% ±

2007, 2010, 2013
Цитата Nic70y, 23.09.2014 в 13:47, в сообщении № 2
не смежные как определяются? вручную?

Да, типа
Код
=СРЗНАЧ(F5;F70;F73;F94;F106)

Я не волшебник. Я только учусь
 
Ответить
Сообщение
Цитата Nic70y, 23.09.2014 в 13:47, в сообщении № 2
не смежные как определяются? вручную?

Да, типа
Код
=СРЗНАЧ(F5;F70;F73;F94;F106)
Автор - light26
Дата добавления - 23.09.2014 в 13:52
light26 Дата: Вторник, 23.09.2014, 13:54 | Сообщение № 5
Группа: Друзья
Ранг: Старожил
Сообщений: 1353
Репутация: 91 ±
Замечаний: 0% ±

2007, 2010, 2013
Цитата Rioran, 23.09.2014 в 13:52, в сообщении № 3
Попробуйте так, где вместо B2:B7 можете поставить нужные диапазоны через точку с запятой.

Это я пробовал формула ругается на превышение диапазона, что не удивительно

Я не волшебник. Я только учусь

Сообщение отредактировал light26 - Вторник, 23.09.2014, 13:56
 
Ответить
Сообщение
Цитата Rioran, 23.09.2014 в 13:52, в сообщении № 3
Попробуйте так, где вместо B2:B7 можете поставить нужные диапазоны через точку с запятой.

Это я пробовал формула ругается на превышение диапазона, что не удивительно
Автор - light26
Дата добавления - 23.09.2014 в 13:54
Rioran Дата: Вторник, 23.09.2014, 14:02 | Сообщение № 6
Группа: Авторы
Ранг: Ветеран
Сообщений: 903
Репутация: 290 ±
Замечаний: 0% ±

Excel 2013
light26, попробую реабилитироваться =)

Где Rio_Damn - именованый диапазон того, чего хотим смотреть. СЧЁТЗ пропускает ноли.

Код
=СУММ(Rio_Damn)/СЧЁТЗ(Rio_Damn)

Роман, Москва, voronov_rv@mail.ru
Яндекс-Деньги: 41001312674279
 
Ответить
Сообщениеlight26, попробую реабилитироваться =)

Где Rio_Damn - именованый диапазон того, чего хотим смотреть. СЧЁТЗ пропускает ноли.

Код
=СУММ(Rio_Damn)/СЧЁТЗ(Rio_Damn)
Автор - Rioran
Дата добавления - 23.09.2014 в 14:02
light26 Дата: Вторник, 23.09.2014, 15:01 | Сообщение № 7
Группа: Друзья
Ранг: Старожил
Сообщений: 1353
Репутация: 91 ±
Замечаний: 0% ±

2007, 2010, 2013
Rioran, сейчас попробую

Я не волшебник. Я только учусь
 
Ответить
СообщениеRioran, сейчас попробую
Автор - light26
Дата добавления - 23.09.2014 в 15:01
light26 Дата: Вторник, 23.09.2014, 15:06 | Сообщение № 8
Группа: Друзья
Ранг: Старожил
Сообщений: 1353
Репутация: 91 ±
Замечаний: 0% ±

2007, 2010, 2013
Цитата Rioran, 23.09.2014 в 14:02, в сообщении № 6
попробую реабилитироваться =)
не получилось
Цитата Rioran, 23.09.2014 в 14:02, в сообщении № 6
СЧЁТЗ пропускает ноли.
не пропускает он ноли

Я не волшебник. Я только учусь
 
Ответить
Сообщение
Цитата Rioran, 23.09.2014 в 14:02, в сообщении № 6
попробую реабилитироваться =)
не получилось
Цитата Rioran, 23.09.2014 в 14:02, в сообщении № 6
СЧЁТЗ пропускает ноли.
не пропускает он ноли
Автор - light26
Дата добавления - 23.09.2014 в 15:06
light26 Дата: Вторник, 23.09.2014, 15:14 | Сообщение № 9
Группа: Друзья
Ранг: Старожил
Сообщений: 1353
Репутация: 91 ±
Замечаний: 0% ±

2007, 2010, 2013
Цитата Nic70y, 23.09.2014 в 13:47, в сообщении № 2
=(C2+СУММ(C4:C5)+C7)/(СЧЁТЕСЛИ(C2;">0")+СЧЁТЕСЛИ(C4:C5;">0")+СЧЁТЕСЛИ(C7;">0"))
Да, этот вариант работает, но очень большая формула получится для семнадцати-то ячеек )))

Я не волшебник. Я только учусь
 
Ответить
Сообщение
Цитата Nic70y, 23.09.2014 в 13:47, в сообщении № 2
=(C2+СУММ(C4:C5)+C7)/(СЧЁТЕСЛИ(C2;">0")+СЧЁТЕСЛИ(C4:C5;">0")+СЧЁТЕСЛИ(C7;">0"))
Да, этот вариант работает, но очень большая формула получится для семнадцати-то ячеек )))
Автор - light26
Дата добавления - 23.09.2014 в 15:14
_Boroda_ Дата: Вторник, 23.09.2014, 15:26 | Сообщение № 10
Группа: Админы
Ранг: Местный житель
Сообщений: 16881
Репутация: 6593 ±
Замечаний: ±

2003; 2007; 2010; 2013 RUS
Вадим, а вот завтра окажется, что считать нужно не эти ячейки, а другие - что тогда делать будешь?
Предлагаю сделать столбец D с единичками в тех строках, которые нужно просматривать. Тогда будет легко менять и формула простая получается
Код
=СРЗНАЧ(ЕСЛИ(B2:B7*$D2:$D7;B2:B7))
К сообщению приложен файл: 4772315_1.xlsx (9.9 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеВадим, а вот завтра окажется, что считать нужно не эти ячейки, а другие - что тогда делать будешь?
Предлагаю сделать столбец D с единичками в тех строках, которые нужно просматривать. Тогда будет легко менять и формула простая получается
Код
=СРЗНАЧ(ЕСЛИ(B2:B7*$D2:$D7;B2:B7))
Автор - _Boroda_
Дата добавления - 23.09.2014 в 15:26
light26 Дата: Вторник, 23.09.2014, 15:47 | Сообщение № 11
Группа: Друзья
Ранг: Старожил
Сообщений: 1353
Репутация: 91 ±
Замечаний: 0% ±

2007, 2010, 2013
Цитата _Boroda_, 23.09.2014 в 15:26, в сообщении № 10
Предлагаю сделать столбец D с единичками в тех строках, которые нужно просматривать. Тогда будет легко менять и формула простая получается
Привет, Саш. Это не мой файл (тоже попросили помочь), и я предлагал такой вариант, но он не устраивает "заказчика", потому как сама таблица - результат экспорта из другой программы, а ячейки нужно учитывать всегда одни и те же. У меня слету не получилось, инет тоже не дал ответа, вот и пришлось опять тут писать (просто катастрофически не хватает времени чтобы посидеть, подумать)

Я не волшебник. Я только учусь
 
Ответить
Сообщение
Цитата _Boroda_, 23.09.2014 в 15:26, в сообщении № 10
Предлагаю сделать столбец D с единичками в тех строках, которые нужно просматривать. Тогда будет легко менять и формула простая получается
Привет, Саш. Это не мой файл (тоже попросили помочь), и я предлагал такой вариант, но он не устраивает "заказчика", потому как сама таблица - результат экспорта из другой программы, а ячейки нужно учитывать всегда одни и те же. У меня слету не получилось, инет тоже не дал ответа, вот и пришлось опять тут писать (просто катастрофически не хватает времени чтобы посидеть, подумать)
Автор - light26
Дата добавления - 23.09.2014 в 15:47
Pelena Дата: Вторник, 23.09.2014, 15:56 | Сообщение № 12
Группа: Админы
Ранг: Местный житель
Сообщений: 19509
Репутация: 4620 ±
Замечаний: ±

Excel 365 & Mac Excel
Как вариант
Код
=СУММ(B2;B4:B5;B7)/СЧЁТ(1/B2;1/B4;1/B5;1/B7)

или массивная
Код
=СУММ(B2;B4:B5;B7)/СЧЁТ(1/B2;1/B4:B5;1/B7)

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеКак вариант
Код
=СУММ(B2;B4:B5;B7)/СЧЁТ(1/B2;1/B4;1/B5;1/B7)

или массивная
Код
=СУММ(B2;B4:B5;B7)/СЧЁТ(1/B2;1/B4:B5;1/B7)
Автор - Pelena
Дата добавления - 23.09.2014 в 15:56
Rioran Дата: Вторник, 23.09.2014, 16:27 | Сообщение № 13
Группа: Авторы
Ранг: Ветеран
Сообщений: 903
Репутация: 290 ±
Замечаний: 0% ±

Excel 2013
light26, есть возможность ввести дополнительные столбцы? Тогда нужные строки можно промаркировать и СРЗНАЧЕСЛИ сработает на ура.

Роман, Москва, voronov_rv@mail.ru
Яндекс-Деньги: 41001312674279
 
Ответить
Сообщениеlight26, есть возможность ввести дополнительные столбцы? Тогда нужные строки можно промаркировать и СРЗНАЧЕСЛИ сработает на ура.
Автор - Rioran
Дата добавления - 23.09.2014 в 16:27
light26 Дата: Вторник, 23.09.2014, 17:04 | Сообщение № 14
Группа: Друзья
Ранг: Старожил
Сообщений: 1353
Репутация: 91 ±
Замечаний: 0% ±

2007, 2010, 2013
Pelena, а формула не даст ошибку при нулевом значении любой из ячеек? Ведь на 0 делить нельзя

Я не волшебник. Я только учусь
 
Ответить
СообщениеPelena, а формула не даст ошибку при нулевом значении любой из ячеек? Ведь на 0 делить нельзя
Автор - light26
Дата добавления - 23.09.2014 в 17:04
light26 Дата: Вторник, 23.09.2014, 17:08 | Сообщение № 15
Группа: Друзья
Ранг: Старожил
Сообщений: 1353
Репутация: 91 ±
Замечаний: 0% ±

2007, 2010, 2013
Цитата Rioran, 23.09.2014 в 16:27, в сообщении № 13
есть возможность ввести дополнительные столбцы
Возможность есть, резона нет :) (см тут)

Я не волшебник. Я только учусь

Сообщение отредактировал light26 - Вторник, 23.09.2014, 17:08
 
Ответить
Сообщение
Цитата Rioran, 23.09.2014 в 16:27, в сообщении № 13
есть возможность ввести дополнительные столбцы
Возможность есть, резона нет :) (см тут)
Автор - light26
Дата добавления - 23.09.2014 в 17:08
ShAM Дата: Вторник, 23.09.2014, 17:38 | Сообщение № 16
Группа: Друзья
Ранг: Старожил
Сообщений: 1347
Репутация: 249 ±
Замечаний: 0% ±

Excel 2010
Цитата light26, 23.09.2014 в 17:04, в сообщении № 14
а формула не даст ошибку при нулевом значении любой из ячеек? Ведь на 0 делить нельзя

Так и задумано. :) СЧЁТ() ошибки проигнорирует, посчитает только числа.
 
Ответить
Сообщение
Цитата light26, 23.09.2014 в 17:04, в сообщении № 14
а формула не даст ошибку при нулевом значении любой из ячеек? Ведь на 0 делить нельзя

Так и задумано. :) СЧЁТ() ошибки проигнорирует, посчитает только числа.
Автор - ShAM
Дата добавления - 23.09.2014 в 17:38
Pelena Дата: Вторник, 23.09.2014, 17:41 | Сообщение № 17
Группа: Админы
Ранг: Местный житель
Сообщений: 19509
Репутация: 4620 ±
Замечаний: ±

Excel 365 & Mac Excel
Ошибка будет, только если ВСЕ значения в диапазоне равны 0. Если такое возможно, то можно немного усложнить
Код
=ЕСЛИОШИБКА(СУММ(B2;B4:B5;B7)/СЧЁТ(1/B2;1/B4:B5;1/B7);0)

или
Код
=ЕСЛИ(СУММ(B2;B4:B5;B7);СУММ(B2;B4:B5;B7)/СЧЁТ(1/B2;1/B4:B5;1/B7);0)

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеОшибка будет, только если ВСЕ значения в диапазоне равны 0. Если такое возможно, то можно немного усложнить
Код
=ЕСЛИОШИБКА(СУММ(B2;B4:B5;B7)/СЧЁТ(1/B2;1/B4:B5;1/B7);0)

или
Код
=ЕСЛИ(СУММ(B2;B4:B5;B7);СУММ(B2;B4:B5;B7)/СЧЁТ(1/B2;1/B4:B5;1/B7);0)
Автор - Pelena
Дата добавления - 23.09.2014 в 17:41
light26 Дата: Вторник, 23.09.2014, 17:49 | Сообщение № 18
Группа: Друзья
Ранг: Старожил
Сообщений: 1353
Репутация: 91 ±
Замечаний: 0% ±

2007, 2010, 2013
Цитата Pelena, 23.09.2014 в 17:41, в сообщении № 17
Ошибка будет, только если ВСЕ значения в диапазоне равны 0.

Цитата ShAM, 23.09.2014 в 17:38, в сообщении № 16
Так и задумано. СЧЁТ() ошибки проигнорирует, посчитает только числа.
Ох уж этот загадочный Excel :)

Я не волшебник. Я только учусь
 
Ответить
Сообщение
Цитата Pelena, 23.09.2014 в 17:41, в сообщении № 17
Ошибка будет, только если ВСЕ значения в диапазоне равны 0.

Цитата ShAM, 23.09.2014 в 17:38, в сообщении № 16
Так и задумано. СЧЁТ() ошибки проигнорирует, посчитает только числа.
Ох уж этот загадочный Excel :)
Автор - light26
Дата добавления - 23.09.2014 в 17:49
light26 Дата: Вторник, 23.09.2014, 18:02 | Сообщение № 19
Группа: Друзья
Ранг: Старожил
Сообщений: 1353
Репутация: 91 ±
Замечаний: 0% ±

2007, 2010, 2013
Pelena, эта формула работает не корректно (все значения меньше 100, а среднее получилось больше 100). Щас, пока минута свободная появилась, попробую сам.

Вот и все, уже не попробую... Все верно, это я ошибся

Я не волшебник. Я только учусь

Сообщение отредактировал light26 - Вторник, 23.09.2014, 18:12
 
Ответить
СообщениеPelena, эта формула работает не корректно (все значения меньше 100, а среднее получилось больше 100). Щас, пока минута свободная появилась, попробую сам.

Вот и все, уже не попробую... Все верно, это я ошибся
Автор - light26
Дата добавления - 23.09.2014 в 18:02
Rioran Дата: Вторник, 23.09.2014, 18:08 | Сообщение № 20
Группа: Авторы
Ранг: Ветеран
Сообщений: 903
Репутация: 290 ±
Замечаний: 0% ±

Excel 2013
light26, подойдёт ли решение UDF?

Роман, Москва, voronov_rv@mail.ru
Яндекс-Деньги: 41001312674279
 
Ответить
Сообщениеlight26, подойдёт ли решение UDF?
Автор - Rioran
Дата добавления - 23.09.2014 в 18:08
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Определение среднего значения не смежных ячеек по условию (Формулы/Formulas)
  • Страница 1 из 2
  • 1
  • 2
  • »
Поиск:

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