modul microsoft excel 2007 tingkat mahir1 21

8/10/2019 Modul Microsoft Excel 2007 Tingkat Mahir1 21

1/21

BAGIAN PENGEMB

SETDITJEN PERBEN

Modul Pemb

A BCA CDE A A BC F

ANGAN PEGAWAI

DAHARAAN

elajaran Microsoft ETingkat Mahir

2011 cel 2007

8/10/2019 Modul Microsoft Excel 2007 Tingkat Mahir1 21

2/21

Daftar Isi

ContentsDaftar Isi ............................................................................................................................................................. 2

ABACDE AF EF E A ..................................................................................................... 41. Format Style ....................................................................................................................................... 4

1.1 Conditional Formatting ............................................................................................................ 4

1.2 Format as Table ......................................................................................................................... 5

! " # " $%$% & " " ' (% # ) ...................................... 5

1.3 Cell Styles ................................................................................................................................... 6

* AF E C A EC BACDE ................................................................................................. 7

1. Split, Freeze Table ............................................................................................................................ 7

1.1 Split Table ................................................................................................................................... 7

1.2 Frezee Table ............................................................................................................................... 7

+ ) F (,-) F (, EFD EF .................................................................................................. 9

1. Fungsi Fungsi Matematika ............................................................................................................. 9

2. Fungsi fungsi Teks ......................................................................................................................... 10

3. Fungsi fungsi tanggal dan aktu ............................................................................................... 11

!. Fungsi fungsi logika ...................................................................................................................... 12

". Fungsi fungsi lookup dan #eferensi ......................................................................................... 13

$. Fungsi fungsi Statistik ................................................................................................................. 14

%. Fungsi fungsi Finansial ................................................................................................................. 15

Modul !. Men&ari data ................................................................................................................................ 19

1. Menggunakan Find ......................................................................................................................... 19

2. Menggunakan #epla&e .................................................................................................................. 20

3.

Mengurutkan data ........................................................................................................................... 20

!. Menyaring data ................................................................................................................................ 21

". 'alidasi data ..................................................................................................................................... 22

$. (onsolidasi data ............................................................................................................................. 24

%. Mengelompokan data ..................................................................................................................... 25

). Membuat Subtotal ........................................................................................................................... 26

. ( C ), AC ( E /,0 E A ........................................................................ 28

1. Sort ..................................................................................................................................................... 28

2. Filter .................................................................................................................................................... 29

3. *roup dan +n*roup ....................................................................................................................... 30

8/10/2019 Modul Microsoft Excel 2007 Tingkat Mahir1 21

3/21

!. SubTotal ............................................................................................................................................ 30

". i-ot Table i-ot C/art .............................................................................................................. 32

1 ('EC,F 2 CB B .......................................................................................................... 35

1. Cara membuat s/aring orkbook adala/ sebagai berikut 0 ................................................ 35

2. enyuntingan s/ared orkbook 0 .............................................................................................. 363. Meng/apus engguna dari s/ared orkbook ........................................................................ 37

!. enyelesaian konflik peruba/an di s/ared orkbook .......................................................... 37

". Meng/apus s/aring a orkbook ................................................................................................ 38

8/10/2019 Modul Microsoft Excel 2007 Tingkat Mahir1 21

4/21

ABACDE AF EF E A

/ # " " 3 "" # # -# & " #

# # & # # # " " B " # # " A4$

# # " " # # " & " # $ A4$ # 5%#

1. Format Style

1.1 Conditional Formatting ! # " " $%$%

& " " # # " " % # # & !

# # "

5 "" & 6

%

* / 5% # % )% "

8/10/2019 Modul Microsoft Excel 2007 Tingkat Mahir1 21

5/21

+ / % % & " # 6

ig/lig/t Sels #ules ! & " # # " &

Top ottom #ules ! & " # # " & 7

!

$ 4ata ars ! # " & # # ""

# Color S&ales ! & " # # " & # # ""

5&on Sets $% # " & # # ""

8 6e #ule &

" Clear #ules " & & " # #

Manage #ules " & - & & " # #

9 B # &

1.2 Format as Table

! " # " $%$%

& " " ' (% # )

5 "" &

% E

* B )%

8/10/2019 Modul Microsoft Excel 2007 Tingkat Mahir1 21

6/21

+ B ( & & " # ( & # F ! ( &

1.3 Cell Styles

& " # # ! " !

# "

5 "" &

% E

* B 5 ( &

+ B ( & & " # ( & # F ! 5 ( &

8/10/2019 Modul Microsoft Excel 2007 Tingkat Mahir1 21

7/21

* AF E C A EC BACDE

1. Split, Freeze Table

1.1 Split Table

( 8 " # A4$ " 3 # 9 : ; " ""

3 # " 9 % !% %% & " #

5 &Split, (lik 'ie 7 Split

1.2 Frezee Table

8 " % % # "" " & " #

# " # !% %% # 3 5 & " 6

8/10/2019 Modul Microsoft Excel 2007 Tingkat Mahir1 21

8/21

% B% % & " # ) , :? A AB?@+@*; & A # * # # - +

8/10/2019 Modul Microsoft Excel 2007 Tingkat Mahir1 21

11/21

5% % 6 > //AC:? ?@+; & A AB- ;8=/C /AC:? A AB?; &

- TT : " " 3 # # " 3 # & " # & #" 8% ;

5% % 6 > A : 77 9@ 7 77 ; & *. 77- C 9# : " " *.. 3 # %# E(5,,;

5% % 6 >5'EC: *.; " - 48;;9# : " " 3 # " #% # # 3 #

" 3 # ;

5% % 6 EC:** = 1; " G+ 9*H.=- C84< : " 3 # %# E(5,,;

5% % 6 >5 A: , 8% ; " =+ >5 A: , ; " =+ 3 "- ; AF: A AB ; & .- C86C9T

8/10/2019 Modul Microsoft Excel 2007 Tingkat Mahir1 21

12/21

- 8+# " # D & " # # 5%#- M56+T< " # & " # # 5%#- S

8/10/2019 Modul Microsoft Excel 2007 Tingkat Mahir1 21

13/21

/ # % #%!or Sele&t 9 Category ;8*5C9; & " "% 6

- 964 "" " %" # " %" & 8 # # " " "

& &- 8# "" " %" # " %" & 8 # # "

& # & # - 68T # %" 3 # % & & " - F9;S< & # %" 3 # % # - T#+< & # %" 3 # % # - 5F # " * % # # " & - S+M5F 3 # " & " " # " % #

". Fungsi fungsi lookup dan #eferensi

) " %% K C 8 $ # " $ 8% #

# 3 # 8 # & " # " #

* C & " # # "" ;ookup # 6

a. ';88(+ :lookupB-alue tableBarray &olBinde:BnumH E # 8 #

% % :0 $ ;

8/10/2019 Modul Microsoft Excel 2007 Tingkat Mahir1 21

14/21

b. ;88(+ :lookupB-alue tableBarray &olBinde:BnumH E #