☰

평균에 가장 가까운 숫자를 찾는 방법

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2010
평균에 가장 가까운 숫자를 찾는 방법

평균에 가장 가까운 값을 찾고 싶은 이와 같은 샘플이 있습니다.

도시와 무게는 두 개의 별도 열입니다.

city  weight
A      23
A      22
A      45
A      97
B      34
B      22
B      23
C      76
C      23
C      23

피벗을 만들어 A-의 평균 체중인 46.75를 계산했습니다.

이 경우 A에 가장 가까운 숫자인 45를 찾아야 합니다.

색인 및 일치 항목을 사용해야 할 것 같은데, 도시 이름이 중복되고 가중치 값이 다른 행이 17,000개 있으면 어떻게 해야 합니까?

도움을 주시면 감사하겠습니다.

그래서 내가 찾고 있는 대답은

Row Labels  Average of WEIGHT     nearest number
A                 46.75            45
B                 38.75            34
C                  23              23

대부분의 유사한 답변은 이 세트를 사용하지 않습니다. 제가 시도한 이 공식을 설정하도록 도와주세요.

INDEX(rawdata,MATCH(MIN(ABS(weight-$B2)),ABS(weight-$B2),0),2)

그러나 AC의 전체 무게 배열을 살펴봅니다. 나는 A의 평균을 비교할 때 A의 값만 보고 싶습니다.

그리고 B의 평균을 비교할 때 B의 가중치는,

등등....

내 공식에 어떤 문제가 있는지 알려주세요.

미리 감사드립니다

답변1

편집하다:

죄송합니다. 귀하의 질문을 제대로 읽지 못했는데, 방금 Weight평균에 가장 가까운 값을 찾고 싶다고 명확하게 말씀하신 것을 깨달았습니다.도시의 가치 중평균을 계산한 것입니다. 그래서 아래 답변을 업데이트했습니다.

발견한 것 같군요XOR LX의 답변비슷한 질문에 대해 답변을 드렸는데 거의 맞으셨습니다.

XOR LX는 정렬되지 않은 데이터를 검색할 때의 한계를 극복하는 매우 깔끔한 작은 공식을 사용했습니다 MATCH(). 아래에서 어떻게 작동하는지 설명하겠습니다.

아래 표시된 데이터 테이블에서 다음을 사용하여 평균을 계산했습니다.

=AVERAGEIF(A$2:A$11,A14,B$2:B$11)(위에 표시된 것과 다른 답변을 얻었습니다.)

Weight다음을 사용하여 평균에 가장 가깝습니다 .

=INDEX((A$2:A$11=A14)*(B$2:B$11),MATCH(TRUE,(A$2:A$11=A14)*ABS(B$2:B$11-B14)=MIN(IF(A$2:A$11=A14,ABS(B$2:B$11-B14))),0))

참고로 이는 배열 수식이므로 CTRLShiftEnter로만 입력하는 것이 아니라 으로 입력해야 합니다 Enter.

여기에 이미지 설명을 입력하세요 ______________________________________________________________________________

작동 방식:

ABS(B$2:B$11-B14)목록에 있는 모든 숫자와 평균 사이의 차이의 배열입니다 Weight. 그리고 는 같음이 있는 값 의 (A$2:A$11=A14)배열입니다 . 이 두 가지를 함께 곱하면 에 해당하는 위치의 차이 배열이 제공됩니다 .True/FalseTrueCityA14City = A140

다음으로 우리는 이러한 차이점의 최소값을 찾고 싶지만 약간 다른 배열을 만들어야 합니다. 왜냐하면 배열에 있는 경우 MIN()반환하기 때문입니다.00's

IF(A$2:A$11=A14,ABS(B$2:B$11-B14))위치를 확인 하고 다른 위치 와 해당 위치 City = A14간의 차이 Weight와 평균을 반환합니다.False

해당 배열의 최소값을 취하면 MIN(IF(A$2:A$11=A14,ABS(B$2:B$11-B14)))가장 작은 차이가 제공됩니다 .해당 직위에 대해서만 City = A14.

이제 동등성은 현재의 가장 작은 차이 위치에 있는 값 (A$2:A$11=A14)*ABS(B$2:B$11-B14)=MIN(IF(A$2:A$11=A14,ABS(B$2:B$11-B14)))의 배열을 제공합니다 . 의 위치 (가장 가까운 숫자의 위치)를 찾아 실제 값을 반환하기 위해 에 입력됩니다.True/FalseTrueCityMATCH()TrueINDEX()

이것이 도움이 되기를 바랍니다. 행운을 빕니다.

관련 정보