☰

열의 가장 최근 값 5개를 가장 오래된 값에서 빼는 Excel 수식이 필요합니다.

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2016
열의 가장 최근 값 5개를 가장 오래된 값에서 빼는 Excel 수식이 필요합니다.

저는 근력 및 컨디셔닝 코치이며 선수 평가 결과를 분석하는 데 도움이 필요합니다. 가장 최근 결과에서 5개 테스트 결과 중 가장 오래된 결과를 빼는 공식을 작성하는 데 도움이 필요합니다. 시트에는 해당 테스트에 실제로 참여했는지에 관계없이 모든 선수가 각각 5개의 행(1학기, 2학기, 3학기, 4학기 및 여름)이 있는 거의 3000개의 행이 있습니다. 따라서 수식은 기본적으로 열 아래로 내려가 5개 셀 중 첫 번째 사용 가능한 값을 가져와 5개 셀 중 마지막 사용 가능한 값에서 빼야 합니다. 시트는 다음과 같이 설정됩니다.

스크린샷

도와주세요! 이것이 충분한 정보를 제공하지 못한다면 죄송합니다. 저는 이 사이트를 처음 접했습니다.

답변1

CHANGE라는 제목의 회색 행에 넣으면 작동하는 공식이 있습니다.

그것은 두지이며 다음과 같습니다.

=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()인덱스로 변환합니다.
  • 따라서 첫 번째 호출에서 가장 최근의 0이 아닌 값이 남고 INDEX()두 번째 호출에서 가장 먼 0이 아닌 값을 뺍니다.INDEX()

스크린샷

변경 행 계산

스프레드시트 링크

여기이 샘플 스프레드시트를 다운로드할 수 있는 링크입니다. 저는 보통 FormulaChop을 사용하여 이러한 수식을 설명하는데 ROW() 함수에서는 작동하지 않습니다. (전체 공개: FormulaChop을 작성했습니다). 그래서 작동하는 공식 버전을 포함시켰고 FormulaChop 탭에서 세부적으로 볼 수 있습니다.

답변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))

답변3

이것이 당신이 찾고 있는 것이기를 바랍니다.

[ 그림 1]

그림에는 두 명의 사용자가 있습니다. 노란색 데이터는 시트에 있는 것이고(내 생각에는 많은 사용자가 있는 것 같습니다) 간단한 수식을 사용하여 이를 평가할 수 있는 다른 열이 있습니다.

사용된 공식은 아래 사진과 같습니다.

답변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

'두 번째 숫자 찾기 n5 > 0이면 SecNum = n5 index2 = 5 ElseIf n4 > 0이면 SecNum = n4 index2 = 4 ElseIf n3 > 0이면 SecNum = n3 index2 = 3 ElseIf n2 > 0이면 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를 보여주는 그림

관련 정보