엑셀, 준비 운동

셀 이동하기 셀 포인터( )를 해당 셀로 위치 후 마우스로 직접 클릭, 혹은 방향키 이용 Ctrl + ↑, ↓, →, ← 셀 포인터( )를 해당 셀로 위치 후 마우스로 직접 클릭, 혹은 방향키 이용 Ctrl + ↑, ↓, →, ← Ctrl + Home

데이터 범위 설정하기 하나의 셀 선택 : 원하는 셀로 마우스 포인터를 클릭 여러 개의 셀 선택 : 첫 번째 셀을 선택한 후 마우스를 이용하여 범위를 설정하고 싶은 위치까지 드래그 전체 행과 전체 열을 선택 : 행 번호나 열 번호를 클릭 범위를 연속적으로 선택 : 첫 번째 셀 클릭, 마지막 셀 shift +클릭 범위를 비연속적으로 선택 : 첫 번째 셀 클릭, ctrl키를 누르며 나머지 셀 클릭

데이터 수정 및 삭제 수정 삭제 셀 포인트 위치시키고 다시 입력 자료가 있는 셀을 더블클릭 혹은 F2 수식 입력줄 부분을 클릭하여 수정 삭제 셀 내용 지우기 :셀 선택 후 delete키 서식까지 지우기 : 오른 클릭>삭제

데이터 입력과 취소 Enter : 데이터 입력을 마치고 셀포인터가 한 행 아래로 이동 화살표키 : 데이터 입력을 마치고 셀 포인터를 방향키의 방향대로 한칸씩 이동 Esc : 입력 중인 데이터를 취소 Enter – shift+enter Tab – shift+tab

엑셀 데이터 종류

문자데이터 영문자, 한글, 특수문자 등의 조합으로 만들어진 데이터, 왼쪽 기준 입력 입력하는 데이터의 길이가 셀의 너비보다 길 경우 바로 옆 셀이 비어있을 경우 이어서 표시, 비어있지 않을 경우 잘려 표시 우편번호나 주민번호 같이 문자로 인식되어야하는 숫자데이터는 문자임을 알리는 기호 ‘’’에 이어 입력

수치데이터 셀 너비는 8자리이고, 입력 숫자 길이가 11자리가 넘으면 셀너비가 자동으로 늘어나고 초과시 지수로 표현 셀 너비보다 초과되는 값 입력 ‘#####’이 표시되므로 열너비 조정 숫자는 15자리까지 기억하고 그 이후는 ‘0’으로 표시 ½입력하려면 ‘0 ½’로 입력

날짜/시간 데이터 날짜데이터 시간데이터 연월일 구분 기호로 ‘-’나 ‘/’를 사용 입력된 날짜 표시 형식을 변경하려면 [서식]-[셀]메뉴를 실행한 후 [표시형식]탭에서 [날짜]항목을 클릭하여 원하는 형식지정 ctrl+; 시간데이터 시간은 ‘:’기호를 사용 am혹은 pm 12시, 24시 모두 가능 ctrl+shift+;

논리 데이터와 메모 데이터 논리 데이터 True(1)나 false(0)의 상태를 나타냄 메모 데이터 shift+F2

수식 데이터 수식데이터임을 알리는 기호 ‘=’에 이어 입력 함수도 수식데이터 연산자와 함께 쓸수 있음

다양한 데이터 입력 방법 특수문자 입력 한자입력 목록 중에서 선택하여 입력 여러 셀에 같은 데이터를 입력 한글자음+한자키 [삽입]-[기호] 한자입력 한글입력 후 한자키 목록 중에서 선택하여 입력 alt+↓ 여러 셀에 같은 데이터를 입력 입력할 범위를 지정하고 데이터를 입력한 후 ctrl+enter 한 셀에 여러 줄 데이터 입력 다음 줄로 넘어가는 부분에서 alt+enter

데이터 자동 채우기 문자 및 숫자데이터에 특정 연속성이 있을 경우 범위면 지정하면 데이터를 자동으로 입력 채우기 핸들을 이용 숫자데이터, 문자데이터 그대로 copy 숫자와 문자 혼합된 데이터는 숫자 증가, 문자copy 예외항목은 사용자지정목록에 지정

편리한 데이터 입력 방법 데이터 자동 채우기 사용자 정의로 연속 데이터 만들기 숫자를 드래그하면 같은 숫자로 채우기 된다 두개의 숫자를 범위로 하여 드래그하면 두 값의 차이만큼씩 증감되어 채우기 된다 날짜 데이터를 입력하고 드래그하면 일이 자동 증가되면서 채우기 된다 달만 증가시키려면 마우스 오른쪽 단추로 드래그한 후 [월단위 채우기]를 선택한다 사용자 정의로 연속 데이터 만들기 [도구]-[옵션], [사용자 지정 목록]탭에서 새 목록을 추가 마우스 오른쪽 단추로 드래그

서식 설정

서식 설정 단순히 숫자만 표현할수도 있지만, 백분율 표시를 하거나 통화표시를 하거나 세 자리마다 콤마를 붙이는 등 알아보기 쉬운 각종 기본 표시 형식을 지정할수 있다 <상반기 매뉴얼 도서 판매 현황표 sheet에서 실습>

자동 서식 지정하기 글꼴 크기, 맞춤, 무늬 등 각종 서식을 쉽고 빠르게 적용할 수 있도록 엑셀 자체에서 미리 제공하는 서식 모음 [서식]-[자동 서식] 자동서식 해제는 [자동 서식]대화상자에서 [없음]을 선택

사용자 지정 표시 형식 지정하기 G/표준 : 숫자의 일반 표시 형식 # : 유효한 자릿수를 표시, 유효하지 않은 0은 표시하지 않음 0 : 0값을 자릿수만큼 표시 , : 천 단위 구분 기호를 표시 색지정 : 색 이름을 대괄호([])로 묶어 코드 첫 부분에 표시 yy, mm, dd : 날짜 데이터의 연, 월, 일 부분을 지정한 자릿수대로 표시 @ : 문자 데이터 표시 위치를 지정 * : * 뒤 입력한 문자 반복 표시

사용자 지정 서식 코드 구성 예 [빨강]↑#,##0;[파랑]↓#,##0;- 세 개의 구역으로 구성되며 세미콜론(;)으로 구분하여 정의 각 구역은 순서대로 양수;음수;0값;문자열을 가리킨다 구역을 생략하려면 세미콜론만 입력한다 색상은 대괄호([])안에 색상명을 써서 표시한다

조건부 서식 지정하기 특정한 조건에 맞는 셀만 자동으로 특정 셀 서식이 적용되게 하는 것으로 특정 데이터를 강조하고자 할 때 많이 사용 <소득액이 250만원 이상일 때 글꼴스타일을 굵게, 색은 녹색으로 지정>

인쇄

인쇄영역과 페이지 설정하기 인쇄영역 설정 페이지 설정 [파일]-[인쇄영역]-[인쇄영역설정] [파일]-[페이지설정] [페이지]탭 용지방향 및 배율지정 [여백]탭: 페이지 가운데 맞춤

머리글과 바닥글 설정 [파일]-[페이지 설정], [머리글/바닥글]탭 위쪽 여백 머리글 아래쪽 여백 바닥글

반복할 행/열 설정 인쇄 영역을 따로 설정하거나 데이터가 많아 여러장에 출력될 때 페이지가 넘어가더라도 제목 행이나 제목열이 매 페이지마다 출력되도록 지정 [파일]-[페이지설정], [시트]탭

함수

상대참조와 절대참조 상대참조: 수식을 복사할 때 기본적으로 지정되는 참조 형식으로, 수식이 있는 셀의 상대적 위치를 기중으로 주소가 지정, 상대 참조의 형식의 수식을 다른 위치에 복사하여 붙여 넣으면 셀주소가 상대 위치에 따라 자동으로 변경되어 원래 수식과는 다른 셀을 참조하게 된다 절대참조 : 특정 셀을 고정시켜 참조하고자 할 경우 사용하는 방식, 열과 행번호 앞에 ‘$’를 붙여 표시, 절대 참조형식의 수식은 다른 위치에 복사해도 셀 위치가 변경되지 않는다 참조 형식 변환키 : F4

수식과 함수 사용법 수식은 반드시 ‘=‘로 시작 직접 수식 입력(예;=1024+45) 셀참조로 계산(예;=A2+B2-C2) 함수계산 =함수이름(인수,인수,…) 함수사용 방법 수식입력줄에서 직접입력 함수 마법사 이용

통계 함수 average() : 인수의 평균을 계산 max() : 인수들 중 최대값 구함 min() : 인수들 중 최소값 구함 count() : 인수들 중 숫자의 개수를 구함 counta() : 인수의 개수를 구함 countif(범위,조건) : 범위 중 조건에 맞는 데이터의 개수를 구함 large(인수,x) : 인수 중 x번째로 큰 수를 구함 small(인수,x) : 인수 중 x번째로 작은 수를 구함 rank(값, 참조범위, 방법) : 순위를 구함

CountIf() 형식 COUNTIF(range,criteria) =COUNTIF(A1:A20,”>500”) 예제 ▼예시) 국어점수 70점 이상이 몇 명일까?

Rank() RANK(number,ref,order) =RANK(K5,K5:K20,1) 형식 RANK(number,ref,order) =RANK(K5,K5:K20,1) K5:K20 범위에서 K5셀 값에 대한 오름차순 순위를 구합니다. order 인수를 생략하거나 0을 지정하면 내림차순 순위를 구합니다. 예제 ▼예시) 평균점수로 순위를 매기려면?

수학 함수 sum() : 인수의 합을 계산 sumif(범위,조건,더할영역) : 범위 중 조건에 맞는 셀의 합을 구함 round(인수,자릿수) : 반올림 roundup(인수,자릿수) : 올림 rounddown(인수,자릿수) : 내림

sumIf() SUMIF(range,criteria,sum_range) 형식 =SUMIF(A1:A3,”>=100”,B1:B3) A1:A3 범위에서 100이상인 셀을 찾아 B1:B3 범위에서 같은 위치에 있는 셀의 합계를 계산합니다. sum_range인수를 생략하면 range인수에서 합계를 구합니다 예제 ▼예시) 제품별 금액합계 구하기

날짜/시간 함수 date(연,월,일):숫자3개를 날짜로 만듦 now():현재 시각을 구하는 함수 year():연도추출 month() : 월추출 day() : 날짜에서 일추출 today() : 오늘 날짜 구함 weekday() : 날짜의 요일 계산 hour(), minute(),second():시간,분,초 추출

논리 함수 if(조건식, 값1,값2):조건식이 참이면 값1을, 거짓이면 값2를 수행 and(): 모든 조건이 참이면 참, 하나라도 거짓이면 거짓을 돌려줌 or(): 조건중 하나로도 참이면 참을, 모두 거짓일 때만 거짓을 돌려줌

If() IF(logical_test,value_if_true,value_if_false) 형식 IF(logical_test,value_if_true,value_if_false) =IF(A1>=80,”합격”,”불합격”) A1셀의 값이 80점이상이면 합격, 80점 미만이면 불합격을 표시합니다. 예제 ▼예시) 실제판매액이 목표액 이상이 되면 “목표달성”, 그렇지 못한 경우 “목표미달”

문자열함수 left(문자열, 수치) : 문자열 왼쪽에서 지정한 수치만큼 문자를 추출 right(문자열, 수치) : 문자열 오른쪽에서 지정한 수치만큼 문자를 추출 mid(문자열,수치1,수치2) : 문자열의 수치1에서부터 수치2 만큼의 문자 추출

찾기/참조 함수 vlookup(값, 참조범위,열번호,찾는방법) : 참조 범위의 첫 번째 열에서 기준값을 찾아 같은 행의 지정한 열에서 값을 구함 choose(찾는번호, 인수값….): 정해진 인수 목록 중 지정한 번호의 인수값을 구함

등급표 [2번째 열]의 값을 결과값으로 반환하라 vlookup() 형식 VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) =VLOOKUP(100,A1:C10,3) A1:C10 범위의 첫 번째 열에서 100보다 작거나 같은 값 중 최대값을 찾아 3번째 열의 값을 반환합니다. 예제 ▼예시) 실제판매액이 목표액 이상이 되면 “목표달성”, 그렇지 못한 경우 “목표미달” 어수선의 [74점]이 몇 등급인지 찾고 싶어 점수에 대한 [등급표에서] (74점을) 찾아라 등급표 [2번째 열]의 값을 결과값으로 반환하라 [근사값]을 찾아라

차트

차트 데이터를 한 눈에 비교, 판단할 수 있는 차트 기능을 제공, 데이터의 경향, 연관성 등을 쉽게 판별할 수 있도록 한다 차트마법사( )아이콘 이용 F11 : 새로운 시트에 기본 차트 자동 작성

그래픽 개체 이용

그래픽 개체 그리기 [그리기]도구상자 활성화 shift+그리기 : 정방형의 도형 그리기 ctrl+그리기 : 중심부에서부터 그리기 alt+그리기 : 셀크기에 맞추어 그리기 회전 크기조절점 형태조절점

도형 편집 도형안에 텍스트 입력과 정렬 원하는 도형 맨 앞으로 배치 여러 개체 묶기 해당 도형을 선택 후 글자 타이핑 도형 오른쪽 클릭후 바로가기 메뉴에서 [도형서식]-[맞춤]탭 이용 원하는 도형 맨 앞으로 배치 도형 선택 후 바로가기메뉴의 [순서]-[맨 앞으로 가져오기]클릭 여러 개체 묶기 shift키를 이용하여 도형을 다중선택 후 바로가기 메뉴 [그룹화]-[그룹]선택

데이터 베이스

데이터베이스 관련 용어 필드(field):각각 구분된 항목으로, 항목의 제목을 필드명이라고 한다 레코드(record):관련있는 필드들의 모임, 하나의 행 단위 데이터를 이루는 개념 파일(file): 여러 개의 레코드가 모여 저장 단위인 파일이 된다

데이터 정렬 정렬 도구 이용( ) [데이터]-[정렬] 사용자 지정 정렬 정렬 도구 이용( ) 셀포인터를 정렬기준이 되는 셀에 놓은 후 도구상자 아이콘 이용 [데이터]-[정렬] 2개 이상의 필드를 조건으로 지정할 때 사용자 지정 정렬 [도구]-[옵션],[사용자지정목록]에 정렬기준목록을 추가 [데이터]-[정렬],[옵션]버튼에서 목록선택

자동 필터 많은 데이터 중에서 사용자가 원하는 자료만 얻고자 할 때 사용하는 개념 <직위가 부장이거나 과장인 레코드> 자동으로 필터 단추를 생성하여 간단하고 빠르게 필요한 데이터 추출

고급 필터 자동필터에서 지정할 수 없는 다양한 조건을 지정가능 [데이터]>[필터]>[고급필터] 고급필터 대화상자 현재 위치에 필터 : 결과데이터를 현재 원본데이터가 위치한 곳 다른 장소에 복사 : ‘복사위치’로 지정한 위치에 결과 데이터표시 데이터범위: 필터 기능이 적용될 원본 데이터가 있는 위치 조건 범위 : 사용자가 지정한 조건이 입력된 위치 복사 위치 : 결과가 추출될 위치를 지정 고유 레코드 : 조건을 만족하는 행 중에서 같은 내용의 행이 있을 경우 한 행만 표시 AND 조건: 같은 행 기술팀이면서 대리 근무팀 직위 기술팀 대리 OR 조건: 다른 행 기술팀이거나 대리 근무팀 직위 기술팀 대리

부분합 그룹별로 데이터의 합계, 평균, 개수 등을 자동으로 구할 수 있게 한 기능 [데이터]-[부분합] 정렬 부분합을 실행할 데이터는 반드시 부분합을 구할 기중에 의해 그룹 단위로 정렬되어 있어야 함 <근무팀별 각 항목들의 합계>

피벗 테이블 엑셀의 거의 모든 기능(정렬, 필터, 부분합, 함수 등)이 총망라된 도구 [데이터]-[피벗 테이블/피벗 차트 보고서] <하역사별 입고량>

문의사항 안 화 미 key4u.tistory.com hwami@nate.com l3eauty@hotmail.com