기획
실무에서 쓰이는 엑셀 함수. 실전 사용법 - 엑셀 입문자용
월하
2012. 3. 11. 22:12
가금 기획자분들 중 엑셀을 사용 할 줄 모르시는 분들이 있습니다.
약간은 믿을 수 없지만 정말 입니다. 신입? 경력? 예외 없습니다.
저도 잘 쓰는건 아니지만 기본 적인 엑셀 함수들을 정리할 겸 해서 해당 포스트를 작성 하였습니다.
아래의 첨부 파일을 열어 보시면 첫 시트에 이렇게 생긴 녀석이 있습니다.
옙. 정말 민망할 정도로 성의 없이 만든듯한 엑셀 시트 입니다. ㄷㄷㄷ
그저 캐릭터 레벨, 직업, 종족, 장비를 선택 하면 최종 스텟이 출력 되는 형태 입니다.
외관상 볼때는 정말 별볼이 없어 보이는 그런 엑셀 문서 이지만...
제가 약 5년( 현재 만 4년 9개월)간 실무에서 계산을 위해 사용한 대부분의 수식이 들 있는거 같습니다.
첫 시트에 사용된 기능들 부터 알아 보자면 다음과 같습니다.
시뮬레이터 / 툴에서 매우 자주 쓰이는 녀석 입니다.
만들기도 쉽고 보기에도 아주 깔끔해져서 개인적으로 상당히 좋아하는 기능 입니다.
사용 방법은....
개발 도구 -> 삽입 -> 콤보 상자 를 원하는 위치에 적당한 크기로 그리시면 생성 됩니다.
물론 요녀석은 껍데기 입니다.
안의 내용물을 보자면 콤보상자 우클릭 -> 컨트롤 서식 -> 컨트롤 탭을 보시면 됩니다.
그럼 레벨의 컨틀로 서식을 보자면...
이름 정의는 엑셀에서 특정 범위의 데이터를 편하게 사용 하기 위해 특정한 단어로 규약 하는 것을 말 합니다.
가령 첨부된 엑셀 파일에서 ref 시트를 보시면
이런 부분이 있습니다.
여기서 붉은색으로 박스를 친 부분이 해당 엑셀 문서에서 이름 정의된 부분 입니다.
이름 정의를 하는 법은 간단 합니다.
원하는 범위를 드래그로 선택 한 후 수식 -> 이름 정의를 클릭 하시면 됩니다.
그럼 아래와 같은 창이 호출 됩니다.
참조 대상이 방금 선택한 범위이며 이름에 적당한 이름을 쓰시면 됩니다.
단, 이름에는 공백이나 특수 문자는 사용 할 수 없습니다.(언더바 제외)
이렇게 정의된 이름은 아래와 같이 이름 관리자 ( 수식 -> 이름 관리자)에서 볼 수 있습니다.
이름을 정할때는 신중히 정해야 합니다.
이름을 정하는건 정말 고민 되죠. (?)
뻘 짤방이고..... 엑셀에서 이름 정의가 중요한 이유는....
수식이나 기타 등등에서 범위값을 일일이 지정 하는것보다 미리 정의된 이름을 넣는게 편하기 때문 입니다.
그런데 이름이 복작하거나 헷갈린다면? 안하느니 못한거죠.
여하튼 위 이미지에 테두리가 줄러진 녀석을 이름 정의한 범위 인데 각
레벨 / 종족_레벨 / 직업_레벨의 3 가지 범위를 이름으로 지정 하였습니다.
이미지에 테두리가 잘못 둘러져 있는데 레벨 부분에 "레벨"이란 글자는 범위에 포함이 되지 않았습니다.
즉, 아까 본 이 녀석은 1 ~ 50 까지의 입력 범위를 가지는 것입니다.
콤보 박스에서 레벨을 1~50 까지의 레벨 중 하나를 선택 하면 해당 값이 C4 셀에 입력이 되는 녀석 입니다.
이제 본격적으로 셀을 하나 하나 뜯어 보도록 하겠습니다.
먼저 C4 물리 공격의 스테이터스가 출력 되는 부분 입니다.
여기선 간단한 두 개의 함수가 사용 되었습니다.
정말 중요한 수식 입니다.
아마 엑셀을 다루다 보면 정말 질리도록 사용 되는 수식이죠.
기본 구조는 다음과 같습니다.
=IF(조건 , 조건이 참일 때 값 , 조건이 거짓일 때 값)
위의 수식은.....
조건: C4=1
조건이 참일 때 값: ref!E3
조건이 거짓일 때 값: ref!E3*((VLOOKUP(G4,종족_레벨,3,FALSE)^(C4-1))*((VLOOKUP(E4,직업_레벨,3,FALSE)^(C4-1))))
입니다.
즉, 조건인 c4(레벨)가 1이면 ref 시트의 E3 셀의 값을 받아 오고 그렇지 않으면 저 수식의 값을 출력 합니다.
해당 수식에 포함된 vlookup은 다음과 같은 함수 입니다.
vlookup의 수식도 마찬가지로 아주 중요한 수식 입니다.
if 못지않게 지겹게 사용 되는 수식인데 구조는 다음과 같습니다.
=vlookup(찾을 값 , 데이터가 들어 있는 셀 범위, 반환해야 할 테이블의 인수 열 변호, 옵션)
음... 이렇게만 해서는 뭔 말인지 알아먹기가 더 힘드네요.
위의 예제 중 하나만 뜯어서 살펴보면...
VLOOKUP(G4,종족_레벨,3,FALSE)
찾을 값: G4
데이터가 들어 있는 셀 범위: 종족_레벨(이름 정의된 범위)
반환해야 할 테이블의 인수 열 변호: 3
옵션: false
이란 구조인데 간단히 설명 하자면...
종족_레벨 이라는 셀 범위에서
G4에 해당 하는 값을 찾은 후
해당 값을 기준으로 우측으로 3번째 있는 값을 찾는 것입니다.
여기서 옵션인 false는 정확히 일치하는 값을 찾는 것입니다.
다른 옵션으로는 true가 있는데 근사값을 찾을 때 사용 됩니다.
자세하 예제는 http://excel.officeapps.live.com/x/_layouts/xlEmbed.aspx?C=1__SKY-WAC-WSHI&su=8247236622081652475&Fi=SD7274120315C4F6FB!361&AllowInteractivity=True&AllowTyping=True&lc=ko-KR
를 참조 바랍니다.
즉 위의 수식
=IF(C4=1,ref!E3,ref!E3*((VLOOKUP(G4,종족_레벨,3,FALSE)^(C4-1))*((VLOOKUP(E4,직업_레벨,3,FALSE)^(C4-1)))))
의 경우 1레벨 이면 ref시트의 E3을 가져오고
1레벨이 아니면 ref시트의 E3에 종족에 해당하는 가중치를 레벨만큼 곱하고 직업에 해당하는 가중치를 레벨만큼 곱해주는 겁니다.
다음 설명 들어가기전에 잠깐 data 시트를 보겠습니다.
굉장히 단순한 구조의 아이템 데이터 입니다.
인덱스 / 이름 / 종족 / 아이템 파츠 / 그리고 각 스텟.......
제일 마지막에 계산용은 시뮬레이터용 입니다. 실제 데이터에는 없죠.
그럼 먼저 A2 시트를 확인해 보겠습니다.
이건 뭐 회사마다, 작업자마다 조금 스타일이 다른데 제가 개인적으로 선호하는 방식 중 하나 입니다.
(종족 인덱스 * 1000000) + (파츠 인덱스 * 10000) + 종족별, 파츠별 등록 번호 입니다.
이렇게 하면 인덱스 번호만 봐도 종족과 어느 파츠의 아이템인지는 쉽게 추적이 가능 하기 때문입니다.
그리고 계산용에 있는 값은 종족 인덱스 * 10 + 파츠 인덱스 입니다.
그럼 이제 가장 중요한 계산 시트를 보겠습니다.
간단하게 위와 아래로 구분을 하겠습니다.
위의 경우는 데이터를 입력 받기 위한 범위값의 설정을 위한 부분이고
아래는 계산기 시트에서 선택한 실제 아이템의 데이터를 호출하는 부분 입니다.
위 시트부터 확인 해 보자면...
A2번은 계산기 시트에서 선택한 종족의 인덱스가 호출됩니다.
B2:B7은 수동으로 입력된 값입니다.
C2:C7은 다음의 함수가 사용 됩니다.
C2를 예로들면....
=VALUE(CONCATENATE($A$2,B2))란 수식이 있습니다.
value는 사용 빈도는 낮지만 알아두면 의외로 좋은 수식입니다.
굉장히 단순한 함수인데 그 내용은
value(데이터) 에서 data가 텍스트일때 이를 숫자로 인식 할 수 있습니다.
음... 사실 이 함수가 단독으로 쓰이는 일은 거의 없습니다.
위의 예에서 처럼 CONCATENATE라 mid, left, right등의 함수랑 복합해서 사용 합니다.
CONCATENATE라 mid, left, right 같은 함수는 텍스트 함수로 실제 값이 숫자이더라도 해당 함수로 출력된 값은 텍스트로 인식해 버리죠.
이럴때 다시 숫자로 변환 해줄 수 있는 함수 입니다.
CONCATENATE는 두 개의 셀을 하나의 값으로 합쳐주는 수식 입니다.
위에서 사용한 CONCATENATE($A$2,B2)의 경우 A2와 B2의 값을 하나로 묶어서 출력 하는 거죠.
즉, A2의 1과 B2의 1을 11이라는 텍스트로 호출하는 함수이고 5번에서 언급한 value 함수로 인하여 숫자 11 로 호출 합니다.
이제 D2와 E2에 쓰인 match 함수를 알아 보겠습니다.
match 함수는 해당 데이터가 테이블(목록)에서 몇 번째에 위치하여 있는가를 찾는 함수 입니다.
가령 A에서 Z까지 있는 목록에서 C가 몇 번째에 있는가.... 같은것을 구할때 사용 합니다.
D2에는 =MATCH(C2,data!$L:$L,0) 라는 수식이 있습니다.
이 수식은 C2의 값이 data시트의 L열에서 몇 번째에 있는지 찾아주는 수식 입니다.
여기서 C2의 값은 종족&파츠의 값 입니다.
그러니 data에 중복되는 값이 많이 있죠.
여기서 D2와 E2의 차이가 들어 납니다.
D2는 =MATCH(C2,data!$L:$L,0) 이고
E2는 =MATCH(C2,data!$L:$L,1) 입니다.
0과 1의 차이 인데 0의 경우 일치하는 첫번째 위치를, 1의 경우 일치하는 마지막 값의 위치를 찾아 줍니다.
그렇기 떄문에 D2의 값은 2가 되고 E2의 값은 22가 됩니다.
이제 인간 헤어의 첫번째 열과 마지막 열을 알았으니 인간 헤어 데이터 범위 값이 나왔습니다.
이름을 기준으로 보면 B2:B22 입니다.
이제 이름의 범위값을 알게되었습니다.
하지만 엑셀은 알 수 없죠.
엑셀이 알 수 있도록 하는 작업이 필요한데 이때 사용하는 수식이 ADDRESS입니다.
Address수식은 다음과 같이 구성 됩니다.
Address(행번호, 열번호, 유형, 스타일, 시트 이름)
입력된 행번호와 열번호에 맞는 셀을 지정된 유형과 스타일에 맞게 시트이름과 함께 호출 하는 형태 입니다.
여기서 유형에 대해 알아보자면
이 됩니다.
그리고 스타일의 경우
true이거나 생략할 경우 AI 스타일을 false일 경우 R1C1 스타일을 사용 합니다.
일반적으로 사용 하는 스타일이 A1 스타일 입니다.
R1C1 스타일을 사용 하는 경우는 거의 없을겁니다.
가령 address(2,2,4,,,)이면
행번호 2 / 열번호 2 / 상대행, 상대열 / 스타일 AI / 시트 이름 생략
이느 B2 셀이 호출 됩니다.
그럼 G2 수식을 살펴보면
=ADDRESS(D2,2,,,"data")&":"&ADDRESS(E2,2)
이라고 되어 있습니다.
ADDRESS(D2,2,,,"data") 와 : 텍스틑, ADDRESS(E2,2) 을 붙여서 표기 하도록 되어 있씁니다.
즉, data시트의 D2값에 해당하는 행과 2번 열을 절대 셀 유형으로 표기 하고
E2값에 해당하는 행과 2번열을 절대셀 유형으로 표기하는 겁니다.
이렇게 되면 data!$b$2:data$b$22라는 값이 호출 됩니다.
데이터 시트의 B2에서 B22까지의 범위를 나타내는거죠.
하지만 이는 텍스트로만 사용되지 실제 테이블로는 사용 할 수 없습니다.
그래서 이름 관리자에서 indirect라는 함수가 사용 되었습니다.
indrect의 경우 해당 값을 참조 하는 기능 입니다.
즉 이름이 헤어인 항목을 보면 indirect(계산! $G$2)이라고 되어 있는데 이는
무기의 참조 대상은 G2에 입력된 값이 지정하는 범위를 참조 하라는 겁니다.
즉, 헤어의 참조 범위는 data!$b$2:data$b$22 가 됩니다.
이상으로 해당 엑셀 문서에서 사용된 대부분의 기능을 다 다루었습니다.(빠진게 있는지 잘 모르겠지만)
솔직히 엑셀 수식의 경우 도움말에 정말 설명이 잘 되어 있습니다.
충분한 예제와 같이 설명이 되어 있죠.
엑셀 수식은 조금만 관심을 가지면 쉽게 익힐수 있습니다.
하지만 엑셀 수식은 단독으로 쓰이는 경우보다 2개 이상의 수식이 복합적으로 사용 되는 경우가 많습니다.
가령 COUNTIF의 경우 테이블 내에서 특정 조건을 만족하는 숫자들의 합을 구합니다.
예를 들면 이름, 원산지, 과일멸, 가격의 테이블에서 원산지가 국산인 과일들의 가격 합을 구할 수는 있습니다.
하지만 국산 사과 가격의 합을 구할 수는 없죠.
이럴 때는 SUM(IF((B:B="국산")*(C:C="사과"),D:D) 같은 형대로 사용 해야 합니다.
이토록 엑셀 함수의 묘미는 두 개 이상의 수식이 복합적으로 사용 될 때 입니다.
하지만 이런건 도움말에 잘 안나오죠.
검색이나 다른 사람 물어 보는게 빠르고 편합니다.
그러니깐.....
다들 vba 배우세요. vba가 짱이에요.
약간은 믿을 수 없지만 정말 입니다. 신입? 경력? 예외 없습니다.
저도 잘 쓰는건 아니지만 기본 적인 엑셀 함수들을 정리할 겸 해서 해당 포스트를 작성 하였습니다.
아래의 첨부 파일을 열어 보시면 첫 시트에 이렇게 생긴 녀석이 있습니다.
스테이터스_계산기_2.2.xlsx
옙. 정말 민망할 정도로 성의 없이 만든듯한 엑셀 시트 입니다. ㄷㄷㄷ
그저 캐릭터 레벨, 직업, 종족, 장비를 선택 하면 최종 스텟이 출력 되는 형태 입니다.
외관상 볼때는 정말 별볼이 없어 보이는 그런 엑셀 문서 이지만...
제가 약 5년( 현재 만 4년 9개월)간 실무에서 계산을 위해 사용한 대부분의 수식이 들 있는거 같습니다.
첫 시트에 사용된 기능들 부터 알아 보자면 다음과 같습니다.
| 1. 콤보 상자 |
시뮬레이터 / 툴에서 매우 자주 쓰이는 녀석 입니다.
만들기도 쉽고 보기에도 아주 깔끔해져서 개인적으로 상당히 좋아하는 기능 입니다.
사용 방법은....
개발 도구 -> 삽입 -> 콤보 상자 를 원하는 위치에 적당한 크기로 그리시면 생성 됩니다.
물론 요녀석은 껍데기 입니다.
안의 내용물을 보자면 콤보상자 우클릭 -> 컨트롤 서식 -> 컨트롤 탭을 보시면 됩니다.
그럼 레벨의 컨틀로 서식을 보자면...
이렇게 되어 있습니다.
이게 뭔 말이냐면....
레벨이란 입력 범위내의 값을 콤보 상자에서 선택 할 수 있게 하면서
해당 값의 데이터를 C4 셀에 입력을 하여 주는 겁니다.
그럼 여기서... 대체 입력 범위에 레벨이 무엇인가... 하는 의문이 들 수 있습니다.
여기에 사용된 레벨 은 이름 정의 기능을 통하여 사전에 정의된 규칙 이름 입니다.
| 2. 이름 정의 |
이름 정의는 엑셀에서 특정 범위의 데이터를 편하게 사용 하기 위해 특정한 단어로 규약 하는 것을 말 합니다.
가령 첨부된 엑셀 파일에서 ref 시트를 보시면
이런 부분이 있습니다.
여기서 붉은색으로 박스를 친 부분이 해당 엑셀 문서에서 이름 정의된 부분 입니다.
이름 정의를 하는 법은 간단 합니다.
원하는 범위를 드래그로 선택 한 후 수식 -> 이름 정의를 클릭 하시면 됩니다.
그럼 아래와 같은 창이 호출 됩니다.
참조 대상이 방금 선택한 범위이며 이름에 적당한 이름을 쓰시면 됩니다.
단, 이름에는 공백이나 특수 문자는 사용 할 수 없습니다.(언더바 제외)
이렇게 정의된 이름은 아래와 같이 이름 관리자 ( 수식 -> 이름 관리자)에서 볼 수 있습니다.
참조 대상에 뭔가 이상한 수식들이 보이지만 일단 나중으로 넘기고 진행 하도록 하겠습니다.
이름을 정할때는 신중히 정해야 합니다.
이름을 정하는건 정말 고민 되죠. (?)
뻘 짤방이고..... 엑셀에서 이름 정의가 중요한 이유는....
수식이나 기타 등등에서 범위값을 일일이 지정 하는것보다 미리 정의된 이름을 넣는게 편하기 때문 입니다.
그런데 이름이 복작하거나 헷갈린다면? 안하느니 못한거죠.
여하튼 위 이미지에 테두리가 줄러진 녀석을 이름 정의한 범위 인데 각
레벨 / 종족_레벨 / 직업_레벨의 3 가지 범위를 이름으로 지정 하였습니다.
이미지에 테두리가 잘못 둘러져 있는데 레벨 부분에 "레벨"이란 글자는 범위에 포함이 되지 않았습니다.
즉, 아까 본 이 녀석은 1 ~ 50 까지의 입력 범위를 가지는 것입니다.
콤보 박스에서 레벨을 1~50 까지의 레벨 중 하나를 선택 하면 해당 값이 C4 셀에 입력이 되는 녀석 입니다.
이제 본격적으로 셀을 하나 하나 뜯어 보도록 하겠습니다.
먼저 C4 물리 공격의 스테이터스가 출력 되는 부분 입니다.
여기선 간단한 두 개의 함수가 사용 되었습니다.
| 3. IF 수식 |
정말 중요한 수식 입니다.
아마 엑셀을 다루다 보면 정말 질리도록 사용 되는 수식이죠.
기본 구조는 다음과 같습니다.
=IF(조건 , 조건이 참일 때 값 , 조건이 거짓일 때 값)
위의 수식은.....
조건: C4=1
조건이 참일 때 값: ref!E3
조건이 거짓일 때 값: ref!E3*((VLOOKUP(G4,종족_레벨,3,FALSE)^(C4-1))*((VLOOKUP(E4,직업_레벨,3,FALSE)^(C4-1))))
입니다.
즉, 조건인 c4(레벨)가 1이면 ref 시트의 E3 셀의 값을 받아 오고 그렇지 않으면 저 수식의 값을 출력 합니다.
해당 수식에 포함된 vlookup은 다음과 같은 함수 입니다.
| 4. vlookup 수식 |
vlookup의 수식도 마찬가지로 아주 중요한 수식 입니다.
if 못지않게 지겹게 사용 되는 수식인데 구조는 다음과 같습니다.
=vlookup(찾을 값 , 데이터가 들어 있는 셀 범위, 반환해야 할 테이블의 인수 열 변호, 옵션)
음... 이렇게만 해서는 뭔 말인지 알아먹기가 더 힘드네요.
위의 예제 중 하나만 뜯어서 살펴보면...
VLOOKUP(G4,종족_레벨,3,FALSE)
찾을 값: G4
데이터가 들어 있는 셀 범위: 종족_레벨(이름 정의된 범위)
반환해야 할 테이블의 인수 열 변호: 3
옵션: false
이란 구조인데 간단히 설명 하자면...
종족_레벨 이라는 셀 범위에서
G4에 해당 하는 값을 찾은 후
해당 값을 기준으로 우측으로 3번째 있는 값을 찾는 것입니다.
여기서 옵션인 false는 정확히 일치하는 값을 찾는 것입니다.
다른 옵션으로는 true가 있는데 근사값을 찾을 때 사용 됩니다.
자세하 예제는 http://excel.officeapps.live.com/x/_layouts/xlEmbed.aspx?C=1__SKY-WAC-WSHI&su=8247236622081652475&Fi=SD7274120315C4F6FB!361&AllowInteractivity=True&AllowTyping=True&lc=ko-KR
를 참조 바랍니다.
즉 위의 수식
=IF(C4=1,ref!E3,ref!E3*((VLOOKUP(G4,종족_레벨,3,FALSE)^(C4-1))*((VLOOKUP(E4,직업_레벨,3,FALSE)^(C4-1)))))
의 경우 1레벨 이면 ref시트의 E3을 가져오고
1레벨이 아니면 ref시트의 E3에 종족에 해당하는 가중치를 레벨만큼 곱하고 직업에 해당하는 가중치를 레벨만큼 곱해주는 겁니다.
| 번외. 데이터 관리 |
다음 설명 들어가기전에 잠깐 data 시트를 보겠습니다.
굉장히 단순한 구조의 아이템 데이터 입니다.
인덱스 / 이름 / 종족 / 아이템 파츠 / 그리고 각 스텟.......
제일 마지막에 계산용은 시뮬레이터용 입니다. 실제 데이터에는 없죠.
그럼 먼저 A2 시트를 확인해 보겠습니다.
이건 뭐 회사마다, 작업자마다 조금 스타일이 다른데 제가 개인적으로 선호하는 방식 중 하나 입니다.
(종족 인덱스 * 1000000) + (파츠 인덱스 * 10000) + 종족별, 파츠별 등록 번호 입니다.
이렇게 하면 인덱스 번호만 봐도 종족과 어느 파츠의 아이템인지는 쉽게 추적이 가능 하기 때문입니다.
그리고 계산용에 있는 값은 종족 인덱스 * 10 + 파츠 인덱스 입니다.
그럼 이제 가장 중요한 계산 시트를 보겠습니다.
간단하게 위와 아래로 구분을 하겠습니다.
위의 경우는 데이터를 입력 받기 위한 범위값의 설정을 위한 부분이고
아래는 계산기 시트에서 선택한 실제 아이템의 데이터를 호출하는 부분 입니다.
위 시트부터 확인 해 보자면...
A2번은 계산기 시트에서 선택한 종족의 인덱스가 호출됩니다.
B2:B7은 수동으로 입력된 값입니다.
C2:C7은 다음의 함수가 사용 됩니다.
C2를 예로들면....
=VALUE(CONCATENATE($A$2,B2))란 수식이 있습니다.
| 5. value 수식 |
value는 사용 빈도는 낮지만 알아두면 의외로 좋은 수식입니다.
굉장히 단순한 함수인데 그 내용은
value(데이터) 에서 data가 텍스트일때 이를 숫자로 인식 할 수 있습니다.
음... 사실 이 함수가 단독으로 쓰이는 일은 거의 없습니다.
위의 예에서 처럼 CONCATENATE라 mid, left, right등의 함수랑 복합해서 사용 합니다.
CONCATENATE라 mid, left, right 같은 함수는 텍스트 함수로 실제 값이 숫자이더라도 해당 함수로 출력된 값은 텍스트로 인식해 버리죠.
이럴때 다시 숫자로 변환 해줄 수 있는 함수 입니다.
| 6. CONCATENATE 수식 |
CONCATENATE는 두 개의 셀을 하나의 값으로 합쳐주는 수식 입니다.
위에서 사용한 CONCATENATE($A$2,B2)의 경우 A2와 B2의 값을 하나로 묶어서 출력 하는 거죠.
즉, A2의 1과 B2의 1을 11이라는 텍스트로 호출하는 함수이고 5번에서 언급한 value 함수로 인하여 숫자 11 로 호출 합니다.
이제 D2와 E2에 쓰인 match 함수를 알아 보겠습니다.
| 7. MATCH 수식 |
match 함수는 해당 데이터가 테이블(목록)에서 몇 번째에 위치하여 있는가를 찾는 함수 입니다.
가령 A에서 Z까지 있는 목록에서 C가 몇 번째에 있는가.... 같은것을 구할때 사용 합니다.
D2에는 =MATCH(C2,data!$L:$L,0) 라는 수식이 있습니다.
이 수식은 C2의 값이 data시트의 L열에서 몇 번째에 있는지 찾아주는 수식 입니다.
여기서 C2의 값은 종족&파츠의 값 입니다.
그러니 data에 중복되는 값이 많이 있죠.
여기서 D2와 E2의 차이가 들어 납니다.
D2는 =MATCH(C2,data!$L:$L,0) 이고
E2는 =MATCH(C2,data!$L:$L,1) 입니다.
0과 1의 차이 인데 0의 경우 일치하는 첫번째 위치를, 1의 경우 일치하는 마지막 값의 위치를 찾아 줍니다.
그렇기 떄문에 D2의 값은 2가 되고 E2의 값은 22가 됩니다.
이제 인간 헤어의 첫번째 열과 마지막 열을 알았으니 인간 헤어 데이터 범위 값이 나왔습니다.
이름을 기준으로 보면 B2:B22 입니다.
| 7. ADDRESS 수식 |
이제 이름의 범위값을 알게되었습니다.
하지만 엑셀은 알 수 없죠.
엑셀이 알 수 있도록 하는 작업이 필요한데 이때 사용하는 수식이 ADDRESS입니다.
Address수식은 다음과 같이 구성 됩니다.
Address(행번호, 열번호, 유형, 스타일, 시트 이름)
입력된 행번호와 열번호에 맞는 셀을 지정된 유형과 스타일에 맞게 시트이름과 함께 호출 하는 형태 입니다.
여기서 유형에 대해 알아보자면
| 1 또는 생략 | 절대 셀 |
| 2 | 절대 행, 상대 열 |
| 3 | 상대 행, 절대 열 |
| 4 | 상대 행, 상대 열 |
이 됩니다.
그리고 스타일의 경우
true이거나 생략할 경우 AI 스타일을 false일 경우 R1C1 스타일을 사용 합니다.
일반적으로 사용 하는 스타일이 A1 스타일 입니다.
R1C1 스타일을 사용 하는 경우는 거의 없을겁니다.
가령 address(2,2,4,,,)이면
행번호 2 / 열번호 2 / 상대행, 상대열 / 스타일 AI / 시트 이름 생략
이느 B2 셀이 호출 됩니다.
그럼 G2 수식을 살펴보면
=ADDRESS(D2,2,,,"data")&":"&ADDRESS(E2,2)
이라고 되어 있습니다.
ADDRESS(D2,2,,,"data") 와 : 텍스틑, ADDRESS(E2,2) 을 붙여서 표기 하도록 되어 있씁니다.
즉, data시트의 D2값에 해당하는 행과 2번 열을 절대 셀 유형으로 표기 하고
E2값에 해당하는 행과 2번열을 절대셀 유형으로 표기하는 겁니다.
이렇게 되면 data!$b$2:data$b$22라는 값이 호출 됩니다.
데이터 시트의 B2에서 B22까지의 범위를 나타내는거죠.
하지만 이는 텍스트로만 사용되지 실제 테이블로는 사용 할 수 없습니다.
그래서 이름 관리자에서 indirect라는 함수가 사용 되었습니다.
| 8. INDIRECT 수식 |
indrect의 경우 해당 값을 참조 하는 기능 입니다.
즉 이름이 헤어인 항목을 보면 indirect(계산! $G$2)이라고 되어 있는데 이는
무기의 참조 대상은 G2에 입력된 값이 지정하는 범위를 참조 하라는 겁니다.
즉, 헤어의 참조 범위는 data!$b$2:data$b$22 가 됩니다.
| 9. 끝 |
이상으로 해당 엑셀 문서에서 사용된 대부분의 기능을 다 다루었습니다.(빠진게 있는지 잘 모르겠지만)
솔직히 엑셀 수식의 경우 도움말에 정말 설명이 잘 되어 있습니다.
충분한 예제와 같이 설명이 되어 있죠.
엑셀 수식은 조금만 관심을 가지면 쉽게 익힐수 있습니다.
하지만 엑셀 수식은 단독으로 쓰이는 경우보다 2개 이상의 수식이 복합적으로 사용 되는 경우가 많습니다.
가령 COUNTIF의 경우 테이블 내에서 특정 조건을 만족하는 숫자들의 합을 구합니다.
예를 들면 이름, 원산지, 과일멸, 가격의 테이블에서 원산지가 국산인 과일들의 가격 합을 구할 수는 있습니다.
하지만 국산 사과 가격의 합을 구할 수는 없죠.
이럴 때는 SUM(IF((B:B="국산")*(C:C="사과"),D:D) 같은 형대로 사용 해야 합니다.
이토록 엑셀 함수의 묘미는 두 개 이상의 수식이 복합적으로 사용 될 때 입니다.
하지만 이런건 도움말에 잘 안나오죠.
검색이나 다른 사람 물어 보는게 빠르고 편합니다.
그러니깐.....
다들 vba 배우세요. vba가 짱이에요.
반응형