Excel Study

엑셀의 기능>

  1. 계산 : 수식입력 함수
  2. 일반 문서작성
  3. 그래프 그리기
  4. 데이터관리
  5. VBA를 이용한 프로그래밍

Excel은 작업공간이 sheet인데 sheet는 여러 개가 있다.

표같이 생긴 조그마한 것을 Cell이라고 한다. (식이나 문자, 여러 개를 넣을 수 있다.)

work sheet : 행(숫자), 열(영문자)로 구성되어 있다.

work sheet의 크기를 알려면 ‘ctrl+방향키’를 누르면 된다

셀의 주소가 있다. A12(열-행), F7(열-행)

왼쪽 위에 셀의 주소가 나타난다.

연속 범위 표기 A1:A10

복수 영역 표기 Ctrl 누르면서 다시 표기하면 된다.

원하는 행의 머리글을 클릭하면 행 전체가 선택되어진다. (열도 마찬가지)

수정을 원하는 곳에 커서를 놓고 고침

F2키를 눌러 커서를 만든 후 수정

입력중인 자료는 ESC누르면 전체가 지워진다 입력 완료된 자료는 셀 선택 후 DEL키를 누른다

자료의 이동 : 이동하고자 하는 셀을 드래그 (이동할 때 마우스 포인터가 달라진다.) 자료의 복사 : 그냥 복붙

먄약 셀 사이에 열을 삽입하고 싶다면 삽입하고 싶은 열 맨 위에 가서 오른쪽 마우스 클릭하고 삽입을 누르면 된다.

행이나 열의 너비를 조절하고 싶으면 경계 라인을 드래그 하면 된다. (다른 열과 함께 조절하고 싶다면 Ctrl클릭하고 다 선택한 후에 조절하면 다 똑같이 조절된다. ) (가운데 정렬을 하고 싶다면 서식-셀-맞춤-세로에서 가운데를 선택하면 된다.)

병합된 걸 풀어주고 싶으면 서식-셀-텍스트 조정에서 셀 병합 해제를 선택한다.

한자입력 : 블록 설정하고 한자 키를 누르면 끝 특수문자 : ㅁ(일반 기호), ㄷ(산술연산자), ㅇ(원문자)에 한자 키를 누르면 끝.

자동채우기 : 셀 왼쪽 아래에 십자가 키가 뜨면 그 때 드래그 해주면 된다. Ctrl키를 누른 후 드래그하면 증가시키면서 복사할 수 있다. 숫자와 문자가 같이 있다면 숫자만 증가한다. 정말 신기한 건 한 셀에 100을 입력하고 바로 옆 셀에 105를 입력한다. 이 후에 드래그로 늘려주면 알아서 5가 증가되어 나타난다. (ctrl키를 )

날짜 입력하기 : 슬래시(/)나 하이픈(-)을 사용해야한다. (17-1-1 or 17/1/1) (서식-셀-표시형식-날짜)

너비가 너무 작아서 ####표시가 나올 수 있다. 너비를 크게 하면 괜찮아진다.

하나의 셀에 두 줄로(엔터 키와 같이) 치려면 ALT+ENTER를 써야한다.

테두리를 그리리면 그냥 홈에서 표 비슷한 게 있음. (아니면 셀 서식-테두리)

보기-눈금선 선택을 해제하면 sheet의 셀 눈금선이 없어진다.

ctrl+c를 눌렀을 때

Ctrl+c를 눌러서 다른 시트에 복사할 때 열 너비는 적용되지 않는다. So, 카메라 기능이 있다. 파일-옵션-빠른 실행 도구-리본에 없는 명령-추가하면 위쪽에 카메라가 생긴다. 찍은 다음에 하면 끝! (하지만 단점은 복사해서 수정할 수 가 없다. )

파일 - 옵션 - 빠른 실행 도구모음(많이사용하는 명령) - 전체화면 인쇄 미리보기(선택/추가)

미리보기 눌러서 인쇄하기전에 볼 수 있다. (여백을 눌러서 여백을 조정할 수 있다.)

보기-페이지 레이아웃에서 머리글, 바닥글을 설정할 수 있다.

수식을 입력하려면?

  1. ‘수식’을 입력할 셀을 선택한다
  2. ’=’을 입력한다.
  3. ‘식’을 입력한다.

수식의 기본형식 : 자료1(셀의 주소) + 연산자 + 자료2(셀의 주소) -> 그냥 클릭해도 되고 쳐서 줘도 된다. 연산자의 종류는 C언어와 같다. %는 백분율. ()로 우선순위를 주자!

홈 표시형식에서 콤마나 원가 표시등등 넣어줄 수 있다.

많은 숫자들이 합을 구하려면?

  1. 합을 구할 셀을 선택한다
  2. 도구 모음중 ‘자동합계’를 선택한다.

SUM(B7:E7) B7~E7까지 합을 구하겠다. 불연속적인 경우에는 그냥 컨트롤 누르고 선택하면 된다. SUM(첫번째 숫자 : 마지막 숫자,숫자 1, 숫자2) 여러합계를 동시계산하려면 그냥 다 블록처리하고 자동계산 누르면 된다.

수식도 마찬가지로 행은 같고 열만 다른 경우라면 드래그 복사로 쉽게 구할 수 있다.

열이 1씩 증가할 때나 행이 1씩 증가할 때 자동계산도 그냥 Ctrl+마우스로 늘려주면 된다.(좌에서 우로 복사, 위에서 아래로 복사)(혹시 모르니 두 개정도 써보고 복사를 하자. )

###값이 나왔을 때 크기에 딱 맞춰주려면 원하는 열을 선택해서 더블클릭을 하면 된다.

#DIV/0!이 나 올때는 수식을 보자. 상대주소 : 연속채우기에 의해 주소값이 증가 절대주소 : 연속채우기에 의해서도 주소값이 불변 ($C$5,$A$7) -> F4를 이용한다. F4를 연속해서 눌러보면 형태가 달라진다.

-> 어떤 부분에서 절대주소를 써야하는지 알아봐야한다.

십자가를 더블 클릭하면 그냥 바로 끝까지 알아서 더해져서 나온다.

시트 선택 -> 그냥 시트에 가서 오른쪽 클릭하면 된다. 시트를 복사하고 싶다면 그냥 Ctrl키를 누른 후 드래그하면 된다. 시트 이름의 변경 -> 그냥 시트에 가서 더블 클릭하면 된다.

시트 간의 자료를 이용한 계산 *그냥 다른 시트에 가서 클릭하면 된다. (=시트이름!셀의 주소) 이렇게 나온다. 연산자는 그 시트에서 바로 쓰면 된다.

차트는 그냥 블록 설정한다음에 차트 삽입을 해주면 된다 차트마법사로 범례나 데이터 레이블이나 다 선택할 수 있다.

수정하고 싶다면 오른쪽 마우스 클릭해서 서식으로 들어가 수정해주면 된다. (거의 다)

표에서 필요한 자료들을 선택해서 차트를 만들어 줄 수 있다. (Ctrl키를 이용하여 각각 선택)

평균만 따로 꺾은 선형으로 한다던가 따로 해줄 수 있다.

데이터 레이블을 설정해주면 차트 위에 데이터가 나온다.

셀에 있는 자료가 바뀌면 차트에 있는 자료도 바뀐다.

원형차트를 했을 때 조각을 떨어지도록 할 수 있다. 두 번 클릭한다음에 그냥 떨어지게 된다.

막대그래프에 그림을 넣는 방법 dgl.microsoft.com에 가서 그림을 다운 받는다. 데이터 계열 서식->무늬->채우기효과->그림->그림선택

도형 그리기도 그냥 그리기도구에서 선택하고 오른쪽 마우스를 클릭해서 설정을 주면 된다.

table은 순서가 뒤바뀌어도 상관없고 코드에 따라 값이 있다. 한 주제가 있으면 그거에 대한 데이터가 있다. 필드, 레코드 데이터베이스랑 똑같네?

도구모음 데이터 관리쪽으로 가면 다 알 수 있다.

Sort : Field들중 원하는 것을 기준으로 정렬 (오름차순, 내림차순) 범위지정은 자료들중 아무곳에서 가서 클릭하면 된다. (표 전체가 블록처리 됌) -> 혹시나 이상이 있다면 직접 블록을 따로 잡아줘야한다.

둘 째 기준 -> 만약에 동명으로 나왔을 때 어떻게 할 것인지. 만약 돌려놓고 싶다면 편집에서 정렬 취소를 눌러야 한다.

도구 -> 옵션 -> 사용자 정의 목록 여기다가 원하는 순위를 따로 넣으면 된다. (일병 상병 병장 중사 상사 대위를 쓰면 된다. 우리가 정의를 내린 것) 데이터 -> 정렬 -> 옵션에 가서 아까 썼던 정의를 적용시키면 된다.

자료 관리는 테이블의 형식일 때 가능하다.

Filter : 조건에 맞는 자료들을 뽑아내는 기능 (항목 ‘관계’ 값) 데이터메뉴 -> 필터 -> 자동필터를 하면 조그만 화살표가 생긴다. 필터를 만들고 싶다면 사용자 정의 자동 필터를 누르면 된다. 그냥 클릭하면 된다.

ex> 등급이 3등급 이상 : 등급 >= “3급”, 남자만 검색 : 성별 = “남”, 김씨는 몇 명? : 성명 = “김*”

ex> 등급이 3급이상 이거나 이씨인 사람 : 등급 >= “3급” AND 성명 = “이*”

TOP10에 보면 상위 20%이내 이런 식으로 정의할 수 있다.

고급필터 구매자 금액 김미래 >=1000000 이렇게 4칸에 써놓고 고급필터로 들어간 후에 조건 범위에 아까 쓴 4칸을 넣으면 된다.

아래에 조건을 쓰면 됌.

Sub Total(부분 합) 원하는 항목들의 합, 평균등을 구한다. *반드시 자료를 정렬를 한 후에 사용한다. 열 너비는 수동으로 조절해야된다.

데이터-> 부분 합 -> 사용할 함수를 정하고 부분 합 계산 항목을 조정하면 된다. (그에 따른 정렬을 먼저 하고 난 뒤에 부분 합을 설정한다.)

피벗테이블 : 테이블 형태의 데이터베이스 자료를 가로와 세로 2개의 키를 사용하는 일반적인 표의 형태로 집계, 분류하는 방법. (테이블인데 자동필터 분위기가 나는 것)

데이터 -> 피벗테이블 -> 그냥 드래그해서 끌어다 놓으면 된다. 정말 쉬움. 필드에서 오른쪽 마우스를 클릭해 쉽게 수정할 수 있다.

함수 : 복잡한 수식이 필요할 때 수식을 쓰려는 셀을 선택한 후 “함수 마법사”도구를 선택 -> 다양ㅇ한 함수들의 목록이 보여진다. -> 이게 함수마다 사용하는 방법이 다르다. ROUND(D3*125%,-2) 두 번째자리에서 반올림 등등… =IF (A1>B1 , “합격”,”불합격”)

문자열 함수 -> LEFT(C3,2) -> 왼쪽에서 문자 2개 추출. (RIGHT이나 MID도 마찬가지)

셀연결함수 H3 & “원” 을쓰면 뒤에 다 원이 써진다. (첫번째 셀에 입력하면 뒤에 것들이 연결되어있어서 다 바뀐다. )

IF 함수 =IF(H4>=70,”합격”,”불합격”)

만약 공백을 표시하려면 “ “으로 그냥 스페이스바를 해주면 된다.

=IF(H4>=70,”합격”,IF(H4>=70,”합격”,”불합격”))

VLOOKUP함수 중첩IF문을 여러 번 써야할 경우 테이블에서 찾아온다.

  1. 원하는 자료를 찾을 때 사용할 키
  2. 테이블의 전체 범위 (모두 절대 주소를 써야한다.)
  3. 테이블안에서 몇 번째 열의 자료인지…

  4. 만약에 없을 때? 그냥 0을 넣어주면 된다.

  5. LOOKUP VALUE
  6. LOOKUP VALUE가 반드시 첫번 째에 올 수 있도록. (ex>번호)

VLOOKUP(B10,$B$15:$F$20,3,0)

* reference

엑셀 오현주 From Youtube