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

Вход

Регистрация

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

 

= Мир MS Excel/СРЗНАЧЕСЛИ с диапазоном усреднения для столбцов >=2? - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » СРЗНАЧЕСЛИ с диапазоном усреднения для столбцов >=2? (Формулы/Formulas)
СРЗНАЧЕСЛИ с диапазоном усреднения для столбцов >=2?
mrdc Дата: Вторник, 28.10.2014, 10:20 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 13
Репутация: 0 ±
Замечаний: 40% ±

Excel 2010
Привет

Удивил меня вчера excel: СРЗНАЧЕСЛИ не работает для 2-х и более столбцов, т.е. если указываем =СРЗНАЧЕСЛИ(A1:A100;"ААА";B1:C100), то вычисляет среднее только для B1:B100.
Пробовал обойти через суммесли и счётесли, но из-за особенностей таблицы не получилось, пока пытаюсь с СРЗНАЧЕСЛИ.
Как победить?
К сообщению приложен файл: 6759667.xlsx (9.6 Kb)


Сообщение отредактировал mrdc - Вторник, 28.10.2014, 20:00
 
Ответить
СообщениеПривет

Удивил меня вчера excel: СРЗНАЧЕСЛИ не работает для 2-х и более столбцов, т.е. если указываем =СРЗНАЧЕСЛИ(A1:A100;"ААА";B1:C100), то вычисляет среднее только для B1:B100.
Пробовал обойти через суммесли и счётесли, но из-за особенностей таблицы не получилось, пока пытаюсь с СРЗНАЧЕСЛИ.
Как победить?
Автор - mrdc
Дата добавления - 28.10.2014 в 10:20
jakim Дата: Вторник, 28.10.2014, 11:44 | Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 1202
Репутация: 313 ±
Замечаний: 0% ±

Excel 2010
Код
=SUMPRODUCT((A2:A35=E2)*B2:C35)/(SUMPRODUCT(--(A2:A35=E2))*2)
 
Ответить
Сообщение
Код
=SUMPRODUCT((A2:A35=E2)*B2:C35)/(SUMPRODUCT(--(A2:A35=E2))*2)
Автор - jakim
Дата добавления - 28.10.2014 в 11:44
mrdc Дата: Вторник, 28.10.2014, 14:45 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 13
Репутация: 0 ±
Замечаний: 40% ±

Excel 2010
Спасибо, попробовал, но не получилось: если в одном из столбцов в ячейке нет значения, то результат некорректный. :(


Сообщение отредактировал Serge_007 - Вторник, 28.10.2014, 17:57
 
Ответить
СообщениеСпасибо, попробовал, но не получилось: если в одном из столбцов в ячейке нет значения, то результат некорректный. :(
Автор - mrdc
Дата добавления - 28.10.2014 в 14:45
Russel Дата: Вторник, 28.10.2014, 15:28 | Сообщение № 4
Группа: Друзья
Ранг: Старожил
Сообщений: 1394
Репутация: 320 ±
Замечаний: 0% ±

Excel 2010
Код
=СУММПРОИЗВ((A1:A34="ААА")*(B1:B34+C1:C34))/СУММПРОИЗВ((A1:A34="ААА")*((B1:B34<>"")+(C1:C34<>"")))

QIWI 9173973973
 
Ответить
Сообщение
Код
=СУММПРОИЗВ((A1:A34="ААА")*(B1:B34+C1:C34))/СУММПРОИЗВ((A1:A34="ААА")*((B1:B34<>"")+(C1:C34<>"")))
Автор - Russel
Дата добавления - 28.10.2014 в 15:28
mrdc Дата: Вторник, 28.10.2014, 16:58 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 13
Репутация: 0 ±
Замечаний: 40% ±

Excel 2010
Огромное спасибо! Отправил + в репутацию. hands


Сообщение отредактировал Serge_007 - Вторник, 28.10.2014, 17:56
 
Ответить
СообщениеОгромное спасибо! Отправил + в репутацию. hands
Автор - mrdc
Дата добавления - 28.10.2014 в 16:58
mrdc Дата: Вторник, 28.10.2014, 19:58 | Сообщение № 6
Группа: Пользователи
Ранг: Новичок
Сообщений: 13
Репутация: 0 ±
Замечаний: 40% ±

Excel 2010
По факту такой "монстр" корректно на моих данных не заработал:
Код
=(СУММПРОИЗВ(($A$2:ДВССЫЛ(АДРЕС(S$1;1))=S$3)*($B$2:ДВССЫЛ(АДРЕС(S$1;2))+$C$2:ДВССЫЛ(АДРЕС(S$1;3)))))/(СУММПРОИЗВ(($A$2:ДВССЫЛ(АДРЕС(S$1;1))=S$3)*($B$2:ДВССЫЛ(АДРЕС(S$1;2))<>"")+($C$2:ДВССЫЛ(АДРЕС(S$1;3))<>"")))

Мой простой вариант работает:
Код
=(СУММЕСЛИМН($B:$B;$A:$A;S$3)+СУММЕСЛИМН($C:$C;$A:$A;S$3))/(СЧЁТЕСЛИ($A:$A;S$3)*2-(СЧЁТЕСЛИМН($A:$A;S$3;$B:$B;"")+СЧЁТЕСЛИМН($A:$A;S$3;$C:$C;"")))

[moder]Еще раз сотрете сообщение модератора - заблокирую полностью.
И ЕЩЕ раз (надеюсь, больше мне этого делать не придется) напоминаю, что для того, чтобы "монстры" заработали, нужно просто приложить файл - тогда мы сможем Вам посоветовать еще какой-то вариант


Сообщение отредактировал mrdc - Вторник, 28.10.2014, 19:59
 
Ответить
СообщениеПо факту такой "монстр" корректно на моих данных не заработал:
Код
=(СУММПРОИЗВ(($A$2:ДВССЫЛ(АДРЕС(S$1;1))=S$3)*($B$2:ДВССЫЛ(АДРЕС(S$1;2))+$C$2:ДВССЫЛ(АДРЕС(S$1;3)))))/(СУММПРОИЗВ(($A$2:ДВССЫЛ(АДРЕС(S$1;1))=S$3)*($B$2:ДВССЫЛ(АДРЕС(S$1;2))<>"")+($C$2:ДВССЫЛ(АДРЕС(S$1;3))<>"")))

Мой простой вариант работает:
Код
=(СУММЕСЛИМН($B:$B;$A:$A;S$3)+СУММЕСЛИМН($C:$C;$A:$A;S$3))/(СЧЁТЕСЛИ($A:$A;S$3)*2-(СЧЁТЕСЛИМН($A:$A;S$3;$B:$B;"")+СЧЁТЕСЛИМН($A:$A;S$3;$C:$C;"")))

[moder]Еще раз сотрете сообщение модератора - заблокирую полностью.
И ЕЩЕ раз (надеюсь, больше мне этого делать не придется) напоминаю, что для того, чтобы "монстры" заработали, нужно просто приложить файл - тогда мы сможем Вам посоветовать еще какой-то вариант
Автор - mrdc
Дата добавления - 28.10.2014 в 19:58
AlexM Дата: Среда, 29.10.2014, 08:56 | Сообщение № 7
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4511
Репутация: 1128 ±
Замечаний: 0% ±

Excel 2003
массивная формула
Код
=СРЗНАЧ(ЕСЛИ(A1:A100="ААА";B1:C100))
или так лучше
Код
=СРЗНАЧ(ЕСЛИ((A1:A100="ААА")*(B1:C100<>0);B1:C100))


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.

Сообщение отредактировал AlexM - Среда, 29.10.2014, 09:05
 
Ответить
Сообщениемассивная формула
Код
=СРЗНАЧ(ЕСЛИ(A1:A100="ААА";B1:C100))
или так лучше
Код
=СРЗНАЧ(ЕСЛИ((A1:A100="ААА")*(B1:C100<>0);B1:C100))
Автор - AlexM
Дата добавления - 29.10.2014 в 08:56
jakim Дата: Среда, 29.10.2014, 11:06 | Сообщение № 8
Группа: Друзья
Ранг: Старожил
Сообщений: 1202
Репутация: 313 ±
Замечаний: 0% ±

Excel 2010
Ещё один вариант.

Код
=AGGREGATE(1;6;B1:C34)

или

=SUM(B1:C34)/COUNT(B1:C34)
Извините за ошибочный ответ, просто забыл, среднее должно быть определено с условием, поэтому предлагаю ещё один ввариан

Код
=SUMPRODUCT((A1:A34="ААА")*B1:C34)/SUMPRODUCT((A1:A34="ААА")*(B1:C34<>""))


Сообщение отредактировал jakim - Среда, 29.10.2014, 12:35
 
Ответить
Сообщение
Ещё один вариант.

Код
=AGGREGATE(1;6;B1:C34)

или

=SUM(B1:C34)/COUNT(B1:C34)
Извините за ошибочный ответ, просто забыл, среднее должно быть определено с условием, поэтому предлагаю ещё один ввариан

Код
=SUMPRODUCT((A1:A34="ААА")*B1:C34)/SUMPRODUCT((A1:A34="ААА")*(B1:C34<>""))
Автор - jakim
Дата добавления - 29.10.2014 в 11:06
Мир MS Excel » Вопросы и решения » Вопросы по Excel » СРЗНАЧЕСЛИ с диапазоном усреднения для столбцов >=2? (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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