[엑셀] 유용한 팁들
특정데이터만 필터링해 색채우기
시트에서 원하는 범위을 선택합니다. 숫자필터에서 조건을 지정합니다. 결과값을 선택하고 색채우기를 실행합니다.
[데이터]탭-[정렬 및 필터]그룹에서 [지우기]를 클릭하여 필터조건을 해지합니다.
원하는 값만 색이 채워진 것을 확인할 수 있습니다.
*필터링된 상태에서 다른 범위의 데이터를 붙여넣는 경우 숨겨진 행(단일 셀이 아닌 연속 범위)에도 데이터가 붙여넣기되어 자료에 오류가 발생합니다.
셀 병합하지 않고 텍스트 채우기
셀을 병합하면 함수식을 복사할 수 없고, 정렬하기가 안되는 등, 문제가 발생할 때가 있습니다. 이럴 경우, 셀을 병합하지 않고, 병합한 것처럼 효과를 낼 수 있는 방법이 있습니다.
지정 범위를 선택하고 마우스 오른쪽 버튼을 눌러 [셀서식]을 선택합니다. [맞춤]탭에서 '텍스트 맞춤'의 '가로'에서 [선택영역의 가운데로]를 선택합니다.
*'세로'에는 '선택 영역의 가운데로' 기능이 없어서 열을 병합할 때만 적용됩니다.
* 세로로 병합된 셀에 함수식을 채우고 싶을 경우, 채우고자 하는 크기가 일정하지 않은 병합된 각 셀을 선택하고 함수식을 입력한 후 Ctrl+Enter를 누릅니다. (Ctrl+Enter를 누르면 셀에 함꺼번에 함수식을 적용할 수 있습니다.)
중복값 확인
조건부 서식을 통해 엑셀에서 셀의 중복값을 쉽게 찾아낼 수 있습니다.
[홈]탭-[스타일]그룹에서 [조건부 서식]을 클릭하고 [셀 강조 규칙]-[중복 값]을 선택합니다.
[중복 값]대화상자가 열리면 '다음 값을 포함하는 의 서식 지정'에서 [중복]을 선택하고, 서식은 임의로 선택합니다.
*데이터를 입력하기 전에 중복값 조건부 서식을 미리 지정해두면 중복 입력을 미리 차단할 수 있습니다.
입력형식 지정하기
핸드폰번호을 입력해야 하고 하이픈을 포함해서 열세자리의 숫자를 입력해야 하는 경우,
입력할 범위를 선택하고 [데이터]탭-[데이터 도구]그룹에서 [데이터 유효성 검사]를 클릭한 후 [데이터 유혀성 검사]를 선택합니다.
[데이터 유효성] 대화상자의 [설정]탭이 열리면 '제한 대상'에서 [사용자 지정]을 선택하고 '수식'에 다음의 함수식을 입력한 후 [확인]을 클릭합니다.
<함수식>
=LEN(해당셀)=13
주말 날짜에만 빨간색 표시하기
범위선택 후 [홈]-[스타일]-[조건부 서식]-[새규칙]
새 서식 규칙 대화상자에서 [수식을 사용하여 서식을 지정할 셀 결정]을 선택
수식(=OR($A5="토", $A5="일")을 입력. 서식창에서 색깔 선택.
(함수 OR은 두 가지 조건 중 하나의 조건만 충족해도 된다는 것. )
서식 복사보다 조건부 서식 활용하기
조건부 서식을 활용하여 자동화된 양식으로 만들어서 사용하면 좀 더 편리합니다.
표 내용에 맞추어 자동으로 셀에 테두리가 추가되고 지워질 수 있도록 할 수 있습니다.
범위 선택 후 -[조건부 서식]-[새규칙]-[수식을 사용하여 서식을 지정할 셀 결정]을 선택
수식(=$A4<>"")를 입력하고 서식을 선택함.
여기서 $A4<>""은 A4셀이 공백이 아닐 경우를 의미함.
불필요한 숫자 0은 표시하지 않기
방법 1> 범위 선택 후 [조건부 서식]-[새 서식 규칙]에서 [다음을 포함하는 셀만 서식 지정]을 선택 후 '='을 지정.
서식에서 글꼴 색을 흰색으로 지정
방법 2> 파일탭에서 옵션 선택 후 대화상자에서 고급범주를 선택. '이 워크시트의 표시 옵션'에서 [0갑이 있는 셀에 0 표시]체크를 해제합니다.
동일한 항목을 요약 및 계산해주는 부분합
부분합을 적용하기 위해서는 부분합을 구할 항목을 정렬해야 합니다.
부분합을 부하기 위해 원하는 범위를 선택하고 [데이터]탭-[개요]그룹-[부분합]을 클릭합니다.
[부분합]대화상자에서 '그굽화할 항목'을 선택하고, '사용할 함수'에서는 [합계]를 선택하고, '부분합 계산 항목'에서는 계산할 항목을 선택하고 확인을 클릭합니다.
행 머리글에 그룹과 윤곽기호와 윤곽 번호가 나타나고 항목별로 부분합계가 계산됩니다.
*실제 사용에서 근로자별로 한해 동안 연말정산을 할 때, 근로자별로 그룹화시키고, 근로자가 각 월별 받은 지급액들을 부분합으로 구할 경우 이용할 수 있는 방법입니다.
*문제를 해결하기 위해 여러 각도에서 요약된 값이 필요하다면 부분합 대신 피벗 테이블을 활용하는 것이 좀 더 효율적입니다. 부분합보다 무조건 피벗 테이블을 사용하는 것이 아니라 상황과 필요에 따라 적절한 작업 패턴을 갖는 것이 중요합니다.
중복된 항목 제거하기 전에 정렬은 필수!
범위선택-[데이터]탭-[데이터 도구]그룹에서 [중복된 항목 제거]
'중복된 항목 제거'기능을 사용하여 데이터를 삭제하려고 할 경우, 삭제 우선순위에 따라 상위 데이터가 남고 아래쪽 행에 입력된 중요한 데이터가 제거될 수 있습니다. 따라서 이 경우, 중복된 항목을 삭제하기 전에 데이터를 정렬해서 위치를 변경해야 합니다.