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

Вход

Регистрация

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

 

= Мир MS Excel/Среднее значение по более чем 1-мерному массиву - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Среднее значение по более чем 1-мерному массиву (Формулы/Formulas)
Среднее значение по более чем 1-мерному массиву
dilius Дата: Суббота, 23.12.2023, 16:37 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 38
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Добрый день!
В файле изложил, что хочу. Вообщем нужно вернуть среднее значение по критерию из строк удовлетворяющих условию,
к сожалению срзначиесли и т.п. срабатывают только на 1-ый столбец.
Данные условные представлены, как-то возможно вернуть среднее из всех значений из произвольного массива?
К сообщению приложен файл: vopros.xlsx (9.2 Kb)

Nothing personal

Сообщение отредактировал dilius - Суббота, 23.12.2023, 16:37
 
Ответить
СообщениеДобрый день!
В файле изложил, что хочу. Вообщем нужно вернуть среднее значение по критерию из строк удовлетворяющих условию,
к сожалению срзначиесли и т.п. срабатывают только на 1-ый столбец.
Данные условные представлены, как-то возможно вернуть среднее из всех значений из произвольного массива?
Автор - dilius
Дата добавления - 23.12.2023 в 16:37
i691198 Дата: Суббота, 23.12.2023, 18:45 | Сообщение № 2
Группа: Проверенные
Ранг: Обитатель
Сообщений: 337
Репутация: 108 ±
Замечаний: 0% ±

Добрый вечер. Функция СРЗНАЧЕСЛИ работает по одному столбцу. Самый простой вариант добавить вспомогательный столбец с формулой
Код
=СРЗНАЧ(F6:K6)
и на него ссылаться в основной формуле.
 
Ответить
СообщениеДобрый вечер. Функция СРЗНАЧЕСЛИ работает по одному столбцу. Самый простой вариант добавить вспомогательный столбец с формулой
Код
=СРЗНАЧ(F6:K6)
и на него ссылаться в основной формуле.
Автор - i691198
Дата добавления - 23.12.2023 в 18:45
DrMini Дата: Суббота, 23.12.2023, 18:58 | Сообщение № 3
Группа: Друзья
Ранг: Старожил
Сообщений: 1873
Репутация: 268 ±
Замечаний: 0% ±

Excel LTSC 2024 RUS
Если количество столбцов постоянно то можно вот так:
Код
=СУММПРОИЗВ((C6:C17=M5)*F6:K17)/(СЧЁТЕСЛИ(C6:C17;M5)*6)

Посмотрел на вариант Елены и подправил для автоматического нахождения количества столбцов в массиве (забыл про ЧИСЛСТОЛБ):
Код
=СУММПРОИЗВ((C6:C17=M5)*F6:K17)/(СЧЁТЕСЛИ(C6:C17;M5)*ЧИСЛСТОЛБ(F6:K17))
К сообщению приложен файл: vopros_1.xlsx (10.2 Kb)


Сообщение отредактировал DrMini - Суббота, 23.12.2023, 19:34
 
Ответить
СообщениеЕсли количество столбцов постоянно то можно вот так:
Код
=СУММПРОИЗВ((C6:C17=M5)*F6:K17)/(СЧЁТЕСЛИ(C6:C17;M5)*6)

Посмотрел на вариант Елены и подправил для автоматического нахождения количества столбцов в массиве (забыл про ЧИСЛСТОЛБ):
Код
=СУММПРОИЗВ((C6:C17=M5)*F6:K17)/(СЧЁТЕСЛИ(C6:C17;M5)*ЧИСЛСТОЛБ(F6:K17))
Автор - DrMini
Дата добавления - 23.12.2023 в 18:58
Pelena Дата: Суббота, 23.12.2023, 18:59 | Сообщение № 4
Группа: Админы
Ранг: Местный житель
Сообщений: 19404
Репутация: 4555 ±
Замечаний: ±

Excel 365 & Mac Excel
Вариант
Код
=СУММПРОИЗВ((C6:C17=M5)*F6:K17)/СУММПРОИЗВ((C6:C17=M5)*F6:K17^0)

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеВариант
Код
=СУММПРОИЗВ((C6:C17=M5)*F6:K17)/СУММПРОИЗВ((C6:C17=M5)*F6:K17^0)
Автор - Pelena
Дата добавления - 23.12.2023 в 18:59
Gustav Дата: Суббота, 23.12.2023, 21:31 | Сообщение № 5
Группа: Админы
Ранг: Участник клуба
Сообщений: 2797
Репутация: 1161 ±
Замечаний: ±

начинал с Excel 4.0, видел 2.1
А для версий Excel, имеющих функцию ФИЛЬТР, всё становится проще:
Код
=СРЗНАЧ(ФИЛЬТР(F6:K17;C6:C17=M5))

Для более ранних версий (не имеющих ФИЛЬТРа) еще такая формула массива (ввод по CSE):
Код
=СРЗНАЧ(ЕСЛИОШИБКА(F6:K17*ЕСЛИ(C6:C17=M5;1;"");""))

И даже совсем простая (тоже массивная):
Код
=СРЗНАЧ(ЕСЛИ(C6:C17=M5;F6:K17))


P.S. Разогнавшись мыслью, сотворил формулу для "вырезки" из середины диапазона. Допустим, нужно вычислить среднее для набора значений, отвечающих следующим условиям:
* категория = 4 - в столбце C6:C17
* дата входит в интервал [03.01.2023; 05.01.2023] - в строке F5:K5.
Этим совместным условиям соответствуют два диапазона: H9:J9, H11:J12, среднее значение для которых равно 59:
Код
=СРЗНАЧ(H9:J9;H11:J12)

Ну, а сама формула для такой "вырезки" выглядит так (формула массива):
Код
=СРЗНАЧ(ЕСЛИ((C6:C17=4)*(F5:K5>=--"03.01.2023")*(F5:K5<=--"05.01.2023");F6:K17))

Замечу, что попытка применить вместо перемножения условий логическую функцию И (AND) терпит неудачу и следующая версия формулы, увы, НЕ работает:
Код
=СРЗНАЧ(ЕСЛИ(И(C6:C17=4;F5:K5>=--"03.01.2023";F5:K5<=--"05.01.2023");F6:K17))


P.P.S. "Поддадим" условий - будем искать среднее для такой ситуации:
* категория = 4 - в столбце C6:C17
* дата входит в интервалы [01.01.2023; 02.01.2023] ИЛИ в [05.01.2023; 06.01.2023] - в строке F5:K5.
Этим совместным условиям соответствуют четыре несвязанных диапазона: F9:G9, J9:K9, F11:G12, J11:K12, среднее значение для которых равно 43,75:
Код
=СРЗНАЧ(F9:G9;J9:K9;F11:G12;J11:K12)

При этом сама формула "вырезки" получилась такая (ввод также по CSE):
Код
=СРЗНАЧ(ЕСЛИ((C6:C17=4) * ((F5:K5>=--"01.01.2023")*(F5:K5<=--"02.01.2023") + (F5:K5>=--"05.01.2023")*(F5:K5<=--"06.01.2023")); F6:K17))

МОИ: Ник, Tip box: 41001663842605

Сообщение отредактировал Gustav - Воскресенье, 24.12.2023, 03:06
 
Ответить
СообщениеА для версий Excel, имеющих функцию ФИЛЬТР, всё становится проще:
Код
=СРЗНАЧ(ФИЛЬТР(F6:K17;C6:C17=M5))

Для более ранних версий (не имеющих ФИЛЬТРа) еще такая формула массива (ввод по CSE):
Код
=СРЗНАЧ(ЕСЛИОШИБКА(F6:K17*ЕСЛИ(C6:C17=M5;1;"");""))

И даже совсем простая (тоже массивная):
Код
=СРЗНАЧ(ЕСЛИ(C6:C17=M5;F6:K17))


P.S. Разогнавшись мыслью, сотворил формулу для "вырезки" из середины диапазона. Допустим, нужно вычислить среднее для набора значений, отвечающих следующим условиям:
* категория = 4 - в столбце C6:C17
* дата входит в интервал [03.01.2023; 05.01.2023] - в строке F5:K5.
Этим совместным условиям соответствуют два диапазона: H9:J9, H11:J12, среднее значение для которых равно 59:
Код
=СРЗНАЧ(H9:J9;H11:J12)

Ну, а сама формула для такой "вырезки" выглядит так (формула массива):
Код
=СРЗНАЧ(ЕСЛИ((C6:C17=4)*(F5:K5>=--"03.01.2023")*(F5:K5<=--"05.01.2023");F6:K17))

Замечу, что попытка применить вместо перемножения условий логическую функцию И (AND) терпит неудачу и следующая версия формулы, увы, НЕ работает:
Код
=СРЗНАЧ(ЕСЛИ(И(C6:C17=4;F5:K5>=--"03.01.2023";F5:K5<=--"05.01.2023");F6:K17))


P.P.S. "Поддадим" условий - будем искать среднее для такой ситуации:
* категория = 4 - в столбце C6:C17
* дата входит в интервалы [01.01.2023; 02.01.2023] ИЛИ в [05.01.2023; 06.01.2023] - в строке F5:K5.
Этим совместным условиям соответствуют четыре несвязанных диапазона: F9:G9, J9:K9, F11:G12, J11:K12, среднее значение для которых равно 43,75:
Код
=СРЗНАЧ(F9:G9;J9:K9;F11:G12;J11:K12)

При этом сама формула "вырезки" получилась такая (ввод также по CSE):
Код
=СРЗНАЧ(ЕСЛИ((C6:C17=4) * ((F5:K5>=--"01.01.2023")*(F5:K5<=--"02.01.2023") + (F5:K5>=--"05.01.2023")*(F5:K5<=--"06.01.2023")); F6:K17))
Автор - Gustav
Дата добавления - 23.12.2023 в 21:31
dilius Дата: Воскресенье, 24.12.2023, 05:56 | Сообщение № 6
Группа: Пользователи
Ранг: Новичок
Сообщений: 38
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Gustav, Pelena, DrMini, i691198 друзья спасибо! Эти варианты я прорабатывал, но мне нужно какое-то общее решение из динамического массива:
Я выбираю категорию из списка, задаю дату, задаю сколько дней назад учитывать значения и мне возращается общее среднее по категории.
К сообщению приложен файл: vopros_2.xlsx (9.8 Kb)

Nothing personal
 
Ответить
СообщениеGustav, Pelena, DrMini, i691198 друзья спасибо! Эти варианты я прорабатывал, но мне нужно какое-то общее решение из динамического массива:
Я выбираю категорию из списка, задаю дату, задаю сколько дней назад учитывать значения и мне возращается общее среднее по категории.
Автор - dilius
Дата добавления - 24.12.2023 в 05:56
DrMini Дата: Воскресенье, 24.12.2023, 06:54 | Сообщение № 7
Группа: Друзья
Ранг: Старожил
Сообщений: 1873
Репутация: 268 ±
Замечаний: 0% ±

Excel LTSC 2024 RUS
Цитата dilius, 24.12.2023 в 05:56, в сообщении № 6 ()
Я выбираю категорию из списка, задаю дату, задаю сколько дней назад учитывать значения

Чем дальше - тем интереснее и интереснее.
Вам же Gustav, уже всё Ваши хотелки придумал.
К сообщению приложен файл: 787.xlsx (10.6 Kb)


Сообщение отредактировал DrMini - Воскресенье, 24.12.2023, 07:14
 
Ответить
Сообщение
Цитата dilius, 24.12.2023 в 05:56, в сообщении № 6 ()
Я выбираю категорию из списка, задаю дату, задаю сколько дней назад учитывать значения

Чем дальше - тем интереснее и интереснее.
Вам же Gustav, уже всё Ваши хотелки придумал.
Автор - DrMini
Дата добавления - 24.12.2023 в 06:54
dilius Дата: Воскресенье, 24.12.2023, 14:32 | Сообщение № 8
Группа: Пользователи
Ранг: Новичок
Сообщений: 38
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
DrMini, Спасибо! Возьму в работу - данный шаблон.

Nothing personal
 
Ответить
СообщениеDrMini, Спасибо! Возьму в работу - данный шаблон.
Автор - dilius
Дата добавления - 24.12.2023 в 14:32
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Среднее значение по более чем 1-мерному массиву (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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