그룹화된 데이터가 주어지면 Excel을 사용하여 사분위수를 추정하는 방법은 무엇입니까?

Question 1

이는 그룹화된 데이터이므로 원시 데이터가 없으면 데이터의 변형을 재현하기 위해 뭔가를 해야 합니다. 단순화를 위해 우리는 평평한 분포를 가정할 수 있습니다. 개는 0과 5가 될 확률이 동일합니다.

이 REPT()함수를 사용하여 각 그룹을 나타내는 쉼표로 구분된 숫자 목록을 출력합니다.

=REPT(B2&",",C2)  --- where B2 is your group upper bound and C2 is the group frequency

맨 아래에서 각 문자열을 하나의 큰 문자열로 연결합니다.

이 문자열을 복사하고선택하여 붙여넣기~처럼가치새 워크시트에서 사용텍스트를 열로도구데이터리본 탭을 사용하여 데이터를 값당 하나의 열로 분할합니다. 이 전체 범위를 복사한 다음선택하여 붙여넣기그리고바꾸어 놓다이를 수직 값 목록으로 뒤집습니다. 데이터는 다음과 같아야 합니다.

가로 행은 자유롭게 삭제하세요. 필요하지 않습니다. 이제 다음과 같은 수식을 사용하여 B열의 일부 값을 보간하려고 합니다.

=(5*COUNTIF($A$4:A5,A5)/COUNTIF($A$4:$A$5000,A5))+(A5-5)

이를 분석하면 다음과 같습니다.

    =(5*                         -- your groups are increments of 5
       COUNTIF($A$4:A5,A5)       -- how far down a row is within a group
       /                         
       COUNTIF($A$4:$A$5000,A5)  -- what the frequency is for that group
      )                          
      +(A5-5)                    -- add this result to the lower bound

이제 QUARTILE()이 추정 값 목록의 함수를 사용하여 사분위수를 대략적으로 계산할 수 있습니다.

=QUARTILE($B$5:$B$130,1)

Answer

이는 그룹화된 데이터이므로 원시 데이터가 없으면 데이터의 변형을 재현하기 위해 뭔가를 해야 합니다. 단순화를 위해 우리는 평평한 분포를 가정할 수 있습니다. 개는 0과 5가 될 확률이 동일합니다.

이 REPT()함수를 사용하여 각 그룹을 나타내는 쉼표로 구분된 숫자 목록을 출력합니다.

=REPT(B2&",",C2)  --- where B2 is your group upper bound and C2 is the group frequency

맨 아래에서 각 문자열을 하나의 큰 문자열로 연결합니다.

이 문자열을 복사하고선택하여 붙여넣기~처럼가치새 워크시트에서 사용텍스트를 열로도구데이터리본 탭을 사용하여 데이터를 값당 하나의 열로 분할합니다. 이 전체 범위를 복사한 다음선택하여 붙여넣기그리고바꾸어 놓다이를 수직 값 목록으로 뒤집습니다. 데이터는 다음과 같아야 합니다.

가로 행은 자유롭게 삭제하세요. 필요하지 않습니다. 이제 다음과 같은 수식을 사용하여 B열의 일부 값을 보간하려고 합니다.

=(5*COUNTIF($A$4:A5,A5)/COUNTIF($A$4:$A$5000,A5))+(A5-5)

이를 분석하면 다음과 같습니다.

    =(5*                         -- your groups are increments of 5
       COUNTIF($A$4:A5,A5)       -- how far down a row is within a group
       /                         
       COUNTIF($A$4:$A$5000,A5)  -- what the frequency is for that group
      )                          
      +(A5-5)                    -- add this result to the lower bound

이제 QUARTILE()이 추정 값 목록의 함수를 사용하여 사분위수를 대략적으로 계산할 수 있습니다.

=QUARTILE($B$5:$B$130,1)

Question 2

그래픽적으로히스토그램"bins"는 사분위수로 설정됩니다. 어떻게 하는지 찾아보시면 됩니다.

또는 수식을 사용할 수 있습니다. 이것을 말로 설명하면 수학적으로 알아낼 수 있습니다.

개는 126마리입니다. 그 중 25%가 31.5입니다. 당신은 가장 가벼운 개 31.5마리의 몸무게보다 적게 나가는 몸무게를 찾고 싶습니다.

가장 가벼운 상자에는 13마리의 개가 있으므로 그 수가 계산됩니다. 그러면 18.5마리의 개가 남지만 다음 상자에는 28마리의 개가 있습니다. 그래서 질문은: 두 번째 상자에 있는 18.5번째로 가벼운 개의 무게는 얼마입니까?

선형 보간법을 사용하면 알 수 없는 무게가 6~10파운드 사이의 18.5/28이라고 추정할 수 있습니다. 6+4*(18.5/28)입니다.

필요한 경우 동일한 접근 방식을 사용하여 다른 사분위수를 찾을 수 있습니다.

여기의 언어는 약간 어색하지만 아이디어를 얻으실 수 있습니다. 이게 도움이 되길 바란다.

편집: 모든 개별 개들의 체중을 알고 있다면 순서대로 순위를 매기고 31번째와 32번째 개들의 평균 체중을 계산하세요.

Answer