Формула Excel не работает со ссылками на столбцы

Question

Привет,

У меня есть 7 результатов футбольных матчей:

             B   D           

             Team A       
2001        0   - 12      
2002        1   - 5        
2003        1   - 11        
2004        4   - 1         
2005        1   - 6  
2006        0   - 5
2007        1   - 2

и эта формула, которая дает мне лучший футбольный счет из них

=INDEX(B2:B8,SUMPRODUCT((B2:B8-D2:D8*1.01=MAX(B2:B8-D2:D8*1.01))*ROW(B2:B8))-ROW(B1))&" - "&INDEX(D2:D8,SUMPRODUCT((B2:B8-D2:D8*1.01=MAX(B2:B8-D2:D8*1.01))*ROW(B2:B8))-ROW(B1))

До сих пор это прекрасно работает, поскольку приведенное значение равно 1-2. Однако, так как я собираюсь добавить оценки ниже B8, мне нужно изменить эту формулу, чтобы она всегда ссылалась на последнюю ячейку с данными. Итак, я попробовал это:

=INDEX(B:B,SUMPRODUCT((B:B-D:D*1.01=MAX(B:B-D:D*1.01))*ROW(B:B))-ROW(B1))&" - "&INDEX(D:D,SUMPRODUCT((B:B-D:D*1.01=MAX(B:B-D:D*1.01))*ROW(B:B))-ROW(B1))

но это не сработало. Как я могу это исправить?

Спасибо.

Answer 1

Занимая свободно от ответа я связал в комментариях ... Откройте диспетчер имен (Ctrl +F3) и определите два имени. Определите range1 как:

=Sheet1!$B$2:INDEX(Sheet1!$B:$B,MATCH(9.9E+307,Sheet1!$B:$B,1))

и range2 как

=Sheet1!$D$2:INDEX(Sheet1!$D:$D,MATCH(9.9E+307,Sheet1!$D:$D,1))

Затем вы можете использовать исходную формулу с заменой диапазонов именованными диапазонами, которые вы только что создали:

=INDEX(range1,SUMPRODUCT((range1-range2*1.01=MAX(range1-range2*1.01))*ROW(range1))-ROW(B1))&" - "&INDEX(range2,SUMPRODUCT((range1-range2*1.01=MAX(range1-range2*1.01))*ROW(range1))-ROW(B1))

Формула бонуса:

=INDEX(range1,MATCH(MAX(range1-range2),range1-range2,0))&" - "&INDEX(range2,MATCH(MAX(range1-range2),range1-range2,0))

Обратите внимание, что это формула массива, и она должна быть подтверждена нажатием Ctrl +Shift +Enter.

Answer 2

Похоже, это работает

=INDEX(B:B, SUMPRODUCT((B:B-D:D*1.01=MAX(B:B-D:D*1.01))*ROW(B:B)))&"-"&INDEX(D:D, SUMPRODUCT((B:B-D:D*1.01=MAX(B:B-D:D*1.01))*ROW(B:B)))

Если вам нужно исключить первую ячейку, этот ответ говорит, что лучший способ - указать номера ячеек вручную (например, B2:B1048576).

Answer 3

Знали ли вы, что ваша формула потерпит неудачу, если в ваших данных окажется более одной строки с общим значением?

Идея реализации одной из настроек для определения последней использованной строки в ваших данных, представленной в ссылке, предоставленной Кайлом, является разумной.

Тем не менее, вы также можете рассмотреть эту альтернативную, более короткую конструкцию, которая будет работать, даже если некоторые из ячеек, на которые делается ссылка, пустые, хотя и с оговоркой, что ни один из результатов из этой части не будет получен:

B2:B100-D2:D100*1.01

отрицательны.

=LOOKUP(1,0/FREQUENCY(0,1/(1+((B2:B100-D2:D100*1.01)))),B2:B100&" - "&D2:D100)

Очевидно, что вы можете изменить верхний ряд, на который ссылается здесь, но не делайте его слишком большим. Такие конструкции (а также SUMPRODUCT AGGREGATE и любая установка требует CSE вычислить по всем ячейкам , переданных им, будь то технически за последние используемые клетки в этих диапазонах или нет.

С уважением