실용적인 엑셀 스킬 공유 : '조건부서식'과 '함수수식'을 함께 사용

이전 글에서 "엑셀 실무능력 공유하기: '데이터 유효성'은 이렇게 활용 가능해요! "에서는 데이터 효율성을 더욱 효율적으로 만드는 3가지 팁을 배웠습니다. 오늘은 함수 수식을 이용한 조건부 서식에 대해 두 가지 예를 통해 "조건부 서식"과 "함수 수식"이 어떻게 함께 사용되는지 소개하겠습니다.

누구나 조건부 서식을 사용할 수 있지만, 100행의 데이터에서 각 행의 최대값을 동시에 찾아 레이블을 지정하는 방법을 모르시는 것 같습니다. 오늘은 함수 수식에 조건부 서식을 사용하는 방법을 알아보기 위해 두 가지 예를 사용하겠습니다.

예시 1 [다중 행 데이터의 최고 점수 색상 표시]

최대값을 표시하기 위해 조건부 서식을 사용한다고 말하면, "항목 선택 규칙"에 있는 항목을 사용하는 것은 다음과 같이 모두가 알고 있다고 믿습니다. 아래 그림에서는 수행할 수 있지만 이 작업은 상대적으로 제한됩니다. 100개의 데이터 행이 있는 경우 각 행의 가장 높은 값을 동시에 표시하려면 어떻게 해야 할까요? 이 문제를 해결하는 공식을 설명하겠습니다.

최근 엑셀, 워드, ppt, 종합 등 4개 과목에 대한 평가를 실시합니다. 학생의 시험 점수가 표에 기록됩니다. 이제 각 학생의 최고 점수가 녹색으로 표시된 셀을 채워야 합니다.

작업 단계:

(1) D2:G13 데이터 영역을 선택하고 [시작] 탭에서 [조건부 서식]을 클릭한 후 [새 규칙]을 선택합니다.

(2) [새 서식 규칙] 대화 상자를 열고 규칙 유형 [수식을 사용하여 서식을 지정할 셀 결정]을 선택합니다. 아래 편집 규칙 설명에 수식=AND(D2=MAX($D2:$G2),D2"")을 입력하세요. [서식]을 클릭하면 셀 채우기 색상이 녹색으로 설정됩니다.

함수 수식 분석

(1) D2=MAX($D2:$G2)는 D2가 $D2:$G2 영역의 최대값과 같은지 확인합니다. TRUE를 반환하고, 그렇지 않으면 FALSE를 반환합니다. 열 좌표에는 $ 기호가 추가되어 절대 참조를 나타내므로 D-G 열에서 데이터가 판단됩니다. 행 좌표는 한 행 아래로 이동할 때마다 수식이 해당 행 좌표로 자동 변경됩니다.

(2) D2""는 D2가 빈 셀보다 크거나 작으면 TRUE가 반환되고, 그렇지 않으면 FALSE가 반환됨을 의미합니다.

(3) AND 함수는 위의 두 조건이 모두 참인지 확인합니다. 둘 다 참이면 TRUE를 반환하고 셀이 녹색으로 채워집니다.

서식 규칙을 편집한 후 [확인]을 클릭하여 [조건부 서식 관리자]에 들어갑니다 (참고: 조건부 서식 관리자는 설정된 조건부 서식을 저장하는 것으로 이해하면 됩니다)

관리자에서 제가 본 방금 설정한 조건부 서식입니다. 현재 조건부 서식이 적용되는 영역은 $D$2:$G$13 셀 범위로 고정됩니다. 데이터 범위가 변경되면 애플리케이션 셀 범위만 수정하면 됩니다.

위 그림에서 [적용]-[확인]을 클릭하면 최고점수 컬러마킹이 완료됩니다. 최종 결과는 다음과 같습니다.

공식을 변경하고 가장 낮은 점수를 색상으로 구분해 볼 수 있습니다!

예시 2 [계약 만료 알림]

계약 만료 알림을 양식에 어떻게 설정하나요? 저는 계약관리에 종사하는 모든 파트너가 그러한 요구를 갖고 있다고 믿습니다. 가장 간단한 방법은 "조건부 서식"의 "발생 날짜"를 사용하여 만료 예정인 데이터가 포함된 셀을 특수 색상으로 표시하는 것입니다.

하지만 이 방법에는 다음과 같은 옵션만 있습니다. 5일 이내에 만료되는 계약을 알고 싶다면 어떻게 해야 할까요? 함수 수식을 사용해야 합니다.

다음 표는 China Unicom Group Corporation 마케팅부 직원의 근로계약 기록표입니다. 해당 표에는 해당 직원의 입사일과 계약 종료 시간이 기록되어 있습니다. 이제 계약이 곧 만료되는 직원을 위해 자동으로 색상 코드를 지정하는 조건부 서식을 사용해야 합니다.

작업 단계:

(1) H2:H13 데이터 영역을 선택하고 [시작] 탭에서 [조건부 서식]을 클릭합니다. [새 서식 규칙]을 선택하고 [새 서식 규칙] 대화 상자를 열고 [수식을 사용하여 서식을 지정할 셀 결정]

을 선택합니다. (2) 편집 규칙 설명에 설정 조건을 =로 입력합니다. DATEDIF(TODAY (),H2,"d")

아마도 DATEDIF(TODAY(),H2,"d")

이 기능을 표로 간단히 소개하겠습니다.

보시다시피 "y"와 "y"를 사용하여 가입 날짜를 현재 날짜와 비교합니다(today()는 현재 날짜를 반환합니다). " 각각 "m"과 "d"는 계산 단위로 두 날짜 사이의 연, 월, 일 수를 순차적으로 반환합니다. 참고: 함수 수식의 첫 번째 매개변수는 작은 날짜이고 두 번째 매개변수는 큰 날짜입니다.

DATEDIF 함수를 이해한 후에는 계약 만료 알림 설정을 위한 DATEDIF(TODAY(),H2,"d")

오늘 우리는 조건부 서식의 수식을 결합하여 데이터를 판단하고 레이블을 지정하는 방법을 두 가지 예를 통해 공유했습니다. 실제 작업에는 다음과 같은 관련 사례가 많이 있습니다.

1. =COUNTIF($H$3:$H$13,H2)>1 함수를 통해 중복된 데이터를 표시할 수 있습니다.

2. =VLOOKUP($H2,$M:$M,1,0)"#N/A" 함수를 사용하여 일치하는 셀에 직접 색상을 지정합니다. 시도해 볼 수 있습니다!

관련 학습 권장 사항: excel 튜토리얼

위 내용은 실용적인 엑셀 스킬 공유 : '조건부서식'과 '함수수식'을 함께 사용의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!