[챕터1]엑셀 핵심 설정부터 주요 기능까지!(2)

오빠두 엑셀
https://www.oppadu.com/challenges/excel-basic-1day/

 

 

1. 셀 참조

---

 

수량*단가로 가격을 구한 뒤 총합계에서 제품의 비율을 구하려고 한다.

가격/합계로 비율을 구하는데 자동채우기를 할 때 합계 셀이 고정되어야 하므로

F4를 눌러서 합계를 절대참조로 변경한 뒤 드래그해주면 전체 제품의 비율을 구할 수 있다.

 

 

아래와 같이 등록건수와 커미션을 곱한 값을 구하려는 경우

등록수는 b열 고정, 커미션은 4행을 고정하여 절대참조를 만들면 한 번 입력한 후 자동채우기로 셀을 완성할 수 있다.

 

 

2. 셀서식

---

첫 번째 행은 10이라는 동일한 값을 입력했지만, 셀 서식을 다르게 적용한 것이고,

두 번째 행은 다른 값을 입력했지만 모두 '(어포스트로피)를 붙여 문자 형태가 된 것이다.

 

• 숫자: 오른쪽 정렬
• 문자: 왼쪽 정렬

 

 

셀 서식은 아래 표를 참조하면 되고, 실무에서는 #,##0, ;(세미콜론) 2가지만 기억해도 충분하다.

 

• #,##0: 천 단위 구분 기호
• ;(세미콜론): 양수일 때, 음수일 때 등 조건을 구분할 때

 

[예제1] 양수;음수에 따라 셀 서식 다르게 설정하기

12345678을 양수일 때는 천 단위 구분, 음수일 때는 천 단위 구분+괄호를 추가하려면 아래와 같이 

셀 서식을 설정하면 된다.

 

 

 

 

[예제2] 셀 서식으로 글자색 다르게 설정하기

 

양수일 때는 빨강색 글씨+증가하는 화살표

음수일 때는 파란색+감소하는 화살표

0일 때는 x를 표시하려고 하면 아래와 같이

양수;음수;0일 때의 조건을 순서대로 입력하면 된다.

 

 

.

 

 

추가로 파랑, 빨강과 같이 색 이름을 입력하지 않고 아래와 같이 색1,2,3 등을 입력해

다양한 색을 사용할 수 있다. 

 

 

3. 조건부 서식

---

 

3-1. 숫자

 

만약 목표치 700,000원보다 낮은 직원을 표시하고자 하면

해당 범위를 선택한 후 조건부 서식 > 셀 강조 규칙 > 보다 작음을 선택하고

직접 값을 입력하거나, 목표치가 입력된 셀을 선택하면 설정이 완료된다.

 

셀을 선택한 경우 셀에서 값을 변경하면 셀서식이 자동으로 적용되므로

값이 변경되면 자동으로 셀서식이 변경되기 원하면 셀을 선택하는 게 유리하다.

 

 

 

3-2. 텍스트

 

특정 단어가 포함된 셀을 강조하고 싶은 경우 조건부 서식 > 셀강조 규칙 > 텍스트포함을 선택하고

직접 텍스트를 입력하거나 해당 셀을 선택하면 된다.

 

 

 

4. 데이터 막대

---

매출현황과 같이 숫자의 크기를 비교해야 하는 표를 보고하는 경우

데이터막대를 활용하면 효과적으로 시각화할 수 있다.

 

데이터 막대 적용 후 내림차순 정렬을 해주면 매출이 높은 순대로 확인할 수 있다.

 

 

 

 

[TIP]

기존에 정해진 서식이 아닌 원하는 서식으로 데이터 막대를 적용하고 싶은 경우 

데이터 막대에서 기타 규칙을 선택하여 색을 선택하면 된다.

 

채우기는 칠(단색)과 그라데이션을 구분하여 선택할 수 있다.

 

기타 규칙과 기본 서식 비교

 

 

5. 스파크라인

---

 

트렌드 분석을 할 때 스파크라인을 활용할 수 있는데 삽입-스파크라인에서 

꺾은선형/열/승패 중 하나를 선택하고 데이터범위를 적용하면 된다.

스파크라인-열

 

 

스파크라인은 제일 높은 숫자, 제일 낮은 숫자 등을 '표시'에서 선택하여 강조할 수 있는 장점이 있다.

또한 막대에서 꺾은 선으로 모양을 변경하고 싶을 때도 '종류'에서 선택하면 변경이 가능하다.

만약 기존 스타일이 아니라 원하는 색으로 변경하고 싶을 때는

일반색은 '스파크라인 색', 강조색은 '표식 색'에서 선택하면 된다.

 

 

 

6. 아이콘 강조

 

---

 

 

[TIP]

• Ctrl+Shift+1: 통화 서식 예) 1000 > 1,000
• Ctrl+Shift+3: 날짜 서식
• Ctrl+Shift+5: 백분율  서식 예)0.62 > 62%

 

 

달성률에 따라 🔴🟡🟢 아이콘을 설정하려고 하면

조건부 서식 > 아이콘 집합 > 기타 규칙을 선택한다.

 

기본설정은 백분율 상위 33%, 중위 33%, 하위 33%와 같이 설정되지만

아래와 같이 등급이 정해져 있는 경우 종류를 숫자로 변경한 후 값을 입력하여 설정한다.

 

• 80%미만 > 경고
• 80% 이상 >주의
• 100% 이상>우수

 

 

 

아이콘 집합은 해당 값 왼쪽에 표시되는데, 80% 미만인 경우만 아이콘을 별도로 입력하고 싶을 때는 어떻게 해야 할까?

 

먼저 우측 열에 달성률을 불러온 다음 조건부 서식 아이콘 집합 > 기타 규칙을 선택한 다음

'아이콘만 표시'를 체크한다.

 

80% 미만일 경우에만 아이콘을 표시하고자 하므로 0.8 이상은 '셀 아이콘 없음'으로 선택한다.

 

 

 

7. 자동화 보고서

---

부서명을 선택하면 아래와 같이 해당 부서의 직원들을 자동으로 강조하고자 한다.

..

 

1)목록상자

우선 부서를 선택할 목록상자를 만든다.

데이터 탭 > 데이터 유효성 검사를 선택한 후 제한대상을 '목록'으로 선택하고 원본을 부서명 범위를 선택한다.

 

 

 

2)조건부 서식

부서명을 선택했을 때 표에서 해당하는 팀의 직원을 강조하려고 하므로

조건부 서식 > 새 규칙을 선택한 다음 '수식을 사용하여 서식을 지정할 셀 결정'을 클릭한다.

 

B열의 부서명과 H3셀의 부서명이 동일할 때 셀 서식을 설정해야 한다.

조건부 서식의 범위는 부서명부터 연봉까지를 전부 선택했지만, 현재 활성화된 셀은 B3이므로

수식에서 등호 입력 후 B3과 H3셀을 선택하고 두 셀이 같은지 확인해야 하므로 셀 사이에 등호를 입력한다.

 

이때, B열의 부서명이 순서대로 움직여야 하므로 &B&3에서 &B3으로 B행만 고정해준다.

그리고 서식 버튼을 눌러 원하는 서식을 선택하면 서식이 적용된다.