HAKINA SUN

엑셀에서 수식을 통한 함수로 데이터를 나타내면 참조되는 데이터값이 변경되면 그에 따른 결과도 자동적으로 변경을 할 수 있습니다. 그런데, 서식 같은 경우에도 자동으로 변경되도록 지정할 수 있을까요?

서식의 경우에도 조건에 따라 사용자가 원하는 서식을 지정할 수 있습니다. 해당 데이터의 값에 따라 말이죠. 엑셀 기능중에서 '조건부서식'이라고 하는 기능을 통해서 해당 값에 따라 서식을 마음대로 지정할 수 있습니다.

'조건부 서식'에 대해 알아보도록 합시다!

1. '조건부 서식'이란 무엇인가?

'조건부 서식'이란 말그대로 데이터 값의 조건에 따른 서식을 지정할 수 있게 하는 엑셀의 기능 입니다.

2. '조건부 서식' 사용 방법!!

조건부 서식을 사용하는 방법을 살펴봅시다.

위 그림은은 고구마, 감자, 오이, 토란, 우엉, 당근, 토마토의 국어, 영어, 수학 점수와 평균을 정리한 표 입니다. 여기서 평균의 점수 구간에 따라 90점이상은 초록색, 80점이상은 노란색, 70점이상은 주황색, 70점미만은 빨간색으로 서식을 나타내고자 합니다.

1) 평균이 적혀있는 데이터를 드래그 합니다. 그런 후에 상단의 메뉴 조건부서식에서 새 규칙을 클릭해주세요.

그러면, '새서식 규칙'이라는 창이하나 나오게 됩니다.

2) '새 서식 규칙' 창에서 규칙 유형 선택에서 가장 아래에 있는 '▶ 수식을 사용하여 서식을 지정할 셀 결정'을 클릭합니다. 그런 다음 '다음 수식이 참인 값의 서식 지정'란에 아래와 같이 기입해주세요

=F3>=90

※ 여기서 F3은 드래그한 값의 제일 첫번째 데이터인 '71.3'에 해당하는 셀 입니다. 조건부 서식의 '▶ 수식을 사용하여 서식을 지정할 셀 결정'에서 수식의 기준이 되는 데이터는 첫번째 셀이니 이점 참고하셔서 수식을 만들어주시면 됩니다. 예를들어, 감자의 평균인 62.7에서 토마토 평균 67.3까지 드래그한 값을 조건부서식으로 지정하고자 할때에는 기준이 되는 셀은 감자의 평균인 62.7에 해당하는 F4셀로 수식을 만들어주면 되겠죠?

그런다음 서식을 눌러주고, 지정하고자 하는 서식을 설정해줍니다. 위에서 기입한 수식은 "=F3>=90", 90점 이상에 대한 값의 서식을 지정해주고 있으므로 초록색으로 지정합니다. '채우기' 에서 초록색을 선택하고 확인을 눌러줍니다.

위까지 90이상인 평균에 대해서 초록색으로 지정하는 조건부 서식을 설정했습니다. 80점, 70점 이상, 70점미만에 대해서도 색을 각각 노란색, 주황색, 빨간색으로 지정해봅시다.

80점이상 - 노란색

70점이상 - 주황색

70점미만 - 빨간색

이렇게 90점이상, 80점이상, 70점이상, 70점미만에 대한 조건부서식을 설정하고 나면, 결과값이 이상하게 나올것입니다.

3) 총 네가지 조건부서식을 지정했습니다. 이 네가지에 대한 순서를 지정해주어야 합니다. 그래서 조건부서식에서아래와 같이 '규칙 관리'를 클릭 해줍니다.

수식에 따라 빨간색, 주황색, 노란색, 초록색 네가지 조건부서식이 설정되어있는 것을 확인 할 수 있습니다. 여기서 조건부서식은 가장 위에서부터 조건부 서식을 먼저 따르게 됩니다. 이러한 이유때문에 값이 이상한 결과가 초래하게 된 것이죠.

90점이상인 데이터는 80점이상인 데이터에 포함되고, 80점이상인 데이터는 70점이상인 데이터에 중복 포함되기 때문에 이에 대한 순서를 정리해주어야 합니다. 첫번째로 90점이상인 데이터, 두번째로 80점 이상인 데이터, 세번째로 70점이상인 데이터, 네번째에 70점미만인 데이터가 오게 순서를 재배열해 주세요.

해당하는 조건부서식을 클릭하고 위, 아래 화살표를 클릭하셔서 순서를 재배열 해주세요. 그러면 결과는 아래와 같이 나타나게 됩니다.

※ 만약 90점이상은 초록색, 80점이상 90점미만은 노란색, 70점이상 80점미만은 주황색, 70점미만은 빨간색과 같이 구간이 정확하게 구분이 되면 순서를 재배열할 필요는 없습니다. 이때의 수식은 아래와 같이 기입해주시면 됩니다.

90점이상

=F3>=90

80점이상 90점미만

=AND(F3>=80,F3<90)

70점이상 80점미만

=AND(F3>=70,F3<80)

70점미만

=F3<70

조건에 따른 서식을 지정하는 기능인 조건부서식에 대해서 알아보았습니다.

엑셀에서 찾고 싶은 값이 몇번째에 있는지 알고 싶을때 어떻게 하시나요? 데이터가 많지 않을때에는 직접 세어가며 몇번째에 있는지 확인 할 수 있죠. 하지만, 데이터가 무수히 많을때에는 찾고 싶은 값이 몇번째 위치에 있는지 알기는 어렵습니다. 이때 사용하면 유용한 함수가 바로 'MATCH' 함수 입니다. MATCH 함수는 찾고자 하는 값이 몇번째 위치에 있는지 알 수 있기 때문에 INDEX 함수와 연계해서 사용하기도 하는데, INDEX 함수와 같이 사용하는 방법은 다른 편에서 알아보도록 하고, 이편에서는 MATCH 함수에 대해서 중점으로 다루어 보겠습니다.

1. MATCH 함수란 무엇인가!?

MATCH 함수는 수많은 데이터 속에서 내가 찾고자 하는 데이터가 몇번째 위치에 있는지 숫자로 표시해주는 함수 입니다. 다음 그림에서 보이는바와 같이 말이죠.

당근이 몇번째 있는가? 여섯번째 위치에 있다는 것을 바로 알 수가 있습니다. 좀 더 자세하게 MATCH 함수 사용방법에 대해서 알아보도록 하겠습니다.

2. MATCH 함수 사용 방법!!

=MATCH(lookup_value, lookup_array, [match_type])

=MATCH(찾을 값, 범위, 정확도) 

※ 정확도 : [ 1 ] 보다 작음 / [ 2 ] 정확히 일치 / [ -1 ] 보다 큼

예시) "감자, 고구마, 바나나, 사과, 오이, 당근, 양배추" 중에서 몇번째에 "당근"이 있는지 찾기

=MATCH("당근",B3:B9,0)

첫번째 인수 : 큰따옴표 안에 당근을 기입한다. "당근"

두번째 인수 : 범위 지정. B3:B9

세번째 인수 : 정확히 일치. 0

※ 결과 : 6

MATCH 함수에 대해서 알아보았습니다. MATCH 함수를 통해서 찾고자 하는 값이 몇번째 위치에 있는지 찾아보세요!

다른 편에서는 INDEX 함수와 MATCH 함수를 함께 사용하는 방법을 알아보도록 하겠습니다.

매달 동일한 문서를 작성하고 계신가요?

동일하지만 몇개의 숫자, 몇개의 글자만 바꿔야 되는 상황인가요?

문서 형식은 유사하지만 바꿔야될 값들이 많다구요?

그렇다면 이 방법을 추천해드립니다!

■ "데이터 값 자동 변경하기" 예시로 미리 알아보기

"OO 업체의 계약기간은 OO 입니다."

→ "HAKINASUN 업체의 계약기간은 2019.07.10 ~ 2019.08.10 입니다."

위 예시 그림에서 업체, 계약기간만 적어주면, 노란색 박스안의 문구가 자동으로 수정됩니다. 형식은 동일하지만, 업체명이나 기간 등을 달리 적용시켜 문서를 작성해야하는 경우 이 방법을 사용하면 아주 편리합니다.

■ "데이터 값 자동 변경하기" 방법

데이터 값 자동 변경하는 방법을 알아보도록 하겠습니다. 먼저, 노란색 박스안에 문구는 수식으로 구성되어야 어떠한 값이 들어오면 그것을 받아들여 자동으로 수정되어집니다.

1) ="(문구를 기입하세요.)"

우선 수식 '='을 이용해서 텍스트를 넣기 위해서는 텍스트가 큰따옴표 사이에 있어야 합니다. 예를들어,

="안녕하세요."

="반갑습니다."

와 같이 말이죠. 그러면 셀은 직접 텍스트를 친것과 같이 표현이 됩니다.

안녕하세요.

반갑습니다.

2) "&(셀 선택)&"

수식으로 텍스트를 넣는 방법을 알았으니, 다른 셀의 값을 읽을 수 있는 방법도 알아야겠죠? 그것이 바로 "&(셀 선택)&" 입니다. ="(문구)" 의 큰따옴표 안에 "&(셀선택)&"을 넣으면 다른 셀의 값을 읽어낼 수 있는 것이죠.

A1셀 : HAKINA SUN BLOG 입니다.

="안녕하세요. "&A1&""

안녕하세요. HAKINA SUN BLOG 입니다.

="반갑습니다. "&A1&""

반갑습니다. HAKINA SUN BLOG 입니다.

텍스트 수식에 "&(셀)&"을 넣어주면 다른 셀에 있는 값을 불러들여 화면상 표시를 할 수 있게 되었습니다. 이제 제일 처음 예시를 따라할 수 있게 되었습니다.

HAKINASUN 업체의 계약기간은 2019.07.10 ~ 2019.08.10 입니다.

이를 수식으로 나타내보면 아래와 같겠죠?

=""&C2&" 업체의 계약기간은 "&C3&" 입니다."

위 그림에서 텍스트 수식에서 두개의 "&(셀)&"이 적용된 것을 볼 수 있습니다. 두개 뿐만아니라 여러개의 값을 읽어낼 수 있습니다. 한개이 텍스트를 구성하기 위해 여러개의 셀에서 값을 읽어드릴 수 있으므로, 수식 구성만 잘한다면 정말 편리하게 사용할 수 있는 방법이 될 수 있습니다.

 동일한 문서에 몇개의 정보만 수정해야되는 경우 이 방법을 사용해보세요. 실수로 수정하지 못할 일도 없으며, 간단하고 쉽게 수정하고자 하는 값들을 변경할 수 있습니다.