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

Вход

Регистрация

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

 

= Мир MS Excel/Учет ячеек в заданном диапазоне при вычислении ср. значения - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
  • Страница 1 из 1
  • 1
Модератор форума: _Boroda_, Manyasha, SLAVICK, китин  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Учет ячеек в заданном диапазоне при вычислении ср. значения (Формулы/Formulas)
Учет ячеек в заданном диапазоне при вычислении ср. значения
kosa4evskiy Дата: Суббота, 03.11.2018, 16:56 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация: 0 ±
Замечаний: 0% ±

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

Праздники проходят мимо меня, в связи с тем что мне не удается сделать рабочую таблицу, прошу советов знатоков.

Предыстория следующая: На работе запустили новый проект, для оценки определили ряд показателей. Самый ключевой то мне и не удается настроить для корректного отображения.
Задача следующая: в ячейке D11 должно отображаться среднее арифметическое значение диапазона (I11:AM11)

Сейчас проблема некорректного расчета возникает из-за наличия лишних нулей в диапазоне (I11:AM11).

В качестве решения вижу такой алгоритм, но не могу исполнить:

1. Требуется формула, считающая вертикальный диапазон напр. (J2:J9) только при условии что все ячейки диапазона заполнены, в противном случае оставить J10 пустой.
2. Производить расчет и отображать результат в J11 только при наличии числа в J10, в противном случае оставлять J11 пустым.

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

Может не с того конца подхожу. Прошу помочь
К сообщению приложен файл: 0435584.xlsx(14.5 Kb)


Сообщение отредактировал kosa4evskiy - Суббота, 03.11.2018, 22:50
 
Ответить
СообщениеВсем доброго времени суток

Праздники проходят мимо меня, в связи с тем что мне не удается сделать рабочую таблицу, прошу советов знатоков.

Предыстория следующая: На работе запустили новый проект, для оценки определили ряд показателей. Самый ключевой то мне и не удается настроить для корректного отображения.
Задача следующая: в ячейке D11 должно отображаться среднее арифметическое значение диапазона (I11:AM11)

Сейчас проблема некорректного расчета возникает из-за наличия лишних нулей в диапазоне (I11:AM11).

В качестве решения вижу такой алгоритм, но не могу исполнить:

1. Требуется формула, считающая вертикальный диапазон напр. (J2:J9) только при условии что все ячейки диапазона заполнены, в противном случае оставить J10 пустой.
2. Производить расчет и отображать результат в J11 только при наличии числа в J10, в противном случае оставлять J11 пустым.

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

Может не с того конца подхожу. Прошу помочь

Автор - kosa4evskiy
Дата добавления - 03.11.2018 в 16:56
bmv98rus Дата: Суббота, 03.11.2018, 17:23 | Сообщение № 2
Группа: Проверенные
Ранг: Старожил
Сообщений: 1398
Репутация: 235 ±
Замечаний: 0% ±

Excel 2013/2016
Может так?
Код
=IFERROR(COUNTIF(J2:J9; ">0")/(COUNTIF(J2:J9; ">0")=8);"")
и
Код
=IF(J10="";"";J10/8)
 
Ответить
СообщениеМожет так?
Код
=IFERROR(COUNTIF(J2:J9; ">0")/(COUNTIF(J2:J9; ">0")=8);"")
и
Код
=IF(J10="";"";J10/8)

Автор - bmv98rus
Дата добавления - 03.11.2018 в 17:23
kosa4evskiy Дата: Суббота, 03.11.2018, 17:41 | Сообщение № 3
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Может так?

=ЕСЛИОШИБКА(СЧЁТЕСЛИ(J2:J9; ">0")/(СЧЁТЕСЛИ(J2:J9; ">0")=8);"")


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

А в предложенной Вами конфигурации, если я правильно вижу результат может быть либо "8" либо ""
 
Ответить
Сообщение
Может так?

=ЕСЛИОШИБКА(СЧЁТЕСЛИ(J2:J9; ">0")/(СЧЁТЕСЛИ(J2:J9; ">0")=8);"")


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

А в предложенной Вами конфигурации, если я правильно вижу результат может быть либо "8" либо ""

Автор - kosa4evskiy
Дата добавления - 03.11.2018 в 17:41
bmv98rus Дата: Суббота, 03.11.2018, 17:56 | Сообщение № 4
Группа: Проверенные
Ранг: Старожил
Сообщений: 1398
Репутация: 235 ±
Замечаний: 0% ±

Excel 2013/2016
А от куда 0-10 должны взяться? Я тупанул, согласен.
Код
=IFERROR(sum(J2:J9)/(COUNTIF(J2:J9; ">0")=8);"")


почему для D11 стразу не использовать
Код
=AVERAGEIF(I11:AM11;">0")


Сообщение отредактировал bmv98rus - Суббота, 03.11.2018, 17:58
 
Ответить
СообщениеА от куда 0-10 должны взяться? Я тупанул, согласен.
Код
=IFERROR(sum(J2:J9)/(COUNTIF(J2:J9; ">0")=8);"")


почему для D11 стразу не использовать
Код
=AVERAGEIF(I11:AM11;">0")

Автор - bmv98rus
Дата добавления - 03.11.2018 в 17:56
kosa4evskiy Дата: Суббота, 03.11.2018, 18:03 | Сообщение № 5
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
От 0 до 8, вот тут я неправильно написал.

почему для D11 стразу не использовать

=СРЗНАЧЕСЛИ(I11:AM11;">0")


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

В том то и загвоздка, что бы убрать из среднего арифметического значения диапазона (I11:AM11) нули от пустых вертикальных диапазонов, что бы в вычислении участвовали только заполненные диапазоны (строка2-строка9)
 
Ответить
СообщениеОт 0 до 8, вот тут я неправильно написал.

почему для D11 стразу не использовать

=СРЗНАЧЕСЛИ(I11:AM11;">0")


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

В том то и загвоздка, что бы убрать из среднего арифметического значения диапазона (I11:AM11) нули от пустых вертикальных диапазонов, что бы в вычислении участвовали только заполненные диапазоны (строка2-строка9)

Автор - kosa4evskiy
Дата добавления - 03.11.2018 в 18:03
TimSha Дата: Суббота, 03.11.2018, 18:12 | Сообщение № 6
Группа: Проверенные
Ранг: Ветеран
Сообщений: 606
Репутация: 92 ±
Замечаний: 0% ±

Excel 2013 Pro +
что бы в вычислении участвовали только заполненные диапазоны

А вы не пробовали иметь классическую плоскую таблицу, и уже по ней строить вычисления - и фильтр проще применить, и пром итоги? Имхо, сводная в таких случаях очень выручает.


"Ctrl+S" - достойное завершение ваших гениальных мыслей!.. ;)
 
Ответить
Сообщение
что бы в вычислении участвовали только заполненные диапазоны

А вы не пробовали иметь классическую плоскую таблицу, и уже по ней строить вычисления - и фильтр проще применить, и пром итоги? Имхо, сводная в таких случаях очень выручает.

Автор - TimSha
Дата добавления - 03.11.2018 в 18:12
bmv98rus Дата: Суббота, 03.11.2018, 18:15 | Сообщение № 7
Группа: Проверенные
Ранг: Старожил
Сообщений: 1398
Репутация: 235 ±
Замечаний: 0% ±

Excel 2013/2016
А так?
Код
=AVERAGEIF(I2:AM9;">=0")
 
Ответить
СообщениеА так?
Код
=AVERAGEIF(I2:AM9;">=0")

Автор - bmv98rus
Дата добавления - 03.11.2018 в 18:15
Светлый Дата: Суббота, 03.11.2018, 18:19 | Сообщение № 8
Группа: Проверенные
Ранг: Ветеран
Сообщений: 707
Репутация: 187 ±
Замечаний: 0% ±

Excel 2010
В ячейку D11 пишем формулу:
Код
=СРЗНАЧЕСЛИ(I11:AM11;">0")
а в 11 строку:
Код
=СЧЁТЕСЛИ(I2:I9;">0")/8


Программировать проще, чем писать стихи.
 
Ответить
СообщениеВ ячейку D11 пишем формулу:
Код
=СРЗНАЧЕСЛИ(I11:AM11;">0")
а в 11 строку:
Код
=СЧЁТЕСЛИ(I2:I9;">0")/8

Автор - Светлый
Дата добавления - 03.11.2018 в 18:19
kosa4evskiy Дата: Суббота, 03.11.2018, 18:35 | Сообщение № 9
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
А вы не пробовали иметь классическую плоскую таблицу, и уже по ней строить вычисления - и фильтр проще применить, и пром итоги? Имхо, сводная в таких случаях очень выручает.


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

А так?

=СРЗНАЧЕСЛИ(I2:AM9;">=0")


Это имеется ввиду пропустить ежедневную оценку (т.е. не считать по каждому отдельному столбцу) и получить данные сразу по всему диапазону заполнения?

Цитата Светлый, 03.11.2018 в 18:19, в сообщении № 8 ()
В ячейку D11 пишем формулу:

=СРЗНАЧЕСЛИ(I11:AM11;">0")
а в 11 строку:

=СЧЁТЕСЛИ(I2:I9;">0")/8


Нули тоже нужно учитывать, просто не во всех случаях, а только если вертикальный диапазон заполнен и по нему результат 0
 
Ответить
Сообщение
А вы не пробовали иметь классическую плоскую таблицу, и уже по ней строить вычисления - и фильтр проще применить, и пром итоги? Имхо, сводная в таких случаях очень выручает.


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

А так?

=СРЗНАЧЕСЛИ(I2:AM9;">=0")


Это имеется ввиду пропустить ежедневную оценку (т.е. не считать по каждому отдельному столбцу) и получить данные сразу по всему диапазону заполнения?

Цитата Светлый, 03.11.2018 в 18:19, в сообщении № 8 ()
В ячейку D11 пишем формулу:

=СРЗНАЧЕСЛИ(I11:AM11;">0")
а в 11 строку:

=СЧЁТЕСЛИ(I2:I9;">0")/8


Нули тоже нужно учитывать, просто не во всех случаях, а только если вертикальный диапазон заполнен и по нему результат 0

Автор - kosa4evskiy
Дата добавления - 03.11.2018 в 18:35
Светлый Дата: Суббота, 03.11.2018, 19:04 | Сообщение № 10
Группа: Проверенные
Ранг: Ветеран
Сообщений: 707
Репутация: 187 ±
Замечаний: 0% ±

Excel 2010
Чтобы нам не гадать на кофейной гуще, напишите числами, что должно быть в ячейках 11 и 10 строки и D11. Задайте разные варианты заполненности столбцов таблицы.


Программировать проще, чем писать стихи.

Сообщение отредактировал Светлый - Суббота, 03.11.2018, 19:07
 
Ответить
СообщениеЧтобы нам не гадать на кофейной гуще, напишите числами, что должно быть в ячейках 11 и 10 строки и D11. Задайте разные варианты заполненности столбцов таблицы.

Автор - Светлый
Дата добавления - 03.11.2018 в 19:04
_Boroda_ Дата: Суббота, 03.11.2018, 19:23 | Сообщение № 11
Группа: Модераторы
Ранг: Местный житель
Сообщений: 13514
Репутация: 5529 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
kosa4evskiy, Прочитайте Правила форума и измените название темы согласно п.2 Правил

Помогающим - deal deal deal


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

Помогающим - deal deal deal

Автор - _Boroda_
Дата добавления - 03.11.2018 в 19:23
kosa4evskiy Дата: Суббота, 03.11.2018, 20:00 | Сообщение № 12
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Цитата Светлый, 03.11.2018 в 19:04, в сообщении № 10 ()
Чтобы нам не гадать на кофейной гуще, напишите числами, что должно быть в ячейках 11 и 10 строки и D11. Задайте разные варианты заполненности столбцов таблицы.


Задача у нас получается следующая:

1. В строке у нас отражается сумма ячеек с положительными числами (за исключением "0") при заполненных строках 2-9 того же столбца, при наличии пустой ячейки в диапазоне 2-9 в ячейке 10 пусто. При этом если в диапазоне 2-9 стоят нули, то значение ячейки №10 - ноль.

2. 11 ячейка остается пустой, если пуста 10 ячейка, либо рассчитывает формулу "10 ячейка / 8"

3. D11 подразумевает среднюю арифметическую значений диапазона I11-AM11 только по заполненных ячеек (в этом как раз сейчас проблема, среднее значение считается с имеющимися нолями, но просто убрать их не могу, потому что таблица ориентирована на фиксацию товарного остатка в магазине и пустая полка обязательно должна учитываться)

kosa4evskiy, Прочитайте Правила форума и измените название темы согласно п.2 Прави


Ищу как переименовать :o
 
Ответить
Сообщение
Цитата Светлый, 03.11.2018 в 19:04, в сообщении № 10 ()
Чтобы нам не гадать на кофейной гуще, напишите числами, что должно быть в ячейках 11 и 10 строки и D11. Задайте разные варианты заполненности столбцов таблицы.


Задача у нас получается следующая:

1. В строке у нас отражается сумма ячеек с положительными числами (за исключением "0") при заполненных строках 2-9 того же столбца, при наличии пустой ячейки в диапазоне 2-9 в ячейке 10 пусто. При этом если в диапазоне 2-9 стоят нули, то значение ячейки №10 - ноль.

2. 11 ячейка остается пустой, если пуста 10 ячейка, либо рассчитывает формулу "10 ячейка / 8"

3. D11 подразумевает среднюю арифметическую значений диапазона I11-AM11 только по заполненных ячеек (в этом как раз сейчас проблема, среднее значение считается с имеющимися нолями, но просто убрать их не могу, потому что таблица ориентирована на фиксацию товарного остатка в магазине и пустая полка обязательно должна учитываться)

kosa4evskiy, Прочитайте Правила форума и измените название темы согласно п.2 Прави


Ищу как переименовать :o

Автор - kosa4evskiy
Дата добавления - 03.11.2018 в 20:00
Светлый Дата: Суббота, 03.11.2018, 21:06 | Сообщение № 13
Группа: Проверенные
Ранг: Ветеран
Сообщений: 707
Репутация: 187 ±
Замечаний: 0% ±

Excel 2010
Расчёт среднего значения с условиями заполненности таблицы - подойдёт?


Программировать проще, чем писать стихи.
 
Ответить
СообщениеРасчёт среднего значения с условиями заполненности таблицы - подойдёт?

Автор - Светлый
Дата добавления - 03.11.2018 в 21:06
kosa4evskiy Дата: Суббота, 03.11.2018, 21:28 | Сообщение № 14
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Цитата Светлый, 03.11.2018 в 21:06, в сообщении № 13 ()
Расчёт среднего значения с условиями заполненности таблицы - подойдёт?


Не совсем понимаю как это
 
Ответить
Сообщение
Цитата Светлый, 03.11.2018 в 21:06, в сообщении № 13 ()
Расчёт среднего значения с условиями заполненности таблицы - подойдёт?


Не совсем понимаю как это

Автор - kosa4evskiy
Дата добавления - 03.11.2018 в 21:28
Светлый Дата: Суббота, 03.11.2018, 22:13 | Сообщение № 15
Группа: Проверенные
Ранг: Ветеран
Сообщений: 707
Репутация: 187 ±
Замечаний: 0% ±

Excel 2010
Не совсем понимаю как это

1. Вы считаете среднее?
2. Учитываете не все значения? Есть условия?
3 Что непонятно?


Программировать проще, чем писать стихи.
 
Ответить
Сообщение
Не совсем понимаю как это

1. Вы считаете среднее?
2. Учитываете не все значения? Есть условия?
3 Что непонятно?

Автор - Светлый
Дата добавления - 03.11.2018 в 22:13
kosa4evskiy Дата: Суббота, 03.11.2018, 22:28 | Сообщение № 16
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Цитата Светлый, 03.11.2018 в 22:13, в сообщении № 15 ()
1. Вы считаете среднее?
2. Учитываете не все значения? Есть условия?
3 Что непонятно?


Звучит все правильно
 
Ответить
Сообщение
Цитата Светлый, 03.11.2018 в 22:13, в сообщении № 15 ()
1. Вы считаете среднее?
2. Учитываете не все значения? Есть условия?
3 Что непонятно?


Звучит все правильно

Автор - kosa4evskiy
Дата добавления - 03.11.2018 в 22:28
krosav4ig Дата: Суббота, 03.11.2018, 22:40 | Сообщение № 17
Группа: Друзья
Ранг: Старожил
Сообщений: 1698
Репутация: 699 ±
Замечаний: 0% ±

Excel 2007,2010,2013
kosa4evskiy, справа под вашим первым постом кнопка правка (листик с карандашиком)


(_)Õvõ(_)
 
Ответить
Сообщениеkosa4evskiy, справа под вашим первым постом кнопка правка (листик с карандашиком)

Автор - krosav4ig
Дата добавления - 03.11.2018 в 22:40
Светлый Дата: Суббота, 03.11.2018, 23:20 | Сообщение № 18
Группа: Проверенные
Ранг: Ветеран
Сообщений: 707
Репутация: 187 ±
Замечаний: 0% ±

Excel 2010
В 11 строке:
Код
=ЕСЛИ(СЧЁТЕСЛИ(I2:I9;"");"";СЧЁТЕСЛИ(I2:I9; ">0")/8)
А в D11:
Код
=СРЗНАЧ(I11:AM11)


Программировать проще, чем писать стихи.
 
Ответить
СообщениеВ 11 строке:
Код
=ЕСЛИ(СЧЁТЕСЛИ(I2:I9;"");"";СЧЁТЕСЛИ(I2:I9; ">0")/8)
А в D11:
Код
=СРЗНАЧ(I11:AM11)

Автор - Светлый
Дата добавления - 03.11.2018 в 23:20
kosa4evskiy Дата: Воскресенье, 04.11.2018, 00:45 | Сообщение № 19
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Цитата Светлый, 03.11.2018 в 23:20, в сообщении № 18 ()
В 11 строке:

=ЕСЛИ(СЧЁТЕСЛИ(I2:I9;"");"";СЧЁТЕСЛИ(I2:I9; ">0")/8)
А в D11:

=СРЗНАЧ(I11:AM11)


Спасибище огромное!

Работает как нужно!!!
 
Ответить
Сообщение
Цитата Светлый, 03.11.2018 в 23:20, в сообщении № 18 ()
В 11 строке:

=ЕСЛИ(СЧЁТЕСЛИ(I2:I9;"");"";СЧЁТЕСЛИ(I2:I9; ">0")/8)
А в D11:

=СРЗНАЧ(I11:AM11)


Спасибище огромное!

Работает как нужно!!!

Автор - kosa4evskiy
Дата добавления - 04.11.2018 в 00:45
_Boroda_ Дата: Воскресенье, 04.11.2018, 00:56 | Сообщение № 20
Группа: Модераторы
Ранг: Местный житель
Сообщений: 13514
Репутация: 5529 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
kosa4evskiy, я просил прочитать Правила форума? Исправляйте все свои нарушения пункта 5j. Во все постах


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщениеkosa4evskiy, я просил прочитать Правила форума? Исправляйте все свои нарушения пункта 5j. Во все постах

Автор - _Boroda_
Дата добавления - 04.11.2018 в 00:56
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Учет ячеек в заданном диапазоне при вычислении ср. значения (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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