Всем привет! Прошу помощи. Есть книга, в ней допустим 5 листов, которые проименованы: 1,2,3,4,5. Необходимо подсчитать среднее значение например ячеек А1, т.е. среднее от А1 на всех пяти листах, причем чтобы считались ячейки больше 0 (во всех ячейках есть данные, включая 0). Если применить формулу =СРЗНАЧ('1:5'!A1) то она при расчетах задействует ВСЕ ячейки, включая те в которых стоит 0. Если же применить формулу =СРЗНАЧЕСЛИ('1:5'!A1;">0"), то выдает #ЗНАЧ!. Горю
Всем привет! Прошу помощи. Есть книга, в ней допустим 5 листов, которые проименованы: 1,2,3,4,5. Необходимо подсчитать среднее значение например ячеек А1, т.е. среднее от А1 на всех пяти листах, причем чтобы считались ячейки больше 0 (во всех ячейках есть данные, включая 0). Если применить формулу =СРЗНАЧ('1:5'!A1) то она при расчетах задействует ВСЕ ячейки, включая те в которых стоит 0. Если же применить формулу =СРЗНАЧЕСЛИ('1:5'!A1;">0"), то выдает #ЗНАЧ!. Горю alexpsk60
Сообщение отредактировал alexpsk60 - Пятница, 25.07.2014, 17:27
alex1248, впринципе как вариант, но , конечно, хотелось бы без вывода значений ячеек на текущий лист, но впринципе можно. Единственное можно ли что бы значения ячеек выводились в строку, а не в столбец, как в Вашем примере? Дело в том что считаться должны будут не только А1, а довольно много строк: А1, А2, А3,.....
alex1248, впринципе как вариант, но , конечно, хотелось бы без вывода значений ячеек на текущий лист, но впринципе можно. Единственное можно ли что бы значения ячеек выводились в строку, а не в столбец, как в Вашем примере? Дело в том что считаться должны будут не только А1, а довольно много строк: А1, А2, А3,.....alexpsk60
Сообщение отредактировал alexpsk60 - Пятница, 25.07.2014, 18:25
Кстати, функция СРЗНАЧЕСЛИ не работает с трехмерными диапазонами. Так что, если и есть вариант, то всё равно сначала как-то преобразовывать данные (чтобы, например, 0 изменился на нечисловое значение), а значит - доп формулы на каждом листе, и применять СРЗНАЧ.
так, с формулой вроде разобрался, спасибо большое, теперь такой вопрос возник: дело в том что мне нужно будет считать среднее ячеек на листах, на большом количестве строк, т.е. посчитать не только среднее А1 всех листов, но так же и А2, А3, .... порядка нескольких сотен Я пытаюсь размножить формулу перетаскиванием за крестик вниз (т.е. чтобы автоматом прописывались =ДВССЫЛ(СТОЛБЕЦ()&"!A2"), =ДВССЫЛ(СТОЛБЕЦ()&"!A3"), =ДВССЫЛ(СТОЛБЕЦ()&"!A4") и так далее но формулы просто копируются, без изменений на А2, А3, А4...
Кстати, функция СРЗНАЧЕСЛИ не работает с трехмерными диапазонами. Так что, если и есть вариант, то всё равно сначала как-то преобразовывать данные (чтобы, например, 0 изменился на нечисловое значение), а значит - доп формулы на каждом листе, и применять СРЗНАЧ.
так, с формулой вроде разобрался, спасибо большое, теперь такой вопрос возник: дело в том что мне нужно будет считать среднее ячеек на листах, на большом количестве строк, т.е. посчитать не только среднее А1 всех листов, но так же и А2, А3, .... порядка нескольких сотен Я пытаюсь размножить формулу перетаскиванием за крестик вниз (т.е. чтобы автоматом прописывались =ДВССЫЛ(СТОЛБЕЦ()&"!A2"), =ДВССЫЛ(СТОЛБЕЦ()&"!A3"), =ДВССЫЛ(СТОЛБЕЦ()&"!A4") и так далее но формулы просто копируются, без изменений на А2, А3, А4...alexpsk60
Да, нарисовалась еще одна проблема, я так понял что в этом примере формула выводит значения со всех листов (с 1-го по 5-й), так вот а можно ли как-то добавить исключение чтобы выводило значения на Лист1 только например с 1 по 3 листа, а 4-й и 5-й игнорировало? Или хотя можно просто не растягивать формулу до 4-й ячейки и все?
Да, нарисовалась еще одна проблема, я так понял что в этом примере формула выводит значения со всех листов (с 1-го по 5-й), так вот а можно ли как-то добавить исключение чтобы выводило значения на Лист1 только например с 1 по 3 листа, а 4-й и 5-й игнорировало? Или хотя можно просто не растягивать формулу до 4-й ячейки и все?alexpsk60
Сообщение отредактировал alexpsk60 - Пятница, 25.07.2014, 19:36
Да, нарисовалась еще одна проблема, я так понял что в этом примере формула выводит значения со всех листов (с 1-го по 5-й), так вот а можно ли как-то добавить исключение чтобы выводило значения на Лист1 только например с 1 по 3 листа, а 4-й и 5-й игнорировало?
Наверное, я не совсем понял суть вопроса. Ну а так, почему нельзя-то? Проще всего - эти столбцы просто очистить. Или это при каком-то условии? Тогда надо добавлять в формулу это условие.
Да, нарисовалась еще одна проблема, я так понял что в этом примере формула выводит значения со всех листов (с 1-го по 5-й), так вот а можно ли как-то добавить исключение чтобы выводило значения на Лист1 только например с 1 по 3 листа, а 4-й и 5-й игнорировало?
Наверное, я не совсем понял суть вопроса. Ну а так, почему нельзя-то? Проще всего - эти столбцы просто очистить. Или это при каком-то условии? Тогда надо добавлять в формулу это условие.alex1248
Прошу помощи. Есть книга, в ней допустим 5 листов, которые проименованы: 1,2,3,4,5. Необходимо подсчитать среднее значение например ячеек А1, т.е. среднее от А1 на всех пяти листах, причем чтобы считались ячейки больше 0 (во всех ячейках есть данные, включая 0). Если применить формулу =СРЗНАЧ('1:5'!A1) то она при расчетах задействует ВСЕ ячейки, включая те в которых стоит 0. Если же применить формулу =СРЗНАЧЕСЛИ('1:5'!A1;">0"), то выдает #ЗНАЧ!.
Аналогичная ситуация и проблема, при использовании функции СРЗНАЧЕСЛИ напр. яч. Е21 >0 на разных листах выдает ошибку ЗНАЧ. Подскажите как быть?
Функция СРЗНАЧ ячеек работает без проблем на разных листах
Решение с выводом нужных значений ячеек разных листов в столбец, а потом поиск среднего значения- выход, но хочется формулой без промежуточных "столбцов"
Прошу помощи. Есть книга, в ней допустим 5 листов, которые проименованы: 1,2,3,4,5. Необходимо подсчитать среднее значение например ячеек А1, т.е. среднее от А1 на всех пяти листах, причем чтобы считались ячейки больше 0 (во всех ячейках есть данные, включая 0). Если применить формулу =СРЗНАЧ('1:5'!A1) то она при расчетах задействует ВСЕ ячейки, включая те в которых стоит 0. Если же применить формулу =СРЗНАЧЕСЛИ('1:5'!A1;">0"), то выдает #ЗНАЧ!.
Аналогичная ситуация и проблема, при использовании функции СРЗНАЧЕСЛИ напр. яч. Е21 >0 на разных листах выдает ошибку ЗНАЧ. Подскажите как быть?
Функция СРЗНАЧ ячеек работает без проблем на разных листах
Решение с выводом нужных значений ячеек разных листов в столбец, а потом поиск среднего значения- выход, но хочется формулой без промежуточных "столбцов"
Павел_22, я, конечно, не модератор, НО! - прочитайте правила форума, приложите свой пример, где вылезает #ЗНАЧ! - тогда и будем искать решение, возможно, и без промежуточных столбцов [p.s.]почему "столбцы" (в кавычках) я не понял
Павел_22, я, конечно, не модератор, НО! - прочитайте правила форума, приложите свой пример, где вылезает #ЗНАЧ! - тогда и будем искать решение, возможно, и без промежуточных столбцов [p.s.]почему "столбцы" (в кавычках) я не понял buchlotnik
Где на листе "Сводный" выходит ошибка "знач", при попытке использовать функцию СРЗНАЧЕСЛИ на всех листах
p.s. столбцы написаны в ковычках, тк выше по теме предлагалось выводить из разных листов значения ячеек для которых нужно использовать СРЗНАЧЕСЛИ в отдельный столбец, и уже из него находить СРЗНАЧЕСЛИ
Извиняюсь Прикладываю пример
Где на листе "Сводный" выходит ошибка "знач", при попытке использовать функцию СРЗНАЧЕСЛИ на всех листах
p.s. столбцы написаны в ковычках, тк выше по теме предлагалось выводить из разных листов значения ячеек для которых нужно использовать СРЗНАЧЕСЛИ в отдельный столбец, и уже из него находить СРЗНАЧЕСЛИПавел_22
Неужели никто не знает как решить данную проблему?
Никто не знает КАК вам надо, как желательно, как возможно, как нельзя. А впустую перебирать для вас варианты - дело долгое и неблагодарное. Соберите все на одном листе и считайте, как вам заблагорассудится.
Неужели никто не знает как решить данную проблему?
Никто не знает КАК вам надо, как желательно, как возможно, как нельзя. А впустую перебирать для вас варианты - дело долгое и неблагодарное. Соберите все на одном листе и считайте, как вам заблагорассудится.TimSha
Повторюсь: функция СРЗНАЧЕСЛИ(), так же как и СУММЕСЛИ() и СЧЁТЕСЛИ(), не работает с трёхмерными ссылками. Поэтому, либо доп. ячейки, либо воспользоваться другими инструментами
Повторюсь: функция СРЗНАЧЕСЛИ(), так же как и СУММЕСЛИ() и СЧЁТЕСЛИ(), не работает с трёхмерными ссылками. Поэтому, либо доп. ячейки, либо воспользоваться другими инструментамиPelena
"Черт возьми, Холмс! Но как??!!" Ю-money 41001765434816
Никто не знает КАК вам надо, как желательно, как возможно, как нельзя. А впустую перебирать для вас варианты - дело долгое и неблагодарное. Соберите все на одном листе и считайте, как вам заблагора
Ок, мне надо чтобы на определенном листе- пусть будет лист1, в определенной ячейке Excel рассчитывал арифметическое среднее значение определенных ячеек (между ячейками напр. А1 на разных листах), значения которых не равно нулю, из всей книги - из диапазона листов книги, и выводил этот результат в ячейку на лист1 Другими словами, в ячейке А1 на листе 1 надобно видеть =СРЗНАЧЕСЛИ(Лист2:Лист4!A1;">0"). В ячейке А1 на листе 1 при этой формуле выдает ошибку #ЗНАЧ!
Повторюсь: функция СРЗНАЧЕСЛИ(), так же как и СУММЕСЛИ() и СЧЁТЕСЛИ(), не работает с трёхмерными ссылками. Поэтому, либо доп. ячейки, либо воспользоваться другими инструментами
а какими например можно воспользоваться другими инструментами, чтобы не плодить доп. ячейки?
Никто не знает КАК вам надо, как желательно, как возможно, как нельзя. А впустую перебирать для вас варианты - дело долгое и неблагодарное. Соберите все на одном листе и считайте, как вам заблагора
Ок, мне надо чтобы на определенном листе- пусть будет лист1, в определенной ячейке Excel рассчитывал арифметическое среднее значение определенных ячеек (между ячейками напр. А1 на разных листах), значения которых не равно нулю, из всей книги - из диапазона листов книги, и выводил этот результат в ячейку на лист1 Другими словами, в ячейке А1 на листе 1 надобно видеть =СРЗНАЧЕСЛИ(Лист2:Лист4!A1;">0"). В ячейке А1 на листе 1 при этой формуле выдает ошибку #ЗНАЧ!
Повторюсь: функция СРЗНАЧЕСЛИ(), так же как и СУММЕСЛИ() и СЧЁТЕСЛИ(), не работает с трёхмерными ссылками. Поэтому, либо доп. ячейки, либо воспользоваться другими инструментами
а какими например можно воспользоваться другими инструментами, чтобы не плодить доп. ячейки?Павел_22
какими например можно воспользоваться другими инструментами
Макросом можно, например.
Уточните, какой результат Вы ожидаете в первом Вашем файле и во втором? Это среднее от посчитанных средних значений или среднее от диапазонов на листах. Это не совсем одно и то же.
какими например можно воспользоваться другими инструментами
Макросом можно, например.
Уточните, какой результат Вы ожидаете в первом Вашем файле и во втором? Это среднее от посчитанных средних значений или среднее от диапазонов на листах. Это не совсем одно и то же.Pelena
"Черт возьми, Холмс! Но как??!!" Ю-money 41001765434816
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
Я пытаюсь размножить формулу перетаскиванием за крестик вниз (т.е. чтобы автоматом прописывались =ДВССЫЛ(СТОЛБЕЦ()&"!A2"), =ДВССЫЛ(СТОЛБЕЦ()&"!A3"), =ДВССЫЛ(СТОЛБЕЦ()&"!A4") и так далее но формулы просто копируются, без изменений на А2, А3, А4...
