수식에 기반한 조건부 서식 지정 – Excel 및 Google 스프레드시트

이 튜토리얼에서는 Excel 및 Google 스프레드시트에서 수식을 기반으로 조건부 서식을 적용하는 방법을 설명합니다.

Excel의 조건부 서식에는 콘텐츠에 따라 셀의 서식을 빠르게 지정할 수 있는 다양한 사전 설정 규칙이 있습니다. 하지만 이러한 사전 설정 규칙에는 한계가 있으므로 수식을 사용하여 셀의 서식을 결정하는 것이 좋습니다. 수식을 사용하면 조건부 서식을 훨씬 더 유연하게 사용할 수 있습니다.

조건부 서식 지정 수식 규칙

다음과 같음

1. 조건부 서식을 설정하려는 셀을 강조 표시한 다음 리본에서 홈 > 조건부 서식 > 새 규칙을 선택합니다.

2. 수식 사용을 선택하여 서식을 지정할 셀을 결정하고 수식을 입력합니다:
   =$C4=”서부”

수식은 항상 등호로 시작해야 합니다.

수식은 기존 Excel IF 문과 마찬가지로 결과가 참인지 거짓인지 테스트하지만 조건부 서식 지정의 경우 참 및 거짓 조건을 추가할 필요가 없습니다. 수식이 TRUE를 반환하면 규칙의 서식 부분에서 설정한 서식이 관련 셀에 적용됩니다.

열을 고정시키고(절대참조로 만들고) 행을 상대참조로 만들려면 이 수식($C4)에서 혼합 참조를 사용해야 합니다. 이렇게 하면 기준을 충족하는 단일 셀이 아닌 전체 행에 서식을 지정할 수 있습니다.

범위의 모든 셀에 대해 규칙이 평가될 때, 행은 변경되지만 열은 변경되지 않습니다. 따라서 규칙은 다른 열의 값을 무시하고  C열의 값에만 집중하게 됩니다. 해당 행의 C열에 서부라는 단어가 포함되어 있으면 수식 결과가 TRUE가 되고 전체 행에 서식이 적용됩니다.

3. 서식...을 클릭하고 원하는 서식을 선택합니다.

4. 확인을 클릭한 다음 다시 확인을 클릭하여 결과를 확인합니다.

열 C가 서부인 행은 강조 표시되고 다른 행은 강조 표시되지 않습니다. 위의 예에서는 두 행만 강조 표시됩니다.

동일하지 않음

반대 규칙을 만들려면 = 대신<>(같지 않음) 연산자를 사용할 수 있습니다.

=$C4<>”서부”

이렇게 하면 반대의 결과가 생성됩니다: 이제 대부분의 행이 강조 표시됩니다!

초과

수식을 사용하여 보다 큰 값인지를 테스트할 수도 있습니다. 예를 들어 수식을 사용하여 D 열의 값이 $23,000보다 큰지 확인할 수 있습니다.

=$D4>23000

이 규칙은 D 열의 값이 $23,000보다 큰 행만 강조 표시합니다.

다음보다 크거나 같음

마찬가지로, 아래 수식을 사용하여 D의 값이 $23,000보다 크거나 같은 행을 강조 표시할 수 있습니다:

=$D4>=23000

이 수식의 결과는 초과 수식의 결과와 약간 달라집니다: 빨간색 값이 정확히 $23,000인 행도 하이라이트됩니다.(여기서는 5행)

미만

기호를 < 기호로 바꾸면 $23,000보다 작은 값이 있는지 테스트합니다.

=$D4<23000

이제 빨간색이 $23,000 미만인 행만 강조 표시됩니다.

다음보다 작거나 같음

대상 값(예: $23,000)을 포함하도록 테스트를 변경하려면 <=를 사용합니다.

=$D4<=23000

이제 D열에 $23,000와 같은 모든 행도 강조 표시됩니다.

OR, AND를 사용하여 규칙 만들기

OR 수식

Excel OR 함수를 통합해 수식을 확장하여 두 개 이상의 값을 찾는 더 복잡한 규칙을 만들 수 있습니다.

1. 수식을 입력합니다:
   =OR($C4=”서부”,$C4=”동부”)

2. 서식... 버튼을 클릭하고 원하는 서식을 선택한 다음 확인을 클릭하여 Excel로 돌아갑니다.

이렇게 하면 지역이 서부 또는 동부인 모든 행이 강조 표시됩니다.

AND 수식

마찬가지로 AND를 사용하여 규칙을 만들 수 있습니다. 두 개 이상의 열의 값을 확인하기 위해 활용 할 수 있습니다.

위의 수식은 C열의 텍스트가 북부이고 D열 값이 $23,000보다 큰지 여부를 확인합니다.

이 조건부 서식 지정 규칙은 두 조건이모두 충족되는 행만 강조 표시합니다.

IF 수식

조건부 서식 지정 규칙에서 수식을 만들 때 조건부 서식 지정은 생성한 수식이 참 값을 반환하는 경우 항상 규칙을 적용하므로 수식에 IF 문을 추가할 필요가 없습니다.

예를 들어 이 수식

=IF($C4=”서부”,TRUE,FALSE)

와 이 수식

c4=”서부”

는 모두 워크시트에 동일한 조건부 서식을 적용합니다.

기본 제공 Excel 함수

많은 기본 제공 Excel 함수를 조건부 서식 지정에 사용할 수 있습니다.(예: ISBLANK, ISERROR, ISEVEN, ISNUMBER 등)

ISBLANK 함수

ISBLANK 함수를 사용하여 워크시트에서 공백 또는 빈 셀이 포함된 행을 찾는 규칙을 만들 수 있습니다.

1. 범위를 강조 표시한 다음 리본에서 홈 > 조건부 서식 > 새 규칙을 선택합니다.
2. 수식을 입력합니다:
   =ISBLANK($E3)

3. 서식… 버튼을 클릭하고 원하는 서식을 선택한 다음 확인을 클릭하여 Excel로 돌아갑니다.

ISBLANK와 OR

빈 셀이나 오류가 있는 셀을 포함하도록 확장할 수 있습니다.

1. 수식을 입력합니다
   =or(isblank($e3),iserror($e3))

2. 서식... 버튼을 클릭하고 원하는 서식을 선택한 다음 확인을 클릭하여 Excel로 돌아갑니다.

조건부 서식의 다른 함수

조건부 서식 지정 수식 기반 규칙을 만드는 데 사용할 수 있는 다양한 다른 함수가 있습니다.

• COUNT – 특정 조건을 충족하는 셀을 계산합니다. ( 중복을 강조 표시하는 데 유용)
• LARGE – k번째로큰 값을 찾습니다
• MAX – 가장 큰 숫자를 찾습니다
• MIN – 가장 작은 숫자를 찾습니다
• MOD – 나누기 후 나머지를 반환합니다.( 모든 행을 강조 표시할 때 유용)
• NOT – TRUE를 FALSE로 또는 그 반대로 변경합니다
• NOW – 현재 날짜 및 시간을 반환합니다( 조건부로 날짜 및 시간 서식을 지정할 때 유용)
• ROW – 배열의 행 수를 반환합니다
• SEARCH – 다른 문자열 내에서 텍스트 문자열을 검색합니다( 특정 텍스트를 찾을 때 유용)
• TODAY – 현재 날짜를 반환합니다( 날짜에 조건부 서식을 적용하는 데 유용)
• VLOOKUP – 세로 조회 결과를 반환합니다
• WEEKDAY – 요일을 반환합니다
• XLOOKUP – 새롭고 더 강력한 조회 기능

예를 들어 MAX 함수를 사용하여 지정된 열에서 최대값이 포함된 행을 강조 표시할 수 있습니다.

=$D4=MAX($D:$D)

이제 D열에서 최대값이 있는 행만 강조 표시됩니다.

조건부 서식 지정 규칙을 만들 때 수식을 적용하는 데 어려움을 겪고 있다면 먼저 Excel 시트에서 수식을 테스트해 보는 것이 좋습니다. 수식이 제대로 작동하는지 확인하면 규칙에 통합할 수 있습니다.

Google 스프레드시트의 수식에 기반한 조건부 서식 지정

조건부 서식은 Excel에서와 마찬가지로 Google 스프레드시트에서도 동일하게 작동합니다.

1. 조건부 서식을 설정하려는 셀을 강조 표시한 다음 메뉴에서 서식 > 조건부 서식을 선택합니다.

2. 적용 범위가 올바른지 확인한 다음 서식 규칙 드롭다운 상자에서 사용자 지정 수식을 선택합니다. 수식을 입력합니다.

3. 그런 다음 서식 스타일을 선택하고 완료를 클릭합니다.

Google 스프레드시트에서 보다 큼, 보다 크거나 같음, 보다 작음, 보다 작거나 같음을 사용할 수도 있습니다.

AND 및 OR도 사용할 수 있으며, 위에 나열된 Excel의 다른 함수도 사용할 수 있습니다.(단, Google 스프레드시트에는 존재하지 않는 XLOOKUP은 제외)