※MATCH 함수와 INDEX 함수의 기본 원리는 우측 링크에서 확인해주시기 바랍니다.(MATCH / INDEX)

｜MATCH-INDEX 함수의 활용

VLOOKUP함수와 MATCH-INDEX함수는 엑셀에서 가장 중요한 함수라고 생각합니다. 

VLOOKUP(또는 HLOOKUP)함수가 단일함수로 작동하는 반면에

MATCH-INDEX함수는 두개의 함수를 결합하여 사용하기 때문에 VLOOKUP(또는 HLOOKUP)함수를 선호하곤 합니다. 

그러나 두 함수를 결합함으로써 더욱 양질의 데이터를 작성할 수 있습니다. 

따라서 VLOOKUP함수에서 그칠것이 아니라, 

MATCH-INDEX함수를 반드시 익혀야 엑셀천재에 한 걸음 다가갈 수 있습니다. 

｜MATCH-INDEX 함수 따라하기

이제, 하나씩 해보겠습니다. 

H6 셀에 적혀있는 \5,600 이라는 가격은, 바로 위에 적힌 인덱스 함수의 결과값입니다. 

B4:E10의 범위의 4번째행, 3번째열의 값이기 때문입니다. (모르겠으면 요기로!)

4번째 행은 '카페모카'를 찾게 해주고, 3번째 열은 '그란데'를 찾게 해줍니다.

그러면, '카페모카'를 찾기 위해서 '4'번째 행을 찾아보겠습니다.

위 함수의 값은 'B4:B10'의 범위에서 '카페모카'의 좌표를 찾는 MATCH함수입니다.(모르겠으면 요기로!)

이 함수를 빈 셀에 넣어 보면 '4'로 출력됨을 알 수 있습니다.

즉,

위의 함수는

위의 함수와 동일한 의미를 가지게 됩니다.(* 4 = MATCH(G6,B4:B10,0))

그러면, '그란데' 열을 찾기 위해서 '3'번째 열을 찾아보겠습니다.

위 함수의 값은 'B4:E4'의 범위에서 '그란데'의 좌표를 찾는 MATCH함수입니다.(모르겠으면 요기로!)

이 함수를 빈 셀에 넣어 보면 '3'으로 출력됨을 알 수 있습니다.

즉, 

위의 함수는

위의 함수와 동일한 의미를 가지게 됩니다.(* 3 = MATCH(H5,B4:E4,0))

따라서 우리는

위 함수와 동일한 결과물을 얻기 위해

INDEX 함수와 MATCH 함수를 결합하여 사용할 수 있습니다. 

｜MATCH 함수

MATCH 함수는 표에서 특정 값이 가지는 좌표를 보여줍니다. 

위의 표에서 "송중기"라는 값은 해당 범위에서 '2'번째에 있기 때문에 '2'를 출력하게 됩니다. 

보통 INDEX함수와 결합하여 사용합니다. (MATCH-INDEX 사용법 → 작업중 / INDEX함수 사용법)

｜MATCH 함수 사용법

=MATCH(①Lookup_value,②Lookup_array,③Match_type)

｜MATCH 함수 구성

①Lookup_value - 찾고 싶은 값(예 : 송중기, 한국)

②Lookup_array - 찾고 싶은 범위(가로, 세로 무관)

③Match_type - "1" : 보다 작음 / "0" : 정확히 일치 / "-1" : 보다 큼

｜IF 함수

IF함수는 '논리적인 조건(조건문)'이 '참일 경우의 값'과 '거짓일 경우의 값'을 출력합니다.

'70점 이상'이라는 조건이 '참일 경우 "합격"', '거짓일 경우 "불합격"'을 출력하고자 한다면,

=IF(C7>=70,"합격","불합격") 으로 IF함수를 사용할 수 있습니다. 

'논리적인 조건'은 결과값이 "TRUE" / "FALSE"로 나오게 되는 함수를 말합니다. 

보통 IF 함수는 결과를 "TRUE" / "FALSE"로 나타내는 AND , OR 함수와 함께 사용할 수 있습니다. 

｜IF 함수 사용법

=IF(①logical_test,②[value_if_true],③[value_if_false])

｜IF 함수 구성

｜IF 함수 세부설명

먼저, 빈 셀에 위의 내용을 적어보게 되면 "TRUE"의 값을 출력합니다. 

C7의 값은  98이기 때문에 70보다 큰가?라는 조건에 '참(TRUE)'임을 출력한 것입니다. 

만일 C7의 값이 60이라면, '거짓(FALSE)'를 출력하게 됩니다. 

따라서 위 함수는 "'C7이 70 이상인가'라는 조건이 참이면 "합격"을, 거짓이면 "불합격"을 출력하라는 내용이 됩니다. 

｜논리적 조건의 종류(참고)

안녕하세요!

김대리입니다. 

이번에는 유효성 검사를 설정하는 방법에 대해서 얘기하려고 합니다.

그럼, 데이터 유효성 검사가 무엇일까요? 

엑셀로 문서를 작성하는 경우가 많이 있으실겁니다.

제가 일하는 곳에서도 엑셀로 많은 자료를 관리하고 있습니다. 

먼저 위의 그림처럼 기존에 만들어져있는 문서에 정보를 추가하신다고 가정하겠습니다. 

직급에 커서를 가져가보았을때 이처럼 값을 기재하는 것이 아닌, 

'목록에서 선택(보기에서 선택)' 하게 하는 문서들이 있었을 것으로 생각합니다.

정확한 정보를 기재하게 하기 위해서 먼저 정보들을 확정지어 목록으로 만든 뒤, 

문서 작성자에게는 해당 목록에서 선택을 하게 하는 기능을 만들때

우리는 유효성 검사라는 기능을 사용하게 됩니다. 

즉, 유효성 검사란, '이 셀에 들어가는 정보가 유효한 정보인지 검사하는 기능' 이라고 할 수 있겠습니다. 

먼저 예제를 따라서 시작해보겠습니다. 

유효성검사 예제.xlsx

현재 예제에는 유효성 검사가 설정되어있지않습니다. 

우리는 소속, 직급, 입사일 모두에 유효성 검사를 생성시켜보도록 하겠습니다. 

가장 먼저 해야할 일은 정확한 소속과 직급을 기재하기위해 소속과 직급의 종류를 확인하는 것입니다. 

참석대상이 되는 팀과 회사에 존재하는 직급을 모두 확인한 뒤, 

새 시트를 만들어서 위와 같이 기재해주었습니다.(시트명은 '참조'로 하였습니다.)

A열과 B열은 제가 편의상 붙여서 적어두었을뿐 관계는 없습니다. (다른 시트에 작성하셔도 됩니다.)

다시 원래의 시트로 돌아와서 소속이 적혀있는 C열을 선택해줍니다.

(헤더가 적혀있는 C1셀은 제외했습니다.)

상단 메뉴에서 '데이터' - '데이터유효성검사'를 클릭합니다.

그러면 '데이터 유효성'이라는 메뉴가 뜨게되고 이 곳에서 우리는 데이터의 성격을 지정해줄 수 있습니다. 

'제한 대상'은 '이 셀에 들어가는 정보는 아래와 같은 값이어야 한다.'를 지정해주는 것입니다. 

-정수 : 정수만 들어가야한다.(예 : -8, 0, 2 등)

-소수점 : 소수점 이하의 숫자가 있어야 한다. (예 : -3.2, 1.4, 8.12 등)

-목록 : 지정한 목록에 있는 항목이어야 한다.

-날짜 : 날짜가 들어가야한다.

-시간 : 시간이 들어가야 한다.

-텍스트 길이 : 지정한 텍스트 길이에 맞는 값이어야 한다.

우리는 '소속'이라는 것을 지정해주고 싶기 때문에, '제한 대상'에서 '목록'을 선택해줍니다. 

'드롭다운 표시'란, 해당 셀을 선택했을때, 목록이 주르륵 나와서 선택할 수 있게 하는 기능입니다. 

하단의 '원본'을 선택 후, 우리가 위에서 만들었던 팀목록이 적힌 위치를 선택해줍니다. 

위의 그림처럼 자동으로 절대참조가 걸리게 됩니다. 

"설명 메시지" 탭에서는 해당 셀을 선택하였을 경우 드롭다운 표시가 되는 것처럼, 

셀에 대한 설명 메시지가 뜨도록 설정하는 내용입니다.

'제목' 부분에 "당신의 소속을 선택해주세요." 라고 기재해주도록 하겠습니다.

'설명 메시지' 부분에 존재하는 소속을 적도록 하겠습니다. 

"오류 메시지" 탭에서는 '유효하지 않은 값'을 입력했을때 

입력을 거절하며 오류를 표시하는 것을 설정하는 부분입니다.

-중지 : 메시지를 보여주며 입력이 거절됩니다.

-경고 : 메시지를 보여주며 입력이 가능합니다.

-정보 : 메시지를 보여줍니다. 

우리는 리스트에 모든 부서를 기재했기 때문에 '중지'를 선택 후 위와 같은 오류 메시지를 기재하겠습니다.

"IME 모드" 탭에서는 해당 셀을 선택하였을 경우 자판의 설정을 바꾸도록 하는 기능입니다. 

변경할 필요가 없으므로 현재 상태 유지로 확인을 눌러보겠습니다 .

소속에 해당하는 C열에 커서를 가져가면 메세지와 함께 드롭메뉴가 표시되며, 

해당하는 팀들을 선택할 수 있습니다.

드롭다운에서 선택하지 않고 저곳에 없는 '비서실'이라는 팀을 수기로 입력하게 되면

위와 같은 '오류 메시지'를 볼 수 있습니다.

똑같은 방법으로 D열의 직급을 선택하는 부분에 직급에 대한 유효성 검사를 추가해보시면 되겠습니다. 

E열의 입사일은 '데이터 유효성' 메뉴에서 

'제한 대상'을 날짜로 선택한 뒤 날짜 범위만 지정해주시면 

다른 기능은 똑같이 사용할 수 있습니다.

우리의 예제는 참석자가 10명 안쪽이기때문에 수기로 입력해도 상관이 없지만, 

극단적인 예로 참석자가 10,000명일 경우에는 데이터의 일관성이 중요한 문제로 부각됩니다.

(예 : 소속을 영업1팀, 영업 1팀, 영업1 팀, 영업 1 팀 등으로 기재할 경우 

피벗테이블에서 모든 팀이 각각의 팀으로 구성이 됩니다.)

이처럼 유효성 검사는 데이터의 정확도를 높혀서 데이터 분석의 정확도를 높히는 역할을 합니다.

감사합니다!

안녕하세요. 김대리입니다. 

이번에는 피벗테이블(PIVOT TABLE)에 대해서 얘기해보겠습니다. 

저는 피벗테이블이 엑셀에서 가장 유용한 기능이라고 생각합니다. 

피벗테이블이란, 

데이터가 나열되어있는 정보들을 활용하여,

필요한 정보를 표의 형태로 요약시킬 수 있는 기능입니다.

지난번에 SUMIFS 함수에 대해서 얘기하면서, 

위와 같은 데이터를 활용할 때에는 SUMIFS함수보다 피벗테이블이 유용하다고 적었었습니다. 

이번에는 똑같은 예제를 가지고 SUMIFS함수와 똑같은, 아래의 정보를 만들어보도록 하겠습니다. 

<SUMIFS함수로 만든 결과물>

피벗테이블을 만드는 것은 어렵지 않습니다. 

사실, 피벗테이블을 활용하기 위해서 자료를 맨 위의 예산표처럼 만드는 것과, 

피벗테이블을 만든 후 원하는 자료의 형태를 만드는것이 더 중요합니다. 

※예제 파일을 다운받아서 따라해주세요.

PIVOT table예제.xlsx

①먼저, 결과물(표)을 위치시킬 곳에 커서를 둔 뒤(저는 새 시트의 A1셀에 커서를 두었습니다.),

②상단 "삽입" 메뉴에서 가장 좌측에 있는 "피벗테이블"을 클릭합니다. 

(클릭 후 "피벗 테이블 만들기"라는 메뉴가 뜨면 두가지를 지정해야합니다.

<분석할 데이터>와 <피벗테이블보고서를 넣을 위치>가 그 두가지 입니다만, 

저와 똑같이 진행하셨다면 피벗테이블을 넣을 위치는 새시트 A1에 지정하였기때문에

<분석할 데이터>만 지정해주시면 됩니다.) 

③예산표를 드래그하여 지정해줍니다. (최상단의 헤더까지 포함시켜주셔야합니다.) 

(*A1:D23까지 지정해주셔도 됩니다만, 저는 그 아랫쪽까지 모두 포함하는 A:D로 지정하였습니다.)

③까지 완료하게 되면, 위와같은 피벗테이블이 생성됩니다. 

피벗테이블에 커서를 올리게 되면 우측에 피벗테이블필드가 생성되었을겁니다. 

이후에는 이 피벗테이블을 통해 원하는 정보를 만들면 되겠습니다. 

우리는 위와 같은 정보를 만들고자 하는 것이므로, 똑같은 모양을 만들어보겠습니다. 

만드는 방법은 간단합니다. 

상단에 있는 '필드' 들을 아래쪽에 있는 '필터', '열', '행', '값' 등에 드래그해서 옮겨주면 됩니다. 

위와같은 표의 모양을 만들기 위해 '회계연도'와 '기능'을 옮겨보겠습니다. 

이렇게 열과 행을 설정하게 되면, 좌측에 있는 피벗 테이블은 아래처럼 변하게 됩니다. 

이렇게 만들게 되면, 우리가 원하는 표의 모양이 완성되었습니다. 

이제 SUMIFS함수를 채워넣었듯 안쪽에 금액의 합계를 표시하기 위해, 

'피벗 테이블 필드'의 하단에 있는 '값'에 "금액"필드를 옮겨보겠습니다. 

이렇게 "금액"을 옮겨주게 되면

이와같이 우리가 원하는 데이터를 요약할 수 있습니다. 

SUMIFS함수를 설명하며, '이런 자료를 피벗테이블을 활용하는 것이 낫다.' 라고 말씀드렸던 이유는 아래와 같습니다.

회사에서 일을 하다보면, SUMIFS함수를 통해 자료를 만든 후에 정보이용자로부터 

Q. 예산항목별로 2017년 2018년 비교한 자료는 없나요? 

Q. 기능별 비교를 포함한 세부항목으로 볼 수는 없나요?

등의 새로운 요구를 받을 때가 많습니다. 

SUMIFS함수나 다른 함수를 활용할 경우 새로운 결과물을 짜기 위해서 함수도 알아야 하고, 

하나하나 오류가 생길 위험도 감수해야 합니다. 

하지만 피벗테이블은 '피벗 테이블 필드'에서 원하는 자료를 열과 행에 넣는것만으로 다양한 자료로 가공이 가능합니다. 

또한, 현재는 2017년과 2018년의 자료만 기재되어있지만, 2019년이 되고 2020년이 되었을때,

예산표의 하단에 2019년 자료를 추가만 해주신 후 피벗테이블을 새로고침하시면, 

추가된 자료까지 한번에 볼 수 있습니다. (이것이 피벗테이블 데이터영역을 A:D로 한 이유입니다.)

※주의할점 

A. 데이터를 추가한 뒤 '새로고침'을 누를 것.

B. 값 필드 설정에서 '값의 형태'를 잘 지정할 것(합계인지, 갯수인지)

C. 자료를 활용하다 행레이블, 열레이블에 누락되는 것이 있는지 확인.

.

안녕하세요! 김대리입니다.

이번에는 지~난번에 포스팅했던 SUMIF에서 개량(?)된 함수인 SUMIFS의 사용법에 대해서 적어보려고 합니다. 

(SUMIF 함수의 사용법은 요기!)

SUMIFS의 사용법

SUMIFS함수는 원하는 여러가지 정보에 해당하는 수의 합계를 구할때 사용합니다. 

위의 표는 "2018년 나라살림 예산개요"에서 추출한 대한민국 2017년, 2018년 예산 중 일부분을 발췌한 것입니다. 

이렇게 연도별 비교가 되어야 하는 자료는 세로줄로 작성하는 경우가 많이 없긴 하지만, 

예제를 위해 양식을 조금 변경해보았습니다. 

SUMIFS함수는 COUNTIFS함수와 마찬가지로, SUMIFS에서 사용된 자료와 비슷한 짜임이지만, 

원하는 정보를 추가하여 자료를 추출할 수 있습니다. 

아래로 길게 나열된 예산표에서 기능과 연도에 해당하는 예산의 총합을 구해보려고 합니다. 

(이러한 자료를 사용한다면, SUMIFS함수가 아닌 피벗테이블 기능을 사용하시는게 더 편리합니다.) 

SUMIF 함수에서는 하나의 조건만 지정할 수 있었지만, 

SUMIFS함수에서는 여러가지 조건을 동시에 지정하는것이 가능합니다.

SUMIFS함수의 의미는 

['지정한 범위ⓐ'에서, '특정한 값ⓐ'에 해당하면서 '동시에' , 

'지정한 범위ⓑ'에서, '특정한 값ⓑ'에 해당하는 항목을 찾아 

더하고 싶은 영역의 총합을 구한다] 

입니다. 

중요한것은 '동시에' 라는 부분입니다. 

SUMIF에서는 불가능했던 여러가지 조건을 모두 만족시키는 항목을 찾아 더하고 싶은 영역의 총합을 구할 수 있습니다.

위와같이 우리는 F2:H4의 표를 수식으로 채우고싶다고 가정하겠습니다. 

G3:H4 셀에는 위와 같이 그 기재한 내용을 참조하는 함수를 적어주시면 됩니다. 

함수는 아래의 사진처럼 구분되어있다고 보시면 됩니다.

함수의 구성은 

'=SUMIFS(

①총합을 구하고 싶은 영역,    

②범위ⓐ,값ⓐ,    

③범위ⓑ,값ⓑ)' 

이기 때문입니다. 

위처럼 가장 먼저 총합을 구하고 싶은 영역(예산액)을 설정한 뒤,

연도가 적힌 A2:A23 이라는 범위에서 G2에 적힌 특정한값(연도)에 해당하면서 """동시에""",

기능이 적힌 B2:B23 이라는 범위에서 F3에 적힌 특정한값(기능)에 해당하는것을 찾으면,

"2017년"의 "1. 일반, 지방행정" 예산의 총합이 얼마인지 구할 수 있습니다. 

+추가 : 절대참조를 설정해주고 H3, G4, H4에 붙여넣으면 원하는 자료를 완성할 수 있습니다. 

이를 한번에 정리해보면 아래와 같이 사용할 수 있습니다. 

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

조건을 여러개 추가하기만 하면 되는 것이기 때문에 어렵지 않게 사용할 수 있습니다. :)

(SUMIF 함수의 사용법은 요기!)

안녕하세요! 김대리입니다.

이번에는 지난번에 포스팅했던 COUNTIF에서 개량(?)된 함수인 COUNTIFS의 사용법에 대해서 적어보려고 합니다. 

=COUNTIFS

COUNTIFS는 다음과 같은 경우에 많이 사용됩니다. 

(COUNTIF 함수의 사용법은 요기!)

위의 표과 같은 자료를 많이 사용하고 계실 것으로 생각됩니다.

COUNTIF에서 사용된 자료와 비슷한 짜임이지만, 추가적인 정보가 기재되어있습니다. 

아래로 길게 나열된 표에서 직급, 소속, 입사년도를 지정하고, 그에 해당하는 인원이 몇명인지 세어보려고합니다.

(이해를 위해 직급등으로 사용했지만 성별, 국적, 고향 등등으로 적용 가능합니다.)

COUNTIF 함수에서는 하나의 조건만 지정할 수 있었지만, 

COUNTIFS함수에서는 여러가지 조건을 동시에 지정하는것이 가능합니다.

COUNTIFS함수의 의미는 

['지정한 범위ⓐ'에서, '특정한 값ⓐ'에 해당하면서 '동시에' , 

'지정한 범위ⓑ'에서, '특정한 값ⓑ'에 해당하는 셀의 갯수를 센다.] 

입니다. 

중요한것은 '동시에' 라는 부분입니다. 

COUNTIF에서는 불가능했던 여러가지 조건을 모두 만족시키는 셀의 갯수를 셀 수 있습니다. 

위와같이 I6:K7에 일단 찾고싶은 정보를 기재한 뒤에 

(이것저것 바꿔가며 적용하기 쉽게 하기 위해 셀에 적은것입니다. 

함수 안에 직접 "주임", "영업팀" 등을 적어도 함수는 유효합니다!)

I3 셀에는 위와 같이 그 기재한 내용을 참조하는 함수를 적어주시면 됩니다. 

함수는 위의 사진처럼 구분되어있다고 보시면 됩니다.

함수의 구성은 '=COUNTIFS(범위ⓐ,값ⓐ,    범위ⓑ,값ⓑ,    범위ⓒ,값ⓒ …)' 이기 때문입니다. 

직급이 적힌 C2:C20 이라는 범위에서 I7에 적힌 특정한값(직급)에 해당하면서 """동시에""",

소속이 적힌 D2:D20 이라는 범위에서 J7에 적힌 특정한값(소속)에 해당하면서 """또 동시에""",

입사년도가 적힌 E2:E20 이라는 범위에서 K7에 적힌 특정한값(입사년도)의 갯수를 센다는 의미이기 때문에,

여러가지 조건을 동시에 만족하는 2개라는 셀의 갯수를 셀 수 있습니다. 

이를 한번에 정리해보면 아래와 같이 사용할 수 있습니다. 

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

조건을 여러개 추가하기만 하면 되는 것이기 때문에 어렵지 않게 사용할 수 있습니다. :)

(COUNTIF 함수의 사용법은 요기!)

이번에는 함수 sumif에 대해서 적어보려고 합니다.

sumif는 다음과 같은 상황에서 쓰입니다. 

위와 같은 데이터가 있다고 가정을 해보겠습니다.

sumif 함수는 위와 같은 데이터베이스에서

"서울"이라는 지역 "인구"의 합이 얼마인지 계산할 때 쓰입니다.

sumif 함수의 의미는

[특정 범위에서, 특정한 값을 찾아서, 다른 범위에 있는 값을 '더한다'.]

입니다.

위처럼, "지역"의 범위에서 '서울'을 찾아서 "인구" 범위에 있는 숫자를 더할 수 있습니다.

함수의 구성은

=sumif[찾는 값이 있는 범위(지역), 찾고 싶은 값(서울), 더할 값이 있는 범위(인구)]

입니다.

H열처럼 입력을 하게 되면 "서울"을 찾아서 "인구"의 값을 더할 결과를 얻을 수 있습니다.

이런 식으로 이해해주시면 편할 것으로 생각됩니다. :)

이런 식으로 이해해주시면 편할 것으로 생각됩니다. :)

이번에 적어볼 함수는 vlookup입니다.

여러 분야에서 두루 쓰이는 함수로 알고 있습니다.

vlookup 함수는 다음과 같은 상황에서 쓰입니다.

위와 같은 데이터베이스를 많이 사용하고 계실 것으로 생각됩니다.

vlookup 함수는 예를 들어,

데이터베이스에서 "김○○"의 "고향"이라는 결과물을 뽑아내고 싶을때 사용합니다.

vlookup 함수의 의미는

['특정한 값'이 있는 행을, '표'에서 찾아서, 그 행의 '몇 번째' 값을]  가져온다.

입니다.

I열에 위와 같이 적어주시면 김○○의 "고향" 값을 가져올 수 있습니다.

함수의 구성은

'=vlookup(찾고 싶은 값을, 표의 범위에서 찾아서, 그 행의 몇 번째 값을,정확히or비슷하게)

입니다.

이런 식으로 이해해주시면 편할 것으로 생각됩니다.

주의해야 할 것은, 찾고 싶은 값이 표의 첫 번째 열에 있어야 한다는 점입니다.

표의 범위를 B열부터 지정하였기 때문에 김○○는 B열에 있어야 원하는 결과를 불러올 수 있습니다.

위에서 적혀있는 대로 구성할 경우

"좌측의 표"에서, "김○○"이 있는 행을 찾아, "3번째" 값을 가져오게 됩니다.(결과물 = 서울)

​

vlookup함수의 구성에서 세 번째 항목인 "몇 번째" 값을 지정할 때

1을 선택하면 "이름"이 적혀있는 열의 값을,

2를 선택하면 " 나이"가 적혀있는 열의 값을,

3을 선택하면 "고향"이 적혀있는 열의 값을,

4를 선택하면 "혈액형"이 적혀있는 열의 값을

불러오게 됩니다.

vlookup함수는 구성이 4가지로 되어있어 처음 사용하기에 어려움을 느낄 수 있지만,

이 함수를 사용하면 평소 관리하던 데이터베이스에서 원하는 값을 가져올 수 있기 때문에 더 수월한 결과물을 만들 수 있습니다. :)