Excel: Múltiplos Critérios de CORRESPONDÊNCIA (com um IF lançado)

Question

Preciso calcular a pontuação geral para cada linha da tabela 3 da seguinte maneira:

1. Pegue o número Round e Pair de Table3 e encontre a linha correspondente em Table1 , que corresponde aos dois. Pegue o valor Score . (Por exemplo, para a linha 5 de Table3 , é a terceira linha de Table1 .) No entanto, se o par estiver listado na coluna Pair2 , então pegue a pontuação com o sinal +/- invertido. (Por exemplo, para a linha 3 de Table3 , será a primeira linha de Table1 , mas o valor obtido será -5 .) Vamos chamar isso de Valor1 .

2. Corresponde o número Round da linha (de Table3 ) ao Round number de Table2 e obtenha o valor Score Average . Vamos chamar isso de Valor2 .

3. O Overall Score dessa linha é Value1 - Value2 .

Exemplo: Para a terceira linha: A Pontuação Geral da B1 na linha 3 é: -5 - (- 1.25) = - 3.75

Como eu combino vários critérios MATCH e cuido da condição IF ao longo do caminho?

    

Answer 1

Isso não pode ser feito com apenas MATCH() e IF() . Você precisa usar uma fórmula usando matrizes.

InsiraafórmulaaseguirnointervaloK3:K26:

=SUMPRODUCT(Table1[Score]*(Table3[[#This Row],[Round]]=Table1[Round])*(Table3[[#This Row],[Pair]]=Table1[Pair1])-Table1[Score]*(Table3[[#This Row],[Round]]=Table1[Round])*(Table3[[#This Row],[Pair]]=Table1[Pair2]))-INDEX(Table2[Score Average],MATCH(Table3[[#This Row],[Round]],Table2[Round],0))

A fórmula acima funcionará no Excel 2007+. A seguinte versão mais fácil de ler funciona para o Excel 2010+:

=SUMPRODUCT(Table1[Score]*([@[Round]]=Table1[Round])*([@[Pair]]=Table1[Pair1])-Table1[Score]*([@[Round]]=Table1[Round])*([@[Pair]]=Table1[Pair2]))-INDEX(Table2[Score Average],MATCH([@[Round]],Table2[Round],0))

A fórmula prettificada é a seguinte:

=
SUMPRODUCT(
  Table1[Score]*([@[Round]]=Table1[Round])*([@[Pair]]=Table1[Pair1])
  -Table1[Score]*([@[Round]]=Table1[Round])*([@[Pair]]=Table1[Pair2])
  )
-INDEX(Table2[Score Average],MATCH([@[Round]],Table2[Round],0))