MS Excel의 조건부 중위수

MS Excel의 조건부 중위수

다음과 같은 차트의 조건부 중위수를 계산하려고 합니다.

A  |  B
-------
x  |  1
x  |  1
x  |  3
x  |  
y  |  4
z  |  5

저는 MS Excel 2007을 사용하고 있습니다.AVERALIF() 문은 알고 있지만, Median에 해당하는 것은 없습니다.주요 특징은 위의 4번째 "a"와 같이 데이터가 없는 행이 있다는 것입니다.이 경우에는 이 행이 계산에 전혀 반영되지 않았으면 합니다.

구글링은 다음과 같이 제안했지만 엑셀은 (2007년이라 그런지) 공식 형식을 받아들이지 않을 것입니다.

=MEDIAN(IF((A:A="x")*(A:A<>"")), B:B)

Excel에서 제 수식에 문제가 있다는 오류가 발생합니다. 저도 다음과 같이 시도했지만 계산에서 빈 셀을 0으로 계산합니다.

=MEDIAN(IF(A:A = "x", B:B, "")

이 공식들이 엑셀을 "arrays"로 반환한다는 것을 알고 있습니다. 즉, 제대로 작동하려면 "Ctrl-shift-enter"를 입력해야 한다는 것을 의미합니다.

빈 셀을 고려하지 않으면서 조건부 평가를 하려면 어떻게 해야 합니까?

문장이 중첩된 경우.

=MEDIAN(IF(A:A = "x",IF(B:B<>"",B:B, ""),"")

설명할 것이 별로 없습니다. A가 x인지 확인하고, A가 x이면 B가 공백이 아닌지 확인합니다.두 조건이 모두 일치하는 항목은 중위수의 일부로 계산됩니다.

다음 데이터 집합이 주어졌을 때:

A | B
------
x | 
x |     
x | 2
x | 3
x | 4
x | 5

위 공식은 3.5를 돌려주는데, 당신이 원했던 것입니다.

구글 공식을 사용하되 공식 막대에 입력한 후에 치는 대신 ++ShiftEnter를 동시에 누릅니다.이렇게 하면 공식 주위에 브래킷이 배치되고 배열로 처리됩니다.

편집하면 다시 칠 수 없거나 공식이 유효하지 않습니다.편집할 경우, (++)CtrlShiftEnter 완료 시 동일한 작업을 수행해야 합니다.

CtrlShiftEnter 작업이 필요한 배열 공식을 포함하지 않는 또 다른 방법이 있습니다.Excel 2010, 2011 및 그 이후에 제공되는 Aggregate() 기능을 사용합니다.이 방법은 최소, 최대 및 다양한 백분위수에도 적용됩니다.Aggregate()를 사용하면 오류를 무시할 수 있으므로 필요하지 않은 모든 값이 오류를 발생시킵니다.위의 작업 집합을 수행하는 가장 쉬운 방법은 다음과 같습니다.

=각도(16,6,(B:B)/((A:A = "x")*(B:B<>"),0.5)

첫 번째 및 마지막 매개 변수는 씬(scene)이 중위수인 백분위수 50%를 수행하도록 설정하고, 두 번째 매개 변수는 모든 오류(DIV#0 포함)를 무시하고, 세 번째 매개 변수는 B 열 데이터를 선택하고, A 열에 x가 있는 모든 비어 있지 않은 값에 대해 1을 사용하고, 그렇지 않은 경우에는 0을 사용하는 숫자로 나눕니다.0은 0 예외로 분할을 생성하며 a/1=a 및 a/0=Div#0이므로 무시됩니다.

이 기법은 적절한 인수가 있는 큰 함수 또는 작은 함수를 사용하여 사분위수(적절한 p 값을 갖는), 다른 모든 백분위수 및 최대값과 최소값에 대해 작동합니다.

이는 매우 일반적인 Sumproduct() 트릭과 유사한 구성이지만 이러한 함수에 대해 숫자처럼 보이는 0을 생성하기 때문에 어떤 분위수 또는 max min 값에도 사용할 수 없습니다.

밥 조던

좀 더 일반화를 하자면, 이것 대신에...

{=MEDIAN(IF(A:A="x",IF(B:B<>"",B:B)))}

... 다음을 사용할 수 있습니다.

{=QUARTILE.EXC(IF(A:A="x",IF(B:B<>"",B:B)),2)}

곱슬곱슬한 괄호는 배열 공식을 참조합니다. 공식에 괄호를 넣지 말고 공식을 입력할 때 CTRL+SHIFT+ENTER(또는 CMD+SHIFT+ENTER)를 누릅니다.

그러면 마지막 숫자를 2에서 1이나 3으로 바꾸면 쉽게 1, 3분위를 얻을 수 있습니다.사분위수.EXC는 대부분의 상용 통계 소프트웨어(예: Minitab)에서 사용하는 것입니다."정규" 함수는 쿼타일(quartile)입니다.INC 또는 이전 버전의 Excel의 경우에는 쿼타일(quartile)만 사용할 수 있습니다.

언급URL : https://stackoverflow.com/questions/742547/conditional-median-in-ms-excel