MS Excel 2007: Bài giảng Tin đại cương Chương 5 về phần mềm bảng tính điện tử

HỌC VIỆN NGÂN HÀNG KHOA HỆ THỐNG THÔNG TIN QUẢN LÝ

ươ

Ch

ng 5

Ề

Ầ

Ệ

Ử

Ả PH N M M B NG TÍNH ĐI N T MS EXCEL 2007

Bài giảng của Khoa Hệ thống thông tin Quản lý

Nội dung

Khởi động/thoát khỏi EXCEL

Các khái niệm cơ bản

Các thao tác cơ bản

Tính toán trên trang tính

Đồ thị

Quản trị dữ liệu trong EXCEL

2 Chương 5 - PM bảng tính điện tử MS EXCEL2007 7/27/16

1.1- Khởi động Excel

v C1: Desktop - Microsoft Office Excel

v C2: Start - All Programs -

Microsoft Office -

Microsoft Office Excel 2007

v C3: Tìm và thi hành file:

EXCEL.EXE

3 Chương 5 - PM bảng tính điện tử MS EXCEL2007 7/27/16

1.2- Thoát khỏi Excel

v C1: nhấn nút MS Office Button,

nhấn tiếp nút

v C2: kích chuột vào biểu tượng

đóng ứng dụng ở góc phải

trên màn hình

v C3: nhấn tổ hợp phím: Alt + F4

4 Chương 5 - PM bảng tính điện tử MS EXCEL2007 7/27/16

ệ

1.3 Gi

Customize Quick Access Toolbar là một thanh công cụ tùy chỉnh ớ i thi u màn hình EXCEL Sổ/bảng tính

Microsoft Office Button

Bộ công cụ Ribbon gồm các nhóm

chức năng chính của EXCEL

Các trang tính

5 Chương 5 - PM bảng tính điện tử MS EXCEL2007 7/27/16

ớ

ệ

1.3 Gi

i thi u màn hình EXCEL

Khu vực trang tính

Các trang tính có cấu trúc giống nhau. Mỗi trang tính đều:

Có 16 384 cột: từ A đến XFD (Ctrl + )↑ Có 1 048 576 dòng: từ 1 đến 1 048 576 (Ctrl + )→

Khu vực Zoom

Trang tính hiện thời trang tính

6 Chương 5 - PM bảng tính điện tử MS EXCEL2007 7/27/16

ớ

ệ

1.3 Gi

i thi u màn hình EXCEL

Thanh công thức: hiện

Chèn hàm

Hủy bỏ nội dung vừa nhập

Lưu nội dung vừa nhập

nội dung ô hiện thời

7 Chương 5 - PM bảng tính điện tử MS EXCEL2007 7/27/16

ơ ả

ệ

2 Các khái ni m c b n

ổ

2.1/ S tính (Workbook/Book), trang tính (WorkSheet/Sheet):

Sổ/bảng tính có tên ngầm

định là Book1, Book2 …

Trang tính có tên ngầm định

là Sheet1, Sheet2 …

8 Chương 5 - PM bảng tính điện tử MS EXCEL2007 7/27/16

ơ ả

ệ

2 Các khái ni m c b n

ị

ỉ

ế

2.2/ Đ a ch ô tính:

ộ thay th cho n i dung ô tính.

ỉ ươ

ị

ng đ i:

ố c tộ dòng. VD: A2, F25

 Đ a ch t

ạ ị

ỉ

ổ khi tham chi u t

i vùng

Lo i đ a ch này s

ươ

đích theo ph

ng, chi u

ị ẽ b thay đ i ả ề và kho ng cách t

ế ớ ươ ứ ng ng.

9 Chương 5 - PM bảng tính điện tử MS EXCEL2007 7/27/16

ơ ả

ệ

2 Các khái ni m c b n

ị

ỉ

ệ ố : $c tộ $dòng. VD: $L$1 ữ ỉ

ư

 Đ a ch tuy t đ i ạ ị Lo i đ a ch này

được gi

ế

nguyên ươ

ồ nh vùng ngu n khi ề và kho ng ả

ng, chi u

tham chi u đ n vùng đích theo cách b t kấ ỳ.

10 Chương 5 - PM bảng tính điện tử MS EXCEL2007 7/27/16

ơ ả

ệ

2 Các khái ni m c b n

ị

ỉ ỗ ợ $c tộ dòng/c tộ $dòng. VD: $A2, B$1

ỉ

ế

 Đ a ch h n h p: ạ ị Lo i đ a ch này s

ẽ c đ nh c t

ướ ươ

i s

ộ hay dòng n u tr c đó ế ng, theo ph ượ ạ ẽ thay đ i ổ ươ ứ ; ng ng ng ấ

ả ế

ướ

có d u $ấ chi uề vµ kho ng cách t ộ c t hay dòng n u tr

ố ị ế khi ta tham chi u đ n vùng đích c l c đó không có d u $.

= B4/SUM($B4:$D4)

= S UM(B4:B7)

= S UM(C4:C7)

= C4/S UM($B4:$D4)

= B5/S UM($B5:$D5)

11 Chương 5 - PM bảng tính điện tử MS EXCEL2007 7/27/16

ơ ả

ệ

2 Các khái ni m c b n

ị

ỉ ị

ề ỉ

ố

ị

ỉ

VD: B2:D3

ề ỉ

ầ

ị

ỉ

ề ị ờ

A1, B3, C2

ề

2.3/ Đ a ch mi n ô:  D ng 1ạ ầ : đ a ch ô đ u : đ a ch ô cu i ồ ề ế n u mi n ô đó g m các ô li n k nhau.  D ng 2ạ ố : đ a ch ô đ u, .. , đ a ch ô cu i ề ồ ế n u mi n ô đó g m các ô r i nhau. VD: ề ế Chú ý: n u mi n ô là dãy các ô li n k

ề ả

ủ

ộ

ề

ị

ỉ

ỉ ề

ọ

ầ

ả

ệ ố c a ô tính/mi n ô. ề ch n mi n ô tính c n đ t tên, nh n chu t ph i, ch n Name ậ

ẽ ấ

t danh trong

ồ

thì còn g i làọ m ng. VD: A2:C2 2.4/ Tên mi n:ề là m t tên ch đ nh thay cho đ a ch tuy t đ i  Cách đ t tên mi n: ộ ấ ặ ặ ệ ạ a Range, s xu t hi n h p tho i New Name: nh p vào tên bi khung: Names, r i nh n OK.

ườ

ố ề ử ế ệ

t ki m

ọ ệ ộ ấ i có s ti n g i ti ệ ồ

ộ ầ ử ớ

ỳ ạ ỏ

ấ

ề ố

VD: m t ng ban đ u là 5 tri u đ ng theo k h n q i v i lãi su t 1,4% tháng. H i sau ố 1 qúi,1 năm, 2 năm, 5 năm thì t ng sổ ề ti n v n và ti n lãi là bao nhiêu ?

12 Chương 5 - PM bảng tính điện tử MS EXCEL2007 7/27/16

ơ ả

ệ

2 Các khái ni m c b n

ị

ỉ ị

ề ỉ

ố

ỉ

ị

VD: B2:D3

ề ỉ

ầ

ị

ỉ

ề ị ờ

A1, B3, C2

ề

2.3/ Đ a ch mi n ô:  D ng 1ạ ầ : đ a ch ô đ u : đ a ch ô cu i ồ ề ế n u mi n ô đó g m các ô li n k nhau.  D ng 2ạ ố : đ a ch ô đ u, .. , đ a ch ô cu i ề ồ ế n u mi n ô đó g m các ô r i nhau. VD: ề ế Chú ý: n u mi n ô là dãy các ô li n k

ề ả

ủ

ộ

ề

ị

ỉ

ỉ ề

ọ

ầ

ả

ệ ố c a ô tính/mi n ô. ề ch n mi n ô tính c n đ t tên, nh n chu t ph i, ch n Name ậ

ẽ ấ

t danh trong

ồ

thì còn g i làọ m ng. VD: A2:C2 2.4/ Tên mi n:ề là m t tên ch đ nh thay cho đ a ch tuy t đ i  Cách đ t tên mi n: ộ ấ ặ ặ ệ ạ a Range, s xu t hi n h p tho i New Name: nh p vào tên bi khung: Names, r i nh n OK.

ườ

ố ề ử ế ệ

t ki m

ọ ệ ộ ấ i có s ti n g i ti ệ ồ

ộ ầ ử ớ

ỳ ạ ỏ

ấ

ề ố

VD: m t ng ban đ u là 5 tri u đ ng theo k h n q i v i lãi su t 1,4% tháng. H i sau ố 1 qúi,1 năm, 2 năm, 5 năm thì t ng sổ ề ti n v n và ti n lãi là bao nhiêu ?

13 Chương 5 - PM bảng tính điện tử MS EXCEL2007 7/27/16

ệ ỗ

ể ữ ệ

ể ứ

2.5/Các ki u d li u:

ạ

ơ ả 2 Các khái ni m c b n ỉ ể ữ ệ trong m i ô tính ch có th ch a 1 ki u d li u. Có 5 ể ữ ệ

ở

ể ố

ầ

lo i ki u d li u dùng trong EXCEL:  Ki u sể ố: là d li u nh p vào b i ký t ữ ệ ị ố  Ki u ngày tháng ạ ằ

ể ể ố ặ

ằ ể

ầ

ặ

ị

ặ ấ (cid:0) và ti p ế ố ự ầ ậ đ u tiên là s ho c d u ủ ề ả c a ô tính. ữ ệ theo là s . Ng m đ nh thì ph i d li u ki u s luôn căn bám l ớ ữ ệ ể ố ậ ủ ữ ệ ộ ộ : là m t b ph n c a d li u ki u s , khác v i d li u ể ấ ấ ị ki u s là trong d ng hi n th có d u phân cách ngày, tháng, năm (b ng d u ữ ệ ữ ế / ho c – ho c b ng ch ti ng Anh). Ng m đ nh thì d li u ki u ngày tháng luôn căn bám l

ph i

ặ

 Ki u chu i ể

ỗ

ữ ố ể

ề ả c a ô tính. ủ ằ ắ ầ ị ầ

ắ ầ

ữ ệ

ượ

ể

ấ

ằ

c b t đ u b ng d u =, sau đó là 1 bi u

ứ : là d li u đ

ứ

ộ

ặ

ấ ữ ỗ : là b t đ u b ng ch cái/ch s và ph i đ t trong c p d u ả ặ ề ỗ ữ ệ nháy kép (‘’chu i”). Ng m đ nh thì d li u ki u chu i luôn căn bám l ủ trái c a ô tính.  Ki u công th c ể ặ th c ho c hàm.  Ki u m ng ể

ả : là dãy ô tính thu c cùng 1 dòng ho c cùng 1 c t ả

ể

ả

ấ

ữ ệ ậ

ứ ẳ

ể

ị

14 Chương 5 - PM bảng tính điện tử MS EXCEL2007

ộ . Ví d : ụ A4:A12. Các công th c hay hàm dùng d li u ki u m ng thì ph i nh n: Ctrl+Shift+Enter đ kh ng đ nh sau khi nh p xong. 7/27/16

ơ ả

3 Các thao tác c b n

15 Chương 5 - PM bảng tính điện tử MS EXCEL2007 7/27/16

ấ

: nh n Microsoft Office Button ,

v M m i m t s /b ng tính ế

ở ớ ộ ổ ả ấ

ế

ặ

ọ nh n ti p nút , ch n ti p ; ho c nh n

ấ Ctrl+N.

16 Chương 5 - PM bảng tính điện tử MS EXCEL2007 7/27/16

ấ

ở ộ ổ

: nh n Microsoft Office Button , nh n

v M m t s tính đã có

ti p ế

ế ườ

ọ

ẫ ệ ầ

ấ

ặ

nút , ch n ti p đ

ở ng d n t p c n m Open; ho c nh n

Ctrl+O

17 Chương 5 - PM bảng tính điện tử MS EXCEL2007 7/27/16

ữ ầ  L u gi ư ầ l n đ u:

18 Chương 5 - PM bảng tính điện tử MS EXCEL2007 7/27/16

ệ ớ

ớ

ổ

ặ

ữ ộ

n i dung m i (có b sung ho c thay đ i) vào t p v i

ờ

ư v L u gi ệ tên hi n th i:

Nh n nút ấ Office Options, ọ ch n Save.

ấ

ọ

19 Chương 5 - PM bảng tính điện tử MS EXCEL2007

ộ ệ

ớ

nh n nút Office Options, ch n

v L u n i dung vào m t t p m i khác:

Save as.

7/27/16 ư ộ

ấ ạ

ố ệ

ộ

ộ nh n t

i ô ch a

ố ệ ộ thì toàn b dòng/c t đó

v Ch n dòng/c t:

ứ s hi u dòng/s hi u c t

ọ ẽ ượ s đ

ọ c ch n.

ấ Nh n vào đây



ấ

ề

Nh nấ vào đây

ề

nh n rê trên mi n ô tính t

ố ằ ở ề

ừ

ầ ừ ô đ u ả ế góc ph i góc trái trên đ n ô cu i n m ấ ặ ế i, n u các ô đó là li n k nhau; ho c nh n CTRL r i nh n trên t ng ô ch n , phím

ọ

ấ

ề ấ ề ề ằ nh n vào ô

ọ v Ch n mi n ô: ằ ở n m ướ d ọ ồ ữ gi ế n u các ô đó là không n m li n k nhau. ằ n m góc v Ch n toàn trang tính: ủ ộ ứ trái trên trang tính t c ô là giao c a c t và ộ ứ ố ệ ộ dòng ch a s hi u c t/dòng thì toàn b ọ ẽ ượ c ch n. trang tính s đ

Nh nấ vào đây

20 Chương 5 - PM bảng tính điện tử MS EXCEL2007 7/27/16

•

, (cid:0) , (cid:0)

, (cid:0)

(cid:0)

Các phím dịch chuyển con trỏ trong ô dịch chuyển 1 ô theo hướng mũi tên

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

• Page Up dịch con trỏ lên 1 trang màn hình. • Page Down dịch chuyển xuống 1 trang màn hình. • Home về cột đầu tiên (cột A) của dòng hiện tại • Ctrl + (cid:0) • Ctrl + (cid:0) • Ctrl + (cid:0) • Ctrl + (cid:0) • Ctrl + (cid:0) + (cid:0) • Ctrl + (cid:0) + (cid:0) • Ctrl + (cid:0) + (cid:0) • Ctrl + (cid:0) + (cid:0) 7/27/16

về ô trái trên cùng (ô A1). về ô phải trên cùng (ô IV1). về ô trái dưới cùng (ô A65536). về ô phải dưới cùng (ô IV65536). Chương 5 - PM bảng tính điện tử MS

EXCEL2007

ấ

ộ ạ

ậ

ậ ồ

• Nh p d li u: ầ

ộ

ế

ộ

ữ ệ ượ

ộ ộ t quá đ r ng ô thì ấ

ế

ự ộ

ẻ ẵ

ố

ậ ữ ệ nh n chu t t ô tính c n nh p r i nh p vào ữ ệ n i dung d li u. N u đ dài d li u v ự ộ đ ng “l n” sang ô bên nó t ố ữ ệ ạ ạ c nh, còn n u b n mu n d li u ể đ ng “b g y” xu ng dòng đ t

ợ

ấ

ộ

ả

ớ ộ ộ phù h p v i đ r ng ô thì nh n ọ Format Cells

ồ

ọ

ph i chu t, ch n ọ  Wrap text. Alignment r i ch n ch aứ ấ

ử ữ ệ nh n ch n ô

• S a d li u:

22 7/27/16

ữ ệ ầ ử ồ

ạ

ấ

EXCEL2007

Chương 5 - PM bảng tính điện tử MS d li u c n s a r i nh n F2, khi đó b n

ự ế ữ ệ

ử

s a tr c ti p d li u trên ô tính.

MỘT SỐ THÔNG BÁO LỖI THƯỜNG GẶP TRONG EXCEL

Những trường hợp gây lỗi

Ký hiệu #####

Ý nghĩa Lỗi độ rộng

#VALUE! Lỗi giá trị

#DIV/0!

Lỗi chia cho 0

#NAME! Sai tên

#N/A

Lỗi dữ liệu

#REF!

Sai vùng tham chiếu

#NUM!

Lỗi dữ liệu kiểu số

- Khi cột thiếu độ rộng. - Khi bạn nhập giá trị ngày tháng hoặc thời gian là số âm. - Nhập vào 1 chuỗi trong khi hàm yêu cầu nhập 1 số/ 1 giá trị logic. - Đang nhập 1 số hoặc các đối số mà nhấn Enter. - Nhập quá nhiều tham số cho một toán tử hoặc một hàm trong khi chúng chỉ dùng một tham số. - Nhập vào công thức số chia là 0. - Số chia trong công thức là một tham chiếu đến ô trống. - Nhập sai tên một hàm số. - Dùng những ký tự không được phép trong công thức. - Nhập một chuỗi trong công thức mà không đặt trong dấu “ ”. - Trong công thức không có dấu 2 chấm : trong dãy địa chỉ ô. - Không đồng nhất dữ liệu - Giá trị trả về không tương thích từ các hàm dò tìm - Xóa những ô đang được tham chiếu bởi công thức. - Dán những giá trị tạo ra từ công thức lên chính vùng tham chiếu đó. - Tham chiếu đến 1 ứng dụng không thể chạy được. - Dùng một đối số không phù hợp trong công thức. - Dùng hàm lặp đi lặp lại dẫn đến hàm không tìm được kết quả trả về. - Dùng hàm trả về 1số quá lớn/nhỏ so với khả năng tính toán của Excel

Lỗi dữ liệu rỗng Dùng một dãy toán tử không phù hợp

#NULL!

Chương 5 - PM bảng tính 7/27/16 23 điện tử MS EXCEL2007

NHẬP NHANH DỮ LIỆU TRONG 1 SỐ TRƯỜNG HỢP ĐẶC BIỆT

ộ

ậ ồ

ộ

ấ

ậ

ữ ệ nh n ấ ữ ệ ồ

ậ

ạ

ậ ậ ọ ư

ả ướ

ể

ệ

ỏ

ủ

ồ

ố

ừ ộ ế ấ ộ

ầ

ấ ỳ

ứ

ậ ậ ọ

ế

ấ

i d u

ộ ạ ấ FillHand đ n ô

ể ừ ủ

ờ 1/ Nh p đ ng th i các ô cùng 1 n i dung d li u: ầ ọ ch n các ô tính c n nh p, nh p vào n i dung d li u r i nh n ờ Ctrl + Enter. ồ đ ng th i ố ộ 2/ Nh p nhanh m t dãy s theo qui lu t: ế ủ ố Nh p 2 s liên ti p c a dãy vào 2 ô tính c nh nhau ậ Ch n 2 ô tính v a nh p ấ  ấ i ô đ xu t hi n d u Đ a tr chu t đ n góc ph i d ố ộ ế (FillHand ) r i nh n rê chu t đ n ô cu i cùng c a dãy s . 3/ Nh p nhanh m t dãy ngày tháng tăng d n: Nh p bi u th c ngày vào ô b t k trong dãy. ậ Ch n ô v a nh p và nh n rê chu t t ố cu i cùng c a dãy. 7/27/16

Chương 5 - PM bảng tính điện tử MS 24

EXCEL2007

NHẬP NHANH DỮ LIỆU TRONG 1 SỐ TRƯỜNG HỢP ĐẶC BIỆT

ạ

ữ ệ

ự ộ

ậ

ề đ ng đi n khi c n: ề

ầ ề ồ

ị ủ

ấ

ọ

ấ

ậ 4/ T o 1 dãy d li u có qui lu t và t ữ ệ Nh p các giá tr c a dãy d li u vào các ô li n k , r i nh n ấ Ctrl +C. ch n dãy các ô đó. Xong, nh n ọ ọ Nh n , ch n , ch n Edit Custom Lists

7/27/16 Chương 5 - PM bảng tính điện tử MS 25

EXCEL2007

NHẬP NHANH DỮ LIỆU TRONG 1 SỐ TRƯỜNG HỢP ĐẶC BIỆT

ự ộ

ầ

ạ

ậ

ữ ệ

ề đ ng đi n khi c n:

ấ

ẽ ệ

ấ

ề

ố

ị

ậ

ấ

ồ

ố

4/ T o 1 dãy d li u có qui lu t và t Nh n s hi n dãy trong khung List entries, xong nh n, , OK. ạ Khi mu n đi n dãy đã t o ỉ ệ vào v trí nào thì ch vi c ủ ị ầ nh p giá tr đ u tiên c a dãy, ạ Fillhand đ n ôế r i nh n rê t i ủ cu i cùng c a dãy.

7/27/16 Chương 5 - PM bảng tính điện tử MS 26

EXCEL2007

ị

ạ

ề

ứ ữ ệ ầ

ạ

ị

c 1ướ : nh n ch n ô/mi n ô ch a d li u c n đ nh d ng c 2ướ : nh n ch n

ọ ọ Home – Format – Format Cells:

ọ

ị

ể ữ ệ ươ ứ

ừ

ạ

1/ Đ nh d ng ô tính/mi n ô tính:  B ấ  B ấ Ch n th

ng ng.

ẻ Number để đ nh d ng cho t ng ki u d li u t

ạ

ầ D ng ban đ u

27 Chương 5 - PM bảng tính điện tử MS EXCEL2007 7/27/16

ị

ạ

ề

ứ ữ ệ ầ

ạ

ị

c 1ướ : nh n ch n ô/mi n ô ch a d li u c n đ nh d ng c 2ướ : nh n ch n

ọ ọ Home – Format – Format Cells:

ọ

ể ữ ệ ươ ứ

ừ

ạ

ị

1/ Đ nh d ng ô tính/mi n ô tính:  B ấ  B ấ Ch n th

ng ng.

ẻ Number để đ nh d ng cho t ng ki u d li u t

ị

ạ

ể

ạ

ị

ể

ạ

ị

ờ ữ ệ

ạ

ạ D ng ban ể ữ ệ Đ nh d ng d li u ki u đ uầ ị ề ữ ệ ạ sốĐ nh d ng d li u ki u ti n tệ ữ ệ Đ nh d ng d li u ki u ngày/tháng/năm ữ ệ /phút/giây Đ nh d ng d li u ki u gi ể ị Đ nh d ng d li u ki u %

ể

ạ

ị

ỗ ữ ệ Đ nh d ng d li u ki u chu i

28 Chương 5 - PM bảng tính điện tử MS EXCEL2007 7/27/16

ạ

ề

ứ ữ ệ ầ

ề

ạ

ị

c 1ướ : nh n ch n ô/mi n ô ch a d li u c n đ nh d ng c 2ướ : nh n ch n

ọ ọ Home – Format – Format Cells: ề

ữ ệ

ọ

1/ Đ nh d ng ô tính/mi n ô tính: ị  B ấ  B ấ Ch n th

cho d li u đã ch n.

ẻ Aligment để căn bám l

29 Chương 5 - PM bảng tính điện tử MS EXCEL2007 7/27/16

ạ

ề

ứ ữ ệ ầ

ề

ạ

ị

c 1ướ : nh n ch n ô/mi n ô ch a d li u c n đ nh d ng c 2ướ : nh n ch n

ọ ọ Home – Format – Format Cells: ể ổ

ữ ệ

ữ

ọ

ữ ỡ ữ ể ẻ Font đ đ i phông ch /c ch /ki u ch cho d li u đã

1/ Đ nh d ng ô tính/mi n ô tính: ị  B ấ  B ấ Ch n th ch nọ

Ch n ọ phông chữ

ọ ỡ Ch n c chữ

ệ

Ch n ọ màu chữ

Ch n hi u ứ

ữ

ọ ng ch

30 Chương 5 - PM bảng tính điện tử MS EXCEL2007 7/27/16

ị ạ

ứ ữ ệ ầ ạ ị

ề ọ ọ 1/ Đ nh d ng ô tính/mi n ô tính:  B ấ c 1ướ : nh n ch n ô/mi n ô ch a d li u c n đ nh d ng ề  B ấ c 2ướ : nh n ch n Home – Format – Format Cells:

Không k ẻ khung

ể ạ ề ề ọ - Ch n th ẻ Border đ t o khung vi n xung quanh cho ô tính/mi n ô tính

ẻ

Ch n ọ ki u ể ườ đ

ng k

Khung bên trong

ọ ườ

Ch n màu ẻ ng k đ

Khung bao ngoài

Ch n ọ ể ừ t ng ki u ẻ ườ ng k đ

7/27/16 Chương 5 - PM bảng tính điện tử MS 31

EXCEL2007

ị

ạ

ề

ứ ữ ệ ầ

ề

ạ

ị

c 1ướ : nh n ch n ô/mi n ô ch a d li u c n đ nh d ng c 2ướ : nh n ch n

ề

ọ

1/ Đ nh d ng ô tính/mi n ô tính:  B ấ  B ấ Ch n th

ọ ọ Home – Format – Format Cells: ể ẻ Fill đ tô màu n n cho ô tính/mi n ô tính đã ch n.

ở ạ

Ch n ọ ki u ể n nề

ạ ỏ Lo i b màu ề i n n (tr l ề n n màu tr ng)ắ

ọ Ch n màu n nề

32 Chương 5 - PM bảng tính điện tử MS EXCEL2007 7/27/16

ị

ạ

ề

ứ ữ ệ ầ

ề

ị

ạ ể

ỉ

ộ

c 1ướ : nh n ch n ô/mi n ô ch a d li u c n đ nh d ng c 2ướ : nh n ch n

ọ ọ Home – Format – Row Height đ cân ch nh đ

1/ Đ nh d ng ô tính/mi n ô tính:  B ấ  B ấ cao dòng.



ể ấ ệ  ớ gi a 2 dòng đ xu t hi n ạ ộ ế v ch ranh gi

ộ ể ớ ỏ ẹ ấ ể ư Chú ý: có th đ a chu t đ n i ồ r i nh n rê chu t đ n i l ng/thu h p kích th ữ ướ c.

33 7/27/16 Chương 5 - PM bảng tính điện tử MS

EXCEL2007

ạ

ứ ữ ệ ầ

ề

ạ

ị

ỉ

c 1ướ : nh n ch n ô/mi n ô ch a d li u c n đ nh d ng ể c 2ướ : nh n ch n

ề ọ ọ Home – Format – Column Width đ cân ch nh

ộ

1/ Đ nh d ng ô tính/mi n ô tính: ị  B ấ  B ấ ộ ộ đ r ng c t.



ữ ấ ồ ộ ể ớ gi a 2 c t đ xu t hi n ệ  r i nh n rê chu t ộ ấ i

ướ ẹ ể ư Chú ý: có th đ a chu t đ n ể ớ ộ đ n i r ng/thu h p kích th ạ ộ ế v ch ranh gi c.

34 7/27/16 Chương 5 - PM bảng tính điện tử MS

EXCEL2007

ữ ệ

ấ

ọ

ế Home – Delete, ch n ti p

v Xóa ô tính: bôi đen d li u, nh n vào Delete Cells

i s đ y lên ị ế ả ẽ ồ Các ô bên ph i s d n ị ị ế sang trái, chi m v trí ô b xóa. Các ô phía d ướ ẽ ẩ ị trên, chi m v trí ô b xóa.

ọ ứ Xóa dòng ch a ô đang ch n

ứ ộ ọ Xóa c t ch a ô đang ch n

35 Chương 5 - PM bảng tính điện tử MS EXCEL2007 7/27/16

ầ

ộ

ấ

ả , nh n chu t ph i

 Xóa trang tính: đ a chu t đ n tên trang tính c n xóa, ộ ế ch n ọ Delete

36 Chương 5 - PM bảng tính điện tử MS EXCEL2007 7/27/16

ọ

ặ

ề

ứ ữ ệ ầ

ể

ộ

ả

ể

ấ ấ ấ

ả ể

ồ

ấ nh n gi ờ

ộ ớ

ệ

ấ ữ Ctrl/Shift r i nh n phím ộ ả ớ ị i tên trang tính hi n th i sang v trí m i, xong th chu t.

 Sao chép/di chuy n d li u ể ữ ệ : Ch n ô tính ho c mi n ô ch a d li u c n sao chép/di chuy n  Nh n chu t ph i, ch n ọ Copy/Cut  Nh n vào ô tính s sao chép/di chuy n sang ẽ  Nh n chu t ph i, ch n ọ Paste. ộ  Sao chép/di chuy n trang tính: rê chu t t

Trang tính ban đ uầ

Trang tính sao chép

ữ ấ Nh n gi Ctrl

ữ

ấ Nh n gi Shift 37 7/27/16 Chương 5 - PM bảng tính điện tử MS

EXCEL2007

ọ

1/ Ch n vùng in: ọ

ề ứ ữ ệ ầ ấ ồ Nh n ch n mi n ô tính ch a d li u c n in r i vào Page Layout Print Area Set Print Area.

38 Chương 5 - PM bảng tính điện tử MS EXCEL2007 7/27/16

ế ặ

ấ

nh n ch n

v Thi

t đ t trang in:

ọ Page Layout – Page Setup , ch nọ

Page

In d cọ ổ ấ kh gi y

In ngang ổ ấ kh gi y

Ch n ọ kh ổ gi y inấ

39 Chương 5 - PM bảng tính điện tử MS EXCEL2007 7/27/16

ế ặ

v Thi

t đ t trang in:

nh n ấ Page Layout Page Setup , ch nọ Margin

mép

Kho ng ả ừ cách t ấ ế gi y đ n dòng ầ đ u tiên

ề

trái

Kho ng ả cách l

ề

ả Kho ng cách ả ph i l

mép

Kho ng ả ừ cách t ấ ế gi y đ n dòng ố cu i cùng

40 Chương 5 - PM bảng tính điện tử MS EXCEL2007 7/27/16

ế ặ

v Thi

t đ t trang in:

nh n ấ Page Layout Page Setup Header/Fooer

ấ

ể

ề ầ

ộ

ề

Đi n n i dung cho tiêu đ đ u trang

ề ầ

Nh n vào đây đ ạ t o tiêu đ đ u trang

41 Chương 5 - PM bảng tính điện tử MS EXCEL2007 7/27/16

ế ặ

v Thi

t đ t trang in:

nh nấ Page Layout Page Setup Sheet

ề

ầ

Mi n ô c n in

ọ

ẻ ướ

Ch n/không ch n in dòng k l

ọ i

ọ

ọ ố ệ ỗ

Ch n/không ch n ộ in s hi u dòng/c t trên m i trang tính

42 Chương 5 - PM bảng tính điện tử MS EXCEL2007 7/27/16

v In trang tính: nh n ấ Office Buton – Print Print

ấ ả ổ In t t c s tính

In 1 sè trang

ừ T trang …

ế Đ n trang …

ướ Nhìn tr c khi in

43 Chương 5 - PM bảng tính điện tử MS EXCEL2007 7/27/16

Ữ Ệ

4 TÍNH TOÁN D LI U TRÊN TRANG TÍNH

ể ữ ệ

ố ớ ừ I Các phép toán đ i v i t ng ki u d li u ố ớ ữ ệ ể ố 1/ Đ i v i d li u ki u s :  Phép toán s h c: + * / ^ ố ọ  Phép toán logic: AND OR NOT  Phép toán so sánh: = <> < <= > >=

ứ ự

ứ ư ấ

ặ ơ

ừ

M c u tiên 3 phép toán trên theo th t 1 (cao nh t)ấ 2

ừ

ộ

D u ngo c đ n ( ) Lũy th a (^) Nhân (*) và chia (/ ) 3 C ng (+) và tr () 4 Quan hệ 5 Logic 6 ế

ể ứ ư

ệ ừ

trái

N u các phép toán ứ ộ trong m t bi u th c có cùng m c u tiên ẽ ự thì s th c hi n t sang ph i.ả

44 Chương 5 - PM bảng tính điện tử MS EXCEL2007 7/27/16

Ữ Ệ

4 TÍNH TOÁN D LI U TRÊN TRANG TÍNH

ố ớ ừ

ể ữ ệ

ố ớ ữ ệ

ể

ỗ có 2 phép toán là

I Các phép toán đ i v i t ng ki u d li u 2/ Đ i v i d li u ki u chu i:  Phép toán ghép chu i : &ỗ

VD:

Chú ý:

 Phép toán so sánh: = , <>, > , <

45 Chương 5 - PM bảng tính điện tử MS EXCEL2007 7/27/16

Ữ Ệ

4 TÍNH TOÁN D LI U TRÊN TRANG TÍNH

ể ữ ệ

ố ớ ữ ệ

ố ớ ừ ể

có các phép toán +, và quan h .ệ

ế

ẽ

ả

ộ

I Các phép toán đ i v i t ng ki u d li u 3/ Đ i v i d li u ki u ngày: ớ  Phép toán c ng 1 btD v i 1 btN s cho k t qu là 1 btD

VD:

ữ

 Phép toán – gi a 2 btD VD:

ệ ự

ắ

ớ

 Phép toán quan h d a trên nguyên t c: ngày hôm sau l n h n ngày

hôm qua

VD:

46 Chương 5 - PM bảng tính điện tử MS EXCEL2007 7/27/16

Ữ Ệ

4 TÍNH TOÁN D LI U TRÊN TRANG TÍNH

ỉ ồ

ố

ạ

ụ

ứ ồ

ề

ỉ

ụ

ặ

ứ II Công th c trong Excel: ứ 1/ Công th c ch g m các con s và các toán h ng: Ví d : = (45 + 127)^2 / 76 0.3 ị 2/ Công th c g m đ a ch mi n ô/tên mi n: Ví d : = A5 * C5 B6 ho c = Ten1 + Ten2 ị

ỉ

ừ

ứ ừ

ụ

ứ

ụ

ố ị

ề

ả

ỉ

ố 3/ Công th c v a có đ a ch ô, v a có con s : Ví d : = (H7 * I8 + H7 * K8)^3 / 5 ỉ 4/ Công th c ch có các hàm: Ví d : = SUM(C3: C9) ( = C3+C4+C5+C6+C7+C8+C9) 5/ Công thúc t ng quát:

ồ ổ g m c các con s , đ a ch ô/mi n ô/tên mi n, các hàm và các phép toán. ụ

Ví d : = IF(LEFT(A2,1)=”N”, $H$3*5, $H$3*3) + $H$5

47 Chương 5 - PM bảng tính điện tử MS EXCEL2007 7/27/16

Ữ Ệ

4 TÍNH TOÁN D LI U TRÊN TRANG TÍNH

ụ

ổ

ề ươ

=Tên hàm(danh sách các đ i số ố) ữ ườ

ở ấ

ặ ấ

c, ặ ấ

ị

ế ng đ u đ t ch hoa/ch th ố ố cách nhau b i d u : ho c d u , ho c d u ; ợ

ề

ầ

ồ

ọ

i ô tính c n đi n hàm ố ố ọ Formula –Insert Function r i ch n hàm và đ i s .

ụ

ử ố

48 Chương 5 - PM bảng tính điện tử MS EXCEL2007 7/27/16

III Các hàm thông d ng trong Excel ạ ủ 1/ D ng t ng quát c a hàm: ữ trong đó: Tên hàm vi danh sách đ i s ớ ả (ph i phù h p v i qui đ nh trong Control Panel). VD: =MAX(A3:I4); =IF(C2=”a”, F2*90%, F2); =SUMIF(D3:D12; ”>3”) ề 2/ Cách đi n hàm:  Nh n t ấ ạ  Nh n ch n ấ ộ ố 3/ M t s nhóm hàm thông d ng:  Nhóm hàm ngày  Nhóm hàm toán h cọ  Nhóm hàm logic  Nhóm hàm x lý chu i ỗ  Nhóm hàm th ng kê  Nhóm hàm dò tìm và tham chi uế

 Nhóm hàm tài chính

Nhóm hàm ngày

• NOW(): Cho ngày và giờ hiện tại. • TODAY(): Cho ngày hiện tại. • DAY(“mm/dd/yy”)/DAY(địa chỉ ô): Cho giá trị ngày. • MONTH(“mm/dd/yy”)/MONTH(địa chỉ ô): Cho giá trị tháng. • YEAR(“mm/dd/yy”)/YEAR(địa chỉ ô): Cho giá trị năm.

vÝ dô:

7/27/16 Chương 5 - PM bảng tính điện tử MS 49

EXCEL2007

Nhóm hàm toán học

7/27/16 Chương 5 - PM bảng tính điện tử MS 50

EXCEL2007

Ví dụ hàm: SUM, AVERAGE, MAX, MIN, RANK

7/27/16 Chương 5 - PM bảng tính điện tử MS 51

EXCEL2007

Ví dụ hàm SUMPRODUCT:

7/27/16 Chương 5 - PM bảng tính điện tử MS 52

EXCEL2007

Nhóm hàm xử lý chuỗi

7/27/16 Chương 5 - PM bảng tính điện tử MS 53

EXCEL2007

Nhóm hàm logic

7/27/16 Chương 5 - PM bảng tính điện tử MS 54

EXCEL2007

Nhóm hàm thống kê

7/27/16 Chương 5 - PM bảng tính điện tử MS 55

EXCEL2007

Ví dụ hàm: SUMIF

Ví dụ hàm: COUNTIF

7/27/16 Chương 5 - PM bảng tính điện tử MS 56

EXCEL2007

Nhóm hàm dò tìm và tham chiếu

ề

ấ

ặ

ả

ị

Chú ý: giá tr dò tìm x ph i là duy nh t trong dãy ho c mi n ô dò tìm

7/27/16 Chương 5 - PM bảng tính điện tử MS 57

EXCEL2007

VD hàm VLOOKUP: dựa vào Bảng phụ cấp để điền cột Phụ cấp

7/27/16 Chương 5 - PM bảng tính điện tử MS 58

EXCEL2007

VD hàm HLOOKUP: dựa vào Qui định tiền thưởng để điền cột Thưởng

7/27/16 Chương 5 - PM bảng tính điện tử MS 59

EXCEL2007

Nhóm hàm tài chính

7/27/16 Chương 5 - PM bảng tính điện tử MS 60

EXCEL2007

Nhóm hàm tài chính (tiếp)

7/27/16 Chương 5 - PM bảng tính điện tử MS 61

EXCEL2007

5- Đồ thị (graph):

1. Năm nào có sự chênh lệch lớn nhất giữa lượng tiền dành cho Cấp phát & cho vay đầu

2. Nhận xét về lượng tiền dành cho Cấp phát & cho vay đầu tư XDCB trong 5 năm

tư XDCB với Tín dụng vốn lưu động?

(1994 – 1998).

7/27/16 Chương 5 - PM bảng tính điện tử MS 62

EXCEL2007

5- Đồ thị (graph):

1. Năm nào có sự chênh lệch lớn nhất giữa lượng tiền dành cho Cấp phát & cho vay đầu

2. Nhận xét về lượng tiền dành cho Cấp phát & cho vay đầu tư XDCB trong 5 năm

tư XDCB với Tín dụng vốn lưu động?

(1994 – 1998).

7/27/16 Chương 5 - PM bảng tính điện tử MS 63

EXCEL2007

5- Đồ thị (graph):

Trong Excel, đồ thị (graph) được sử dụng để diễn tả sự phân bố của các đại

lượng dưới dạng HÌNH ẢNH.

7/27/16 Chương 5 - PM bảng tính điện tử MS 64

EXCEL2007

5.1. Tạo mới đồ thị trong Excel:

• B1: chọn vùng dữ liệu cần vẽ đồ thị

VD: vẽ đồ thị so sánh Cấp phát & cho vay đầu tư XDCB

với Tín dụng vốn lưu động 1994-1998, chọn A5:F6

7/27/16 Chương 5 - PM bảng tính điện tử MS 65

EXCEL2007

5.1. Tạo mới đồ thị trong Excel:

• B1: chọn vùng dữ liệu cần vẽ đồ thị VD: vẽ đồ thị cho Tín dụng vốn lưu động ta chọn A6:F6

7/27/16 Chương 5 - PM bảng tính điện tử MS 66

EXCEL2007

• B2: vào Insert – nhấn Chat

7/27/16 Chương 5 - PM bảng tính điện tử MS 67

EXCEL2007

Xuất hiện hộp thoại Insert Chart để chọn kiểu đồ thị Column: dạng cột dọc

Bar: dạng thanh ngang.

Line: dạng đường

Pie: bánh tròn.

XY: Đường, trục X là số

Area: dạng vùng.

Doughtnut: băng tròn.

Radar: dạng ra đa.

Surface: dạng bề mặt.

Bubble: dạng bong bóng.

Stock: 3 dãy (cao, thấp, khớp).

…

7/27/16 Chương 5 - PM bảng tính điện tử MS 68

EXCEL2007

Ồ Ị

Ấ

Đ TH SAU KHI HOÀN T T

Tín dụng vốn lưu động

7/27/16 Chương 5 - PM bảng tính điện tử MS 69

EXCEL2007

5.2- Hiệu chỉnh đồ thị:

1/ Thay đổi dữ liệu cần minh họa trên đồ thị:  Nhấn chọn đồ thị cần hiệu chỉnh  Nhấn vào Design - chọn Data.

Đổi dữ liệu

dòng ↔ cột

Xác nhận lại

miền dữ liệu

Hoặc nhấn chọn từng

nút Add/Edit/Remove

để thêm/sửa/bỏ đi từng

7/27/16 70

Chương 5 - PM bảng tính điện tử MS dl EXCEL2007

5.2- Hiệu chỉnh đồ thị:

2/ Thay đổi kiểu dạng:  Nhấn chọn đồ thị cần hiệu chỉnh  Nhấn vào Design - chọn Changes Chart type.

7/27/16 Chương 5 - PM bảng tính điện tử MS 71

EXCEL2007

5.2- Hiệu chỉnh đồ thị:

3/ Thay đổi mẫu dạng trong kiểu đã chọn:  Nhấn chọn đồ thị cần hiệu chỉnh  Nhấn vào Design - chọn Chart Styles.

7/27/16 Chương 5 - PM bảng tính điện tử MS 72

EXCEL2007

5.2- Hiệu chỉnh đồ thị:

4/ Thay đổi cách thể hiện:  Nhấn chọn đồ thị cần hiệu chỉnh  Nhấn vào Design - chọn Chart Layouts.

7/27/16 Chương 5 - PM bảng tính điện tử MS 73

EXCEL2007

ị ơ ở ữ ệ

ả

6 Qu n tr c s d li u trong EXCEL

ệ

ề

6.1 Khái ni m v CSDL trong Excel

ộ ố

6.2 M t s thao tác trên CSDL

ữ ệ

ọ

6.2.1 Trích l c d li u.

ữ ệ

6.2.2 Phân nhóm d li u.

ế ữ ệ

6.2.3 Liên k t d li u.

ữ ệ

6.2.4 Phân tích d li u

ươ ệ ử ả Ch ng 5 PM b ng tính đi n t MS 7/27/16 74

EXCEL2007

6.1 - Khái niệm về CSDL trong Excel

1/ Đ.n CSDL: cơ sở dữ liệu trong Excel là 1 bảng chứa dữ liệu được tập hợp từ các trường (Fields) và các bản Quan sát nội dung trang tính sau: ghi (Records).

CSDL: miền ô A3:I11 Các trường: A3:I3

Các bản ghi: A4:I11

Chương 5 - PM bảng tính điện tử MS 7/27/16 75

EXCEL2007

6.1 - Khái niệm về CSDL trong Excel

(cid:0) Trường là một cột CSDL, mỗi trường biểu thị

một thuộc tính của đối tượng; được đặt tên và

chứa một kiểu dữ liệu nhất định. (cid:0) Bản ghi là một dòng dữ liệu (bắt đầu từ dòng

thứ 2 trở đi trong bảng dữ liệu), còn gọi là một

đối tượng trong CSDL.

7/27/16 Chương 5 - PM bảng tính điện tử MS 76

EXCEL2007

Ví dụ:

Các tr ngườ

TT

Họ và tên

Phòng

Giới tính

Đảng viên

Xếp loại

Ghi chú

Ngày sinh Nguyễn Văn An 01/01/89 Nam Kế toán

Có

A

Trần Thị Cúc

12/10/88 Nữ

Hành chính

Có

Các

B

Lê Kim Kiều

21/01/89 Nữ

Tổ chức

Có

b nả

A

Lý Trung Tấn

15/10/88 Nam Bảo vệ

Chưa

ghi

C

ệ ả ờ B n ghi hi n th i

7/27/16 Chương 5 - PM bảng tính 77

điện tử MS EXCEL2007

6.1 - Khái niệm về CSDL trong Excel

2/ Sắp xếp CSDL:

(cid:0) Đ.n: sắp xếp CSDL là sự sắp đặt lại giá trị của các thuộc tính

theo 1 trật tự nào đó

• Ví dụ: tạo ra danh

sách cán bộ theo vần

alphabel của trình độ,

những người cùng trình

độ thì thu nhập tăng dần.

•

Ý nghĩa: sau sắp xếp thì tạo ra các nhóm dữ liệu.

Chương 5 - PM bảng tính điện tử MS 78 7/27/16

EXCEL2007

2/ Sắp xếp CSDL:

• Cách thực hiện: chọn miền CSDL (A2:I10), vào Data - Sort

7/27/16 79 Chương 5 - PM bảng tính điện tử MS

EXCEL2007

•

Ví dụ: sắp xếp danh sách cán bộ tăng dần theo giới tính, những người cùng giới tính

Thiết lập 1

số tùy chọn

Thêm vào các mức ưu tiên

Xóa mức ưu tiên đang chọn

Sao chép mức ưu tiên đang chọn

ộ ư ấ ệ ắ

ứ C t u tiên th ề nh t trong đi u ế ki n s p x p

ộ ư

ứ

C t u tiên th 2

thì tăng dần theo trình độ.

80 7/27/16 Chương 5 - PM bảng tính điện tử MS

EXCEL2007

Nếu nhấn chọn Options thì hiện tiếp hộp thoại Sort Options:

ề ữ Mi n d ứ ệ li u có ch a dòng đ u ầ tiên

ế ừ ố ướ X p t trên xu ng d i

ế ừ X p t ả trái sang ph i

ệ ữ ớ

7/27/16 Chương 5 - PM bảng tính điện tử MS ữ t ch hoa v i ch 81 Phân bi ngườ th

EXCEL2007

6.1 - Khái niệm về CSDL trong Excel

3/ Hàm CSDL: (cid:0) Hàm CSDL dùng để xử lý thông tin trong CSDL. (cid:0) Đặc trưng chung của các hàm trong CSDL là gồm 3 đối số: vùng CSDL, tên trường/cột n, vùng tiêu chuẩn.

Là toàn bộ hoặc một phần CSDL ban đầu (nhưng luôn phải có dòng đầu chứa tên các thuộc tính).

Là đối tượng hàm tác động lên.

Là miền điều kiện, bao gồm: Tên trường và biểu thức chứa điều kiện của các bản ghi chịu tác động của hàm.

(cid:0) Ví dụ:

82 7/27/16 Chương 5 - PM bảng tính điện tử MS

EXCEL2007

Các cách mô tả vùng tiêu chuẩn:

- NÕu lµ ®iÒu kiÖn ®¬n:

ả ồ

ệ

ề

ế N u là đi u ki n tho đ ng th i:ờ

83 Chương 5 - PM bảng tính điện tử MS 7/27/16

EXCEL2007

M t s hàm CSDL ườ

ng/ c t n

ườ

ệ ượ

ề

ng/c t n

ộ , vùng tiêu chu nẩ ): tính trung c

ộ trong vùng CSDL theo đi u ki n đ

ộ ố DAVERAGE(vùng CSDL, tên tr bình các giá tr thu c di n t

ộ tên tr ị ễ ả ở vùng tiêu chu nẩ .

ườ

ấ

ng/ c t n

ị ộ , vùng tiêu chu nẩ ): l y ra giá tr

ệ ượ

ề

ng/ c t n

ộ trong vùng CSDL theo đi u ki n đ

c di n t

ễ ả ở vùng

 DGET(vùng CSDL, tên tr ườ thu c ộ tên tr tiêu chu nẩ .

ườ

ấ

ị ớ

ấ

ườ

ng/ c t n ng/ c t n

 DMAX/DMIN(vùng CSDL, tên tr ộ tên tr ỏ giá tr l n nh t/nh nh t thu c

ộ , vùng tiêu chu nẩ ): l y ra ộ trong vùng CSDL theo đi u ề

ệ ượ

ấ ễ ả ở vùng tiêu chu nẩ .

ki n đ

c di n t

ườ

ế ề

ộ tên tr

ng/ c t n

ng các giá tr thu c

ố ộ , vùng tiêu chu nẩ ): đ m s ng/ c t n ệ ộ trong vùng CSDL theo đi u ki n

 DCOUNT(vùng CSDL, tên tr ườ ị ượ l ượ

ễ ả ở vùng tiêu chu nẩ .

c di n t 7/27/16

EXCEL2007

ườ

ổ

ộ

DSUM(vùng CSDL, tên tr

Chương 5 - PM bảng tính điện tử MS ng/ c t n

ộ , vùng tiêu chu nẩ ): tính t ng c ng

ị

ườ

ệ ượ

ề

ễ

ộ tên tr

ng/ c t n

các giá tr thu c

ộ trong vùng CSDL theo đi u ki n đ

c di n

ả ở vùng tiêu chu nẩ .

Ví dụ:

7/27/16 Chương 5 - PM bảng tính điện tử MS 85

EXCEL2007

6.2 - Một số thao tác trên CSDL

6.2.1- Đặt lọc/rút trích dữ liệu (AutoFilter/Advanced Filter)

Đặt lọc dữ liệu/rút trích dữ liệu là nhằm lấy ra những bản ghi thoả mãn điều kiện nhất

định. Có 2 hình thức lọc:

1/ Lọc tự động (AutoFilter): Excel hỗ trợ điều kiện lọc

– Bước 1: chọn miền dữ liệu cần lọc (kể cả dòng tiêu đề)

– Bước 2: vào menu Data - Filter

7/27/16 Chương 5 - PM bảng tính điện tử MS 86

EXCEL2007

Ví dụ: lọc những mặt hàng là Đĩa cứng có số lượng bán ra trong khoảng [50,100)

Nhấn

chọn

Các dạng điều

mục Đĩa

kiện cho việc

cứng

chọn dữ liệu

kiểu chuỗi

7/27/16 Chương 5 - PM bảng tính điện tử MS 87

EXCEL2007

Ví dụ: lọc những mặt hàng là Đĩa cứng có số lượng bán ra trong khoảng [50,100)

Chọn và nhập cho biểu thức

điều kiện

7/27/16 Chương 5 - PM bảng tính điện tử MS 88

EXCEL2007

Ví dụ: lọc những mặt hàng là Đĩa cứng có số lượng bán ra trong khoảng [50,100)

Kết quả như sau:

7/27/16 Chương 5 - PM bảng tính điện tử MS 89

EXCEL2007

6.2 - Một số thao tác trên CSDL

2/ Lọc dữ liệu dùng Advanced Filter: được dùng khi điều kiện lọc là phức tạp. Khi đó

- Bước 1: biểu diễn vùng tiêu chuẩn mô tả điều kiện lọc (cách

thức giống như biểu diễn vùng tiêu chuẩn của hàm CSDL). (cid:0) Dòng đầu ghi tên trường liên quan đến điều kiện (chú ý phải

giống hệt tên trường của CSDL - tốt nhất là copy từ tên

trường CSDL).

(cid:0) Các dòng tiếp dướ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.

người sử dụng phải tự biểu diễn vùng tiêu chuẩn mô tả điều kiện lọc.

7/27/16 Chương 5 - PM bảng tính điện tử MS 90

EXCEL2007

ề

ả

ề

ả

ố

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

ể ọ Mi n đ/k đ l c các b n ố ghi có s SP bán ra trong ớ tháng 1 là l n h n

ơ 150

ả

ề

ả

ề

ố

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

~~ể ọ Mi n đ/k đ l c các b n ố ghi có s SP bán ra trong ơ 150 ho c ặ ớ tháng 1 là l n h n ơ ớ trong tháng 2 là l n h n, ằ b ng 200~~

7/27/16 Chương 5 - PM bảng tính điện tử MS 91

EXCEL2007

- Bước 2: thực

hiện lọc: Data-

Advanced

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

ệ

ọ Hi n KQ l c ơ ra n i khác

ả

ề Mi n CSDL đã ch nọ ả

ỉ ệ Ch hi n 1 b n ệ ạ ghi đ i di n trong ố ữ s nh ng b n ghi trùng l pặ ọ ệ ề Ch n mi n hi n KQ

Vùng đi u ề ki nệ

7/27/16 Chương 5 - PM bảng tính điện tử MS 92

EXCEL2007

6.2 - Một số thao tác trên CSDL

6.2.2 - Phân nhóm dữ liệu (SUBTOTAL)

ữ ệ ứ ừ ố Đây là ch c năng tính toán, th ng kê theo t ng nhóm d li u.

ự

ệ

ộ ườ

ủ

ng c a CSDL:

ế

ế ữ ệ

ả

ầ

ầ

ặ

ắ

1/Th c hi n Subtotal trên m t tr         B

c 1ướ : ti n hành s p x p d li u (tăng d n ho c gi m d n) ữ ệ

ườ

ầ

ng c n phân nhóm d li u.

trên tr

ự

 B         B

ọ c 2ướ : l a ch n vùng CSDL. c 3ướ : vào menu Data Subtotals

7/27/16 Chương 5 - PM bảng tính điện tử MS 93

EXCEL2007

ọ

ầ

ng c n phân nhóm d li u

ố

ọ

ườ ể

ữ ệ ừ

ữ ệ

ọ

ườ

ầ

ố

ọ

ng c n tính toán/th ng kê trên m i

ữ ệ

ọ ở ướ d

ướ

ế

ệ

ỗ

ệ

ố

ướ

i

ữ ệ

(cid:0) At Each Change in: ch n tên tr (cid:0) Use Function: ch n hàm dùng đ tính toán, th ng kê theo t ng nhóm d li u. (cid:0) Add Subtotal to: ch n tên tr nhóm d li u. i có nghĩa sau: Ba tùy ch n    Replace Current Subtotal: thay th phân nhóm tr c đó.    Page Break Between Groups: m i nhóm hi n trên 1 trang.    Summary Below Data: xu t hi n dòng tính toán, th ng kê  d ấ ừ t ng nhóm d li u. 7/27/16

Chương 5 - PM bảng tính điện tử MS 94

EXCEL2007

Ví dụ:

7/27/16 Chương 5 - PM bảng tính điện tử MS 95

EXCEL2007

(cid:0) Ví dụ: từ CSDL DANH SÁCH CÁN BỘ cần đưa ra thống kê tổng thu nhập theo từng nhóm cán bộ nam/nữ và ngày sinh của cán bộ trẻ nhất theo từng nhóm trình độ và từng nhóm nam/nữ trên.

2/ Thùc hiÖn S UBTOTAL ®ång thê i trªn nhiÒu trê ng c ña CS DL:

7/27/16 Chương 5 - PM bảng tính điện tử MS 96

EXCEL2007

ề ồ ờ ủ ườ ự ệ ng c a CSDL:

ự

ườ ầ ầ ả ế ế ữ ệ ng c n phân

ắ ữ ệ ầ ặ ạ Sort

ữ ệ ọ ườ ng s đ

cc phân chia thành các nhóm d li u chính. ọ ữ ệ ẽ ượ ọ ế c phân nhóm ti p (g i là các ng mà d li u s đ

ỗ ụ ằ

ự ệ ấ ấ ệ ố ọ Data Subtotals, xu t hi n h p tho i ư ạ Subtotal: th c hi n gi ng nh

ệ ộ ộ c phân thành các nhóm chính. i ấ ọ ạ DataSubtotals, xu t hi n h p th ai Subtotal:

ọ ữ ệ ườ ầ ọ ụ ng c n phân nhóm d li u ph .

ừ ọ ố

ườ ọ ữ ệ ầ ọ ố ụ ng c n tính toán/th ng kê trên m i nhóm d li u ph .

ắ ọ  Replace Current Subtotal.

2/ Th c hi n SUBTOTAL đ ng th i trên nhi u tr     (cid:0) Cách th c hi n ệ :     B c 1ướ : ti n hành s p x p d li u (tăng d n ho c gi m d n) trên các tr ắ ộ nhóm d li u theo nguyên t c sau: trong h p tho i ẽ ượ         M c ụ Sort by: ch n tên tr ườ         Các m c ụ Then by: ch n tên các tr ầ nhóm ph n m trong m i nhóm ban đ u).     B ọ ự c 2ướ : l a ch n vùng CSDL.     B c 3ướ : nh n ch n ữ ệ ẽ ượ ứ trên t c là d li u s đ     B ấ c 4ướ : nh n ch n l         At Each Change in: ch n tên tr ể         Use Function: ch n hàm đ tính toán/th ng kê theo t ng nhóm ụ ữ ệ          d li u ph .         Add Subtotal to: ch n tên tr         T t tùy ch n:           Xong, nh n ấ OK.

7/27/16 Chương 5 - PM bảng tính điện tử MS 97

EXCEL2007

ế

ắ

ư Trình đ ộ ( u tiên 2)

ớ

ư

VD trên: ầ

( u tiên 1),

./ B

c 1ướ : s p x p tăng d n theo

Gi

i tính

ướ ./ B c 2 và B ướ : c 3

7/27/16 Chương 5 - PM bảng tính điện tử MS 98

EXCEL2007

ệ

ấ

ộ

./ B

ạ c 4ướ : vào Data Subtotals, xu t hi n h p tho i

Subtotal:

ả ố ư ế Nh n ấ OK ta có k t qu cu i cùng nh sau:

7/27/16 Chương 5 - PM bảng tính điện tử MS 99

EXCEL2007

ể ễ

ử

ạ

ơ

ạ

i

ị ữ ệ

ướ

ể ỉ ạ Đ d nhìn h n, b n có th ch nh s a, xoá đi, đ nh d ng l ọ các dòng chú thích d

ặ i m i nhóm ho c các d li u:

7/27/16 Chương 5 - PM bảng tính điện tử MS 100

EXCEL2007

6.2 - Một số thao tác trên CSDL

6.2.3- Liên kết dữ liệu (Group and Outline)

7/27/16 Chương 5 - PM bảng tính điện tử MS 101

EXCEL2007

Có 2 tình hu ng sau:

ủ ộ

ự

ế

ặ

ườ

ố ệ i dùng ch đ ng th c hi n liên k t các dòng ho c

I Ng

ữ ệ

ừ

ừ

ộ

ấ các c t vào thành t ng nhóm d li u theo t ng c p:

ướ

ỉ ự

ệ

ố

ữ ệ

 B

c 1:

(ch th c hi n khi mu n liên k t ế ữ ệ

ườ

ẽ

ế các dòng d li u ữ ng s phân nhóm d

ắ ừ vào t ng nhóm): s p x p d li u trên tr li u. ệ

ướ

ọ

ộ ữ ệ ầ

ế

ặ

ch n các dòng ho c các c t d li u c n liên k t

 B

c 2: ộ trong m t nhóm.

ướ

 B

c 3:

ch n ọ Data – Group – Group

7/27/16 Chương 5 - PM bảng tính điện tử MS 102

EXCEL2007

Ví d :ụ

7/27/16 Chương 5 - PM bảng tính điện tử MS 103

EXCEL2007

ự

ẽ ự ộ

ệ

ệ

ấ

II Excel s t

ế đ ng th c hi n liên k t khi phát hi n th y ộ

ữ ệ

ữ

ữ

ặ

ứ

có nh ng c t ho c nh ng dòng mà d li u có liên quan ẫ : l n nhau qua công th c/hàm

ướ

ộ ế

ấ ỳ

ữ ệ

ư

ỏ

đ a tr chu t đ n ô b t k trong vùng d li u.

 B

c 1:

ướ

ch n ọ Data – Group Auto OutLine => s t

c 2: ấ

ế

ệ

ấ

ộ

 B ộ ượ

ẽ ự ể đ ng xu t hi n các liên k t dòng/c t theo các c p có th đ

c.

7/27/16 Chương 5 - PM bảng tính điện tử MS 104

EXCEL2007

ạ

ế

ấ

ế ỡ ỏ III Cách g b các liên k t đã t o: ỡ ỏ ừ        1/ G b t ng liên k t: ế ữ ạ             B n bôi đen nh ng dòng/c t đã liên k t             Nh n ch n

ế t c các liên k t: ộ ấ ỳ

ữ ệ

ộ ọ Data Ungroup ấ ả ị ế ầ

ỡ ỏ       2/ G b cùng lúc t ỏ ế ư             Đ a tr đ n 1 v trí ô b t k thu c vùng d li u đang có ỡ ỏ ữ              nh ng liên k t c n g b . ấ             Nh n ch n

ọ Data – Ungroup Clear OutLine

7/27/16 Chương 5 - PM bảng tính điện tử MS 105

EXCEL2007

6.2 - Một số thao tác trên CSDL

6.2.4- Phân tích dữ liệu (PivotTable & PivotChart) (cid:0) PivotTable và PivotChart là những công cụ phân tích dữ liệu rất mạnh trong Excel. Chúng có thể biến những con số dường như vô nghĩa trong một khối dữ liệu khổng lồ thành những con số có nghĩa, bởi lẽ đây là một công cụ đầy tiềm năng giúp ta phân tích dữ liệu theo những mô hình, từ đó có thể hệ thống, khai thác các mối quan hệ trong CSDL theo các hướng khác nhau tức là tạo ra BẢNG THAM CHIẾU CHÉO GiỮA CÁC MỤC DỮ LiỆU.

(cid:0) So với PivotTable ở các phiên bản trước (Excel 2003, Excel XP, ..) thì với Excel 2007 ta sẽ thấy có nhiều thay đổi. Các bảng và các biểu đồ thì không thay đổi bao nhiêu, nhưng phương pháp để tạo ra và làm việc với một PivotTable thì nhanh gọn và đơn giản hơn nhiều.

7/27/16 Chương 5 - PM bảng tính điện tử MS 106

EXCEL2007

6.2 - Một số thao tác trên CSDL

Ví dụ: cho bảng dữ liệu trong trang tính dưới đây.

ầ

ữ Yêu c u phân tích d

li uệ

ớ

V i m i

ơ ị ỗ Tên đ n v

vay, cho bi

t ế Ngày vay và

ờ ạ

ươ ứ t

ng ng

Th i h n vay

ố ề

ổ cùng v iớ T ng s ti n

ị

ủ ơ ả ả c a đ n v vay

ph i tr

đó

7/27/16 Chương 5 - PM bảng tính điện tử MS 107

EXCEL2007

6.2 - Một số thao tác trên CSDL

ớ

Yêu c u phân tích d li u V i m i

Ví dụ: cho bảng dữ liệu trong trang tính dưới đây.

ầ ữ ệ ỗ Năm ph i trả ả, ế t danh sách

cho bi ơ ị

Tên ố ố và V n g c

đ n v vay ươ ứ t

ng ng cùng v i

ả

ớ T ng ổ ả tr trong năm

ố ề s ti n ph i

đó.

7/27/16 Chương 5 - PM bảng tính điện tử MS 108

EXCEL2007

6.2 - Một số thao tác trên CSDL

ấ

ả I C u trúc b ng PivotTable:

Phân tích dữ Mức tổng hợp dữ liệu cao nhất

liệu

Thống

Mức tổng hợp dữ liệu

cao thứ nhì kê/tính toán trên dữ liệu

7/27/16 Chương 5 - PM bảng tính điện tử MS 109

EXCEL2007

PAGE

Trở lại ví dụ trên

COLUMN

DATA

ROW

7/27/16 Chương 5 - PM bảng tính điện tử MS 110

EXCEL2007

ạ ả

II T o b ng Pivot Table

:

ự

ọ

c 1

  B

ướ : l a ch n CSDL

c 2

 B

ướ : vào menu Insert Pivot Table

Sẽ tạo ra bảng

Sẽ tạo ra đồ thị

PivotTable

PivotChart

7/27/16 Chương 5 - PM bảng tính điện tử MS 111

EXCEL2007

ệ

ế

ạ

ấ

ộ

N u ch n

ọ PivotTable thì xu t hi n h p tho i Create

PivotTable:

Đồ thị PivotChart sẽ đặt ngay tại trang tính hiện thời

Bảng PivotTable sẽ để ở 1 trang tính mới

7/27/16 Chương 5 - PM bảng tính điện tử MS 112

EXCEL2007

ệ ấ

ủ ả

ể

ấ

ỗ

ườ

ụ

ả

ồ

ị

 Hi n c u trúc r ng c a b ng PivotTable đ ta nh n rê tên ừ ng r i th vào t ng m c đã qui đ nh

ừ t ng tr

7/27/16 Chương 5 - PM bảng tính điện tử MS 113

EXCEL2007

ệ

ấ

ượ

ể ệ

ả

c th hi n ngay trong mô t

ả ủ ả

ế K t qu c a vi c nh n rê đ ấ c u trúc b ng:

7/27/16 Chương 5 - PM bảng tính điện tử MS 114

EXCEL2007

ả ư ế ế N u ch n ọ PivotChart thì k t qu nh sau:

7/27/16 Chương 5 - PM bảng tính điện tử MS 115

EXCEL2007

:

ứ

ổ ộ : t c là thay đ i n i dung các m c

ỉ ử III Ch nh s a PivotTable & PivotChart ử ấ         1/ S a c u trúc

ụ Report

Filter, Row Label, Column Label, Values. ụ ế

ữ

ừ

ộ

ị

(cid:0)

ấ nh n rê chu t trên n i dung t ng m c đ n nh ng v trí ớ ể

ố

ộ ổ m i đ thay đ i theo ý mu n

7/27/16 Chương 5 - PM bảng tính điện tử MS 116

EXCEL2007

ử ị

ử ụ

ạ : s d ng PivotTable Tools/PivotChart Tools

2/ S a đ nh d ng

7/27/16 Chương 5 - PM bảng tính điện tử MS 117

EXCEL2007

BÀI TẬP: với nội dung trang tính dưới đây

ể ư

ạ ệ

ủ

ỗ

, ngày thanh toán c a m i

ạ ạ ệ

ừ ạ

ổ

ỗ

đó ớ ổ

ố ề ổ

ả ậ

ố ề

ừ

Dùng tính năng PivotTable & PivotChart đ đ a ra:  Danh sách các khách hàng n ti n theo t ng lo i ngo i t ợ ề ợ khách hàng và t ng s ti n n theo m i lo i ngo i t  Danh sách các mã s theo t ng ngày thanh toán v i t ng s ti n lãi tr ch m theo t ng ừ ổ mã s đó. 7/27/16

Chương 5 - PM bảng tính điện tử MS 118

EXCEL2007

Kết quả như sau:

ạ ệ ạ ỗ ủ , ngày thanh toán c a m i khách hàng

ợ ề ạ ạ ệ ố ề ổ ỗ ợ Danh sách các khách hàng n ti n theo t ng lo i ngo i t và t ng s ti n n theo m i lo i ngo i t ừ đó

7/27/16 Chương 5 - PM bảng tính điện tử MS 119

EXCEL2007

Kết quả như sau:

ạ ệ ủ ỗ , ngày thanh toán c a m i khách

ừ ạ ệ ợ ề ỗ ố ề ạ ổ  Danh sách các khách hàng n ti n theo t ng lo i ngo i t ợ hàng và t ng s ti n n theo m i lo i ngo i t ạ đó

7/27/16 Chương 5 - PM bảng tính điện tử MS 120

EXCEL2007

Kết quả như sau:

ả ậ ớ ổ ố ề ừ ừ ổ

 Danh sách các mã s theo t ng ngày thanh toán v i t ng s ti n lãi tr ch m theo t ng mã ổ s đó.

7/27/16 Chương 5 - PM bảng tính điện tử MS 121

EXCEL2007

TỔNG KẾT CHƯƠNG V CHƯƠNG I – CÁC VẤN ĐỀ CƠ BẢN CỦA TIN HỌC ầ

ề

ệ ử

MS EXCEL 2007

ữ

ệ ủ

ạ

ặ

ỗ

Phải trả lời các

ữ ữ ệ

câu hỏi

ắ ự

sau

i th ụ ệ ộ

ọ

ợ ữ ệ

ủ

ậ

ả Ph n m m b ng tính đi n t ứ có nh ng ch c năng gì? Màn hình giao di n c a MS EXCEL 2007 có gì m i?ớ ợ ớ ả ị ữ ệ Đ nh d ng d li u trong Excel ph i phù h p v i ử ườ ị nh ng qui đ nh nào? Các l ng g p khi x lý d li u là gì, cách kh c ph c? Trong MS Excel 2007, th c hi n các thao tác: ắ ế ế s p x p, l c, liên k t dòng/c t, tính toán theo ơ ổ nhóm, phân tích và t ng h p d li u trên 1 c ư ế ở ữ ệ s d li u đã cho nh th nào? Các th thu t trong MS EXCEL 2007 là gì?

ế

ươ

K t thúc ch

ng V