rand(life)
반, 번호, 이름 따로 떼어내기 [엑셀에서 문자열 추출함수] 본문
앞에서 작업을 하여 학생 개인의 성적과 개별 답안지에 표기한 정답이 같은 행에 배열이 되도록 했습니다. 그런데 이 성적이 누구의 것인지 모른다면 반쪽짜리 통계밖에 안되겠죠. 그래서 성적 행 앞에 반, 번호, 이름을 넣도록 하겠습니다. 처음부터 학년, 반, 번호를 별도의 셀에 넣어주었으면 좋았을텐데, 지금처럼 E3셀에 몰아서 다 입력해놓으니 여기서 반, 번호를 추출해 내는게 쉽지는 않아보입니다. 물론, 수작업으로 한명씩 입력하는 것은 생각조차 할 수 없지요^^
일단 답부터 보겠습니다.
=MID($E3,SEARCH("학년",$E3)-2,2)
=MID($E3,SEARCH("반",$E3)-3,2)
=MID($E3,SEARCH("번",$E3)-3,2)
=MID($E3,SEARCH("번",$E3)+2,6)
맨위에서부터 학년, 반, 번호, 이름을 입력하는 수식입니다. 모두 MID함수와 SEARCH함수를 사용했습니다. 하나만 살펴보면 나머지는 모두 알 수가 있으니, 맨 처음에 있는 학년을 추출하는 수식을 살펴보겠습니다.
=MID($E3,SEARCH("학년",$E3)-2,2)
일단, 가운데 있는 SEARCH함수 부분부터 보겠습니다.
SEARCH함수는 지정한 문자열이 지정한 텍스트내에서 몇번째에 나오는지를 알려줍니다.
SEARCH함수는 두개 또는 세 개의 인수로 구성되어있습니다. 첫째인수("학년")는 찾고자하는 문자열입니다. 둘째인수 ($E3)는 어느 셀에서 찾아야할지를 지정해줍니다. 세번째 인수 (여기서는 생략되었습니다) 는 셀내에서도 몇번째위치에서 찾기 시작할지를 지정해줍니다. 생략하면 맨처음위치에서부터 찾기 시작합니다.
자, 그럼 위의
SEARCH("학년",$E3)
는 어떤 의미일까요? 네, "학년"이라는 문자열이 E3셀에서 몇번째위치에서 나오는지를 보여주는 것이죠.
제가 계산해보았더니 54가 나오더군요. 즉 "****고등학교"에서부터 54번째 글자가 바로 "학년"이라는 글자라는 의미입니다.
다음으로 MID함수를 보겠습니다.
MID함수는 지정한 문자 수에 따라 문자열의 지정한 위치로부터 지정한 개수의 문자를 표시합니다.
MID함수는 세 개의 인수로 이루어져 있습니다. 첫째인수($E3)는 문자열이 있는 셀이고, 둘째인수(SEARCH("학년",$E3)-2)는 문자열내의 어느위치에서 시작할것인지를 지정합니다. 세째인수(2)는 시작위치에서 몇개의 문자를 표시할 것인지를 나타냅니다.
그럼 위에 표시한
=MID($E3,SEARCH("학년",$E3)-2,2)
는 E3셀에서, "SEARCH("학년",$E3)-2"의 위치에서부터 2글자를 보여준다는 의미겠죠. 아까 SEARCH("학년",$E3)를 계산하니까 54가 나왔다고 했죠? 지금은 뒤에 -2가 붙어있으므로, E3셀에서 52번째부터 2개의 글자를 보여준다는 의미가 됩니다.
"학년"이 54번째에 나왔으므로, 52번째에서 2글자는 2와 '공백'이 됩니다. '공백'은 숫자로 취급하지 않으므로 2가 출력이 되겠네요.
나머지도 같은 식으로 설명이 됩니다. 다만, 이름을 보여주는 수식은
=MID($E3,SEARCH("번",$E3)+2,6)
보다시피, 맨뒤에 6이 붙어있죠? 그것은 한글의 한 글자는 영어나 숫자의 두 글자와 크기가 같기 때문입니다. 정확하게는 숫자나 영어는 1바이트고, 한글은 2바이트라서 그렇다고 합니다. 아무튼, 6으로 해줘야 세글자인 한글 이름이 모두 나오게 됩니다.
앞에서와 마찬가지로, 매크로를 300번 자동 반복되게 해 보겠습니다.
일단 위에서 설명한 수식을 A6~D6의 셀에 입력해 둡니다. 주의할 점은, 6행이후의 행을 클릭한 후 아래의 매크로를 실행해야합니다. 6행보다 위의 행을 클릭하면 6행에서 매크로가 종료되고 작업이 더이상 진행되지 않습니다. (이렇게 하지 않고서도 작동하는 매크로를 만들고 싶었는데 능력이 딸리네요. 나중에 더 좋은 방법이 있으면 추가하겠습니다.)
===============================
Sub Macro7()
For i = 1 To 300
Cells.Find(What:="영어1", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, MatchByte:=False, SearchFormat:=False).Activate ' "영어1"이란 문자를 찾아서
ActiveCell.Offset(0, -5).Range("A1").Select '왼쪽으로 5칸 이동합니다.
If ActiveCell.Value <> 0 Then Exit Sub '그 셀이 비어있지 않으면 매크로를 종료합니다.
'비어있으면 다음으로 넘어갑니다.
Range("$A$6:$D$6").Copy '맨처음 입력한 수식(A6~D6셀)을 복사해옵니다.
ActiveCell.Range("A1:d1").Select '붙여넣기할 장소를 선택합니다.
ActiveSheet.Paste 'A6~D6셀을 붙여넣기합니다.
ActiveCell.Offset(1, 0).Range("A1").Select '다음 칸으로 이동합니다.
Next i
End Sub
=========================================
