오즈맨의 이야기

연 월을 나타내는 C1 셀은 셀 서식이 다음과 같이 지정되어 있습니다.

J3 셀에 매월 1일을 구하는 수식=DATE(YEAR(J2),MONTH(J2)+1,1)를 입력하고 아래로 드래그합니다.

DATE 함수 DATE(연,월,일)로 사용합니다.

MONTH 함수 MONTH(지정날짜)는 지정 날짜의 月을 나타냅니다.

연월을

마우스로 입력하기 위해서

유효성 검사를 선택하고

목록을 지정합니다.

달력의 첫주의 첫 날짜를구하는 수식=C1-WEEKDAY(C1)+1
를 A4셀에 입력합니.

WEEKDAY
함수

WEEKDAY(지정날짜)는
지정날짜의 요일번호를 나타냅니다.

B4~G4 를 선택한 다음 수식 =A4+1 을 입력하고서는

컨트롤 + 엔터를 합니다.

단축키 컨트롤 + 엔터 는

선택된 셀에

수식이 한방에 입력됩니다.

+1 하면 지정한 날의 다음날짜가 나옵니다.

A7~G7, A10~G10,
A13~G13, A16~G16,
A19~G19

를 선택한 다음에

현재 선택된 셀 기준

A16+7을 입력하고 컨트롤 + 엔터

일주일 후의 날짜를 나오게 합니다.

A4~G4 를 선택한 다음

조건부 서식을

그림과 같이 지정합니다.

수식은

=MONTH(A4)<>MONTH($C$1)

는 선택된 셀의 月 이랑

C1 셀의 月 을비교해서 만일 다르면 서식을 적용하라는 내용입니다.

A4~G6

을 선택한 다음

21행까지 드래그하고

서식만 채웁니다.

불필요한 열을

안보이게 하면 더욱 좋습니다

서식-열-숨기기

를 선택합니다.

① ‘월별급여대장’시트의

[D6:J6]영역을 블록을 설정하고

표준 도구 모음의 복사 를 클릭해서 복사를 합니다.

② 데이터를 행/열을 바꿔 기록할

‘개인내역’ 시트의 [C7]셀을 선택,

표준 도구 모음의 붙여넣기 의 드롭다운 단추 를 클릭한 다음, [선택하여 붙여넣기] 메뉴를 선택합니다.

엑셀 2007 은

홈 탭 클립보드 그룹에 있습니다.

③ [선택하여 붙여넣기] 대화상자가 나오면

붙여넣기 영역에서 ‘값’을 선택하고 ‘행/열 바꿈’을 선택하고 [확인] 단추를 클릭합니다.

④ ‘월별급여대장’시트의

[K6:P6]영역을 블록을 설정하고

표준 도구 모음의 복사 를

클릭해서 복사를 한 다음,

‘개인내역’ 시트의 [E7]셀을 선택,

[편집-선택하여 붙여넣기] 메뉴를 선택합니다.

⑤ [선택하여 붙여넣기] 대화상자가 나오면 붙여넣기 영역에서 ‘값’을 선택하고 ‘행/열 바꿈’을 선택하고 [확인] 단추를 클릭합니다.

① [A4] 셀을 선택한 다음

[데이터-피벗 테이블/피벗차트보고서] 메뉴를 선택합니다.

② [피벗 테이블/피벗 차트마법사]창의

‘3단계 중 1단계’에서 [다음]을 클릭합니다.

③ [피벗 테이블/피벗 차트마법사]창의

‘3단계 중 2단계’ 창이 나오면 ‘범위’가 자동으로 선택이 되었으므로 [다음]을 클릭합니다.

④ [피벗 테이블/피벗 차트마법사]창의

‘3단계 중 3단계’ 창이 나오면 [레이아웃]을 클릭합니다.

⑤ [레이아웃]창이 나오면 문서의 좌측(행)은 월별로 구분을 할 수 있도록

오른쪽에 있는 일자 필드단추를 왼쪽에 있는 다이어그램의 행(R)으로,

항목 필드단추를 왼쪽에 있는 다이어그램의 열(C)로 드래그해서 이동하고,

출금금액 필드단추를 다이어그램의 데이터(D)로 드래그해서 이동을 한 다음 [확인] 단추를 클릭합니다.

⑥ [피벗 테이블/피벗 차트마법사]창의

‘3단계 중 3단계’ 창으로 돌아오면 [마침] 단추를 클릭해서 피벗 테이블을 완성합니다.

⑦ 집계를 구하고자 하는 출금금액이 개수로 되어 있습니다. [A3]셀의 ‘개수 : 출금금액’ 필드를 선택하고, 마우스 오른쪽 버튼을 클릭한 다음 [필드 설정]메뉴를 선택합니다.

⑧ [피벗 테이블 필드] 대화상자가 나오면 사용할 함수를 [합계]로 지정을 하고 ‘이름’을 경비합계로 입력한 다음 [확인] 단추를 클릭합니다.

⑨ 일자별 항목별 집계가 나왔습니다.

월별로 그룹을 짓기 위해서

[A4]셀의 ‘일자’ 필드를 선택한 다음

마우스 오른쪽 버튼을 클릭하고,

[그룹 및 하위수준 표시-그룹] 메뉴를 선택해서

집계내는 기준을 ‘월별’로 설정합니다.

⑩ 집계를 내야 하는 [B3]셀의 ‘항목’필드의 드롭다운 단추 를 클릭하고 대화상자가 나오면 경비지출에 해당하는 항목을 선택하고 나머지 항목은 선택 해제를 한 다음 [확인] 단추를 클릭합니다.

⑪ 모두 선택 단추(

)를 클릭한 다음

[서식-열-선택한 열에 맞게] 메뉴를

선택해서

열의 너비를 자동으로 맞춘 다음, 행의 높이는 24로 설정합니다.

피벗 테이블 보고서 기능은

많은 양의 데이터를 빠르게 요약할 때 매우 유용합니다.

데이터를 항목별로 분류하고, 부분합을 구하려고 할 때,

분류하고자 하는 형식을 선택하고

항목명을

‘행 영역’, ‘열 영역’,

부분합을 구할 이름을 ‘계산할 영역’으로 드래그하여 간편하게 계산할 수 있습니다.

① 자료가 있는

[A4:F16]영역을 마우스 드래그를 해서 블록을 설정한 다음 [데이터-부분합] 메뉴를 선택합니다.

엑셀 2007 은

데이터 탭 윤곽선 그룹에

있습니다.

② [부분합]대화상자가 나오면

‘그룹화할 항목’ 목록의 드롭다운 단추 를 클릭해서 [자산종류]를 선택하고,

‘사용할 함수’는 [합계]를 선택한 다음, ‘

부분합 계산 항목’은 합계를 내고자 하는 항목을 선택하고

나머지 항목은 선택을 해제 한 다음 [확인] 단추를 클릭합니다.

③ 데이터의 마지막에 추가된 두 행의 셀 테두리를 서식 도구 모음의 테두리 를 클릭해서 지정합니다.

④ ‘부분합’이 만들어진 문서에서 부분합윤곽기호123 의 [2]를 클릭하면 자산의 종류별로 볼 수 있도록 설정이 됩니다.

① [4:16]행을 행머리글을 드래그해서 블록을 설정한 다음

[데이터-정렬] 메뉴를 선택합니다.

엑셀2007 에서는

데이터 탭의 정렬 및 필터그룹에 있습니다.

② [정렬] 대화상자가 나오면

첫째 기준을 ‘성명’으로 선택하고 [확인] 단추를 클릭합니다.

③ 두 가지 이상의 조건으로 정렬을 하기 위해서 [데이터-정렬] 메뉴를 선택합니다.

④ [정렬] 대화상자가 나오면 첫째 기준을 ‘직급’으로 선택하고, 둘째기준을 ‘성명’으로 선택하고 [확인] 단추를 클릭합니다.

엑셀 2007 의 정렬 예제입니다.

셀 색을 기준으로 정렬할 수도 있습니다,

정렬기준은 64개까지

사용가능합니다.

① [C5]셀을 선택하고

[창-틀 고정] 메뉴를 선택합니다.

엑셀 2007에서는

보기 탭의 창그룹에 있습니다.

창 그룹에는 열려있는 워크북
(파일)을 숨기거나 나타나게 할 수 있는

숨기기/취소 기능도 함께 있습니다.

② [I25]셀을 선택을 해서 보면 [C5]셀의 위행인 [4]행 까지, 왼쪽의 열인 [B] 열까지가 고정이 되어 있음을 알 수 있습니다.

③ ‘인사기록부’ 시트를 선택하고, [C5]셀에서 [창-나누기] 메뉴를 선택합니다.

④ 고정된 제목을 더욱 넓게 볼 수 있도록

[B]열에서 구분된

분할선()을 마우스로 선택하고 오른쪽으로 드래그합니다.

⑤ 고정된 제목부분이 [C] 열까지 지정된 모습을 볼 수 있습니다.

① [데이터-필터-

자동 필터] 메뉴를 선택합니다.

엑셀2007 에서는

데이터 탭의 정렬 및 필터그룹에 있습니다.

② ‘품명’이 있는 [C3]셀의 드롭다운 단추()를 클릭해서

‘시디롬’을 선택

‘품명’이 ‘시디롬’인 상품만 조회합니다.

③ ‘출고내역’ 시트의 [A3:I229]

영역을 복사를 한 다음,

‘Sheet2’의 [A3]셀을 붙여넣으면

‘품명’이 ‘시디롬’인 상품의 출고내역이 만들어 집니다.

[서식-열-선택한 열에 맞게]

메뉴를 선택해서 열의 너비를 조정합니다.

④ ‘시디롬’을 판매한 업체 목록
을 알기 위해

상호가 있는 [B3:B44]영역을 블록을 설정한 다음

[데이터-필터-고급 필터]메뉴를 선택합니다.

⑤ [고급 필터] 대화상자가 나오면

‘다른 장소에 복사’를 선택하고,

‘복사위치’를 [J3]셀로 지정한 다음

‘동일한 레코드는 하나만’을 선택하고 [확인] 단추를 클릭합니다.

⑥ 필터링을 한 결과 중 일부만을 필터링을 하기 위해서 [B3:D44]영역을 블록을 지정하고 [데이터-필터-자동 필터] 메뉴를 선택합니다.

⑦ ‘규격’이 있는 [D3]셀의 드롭다운 단추()를 클릭해서 ‘사용자 지정’ 을 선택합니다.

⑧ [사용자 지정 자동 필터] 대화상자가 나오면

찾을 조건의 조건 부호를 ‘=’를 선택하고

지정할 값을 ‘*64X’ 로 입력하고 [확인] 단추를 클릭합니다.

① [C]열 머리글을 클릭해서 [C] 열을 선택한 다음

[데이터-유효성 검사] 메뉴를 선택합니다.

엑셀 2007에서는

데이터 탭의

데이터 도구

그룹에 있습니다.

② [데이터 유효성] 대화상자가 나오면

[설정] 탭에서

제한 대상을 ‘목록’으로 선택합니다.

원본에 적당한 목록 값을 입력합니다.

③ [F:G]열 머리글을 클릭해서 열을 선택한 다음

[데이터-유효성 검사] 메뉴를 선택하고,

[데이터 유효성] 대화상자가 나오면

[설정] 탭에서 제한 대상을 ‘날짜’로 선택한 다음,

제한 방법을

｢다음 값 보다 작거나 같음｣을 선택하고

끝 날짜를 ｢=TODAY()｣ 로 입력하고

[설명 메시지] 탭을 선택합니다.

④ [설명 메시지] 탭이 선택되면

‘셀을 선택하면 설명 메시지 표시’를 선택한 다음,

‘제목’의 입력란에 ‘날짜의 입력방법은’으로 입력하고,

‘설명 메시지’ 의 입력란은 ‘YYYY-MM-DD형식의 숫자로 입력합니다.’를 입력한 다음 [오류 메시지] 탭을 선택합니다.

⑤ [오류 메시지] 탭이 선택되면 ‘유효하지 않은 데이터를 입력하면 오류 메시지 표시’를 선택한 다음,

‘스타일’을 ‘중지’를 선택하고,

‘제목’의 입력란에 ‘날짜 오류’로 입력,

‘오류 메시지’ 의 입력란은 ‘문자가 올 수 없습니다.’를 입력하고 [확인] 단추를 클릭합니다.

유효성의 조건에 맞지 않을 경우

입력이 안 되고

오류 메시지가

나오게 됩니다.

① [G]열을 선택한 다음
서식-조건부 서식 메뉴를 선택합니다.

엑셀 2007에서는

홈 탭의
스타일 도구그룹에

있습니다.

② [조건부 서식] 대화상자가 나오면 조건 1 을 ‘셀 값이’로 선택을 하고 조건을 ‘다음 값보다 작음’  을 선택한 다음 입력상자에 ｢2005-12-31｣을 입력하고 서식 단추()을 클릭합니다.

③ [셀 서식] 대화상자가 나오면 [글꼴] 탭의 ‘글꼴 스타일’을 ‘기울임꼴’로 지정한 다음 [확인] 단추를 클릭합니다.

조건부 서식에서 지정할수 있는 서식은 제한적입니다.

④ [A:C]열을 선택해서 [서식-조건부 서식] 메뉴를 선택하고,

[조건부 서식] 대화상자가 나오면
조건 1 을 ‘수식이’로 선택한 다음,
입력상자에 수식을 ｢=AND($F1>0,$G1=0)｣ 으로 입력하고 서식 단추()을 클릭합니다.

엑셀 2007 의 조건부 서식 예제입니다.

① 분기별 금액을 구하기 위해서 [E5]셀을 선택하고 [삽입-이름-정의] 메뉴를 선택합니다.

엑셀 2007 에서는

수식 탭의 정의된 이름그룹에

있습니다.

그리고 수정/편집도 쉽습니다.

② [이름 정의] 대화상자가 나오면

‘통합 문서에 있는 이름’ 입력상자에 ‘매출1기’를 입력,

 ‘참조 대상’의 입력란의 참조 대상 영역을 [B5:B7]영역을 선택하고, 추가 단추()를 클릭합니다.

③ 같은 방법으로 이름정의를 추가합니다.

이름

매출2기

매출3기

매출4기

④ 1/4분기의 합계를 구할

[E5]셀을 선택하고

｢=SUM(매출1기)｣로 입력합니다.

⑥ 분기별 평균 매출액을 구하기 위해서 [E12]셀을 선택하고, 표준 도구 모음의 자동합계 의  드롭다운 단추()를 클릭해서 [평균]메뉴를 선택합니다.

⑦ 수식이 자동으로

=AVERAGE(E5:E11)로 만들어 지면 [삽입-이름-붙여 넣기] 메뉴를 선택합니다.

엑셀 2007 에서는

수식 탭의 정의된 이름그룹-

수식에서 사용을 선택합니다.

※ Tip

이름정의를 많이 한 경우나 이름정의 목록이 기억이 나지 않을 경우 간편하게 이름을 입력할 수 있습니다.

⑨ [E12]셀의 수식이

｢=AVERAGE(매출1기)｣로 지정된 것을 확인하고 엔터를 입력합니다.

⑪ [삽입-이름-정의] 메뉴를 선택하고 [이름 정의] 대화상자가 나오면, 이름을 ‘총매출’로 지정하고 참조 대상을 ｢=SUM(매출1기,매출2기,매출3기,매출4기)｣로 입력하고 [확인] 단추를 클릭합니다.

이름정의가 된 하나의 셀

[AL13]을 선택하면

이름상자에 이름이 나타납니다.

이름정의가 된 영역

[AK4:AL9]을 선택하면

이름상자에 이름이 나타납니다.

이름정의를 수식입력줄 혹은

셀의 수식을 보면

정의된 이름에는 "" 기호가 붙지 않지요.

그리고

함수마법사 단추를 누르면

이름정의된 영역의 값이

차례차례 보이게 되지요.

공급가액을 이름정의한 [AL13]과

세액을 이름정의한 [AL14]를

이름정의 없이 사용하면

셀 주소를 잘못 사용하기 쉽겠습니다.

게다가

공급가액은 [G10~Q10]에서

사용하지만

세액은 [R10~AA10]셀에서

사용되어

바로 옆 셀에 엉뚱한 수식을 넣을 수 있습니다.

이름 목록을 출력해서 보려면

삽입-이름 메뉴에서

붙여넣기를 선택합니다.

비슷한 영역 혹은 인접 영역은

머리글을 같게 함으로써

효율적인 관리가 쉽습니다.

고급필터를 사용할 경우

필터의 원본영역을 알아보기가

매우 어렵지요

대화상자가 너무 작아서 ^^

이럴 경우

의미 있는 이름정의를 사용하면 매우 효율적입니다.

시트의 개수가 적을 경우는 무리가 없으나

시트의 수가 많을 경우 일일이 드래그 해서 사용하기에는 매우 번거로움이 따릅니다.

-- 이를 해소할 수 있습니다

① ‘내기능_05.xls’ 파일을 연 후, [절대참조]시트의 [C7]셀에 수식 ｢=$C$5*B7｣을 입력해서 외화금액의 원화 환산액을 계산합니다.

② [C7]셀을 [C16]셀까지 드래그해서 복사하고 [C8]셀의 수식을 살펴보면,

[C7]셀에 있는 수식 ｢=$C$5*B7｣을 복사했는데

[C8]셀에는 수식이 ｢=$C$5*B8｣로 변경이 되어 있습니다. $ 기호가 붙은 [5] 는 변경이 되지 않고 [7] 은 [8]로 변경이 되었습니다.

③ [혼합참조] 시트를 선택하고 [C7]셀에 수식 ｢=C$5*$B7｣를 입력한 다음 [N16]셀까지 복사합니다.

④ [C7]셀의 수식 ｢=C$5*$B7｣을 복사했으나, [C8]셀의 수식은 ｢=C$5*$B8｣ 으로

[D8]의 수식은 ｢=D$5*$B8｣로 $ 기호가 없는 부분만이 자동으로 조정되었습니다.

① ‘내기능_04.xls’ 파일을 연 후, [A5]셀을 선택해서 셀의 오른쪽 아래 부분의 마우스 포인트()를 클릭하고 [A11]셀까지 드래그합니다.

② 자동 채우기()를 클릭해서 채우기 핸들의 옵션 중 ‘연속 데이터 채우기’를 선택합니다.

③ [E5]셀을 선택하고 채우기 핸들을 이용해서 [E11]셀까지 드래그한 다음, 채우기 핸들의 옵션 중 ‘셀 복사’를 선택합니다.

④ ‘매입단가’, ‘매출단가’가 있는 [D5:E5]영역을 마우스로 드래그해서 블록을 설정한 다음, 서식 도구 모음의 쉼표 스타일 을 클릭해서 숫자의 표시형식을 지정하고, 채우기 핸들을 이용해서 [E11]셀까지 드래그한 다음, 채우기 핸들의 옵션 중 ‘서식만 채우기’를 선택합니다.

채우기 핸들이 나타나지 않으면

셀 선택 영역에 채우기 핸들을 표시하려면 [도구-옵션] 메뉴를 선택하고, [편집] 탭에서 ‘셀 끌어서 놓기 허용’ 확인란을 선택합니다.

엑셀 2007 에서는

홈 메뉴 클릭 후

Excel 옵션 의 고급에서

선택합니다.

[A4]셀에 =A1+A2 를 입력합니다.

[B4]셀에 +B1+B2를 입력합니다.

도구모음에 있는 자동합계 단추를 클릭한 다음

셀 영역 [A1:B2]를 드래그합니다.

창 메뉴에서 새 창을 선택합니다.

창 메뉴에서 정렬을 선택합니다.

창 정렬 상자가 나오면 세로 를 선택합니다.

도구모음에 있는 자동합계 단추를 클릭한 다음 바로 옆 SHEET2 의

셀 영역 [A1:A2]를 드래그합니다.

수식 적용 후 결과입니다.

창을 원래대로 하려면 내기능_03:2 창을 닫습니다.

셀에 숫자를 입력하면 숫자 그대로를 인식합니다.

자동합계 단추를 클릭하면 합계가 계산됩니다.

숫자를 문자 형식으로 지정하려고

숫자 앞에 ' 을 먼저 입력합니다.

문자 처리된 C 열의 합계를 구합니다.

' 없이 숫자를 입력해도 셀 서식이 텍스트라면 ???

마찬가지로 합계를 못 구합니다.

수식 그대로를 보려면

도구-옵션에서

화면표시-수식을 선택합니다.

해제 하려면

컨트롤 + 땡 ` (~ 키입니다)을 누릅니다.