|
5일차
찾기 참조함수에서 VLOOKUP, HLOOKUP 함수는 참조표를 가지고 구분하면됨.
TRUE( 1 ) : 연결값과 참조의범위안에 연결된 값들중에서 유사일치한 값을 가지고 와야될때 사용됨.
대체적으로 참조의범위에서 숫자로 연결시켰을 경우에 옵션을 1(TRUE)로 많이 주는데 단, 찾는값 부분이 LEFT, RIGHT, MID, LARGE, SMALL, max, min 등과같이 함수로 연결시켜주게 되면 정확히 일치하는 것(FALSE)으로 찾아줘야됨.
FALSE( 0 ) : 찾는값과 참조의 범위의 연결된 부분이 문자형태로 되어있을 때 주로 이용함. 정확하게 일치한것만 찾아서 가지고 올때 사용.
참조표가 열단위로 되어있으면 VLOOKUP
=VLOOKUP(찾는값, 찾는값이 들어있는곳부터 시작한 참조의전체범위, 가지고 올 열의 번호, 검색유형)
검색유형은 정확하게 일치하면 FALSE(0)을 근사치한 값을 가져올때는 TRUE(1)을 쓰면 된다.
참조표가 행단위로 되어있으면 HLOOKUP
=HLOOKUP(찾는값, 찾는값이 들어있는곳부터 시작한 참조의전체범위, 가지고 올 행의 번호, 검색유형)
※ 검색 유형 부분은 찾는값 부분과 참조의 범위의 내용이 어떻게 되어있는지에 따라서 정해주면 되는데 찾는값 부분이 문자로 되어있으면 검색유형은 같은 글자에 대한 내용을 찾아와서 표시해주는게 되므로 정확히 일치(FALSE 또는 0) 를 입력해주면되고, 찾는값 부분이 숫자로 되어있을때는 유사일치(TRUE 또는 1)로 입력하는 경우가 많다. 단, 찾는값 부분이 LARGE, SMALL, MAX, MIN, LEFT, RIGHT, MID 등과 같은 함수를 이용하여 값을 찾을 때는 검색유형 부분이 정확히 일치하는 값을 찾아오는 것으로 해주어야 된다.
그런데 AVERAGE, SUM 과 같이 숫자를 합하거나 평균내거나 하는 함수들이 들어갈때는 검색유형부분이 TRUE일 수도 있다.
p.1-92 찾기참조2시트
① [표1]에서 사원코드[A3:A10]와 부서코드[B13:D14]를 이용하여 부서명을 표시하시오.
▶ HLOOKUP와 LEFT 함수 사용
1) 사원코드 추출
=LEFT(A3,1)
2) 참조의 범위 안에서 찾아 부서명 추출하기
=HLOOKUP(LEFT(A3,1),$B$13:$D$14,2,FALSE)
② [표2]에서 중간고서[G3:G9], 기말고사[H3:H9]와 학점기준표[G12:K14]를 참조하여 학점[I3:I9]을 계산하시오.
▶ 평균은 각 학생의 중간고사와 기말고사로 구함
▶ AVERAGE, HLOOKUP 함수 사용
1) 평균 구하기
=AVERAGE(G3:H3)
2) 참조의 범위 안에서 찾아 학점 추출하기
=HLOOKUP(AVERAGE(G3:H3),$G$12:$K$14,3,TRUE)
③ [표3]에서 [A18:A27]영역에 함수를 이용하여 1, 2, 3,,,,의 일련번호를 작성하고, [B17:D17]영역에 함수를 이용하여 7월, 8월, 90월로 표시하시오.
▶ COLUMN, ROW함수와 & 연산자 이용
=COLUMN() 열번호를 반환 =COLUMNS(범위) 범위 안에 사용된 열 개수를 구함
=ROW() 행번호를 반환 =ROWS(범위) 범위 안에 사용된 행 개수를 구함.
1) [A18] 셀에 셀포인터를 위치시킨다.
2) 행번호가 일련번호로 표시되도록 함수를 입력한다.
말로풀기 : =ROW() 까지입력하면 현재 작업하고 있는 행번호인 ‘18’값이 표시되므로 ‘1’이 되게하려면 –17을 해줌
정답 : =ROW()-17
3) 채우기핸들로 [A27]셀까지 드래그하여 함수를 채워준다.
4) [B17] 셀에 셀포인터를 위치시킨다.
5) 7월, 8월, 9월처럼 표시되도록하려면 현재 작업하고 있는 열의 값을 구한 후 & 연산자를 이용하여 “월”이 붙게함
말로풀기 : =COLUMN() 함수를 입력하면 현재 B열에서 작업중이므로 결과는 2가 나온다. 7이 되도록 하려면 +5를 더해주면 된다.
정답 : =COLUMN()+5&“월”
6) 채우기핸들로 [D17]셀까지 드래그하여 함수를 채워준다.
④[표4]의 [G18:J21] 영역과 지역코드표[G24:J25]를 이용하여 부산에서 목포까지의 요금을 구하여 [H28]셀에 표시하시오. 단, 출발지[F18:F21]은 행, 도착지 [G17:J17]은 열로 참조한다.
▶ INDEX, HLOOKUP함수 사용
1) [H28]셀에 셀포인터를 위치시킨 후 출발점‘부산’에 대한 코드 행 값을 HLOOKUP 함수를 이용하여 구함.
말로풀기 : =HLOOKUP(‘부산’, 출발지 값이들어있는 참조전체범위[F18:F21], 가지고올 행번호, 검색유형)
=HLOOKUP(F20,$G$24:$J$25,2,FALSE)
=HLOOKUP(‘목포’, 도착지 값이들어있는 참조전체범위[G17:J17], 가지고올 행번호, 검색유형)
=HLOOKUP(J17,$G$24:$J$25,2,FALSE)
2) 요금표[G18:J21]부분을 INDEX함수를 이용하여 가져온다.
말로풀기 : =INDEX(표시할 범위, 행, 열) ☞ =INDEX(G18:J21, ‘부산’에대한 코드행값, ‘목포’에대한 코드열값)
정답 : =INDEX(G18:J21,HLOOKUP(F20,$G$24:$J$25,2,FALSE),HLOOKUP(J17,$G$24:$J$25,2,FALSE))
⑤[표5]의 학년, 과목과 [표6]의 할인율표를 이용하여 [O3:O30]영역에 학년과 과목에 따른 수강료 할인율을 계산하여 표시하시오.
▶HLOOKUP, MATCH 함수 사용
1) MATCH 함수를 이용하여 과목의 위치값 구하기
식 : =MATCH(N3,{"영어","수학"},-1)+1
[N3]셀의 값에 대한 행의 위치값이 반환되어야 함. [표6]의 할인율표에 ‘영어’와 ‘기타’만 있고, ‘수학’과 ‘국어’에 대한 값이 없으므로 ‘기타’의 행 값이 ‘수학’과 ‘국어’ 할인율 값으로 반환되어야 함.
위와 같이 { } 안에 “영어”, “수학”을 넣어주면 “영어”를 1, “수학”을 2의 값을 반환한다. “수학” 대신 “국어”를 넣으면 제대로 된 값을 반환하지 못하므로 반드시 “영어”, “수학” 순서대로 넣어주고, 마지막 정렬방법 부분에는 ㅇ → ㅅ 순서이므로 내림차순(-1)으로 정렬된 것에서 위치 값 구하라고 넣어주면 된다.
그러면 “영어”가 1, “수학”,“국어”는 2의 값을 반환한다. [표6]에서는 “영어”가 2, 나머지는 3행의 “기타”에 있는 할인율 값이 가져와져야 되므로 [=MATCH(N3,{"영어","수학"},-1)+1] 뒤에 ‘+1’을 해줘서 “영어”가 2, “수학”,“국어”가 3 값이 되도록 해준다.
2) 학년과 과목에 따른 할인율값을 가지고와서 표시해준다.
=HLOOKUP(M3,$R$3:$T$5,MATCH(N3,{"영어","수학"},-1)+1,FALSE)
=MATCH(찾는값, 찾는값이 들어있는 범위, 정렬방법)
정렬방법부분은 찾는값이 들어있는 범위가 오름차순 정렬이 되어있으면 1, 내림차순 정렬이 되어있으면 -1, 정렬이 안되어 있으면 0을 입력한다.
1-96
수수료율을 구하기
1) 한국카드, 기타카드부분에 들어갈 위치값을 MATCH함수를 이용해서 구하기(HLOOKUP의 행값이 됨)
(1) MATCH(C3,{"한국카드","대한카드"},-1)+2
2) HLOOKUP함수를 이용해서 수수료율 구하기
=HLOOKUP(E3,$B$16:$E$19,MATCH(C3,{"한국카드","대한카드"},-1)+2)
3) 지불수수료=매출액*수수료율
정답 : =B3 * HLOOKUP(E3,$B$16:$E$19,MATCH(C3,{"한국카드","대한카드"},-1)+2)
2. 1) 지역에 대한 행 위치값 구하기
=MATCH(H3,$A$23:$A$25,0)
면적에 대한 열 위치값 구하기
=MATCH(I3,$B$22:$E$22,1)
2) =INDEX(청약가능액범위, 지역행값, 면적열값)
=INDEX($B$23:$E$25,MATCH(H3,$A$23:$A$25,0),MATCH(I3,$B$22:$E$22,1))
3. 타이틀명 가져오기
1) DVD코드 뒤 2글자 빼내기
=RIGHT(H25,2)
위에서 빼낸 숫자는 문자이므로 참조표의 값이 숫자로 들어가져있으니 마지막 부분에 *1을 해서 RIGHT값을 숫자로 변경해 준다.
=RIGHT(H25,2)*1
2) =LOOKUP(찾는값, 찾는값이들어있는 범위, 결과로 나타내야되는 범위)
=LOOKUP(RIGHT(H25,2)*1,$A$29:$E$29,$A$28:$E$28)
4. 1) 평균구하기 : =SUMPRODUCT(N3:P3,{0.3,0.2,0.5})
2) =HLOOKUP(SUMPRODUCT(N3:P3,{0.3,0.2,0.5}),$M$28:$Q$29,2,TRUE)
5. 1) =LEFT(S3,2)
=MOD(RIGHT(S3,3),4)
=MOD(RIGHT(S3,3),4)+1
=CHOOSE(MOD(RIGHT(S3,3),4)+1,"동부","서부","남부","북부")
=VLOOKUP(LEFT(S3,2),$S$25:$T$29,2,FALSE)
=VLOOKUP(LEFT(S3,2),$S$25:$T$29,2,FALSE)&"("&CHOOSE(MOD(RIGHT(S3,3),4)+1,"동부","서부","남부","북부")&")"
1-109쪽
4번문제
1) 유통기한 구하기
=EDATE(Q3,R3)
2) 할인율=(유통기한-기준일)/30 을 QUOTIENT 함수로 구하기(참조표앞의 남은기간 부분이 됨)
=QUOTIENT(EDATE(Q3,R3)-$S$1,30)
3) VLOOKUP함수를 이용해서 참조표 범위 안에서 할인율 구하기
=VLOOKUP(QUOTIENT(EDATE(Q3,R3)-$S$1,30),$L$22:$M$25,2,TRUE)
4) 할인가=판매가 * (1-할인율)
=P3 * (1- VLOOKUP(QUOTIENT(EDATE(Q3,R3)-$S$1,30),$L$22:$M$25,2,TRUE) )
P.1-130쪽 재무함수
재무함수 문제를 풀때는 현재 돈이 나에게 있는지 없는지만 잘 생각하면됨.
현재 나에게 돈이 있으면 현재가치(PV), 현재 나에게 돈이 없으면 미래가치(FV)로 생각할 것.
납입시점이 월말이면 0 또는 생략, 월초이면 1
※ 감가상각액 : 회사에서 업무를 위해 사용하는 기계나 물건, 설비 등은 사용되면서 소모가 되어 그 가치가 떨어지는데 그 만큼의 감소분을 보전하기 위한 비용을 의미함
AMORDEGRC 함수
프랑스식 회계 방식으로 감가상각 계수를 사용하여 매 회계 기간에 대한 감가 상각액을 반환합니다. 회계 기간 중에 자산을 취득하면 상각 기간에 따라 감가 상각액을 비례 배분합니다. 이 함수는 자산의 내용 연수에 따라 감가 상각 계수를 달리 적용하는 것을 제외하면 AMORLINC와 유사합니다.
구문
AMORDEGRC(cost, date_purchased, first_period, salvage, period, rate, [basis])
※중요 날짜는 DATE 함수를 사용하거나 다른 수식 또는 함수의 결과로 입력해야 합니다. 예를 들어 2008년 5월 23일을 입력하려면 DATE(2008,5,23)을 사용합니다. 날짜를 텍스트로 입력하면 문제가 발생할 수 있습니다.
AMORDEGRC 함수 구문에는 다음과 같은 인수 (인수: 동작, 이벤트, 메서드, 속성, 함수 또는 프로시저에 정보를 제공하는 값입니다.)가 사용됩니다.
cost 필수 요소입니다. 자산 취득가액입니다.
date_purchased 필수 요소입니다. 자산 취득일입니다.
first_period 필수 요소입니다. 첫째 회계 기간의 마지막 날짜입니다.
salvage 필수 요소입니다. 자산의 수명이 다 되었을 때의 잔존 가치입니다.
period 필수 요소입니다. 기간입니다.
rate 필수 요소입니다. 감가 상각률입니다.
basis 선택 요소입니다. 사용할 날짜 체계입니다.
날짜체계
basis | 날짜 체계 |
0 또는 생략 | 360일(미국증권업협회 방식) |
1 | 실제 |
3 | 1년 = 365일 |
4 | 1년 = 360일(유럽식) |
※주의
◾날짜는 계산에 사용할 수 있도록 순차적인 일련 번호로 저장됩니다. 기본적으로 1900년 1월 1일이 일련 번호 1이고, 2008년 1월 1일은 1900년 1월 1일에서 39,448일째 날이므로 일련 번호 39448이 됩니다.
◾이 함수는 자산의 수명이 다하는 마지막 기간까지 또는 감가 상각 누적액이 자산의 취득가에서 잔존 가치를 뺀 값보다 커질 때까지 감가 상각을 반환합니다.
◾감가 상각 계수는 다음과 같습니다.
자산의 수명(1/rate) | 감가 상각 계수 |
3년에서 4년 사이 | 1.5 |
5년에서 6년 사이 | 2 |
6년 초과 | 2.5 |
◾감가 상각률은 마지막 기간 바로 앞의 기간 동안 50%까지 증가하고 마지막 기간에 100%로 증가합니다.
◾자산의 수명이 1년 이하, 1년에서 2년, 2년에서 3년 또는 4년에서 5년 사이인 경우 #NUM! 오류 값이 반환됩니다.
AMORLINC 함수
매 회계 기간에 대한 감가 상각액을 반환합니다. 이 함수는 프랑스식 회계 시스템을 위한 것입니다. 회계 기간 중에 자산을 취득하면 상각 기간에 따라 감가 상각액을 비례 배분합니다.
구문
AMORLINC(cost, date_purchased, first_period, salvage, period, rate, [basis])
※ 중요 날짜는 DATE 함수를 사용하거나 다른 수식 또는 함수의 결과로 입력해야 합니다. 예를 들어 2008년 5월 23일을 입력하려면 DATE(2008,5,23)을 사용합니다. 날짜를 텍스트로 입력하면 문제가 발생할 수 있습니다.
AMORLINC 함수 구문에는 다음과 같은 인수 (인수: 동작, 이벤트, 메서드, 속성, 함수 또는 프로시저에 정보를 제공하는 값입니다.)가 사용됩니다.
cost 필수 요소입니다. 자산 취득가액입니다.
date_purchased 필수 요소입니다. 자산 취득일입니다.
first_period 필수 요소입니다. 첫째 회계 기간의 마지막 날짜입니다.
salvage 필수 요소입니다. 자산의 수명이 다 되었을 때의 잔존 가치입니다.
period 필수 요소입니다. 기간입니다.
rate 필수 요소입니다. 감가 상각률입니다.
basis 선택 요소입니다. 사용할 날짜 체계입니다.
날짜체계
basis | 날짜 체계 |
0 또는 생략 | 360일(미국증권업협회 방식) |
1 | 실제 |
3 | 1년 = 365일 |
4 | 1년 = 360일(유럽식) |
※주의
날짜는 계산에 사용할 수 있도록 순차적인 일련 번호로 저장됩니다. 기본적으로 1900년 1월 1일이 일련 번호 1이고, 2008년 1월 1일은 1900년 1월 1일에서 39,448일째 날이므로 일련 번호 39448이 됩니다.
p.1-137 정보함수
ISBLANK(값) : 공백 셀이면 TRUE
ISERROR(값) : 에러값이면 TRUE
ISERR(값) : #N/A 오류를 제외한 오류의 값일 경우 TRUE
ISEVEN(값) : 숫자가 짝수이면 TRUE
ISODD(값) : 숫자가 홀수이면 TRUE
ISNUMBER(값) : 숫자값이면 TRUE
ISTEXT(값) : 텍스트값이면 TRUE
=IF(ISBLANK(C4)," ", VLOOKUP(C4,$I$4:$K$13,3,0))
=IF(ISERROR(E4*F4)," ",E4*F4)
규격, 단가 : 품명이 공백일 경우에는 공백으로 처리하고 품명이 공백이 아니면, vlookup 함수를 이용하여 제품목록에서 찾아 표시하시오.
1) =VLOOKUP(연결값, 참조의전체범위, 열 번호, 연결방법)
연결값은 값을 구하는 곳의 줄 중에 한곳을 이용하여야 한다.
참조의 전체범위는 데이터 제목을 포함해도 되고 안해도 되는데 연결값부분의 내용이 들어있는 곳부터 블록지정을 해야된다.
=VLOOKUP(C4,$I$4:$K$13,2,0)
2) 만약에 C4셀이 공백이면...공백으로 표시하고 그렇지 않으면 VLOOKUP함수의 내용을 표시
규격 =IF(ISBLANK(C4),"",VLOOKUP(C4,$I$4:$K$13,2,0))
단가 =IF(ISBLANK(C4),"",VLOOKUP(C4,$I$4:$K$13,3,0))
공급가액은 IF와 ISERROR 함수를 이용하여 수량*단가의 계산식에 오류가 발생하면 공백으로 처리하고, 오류가 없으면 수량*단가를 계산하여 표시하시오.
1) = 수량*단가를 한 후 채우기 핸들로 드래그해보면 오류가 나오는 것을 확인할 수 있다.
2) =IF(ISERROR(수량*단가),“”,수량*단가)처럼 하고 드래그하면 오류가 나오지 않음.
=IF(ISERROR(E4*F4),"",E4*F4)
|