안녕하세요.
거래 내역 중 원하는 품명의 거래를 한 눈에 볼 수 있도록 채우기 색을 나타내려고 합니다.
품명이 여러 개이고, 원하는 품명이 고정된 것이 아니라 그때 그때 달라질 수 있어 [조건부 서식] 기능에 수식으로 조건을 지정하고, COUNTIF 함수로 조건 목록에 해당하는지 여부를 확인하도록 합니다.
달력을 만들 때 공휴일을 강조하려고 할 때도 사용하는 방법입니다.
거래 내역이 있습니다.
품명 한 두 개만 조건부 서식으로 강조하려면 [조건부 서식] 기본 기능을 쓰면 됩니다.
2017.01.05 - 엑셀 24강] 조건에 일치하는 데이터를 한눈에 확인하는 조건부 서식
엑셀 24강] 조건에 일치하는 데이터를 한눈에 확인하는 조건부 서식
조건부 서식은 특정 조건에 맞는 데이터를 한 눈에 확인하기 위해 셀이나 셀 범위에 색상, 글자 크기, 진하게, 테두리 등의 서식을 설정하는 기능입니다. 실습 데이터 다운로드 ↓ 예제를 사용하
hantip.net
이 강좌에서는 강조하려는 품명이 아주 많을 수 있고, 강조하려는 품명이 고정된 것이 아니라 자주 바뀌는 경우 쓸 수 있는 방법을 소개합니다.
실습 파일 다운로드
완성 파일 다운로드
사용할 기능은 [조건부 서식], COUNTIF 함수, 이름 정의, 동적 범위 지정, OFFSET 함수 등입니다.
'조건목록' 시트에 강조하려는 품명을 미리 입력해 두었습니다.
지금은 2개만 입력했지만 여러 개를 입력해도 쉽게 쓸 수 있도록 구현할 것입니다.
'거래내역' 시트 품명 열에 [조건부 서식]을 적용합니다.
먼저 [B3:B122] 셀 범위를 선택합니다.
[홈]-[스타일]-[조건부 서식]-[새 규칙]을 선택합니다.
[새 서식 규칙] 대화상자에서 '수식을 사용하여 서식을 지정할 셀 결정'을 선택합니다.
COUNTIF 함수를 이용한 조건 수식을 작성합니다.
함수명은 모두 입력해야 하고 셀 범위는 마우스로 시트를 선택하고 드래그해서 나타냅니다.
참조한 셀 범위를 절대 참조로 표시되는데, 두번째 인수의 참조 범위는 혼합 참조가 되도록 직접 수정해야 합니다.
=countif(조건목록!$A$2:$A$3,거래내역!$D3)>=1[서식]을 클릭해서 [채우기] 탭에서 적당한 색을 선택합니다.
[확인]을 클릭한 뒤 [새 규칙 편집] 대화상자에서도 [확인]을 클릭합니다.
조건부 서식이 적용된 결과를 확인합니다.
다른 품명을 강조하고 싶다면 '조건목록' 시트의 품명을 수정하면 됩니다.
현재 두 개를 입력해 두었기 때문에 두 개만 수정하는 경우에는 [조건부 서식]을 열어 조건을 바꿀 필요 없습니다.
강조할 품명이 하나이거나 세 개 이상인 경우에는 [조건부 서식]을 열어 조건에서 셀 범위를 직접 수정해야 합니다.
이렇게 수정하는 것은 불편하기 때문에 [이름 정의] 기능을 이용해 셀 범위를 지정하고, [조건부 서식]의 조건에는 [이름 정의]에서 만든 이름만 입력해 두어 셀 참조 범위를 수정하기 쉽도록 바꿉니다.
엑셀 11강] 이름 정의
11강에서는 이름 정의에 대한 설명입니다. 실습 데이터 다운로드 ↓ 수식을 작성할 때 참조 셀 또는 범위를 이름으로 정의하여 사용한다면 주변사람들에게 엑셀 좀 쓰는군요!라는 반응을 얻을
hantip.net
'조건목록' 시트에서 [수식]-[정의된 이름]-[이름 관리자]를 선택합니다.
[새로 만들기]를 클릭하고, '이름'에는 조건목록, '참조 대상'에는 '조건목록' 시트 [A2:A3] 셀 범위를 선택한 뒤 [확인]을 클릭합니다.
[이름 관리자] 대화상자에 방금 만든 조건목록 이름이 보입니다.
[닫기]를 클릭해 [이름 관리자] 대화상자를 닫습니다.
이제 미리 설정했던 [조건부 서식]의 조건에 입력한 참조 범위를 정의한 이름으로 바꿉니다.
'거래내역' 시트 [D3] 셀에 커서를 둡니다.
[홈]-[스타일]-[조건부 서식]-[규칙 관리]를 선택합니다.
수정할 규칙을 선택하고 [규칙 편집]을 클릭합니다.
[서식 규칙 편집] 대화상자에서 셀 참조부분을 정의한 이름으로 바꿉니다.
이 때 주의할 점은 키보드 화살표를 쓸 수 없으므로 마우스로 정확히 범위 선택해서 필요없는 부분을 지워야 합니다.
[확인]을 클릭해서 규칙 편집을 마무리 합니다.
한단계 더 나가 이름 정의한 범위가 자동으로 바뀌도록 동적 범위 지정을 합니다.
동적 범위는 이름 정의에 OFFSET 함수를 이용하는 것입니다.
2017.01.08 - 엑셀 50강] 이름 범위를 동적 범위로 설정하여 함수식에서 활용하기
엑셀 50강] 이름 범위를 동적 범위로 설정하여 함수식에서 활용하기
동적 범위를 이름으로 정의하는 방법과 활용법에 대해 소개하겠습니다. 범위를 이름으로 정의해 두면 함수식을 작성할 때 편리합니다. 특히나 동적 범위로 만들면 데이터가 추가 또는 삭제가
hantip.net
'조건목록' 시트에 커서를 두고 [수식]-[정의된 이름]-[이름 관리자]를 선택합니다.
조건목록 이름을 선택하고 아래 '참조 대상'에서 수식으로 수정합니다.
=offset(조건목록!$A$2,0,0,counta(조건목록!$A:$A)-1,1)
[닫기]를 클릭해 바꾼 내용을 적용합니다.
강조하고 싶은 품명을 추가하거나 수정하고, 필요없는 경우 지우기만 하면 '거래내역' 시트의 거래내역에 [조건부 서식]이 자동으로 적용됩니다.
필요하신 분께 도움이 되길 바랍니다.
'기초에서 실무까지 > 엑셀' 카테고리의 다른 글
| 번호가 여러 번 입력되어 있고 지급액이 각각 다른 경우 특정 번호에 해당하는 지급액 최고값의 지급일 구하기 (0) | 2022.03.21 |
|---|---|
| 번호가 여러 번 입력되어 있고 지급액이 각각 다른 경우 특정 번호에 해당하는 최고값 구하기 (0) | 2022.03.21 |
| 엑셀] SUMIF와 SUMIF, 이름 정의를 사용해 조건에 일치하는 합계 구하기 (0) | 2022.03.10 |
| 엑셀] 데이터 입력이 빨라지는 꿀팁 (0) | 2022.01.28 |
| 텍스트 형식으로 입력된 날짜를 원하는 모양으로 바꾸는 방법 (0) | 2022.01.25 |