☰

스프레드시트에서 평균 및 표준 편차 출력을 어떻게 적절하게 정렬합니까?

tag-icon • tag-icon microsoft-excel worksheet-function spreadsheet libreoffice-calc
스프레드시트에서 평균 및 표준 편차 출력을 어떻게 적절하게 정렬합니까?

지금 당장은 이 질문을 공식화하는 최선의 방법을 확신할 수 없으므로 난수를 사용하는 예를 사용하겠습니다. 각 ID가 n=1, n=2, ... 등이 될 수 있도록 ID에 할당된 값부터 시작합니다.

ID  Value   
1   1235        
1   326     
1   567     
2   768     
2   646     
3   4367        
3   346     
3   35      
4   436     
5   3467        
5   46      
6   3467        
6   3532        
6   457     
7   3463        
7   3463
7   9328
7   2498

등

Excel/Calc에서 평균 및 SD를 계산하여 값이 올바르게 정렬되도록(이상적으로는 병합된 셀이 있음) 입력 셀 1개, 2개, 3개... 등의 입력 셀, 출력 셀 1개를 고려하고 싶습니다.

예시 스크린샷:

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

내가 얻고 싶은 것. AVG 및 SD는 (임의의) 주어진 데이터에 대한 적절한 값을 의미합니다. 따라서 AVG와 SD가 올바르게 정렬됩니다.]1

즉, 서로 다른 n을 고려하여 평균과 SD를 계산하여 적절하게 정렬/형식을 지정하는 자동화된 방법을 원합니다.

쉽게 할 수 있는 방법이 있을 텐데 지금은 아무것도 모르겠습니다. -_-

어떤 제안이라도 감사하겠습니다.

답변1

Column이 정렬되어 있다고 가정하면 이는 어렵지 않으므로 AColumn의 연속 범위를 다루고 있습니다 B (귀하의 질문의 시각적 측면에서 알 수 있듯이). E다음 수식을 사용하여 Column을 도우미 열로 설정합니다 .

  • E2→=IF(A2=A3, E3, ROW())

각 행에 대해 현재 행이 있는 범위의 마지막 행을 식별합니다. 그런 다음 원하는 결과를 얻을 수 있습니다.

  • B2→=IF(A1=A2, "", AVERAGE(B2:INDIRECT("B" & E2)))
  • C2→=IF(A1=A2, "", STDEV(B2:INDIRECT("B" & E2)))

(또는 표준 편차를 계산하기 위해 원하는 방법을 사용하십시오.) 범위의 첫 번째 행인지 확인합니다. 그렇다면 INDIRECT()함수를 사용하여 현재 셀과 동일한 ID 값을 가진 마지막 셀 사이의 범위를 구성합니다.

       

물론 Column 을 숨기 E거나 보이지 않는 일부 열(예: Z)을 도우미 열로 사용할 수도 있습니다. 이 솔루션은 배열 수식을 사용하지 않습니다.

답변2

이것은 정확히 당신이 요청한 것이 아니지만 피벗 테이블을 사용하겠습니다.

평균 및 표준편차가 포함된 피벗 테이블

(첫 번째 열 머리글을 ID로 변경하고 두 번째와 세 번째 열의 서식을 소수점 이하 두 자리만 표시하도록 지정했습니다. 그렇지 않으면 피벗 테이블 작성기에 표시되는 것과 똑같습니다.)

다음을 배치하여 요청한 것을 얻을 수 있습니다.

=IF($A2=$A1,"",AVERAGEIF($A2:$A19,$A2,$B2:$B19))

C2에서, 그리고:

=IF($A2=$A1,"",STDEV.P(INDIRECT("R"&MATCH($A2,$A:$A,0)&"C2:R"&MATCH($A2,$A:$A,1)&"C2",0)))

D2에서 두 열을 모두 채웁니다. 각 수식의 외부 IF는 특정 ID를 포함하는 첫 번째 행에만 값을 배치하는 것입니다. C2 공식의 나머지 부분은 간단해야 합니다. AVERAGEIF는 특정 기준이 충족되는 숫자의 평균을 구합니다. 이 경우 첫 번째 열을 살펴보고 첫 번째 열에서 현재 행의 값과 동일한 값을 가진 숫자를 선택한 다음 두 번째 열에서 해당 숫자의 평균을 구합니다.

불행히도 "STDEVIF"는 없습니다(적어도 Mac의 Excel 2011에는 사용 중인 스프레드시트 프로그램에 있을 수 있습니다. 그렇다면 C2 수식에서 AVERAGE 대신 사용하십시오). :-). 방법은 표준 편차를 구하려는 셀 범위를 찾고 해당 셀에 대한 참조를 구성한 다음 해당 참조를 STDEV.P에 전달하는 것입니다. 범위는 다음을 구하여 구성됩니다.첫 번째열 1의 현재 행 값과 동일한 값을 가진 열 1의 행에서 다음을 찾습니다.마지막열 1의 현재 행 값과 동일한 값을 가진 열 1의 행. 이 두 값은 사용하려는 열 1의 하위 범위의 위쪽과 아래쪽을 나타내므로 문자열로 R1C1 스타일 참조를 구성하고 INDIRECT를 사용하세요. 이를 실제 참조로 변환한 다음 이를 STDEV.P에 전달합니다. 단순한! :-) 좋아요, 조금 끔찍하지만 작동합니다.

답변3

Excel에는 이와 같은 기능이 내장되어 있지 않습니다. 이후 작업을 수행하지 않는 소계 또는 피벗 테이블을 사용해야 합니다.

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

수식을 사용하여 테이블을 작성하려면 다음 두 함수를 사용하십시오.

C2에 넣어

=IF(A2<>A1,AVERAGEIF($A$2:$A$13,A2,$B$2:$B$13),"")

D2에서 Enter를 눌러 입력하세요.ctrl+shift+enter

=IF(A2<>A1,STDEV(IF($A$2:$A$13=A2,$B$2:$B$13)),"")

그런 다음 이 수식을 아래로 복사하세요.

시작 부분의 IF(A2<>A1...은 기본적으로 열 A가 이 행과 위 행 사이에 다른 경우에만 무언가를 표시한다고 말합니다.

Averageif는 여러분이 생각하는 것과 똑같이 작동합니다.

D열은 배열 수식이므로 먼저 범위의 각 셀에 대해 if 문을 수행하고 각 셀에 대해 (1,14,13,3,FALSE,FALSE...)와 같은 배열을 반환합니다. 그런 다음 기본적으로 FALSE 값을 무시해야 하는 표준 편차를 계산합니다.

이 방법은 데이터가 ID를 기준으로 정렬되어 있다고 가정합니다. 평균 및 표준 편차 계산은 정렬되지 않은 경우 옳지만 처음뿐만 아니라 ID가 변경될 때마다 표시됩니다.

관련 정보