엑셀을 이용하여 MNIST 신경망 구축하기 Machine Learning MNIST using a Neural Network in Excel (2) - Control 탭 화면해설
MNIST 화면 해설
(1)편에서 만든 5개의 탭 중 Control 탭부터 살펴보자. (1)편의 내용대로 진행을 했다면 Training 탭과 Testing 탭의 경우 데이터로 가득차 있을 것이다.
최종적으로 만들어지게 될 Control 탭의 모습은 다음과 같다.
부분 부분 나눠서 살펴봅니다.
① RANDBETWEEN 함수를 이용하여 1과 60,000(=AF2) 사이의 수를 랜덤으로 가져오게 된다. Training 탭의 훈련용 데이터가 60,000개이므로 60,000을 넘어서는 안된다. 이번에는 39994라는 값이 선택이 되었고, AG2 에서 아래와 같이 Training 탭의 A39994 셀을 가리키도록 한다. 참고로 Training 탭의 A39994 셀은 다음과 같다 (Training 탭과 Testing 탭으로 데이터를 가져오는 방법은 이미 (1)편에서 다룬바 있다).
② INDIRECT 함수를 이용하여 AG2 셀에 있는 참조의 값인 Training 탭의 A39994 셀의 값을 반환한다.
③ A30의 셀 값을 B32:B815 까지 각각의 셀로 가져온다. 그리고 AD7 에 위치한 Target 값은 해당 데이터셋이 나타내는 실제 숫자인 B32 셀의 값을 불러온다. 앞서도 설명드렸듯이 Training 탭의 한 행은 총 785개의 열로 구성이 되는데, 맨 앞의 셀이 그 데이터셋이 나타내는 실제 숫자이고, 2번 열부터 785번 열까지 총 784(=28*28)개의 셀이 각 픽셀의 그레이스케일 정보를 담고 있다. 이 부분의 함수를 좀 더 살펴보자.
A32 셀의 2라는 값이 어떻게 나왔는지 살펴본다. 해당 셀의 함수는 SEARCH(",",$A$30) 와 같이 되어있다. SEARCH 함수는 텍스트 문자열(여기서는 A30)에서 지정된 텍스트 문자열(,)이 몇 번째 위치에서 시작하는지 나타내는 값을 반환한다. 여기서는 9 다음에 두 번째 위치에서 , 가 나오므로 2가 되는 것이다.
A33 셀의 4 라는 값을 도출하는 SEARCH(",",$A$30,A32+1) 함수를 보면 이번에는 검색을 시작하는 위치(여기서는 A3
2+1, 즉 3)가 지정되어 있다. 즉 A30 텍스트 문자열의 3번째 위치(여기서는 0)에 시작하여 콤마(,) 찾으면 전체 텍스트 문자열 기준으로 4번째 위치에 , 가 나오므로 그 값은 4가 된다.
| 9 | , | 0 | , |
이번에는 B32 셀의 값 9에 대해 살펴보자. 해당 셀의 함수를 보면 다음과 같다.
=INT(MID($A$30,A31+1,A32-1-A31))
MID 함수는 텍스트 문자열에서 지정된 위치로부터 지정된 수만큼 문자를 반환한다. 위 함수중 파란색 텍스트인 MID($A$30,A31+1,A32-1-A31) 부터 보면, A30셀의 A31+1=1, 즉 첫 번째 문자부터 A32-1-A31=1, 즉 1자를 표시한다. 여기서는 9가 된다. 이어서 가장 가까운 정수로 내리는 INT 함수를 적용하면 값이 9가 된다.
이제 B34 셀의 값을 도출하는 INT(MID($A$30,A32+1,A33-1-A32)) 함수 차례다. MID($A$30,A32+1,A33-1-A32) 는 MID($A$30,2+1,4-1-2) 이므로, A30 텍스트 문자열의 세번째 문자부터 1자를 표시하므로 0이 된다. 결국 이 함수는 A30 셀의 텍스트 문자열의 콤마(,) 를 제외한 숫자값을 셀 하나에 각각 넣는 역할을 하게 된다.
④ OFFSET 함수를 이용하여 A1:AB28 범위를 B33:B815 셀의 값으로 채운다. 그리고 조건부 서식을 활용하여 이미지처럼 보일 수 있도록 한다. 먼저 OFFSET 함수의 구문을 살펴보면 다음과 같다.
|
OFFSET(reference, rows, cols, [height], [width]) Reference : 필수 요소, 오프셋의 기준점 rows : 필수 요소, 왼쪽 위에 있는 셀이 참조할 위 또는 아래에 있는 행의 수, 양수(시작 참조보다 아래) 또는 음수(시작 참조보다 위) height : 선택 요소, 반환되는 참조의 높이(단위: 행 수), 양수 |
A1 셀을 살펴보자.
함수식은 다음과 같다.
| =OFFSET( $B$33 , (ROW()-ROW($A$1))*28+(COLUMN()-COLUMN($A$1)) , 0 ) |
|
Reference : $B$33, 오프셋의 기준점으로 그레이스케일 값의 첫번째를 나타낸다.
ROW() = 1
따라서 (ROW()-ROW($A$1))*28+(COLUMN()-COLUMN($A$1)) = (1-1)*28+(1-1) = 0 으로 기준점인 $B$33 을 참조한다는 것이다.
cols : 마지막 요소로 참조할 열의 수로, 이 값이 0 이므로 A33도 B33도 아닌 기준점의 열인 $B$33 을 참조한다. |
이런 식으로 $B$33 부터 28개 셀의 값을 한 줄씩 채워나가는 것이다. 다음의 이미지와 하단의 엑셀 표를 참고하면 되겠다.
1행, 2행에 대한 OFFSET 함수 계산 결과
OFFSET 함수를 통해 28*28 개의 셀에 값을 다 넣었다면 이미지처럼 보이도록 조건부 서식을 지정할 차례다. 조건부 서식을 적용하지 않은 현재의 모습은 다음과 같다.
$A$1:$AB$28 셀을 선택한 후 홈 > 조건부서식을 클릭한다. 먼저 셀 값이 0과 80 사이인 경우 다음과 같이 글꼴 색을 흰색으로 지정한다. 이 서식이 적용된 모습은 다음과 같다.
그 다음으로 141 에서 200 사이의 값을 가지는 셀의 글꼴을 다음과 같이 연한 회색으로 지정하였다.
마지막으로 셀 값을 기준으로 색상을 부여하자. 아래와 같이 규칙 유형을 셀 값을 기준으로 모든 셀의 서식 지정을 선택하고 서식 스타일은 2가지 색조, 그리고 색의 경우 흰색과 검정색은 각각 선택하여 다음과 같은 그라데이션을 만든다. 해당 규칙을 적용한 최종 이미지는 다음과 같다.
전체 규칙은 다음과 같다.
이제 Target 과 Output 영역을 살펴보자. 아래와 같이 결과값이 이미지의 레이블값과 일치하면 녹색배경으로 나오고, 그렇지 못하면 빨간색으로 배경색이 바뀐다. 이 내용은 조건부 서식에 아래와 같이 규칙으로 포함되어 있다.
좌측 Target 은 B32 셀을 참조하는데, B32 셀은 해당 데이터의 레이블 정보가 들어있다. 여기서는 숫자 9이다.
우측 Output 영역은 Answer 값을 가져온다.
Answer 값은 아래에서 보듯이 NN 탭의 AFF12 셀의 값을 참조한다. NN탭의 연산에 대해서는 다음 포스팅에서 다룬다.
이제 CONTROL 탭의 마지막 부분이다.
먼저 빨간색 박스 부분은 테스트 결과를 전체적으로 보여주게 된다.
예를 들어 선택된 AP18 셀의 경우 함수식은 다음과 같다.
=IF( AND($AD$7=$AO18,$AJ$7=AP$28) , AP18+1 , AP18 )
여기서 $AD$7은 타겟값, $AJ$7은 결과값이다. AND 함수가 적용되는 AND($AD$7=$AO18,$AJ$7=AP$28) 부분을 보면, 타겟값이 9이지만, 결과값이 0 으로 나온 경우, AP18 셀에 카운트를 추가한다는 것이다.
다음으로 AP17:AY17의 비율에 대한 부분이다.
AP17 셀의 함수값은 다음과 같다. 전체 실행건 중에 결과값이 0 으로 나온 경우를 보여주는 것으로 위에서 보듯이 0 에서 9가지 10% 내외로 골고루 결과값이 나왔음을 알 수 있다.
=SUM(AP18:AP27)/SUM($AP$18:$AY$27)
다음으로 AZ18:AZ27 셀에 위치한 비율값에 대한 것이다.
AZ18 셀의 함수값은 =AY18/SUM(AP18:AY18) 으로 타겟값이 9인 경우, 결과가 9로 나온 비율을 보여준다. 현재까지의 결과로는 숫자 1에 대한 정확도가 95.71076% 으로 가장 높고, 숫자 5에 대한 정확도가 79.45447% 으로 가장 낮음을 알 수 있다.
그리고 마지막으로 매 실행시마다 보라색박스가 쳐진 부분처럼, 맞춘 횟수 및 틀린 횟수와 각각의 비율이 업데이트된다.
각 셀의 함수식은 다음과 같다. Target(AD7) 와 Output(AJ7) 값 일치 여부를 매번 카운트해서 각각의 셀에 반영한다.
Count(AK2) = +AK2+1
Hit(AL2) = IF(AD7=AJ7,AL2+1,AL2)
Miss(AM2) = IF(AD7<>AJ7,AM2+1,AM2)
다음 포스팅에서 신경망에 해당하는 NN탭에 대해 살펴본다.