나도 몰라서 올려보는 블로그

안녕하세요 오늘 배워볼 함수는

VLOOKUP과 HLOOUP입니다.

위의 함수를 사용할 때는 표 또는 행별로 범위에서 항목을 찾아야 할 때는 VLOOKUP을 사용 합니다. 예를 들어 부품 번호를 기준으로 자동차 부품의 가격을 조회 하거나 직원 ID를 기준으로 직원 이름을 찾습니다.

=VLOOKUP(1, $B$4:$D$9, 2, FALSE)

사원번호 1에 해당하는 직원이름을 찾았습니다.

인수별 설명을 드리자면

=VLOOKUP( 찾을값, 배열, 찾을 열번호, [TRUE:유사일치, FALSE:정확하게일치] )

입니다.

선택한 범위의 첫번째 열에서 값 1에 해당하는 행의 두번째 열의 값을 찾겠다.

라는 말이 됩니다.

그래서 위의 사진을 보면 사원번호 1에 해당하는 행의 2번째 열값이 런빈인걸 알 수 있습니다.

찾을값을 직접 입력할 수도 있지만

셀을 참조하여 검색할 수도 있습니다.

위의 수식과 바뀐점은 첫번째 인수가 직접입력방식이 아닌 셀참조방식으로 바뀐점입니다.

=VLOOKUP( F6, $B$4:$D$9, 2, FALSE)

해당 사원번호의 부서를 찾고싶다면 어떻게 해야할까요?

찾을 열번호 3으로 바꿔 부서를 찾을수 있도록 해주면 됩니다. :)

HLOOKUP은 어떻게 사용할까요?

VLOOKUP이 열을 찾는거라면 HLOOKUP은 행을 찾습니다.

위의 사진의 배열을 옆으로 눕였다고 생각하시면 됩니다.

사용법은 VLOOKUP과 동일합니다.

어렵지 않습니다.

따라해보시면 쉽게 하실수 있어요 ㅎㅎ

위 예제들만 보면 이 함수를 실제로는 어떻게 사용할까? 라고 생각하시잖아요.

그래서 간단하게 만들어봤습니다.

[실생활에서 필요한 예제]

갑자기 뭔가 많이졌다구요?

기분탓일겁니다 ㅎㅎㅎ

예제파일 다운받으셔서 직접 해보는게 도움됩니다.

시트1 -> VLOOKUP

시트2 -> HLOOKUP

시트3 -> 예제

로 구성되있습니다.

이상 VLOOUP, HLOOKUP 사용법이였습니다.

오늘은 COUNT 함수 심화과정에 대한 포스팅입니다.

COUNT 함수 인수 목록에서 숫자의 개수를 반환합니다.

COUNTA 함수 인수 목록에서 값의 개수를 반환합니다.

COUNTBLANK 함수 범위 내에서 비어 있는 셀의 개수를 반환합니다.

COUNTIF 함수 범위 내에서 주어진 조건을 만족하는 셀의 개수를 반환합니다.

COUNTIFS 함수 범위 내에서 여러 조건을 만족하는 셀의 개수를 반환합니다.

다양한 COUNT 함수들이 존재하는데요

예제를 통해 한개씩 배워봅시다.

COUNT 함수는 숫자만 셋다면 COUNTA 함수는 값이 있는 셀의 갯수를 셉니다.

공백이 아닌 모든 값들을 세기때문에 스페이스바가 입력된 셀도 문자로 인식하기 때문에 갯수에 포함이 됩니다.

사용방법은 COUNT 함수과 동일합니다.

=COUNTA( 범위 )

COUNTBLANK 함수는 값이 없는 공백인 셀의 갯수만 셉니다.

사용방법은 COUNT 함수와 동일합니다.

=COUNTBLANK( 범위 )

COUNTIF 함수는 해당 범위에서 찾고자하는 조건값과 일치하는 값을 찾습니다.

위 예제처럼 애완동물 종류중 개를 기르는 사람의 수를 세고자 할때 유용하게 쓰일 수 있습니다.

사용법은 SUMIF와 비슷합니다.

=COUNTIF(범위, 찾을값)

위 예제처럼 셀을 참조하여 찾을 수도 있습니다.

=COUNTIF(D5:D11, "개") 와 같은 의미라고 생각하시면 될것 같습니다.

하지만 셀을 참조하여 함수를 사용할때는 주의해야할 점이 있는데요,

D5셀의 값이 다른 값으로 바뀌게 된다면 D5에 입력된 값으로 다시 찾게됩니다.

위 예제처럼 나는 "개"를 찾고싶지만 나버터라는 사람의 애완동물을 잘못 조사하여 애완동물의 이름이 바뀐다면

=COUNTIF(D5:D11, "사자") 로 바뀌게 되므로 내가 찾고자 하던 값과 다를 수 있게됩니다.

이처럼 셀참조를 사용할 때에는 바뀌지 않을 값으로 설정하거나

찾을 값을 따로 빼놓는 방법이 있습니다.

위 예제처럼 사용한다면 큰 장점으로는 다른 동물을 찾고싶을때

H3셀의 값만 변경하면 쉽게 다른 값을 찾을 수 있습니다.

COUNTIFS 는 여러 조건을 만족하는 값의 갯수를 구하는 함수입니다.

예제에서 보듯이 서울에서 개를 기르는 수를 찾고자 할때 사용했습니다.

=COUNTIFS(범위, 찾을값, [범위, 찾을값]);

[ ]는 선택요소이므로 찾을 조건이 추가로 입력하면 됩니다.

=COUNTIFS(C4:C11, "서울")  -> 서울의 갯수를 구합니다.

=COUNTIFS(C4:C11, "서울", D4:D11, "개") -> 서울에서 개를 기르는 수

만약 추가로 조건을 설정했다면 최초범위의 크기와 같게 잡아야 오류가 나지 않습니다.

[와일드카드 문자]

와일드카드 문자인 물음표(?), 별표(*), 물결표(~)를 사용할 수 있는데요

?표는 단어 한 문자에 해당합니다.

찾을값에 "?" 를 입력하게되면 1글자인 애완동물을 모두 찾습니다.

"고??"를 입력한다면 "고"로 시작하는 3글자 애완동물을 찾습니다.

*는 모든 글자를 찾는걸 의미합니다.

"*"을 입력한다면 모든 글자를 찾게되므로 공백을 제외한 모든 애완동물의 갯수를 찾게됩니다.

"고*"을 입력한다면 어떻게 될까요?

마지막으로 ~는 별표(*)나 물음표(?)를 직접 사용하고 싶을때 사용합니다.

"~*"를 입력하게되면 모든 글자를 찾는게 아닌 별표(*)를 찾게됩니다.

=COUNTIFS(A1:A13, "~*") -> 별표(*)로 된 문자의 갯수를 구한다.

글로는 한계가 있으니 직접 해보는게 더 도움이 되니까 꼭 해보세요 :)

이상 COUNT함수의 심화과정이였습니다.

첫장에서 배웠던 SUM의 심화과정입니다.

단순 SUM에서 조건이 붙은 SUMIF,

여러 조건이 붙는 SUMIFS를 소개해드립니다.

서울지역의 지급비용 총 합계를 구했습니다.

SUMIF 사용법

SUMIF(range, criteria, [sum_range])

SUMIF(범위, 비교값, 합계범위)

범위는 합계를 구할 범위로, 지역부터 지급비용까지를 말합니다.

비교값은 ">50", "서울", C5, 등 비교하고 싶은 값을 적으면 됩니다.

합계범위는 실제 구할 숫자범위로, 지급비용 범위가 됩니다.

[sum_range] 는 선택사항이므로 필요할 경우만 적으면 됩니다.

SUMIF는 범위, 조건만 입력해도 계산이 됩니다.

입맛에 따라 사용하시면 될것 같습니다.

만약 서울에 사는 사람중 지급비용이 35,000 이하인 합계만 구하고 싶다면 어떻게 해야될까요?

SUMIF로는 조건을 한개밖에 입력을 못합니다.

만일 여러개의 조건을 만족하는 합계를 구하고 싶다면 다음과 같은 함수를 사용하면 됩니다.

SUMIFS

위와같이 여러 조건을 만족하는 합계를 구하실 수 있습니다.

SUMIFS( 합계범위, 조건범위1, 조건1, [조건범위2, 조건2], ... )

[그 밖의 SUM관련 함수]

SUMSQ : 인수의 제곱의 합을 반환합니다.

SUMSQ(5, 5) = 25

SUMPRODUCT : 주어진 배열에서 해당 요소를 모두 곱하고 그 곱의 합계를 반환합니다.

각 배열의 차원이 같을때만 사용가능한 함수입니다.

차원이 같지 않으면 #VALUE! 오류가 표시됩니다.

SUMPRODUCT은 각 배열을 모두 곱한뒤 곱한 합계를 반환합니다.

5*5 + 4*2 + 12*3 + 3*3

이상 SUM 함수 심화과정이였습니다.

설명을 잘 못해서 이해가 안되시거나 질문은 댓글로 남겨주시기 바랍니다. :)

지정 범위의 숫자의 갯수를 알려주는 함수 COUNT 함수를 사용해 봅시다.

COUNT 함수는 숫자가 포함된 셀의 개수를 세고 인수 목록 내에 포함된 숫자의 개수를 셉니다.

COUNT 함수를 사용하면 범위나 숫자 배열에 포함되어 있는 숫자 필드에서 항목의 수를 구할 수 있습니다.

예를 들어 다음과 같은 수식을 입력하면 범위 B1:B10에 있는 숫자의 개수를 구할 수 있습니다.

위 설명대로 COUNT 함수는 숫자의 갯수만 알려줍니다.

인자로 직접 입력할시엔 결과값이 달라집니다.

=COUNT( 1, 2, 3, "사과", "배")

의 결과값은 숫자가 3개가 있으므로 3으로 출력이되지만,

=COUNT( 1, 2, 3, "4", "5")

의 결과값은 5가 출력이 됩니다.

"4", "5"가 텍스트이지만 텍스트로 표현된 숫자이기 때문에 숫자로 인식하여 카운팅을 해줍니다.

이는 인수로 직접 입력했을경우에만 적용이 되고 범위로 지정하게되면 숫자만 카운팅하게 됩니다.

텍스트로 표현된 숫자를 입력해보았습니다.

COUNT함수를 사용하니 5, 3만 카운팅하여 결과값 2를 출력하게 됩니다.

이를 인수로 입력하게되면

"1", "2", "3" 이 텍스트이지만 숫자로 되어있기 때문에 결과값이 5로 출력이 되었습니다.

인자로 입력할 일은 거의 없기때문에 재미로 알아두시면 좋을것 같습니다

오늘은 조건부 서식이라는 엑셀 기능에 대해서 알아보도록 합시다.

조건부 서식이란 해당 조건에 맞는 곳의 서식을 변경시켜주는 엑셀 기능입니다.

무슨 말인지 잘 모르시겠죠?

말씀드렸던 해당 조건에 해당하는 서식을 바꿔주는 엑셀 기능인데요.

제가 설정한 조건은 성적이 90초과하는 곳의 서식을 파랑색으로 변경해주는 조건부 서식을 설정했습니다.

조건부 서식의 위치는 홈-스타일탭-조건부서식에 있습니다.

이제 사용방법에 대해서 알아보도록 합시다.

[ 조건부서식 사용방법 ]

먼저 조건부 서식을 설정한 범위를 선택합니다.

위에서 설명드렸던곳에서 조건부서식을 클릭합니다.

저는 창을 작게해서 아이콘이 조그맣게 나왔네요.

새규칙을 클릭합니다.

새규칙을 클릭하면 새 서식 규칙창이 띄워집니다.

그러면 규칙 유형 선택 맨 밑 [수식을 사용하여 서식을 지정할 셀 결정]을 클릭합니다.

그리고 밑에 수식을 입력할 수 있는 칸을 클릭하고 C4셀을 클립합니다.

C4가 입력칸에 입력되면 F4키를 눌러 C열만 고정시켜줍니다.

서식버튼을 클릭해 지정할 서식을 선택합니다.

저는 바탕색을 채우기 위해 채우기탭에서 배경색을 선택했습니다.

확인을 모두 누르고 빠져나오면 적용된 셀 모습이 보입니다.

제가 설정했던 성적 90점 초과인 곳만 파랑색으로 바뀐 모습입니다.

조건부서식을 사용하게 되면 일반적으로 가독성을 높일 수 있습니다.

가독성을 높일 엑셀문서를 만들때 유용하게 쓰일 수 있는 엑셀 기능입니다.

이상 조건부서식 설명이였습니다.

엑셀 상용법 두번째입니다.

오늘 설명할 내용은 if문입니다.

먼저 if의 사전적의미를 보면

if 미국·영국 [ɪf]  영국식  

[접속사] (가정적 조건을 나타내어) (만약) …면

사전적 의미로는 만약에 ~면이라 설명되어 있는데요.

엑셀함수 if도 비슷하게 이해하시면 될것같습니다.

IF(조건문, 참, 거짓)

=IF(  A1 = "A",   1,  0)

위의 문장을 말로 풀어서 설명하면

만약에 A1의 셀이 A라면 1를 출력, 그렇지 않으면 0을 출력하라는 뜻입니다.

IF(C4="서울", "O", "X")

=을 입력해 줌으로써 C4의 내용과 "서울"과 내용이 일치하면 O를 표시하고 그렇지 않으면 X를 표시합니다.

<, <=, >, >=, <>, =

여러 부등호를 사용하여 if문을 사용해도 됩니다.

IF문을 중첩해서 사용해도 되는데요

예로는 성적을 구하는 중첩IF문 작성해봤습니다.

 IF( 이민철의 성적이 90점을 초과한다, 참이면 "A" 그렇지 않으면  IF (이민철의 성적이 80을 초과한다. , 참이면 "B", 그렇지 않으면   IF( 이민철의 성적이 70을 초과한다, 참이면 "C", 그렇지않으면 "D" )    )   ) 

IF문 안에 IF문을 넣는 형식으로 참, 거짓 둘다 적용이 가능합니다.

조건이 많아지면 지문이 길어져 복잡해질수 있습니다.

IF문 안에 IF문을 적용시킬 수 있다면 다른 함수도 적용이 가능하겠죠?

이전에 배웠던 평균값을 구하는 함수 =AVERAGE(  ) 를 조건문에 사용하여

평균값 이상인 곳에만 "O" 표시를 넣었습니다.

 =AVERAGE(  )  함수에 $표시가 되어있는게 보이시나요?

$표시는 고정을 의미합니다. 

만약에 $표시가 없다면 셀을 드래그로 복사할때 평균 함수 범위도 같이 내려가게 됩니다.

$를 표시해준다면 내가 지정한 범위를 벗어나지 않게됩니다.

$를 설정하는건 고정할 범위를 블럭시켜놓고 F4키를 누르면됩니다.

엑셀 $ 의미

C4 : 고정하지 않음

$C4 : C열을 고정함

C$4 : 4행을 고정함

$C$4 : C열과 4행을 고정함

글로 읽으면 이해가  잘되어서 직접 해보셔야 합니다.

이상으로 엑셀 기초 두번째 글이구요

궁금한점 있으시면 댓글로 남겨주세요~

엑셀을 계산기처럼 사용하는 방법입니다.

아주 기초단계부터 나중에는 고급함수까지 알려드리려고해요

저도 처음 엑셀을 접하면서 신기한게 많아서 같이 공유하려고 글을 올려봅니다.

엑셀을 처음 실행하면 엑셀화면이 나옵니다.

엑셀 계산작업을 할때는 = 을 맨 앞에 붙여주면 됩니다.

참 쉽죠?

근대 이렇게 엑셀을 사용할 바엔 계산기를 켜서 사용하는게 더 효율적이죠

근대 엑셀은 입력한 값들을 한번에 계산할 수도 있습니다.

=을 입력하면 셀을 선택할 수가 있습니다.

값 1에 해당하는 셀을 선택하면 = 입력한 칸에 B4라고 자동으로 입력이 됩니다.

그런다음 +을 입력하고 다음 값인 2를 선택하고 +를 입력하고

값 5까지 반복하고 엔터를 누르면 엑셀이 자동으로 해당값을 계산해줍니다.

귀찮죠?

그래서 엑셀은 함수를 제공합니다.

여러 함수중 더하기를 자동으로 해주는 SUM함수를 이용해볼께요

SUM 함수는 위에 하나하나 입력해야 했던것들을 자동으로 해줍니다.

=SUM(

괄호 여는것까지 입력한다음 마우스로 값1 ~ 5까지 드래그 해줍니다.

그려면 자동으로 B4:B8이 입력될꺼에요

그대로 엔터를 눌러주세요

그러면 SUM함수에 의해 값이 계산이 됩니다.

참 쉽죠?

함수로 인해 출력된 값들은 재사용을 할 수 있는데요.

1번째 1 ~ 5까지 더하는 sum과

2번째 6 ~ 10까지 더하는 sum을

sum했습니다.

이런식으로 결과로 출력된 숫자들은 함수로 인식하지 않고

숫자로 활용할 수 있습니다.

그리고  함수끼리고 계산을 할 수 있습니다.

sum() + sum() 을 하면 위에서 했던 결과값 그대로 나오게 됩니다.

기초적인 몇개만 설명해봤는데요

sum 함수말고도 다른 함수들도 존재합니다.

average : 평균

max : 최대값

min : 최소값

sum : 합계

사용법은 같이니 sum 대신 넣보세요.

계산기를 사용했을때는 잘못입력하면 다시 계산을 해야되는 단점이 있지만

엑셀을 이용하면 잘못된 셀 숫자만 변경하면 되니 좀 복잡한 계산을 하게된다면

계산기보다는 엑셀을 활용하는게 더 좋을것 같습니다.

제가 아는 엑셀 기초부터 응용, 간단한 VBA까지 올려볼 생각입니다.

자주 들어와서 확인해주세요 ~ :)