Tenho alguns dados espalhados por mais de 6.500 linhas e 12 colunas. Algumas das colunas possuem valores de um conjunto de 3 números (colunas de entrada), enquanto outras colunas possuem todos valores exclusivos (colunas de saída).
Estou usando filtros do Excel em colunas de entrada para filtrar algumas linhas com base em conjuntos específicos de entrada. Acredito que os filtros no Excel apenas ocultam as linhas que não satisfazem o filtro. Preciso obter a média das colunas de saída filtradas. Os dados filtrados não têm posicionamento contínuo ou uniforme nas linhas, portanto, não consigo especificar o intervalo (facilmente) para usar a fórmula média.
Como posso obter a média das linhas filtradas?
Dados de amostra -Link
Explicação de dados de exemplo - As colunas A e B (colunas de entrada) obtêm seu valor de um conjunto específico (ou seja, {5,10,15} e {20,40,60} respectivamente). As colunas C e D (colunas de saída) possuem valores exclusivos em todas as células. Suponha que eu coloque o filtro como-
Na coluna A, filtre todas as linhas com value=5. Isso filtra as linhas 1,2,7,10. Agora desejo obter a MÉDIA nessas linhas filtradas de valores nas colunas C e D.
Responder1
A resposta é =SUBTOTAL(1,...). Leva em consideração apenas as células visíveis.
Por exemplo
=SUBTOTAL(1,C1:C12)
=SUBTOTAL(1,C:C)
=SUBTOTAL(1,D1:D12)
=SUBTOTAL(1,D:D)
SUBTOTAL é uma função que permite realizar diferentes tipos de cálculos de agregação (por exemplo, SUM, AVERAGE, COUNT, MAX, etc.), bem como selecionar se deseja ou não incluir valores ocultos manualmente. O primeiro parâmetro especifica qual função de agregação você deseja, bem como se deve ignorar valores ocultos manualmente. Para AVERAGE, um valor de parâmetro 1inclui valores ocultos e 101exclui valores ocultos. Os valores ocultos por meio de filtros são sempre excluídos; portanto, 1ou 101funciona para este exemplo. Você também pode incluir uma lista de intervalos não contíguos; ela não está limitada a uma única especificação de intervalo. Para obter mais explicações sobre o que você pode fazer com SUBTOTAL, consulteesse link
Responder2
Coloque =IF($D$16=A1,C1,"")F1 e arraste até F12.
Depois coloque =AVERAGE(F:F)E16.
E insira o valor “5” em D16.
Feito.
p/s : use if(), or() ou and() se precisar atender a mais de uma condição. Em seguida, use média() para obter o resultado desejado.
Espero que ajude. ( :