Excel의 마스터 수식 기반 조건부 서식

조건부 서식은 스프레드시트 값을 기반으로 색상, 아이콘, 굵은 글꼴 등의 서식을 자동으로 적용하는 강력한 Excel 기능입니다. Excel에서는 조건부 서식을 위한 사용하기 쉬운 기본 제공 규칙을 제공하지만 수식 기반 규칙은 더 큰 유연성을 제공합니다. 이 튜토리얼에서는 표준 내장 옵션보다 훨씬 뛰어난 유연성을 제공하는 수식 기반 규칙을 사용하는 고급 조건부 서식을 보여줍니다.

수식 기반 조건부 서식 규칙 생성

조건부 서식은 특정 조건에 따라 작동하며 수식 기반 사용자 정의 규칙도 제공합니다. 수식 기반 조건부 서식은 워크시트의 데이터를 평가하는 사용자 지정 수식을 사용하여 규칙을 정의합니다. 수식이 TRUE를 반환하면 서식이 적용됩니다. 수식 기반 규칙을 적용하려면 특정 단계를 따라야 합니다:

• 조건부 서식을 적용할 셀 범위를 선택하세요.
• 홈으로 이동 탭>> 조건부 서식에서>> 새 규칙을 선택합니다. .
• 새 서식 규칙 대화 상자;
  • 수식을 사용하여 서식을 지정할 셀 결정 옵션을 선택합니다. .
  • 이 수식이 참인 형식 값 상자;
    • 맞춤 수식을 삽입하세요.
  • 형식을 클릭하세요. 원하는 형식을 선택하세요.
  • 확인을 클릭하세요. .

수식 기반 규칙을 사용하여 고급 조건부 서식을 적용하는 판매 데이터세트를 고려해 보겠습니다.

1. 조건에 따라 행 강조

매출이 특정 값보다 큰 행을 강조 표시하려면 다음 비교 공식을 사용하면 됩니다.

수식:

=$G2>=5000

이 수식은 C2 셀(매출)의 값이 5000보다 큰지 확인합니다. 행 전체에서 비교가 일관되면 열에 절대 참조($)를 사용하세요.

• 형식을 클릭하세요. 선호하는 서식 옵션을 설정하고 녹색을 선택했습니다.
• 확인 을 클릭하세요. 규칙을 적용합니다.

2. 중복된 판매 항목 강조 표시

데이터세트에서 중복된 판매 항목을 강조표시하여 해당 항목을 제거할 수 있습니다. 다음 수식을 삽입하세요.

수식:

=COUNTIFS($A$2:$A$17, $A2, $B$2:$B$17, $B2, $C$2:$C$17, $C2, $D$2:$D$17, $D2, $E$2:$E$17, $E2, $F$2:$F$17, $F2, $G$2:$G$17, $G2)> 1

이 수식은 A부터 G까지의 각 열을 확인하여 각 행의 값 조합이 지정된 범위 내에서 중복되는지 확인한 다음 A부터 G 열에 있는 각 행의 고유한 값 조합을 계산합니다. 개수가 1보다 크면 행이 중복된 것으로 간주됩니다. 그런 다음 해당 행이 중복 항목으로 강조 표시됩니다.

• 형식을 클릭하세요. 을 클릭하고 형식을 선택하면 노란색 채우기가 선택됩니다.
• 확인을 클릭하세요. 규칙을 적용합니다.

3. 한 행씩 걸러 색상 지정(얼룩말 줄무늬)

데이터 테이블의 형식을 지정하고 읽기 쉽게 만들기 위해 행 하나 걸러마다 색상을 추가할 수 있습니다. 이 형식을 추가하려면 다음 공식을 사용하세요.

수식:

=MOD(ROW(),2)=0

ROW() 함수는 현재 행 번호를 반환합니다. 그런 다음 MOD(ROW(),2)=0은 행 번호가 짝수인지 확인합니다.

• 형식을 클릭하세요. 을 클릭하고 형식을 선택하면 밝은 회색 배경 채우기를 선택했습니다.
• 확인을 클릭하세요. 규칙을 적용합니다.

4. 지난 날짜 강조

오늘 날짜가 지난 날짜를 강조표시하여 연체된 항목을 식별하려면 TODAY 수식을 사용하면 됩니다. 기능입니다.

수식:

=AND(ISNUMBER(A2), A2 <오늘())

이 수식은 A2의 날짜가 오늘 날짜 이전인지 확인하여 데이터세트의 과거 날짜를 강조표시합니다.

• 형식을 클릭하세요. 을 클릭하고 형식을 선택하면 빨간색 채우기가 선택됩니다.
• 확인을 클릭하세요. 규칙을 적용합니다.

5. 상위 10% 판매 가치 강조

판매 데이터세트에서 매출의 상위 10%를 강조표시할 수 있습니다. 최고 매출을 강조하려면 PERCENTILE 기능을 사용할 수 있습니다.

수식:

=$G2>=PERCENTILE($G$2:$G$17,0.9)

PERCENTILE($G$2:$G$17,0.9)는 범위의 90번째 백분위수를 계산하므로 이보다 크거나 같은 값이 상위 10%에 포함됩니다. G 열의 값이 이 백분위수보다 크거나 같으면 서식이 전체 행에 적용됩니다.

• 형식을 클릭하세요. 을 클릭하고 서식을 선택하면 녹색 채우기 색상이 있는 굵은 텍스트가 선택되었습니다.
• 확인을 클릭하세요. 규칙을 적용합니다.

6. 여러 조건 결합

여러 조건을 논리 함수와 결합하여 복잡한 조건부 서식 규칙을 만들 수 있습니다. AND/OR 함수를 사용하여 여러 조건에 따라 셀을 강조 표시합니다.

수식:

=AND($G2>=3000, $C2=”동쪽”)

이 수식은 매출이 3000보다 크거나 같고 지역이 동부인 경우에만 행을 강조 표시합니다.

• 형식을 클릭하세요. 을 클릭하고 형식을 선택하면 기울임꼴 글꼴과 복숭아색 채우기 색상이 선택됩니다.
• 확인을 클릭하세요. 규칙을 적용합니다.

조건부 서식 규칙 관리

조건부 서식을 관리하려면:

• 조건부 서식으로 이동 탭>> 규칙 관리를 선택합니다. .
• 여기에서 규칙의 우선순위를 확인, 추가, 수정, 삭제, 복제 또는 변경할 수 있습니다.
• '적용 대상'을 사용할 수도 있습니다. 필드를 사용하여 규칙을 적용할 셀 범위를 수정하세요.

결론

Excel에서 수식 기반 규칙과 함께 고급 조건부 서식을 사용하여 데이터를 강조 표시할 수 있습니다. 데이터를 시각화하는 유연한 방법을 제공합니다. 특정 조건을 기반으로 셀을 강조 표시하거나, 중복 항목을 관리하거나, 여러 기준을 결합하거나, 마감일을 확인하는 경우 이러한 수식 기반 규칙이 도움이 됩니다. 이 기능을 익히면 스프레드시트가 더욱 통찰력 있고 시각적으로 매력적으로 만들어집니다. 다양한 수식을 실험하여 작동 방식을 확인하고 스프레드시트를 향상시키세요.