У меня есть некоторые данные, распределенные по 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(), чтобы получить желаемый результат.
Надеюсь, поможет. ( :