☰

MS Excel 2016: Как получить СРЕДНЕЕ число по большому количеству дискретных диапазонов?

tag-icon • tag-icon microsoft-excel microsoft-excel-2016
MS Excel 2016: Как получить СРЕДНЕЕ число по большому количеству дискретных диапазонов?

У меня есть некоторые данные, распределенные по 6500+ строкам и 12 столбцам. Некоторые столбцы имеют значения из набора из 3 чисел (входные столбцы), в то время как другие столбцы имеют все уникальные значения (выходные столбцы).

Я использую фильтры Excel для входных столбцов, чтобы отфильтровать некоторые строки на основе определенных наборов входных данных. Я считаю, что фильтры в Excel просто скрывают строки, которые не удовлетворяют фильтру. Мне нужно получить среднее значение отфильтрованных выходных столбцов. Отфильтрованные данные не имеют непрерывного или равномерного размещения по строкам, поэтому я не могу указать диапазон (легко) для использования формулы среднего.

Как получить среднее значение отфильтрованных строк?

Образец данных -Связь

Пример пояснения данных - Столбцы A и B (входные столбцы) берут свои значения из определенного набора (а именно, {5,10,15} и {20,40,60} соответственно). Столбцы C и D (выходные столбцы) имеют уникальные значения во всех ячейках. Предположим, я устанавливаю фильтр, как-

В столбце A отфильтровать все строки с value=5. Это отфильтрует строки 1,2,7,10. Теперь я хочу получить СРЕДНЕЕ значение для этих отфильтрованных строк значений в столбцах C и D.

решение1

Ответ =SUBTOTAL(1,...): Он учитывает только видимые ячейки.

Например

=SUBTOTAL(1,C1:C12)
=SUBTOTAL(1,C:C)
=SUBTOTAL(1,D1:D12)
=SUBTOTAL(1,D:D)

SUBTOTAL — это функция, которая позволяет выполнять различные виды вычислений агрегации (например, SUM, AVERAGE, COUNT, MAX и т. д.), а также выбирать, включать или нет скрытые вручную значения. Первый параметр указывает, какую функцию агрегации вы хотите, а также следует ли игнорировать скрытые вручную значения. Для AVERAGE значение параметра 1включает скрытые значения, 101исключает скрытые значения. Значения, скрытые с помощью фильтров, всегда исключаются, поэтому для этого примера подходит либо , 1либо 101. Вы также можете включить список несмежных диапазонов, он не ограничивается одной спецификацией диапазона. Для получения дополнительных сведений о том, что можно делать с SUBTOTAL, см.эта ссылка

решение2

Нажмите =IF($D$16=A1,C1,"")F1 и перетащите до F12.

Затем добавьте =AVERAGE(F:F)E16.

И введите значение «5» в D16.

Сделанный.

p/s : используйте if(), or() или and(), если вам нужно выполнить более одного условия. Затем используйте average(), чтобы получить желаемый результат.

Надеюсь, поможет. ( :

Связанный контент