엑셀 배우기(36) - 조건부 서식(5) - 수식을 사용해 셀 지정

⑥ 수식을 사용하여 서식을 지정할 셀 결정

엑셀배우기35.xlsx

0.01MB

같은 줄, 성명 별로 최댓값 찾아서 서식 적용하기

㉮ 마우스로 B3셀부터 E7셀까지 끌어 사각형 모양의 범위를 잡습니다. 그리고, 조건부 서식 - 새 규칙을 누른 후 '수식을 사용하여 서식을 지정할 셀 결정'을 클릭합니다.

㉯ 수식 입력 줄에
=b3=max($b3:$e3)
이라고 입력합니다.

b3는 범위 내 첫 번째 셀이며, 그다음 max는 최댓값을 구해주는 함수로서, 인수가 $b3:$e3라고 되어 있으므로 첫 번째 줄에서 최댓값을 구해줍니다. 여기서 $b3는 $b3라고 입력해도 되고, b3라고 입력한 다음 F4키를 3번 눌러도 됩니다.

그리고, 첫 번째 =은 수식 입력 시 사용하는 기호이며, 두 번째 =은 같은지 비교하는 비교 연산자입니다. 따라서, 이 수식은 "b3셀이 b3에서 e3까지의 최댓값과 같다면"이 되는데, b3셀의 71이 최댓값 87이 아니므로 서식이 적용되지 않고, D3셀의 값이 최댓값과 같으므로 D3셀에 서식이 적용됩니다.

또한 서식이 적용 대상이 B3셀에서 E7셀까지이니까 그 범위 내의 셀을 모두 돌아다니면서 해당 줄의 최댓값과 맞는지 비교하는 것이고, 셀이 이동할 때 최댓값을 구하는 열이 변하면 안 되니까 B와 E열 앞에 $기호를 넣어서 변하지 않도록 한 것입니다.

㉰ 오른쪽 아래 서식 버튼을 누른 후 배경 색을 초록으로 지정하고, 확인 버튼을 누릅니다.

㉱ 그리고 다시 확인 버튼을 누르면

㉲ 줄 별로 다시 말해 성명 별로 최대 점수에 초록색을 칠합니다.

같은 열, 과목별 최댓값을 찾아서 서식 적용하기

위에서는 행 기준이었는데, 열 기준이므로 최댓값을 구할 때 행이 변하면 안 됩니다. 따라서 수식을
=b3=max(b$3:b$6)
로 입력해야 합니다.

이와 같이 변하지 말아야 할 것에만 $표시를 추가해서 혼합 참조 형식으로 바꾸는 것이 중요합니다. b열은 오른쪽으로 이동할 때 c, d, e로 바뀌어야 하기 때문에 b앞에는 $기호를 추가하면 안 됩니다.

㉮ 현재 범위에서 열 기준으로 할 경우 최댓값이 무조건 총점이 되므로 범위를 B3셀에서 E6셀까지로 수정합니다.
범위를 B3셀에서 E6셀까지 다시 끌어서 지정할 수도 있고, Shift 키를 누른 상태에서 위 화살표 키를 한 번 누르면 범위가 한 줄 축소되므로 같은 결과가 나옵니다.

㉯ 조건부 서식 - 새 규칙을 누르고, '수식을 사용하여 서식을 지정할 셀 결정' 유형을 선택한 다음, 수식에 =b3=max(b$3:b$6) 라고 입력합니다.

㉰ 위 화면에서 서식 버튼을 누른 후 배경 색으로 주황으로 클릭하고 확인 버튼을 누릅니다.

㉱ 아래와 같은 화면이 되는데, 확인 버튼을 누릅니다.

㉲ 과목별 최댓값이 구해졌는데, 갑순이의 최댓값은 없어졌습니다. 나중에 적용된 규칙이 우선해서 그렇습니다.

규칙 적용 순서 지정하기

두 번째로 지정한 규칙(과목별 최댓값과 같은 셀)이 우선됐는데, 첫 번째로 지정한 규칙(성명 별이 우선되도록 하려면 조건부 서식 - 규칙 관리를 누른 다음 빨간 선으로 칠한 네모 안의 위, 아래 화살표 키를 눌러 규칙의 우선순위를 바꿔주면 됩니다.

현재 첫 번째 줄이 두 번째로 지정한 규칙인데, 아래 화살표 키를 누르면 첫 번째 줄이 두 번째 줄로 이동합니다. 확인 버튼을 누릅니다.

그러면 어떤 셀이 과목별 최댓값이면서 성명 별 최댓값일 때 과 성명 규칙이 우선돼서 초록색이 됩니다. 다시 말해 갑순이의 영어 점수 100점은 영어의 최댓값이므로 주황이었다가 갑순이의 최댓값이므로 초록색으로 변경됐고, 과학의 최댓값 99점도 주황이었는데, 이몽룡의 최대 점수이므로 초록색으로 바뀌었습니다.

조건에 맞는 한 줄 모두 색칠하기

예를 들어 영어점수가 90점 이상인 경우 그 행을 모두 같은 색으로 칠하려고 하는 경우는 비교할 값이 영어 열이어야 하므로
=$c3>=90
이라고 C열을 고정하고, 비교 값이 90 이상이므로 >=90이라고 수식을 작성하면 됩니다.

㉮ 적용 범위는 A3셀부터 G6셀까지로 하고,

㉯ 수식에 =$c3>=90을 입력합니다. 그리고, 서식 버튼을 누른 후

㉰ 서식의 배경색을 파란색으로 합니다. 확인 버튼을 누르고,

㉱ 확인 버튼을 누르면 서식이 파랑 배경색으로 지정됩니다.

㉲ 아래 화면으로 돌아오는데, 다시 확인 버튼을 누릅니다.

㉳ 그러면 아래와 같이 영어 점수가 90점 이상인 4행과 5행이 파란색으로 칠해집니다. 기존에 적용됐던 서식이 후순위가 돼서 지워졌습니다.

이외에도 평균을 적용하려면 average 함수, 최솟값을 적용하려면 min 함수를 적용하면 됩니다.

엑셀배우기36(완성).xlsx

0.01MB