초보자를 위한 엑셀 핵심 기능 총정리! 34개 실전 스킬 모음.zip
엑셀 핵심 기능 목차 바로가기
영상 강의
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [기초입문] 2024년 필수! 엑셀 필수 기능 34개 총정리 특강 - 예제파일예제파일
라이브 강의 전체영상도 함께 확인해보세요!
위캔두 회원이 되시면 매주 오빠두엑셀에서 진행하는 라이브강의 풀영상을 확인하실 수 있습니다.
데이터 관리 : 행 별로 빠르게 병합! - 전체병합 기능
- 예제파일을 실행한 후, [데이터 관리] 시트로 이동하면 등록 차수별 머릿글을 확인할 수 있습니다.
등록 차수별 머리글 범위가 작성되어 있습니다. - 실무에서 셀 병합을 적용하면, 아래 그림과 같이 항상 '가운데 정렬'이 기본으로 적용되어 불편할 때가 있습니다.
기본 셀 병합을 사용하면, 값이 가운데로 정렬됩니다. - 그럴 때, '전체 병합' 기능을 사용하면 선택한 여러 범위를 '행 단위'로 편리하게 병합할 수 있습니다. Ctrl 키를 누른 상태로, 병합할 범위를 모두 선택합니다.
Ctrl 키를 누른 상태로 병합할 범위를 모두 선택합니다. - [홈] 탭 - [병합합하고 가운데 맞춤] 우측 화살표 - [전체 병합]을 클릭하면, 선택한 범위가 행 단위로 한 번에 병합됩니다.
전체병합을 적용하면 모든 범위가 행 단위로 한 번에 병합됩니다. 오빠두Tip : 전체 병합을 사용하면 셀의 기본 정렬을 유지한 상태로 셀을 병합할 수 있습니다!
셀 병합 보고서 : 서식 복사로 셀병합 문제 해결하기
- 셀 병합 기능은 데이터를 올바르게 관리하려면 되도록 사용하지 않는 것이 좋습니다. 만약 셀 병합을 불가피하게 사용해야 할 경우, 아래 입문 강의에서 안내해드린 셀 병합 주의사항과 해결 방법을 꼭 숙지한 후 사용하세요!
- 예제파일에서 [셀 병합] 보고서 시트로 이동하면 '각 기수별'로 셀이 병합된 표가 있습니다.
각 기수별로 셀이 병합되어 있습니다. - 셀 병합된 범위는 첫번째 셀을 제외한 나머지는 빈칸으로 관리되어 실무에서 함수나 필터를 적용할 때 다양한 문제가 발생할 수 있습니다. 예를 들어, 기수에서 [2차]로 필터를 적용하면 2차의 첫번째 값만 필터링 됩니다.
병합된 범위에 필터를 적용하면 첫번째 셀만 필터링 됩니다. - 이러한 문제를 해결하기 위해 병합된 범위의 빈칸을 채워보겠습니다. 먼저 셀 병합된 범위를 복사한 후, 좌측 비어있는 곳에 붙여넣기 합니다.
셀 병합된 범위를 주변 비어있는 곳에 하나 더 추가합니다. - 기존의 병합된 범위를 선택한 후, [홈] 탭 - [병합하고 가운데 맞춤]을 클릭하여 셀 병합을 해제합니다.
셀 병합을 해제합니다. - 셀 병합이 풀린 범위를 선택한 상태로, Ctrl + G 를 동시에 누르거나 F5를 눌러 이동창을 실행한 후, [옵션]을 클릭해서 '이동 옵션'으로 이동합니다.
병합이 해제된 범위를 선택한 상태에서 이동 옵션을 실행합니다. - '이동 옵션' 대화상자에서 '빈 셀'을 선택한 후, [확인] 버튼을 클릭합니다.
빈 셀을 선택한 후, [확인] 버튼을 클릭합니다. - 수식입력줄에 현재 활성화된 셀 기준 위쪽셀을 입력한 후, Ctrl + Enter로 수식을 일괄 입력하면 빈 칸이 한 번에 채워집니다.
활성화된 셀 기준 윗셀을 입력한 후, Ctrl + Enter로 수식을 한 번에 입력합니다. 오빠두Tip : Ctrl + Enter는 선택한 범위에 수식을 일괄 입력하는 단축키입니다. - '4'번 단계에서 추가한 임시 범위 선택한 후, [홈] - [서식 복사]를 클릭해서 서식을 복사합니다.
이전에 복사한 병합된 범위를 선택한 후, [서식 복사] 합니다. - 이후 기존 셀 병합 범위의 시작셀을 클릭하면, 빈칸이 모두 채워진 셀 병합 범위가 완성됩니다.
기존 셀 병합된 범위의 첫번째 셀을 클릭하면, 셀 병합 서식이 적용됩니다. - 마지막으로 '4'번 단계에서 추가한 임시 셀 병합 범위를 삭제한 후, 완성된 셀 병합 범위에 필터나 함수를 적용하면 데이터를 오류 없이 올바르게 집계할 수 있습니다.
이렇게 셀 병합을 적용하면 필터, 함수를 올바르게 사용할 수 있습니다.
그룹별 순번 : IF 함수로 그룹별 순번 구하기
- [그룹별 순번] 시트로 이동한 후, C3셀에 아래 IF 함수 수식을 입력합니다.
=IF(B3<>"",1,C2+1)
- 작성한 수식을 아래로 자동채우기하면 그룹별 순번이 완성됩니다.
수식을 아래로 자동채우기하면 그룹별 순번이 완성됩니다. 오빠두Tip : 그룹이 가로로 셀 병합된 경우 순번을 매기는 방법은 아래 1분 영상 강의를 참고하세요!
필터팁 : 구간 데이터 시각화 & 분석 - 아이콘 집합
- 아이콘 집합을 사용하면 구간 데이터를 편리하게 시각화하고 분석할 수 있습니다 .아이콘 집합 기능에 대한 보다 자세한 설명은 아래 기초 입문 강의를 참고하세요!👇
- 먼저 아이콘을 적용할 범위를 선택합니다. 예제파일에서 평균 범위가 작성된 H3:H26 범위를 선택한 후, [홈] - [조건부서식] - [아이콘 집합] - [기타 규칙]으로 이동합니다.
아이콘을 추가할 범위를 선택한 후, [조건부서식] - [아이콘 집합] - [새 규칙]으로 이동합니다. - [새 서식 규칙] 대화상자가 실행되면, 아래 그림과 같이 종류를 모두 '숫자'로 변경한 후 기준에 맞게 값을 적절히 입력합니다.
서식 규칙에서 종류를 모두 '숫자'로 변경한 후, 값을 적절히 변경합니다. - [확인] 버튼을 클릭하면 아이콘이 한 번에 적용됩니다.
[확인] 버튼을 클릭하면 아이콘이 적용됩니다. - 이제 필터에서 [색 기준 필터]를 사용하면, 각 아이콘마다 데이터를 편리하게 필터링할 수 있습니다.
아이콘 집합을 적용하면, 아이콘 기준으로 구간별 필터를 편리하게 사용할 수 있습니다.
복붙팁 : 무작위 데이터를 복사 - 클립보드 기능
- 무작위 데이터를 복사/붙여넣기 할 때, 클립보드를 활용하면 매우 편리합니다. [홈] 탭에서 '클립보드 그룹' 우측 하단에 있는 '펼침' 버튼을 클릭하면 [클립보드] 패널이 실행됩니다.
[홈] 탭 - [클립보드] 그룹의 확장버튼을 클릭합니다. - 만약 클립보드에 기존 복사된 값이 있다면 [모두 지우기]로 초기화한 후, 데이터를 무작위로 복사하면 아래 그림과 같이 클립보드에 데이터가 하나씩 추가됩니다.
데이터를 복사하면 클립보드 패널에 복사한 데이터가 추가됩니다. - 복사한 데이터를 붙여넣기할 시트로 이동한 후, [모두 붙여넣기]를 클릭하면 복사한 데이터가 한 번에 붙여넣기 됩니다.
[모두 붙여넣기]를 클릭하면 클립보드의 모든 데이터가 일괄 붙여넣기 됩니다. 오빠두Tip : 클립보드 기능을 사용하면 셀 병합된 범위의 텍스트를 편리하게 나눌 수 있습니다. '클립보드'의 또 다른 사용법은 아래 1분 영상 강의를 참고하세요!👇
틀고정 : 빅데이터 분석 핵심 기능 - 틀고정
- 틀 고정 기능은 한 화면에 보이지 않는 대량의 데이터를 분석할 때 유용한 기능으로, 아래 2가지 규칙만 기억하면 충분합니다!
· 단축키 : Alt - W - F - F
· 선택한 셀의 왼쪽/위쪽 기준 고정 - 예제파일에서 [틀고정&스크롤] 시트로 이동한 후, 틀 고정할 기준셀인 D2셀을 선택합니다. 틀고정은 선택한 셀의 '왼쪽/위쪽' 기준으로 적용되는 것만 기억하세요!
D2셀을 선택한 후, 틀 고정을 적용합니다. - 단축키 Alt - W - F - F 를 차례대로 누르면 틀 고정이 적용됩니다. 이제 스크롤을 아래/오른쪽으로 이동해도 머리글이 항상 화면에 고정됩니다.
- 엑셀 2019 이후 버전을 사용중일 경우, Ctrl + Shift + 스크롤로 횡 스크롤을 사용할 수 있습니다.
테이블 : 표 기능으로 머리글 항상 고정하는 법
- 표 기능을 사용하면 틀 고정을 사용하지 않고 머리글을 편리하게 고정할 수 있습니다. 예제파일에서 [테이블] 시트로 이동한 후, 아래 표에서 임의의 셀을 선택합니다.
- 셀을 선택한 상태로 [삽입] - [표]를 클릭하거나, Ctrl + T를 눌러 표 만들기 대화상자를 실행합니다. [머리글 포함]을 선택한 상태로 [확인] 버튼을 클릭하면 범위가 표로 변경됩니다.
범위를 표로 변경합니다. - 이제 표를 선택한 상태로 스크롤을 아래로 내리면, 머리글을 항상 표시할 수 있습니다.
표로 변경된 범위는 스크롤을 아래로 내렸을 때 머리글이 항상 표시됩니다. - 표 기능은 앞으로 엑셀에 추가될 다양한 기능인 파워쿼리, 코파일럿을 사용하려면 반드시 알아야 하는 중요한 기능입니다. 표에 대한 자세한 설명은 아래 5분 기초 영상 강의를 참고하세요!👇
행열 강조 : 현재 셀 행/열 실시간 강조 방법
- 조건부서식을 사용하면 현재 선택된 셀의 행/열을 편리하게 강조할 수 있습니다. 예제파일에서 [행열강조] 시트로 이동한 후, X1 셀에 작성된 공식을 앞의 작은따옴표(')를 제외해서 복사합니다.
행/열 강조 공식을 복사합니다. - 왼쪽 표에서 임의 셀을 선택한 후, Ctrl + A 를 눌러 표를 전체 선택합니다. 이후 [홈] 탭 - [조건부서식] - [새 규칙]으로 이동합니다.
행/열 강조를 적용할 범위를 선택한 후, [조건부서식] - [새 규칙]으로 이동합니다. - '새 서식 규칙' 대화상자가 실행되면 마지막 항목인 '수식을 사용하여 서식을 지정할 셀 결정'을 선택한 후, 입력창에 복사한 공식을 붙여넣기 합니다.
수식을 사용하여 서식을 지정할 셀 결정을 선택한 후, 입력창에 공식을 붙여넣기합니다. - [서식] 버튼을 클릭한 후, '채우기' 및 '글꼴' 탭에서 행/열 범위에 적용할 서식을 선택합니다.
행/열 강조시 적용할 글꼴, 채우기 색을 적절히 선택합니다. - [확인] 버튼을 클릭하면 선택한 셀의 전체 행/열이 강조되며, F9 키를 눌러 강조되는 범위를 갱신할 수 있습니다.
선택한 셀의 행/열이 강조됩니다. 오빠두Tip : 매크로를 사용하여 전체 행/열을 강조하는 모든 과정과 주의사항은 영상 강의를 확인해주세요!
셀 서식 : 주식 스타일의 증/감서식 만들기
- 셀 서식은 엑셀 보고서 시각화에 매우 중요한 요소입니다. 실무에서 사용되는 주요 셀 서식은 아래 2가지만 기억하면 충분합니다.
· #,##0 : 천단위 구분기호가 적용된 정수
· ;(세미콜론) : 양수;음수;0;텍스트 서식 구분오빠두Tip : 실무에서 꼭 필요한 셀 서식 핵심 규칙에 대한 자세한 설명은 아래 5분 기초 영상 강의를 참고하세요! 👇
- 예제파일에서 [셀 서식] 시트로 이동한 후, 주식 스타일의 증/감 서식을 적용할 값이 작성된 D3:D17 범위를 선택합니다.
예제파일에서 증감 서식을 적용할 범위를 선택합니다. - 단축키 Ctrl + 1 을 동시에 눌러 셀 서식 대화상자를 실행한 후, [사용자 지정] 항목을 선택하고 입력창에 아래 형식을 작성합니다.
[빨강]▲#,##0;[파랑]▼#,##0;-
'양수;음수;0 일때 서식이 각각 적용됩니다.
'양수일때에는 빨강색의 증가하는 숫자, 음수일 때에는 파랑색의 감소하는 숫자, 0일 때는 "-"를 표시합니다.
사용자 지정으로 미리 작성해드린 표시 형식을 입력합니다. - [확인] 버튼을 클릭해서 셀 서식을 적용하면 주식 스타일의 증/감 서식이 적용됩니다.
[확인] 버튼으로 셀 서식을 적용하면 주식 스타일로 숫자가 표시됩니다.
스파크라인 : 트렌드 분석 핵심 기능! - 스파크라인
- 스파크라인은 '가로로 작성된 시계열 데이터의 트렌드'를 분석할 때 매우 유용한 기능입니다.
스파크라인은 트렌드를 분석할 때 매우 유용한 기능입니다. - 예제파일에서 [스파크라인] 시트로 이동한 후, G2셀에 '트렌드'라는 머리글을 작성합니다. 이후 스파크라인을 추가할 G3:G14 범위를 선택한 후, [삽입] - [스파크라인] - [꺾은선] 을 선택합니다.
스파크라인을 추가할 범위를 선택한 후, [삽입] - [스파크라인]을 선택합니다. - [스파크라인 만들기] 대화상자가 실행되면 데이터가 작성된 C3:F14 범위를 선택한 후, [확인] 버튼을 클릭합니다.
데이터가 작성된 범위를 선택합니다. - 선택한 범위에 트렌드가 한 번에 추가됩니다. 이제 스파크라인을 선택한 후, [스파크라인] 탭에서 스파크라인 종류, 높은점/낮은점 강조, 스타일을 자유롭게 꾸밀 수 있습니다.
오빠두Tip : 스파크라인 시각화에 대한 자세한 설명은 아래 5분 기초 영상 강의를 참고하세요!👇
- 예제파일을 실행한 후, [데이터 관리] 시트로 이동하면 등록 차수별 머릿글을 확인할 수 있습니다.
M365에서는 모든 기능을 사용하실 수 있습니다. :)
어느 기능이 사용이 안되는지 자세히 남겨주시면 확인해드리겠습니다.
감사합니다.
선택한 영역 옆에 있는 모든 데이터를 확인했지만 사용자의 채우기 값에 대한 패턴을 찾을 수 없습니다. 빠른채우기를 사용하려면 출력물의 예제를 몇개 입력한 다음 채우려는 열에 있는 셀 하나를 활성화한 상태에서 [빠른채우기] 단추를 다시 클릭하세요.
----->> 이말이 뜨는데 왜그런걸까요??
타이타닉 이라는 말을 입력하고 타이타닉 바로 밑에 셀에서 ctrl+e 키를 눌렀거든요, 제가 잘못된 방법으로 하는걸까요??
빠른채우기는 '인접한' 데이터에서만 실행할 수 있습니다.
'타이타닉'을 작성한 셀이, 혹시 원본데이터가 작성된 범위와 떨어져 있진 않은지 한번 확인해보시겠어요? :)
감사합니다!
원본데이터와 작성된 범위가 떨어져 있다는게 무슨말인지 잘 모르겠어요..
같은 시트 내에서 했는데요ㅜ
영화제목들 주루룩 나와있고 강의에서 하시는 것처럼 바로 옆에서 타이타닉이라고 쓰고 ctrl+e했는데 저런 말이 계속 뜹니다
먼저 남겨주신 글의 상황으로는 빠른채우기가 잘 실행되어야 하는 것이 맞습니다. 아마도 오류가 발생하는 다른 원인이 있을 것 같습니다.
사용하고 계신 데이터와 오류가 발생한 상황의 스크린샷을 아래 이미지 첨부로 함께 올려주시겠어요?
확인 후 답변 드리겠습니다. 감사합니다.
스크린샷 첨부를 뭘 눌러야 하는지...
컴터초짜라,,죄송해요ㅜ
스크린샷은 댓글 오른쪽 하단의 이미지 모양 아이콘을 클릭하시면 첨부할 수 있습니다.
또는 홈페이지의 Q&A 게시판을 이용하시면 더 편리하게 질문/답변을 얻으실 수 있으니 아래 링크도 한번 확인해보세요. :)
엑셀 Q&A 게시판 - 오빠두엑셀 (oppadu.com)
감사합니다.
행 높이는 기본적으로 같이 붙여넣기 됩니다. :)
단 예외 상황으로, 복사한 범위의 행높이가 복사한 범위와 같은 행에 위치한 다른 범위의 데이터로 인해 높은 상황이라면, 행 높이가 복사되지 않을 수 있으며 그럴 경우에는 행 높이를 직접 조정해야 합니다.
감사합니다.
그런 경우 A3셀 기준 IF 함수를 아래와 같이 작성해보세요.
=IF(B3=B2,A2,A2+1)
감사합니다.
=IF(B3<>B2,N(C2)+1,C2)