Excel-엑셀 활용-02

엑셀 함수 활용에 대해 알아보겠습니다.

이번 포스트에서는 아래의 개념들이 사용됩니다.

index, match, 중복값 확인, if, ifs, and, or

01. if '조건'에 맞으면 '참 값', 아니면 '거짓 값'

=IF(조건,참,거짓)

조건은 여러가지로 걸 수가 있습니다.

>보다 클때, <보다 작을때

=같은 수, 텍스트, 셀 일때

그리고 조건으로 또 다른 함수도 넣을 수 있습니다.

스무고개를 하듯, IF(IF(조건,참,거짓),참,거짓)) 조건 안에 계속 함수를 넣을 수도 있죠.

=and(조건,조건) 나 =or(조건,조건) 으로 둘 이상의 조건을 한꺼번에 만족할 때나, 하나라도 만족하지 않을 때도 설정할 수 있습니다.

단점은 함수식이 길어진다는 것 말곤 없죠.

이 단점마저 극복하기 위해 2016버전부터는 =IFS(조건1,참1,조건2,참2...) 함수가 나왔습니다.

기존 IF는 참값의 조건만 설정할 수 있었다면 IFS는 거짓값의 조건도 설정할 수 있습니다. 거짓값도 그 값이 도출되는 조건을 달아주면 참이 되니까요. 

다만 함수값의 범위 설정에 빈틈이 생기면 오류가 발생하겠죠.

=IFS(B1>0,"0보다크다",B1<0,"0보다작다")

위 함수는 1이나 -1엔 제대로 된 값을 출력하지만 0이 참조값이 되면 오류가 발생합니다.

=IF(B1>0,"0보다크다","0보다작다")

IF함수일 경우 위와 같이 하면 0일 때는 거짓값이 출력됩니다. 맞고 틀리고를 떠나서 오류는 없죠.

대신 IFS 함수는 조건을 많이 달아서 정확한 값을 출력할 수 있다는 것이 장점입니다.

02. 수 많은 셀 중에서 내가 원하는 셀은 너다!

시트는 행과 열로 이뤄져있습니다.

조건이 한 두개쯤이라면 if함수나 vlookup, hlookup함수로 필요한 정보를 건질 수 있습니다.

하지만 조건이 세개, 네개가 되면 불가능합니다.

index함수는 좌표의 값을 뽑아내는 함수입니다. 색인을 알고 있다면 바로 해당 페이지를 펼칠 수 있는 것 같이 좌표를 알고 있다면 해당 정보를 바로 건질 수 있습니다.

=index(범위,행,열)

하지만 우리는 좌표도 모릅니다. 10x10짜리 표라면 세아릴 수 있지만 500x500짜리 표는 어떻습니까.

=match(찾을값,찾을범위,찾을방법)

match함수는 행 또는 열의 좌표를 산출하는 함수입니다.

찾고자 하는 값을 찾을값에 넣은 뒤, 찾을 범위를 행방향 혹은 열방향으로 지정해줍니다. 행방향으로 지정하면 행의 좌표를 얻을 것이고, 열방향으로 지정하면 열의 좌표를 얻습니다.

찾을방법은 0, -1, 1 세가지입니다.

0=정확히 일치하는 값

1=작거나 같은 값 중 최대값

-1=크거나 같은 값 중 최소값

찾는 값이 텍스트라면 0으로 하면 됩니다.

이 두 함수를 섞게 되면, 아주 정확한 셀 집게가 됩니다.

=index(범위,match(찾을값,찾을범위=행방향,찾을방법),match(찾을값, 찾을범위=열방향,찾을방법))

원하는 셀의 값의 뿅하고 튀어나옵니다.

03. 똑같은 게 저기 어디 있었는데...

중복된 값은 여러 방법으로 찾을 수 있는데 

가장 간단하고 시각적인 방법을 알아보겠습니다.

중복값을 알아볼 범위를 선택한 뒤, 조건부서식>셀강조규칙>중복값을 눌러주면 됩니다.

중복값을 가진 셀들이 지정된 셀서식으로 변합니다.