뛰어나다

수식이 있는 조건부 서식(10개의 예)

Conditional Formatting With Formulas

빠른 시작 | 예 | 문제 해결 | 훈련

조건부 서식은 스프레드시트에서 데이터를 빠르게 시각화하는 환상적인 방법입니다. 조건부 서식을 사용하면 다음 30일 동안의 날짜 강조 표시, 데이터 입력 문제 플래그 지정, 상위 고객이 포함된 행 강조 표시, 중복 표시 등의 작업을 수행할 수 있습니다.





Excel에는 수식 없이 새 규칙을 쉽게 만들 수 있는 많은 '사전 설정'이 포함되어 있습니다. 그러나 고유한 사용자 지정 수식을 사용하여 규칙을 만들 수도 있습니다. 고유한 공식을 사용하여 규칙을 트리거하는 조건을 인수하고 필요한 논리를 정확히 적용할 수 있습니다. 공식은 최대의 힘과 유연성을 제공합니다.

예를 들어 '같음' 사전 설정을 사용하면 '사과'와 같은 셀을 쉽게 강조 표시할 수 있습니다.





그러나 '사과', '키위' 또는 '라임'과 같은 셀을 강조 표시하려면 어떻게 해야 합니까? 물론 각 값에 대한 규칙을 만들 수는 있지만 많은 문제가 있습니다. 대신 다음이 포함된 공식을 기반으로 한 규칙을 간단히 사용할 수 있습니다. OR 함수 :

x, y 또는 z를 강조 표시하는 규칙



다음은 이 스프레드시트에서 B4:F8 범위에 적용된 규칙의 결과입니다.

OR 함수를 사용한 조건부 서식

사용된 정확한 공식은 다음과 같습니다.

 
= OR (B4='apple',B4='kiwi',B4='lime')

빠른 시작

다음 네 단계로 수식 기반 조건부 서식 규칙을 만들 수 있습니다.

1. 서식을 지정할 셀을 선택합니다.

서식을 지정할 셀 선택

2. 조건부 서식 규칙을 만들고 수식 옵션을 선택합니다.

수식 옵션 선택

3. TRUE 또는 FALSE를 반환하는 수식을 입력합니다.

활성 셀을 기준으로 수식 입력

4. 서식 옵션을 설정하고 규칙을 저장합니다.

서식 옵션 설정

NS ISODD 기능 규칙을 트리거하는 홀수에 대해서만 TRUE를 반환합니다.

함수의 경우 Excel에서 크거나 같음을 쓰는 방법

ISODD 함수는 홀수에 대해 TRUE를 반환하여 규칙을 트리거합니다.

동영상: 수식으로 조건부 서식을 적용하는 방법

우리는 또한 제공합니다 이 주제에 대한 비디오 교육 .

수식 논리

조건부 서식을 적용하는 수식은 TRUE 또는 FALSE 또는 이에 상응하는 숫자를 반환해야 합니다. 여기 몇 가지 예가 있어요.

Excel에서 레이블을 변경하는 방법
 
= ISODD (A1) = ISNUMBER (A1) =A1>100 = AND (A1>100,B1<50) = OR (F1='MN',F1='WI')

위의 수식은 모두 TRUE 또는 FALSE를 반환하므로 조건부 서식에 대한 트리거로 완벽하게 작동합니다.

조건부 서식이 셀 범위에 적용되면 선택 항목의 첫 번째 행과 열(즉, 왼쪽 상단 셀)에 대한 셀 참조를 입력합니다. 조건부 서식 수식이 작동하는 방식을 이해하는 비결은 동일한 수식이 적용되는 것을 시각화하는 것입니다. 선택 영역의 각 셀 , 평소와 같이 셀 참조가 업데이트되었습니다. 선택 영역의 왼쪽 상단 셀에 수식을 입력한 다음 전체 선택 영역에 수식을 복사했다고 상상해 보십시오. 이것으로 어려움을 겪고 있다면 섹션을 참조하십시오. 더미 공식 아래에.

수식 예

다음은 조건부 서식을 적용하는 데 사용할 수 있는 사용자 지정 수식의 예입니다. 이러한 예제 중 일부는 셀 강조 표시를 위한 Excel의 기본 제공 사전 설정을 사용하여 만들 수 있지만 사용자 지정 수식은 아래에서 볼 수 있듯이 사전 설정을 훨씬 능가할 수 있습니다.

또한 다음을 참조하십시오. 30개 이상의 조건부 서식 수식

텍사스에서 주문 강조

텍사스(TX)의 주문을 나타내는 행을 강조 표시하려면 열 F에 대한 참조를 잠그는 공식을 사용하십시오.

 
=$F5='TX'

수식을 사용하여 상태 =인 행 강조 표시

자세한 내용은 다음 문서를 참조하세요. 조건부 서식이 있는 행 강조 표시 .

동영상: 조건부 서식으로 행을 강조 표시하는 방법

다음 30일의 날짜 강조 표시

다음 30일 이내에 발생하는 날짜를 강조 표시하려면 (1) 날짜가 미래인지 확인하고 (2) 날짜가 오늘로부터 30일 이하인지 확인하는 공식이 필요합니다. 이를 수행하는 한 가지 방법은 다음을 사용하는 것입니다. AND 함수 함께 지금 기능 이와 같이:

 
= AND (B4> NOW (),B4<=( NOW ()+30))

현재 날짜가 2016년 8월 18일인 경우 조건부 서식은 날짜를 다음과 같이 강조 표시합니다.

다음 30일의 날짜를 강조 표시하는 조건부 서식

NS 지금 기능 현재 날짜와 시간을 반환합니다. 이 수식의 작동 방식에 대한 자세한 내용은 다음 문서를 참조하세요. 다음 N일의 날짜 강조 표시 .

열 차이점 강조 표시

유사한 정보가 포함된 두 개의 열이 있는 경우 조건부 서식을 사용하여 미묘한 차이를 찾을 수 있습니다. 아래 서식을 트리거하는 데 사용되는 수식은 다음과 같습니다.

 
=$B4$C4

열을 비교하기 위한 조건부 서식

또한보십시오: 대소문자를 구분하는 비교를 수행하기 위해 EXACT 함수를 사용하는 이 공식의 버전 .

누락된 값 강조 표시

한 목록에서 다른 목록에서 누락된 값을 강조 표시하려면 다음을 기반으로 수식을 사용할 수 있습니다. COUNTIF 함수 :

 
= COUNTIF (list,B5)=0

조건부 서식으로 누락된 값 강조 표시

이 수식은 단순히 각 값을 확인합니다. 목록 A 명명된 범위 'list'(D5:D10)의 값에 대해. 개수가 0이면 수식은 TRUE를 반환하고 규칙을 트리거하여 값을 강조 표시합니다. 목록 A 에서 누락된 것 목록 B .

동영상: COUNTIF로 누락된 값을 찾는 방법

0,000 미만의 침실이 3개 이상 있는 부동산을 강조하세요.

이 목록에서 침실이 3개 이상 있지만 0,000 미만인 속성을 찾으려면 AND 함수를 기반으로 하는 공식을 사용할 수 있습니다.

 
= AND ($C5<350000,$D5>=3)

달러 기호($)는 C 및 D 열에 대한 참조를 잠그고 AND 함수 두 조건이 모두 TRUE인지 확인하는 데 사용됩니다. AND 함수가 TRUE를 반환하는 행에는 조건부 서식이 적용됩니다.

속성 목록을 강조 표시하는 조건부 서식

상위 값 강조 표시(동적 예)

Excel에는 '상위 값'에 대한 사전 설정이 있지만 이 예에서는 수식을 사용하여 동일한 작업을 수행하는 방법과 수식이 더 유연해질 수 있는 방법을 보여줍니다. 수식을 사용하여 워크시트를 대화형으로 만들 수 있습니다. F2의 값이 업데이트되면 규칙이 즉시 응답하고 새 값을 강조 표시합니다.

상위 값에 대한 동적 조건부 서식

이 규칙에 사용된 공식은 다음과 같습니다.

텍스트 파일을 Excel 2013으로 변환
 
=B4>= LARGE (data,input)

여기서 'data'는 명명된 범위 B4:G11이고 'input'은 명명된 범위 F2입니다. 이 페이지에는 세부 사항 및 전체 설명 .

간트 차트

믿거 나 말거나 수식을 사용하여 다음과 같은 조건부 서식으로 간단한 Gantt 차트를 만들 수도 있습니다.

조건부 서식을 사용하여 Gantt 차트 만들기

이 워크시트는 막대에 대한 규칙과 주말 음영에 대한 규칙의 두 가지를 사용합니다.

 
= AND (D>=$B5,D<=$C5) // bars = WEEKDAY (D,2)>5 // weekends

이 문서에서는 막대의 공식을 설명합니다. , 그리고 이 기사에서는 주말 음영 공식을 설명합니다. .

간단한 검색창

조건부 서식으로 할 수 있는 멋진 트릭 중 하나는 간단한 검색 상자를 만드는 것입니다. 이 예에서 규칙은 F2 셀에 입력된 텍스트가 포함된 B열의 셀을 강조 표시합니다.

조건부 서식 검색 상자

사용된 공식은 다음과 같습니다.

 
= ISNUMBER ( SEARCH ($F,B2))

자세한 내용과 전체 설명은 다음을 참조하세요.

문제 해결

조건부 서식 규칙을 올바르게 실행할 수 없으면 수식에 문제가 있을 가능성이 큽니다. 먼저 등호(=)로 수식을 시작했는지 확인합니다. 이 단계를 잊어버리면 Excel은 자동으로 전체 수식을 텍스트로 변환하여 쓸모 없게 만듭니다. 수정하려면 Excel 양쪽에 추가된 큰따옴표를 제거하고 수식이 등호(=)로 시작하는지 확인하세요.

수식을 올바르게 입력했지만 규칙을 실행하지 않는 경우 조금 더 깊이 파고 들었을 수 있습니다. 보통, F9 키를 사용하여 수식의 결과를 확인하거나 평가 기능을 사용하여 수식을 단계별로 실행할 수 있습니다. 유감스럽게도 조건부 서식 수식에는 이러한 도구를 사용할 수 없지만 '더미 수식'이라는 기술을 사용할 수 있습니다.

더미 공식

더미 수식은 워크시트에서 직접 조건부 서식 수식을 테스트하는 방법이므로 실제로 수행하는 작업을 볼 수 있습니다. 이것은 셀 참조가 올바르게 작동하도록 고군분투할 때 시간을 크게 절약할 수 있습니다.

간단히 말해서 데이터 모양과 일치하는 셀 범위에 동일한 수식을 입력합니다. 이를 통해 각 수식에서 반환된 값을 볼 수 있으며 수식 기반 조건부 서식이 작동하는 방식을 시각화하고 이해할 수 있습니다. 자세한 설명을 위해, 이 기사를 참조하십시오 .

더미 수식을 사용하여 조건부 서식 수식 확인

동영상: 더미 수식으로 조건부 서식 테스트

제한 사항

수식 기반 조건부 서식에는 다음과 같은 몇 가지 제한 사항이 있습니다.

  1. 사용자 지정 수식에는 아이콘, 색상 눈금 또는 데이터 막대를 적용할 수 없습니다. 숫자 형식, 글꼴, 채우기 색상 및 테두리 옵션을 포함한 표준 셀 형식으로 제한됩니다.
  2. 조건부 서식 기준에 합집합, 교집합 또는 배열 상수와 같은 특정 수식 구성을 사용할 수 없습니다.
  3. 조건부 서식 수식에서 다른 통합 문서를 참조할 수 없습니다.

때때로 #2와 #3을 해결할 수 있습니다. 수식의 논리를 워크시트의 셀로 이동한 다음 대신 수식에서 해당 셀을 참조할 수 있습니다. 배열 상수를 사용하려는 경우 대신 명명된 범위를 만들어 보십시오.

더 많은 CF 공식 리소스

  • 30개 이상의 조건부 서식 수식 예
  • 연습 워크시트를 사용한 비디오 교육
저자 데이브 브런스


^