Нужна формула Excel, которая вычтет самое последнее из 5 значений в столбце из самого старого

Question

Я тренер по силовой и кондиционной подготовке и мне нужна помощь с разбивкой результатов оценки моих спортсменов. Мне нужна помощь в написании формулы, которая вычтет самые старые из 5 результатов тестов из самых последних. Таблица содержит почти 3000 строк, и каждому спортсмену отведено по 5 строк (семестр 1, 2, 3, 4 и лето), независимо от их фактического участия в указанном тестировании. Таким образом, формула в основном должна проходить по столбцу и брать первое доступное значение из 5 ячеек и вычитать его из последнего доступного значения из 5 ячеек. Таблица настроена следующим образом:

Пожалуйста, помогите! Извините, если это не дает достаточно информации, я новичок на сайте.

Answer 1

У меня есть формула, которая работает, если вставить ее в любую из этих серых строк с заголовком ИЗМЕНИТЬ.

Это просто чушь, выглядит вот так:

=INDEX(Values, SMALL(INDEX((Values<>0)*ROW(Values), 0), ROWS(Values)) - ROW(StartValue) + 1,1)- INDEX(Values, SMALL(INDEX((Values<>0)*ROW(Values), 0), COUNTIF(Values, "")+1) - ROW(StartValue)+1,1)

Где

• Valuesэто диапазон значений, в моем примере этоH5:H9
• StartValueэто первая ячейка в диапазоне значений, для меня этоH5

Как это работает

• Оператор INDEX((Values<>0)*ROW(Values), 0)возвращает массив, если 0ячейка пуста, и номер строки ячейки, если она не пуста.
• Функция SMALL()сортирует их в порядке возрастания, а затем возвращает значение в массиве, соответствующее второму аргументу.

Итак, в приведенном ниже примере таблицы INDEX((Values<>0)*ROW(Values), 0)возвращается

0
6
7
8
0

SMALL()сортирует его так, чтобы он выглядел следующим образом:

0
0
6  <- Row of first nonzero value, second argument to SMALL(): =COUNTIF(Values, "")+1 = 3
7
8  <- Row of last nonzero value, second argument to SMALL(): ROWS(Values) = 5

• Теперь, когда мы знаем строку, мы используем INDEX()для получения значения. Строка - ROW(StartValue) + 1переводит строку, возвращенную SMALL()в индекс
• Таким образом, у нас остается самое последнее ненулевое значение из первого вызова, INDEX()и мы вычитаем из него самое отдаленное ненулевое значение из второго вызова.INDEX()

Скриншот

Ссылка на электронную таблицу

Здесьссылка для загрузки этого примера таблицы. Обычно я использую FormulaChop для объяснения этих формул, но он не работает с функцией ROW(). (Полное раскрытие информации: я написал FormulaChop). Поэтому я включил версию формулы, которая работает, и вы можете увидеть ее в разобранном виде на вкладке FormulaChop.

Answer 2

Имея даты в столбце A (A2:A3001), имена в столбце B и значения в столбцах C, поместите имя для поиска в F2 и используйте эту формулу в G2.

=SUMIFS(C:C, B:B, F2, A:A, AGGREGATE(14, 7, (A$2:A$3001)/(B$2:B$3001=F2), 1))-
 SUMIFS(C:C, B:B, F2, A:A, AGGREGATE(15, 7, (A$2:A$3001)/(B$2:B$3001=F2), 1))

Answer 3

Надеюсь, это то, что вы ищете?

[ ]

На рисунке у нас есть два пользователя. Желтые данные — это то, что у вас на листе (я думаю, со многими пользователями), а другие столбцы нужны для их оценки с помощью простых формул.

Answer 4

Это может быть то, что вы ищете Изображение, показывающее, как выглядят результаты

Вот как выглядит формула, она очень проста в использовании. Картинка с формулами

Как создать формулу? Нажмите Alt+F11 и скопируйте и вставьте следующее

Публичная функция Diff2(n1 как Double, n2 как Double, n3 как Double, n4 как Double, n5 как Double)

индекс1 = 0 индекс2 = 0

'найти первое число If n1 > 0 Then FirstNum = n1 index1 = 1 ElseIf n2 > 0 Then FirstNum = n2 index1 = 1 ElseIf n3 > 0 Then FirstNum = n3 index1 = 3 ElseIf n4 > 0 Then FirstNum = n4 index1 = 4 End If

'найти второе число If n5 > 0 Then SecNum = n5 index2 = 5 ElseIf n4 > 0 Then SecNum = n4 index2 = 4 ElseIf n3 > 0 Then SecNum = n3 index2 = 3 ElseIf n2 > 0 Then SecNum = n2 index2 = 2 End If

'Проверка ошибок If (index1 = 0) Or (index2 = 0) Or (index1 - index2 = 0) Then Diff2 = "ERROR" Else Diff2 = SecNum - FirstNum End If

Конечная функция

Изображение Excel VBA