Excel: несколько критериев MATCH (с добавлением IF)

Question

Мне нужно рассчитать общий балл для каждой строки таблицы 3 следующим образом:

1. Возьмите номер Round и имя Pair из Table3 и найдите соответствующую строку из Table1 которая соответствует обоим. Возьмите значение Score . (Например, для строки 5 Table3 это третий ряд Table1 Однако, если Пара указана в столбце Pair2 , возьмите Счет с инвертированным знаком +/-. (Например, для строки 3 Table3 3 это будет первая строка Table1 , но полученное нами значение будет равно -5 .) Давайте назовем это Value1 .

2. Сопоставьте номер Round в строке (из Table3) с номером Round из Table2 и получите значение Score Average . Давайте назовем это Value2 .

3. Overall Score для этой строки - Value1 - Value2 .

Пример: Для третьей строки: Общая оценка B1 в третьей строке: -5 -(- 1,25)= - 3,75

Как мне объединить несколько критериев MATCH и позаботиться о состоянии IF по пути?

Answer 1

Это не может быть сделано только с помощью MATCH() и IF() . Вам нужно использовать формулу с использованием массивов.

Введите следующую формулу в диапазоне K3: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))

Приведенная выше формула будет работать в Excel 2007+. Следующая версия легче читается работает для 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))

---

Предварительно подтвержденная формула выглядит следующим образом:

=
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))