'말랑말랑 컴퓨터/엑셀 강좌&팁'에 해당되는 글 86건

  1. 2018.08.15 [엑셀활용] 자동으로 일련번호가 표시되도록 하기 (IF, ROW 함수 활용) by Y
  2. 2014.07.18 엑셀로 재직증명서 만들기 (LOOKUP, DATEDIF 함수 활용) by Y (6)
  3. 2014.07.11 [엑셀기초] 엑셀 줄바꾸기 (Alt+Enter, 텍스트 줄 바꾸기) by Y
  4. 2014.07.10 엑셀 내용 합치기 by Y (2)
  5. 2014.07.02 엑셀 수식 표시하기 by Y
  6. 2014.06.25 컴활2급 실기 시험을 위한 엑셀 환경 설정하기 by Y (7)
  7. 2014.05.31 컴활2급 실기 도움 파일 모음 (시험시 유의 사항, 함수 총정리 파일, 단축키 모음 파일) by Y (34)
  8. 2014.05.30 외부 데이터 가져오기 3 (메모장 파일을 엑셀로 가져오기) by Y (4)
  9. 2014.02.12 ITQ 자격증 문제 풀이 팁 (ITQ 엑셀, ITQ 파워포인트, ITQ 한글) by Y (3)
  10. 2014.01.29 엑셀 이름정의, 이름삭제, 이름 활용 방법 by Y (3)
  11. 2013.12.18 엑셀 가상 분석 기능 '시나리오' by Y (8)
  12. 2013.11.28 엑셀 함수 23 - OFFSET 함수 (동적인 셀 참조하기) by Y
  13. 2013.11.22 엑셀 가상 분석 기능 '데이터 표' by Y (14)
  14. 2013.10.11 [엑셀기초] 모든 셀에 같은 수 더하기/곱하기 (선택하여 붙여넣기 - 연산 기능) by Y (8)
  15. 2013.09.30 엑셀 함수 22 - INDIRECT 함수 (동적인 셀 참조하기) by Y (14)
  16. 2013.09.26 [엑셀 활용 예제] 데이터 필터링 결과 자동 계산하기 (SUBTOTAL 함수, 매크로, 고급 필터 활용) by Y (12)
  17. 2013.09.09 엑셀 함수 21 - TRANSPOSE 함수, 선택하여 붙여넣기 (행/열 바꿈) by Y
  18. 2013.08.16 엑셀 차트 만들기 3 - 데이터 선택, 차트 이동, 축 방향 변경 등 by Y (7)
  19. 2013.08.14 엑셀 차트 만들기 2 - 차트의 구성 요소 추가/삭제/편집 by Y (3)
  20. 2013.08.14 엑셀 차트 만들기 1 - 차트 레이아웃, 차트 스타일 by Y (2)
  21. 2013.08.13 2007 엑셀파일 암호(비밀번호)설정 및 해제방법 by S (6)
  22. 2013.07.25 엑셀 중복값 찾기/중복값 제거 (조건부 서식과 중복된 항목 제거) by Y
  23. 2013.07.12 [엑셀기초] 엑셀 틀고정 by Y
  24. 2013.07.09 엑셀 함수 20 - DATEDIF 함수 (경과일수, 근속일수 구하기)
  25. 2013.07.05 [엑셀기초] 숫자에 표시형식 적용하기 by Y
  26. 2013.07.04 [엑셀기초] 엑셀 표 만들기 (매입매출장 만들기) by Y (12)
  27. 2013.07.01 엑셀 단축키 모음 파일 (자주 사용하는 단축키 위주) by Y (15)
  28. 2013.06.21 엑셀 함수 총정리 파일 (컴활, ITQ 대비) by Y (112)
  29. 2013.06.18 엑셀 주소라벨 만들기 (VLOOKUP 함수 실습) by Y (32)
  30. 2013.06.11 엑셀 빈셀/빈칸 없애기, 공백 문자 없애기 by Y (7)

 

 

 

일련번호는  1, 2, 3,....의 숫자를 의미한다.

일련번호는 주로 엑셀에 데이터가 많을 경우 데이터의 순서를 알기 위해 사용되므로 직접 입력하는 것보다는 자동으로 입력되는 것이 편리한 것은 당연~

난 주로 쇼핑몰 관리 엑셀 파일에서 주문번호에 이 일련번호를 사용하는데 자동으로 입력되도록 함수식을 만들어서 사용했더니 세상 편하다 ㅋ




일련번호가 자동으로 매겨질 때 필수 함수는 ROW 함수다.

ROW 함수는 '=ROW(A3)'처럼 사용되는데 참조된 A3셀의 행번호인 '3'을 결과로 보여주는 행번호 함수다. 하지만, ROW함수만 사용하면 아래처럼 미리 일련번호가 다 매겨져서 한눈에 끝 데이터를 찾기 힘들고 지저분해진다.



<ROW> 함수만 사용한 일련번호




'이름'과 같은 데이터가 있는 경우에만 자동으로 일련번호가 매겨지게 하려면 IF 함수를 같이 사용하면 된다. IF 함수는 '이름'에 데이터가 있는 경우에는 일련번호를 표시하고, 아닐 경우에는 공백으로 표시하기 위해 사용한다.



<IF, ROW> 함수를 사용했을 경우의 일련번호







실습해보기


완성파일.xlsx







1. B3 셀에 '=IF(C3<>"",ROW(C3)-2,"")' 함수식을 입력한다.


=ROW(C3)-2


위의 식을 [B3] 셀에 입력하면 'ROW(C3)'에서 3이 구해지고 2를 빼서 1이 입력된다.

즉, 첫 데이터가 시작하는 3행부터 아래로 1, 2, 3, 4....와 같은 일련번호가 표시되도록 하는 식이다.


위의 식에 IF 함수를 추가해서 식을 완성해보겠다.


=IF(C3<>"",ROW(C3)-2,"")


IF 함수는 'IF(조건, 참, 거짓)'처럼 세개의 인수로 구성되어 있다.

첫번째 인수인 조건이 참이면 두번째 값이 표시되고, 거짓이면 세번째 값이 표시되는 함수다.


여기에서는 조건이 'C3<>""'인데, C3셀 즉, 이름이 ""(공백)이 <>(아니면)...이라는 조건이다.

이 조건으로 인해 이름이 공백일 경우와 공백이 아닐 경우에 다른 결과를 표시할 수 있게 된다.

공백이 아닐 경우 표시할 두번째 인수에는 위의 ROW 함수식을 넣었기 때문에 일련번호가 표시된다.

하지만, 공백일 경우 즉, 조건이 거짓일 경우에는 ""(공백)이 표시되는 것이다.








2. B3 셀에 입력한 함수식을 아래로 드래그하여 자동채우기한다.

드래그를 못할 정도로 데이터가 무진장 많을 경우에는 채우기 핸들에서 더블 클릭해도 된다.







3. 식을 모두 복사해도 데이터(이름)이 없기 때문에 아무것도 표시가 안된다.







4. 하지만, 이름을 입력하면 일련번호가 자동으로 매겨지게 된다.









 

 

 

 

 

 

Posted by Y&S

댓글을 달아 주세요

 

 

 

엑셀에서 함수와 데이터 유효성 검사 등을 이용하여 재직증명서 등의 양식을 만들면,

이름, 주민등록번호 검색으로 양식의 내용이 자동으로 채워져 일의 능률을 올릴 수 있다.

 

 

이 예제에서는 데이터 유효성 검사, LOOKUP 함수, DATEDIF 함수가 사용되니 잘 모른다면 미리 함 보자~!

엑셀 데이터 유효성 검사 실습 (조건부 서식 자동으로 지정하기) by Y

엑셀 함수 4 (찾기/참조 함수) by Y

엑셀 함수 20 - DATEDIF 함수 (경과일수, 근속일수 구하기)

 

 

유의할 점

1. LOOKUP 함수는 두 번째 인수에 사용될 범위는 반드시 오름차순으로 정렬되어 있어야 한다.

이 예제에서는 주민등록번호로 데이터를 찾고 있으므로 주민등록번호를 오름차순으로 정렬해야한다.

2. 본인이 가지고 있는 데이터로 재직증명서를 만들 경우 [이름 지정하기] 작업을 꼭 다시 해야한다.

만약 준비 파일을 수정하고 싶은 경우 [수식]-[이름 관리자] 명령을 실행하여 모든 이름을 제거한 후 다시 이름을 지정해야 한다.

 

이미 지정된 이름을 제거하는 방법을 더 자세히 알고 싶다면~!  엑셀 이름정의, 이름삭제, 이름 활용 방법 by Y

 

 

 

 

 

준비 파일과 완성 파일

 

 준비 파일.xlsx 완성 파일.xlsx

 

'준비 파일'을 열어보면 [재직증명서] 시트와 [직원명단] 시트의 2개의 시트가 있는데,

[재직증명서] 시트는 재직증명서 표가 2페이지에 인쇄되도록 만들어져 있고,

[직원명단] 시트에는 직원들의 정보가 입력되어 있다.

 

 

 

 

 

 

1. 주민등록번호를 오름차순으로 정렬하기

 

 

 

1. [직원명단] 시트의 [C2] 셀을 선택한 후 [데이터]-[정렬 및 필터]-[오름차순] 명령을 클릭한다.

첫 번째 데이터인 [C3] 셀을 선택한 후 작업해도 된다.

 

 

 

 

2. 주민등록번호가 '작은수->큰수'로 정렬된다.

 

 

 

 

 

 

2. 이름 지정하기

 

 

 

[재직증명서] 시트에서 [직원명단] 시트의 내용을 참조할 때 'B3:B34'가 아닌 '성명'처럼 문자 방식으로 참조하면 매우 직관적으로 식을 작성할 수 있게 되어 이해하기 편하고, 여러 사람과 공동 작업을 할 경우 관리가 편리해진다.

아래와 같이 [직원명단] 시트의 성명, 주민등록번호, 소속, 직위, 주소, 입사일 범위의 이름을 표의 제목으로 지정해보자.

 

 

 

 

1. [B2:G34] 범위를 지정한 후 [수식]-[정의된 이름]-[선택 영역에서 만들기] 명령을 클릭한다.

 

 

 

 

2. [선택 영역에서 이름 만들기] 대화 상자가 나타나면 '첫 행'을 선택한 후 [확인] 버튼을 클릭한다.

 

 

 

 

 

 

 

3. 주민등록번호를 선택할 수 있도록 유효성 검사 적용하기

 

 

 

1. [재직증명서] 시트의 [D2] 셀에서 주민등록번호를 선택할 수 있도록 데이터 유효성 검사를 적용해보자.

[D2] 셀을 선택한 후 [데이터]-[데이터 도구]-[데이터 유효성 검사] 명령을 클릭한다.

 

 

 

 

2. [데이터 유효성] 대화 상자가 나타나면 '제한 대상'을 '목록'으로 변경한 후 '원본'에 '=주민등록번호'를 입력한다.

'주민등록번호'는 [직원명단] 시트의 '주민등록번호' 범위를 의미한다.

 

 

 

 

3. 이제 아래와 같이 목록 버튼이 생성되어 버튼을 클릭하면 주민등록번호를 쉽게 선택할 수 있게 된다.  

 

 

 

 

 

 

 

4. LOOKUP 함수로 해당 직원 정보 가져오기/

DATEDIF 함수로 재직기간(근속일수) 구하기

 

 

 

1. 재직증명서 양식의 성명, 주민등록번호, 주소, 소속, 직급을 수식을 이용하여 구한다. 

 

 

=LOOKUP(D2,주민등록번호,성명) 식의 의미는?

LOOKUP 함수는 첫 번째 인수값을 두 번째 인수인 범위에서 찾아 세 번째 인수 범위 중에서 같은 행에 위치한 값을 찾아주는 함수다. 즉, 주민등록번호 범위에서 D2값을 찾은 후 같은 행에 위치한 성명을 찾아준다.

 

 

 

2. 재직기간을 구하기 위해 먼저 입사일과 오늘 날짜를 수식으로 구한다.

 

 

=LOOKUP(D2,주민등록번호,입사일) => 입사일을 구한다.

=TODAY() => 오늘 날짜를 구한다.

 

 

 

3. 입사일의 표시형식을 'YYYY"년" M"일" D"일부터"'로 지정하여 '○○○○년 ○월 ○일부터'로 표시되도록 표시 형식을 수정해준다.

 

 

 

 

4. 오늘 날짜의 표시형식을 'YYYY"년" M"일" D"일까지"'로 지정하여 '○○○○년 ○월 ○일까지'로 표시되도록 표시 형식을 수정해준다.

 

 

 

 

5. DATEDIF 함수를 이용하여 입사일로부터 오늘 날짜까지의 기간을 구하여 재직기간을 구해준다. 

 

 

=DATEDIF(D10,D11,"Y")&"년 "&DATEDIF(D10,D11,"YM")&"개월 "&DATEDIF(D10,D11,"MD")&"일" 식의 의미는?

입사일(D10)로부터 오늘 날짜(D11)까지의 기간 년수(Y)에 '년 '을 붙이고.

입사일(D10)로부터 오늘 날짜(D11)까지의 1년 미만의 개월(YM)에 '개월 '을 붙이고.

입사일(D10)로부터 오늘 날짜(D11)까지의 1개월 미만의 일수(MD)에 '일'을 붙였다.

 

 

 

 

 

 

재직증명서 인쇄하기

 

 

 

1. 재직증명서를 작성할 직원의 주민등록번호를 선택하면, 재직증명서의 직원 정보가 자동으로 채워지게 된다.

'용도'는 따로 입력해준다.

 

 

 

 

2. [재직증명서] 시트에서 재직증명서 표만 따로 2페이지로 지정되어 있으니 인쇄할 경우 2페이지만 인쇄하도록 한다.

 

 

 

 

3. 아래와 같이 인쇄된다. 

 

 

 

 

 

 

 

 

Posted by Y&S

댓글을 달아 주세요

 

 

 

엑셀에서 열너비보다 내용이 길 경우 한 셀의 내용을 2줄, 3줄로 표시하는 방법에는 두 가지 방법이 있다.

첫 번째 방법인 'Alt + Enter'는 내가 줄을 바꾸고 싶은 부분에서 강제로 줄을 바꾸는 단축키이고,

두 번째 방법인 '텍스트 줄 바꾸기'는 열 너비에 맞춰 넘치는 부분만큼 자동으로 줄을 바꾸는 기능이다.

 

 

 

1. Alt + Enter

원하는 부분에서 강제로 줄을 바꿀 수 있는 기능이다.

열 너비가 내용보다 좁아질 경우 - 줄 바꿈 내용에 변화가 없고 열 너비만큼만 표시되고 넘치는 부분은 숨겨진다.

열 너비가 내용보다 넓어질 경우 - 줄 바꿈 내용에 변화가 없다.

 

이 기능은 주로 표에서 소제목이 길 경우 사용한다.

 

 

 

 

2. 텍스트 줄 바꿈

[홈]-[맞춤]-[텍스트 줄 바꿈], [셀 서식]-[맞춤]-[텍스트 줄 바꿈]

열 너비보다 넘치는 부분을 자동으로 줄 바꿈해준다.

열 너비가 좁아지거나 넓어질 경우 - 열 너비에 맞춰서 줄 바꿈이 자동으로 변경된다.

 

이 기능은 주로 제목이 아닌 내용이 넘칠 경우 사용한다.

 

리본 메뉴를 이용할 경우

 

 

[셀 서식] 대화 상자를 이용할 경우

 

 

 

 

 

'Alt + Enter' 활용

 

 

 

 

1. 아래와 같이 두 줄로 입력된 내용이 있을 경우 열 너비에 맞춰 내용을 균등 분할하여 보기 좋게 변경해보자.

셀의 바로 가기 메뉴에서 [셀 서식] 메뉴를 선택한다.

 

 

 

 

2. [셀 서식] 대화 상자가 나타나면 '텍스트 맞춤'의 '가로'에서 '균등 분할 (들여쓰기)'를 선택한다.

이 기능을 선택하면 '들여쓰기'가 활성화되는데 '0'이 아닌 다른 숫자를 입력하면 그 숫자만큼 양쪽 끝에 여백을 주게 된다.

 

 

 

 

3. 아래와 같이 셀 내용에 균등 분할이 적용되어 열 너비에 맞춰 같은 간격으로 띄어진다. 

이처럼 Alt + Enter와 균등분할 기능을 사용하여 셀 내용을 꾸밀 수 있다.

 

 

 

 

'텍스트 줄 바꿈' 활용

 

 

 

 

1. 내용이 길 경우 텍스트 줄 바꿈으로 열 너비에 맞게 내용을 표시하면 아래와 같이 여러 줄로 나뉘는 걸 볼 수 있다.

 

 

 

 

2. 내용을 두 줄로 표시하기 위해 B 열의 너비를 넓히면 기존의 행 높이가 그대로 유지되어 위아래 공백이 생기는 것을 볼 수 있다. 행 구분선에서 더블 클릭해주면...

 

 

 

 

3. 아래와 같이 내용에 맞게 행 높이가 자동 맞춤이 되어 공백이 사라지게 된다.

 

 

 

 

 

 

 

 

 

Posted by Y&S

댓글을 달아 주세요

 

 

 

엑셀에서 따로 입력된 내용을 하나의 셀로 합칠 경우 두 가지 방법이 있다.

바로 '&' 연산자를 사용하는 방법과 'CONCATENATE' 함수를 이용하는 방법이다.

&는 +, - 등의 연산자와 같은 연산자로 문자, 숫자, 함수식 등을 연결해준다.

CONCATENATE 함수는 함수 안의 인수를 모두 연결해주는 함수다.

 

 

 

 

간단히 '&' 연산자와 'CONCATENATE' 함수를 사용하는 방법을 먼저 알아보자.

 

 

A5셀, B5셀의 값과 '도서'라는 문자를 합치는 경우

 &

 =A5&B5&"도서"

 CONCATENATE

 =CONCATENATE(A5,B5,"점")

※ 문자를 직접 쓸 경우 문자의 앞뒤에 큰 따옴표를 넣어야한다.

CONCATENATE 함수에서는 쉼표로 구분된 각각의 인수를 연결해준다.

 

 

A5셀부터 A10셀까지 더한 값에 '점'이라는 문자를 합치는 경우 

 &

 =SUM(A5:A10)&"점"

 CONCATENATE

 =CONCATENATE(SUM(A5:A10),"점")

※ SUM 함수의 계산 결과에 '점'이 연결되어 표시된다. 예) 450점

사이에 공백을 넣을 경우 " 점"과 같이 공백도 직접 입력한다.

 

 

 

 

아래와 같은 표에서 제품종류, 제품번호, 제조년도 셀의 값을 'W101-2014'와 같은 방식으로 합쳐보자.

 

 

 

 

 

 

& 연산자 활용하기

 

 

 

 

1. E6셀에 '=B6&C6&"-"&D6'와 같이 식을 완성한다.

'-'는 셀에 포함된 내용이 아니므로 직접 큰 따옴표를 이용하여 입력해줘야 한다.

 

 

 

 

2. [Enter] 키를 누르면 각각의 셀의 내용을 합친 결과가 표시된다.

수식을 복사하기 위해 채우기 핸들에서 마우스 포인터 모양이 '+' 모양으로 변경되면 아래로 드래그한다.

 

 

 

 

3. 수식이 복사되어 나머지 셀에도 왼쪽 셀의 내용을 합친 결과가 구해진다.

 

 

 

 

4. 테두리 모양에 따라 [자동 채우기 옵션] 버튼을 눌러 '서식 없이 채우기' 옵션을 선택한다.

이 옵션을 선택하면 테두리는 복사되지 않아 바깥쪽 굵은 테두리가 유지된다.

 

 

 

 

 

CONCATENATE 함수 활용하기

 

 

 

 

1. F6 셀에 '=CONCATENATE(B6,C6,"-",D6)' 수식을 입력한다.

 

 

 

 

2. 위와 마찬가지 방법으로 수식을 복사하면 동일한 결과가 표시되는 것을 볼 수 있다.

 

 

 

 

 

 

 

 

Posted by Y&S

댓글을 달아 주세요

 

 

 

 

 

 

엑셀에 수식을 입력하면 원래 편집 상태에서만 식이 보이고 (Enter 키 등을 눌러서) 입력이 완료되면 수식의 답이 보이게 된다. 즉, 시트에 여러 수식이 입력되었을 경우에 동시에 하나의 식만 확인할 수 있다는 의미다.

이때 시트에 입력된 수식을 모두 표시하게 되면 수식을 확인하는 것이 편해져 틀린 수식을 찾아내거나, 함수식을 공부하는 데에 도움이 된다.

 

 

 

 

시트에 입력된 수식을 그대로 표시하는 방법은 두 가지가 있다.

1. 내가 원하는 수식만 표시하고자 할 경우

셀 안의 내용 앞에 작은 따옴표(')를 입력하면 ' 다음에 입력한 내용이 그대로 표시되므로 원하는 식만 골라 표시할 수 있다.

 

2. 시트에 입력된 모든 수식을 표시하고자 할 경우

[Ctrl]+[`] 단축키를 누르면 시트에 입력된 모든 수식이 표시된다. 단축키를 다시 누르면 원래대로 답이 표시된다. [수식] 탭의 [수식 분석] 그룹의 [수식 표시] 명령을 클릭해도 된다. 

 

 

 

 

 

내가 원하는 수식만 표시하기

 

 

 

 

1. 아래와 같은 내용이 입력된 시트에서 [D16] 셀에 입력된 수식만 표시해보자.

 

 

 

 

2. [D16] 셀을 더블 클릭하여 편집 상태로 들어간다.

 

 

 

 

3. 수식 왼쪽에 작은 따옴표(')를 입력한다.

 

 

 

 

4. [Enter] 키를 눌러도 답이 아닌 수식이 표시되는 것을 볼 수 있다.

 

 

 

이처럼 내가 원하는 수식만 표시하고자 할 경우 작은 따옴표를 이용한다.

 

 

 

 

시트에 입력된 모든 수식 표시하기

 

 

 

 

1. 아래 내용에 포함된 모든 수식을 모두 표시하기 위해 단축키 [Ctrl]+[`] 키를 누른다.

 

 

 

 

 

2. 아래와 같이 모든 수식이 한 번에 표시되지만, 열 너비가 자동으로 넓어지지 않으므로 수식이 짤려서 표시된다.

 

 

 

 

3. 수식을 모두 보이도록 열 너비를 조정하기 위해 원하는 열만큼 열 머리글을 드래그하여 선택한다.

 

 

 

 

4. 선택한 범위에 포함된 임의의 열 구분선을 더블 클릭한다.

 

 

 

 

5. 제일 긴 데이터에 맞춰 자동으로 열 너비가 조정되어 수식을 모두 확인할 수 있다.

 

 

이처럼 [Ctrl]+[`] 단축키를 이용하여 시트에 포함된 모든 수식을 표시할 수 있다.

 

 

 

 

 

 

Posted by Y&S

댓글을 달아 주세요

 

 

 

엑셀 2007을 설치하면 매크로 등의 고급 기능이나 자동 서식과 같은 엑셀 2003 기능은 없다.

그래서, 컴활 실기 시험을 보기 전, 연습하기 위해서 또는 시험장에서 제대로 시험보기 위해서 [개발 도구] 탭, 자동 서식, 카메라 도구 표시, 매크로가 실행되도록 설정하는 등의 엑셀 환경 설정을 할 줄 알아야한다.

 

 

 

각각의 방법은 아래 글을 참조해보자. 

빠른 실행 도구 모음에 [자동 서식] 명령 추가하기 by Y 

엑셀 리본 메뉴에 [개발 도구] 탭 표시하기 by Y 

엑셀 매크로 포함시켜 실행되도록 설정하기 by Y

 

 

 

 

빠른실행도구모음에 자동서식, 카메라 표시하기

 

 

자동서식과 카메라 기능은 2007 버전 이후부터 제공되지 않는 기능이므로 수동으로 추가하여 사용해야 한다.

 

 

 

 

리본 메뉴에 [개발 도구] 탭 표시하기

 

 

매크로 기능 등이 포함된 [개발 도구] 탭은 엑셀 프로그램을 처음 설치했을 때 표시되지 않으므로

엑셀에서 매크로 기능을 사용하기 위해서는 수동으로 표시해줘야 한다.

 

 

 

 

매크로 사용할 수 있도록 설정하기

 

 

[개발 도구] 탭을 표시해도 처음에는 '모든 매크로 제외'로 기본 설정되어 있어

매크로 기능을 사용하기 위해서는 사용할 수 있도록 따로 설정해줘야 한다.

매크로를 사용할 수 있는지 알아보는 방법은 매크로 기록을 이용하여

매크로를 만든 이후에 [디자인 모드] 명령이 활성화되는지 여부이다.

이때까지도 [디자인 모드] 명령이 비활성화된다면 매크로를 사용할 수 없는 상태다.

 

 

 

 

 

 

 

 

 

Posted by Y&S

댓글을 달아 주세요

 

 

 

학원에서 컴활2급 실기반을 가르친지도 벌써 2년이 넘어가고 있다.

10년 전부터 가르쳐오긴 했지만 개인 지도를 주로 해왔기에 그때는 단체반과는 가르치는 것이 다를 수밖에 없었다.

개인 지도야 학생의 습득 속도에 따라 속도가 달라지고, 학생의 컴활 능력 수준에 따라 가르치는 내용 또한 달라지니까~

 

그런데 단체반을 오래 지도하다보니 수준 차이가 큰 학생들을 모두 만족시킬 수 있는 일종의 노하우? 학습 가이드가 생겼달까?

나름 쉽게~쉽게~가르치는 데 포커스를 맞추다보니 결국 쉬운 설명과 반복 학습이 최고라는 걸 깨닫고, 1달 안에 '엑셀 기본서+함수 실습서+컴퓨터활용능력 2급 실기 기본서'의 순서대로 가르치는 나만의 커리큘럼이 만들어졌다.

엑셀의 기본 개념을 잡은 후 함수 문제에 익숙해지고, 가장 마지막에 실기 기본서로 문제에 익숙해지는 방법으로 '엑셀'과 '자격증' 모두 손에 쥘 수 있기 때문이다.

 

그러던 올해 초..겨울 방학을 맞아 자격증을 따놓으려는 대학생들에게 개인 교습을 하면서 똑같은 말을 반복하는게 힘들어 강의 내용을 동영상 강좌를 만들어 저장해놓으려고 야심차게 헤드셋과 컴퓨터까지 장만했다가 기관지염에 걸리는 바람에 목이 완전히 맛이 가서 포기하게 되었다 ㅠ

그 다음에는 동영상은 포기하더라도 문서로라도 만들어놓자고 시도했다가 너무 많은 양에 다시 좌절 ㅠ

아무래도 쉬운 말과 예시로 설명하는 내용이라 문서화하기에도 적당치 않기도 하고,

결국 수업 내용은 녹화를 하든지 해서 나중이라도 한 번은 꼭 동영상화하는 수밖에 없어보임.

 

 

--------------------------------------------------------------------------------------------------------------------------------------------

 

 

대신 수업하면서 학생들이 놓치기 쉬운 부분이거나 중요한 내용만 모아 '컴활2급 실기 시험시 유의 사항.hwp' 파일을 만들어보았다. 일단은 수업이 끝난 학생들에게 나눠주기 위해 만든 파일이지만 혼자 공부해서 자격증을 따는 분들에게도 도움이 되지 않을까나?

올리는 김에 예전에 만든 함수 총정리 파일과 단축키 파일도 같이 올린다.

두 파일의 자세한 설명을 보려면 클릭~!

엑셀 함수 총정리 파일 (컴활, ITQ 대비) by Y

엑셀 단축키 모음 파일 (자주 사용하는 단축키 위주) by Y

 

 

 

 컴활2급 실기용 파일

컴활2급 실기 시험시 유의 사항.hwp

엑셀_단축키_모음.xlsx 

함수_총정리.xlsx 

 

 

 

 

 

 

컴활2급 실기 시험시 유의 사항 파일은 한글 문서로 총 3페이지로 구성되어 있다.

1페이지는 컴활2급 실기에 대한 전반적인 유의점이다.

 

 

 

이어서 총 4작업을 기준으로 시험시 유의점과 각 기능 설명글을 링크해놓았다.

 

 

 

 

3페이지의 매크로 링크 글은 시험 유형과는 다르므로 기본서로 필히 공부해야 한다.

 

 

이 파일은 배포용 문서로 인쇄는 할 수 있지만 내용을 수정할 수는 없어용~

그럼 열공하시길~!!

 

 

 

 

 

 

 

Posted by Y&S

댓글을 달아 주세요

 

 

 

관련글 

외부 데이터 가져오기 1 (웹 데이터 엑셀로 가져오기) by Y 

외부 데이터 가져오기 2 (한글 문서를 엑셀로 가져오기) by Y

 

 

메모장은 다른 프로그램과 호환이 거의 100% 가능한 프로그램이기 때문에

우편번호 파일처럼 내용이 많은 데이터를 다양한 용도로 사용할 수 있도록 배포하는 경우 등에 많이 사용된다.

 

여기에서는 아래 그림과 같이 메모장에 작성한 우편번호 파일 엑셀로 가져와보자.

참고로 컴활2급 실기에서는 기본작업의 3번 문제에 외부데이터 가져오기 중에서 메모장(텍스트) 파일을 가져오는 문제가 랜덤으로 출제된다.

 

 

 

 

 

예제 파일과 완성 파일 

 zipcode.txt 

완성.xlsx

 

 

 

 

메모장 파일을 엑셀로 가져오기

 

 

 

1. 아래와 같은 메모장 파일이 있을 경우 이 파일이 위치한 경로를 미리 알아둬야한다.

 

컴활2급 실기에서는 C 드라이브의 OA 폴더 안에 위치해있다.

 

 

 

 

2. 엑셀로 위의 파일을 가져오기 위해 [데이터] 탭의 '외부 데이터 가져오기' 그룹의 [텍스트] 명령을 클릭한다.

 

 

 

 

3. [텍스트 파일 가져오기] 창이 나타나면 메모장 파일이 위치한 경로를 찾아 파일을 선택한 후 [가져오기] 버튼을 클릭한다.

 

 

 

 

4. [텍스트 마법사] 창 나타나고 마법사 3단계 중에서 1단계인 메모장 파일이 어떤 식으로 입력되어 있는지를 선택한다. 여기에서는 텍스트 파일 미리보기 부분에서 데이터가 세로로 너비를 딱딱 맞춰 입력된 것이 아니라 데이터 사이마다 탭(조그만 동그라미 표시)이 입력되어 있는 것을 볼 수 있다.

 

이처럼 공백, 쉼표(,), 샵(#), 탭 등의 구분 기호로 분리된 경우가 99%이므로 '구분 기호로 분리됨'을 대부분 선택한다.

 

 

 

 

5. 2단계는 앞에서 선택한 내용에 따라 달라진다.

'구분 기호로 분리됨'을 선택한 경우 아래와 같이 무슨 구분 기호인지를 선택하는 화면이 나타난다.

여기에서는 '탭'이 선택된 상태에서 [다음] 버튼을 클릭한다.

 

 

 

 

6. 마법사의 마지막 단계는 각 데이터의 표시 형식을 지정하는 부분이다.

딱히 따로 지정할 서식이 없다면 [마침] 버튼을 클릭하면 된다.

 

만약에 데이터 중에 가져오지 않을 필드가 있다면 하단의 미리보기에서 필드를 선택한 후 '열 가져오지 않음(건너뜀)'을 선택하면 해당 필드는 건너뛰고 가져오게 된다.

 

 

 

 

7. [데이터 가져오기] 창에서는 외부데이터를 가져올 위치를 기존 워크시트의 원하는 셀을 선택하거나, 새 워크시트를 선택할 수 있다. '새 워크시트'를 선택했을 경우에는 새로 추가된 시트에 데이터를 가져올 수 있다.

여기에서는 현재 시트의 'A1' 셀을 선택한 후 [확인] 버튼을 클릭한다.

 

 

 

 

8. 현재 시트의 A1 셀부터 데이터가 불러와진 것을 볼 수 있다.

 

열 너비의 경우 데이터의 길이에 따라 자동으로 조정되므로 따로 조절할 필요는 없다.

 

 

 

 

 

 

 

Posted by Y&S

댓글을 달아 주세요

 

 

 

ITQ 자격증은 실기만 합격해도 자격증이 나오는 누구나 응시할 수 있는 국가공인자격증이다.

ITQ 자격증에 대한 자세한 설명은 아래 글을 참조해보자.

 

관련글  ☞  2013/04/18 - '누구나 응시 가능한' 컴퓨터 자격증 정리 by Y

 

 

ITQ 과목 중에서 엑셀, 파워포인트, 한글의 세 과목은 학교나 회사에서 자주 사용되는 프로그램이므로 학생은 점수를 위해, 직장인들은 OA 능력을 인정받기 위해 많이 따는 편이다. (인터넷과 엑세스는 다른 3개의 프로그램만큼 자주 사용되는 프로그램이 아니므로 따는 사람이 드물다) 게다가 다른 자격증과는 달리 필기 없이 실기만 따면 자격증이 나오므로 손쉽게 딸 수 있다는 장점이 있어 다른 자격증을 따기 전에 미리 따두기도 한다. 직장인들의 경우에는 바빠서 여러 번 시험 볼 시간이 없어 ITQ 엑셀, ITQ 파워포인트, ITQ 한글 세 개를 하루에 모두 시험보는 경우가 많다.

 

그래서 여기에서는 ITQ를 준비하면서 조금이라도 도움이 되었으면 하는 바램으로 ITQ 엑셀, ITQ 파워포인트, ITQ 한글을 시험볼 경우 각각 처음에 공통적으로 풀어야하는 부분과 문제를 풀 때의 유의사항에 대해서 정리해보았다.

 

 

아래 파일은 공통 작업만 완성된 파일이다. 

 엑셀

 itq 엑셀.xlsx

 파워포인트

itq 파워포인트.pptx

 한글

itq 한글.hwp

 

 

 

 

ITQ 엑셀

 

 

 

전체 구성

ITQ 엑셀은 총 네개의 시트에 각각의 문제를 풀어야한다.

'제1작업' 시트 - 데이터와 도형 등을 입력하고 조건부 서식을 포함한 서식을 지정해야하며 총 6개의 함수를 풀어야한다.

'제2작업' 시트 - 고급 필터, 고급 필터+목표값 찾기, 자동 필터 중 한 가지가 나온다.

'제3작업' 시트 - 부분합, 피벗테이블 중 한가지가 나온다.

'제4작업' 시트 - 차트 문제가 나온다.

 

 

TIP

'제1작업'의 함수 부분은 나머지 작업에 포함되지 않으므로 함수 문제는 가장 마지막에 풀어도 된다.

'제2작업'은 정답이 표시되지 않지만, '제3작업', '제4작업'은 정답이 문제에 표시되므로 문제와 정확히 일치하는지 꼼꼼하게 확인해야 한다.

 

 

공통부분작업

1. 새로운 파일에서 시트 이름을 각각 '제1작업', '제2작업', '제3작업'으로 변경한 후 3개의 시트를 모두 선택한다.

(모든 시트를 선택하기 위해 끝 시트가 선택된 상태에서 처음 시트를 Shift 키를 누른 상태에서 클릭하면 모든 시트탭이 하얀색으로 반전되는데 이때 모든 시트가 선택됐다는 의미다)

이렇게 모든 시트를 선택하면 이후에 하는 모든 작업은 모든 시트에 다 적용된다.

 

 

 

 

 

2. A열의 너비를 조정하기 위해 A열 머리글의 바로가기 메뉴에서 [열 너비]를 클릭한다.

 

 

 

 

3. 열너비에 1을 지정한다.

 

 

 

 

4. A열 머리글 왼쪽 부분을 클릭하여 시트 전체를 선택한 후 [홈]-[가로 가운데 정렬]을 지정한다.

그리고 글꼴이나 글꼴 크기를 문제에 제시한대로 변경한다.

 

 

 

 

5. 그리고 시트의 그룹을 해제하기 위해 '제1작업' 시트탭을 클릭한 후 문제를 보고 데이터 입력을 시작한다.

 

 

 

 

 

ITQ 파워포인트

 

 

 

 

전체 구성

ITQ 파워포인트는 마스터 작업 후에 총 6개의 슬라이드를 작성해야 한다.

1. 제목 슬라이드 - 도형의 점편집, 워드아트, 그림 삽입이 나온다.

2. 목차 슬라이드 - 도형과 하이퍼링크가 나온다.

3. 텍스트 슬라이드 - 텍스트 입력/편집과 동영상 삽입이 나온다.

4. 표 슬라이드 - 표 입력/편집과 도형이 나온다.

5. 차트 슬라이드 - 차트가 나온다.

6. 도형 슬라이드 - 도형, 스마트아트와 애니메이션이 나온다.

 

 

TIP

ITQ 파워포인트는 난이도가 높다기보다는 1시간 안에 작성해야 하는 양이 많은 편이므로 반복된 연습으로 시간을 단축해야 한다.

 

 

공통부분작업

1. 새 파일에서 1번 슬라이드를 클릭한 후 [Enter] 키를 다섯 번 누르면 '제목 및 내용' 레이아웃의 슬라이드가 5개 추가된다.

 

 

 

 

2. [디자인]-[페이지 설정]을 클릭한다.

 

 

 

 

3. [페이지 설정] 창이 나타나면 '슬라이드 크기'를 'A4 용지'로 지정한다.

 

 

 

 

4. [보기]-[슬라이드 마스터] 명령을 클릭하여 슬라이드 마스터 편집 화면으로 이동한다.

 

 

 

 

5. 마스터 편집 화면에서 1번 슬라이드에서 작업해야 모든 레이아웃에 적용되므로 1번 슬라이드를 선택한다.

 

 

 

 

6. 이제 제목, 하단 로고, 슬라이드 번호의 세 가지를 작성한다.

이 부분은 문제에 따라 다르므로 여기에서는 임의의 문제를 풀어보았다.

 

 

 

 

7. [슬라이드 마스터]-[마스터 보기 닫기] 명령을 클릭하여 마스터 편집 상태에서 빠져나온다.

 

 

 

 

8. 1번 슬라이드를 클릭하여 선택한 후 [디자인]-[배경 그래픽 숨기기]를 체크한다.

 

 

 

 

9. 이제 1번 슬라이드에서 제목과 부제목을 지운 후 문제를 보고 슬라이드 작성을 시작한다.

 

 

 

 

 

ITQ 한글

 

 

 

 

전체 구성

ITQ 한글은 총 3페이지의 내용을 작성해야 한다.

1 페이지 - 1번 문제에는 스타일, 2번 문제에는 표, 차트가 나온다.

2 페이지 - 3번 문제에는 수식, 4번 문제에는 그림, 글맵시, 그림 삽입 문제가 나온다.

3 페이지 - 문서 작성 문제가 나온다.

 

 

TIP

ITQ 파워포인트와 마찬가지로 난이도보다는 입력해야 하는 양이 많은 편이므로 타수가 빠를수록 유리하다.

문제에서 [출력 형태]라고 나온 부분만 입력해야 하고, 1~4번의 각 문제 사이에는 한 줄씩 띄워준다.

 

 

공통부분작업

1. [F7] 키를 눌러 [편집 용지] 창을 연 후 위쪽, 아래쪽, 머리말, 꼬리말은 10mm, 왼쪽, 오른쪽은 11mm로 지정한다.

빠르게 입력하려면 [Tab] 키를 여러 번 눌러 위쪽 여백에 커서를 위치시킨 후 '10'을 입력하고 다시 [Tab] 키를 눌러 다음 칸으로 이동한 후 같은 방법으로 차례대로 입력하면 된다.

 

 

 

 

 

2. 이제 [Shift]+[Alt] 키를 누른 상태에서 [Enter] 키를 두 번 눌러 3 구역으로 나눈다.

페이지가 아닌 구역으로 나누는 이유는 3페이지에 작성할 페이지 번호가 1, 2 페이지에 나타나지 않도록 하기 위해서다.

 

이제 1 페이지로 커서를 이동한 후 입력하기 시작하면 된다.

 

 

 

 

 

 

 

 

Posted by Y&S

댓글을 달아 주세요

 

 

 

엑셀에서 이름은 셀 주소나 범위 주소를 의미한다.

셀이나 범위에 한글로 된 의미있는 이름을 지정하면 더 편하게 셀이나 범위를 참조할 수 있다.

예를 들어, 사원명이 입력된 셀의 이름을 '사원명'이라고 지정하면

이 셀을 참조하는 함수, 유효성 검사 등에서 셀 주소를 몰라도 쉽게 셀을 참조할 수 있게 된다.

여기에서는 범위의 이름을 정의하고, 함수에서 활용하고, 이름을 삭제하는 방법에 대해 알아보자.

 

이름 정의 방법

1. 셀 이름 정의

    - 셀 선택 후 [이름 상자]에 이름 입력한 후 [Enter] 누르기

2. 범위 이름 정의

    - 셀 선택 후 [이름 상자]에 이름 입력한 후 [Enter] 누르기

    - 범위 지정 후 [수식]-[이름 정의]

3. 범위의 첫 행을 이름으로 자동으로 정의하기

    - 범위 지정 후 [수식]-[선택 영역에서 만들기]

 

이름 삭제 방법

[수식]-[이름 관리자]에서 삭제할 이름을 선택한 후 '삭제'

 

 

 

아래의 예제 파일을 다운받아 연습해보자.

 예제 파일.xlsx

 

 

 

 

이름 정의하기

 

 

 

 

1. 아래 범위의 이름을 '찾기참조'로 지정해보자.

 

 

 

 

2. 범위를 지정한 후 이름상자에 '찾기참조'를 입력한 후 [Enter]를 누른다.

마지막에 [Enter]를 누르지 않으면 이름이 바뀌지 않는다는 점~꼭꼭 기억하자!

 

 

 

 

3. 표에서 각각의 열을 따로따로 '사번', '이름', '부서명' 등으로 이름을 지정해보자.

 

 

 

 

4. 표 범위를 모두 지정한 후 [수식]-[선택 영역에서 만들기]를 클릭한다.

 

 

 

 

5. 창이 열리면 '첫 행'이 선택된 상태에서 [확인] 버튼을 클릭한다.

선택된 범위의 첫 행인 제목을 각각의 열범위의 이름으로 사용한다는 의미다.

 

 

 

 

6. 이제 범위를 지정하면 변경된 이름을 이름 상자에서 확인할 수 있다.

 

 

 

 

 

이름 활용하기

 

 

 

1. AVERAGEIF 함수를 이용하여 총무부 직원들의 직무수행능력 점수의 평균을 구해보았다.

이름을 정의하지 않았다면 '=AVERAGEIF(D5:D18,"총무부",F5:F18)'와 같이 구해진다.

 

하지만, 이름을 사용하면 '=AVERAGEIF(부서명,"총무부",직무수행능력)'으로 구할 수 있다.

이렇게 식을 구하면 편하기도 하거니와 다른 사람이 보기에도 알기 쉽다는 장점이 있다.

 

 

AVERAGEIF 함수를 모른다면 함 보자~!!  엑셀 함수 11 (SUMIFS, AVERAGEIFS) by Y

 

 

 

 

2. 데이터를 찾는 VLOOKUP 함수를 이용하여 박영훈의 이해판단력 점수를 찾아보았다.

식은 '=VLOOKUP("박영훈",C5:G18,5,0)'와 같이 사용하였다.

 

VLOOKUP 함수를 모른다면 함 보자~!! 엑셀 함수 4 (찾기/참조 함수) by Y

 

 

 

 

 

이름 삭제하기

 

 

 

1. [수식]-[이름 관리자]를 클릭한다.

 

 

 

 

2. 이름 목록이 나타나면 지울 이름을 선택한 후 [삭제] 버튼을 클릭한다.

 

 

 

 

 

 

 

 

 

Posted by Y&S

댓글을 달아 주세요

 

 

 

시나리오는 수식에 영향을 주는 값이 변할 경우 수식의 결과가 어떻게 바뀌는지 보여주는 가상 분석 기능이다.

이때, 값이 변하는 셀은 '변동셀', 변하는 값에 영향을 받는 수식셀은 '결과셀'이다.

같은 기능을 하는 '데이터 표'의 경우 값이 다양하게 변하는 많은 경우의 결과를 볼 수 있다는 점이 시나리오와 다르다.

 

관련글    엑셀 가상 분석 기능 '데이터 표' by Y

 

 

 

 

시나리오 작성 순서

 

 

1. 셀 이름 미리 정의하기 

이유 : 셀 이름을 미리 정의하지 않으면 시나리오 결과에서 변동셀과 결과셀이 $C$3 등으로 지저분하게 표시되기 때문이다.

2. [데이터]-[가상 분석]-[시나리오 관리자]를 실행한다.

3. [추가] 버튼으로 시나리오를 원하는 개수만큼 추가해준다.

이때 시나리오 이름, 변동셀, 변동값 등을 지정해줄 수 있다.

4. [요약] 버튼을 눌러 시나리오 요약을 작성한다.

이때 결과셀을 지정할 수 있다.

 

 

 

 

아래의 예제파일을 다운받아 연습해보자.

 

 시나리오예제파일.xlsx

 

예제 파일은 [예제], [활용], [문제]의 3개의 시트로 되어 있다.
[예제] 시트는 따라하기 예제이고,
[활용], [문제] 시트 2개는 혼자서 연습해보기 위한 예제이다.

 

 

 

 

시나리오 연습

 

 

'예제' 시트에는 사과와 배의 개수를 합한 값이 C4셀에 구해져 있다.

사과 개수가 5로 증가하거나 1로 감소하면 과일의 총개수가 어떻게 변하는지 시나리오로 분석해보자.

이때 시나리오는 '증가', '감소'의 2개의 시나리오를 만들어야 한다.

 

 

 

 

1. 먼저 변경셀인 C2, 결과셀인 C4의 이름을 각각

'사과개수', '과일총개수'로 지정한다.

 

*** 이름 상자에 이름을 입력한 후 꼭 [Enter] 키를 눌러야 제대로 바뀐다 ***

 

 

 

 

2. [데이터] 탭의 [가상 분석]-[시나리오 관리자]를 클릭한다.

 

 

 

 

3. [시나리오 관리자] 창이 나타나면 시나리오를 추가하기 위해 [추가] 버튼을 클릭한다.

 

 

 

 

4. [시나리오 추가] 창이 나타나면 시나리오 이름에 '증가'를 입력하고,

변경셀에는 변동되는 값인 'C2'를 클릭하여 선택한다.

 

 

 

 

 

5. 변동값을 입력하는 창이 나타나면 '5'를 입력한다.

 

 

 

 

6. '증가' 시나리오가 추가되면 같은 방법으로 '감소' 시나리오도 추가한다.

'감소' 시나리오는 위의 4~5번과 동일하게 만드는데 변동값만 '1'로 지정해준다.

 

 

 

 

7. 이제 시나리오 결과를 보기 위해 [요약] 버튼을 클릭한다.

 

 

 

 

8. [시나리오 요약] 창이 나타나면 '결과 셀'에 'C4'셀을 지정해준다.

 

 

 

 

9. 이제 '예제' 시트 앞에 '시나리오 요약' 시트가 추가된다.

시나리오는 '현재 값' 오른쪽에 표시되어 각각 결과를 확인해볼 수 있다.

 

 

 

 

 

 

 

 

Posted by Y&S

댓글을 달아 주세요

 

 

 

이전 엑셀 강좌에서 동적인 셀을 참조하기 위한 INDIRECT 함수에 대해서 알아보았었다.

관련글 ☞ 엑셀 함수 22 - INDIRECT 함수 (동적인 셀 참조하기) by Y

 

 

이번에는 INDIRECT 함수처럼 동적인 셀을 참조할 수 있는 또다른 참조 함수 OFFSET 함수에 대해 알아보자.

 

 

 

OFFSET(참조, 행, 열, [높이], [너비])

참조 영역에서 행과 열만큼 떨어진 위치의 참조 영역을 돌려주는 함수다.

참조는 셀 주소이고, 행/열/높이/너비는 숫자이거나 숫자가 입력된 셀이다.

 

행/열 - 행은 아래로, 열은 오른쪽으로 지정된 숫자(행/열)만큼 떨어진 위치의 참조 영역을 돌려주게 된다.

높이/너비 - 참조 영역의 높이(행 수)와 너비(열 수)를 지정할 수 있다.

 

 

 

 

 

OFFSET 함수의 행/열이란??

 

 

 

아래와 같이 데이터가 입력되어 있을 때

'=OFFSET(B2,2,0)' 식을 입력하면

B2 셀에서 행이 2번 이동된 셀인 B4셀이 식의 결과가 된다. 

 

 

 

 

'=OFFSET(B2,0,3)' 식은 B2 셀에서 열이 3번 이동된 셀인

E2 셀이 결과로 출력된다. 

 

 

 

 

'=OFFSET(B2,4,2)' 식은 B2 셀에서 행이 4, 열이 2만큼 떨어진 위치인

D6 셀이 결과로 출력된다.

  

 

 

 

 

 

OFFSET 함수의 높이/너비란?

 

 

 

 

'=OFFSET(C2,0,0,3,1)' 식은 C2 셀에서부터

높이(행)이 3칸, 너비(열)이 1칸인 범위를 의미하므로

'C2:C4' 참조 범위를 반환한다.

 

그러므로 '=SUM(OFFSET(C2,0,0,3,1))' 식은

'=SUM(C2:C4)' 식이 되어 답은 6이 된다. 

 

 

 

 

 

 

OFFSET 함수 활용

 

 

 

아래의 표에서 놀이공원의 섹션별로 평균방문자수를 구하고자한다.

이때 지정된 일수만큼만 구하는 식을 OFFSET 함수를 이용하여 두 가지 방식으로 구해보겠다.

예를 들어, 어드벤처는 8월 1일부터 4일까지의 방문자수의 평균만 구해져야한다.

 

 

 

1. =AVERAGE(C2:OFFSET(C3,C12-1,0))

 

'OFFSET(C3,C12-1,0)' 식은 'OFFSET(C3,4-1,0)' 식과 같다.

즉, C3 셀에서 3(4-1)만큼 아래로 이동한 셀 C6셀이 구해진다.

그럼 결과적으로 '=AVERAGE(C2:C6)'과 같으므로 4개 숫자의 평균이 구해진다.

 

 

2. =AVERAGE(OFFSET(C3,0,0,C12,1))

 

'OFFSET(C3,0,0,C12,1)' 식은 'OFFSET(C3,0,0,4,1)' 식과 같다.

즉, C3 셀에서부터 높이(행)가 4, 너비(열)가 1인 'C3:C6' 범위가 구해진다.

결국 1번의 경우와 마찬가지로 '=AVERAGE(C2:C6)'의 식이 되어 평균이 구해진다.

 

 

 

그리고 식을 오른쪽으로 수식 복사하면 일수(C12:E12) 값이 변함에 따라

각기 다른 일수만큼의 방문자수평균을 구하는 동적인 식이 구해진다.

이처럼 OFFSET 함수를 이용하면 다른 함수와 함께 사용하여 동적인 참조가 가능해진다.

 

 

 

 

 

 

 

 

 

Posted by Y&S

댓글을 달아 주세요

 

 

 

데이터 표 기능은 목표값 찾기, 시나리오 분석과 함께 데이터 변화를 파악할 수 있는 가상 분석 기능이다.

예를 들어, 적금을 넣을 때 매달 납입 금액과 이율이 변하면 내가 받는 금액이 어떻게 바뀌는지를

데이터 표 기능을 사용하면 한 눈에 파악할 수 있다.

 

 

 

 

[데이터 표] 작성 순서

 

1. 수식 입력하기

변하는 값이 두 개일 경우(행/열) - 두 값의 사이의 셀에 수식을 입력해준다.

변하는 값이 한 개일 경우(열) - 오른쪽 상단 셀에 수식을 입력해준다.

2. 수식과 변하는 값을 모두 드래그하여 범위를 선택한다.

3. [데이터]-[데이터 도구] 그룹의 [가상 분석]-[데이터 표] 명령을 실행한다.

4. 행과 열 입력란에 실제 수식에서 사용된 값을 각각 지정해준다.

 

행과 열은 아래 그림과 같다. 

 

 

 

아래의 예제 파일을 다운받아 연습해보자.

 

데이터표예제.xlsx 

 

예제 파일은 [예제1], [예제2], [예제3]의 3개의 시트로 되어 있다.

 [예제1], [예제2] 시트 2개는 따라하기 예제이고,

[예제3] 시트는 혼자서 연습해보기 위한 예제이다.

 

 

 

 

행과 열이 있을 경우 [데이터 표] 따라해보기

 

 

 

단가, 수량을 이용하여 매출액(단가*수량)이 이미 구해져있다.

이럴 때 단가와 수량이 다양하게 바뀌면 매출액이 어떻게 바뀌는지

[데이터 표] 기능을 이용하여 쉽게 구해보자.

 

 

 

 

1. F3 셀에 수식을 입력하기 위해 '=C4'를 입력한다.

C4 셀을 참조하여 식을 복사한 것과 마찬가지가 된다.

 

 

 

 

2. 복사한 수식과 변하는 값들을 모두 선택한 후

[데이터]-[가상 분석]-[데이터 표] 명령을 실행한다.

 

 

 

 

3. [데이터 표] 창이 나타나면 여기에서는 수량이 행, 단가가 열이므로

실제 수식에서 사용된 수량과 단가를 각각 행, 열로 지정한다.

 

쉽게 데이터 표에서 가로는 행, 세로는 열이라고 기억해두면 된다.

 

 

 

 

4. 수량과 단가의 변화에 따른 매출액을 한 눈에 확인할 수 있다.

 

 

 

 

 

열만 있을 경우 [데이터 표] 따라해보기

 

 

 

이번에는 단가의 변화에 따른 매출액을 구해보자.

하나의 값만 변할 경우 주로 세로(열)로 작성해놓는다.

 

 

 

 

1. G3 셀에 수식을 복사해놓는다.

값이 열만 있을 경우에는 F3 셀이 아닌 C4 셀에 수식을 입력해야 한다.

 

 

 

 

2. 수식과 변하는 값을 포함하는 범위를 지정한 후

[데이터 표] 명령을 실행한다.

 

 

 

 

3. 행은 입력할 값이 없으므로 비워놓고,

열에만 단가 값을 선택한다.

 

 

 

 

4. 단가가 변화함에 따라 변하는 매출액을 확인할 수 있다.

 

 

 

 

 

 

 

Posted by Y&S

댓글을 달아 주세요

 

 

 

블로그의 유입 경로를 보니 '모든 셀에 같은 수 더하기'가 있었다.

그래서 '모든 셀에 같은 수를 더하려면 어떻게 해야할까'를 생각하다가 호기심에 엑셀을 실행하고 잠시 멍~

머리속에 떠오르는 거라고는 참조와 함수, 식을 이용한 복잡한 방법밖에는 없었다.

겨우 아주 쉬운 방법~선택하여 붙여넣기 기능이 생각났는데,

아마도 사람의 두뇌는 아는 지식 중에서 어려운 방법을 먼저 도출해내는 듯하다;;

 

 

수많은 데이터에 모두 같은 수를 더해 업데이트를 해야하는 경우

'Ctrl + Enter'를 이용한 입력선택하여 붙여넣기의 '연산' 기능을 이용하면 된다.

선택하여 붙여넣기의 단축키는 Ctrl + Alt + V이다.

 

 

아래 표에서 단가에는 일괄적으로 1000을 더하고

생산량은 일괄적으로 두배로 수정해보자.

 

 

 

 

단가에 더할 1000을 아무데나 입력해보자.

1. 6개의 셀을 범위로 지정한다.(단가가 6개의 셀이므로)

2. 1000을 입력한다.

3. Ctrl + Enter 를 누른다.

 

*** 물론 그냥 입력해도 되지만, 많이 입력해야할수록 아래 방법이 편리하다 ***

 

 

 

 

입력된 숫자를 복사한다 (Ctrl + C 키를 눌러도 된다)

 

 

 

 

단가의 첫 번째 셀에 [선택하여 붙여넣기]한다 (Ctrl + Alt + V 키를 눌러도 된다)

 

 

 

 

[선택하여 붙여넣기] 창이 열리면 '연산'의 '더하기'를 체크하고 [확인] 버튼을 클릭한다.

 

 

 

 

아래와 같이 단가가 1000씩 증가한 걸 볼 수 있다.

 

 

 

 

위와 같은 방법으로 아무데나 2를 6개 입력하여 복사한 후

생산량의 첫 번째 셀에서 [선택하여 붙여넣기]한다.

 

 

 

 

[선택하여 붙여넣기] 창이 열리면 '연산'의 '하기'를 체크하고 [확인] 버튼을 클릭한다.

 

 

 

 

아래와 같이 생산량이 2배로 증가한다.

 

 

 

 

 

 

 

 

 

 

Posted by Y&S

댓글을 달아 주세요

 

 

 

INDIRECT 함수는 텍스트로 지정한 셀 주소를 반환하는 함수다.

예를 들어 '=INDIRECT("A3")'은 '=A3'을 의미한다.

그럼 '그냥 A3을 참조하면 될 것을 왜 이렇게 복잡한 방법으로 참조하지?' 하는 의문이 생길 것이다.

이 함수는 혼자 쓰이지 않으며 다른 찾기/참조 함수와 같이 쓰여, 동적(변하는)인 셀 참조를 가능하게 해준다.

예를 들어, INDEX, MATCH, CHOOSE, VLOOKUP, HLOOKUP, LOOKUP 과 같은 값을 찾을 경우 주로 사용하는 함수와 같이 쓰여서 동적으로 변하는 조건에 따른 정확한 값을 찾게 해준다는 것이다.

 

 

예제 파일과 완성 파일

 

성적표.xlsx 

성적표완성.xlsx

 

 

 

 

INDIRECT 함수 기초

 

 

 

=INDIRECT("셀 주소", [옵션])

셀 주소는 텍스트 문자열 형태로 A1 스타일 또는 R1C1 스타일의 셀주소를 의미한다.

옵션은 생략하거나 TRUE 값이면 A1 스타일을 사용한다는 의미이고,

FALSE 값이면 R1C1 스타일을 사용한다는 의미이다.

 

A1 스타일? R1C1 스타일?

A1 스타일의 셀 주소는 널리 사용되는 B5, M2와 같은 셀 주소 스타일이다.

RIC1 스타일은 ROW(행) 번호, COLUMN(열) 번호로 셀 주소를 참조하는 스타일을 의미한다.

예를 들어, 'R3C4'는 3행의 4열이므로 'D3' 셀을 의미한다.

 

 

INDIRECT 함수는 아래 두 가지 방법으로 사용할 수 있다.

 

 

 

 

INDIRECT에서 셀 주소가 진짜 셀 주소를 의미할 경우 문자열로 표시하기 위해 큰 따옴표(")를 사용하고,

셀 주소가 입력된 셀을 참조할 경우 큰 따옴표 없이 사용한다.

위의 예를 보면 'B9'셀에 입력된 식인 '=INDIRECT("B3")'은 '=B3'과 같으므로 '이순신'이 결과로 표시된다.

'B10'셀에 입력된 식인 '=INDIRECT(E3)'은 'E3' 셀에 입력된 값인 'B3'을 셀 주소로 사용한다는 의미이므로 '=B3'이 되어 역시 '이순신'이 결과로 표시되는 것이다.

 

 

 

INDIRECT 함수 활용

 

 

 

아래와 같이 '중간고사', '기말고사' 시트에 각각 학생들의 점수와 평균, 석차가 입력되어 있을 때 '학생별성적' 시트에서 '시험구분'과 '이름'을 지정하면 '국어', '영어', '수학', '평균', '석차'를 자동으로 찾아주는 예제를 만들어보겠다.

이 예제에서는 INDIRECT 함수와 함께 데이터 유효성 검사, VLOOKUP 함수가 사용되므로 모르겠다면 아래의 글을 함 보자~!!

 

엑셀 데이터 유효성 검사 실습 (조건부 서식 자동으로 지정하기) by Y 

엑셀 함수 4 (찾기/참조 함수) by Y

 

  

 

 

 

시험구분은 데이터 유효성 검사 기능을 이용하여 선택하도록 만들어보겠다.

C2 셀을 선택한 후 [데이터]-[데이터 유효성 검사] 명령을 실행하여 '제한 대상'은 '목록', '원본'에는 '중간고사, 기말고사'를 입력한다. '중간고사, 기말고사'는 시트 이름이므로 정확하게 입력해야 한다.

 

 

 

 

아래와 같이 데이터를 선택할 수 있게 되면 하를 선택하고, 이름에는 학생 이름 중에 아무 이름이나 입력한다.

물론 이름도 '시험구분'처럼 데이터유효성검사를 이용하여 선택해도 되지만, 여기에서는 직접 입력하도록 했다.

 

          

 

 

 

C4~C8 셀에 아래와 같이 식을 입력하면, 이순신의 중간고사 데이터가 모두 찾아진다.

 

 

 

 

사실 C4 셀에 입력된 식에서 빨간색으로 표시된 'C2' 부분 때문에 INDIRECT 함수를 사용한 것이다.

C2 셀의 값이 현재는 '중간고사'지만, '기말고사'가 될 수도 있다.

이 변하는 값을 INDIRECT 함수를 이용하면 모든 참조가 가능해지는 것이다.

이와 같이 참조에서 변하는 부분이 있다면 INDIRECT 함수를 사용하면 해결된다.

 

 

 

 

 

 

 

 

Posted by Y&S

댓글을 달아 주세요

 

 

 

이번 강좌에서는 매크로를 이용하여 원하는 조건의 데이터만 표시(필터링)한 후,

화면에 표시된 데이터에 한해서만 자동으로 계산되는 예제를 만들어보겠다.

 

 

 

 

아래 예제 파일로 같이 실습한 후 완성 파일과 비교해보자.

 

예제 파일과 완성 파일

 

예제 파일.xlsm 

완성 파일.xlsm

 

 

 

 

여기에서 사용되는 기능은 데이터 유효성 검사, 고급 필터, SUBTOTAL, 매크로 기능인데,

이 중 모르는 기능이 있다면 아래 글을 보고 미리 공부해두는 것도 좋을듯하다.

 

엑셀 함수 17 - SUBTOTAL 함수 (자동필터 결과 합하기, 일련번호 만들기 예제) by Y 

고급필터로 데이터 추출하기 (고급필터 조건 작성법) by Y

엑셀 매크로 실습 (데이터를 원하는 위치로 이동하기) by Y

엑셀 데이터 유효성 검사 실습 (조건부 서식 자동으로 지정하기) by Y 

 

 

 

 

 

데이터 유효성 검사

 

 

 

먼저 데이터 유효성 검사를 이용하여

부서를 선택할 수 있도록 만들어보겠다.

 

B6셀을 선택한 후 [데이터] 탭의 [데이터 유효성 검사] 명령을 클릭한다.

 

 

 

 

[데이터 유효성] 창이 나타나면 '제한 대상'에서 '목록'을 선택한 후

원본에 '총무부,인사부,기획실'을 입력한다.

 

*** 만약 입력할 데이터가 많다면 시트에 미리 입력해놓고 범위를 지정해도 된다. ***

 

 

 

 

이제 B6 셀에서 목록 버튼을 클릭하여 원하는 부서를 선택할 수 있다.

 

 

 

 

 

 

SUBTOTAL 함수

 

 

 

다음으로 SUBTOTAL 함수를 이용하여 화면에 표시되는

데이터의 각각의 평가점수를 평균 계산해보겠다.

 

F6 셀에서 '=SUBTOTAL('까지 입력하면 사용할 수 있는 함수 목록이 나타난다.

여기에서는 평균을 구하기 위해 '1 - AVERAGE'를 더블 클릭하여 선택한다.

 

 

 

 

'=SUBTOTAL(1'까지 입력되면 쉼표를 입력하고 '직무수행능력' 데이터를 범위 지정한다.

완성된 식은 'SUBTOTAL(1,F9:F22)'이다.

 

 

 

 

같은 방법으로 나머지 세 과목의 평균 점수를 구한 후

가운데 정렬하고 소수점 이하 한 자리까지만 표시한다.

 

 

 

 

 

 

고급필터와 매크로

 

 

 

이제 마지막으로 원하는 부서만 필터링하는 고급필터를 매크로를

이용하여 자동으로 실행되도록 만들어보겠다.

 

매크로 기록을 시작하기 위해 [개발 도구] 탭의 [매크로 기록] 명령을 클릭한다.

 

 

 

 

매크로 기록 창이 나타나면 매크로 이름과 바로 가기 키를 입력한 후 [확인] 버튼을 클릭한다.

 

 

 

 

데이터 범위를 모두 지정한 후 [데이터] 탭의 [고급 필터] 명령을 클릭한다.

 

*** 매크로 기록이 시작되면 모든 작업이 저장되므로 신중히 작업해야 한다. ***

 

 

 

 

고급 필터 창이 나타나면 '조건 범위' 입력란을 클릭한 후 [B5:B6] 범위를 선택하고 [확인] 버튼을 클릭한다.

 

 

 

 

고급 필터가 실행되면 할 일이 모두 끝났으므로 [개발 도구]로 돌아가 [기록 중지] 명령을 클릭한다.

 

 

 

 

 

 

완성된 결과 보기

 

 

 

이제 부서를 변경한 후 매크로 단축키로 지정한 Ctrl+R 키를 누르면

자동으로 필터링이 되어 해당 부서 데이터만 표시되고, 평균점수가 다시 계산된다.

 

 

 

 

필터링을 제거하고 모든 데이터를 표시하고 싶다면

[데이터]-[정렬 및 필터]-[지우기]를 클릭하면 된다.

 

 

 

 

동영상 강좌 보기

 

 

 

 

 

 

 

 

Posted by Y&S

댓글을 달아 주세요

 

 

 

데이터의 행열을 바꾸는 방법에는 [선택하여 붙여넣기]-[행/열 바꿈] 명령을 사용하는 방법과

Transpose 함수를 이용하는 방법의 두 가지 방법이 있다.

 

 

각 방법의 장점

행/열 바꿈 - 서식까지 복사되므로 따로 서식을 지정할 필요가 없다. 함수보다는 좀 더 쉽다.

Transpose 함수 - 원본값이 변경되면 자동으로 변경되므로 데이터 업데이트에 신경쓰지 않아도 된다.

 

두 가지 방법의 장점을 잘 살펴보고 각각 용도에 맞게 사용하면 좋을 듯하다.

(참고로 Transpose 함수는 컴활 1급 실기에 나오는 함수다.)

 

 

 

 

[선택하여 붙여넣기]-[행/열 바꿈] 사용하여 행열 바꾸기

 

 

복사할 범위를 지정한 후 Ctrl+C 키를 누른다.

 

 

 

붙여넣을 셀의 바로가기 메뉴에서 [선택하여 붙여넣기]를 클릭한다.

선택하여 붙여넣기의 단축키 Ctrl+Alt+V 를 눌러도 된다.

 

 

 

'행/열 바꿈' 옵션을 체크한 후 [확인]을 클릭한다.

 

 

 

아래와 같이 행열이 바뀌고 서식까지 모두 붙여넣기 된다.

 

 

 

 

 

Transpose 함수를 사용하여 행열 바꾸기

 

 

Transpose 함수 배열 함수이므로 사용법이 다른 함수와는 다르다.

 

Transpose 함수의 사용 순서

1. 범위 지정

2. '=transpose(원본범위)' 식 입력 후 Shift+Ctrl+Enter

 

 

 

값이 붙여질 범위를 미리 지정한다.

 

 

 

'=Transpose(B2:E7)' 식을 입력한 후 Shift+Ctrl+Enter 키를 누른다.

 

 

 

원본 범위의 값만 행열이 바뀌어 표시된다.

 

 

 

 

 

[선택하여 붙여넣기]를 이용하여 서식을 행열 바꾸어 붙여넣기

 

 

 원본을 복사한 후

붙여넣을 셀의 바로가기 메뉴에서 [선택하여 붙여넣기]를 클릭한다.

아니면 Ctrl+Alt+V키를 눌러도 된다.

 

 

 

붙여넣을 옵션으로 '서식'을 선택한 후 '행/열 바꿈'을 선택한다.

이렇게 설정하면 서식이 행열이 바뀌어 붙여넣기된다.

 

 

 

아래와 같이 서식이 행열이 바뀌어 붙여넣어진다. 

 

 

 

 

 

이제 원본을 변경해보면 Transpose 함수를 이용하여 붙여넣은 값만 자동으로 변경되는 것을 볼 수 있다.

 

 

 

 

 

 

 

 

 

Posted by Y&S

댓글을 달아 주세요

 

 

 

이전 글

엑셀 차트 만들기 1 - 차트 레이아웃, 차트 스타일 by Y

엑셀 차트 만들기 2 - 차트의 구성 요소 추가/삭제/편집 by Y

 

 

마지막으로 이번 강좌에서는 원하는 데이터만 골라 차트를 만들고, 차트 시트로 이동하고, 축 방향을 변경하고, 서식을 지정하는 등의 기능에 대해 알아보겠다.

첫 번째 차트 만들기 강좌에서 다운받은 파일의 '고급' 시트의 데이터를 이용하여 만들면 된다.

 

 

 

아래와 같은 데이터에서 '영업1부'에 해당하는 사원의 목표액, 달성액을 차트로 만들어보자.

 

 

 

 

 

1. '영업1부' 데이터만 선택하기 위해 아래와 같은 순서로 선택한다.

 

*** 1번은 그냥 드래그하고, 2~6번까지는 Ctrl 키를 누른 상태에서 드래그한다 ***

 

 

 

 

 

2. '3차원 묶은 세로 막대형' 차트를 만든 후

'차트 레이아웃'은 '레이아웃3', '차트 스타일'은 '스타일48'을 선택한다.

마지막으로 제목을 '매출현황'으로 변경하면 아래와 같은 차트가 만들어진다.

 

 

 

 

 

차트 이동

 

 

 

 

3. [차트 도구]-[디자인] 탭의 가장 오른쪽을 보면 [차트 이동] 명령이 있다.

차트를 기존에 있는 워크시트로 이동하거나, 새 시트를 만들어 이동할 수 있는데

여기에서는 '새 시트'를 선택한 후 시트 이름을 '매출현황차트'로 입력했다.

 

결과적으로 '매출현황차트' 시트가 만들어지고 차트가 화면에 꽉차도록 표시된다.

 

 

 

 

 

가로 축 방향 바꾸기

 

 

 

 

4. [차트 도구]-[레이아웃] 탭의 [축]-[기본 가로 축]-[오른쪽에서 왼쪽으로 축 표시] 명령을 클릭한다.

 

 

 

 

 

5. 아래와 같이 가로 축이 오른쪽에서 왼쪽 방향으로 반대로 바뀌어 표시된다.

세로 축도 왼쪽에서 오른쪽으로 변경되어 표시된다.

 

 

 

 

 

 

차트 서식 꾸미기

 

 

 

 

6. 김남진의 목표액 계열만 눈에 띄도록 서식을 바꿔보자.

김남진의 목표액 계열만 선택하기 위해 한 번 클릭한 후 조금 있다가 다시 한 번 클릭한다.

 

 

 

 

 

7. [차트 도구]-[서식] 탭의 [도형 채우기]-[질감]에서 '꽃다발'을 선택한다.

 

 

 

 

 

8. 아래와 같이 선택된 계열만 서식이 변경되었다.

 

 

 

 

 

9. 같은 방법으로 [차트 도구]-[서식] 탭의 [도형 채우기] 명령을 이용하여 아래와 같은 차트를 완성한다.

 

뒷면 - [도형 채우기]-채우기 없음
밑면 - [도형 채우기]-흰색

차트 영역 - [도형 채우기]-[그라데이션]-어두운 그라데이션의 두번째(선형 아래쪽)

 

 

 

다 설명하지 못한 기능들도 있긴 하지만 웬만한 기능들은 담도록 노력했으니 잘 활용해보자!

 

 

동영상 강좌 보기

 

 

 

 

☞ 관련글

 

2013/03/18 - [말랑말랑 컴퓨터/엑셀 강좌&팁] - 엑셀 차트 만들기 (데이터 유효성 검사, VLOOKUP 함수) by Y

 

 

 

 

 

 

 

 

Posted by Y&S

댓글을 달아 주세요

 

 

 

이전 글  엑셀 차트 만들기 1 - 차트 레이아웃, 차트 스타일 by Y

 

이전 차트 만들기 초급에서는 두 가지 명령([차트 레이아웃], [차트 스타일])만 가지고 쉽게 뚝딱 차트를 만드는 방법에 대해 알아보았다. 이번 중급 과정에서는 [차트 도구]-[레이아웃] 탭을 이용하여 차트 구성 요소를 추가/삭제/편집하는 방법에 대해 알아보자.

 

 

차트의 구성 요소를 알고 있으면 편집이 쉬워진다.

  

 

차트 구성 요소

 

 

 

 

 

 

 이전 글에 첨부한 파일에서 '중급' 시트의 데이터를 이용하여 아래와 같은 차트를 작성해보자.

 

 

 

 

 

차트 만들기

 

 

 

 

1. 원산지, 수량, 수입금액의 데이터를 선택한 후

'묶은세로막대형' 차트를 삽입하도록 한다.

 

그럼 수량과 수입금액의 차이가 크기 때문에 수량 계열은 표시가 잘 안된다.

 

 

 

 

 

2. 이전 글과 같은 방법으로 [차트 도구]-[디자인]-[차트 레이아웃],

[차트 도구]-[디자인]-[차트 스타일] 명령을 이용하여

각각 '레이아웃9', '스타일 32'를 차트에 적용한다.

 

 

 

 

 

'수량' 계열 차트 종류를 변경하고 보조축으로 지정하기

 

 

 

보조축이란?

 계열끼리 값의 차이가 현저하게 클 경우 오른쪽에 세로축을 추가하여

추가된 축을 참조하도록 하는 것이다.

 

 

 

1. 먼저 '수량' 계열을 선택하려면 마우스로는 잘 선택이 안될 것이다.

이럴 경우 [레이아웃]이나 [서식] 탭에서 수동으로 선택할 수 있다.

 

 

 

 

 

2. 선택된 '수량' 계열의 바로가기 메뉴에서 [계열 차트 종류 변경] 메뉴를 클릭한 후

[표식 있는 꺽은선형] 종류를 선택하면 '수량' 계열만 차트 종류가 변경된다.

 

 

 

 

 

3. 수입금액과 값이 너무 차이가 나 표시가 잘 안되는 수량 계열을

보조축을 참조하도록 수정하여 눈에 잘 띄도록 수정해보자.

 

'수량' 계열의 바로가기 메뉴에서 [데이터 계열 서식] 메뉴를 클릭한 후

'데이터 계열'을 '보조축'으로 변경하면 오른쪽에 보조 세로축이 추가되고

'수량' 계열이 눈에 확 띄게 변경된다.

 

 

 

 

 

 

구성 요소 삭제/편집/추가

 

 

 

1. 차트의 구성 요소 중에서 제거하고 싶은 요소가 있을 경우

요소를 선택한 후 [Delete] 키를 누르면 된다.

여기에서는 세로축제목을 선택한 후 제거했다.

 

 

 

 

 

2. 편집하고 싶은 구성 요소가 있을 경우 [레이아웃] 탭에서

해당 구성 요소를 클릭하거나

바로가기 메뉴에서 [.... 서식] 메뉴를 이용하면 된다.

 

여기에서는 범례의 위치를 변경하기 위해 [차트 도구]-[레이아웃] 탭에서

[범례]-[위쪽에 범례 표시]를 클릭하여 위쪽으로 변경했다.

 

 

 

 

 

3. 보조 세로축의 숫자를 바꾸기 위해 바로 가기 메뉴의 [축 서식]을 클릭한 후

'최대값', '주 단위'를 각각 '80000', '20000'으로 변경하면

아래와 같이 보조축 눈금이 변경되어 '수량' 계열이 보기 좋게 변경된다.

 

 

 

 

 

4. '수입금액' 계열 중에서 '미국산' 계열에만 데이터 레이블을 추가해보자.

해당 계열을 한 번 클릭한 후 잠시 뒤에 다시 한 번 클릭하면 그 계열만 선택된다.

그 뒤 바로가기 메뉴에서 [데이터 레이블 추가]를 클릭하면 그 계열에만 '값' 레이블이 추가된다.

 

 

 

 

 

5. 이제 차트를 이동하고 크기를 변경하여 완성시킨다.

 

 

 

 이와 같이 [레이아웃] 탭과 서식 바로가기 메뉴를 잘 활용하면 각 구성 요소를 편집할 수 있다.

 

다음 강좌에서는 차트의 고급 기능에 대해 알아보도록 하자.

다음 글  엑셀 차트 만들기 3 - 데이터 선택, 차트 이동, 축 방향 변경 등 by Y

 

 

동영상 강좌

 

 

 

 

 

 

 

 

 

Posted by Y&S

댓글을 달아 주세요

 

 

 

엑셀에서 데이터를 이용하여 차트를 만들 경우

1. [차트]-[디자인] 탭의 [차트 레이아웃], [차트 스타일] 명령을 이용하여 쉽게 만들기

2. [차트]-[레이아웃] 탭의 기능을 이용하여 구성 요소를 자유자재로 추가/삭제/편집하기

3. 원하는 데이터만 이용하여 차트 만들기

 

난이도로 기준으로 위의 3가지 수준으로 나눌 수 있다.

 

이번 차트 만들기 강좌에서는 위의 3단계를 각각 초급, 중급, 고급으로 나누어 설명하고자 한다.

아래 예제 파일을 다운받은 후 초급부터 고급까지 따라해본다면 실무와 자격증 시험에 나오는 차트를 모두 작성할 수 있을 것이다.

 

 

 차트.xlsx

 

 

 

 

아래와 같은 표가 있을 경우 '제품코드, 제품명, 생산원가, 판매금액'

데이터만 이용하여 아래의 차트를 만들어보겠다.

 

 

 

 

 

 

쉬운 방법으로 차트 뚝딱 만들어보기

 

 

 

1. [C4:D10] 범위를 먼저 드래그한다.

[Ctrl] 키를 누른 상태에서 [F4:G10] 범위를 드래그한다.

 

*** Ctrl 키는 떨어져 있는 범위를 지정할 경우 사용한다 ***

 

 

 

 

 

2. 메뉴에서 [삽입] 탭을 누른 후 [세로 막대형]에서 [묶은세로막대형] 차트를 선택한다.

 

 

 

 

 

3. 시트에 아래와 같은 모양의 차트가 삽입된다.

 

 

 

 

 

4. 차트가 선택된 상태에서는 메뉴에 [차트 도구]가 자동으로 표시된다.

[디자인] 탭의 '차트 레이아웃'에서 [자세히] 버튼을 클릭한다.

그럼 '차트 레이아웃'이 모두 표시되는데 '레이아웃 9' 선택한다.

 

*** 차트 레이아웃은 차트의 구성 요소를 쉽게 지정할 수 있는 명령이다 ***

 

 

 

 

 

5. 이어서 [디자인] 탭의 '차트 스타일'에서 [자세히] 버튼을 클릭한다.

차트 스타일이 모두 표시되면 '스타일 48'을 선택한다.

 

*** 차트 스타일은 차트의 서식을 쉽게 지정할 수 있는 명령이다 ***

 

 

 

 

 

6. '차트 제목', '축제목' 부분을 한 번 클릭한 후 다시 한번 클릭하면

커서가 깜빡거리면서 글자를 입력할 수 있는 상태로 변한다.

원래 글자를 지우고 원하는 글자를 입력한다.

 

 

 

 

 

7. 이제 차트를 보기 좋게 이동하기 위해 아래처럼 원 부분을 클릭한 후 왼쪽으로 드래그한다.

이때, [Alt] 키를 누르고 이동하면 스냅 기능(자석 기능)이 적용되어 셀에 딱 맞게 위치시킬 수 있다.

 

 

 

 

 

8. 차트의 크기를 조절하기 위해 차트 오른쪽 하단 모서리를 클릭하고 드래그한다.

 

 

 

 

 

9. 아래와 같이 차트가 완성되었다.

 

 

 

이와 같이 [차트 도구]-[디자인] 탭의 '차트 레이아웃', '차트 스타일'의 두 명령을 이용하면

고급스러워 보이는 차트를 누구나 아주 쉽게 뚝딱 완성할 수 있다.

 

다음 강좌에서는 추가적인 구성 요소를 추가하고 편집하는 방법에 대해 알아보겠다.

다음 글  엑셀 차트 만들기 2 - 차트의 구성 요소 추가/삭제/편집 by Y

 

 

 

동영상 강좌

 

 

 

 

 

 

 

 

 

Posted by Y&S

댓글을 달아 주세요

 

 

 

엑셀에는 중요한 자료를 많이 넣어놓는 경우가 있어서

아무나 보지 못하도록 암호화 해야할 때가 있다.

 

 

 

 

아래와 같이 준비-> 문서암호화에서 사용 할 비밀번호를 넣어주면 된다.

(아래는 엑셀2007버전으로 2010에서는 파일->정보->통합문서보호->암호설정)

 

 

 

 

 

 

 

이렇게 사용할 암호를 두 번 입력하여 설정해주고 나면

반드시 파일을 다시 저장해줘야 설정한 암호가 적용된다.

 

 

 

설정한 암호를 변경하거나 해제할 시에도 마찬가지로

위의 경로에서 넣어놓은 비밀번호를 없애주거나 바꿔넣어주면 되는 것. 

물론 이 경우도 파일을 다시 저장을 해줘야 설정이 적용된다.

 

 

 

 


 

 

 

여기서 만일 다른이름으로 저장한다면

읽기와 쓰기 두가지를 나누어 암호를 설정할 수 있다.

 

 

다른이름 저장 시 하단 도구->일반옵션을 누르면

열기암호와 쓰기암호를 각각 지정해 줄 수 있는데

여기서 열기암호만 적어넣는다면 첫 번째 방법과 동일해진다.

 

 

 

 

 

 

 

 

 

만일 쓰기까지 암호를 지정해줬다면 열기암호로 문서를 열었어

아래처럼 쓰기암호를 다시 넣어야 엑셀파일을 변경할 수 있다.

(이때, 쓰기암호를 넣지않고 읽기전용으로 연다면 물론 파일변경이 안된다.)

 

 

 

 

 


 

 

 

그럼 시트보호는?

 

검토->시트보호에서 보호할 내용을 체크하고 암호를 넣어주면 된다.

(이건 편집에 관한 보호지 시트자체를 못 보게 하는건 아니다.)

 

시트해제 시 암호를 적어 넣을때에도 마찬가지로

검토->시트보호에서 암호를 넣어주면 된다.

 

 

 

 

 

 

 

 

근데 만일 자신이 설정해 놓은 암호를 잃어버려서 엑셀파일을 열 수 없다면 골치 아파진다.

 

암호를 푸는 프로그램들이 많이 있기는 하지만 대부분 유료이고

본인이 이것저것 실험해 본 결과 암호가 적은자리수의 간단하다면 그나마 금방 풀리는데

5자리를 넘어가면서 특수문자가 들어가는등 복잡해지면 어느세월에 풀릴지 모르는 관계로

어지간하면 비밀번호는 어디다 따로 적어놓던지 하자;;

 

 

 

 

 

아래는 데모버전으로 비밀번호가 5자리 이내라면 찾아주는 프로그램이다.

 

http://software.naver.com/software/summary.nhn?softwareId=MFS_104624

 

 

 

 

☞ 관련 글

 

2013/03/13 - 엑셀 시트의 일부분만 보호하기 (잠금, 시트보호, 시트보호해제) by Y

 

 

 

 

 

 

 

 

Posted by Y&S

댓글을 달아 주세요

 

 

 

엑셀에서 특정 필드를 기준으로 중복값을 찾아 제거하는 [중복된 항목 제거] 명령이 있다.

하지만, 중복값을 찾는 기능은 없으므로 함수나 조건부 서식 등을 응용하는 방법으로 재량껏 찾아야 한다.

물론 눈으로 찾는 방법도 있겠지만, 데이터가 많을수록 불가능한 방법이므로 재껴두자.

여기에서는 중복값을 조건부 서식을 이용하여 찾아내는 방법을 먼저 알아보고

[중복된 항목 제거] 명령을 이용하여 중복값을 제거하는 방법을 공부해보자.

 

조건부 서식에 대해 모른다면 참조~!!

엑셀 조건부 서식 3 (수식을 이용한 조건부 서식) by Y

 

 

 

 

중복값 찾기(조건부 서식 이용)

 

 

 

 

1. 제목을 제외한 데이터 범위를 모두 선택한 후

[홈]-[스타일] 그룹의 [조건부 서식]-[새 규칙] 명령을 클릭한다.

 

 

 

 

2. '수식을 사용하여 서식을 지정할 셀 결정'을 선택한 후

수식 입력란에 '=COUNTIF($B$3:$B$29,$B3)>=2' 식을 입력한다.

성명 데이터의 개수가 2개 이상(중복될 경우)일 경우 서식을 지정한다는 의미이다.

 

COUNTIF 함수를 모른다면 참조~!!  

 엑셀 함수 10 (개수 구하기) by Y

 

 

 

 

3. 서식은 데이터를 강조할 수 있는 서식을 지정해주자.

여기에서는 셀 음영을 지정하기 위해

[채우기] 탭을 클릭한 후 아무 색이나 선택했다.

 

 

 

 

4. 마지막으로 [확인] 버튼을 클릭한다.

 

 

 

 

5. 성명이 중복되는 행에 자동으로 서식이 적용되어

중복 데이터를 쉽게 구분할 수 있게 되었다.

 

 

 

 

 

중복값 제거(중복된 항목 제거 이용)

 

 

 

1. 데이터를 모두 선택한 후 [데이터]-[데이터 도구] 그룹의 [중복된 항목 제거] 명령을 클릭한다.

 

 

 

 

2. 모든 열이 체크되어 있으므로 모두 해제하기 위해 [모두 선택 취소] 버튼을 클릭한다.

 

 

 

 

3. 중복 기준이 될 열만 선택한다.

여기에서는 성명 열만 선택했다.

 

 

 

 

4. 중복되었던 4개의 데이터 중에서 고유하지 않은 2개의 데이터가 자동으로 제거된다.

 

 

 

 

 

 

 

 

 

Posted by Y&S

댓글을 달아 주세요

 

 

 

엑셀에서 데이터가 한 눈에 들어오지 않을 정도로 많을 경우

화면을 이동하면 제목이 보이지 않아 데이터 파악이 어려울 경우가 있다.

아니면 화면을 이동해도 특정 부분을 항상 표시해야할 경우도 있다.

이런 경우 엑셀의 틀고정 기능을 이용하게 된다.

틀고정 기능 자체는 굉장히 쉬운 기능이라 한번만 따라해보면 이해가 될 것이다.

 

 

틀 고정 방법

 

1. 행을 고정하여 화면을 아래로 내려도 항상 고정한 행이 화면에 표시되도록 할 수 있다.

=> 고정할 행의 아래쪽 행을 클릭한 후 틀 고정      

예) 고정할 행이 2행이라면 3행 클릭

 

2. 열을 고정하여 화면을 오른쪽으로 이동해도 항상 고정한 열이 화면에 표시되도록 할 수 있다.

=> 고정할 열의 오른쪽 열을 클릭한 후 틀 고정       

예)  고정할 열이 B열이라면 C열 클릭

 

3. 원하는 행과 열을 고정하여 화면을 오른쪽 아래로 이동해도 항상 고정한 행/열이 화면에 표시되도록 할 수 있다.

=> 원하는 행의 아래쪽, 원하는 열의 오른쪽에 해당하는 셀을 클릭한 후 틀 고정

예) 고정할 행이 3행, 고정할 열이 C열이라면 D4셀 클릭

 

 

 

 

행 고정

 

 

 

아래와 같이 내용이 많을 경우 화면을 아래로 이동해도

제목행은 항상 표시되도록 틀고정을 적용해보겠다.

 

 

 

 

 

5행을 클릭하여 선택한 후 [보기]-[틀고정]-[틀고정] 명령을 클릭한다.

 

 

 

 

 

5행 아래에 아래와 같이 긴 선이 표시된다.

 

 

 

 

 

화면을 아래로 내려보면 긴 선 윗부분은

틀처럼 고정되어 항상 표시되는 걸 볼 수 있다.

 

 

 

 

 

틀고정을 취소하려면 [보기]-[틀고정]-[틀고정 취소] 명령을 클릭하면 된다.

 

 

 

 

열 고정

 

 

 

이번에는 왼쪽의 거래처를 고정시키기 위해 B열을 클릭하여 선택한 후

 [보기]-[틀고정]-[틀고정] 명령을 클릭한다.

오른쪽으로 화면을 이동할 경우 A:B 열 부분이 고정되어 항상 표시되는 걸 볼 수 있다.

 

 

 

 

 

 

행/열 고정

 

 

 

행과 열을 동시에 고정하려면 원하는 행의 아래쪽,

원하는 열의 오른쪽에 해당하는 셀을 클릭한 후 틀고정을 하면 된다.

여기에서는 4행과 B열을 화면에서 항상 표시되도록 고정하기 위해

C5셀을 클릭한 후 [보기]-[틀고정]-[틀고정] 명령을 클릭한다.

 

 

 

 

 

화면을 아래와 오른쪽으로 이동해보면 4행과 B열이 항상 표시되는 걸 볼 수 있다.

 

 

 

 

 

 

 

 

Posted by Y&S

댓글을 달아 주세요

 

 

 

DATEDIF 함수란?

경과년수, 경과개월수, 경과일수 등을 구할 때 자주 사용하는 함수다.

 

 

 

=DATEDIF(시작일, 종료일, "옵션")

시작일부터 종료일까지 경과일을 옵션에 따라 구한다.

 

옵션 설명

※ 옵션이 'MD', 'YM', 'YD' 일 경우 윈도우 버전에 따라

오류가 나거나 제대로 답이 구해지지 않는 경우가 있다.

 

 

 

 

DATEDIF 함수 활용 예 1

 

연인이 만난지 몇일이 지났는지 구해보자.

=DATEDIF(처음 만난 날짜,TODAY(),"D")

예) =DATEDIF("2012-05-11",TODAY(),"D")

 

*** TODAY()는 시스템의 현재 날짜를 의미한다 ***

 

 

 

 

DATEDIF 함수 활용 예 2

 

직원의 근무년수, 근무개월수, 근무일수와 근속기간을 모두 구하는 식을 구해보자. 

일단 작성일에 '=TODAY()' 식을 입력하여 현재 날짜가 표시되도록 한다.

 

 

 

 

근무년수에 '=DATEDIF(C4,$C$2,"Y")' 식을 입력한다.

시작일은 입사일이고, 종료일은 작성일로 지정한 후

경과년수만 구하기 위해 옵션에 Y를 입력한다.

 

 

 

 

근무개월수에 '=DATEDIF(C4,$C$2,"M")' 식을 입력한다.

경과월을 구하기 위해 옵션에 M 입력한다.

 

 

 

 

근무일수에 '=DATEDIF(C4,$C$2,"D")' 식을 입력한다.

경과일을 구하기 위해 옵션에 D를 입력한다.

 

 

 

 

'근속기간1'에는 경과년/경과월/경과일이 0이 되어도 그대로 표시해보겠다.

 

=DATEDIF(C4,$C$2,"Y")&"년 "&DATEDIF(C4,$C$2,"YM")&"개월 "&DATEDIF(C4,$C$2,"MD")&"일"

 

YM은 1년 미만의 개월수를 표시해주고,

MD는 1개월 미만의 일수를 표시해주는 옵션이다.

*** & 기호는 함수식이나 문자열을 연결하는 연산자다.***

 

 

 

 

'근속기간2'에는 경과년/경과월/경과일이 '0'일 경우 생략해보겠다.

 

=IF(DATEDIF(C4,$C$2,"Y")=0,"",DATEDIF(C4,$C$2,"Y")&"년 ")

&IF(DATEDIF(C4,$C$2,"YM")=0,"",DATEDIF(C4,$C$2,"YM")&"개월 ")

&IF(DATEDIF(C4,$C$2,"MD")=0,"",DATEDIF(C4,$C$2,"MD")&"일")

 

'IF(DATEDIF(C4,$C$2,"Y")=0,"",DATEDIF(C4,$C$2,"Y")&"년 ")' 식의 뜻은???

=> 경과년수가 0이라면 ""(공백)이고, 아니면 경과년수에 '년 '을 붙여 표시해준다.

 

IF 함수에 대해 알고 싶다면 함 보자~!!  엑셀 함수 7 (논리 함수, IF 함수) by Y

 

 

 

 

 

 

 

 

 

Posted by Y&S

댓글을 달아 주세요

 

 

 

엑셀 초보들이 숫자 표시형식으로 당황하는 경우들은?

1. 실제로 '15년'이라고 표시해야해서 '15년'이라고 입력한 후 더하기, 빼기 등의 계산을 하려고 하니 계산이 안된다.

2. '15%'를 입력하기 위해 '15'라고 쓰고 표시형식의 '%' 명령을 클릭하니 '1500%'로 표시된다.

3. 소수점 이하를 한 자리만 입력했는데, 소수점 이하 두 자리까지 표시가 된다.

 

 

엑셀에서는 숫자를 입력할 경우 순수하게 숫자만 입력한 후 표시형식을 적용하여 꾸미는 경우가 많다.

이럴때 많이 실수하는 경우 위주로 실습해보고 위와 같은 실수는 더이상 하지 말자~

 

 

실습파일과 완성파일

 

  표시형식.xlsx

표시형식 완성.xlsx

 

 

 

아래와 같이 숫자에 표시형식을 적용시키고, 백분율을 입력해보자.

 

 

 

 

1. 숫자를 입력한 후 원하는 문자 붙여 표시하기

 

숫자를 입력한 후 문자를 붙여 표시하려고 할 경우

1. 계산할 일이 없다면 그냥 문자까지 붙여서 입력해도 된다.  => '15년' 입력

2. 나중에 계산해야하는 숫자의 경우 반드시 숫자로 입력한 후 표시형식으로 문자를 붙여야한다. => '15' 입력

 

 

숫자 범위를 지정한 후 Alt+1 을 누르거나,

마우스 오른쪽 메뉴의 [셀 서식] 메뉴를 클릭한다.

 

 

 

 

'사용자 지정' 범주를 선택한 후 'G/표준' 오른쪽에 '년'을

큰 따옴표로 감싸서 입력한 후 [확인] 버튼을 클릭한다.

'G/표준'은 표시형식을 지정하지 않은 순수한 숫자를 의미한다.

 

여기서 잠깐~!!

큰 따옴표는 한글일 경우 생략해도 되지만, 영어나 특수문자일 경우

꼭 앞뒤에 넣어야하므로 처음부터 큰 따옴표를 넣어주는 습관을 기르자.

 

 

 

 

 

이제 계산할 수 있는 숫자이면서 문자가 같이 표시되는 데이터가 되었다.

 

 

 

 

 

2. 숫자를 입력한 후 '15,000원' 처럼 표시하기

 

우리나라에선 금액일 경우 '\ 15,000'와 같이도 표시하지만, '15,000원'와 같이 더 많이 표시한다.

하지만 1번과 마찬가지로 '15,000원'이라고 입력해버리면 계산할 수 없는 문자가 되어버린다.

결국 1번처럼 '사용자 지정'을 이용해야 한다.

 

 

기본급의 숫자를 모두 범위 지정한 후 Alt+1 을 누르거나,

마우스 오른쪽 메뉴의 [셀 서식] 메뉴를 클릭해서

[셀 서식] 창이 나타나면 아래와 같은 순서대로 작업하면 된다.

 

'#,##0'은 천단위 구분 기호(쉼표)를 숫자에 적용하는 사용자 지정 코드다.

 

 

 

 

이제 우리에게 친근한 '15,000원'의 방식으로 금액이 표시된다.

 

 

 

 

 

3. 백분율 입력하기

 

백분율을 입력하는 방법에는 두 가지 방법이 있다.

1. '15%'일 경우 입력할 때 '15%'라고 입력하는 방법으로 가장 많이 사용하는 방법이다.

2. '15%'일 경우 입력할 때 '0.15'를 입력한 후 '%' 서식 명령을 적용하면 입력한 숫자에 100을 곱한 후 %를 붙여서 '15%'가 되는 방법이다.

이 방법은 입력할 때 모두 100으로 나눈 결과를 입력해야 하므로 머리가 좀 아플 수 있다 ㅎㅎ

 

백분율 입력시 흔히 틀리는 경우

'15%'일 경우 '15'를 입력한 후 '%' 서식 명령을 적용하려고 하는 경우인데,

이렇게 입력하는 사람이 의외로다능~

이처럼 작업한 경우 결과는? '1500%'가 된다.

숫자 1이 100%에 해당한다는 것만 숙지하면 될 듯하다.

 

 

 

 백분율 입력시 맞는 방법과 틀린 방법  

 

위의 그림에서처럼 백분율을 입력하면 소수점 이하 자릿수가 제멋대로인 경우가 많다.

이럴때는 [자릿수 늘림], [자릿수 줄임] 명령으로 소수점 이하 자릿수를 가지런히 이쁘게 표시할 수 있다.

 

 

 

☞ 기타 표시형식 관련글

 

2013/01/03 - [말랑말랑 컴퓨터/엑셀 강좌&팁] - 엑셀 '표시 형식'의 '사용자 지정' 알아보기 by Y 

2013/01/07 - [말랑말랑 컴퓨터/엑셀 강좌&팁] - 엑셀 '표시 형식' by Y 

2013/01/14 - [말랑말랑 컴퓨터/엑셀 강좌&팁] - 엑셀 조건부 서식 1 ('표시 형식'의 '사용자 지정'으로 작성) by Y

 

 

 

 

 

 

 

 

Posted by Y&S

댓글을 달아 주세요

 

 

 

엑셀에서 표를 만들 경우 유의할 점

1. 엑셀의 셀은 나눈다는 개념이 아예 없으므로 필요한 만큼 행/열을 확보해야 한다.

2. 확보한 셀에 '모든 테두리'를 적용하면 전체적인 작업이 쉬워진다.

3. 여러 셀을 하나로 합칠 경우 범위 지정 후 '병합하고 가운데 맞춤' 명령으로 합친다.

4. [Office 단추]-[인쇄]-[인쇄 미리보기]에서 한 페이지에 모두 표시되는지 확인한다.

5. 페이지보다 넘칠 경우 [페이지 레이아웃]에서 여백, 확대/축소 등의 기능으로 조절한다.

6. 페이지보다 부족할 경우 행은 삽입 기능으로, 열은 열 너비를 늘려서 페이지를 채운다.

 

 

 

 

 

엑셀로 매입매출장 만들기

 

아래와 같은 매입매출장을 만들어보자.

필요한 열을 세어보면 12개의 열이 필요한 걸 알 수 있다.

 

 

 

 

열은 12개, 행은 임의로 범위를 지정한 후 [모든 테두리]를 클릭한다.

 

 

 

 

이제 합칠만큼 범위를 지정한 후 [병합하고 가운데 맞춤] 명령을 클릭한다.

 

 

 

 

같은 방법으로 각각 셀을 병합한다.

 

 

 

 

글자를 입력한 후 각 열너비와 행높이를 조절해준다.

열과 열 사이의 선, 행과 행 사이의 선을 드래그하면 원하는대로 조절할 수 있다.

 

 

 

 

[Office 단추]-[인쇄]-[인쇄 미리보기]를 실행한 후 돌아와보면

아래와 같이 페이지를 구분해주는 페이지 구분선이 표시된다.

아래의 경우 가로로 한 페이지에 다 표시가 안되고 넘친다는 의미이다.

 

 

 

 

가로로 페이지 안에 모두 표시되도록 하기 위해

[페이지 레이아웃] 탭의 [크기 조정] 그룹에서 [너비]를 '1페이지'로 지정한다.

 

그럼 자동으로 1페이지가 되는 배율이 자동으로 지정되는데 여기에서는 '82%'로 축소되었다.

글자가 축소되는게 싫은 경우 여백을 좁게 조정하거나 직접 열너비를 줄이는 방법도 있다.

 

 

 

 

이제 1행의 높이를 늘리고 [B1:M1] 범위를 병합한 후 제목을 쓰고 테두리를 지정해준다.

 

 

 

 

화면을 아래로 내려보면 1페이지의 높이를 구분해주는 선이 보인다.

한 페이지에서 많이 모자란걸 볼 수 있다.

 

 

 

 

필요한만큼 행을 선택한 후 [삽입] 명령으로 행을 삽입하면 세로칸을 채울 수 있다.

 

 

 

 

이제 완성된 표를 미리보기하면 A4 용지에 알맞게 채워진 표를 확인할 수 있다.

 

 

 

 

완성된 매입매출장 파일

 

 매입매출장.xlsx

 

 

 

동영상 강좌

 

 

 

 

 

 

 

 

 

Posted by Y&S

댓글을 달아 주세요

 

 

 

엑셀에서 사용되는 단축키 중에서도 유용하여

사람들이 자주 사용하는 단축키를 용도에 따라 정리해보았다.

 

엑셀에서 정말 자주 사용하는 단축키라면 Alt + Enter, Ctrl + Home, F4, Delete 등의 단축키와

파일 관련, 복사 관련 단축키를 들 수 있다.

그 외의 단축키도 사람에 따라 쓰거나 안쓰거나 하겠지만,

단축키는 많이 사용할수록 작업 시간이 단축된다는 걸 잊지 말자~!!

 

 

 

엑셀 단축키 모음.xlsx

 

 

 

 

서식 관련에서는 간단히 셀서식 단축키와

글자 꾸미는데 사용되는 단축키를 모아봤다. 

 

 

 

 

편집 관련 단축키가 좀 많은데..ㅎㅎ

워낙 알고 있으면 편리한 단축키가 많아 추려도 많음

  

 

 

 

입력에 관련된 단축키다.

Alt+Enter 단축키는 엄청 많이 쓰이므로 별 다섯개~!!

  

 

 

 

파일 관련 단축키는

대부분 응용 프로그램이 같다.

 

 

 

 

기타 단축키는 매크로나 빠른 실행 도구들의 단축키와 같이

사람들이 잘 모르지만 알고 사용하면 편리한 단축키를 모아보았다.

 

 

 

 

 

 

 

 

 

Posted by Y&S

댓글을 달아 주세요

 

 

 

70여개의 엑셀 함수를 총~~정리한 파일을 만들어보았다.

컴활 1급에 출제되는 배열수식이나 사용자 정의 함수,

재무 함수를 제외한 모든 함수를 정리했는데,

꼭 자격증 대비가 아니더라도 업무에 필요한 경우에도 사용되면 좋을듯하다.

 

파일 자체를 불법으로 공유하는 것을 막기 위해 배경 그림(워터마크)를 설정하고,

시트에 암호를 걸어 보호하여 배경 그림을 지우거나 내용을 수정하지 못하도록 설정했다.

 

하지만, 인쇄할 경우에는 배경 그림 없이 인쇄되니

 개인적으로 사용하는데는 아무런 지장이 없다능~

 

 

 

엑셀 함수 총정리 파일

함수 총정리.xlsx

 

 

 

'함수 총정리' 파일은 총 6페이지이며, 가로로 설정되어 있다.

 

수학/삼각 함수

'인수' 부분에는 함수의 인수를 각각 어떤 순서로 작성해야하는지

몇개의 인수를 적어야하는지를 최대한 알기쉽게 적었다.

 

 

 

 

 

통계 함수

'활용예'는 실제로 함수식을 어떻게 사용하는지

예를 들어서 적어보았다.

  

 

 

 

논리 함수와 정보 함수

'답' 부분에는 옆의 활용예의 답을 적었으며,

복잡한 식의 경우 설명도 같이 적었다.

정보 함수는 컴활1급용 함수다.

 

 

 

 

찾기/참조 함수

OFFSET, TRANSPOSE 함수는 컴활1급용 함수이고,

INDEX, MATCH 함수는 ITQ 엑셀용 함수다.

컴활2급을 대비한다면 나머지 함수를 공부하면 된다.

 

 

 

 

문자열 함수와 데이터베이스 함수

LEFT, RIGHT, MID 함수는 모든 자격증에 공통적으로 사용되고,

TEXT, LEN 함수는 ITQ 엑셀에서 출제된걸 본적있다.

  

 

 

 

날짜/시간 함수

개인적으로 함수 중에서 가장 쉬운 함수라고 생각함~

 

 

 

모두 끈기있기 공부하다보면 함수가 언젠간 내 발밑에

무릎꿇는 날이 올거라 믿으면서 열공하길~!!

 

 

 

 

 

 

 

 

Posted by Y&S

댓글을 달아 주세요

  1. 이전 댓글 더보기

 

 

 

보통 수백, 수천개가 넘는 주소로 우편물을 발송할 경우 엑셀, 한글, 워드, 엑세스 등의 프로그램을 이용해서 주소라벨을 작성한 후 구입한 주소 라벨 스티커에 인쇄하고, 그 스티커를 편지 봉투에 붙여 우편을 발송한다.

 

엑셀 강좌이니만큼 엑셀의 VLOOKUP 함수를 이용하여 주소 라벨을 만들어보겠다.

VLOOKUP 함수에 대해 잘 모른다면 한번 미리 보자~!!

엑셀 함수 4 (찾기/참조 함수) by Y

 

 

예제를 위한 준비파일과 완성파일이다.

 

 주소라벨.xlsx   주소라벨완성.xlsx

 

 

 

[고객목록] 시트에는 32개의 고객 정보가 입력되어 있다.

모든 데이터가 다 사용되는건 아니고, 실제로 주소라벨에 사용되는 데이터는 이름, 우편번호, 주소만이다.

 

 

 

 

라벨을 만들 첫번째 고객의 고객번호를 F3셀에 입력하면, 자동으로 다음 16개의 고객에 대한 주소라벨이 채워지도록 만들어 아무리 많은 주소가 있더라도 쉽게 주소라벨을 만들 수 있도록 할 것이다.

주소용 라벨 스티커로는 16칸, 18칸, 21칸, 24칸이 가장 많이 사용되는데, 여기에서는 16칸 우편발송라벨 규격에 맞추어서 미리 [주소라벨] 시트에 만들어놓았다.

 

 

 

 

1. 이름 정의하기

 

함수식을 복사할 경우 인수에 사용된 범위가 항상 고정되도록 하려면??

절대참조를 사용하는 방법과 이름을 정의하는 방법의 두 가지 방법이 있다.

 

여기에서는 제목을 제외한 모든 목록범위([고객목록] 시트의 B2:H33)와 고객번호 입력란([주소라벨] 시트의 F3셀)의 이름을 정의하여 함수를 작성해보겠다.

 

범위를 선택하려면??

1. 범위의 크기가 작다면 마우스로 드래그한다.

2. 범위의 크기가 굉장히 크다면 첫번째 셀을 클릭한 후 마지막 셀을 Shift 키를 누른 상태에서 클릭한다.

 

주소 목록은 굉장히 데이터가 많은 경우가 대부분이므로 아래와 같은 방법으로 범위를 지정해보았다. 

 

 

 

 

[이름 상자]에 '주소목록'을 입력한다.

 

 

 

 

이어서 [주소라벨] 시트의 F3 셀을 선택한 후 [이름 상자]에 '고객번호'를 입력한다.

 

 

 

 

2. 함수식 입력

 

이름 정의가 모두 되었다면, [주소라벨] 시트의 첫번째 라벨 입력 부분에 각각의 함수식을 작성해보자.

 

A1 셀    =VLOOKUP(고객번호+INT(ROW(A1)/3),주소목록,6,0)
A2 셀    =VLOOKUP(고객번호+INT(ROW(A1)/3),주소목록,2,0)
A3 셀    =VLOOKUP(고객번호+INT(ROW(A1)/3),주소목록,5,0)

 

ROW 함수와 INT 함수를 모른다면 한번 미리 보자~!!

엑셀 함수 15 - ROW 함수 (엑셀 일련번호 만들기) by Y

엑셀 함수 18 - INT, TRUNC, ROUND, ROUNDUP, ROUNDDOWN 함수 by Y

 

 

함수식 설명

 

 

1) INT(ROW(A1)/3) ??

 

ROW(A1) => ROW 함수는 셀의 행번호를 구하는 함수이므로 1이 구해진다

ROW(A1)/3 => 1을 3으로 나누면 0.3333....이 구해진다.

INT(ROW(A1)/3) => INT 함수는 가까운 정수를 구하는 함수이므로 0이 구해진다.

 

여기서 잠깐~!!

식을 A4 셀에 복사할 것이므로 A4셀에는 식이 'INT(ROW(A4)/3)' 식으로 변경되어 들어간다.

(왜냐? 상대참조니까~)

결국 A4셀에는 위의 식으로는 1이 구해진다.

같은 방법으로 A7, A11... 셀에는 각각 2, 3...이 구해지므로,

결국 다음 고객번호에 해당하는 고객의 주소가 입력된다.

 

2) 고객번호+INT(ROW(A1)/3) ??

 

INT(ROW(A1)/3)의 값이 0이므로 고객번호가 1이라면 1+0 값이 구해진다.

 

2) VLOOKUP(고객번호+INT(ROW(A1)/3),주소목록,6,0) ??

 

1번 고객부터 라벨을 만들기 위해 고객번호에 1을 입력한 경우 'VLOOKUP(1,주소목록,6,0)' 식으로 생각할 수 있다. 결국~~~!!

VLOOKUP(1,주소목록,6,0) => 주소목록 범위에서 고객번호가 '1'인 데이터의 6번째 열(주소)의 값을 구해온다.

VLOOKUP(고객번호+INT(ROW(A1)/3),주소목록,2,0) => 위와 같은 데이터의 2번째 열(이름)의 값을 구해온다.

VLOOKUP(고객번호+INT(ROW(A1)/3),주소목록,5,0) => 위와 같은 데이터의 5번째 열(우편번호)의 값을 구해온다.

 

 

여기서 잠깐~!!

아래와 같이 오류가 나는 이유는 고객번호를 아직 입력하지 않았으므로 'VLOOKUP(0,주소목록,6,0)' 식으로 간략화되어 고객번호가 0인 고객을 찾게 되는데, 데이터에서 고객번호가 0인 데이터가 없으므로 값을 찾을 수 없어 오류표시가 보여진다. 어짜피 고객번호를 입력하면 데이터가 제대로 표시된다.

 

 

 

 

이어서 오른쪽 상단의 라벨 부분에 아래와 같은 함수식을 각각 작성해보자.

 

C1 셀   =VLOOKUP(고객번호+INT(ROW(A1)/3)+8,주소목록,6,0)
C2 셀   =VLOOKUP(고객번호+INT(ROW(A1)/3)+8,주소목록,2,0)
C3 셀   =VLOOKUP(고객번호+INT(ROW(A1)/3)+8,주소목록,5,0)

 

 

함수식 설명

 

 

VLOOKUP(고객번호+INT(ROW(A1)/3)+8,주소목록,6,0) ??

 

1번 고객부터 라벨을 만들기 위해 고객번호에 1을 입력한 경우 'VLOOKUP(9,주소목록,6,0)' 식으로 생각할 수 있다. 결국~~~!!

VLOOKUP(9,주소목록,6,0) => 주소목록 범위에서 고객번호가 '9'인 데이터의 6번째 열(주소)의 값을 구해온다.

VLOOKUP(고객번호+INT(ROW(A1)/3)+8,주소목록,2,0) => 위와 같은 데이터의 2번째 열(이름)의 값을 구해온다.

VLOOKUP(고객번호+INT(ROW(A1)/3)+8,주소목록,5,0) => 위와 같은 데이터의 5번째 열(우편번호)의 값을 구해온다.

 

 

여기서 잠깐~!!

아래와 같이 값이 표시되는 이유는 고객번호를 입력하지 않아도 'VLOOKUP(8,주소목록,6,0)'이므로 고객번호가 8인 데이터가 자동으로 입력되기 때문이다. 어짜피 고객번호를 입력하면 값은 변하게 된다.

 

 

 

 

3. 함수식 복사하기

 

주소라벨에 함수식을 모두 채우기 위해 함수식을 작성한 [A1:D3] 셀까지 범위지정한 후 하단에 각각 붙여넣는다.

 

 

 

 

4. 고객번호 입력하기

 

F3 셀에 1을 입력하면 고객번호가 1인 고객부터 16인 고객까지 주소와 이름, 우편번호가 자동으로 채워진다.

 

 

 

 

F3셀에 17을 입력하면 고객번호가 17인 고객부터 32인 고객까지 주소와 이름, 우편번호가 자동으로 채워진다.

 

 

 

여기서 팁~!!

만약 24칸 주소라벨을 만든다면 세로로 12개가 입력되므로 함수식에서 아래 부분을 수정하면 된다.

 

C1 셀   =VLOOKUP(고객번호+INT(ROW(A1)/3)+12,주소목록,6,0)
C2 셀   =VLOOKUP(고객번호+INT(ROW(A1)/3)+12,주소목록,2,0)
C3 셀   =VLOOKUP(고객번호+INT(ROW(A1)/3)+12,주소목록,5,0)

 

 

동영상 강좌 보기

 

 

 

 

 

 

 

 

 

Posted by Y&S

댓글을 달아 주세요

 

 

 

엑셀에서 빈셀/빈칸을 모두 없애는 경우이거나, 데이터에 입력된 쓸데없는 공백을 모두 없애는 경우 [홈]-[편집]-[찾기 및 선택] 기능을 이용하면 편리하게 없앨 수 있다.

 

중간중간 있는 빈셀을 삭제할 경우 빈셀들이 떨어져 있으므로 Ctrl 키를 누르고 빈셀을 각각 선택한 후 삭제하는 경우가 대부분일 것이다. 하지만, 이 방법은 데이터가 많으면 많을수록 즉, 빈셀이 많으면 많을수록 손이 많이 가고 오래 걸리므로 그럴 경우에는 아래와 같은 방법을 사용하면 금방 빈셀을 없앨 수 있다.

 

 

아래 첨부 파일을 다운로드한 후 같이 따라해보자. 

 

빈셀빈칸없애기.xlsx 

 

 

 

 

 

빈셀/빈칸 없애기

 

 

 

 

 

파일을 열어보면 '컴퓨터부품이력' 시트에 아래와 같은 데이터가 입력되어 있다.

 

 

 

 

1. 표 범위를 선택한다.

 

 

 

 

2. [홈]-[찾기 및 선택] 메뉴에서 [이동 옵션] 메뉴를 클릭한다.

 

 

 

 

3. 선택된 범위에서 '빈 셀'만 선택하기 위해 '빈 셀' 옵션을 선택한 후 [확인] 버튼을 클릭한다.

 

 

 

 

4. 아래와 같이 처음 선택한 범위 안에 있는 모든 빈 셀이 자동으로 선택된다.

 

 

 

 

5. 선택된 빈 셀 중에서 아무 곳에서나 마우스 오른쪽 버튼을 눌러 메뉴가 나타나면 [삭제] 메뉴를 클릭한다.

 

 

 

 

6. 원하는 옵션을 선택한다.

여기에서는 '셀을 위로 밀기' 옵션을 선택했다.

이 경우 빈셀이 삭제되고 아래 데이터가 위로 올라오게 된다.

 

 

 

 

7. 이제 아래와 같이 빈셀이 삭제되어 깔끔한 표가 되었다.

 

 

 

 

 

 

 

공백 문자 없애기

 

 

 

 

 

'원제품' 시트를 선택하면 아래와 같은 표가 있다.

각 데이터에는 쓸데없는 공백이 많이 포함되어 있는 걸 볼 수 있다.

 

 

 

 

1. [홈]-[찾기 및 선택]-[바꾸기] 메뉴를 클릭한다.

 

 

 

 

2. '찾을 내용'에서는 스페이스바를 한 번 눌러 공백을 입력하고,

바꿀 내용은 건드리지 않은 상태에서 [모두 바꾸기] 버튼을 클릭한다.

 

 

 

 

3. 데이터의 공백이 모두 없어져 깔끔한 데이터가 되었다.

 

 

 

 

 

 

 

 

 

 

Posted by Y&S

댓글을 달아 주세요