5.8개

블로그에 올린 글에는 내키는대로 간간히 엑셀 수식을 소개하고 있는데,
그러다 문득 생각난...

소개된 수식에 어떤 함수들이 사용되고 있을까?

그래서 간단히 정리 들어가신다~

 

참고로...

Average(범위) = Sum(범위)÷Count(범위)

좌변과 우변의 결과는 같다.
그러나 좌변은 함수가 1개, 우변은 함수가 2개 사용됐는데...
처리 대상은 우변 처럼 하나의 수식에 함수가 2개 이상 함께 적용된 경우로 하겠다.

현재까지 소개한 수식은 총 17개이며, 총 31개의 함수가 사용됐다.
그리고 수식당 사용된 함수는 평균 5.8개.

그런데 정작 일 할 때는 수식 만드는데 이렇게 많은 함수를 사용한 적은 거의 없었다.
두세개면 얻고자 하는 결과는 대충 나오고,
네개를 넘어가면 넘에게 떠넘기는게 상책이다.

참고로 엑셀2003에 도움말에 보면, 총 333개의 함수 목록이 나타난다.
대략 10% 가량의 함수를 사용했다.

'더하기' 시리즈에서 애용한 MMult 함수의 경우 총 6번 소개 됐으며,
MMult 함수가 사용된 수식의 평균 함수 갯수는 7.0개.
또한 MMult 함수가 사용된 수식에서 Sum 함수가 함께 사용된 경우는 83%,
Transpose 함수가 함께 사용된 경우는 100%,
그에 반해 Frequency 함수가 함께 사용된 경우는 17%로 나왔다.

그림을 누르면 크게 나오게 했는데, 뭐 신경써서 볼거 있겠나?
배열수식 위주로 소개하다 보니, Sum 함수가 우두머리라는거 외에...
그런데 의외로 통계와 직접적으로 관련된 함수는 달랑 세개 밖에 없다.

g2 셀 (빈도)
=SUMPRODUCT(N(FREQUENCY(MATCH($A$2:$A$99&$B$2:$B$99,$A$1:$A$99&$B$1:$B$99,0),MATCH($A$2:$A$99&$B$2:$B$99,$A$1:$A$99&$B$1:$B$99,0))>0))

g3 셀 (평균)
=ROWS($C$2:$C$99)/G2

h1 셀 (함수명)
=IF(SUM(N((FREQUENCY(MATCH($C$2:$C$99,$C$2:$C$99,0),MATCH($C$2:$C$99,$C$2:$C$99,0))>0)))>=COLUMN(A$1),INDEX($C$2:$C$99,LARGE(((FREQUENCY(MATCH($C$2:$C$99,$C$2:$C$99,0),MATCH($C$2:$C$99,$C$2:$C$99,0))>0)*(COUNTIF($C$2:$C$99,$C$2:$C$100))=LARGE((FREQUENCY(MATCH($C$2:$C$99,$C$2:$C$99,0),MATCH($C$2:$C$99,$C$2:$C$99,0))>0)*(COUNTIF($C$2:$C$99,$C$2:$C$100)),COLUMN(A$1)))*ROW(INDIRECT("1:"&ROWS($C$2:$C$100))),SUM((LARGE((FREQUENCY(MATCH($C$2:$C$99,$C$2:$C$99,0),MATCH($C$2:$C$99,$C$2:$C$99,0))>0)*(COUNTIF($C$2:$C$99,$C$2:$C$100)),ROW(INDIRECT("1:"&ROWS($C$2:$C$100))))=LARGE((FREQUENCY(MATCH($C$2:$C$99,$C$2:$C$99,0),MATCH($C$2:$C$99,$C$2:$C$99,0))>0)*(COUNTIF($C$2:$C$99,$C$2:$C$100)),COLUMN(A$1)))*(ROW(INDIRECT("1:"&ROWS($C$2:$C$100)))<=COLUMN(A$1))))),"")

h2 셀 (포함 수식)
=COUNTIF($C$2:$C$99,H1)

h3 셀 (평균 함수)
=SUM(N(($A$2:$A$99*100+$B$2:$B$99)=TRANSPOSE(SMALL(IF($C$2:$C$99=H$1,$A$2:$A$99*100+$B$2:$B$99),ROW(INDIRECT("1:"&COUNTIF($C$2:$C$99,H$1)))))))/H2

h4 셀 (비율)
=SUM((($A$2:$A$99*100+$B$2:$B$99)=TRANSPOSE(SMALL(IF($C$2:$C$99=H$1,$A$2:$A$99*100+$B$2:$B$99),ROW(INDIRECT("1:"&COUNTIF($C$2:$C$99,H$1))))))*($C$2:$C$99=$F4))/H$2

그리고 얘깃거리를 만들기 위해 상관계수 행렬을 구했다.

MMult 함수와 Transpose 함수 간에 상관계수는 1 이다.
Transpose 함수는 행렬을 전치시켜 주는 용도이나,
여기선 보통 상/하 삼각행렬 또는 단위 행렬을 생성할 때 주로 이용했다.
즉 이 두 함수는 바늘과 실의 관계로 함수의 기능은 서로 다르지만,
요구되는 결과를 얻기 위해선 '함께 사용하는게 효과적이다'라는 얘기라고 우길 수 있겠다.

그리고 Frequency 함수와 Match 함수 간에 상관계수는 0.9355 이다.
상관관계가 양의 방향으로 매우 높게 나타났다.
Frequency 함수는 빈도를 구하는 기능이기 때문에
Match 함수와 중첩해 사용할 경우는 일반적으론 거의 없으나,
'유일성'을 계산하는 경우 이 두 함수를 중첩하면 아주 딱~ 이다.

이에 반해 Offset 함수와 Index 함수 간에 상관계수는 -0.3790 이다.
이 두 함수의 기능은 비슷하기에 함께 사용되는 경우는 매우 드물고,
또 수식에 함께 사용된 다른 함수들도 서로 다르다는 얘기가 된다.

또 다른 함수로 Match 함수와 Index 함수 간에 상관계수는 0.0871 로 매우 낮게 나타났다.
그러나 Match 함수와 Offset 함수 간에는 0.7182 로 상대적으로 높게 나타났다.
이유는?
통상 Index 와 Match 의 조합이 더 빈번한데, 값이 아닌 범위를 참조하는 수식이
더 많았나 보다.

이렇듯 함수 간에 상관관계를 참고해 어떤 목적으로 수식을 구성했는지를
대충 짐작할 수 있다.

h40 셀 (상관계수)
=IF(ROW(A1)>COLUMN(A1),CORREL(OFFSET($G$4:$G$34,0,MATCH(H$39,$H$1:$AL$1,0)),OFFSET($G$4:$G$34,0,MATCH($F40,$H$1:$AL$1,0))),"")

얘깃거리를 더 많들 순 있겠으나,
그러려면 잊고 사는 다변량분석을 끄집어내야 될거 같으니...
여기서 끝.