행렬의 연산을 이용한 배열 수식

지식인 질문 중 큰형님 답변

 F:H 범위와 같이 주어진 표를 이용해

노란색 셀에 들어갈 수식을 찾는 문제이다.

처음에는 If 구문을 세 번 쓰고

그 안에서 match 함수를 이용해 찾는다고 생각을 했다. 오른쪽으료 표가 더 길어지만 VBA를 이용하고......

그런데 큰형님의 답은

=MAX(INDEX(($F$2:$H$6=B2)*$F$1:$H$1,))

아주 간결하다.

이 수식이 어떻게 동작하는지 알려면 

엑셀에서 범위의 곱셈이 어떻게 동작하는지 알아야한다.

엑셀의 범위는 수학에서 배우는 행렬과 유사하게 생겼지만 연산은 약간 다르다 

일단 수학에서 말하는 행렬의 연산을 위키백과에서 알아보면 다음과 같다. 

행렬 $${\displaystyle A={\begin{bmatrix}a\\b\end{bmatrix}}}와 $${\displaystyle B={\begin{bmatrix}c&d\end{bmatrix}}}가 있을 때, 이 둘의 곱셈은 다음과 같다.

$${\displaystyle A\times B={\begin{bmatrix}a\\b\end{bmatrix}}\times {\begin{bmatrix}c&d\end{bmatrix}}={\begin{bmatrix}ac&ad\\bc&bd\end{bmatrix}}}

행렬 $${\displaystyle A={\begin{bmatrix}a&b\\c&d\end{bmatrix}}}와 $${\displaystyle B={\begin{bmatrix}e&f\\g&h\end{bmatrix}}}가 있을 때, 이 둘의 곱셈은 다음과 같다.

$${\displaystyle A\times B={\begin{bmatrix}a&b\\c&d\end{bmatrix}}\times {\begin{bmatrix}e&f\\g&h\end{bmatrix}}={\begin{bmatrix}ae+bg&af+bh\\ce+dg&cf+dh\end{bmatrix}}}

그리고, 정방형 행렬이 아닌 행렬의 곱셈을 제타위키에서 찾아보면 다음과 같다.

즉, 앞의 행렬의 '열'과 뒤의 행렬의 '행'이 같으면 곱할 수 있다.

하지만, 엑셀에서 이렇게하면 에러가 난다. 

=A1:B4*D1:F2

이렇게 두 범위를 곱하면 위의 그림과 같이 에러가 수두룩하게 나온다.

그리고, 수학의 행렬 곱셈은 교환법칙이 성립하지 않지만, 엑셀에서 범위의 곱은 위치를 바꿔도 결과가 같다. 

즉, 

=D1:D4*A1:B4

=A1:B4*D1:D4

둘 다 같은 결과가 나온다.

엑셀에서 범위 대 범위의 곱의 예를 몇가지 들어보자. 

=A1:B4*D1:E1

이렇게 곱하면 결과가 깔끔하게 나온다

계산된 결과를 보면, 일단 D1셀은 A열과만 곱을 하고, E1셀은 B열과만 곱을 한 것을 알 수 있다.

그리고 뒤에 나오는 범위는, 행이 하나이므로, 

앞의 범위에 있는 같은 열 위치에 있는 모든 셀과 곱셈을 했다. 

D1은 A1,A2,A3,A4와 모두 곱셈을 했고

E1은 B1,B2,B3,B4와 모두 곱셈을 했다.

 

=A1:B4*D1:E4

이렇게, 앞의 범위와 똑같은 범위와 곱셈을 하게 하면,

각 셀은 자기 셀과 대응되는 위치에 있는 셀과만 곱셈을 하는 것을 볼 수 있다. 

 A1은 D1하고만 곱셈을 했고 D4는 A4하고만 곱셈을 했다.

=A1:B4*D1:D4

첫번째 경우와는 반대로, 열이 하나이고 행은 앞의 범위와 같은 경우이다

이 경우, 열이 하나이므로, 하나 있는 열이 그 옆의 열까지 같이 계산을 하고 있다

D1은 A1과도 곱셈을 하고 B1과도 곱셈을 한다

따라서, 위에서 행이 하나였던 경우와 비교해서 생각해보면,

범위끼리 곱셈을 할 때, 기본적으로 두 범위가 서로 같은 크기이면,

서로의 위치에 대응하는 셀이 일대일로 곱셈을 하고

행이나 열이 하나뿐이면, 그 하나의 행이나 열이 대신해서 나머지 범위와 곱셈을 한다는 것을 알 수 있다.

=A1:B4*D1:E2

이런 경우는 

행이나 열이 하나였으면 그  하나 있는 행이나 열이 대신 했을 계산을, 

행이나 열이 두 개가 되면서, 각자 자기와 대응되는 셀 하나와만 계산을 하게 되므로

계산할 짝이 없는 나머지는 오류를 발생시키게 된다. 

자, 이제 애초의 수식으로 돌아가자.

=MAX(INDEX(($F$2:$H$6=B2)*$F$1:$H$1,))

C2셀의 수식을 예로 들어, 

수식 - 수식 계산을 통해, 단계별로 어떻게 계산이 되는지 살펴보자.

이전에 살펴본 것처럼, 밑줄이 쳐져 있는 부분이 이번 단계에서 계산이 될 예정인 것이고

이탤릭체로 된 부분은 이전단계에서 계산이 끝난 결과이다. 

표 범위와 B2 이름이 같은지 확인하는 과정이다

F2:H6 범위의 값과 b2의 값인 '완자'를 비교한다

밑줄친 부분을 잘 보면, 세미콜론(;)과 쉼표(,)가 섞여있는데, 

세미콜론은 행이 바뀔때 나타나고

쉼표는 열이 바뀔때 나타난다.

즉, 세미콜론이 나올때까지는 모두 같은 행인것이다.

'완자'라는 글자가 있는지 확인해서 TRUE/FALSE 값으로 바뀌었다. 

TRUE/FALSE 는 나중에 계산할 때 1과 0으로 취급된다

이제 F1:H1의 범위와 곱하게 된다

F1:H1의 범위는 행이 하나뿐이므로, 

F1의 값을 첫열 F2:F6의 모든 값과 곱하고

G1의 값을 두번째열 G2:G6의 모든 값과 곱하고

H1의 값을 세번째열 H2:H6의 모든 값과 곱하게 된다.

그 결과, TRUE는 G3에 한 군데 밖에 없었으므로, 

G3의 TRUE와 G1의 3이 곱해서 나온 3 외에 나머지는 모두 0이 된다.

배열관련.xlsx