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


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


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


4/21

ABACDE AF EF E A

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

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

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

1. Format Style

1.1 Conditional Formatting ! # " " $%$%

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

# # "

5 "" & 6

%

* / 5% # % )% "


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 )%


6/21

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

1.3 Cell Styles

& " # # ! " !

# "

5 "" &

% E

* B 5 ( &

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


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/21

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


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


12/21

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


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 $ ;


14/21

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


15/21

- Min :C " ; 6 $ $ # "- Sum :C " ; 6 $ 3 # # & " # # "- 9-erage :C " ; 6 $ - # "- Count :C " ; 6 $ 3 # & " # # "

- Ceiling :( ( " 8 ; 6 $ & " # " " 8 *

# # & +- Floor :( ( " 8 ; 6 $ & " # " " 8 *

# # & *- #oundup :( # " ; 6 $ " # + * # #

7 & 9- #ounddo n :( # " ; 6 $ ! " # + * #

# & 7 & +

5 " 3 6

D F # $ )1 # >( :516A1; L51L 1LA1

% F B # $ 5 1 # >( :5165 .;

C - F B # $ 5 = # >EI " :5165 .;F # # $ 5 H # > :5165 .;

F # $ 5 M # > 4:5165 .;

D # $ 5*7 # >5% :5165 .;

%. Fungsi fungsi Finansial

) # # " # % #%! ( $ E 5 "% & # )

# 3 "" 8 " )


16/21

5% % 6

" # % % " C 7 777 777 -

# " D " . N 3 " & " # " "

3 ! & " 8 " /0 :/ 0 ;

5% % 6

3 # # & $ C * 777 777 -

# " " # # .N # 3 .

" & " # & O 3 ! & " 8 " /


17/21

(asus 10

( , ( E 5 # ! # & ! # ( 3 9 3 &

3 , 3 ,, 3 ,,, # 3 ,0 B 3 # %

3 # " 6

- 3 , .N- 3 ,, +7N- 3 ,,, . N

- 3 ,0 97 N

D # # " 17 ! # 8

& " # # %

F% F 3 , 3 ,, 3 ,,, 3 ,0 E % H. H7 H* H

* # .1 =. =7 1*+ 5 11 1. 1M 1=9 9+ 1. 1+ .*. A# =+ =. == =91 ) M H. H. HH= B% += .9 .. 9MH ' # 19 17 1* .HM , =. .1 H= M7

7 D% % =1 1M = =+

" 8 " %" # & " #

% % B # " % % ' # " 8 " ,)

# P

(asus 20

# 8 # ! # 8 # " # 3 #

A # " " 6

4aftar 6ilai

F, FE E 3 , 3 ,, 3 ,,, E % H. H7 H** # .1 =. =7+ " 11 1. 1M9 5 9+ 1. 1+. A# =+ =. ==1 ) M* H. H.= " += .9 ..H J # 1= 17 1+M ,% =. .H .1

7 ( " " =1 1M HM

B% % # 3 , *.N 3 ,, *.N # 3 ,,, .7N 8

# % I 3 # 8 E 5 # A # " " 6


18/21

8 C 8F ' 8 (

# H. E ( " "E =7 Q H. "E .. Q =7 5 5E 97- .. B "B " # 97 A


19/21

Modul !. Men&ari data

E 8 %ome Find Sele&t $ & " 6

- FindI $ #

- #epal&eI "" # - *o toI, 3 # " $ # " $ " - *o to spe&ialI, $ # & " " # " - Formulas $ & " " # " 8%- Comments $ & " " # " %- Conditional Formatting # " & & " " # " $% # % 8% "

- Constants $ & " " # " %

- 4ata 'alidation # " & & " " # " I # #- Sele&t 8bJe&t # " & % 3 -% 3 - Sele&tion paneI, " # % 3 -% 3

1. Menggunakan Find

B %ome # # "


20/21

2. Menggunakan #epla&e

B %ome # # "


21/21

!. Menyaring data

1. 5 & & " # & " # "

2. B %ome # # "

modul microsoft excel 2007 tingkat mahir1 21

Documents