Bài giảng Excel - Lê Thị Minh Thùy

BÀI GI NG EXCEL

Ả

Gi ng viên: Lê Th Minh Thùy B môn: Công ngh ph n m m ề ả ộ ị ệ ầ

N i dung

ộ

ơ ả

ơ ở ữ ệ

ế

 Các thao tác c b n v i Microsoft Excel ớ  Các hàm s quan tr ng ọ ố  C s d li u (S p x p, tìm ki m) ế ắ  Đ thồ ị

10/30/14

2

Bài giảng Excel

Kh i đ ng Excel

ở ộ

 C1: Kích đúp chu t vào bi u t

ng trên

ể ượ

ộ

n n màn hình (Destop). ề

 C2: Menu Start/Programs/Microsoft

Office/Mircrosoft Excel

 …

10/30/14

3

Bài giảng Excel

C a s làm vi c c a Excel

ử ổ

ệ ủ

10/30/14

4

Bài giảng Excel

M m t t p tr ng m i (New) ắ

ở ộ ệ

ớ

 C1: Kích chu t vào bi u t

ng New trên

ể ượ

ộ

Ấ ổ ợ

Toolbar. h p phím Ctrl+N  C2: n t  C3: Vào menu File/New…/Workbook

10/30/14

5

Bài giảng Excel

đĩa

ổ

ng Open trên ượ ể

Ấ ổ ợ

M m t t p đã ghi trên ở ộ ệ (Open)  C1: Kích chu t vào bi u t ộ Toolbar. h p phím Ctrl+O  C2: n t  C3: Vào menu File/Open… 

ơ

ọ

ứ

ọ ệ ầ

1. Ch n n i ch a t pệ 2. Ch n t p c n mở

ấ

3. B m nút ấ Open đ m t p ể ở ệ

B m nút Cancel đ ể h y l nh m ở ủ ệ t pệ

6

10/30/14

Bài giảng Excel

đĩa (Save)

Ghi t p vào ệ

ổ

 C1: Kích chu t vào bi u t

 C2: n t

ng Save trên Toolbar. ể ượ ộ

 C3: Vào menu File/Save.

h p phím Ctrl+S. Ấ ổ ợ

tr

ướ ừ ướ thì l n ghi t p c ầ l n ghi tr ượ i s ghi l c ghi tr ạ ự ệ ướ

 N u t p đã đ ế ệ hi n t ệ ạ ẽ (có c m giác là Excel không th c hi n vi c gì ả

 N u t p ch a đ

). c c t i s thay đ i k t ổ ể ừ ầ ệ ệ ự

ư ẽ ệ ấ ộ

ế ệ ạ ượ ọ ệ ơ

10/30/14

7

Bài giảng Excel

s xu t hi n h p c ghi l n nào ầ tho i Save As, ch n n i ghi t p trong khung Save in, gõ tên t p c n ghi vào khung File name, n nút Save. ệ ầ ấ

ớ

Ghi t p vào đĩa v i tên khác (Save ổ ệ As) Khi ghi t p v i 1 tên khác thì t p cũ v n t n t i, ẫ ồ ạ ệ ớ c t o ra có cùng n i dung v i t p ớ ệ

ớ ượ ạ

ộ

ệ t p m i đ ệ cũ.

 Vào menu File/Save As... 

1. Ch n n i ghi t p ệ ơ

ọ

2. Gõ tên m i cho t p ệ

ớ

ấ

3. B m nút ấ Save đ ghi ể t pệ

B m nút Cancel đ ể h y l nh ghi ủ ệ t pệ

8

10/30/14

Bài giảng Excel

Thoát kh i Excel (Exit)

ỏ

h p phím Alt+F4

Ấ ổ ợ

 C1: n t  C2: Kích chu t vào nút Close

góc trên cùng

ộ

ở

bên ph i c a s làm vi c c a PowerPoint.

ệ ủ

đĩa

thì xu t hi n 1

ả ử ổ  C3: Vào menu File/Exit  N u ch a ghi t p vào

ổ

ệ

ấ

ư

ế

ệ

Message Box, ch n:ọ  Yes: ghi t p tr ệ  No: thoát không ghi t p,ệ  Cancel: hu l nh thoát.

c khi thoát, ướ

10/30/14

9

Bài giảng Excel

ỷ ệ

Đ a ch ô và mi n

ề

ỉ

ị

c dùng trong ỉ ị ủ ế ỉ

 Đ a ch ô và đ a ch mi n ch y u đ ề ứ ể ấ ữ ệ ươ ứ

ượ ng ng.

ị các công th c đ l y d li u t ồ

 Đ a ch ô bao g m: ỉ ị  Đ a ch t ố ỉ ươ ị

ng đ i: g m tên c t và tên hàng. Ví d : ụ ộ ồ

A15, C43.

ỉ ị ộ

c tên c t và ụ ướ ầ ố

ng đ i thay đ i khi sao chép công th c, ứ ố

 Đ a ch tuy t đ i: thêm d u $ tr /ho c ặ ệ ố ấ tên hàng n u mu n c đ nh ph n đó. Ví d : $A3, ố ị ế B$4, $C$5. Đ a ch t ổ ỉ ươ ị đ a ch tuy t đ i thì không. ệ ố ị

10/30/14

10

Bài giảng Excel

ỉ

Đ a ch ô và mi n (ti p)

ề

ế

ị

ỉ

ề

ề

ị

 Mi n là m t nhóm ô li n k nhau.  Đ a ch mi n đ ỉ ị

ề c khai báo theo cách: ấ

ộ ề ượ ỉ

ỉ

ị

Đ a ch ô cao trái : Đ a ch ô th p ph i ả B2:D5

Ví dụ: A3:A6

$C$5:$D$8

10/30/14

11

Bài giảng Excel

D ch chuy n con tr ô ể

ỏ

ị

 Dùng chu t kích vào ô. ộ  Gõ phím F5 (Ctrl+G), gõ đ a ch ô c n đ n vào

ế

ầ

ỉ

ị

khung Reference, b m nút OK.

ấ

Gõ đ a ch ô ỉ ị mu n đ n ế ố

 Dùng các phím sau đây:

10/30/14

12

Bài giảng Excel

Các phím d ch chuy n con tr ô:

ỏ

ể

ị

ng mũi tên

ướ

i.

ủ

ủ

ộ

ị ị ị i ủ ộ ầ i c t cu i cùng (c t IV) c a dòng hi n t ớ ộ ệ ạ ộ ố i. i c t đ u tiên (c t A) c a dòng hi n t ệ ạ ớ ộ ầ i dòng cu i cùng (dòng 65536) c a c t hi n ủ ộ ớ

ệ

ố

i.

ủ ộ

ệ ạ

ầ

i cùng (ô A65536).

ướ i ô ph i d

i cùng (ô IV65536).

i dòng đ u tiên (dòng 1) c a c t hi n t t ớ t i ô trái trên cùng (ô A1). ớ t i ô ph i trên cùng (ô IV1). ả ớ i ô trái d t ớ t ớ

, fl , › , fi + ‹ d ch chuy n 1 ô theo h ể d ch con tr lên 1 trang màn hình. + Page Up ỏ + Page Down d ch chuy n xu ng 1 trang màn hình. ố ể c t đ u tiên (c t A) c a dòng hi n t + Home ệ ạ ộ + Ctrl + fi t + Ctrl + ‹ t + Ctrl + fl t i.ạ t + Ctrl + › + Ctrl + › + ‹ + Ctrl + › + fi + Ctrl + fl + ‹ + Ctrl + fl + fi 10/30/14

13

ả ướ Bài giảng Excel

Ch n mi n, c t, hàng, b ng ộ

ọ

ề

ả

 Ch n mi n: kích chu t vào ô cao trái, gi

và di t

i ô th p

ộ

ữ

ớ

ấ

ộ

ộ

ộ

ộ

 Ch n c hàng: kích chu t vào ô tên hàng.  Ch n c c t: kích chu t vào ô tên c t.  Ch n c b ng tính: kích chu t vào ô giao gi a tên hàng

ữ

ộ

ề ọ ph i, nh chu t. ả ả ọ ả ọ ả ộ ả ả ọ

 N u ch n nhi u mi n r i nhau thì gi

phím Ctrl trong

ề ờ

ữ

và tên c t.ộ ọ

ế

ề

khi ch n các mi n đó. ỉ

 Khi c n l y đ a ch ô ho c mi n trong công th c thì ặ bàn phím mà nên dùng chu t ch n đ ể

ứ ọ

ộ

ề ọ ề ầ ấ ị không nên gõ t ừ tránh nh m l n. ầ

ẫ

10/30/14

14

Bài giảng Excel

Nh p d li u vào ô

ậ ữ ệ

 Cách th c: kích chu t vào ô, gõ d li u vào,

ữ ệ

ứ

ộ

nh p xong gõ Enter.

ậ

 D li u ch nh p bình th

ng

ữ ậ

ữ ệ

ườ

 D li u s nh p d u ch m (.) thay d u ph y (,)

ẩ

ấ

ữ ệ ố

ậ

ấ ngăn cách ph n th p phân. ầ

ấ ậ

 Đ Excel d ng ch

ữ ệ ể

 Ví d : ụ ’04.8766318

10/30/14

15

Bài giảng Excel

hi u m t d li u d ng khác là d li u ạ c d li u đó. ướ ữ ệ ộ ữ ệ ấ ’ tr ữ thì nh p d u ậ ể ạ

Nh p d li u vào ô

ậ ữ ệ

ạ

ữ ệ ặ

ệ

ặ

ộ

 D li u ngày tháng nh p theo d ng MM/DD/YY ậ ho c DD/MM/YY tùy thu c vào vi c cài đ t thông s c a Windows.

ố ủ

 Đ xem cài đ t c a h th ng vào Start / Control

ể



ở

ậ

ặ ủ ệ ố Panel / Regional and Language Options. d ng tab Format, n u Short date ạ ế Ở MM/DD/YY thì d li u khi nh p ph i nh p theo ậ ả ữ ệ d ng d ng MM/DD/YY. N u Short date ở ạ ế ạ DD/MM/YY thì ph i nh p là DD/MM/YY ậ ả

10/30/14

16

Bài giảng Excel

Công th cứ

 Công th c:ứ

 b t đ u b i d u  sau đó là các h ng s , đ a ch ô, hàm s đ

ắ ầ

ở ấ = ằ ố ị ỉ ố ượ c n i ố

 Các phép toán: + , - , * , / , ^ (lu th a)

v i nhau b i các phép toán. ở ớ

 Ví d :ụ

= 10 + A3 = B3*B4 + B5/5 = 2*C2 + C3^4 – ABS(C4) = SIN(A2)

10/30/14

17

Bài giảng Excel

ỹ ừ

ộ

Công th cứ N u th y ấ ế ###### #DIV/0! #NAME?

ự

ộ

ớ ị

#N/A

Có th là do ể C t quá h p ẹ Chia cho 0 Th c hi n phép tính v i m t ệ bi n không xác đ nh ế ỗ

ế

ộ

ặ

#VALUE! AE+b (AE-b)

Tham chi u đ n m t ô r ng ế ho c không có trong danh sách Sai v ki u c a toán h ng ạ ề ể ủ b (A*10-b) Giá tr b ng A*10 ị ằ

10/30/14

18

Bài giảng Excel

ạ



/Copy) ấ



ề ớ ị

ế

ấ

góc ph i d ấ ở

ỉ ươ

Các thao tác so n th o ả 1. Sao chép (Copy):  Ch n mi n ề ọ n Ctrl+C (b m nút Copy, menu Edit Ấ  D ch t i ô trái trên c a mi n đ nh dán ủ ị /Paste) n Ctrl+V (b m nút Paste, menu Edit Ấ N u sao chép công th c sang các ô lân c n: ậ ứ i d u ch m  Di chu t t ả ướ ủ b m gi ộ ấ nh chu t (Drag & Drop). Đ a ch t ả các ô trong công th c s đ

10/30/14

19

Bài giảng Excel

c thay t i c a ô, ộ ớ ấ trái chu t và di qua các ô lân c n r i ậ ồ ữ ng đ i c a ố ủ ị ộ ng ng. ứ ẽ ượ ươ ứ

Các thao tác so n th o (2)

ả

ạ

2. D ch chuy n (Move):

ị

ể ề



/Cut)

ấ

ề

ớ

ị



 Ch n mi n ọ n Ctrl+X (b m nút Cut, menu Edit Ấ i ô trái trên c a mi n đ nh dán  D ch t ủ ị n Ctrl+V (b m nút Paste, menu Edit Ấ

ấ  C2: di chu t t

/Paste) ỏ

ề ờ ữ ệ ớ ị

ớ

ế ữ ệ

ố

phím Shift trong khi Drag.

i b c a mi n, con tr thành ộ ớ ờ ủ i v trí m i, hình mũi tên, Drag r i d li u t thay th d li u cũ. Mu n xen k d li u cũ ẽ ữ ệ và m i thì gi ớ

ữ

10/30/14

20

Bài giảng Excel

Các thao tác so n th o (3)

ả

ạ

ớ ử ộ

3. S a:ử D ch t i ô c n s a: kích đúp chu t, ho c n ị phím F2, ho c kích chu t trên thanh công th c, ộ con tr nh p nháy trong ô thì s a bình th

ầ ặ

ặ ấ ứ ng. ử

3. Xoá: 4. Undo và Redo:

ườ ỏ Ch n mi n c n xoá, n phím Delete. ấ ọ ề ầ ấ

ặ ấ

ụ ỷ ỏ ệ ể

ụ ả ạ

10/30/14

21

Bài giảng Excel

i vi c v a b Undo: Ctrl+Z, ho c b m nút trên Toolbar: có tác d ng hu b vi c v a làm, hay dùng đ khôi ừ ph c tr ng thái làm vi c khi x y ra sai sót. ệ Redo: Ctrl+Y, ho c b m nút trên Toobar: làm ấ l ệ ừ ạ ặ ệ ừ ỏ / vi c v a làm.

M t s thao tác h u d ng

ữ ụ

ộ ố

ả

1. Thêm/xoá hàng, c t, b ng tính: ộ  Thêm m t hàng vào bên trên hàng hi n t

 Thêm m t c t vào bên trái c t hi n t

i: menu ệ ạ ộ

i: menu ệ ộ ạ Insert/Rows ộ

ộ Insert/Columns

/Worksheet ộ ả

i: menu Edit ệ ạ

i: menu Edit /Delete…Entire Row /Delete…Entire Column

 Thêm m t b ng tính (sheet): menu Insert  Xoá hàng hi n t  Xoá c t hi n t ệ ạ ộ  Xoá b ng tính: menu Edit ả

10/30/14

22

Bài giảng Excel

/Delete Sheet

M t s thao tác h u d ng (2)

ữ ụ

ộ ố

ậ ữ ệ

2. Các thao tác giúp nh p d li u:  Gõ đ a ch tuy t đ i c a ô và mi n trong công

ị

ề

ệ ố ủ

ứ

ề

ầ

ọ

ộ

6,000.00

, VD: $

ề ệ

ấ

ỉ th c: dùng phím F4 VD: c n gõ $A$5:$C$8: dùng chu t ch n mi n A5:C8, r i n phím F4. ồ ấ  Nh p d li u ti n t ậ ữ ệ ch c n nh p 6000, sau đó n nút Currency $ ậ ỉ ầ trên thanh đ nh d ng. ị

ạ

10/30/14

23

Bài giảng Excel

M t s thao tác h u d ng (ti p)

ữ ụ

ộ ố

ế

ộ ậ ề ề

 Nh p m t dãy liên t c cách đ u vào các ô li n k ề nhau (không b t bu c tăng 1 đ.v ), vd: nh p STT, ắ nh p các tháng trong năm…:

ụ ộ ậ ị

ậ

ng ng ậ ươ ứ



ầ ừ

ự 3. Đ i tên sheet: ả ạ

góc i b ng tính, ch n Rename, gõ tên m i cho

10/30/14

24

Bài giảng Excel

/t đ u tiên c a dãy vào 2 ô t  Nh p 2 g ủ  Bôi đen 2 ô v a nh p ậ Th c hi n Drag nh khi sao chép công th c ứ ư ệ nháy chu t ph i t i tên sheet ở ộ ổ trái d ớ ọ ướ ả sheet r i n Enter. ồ ấ

Đ nh d ng

ạ

ị

c hàng

ộ

ướ

/c t: 2 cách chính: ỏ

ế

ả

/c t, kích đúp đ ể ộ

c v a khít.

/c t b ng cách vào c hàng

1) Thay đ i kích th ổ /c t, con tr thành  C1: Di chu t vào mép hàng ộ ộ hình mũi tên 2 chi u, n gi trái chu t, di đ n ề ấ ộ ữ v trí m i r i nh chu t. ộ ớ ồ ị  C2: Di chu t vào mép hàng ộ c kích th ướ ừ Có th n đ nh kích th ướ ị

ộ ằ

10/30/14

25

Bài giảng Excel

đ ượ ể ấ menu Format/Row/Height… và Format/Column/Width…

Đ nh d ng (2) ạ

ị

ị ọ

ạ ề

2) Đ nh d ng ô (Menu Format - Ch n mi n, vào menu Format + Tab Number: đ nh cách hi n th s ị

/Cells…) /Cells… ị ố

ể

ị ỉ ữ li uệ

ị

10/30/14

26

Bài giảng Excel

ữ ng k vi n các ô + Tab Alignment: đ nh cách ch nh v trí d ị + Tab Font: đ nh font ch + Tab Border: đ nh đ ườ ẻ ề ị

Menu Format/Cells… Tab Number

Khung

xem tr

cướ

S ch s ố ữ ố th p phân ậ

Ki u ể hi n th ị ể số

ử ụ

ệ

S d ng ký hi u ngăn cách hàng nghìn

Cách hi n ể th s âm ị ố

Chú gi

iả

10/30/14

27

Bài giảng Excel

Menu Format/Cells… Tab Alignment

ữ ệ

Căn d li u chi u ngang ô

ề

Đ nh h

ng

ị ướ văn b nả

Căn d li u ữ ệ chi u d c ô ọ

ề

Xu ng dòng ố v a đ r ng ô ừ ộ ộ

Nh p các ô li n ề ậ k thành 1 ô ề

kích th

c ô

Thu nh ỏ ch v a ữ ừ ướ

10/30/14

28

Bài giảng Excel

Menu Format/Cells… Tab Font

Ch n ọ ki u chể ữ

Ch n ọ phông chữ

c ch

Ch n kích th ữ

ọ ướ

G ch ạ chân chữ

Ch n màu ọ chữ

Xem tr

cướ

10/30/14

29

Bài giảng Excel

Menu Format/Cells… Tab Border

Không k ẻ khung

Khung bao ngoài

Khung bên trong

Ch n ọ ki u ể ng k

đ

ườ

ẻ

Ch n ọ t ng ừ

Màu ng k

đ

ườ

ẻ

đ

ng k ẻ

ườ khung

10/30/14

30

Bài giảng Excel

ỗ ề ố ố ử ụ ử

ế ng hay hoa ho c v a vi ặ ừ

th

Hàm số  Excel có r t nhi u hàm s s d ng trong các lĩnh ấ , v c: toán h c, th ng kê, logic, x lý chu i ký t ự ọ ự ngày tháng … ạ ổ ể ế ế

ườ

ng v a vi ừ ế c. ặ

ư ở ấ ả ẩ ấ

 Các hàm có d ng t ng quát: Tên hàm(các tham bi n) t th t  Tên hàm có th vi ế ườ t hoa đ u đ ề ượ  Các tham bi n có th có ho c không nh ng ph i ể đ t trong hai d u ( ) và cách nhau b i d u ph y ặ (ho c d u ch m ph y ho c m t d u ngăn cách nào ặ ặ khác).

10/30/14

31

Bài giảng Excel

ộ ấ ẩ ấ ấ

ổ ấ ữ ế

ọ

ổ ấ

Hàm số  Đ thay đ i d u cách nhau gi a các tham bi n ta ể vào Start / Control Panel / Regional and Language Options / tab Formats. Sau đó ch n Customize Regional Options, ch n tab Numbers. Thay đ i d u cách nhau

ở

ọ dòng List separator. ượ

ứ

c có d u cách  Trong hàm không đ ấ c dùng trong công th c.  Hàm s đ ố ượ  Hàm ph i b t đ u b i d u = ho c d u c a m t ặ ở ấ ả ắ ầ ủ ấ ộ

 Trong hàm có x lý các h ng ký t ử

phép tính.

ằ ặ

10/30/14

32

Bài giảng Excel

ký t thì chúng ph i đ ự ả ượ ho c h ng xâu ằ ự ấ “ ” c bao trong c p d u ặ

Cách nh p hàm s vào b ng tính

ố

ả

ậ

bàn phím

 Gõ vào t

 S d ng Insert Function f

ặ

x (ho c vào Insert /

ừ  Gõ d u =ấ  Gõ vào tên hàm, d u ( ấ  Các tham bi n theo đúng d ng th c quy đ nh  Gõ d u )ấ ử ụ Function)

10/30/14

33

Bài giảng Excel

ứ ế ạ ị

Cách nh p hàm s vào b ng tính

ố

ậ

ả

ọ ọ

Ch n các Ch n các nhóm hàm nhóm hàm

Tìm ki m ế hàm

Ch n ọ Ch n ọ Ch n ọ Ch n ọ Ch n ọ hàm hàm hàm hàm hàm

Ch p ấ Ch p ấ Ch p ấ nh n thì nh n thì nh n thì ậ ậ ậ b m OK b m OK b m OK

ấ ấ ấ

10/30/14

34

Bài giảng Excel

Cách nh p hàm s vào b ng tính

ố

ả

ậ

ậ

Nh p các tham bi nế

 N u tham bi n là đ a ch ô hay mi n thì s d ng chu t đ ộ ể

ử ụ

ề

ế

ỉ

ị

ế ch n.ọ

10/30/14

35

Bài giảng Excel

M t s hàm s quan tr ng ố

ộ ố

ọ

: phép VÀ, là hàm logic, ố

ỉ ố t c các đ i s có giá tr đúng. ấ ả

.

 AND (đ i 1, đ i 2,…, đ i n) ố ố ố ể

ố ố

ch đúng khi t ị Các đ i s là các h ng, bi u th c logic ứ ằ VD: = AND (B3>=23,B3<25)

 OR (đ i 1, đ i 2, …, đ i n) ố

ố ố

t c các đ i s có giá tr sai. logic, ch sai khi t ỉ ấ ả ố ố : phép HO C, là hàm Ặ ị

10/30/14

36

Bài giảng Excel

VD: = OR (D3>=25,D3<23)

M t s hàm s quan tr ng (2) ố

ộ ố

ọ

 SUM (đ i 1, đ i 2, …, đ i n)

:

ố

ố

ố

ố ố ằ

ị

cho t ng c a các đ i s ủ ổ Các đ i s là các h ng, đ a ố ố ề . ch ô, mi n

ỉ

 AVERAGE (đ i 1, đ i 2, …, đ i

ố

ố

ố n): cho giá tr TBC c các s ị

ố

10/30/14

37

Bài giảng Excel

M t s hàm s quan tr ng (3) ố

ộ ố

ọ

 MAX (đ i 1, đ i 2, …, đ i n)

: cho giá tr l n nh t.

ố

ố

ố

ị ớ

ấ

 MIN (đ i 1, đ i 2, …, đ i n)

ố

ố

ố

: cho giá tr nh nh t. ị

ấ

ỏ

10/30/14

38

Bài giảng Excel

M t s hàm s quan tr ng (4) ố

ộ ố

ọ

ị

: n u ế BT logic có g/t True

 IF (bt logic, tr đúng, tr sai) ị ị tr đúng ị ị tr sai ị

n u ế BT logic có g/t False

t”)

ượ

 - Hàm IF có th vi

 Hi n th ể  Hi n th ể VD: =IF(A3>=5,“Đ ”,“Tr ỗ t l ng nhau. ể ế ồ

ế

1

- Hàm trên cho k t qu c a phép th sau: ữ ệ

£ (cid:236)

(cid:239)

400

ữ ệ

£ (cid:237)

2 3

(cid:239)

(cid:238)

300 < [d li u trong ô C6] > 400

VD: = IF(C6<=300,1,IF(C6<=400,2,3)) ử ả ủ n u [d li u trong ô C6] ế n u 300 ế n u [d li u trong ô C6] ế

ữ ệ

10/30/14

39

Bài giảng Excel

M t s hàm s quan tr ng (5) ố

ộ ố

ọ

 SUMIF (mi n_đ/k, đ/k, mi n_t ng)

: hàm tính

ề ổ

ề

t ng có đi u ki n ệ ổ

ề

ề

ứ

ề

ậ

ặ

ả ử ứ

ề

ề 21,

ứ thì

14,

ng

28

7,

ứ

ế

ả

ằ

Gi /t ti n nh p 4 m t hàng s mi n B2:B5 ch a các g t ng ng 100, 200, 300, 400. Mi n C2:C5 ch a ti n lãi ươ hàm t ng ươ SUMIF(B2:B5,“>160”,C2:C5) cho k t qu b ng 63 (=14+21+28)

10/30/14

40

Bài giảng Excel

M t s hàm s quan tr ng (6) ố

ộ ố

ọ

ng các ô có

 COUNT(đ i1, đ i2,…) ố

ố ượ

ế

ứ ố

: đ m s l ố ch a s và các s trong các đ i s . ố ố ố  Các đ i s là các s , ngày tháng, đ a ch ô, đ a ch ỉ ị

ố ố ố ỉ ị

mi n.ề

Ví d 1ụ

Ví d 2ụ

10/30/14

41

Bài giảng Excel

M t s hàm s quan tr ng (7) ố

ộ ố

ọ

ế

ề

ệ : đ m s ố ế ng các ô trong mi n đ m tho mãn đi u

 COUNTIF(mi n_đ m, đi u_ki n) ả

ề ế

ề

ề

l ượ ki n.ệ

Ví d 1ụ

Ví d 2ụ

10/30/14

42

Bài giảng Excel

M t s hàm s quan tr ng (8) ố

ộ ố

ọ

ứ

ị

 VLOOKUP (tr _tra_c u, b ng_tra_c u, c t_l y_d _li u, ả ộ ấ ữ ệ /t trong c t đ u tiên c a /t v i các g ủ ộ ầ ớ ng ng trong b ng tra c u n m ằ ả ị ữ ệ ươ

ứ

ứ

/t nh nh t trong c t đ u tiên c a

ỏ

ộ ầ

ủ

ấ

ế ố ố ứ

(ho c 1): ặ

c s p x p tăng

ủ ả

ộ ầ

ả ượ ắ ế

/t tra c u ứ không có trong c t đ u tiên c a b ng thì

ủ

ả

ứ [True/False]): tra c u gứ b ng và hi n th d li u t ể ả đ i s 3. trên c t ộ ở ố ố VD: = VLOOKUP(E3, $E$12:$F$16, 2, True) /t tra c u nh h n g - N u gế ỏ ơ ứ ả ề ỗ #N/A. i b ng thì tr v l ả - N u đ i s th 4 b ng True ằ + Các g/t trong c t đ u tiên c a b ng ph i đ d n.ầ + N u gế £ hàm s ẽ tra c u g/t trong b ng

ộ ầ g/t tra c uứ .

ứ

ả

10/30/14

43

Bài giảng Excel

Ví d hàm VLOOKUP

ụ

10/30/14

44

Bài giảng Excel

M t s hàm s quan tr ng (9) ố

ộ ố

ọ

: hàm

ệ ấ hàm VLOOKUP

 HLOOKUP(g/t, b ng_g/t, hàng_l y_d.li u, [1/0]) ng t

ứ

ự

ế

ứ ạ ế

ẩ

ế ể

ứ

/t (VD: b ng đi m) ả

ể

ố ố ố ố ố ố

ấ ế

ả tra c u theo hàng, t ươ  Hàm x p th h ng: RANK(g/t_x p, b ng_g/t, tiêu_chu n_x p) ả - đ i s 1: là giá tr c n x p th (VD: đi m 1 HS) ị ầ ế - đ i s 2: b ng ch a các g ứ ả - đ i s 3: = 0 thì g ố ỏ x p th h ng các HS trong l p theo đi m) ế

ứ ạ

= 1 thì g/t nh nh t x p đ u tiên (VD khi x p

ớ ấ ế

ế

ỏ

th h ng cho các VĐV đua xe theo th i gian)

/t nh nh t x p cu i cùng (VD khi ể ầ ờ

ứ ạ

VD: =RANK(A3,$A$3:$A$10,1)

10/30/14

45

Bài giảng Excel

Ví d hàm RANK

ụ

RANK(g/t_x p, b ng_g/t, tiêu_chu n_x p)

ế

ế

ả

ẩ

Khi th th x p b ng 1

Khi th th x p b ng 0

ứ ự ế

ằ

ứ ự ế

ằ

10/30/14

46

Bài giảng Excel

M t s hàm s quan tr ng (10)

ộ ố

ọ

ố

Cho n ký t

bên trái c a

 LEFT(“Chu i ký t ”, n): ỗ

ự

ự

ủ

chu i.ỗ  VD: =LEFT(“Gia Lâm – Hà N i”,7) ộ cho k t qu là chu i “Gia Lâm”

ế

Cho n ký t

bên ph i c a

ả  RIGHT(“Chu i ký t ”, n): ỗ

ỗ ự

ự

ả ủ

ộ

ế

ả

ỗ

ộ Cho n ký t

tính t

ký t

chu i.ỗ  VD: =RIGHT(“Gia Lâm – Hà N i”,6) cho k t qu là chu i “Hà N i” ự

ự

ừ

th ự ứ

ủ

 MID(“Chu i ký t ”, m, n): ỗ m c a chu i. ỗ  VD: =MID(“Gia Lâm–Hà N i”,9,2)

ộ

cho k t qu là chu i “Hà”

ế

ả

10/30/14

47

ỗ Bài giảng Excel

M t s hàm s quan tr ng (11)

ộ ố

ố

ọ

i.

th i đi m hi n t ể

ệ ạ

ờ ở ờ ệ ạ

 NOW(): Cho ngày và gi  TODAY(): Cho ngày hi n t  DAY(“mm/dd/yy”): Cho giá tr ngày.

i. ị

 VD: =DAY(“11/25/80”) cho k t qu là 25

 MONTH(“mm/dd/yy”): Cho giá tr tháng.

ị

ế ả

 VD: =MONTH(“11/25/80”) ả

10/30/14

48

Bài giảng Excel

cho k t qu là 11 ế

M t s hàm s quan tr ng (12)

ộ ố

ọ

ố

 YEAR(“mm/dd/yy”): Cho giá tr năm.

ị

ế

 VD: =YEAR(“11/25/80”) cho k t qu là 1980 ả  Hàm Year th ườ ngày sinh:

10/30/14

49

Bài giảng Excel

ng đ c dùng đ tính tu i khi bi t ượ ể ổ ế

ơ ở ữ ệ

C s d li u (CSDL) 1) Khái ni mệ

ng (field) và b n ghi

 CSDL g m các tr ồ

ườ

ả

ng là m t c t CSDL, m i tr ộ ộ ỗ ườ

(record).  Tr ườ m t thu c tính c a đ i t ộ ộ nh t đ nh. ấ ị ả

 B n ghi là m t hàng d li u. ộ

ủ ố ượ ng bi u th ể ị ng và có ki u d li u ữ ệ ể

 Dòng đ u c a mi n CSDL ch a các tên tr

ng,

ề

ườ

ứ các dòng ti p sau là các b n ghi.

ầ ủ ế

ả

10/30/14

50

Bài giảng Excel

ữ ệ

2) S p x p - Menu Data/Sort

ắ ế

1 danh sách (CSDL), ph i

 Khi x p th t ế

ứ ự

ả ch n t

t c ọ ấ ả

các c tộ đ tránh s m t chính xác d li u. ự ấ

ữ ệ

ể

 DS không có tên tr

ườ

ng thì tên c t s thay th . ế ộ ẽ

ị

g i là /t ở

khoá. Có th ể khoá th nh t khoá th 2

ứ ấ ứ

ở

ứ ự ọ ế 3 khoá. Các b n ghi cùng g ả theo khoá th 2 theo khoá th 3.

ng quy đ nh cách x p th t  Tr ườ i đaố t đ nh ị c x p th t đ ứ ự ế ượ c x p th t đ ứ ự ượ ế

ứ ; cùng g/t ứ

 Cách làm: Ch n mi n. Ch n Menu Data

/Sort…

ề

ọ

ọ

10/30/14

51

Bài giảng Excel

Ch n khoá ọ th nh t ứ ấ

ắ

ế

S p x p tăng d nầ

[Ch n khoá th hai]

ọ ứ

S p x p ế gi m d n ầ

ắ ả

ọ

[Ch n khoá th ba]ứ

Dòng đ u là ầ ng tên tr ườ (ko s p x p) ế ắ

Ko có dòng tên tr ng ườ (s p x p c ế ả ắ dòng đ u)ầ

X p t ế ừ xu ng d ố

trên i ướ

trái

X p t ế ừ sang ph iả

10/30/14

52

Bài giảng Excel

ọ ữ ệ

ế

3) Tìm ki m (L c d li u) Menu Data/Filter

 M c đích: L y ra nh ng b n ghi (thông tin) tho ả

ả

ụ

ấ mãn đi u ki n nh t đ nh. ệ

ữ ấ ị  Có th l c theo 2 cách:

ề ể ọ

 AutoFilter: Excel h tr đi u ki n l c ệ ọ i s d ng t  Advanced Filter…: ng

ỗ ợ ề

đ nh đi u ườ ử ụ ự ị ề

10/30/14

53

Bài giảng Excel

ki n l c. ệ ọ

a) L c d li u dùng AutoFilter

ọ ữ ệ

ng

ọ

ề

ả

ồ

ườ

 Ch n mi n CSDL g m c dòng tên tr  Menu Data/Filter/AutoFilter, ô tên tr

ng có đ u

ườ

ầ

mũi tên th xu ng c a h p danh sách

ủ ộ

ố

ả ộ

ả

ố

 Kích chu t vào đó, có danh sách th xu ng: i m i b n ghi  All: đ hi n l ọ ả  Top 10…: các giá tr l n nh t ấ ị ớ đ nh đi u ki n l c  Custom…: t ệ ọ ề ự ị  Các giá tr c a c t ị ủ ộ

10/30/14

54

Bài giảng Excel

ể ệ ạ

L c d li u dùng AutoFilter (ti p)

ọ ữ ệ

ế

tho i Custom

ế

ẽ

ộ

ọ

AutoFilter đ ng

i s d ng t

 N u ch n Custom… s hi n h p ề ể ườ ử ụ

ệ đ nh đi u ki n l c: ự ị

ạ ệ ọ

ả

VD: L c nh ng b n ả ọ ữ tho mãn s ghi ố l ra ng SP bán ượ trong tháng 1 n m ằ trong kho ng ả (120,400]

10/30/14

55

Bài giảng Excel

b) L c d li u dùng Advanced Filter

ọ ữ ệ

1. B1: Đ nh mi n đi u ki n:

ề

ề

ệ

ị

 Dòng đ u ghi tên tr ầ

ườ ệ

ả

 Các dòng ti p d

ệ ố t nh t là copy t ng đ đ nh đi u ki n, chú ề ể ị ng c a mi n CSDL, ề ủ ng CSDL. ý ph i gi ng h t tên tr tên tr t ố ừ ấ ườ ườ

ế ướ ề

ề ệ ề ệ

 VD v i mi n CSDL nh trên: ề

ư

ớ

10/30/14

56

Bài giảng Excel

i ghi đi u ki n: các đi u ệ ki n cùng dòng là phép AND, các đi u ki n khác dòng là phép OR.

ề ề

ả ả

ề ề

ả ả

ố ố

ố ố

Mi n đ/k đ l c các b n ể ọ Mi n đ/k đ l c các b n ể ọ ghi có s SP bán ra trong ghi có s SP bán ra trong tháng 1 =400 tháng 1 =400

Mi n đ/k đ l c các b n ể ọ Mi n đ/k đ l c các b n ể ọ ghi có s SP bán ra trong ghi có s SP bán ra trong tháng 1 >>150150 tháng 1

ả ả

ề ề

ề ề

ả ả

Mi n đ/k đ l c các b n ể ọ Mi n đ/k đ l c các b n ể ọ ghi có s SP bán ra trong ố ghi có s SP bán ra trong ố £ 500500 tháng 1 150<

Mi n đ/k đ l c các b n ể ọ Mi n đ/k đ l c các b n ể ọ ghi có s SP bán ra trong ố ghi có s SP bán ra trong ố 150 ho c trong tháng 1 >>150 ho c trong ặ tháng 1 ặ ‡ 200200

tháng 2 ‡ tháng 2

10/30/14

57

Bài giảng Excel

L c d li u dùng Advanced Filter (ti p)

ọ ữ ệ

ế

2. B2: Th c hi n l c ệ ọ ự  Vào menu Data/Filter/Advanced Filter…

t

Hi n KQ l c ngay ọ i mi n d li u ề ữ ệ

ệ ạ

ệ

Hi n KQ l c ra n i ơ ọ khác Ch n mi n CSDL ề

ọ

Ch n mi n đi u ki n ệ

ề

ề

ọ

Ch n mi n hi n KQ ề

ệ

ọ

ả

ỉ ệ

Ch hi n 1 b n ghi trong s nh ng KQ ố ữ trùng l pặ

10/30/14

58

Bài giảng Excel

Đ thồ ị

 Ch n mi n d li u v đ th ,

ọ

ề

ữ ệ

ả ọ ồ ị ể

ề ộ ố ớ

ẽ ồ ị chú ý ch n c 1 tiêu đ ề hàng và 1 tiêu đ c t đ i v i các đ th ki u Column, Line và Pie.

ấ

ặ

ệ

ạ

 B m nút Chart Wizard trên Toolbar ho c vào menu H p tho i Chart Wizard hi n ra giúp ộ c: ướ

ị ị

i …

ụ

ả

Insert/Chart… fi t o đ th qua 4 b ồ ị ạ 1. Đ nh ki u đ th ồ ị ể 2. Đ nh d li u ữ ệ 3. Các l a ch n: tiêu đ , các tr c, chú gi ề ọ 4. Ch n n i hi n đ th ồ ị

ự ọ ơ

ệ

10/30/14

59

Bài giảng Excel

B

ướ

c 1: Đ nh ki u đ th ể ồ ị

ị

Ch n ki u đ th có s n: Ch n ki u đ th có s n:

ồ ị ồ ị

ể ể

ẵ ẵ

ọ ọ

+ Column: c t d c ộ ọ + Column: c t d c ộ ọ

+ Line: đ + Line: đ

ng so sánh ng so sánh

ườ ườ

+ Pie: bánh tròn + Pie: bánh tròn

ng t ng t

ng quan ng quan

ươ ươ

+ XY: đ ườ + XY: đ ườ (XY scatter) (XY scatter)

ọ ọ

ộ ạ ộ ạ

Ch n m t d ng Ch n m t d ng c a ki u đã ch n ọ ủ c a ki u đã ch n ọ ủ

ể ể

10/30/14

60

Bài giảng Excel

B

c 2: Đ nh d li u

ướ

ữ ệ

ị

Tiêu đ c t ề ộ Tiêu đ c t ề ộ iả làm chú gi iả làm chú gi

ề ề

Mi n DL v đ ẽ ồ Mi n DL v đ ẽ ồ thịthị

Tiêu đ hàng ề Tiêu đ hàng ề i đây hi n t ệ ạ i đây hi n t ệ ạ

ọ ọ

Ch n DL v đ ẽ ồ Ch n DL v đ ẽ ồ th theo hàng ho c ặ ị th theo hàng ho c ặ ị theo c tộ theo c tộ

10/30/14

61

Bài giảng Excel

ướ

c 3: Các l a ch n - Tab Titles B ọ Tiêu đ đ th và tiêu đ các tr c ụ

ự ề ồ ị

ề

Nh p tiêu ậ đ đ th ề ồ ị

Nh p tiêu ậ đ tr c X ề ụ

Nh p tiêu ậ đ tr c Y ề ụ

10/30/14

62

Bài giảng Excel

c 3: Các l a ch n - Tab Legend ọ

ự

B ướ Chú gi

iả

Chú gi

iả

Hi n/ n ệ ẩ Hi n/ n ệ ẩ iả chú gi iả chú gi

V trí đ t ặ ị V trí đ t ặ ị iả chú gi iả chú gi

10/30/14

63

Bài giảng Excel

c 3: Các l a ch n - Tab Data Labels

ự

ọ

ướ

B Nhãn d li u

ữ ệ

Nhãn d li u

ữ ệ

ệ ầ

Không hi nệ Không hi nệ //tt Hi n gệHi n gệ Hi n ph n trăm ầ ệ Hi n ph n trăm ệ ầ ệHi n nhãn ệ Hi n nhãn ệHi n nhãn và Hi n nhãn và ầph n trăm ph n trăm

10/30/14

64

Bài giảng Excel

B

ướ

c 4: Đ nh n i đ t đ th ơ ặ ồ ị

ị

Đ th hi n trên 1 sheet m i ớ Đ th hi n trên 1 sheet m i ớ

ồ ị ệ ồ ị ệ

ồ ồ

Đ th hi n trên 1sheet đã t n ồ ị ệ Đ th hi n trên 1sheet đã t n ồ ị ệ iạ iạt t

10/30/14

65

Bài giảng Excel

ượ ạ

ng th c Drag &

c t o, có th : ể i v trí m i b ng ph

ứ

ươ

ớ ằ

ồ ị ớ ị

2.

ủ

ồ ị ằ ấ

ng, đ t chu t vào ch m đen, gi

ộ

c đ th b ng cách kích chu t vào 8 i ớ

ở ấ trái chu t và di t ộ

ả

3.

ả

ộ

nháy chu t ph i vào vùng tr ng ả

ằ

ộ

ế

ọ trên.

4.

ủ

ồ ị các tr c, màu s c n n,…) b ng cách

ộ ụ

ầ ằ

ề

và ch n Format …

Khi đ th đã đ ồ ị 1. Chuy n đ th t ể Drop. Thay đ i kích th ướ ộ ổ vùng tr ng c a đ th đ xu t hi n 8 ch m đen ệ ồ ị ể ố h ữ ặ ấ ướ kích th c mong mu n r i nh chu t. ộ ố ồ ướ i, Thay đ i các thu c tính c a đ th (tiêu đ , chú gi ề ồ ị ủ ổ ố c a đ …) b ng cách ồ ủ th và ch n Chart Options… Thao tác ti p theo nh ư ị b c 3 ướ ở Thay đ i các thu c tính c a các thành ph n đ th (font ổ nháy ch , t l ắ ữ ỷ ệ chu t ph i vào thành ph n đó ả ộ

ầ

ọ

10/30/14

66

Bài giảng Excel

ổ

* Thay đ i t l

Thay đ i thu c tính tr c đ th ụ ồ ị ộ ổ ỷ ệ

trên tr c ụ

Giá tr nh nh t ấ Giá tr nh nh t ấ

ỏ ỏ

ị ị

ị ớ ị ớ

Giá tr l n Giá tr l n nh tấnh tấ

ả

ảKho ng cách Kho ng cách các đi m chia ể các đi m chia ể

Chu t ph i ả ộ trên tr c, ụ ch n Format ọ Axis

10/30/14

67

Bài giảng Excel

ổ

ộ

Thay đ i thu c tính tr c đ th ụ ồ ị * Thay đ i v trí hi n th d li u ị ữ ệ

ổ ị

ể

Khối lượng của lợn qua các ngày tuổi

20

16

12

l

8 ư ối

h K

) g k ( g ợn

4

0

S sinh

10

20

40

50

60

30

ơ

Ngày

Móng Cái

Yorkshire

ư ư

ư ư

Đ i v i đ th d ng Line, nhi u khi đ th v xong nh trên ề Đ i v i đ th d ng Line, nhi u khi đ th v xong nh trên ề nh ng v n ch a chính xác vì các m c th i gian không n m ằ nh ng v n ch a chính xác vì các m c th i gian không n m ằ đúng đi m chia trên tr c X, do l a ch n m c đ nh c a Excel. ự đúng đi m chia trên tr c X, do l a ch n m c đ nh c a Excel. ự

ố ớ ồ ị ạ ố ớ ồ ị ạ ẫ ư ẫ ư ể ể

ồ ị ẽ ồ ị ẽ ờ ờ ặ ị ặ ị

ố ố ọ ọ

ủ ủ

ụ ụ

10/30/14

68

Bài giảng Excel

ổ

ộ

Thay đ i thu c tính tr c đ th ụ ồ ị * Thay đ i v trí hi n th d li u ị ữ ệ

ổ ị

ể

Khối lượng của lợn qua các ngày tuổi

20

16

12

l

8 ư ối

h K

) g k ( g ợn

4

10

20

40

50

60

30

0 S sinh ơ

Ngày

Móng Cái

Yorkshire

ổ ổ

ể ử ể ử

ọ ọ

ỏ ự ỏ ự ơ ơ

ủ ủ ớ ớ

ượ ượ

ầ ầ

ị ị ầ ầ i này mà không bi i này mà không bi

Đ s a đ i ch c n b l a ch n m c đ nh c a Excel nh ư ặ ỉ ầ Đ s a đ i ch c n b l a ch n m c đ nh c a Excel nh ặ ỉ ầ ư c. Tuy đ n gi n nh ng c n nh vì h u nh hình trên là đ ư ư ả c. Tuy đ n gi n nh ng c n nh vì h u nh hình trên là đ ư ư ả t 100% SV làm báo cáo TN m c ph i l ế ả ỗ ắ t 100% SV làm báo cáo TN m c ph i l ế ả ỗ ắ s a.ửs a.ử 10/30/14

69

Bài giảng Excel

Chú ý

ng t

ớ

ố

ươ

ồ

 Đ i v i d ng đ th đ ạ ướ

ng quan (XY c khi v đ th ph i s p x p d ữ ế

ả ắ

Scatter), tr li u c a X theo th t ệ ủ

ứ ự

ị ườ ẽ ồ ị tăng d n. ầ

10/30/14

70

Bài giảng Excel