Bài giảng Tin học đại cương: Chương 4 – Học viện ngân hàng (Khoa Hệ thống thông tin quản lý)

66 2/16/16

ọ ữ ệ

ắ ế

4.3 – S p x p và l c d  li u

ệ ọ

-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ệ 67 2/16/16

ố ượ

ồ ọ

ệ ớ 5 – Làm vi c v i các đ i t

ng đ  h a

ố ượ

ồ ọ

ầ

ả

ườ

v Đ i t

ng đ  h a là các thành ph n hình  nh mà ng

i

ể

dùng có th  chèn vào trong trang tính.

ả

ượ ư

ữ

ạ

ồ ấ ỳ ị c l u tr  trong b t k  đ nh d ng file đ

§ Picture: M t hình  nh đ ộ

ọ h a nào

• Screenshot: là nút l nh cho phép ng

ệ ủ ụ i dùng ch p màn hình giao di n c a các

ố ượ

ẽ ơ

ề

ể

ả

ng hình v  đ n gi n, 2 chi u và có th  thêm vào

§ ClipArt: Là đ i t trong trang tính

ố ượ

ọ ơ

ả

ườ

ng hình h c đ n gi n mà ng ố ượ

ế ợ ạ ể ạ

ể i dùng có th   ọ

ồ ọ ể

§ Shape: Là các đ i t ẽ v  và k t h p l

i đ  t o thành đ i t

ng đ  h a đ  minh h a.

ấ

ể i dùng đ

§ Smart Art: Là công c  đ  h a Excel cung c p cho ng ụ ồ ọ

ể

ứ ạ

ễ

ệ

ườ ỏ

ơ ồ ệ bi u di n các quy trình công vi c ph c t p hay mô ph ng s  đ  h   68

ệ ụ ươ ứ ệ ườ ở ng trình  ng d ng hi n đang m . ch

ạ

ộ

ố

th ng m ng m t cách nhanh chóng.

2/16/16

ố ượ

ồ ọ

ệ ớ 5 – Làm vi c v i các đ i t

ng đ  h a

ệ

ả

ấ

ộ

v Pictures Tools Format: Xu t hi n khi chèn thêm m t hình  nh

(picture or clip art)

ắ ủ ả

ổ

§ Thay đ i màu s c c a  nh.

ộ ươ

ả

ộ

ả

§ Tăng/gi m đ  sáng, đ  t

ng ph n,

ụ

§ Áp d ng Picture styles

ườ

ệ

ề

§ Thêm đ

ậ ng vi n ngh  thu t

ỉ

ướ ủ ả

§ Ch nh s a kích th ử

c c a  nh.

ệ ứ ả

ệ

§ Hi u  ng  nh ngh  thu t ậ

69 2/16/16

ố ượ

ồ ọ

ệ ớ 5 – Làm vi c v i các đ i t

ng đ  h a

ộ ố ượ

ấ

ọ

ng hình h c

ệ v Drawing Tools: Xu t hi n khi chèn thêm m t đ i t

(shape)

§ Shape Styles: cho phép ng ụ

ườ ề ẽ i dùng tô màu cho hình v , tô màu cho khung vi n hình

ư ẽ v , và áp d ng các hi u  ng ệ ứ Shape Effect nh  Preset, Shawdow,…

§ WordArt Style: cho phép ng

ườ ệ ứ ữ ụ ệ ầ ậ i dùng áp d ng các hi u  ng ch  ngh  thu t cho ph n

ả ộ ẽ văn b n n i dung bên trong hình v .

§ Arrange: S p x p th  t

ế ắ ứ ự ấ ệ ủ ẽ xu t hi n c a các hình v .

§ Size: Cho phép ng

ườ ề ỉ ướ i dùng đi u ch nh kích th ẽ c hình v .

70 2/16/16

ố ượ

ồ ọ

ệ ớ 5 – Làm vi c v i các đ i t

ng đ  h a

ộ ồ ọ

ệ

ấ

v SmartArt Tools: Xu t hi n khi chèn thêm m t đ  h a smart art

ể

ộ ồ ọ

ọ

ẻ Insert, trong nhóm Illustrations,  n ấ

§ Đ  chèn m t đ  h a smart art, ch n th   SmartArt.

vào nút l nh ệ

ộ

ị

§ H p tho i

ể ạ Choose a SmartArt Graphic hi n th  các SmartArt theo 8 nhóm:

ệ

- Li

t kê (List)

- Quy trình (Process)

- Vòng tròn (Cycle)

ấ

- Phân c p (Hierrachy)

ố

ệ - M i quan h  (Relationship)

ậ

- Ma tr n (Matrix)

ự

- Kim t

tháp (Pyramid)

ả

- Hình  nh (Picture)

71 2/16/16

ố ượ

ồ ọ

ệ ớ 5 – Làm vi c v i các đ i t

ng đ  h a

v SmartArt Tools Design:

ổ ị

ầ

§ Creat Graphic: Thêm hình và thay đ i v  trí các thành ph n trong SmartArt

ổ ố ụ ủ

ể ừ ố ụ ệ

ụ

§ Layout: Thay đ i b  c c c a SmartArt, ví d  chuy n t

b  c c li

t kê

ố ụ

thành b  c c quy trình.

ư ệ

ượ

ế ế ẵ

ắ

§ SmartArt Styles: Th  vi n các SmartArt đ

c thi

ề t k  s n v  màu s c,

ệ ứ

ể ườ

ụ

ể

hi u  ng đ  ng

i dùng có th  áp d ng nhanh.

ủ ỏ ấ ả

ệ ứ

ụ

ể

ạ

ặ

§ Reset: H y b  t

t c  các hi u  ng đã áp d ng ho c chuy n sang d ng hình

v .ẽ

72 2/16/16

ố ượ

ồ ọ

ệ ớ 5 – Làm vi c v i các đ i t

ng đ  h a

v SmartArt Tools Format:

ạ

ướ ủ

ầ

§ Shapes: Thay đ i hình d ng, kích th ổ

ẽ c c a các hình v  thành ph n trong

SmartArt.

ư ệ

ế ế

ẽ

ẵ

ầ

§ Shape Styles: Th  vi n các thi

t k  có s n cho các hình v  thành ph n.

ệ ứ

ụ

ữ

ệ

ậ

ả

ầ

§ WordArt Style: Áp d ng các hi u  ng ch  ngh  thu t cho ph n văn b n

ộ

n i dung trong SmartArt.

73 2/16/16

6 – Excel Option

ượ

v Excel Option đ

c chia thành các nhóm sau:

ề

ệ

ể

ỉ

§ General: Tùy ch nh chung v  giao di n hi n th . ị

ứ

ế

ỉ

§ Formulas: Tùy ch nh liên quan đ n công th c, hàm trong Excel.

ử ỗ

ế

ể

ỉ

ả

§ Proofing: Tùy ch nh liên quan đ n ki m tra, s a l

i chính t

ữ , ng  pháp.

ơ ư ự ộ

ề ờ

ủ

ỉ

§ Save: Các tùy ch nh v  th i gian và n i l u t

ặ ị  đ ng và m c đ nh c a Excel.

ề

ỉ

§ Language: Tùy ch nh v  ngôn ng . ữ

ỉ

§ Advanced: Tùy ch nh nâng cao.

ườ

ệ

ỉ

ể

ẩ

i dùng có th  cho  n

§ Customize Ribbon: Tùy ch nh giao di n Ribbon. Ng ớ

ử

ữ

ệ

ỉ hi n các ribbon, thêm m i, ch nh s a ngôn ng  trên ribbon,…

ẽ ượ

ệ

ỉ

ể

§ Quick Access Toolbar: Tùy ch nh các nút l nh s  đ

ị c hi n th  trên thanh

ụ

ậ

công c  truy c p nhanh.

ệ

ế

ổ

ỉ

§ Add­Ins: Tùy ch nh liên quan đ n các ti n ích b  sung, macro, template,…

ả

ậ

ỉ

ệ 74

§ Trust Center: Tùy ch nh liên quan đ n an toàn, b o m t cho tài li u Excel. ế

2/16/16

6 – Excel Option

ỉ

v Tùy ch nh chung (General)

75 2/16/16

Excel option

Các chức năng trong nhóm tùy chỉnh chung được mô tả chi tiết trong bảng sau:

ứ Ch c năng

ượ ọ ể ị ụ ạ Mô tả ị c ch n, Excel s  hi n th  thanh công c  mini đ  đ nh d ng

ả ượ Mini  on Khi đ ạ đo n văn b n đang đ ẽ ể ọ c ch n.

ả ế ượ ệ ứ ị ướ Live c  k t  qu   các  hi u  ng  áp

ọ ể ườ

ệ ẽ ể ễ ự i dùng d  l a ch n h n. ạ ự Show  Toolbar  selection Enable  Preview Color scheme

ọ

ự ọ ợ ẩ ScreenTip Style ị i  dùng  l a  ch n  hi n  th   hay  n    màn  hình  g i  ý

ể

ộ ổ ộ ổ

ữ ướ ể ự ớ for t l p cách hi n th  m c đ nh cho m t trang tính m i. Có 3 l a

ượ ạ ặ ị ọ c  ch n,  Excel  s   hi n  th   tr Khi  đ ọ ơ ụ d ng đ  ng ọ Danh sách này cho phép b n ch n màu cho giao di n Excel (có 3 l a  ạ ch n: màu b c, màu xanh và màu đen) ể ườ Cho  phép  ng ScreenTip. ự ớ ạ ặ ị ọ L a ch n ki u ch  (font) m c đ nh khi t o m t s  tính m i. ạ ữ ặ ị ớ ọ ự L a ch n kích th c ch  m c đ nh khi t o m t s  tính m i. ộ ị ặ ị ế ậ Thi ọ ặ ch n Normal View, Page Break Preview ho c Page Layout View. ớ ộ ổ ố ượ S  l c t o ra khi t a m i m t s  tính. ng trang tính m c đ nh đ

ổ ể ị Use this font Font size Defaul  view  new sheets Include  this  many  sheets User name ả Đ nh danh đ  cá nhân hóa b n sao chép các s  tính Excel.

76 2/16/16

6 ­ Excel Option ứ

ỉ

v Tùy ch nh công th c (Formulas)

ươ ệ ử 77 Ch ả ng 4 ­ PM b ng tính đi n t MS EXCEL2010 2/16/16

ộ

N i dung

1 1

Làm quen với môi trường làm việc Excel 2010

Các hàm số và phân tích dữ liệu với Excel 2010

2 2

1

6

ươ ệ ử 78 Ch ả ng 4 ­ PM b ng tính đi n t MS EXCEL2010 2/16/16

ơ ả

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

ị

ế

ỉ Đ a ch  ô tính ỉ ươ

ị v  Đ a ch  t

ộ : thay th  cho n i dung ô tính. ố c tộ dòng. VD: A2, F25  ng đ i:

ạ ị

ế ớ

ị

ỉ

i vùng đích

ươ ứ

Lo i đ a ch  này s   ươ ng, chi u

theo ph

ẽ b  thay đ i ổ  khi tham chi u t ả ề  và kho ng cách t

ng  ng.

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

ị

ỉ Đ a ch  ô tính

ị

ữ

ỉ

ồ

ệ ố : $c tộ $dòng. VD: $L$1  nguyên

gi

ỉ v Đ a ch  tuy t đ i ạ ị        Lo i đ a ch  này  ế

ế

ề

ả

chi u  đ n  vùng  đích  theo  ph

ư nh  vùng ngu n khi tham  ươ ng,  chi u  và  kho ng

nguyên

ướ ướ

ế ế

ữ ẽ gi i s

c l

ế

ươ

ả

ấ ỳ cách b t k .  ỉ ỗ ợ  $c tộ dòng/c tộ $dòng. VD: $A2, B$1  ị v  Đ a ch  h n h p: ộ ỉ ạ ị c đó  c t/dòng n u tr        Lo i đ a ch  này s   ộ ượ ạ ẽ thay đ i ổ c t/dòng n u tr ấ c đó  có d u $, ng ấ không  có  d u  $  khi  ta  tham  chi u  đ n  vùng  đích  theo  ề ph

ng, chi u kho ng cách t

ế ươ ứ ng  ng.

ươ ệ ử 80 Ch ả ng 4 ­ PM b ng tính đi n t MS EXCEL2010 2/16/16

ể ữ ệ

Các ki u d  li u

ể ữ ệ

ạ

ỗ

ỉ

ể ứ v 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ể ố

ể

§ Ki u ngày tháng

§ Ki u chu i ỗ ể

ữ ệ

ể

ượ

ắ ầ

ứ

ể

ằ

ấ

§ Ki u  công  th c:  là  d   li u  đ ứ

c  b t  đ u  b ng  d u  =,  sau  đó  là  1  bi u  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. Ví d : A4:A12.  ả ữ thì  ph i  nh n:  th c  hay  hàm  dùng  d

Các  công

ể

ẳ

ị

ể li u  ki u  m ng  ậ

Ctrl+Shift+Enter đ  kh ng đ nh sau khi nh p xong.

ươ ệ ử 81 Ch ả ng 4 ­ PM b ng tính đi n t MS EXCEL2010 2/16/16

4­

ể ố

ầ

ị

ả ủ  ph i c a ô

v   Ki u s :

ố ớ ừ ể ữ ệ ề ữ ệ ể ố ng m đ nh thì d  li u ki u s  luôn căn bám l     Các phép toán đ i v i t ng ki u d  li u

tính.

§ 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

sau:

ứ ư ấ

ặ ơ

ừ

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                           6    Logic

ứ ư

ứ

ể

ẽ          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

ộ  trái sang ph i.

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

ể ữ ệ

ố ớ ừ Các phép toán đ i v i t ng ki u d  li u

ể

ể ố

ộ ộ

ể

ấ

ạ

ị

v Ki u ngày tháng ậ ủ ữ ệ Là m t b  ph n c a d  li u ki u s , 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 c a ô tính

§ Phép toán c ngộ

§ Phép toán trừ

ệ ự

ắ

§ 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 ươ

ệ ử 83 Ch ả ng 4 ­ PM b ng tính đi n t MS EXCEL2010 2/16/16

ể ữ ệ

ố ớ ừ Các phép toán đ i v i t ng ki u d  li u

ể

ắ ầ

ữ

ằ

Ở ế ộ

v Ki u chu i:

ỗ  là b t đ u b ng ch  cái.  ỗ ữ ệ

ể

căn bám l

trái

ầ  ch  đ  ng m   c a ô ủ ề

ị đ nh thì d  li u ki u chu i luôn  tính.

ệ

ồ

§ Phép toán quan h , bao g m: =, <, >, ==, <>

§ Phép toán ghép chu i: &ỗ

ữ ệ

§ Chú ý: n u dùng phép toán & trên các d  li u ki u s   ể ố ỗ

ế ả

ế

ữ ố thì k t qu  cho ra là chu i ch  s .

ươ ệ ử 84 Ch ả ng 4 ­ PM b ng tính đi n t MS EXCEL2010 2/16/16

ứ

Công th c trong excel

ộ ậ

ứ

ệ

ợ

v Công th c trong Excel là m t t p h p các câu l nh toán

ể

ữ ệ ườ

ắ ầ

ớ ấ

ứ

ọ ử ụ h c s  d ng đ  tính toán d  li u trong trang tính Excel.  ng b t đ u v i d u “=”,  Các công th c trong Excel th ư

ử

ế

ầ

, hàm, tham chi u,

ể ứ

ứ

ố

ộ

ộ

sau đó là các thành ph n nh  toán t ằ h ng s . M t công th c trong Excel có th  ch a m t vài

ặ ấ ả

ữ ệ

ầ

ổ

t c  các thành ph n trên. Khi d  li u thay đ i thì  ự ộ

ứ

ả

ổ

ho c t ế k t qu  trong công th c cũng t

đ ng thay đ i theo.

ươ ệ ử 85 Ch ả ng 4 ­ PM b ng tính đi n t MS EXCEL2010 2/16/16

ơ ả Các hàm c  b n trong excel

ứ ượ

ự

ẵ

v Hàm là công th c đ

c xây d ng s n trong Excel dùng

ể ử đ  x  lý các thao tác tính toán.

ủ

ạ

ổ

v D ng t ng quát c a hàm:

ố ố  Tên hàm (Danh sách các đ i s )

ế

ữ

ữ ườ

ng. Tên hàm

t ch  hoa ho c ch  th ớ

ặ ủ

ng g n v i ý nghĩa c a các thao tác mà nó cung

§ Tên hàm vi ắ ườ th c p.ấ

ị ầ

ấ

§ Danh sách các đ i s  là các giá tr  đ u vào cung c p

ố ố ử

ể ữ ệ d  li u đ  hàm x  lý

ươ ệ ử 86 Ch ả ng 4 ­ PM b ng tính đi n t MS EXCEL2010 2/16/16

ộ ố

ụ

M t s  nhóm hàm thông d ng

v Financial: hàm tài chính

v Date & Time: hàm ngày tháng

ượ

v Math & Trig: hàm toán và l

ng giác

ố

v Statistical: hàm th ng kê

ế

ế v Lookup & Reference: hàm tìm ki m và tham chi u

ơ ở ữ ệ v Database: hàm c  s  d  li u

ử

ả

v Text: hàm x  lý văn b n

v Logic: hàm lu n lýậ

ệ ử 87 ả ng 4 ­ PM b ng tính đi n t MS EXCEL2010

v Information: hàm thông tin ươ Ch 2/16/16

ộ ố

ặ

M t s  thông báo l

ỗ ườ i th

ng g p

ườ

ỗ

Ý nghĩa

ữ Nh ng tr

ợ ng h p gây l

i

Ký  hi uệ

#####

ộ ộ ị

ộ ạ

ờ

ố

ặ

ế ­ 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.

ỗ ộ L i đ   r ngộ

ỗ

ị

ậ

ầ

ố

ỗ

L i giá tr

#VALU E!

ố ặ ề

ộ

ậ

ho c m t

ố ố ộ ộ

ỉ

ậ

#DIV/0! L i chia

ứ ố ứ

ố

ộ

ố

ế

ế

- 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 ố ử ặ ố 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.

ỗ cho 0

ậ

Sai tên

ượ

ự

ứ

#NAME !

ặ

ấ ệ ử

ứ

ị

ả ng 4 ­ PM b ng tính đi n t

ấ 88

- Nh p sai tên m t hàm s .  ố ộ - Dùng nh ng ký t ữ c phép trong công th c.  không đ - 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

2/16/16

ỉ

ch  ô.

MS EXCEL2010

ộ ố

ặ

M t s  thông báo l

ỗ ườ i th

ng g p

ườ

ỗ

Ý nghĩa

ữ Nh ng tr

ợ ng h p gây l

i

Ký  hi uệ

#N/A

ỗ ữ ệ L i d  li u

ừ

ng thích t

các hàm dò

ữ

ượ

ế ở

#REF!

c tham chi u b i công

Sai vùng tham  chi uế

ừ

ị ạ

ứ

công th c lên chính

ể ạ

ứ

ụ

ế

ế

ỗ ữ ệ

#NUM! L i d  li u

ặ ạ ẫ

ợ ế

ki u sể ố

ộ ố ố ặ ả ả ề c k t qu  tr  v . ả ề ố

ỏ

ớ

ớ

ả

ủ

ệ ử ươ ả ng 4 ­ PM b ng tính đi n t 89

ỗ ữ ệ

ử

ợ

2/16/16 #NULL! L i d  li u

- Không đ ng nh t d  li u ấ ữ ệ ồ - Giá tr  tr  v  không t ươ ị ả ề tìm   - Xóa nh ng ô đang đ th c.ứ - Dán nh ng giá tr  t o ra t ữ ế 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  ượ ế đ - Dùng hàm tr  v  1s  quá l n/nh  so v i kh  năng  tính toán c a Excel Ch ộ   Dùng m t dãy toán t

r ngỗ

không phù h p MS EXCEL2010

Nhóm hàm ngày (Date & Time)

HÀM

ế ờ ệ NOW() Cho bi t ngày, gi ờ  hi n th i

ế ệ TODAY() Cho bi ờ t ngày hi n th i

ế ủ DAY(serial_number) Cho bi t ngày c a serial_number

ế ủ MONTH(serial_number) Cho bi t tháng c a serial_number

ế ủ YEAR(serial_number) Cho bi t năm c a serial_number

ươ ệ ử 90 Ch ả ng 4 ­ PM b ng tính đi n t MS EXCEL2010 2/16/16

HÀM

VÍ DỤ

ọ

FACT(number)

= FACT(4)

Nhóm hàm toán h c (Math & trig) ừ ủ Tính giai th a c a number

ả ề ế

ả

Tr  v  k t qu  24

INT(number)

= INT(123.7)

ủ

ầ

ấ

L y ph n nguyên c a number

ả ề ế

ả

Tr  v  k t qu  123

MOD(number,divisor)

= MOD(23,5)

ư ủ

ầ

Ph n d  c a phép chia number cho divisor

ả ề ế

ả

Tr  v  k t qu  3

SQRT(number)

=SQRT(144)

ủ

ậ

ả ề ế

ả

Căn b c hai c a number

Tr  v  k t qu  12

PRODUCT(number1, number2,...)

=PRODUCT(9,8,7)

ố

Tích các s  number1, number2,...

ả ề ế

ả

Tr  v  k t qu  504

SUM(number1, number2,...)

=SUM(15.8,35.6)

ổ

ố

T ng các s  number1, number2,...

ả ề ế

ả

Tr  v  k t qu  51.4

ROUND(number, num_digits)

= ROUND(35.67, 1)

ữ ố

ầ

ậ

ớ

ố

Làm tròn s  number v i num_digits ch  s  ph n th p phân

ả ề ế

ả

Tr  v  k t qu  35.7

LN(number)

=LN(1)

Tính ln

ả ề ế

ả

Tr  v  k t qu  1

ươ ệ ử 91 Ch ả ng 4 ­ PM b ng tính đi n t MS EXCEL2010 2/16/16

ậ Nhóm hàm lu n lý (Logical)

HÀM

ả ề ế

ả

AND(logical1,logical2,..)

VÍ DỤ =AND(3<7,2>=0)  Tr   v   k t  qu   TRUE

ả ề ế

ả =AND(3>7,2>=0)                            Tr   v   k t  qu

ọ ố ố

ế

ị

Cho tr  TRUE n u m i đ i s  là TRUE, ng

ượ ạ c l

i

ị

cho tr  là FALSE OR(logical1,logical2,..)

FALSE =OR(3>7, 2>=0)

ế

ị

ấ ỳ ố ố Cho tr  TRUE n u b t k  đ i s  nào là TRUE, cho

ả ề ế

ả

Tr  v  k t qu  TRUE

ị

ế ấ ả ố ố

t c  đ i s  là FALSE

tr  là FALSE n u t NOT(logical)

=NOT(6<5)

ượ ạ ớ ị

ủ ố ố

ị Cho tr  logic ng

i v i tr  logic c a đ i s

c l

ả ề ế

ả

Tr  v  k t qu  TRUE

ả ử

ị

ạ

ề

IF(logical_test, value_if_true, value_if_false)

Gi

s  giá tr  ô A1=5 và t

i ô B1 ta đi n hàm

ế

ế

ả

=IF(A1>=5, "Trung bình”, "Kém”)

ế

ị

Hàm  cho  k t  qu   là  value_if_true  n u  logical_test  ả i  hàm  cho  k t  qu

có  giá  tr   là  TRUE,  ng

ượ ạ c  l

ả ề ế

ả Tr  v  k t qu  "Trung bình”.

ế

ị

value_if_false n u logical_test có giá tr  là FALSE .

ươ ệ ử 92 Ch ả ng 4 ­ PM b ng tính đi n t MS EXCEL2010 2/16/16

ử

ỗ

Nhóm hàm x  lý chu i (Text)

HÀM

ụ

VÍ DỤ ọ ứ

LEFT(text, num_chars)

= LEFT(“Tin h c  ng d ng”, 7)

ỗ

ả ề ế

ọ

ữ ạ  l

i num_chars ký t

bên trái chu i text

ọ ứ

ự Gi RIGHT(text, num_chars)

ả Tr  v  k t qu  “Tin h c” ụ =RIGHT(“Tin h c  ng d ng”,8)

ự

ỗ

ả ề ế

ả ứ

ụ

ữ ạ  l

i num_chars ký t

bên ph i chu i text

ụ

ả Gi MID(text, start_num,num_chars)

Tr  v  k t qu  “ ng d ng” ọ ứ =MID(“Tin h c  ng d ng”,4,3)

ự ứ

ủ

ả ề ế

ả ọ

ả ề

ự ể ừ   k   t

ký  t

th   start_num  c a

Tr  v  k t qu  “h c”

ọ ứ

ụ

Tr   v   num_chars  ký  t chu iỗ LEN(text)

= LEN(“Tin h c  ng d ng”)

ộ

ả ề ế

ọ ứ

ụ

ỗ Đ  dài chu i PROPER(text)

ả Tr  v  k t qu  16 =PROPER(“Tin h c  ng d ng”)

ả ề

ữ

ỗ

ở ầ

ỗ ừ

ả

ả ề ế

ọ Ứ

Tr  v  chu i có ch  in hoa

đ u m i t

ng

UPPER(text)

Tr   v   k t  qu   “Tin  H c  D ng”ụ =UPPER(“Tin h c”)ọ

ổ

ỗ

ả ề ế

Ọ

Đ i thành chu i in hoa LOWER(text)

ả Tr  v  k t qu  “TIN H C” LOWER(“HA NOI”) = “ha noi”

ổ

ỗ

ườ

ng

Đ i thành chu i in th VALUE(text)

VALUE(“0834”) = 834

ệ ử ươ 93 MS EXCEL2010 2/16/16 ổ

ỗ

ả Ch ng 4 ­ PM b ng tính đi n t ữ ố ố Đ i chu i các ch  s  thành dãy các con s

ố

Nhóm hàm th ng kê (Statistical)

HÀM

Ý NGHĨA

ị ỏ ấ

ả ề

ố ố

MIN(number1,[number2],…)

Tr  v  giá tr  nh  nh t trong các đ i s

ả ề

ị ớ

ố ố

ấ

MAX(number1,[number2],…)

Tr  v  giá tr  l n nh t trong các đ i s

ả ề

ố ố

ủ

ị

AVERAGE(number1,[number2],…)

Tr  v  giá tr  trung bình c a các đ i s

ố ượ

ế

ể ố

ề

COUNT(value1,[value2],…)

Đ m s  l

ng các ô ki u s  trong mi n ô

ố ượ

ế

ỗ

ề

COUNTA(value1,[value2],…)

Đ m s  l

ng các ô khác r ng có trong mi n

ô

ố ượ

ế

ố

ề

COUNTBLANK(range)

Đ m s  l

ng các ô tr ng trong mi n ô

ố ượ

ế

ả ề

ệ

COUNTIF(range,criteria)

Đ m  s   l

ng  các  ô  tho   đi u  ki n  criteria

ư

ề

đ a ra có trong mi n ô range

ị ố

ổ

ề

SUM(number1,[number2],…)

Tính t ng các giá tr  s  trong mi n ô

ị ố

ổ

SUMIF(range,criteria,[sum_range])

ả Tính t ng các giá tr  s  trong sum_range tho

ứ

ề

ệ

ề

ề

đi u ki n criteria có trong mi n ô ch a đi u

ệ

ki n range

ươ ệ ử 94 Ch ả ng 4 ­ PM b ng tính đi n t MS EXCEL2010 2/16/16

ế

Nhóm hàm dò tìm và tham chi u (Lookup and reference)

HÀM

ộ

ề

INDEX(Array,Row_num,Column_num)

Ý NGHĨA Cho n i dung ô tính trong mi n ô dò tìm Array,

ỉ ố

ỉ ố ô tính này có ch  s  dòng là Row_num và ch  s

ố ủ

ươ

MATCH(lookup_value,lookup_array,

ng đ i c a lookup_value trong dãy

ộ c t là Column_num ị Cho v  trí t

ộ

ỉ

match_type)

dò  tìm  lookup_array  (dãy  ch   là  dòng  hay  c t)

ớ ể

ng  ng v i ki u tìm match_type.   ả ề

ị

LOOKUP(lookup_value,lookup_vector,

ươ ứ t ế Tìm  ki m  và  tr   v   giá  tr   có  trong  dãy

ở

ứ

ớ

result_vector)

result_vector

ị   v

trí

ươ t

ng

ng  v i

ở

ầ

ị

HLOOKUP(lookup_value,table_array,row_i

lookup_value trong dãy dò tìm lookup_vector. Tìm  giá  tr   lookup_value

dòng  đ u  tiên  trong

ề

ndex_num,range_lookup)

ữ mi n  ô  d

ệ li u

table_array

có

ứ ch a

ấ ẽ ả ề

ế

ị

lookup_value.  N u  tìm  th y  s   tr   v   giá  tr   ô

ở

ứ

ế

ươ t

ứ ng  ng

dòng  th   row_index_num.  N u

ấ ẽ ư

không  tìm  th y  s   đ a  ra  thông báo  #N/A (Not

ở ộ ầ

ị

Available). Range_lookup là tham s  tu  ch n. Tìm  giá  tr   lookup_value

ố ỳ ọ   c t  đ u  tiên  trong

VLOOKUP(lookup_value,table_array,col_in

table_array

ứ ch a

dex_num,range_lookup) ươ

ữ ề mi n  ô  d   ệ ử

Ch ả ng 4 ­ PM b ng tính đi n t

ệ li u   MS EXCEL2010

2/16/16

có  95 ấ ẽ ả ề

ế

ị

lookup_value.  N u  tìm  th y  s   tr   v   giá  tr   ô

ươ

ứ

ở ộ

ứ

ế

t

ng

ng

c t  th   col_index_num.  N u

ấ ẽ ư

không  tìm  th y  s   đ a  ra  thông báo  #N/A (Not

ố ỳ ọ

Available). Range_lookup là tham s  tu  ch n.

ơ ở ữ ệ Nhóm hàm c  s  d  li u (Database)

v TÊN_HÀM_CSDL (database,field,criteria)

ầ

ặ

ộ

ộ

§ Database: Vùng CSDL là toàn b  ho c m t ph n

CSDL ban đ u.ầ

ứ

ỉ

§ Field: Tên tr

ườ

ặ ươ ứ

ộ

ị ngườ  (thông qua đ a ch  ô ch a tên  ộ c t thu c tính th  n

ng  ng là

ứ  trong

ng) ho c t tr vùng CSDL đã ch n.ọ

ườ

ứ

ề

ng và

§ Criteria: Vùng tiêu chu nẩ  là mi n ô ch a tên tr ị

ứ

ứ

ề

ể

ả

ệ ủ 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.

ươ ệ ử 98 Ch ả ng 4 ­ PM b ng tính đi n t MS EXCEL2010 2/16/16

M t s  hàm CSDL

Ý nghĩa ị

ộ

ườ

ộ ốTên hàm DAVERAGE(database,field,criteria)

Tính trung bình các giá tr  thu c tên tr

ộ ng/c t n

ệ

ề

ượ

c  di n  t

ễ ả ở

ị ớ

ỏ

ộ

ấ

ấ

ấ

ườ

DMAX/DMIN(database,field,criteria)

trong  vùng  CSDL  theo  đi u  ki n  đ vùng tiêu chu n.  ẩ L y ra giá tr  l n nh t/nh  nh t thu c tên tr

ng/

ệ

ề

ượ

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

ễ c di n

ẩ ộ

ườ

ộ

DGET(database,field,criteria)

ả ở  vùng tiêu chu n. t   ị ấ L y  ra  giá  tr   thu c  tên  tr

ng/  c t  n  trong  vùng

ệ

ề

ượ

ễ ả ở

c  di n  t

vùng  tiêu

ố ượ

ộ

ố

ị

CSDL  theo  đi u  ki n  đ chu n.ẩ ế Đ m  s   l

DCOUNT(database,field,criteria)

ể ng  các  giá  tr   ki u  s   thu c  tên

ườ

ộ

ề

ệ

tr

ng/  c t  n  trong  vùng  CSDL  theo  đi u  ki n

vùng tiêu chu n.

ễ ả ở c di n t   ố ượ

ượ ế

ộ

ườ

DCOUNTA(database,field,criteria)

đ Đ m  s   l

ẩ ố ng  ô  không  tr ng  thu c  tên  tr

ng/

ệ

ề

ượ

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

ễ c di n

vùng tiêu chu n. ộ

ổ

ộ

ị

ườ

ẩ ả ở t   Tính t ng c ng các giá tr  thu c tên tr

ộ ng/ c t n

DSUM(database,field,criteria)

ề

ượ

ươ

ễ ả ở c  di n  t   99

Ch ả ng 4 ­ PM b ng tính đi n t

ệ  MS EXCEL2010

2/16/16

trong  vùng  CSDL  theo  đi u  ki n  đ ệ ử vùng tiêu chu n.ẩ

Ví dụ

ươ ệ ử 100 Ch ả ng 4 ­ PM b ng tính đi n t MS EXCEL2010 2/16/16

ữ ệ

Phân nhóm d  li u (SUBTOTAL)

ế ữ ệ

ế

ắ

ả

ặ

ầ

ti n hành s p x p d  li u (tăng d n ho c gi m

ườ

ữ ệ

ầ

ướ v B c 1: ầ d n) trên tr

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

ự

ọ

ướ v B c 2:

l a ch n vùng CSDL.

v B c 3

ướ : Data – Subtotal.

ươ ệ ử 101 Ch ả ng 4 ­ PM b ng tính đi n t MS EXCEL2010 2/16/16

Ví dụ

ừ

ậ

ổ

ớ

Tính t ng thu nh p theo t ng nhóm gi

i tính

ươ ệ ử 102 Ch ả ng 4 ­ PM b ng tính đi n t MS EXCEL2010 2/16/16

ế ữ ệ

Liên k t d  li u (tính năng GROUP and

ườ

ủ ộ

ự

ệ

OUTLINE) ố v Tình hu ng 1: Ng

ộ ề

ế

i dùng ch  đ ng th c hi n liên  ừ

ề k t các dòng/c t li n k  vào thành t ng nhóm

ỉ ự

ế

ệ

ố

ữ ệ

ườ

ừ

§ B c 1 ữ ướ : (ch  th c hi n khi mu n liên k t các dòng d   ẽ ế ệ ng s

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

ữ ệ

phân nhóm d  li u.

ộ ữ ệ ầ

ế

ọ

§ B c 2 ướ : ch n các dòng/ c t d  li u c n liên k t trong  ộ

m t nhóm.

ọ

§ B c 3

ướ : ch n Data – Group – Group

ươ ệ ử 103 Ch ả ng 4 ­ PM b ng tính đi n t MS EXCEL2010 2/16/16

Ví dụ

ươ ệ ử 104 Ch ả ng 4 ­ PM b ng tính đi n t MS EXCEL2010 2/16/16

ế ữ ệ

Liên k t d  li u (tính năng GROUP and

OUTLINE) ố

ẽ ự ộ

ự

ế

ệ

v Tình hu ng 2: Excel s  t

ấ ữ ệ

ệ

đ ng th c hi n liên k t  ề

ộ ề

ứ

ẫ

ộ c t khi phát hi n th y d  li u trên các c t li n k  có  liên quan l n nhau qua công th c/hàm

ộ ế

ữ ệ

ư

ấ

§ B

c 1

ướ : Đ a chu t đ n ô b t kì trong vùng d  li u.

ọ

ấ

§ B

ế

ệ

ộ

: Nh n ch n Data ­ Group ­ Auto OutLine =>    đ ng  xu t  hi n  các  liên  k t  dòng/c t  theo  các

ấ ể ượ

ướ c 2 ẽ ự ộ s   t ấ c p có th  đ

c.

ế

ữ

ế

ố

ộ

ẽ ừ ố

ề

ề

ệ

Chú ý: n u trong CSDL không có m i liên k t gi a các c t  ự li n k  thì Excel s  t

ch i th c hi n tính năng Auto

ươ ệ ử 105 Ch ả ng 4 ­ PM b ng tính đi n t MS EXCEL2010

OutLine. 2/16/16

ế

ả

B ng tham chi u chéo ­ Pivot Table

ự

ọ v L a ch n CSDL

v Insert ­ PivotTable/ PivotChart

ươ ệ ử 106 Ch ả ng 4 ­ PM b ng tính đi n t MS EXCEL2010 2/16/16

ấ

ả

C u trúc b ng PivotTable

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

Report Filter

Phân tích d  ữ li u  ệ

Column Labels

Row Labels

∑ Values

ứ ổ M c t ng  ợ ữ h p d   ệ li u cao  ứ th  nhì

Th ng ố kê/tính  toán  trên d  ữ li u ệ

ươ ệ ử 107 Ch ả ng 4 ­ PM b ng tính đi n t MS EXCEL2010 2/16/16

Ví dụ

ơ ị

ớ

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

đó

ươ ệ ử 108 Ch ả ng 4 ­ PM b ng tính đi n t MS EXCEL2010 2/16/16

Ví dụ

ươ ệ ử 109 Ch ả ng 4 ­ PM b ng tính đi n t MS EXCEL2010 2/16/16

ễ ữ ệ

ể ồ

ể

ằ

Bi u di n d  li u b ng bi u đ

ươ ệ ử 110 Ch ả ng 4 ­ PM b ng tính đi n t MS EXCEL2010 2/16/16

ướ

ữ ệ

Phân tích xu h

ng d  li u

v Sparkline

v Trendline

ươ ệ ử 111 Ch ả ng 4 ­ PM b ng tính đi n t MS EXCEL2010 2/16/16