Calculando Mediana de uma coluna para linhas nas quais duas outras colunas correspondem aos critérios

Question

Infelizmente, o Excel não permite o cálculo médio em tabelas dinâmicas, por isso estou tentando fazer algo semelhante com uma fórmula.

Para o meu exemplo, tenho 5 colunas de dados (A-E).

Minhas colunas A e amp; B tem diferentes variáveis de classificação de dados. A coluna E tem os dados para os quais preciso da mediana. No entanto, quero filtrar para determinados valores de A & B para calcular a mediana das linhas apenas com esses valores filtrados (como eu faria em um pivô)

Qualquer um sabe a melhor fórmula que dizendo quando A = xe quando B = y, calcule a Mediana da coluna E. Portanto, somente para as linhas que A = x e B = y são verdadeiras. Eu tentei algumas fórmulas IF, mas elas não parecem dar a resposta correta.

obrigada!

    

Answer 1

Nos arquivos de ajuda do MEDIAN , vemos que" Se um argumento de matriz ou referência contiver texto, valores lógicos ou células vazias, esses valores serão ignorados; no entanto, as células com o valor zero serão incluídas " Portanto, uma solução seria (digamos, na coluna F) definir uma fórmula IF que retorna o valor na coluna E se os critérios forem atendidos e "" ou similar se os critérios não forem atendidos. Então, basta tomar a mediana da coluna F.

Suponha que eu queira saber a fofura mediana apenas de cães de olhos castanhos:

Oquedáoresultadonecessário:

Estanãoéumasoluçãobrilhante,maséviável.Vocêconseguevermuitoexplicitamentequaiscélulasforamselecionadas,assimvocêpodeverimediatamentesevocêusouoIF,masadesvantageméquevocêpodepreferirnãoterumacolunausadadessamaneira.

Ométodo"mais inteligente" é usar uma fórmula de matriz, mas isso não oferece uma verificação visual de quais células você selecionou:

=MEDIAN(IF((A2:A9="Dog")*(B2:B9="Brown"),E2:E9,""))

Lembre-se de pressionar CONTROL-SHIFT-ENTER para terminar de inserir a fórmula de matriz; a chave de entrada sozinha não é suficiente. O IF na fórmula da matriz seleciona efetivamente apenas as células em E2: E9 que correspondem aos critérios, e a MEDIAN será aplicada apenas às células desejadas. Isso significa que você pode fazer algo assim para obter uma tabela de medianas de acordo com vários critérios:

Tomecuidadocomasreferênciasdecélulasabsolutaserelativasparafacilitaroarrastodafórmula!Porexemplo,afórmuladematrizemH2deveseraseguinte;Observeque,emH$1,alinhaéfixa,masacolunavariaàmedidaquevocêarrastaeocontrárioocorreem$G2.

=MEDIAN(IF(($A$2:$A$9=H$1)*($B$2:$B$9=$G2),$E$2:$E$9,""))

Isso fornece uma tabela útil de resultados.