빅데이터 관련 프로그래밍/excel

1. 기술통계
  - 세팅 : 파일 - 옵션 - 추가기능 - 분석도구 - Execel추가기능 : 이동 - 분석도구
  - 사용 : [ 데이터-분석-데이터분석 ] -> 기술통계법 -> 입력/출력범위 선택후 [요약통계량] 체크


2. 부분합
  - 세팅 : 기준열 이름을 선택한 상태에서, 기준열을 오름/내림차순 정렬으로 그룹화(선행작업)
  - 사용 : 셀하나만 선택한 상태에서, [데이터-개요-부분합] -> 그룹화할 항목에 기준열 선택후, 함수/계산항목 선택
  - 이후 윤곽선 조절 및 ctrl+8로 조절


3. 피벗테이블-차트
  - 세팅 : 셀 하나만 선택한 상태에서, [삽입 - 표 - 피벗테이블]알아서 범위가 잡힌다.
  - 사용 : 보통은 [새 워크시트]에 생성하도록 한다. 행/열레이블에 드래그를 통해서 index/columns를 만든다.
       값 레이블에는 함수를 적용할 숫자데이터만 드래그해준다. default는 합계함수가 적용된다.
  - 수정 : [피벗테이블도구 - 분석 - 활성필드 - 필드설정]에서 집계함수와 표시형식을 수정할 수 있다.
            [피벗테이블도구 - 디자인 - 활성필드 - 필드설정]에서 부분합/총합계/보고서 레이아웃을 수정할 수 있다.
  - 차트 : [피벗테이블도구 - 분석- 도구 - 피벗차트]를 선택해서 원하는 차트를 고르면 된다-> 필터도 적용되어있다.

간단하게 기술통계 구하기

강의10_예제.xlsx

기술통계를 구하려면 데이터 분석도구를 추가해야한다.

1. 파일->  옵션 -> 추가기능 -> 관리 : EXCEL추가기능 : 이동 -> 분석도구 를 클릭하면, 데이터탭에 [분석- 데이터분석]이 추가된다.

2. 데이터분석을 눌러서 [기술통계법]을 클릭하면 된다.

image
image
image
image
image


기술 통계의 [입력범위]는 열이름을 포함하지않고, 통계를 내고 싶은 숫자데이터만 클릭을 한다.
출력옵션은, 현재시트 중 선택하든지, 새로운 워크시트에 추가하면된다.
통계량을 확인하기 위해선, [요약 통계량]을 체크해줘야한다.
image
image



부분합으로 그룹집계보기

부분합은,
1. 선행작업으로 그룹화할 열을 먼저 오름OR내림차순으로 정렬 한다.
2. [데이터 - 윤곽선탭 - 부분합]을 적용한다.
3. 순서대로 그룹화할 열/ 집계함수/ 연산할데이터를 선택해준다.
4. 나타나는 왼쪽의 윤곽선은 Ctrl+8로 조정할 수 있다.

image
image


부분합 실습

1. 그룹화할 열(근무팀)을 정렬해줘야한다.(묶어져있어야한다)
- 열이름을 클릭한 상태에서, 데이터- 정렬및 필터탭- 오름차순을 선택해준다.

image

2.셀 하나만 선택해놓고,  데이터- 윤곽선탭(개요 탭) - 부분합을 선택한 뒤, 방금 정렬했던 열을 그룹화할 항목(그룹화 기준열)으로 넣어주고, 함수, 계산항목도 넣어준다.
(셀 하나만 선택해도, 열이름 포함 전체데이터가 잡힌다)
image

근무팀을 기준열로 그룹화한 뒤, 기본급/식대/교통비의 합계를 요약으로 볼 수 있다.
image

윤곽선을 통해 번호를 선택하면 , 총합계/ 그룹별 합계 / 전체데이터와 합계의 요약을 볼 수 있다.
image
image
image


윤곽선을 숨기고 싶다면 [ctrl+8]을 통해 조절하면 된다.
image



피벗테이블로 그룹 집계표 만들기

1. [삽입] - [피벗테이블]에 위치해있다.  클릭하여 피벗테이블을 만들면, 필드를 배치하도록 해준다.

2. 각 열들을 필드라 부르는데, 필요한 곳에 드래그를 통해서 내려놓으면 된다.
- 반대로 드래그하면 제거가 된다.

3. 피벗테이블을 만들면, 자동필터가 자동으로 적용된다.

4. 피벗테이블은 3가지를 수정할 수 있다. 집계함수 / 표시형식 / 레이아웃 수정

image

image


실습 - 피벗테이블

셀하나를 누른상태에서, [삽입]-[표]-[피벗테이블]을 만들면, 알아서 범위를 잡아준다.
새 워크시트에 만드는 것이 일반적이다.

image

오른쪽의 인터페이스를 통해 필드목록을 확인하고, 드래그하면된다.

image


행/열 레이블에는, 그룹화의 기준이 될 필드를 / 값 레이블에는 연산이 가능한 필드가 내려와야한다.
제거하고 싶다면 반대로 드래그해주면 된다.
집계함수가 default로 합계가 지정되어있다. 나중에 수정할 수 있다.
image

기본급을 2개 내려놔도 괜찮다.
image


하나는 합계:기본급이 아니라 평균:기본급으로 집계함수를 하나만 바꾸어보자.

바꿀 집계함수의 셀을 하나 클릭한 뒤, 피벗테이블의 [분석/디자인] 중 [분석]탭에서 [ 활성필드 - 필드설정]을 클릭한다.

image


[ 요약기준]탭에서 합계를 [평균]으로 바꾸면 된다.
image
image


이제 보이는 방식을 바꿔주기 위해서, 집계함수를 바꿔주는 것과 동일하게
[피벗테이블-분석- 필드설정] - [표시형식]을 눌러서 수정해준다.
값을 보기 좋게 하기 위해서, 회계를 선택하고, 소수점자리를 0으로 수정해주자.

image
image
image

합계도 동일하게 바꿔주자.


이제 행레이블을 추가해주자. 근무팀아래에 직위를 드래그하여 <근무팀별 직위에 따른> 기본급의 합계/ 평균이 나올 것이다.

image


이제 [피벗테이블도구 - 디자인]탭을 알아보자.

먼저, 부분합 탭에서는 부분합을 표시할 것인지, 위/아래에 표시할 것인지 결정할 수 있다.
image

총합계도 마찬가지다.

보고서 레이아웃에서도, 압축/개요/테이블 형식을 지정할 수 있다.
image


피벗테이블을 피벗차트로 연결해서 집계정보 시각화하기

피벗테이블의 한 데이터 셀 선택 -> [피벗테이블 도구- 분석 - 도구- 피벗차트]를 선택-> 원하는 차트를 선택
image
image
image


피벗차트의 장점은, 차트자체로 필터기능을 가지고 있다는 것이다.
image
image

조건식의 유형

1. 셀조건 : 해당열이름과 그에 대한 조건을 셀에 직접 적어놓는다(데이터범위와 조건선택시 항상 열이름까지 포함시켜준다.)
  (1) 고급필터 : 열이름을 포함한 데이터, 셀조건을 선택해서 새로운 데이터로 필터링할 수 있다.
  (2) D계열 함수 : =DSUM(열이름을 포함한 데이터범위,  "조건에따라 합을 구할 열이름", 열이름 포함 셀조건)이 들어간다.
     =DSUM(A3:G38, "기본급",Criteria)

***셀조건에서 조건에 함수식을 포함할 경우 조건적는 법 : ="조건부호"& 함수식()
---조건연산자는 ""쌍따옴표로 묶고 함수식은 &로 연결해준다.

2. 조건식을 넣는 함수/기능들(데이터 선택시, 열이름 빼고 선택한다)
  (1) IF함수의 첫번째 인자
  (2) 조건부서식 - [수식을 사용하여 서식을 지정할 셀 결정] : =()안에 첫셀(첫행)기준으로 $열만 고정시켜놓고 조건식 작성
     =($E4<1000000)

3. 조건범위와 조건을 나누어 넣는 함수
  (1) COUNTIF 외 ~IF/~IFS함수들 ( 조건범위, "조건")
     =COUNTIF(E4:E38, ">2000000")

4. 다중조건을 이용하는 방법
(1) 셀조건 : 조건을 줄 열이름과 셀조건을 가로배치=AND / 세로배치 = OR연산
(2) 조건식을 넣는 함수/기능들(IF함수, 조건부서식) : =AND( 조건식1, 조건식2) / =OR( , ) / =NOT( , )

5. IFERROR( 계산식, "오류났을때의 표시값")
  - 오류는 나누기0이 되게해서 오류를 낸다.

강의9_예제.xlsx


조건식의 2가지 유형


image
1. 어떠한 기능에다가 조건식을 넣는 유형
2. 어떠한 함수에다가 조건식을 넣는 유형


조건의 형식 3가지


image



실습


셀 조건을 실습해보자. 셀 조건이라는것은 시트속의 셀에 조건을 표현해놓고 이용하는 것이다.
이러한 셀조건과 연동이 가능한 것은 [고급필터] [ D데이터베이스함수]이다.
자동필터를 써서 직접 클릭해서 사용했지만. 이번에는 고급필터를 쓴다.
고급필터는 [데이터 - 정렬 및 필터 - 고급필터 - 다른장소에 복사 ]를 선택하고
목록범위 데이터이름을 포함한 첫행까지 잡아줘야한다.
image

조건범위는 조건에 맞는 데이터이름을 가진 첫행까지 포함하여 셀조건을 선택한다.
image

복사위치는 조건에 맞는 놈들로 구성된 데이터를 만들 첫셀을 지정해준다.
image

2개의 셀조건을 만족시키는 새로운 데이터를 뽑아냈다.
image



고급필터 뿐만 아니라 D로 시작하는 데이터베이스 함수도 셀조건을 이용한다.
그중 DSUM()함수는 인자로서,
=DSUM(열이름을 포함한 데이터범위,  "조건에따라 합을 구할 열이름", 열이름 포함 셀조건)이 들어간다.
image

image



IF함수에서는 조건식이 첫번재로 인자로 들어간다. 한 셀에 대해 IF함수를 연습해보자.
=IF(E18>1000000, "100만원초과", "100만원 이하")
image



조건부서식을 통해 조건식을 적을 때는, 먼저 열이름 제외 데이터를 선택하고 나서 [홈-조건부서식-새규칙]을 건다
image

그리고 [수식을 사용하여 서식을 지정할 셀 결정]을 눌러서,
=()안에 첫번째 인자에, 첫번째 셀(첫행)을 기준으로 $열 고정시킨다음, 조건을 걸어줬었다.
기본급 열이 200만원 이상인 것만 조건부서식을 활용해 주황색 채우기를 해주자.
=( $E4 >= 2000000)
image



~IF, ~IFS함수에서 조건식을 사용해보자.
COUNTIF( 조건범위 , "조건내용")
=COUNTIF(E4:E38, ">=2000000")
image


여기까지 정리해보면)

1. 셀조건셀 안에 조건식이 들어있는 것이며 (고급필터 OR DSUM()함수)
2. IF함수조건부서식함수안에 1개의 인자로 조건식이 들어있다.
3. COUNTIF2개의 인자(기준 + 조건)를 합쳐서 조건식을 완성시킨다.

각 조건형식의 특징들

image



다중 조건을 적용하는 방법

image


실습

셀조건의 셀을 세로로 배치하여 --> OR연산으로 조건식을 사용해보자.

셀조건으로 기술팀 OR 영업팀을 고급필터로 걸러서 새로운 데이터를 만들어보자.
목록범위는 열이름포함 전체데이터 / 조건범위는 세로로 배치된 셀조건 / 복사위치는 새로운 위치에 넣어주자.
image


셀조건안에 함수를 포함하는 조건수식을 만들어보자.
기본급이 평균보다 것을 셀조건으로 만들어보자. 가로로 배치되어있으니, 고급필터에서 AND연산으로 작용될 것이다.
>=AVERAGE(E4:E38)
image

확인결과 값이 이상하게 나온다. 평균이 160만원임에도 불구하고, 160만원 이상인 데이터들이 안나왔다.

image

***셀조건에서 함수가 포함된 조건식을 넣을 때는  = ">=" & 함수( )로 작성해줘야한다.
조건연산자는 ""쌍따옴표로 묶고 함수는 &로 연결해준다.
=">=" & AVERAGE(E4:E38)
image

다시 고급필터를 적용할 때는, 새로운영역에 해주자.
(나 같은 경우에는, SHIFT+자동채우기핸들 위로 올리기로 해당 구역만 삭제했다)
image


함수속 조건식(IF/조건부서식)에 AND나 OR연산자를 사용해보자.
여기서는 조건부서식에다가  기존조건을 편집해서
=()가 아닌 =AND( 조건1, 조건2)의 방식으로 열고정+첫행으로 조건식을 작성해보자.
=AND( $E4>= 2000000, $E4<3000000 )

image

300만원 이상의 값들은 주황색 채우기가 없어졌다.
image


이제 IF함수안에서 다중조건식을 만들어보자.
=IF(AND(E17>1000000, E17<=2000000), "100만원이상~200만원이하", "---")
image



조건에 따라 참/거짓에 따라 다른값을 지정할 수 있는 함수 2가지

IF함수는 많이 사용해왔다.
IFERROR함수는 TRUE/FALSE와 관계없이
오류발생안하면 그 값을 반환 / 오류가 생길시 특정 값
image


IFERROR 함수 실습

IFEEROR()함수에 일부러 에러가 나도록 식을 만들어보자. 숫자를 0으로 나게 해서 오류를 발생시켜보자.
기본급을 교통비로 나누고, 0으로 나누어서 오류가 나면, "에러"를 표시하자

=IFERROR( E4/G4, "에러")
image

데이터정리 8가지 방법


1. 필터로 빈데이터 수정(ctrl+enter) or 시트행 삭제
2. 필터로 비유효데이터 수정or 삭제
3. 필터로 데이터 그룹화 열 만들어주기

4. if중첩 or vlookup으로 데이터 그룹화 열 만들어주기
- if함수의 3번째 인자인 거짓일때의 입력값에 다시 if를중첩해서 사용하여, 데이터를 그룹화 할 수 있다
- VLookup의 인자로는 ( 평가할 셀, { 구간에 대한 배열;들을 오름차순 }, 2, 1) 순으로 온다. 이 때, 2, 1은 고정된 값이라 생각하면된다.
   =VLOOKUP(E2, { 0,"C";1500000,"B";2500000,"A" }, 2, 1)
- VLookup함수로 조회할 때는, 인자의 순서가 아래와 같다.
   =VLookup ( 조회할 셀, 조회할 셀이 1열에 포함된 조회데이터 범위, 조회데이터의 추출열 번호, 0)

5. 텍스트나누기로 구분기호가 명확한 데이터 나누기

6. 텍스트 추출하기
구분자 직전까지 추출 : Left( 대상 셀,  Find("구분자", 대상 셀) -1)
구분자 뒷부분을 추출 : Right(대상 셀,  Len(대상셀) - Find("구분자", 대상 셀))
*MID함수는 (대상셀, 시작위치, 갯수)
*find만 대상셀이 뒤쪽인자가 되며, 왼쪽에서부터 구분자까지의 문자열 수를 반환한다.

7. 텍스트 변환하기
(1) 연산을 위한) 문자열숫자 -> 숫자 방법 : 텍스트나누기 - 일반 / value()
(2) 연산을 위한) 문자열날짜 -> 숫자 방법 : 텍스트나누기 - 날짜 / datavalue() + 셀서식
(3) 추출을 위한) 숫자 -> 문자열로 : text( 해당셀, "###" ) - "#,###" "####.#"
(4) 추출을 위한) 날짜 -> 문자열로 : text( 해당셀, "yyyy-mm-dd" )

8. 중간셀에 참조할 셀이름 입력하여, 중간셀 이용하기 - INDIRECT함수
- INDIRECT( 참조할 셀이름이 적힌 중간셀


사용자지정 표시형식

#,##0, "천원"
yyyy"년" mm"월" dd"일" aaaa
#,###;(#,###);"-";@
;;;@
;;;


조건부 서식에서 [ 수식을 지정하여 서식을 지정할 셀 결정 ] 을 이용해, 행단위로 셀 채우기

데이터의 범위를 선택한 뒤,  =( ) 안에  조건이 될 기준열은 고정 $E  + 첫번째 행을 기준으로 작성으로 조건 수식을 만든다.

데이터 읽기 좋게 만들기

데이터의 표시형식을 바꾸어서 읽기 좋게 해줄 수 있다.
홈- [표시형식] 탭을 활용하거나   [셀서식](CTRL+1)로 빠르게 표시형식 지정이 가능하다.
- 숫자에 천단위 구분기호는  [숫자 - 1000단위 구분 기호 사용]을 체크한다.

image


실습

숫자데이터에 [홈 - 표시형식] 의 다운드롭을 입혀서 [회계]를 선택하여 금액으로 표시해보자.

image
image


이제 2014-04-02의 날자데이터를 [ 표시형식 - 자세한 날짜]로 바꿔보자. 요일까지 표시가 된다.

image
image

이제 셀서식을 이용해서 날짜의 표시형식의 여러양식들을 보자.
image



셀서식 - 사용자 지정 형식 TOP4

image

1번에서 마지막 aaaa는 요일을 의미한다.
2번에서는 마지막 천단위를 끊어주는데, 마지막은 0을 넣어서 [ 천 이하의 단위는 무시]하겠다는 의미이다.
3번을 해석해보면 ) 양수는 천단위 끊어서 ; 음수는 -없이 괄호안에, 0은 -로 ; 텍스트는 그대로 사용한다는 의미이다.
          그 밑에껀) 양수는 천단위 끊어서; 음수는 괄호안에 ; 0은 비워두고 ; 텍스트는 그대로
4번을 해석해보면 ) 숫자를 무시하고 텍스트만 그대로 표시
          그 밑에껀) 양수는 천단위 끊어서 ; 음수는 -를 달고 천단위로 끊어서;  0은 -로 ;  텍스트는 무시(숫자만)



실습

사용자 지정 형식을 이용해서 기본급 데이터를 ,  천단위씩 끊어서 마지막 "천원"으로 표시되도록 바꾸어보자.
보기를 보면서 확인해본다.
#,##0, "천원"
image
image


날자를 사용자지정 형식으로 바꿔보자.
image
image


***만약 a가 3면,  요일을 한글자로 표시한다.
image


이번에는 교통비를 양수/음수/0/테스트의 형식을 지정해주자.
image
image
image


이상애텡서 텍스트를 안보이게 하려면, 마지막 텍스트 형식란을 비워버린다.
#,###; (#,###);"-";
image


다시 이상태에서, 텍스트란만 남겨보자.

;;;@
image
image


만약  표시형식을 다 비워두면 어떤 결과가 나올까?
표시형식에서 보는 방법을 안보이는 것으로 선택한 것이다. 실제데이터는 존재한다.
이러한 방식은 셀을 가려놓을 때 사용하자.

;;;
image
image




조건부 서식을 이용해서 데이터를 구분해서보자.


조건부 서식을 적용할 셀(들) 선택 => [홈 - 조건부서식 - 규칙관리 - 새규칙 ]을 통해 적용할 서식을 정해주자.
여기서 [다음을 포함하는 셀만 서식지정]을 선택하여 조건을 작성하고 특정서식을 지정해준다.
(조건부 서식 주기, 수정하기, 삭제하기 는 모두 해당 데이터들을 선택한 상태에서 가능하다)


image


행 단위로 구분해서 보이게 하는 방법은, 전체데이터를 선택한 다음, [홈 - 조건부서식 - 규칙관리 - 새규칙]에서 [수식을 사용하여 서식을 지정할 셀 결정]을 사용하자.
이 규칙이 가장 유용한 규칙이다. 이 때, 수식을 이용하여 조건을 만들어 줄 때,  선택한 데이터의 첫 행(1개의 열이면 첫 셀)을 기준으로 조건식을 작성한다.
그리고 기준이 되는 열에는 $E2 와 같이 열이 고정이 되어야한다(열만!)

image


지정한 조건부수식을 수정하려면, 마찬가지로[ 홈- 조건부서식 - 규칙관리]로 가서 [규칙 편집]을 선택하면 된다.
이 때, 상단에 위치한 규칙이 우선순위가 높다.


image


실습

홈- 조건부서식 - 규칙관리 - 새규칙 - [다음을 포함하는 셀만 서식 지정]을 이용해,
기본급이 200만원 이상인 셀만 노란색 칠을 해주자.

image
image
image


다음으로 기본급에 새규칙을 추가해서. [수식을 사용하여 서식을 지정할 셀 결정] 을 선택한 뒤, 수식을 직접 적어주자.
이 때, 수식을 적용하는 방식은 =( ) 안에 넣어준다. 그리고 첫 행(셀)을 기준으로 작성해야한다.( 그 이후의 행들이 알아서 지정된다)
이 때, 첫 행(셀)은 E4이다.
1열만 적용시킬 때는 안해줘도 되는데, E4의 E앞에 $를 넣어 E열을 고정시키자. 왜냐하면 규칙의 적용대상을 테이블로 확장시킬 경우, 기준이 되는 E열을 고정 시켜야한다.

=($E4 < 1000000)

image

image



이제 선택된 테이블 단위의 데이터를 행단위로 조건부서식을 주고싶다.  이 때, 열 단위로 준 조건부서식의 적용대상을 넓히면 된다.
이 때, 조건의 기준이 되는 E열은 $E로 고정되어있어야 한다( 안되어있으면, 직접 가서 E4 -> $E4로 바꿔야 제대로 나옴)
image
위 사진은 $E4가 아니라 E4로 작성하였다. 하나의 열이면 상관없지만, 적용 대상을 테이블단위로 넓히면 에러가 난다.

image
image


이제, 기본급이 200만원 이상인 행만 조건부서식을 걸어 색칠하자.
항상, =( ) 안에  기준열은 고정 $E  + 첫번째 행을 기준으로 작성 4 으로 조건 수식을 만든다.
image
image

데이터 추출하기 2

데이터를 추출할 때, 구분자가 명확한 경우에는 앞서 사용한 텍스트나누기를 사용하면 되지만,
구분자가 명확하지 않은 경우는 Left, Right, Mid의 추출함수들과 find함수, len함수를 중첩해서 쓴다.
***find함수는 대상이 되는 셀이 2번째 인자로 온다.그리고 반환결과는 왼쪽에서부터 몇번째 자리수인지를 알려준다.
find에다가 구분자를 찾을 문자열로 넣으면, 구분자까지를 포함해서 추출하니, -1 +1 등을 해주면 된다.
***만약 구분자 뒷부분을 가져오고 싶다면 Right함수에다가 Len함수를 이용해 전체길이-(구분자까지길이)로 구하면된다.

정리)
구분자 직전까지 추출 : Left( 대상 셀,  Find("구분자", 대상 셀) -1)
구분자 뒷부분을 추출 : Right(대상 셀,  Len(대상셀) - FInd("구분자", 대상 셀))

image


실습 - 데이터 추출하기

image

위 데이터에서 출생년도를 구분해보자.
=LEFT(A2, 2)

image

출생월 을 가져오고 싶다면, 가운데이므로, mid함수를 써야한다.
=MID(A2, 3, 2)
image

생년월일을 추출해보자. 구분자 앞까지만 가져오면 되므로
left를 이용해서 왼쪽부터 6개를 뽑아와도 된다.(=LEFT(A2, 6)). 하지만 구분자를 이용하기 위해서
 left함수find함수를 충첩해서 사용해보자.
=LEFT(A2, FIND("-",A2)-1)

image

주민번호 뒷자리를 추출해보자. 뒤에서부터 7자리를 가져오면 되지만(=RIGHT(A2, 7))
뒤에서 부터 잘라와야하기 때문에, right 함수에다가  전체길이 -구분자까지의 길이를 가져오면 된다.
=RIGHT(A2, LEN(A2) - FIND("-",A2))

image
image


[텍스트 나누기]를 통해 데이터 변환하기

image

문자열 1을 ['1으로 입력]---> 숫자1로 바꾸는 것은
데이터-데이터나누기-[텍스트 나누기]를 통해 2단계에서 [공백]으로  설정, 3단계에서 [일반]을 누르면 문자열을 숫자로 바꿀 수 있다.
image
image
image
image


데이터변환 함수 정리

image

*숫자 형식은 문자열로 바꿀 때 꼭 들어가는 서식이다.
*datavalue는 활용할 수 없는 날짜 정보를 주므로, date나 month, day함수를 중첩해서 사용한다.


cf)셀서식은 데이터자체가 아니라 보이는 모양만 바꿔주는 것이다.
cf) 날짜를 계산할 때는, datevalue()를 쓰지만,
    텍스트로 날짜들을 구분(yyyy-mm-dd)하고 싶을 땐, 날자->문자열 + 숫자형식으로 바뀐 뒤->문자추출함수를 사용하는 것이 일반적이다.
cf) 실제 데이터의 양식을 보고 싶다면 [Ctrl + `]를 이용해서 확인한다.


실습


문자열로 적힌 숫자들을, 연산하기 위해서는 숫자데이터로 바꾸어야한다.

먼저 문자열로 적힌 숫자를 ---> 숫자로 바꾸는데, [텍스트나누기-공백-일반 ]을 활용해보자.

image
image


이제 똑같은 데이터를 변환함수를 이용해서 문자<-->숫자 데이터를 변환해보자.
문자열 숫자--> 숫자로 바꾸는 것은 value()함수.
image


이번에는 숫자데이터를 -> 문자열로 바꾸어보자.
주민번호나 생년월일, 전화번호로 같은 숫자정보들에 [문자 추출함수 len,left,mid,right,find]함수들을 사용하기 위해서 문자열로 바꾼다.
이 때 사용하는 함수는 text함수이다. 2번째 인자 숫자형식에다가는 눈에보이는대로의 형식을 #을 이용해 넣어준다. "###" 등을 넣을 수 있다.image
image


2000이 2,000으로 표시해야한다면, text()함수의 숫자형식에 "#,###" 으로 넣어준다.

image


3000.1 같은 경우 "####.#" 으로 입력해줘야 소수점이 표시된다.
image


날짜처럼 보이는 문자열을 ---> 날짜데이터로 변환하는 것이 datevalue()함수다.
하지만 날짜가 아닌 값(1990.1.1부터 계산한 날 수)을 반환하기 때문에, 셀서식[ctrl+1]로 날짜 서식을 줘야한다.
image
image
image


[텍스트나누기]를 이용해서 문자열 --> 날짜로 바꿔보자.
[텍스트나누기] - [공백] - [날짜]를 선택해서 바꿔주자.
image
image


이번에는 날짜 ---> 문자열로 바꿔보자.
숫자 ---> 문자열로 바꿔 추출함수를 사용할 때도, text()함수를 썼으나, 날짜도 동일하다.
숫자형식만 달라진다.( 숫자는 "#", 날짜 "yyyy-mm-dd")
=TEXT(E17, "yyyy-mm-dd")
image


중간 셀에 있는  [ 셀이름형식의 문자열 ] --->  [ 참조셀로서 값 반환]해주는 INDIRECT함수( 중간셀 )를 사용해보자
예를 들어,  중간 셀인, 연초록 J3셀에 있는 "문자열(H2)"를 실제로 찾아가서 참조하고 싶을 땐,
INDIRECT( J3)를 사용하면 실제 H2값이 참조되어 반환된다.
INDIRECT(  중간셀  )   --- 중간셀에 있는 문자열을--> 참조 셀로 읽어온다. --- 참조셀 값을 반환

=INDIRECT(J3) + 200

image
image
image

참조할 셀이름을 H5로 바꾸면, 그 값을 읽어온다.
image

강의7_예제.xlsx


데이터 정리하기 7가지

image


if함수와 VLookup 으로 그룹 데이터 만들기2 ( 1번째는 필터로 나누어, 새로운 열에 그룹을 만들어줬음.)

image

- if함수의 인자로는  (조건, 참일 때의 입력값, 거짓일 때의 입력값)이다.
- if함수의 3번째 인자인 거짓일때의 입력값에 다시 if를중첩해서 사용하여, 데이터를 그룹화 할 수 있다.



image

- VLookup의 인자로는 ( 평가할 셀, { 구간에 대한 배열을 오름차순 }, 2, 1) 순으로 온다. 이 때, 2, 1은 고정된 값이라 생각하면된다.


VLookup함수에 대해서


원래 VLookup함수는 대표적인 조회함수 이다.
image
조회시 인자의 순서는 (조회할 셀(값), 조회데이터의 범위, 조회데이터의 특정 열 번호, 0)을 입력한다.
마지막인자가 1이 되면, 유사한 값을 찾을 때 주는 인자인다. 보통은 0으로서 정확한 값을 찾아온다.
***VLookup함수의 규칙으로서 조회데이터의 첫번째 열이 자동으로 기준열이 된다.


실습 1 -  if문 또는 vlookup으로 데이터 그룹화 하기

if함수를 써서 데이터를 특정열에 그룹화해주자.
기본급을 구간에 따라서 A,B,C로 표시되도록 중복IF문을 써보자.
=IF(E2<= 1500000, "C", IF( E2 <= 2500000, "B", "A"))

image


VLookup을 사용해서 똑같이 그룹화를 해주자.
- 여기서는 특정 데이터구간에서 조회하는 것이 아니므로,
  {} 대괄호를 이용하여 구간을 오름차순으로 정리하되, 세미콜론(;)으로 각 구간을 직접나누고,
마지막 인자 2개에는 2, 1을 넣어줘야한다.
=VLOOKUP(E2, { 0,"C";1500000,"B";2500000,"A" }, 2, 1)

image

확인결과 if문을 중첩해서 만든 것과 동일하게 등급을 확인할 수 있다.
image


if문을 많이 쓰지만, vlookup이 더 오류가 적다.


실습2 - vlookup을 통해 조회하기

교육성적표 중에, 유지범이라는 사람이 무슨팀에 속해있는지 vlookup함수로 조회해보자.
image


VLookup함수로 조회할 때는, 인자의 순서가 아래와 같다.
- VLookup ( 조회할 셀, 조회할셀이 1열인 조회데이터 범위, 조회데이터의 추출열 번호, 0)
그러므로 조회할데이터는 유지범이 1열로 구성되어있어야한다.
추출열은 조회데이터 범위에서 1,2,3,4 순으로 세어야한다. 팀명은 2번째 열에 있다.

image
image


데이터- 데이터도구- 중복된 항목제거 사용하기

image


실습3- 중복된 항목제거

아래 보이는 것과 같이 강상택 과장의 데이터가 중복되어있다.

image


먼저, 중복된 항목을 제거할 데이터범위를 지정해준다.
그리고 데이터- 데이터도구- [중복된 항목 제거]를 클릭한다.
image


중복되는 기준을 선택할 수 있다. 이 중에 이름, 근무팀, 직위만 검사항목으로 지정해준다.
image
image

하나의 데이터만 남게 된다.
image


데이터 - 데이터도구 - [텍스트 나누기]를 사용해 데이터 나누기

image

텍스트나누기를 선택한 뒤,
1번째 단계에서는 구분기호로 분리됨을 선택해준다.
2번째 단계에선 공백을 선택해서, 공백기준으로 데이터를 나눈다.
3번째 단계에선, 일반적으로 [일반]을 선택하면 된다. 만약 숫자데이터도 텍스트로 받아오고 싶다면 [텍스트]를 선택하면 된다.


실습4 - 텍스트나누기(데이터나누기) - 구분기호가 명확할 때,

공백으로 나누어진 시/군/구 주소 데이터를 나누어보자.

데이터범위를 지정한 뒤,  데이터-데이터도구-[텍스트나누기]를 선택하자.

image
image
image
image

sql와 엑셀 필터

image

SQL의 Select / From / Where 절 중, Where절에 해당하는 것이, 엑셀의 필터이다.

필터를 이용해서, 자료를 보는 것만이 아니라, 데이터를 골라낼 수 있어야한다.


자동필터 - 데이터 골라보기

자동필터는 홈탭 > 편집캡션 > 정렬 및 필터 > 필터  / 또는 /  데이터탭 > 필터에 위치해있고, 단축키는 [Ctrl + shift + L ]이다.
필터를 달 데이터에 속하는 하나의 셀을 클릭한 상태에서, 적용시켜야한다.
image
image

자동필터를 적용하면, 첫 행에, 역삼각형의 필터가 적용되고, 가려볼 데이터를 선택한 뒤 확인을 누르면 해당 데이터만 볼 수 있다.

image


실습 - 필터로 데이터 골라보기

필터를 달고, 직위 열의 역삼각형을 눌러보자. 해당열의 중복없는 데이터를 관찰할 수 있다.

image

모두선택을 체크하여 풀고 ,  과장만 선택해서 데이터를 확인해보자.

image

필터링 하고 있는 열에는, 역삼각형이 --> 와이파이같은 모양의 아이콘으로 바껴있다.
image

다시 직위 필터에서, 필터를 해제하자.
image


이번에는 교통비의 필터를 보자. 빈셀은 (필드 값 없음)으로 뜬다.
image


이번에는 숫자로만 구성된 기본급 필터를 열고, [숫자필터]를 알아보자. 가장 많이 쓰는 것은 [해당범위] 이다.

image


숫자필터 > 해당범위를 이용해서, 100만원보다 크고, 200만원보다 작은 기본급을 가진 사람만 필터링 해보자.
image
image


숫자필터 > 상위항목 을 이용해서, 상위의  항목(순위) 또는 %(퍼센트)를 알아보자.
image 

먼저, 기본급의 상위 10 보자.
image
image

다음으로, 상위 20%를 보자. 총 데이터가 35개이므로, 상위 20%는 (35*0.2=7) 7개만 보여질 것이다.
image
image


이번에는 기본급에서 특정 셀들만 셀서식-채우기(셀 색)을 입힌 뒤,  필터> 색기준 필터를 이용해보자.
image

색기준 필터에 들어가면, 셀서식으로 입힌 색채우기없음으로 구분이 된다.

image

보고싶은 데이터만 셀 서식을 입혀두고, 원하는 데이터만 가져올 수 있다.

image



자동필터2 - Bad 데이터 바꿔주기

빈 셀비유효값을 정리해보자.

비유효한값의 예로는,, 숫자로만 구성된 열에, 한글로 표시된 것을 가려내는 것이다.
필터에서, 숫자 데이터 중 한글을 직접 보고 bad데이터를 필터링해서 골라낸 다음, 그것을 전체선택하여 ctrl+enter로 일괄 입력해주면 된다.
빈셀의 경우, (필드값없음)을 필터링해서 똑같은 방식으로 해주면 된다.
image


실습

image

필터로, 기본급 열에, bad데이터인 백만원만 골라내보자.
image
image

골라낸 bad데이터를 전체 선택해서, 숫자로 바꾸어 ctrl+enter로 일괄 입력하자
imageimage


교통비라인에서, 빈셀을 필터링하여, 0으로 바꾸어 입력해보자.

imageimageimage



자동필터3 - 계급 데이터 만들기

image

실습을 참고하자.


실습

1. 설계 : 그룹화하고싶은 특정열(기본급 열)을 보고, 나눌 구간을 생각하자.
기본급을   [100미만] /[100~ 200]/  [200~300] / [300 이상]으로 나누어보자.
image

2. 그룹 데이터를 만들 새로운 열을 추가하자. 기본급을 기준으로 나눌 것이기 때문에,  기본급열 옆에 추가하자.( 기본급 그룹 열)
- 열 추가 단축키 : [ctrl + space ]로  열 선택 ->  [ ctrl + (+)] 로 열 추가
image


3. 그룹화대상 열을 숫자필터 > [보다 작음/해당범위/보다 큼]로 필터링하여  각 그룹의 데이터들을 뽑아낸뒤, 추가한 열에 각 그룹명을 적어주자.
***해당범위만으로 보다작음을 사용하고 싶다면, 2개조건 중 1개는 빈칸으로 선택해라.

먼저, 100만원 미만 그룹만 뽑아낸 뒤, 추가열을 전체선택하여 그룹명을 ctrl+enter로 일괄 적어주자.
image
image

100~200/ 200이상 ~300미만/ 300이상 구간도 똑같이 해준다.
image

4. 그룹화한 각 구간은 피벗테이블 등 데이터를 구간별로 집계할 때 이용한다.
- 차후에 다루자.



자동필터 적용후 많이 하는 작업 정리

image

2. 시트행 삭제는 셀만 선택하더라도, 해당 행을 삭제해주는 기능이다.
3. 필터링된,  값만 복사해서 붙여놓고 싶을 때는,  [이동옵션]을 이용해서, [화면에 보이는 셀만]을 선택한 다음 복사해줘야한다.


실습

필터링 된 자료 중에, 시트 행 삭제( 따로 행선택 안하고, 한 셀만 선택하더라도, 해당 행 삭제)를 해보자.
시트행삭제는 [ alt+7]에 추가했었다.  홈탭> 셀> 삭제 > [시트행 삭제]에 위치해있다.
image
100만원 이하인사람만 필터링하자(그룹데이터는 100만원 미만 이라서, 새로 필터링 따로 해줘야한다)
image


삭제하고 싶은 셀들을 선택(첫행 선택하면 안됨)한 다음,  시트행 삭제 [alt+7]을 통해, 행 선택없이 바로 해당 행들을 삭제해보자.
image
image|

100만원 이하의 데이터들(행들)이 다 사라졌다.
image



이제 필터링한 데이터들 중, 해당 행들만 복사해서 다른시트에 [값만 붙여넣기]를 해보자.

직위 중 대리들만 필터링 한 뒤, 모든 데이터들을 선택한 다음, 다른 시트에 옮겨보자.
image
image

새로운 시트에서, [ctrl+ alt+ v]를 이용해 값만 붙혀넣자.
image
이 때, 새로운 시트에서는, 눈치껏 첫행은 남겨두고 붙히자...
image

안전장치를 해보자.
사실 복사한 데이터들의 [행]을 보게 되면, 필터링 되어 띄엄띄엄 되어있다.
이 때, 중간중간 데이터들이 숨어있게 되어, 복붙시, 다른 값들도 가져오는 경우가 있다.
필터링후 복사할 데이터 선택 -> 필터링 된 값을 Ctrl+c로 복사하기 전에, 항상 [이동옵션]-[화면에 보이는 셀만]을 선택 한뒤, -> Ctrl + c로 복사하자.
이동옵션은 홈탭> 편집>찾기및 선택> 이동옵션에 있다. 단축키 [alt+07]에도 지정되어 있다.
imageimage

[ 합/평균/최대최소/카운트/순위 ]
COUNT A=빈칸제외
COUNT = 숫자만
RANK = 조회값, (조회값의 열인) 순위범위 // 내림차순

[ 전체에서 ~ 중 / - 의 합/평균/수 ] : 조건이 하나이니, 앞에서 조건을 단다.
SUMIF = 조건범위, 조건 // 조건범위길이의 SUM할 범위
AVERAGEIF = 조건범위,조건// 조건범위길이의 AVERAGE할 범위
COUNTIF = 조건범위,조건//

[ 전체에서 ~중, ~인 / ~의 합/평균/수 ] : 조건들이 여러개이니 뒤로 밀었다.
SUMIFS = SUM할 범위,// 같은길이의 조건범위1, 조건1 // 조건범위2,조건2...
AVERAGEIFS = 평균구할할 범위,// 같은길이의 조건범위1, 조건1 // 조건범위2,조건2...
COUNTIFS =  조건범위1, 조건1 // 조건범위2,조건2...

데이터 집계함수 Best 5

image_thumb5


실습

강의5_예제.xlsx

image5_thumb
기본급에 대한 합계, 평균은 =SUM, AVERAGE를 완성해주면 된다.

교통비 지급인(명수)을 구할 때는, 교통비를 범위로 하여, COUNTA를 사용해보자.
=COUNTA(G2:G36)
image8_thumb5
COUNTA함수는 교통비가 비어있는 사람은 세지 않는다.(빈셀만 제외)
COUNT함수는 숫자만 세준다. 바로 밑에 칸에 식대를 범위로해서 명수를 세어보자.
없음으로 표시된 문자열은 세어지지 않는다. 35행 중에 오로지 숫자인 것만 센다.
=COUNT(F2:F36)

image11_thumb4
이 때, 숫자가 아닌 [없음]COUNT에서 세지 않는다.


같은 식대범위에서, COUNTA를 사용해보자. 빈셀만 제외하고 문자열 데이터[없음]도 다 센다.
=COUNTA(F2:F36)
image14_thumb5

기본급의 최대값/최소값을 구해보자.
image17_thumb5

순위에 대해 RANK함수를 사용해보자.
먼저 익숙치 않은 함수이므로, 순위 첫번째 셀만 선택한 뒤, =RANK()를 입력하고, [SHIFT+F3]으로 함수인자를 살펴보자.
image_thumb16
첫번째 인자에는 순위를 조회할 값이다. 기본급에 대한 순위를 구할 것이므로, 해당하는 기본급(E2)를 순위조회할 인자로 넣어준다.
두번째 인자에는 첫번째 인자를 포함한 열로서, 순위조회할 범위이다. 기본급 전체를 인자로 넣어준다.
세번째 인자는 Order로서, 0 : 내림차순, 그외 : 오름차순
image_thumb18
빈칸이나, 0을 넣으면 내림차순으로 정렬하게 된다.
image_thumb20
첫번째 행의 기본급의 순위는 24등이다.
image33_thumb4
이제 자동채우기 핸들로 다 채워보자.|
image36_thumb






조건에 따른 데이터 집계함수 3개 이용하기(SUMIF, AVERAGEIF, COUNTIF)

image_thumb30

SUMIF조건범위(C16부터 C19까지), 조건( 2000보다 크면), SUM할 데이터범위(F16부터 F19까지 더할 것이다) 순으로 온다.
즉,  단가 전체에서 ,  2000이상인 것들에 대해,  금액의 합을 구한다.

AVERAGEIF는 SUMIF와 동일한 인자를 가지며, 마지막 통계함수만 다르다. 조건범위, 조건, AVERAGE할 데이터범위
즉, ~범위에서, ~조건을 만족시키는 것들에 대해, ~의 평균을 구한다.

COUNTIF는 2개의 인자로, 조건범위와 조건만 오면 수를 자동으로 세준다.
즉, ~범위에서, ~조건을 만족시키는 갯수를 구한다.

*** 이때, 조건범위와 SUM할 범위의 열의 범위(16부터 19)가 같아야한다.


실습

조건문을 활용하여, 전체팀 중 기술팀을 골라내어 기본급의 합과 평균을 구해보자.
즉, 전체팀을 조건범위, 기술팀을 조건 // sum할범위를 기본급 전체를 선택한다.

조건은 팀이름 전체를 선택하고, 조건은 "문자열"로 "기술팀"을 입력해준다.(공백허용x)

image46_thumb4
조건범위가 팀 전체이므로, 그 행수와 동일한 범위로서, 기본급도 전체를 선택해야한다.
image49_thumb4
조건은 ""쌍따옴표 안에, 넣어줘야한다. 수식일 경우도, " >200 " 형식으로 넣는다.
image52_thumb4

금액을 잘 알아볼 수 있게,  셀 서식[ Ctrl+1]을 눌러서, [회계]를 선택해준다.
image55_thumb
image58_thumb5


평균값도 마찬가지로 AVERAGEIF를 이용해서 구하면 된다.

기본급이 200만원 이상인 직원수를 구해보자.
이 때, 수식조건도 " "쌍따옴표 안에 들어가야한다.
=COUNTIF(E2:E36,">2000000")
image61_thumb5


여러 조건에 따른 데이터 집계

image_thumb43

여러조건에 대해 데이터집계함수를 사용하려면, ~ IFS 함수를 이용하면 된다.
~IF함수들과 인자의 형식이 다르다.

SUMIF는 조건범위,조건// 조건범위길이의 SUM할 데이터범위 였으나,
SUMIFS SUM할 범위,// 같은길이의 조건범위1, 조건1 // 조건범위2,조건2 ... 식으로 나간다.


실습2

전체팀 중 영업팀에서, 기본급 200만원이상인, 기본급의 합을 구하고 싶다.
SUMIFS ( SUM할범위조건범위1, "조건", 조건범위2, "조건2")로 구하면 된다.
=SUMIFS(E2:E36, B2:B36, "영업팀", E2:E36, ">2000000")
image70_thumb5

전체팀 중 영업팀에서, 대리들만 뽑아서, 기본급의 평균을 구해보자.
조건 2개가 있는, 평균이므로, =AVERAGEIFS 를 이용하면 된다.
=AVERAGEIFS(E2:E36, B2:B36, "영업팀", D2:D36, "대리")
image73_thumb6

기술팀에서 기본급 120만원이상인 대리의 수를 구해보자.
COUNTIFS는 조건만 가지므로, 3개의 조건을 입력해주면 된다.
전체팀범위 ,"기술팀" / 기본급범위, ">=1200000", / 직위범위 , "대리"
=COUNTIFS(B2:B36, "기술팀", E2:E36, ">= 1200000", D2:D36,"대리")
image76_thumb4

수식과 함수식의 기본형식

image


강의4_예제.xlsx


수식과 함수식을 복합적으로 적용할 수 있어야한다.
image
아래 표에서 급여총액을 수식으로 구해보자.
직접 수식을 작성해보자.
image

자동채우기 핸들을 이용해보자.
image

급여총액을 함수식으로 구해보자.

image
SUM함수는 범위를 드래그를 통해서 범위를 지정할 수 있다. 콜론(:)이 범위를 나타낸다.
imageimage

함수를 이용할 때는 단축키 [SHIFT +F3]을 이용해서 함수식 입력창을 띄워, 함수 인자(인수)들을 입력시킬 수 있다.
함수 이름을 아는데, 그에 대한 내용을 모를 때는 이러한 방식을 사용하면 된다.( CTRL+F3 은 이름관리자 )
image


급여총액의 10%를 계산해서 소득세를 구해보자.
=SUM(E2:H2)*0.1 처럼,  급여총액까지는 함수식을, 10%는 수식을 복합적으로 이용했다.
image

옆에 빈칸에 기술팀의 기본급+식대의 합을 구해보자.
SUM ( 기본급의 합 + 식대의 합 ) 형태로,  SUM함수의 인자에 특정범위나 특정값이 아닌, 다시 함수식을 넣었다.
=SUM( SUM(E2:E13),SUM(F2:F13) )
image


참조(셀의 위치)를 이해하기

참조는 셀의 위치를 의미하고, 수식 안에서는 변수로 작동한다.
참조는 상대참조와 절대참조로 나뉘어진다.
상대 참조는 아무것도 안붙어있지만, 절대 참조는 앞에 $표시(고정의 의미)가 붙어있다.
예를 들어 B1은 상대참조, $B$1은 절대참조이며  B앞의 $는 열을 고정 / 1앞의 $는 행을 고정시킨다는 의미이다.
열 혹은 행에만 달러가 붙을 수 도 있다. ($B1, B$1)

범위에 있어서도 절대 참조가 있다. A1부터 B3까지는 A1:B3로 나타낼 수 있는데, 고정된 위치라면 $A$1 : $B$3으로 나타낼 수 있다.

절대참조를 상대참조와 구분하는 이유는 [복붙] 혹은 [자동채우기핸들]을 이용시 , 참조의 위치를 변경시킬 것이냐 아니냐를 결정하는데 있다.

image

*특정인자를 [F4]를 눌러서, 참조형식을 바꿀 수 있다.

실습 : 가격에 할인율을 적용시켜 판매가격을 결정해보자

image

판매가격 = 가격 - (가격*할인율)을 수식으로 적용시켜보자.
image

아래 2개를 자동채우기로 채웠더니, 문제가 생겼다.
프린터는 할인율이 적용되지 않았고, 마우스는 에러가 났다.
image

프린터의 경우를 보자. 할인율이 C2가 되어야하는데, C3로 옮겨져서 에러가 된것이다.
상대적으로 같이 변해야할 가격의 위치와 달리, 할인율은 위치가 변하면 안되는 것이다.
image
변하지 않아야할 위치인 할인율(C2)를 달러$표시[F4]를 붙혀서 고정시킨 뒤, 자동채우기를 하자.
image
image


아래 예제에서,  총점을 계산할 때, 가산점10점을 주기로 했다. 가산점을 붙힌 총점을 계산해보자.
image

수식을 작성해보자.
image
이 때, 고정된 가산점은 고정되어야하므로, 절대참조[F4]로 만들고 난 뒤, 자동채우기로 채우자.
image
이 때, 참조의 형식을 바꿀필요가 있는 인자를 선택한 뒤, [F4]키를 눌러서 바꿔보자.
image

기본 단축키

image


주요 기능 단축키

image



실습

  • [ctrl+d] 는 바로 위에있는 셀의 값을 복사해온다.(수식일 경우, 수식통째로 복사)
    imageimage

  • [ ctrl + 세미콜론(;) ]은  오늘의 날자가 상수로 자동 입력된다.
    image

  • [ shift+F3 ] 을 통해, 함수마법사를 불러와서,  범주 > 모두 를 선택해 [TODAY] 함수를 입력해보자.
    imageimageimage
    image
    [ ctrl + ; ]는 상수 인데 비해, [ shift+F3 ] = TODAY()는 매일매일 바뀌는 함수식이 된다.

선택 및 이동의 실습

  • 데이터가 있는 셀을 선택한 상태에서,  [Ctrl+shift + *]를 누르면 뭉쳐져있는 데이터들이 전체선택된다.
    imageimage


    imageimage



    imageimage
    데이터가 없는 곳에서 ctrl+shift+*를 누르면, 아무 반응이 없다.

  • 셀을 선택한 상태에서, [ shift + space ]행을 선택
    imageimage

    셀을 선택한 상태에서, [ ctrl + space ]열을 선택
    imageimage

    이렇게, 행 선택/ 열 선택은 < 행 or 열을 삭제 할 때, 가장 많이 사용된다>
    [ shift + space ] -> [ctrl + (-) ] : 행 삭제
    [ ctrl+ space ] -> [ctrl + (-) ] : 열 삭제


  • [ ctrl+ 방향키 ]는 데이터가 있는 구간에서, 끝으로 이동하기 때문에 < shift와 같이써서, 데이터가 있는 행/열을 선택할 수 있다>
    imageimage

  • [Ctrl + PgUP / PgDn ] 은 시트를 쉽게 이동할 수 있다.
    imageimage


주요 기능 단축키 실습

  • [ Ctrl + shift + L ]은 자동필터를 붙히거나 해제한다. 메뉴에서는 홈 탭> 정렬 및 필터 > 필터 에 위치해있다.
    셀만 선택한 상태에서 해도, 뭉쳐있는 데이터 전체에 대해, 가장 위에 있는 행에다가 역삼각형의 필터를 붙혀준다.
    imageimage

  • [ Ctrl + L ]은 자동필터와 혼동하기 쉬운 [표 만들기] 단축키이다.
    모습을 보면, 자동필터처럼, 첫 행에 필터가 생긴다. 하지만 일반 데이터테이블과 차이점있다.

    imageimage

    자동필터만 달아준 것과 달리, 표 아래의 행에 데이터를 추가하면,  표에 대한 행으로 확장되어 추가된다.
    문제는 자동필터처럼 단축키를 다시 눌러 복구 되는 것은 안된다.
    imageimage

  • [ ctrl + 1]을 누르면 셀 서식 상자가 나타난다.
    image

  • [ ctrl + F3 ]은 이름관리자 단축키이다. 새로 만든 엑셀에는 텅 비어있는게 정상.
    image


빠른 실행도구 모음 사용하기

순서대로 alt + 1,2,3 로 단축키가 지정되므로, 순서를 잘 정해서 넣어야한다.
추가하는 방법은

  1. 좌측상단의 빠른실행도구 모음 옵션키를 눌러 [ 기타명령 ]으로 간다
  2. [사용자 지정]탭에서, 명령 선택을 [모든 명령]으로 바꾼 뒤, 원하는 것을 추가한다.
  3. 추가된 빠른실행도구의 순서를 조정한다.
  4. 다시 옵션키를 눌러 [ 리본메뉴 위에 표시]를 선택해, 보기 편하게 위치를 바꿔준다.
    imageimageimage


빠른실행도구 모음에 유용하게 쓸 수 있는 기능들

  1. 값만 붙여넣기(ctrl + alt+ v)
  2. 병합하고 가운데 맞춤 <->  셀분할  :  특정셀을 묶어서 병합하고 <--> 풀어주는 기능
  3. 글꼴 크기 크게/작게(파포에 있지만 엑셀에는 단축키가 없다)
  4. 시트행 삭제
  5. 시트 삭제, 시트이름 바꾸기
  6. 모든 테두리, 굵은 상자 테두리
  7. 이동옵션
  8. 틀 고정 / 틀고정 취소
  9. 인쇄 미리보기 및 인쇄

image


  • 셀서식이 있는 직위라는 셀을 복사한 다음, 빈 셀에서 [alt+1]을 통해 값 붙여넣기를 실행해보자
    imageimageimage

  • [alt+2]를 통해서 병합하고 가운데 맞춤을 해보자.
    imageimageimage

    원래대로 풀기 위해선 [alt+3]으로 병합풀기를 해보자.
    image

  • [alt+4]로 글자크기를 키워보자 [alt+5]는 글자크기 작게
    image

  • [alt+6]로 행 삽입/ [7]으로 행삭제를 해보자.
    image

  • [alt+8]로 shee3시트를 삭제해보자.(되돌리기 안됨)
    imageimage

  • [alt+9]로 시트이름을 바꿔보자.
    image

  • 10은 단축키가 없다 그다음 단축키인 [alt+09]를 통해 모든테두리를 쳐보자.
    그다음 단축키인 [alt+08]로 굵은 테두리를 쳐보자.
    imageimage

  • 그 다음 빠른실행도구모음인 [ 이동옵션 ]을 클릭해보자.
    자동필터와 관련해서 [화면에 보이는 셀만(y)] 옵션이 자주 쓰일 것이다.
    image

    다음으로 [틀 고정]
    image

    인쇄 미리보기
    image


다른기능을 추가하고 싶다면, 리본메뉴들 중에서 우클릭해서 [빠른실행도구 모음 추가]로 추가해주면 된다.

셀과 시트

image_thumb49


셀의 데이터 다루기

  1. 편집하기 : 입력값을 수식입력줄에 입력후 enter

  2. 삭제하기 : 시트 행삭제, 시트 열삭제, 셀 1개만 삭제 [ shift + 자동채우기 위로 드래그]
    * del키를 눌러서 삭제하는 것은 cell삭제가 아니라, 셀이 가진 데이터를 삭제한다.
    image_thumb47
    그냥 자동채우기 핸들 위로 드래그 : del처럼 셀 데이터만 삭제
    image_thumb53

    shift 누른채 자동채우기 핸들 위로 드래그 : 셀 1개가 삭제, 아래칸이 위로 밀려온다.
    image13_thumb3

    열삭제 : 열 이름에 우클릭후 삭제
    image16_thumb3

    셀 삭제 속 메뉴를 이용해 열 삭제
    image28_thumb3

    행삭제 : 행 숫자에 우클릭 후 삭제
    image22_thumb4
    셀 삭제 속 메뉴를 이용해  행삭제
    image25_thumb3


  3. 추가하기 : 시트 행/열/셀 삽입[ ctrl + (+) ], 시트 행/열/셀 삭제 [ ctrl+ (-) ]
    행 숫자 or 열이름에 우클릭으로 행/열을 삽입할 수 있다.
    단축키로는 ctrl + [+]를 눌러서  셀 삽입/ 행삽입(기존 아래로 한칸 밈) /열삽입(기존 오른족으로 한칸 밈)
    ctrl + [-]를 눌러서 셀 삭제 / 행삭제 / 열삭제

  4. 복사하기 : ctrl+c/v ,   ctrl+ alt + v(선택하여 붙혀넣기) , ctrl+ 셀 경계드래그
    *** 엑셀에서의 복사/ 붙혀넣기는 서식까지 같이 가져간다. 그러므로 붙히기시 항상 붙여넣기옵션이 따라붙는다.
    image31_thumb3

    ctrl+alt+v를 통해 [값만] 붙여넣을 수 있는 옵션을 미리 띄울 수 있다.
    image34_thumb

    image37_thumb3


채우기 핸들

채우기핸들을 드래그해서 내릴 때,
- 숫자의 경우 : 데이터 그대로 복사
- 문자열의 경우 : 일련번호로 채우기
  *ctrl을 누른채로(++) 드래그시 반대로 행동한다. 즉, 숫자의 경우 일련번호로 채움 / 문자열의 경우 데이터 그대로 복사
  (  + : 채우기핸들,  ++ : ctrl누른 채우기핸들 / 미리보기도 같이 나타난다. *함수식으로 된 경우, 일련번호는 안됨)
  cf)shift를 누른채로 드래그시는 다른 행동이 나타난다. shift+채우기 위로 드래그 :  셀 삭제 / 아래로 드래그 : 셀 삽입?
   image43_thumb9

실습


image46_thumb3
위와 같이 순번을 차례대로 채우고 싶다.

먼저 아무것도 안누르고 채우기핸들 드래그시는 그냥 데이터만 복사한다.
image49_thumb3
이 때, 자동채우기 옵션이 따라오는데, 이것을 선택해서 일련번호로 채울 수 있다.
image52_thumb3
image58_thumb5
image61_thumb3

단축키(ctrl)를 누른체 자동채우기(스샷을 보면 +에 +가 하나 더있는 상태)를 아래로 드래그하면, 일련번호로 채워진다.
image68_thumb3
image71_thumb3


문자열의 경우, 아무것도 안누르고 자동채우기핸들 드래그시는 일련번호가 나온다.
image74_thumb3image77_thumb3

**간혹 필요에 의해 순번의 간격이 1이 아닌 경우에는, 셀1개가 아닌, 간격이 주어진 2개의 셀을 선택한 상태에서 자동채우기 핸들을 하면 된다.
  ctrl누를 필요없이 차이만큼 연속된 데이터가 나타난다.

shift를 누른상태에서 자동채우기핸들 아래로 드래그시   아래로내려간 칸만큼 셀이 삽입된다.
image80_thumb3image83_thumb3
빈 셀을 선택한 상태에서 shift + 채우기핸들 아래로 드래그시, 빈셀이 그만큼 삽입된다.
image86_thumb4image89_thumb3
반대로 , shift+채우기핸들 위로 드래그는, 원래 선택된 셀까지는 삭제 되나  //// 그 위로 넘어가면 셀이 삽입되는 현상 ㅠ

시트의 경우 추가하게 되면, 해당 시트의 왼쪽에 추가된다.
image92_thumb
아래 추가버튼을 클릭해서 쉽게 추가할 수도 있다.
image95_thumb3
삭제시 컨트롤을 누른상태에서 여러개 선택하여 한꺼번에 삭제가 가능하다.

시트 복사의 경우, 이동/복사 메뉴를 선택한 상태에서, [복사본 만들기]를 체크해야한다.
image98_thumb

자주 쓰이는 형식들

  1. 관리형 : 데이터가 한 행(record)씩 쌓여가는 것
  2. 집계형 : 관리형 데이터를 집계하여 통계량을 확인하는 데이터
  3. 참조목록형 : 정해진 정보를 참조할 수 있도록 구성한 것( 상품에 대한 단가나 배송료 등 미리 정해진 것)

기본 데이터의 입력

엑셀은 셀(cell)단위로 데이터를 다루고, 들어갈 수 있는 것은 [ 값 혹은 식 ]이 들어간다.

  1. ( 또는 상수) : 숫자 문자열(숫자문자열의 경우 '를 먼저 입력) / 날짜(대쉬-로 연결), 시간(콜론(:)으로 연결) / 조건값
  2. (=으로 시작) : 수식(=4칙 연산 등) / 함수식(=sum(,)함수 등)

값의 입력은 기본적으로 [enter]이나, 여러셀이 블록지정 되어있을 때는, [ctrl +enter]로 일률적으로 입력시킬 수 있다.


실습

  • 기본적으로 숫자는 우측정렬 / 문자열은 좌측정렬이다.
    만약 숫자를 문자열 형식으로 저장하고 싶다면 '를 붙힌상태에서 숫자를 기입하자.
    image

  • 아래처럼 숫자123앞에 '를 넣어서 문자열 형식으로 입력하면, 문자열들처럼 좌측정렬이 됨과 동시에, 초록마커가 생긴다
    image

  • 분수를 입력할 때에는,  1/2을 입력하면 (/)가 날짜로 인식된다.
    이 때, 대분수처럼 생각하면서 1/2을 0과 1/2로 생각하여 [ 0 1/2]을 입력해보자.
    image
    image
    image
    image


  • 날짜를 입력할 땐 대쉬를 사용한다. 만약 3월 14일을 의미하는 03-14를 입력하면 년도도 자동으로 입력된다.
    image
    image

  • 시간 입력시 콜론:을 이용한다. 시간을 입력하면, 수식입력란에 자동으로 AM PM이 붙게 된다
    image
    image

  • 수식( =사칙연산)을 입력해 나온 결과값과 일반 숫자값과 비교하기 위해서는 [ ctrl + grave(`)키 ] 를 눌러서 확인할 수 있다.
    수식을 입력했다면, 수식입력란에 해당 수식이 나온다.
    image
    image
    image


  • 함수식(=sum)을 입력할 때도, 기본 숫자데이터와 비교하기 위해선 [ctrl+ grave]를 이용한다.
    수식입력란에 보면, 함수식이 나와있다.
    image
    image
    image

+ Recent posts