유사한 첫 번째 열 데이터에 대해 Excel 2021에서 데이터의 평균 찾기

Question 1

귀하가 데이터를 그림으로만 제공했다는 점을 고려하여 다음과 같이 범위 내에서 임의의 데이터를 생성했습니다 A1:E17.

ㅏ	비	씨	디	이자형
142	0.02	0.19	0.83	0.14
142	0.65	0.61	0.38	0.82
142	0.60	0.34	0.36	0.37
141	0.98	0.95	0.23	0.97
141	0.83	0.24	0.50	0.67
141	0.02	0.14	0.33	0.23
140	0.33	0.14	0.85	0.64
140	0.78	0.09	0.17	0.06
139	0.08	0.70	0.26	0.26
139	0.25	0.16	0.35	0.67
138	0.52	0.44	0.18	0.44
138	0.21	0.93	0.04	0.23
138	0.66	0.33	0.72	0.39
138	0.91	0.91	0.51	0.30
137	0.39	0.60	0.28	0.46
137	0.43	0.81	0.41	0.14

G2이제 셀 과 H2다음 수식을 각각 입력합니다 . AVERAGEIF()추가 열에 대해 수식을 반복할 수 있습니다 .

=SORT(UNIQUE($A$2:$A$17))
=AVERAGEIF($A$2:$A$17,$G$2#,B2:B17)

이제 몇 가지 추가 설명이 있습니다. 이 UNIQUE()함수를 사용하여 고유 ID를 추출할 수 있습니다. 이 함수는 배열을 반환하며, AVERAGEIF()이 배열은 자동으로 유출되도록 사용할 수 있습니다. 이 SORT()함수는 ID 값을 오름차순으로 배치하는 데 사용됩니다. 함수 의 경우 AVERAGEIF()먼저 ID를 조회하려는 범위(예: 열)를 지정합니다 A. 다음으로 우리가 얻은 배열인 기준을 지정 UNIQUE()하고 마지막으로 평균을 구하려는 열을 지정합니다.

출력은 다음과 같습니다.

ID	평균
137	0.41
138	0.57
139	0.17
140	0.55
141	0.61
142	0.42

모든 평균에 하나의 배열로 액세스하고 하나의 셀에 모든 수식을 포함하는 확장:

사용 사례에 따라 모든 평균을 하나의 배열로 액세스하는 데 관심이 있을 수 있습니다. 이를 달성하려면 AVERAGEIF()함수를 HSTACK(). 그런 식으로 수식은 함수 때문에 아래로 쏟아지고 함수 UNIQUE()때문에 오른쪽으로 쏟아집니다 HSTACK(). 이렇게 하면 단일 셀에 모든 수식이 있으므로 때로는 유지 관리가 더 쉬울 수도 있습니다.

=HSTACK(
AVERAGEIF($A$2:$A$17,$G$2#,B2:B17),
AVERAGEIF($A$2:$A$17,$G$2#,C2:C17),
AVERAGEIF($A$2:$A$17,$G$2#,D2:D17),
AVERAGEIF($A$2:$A$17,$G$2#,E2:E17)
)

수식이 여전히 cell 에 있다고 가정하면 H2다음과 같이 전체 배열에 액세스할 수 있습니다.

=SUM(H2#)

동적 범위를 포함하는 주석 기반 확장:

LET()이를 달성하려면 다음과 같은 명령문을 사용할 수 있습니다 .

=LET(
    lastCol, "E",
    data, INDEX(A:A,2):INDEX(INDIRECT(CONCATENATE(lastCol, ":", lastCol)),
    MAX(IF(A:A<>"", ROW(A:A)))),
    id, SORT(UNIQUE(INDEX(data,2,1):INDEX(data,ROWS(data),1))),
    b, AVERAGEIF(INDEX(data,,1), id, INDEX(data,,2)),
    c, AVERAGEIF(INDEX(data,,1), id, INDEX(data,,3)),
    d, AVERAGEIF(INDEX(data,,1), id, INDEX(data,,4)),
    e, AVERAGEIF(INDEX(data,,1), id, INDEX(data,,5)),
    HSTACK(id,b,c,d,e)
)

먼저 데이터가 포함된 "마지막" 열을 지정합니다. 행 개수는 컬럼을 기준으로 자동 계산되므로 A사용하려는 데이터 이하에 데이터가 없는지 확인해야 합니다. 다른 모든 측면은 이전과 유사하며, 중간 결과를 저장할 수 있기 때문에 LET()명령문으로 래핑되어 데이터 범위에 동적으로 액세스할 수 있습니다 .INDEX()

이 접근 방식은 귀하가 요청한 것을 달성할 것입니다. 그러나 주의할 점은 지금 당장은 각 열에 대해 수식을 반복해야 하기 때문에 많은 열에 맞게 확장할 필요가 없다는 것입니다. 이론적으로는 이 차원을 더욱 동적으로 만드는 것이 가능해야 하지만 여기서는 이 확장의 범위를 벗어납니다.

Answer