윤슬을 향해

조건부 서식 ❾ 선택 범위의 표시 형식이 백분율일 경우 주의 사항 및 하나의 아이콘만 사용하기 이전 강좌에서 아이콘 집합 적용 시 기본적으로 값의 백분율로 나눈다는 것을 확인했습니다. 백분율로 나눌 경우 나누는 값의 기준이 명확하지 않기 때문에 숫자로 변경해서 사용하는 것이 일반적이라고 말씀드렸습니다. 그런데 조금 애매한 경우가 있습니다. 바로 선택한 범위의 표시 형식이 백분율인 경우입니다. 예제부터 보겠습니다. 예제는 다팔아몰 판매팀 매출 현황으로 데이터 막대를 적용한 예제입니다. 그리고 지금 하고자 하는 것은 이익률의 10% 이상은 초록색 신호등, 10% 미만 0% 이상은 노란색 신호등, 0% 미만은 빨간색 신호등을 적용하려 합니다. 그런데 이익률의 표시 형식은 백분율로 적용되어 있죠. 값에 100을 곱하고 "%" 기호를 붙여서 표현한 것이죠. 이 경우 아이콘 집합을 기본으로 적용하면 백분율을 백분율로 나누는 이상한 형태가 되어 버립니다. 굉장히 복잡해지죠. __adsens-adv1__ 하지만 표시 형식에 대해 이해하고 있다면 사실 별다른 것이 없습니다. 이익률의 값을 백분율로 보지 않고 실제 값 즉 0.07(H5셀), 0.04(H6셀), -0.13(H7셀),... 으로 생각하면 단순해집니다. 그럼, 이익률의 10% 이상은 초록색 신호등, 10% 미만 0% 이상은 노란색 신호등, 0% 미만은 빨간색 신호등 표시는 실제 셀 값으로 0.1 이상은 초록색 신호등, 0.1 미만 0 이상은 노란색 신호등, 0 미만은 빨간색 신호등으로 판단할 수 있습니다. 그리고 "종류를 백분율로 나누지 않고 숫자로 나눈다"라는 것만 기억하고 있으면 됩니다. 조건부 서식을 적용할 이익률 데이터 범위를 클릭 드래그하여 모두 선택합니다.홈 탭 > 조건부 서식 > 아이콘 집합 > 기타 규칙을 클릭합니다. => 아이콘 집합의 새 서식 규칙 대화상자가 나옵니다.(신호등 선택 후 규칙 편집을 통해 변경해도 되지만 지금처럼 기타 규칙을 선택하면 바로 상세 설정을 할 수 있습니다.)종류를 기본 백분율에서 숫자로 모두 변경합니다.기준값을 설정합니다. 초록색 신호등은 셀 값으로 0.1 이상이니 0.1을, 빨간색 신호등은 0 미만이니 0으로 설정합니다.최종, 확인 버튼을 클릭하여 선택 범위에 조건부 서식을 적용합니다. 결과, 설정한 값을 기준(0.1 이상은 초록색, 0.1 미만 0 이상 노란색, 0 미만 빨간색)으로 아이콘 집합이 적용됩니다. 그런데 결과를 잘 확인해 보세요. 이상하죠. 원포의 경우 이익률은 10%인데 노란색 신호등이 표시됩니다. 왜 그럴까요? __adsens-adv1__ 이유는 아이콘 집합이 아니라 셀 표시 형식 때문입니다. 숫자의 셀 표시 형식일 경우 기본으로 소수점 이하는 표시하지 않습니다. 소수점 이하의 수에서는 반올림하죠. "," 기호로 단위를 변경해도, "%" 기호도 마찬가지로 반올림합니다. 즉 10%는 9.5%(0.095)~10.4%(0.104)까지 모두 10%라는 것입니다. 10%가 아닐 수 있다는 것이죠. 그렇다면 소수점 한 자리까지 확인을 해봐야겠습니다. 그림의 순으로 소수점 한 자리까지 표시합니다. 자릿수 늘림 버튼을 한번 클릭할 때마다 소수점 자릿수가 한 자리씩 늘어 납니다. 바로 옆에 자릿수 줄임 버튼을 클릭하면 반대로 클릭할 때마다 한 자리씩 줄어들게 됩니다. 이제 다시 원포의 이익률을 확인하면, 9.6%임을 확인 할 수 있습니다. 10% 미만이 맞다는 것이죠. 이런 이유로 1의 자리까지 구분한 경우, 소수점 한 자리까지 표시해 주는 것이 좋습니다. 만약 소수점 한 자리까지 구분한 경우라면, 소수점 두 자리까지 표시해야 문서를 보는 사람이 오해하지 않게 됩니다. 비단 % 뿐만 아니라 셀 값이 수식으로 나누어진 경우라면, 반올림할 것을 예측하여, 항상 표시할 소수점의 자릿수보다 한 자리를 더 표시해 주는 것이 좋습니다. __adsens-adv1__ 지금까지 3색 신호들을 사용하여 3가지 아이콘으로 아이콘 집합을 사용했습니다. 하지만 항상 3가지 이상의 아이콘을 사용해야 하는 것은 아닙니다. 특정 아이콘을 하나만 사용할 수도 있습니다. 이번에는 달성률이 80% 미상에 빨간색 깃발을 표시해 보겠습니다. 이것도 기타 규칙을 사용하면 간단하게 처리할 수 있습니다. 달성률 범위를 모두 선택합니다.조건부 서식 > 아이콘 집합 > 기타 규칙을 클릭합니다. => 아이콘 집합의 새 서식 규칙 대화상자가 나옵니다.지금껏 해 왔듯이 종류부터 숫자로 변경합니다.값을 변경합니다. 이때 상윗값이 하윗값보다 작으면 에러가 발생합니다. 불필요하더라도 최소한 같은 값으로 설정해야 합니다.상위 2개의 아이콘은 불필요하니 "셀 아이콘 없음"으로 선택하고 가장 작은 값에 아이콘을 원하는 아이콘으로 선택합니다.최종, 확인 버튼을 클릭하여 선택 범위에 아이콘 집합을 적용합니다. 결과 80% 미만의 값에 빨간색 깃발이 적용됩니다. 주의하실 점은 미만으로 적용했기 때문에 가장 작은 값에 아이콘을 표시했다는 것입니다. 만약 이상으로 적용하고 싶었다면 가장 큰 값에 아이콘을 적용해야 했겠죠. 그 경우라면 미만의 값에는 마찬가지로 "셀 아이콘 없음"을 선택해 주면 되겠습니다. 이것으로 서식의 모든 강좌를 마칩니다. 데이터 시각화 아이콘 집합 v2.xlsx(12.5 Kb)save_alt __adsens-adv1__

2025.08.12 / 18:15

조건부 서식 ❽ 아이콘 집합 기본 사용법과 원리, 상위/하위와의 비교 이번 강좌는 조건부 서식의 아이콘 집합입니다. 아이콘 집합은 셀의 좌측에 여러 가지 아이콘을 표시하여 시각화합니다. 일상에서 주로 사용하는 화살표, 신호등, 별표 등을 아이콘으로 사용하므로 문서를 작성하는 사람과 읽는 사람이 특별히 합의하지 않더라도 데이터 크고 작음을 쉽게 파악할 수 있습니다.보통 3가지, 4가지, 5가지 등으로 데이터의 크고 작음을 구분하고 각각 다른 아이콘으로 표시하는 형태입니다. 조건부 서식의 상위/하위 규칙과 매우 유사한 기능이지만 전혀 다른 결과를 반환합니다. 그래서 아이콘 집합을 알아보기 전에 "상위/하위 규칙"을 이해하고 있으면 이해하는 데 도움이 됩니다. "상위/하위 규칙"이 기억나지 않는다면 이전 강좌를 한번 보시고 오시길 바랍니다. 조건부 서식 ❸ 상위/하위 규칙엑셀 조건부 서식 중 상위/하위 규칙에 관해 설명합니다. 상위/하위 규칙 적용 시 상위/하위를 항목의 개수로 정할 때와 %로 정할 때의 차이를 보고 문제를 확인합니다.https://flylight.kr/ 예제 파일을 확인하겠습니다. 예제 파일은 데이터 막대에서 사용한 것과 동일한 파일입니다. 이번에는 실매출에 데이터 막대가 아니라 아이콘 집합의 3색 신호등을 적용해 보겠습니다. __adsens-adv1__ 적용 방법도 별다른 것 없습니다. 이전과 동일하게 범위를 선택하고 조건부 서식 > 아이콘 집합 > 적용할 아이콘을 클릭하면 끝납니다. 어떤 것은 초록색 신호등, 어떤 것은 노란색, 어떤 것은 빨간색 신호등 아이콘이 셀의 좌측에 표시되었습니다. 대략 살펴보면 초록색 아이콘 셀 값이 크고, 노란색은 중간 정도, 빨간색은 작은 값이란 것 정도는 확인됩니다. 어떤 느낌인진 알 것 같죠. 그럼, 각 아이콘을 나눈 기준이 있다는 것이네요. 이걸 알아보려면 어떻게 해야 할까요? 조건부 서식을 적용하면, 규칙이 생긴다는 것 기억하지요? 그 규칙을 확인하면 됩니다. 조건부 서식을 적용한 아무 셀이나 클릭하여 선택합니다.조건부 서식 > 규칙 관리를 클릭합니다. => 조건부 서식 규칙 관리자 대화상자가 나옵니다.적용된 규칙 중 편집할 규칙을 클릭하여 선택합니다. => 선택된 규칙이 파란색 배경으로 변경됩니다.규칙 편집 버튼을 클릭합니다. => 서식 규칙 편집 대화상자가 나옵니다.서식 규칙 편집 대화상자를 보면 어떤 기준으로 아이콘이 적용되었는지 알 수 있습니다. 선택 범위 값 중 67% 이상은 초록색, 67% 미만 33% 이상은 노란색, 33% 미만은 빨간색이 적용되었습니다. 즉 선택한 범위의 값을 큰 값, 중간값, 작은 값으로 33%씩 3등분 했다는 것입니다. 아이콘이 4개라면 25%씩 4등분, 5개라면 20%씩 5등분하는 것이죠.그리고 값은 바꿀 수 있게 되어 있으니 원하는 수치가 있다면 값만 변경하면 됩니다. 간단해 보이죠. __adsens-adv1__ 조건부 서식의 상위/하위 규칙도 "상위 OO%", "하위 OO%"로 만들 수 있었습니다. 같이 적용하고 비교해 보겠습니다. 주의하실 건 "상위/하위 규칙"은 "OO%"를 포함하여 적용하기 때문에 이상이나 이하로만 사용할 수 있습니다. 초과, 미만은 사용할 수 없다는 것입니다. 그럼, 위와 같이 이상으로 되게 하려면 상위 규칙으로 진행해야 합니다. 홈 탭 > 조건부 서식 > 상위/하위 규칙 > 상위 10%를 클릭 => 상위 10% 대화상자가 나옵니다.67% 입력하고 서식은 "진한 노랑 텍스트가 있는 노랑 채우기"를 선택한 후 하단 확인 버튼을 클릭합니다. => 상위 67% 이상의 서식이 노랑 계열로 변경됩니다.다시 조건부 서식> 상위/하위 규칙 > 상위 10%를 클릭 => 상위 10% 대화상자가 나옵니다.33% 입력하고 서식은 "진한 녹색 텍스트가 있는 녹색 채우기"를 선택한 후 하단 확인 버튼을 클릭합니다. => 상위 33% 이상의 서식이 초록 계열로 변경됩니다. 먼저 상위 67% 적용하고 33% 나중에 적용한 이유는, 나중에 적용한 규칙이 우선순위가 되기 때문입니다. 규칙의 순위가 높은 것부터 우선 적용되는 것 기억하시죠? 기억 안 나신다면, 아래 강좌 보신 후 돌아와 주세요. 조건부 서식 ❷ 텍스트 포함 규칙과 규칙의 순위엑셀 조건부 서식 셀 강조 규칙 중 텍스트 포함 규칙에 관해 설명합니다. 여러 개의 규칙을 적용하고 규칙 관리를 통해 적용할 규칙의 순위를 조정하는 법에 관해 알아봅니다.https://flylight.kr/ 상위/하위 규칙까지 적용했습니다. 비교를 위해 정렬도 간단하게 하겠습니다. 정렬하고자 하는 항목의 아무 셀이나 클릭하여 선택합니다.홈 탭 > 정렬 및 필터 > 숫자 내림차순 정렬을 클릭합니다. 실매출 기준 내림차순으로 표가 정렬되었습니다. 어떤가요? 좀 이상하죠? 아이콘 집합과 상위/하위 모두 동일하게 3등분 했기 때문에 서식과 신호등 색이 동일한 계열이어야 합니다. 다시 말해 초록 계열 서식은 초록색 신호등, 노란 계열 서식은 노란 신호등이 나와야 하죠. 하지만 장먼옥의 경우 노란 계열 서식인데 초록색 신호등이 표시 되었습니다. 이유는 바로 아이콘 집합과 상위/하위 규칙의 기준 차이 때문입니다. __adsens-adv1__ 상위/하위 규칙의 경우 기준은 항목의 개수입니다. 즉 선택한 셀의 개수라는 것이죠. 지금 선택했던 범위의 셀의 개수는 10개입니다. 상위 67% 이상은 10*0.67 = 6.7이므로 6개 이상이 되어 6개 이상이 노란 계열 서식이 되고, 상위 33% 이상은 10*0.33 = 3.3이므로 3개 이상이 되어 3개 이상이 초록 계열 서식이 됩니다. 반면 아이콘 집합의 기준은 항목의 개수가 아니라 선택한 범위의 값이 기준입니다. 즉 최소값 2,250,000부터 최대값 3,675,000을 3등분한 것이 됩니다. 3,675,000 - 2,250,000 = 1,425,000을 3등분한 것이죠. 결과, 노란색 신호등은 2,250,000+(1,425,000/3) = 2,725,000이므로 셀 값이 2,725,000 이상에서 표시되고, 초록색 신호등은 3,675,000-(1,425,000/3) = 3,200,000이므로 셀 값이 3,200,000 이상에서 표시됩니다. 결과, 위와 같이 상위/하위 규칙으로 3등분을 할 경우 반드시 3/3/4개로 셀이 개수가 나누어져 서식이 적용되지만, 아이콘 집합을 사용할 경우 셀 값의 차에 의해 신호등이 다르게 나타납니다. 극단적으로 노란색 신호등이 나오지 않을 수도 있게 됩니다. 위의 경우라면 노란색 신호등의 값이 만약 2,725,000보다 작을 경우가 되겠습니다. 그럴 경우는 실매출이 작은 값으로 몰려 있다는 얘기가 되겠죠. 위와 같은 이유로 아이콘 집합을 별다른 설정 없이 진행하면, 기본값인 백분율로 나누기 때문에 셀 값의 크고 작음을 대략적인 분포 정도로만 알 수 있을 뿐, 셀 값이 몇 이상부터 적용된 것인지 알 수 없습니다. 물론 위처럼 계산을 해볼 수는 있겠죠. 그러자면 또 생각하고 다시 계산을 해보는 절차가 필요하겠죠. 귀찮은 작업이 되어 버립니다. 그래서 실무에서는 셀 값을 백분율로 진행하지 않고, 직관적으로 셀 값 그대로 사용하는 편을 선호합니다. 3,300,000 이상은 초록색 신호등, 3,300,000 미만 2,300,000 이상 노란색 신호등, 2,300,000 미만 빨간색 신호등 의 형태로 사용하는 것이죠. 규칙을 수정하여 적용해 보겠습니다. 조건부 서식이 적용된 범위 중 아무 셀이나 클릭하여 선택합니다.조건부 서식 > 규칙 관리를 클릭합니다. => 조건부 서식 규칙 관리자 대화상자가 나옵니다.적용된 규칙 중 편집할 규칙을 클릭하여 선택합니다. => 선택된 규칙이 파란색 배경으로 변경됩니다.규칙 편집 버튼을 클릭합니다. => 서식 규칙 편집 대화상자가 나옵니다.아이콘 집합의 값의 종류는 기본 백분율로 선택되어 있습니다. 종류를 백분율에서 숫자로 변경합니다.값을 원하는 기준 숫자 그대로 작성합니다. 저는 3,300,000과 2,300,000으로 적용했습니다.서식 규칙 편집 대화상자의 확인 버튼을 클릭합니다. => 서식 규칙 편집 대화상자가 닫히고 조건부 서식 규칙 관리자 대화상자 다시 나옵니다.최종, 조건부 서식 규칙 관리자 대화상자의 확인 버튼을 클릭하여 변경된 규칙을 적용합니다. 결과, 명확하게 3,300,000과 2,300,000을 기준으로 나눈 아이콘 집합이 적용됩니다. 데이터 시각화.xlsx(10.7 Kb)save_alt __adsens-adv1__

2025.08.09 / 11:08

조건부 서식 ❼ 데이터 막대 - 값 없이 막대만 표시하기, 음수와 축 지난 강좌에 이어 조건부 서식의 데이터 막대 사용법을 조금 더 설명하려 합니다. 데이터에 음수 값이 있는 경우의 설정과 막대만 표현하는 방법에 관한 얘기입니다. 지난 강좌와 이어지는 얘기이니, 이 글로 바로 들어오신 분들은 이전 강좌를 확인하고 돌아와 주세요. 조건부 서식 ❻ 데이터 막대의 기본 사용법과 원리조건부 서식 중 데이터 막대의 기본 사용법을 소개합니다. 데이터 막대가 그려지는 원리를 알아보고, 데이터 막대 규칙 편집을 통해 기준값을 최소값으로 변경하는 방법을 알아봅니다. 최소, 최대로 변경 시 데이터 막대의 크기 변화를 확인하여 값을 비교해 봅니다.https://flylight.kr/ 예제는 지난 강좌에서 적용한 데이터 막대의 결과입니다. 잘 적용되었지만, 살짝 아쉬운 것은 데이터 막대와 셀 값이 같이 나온다는 것이죠. 이 경우 만약 데이터 막대의 색을 검은색으로 하게 되면 셀 값의 일부는 보이지 않게 됩니다. 검은색이 아니더라도 데이터 막대 색을 어두운 계열로 사용하면 값이 눈에 잘 보이지 않게 되죠. 가독성이 떨어진다는 것입니다. __adsens-adv1__ 그래서 이번에는 매출 이익을 값 따로, 막대 따로 나오게 만들어 보겠습니다. 그런데 매출 이익은 실매출과는 다르게 음수 값도 존재합니다. 시작 기준이 달라지겠죠. 일단 유념하시고 진행해 주세요. 먼저 진행해야 하는 것은 데이터 막대를 표시할 공간(열)을 추가로 만들어야 합니다. 열 머리글에서 G열을 우클릭하고 삽입을 눌러 매출 이익 우측으로 하나의 열을 더 추가합니다. 열 추가가 어려우신 분들은 아래 강좌를 참고해 주세요. 행 머리글, 열 머리글 ❶ - 행과 열의 삽입과 삭제행 머리글과 열 머리글에서 행과 열을 삽입하고 삭제하는 방법을 설명합니다.https://flylight.kr/ 추가한 열의 각 셀에도 값은 있어야 합니다. 값이 있어야 데이터 막대를 만들 수 있으니까요. 매출 이익 값을 참조해 옵니다. 데이터 막대를 시작할 셀(G5)을 클릭하여 선택합니다.수식 입력줄에서 수식(=)을 시작하고 매출 이익 첫 셀을 참조하고 엔터를 눌러 G5셀을 완성합니다.다시 G5셀을 클릭하여 선택하고 우측 하단에서 클릭 드래그하여 G14셀까지 자동 채우기 합니다. 완성된 G5:G14 범위에 데이터 막대를 적용하겠습니다. 이번에는 조금 다른 방식으로 진행해 보겠습니다.이전까지는 일단 조건부 서식을 적용하고 규칙 편집을 통해 옵션들을 조정했습니다. 하지만 꼭 그렇게 진행할 필요는 없습니다. 먼저 옵션을 선택하고 조건부 서식을 적용할 수도 있습니다. 상세 설정을 미리 하면 다시 규칙 편집으로 들어가는 수고를 덜 수 있습니다. __adsens-adv1__ 데이터 막대를 적용할 범위(G5:G14)까지 클릭 드래그하여 선택합니다.조건부 서식 > 데이터 막대 > 기타 규칙을 클릭합니다. => 새 서식 규칙 대화상자가 나옵니다.(조건부 서식을 적용한 뒤 규칙 관리를 클릭했을 때 나오는 창과 동일한 녀석입니다. 기타 규칙을 클릭하면 이렇게 옵션을 먼저 설정할 수 있습니다.)막대만 표시를 클릭합니다. (값을 표시하지 않겠다는 것입니다.)일단 설정은 여기까지 하겠습니다. 하지만 그림의 파란색 부분을 확인해 주세요. "음수 값 및 축"이라는 것이 보입니다. 음수의 설정도 할 수 있다는 것이겠죠.최종, 확인 버튼을 클릭하고 선택 범위에 데이터 막대를 적용합니다. 결과, 셀 값은 사라지고 데이터 막대만 표시됩니다. 하지만 음수가 있기 때문에 막대가 이전과는 좀 다르게 나타납니다. 데이터 막대 좌측 기준의 기본 설정은 "0"입니다. 우측 기준은 최대값이 되었죠. 하지만 지금처럼 음수가 존재할 경우 좌측 기준은 최소값이 됩니다. "0"이라면 음수를 표현할 수 없기 때문이겠죠. 그리고 하나 더 알아야 할 것이 있는데 바로 축입니다. 지금처럼 데이터에 음수가 존재할 때 음수와 양수 사이 어느 지점에는 "0"이 되는 지점이 생길 것입니다. 바로 그 위치가 축입니다. 그림에서 검은색 점선으로 나타난 부분입니다. 축을 기준으로 좌로는 음수, 우로는 양수가 되는 것이죠. 그리고 음수와 양수의 막대 색이 다르게 나타납니다. 당연한 얘기겠지만, 색은 바꿀 수 있습니다. 하지만 음수, 양수 다른 색을 지정해야 구분이 쉽겠죠. 또한 축의 색도 바꿀 수 있으며 축의 위치를 셀의 한가운데로 이동할 수도 있습니다. __adsens-adv1__ 확인해 보겠습니다. 조건부 서식이 적용된 범위 중 아무 셀이나 클릭하여 선택합니다.조건부 서식 > 규칙 관리를 클릭합니다. => 조건부 서식 규칙 관리자 대화상자가 나옵니다.적용된 규칙 중 편집할 규칙을 클릭하여 선택합니다. => 선택된 규칙이 파란색 배경으로 변경됩니다.규칙 편집 버튼을 클릭합니다. => 서식 규칙 편집 대화상자가 나옵니다.양수의 채우기 색을 변경합니다. 원하는 색으로 선택해 주세요.음수의 값 및 축 버튼을 클릭합니다. => 음수 값 및 축 설정 대화상자가 나옵니다.음수의 채우기 색을 변경합니다.축 설정 영역에서 "셀 중간점"을 선택합니다. (축을 G열 셀의 중간으로 맞추겠다는 것입니다.)축 색을 변경합니다. (저는 그냥 두었습니다. 바꾸고 싶다면 클릭하여 변경해 주시면 됩니다.)음수 값 및 축 설정 대화상자의 확인 버튼을 클릭합니다. => 음수와 축의 설정이 완료되어 닫히고 다시 서식 규칙 편집 대화상자가 나옵니다.서식 규칙 편집 대화상자의 확인 버튼을 클릭합니다. => 서식 규칙 편집 대화상자가 닫히고 조건부 서식 규칙 관리자 대화상자 다시 나옵니다.최종, 조건부 서식 규칙 관리자 대화상자의 확인 버튼을 클릭하여 변경된 규칙을 적용합니다. 상당히 많은 단계를 거친 것 같지만 실제로 해보면 대부분 클릭으로 진행되어 어려운 것이 없습니다. 결과, 막대들의 색이 바뀌고 축이 셀의 가운데로 이동됩니다. 여기서 주의하셔야 할 것은 이제 셀의 좌측 끝 기준은 더 이상 최소값이 아니란 겁니다. 축을 가운데로 옮겼기 때문에 축을 기준으로 좌측 크기도 우측과 동일한 크기가 돼버린 것입니다. 즉 최대값에 마이너스를 곱한 결과가 되는 것이죠. 최소값이 셀의 좌측 끝에 붙지 않는 이유입니다. __adsens-adv1__ 조건부 서식 데이터 막대를 마칩니다. 다음 강좌 색조를 알아볼 차례지만, 데이터 막대와 별 차이 없기 때문에 넘어가겠습니다. 직접 해보시면 금방 하실 수 있을 겁니다. 사실 색조는 실무에서도 잘 사용하진 않습니다. 색조의 경우 문서를 만든 사람과 문서를 보는 사람 간에 합의된 색이 있어야 구분이 편하기 때문입니다. 색의 기준이 없다면 무엇이 큰 값이고 무엇이 작은 값인지 색만으로는 구분할 수 없기 때문이죠. 직관적인 데이터 막대를 사용하는 편이 좋습니다. 다음 강좌는 아이콘 집합입니다. 데이터 시각화 데이터 막대 v2.xlsx(11.1 Kb)save_alt

2025.08.08 / 09:27

조건부 서식 ❻ 데이터 막대의 기본 사용법과 원리 조건부 서식은 특정 조건이 일치할 때 서식을 변경한다고 했습니다. "조건"과 "서식"이라는 2가지 관점으로 이해할 수 있습니다. 이때 셀 서식을 변경한다는 것을 다시 말하면, 특정 데이터를 시각화한다고 할 수 있습니다. 즉 조건이라는 관점과 시각화라는 관점으로 구분할 수 있습니다. 지금까지 알아본 "셀 강조 규칙"과 "상위/하위 규칙" 그리고 "수식을 사용하여 서식을 지정" 등은 조건이라는 관점에 초점을 두었던 얘기였습니다. 그리고 이제부터 알아볼 얘기는 시각화라는 관점에서 알아볼 것들입니다. 조건부 서식을 클릭하면 셀 강조 규칙과 상위/하위 규칙이 한 묶음으로, 데이터 막대와 색조와 아이콘 집합이 또 한 묶음으로 나뉘어 있는데 시각화란 관점으로 분류해 놓은 것입니다. 이 세 가지(데이터 막대, 색조, 아이콘 집합)는 사실 조건에 깊게 관련되었다고 볼 수 없습니다. 단순히 셀에 그래프나 색깔 또는 아이콘을 표시하여 값의 비교를 쉽게 하기 위한 것이죠. 셀 값의 크기 비교 조건 정도로 생각하시면 될 것 같습니다. __adsens-adv1__ 데이터 막대부터 시작하겠습니다. 예제 파일은 한 회사 판매팀의 매출 현황입니다. 실매출이 가장 높은 사람과 가장 낮은 사람은 누구일까요? 지금까지 알아 온 것만으로는 찾기 힘듭니다. 10명의 실매출 값을 다 비교하면서 확인해야 알 수 있습니다. 짜증 나는 일이죠. 하지만 수치별로 그래프가 표시된다면 간단하게 찾을 수 있습니다. 그것이 바로 데이터 막대입니다. 실매출 범위(D5:D14)를 클릭 드래그하여 선택합니다.조건부 서식 > 데이터 막대 > 막대 선택 => 선택 범위에 선택한 색의 막대그래프가 적용됩니다. 이제 어떤가요? 찾기가 훨씬 쉬워졌죠. 특정 조건으로 서식으로 변경한 것은 아니지만 막대의 크기로 데이터를 표현해서 각 값을 비교하기 쉽게 시각화한 것입니다. __adsens-adv1__ 하지만 여전히 가장 작은 값을 찾기 위해서는 작은 막대의 값을 비교해 봐야 합니다. 왜냐하면 막대의 크기가 너무 비슷해서 비교가 안 되기 때문이죠. 이제부터 데이터 막대가 어떤 원리로 적용되었는지 알아보고 좀 더 쉽게 비교할 수 있도록 막대의 크기로 조정하는 법을 알아보겠습니다. 실매출이 가장 높은 셀은 바로 알 수 있죠. D14셀이고 값은 3,675,000입니다. 최대값인 3,675,000의 막대는 셀의 좌측 끝부터 우측 끝까지 꽉 차있는 것이 확인됩니다. 모든 막대는 셀의 좌측 끝에서 우측으로 커지고 있으니 기준은 좌측 끝인 것을 알 수 있고, 우측 끝은 3,675,000이라는 것도 유추할 수 있습니다. 여기서 엑셀은 시작이 되는 셀의 좌측 끝을 기본으로 0으로 설정합니다. 그래서 셀의 너비를 3,675,000 조각으로 나눈 결과가 됩니다. 물론 최대값이 더 작았다면 더 작은 조각을, 더 크다면 더 많은 조각을 냈을 것입니다. 즉 선택한 범위(D5:D14)에서 가장 큰 최대값이 우측 끝 기준이 되고 그 최대값만큼 조각을 낸다는 것입니다. 그런데 지금처럼 많은 조각을 내게 되면 1, 2 정도의 작은 조각은 눈으로 비교하기 힘들게 됩니다. 너무 작은 조각일 테니까요. 그럼, 이 작은 조각을 크게 만드는 방법을 생각해 보면 됩니다. 간단하게는 D열의 너비를 아주 크게 만들면 되겠죠. 그러면 조각의 크기도 커지게 될 것입니다. 하지만 표 자체가 커져서 보기에 좋지 않겠죠. 다른 방법은 시작 기준을 바꾸는 것입니다. 시작 기준이 0이 아니라 2,000,000으로 설정하면, 3,675,000 조각 낼 것을 1,675,000 조각으로 바꾸게 되니 조각의 크기는 당연히 커지게 될 것입니다. 그렇다면 시작 기준을 어떤 값으로 설정하는 것이 셀의 너비를 가장 작은 개수로 나누게 하는 걸까요? 바로 선택한 범위(D5:D14)의 최소값이 되겠죠. 이것 역시 조건부 서식을 적용한 것이니 규칙이 생겼을 것이고 그렇다면 규칙 관리를 통해 편집이 가능할 것입니다. 조건부 서식이 적용된 범위 중 아무 셀이나 클릭하여 선택합니다.조건부 서식 > 규칙 관리를 클릭합니다. => 조건부 서식 규칙 관리자 대화상자가 나옵니다.적용된 규칙 중 편집할 규칙을 클릭하여 선택합니다. => 선택된 규칙이 파란색 배경으로 변경됩니다.규칙 편집 버튼을 클릭합니다. => 서식 규칙 편집 대화상자가 나옵니다.최소값의 종류를 최소값으로 변경합니다. 시작 기준을 변경한 것입니다. 최대값은 자동일 때 최대값입니다. 변경할 필요는 없지만 확인차 최대값으로 변경합니다.(만약 막대의 색을 변경하고 싶다면 파란색 부분 막대 모양을 변경하시면 됩니다. 테두리까지 설정할 수 있습니다. 이건 넘어가겠습니다. 궁금하시면 직접 해보세요.)서식 규칙 편집 대화상자의 확인 버튼을 클릭합니다. => 서식 규칙 편집 대화상자가 닫히고 조건부 서식 규칙 관리자 대화상자 다시 나옵니다.최종, 조건부 서식 규칙 관리자 대화상자의 확인 버튼을 클릭하여 변경된 규칙을 적용합니다. 그럼처럼 셀의 좌측 기준이 선택 범위의 최소값으로 변경되고 데이터 막대의 크기가 비교하기 쉽게 바뀝니다. 이제 가장 작은 값을 찾는 것이 간단해졌습니다. 좌측 기준을 범위의 최소값으로 설정했으니 데이터 막대가 없는 것이 최소값이 되고 데이터 막대로 모두 채워진 셀이 최대값이 됩니다. 데이터 시각화.xlsx(10.7 Kb)save_alt __adsens-adv1__

2025.08.07 / 10:26

조건부 서식 ❺ 수식을 사용하여 서식을 지정 ② 다중 조건 이번 강좌는 AND 함수를 이용해 전체 행 강조를 해보려 합니다.하지만 AND 함수는 OR 함수와 같이 이해하시는 편이 좋고, 이 함수들은 일반적으로 IF 함수와 같이 사용하는 함수이므로 IF 함수에 대해서도 반드시 알고 있어야 합니다.즉 IF, AND, OR 3가지 함수를 반드시 이해하시고 있어야 합니다. 이 강좌를 보시기 전에 엑셀 함수 코너 IF 함수에 관한 3가지 강좌를 꼭 보시고 오시기 바랍니다. 아래 3개입니다. IF 함수 ❶ 비교 연산자와 IF 함수 사용법엑셀의 IF 함수 사용법을 설명합니다. 비교 연산자가 어떤 결과를 반영하는지 확인하고 IF 함수에 적용해 봅니다. IF 함수의 인수들에 관해 알아보고 예제를 통해 IF 함수를 사용해 봅니다.https://flylight.kr/ IF 함수 ❷ 중첩 IF - 점수를 학점으로 분류하기엑셀의 IF 함수 안에 또 IF 함수를 사용하는 방법을 설명합니다. IF 함수 인수 value_if_false 대신 IF을 사용하는 중첩 IF 사용 시 원리와 주의할 사항을 소개하고 예제를 통해 시험 점수를 학점으로 반환해 봅니다.https://flylight.kr/ IF 함수 ❸ IF의 짝꿍, 논리 함수 AND와 OR엑셀의 IF 함수 안에 사용하는 논리 함수 AND와 OR에 관해 알아봅니다. AND와 OR 함수는 단독 사용보다는 IF의 짝꿍처럼 같이 사용된다는 것을 확인하고 두 함수의 공통점과 차이점을 설명합니다.https://flylight.kr/ 위 3가지 강좌를 보셨다면 이번 강좌에서는 별로 설명할 것도 없습니다. 조건부 서식의 새 규칙과 매칭만 시켜주면 끝나니까요. 구팡 파트너스 이벤트 일단 예제 파일을 확인하겠습니다. 예제 파일은 엑셀 특강 출석 현황입니다. 1, 2차 모두 결석한 학생들에게 "재수강" 통보하려 합니다. "IF 함수 ❸ IF의 짝꿍, 논리 함수 AND와 OR" 강좌를 보셨다면 금방 알아차리셨을 겁니다. 비고란이 있고 없고의 차이일 뿐 같은 예제입니다. "IF 함수 ❸ IF의 짝꿍, 논리 함수 AND와 OR" 강좌에서는 비고란에 "재수강"을 반환했었습니다. 하지만 여기 조건부 서식에서는 "재수강"이란 특정 값을 반환하는 것이 아니라 셀의 서식을 변경하려는 것이죠. 1차와 2차가 모두 "X"표시한 학생들에게 말이죠. 이쯤에서 IF 함수와 조건부 서식을 다시 정리해 보겠습니다. IF 함수 : 특정 조건이 일치하는지 그렇지 않은지에 따라 결괏값을 반환조건부 서식 : 특정 조건이 일치할 때 서식 변경 어떤가요? 동일한 얘기죠. 조건부 서식을 적용한다는 것이 IF 함수를 적용한 것과 같다는 얘깁니다. 더 정확히는 조건(logical_test)이 참(value_if_true)인 경우 반환할 값을 설정하는 것이 아니라 서식을 변경한다는 것이죠. 거짓(value_if_false)일 때는 아무것도 하지 않는다는 것이고요. 그럼, 조건부 서식에서 필요한 것은 무엇일까요? value_if_true는 서식을 바꾸는 것으로 대체 되니 필요 없으며, value_if_false 또한 아무것도 하지 않을 것이니 필요치 않습니다. 결국 조건부 서식에서 필요한 것은 logical_test만 있으면 된다는 것이 되죠. 즉 조건만 작성해 주면 끝난다는 것입니다. __adsens-adv1__ 표 전체에 셀 서식을 적용해야 하므로 표 레코드 모든 범위(B7:G16)를 클릭 드래그하여 선택합니다.홈 탭 > 조건부 서식 > 새 규칙을 클릭합니다. => 새 서식 규칙 대화상자가 나타납니다.규칙 유형에서 "수식을 사용하여 서식을 지정할 셀 결정"을 클릭합니다. => 아래 규칙 설명 편집 영역이 바뀝니다.수식을 사용하여 조건을 만들어 줍니다.수식의 시작은 "="부터였습니다. "="를 입력합니다. => =1, 2차 모두 X인 경우만 서식을 변경해야 합니다. 모든 조건이 만족해야 하므로 함수는 AND입니다. => =AND(첫 번째 조건 logical1 인수를 작성합니다. D7셀은 클릭하여 참조하고 F4키를 반복해서 눌러 열을 고정합니다. 나머지는 입력하여 logical1 인수 작성을 완성합니다. => =AND($D7="X"쉼표로 구분하고 두 번째 조건 logical2 인수를 작성합니다. E7셀 참조, 열 고정 혼합참조, 나머지 입력. => =AND($D7="X",$E7="X"두 가지 조건을 모두 만들었고 더 이상의 조건은 없습니다. AND 함수 괄호를 닫아 조건 수식을 완성합니다. => =AND($D7="X",$E7="X")서식 버튼을 클릭하고 원하는 서식을 만들어 줍니다.최종 확인 버튼을 클릭하고 새 규칙을 선택한 범위(B7:G16)에 적용합니다. 그림처럼 1, 2차 모두 결석한 학생들의 서식이 전체 행 강조로 변경됩니다. 전체 행 강조를 모르겠다면 이전 강좌를 참고해 주세요. 조건부 서식 ❹ 수식을 사용하여 서식을 지정 ① 전체 행 강조엑셀 조건부 서식 중 새 규칙을 만드는 방법을 소개합니다. 자신의 셀 값과 기준값을 비교하지 않고 특정 셀 값과 기준값을 비교하기 위해 수식을 사용하여 서식을 지정하는 방법을 설명하고 전체 행을 강조하는 조건을 만들어 봅니다.https://flylight.kr/ 이 문제의 핵심 내용은 "새 규칙을 통해 여러 가지 조건을 상황에 맞게 적용할 수 있는지"와 "전체 행 강조를 위한 조건을 만들 때 열 고정 혼합참조를 사용할 수 있는지"에 관한 것입니다. 구체적으로 조건부 서식의 개념, AND와 OR 함수 사용법, 혼합참조 사용법이라 볼 수 있겠습니다. 지금까지 잘 봐오신 분들은 충분히 이해하셨을 걸로 생각합니다만 여전히 어려우신 분들은 이전 강좌를 참고하시고 다시 정리해 주시길 바랍니다. __adsens-adv1__ 한 가지 더 해보겠습니다. 지금은 재수강할 학생의 서식을 변경한 것이지만 반대로 재수강하지 않을 학생의 서식을 변경하는 방법도 있습니다. 그럼 재수강할 학생만 기존 서식이 유지되고 나머지 학생은 다른 서식이 될 테니까요. 그렇게 하려면 조건을 어떻게 만들어야 할까요? 1, 2차 중 한 번이라도 출석한 사람은 서식을 바꿔주면 되겠죠. 조건에 만족하는 경우가 확장하는 형태입니다. OR 함수를 써야 한다는 것입니다. OR($D7="O",$E7="O") 로 되어야 1차나 2차 중 하나라도 "O"인 경우 일치하게 됩니다. 조건부 서식을 적용하면 규칙이 생긴다는 것 잊지 않으셨죠? 규칙 관리를 통해 진행하겠습니다. 조건부 서식을 적용한 아무 셀이나 클릭하여 선택합니다.홈 탭 > 조건부 서식 > 규칙 관리를 클릭합니다. => 조건부 서식 규칙 관리자 대화상자가 나옵니다.적용된 규칙 중 편집할 규칙을 클릭하여 선택합니다. => 선택된 규칙이 파란색 배경으로 변경됩니다.규칙 편집 버튼을 클릭합니다. => 서식 규칙 편집 대화상자가 나옵니다.위에서 알아본 대로 조건 수식을 변경합니다. => =OR($D7="O",$E7="O")이때 마우스로 드래그하여 변경하시는 편이 좋습니다. 방향키를 이용하면 다른 셀이 참조되어 수식이 엉망이 되기 때문에 변경할 부분만 마우스로 블럭 선택 후 바꾸는 방법이 초급자에게 더 편할 수 있습니다. 방향키를 이용하고 싶다면, 방향키를 사용하기 전에 F2키를 누르고 방향키를 사용하시면 참조하지 않고 원래 쓰시던 것처럼 사용할 수 있습니다.이 부분을 조금 설명하면, 우리가 셀에 값을 입력할 때 준비, 입력, 편집, 참조의 상태가 있었습니다. 그와 비슷합니다. 그냥 방향키를 사용하면 참조 상태가 되고 F2키를 누르면 편집 상태가 되는 것입니다. 편집 상태가 되면 방향키가 참조로 사용되지 않습니다. 그냥 이동되는 것이죠. 다시 방향키를 참조로 사용하고 싶다면 F2키를 다시 누르면 됩니다. 일정 수준 이상이 되면 이 방법이 편할 수 있지만 당장은 마우스로 선택하고 변경하는 편이 편할 것으로 생각됩니다.서식 규칙 편집 대화상자의 확인 버튼을 클릭합니다. => 서식 규칙 편집 대화상자가 닫히고 조건부 서식 규칙 관리자 대화상자 다시 나옵니다.최종, 조건부 서식 규칙 관리자 대화상자의 확인 버튼을 클릭하여 변경된 규칙을 적용합니다. 결과, 모두 결석한 학생을 제외한 행만 서식이 적용되었습니다. 서식이 적용되지 않은 학생만 보면 재수강할 학생을 찾을 수 있게 되었습니다. 다중 조건 규칙.xlsx(11.3 Kb)save_alt 이 포스팅은 쿠팡 파트너스 활동의 일환으로, 이에 따른 일정액의 수수료를 제공받습니다.

2025.08.06 / 09:16

조건부 서식 ❹ 수식을 사용하여 서식을 지정 ① 전체 행 강조 이번에는 실무에서 조건부 서식을 어떤 식으로 사용하는지 소개하려 합니다.이 방법은 ITQ 시험에도 매번 등장하는 문제기도 하지만, 실제 데이터를 확인할 때 유용한 방법이니 꼭 아셨으면 합니다. 예제 파일은 3차까지의 엑셀 시험 정리한 표입니다. 여기서 합계 250점 미만의 학생은 재수강을 해야 하므로 걸러내려 합니다. 이전에 한 예제와 비슷하죠. 가장 손쉽게 할 수 있는 방법은 "조건부 서식 > 셀 강조 규칙 > 보다 작음"을 통해서 진행할 수 있습니다. 일단 진행 전에 기준값인 D4셀의 값을 확인해 보겠습니다. D4셀을 클릭하여 선택하고 수식 입력줄의 값을 보면 250임을 확인할 수 있습니다. 표현은 250점(이상)으로 되어 있지만 실제 값은 250이죠. 이 얘기는 "점(이상)"은 실제 작성한 것이 아니고 셀 서식 표시 형식을 변경해서 숫자 250을 250점(이상)으로 보이게 표현했다는 것입니다. 결국 값은 숫자라는 것이고 숫자라면 참조를 통해 계산이 가능하다는 것입니다. __adsens-adv1__ 그래서 이번에는 기준값을 입력하지 않고 참조를 통해서 진행해 보겠습니다. 조건부 서식을 적용할 합계 범위(G7:G18)를 클릭 드래그하여 선택합니다.조건부 서식 > 셀 강조 규칙 > 보다 작음을 클릭합니다. => 보다 작은 대화상자가 나옵니다.기준값을 250으로 작성해도 되지만, 지금은 참조로 작성해도 됩니다. 왜냐하면 D4셀의 실제 값이 문자가 아니라 숫자기 때문입니다. 만약 문자라면 당연히 숫자의 크기를 비교할 수 없으므로 참조하면 안 됩니다. 그렇다면 어쩔 수 없이 기준값을 이전처럼 숫자로 입력해야 합니다.D4를 클릭하여 참조합니다. => 절대 참조됩니다.(참조가 가능하다면 값을 입력하기보다 참조하는 것이 좋습니다. 오타의 확률도 없어지고 기준값을 변경해야 한다면 D4셀만 바꾸면 되기 때문입니다.)적용할 서식을 선택하거나 작성합니다.확인 버튼을 클릭하여 선택한 범위에 서식을 적용합니다. 250 미만만 빨강 계열 서식으로 잘 적용되었습니다. 이렇게 참조를 통해 조건부 서식을 적용하면 통과 기준을 240으로 수정하고 싶을 때 "조건부 서식 > 규칙 관리"로 들어갈 필요가 없습니다. 그냥 D4셀의 값을 240으로 변경하면 자동 적용됩니다. 결과, 우측 그림처럼 D4셀의 값이 바뀌면 자동으로 조건부 서식이 변경되는 것이 확인됩니다. 조건부 서식의 규칙은 유지한 채 조건의 기준값만 바꾼 것이죠. 이렇게 조건부 서식의 기준값을 참조할 수 있다는 것도 꼭 기억해 주세요. 이 포스팅은 쿠팡 파트너스 활동의 일환으로, 이에 따른 일정액의 수수료를 제공받습니다. 서론이 길어졌는데, 하고 싶은 얘기는 지금부터입니다. 실제 우리가 찾고자 하는 것은 합계가 240점 미만을 찾는 것은 아닙니다. 240점 미만 학생 이름을 찾는 것이죠. 즉 강조된 합계의 셀을 보고 좌로, 좌로, 좌로... 찾아 이름까지 가야 하는 것이죠. 그림처럼 실제 찾고자 하는 것은 "남궁만, 이연생"이라는 것입니다. 지금이야 학생 수도 몇 안 되고 시험도 3차까지 밖에 없어 그다지 어렵지 않지만, 만약 시험이 8차였다면, 그림 같은 결과가 되어 이름 확인이 어려워집니다. 물론 화면을 축소하거나 행 머리글의 번호를 확인하여 찾을 수도 있겠지만, 직관적이진 않죠. 뭐가 됐든 한 가지 작업을 더 해야 한다는 것이고 실수할 확률이 높아지게 됩니다. __adsens-adv1__ 그런데 만약 합계 셀에만 서식을 적용하는 것이 아니라 적용된 셀의 행 전체에 서식을 적용하게 되면, 그림처럼 바로 실제 찾고자 "남궁만, 이연생"을 찾을 수 있다는 것이죠. 학생이 아무리 많아도, 시험의 차수가 아무리 많아도 문제 될 소지가 없어집니다. 그래서 실무에서는 이렇게 조건부 서식을 적용할 때는 기본이 전체 행을 강조하는 방식을 사용합니다. 왜 이런 방식을 사용하는지를 이해했다면, 어떻게 해야 하는지를 생각해 보겠습니다. 지금까지 알아본 "셀 강조 규칙과 상위/하위 규칙"에서는 기준값과 자기 자신(합계)의 셀 값을 비교하고 서식을 변경했습니다. 하지만 번호 열, 이름 열, 1차, 2차, 3차 열 모두 자기 자신의 셀 값과 기준값을 비교하면 안 됩니다. 남궁만과 240을 비교하는 것이 아니라 남궁만의 합계 값과 240을 비교해야 한다는 것입니다. 즉, 각 행의 셀들은 모두 G열의 값을 참조하여 기준값과 비교해야 한다는 것이 됩니다. 7행의 모든 값은 G7의 셀 값을, 8행의 모든 값은 G8의 셀 값을, 9행의 모든 값은 G9의 셀 값을, 10행의 모든 값은 G10의 셀 값을... 참조하여 기준값인 240과 비교하고, 서식은 자기 자신을 변경해야 한다는 결론에 이르게 됩니다. 복잡해 지죠. 일단 지금까지 적용한 조건부 서식을 지우겠습니다. 그림의 순으로 적용한 조건부 서식을 삭제합니다. __adsens-adv1__ 이제 조건부 서식을 다시 적용하여 전체 행 강조를 하겠습니다. 하지만 자기 자신의 셀 값과 기준값을 비교하려는 것이 아니기 때문에 "셀 강조 규칙"과 "상위/하위 규칙"은 사용할 수 없습니다. 그렇다면 규칙을 내가 원하는 형태로 만들어 줘야 합니다. 새 규칙을 만드는 것이죠. 표 전체에 셀 서식을 적용해야 하므로 표 레코드 모든 범위(B7:G18)를 클릭 드래그하여 선택합니다.홈 탭 > 조건부 서식 > 새 규칙을 클릭합니다. => 새 서식 규칙 대화상자가 나타납니다.규칙 유형에서 "수식을 사용하여 서식을 지정할 셀 결정"을 클릭합니다. => 아래 규칙 설명 편집 영역이 바뀝니다.이제 가장 중요한 부분입니다. 수식을 사용하려면 수식 입력줄이 필요한데 그 역할을 하는 것인 바로 "다음 수식이 참인 값의 서식 지정(O):"입니다. 수식의 시작은 "="부터였습니다. "="를 입력합니다. => =비교할 값은 "합계 값"이니 시작 행인 7행은 모두 G7셀의 값으로 비교해야 합니다. G7셀을 클릭하여 참조합니다. $G$7로 절대 참조됩니다. 그런데 여기서 이번 강좌의 핵심이 나옵니다. 비교할 값이 절대 참조되면 다음 행도 그다음 행도 모두 G7셀의 값인 252와 기준값인 240을 비교하게 될 것입니다. 원하는 결과가 아니겠죠. 모두 같은 결과를 반환하게 될 것입니다. 다음 행은 G8, 그다음 행은 G9, 그다음은 G10...을 기준값인 240과 비교해야 합니다. 즉 열이 고정되고 행이 변하는 혼합 참조가 돼야 한다는 것입니다. 혼합 참조를 위해 F4키를 반복해서 누르고 $G7로 만들어 줍니다. => =$G7비교할 값은 기준값 미만이어야 하니 "<"를 입력합니다. => =$G7<마지막으로 기준값 D4셀을 클릭하여 참조합니다. 여기도 마찬가지 절대 참조됩니다. 기준값은 변경되면 안 됩니다. 만약 혼합 참조나 상대 참조되면 B7셀의 조건부 서식이 적용된 후 C7셀로 이동해서 조건부 서식을 적용하려 할 때 기준값 역시 D4에서 H4 이동하게 되서 기준값이 없어지게 됩니다. 기준값은 반드시 절대 참조야 돼야 합니다. => =$G7<$D$4새 규칙의 조건이 완성되었습니다. 서식 버튼을 클릭하고 원하는 서식을 만들어 줍니다.최종 확인 버튼을 클릭하고 새 규칙을 선택한 범위(B7:G18)에 적용합니다. 조건에 맞은 행 전체가 서식이 적용된 결과를 얻을 수 있습니다. 구팡 파트너스 이벤트 이번 강좌에서 조건부 서식의 새 규칙을 만드는 것은 사실 별로 어려운 것은 없습니다. 하지만 역시 여기서도 "절대 참조"와 "혼합 참조"의 사용이 등장합니다. 이 개념이 부족하면 어렵게 느껴질 수밖에 없습니다. 잘 이해가 안 된다면 이전 강좌 중 "참조와 자동 채우기"편 5개의 강좌를 다시 확인하시고 상대, 절대, 혼합 참조의 개념을 반드시 정립하시기를 바랍니다. 앞으로도 계속 나올 것이니 원리를 파악하지 못하면 계속 어려울 수밖에 없습니다. 참조와 자동 채우기 ❶ - 상대 참조와 자동 채우기참조와 자동 채우기의 원리를 확인합니다. 상대 참조가 무엇인지 알아보고 상대 참조의 자동 채우기 결과를 알아봅니다. 자동 채우기의 결과가 상대 참조로 안 되는 상황을 알아봅니다.https://flylight.kr/ 전체행 강조.xlsx(12.1 Kb)save_alt

2025.08.02 / 09:53

조건부 서식 ❸ 상위/하위 규칙 조건부 서식 중 상위/하위 규칙에 대해 알아보겠습니다. 셀 강조 규칙과 마찬가지로 상위/하위 규칙도 그다지 어렵지 않습니다. 적용하는 방법 또한 셀 강조 규칙과 동일합니다. 먼저 적용할 셀의 범위를 선택하고 조건부 서식의 상위/하위 규칙 중 원하는 것을 클릭하여 적용하면 끝입니다. 하지만 개념적으로는 셀 강조 규칙과 차이 좀 있습니다. 셀 강조 규칙의 경우 직접적으로 특정 기준값 정하고 정해진 기준값과 선택한 범위의 각 셀 값을 비교하여 셀의 서식을 변경하지만, 상위/하위 규칙의 경우는 직접적인 기준값을 정하지 않고 선택한 범위 셀 중 값이 큰 상위 몇 개 또는 셀 값이 작은 하위 몇 개 등으로 정해 서식을 변경합니다. 즉 셀 강조 규칙은 절대 평가를, 상위/하위 규칙은 상대 평가를 의미하게 됩니다. 예제 파일은 엑셀 시험 현황입니다. 총 3차의 시험이 진행되었고, 학생별로 차수 점수와 총합계 점수가 정리된 표입니다. 데이터가 모두 수집되었으니, 통과된 학생과 유급된 학생을 구분하려 합니다. 일단 유급된 학생을 걸러내는 것이 문제가 되겠네요. 유급의 기준은 하위 30%입니다. 그런데 상위, 하위를 구분할 때 기준을 개수로 하면 아무런 문제가 없지만 지금처럼 %로 하면 애매한 상태가 될 수 있습니다. 현재 학생 수는 총 12명입니다. 하위 4개 항목이라 하면 당연히 점수가 낮은 4명의 학생만 찾아내면 됩니다. 하지만 지금처럼 하위 30%라 하면 계산을 해봐야 합니다. 학생 총수는 12명이니 하위 30%는 12명*0.3=3.6명이 되게 됩니다. 같은 방법으로 상위 70%는 12명*0.7=8.4명이 되게 되죠. 그럼, 하위 점수로 4번째 학생은 하위에 속할까요? 상위에 속할까요? 애매하죠. 사람을 나눌 수는 없습니다. 즉 상위에도 속하지 않고 하위에도 속하지 않는 항목이 생기게 된 것이죠. 상위에 속하는 학생 수는 8명, 하위에 속하는 학생은 3명, 이도 저도 아닌 학생이 1명이 됩니다. __adsens-adv1__ 하위 30%를 진행해 보겠습니다. 조건부 서식을 적용할 셀 범위(G7:G18)를 클릭 드래그하여 선택합니다.조건부 서식 > 상위/하위 규칙 > 하위 10%를 클릭합니다. => 하위 10% 대화상자가 나타납니다.(10%는 예입니다. 수치는 조정이 가능합니다.)하위 순위에 속할 기준값을 30으로 변경합니다.원하는 서식을 선택하거나 사용자 지정 서식을 선택한 후 만들어 줍니다.(여기서는 그냥 빨강 계열로 선택했습니다.)확인 버튼을 클릭하여 선택한 범위에 적용합니다. 이어서 상위도 적용하겠습니다. 조건부 서식을 적용할 셀 범위(G7:G18)를 클릭 드래그하여 선택합니다.(이미 선택되어 있다면 건너 뜁니다.)조건부 서식 > 상위/하위 규칙 > 상위 10%를 클릭합니다. => 상위 10% 대화상자가 나타납니다.상위 순위에 속할 기준값을 70으로 변경합니다.원하는 서식을 선택하거나 사용자 지정 서식을 선택한 후 만들어 줍니다.(빨강 계열은 이미 하위 30%에 적용했으니 다른 서식을 선택하여 구분합니다.)확인 버튼을 클릭하여 선택한 범위에 적용합니다. 상위/하위가 잘 구분되었습니다. 구팡 파트너스 이벤트 이번에는 정렬도 진행해 보겠습니다. 정렬의 방법은 몇 가지 있지만 정렬은 나중에 자세히 정리하기로 하고 일단 가장 간단한 방법만 알려드리겠습니다. 정렬할 기준 항목(합계)에서 아무 셀이나 클릭합니다. 범위가 선택되지 않게 주의해 주세요. 셀 하나만 선택해야 합니다. 범위를 선택하고 진행하는 방법은 나중에 정렬 편에서 소개하겠습니다.홈 탭 > 정렬 및 필터 > 숫자 내림차순 정렬을 클릭합니다. => 합계의 셀 값이 높은 순에서 낮은 순으로 정렬됩니다. 그림처럼 표의 레코드가 바뀌고 합계 값이 높은 순에서 낮은 순으로 정렬되었습니다. 간단하죠. 그런데 좀 이상한 것 없나요? 위에서 알아본 바로는 하위 30%에 속하는 항목의 개수는 3개였죠. 그런데 4개의 항목이 하위의 서식으로 적용되었습니다. 뭔가 잘못 알아본 것일까요? 아닙니다. 하위 3번째 4번째를 확인해 보세요. 모두 240입니다. 하위 3번째와 4번째가 동일 값인 거죠. 다시 말하면 하위 4번째는 없는 것입니다. 3번째가 2개인 것이죠. 그래서 3개여야 되지만 4개로 하위 서식이 적용된 것입니다.그렇다면 하위 4번째, 조조의 합계 점수가 만약 241점이었다면 조조는 상위도 하위도 속하지 않는 순위가 될 것입니다. 현재 합계는 SUM 함수를 통해 1, 2, 3차의 시험 점수를 합계 낸 것이니 조조의 3차 시험을 1점 올려 보면 확인할 수 있을 것입니다. 조조의 3차 시험 점수를 1점 올려주었더니 상위도, 하위도 속하지 않는 항목으로 확인됩니다. 지금 같은 이유로 사실 상대 평가를 하고자 할 때 %를 사용하는 것보다 항목 개수를 사용하는 것이 훨씬 직관적이고 보기 편한 결과를 얻을 수 있습니다. 이것으로 조건부 서식의 상위/하위 규칙을 마칩니다. 상위/하위 규칙의 나머지 규칙은 별다른 것 없으니 직접 해보시기 바랍니다. 추가로 규칙의 기준값이나 서식을 변경하고 싶다면 조건부 서식 > 규칙 관리를 통해 진행하면 됩니다. 조건부 서식을 적용하면 규칙이 생기고 규칙은 항상 조건부 서식 > 규칙 관리를 통해 수정, 삭제, 순위 변경을 할 수 있으니까요. 앞의 두 강좌에서 언급한 내용입니다. 상위 하위 규칙.xlsx(12.1 Kb)save_alt __adsens-adv1__

2025.08.01 / 09:29

2025.07.30 / 16:53

조건부 서식 ❶ 셀 강조 규칙과 규칙 관리 셀은 세 가지 요소가 있다고 했습니다. 위치, 값, 서식이었죠. 위치와 값은 이미 알아보았고, 서식도 대부분 진행이 되었습니다. 앞으로 진행할 얘기도 서식이긴 하지만 셀의 표현 방법에 관한 얘기는 아닙니다. 이제부터는 어떤 범위에 있는 셀 중 특정 조건에 해당하는 셀의 표현을 바꾸는 방법을 소개하려 합니다. 그러니까 셀의 표현을 바꾸는 방법이 아니라 특정 조건을 만드는 방법에 관한 얘기라고 보시면 되겠습니다. 셀의 표현을 바꾸는 방법은 이미 진행한 방법과 동일합니다. 예제 파일을 열고 무엇을 해야 할지부터 확인하겠습니다. 예제 파일은 한 초등학교에서 줄넘기 시험을 보고 학생별로 정리한 내역입니다. 이 표를 보고 우선 해야 할 것은 재시험을 볼 학생을 찾아내야 하는 것이겠죠. 횟수를 보고 40회를 못 한 학생을 찾아야 합니다. 그런데 그러자면 10명의 횟수를 모두 확인해 봐야 합니다. 번거로운 일이죠. 만약 학생 수가 100명이라면, 찾다가 빠지거나 횟수를 잘못 보고 넘어가는 일도 생길 것입니다. 실수하지 않으려면 몇 번을 봐야 하겠죠. __adsens-adv1__ 이런 경우 가장 간단한 방법은 횟수를 내림차순으로 정렬하는 것입니다. 횟수가 가장 높은 순에서 낮은 순으로 정리되면 40 미만인 학생들만 찾으면 되니 모든 학생의 횟수를 확인할 필요가 없어집니다. 하지만 우린 아직 정렬에 대해 알아보지 않았으니 잠깐 미뤄두겠습니다. 정렬 편에서 자세히 소개하겠습니다. 다시 돌아가서 이 표를 지면으로 출력했다고 가정해 보겠습니다. 그럼, 재시험 볼 학생을 걸러내기 위해서는 빨간색 펜을 꺼내고 40 미만의 횟수에 동그라미나 체크 표시를 할 것입니다. 아니면 형광펜으로 칠을 해 놓을 수도 있겠죠. 그러면 체크된 학생만 찾으면 되니 재시험 볼 학생이 구분됩니다. 지금 한 이 작업이 "조건부 서식의 셀 강조 규칙"입니다. 셀 값이 40 미만이라는 조건에 있는 셀에 특정 표시를 해 두는 것이죠. 특정 표시를 한다는 것은 셀을 다르게 표현한다는 뜻이고 셀을 다르게 표현하는 것은 셀의 서식을 바꾸는 것이죠. 셀 서식을 바꾸어 강조해 두는 것입니다. 방법은 간단합니다. 횟수 데이터 전체 셀 범위(D9:D18)를 클릭 드래그하여 선택합니다.홈 탭 > 조건부 서식 > 셀 강조 규칙 > 보다 작음을 클릭합니다. => 보다 작음 대화상자가 나타납니다.기준은 40 미만이니 40으로 변경합니다.적용할 서식을 선택합니다. 적용할 서식의 선택 박스를 클릭하며 기본 제공되는 몇 가지 서식과 "사용자 지정 서식"이 있습니다. 일단 "진한 빨강 텍스트가 있는 연한 빨강 채우기"로 선택했습니다.최종 확인 버튼을 클릭하여 선택된 범위에 서식을 적용합니다. 결과, 값이 40 미만인 셀의 서식이 바뀌는 것이 확인됩니다. 그럼, 빨강 계열로 바뀐 셀만 확인하면 재시험 볼 학생들이 바로 알 수 있게 된 것이죠. 이 포스팅은 쿠팡 파트너스 활동의 일환으로, 이에 따른 일정액의 수수료를 제공받습니다. 물론 셀 서식을 내가 원하는 서식으로 만드는 것도 할 수 있습니다. 위의 4번 단계에서 사용자 지정 서식을 활용하면 간단합니다. 이번에는 우수한 학생들만 다른 셀 서식을 적용해 보겠습니다. 횟수 데이터 전체 셀 범위(D9:D18)를 클릭 드래그하여 선택합니다.(이미 선택되어 있다면 건너뜁니다.)홈 탭 > 조건부 서식 > 셀 강조 규칙 > 보다 큼을 클릭합니다. => 보다 큼 대화상자가 나타납니다.기준은 50 초과이니 50으로 변경합니다.적용할 서식의 선택 박스를 클릭하고 "사용자 지정 서식"으로 선택합니다. => 셀 서식 대화상자가 나옵니다.셀 서식 대화상자에서 원하는 셀 서식으로 변경합니다. 채우기를 변경하셔도 좋고 글꼴, 테두리, 표시 형식까지 아무거나 상관없습니다.(이 부분은 계속 해온 것이니 건너뛰겠습니다. 저의 경우 글꼴만 굵게 하고 연한 파랑으로 색을 바꾸었습니다.)셀 서식 대화상자의 확인 버튼을 클릭하여 사용자 지정 서식을 완성합니다.최종, 보다 큼 대화상자의 확인 버튼을 클릭하여 적용합니다. 결과 50 초과 셀 서식도 바뀐 것이 확인됩니다. 적용은 간단하죠? 특정 범위를 선택하고 조건부 서식을 적용하면 선택된 범위의 셀 중 적용된 조건과 일치한 셀을 찾아 설정된 서식으로 바꾸는 것입니다.서식을 적용했으면 이미 적용된 조건부 서식의 조건이나 서식을 바꿀 수 있습니다. 물로 서식 삭제도 가능합니다. __adsens-adv1__ 규칙 관리 - 규칙 삭제와 편집 조건부 서식은 보셨듯이 꽤 많이 있습니다. 셀 강조 규칙, 상위/하위 규칙, 데이터 막대, 색조, 아이콘 집합 등이 있으며 나중에 알아보겠지만 사용자가 원하는 특이한 조건까지 만들 수 있습니다. 하지만 어떤 식이든 조건부 서식을 적용하면 조건의 규칙이라는 것이 생깁니다. 지금의 경우에도 횟수의 범위(D9:D18)에는 40 미만의 규칙과, 50 초과의 규칙이 생겼을 것입니다. 이렇게 생긴 조건의 규칙을 확인하고 관리할 방법이 조건부 서식의 규칙 관리입니다. 조건부 서식을 적용한 범위의 아무 셀이나 클릭하여 선택(범위를 선택해도 상관없습니다.)조건부 서식 > 규칙 관리를 클릭합니다. => 조건부 서식 규칙 관리자 대화상자가 나옵니다. 조건부 서식 규칙 관리자 대화상자는 조건부 서식을 적용할 때 매우 중요한 역할을 합니다. 여러 개의 규칙이 생기면 규칙의 순위도 생기는데 일단 이 얘기는 다음 강좌로 미뤄두겠습니다. 어쨌든 매우 중요하다는 것만 유념해 두시고 내용을 확인해 보겠습니다. 먼저 적용한 40 미만 규칙이 두 번째 줄에 있습니다. 나중에 적용한 50 초과 규칙이 첫 번째 줄이 있는 것이 확인됩니다. 나중에 적용한 규칙이 위로 올라온다는 것을 알 수 있습니다.이어서 옆으로 보면, 서식 미리 보기와 적용 대상도 보입니다. 절대 참조의 형태로 확인되고 2가지 규칙 모두 D9:D18 범위임을 알려 줍니다. 첫 번째 적용한 40 미만 규칙을 삭제해 보겠습니다. 삭제할 규칙을 클릭하여 선택합니다. => 파란색 배경으로 선택되는 것이 확인됩니다.규칙 삭제 버튼을 클릭합니다. => 선택되었던 규칙이 사라집니다.최종, 확인 버튼을 클릭하여 적용 대상에 적용합니다. 40 미만 규칙이 사라졌습니다. 현재는 50 초과 조건만 적용되어 보입니다. 구팡 파트너스 이벤트 이번에는 50 초과의 조건을 40 이상으로 변경해 보겠습니다. 그럼 40 미만은 원래의 서식(흰색 배경, 검정 글씨)이 유지되고 통과한 학생들의 횟수는 다른 서식이 되겠죠. 다시 조건부 서식의 규칙 관리를 클릭하여 조건부 서식 규칙 관리자 대화상자를 띄웁니다. 조건부 서식 규칙 관리자 대화상자에서 규칙을 클릭하여 선택합니다.(규칙이 하나 일 경우에는 선택에 의미가 없으니 넘어가도 됩니다.)규칙 편집 버튼을 클릭합니다. => 서식 규칙 편집 대화상자가 나옵니다.규칙 유형 선택 영역에 "다음을 포함하는 셀만 서식 지정"으로 선택된 것이 확인됩니다. 특별히 이 영역은 신경 쓰지 않으셔도 됩니다. 조건부 서식에서 선택한 것에 따라 자동 선택됩니다. 선택 박스를 변경하여 셀 값이 40 이상으로 조건을 변경합니다.서식 버튼을 클릭하고 서식을 변경합니다.(서식 버튼을 클릭하면 셀 서식 대화상자가 나옵니다. 이 부분은 이미 진행한 부분이니 건너뛰겠습니다. 직접 원하는 서식으로 변경해 주세요)서식 규칙 편집 대화상자에 확인 버튼을 클릭하여 규칙 편집을 완성합니다.마지막으로 조건부 서식 규칙 관리자 대화상자에서 확인을 클릭하여 편집된 규칙을 적용합니다. 셀 값이 40 이상인 경우 서식이 적용된 것이 확인됩니다. 줄넘기 시험에 통과한 회수만 강조된 서식이 적용되었습니다. 재시험을 봐야 하는 학생들은 강조되지 않은 서식만 확인하면 되겠습니다. 셀 강조 규칙.xlsx(10.1 Kb)save_alt __adsens-adv1__

2025.07.30 / 09:17