[기초방] VBA 100제 #86 [ MMULT 를 이용한 행렬만들기 ]

 

이문제는 사실 SUMIFS로 풀면된다.

SUMIFS의 조건식에 *&조건&* 이렇게 와일드카드를 이용해서  조건이 포함된 셀의 값을 가져오게 된다.

 

365 함수를 계속 풀고 있었기 때문에 이렇게 접근을 안하겠지 했지만.. 역시 걸렸다....ㅜ.,ㅡ

 

이번 문제를 풀면서 MATRIX[행렬]에 대해서 왜 중요한지 알게 된 시간이다.

서랍님은 참 사고의 깊이가 넘사벽임을 또한번 느꼈다.

 

 

이런 대화 너무 좋다...크윽

 

솔직하게 이게 왜 SUMIF 같은 역할을 하게 되는지 행렬의 곱 이야기가 나오기 전까지 전혀 이해를 못하고 있었다.

 

 

=MMULT(--ISNUMBER(SEARCH(G4:G8,TRANSPOSE(C4:C54))),D4:D54)

1. SEARCH(G4:G8,TRANSPOSE(C4:C54))

2. --ISNUMBER(SEARCH(G4:G8,TRANSPOSE(C4:C54)))

3. =MMULT(--ISNUMBER(SEARCH(G4:G8,TRANSPOSE(C4:C54))),D4:D54)

1번에서 SEARCH 함수를 통해서 5 x 51의 배열이 만들어진다. 찾은 값은 해당 위치를 나타나게 되고, 없는 값은 에러를 발생

6	#VALUE!	4	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!
#VALUE!	4	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	9	#VALUE!
#VALUE!	#VALUE!	#VALUE!	7	8	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!
#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	7	8	9	#VALUE!	#VALUE!
#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	4

 

2번에서 찾은값은 숫자가 표현되기 때문에 TRUE / FALSE 값을 반환한다. 여기에 [--]를 붙혀서 TRUE, FALSE 값을 1, 0으로 변환한다.

 

1	0	0	0	0	0	0	0	0	0
0	0	0	0	0	0	1	0	0	1
0	1	1	0	0	0	0	0	0	0
0	0	0	1	1	1	0	0	0	0
0	0	0	0	0	0	0	1	1	0

 

3번에서 MMULT(배열1 , 배열2) 각각의 배열의 곱이 이뤄지는데 , 

 

이렇게 되었을 때 배열1(행1,열1) X 배열2(행2,열2) 의 곱은    [열1과 행2]의 크기가 같아야 하며 이렇게 행렬의 곱을 통해서 행1 X 열2 크기의 새로운 행렬이 나오게 된다.

 

배열1(5, 51) X 배열2(51,1) 이렇게 해서 배열3(5,1) 이 나오게 되는것이고 따라서 SUMIF처럼 5X1의 배열이 만들어져서 각각의 품목의 합이 구해지는 것이다.

 

너무 재밌지 않은가...크윽

 

 

자 이것을 VBA로 해보자

Va = Range([c4], [c4].End(4))
Vb = [g4:g8]
Vc = Range([d4], [d4].End(4))

With Application

    1) Vtemp = .IsNumber(.Search(Vb, .Transpose(Va)))

    2) Vtemp = .ArrayToText(.IsNumber(.Search(Vb, .Transpose(Va))), 1)  

End With

 

1) Vtemp에 값이 어떻게 들어갔는지 상상이 되는가?

 

이런식으로 5 X 51 의 배열이 만들어진다.

 

각 배열에는 TRUE / FALSE 값으로 값이 담겨져 있다.

 

이값들을 추출하려면 전통적인 방식으로는 배열을 순환하면서 값을 하나씩 꺼내야 하지만 ARRAYTOTEXT를 이용하면

2) VTEMP 이런식으로 같은 열 방향으로는 콤마[,]를 행방향으로는 세미콜론[;] 으로 구분이 지어지게 된다.

 

본인이 최근에 함수식 TEXTJOIN 과 TEXTSPLIT를 이용하면서 배열을 다루는것을 ARRAYTOTEXT가 해주는 것이다.

 

최종적으로 Vc 도 MMULT로 행렬의 곱을 이용하기 위해서 ARRAYTOTEXT(Vc,1) 하게 되면 준비는 끝났다.

 

 

Vc는 배열이 51 X 1인 배열이기에 ARRAYTOTEXT로 가공된 배열상수를 보면 세미콜론으로 배열상수가 나눠져 있을 것이다.

 

이렇게 하면 우린 [ 5X51 ] [51 X 1]  행렬의 곱을 하기까지 준비과정을 끝냈다.

 

마지막으로 배열상수식을 셀에 출력하기 위해

[I4].Formula2 = "=MMULT(--" & Vtemp & "," & Vc & ")"

 

이렇게 해주면 우리가 원하는 값을 얻을 수 있다.

 

Option Explicit

Sub 기초방86()
    
    Dim Va, Vb, Vc
    Dim Vtemp
    
    Va = Range([c4], [c4].End(4))
    Vb = [g4:g8]
    Vc = Range([d4], [d4].End(4))
    
    With Application
      
        
        Vtemp = .ArrayToText(.IsNumber(.Search(Vb, .Transpose(Va))), 1)  '= 2차원 배열을 배열상수식으로 전환
       
        Vc = .ArrayToText(Vc, 1)                                         '= 갯수 배열을 배열상수식으로 전환
    
        [I4].Formula2 = "=MMULT(--" & Vtemp & "," & Vc & ")"             '= 행렬의 곱으로 5 X 1 의 배열을 출력
        

    End With

End Sub

 

아저씨 마인드로 하나 추가하면 우리 교육이 미래의 세계와 얼마나 역행하는지 말할 수 있는 대목이다.

 

우리 아이들은 행렬을 배워야한다. 왜 배워야 하는지를 알게 해야 한다.

기초방86.xlsm

0.02MB