Среднее значение для чисел кроме нулевых, в разных ячейках

Дата: Пятница, 20.03.2015, 14:15 | Сообщение № 1

Группа: Пользователи

Ранг: Прохожий

Сообщений: 7

Репутация: 0 ±

Замечаний: 0% ±

Excel 2007

Всем доброго времени суток !

Собственно такой вопрос: Необходимо подсчитать среднее значение ячеек в ряду кроме нулевых. Вся трудность заключается в том что ячейки стоят не по порядку и диапазон использовать не представляется возможным. Использую формулу AVERAGEIF. Попробовал создать массив через «Формулы» -> раздел «Определенные имена» -> кнопка «Диспетчер имен», все равно формула пишет ошибку.

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

Подскажите как можно решить мою задачу ?

К сообщению приложен файл: 1111.xlsx (8.3 Kb)

Сообщение отредактировал Verniy55 - Пятница, 20.03.2015, 14:21

Дата: Пятница, 20.03.2015, 14:19 | Сообщение № 2

Группа: Админы

Ранг: Местный житель

Сообщений: 19511

Репутация: 4620 ±

Замечаний: ±

Excel 365 & Mac Excel

Не приложился файл

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816

Дата: Пятница, 20.03.2015, 14:22 | Сообщение № 3

Группа: Пользователи

Ранг: Прохожий

Сообщений: 7

Репутация: 0 ±

Замечаний: 0% ±

Excel 2007

Пардон, приложил.

_Boroda_

Дата: Пятница, 20.03.2015, 14:33 | Сообщение № 4

Группа: Админы

Ранг: Местный житель

Сообщений: 16885

Репутация: 6599 ±

Замечаний: ±

2003; 2007; 2010; 2013 RUS

Формула массива. Вводится одновременным нажатием Контрл Шифт Ентер

Код

=СРЗНАЧ(ЕСЛИ(ЕЧЁТН(СТОЛБЕЦ(B2:P2));B2:P2))

Не заметил, что "Кроме нулевых". Ща поправлю
Вот так

Код

=СРЗНАЧ(ЕСЛИ(ЕЧЁТН(СТОЛБЕЦ(B2:P2))*B2:P2;B2:P2))

К сообщению приложен файл: 1111757575_2.xlsx (9.1 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995

VEKTORVSFREEMAN

Дата: Пятница, 20.03.2015, 15:03 | Сообщение № 5

Группа: Друзья

Ранг: Ветеран

Сообщений: 772

Репутация: 130 ±

Замечаний: 0% ±

MS Office Excel 2010

Verniy55, здравствуйте!
вдруг можно куда-нибудь влепить две доп.строки, но ежели посчитать две строки, то добавлять придётся 3 доп., если 3 то 4 доп.
вряд ли устроит, но тем не менее (см. файл) <_<

Код

=СРЗНАЧЕСЛИМН(B3:P3;B2:P2;"м";B1:P1;">"&0)

К сообщению приложен файл: 3414373.xlsx (9.5 Kb)

"Опыт - это то, что получаешь, не получив того, что хотел"

Сообщение отредактировал VEKTORVSFREEMAN - Пятница, 20.03.2015, 15:43

Дата: Пятница, 20.03.2015, 15:13 | Сообщение № 6

Группа: Пользователи

Ранг: Прохожий

Сообщений: 7

Репутация: 0 ±

Замечаний: 0% ±

Excel 2007

Спасибо за помощь, в оригинальном файле стоят следующие ячейки (H11;U11;AH11;AU11;BH11;BU11;CH11;CV11;DI11;DV11;EI11;EV11). Как проделать ту же формулу только с ними ?

И подскажите какую формулу можно применить в случае если ячейки стоят в рандомном порядке как в файлике ?

К сообщению приложен файл: 2222.xlsx (8.3 Kb)

Дата: Пятница, 20.03.2015, 15:29 | Сообщение № 7

Группа: Админы

Ранг: Местный житель

Сообщений: 19511

Репутация: 4620 ±

Замечаний: ±

Excel 365 & Mac Excel

Verniy55, а шапка у таблицы есть? Может, к ней привязаться можно?

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816

krosav4ig

Дата: Пятница, 20.03.2015, 15:45 | Сообщение № 8

Группа: Друзья

Ранг: Старожил

Сообщений: 2347

Репутация: 997 ±

Замечаний: 0% ±

Excel 2007,2010,2013

как вариант

Код

=СУММ(ПРОСМОТР({2;4;5;8;10;13;14;16};СТОЛБЕЦ(1:1);2:2))/СЧЁТ(1/ПРОСМОТР({2;4;5;8;10;13;14;16};СТОЛБЕЦ(1:1);2:2))

К сообщению приложен файл: 8705060.xlsx (8.9 Kb)

email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460

Дата: Пятница, 20.03.2015, 15:50 | Сообщение № 9

Группа: Пользователи

Ранг: Прохожий

Сообщений: 7

Репутация: 0 ±

Замечаний: 0% ±

Excel 2007

Цитата Verniy55, 20.03.2015 в 15:50, в сообщении № 9

Verniy55, а шапка у таблицы есть? Может, к ней привязаться можно?

Да есть, файлик прилагаю.

Необходимо подсчитать среднее значение всех ячеек "яблоки" за исключением нулевых значений.

К сообщению приложен файл: 2222_1.xlsx (15.1 Kb)

Сообщение отредактировал Verniy55 - Пятница, 20.03.2015, 15:51

Дата: Пятница, 20.03.2015, 16:01 | Сообщение № 10

Группа: Админы

Ранг: Местный житель

Сообщений: 19511

Репутация: 4620 ±

Замечаний: ±

Excel 365 & Mac Excel

Тогда такой вариант возможен

Код

=СУММЕСЛИ($E$10:$FD$10;"яблоки";$E$11:$FD$11)/СЧЁТЕСЛИМН($E$10:$FD$10;"яблоки";$E$11:$FD$11;">0")

или

Код

=СУММЕСЛИ($E$10:$FD$10;"яблоки";$E$11:$FD$11)/СУММПРОИЗВ(($E$10:$FD$10="яблоки")*($E$11:$FD$11>0))

К сообщению приложен файл: 2974900.xlsx (12.4 Kb)

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816

_Boroda_

Дата: Пятница, 20.03.2015, 16:02 | Сообщение № 11

Группа: Админы

Ранг: Местный житель

Сообщений: 16885

Репутация: 6599 ±

Замечаний: ±

2003; 2007; 2010; 2013 RUS

Так нужно?

Код

=СРЗНАЧЕСЛИМН(E11:FD11;E10:FD10;"Яблоки";E11:FD11;"<>0")

К сообщению приложен файл: 2222_4.xlsx (12.4 Kb)

VEKTORVSFREEMAN

Дата: Пятница, 20.03.2015, 16:07 | Сообщение № 12

Группа: Друзья

Ранг: Ветеран

Сообщений: 772

Репутация: 130 ±

Замечаний: 0% ±

MS Office Excel 2010

Verniy55, вы смеётесь что-ли?
на прошлых файлах потратил уйму времени придумывая решение, а тут за минуту решил

Код

=СРЗНАЧЕСЛИМН(E11:FD11;E10:FD10;"яблоки";E11:FD11;">"&0)

К сообщению приложен файл: 5278983.xlsx (12.3 Kb)

"Опыт - это то, что получаешь, не получив того, что хотел"

Дата: Пятница, 20.03.2015, 16:13 | Сообщение № 13

Группа: Пользователи

Ранг: Прохожий

Сообщений: 7

Репутация: 0 ±

Замечаний: 0% ±

Excel 2007

VEKTORVSFREEMAN
_Boroda_
Pelena

Спасибо за помощь !

VEKTORVSFREEMAN
Прошу прощения за неточность задания.

Дата: Пятница, 20.03.2015, 16:33 | Сообщение № 14

Группа: Админы

Ранг: Местный житель

Сообщений: 19511

Репутация: 4620 ±

Замечаний: ±

Excel 365 & Mac Excel

krosav4ig вообще молодец! Решил первоначальный вариант respect

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816