Excel среднее значение по всем листам - Word и Excel - помощь в работе с программами

Если вы хотите рассчитать среднее значение одного и того же диапазона / ячеек из разных листов, как вы могли бы быстро с этим справиться? В этой статье я расскажу о нескольких способах вычисления среднего числа ячеек на разных листах в Excel.

Вычислить среднее значение ячеек из нескольких листов в Excel
Вычислить среднее значение одной и той же ячейки из нескольких листов с помощью Kutools for Excel
Пакетное усреднение многих ячеек на нескольких листах с помощью Kutools for Excel

Вычислить среднее значение ячеек из нескольких листов в Excel

Например, вы хотите вычислить среднее значение диапазона A1: A10 от листа 1 до листа 5, вы можете легко решить это следующим образом:

Шаг 1. Выберите пустую ячейку, говорит Ячейка C3, введите формулу = СРЕДНЕЕ (Лист1: Лист5! A1: A10) в него и нажмите Enter ключ. Теперь вы получите среднее значение в ячейке.

Ноты:
(1) В формуле = СРЕДНЕЕ (Sheet1: Sheet5! A1: A10), Sheet1: Sheet5 — это несколько смежных листов, на основе которых вы вычисляете среднее значение, а A1: A10 означает те же ячейки / диапазон, в которых вы вычисляете среднее значение. несколько листов. Вы можете изменить их в зависимости от ваших потребностей.
(2) Если вы хотите рассчитать среднее значение для разных ячеек / диапазона из разных листов в Excel, например, вы хотите рассчитать среднее значение для диапазона A1: A5 в Sheet1, диапазона A3: A6 в Sheet2, диапазона A7: A9 в Sheet3 , Диапазон A2: A10 на Листе 4 и Диапазон A4: A7 на Листе 5, вам следует применить эту формулу =AVERAGE(A1:A5,Sheet2!A3:A6,Sheet3!A7:A9,Sheet4!A2:A10,Sheet5!A4:A7).

Вычислить среднее значение одной и той же ячейки из нескольких листов с помощью Kutools for Excel

Kutools for Excel’s Динамически обращаться к рабочим листам Утилита может помочь пользователям извлекать значения из одних и тех же ячеек других листов. Следовательно, мы можем расширить эту утилиту, чтобы легко усреднить одни и те же ячейки из нескольких листов в Excel.

Kutools for Excel — Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная пробная версия 30-день, кредитная карта не требуется! Get It Now

1. Создайте новый рабочий лист, а затем выберите ячейку, которую вы будете усреднять, из нескольких рабочих листов. В нашем случае мы выбираем ячейку D7.

2. Нажмите Кутулс > Больше (в Формула группа)> Динамически обращаться к рабочим листам.

3. В открывшемся диалоговом окне (1) Укажите порядок заполнения из Заполнить заказ раскрывающийся список. В нашем случае мы выбираем Заполнять по вертикали ячейку за ячейкой; (2) Перейдите к Список рабочих листов установите флажок и отметьте листы, ячейки которых вы будете усреднять; (3) Щелкните значок Диапазон заполнения кнопку и закройте диалоговое окно. Смотрите скриншот:

4. Теперь значения извлекаются из определенной ячейки указанного рабочего листа и заполняются в новый рабочий лист. Просто выберите пустую ячейку и примените формулу = СРЕДНИЙ (D7: D11) (D7: D11 — это диапазон, заполненный значениями, извлеченными из других листов) и нажмите Enter .

Пакетное усреднение многих ячеек на нескольких листах с помощью Kutools for Excel

Иногда вам нужно усреднить одни и те же ячейки на нескольких листах одновременно. Например, усреднить ячейку A1 со всех рабочих листов, усреднить ячейку B1 со всех рабочих листов, а также ячейку C1 одновременно. Можешь попробовать Kutools for Excel’s Combine (рабочие листы и рабочие книги), чтобы сделать это легко!

1. Нажмите Кутулс Плюс > Сочетать , чтобы открыть мастер объединения листов.

2. В мастере объединения рабочих листов — шаг 1 из 3 проверьте Объединяйте и вычисляйте значения из нескольких книг на одном листе и нажмите Download кнопку.

3. В мастере объединения рабочих листов — шаг 2 из 3:
(1) Проверьте рабочие листы, по которым вы будете усреднять Список рабочих листов;
(2) Щелкните значок Приложения кнопка выбрать диапазон, в котором вы будете усреднять;
(3) Щелкните значок Тот же диапазон кнопку.
(4) Щелкните значок Download кнопку.

4. В мастере объединения рабочих листов — шаг 3 из 3 выберите Средняя из Функция раскрывающийся список с подсветкой, укажите ярлыки в соответствии с вашими потребностями и щелкните Завершить кнопку.

5. Появится диалоговое окно с просьбой сохранить сценарий комбинации. Щелкните значок Да или Нет кнопку в зависимости от ваших потребностей. В нашем случае мы нажимаем кнопку Нет кнопку.

До сих пор каждая ячейка в указанном диапазоне была усреднена из указанного рабочего листа.

Демо: средние ячейки с разных листов в Excel

Лучшие инструменты для работы в офисе

Kutools for Excel Решит большинство ваших проблем и повысит вашу производительность на 80%

Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма …
Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон…
Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы… Предотвращение дублирования ячеек; Сравнить диапазоны…
Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор …
Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое …
Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии…
Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом …
Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF…
Более 300 мощных функций. Поддерживает Office/Excel 2007-2021 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.

Вкладка Office: интерфейс с вкладками в Office и упрощение работы

Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!

Комментарии (2)

Оценок пока нет. Оцените первым!

Источник

На чтение 6 мин. Просмотров 79 Опубликовано 21.05.2021

Если вы хотите вычислить среднее значение одного и того же диапазона/ячеек на разных листах, как вы могли бы быстро с этим справиться? В этой статье я расскажу о нескольких способах вычисления среднего количества ячеек на разных листах в Excel.

Вычислить среднее значение ячеек на нескольких листах в Excel
Вычислить среднее значение одной и той же ячейки из нескольких листов с помощью Kutools for Excel
Пакетное среднее количество ячеек на нескольких листах с помощью Kutools for Excel

Содержание

Простое объединение нескольких листов/книг в один лист/книгу
Вычислить среднее значение ячеек из нескольких листов в Excel
Вычислить среднее значение одной и той же ячейки из нескольких листов с помощью Kutools for Excel
Пакетное среднее количество ячеек на нескольких листах с помощью Kutools for Excel
Демо: среднее значение ячеек с разных листов в Excel

Простое объединение нескольких листов/книг в один лист/книгу

Объединение десятков листов может быть утомительным из разных книг на один лист. Но с помощью Kutools for Excel’s Combine (рабочие листы и книги) утилита, вы можете сделать это всего за несколько щелчков мышью! 30-дневная бесплатная пробная версия полнофункциональной версии!

Вкладка «Office» Включает редактирование и просмотр с вкладками в Office и делает вашу работу намного проще …

Подробнее … Бесплатная загрузка …

Kutools for Excel решает большинство ваших проблем и увеличивает Ваша производительность на 80%.

Повторное использование чего угодно: добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
Более 20 текстовых функций: извлечение числа из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
Инструменты слияния: несколько книг и листов в одну; Объединить несколько ячеек/строк/столбцов без потери данных; Объедините повторяющиеся строки и суммируйте.
Инструменты разделения: разделение данных на несколько листов в зависимости от значения; Из одной книги в несколько файлов Excel, PDF или CSV; Один столбец в несколько столбцов.
Вставить пропуск скрытых/отфильтрованных строк; Подсчет и сумма по цвету фона; Массовая отправка персонализированных писем нескольким получателям.
Суперфильтр: создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделе, дню, частоте и т. Д. Фильтр жирным шрифтом, формулами, комментарием …
Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.

Подробнее … Бесплатная загрузка …

Вычислить среднее значение ячеек из нескольких листов в Excel

Шаг 1 : Выберите пустую ячейку, говорит «Ячейка C3», введите в нее формулу = СРЕДНЕЕ (Sheet1: Sheet5! A1: A10) и нажмите клавишу Enter . Теперь вы получите среднее значение в ячейке.

Примечания:
(1) В формуле = AVERAGE (Sheet1: Sheet5! A1: A10), Sheet1: Sheet5 – это несколько соседних листов, по которым вы вычисляете среднее значение, а A1: A10 означает те же ячейки/диапазон, в которых вы будете вычислять среднее значение на нескольких листах. Вы можете изменить их в зависимости от ваших потребностей.
(2) Если вы хотите рассчитать среднее значение для разных ячеек/диапазона из разных листов в Excel, например, вы хотите вычислить среднее значение для диапазона A1: A5 на листе Sheet1, диапазона A3: A6 на листе 2, диапазона A7: A9 на листе 3, диапазона A2: A10 в Sheet4 и Range A4: A7 в Sheet5, вы должны применить эту формулу = AVERAGE (A1: A5, Sheet2! A3: A6, Sheet3! A7: A9, Sheet4! A2: A10, Sheet5! A4: A7).

Вычислить среднее значение одной и той же ячейки из нескольких листов с помощью Kutools for Excel

Kutools for Excel Утилита Динамически ссылаться на рабочие листы может помочь пользователям извлекать значения из одних и тех же ячеек других листов. Поэтому мы можем расширить эту утилиту, чтобы легко усреднить одни и те же ячейки из нескольких листов в Excel.

Kutools for Excel – включает более 300 удобных инструментов для Excel. Полнофункциональная 30-дневная бесплатная пробная версия, кредитная карта не требуется! Получить сейчас

2. Нажмите Kutools > Еще (в группе Формула )> Динамически ссылаться на рабочие листы .

3. В открывшемся диалоговом окне (1) Укажите порядок заполнения из раскрывающегося списка Порядок заполнения . В нашем случае мы выбираем ячейку Заливка по вертикали после ячейки ; (2) Перейдите в поле Список рабочих листов и проверьте рабочие листы, ячейки которых вы будете усреднять; (3) Нажмите кнопку Диапазон заполнения и закройте диалоговое окно. См. Снимок экрана:

Kutools for Excel – Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Получить сейчас

4. Теперь значения извлекаются из определенной ячейки указанного рабочего листа и заполняются в новый рабочий лист. Просто выберите пустую ячейку и примените формулу = СРЕДНЕЕ (D7: D11) (D7: D11 – это диапазон, заполненный извлеченными значениями из других листов) и нажмите клавишу Enter .

Пакетное среднее количество ячеек на нескольких листах с помощью Kutools for Excel

Иногда вам нужно усреднить одни и те же ячейки на нескольких листах одновременно. Например, средняя ячейка A1 со всего листа, средняя ячейка B1 со всех листов, а также ячейка C1 одновременно. Вы можете попробовать Kutools for Excel’s Combine (рабочие листы и книги), чтобы сделать это легко!

1. Нажмите Kutools Plus > Объединить , чтобы открыть мастер объединения рабочих листов.

2. В мастере “Объединить рабочие листы – шаг 1 из 3” установите флажок Объединить и вычислить значения из нескольких книг в один рабочий лист и нажмите кнопку Далее .

3. В мастере объединения рабочих листов – шаг 2 из 3:
(1) Проверьте рабочие листы, по которым будет выполнено усреднение, в списке Рабочий лист ;
(2) Нажмите кнопку Обзор , чтобы выбрать диапазон для усреднения;
(3) Нажмите кнопку Одинаково диапазон .
(4) Нажмите кнопку Далее .

Kutools for Excel – включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Получить сейчас

4. В мастере объединения рабочих листов – шаг 3 из 3 выберите Среднее в раскрывающемся списке Функция , укажите ярлыки в соответствии с вашими потребностями и нажмите Готово .

5. Появится диалоговое окно с просьбой сохранить сценарий комбинации. Нажмите кнопку Да или Нет в зависимости от ваших потребностей. В нашем случае мы нажимаем кнопку Нет .

До сих пор каждая ячейка в указанном диапазоне была усреднена из указанного рабочего листа.

Демо: среднее значение ячеек с разных листов в Excel

Kutools for Excel включает в себя более 300 удобных инструментов для Excel, которые можно бесплатно попробовать без ограничений в течение 30 дней. Загрузить и бесплатную пробную версию !

Источник

StasStoychev

Пользователь

Сообщений: 8
Регистрация: 30.09.2015

12.10.2020 13:52:12

Добрый день!
Необходимо вычислить среднее значение по данным из нескольких ячеек каждая из которых находится на отдельном листе. Тему «

https://www.planetaexcel.ru/forum/index.php?PAGE_NAME=read&FID=8&TID=27858

» — почитал, пробую применить под свою задачу, не совсем получается. Диапазон ячеек из разных листов ввожу через «SHIFT»: для простых функций, сумм, счет, срзнач — работает

Код
(=СРЗНАЧ('29.09.2020:06.10.2020'!AE6) =СУММ('29.09.2020:06.10.2020'!AE6) =СЧЁТ('29.09.2020:06.10.2020'!AE6)

каждая по отдельности даёт результат,), а вот запись вида

Код
=СЧЁТЕСЛИ('29.09.2020:06.10.2020'!AE6;"<>0")

даёт ошибку «#ЗНАЧ!» …
Помогите пожалуйста понять что не так делаю … поиск по форуму пока не помог …

Дмитрий(The_Prist) Щербаков

Пользователь

Сообщений: 14181
Регистрация: 15.09.2012

Профессиональная разработка приложений для MS Office

12.10.2020 13:58:49

Цитата
StasStoychev написал: что не так делаю

применяете как трехмерную функцию, которая этого не умеет.

Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы…

	Ок. Каким образом вычислить среднее в таком случае можете подсказать?

Mershik Пользователь Сообщений: 8277 Регистрация: 03.12.2015	StasStoychev, вы бы файл-пример приложили и в нем показали вот это есть вот это хочу, Вам бы может и варианты предложили. Не бойтесь совершенства. Вам его не достичь.

Дмитрий(The_Prist) Щербаков

Пользователь

Сообщений: 14181
Регистрация: 15.09.2012

Профессиональная разработка приложений для MS Office

12.10.2020 14:45:04

Так можно попробовать:

Код
=СУММ(Лист1:Лист3!A1)/СУММ(ЕСЛИ(СЧЁТЕСЛИ(ДВССЫЛ({"Лист1":"Лист2":"Лист3"}&"!A1");"<>0");1;0))

или записать имена нужных листов в диапазон(скажем L1:L3) и использовать его:

Код
=СУММ(Лист1:Лист3!A1)/СУММ(ЕСЛИ(СЧЁТЕСЛИ(ДВССЫЛ(L1:L3&"!A1");"<>0");1;0))

обе формулы —

формулы массива

Изменено: Дмитрий(The_Prist) Щербаков — 12.10.2020 14:50:50

Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы…

Дмитрий(The_Prist) Щербаков написал:

«или записать имена нужных листов в диапазон(скажем L1:L3) и использовать его:Код ? 1=СУММ(Лист1:Лист3!A1)/СУММ(ЕСЛИ(СЧЁТЕСЛИ(ДВССЫЛ(L1:L3&»!A1″);»<>0″);1;0))обе формулы — формулы массива .»
Благодарю за подсказку. Пока не получилось. с формулами массива работать не приходилось. Может не понял в какой момент надо вводить через «Ctrl+Shift+Enter» …
Пробовал приложить файл в архиве размером 28 килобайт — не получается, хотя ограничение 100 кб , если пройдёт ссылка, вот он на яндекс-диске …

https://yadi.sk/i/nstyj6gFaD0i4g

, буду благодарен за помощь …

	На листе 07.10.2020, в ячейке АМ8, пытаюсь получить среднее значение из ячеек АЕ6 из листов с «29.09.2020» по «06.10.2020»

Дмитрий(The_Prist) Щербаков

Пользователь

Сообщений: 14181
Регистрация: 15.09.2012

Профессиональная разработка приложений для MS Office

12.10.2020 16:09:22

Цитата
StasStoychev написал: Может не понял в какой момент надо вводить через «Ctrl+Shift+Enter»

нет. Вы не поняли куда что писать, это факт.

Код
СЧЁТЕСЛИ(ДВССЫЛ(29.09.2020:06.10.2020&"!AE6");"<>0")

откуда здесь опять трехмерная ссылка на листы? Посмотрите как написал я и что придумали Вы.
Вам надо было записать в массив(в фигурных скобках) имена листов(формула для ячейки АМ7):

Код

=СУММ('29.09.2020:06.10.2020'!AE6)/СУММ(ЕСЛИ(СЧЁТЕСЛИ(ДВССЫЛ({"29.09.2020":"30.09.2020":"01.10.2020":"02.10.2020":"03.10.2020":"04.10.2020":"05.10.2020":"06.10.2020"}&"!AE6");"<>0");1;0))

это еще раз говорит о том, что как кто чего ни думал — но если знаний не хватает, то прикладывать пример обязательно.

P.S. Файл-то ни фига не 28Кб — он 528
P.P.S. Не надо мне в личные сообщения писать — будет возможность и желание сам загляну. А все эти просьбы в личке только негативный эффект имеют. Это на будущее.

Изменено: Дмитрий(The_Prist) Щербаков — 12.10.2020 16:13:05

Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы…

	Благодарю за оперативный ответ! Я пытался записать диапазон как в Вашем втором примере … Знаний мне катастрофически не хватает, так что прошу извинить …

Дмитрий(The_Prist) Щербаков

Пользователь

Сообщений: 14181
Регистрация: 15.09.2012

Профессиональная разработка приложений для MS Office

#10

12.10.2020 16:17:12

Цитата
StasStoychev написал: как в Вашем втором примере

для этого надо для начала вот это:

Цитата
Дмитрий(The_Prist) Щербаков написал: записать имена нужных листов в диапазон(скажем L1:L3)

Т.е. у меня использовалось три листа. У Вас листов больше и диапазон должен быть тоже другой.
Чтобы было понимание того, как примерно работает формула советую ознакомиться:

Как просуммировать данные с нескольких листов, в том числе по условию

Формула там другая, но принцип определения листов тот же.

Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы…

StasStoychev

Пользователь

Сообщений: 8
Регистрация: 30.09.2015

#11

12.10.2020 16:17:51

Цитата
Дмитрий(The_Prist) Щербаков написал: P.S. Файл-то ни фига не 28Кб — он 528

28 кб — это в архииве было, а так да 528

Цитата
Дмитрий(The_Prist) Щербаков написал: P.P.S. Не надо мне в личные сообщения писать — будет возможность и желание сам загляну. А все эти просьбы в личке только негативный эффект имеют. Это на будущее.

Принял, не буду , прошу извинить

День добрый!

Ещё раз спасибо за помощь, теперь возникла следующая проблема, если есть возможность помогите разобраться:

при расчете среднего значения в столбце АМ на листе «07.10.2020», иногда случается что предыдущих листах нет значений отличных от «0», следовательно возникает ошибка «#Дел/0». Как в такой громоздкой формуле применить логику с «ЕСЛИ»? У меня не получилось …

Ссылка на файл:

https://yadi.sk/i/rn1GavSic10mCQ

ЕСЛИОШИБКА прекрасно справляется с задачей. Вы хоть пробовали искать решение проблемы или сразу в форум побежали?

Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы…

StasStoychev Пользователь Сообщений: 8 Регистрация: 30.09.2015	#14 13.10.2020 10:55:15 Ещё раз благодарю за подсказку! С «ЕСЛИОШИБКА» действительно очень просто получается! Ура!

Источник

Среднее значение не нулевых ячеек на разных листах

alexpsk60

Дата: Пятница, 25.07.2014, 17:26 |
Сообщение № 1

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

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

Сообщений: 6

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

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

Excel 2007

Всем привет!
Прошу помощи. Есть книга, в ней допустим 5 листов, которые проименованы: 1,2,3,4,5. Необходимо подсчитать среднее значение например ячеек А1, т.е. среднее от А1 на всех пяти листах, причем чтобы считались ячейки больше 0 (во всех ячейках есть данные, включая 0). Если применить формулу =СРЗНАЧ(‘1:5’!A1) то она при расчетах задействует ВСЕ ячейки, включая те в которых стоит 0.
Если же применить формулу =СРЗНАЧЕСЛИ(‘1:5’!A1;»>0″), то выдает #ЗНАЧ!.
Горю :'(

Сообщение отредактировал alexpsk60 — Пятница, 25.07.2014, 17:27

Ответить

alex1248

Дата: Пятница, 25.07.2014, 18:16 |
Сообщение № 2

Группа: Проверенные

Ранг: Обитатель

Сообщений: 384

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

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

Excel 2007, 2010

alexpsk60, Не знаю, подойдет ли вам вариант с доп столбцом в листе с итогом. Но если подойдет, то, возможно, так.

skype alex12481632
Qiwi +79276708519

Сообщение отредактировал alex1248 — Пятница, 25.07.2014, 18:16

Ответить

alexpsk60

Дата: Пятница, 25.07.2014, 18:23 |
Сообщение № 3

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

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

Сообщений: 6

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

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

Excel 2007

alex1248, впринципе как вариант, но , конечно, хотелось бы без вывода значений ячеек на текущий лист, но впринципе можно. Единственное можно ли что бы значения ячеек выводились в строку, а не в столбец, как в Вашем примере?
Дело в том что считаться должны будут не только А1, а довольно много строк: А1, А2, А3,…..

Сообщение отредактировал alexpsk60 — Пятница, 25.07.2014, 18:25

Ответить

alex1248

Дата: Пятница, 25.07.2014, 18:26 |
Сообщение № 4

Группа: Проверенные

Ранг: Обитатель

Сообщений: 384

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

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

Excel 2007, 2010

Единственное можно ли что бы значения ячеек выводились в строку, а не в столбец, как в Вашем примере?

В формуле замените СТРОКА на СТОЛБЕЦ и протяните ее из любой ячейки первого столбца.

skype alex12481632
Qiwi +79276708519

Ответить

alexpsk60

Дата: Пятница, 25.07.2014, 18:46 |
Сообщение № 5

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

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

Сообщений: 6

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

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

Excel 2007

В формуле замените СТРОКА на СТОЛБЕЦ и протяните ее из любой ячейки первого столбца.

Поменял в ячейке С1 (листа Лист1) СТРОКА на СТОЛБЕЦ, перетянул вправо на 5 ячеек, выдало какую-то ерунду

Ответить

alex1248

Дата: Пятница, 25.07.2014, 18:51 |
Сообщение № 6

Группа: Проверенные

Ранг: Обитатель

Сообщений: 384

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

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

Excel 2007, 2010

alexpsk60, ловите.

Кстати, функция СРЗНАЧЕСЛИ вообще не работает с трехмерными диапазонами.

skype alex12481632
Qiwi +79276708519

Сообщение отредактировал alex1248 — Пятница, 25.07.2014, 19:10

Ответить

alexpsk60

Дата: Пятница, 25.07.2014, 19:10 |
Сообщение № 7

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

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

Сообщений: 6

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

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

Excel 2007

Кстати, функция СРЗНАЧЕСЛИ не работает с трехмерными диапазонами. Так что, если и есть вариант, то всё равно сначала как-то преобразовывать данные (чтобы, например, 0 изменился на нечисловое значение), а значит — доп формулы на каждом листе, и применять СРЗНАЧ.

так, с формулой вроде разобрался, спасибо большое, теперь такой вопрос возник: дело в том что мне нужно будет считать среднее ячеек на листах, на большом количестве строк, т.е. посчитать не только среднее А1 всех листов, но так же и А2, А3, …. порядка нескольких сотен Я пытаюсь размножить формулу перетаскиванием за крестик вниз (т.е. чтобы автоматом прописывались =ДВССЫЛ(СТОЛБЕЦ()&»!A2″), =ДВССЫЛ(СТОЛБЕЦ()&»!A3″), =ДВССЫЛ(СТОЛБЕЦ()&»!A4″) и так далее но формулы просто копируются, без изменений на А2, А3, А4…

Ответить

alex1248

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

Группа: Проверенные

Ранг: Обитатель

Сообщений: 384

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

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

Excel 2007, 2010

alexpsk60,

skype alex12481632
Qiwi +79276708519

Ответить

alexpsk60

Дата: Пятница, 25.07.2014, 19:23 |
Сообщение № 9

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

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

Сообщений: 6

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

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

Excel 2007

alex1248, ого, интересно) Вроде как понял, спасибо большое! Буду завтра на работе пробовать на реальном файле

Сообщение отредактировал alexpsk60 — Пятница, 25.07.2014, 19:31

Ответить

alexpsk60

Дата: Пятница, 25.07.2014, 19:33 |
Сообщение № 10

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

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

Сообщений: 6

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

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

Excel 2007

Да, нарисовалась еще одна проблема, я так понял что в этом примере формула выводит значения со всех листов (с 1-го по 5-й), так вот а можно ли как-то добавить исключение чтобы выводило значения на Лист1 только например с 1 по 3 листа, а 4-й и 5-й игнорировало?
Или хотя можно просто не растягивать формулу до 4-й ячейки и все?

Сообщение отредактировал alexpsk60 — Пятница, 25.07.2014, 19:36

Ответить

alex1248

Дата: Пятница, 25.07.2014, 19:39 |
Сообщение № 11

Группа: Проверенные

Ранг: Обитатель

Сообщений: 384

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

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

Excel 2007, 2010

Наверное, я не совсем понял суть вопроса.
Ну а так, почему нельзя-то? Проще всего — эти столбцы просто очистить.
Или это при каком-то условии? Тогда надо добавлять в формулу это условие.

skype alex12481632
Qiwi +79276708519

Ответить

alex1248

Дата: Пятница, 25.07.2014, 19:39 |
Сообщение № 12

Группа: Проверенные

Ранг: Обитатель

Сообщений: 384

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

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

Excel 2007, 2010

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

Ну да, и так можно.

skype alex12481632
Qiwi +79276708519

Ответить

Павел_22

Дата: Воскресенье, 07.12.2014, 13:58 |
Сообщение № 13

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

Ранг: Новичок

Сообщений: 13

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

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

Excel 2010

Прошу помощи. Есть книга, в ней допустим 5 листов, которые проименованы: 1,2,3,4,5. Необходимо подсчитать среднее значение например ячеек А1, т.е. среднее от А1 на всех пяти листах, причем чтобы считались ячейки больше 0 (во всех ячейках есть данные, включая 0). Если применить формулу =СРЗНАЧ(‘1:5’!A1) то она при расчетах задействует ВСЕ ячейки, включая те в которых стоит 0.
Если же применить формулу =СРЗНАЧЕСЛИ(‘1:5’!A1;»>0″), то выдает #ЗНАЧ!.

Аналогичная ситуация и проблема, при использовании функции СРЗНАЧЕСЛИ напр. яч. Е21 >0 на разных листах выдает ошибку ЗНАЧ. Подскажите как быть?

Функция СРЗНАЧ ячеек работает без проблем на разных листах

Решение с выводом нужных значений ячеек разных листов в столбец, а потом поиск среднего значения- выход, но хочется формулой без промежуточных «столбцов»

Заранее спасибо!

Ответить

buchlotnik

Дата: Воскресенье, 07.12.2014, 14:08 |
Сообщение № 14

Группа: Заблокированные

Ранг: Участник клуба

Сообщений: 3442

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

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

2010, 2013, 2016 RUS / ENG

Павел_22, я, конечно, не модератор, НО! — прочитайте правила форума, приложите свой пример, где вылезает #ЗНАЧ! — тогда и будем искать решение, возможно, и без промежуточных столбцов [p.s.]почему «столбцы» (в кавычках) я не понял

Ответить

Павел_22

Дата: Воскресенье, 07.12.2014, 14:28 |
Сообщение № 15

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

Ранг: Новичок

Сообщений: 13

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

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

Excel 2010

Извиняюсь Прикладываю пример

Где на листе «Сводный» выходит ошибка «знач», при попытке использовать функцию СРЗНАЧЕСЛИ на всех листах

p.s. столбцы написаны в ковычках, тк выше по теме предлагалось выводить из разных листов значения ячеек для которых нужно использовать СРЗНАЧЕСЛИ в отдельный столбец, и уже из него находить СРЗНАЧЕСЛИ

К сообщению приложен файл:

0794250.xlsx
(11.9 Kb)

Ответить

Павел_22

Дата: Понедельник, 08.12.2014, 10:40 |
Сообщение № 16

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

Ранг: Новичок

Сообщений: 13

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

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

Excel 2010

Неужели никто не знает как решить данную проблему?

Ответить

TimSha

Дата: Понедельник, 08.12.2014, 11:12 |
Сообщение № 17

Группа: Проверенные

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

Сообщений: 627

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

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

Excel 2013 Pro +

Неужели никто не знает как решить данную проблему?

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

«Ctrl+S» — достойное завершение ваших гениальных мыслей!..

Ответить

Pelena

Дата: Понедельник, 08.12.2014, 11:15 |
Сообщение № 18

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

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

Сообщений: 18797

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

Замечаний:
±

Excel 2016 & Mac Excel

Повторюсь: функция СРЗНАЧЕСЛИ(), так же как и СУММЕСЛИ() и СЧЁТЕСЛИ(), не работает с трёхмерными ссылками. Поэтому, либо доп. ячейки, либо воспользоваться другими инструментами

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

Ответить

Павел_22

Дата: Понедельник, 08.12.2014, 16:37 |
Сообщение № 19

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

Ранг: Новичок

Сообщений: 13

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

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

Excel 2010

Ок, мне надо чтобы на определенном листе- пусть будет лист1, в определенной ячейке Excel рассчитывал арифметическое среднее значение определенных ячеек (между ячейками напр. А1 на разных листах), значения которых не равно нулю, из всей книги — из диапазона листов книги, и выводил этот результат в ячейку на лист1 yes
Другими словами, в ячейке А1 на листе 1 надобно видеть =СРЗНАЧЕСЛИ(Лист2:Лист4!A1;»>0″). В ячейке А1 на листе 1 при этой формуле выдает ошибку #ЗНАЧ!

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

Сообщение отредактировал Павел_22 — Понедельник, 08.12.2014, 17:46

Ответить

Pelena

Дата: Понедельник, 08.12.2014, 17:42 |
Сообщение № 20

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

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

Сообщений: 18797

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

Замечаний:
±

Excel 2016 & Mac Excel

какими например можно воспользоваться другими инструментами

Макросом можно, например.

Уточните, какой результат Вы ожидаете в первом Вашем файле и во втором? Это среднее от посчитанных средних значений или среднее от диапазонов на листах. Это не совсем одно и то же.

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

Ответить

Источник

Содержание

Стандартный способ вычисления
Вычисление с помощью Мастера функций
Панель формул
Ручной ввод функции
Расчет среднего значения по условию
Вопросы и ответы

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

Стандартный способ вычисления

Самый простой и известный способ найти среднее арифметическое набора чисел — это воспользоваться специальной кнопкой на ленте Microsoft Excel. Выделяем диапазон чисел, расположенных в столбце или в строке документа. Находясь во вкладке «Главная», жмем на кнопку «Автосумма», которая расположена на ленте в блоке инструментов «Редактирование». Из выпадающее списка выбираем пункт «Среднее».

После этого, с помощью функции «СРЗНАЧ», производится расчет. В ячейку под выделенным столбцом, или справа от выделенной строки, выводится средняя арифметическая данного набора чисел.

Этот способ хорош простотой и удобством. Но, у него имеются и существенные недостатки. С помощью этого способа можно произвести подсчет среднего значения только тех чисел, которые располагаются в ряд в одном столбце, или в одной строке. А вот, с массивом ячеек, или с разрозненными ячейками на листе, с помощью этого способа работать нельзя.

Например, если выделить два столбца, и вышеописанным способом вычислить среднее арифметическое, то ответ будет дан для каждого столбца в отдельности, а не для всего массива ячеек.

Вычисление с помощью Мастера функций

Для случаев, когда нужно подсчитать среднюю арифметическую массива ячеек, или разрозненных ячеек, можно использовать Мастер функций. Он применяет все ту же функцию «СРЗНАЧ», известную нам по первому методу вычисления, но делает это несколько другим способом.

Кликаем по ячейке, где хотим, чтобы выводился результат подсчета среднего значения. Жмем на кнопку «Вставить функцию», которая размещена слева от строки формул. Либо же, набираем на клавиатуре комбинацию Shift+F3.

Запускается Мастер функций. В списке представленных функций ищем «СРЗНАЧ». Выделяем его, и жмем на кнопку «OK».

Открывается окно аргументов данной функции. В поля «Число» вводятся аргументы функции. Это могут быть как обычные числа, так и адреса ячеек, где эти числа расположены. Если вам неудобно вводить адреса ячеек вручную, то следует нажать на кнопку расположенную справа от поля ввода данных.

После этого, окно аргументов функции свернется, а вы сможете выделить ту группу ячеек на листе, которую берете для расчета. Затем, опять нажимаете на кнопку слева от поля ввода данных, чтобы вернуться в окно аргументов функции.

Если вы хотите подсчитать среднее арифметическое между числами, находящимися в разрозненных группах ячеек, то те же самые действия, о которых говорилось выше, проделывайте в поле «Число 2». И так до тех пор, пока все нужные группы ячеек не будут выделены.

После этого, жмите на кнопку «OK».

Результат расчета среднего арифметического будет выделен в ту ячейку, которую вы выделили перед запуском Мастера функций.

Панель формул

Существует ещё третий способ запустить функцию «СРЗНАЧ». Для этого, переходим во вкладку «Формулы». Выделяем ячейку, в которой будет выводиться результат. После этого, в группе инструментов «Библиотека функций» на ленте жмем на кнопку «Другие функции». Появляется список, в котором нужно последовательно перейти по пунктам «Статистические» и «СРЗНАЧ».

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

Дальнейшие действия точно такие же.

Ручной ввод функции

Но, не забывайте, что всегда при желании можно ввести функцию «СРЗНАЧ» вручную. Она будет иметь следующий шаблон: «=СРЗНАЧ(адрес_диапазона_ячеек(число); адрес_диапазона_ячеек(число)).

Конечно, этот способ не такой удобный, как предыдущие, и требует держать в голове пользователя определенные формулы, но он более гибкий.

Расчет среднего значения по условию

Кроме обычного расчета среднего значения, имеется возможность подсчета среднего значения по условию. В этом случае, в расчет будут браться только те числа из выбранного диапазона, которые соответствуют определенному условию. Например, если эти числа больше или меньше конкретно установленного значения.

Для этих целей, используется функция «СРЗНАЧЕСЛИ». Как и функцию «СРЗНАЧ», запустить её можно через Мастер функций, из панели формул, или при помощи ручного ввода в ячейку. После того, как открылось окно аргументов функции, нужно ввести её параметры. В поле «Диапазон» вводим диапазон ячеек, значения которых будут участвовать в определении среднего арифметического числа. Делаем это тем же способом, как и с функцией «СРЗНАЧ».

А вот, в поле «Условие» мы должны указать конкретное значение, числа больше или меньше которого будут участвовать в расчете. Это можно сделать при помощи знаков сравнения. Например, мы взяли выражение «>=15000». То есть, для расчета будут браться только ячейки диапазона, в которых находятся числа большие или равные 15000. При необходимости, вместо конкретного числа, тут можно указать адрес ячейки, в которой расположено соответствующее число.

Поле «Диапазон усреднения» не обязательно для заполнения. Ввод в него данных является обязательным только при использовании ячеек с текстовым содержимым.

Когда все данные введены, жмем на кнопку «OK».

После этого, в предварительно выбранную ячейку выводится результат расчета среднего арифметического числа для выбранного диапазона, за исключением ячеек, данные которых не отвечают условиям.

Как видим, в программе Microsoft Excel существует целый ряд инструментов, с помощью которых можно рассчитать среднее значение выбранного ряда чисел. Более того, существует функция, которая автоматически отбирает числа из диапазона, не соответствующие заранее установленному пользователем критерию. Это делает вычисления в приложении Microsoft Excel ещё более удобными для пользователей.

Источник

Вычислить среднее значение ячеек из нескольких листов в Excel

Вычислить среднее значение одной и той же ячейки из нескольких листов с помощью Kutools for Excel

Пакетное усреднение многих ячеек на нескольких листах с помощью Kutools for Excel

Демо: средние ячейки с разных листов в Excel

Лучшие инструменты для работы в офисе

Kutools for Excel Решит большинство ваших проблем и повысит вашу производительность на 80%

Вкладка Office: интерфейс с вкладками в Office и упрощение работы

Простое объединение нескольких листов/книг в один лист/книгу

Вычислить среднее значение ячеек из нескольких листов в Excel

Вычислить среднее значение одной и той же ячейки из нескольких листов с помощью Kutools for Excel

Пакетное среднее количество ячеек на нескольких листах с помощью Kutools for Excel

Демо: среднее значение ячеек с разных листов в Excel

Стандартный способ вычисления

Вычисление с помощью Мастера функций

Панель формул

Ручной ввод функции

Расчет среднего значения по условию

А вот еще интересные статьи: