Doosan Heavy Industries Vietnam
KHÓA H C V MS EXCEL
Ọ Ề
28 May 2009 DOOSAN HEAVY INDUSTRIES VIETNAM
Nhóm hàm Toán H cọ
Hàm Sum
Dùng đ tính t ng trong m t ph m vi ho c m t dãy s nào đó ổ ộ ộ ố ể ạ ặ
=Sum(Ph m vi c n tính t ng) ổ ạ ầ
Ho c = Sum(giá tr 1, giá tr 2, …., giá tr n) ặ ị ị ị
Bài t pậ
Hàm Int
L y ph n nguyên c a m t phép tính có d . Làm tròn t ủ ư ộ ấ ầ ớ ố i s nguyên nh h n g n nh t ấ ỏ ơ ầ
= Int(s th p phân) ố ậ
VD: int(11/2) k t qu 5 ế ả
Hàm Mod
L y ph n d c a m t phép chia có d ầ ư ủ ộ ấ ư
=mod(a,b) Trong đó a: s b chia; b: s chia, giá tr này ph i khác 0 ố ị ố ả ị
Hàm round
Làm tròn s x đ n v trí th n ế ị ứ ố
=Round(x,n)
Trong đó x là s th p phân ho c phép tính ố ậ ặ
2
n: s s th p phân c n l y ố ố ậ ầ ấ
Doosan Heavy Industries Vietnam
Nhóm hàm th ng kê
ố
Hàm Average
Tính trung bình c ng c a các đ i s . Có th l y t i 30 tham s trong hàm này ể ấ ớ ố ố ủ ộ ố
=average(giá tr 1, giá tr 2,…,giá tr n) ị ị ị
Hàm Max
Tr v giá tr l n nh t trong dãy các đ i s đã cho ố ố ả ề ị ớ ấ
= Max(giá tr 1, giá tr 2,…,giá tr n) ị ị ị
Hàm Min
Tr v giá tr nh nh t trong dãy các đ i s đã cho ị ỏ ấ ố ố ả ề
= Min(giá tr 1, giá tr 2,…, giá tr n) ị ị ị
Hàm Rank
Tr v th h ng c a m t s c th trong t p h p s ộ ố ụ ể ả ề ứ ạ ậ ợ ố ủ
= Rank(s c n x p b c, danh saxchsm ki u x p b c) ố ầ ế ậ ể ế ậ
Ki u 0: x p b c t l n đ n nh ế ậ ừ ớ ế ể ỏ
Ki u 1: X p b c t nh đ n l n ế ậ ừ ỏ ế ớ ể
3
Bài t pậ
Doosan Heavy Industries Vietnam
Nhóm hàm logic
Hàm If
Dùng ki m tra đi u ki n và tr l i m t trong các giá tr đ ả ạ ộ ị ượ ề ệ ể c ch ra ỉ
=If(Đi u ki n, giá tr 1, giá tr 2) ệ ề ị ị
L u ý:ư
S If luôn luôn nh h n s tr ỏ ơ ố ườ ố ng h p là 1 ợ
K t thúc hàm if có bao nhiêu if thì có b y nhiêu d u ngo c ặ ế ấ ấ
Hàm And
t c các đi u ki n đ ng th i đ c th a mãn, ng c l ế ấ ả ệ ồ ờ ượ ề ỏ ượ ạ ả ề i tr v giá tr ị
Tr v giá tr TRUE n u t ị ả ề FALSE
And(đi u ki n 1, đi u ki n 2,…, đi u ki n n) ệ ề ệ ề ệ ề
Hàm Or
t c các đk đ a ra không th a ả ề ệ ư ỏ ế ấ ả ư ỏ
Tr v giá tr TRUE n u m t đi u ki n đ a ra không th a mãn, t ề ộ ị mãn thì tr v giá tr FALSE ả ề ị
=Or(đi u ki n 1, đi u ki n 2, …, đi u ki n n) ề ệ ề ệ ề ệ
4
Bài t pậ
Doosan Heavy Industries Vietnam
Nhóm hàm đ mế
Hàm count
Đ m các ô d li u s ữ ệ ố ế
=Count(Ph m vi ô c n đ m) ế ạ ầ
Hàm counta
=Counta(ph m vi ô c n đ m) ầ ế ạ
Hàm Countblank
Đ m các ô tr ng( không ch a d li u) ứ ữ ệ ố ế
=countblank(Pham vi ô c n đ m) ầ ế
Hàm countif
Đ m các ô ch a d li u th a mãn đi u ki n ứ ữ ệ ỏ ề ệ ế
=countif(ph m vi ô c n đ m, đi u ki n) ầ ế ề ệ ạ
5
Bài t pậ
Doosan Heavy Industries Vietnam
TRÍCH KÝ T VÀ NHÓM HÀM TÌM KI M
Ự
Ế
Hàm Left
Trích ký t phía bên trái chu i văn b n ự ở ỗ ả
=Left(“Chu i văn b n”, s ph n t c n trích) ố ầ ử ầ ỗ ả
Hàm Right
Trích ký t phía bên ph i chu i văn b n ự ở ỗ ả ả
=Right(“Chu i văn b n”, s ph n t c n trích) ố ầ ử ầ ỗ ả
Hàm Mid
Trích ký t v trí mà ta ch ra ự trong chu i văn b n t ỗ ả ừ ị ỉ
c n trích) =Mid(“Chu i văn b n”, v trí b t đ u trích, s ph n t ị ố ầ ử ầ ắ ầ ổ ả
Hàm Vlookup
Tìm theo c t ch s (giá tr d a tìm ki m n m trên c t) ị ự ỉ ố ộ ộ ế ằ
=Vlookup (Giá tr d a tìm ki m, b ng đ i chi u, stt c t c n l y giá tr , ki u tìm) ố ộ ầ ấ ị ể ị ự ế ế ả
Hàm Hlookup
Tìm theo hàng ch s (giá tr d a tìm ki m n m trên hàng) ị ự ỉ ố ế ằ
=Hlookup(Giá tr d a tìm ki m, b ng đ i chi u, stt hàng c n l y giá tr , ki u tìm) ố ầ ấ ị ể ị ự ế ế ả
6
Bài t pậ
Doosan Heavy Industries Vietnam
S D NG HÀM SUMPRODUCT
Ử Ụ
Hàm Sumproduct
Nhân t ng ng 2 hay nhi u dãy s có cùng s c t và c ng k t qu l i ươ ứ ố ộ ả ạ ố ộ ế ề
Cú pháp: =sumproduct (m ng 1, m ng 2, … m ng n) ả ả ả
Chú ý: Khi s d ng hàm này thì s ph n t trong các m ng ph i b ng nhau ố ầ ử ử ụ ả ằ ả
Bài t pậ
Đ t m t kh u b o v b ng tính ẩ ả ệ ả ặ ậ
M b ng tính c n b o v vào Tool/ Protection/ Ch n Protection Sheet ầ ả ệ ở ả ọ
Gõ m t kh u b o v b ng tính ẩ ả ệ ả ậ
L a ch n các tùy ch n c n b o v cho sheet ọ ầ ả ệ ự ọ
Nh n OK, gõ l ấ ạ ậ i m t kh u, nh n ok ẩ ấ
Ch n Save ọ
Cách b m t kh u ỏ ậ ẩ
M b ng tính v a đ t m t kh u ừ ặ ở ả ậ ẩ
Vào menu Tool/ Protection, ch n Unprotect Sheet ọ
7
Gõ m t kh u ậ ẩ
Doosan Heavy Industries Vietnam
HÀM TÌM KI MẾ
Hàm Index
Tr v giá tr c a m t ô(hay là m ng các ô) d a vào giao đi m c a c t và hàng trong m t m ng ủ ộ ả ề ị ủ ự ộ ộ ể ả ả
Hàm ch có tác d ng đ i v i s m ng và tr v giá tr k t qu ch không ph i tham chi u ô ố ớ ố ả ả ứ ả ề ị ế ụ ế ả ỉ
D ng th c: = Index(array, row_num, Column_num) ứ ạ
Array: vùng c n tìm giá tr ị ầ
Row_num:s th t c a hàng trong b ng ố ứ ự ủ ả
Column_num: s th t c a c t trong b ng ố ứ ự ủ ộ ả
Hàm Match
ng đ i c a m t ph n t trong m ng th a mãn m t giá tr cho tr c theo m t ph ng ố ủ ầ ử ộ ỏ ộ ả ị ướ ộ ươ
Cho v trí t ươ ị th c xác đ nh ị ứ
D ng th c: ứ ạ
=Match(giá tr c n tìm, vùng c n tìm, ki u tìm ki m) ị ầ ể ế ầ
-1: n u mu n tìm giá tr nh nh t l n h n hay b ng giá tr c n tìm ị ỏ ấ ớ ơ ị ầ ố ế ằ
0: tìm chính xác
1: n u mu n tìm giá tr l n nh t nh h n hay b ng giá tr c n tìm ỏ ơ ị ầ ị ớ ố ế ằ ấ
8
Bài t pậ
Doosan Heavy Industries Vietnam
HÀM C S D LI U
Ơ Ở Ữ Ệ
Hàm DSum
Công d ng: L y t ng các s trong c t th a mãn đi u ki n ấ ổ ụ ố ộ ỏ ệ ề
D ng th c: Dsum (database, field, criteria) ứ ạ
Database: vùng l y d li u ấ ữ ệ
Field: là tên tr ng hay m t dãy các ô bi u di n các c t c n tính t ng ườ ộ ầ ộ ổ ể ễ
ườ ng hay m t dãy các ô ch a đi u ki n đ xác đ nh ràng bu c trên nh ng m u tin nào c n tính ị ệ ể ứ ữ ộ ộ ề ẫ ầ
Criteria: là tên tr t ngổ
Hàm DCount
Đ m các ô th a mãn đi u ki n ỏ ế ề ệ
DCount(database, field, criteria)
Database: Vùng l y d li u ấ ữ ệ
Field: là tên tr ng hay m t dãy các ô bi u di n các c t đ m ườ ộ ế ộ ể ễ
Criteria: là tên tr ng hay m t dãy các ô ch a đi u ki n đ xác đ nh tr ng s trong khi đ m ườ ệ ể ứ ộ ề ị ườ ố ế
9
Bài t pậ
Doosan Heavy Industries Vietnam
HÀM FIND VÀ M T S HÀM TH I GIAN Ộ Ố
Ờ
Hàm Hour
Tr v giá tr gi trong ô d li u ki u th i gian ả ề ị ờ ữ ệ ờ ể
D ng th c: =Hour(Serial_Number) ứ ạ
Hàm Minute: tr v giá tr phút trong d li u ki u th i gian ữ ệ ả ề ờ ể ị
D ng th c: =minute(Serial_Number) ứ ạ
Hàm second: tr v giá tr giây trong d li u ki u th i gian ữ ệ ả ề ờ ể ị
D ng th c: =minute(Serial_Number) ứ ạ
Trong đó: Serial_Number: là giá tr gi ho c tham chi u ô ị ờ ặ ế
Hàm Find
Tìm 1 chu i ký t trong chu i ký t khác ỗ ự ỗ ự
Find(Find_text, Within_text,start_Num)
Find text: chu i văn b n c n tìm ả ầ ỗ
Within text: ô c n tìmầ
Start_Num: v trí b t đ u tìm ắ ầ ị
10
Bài t pậ
Doosan Heavy Industries Vietnam
HÀM SUMIF VÀ CH C NĂNG SUBTOTAL Ứ
Hàm Sumif
Công d ng: Tính t ng c a m t dãy ô th a mãn đi u ki n nào đó ủ ụ ộ ổ ỏ ề ệ
Dang th c: =Sumif(Range, Criteria, Sum_Range) ứ
Trong đó:
Range: Vùng th c hi n vi c ki m tra đi u ki n ự ề ể ệ ệ ệ
Criteria: đi u ki n có th là m t s , m t bi u th c hay m t chu i văn b n xác đ nh đi u ki n đ tính t ng ộ ố ộ ứ ổ ộ ỗ ể ệ ề ể ệ ề ể ả ị
Sum_range: Vùng s tính t ng ổ ẽ
Ch c năng Subtotal ứ
Công d ngụ
Th c hi n th ng kê theo t ng nhóm d li u ữ ệ ừ ự ố ệ
Các b c th c hi n: ướ ự ệ
Ph i s p x p th t các dòng c a danh sách theo các vùng khóa c n tính th ng kê ả ắ ế ứ ự ủ ố ầ
Đ t con tr trong vùng CSDL ỏ ặ
Ch n Data/ Subtotal ọ
At each change in: ch n tên tiêu đ c t ề ộ ọ
Use function: ch n hàm đ tính toán trong nhóm d li u ữ ệ ọ ể
11
Bài t pậ
Doosan Heavy Industries Vietnam
Ữ Ệ Ự Ộ
Ế Ữ Ệ
Ọ
Ắ
CÁCH S P X P D LI U VÀ L C D LI U T Đ NG AUTOFILTER
Cách s p x p d li u ắ ế ữ ệ
Ch n mi n d li u c n đ a vào s p x p ề ữ ệ ầ ư ắ ế ọ
Ch n data/ Sort. Màn hình xu t hi n h p h i tho i ạ ệ ộ ộ ọ ấ
Sort by: C t u tiên nh t trong khóa s p x p ắ ế ộ ư ấ
Then by: c t u tiên th 2 và th 3 trong khóa s p x p ắ ế ộ ư ứ ứ
Case Sensitive: Phân bi t ch hoa và ch th ng ệ ữ ườ ữ
Cách l c d li u ọ ữ ệ
Đ a ra các b n ghi th a mãn đi u ki n nào đó ỏ ư ề ệ ả
Đ t con tr t ỏ ạ ặ i vùng d li u c n l c ữ ệ ầ ọ
Ch n Data/ Filter/ Auto Filter ọ
Ch n d li u theo ý mu n ọ ữ ệ ố
N u mu n ch n đ ng th i 2 giá tr , ta vào custom, có các toán t sau ọ ồ ố ờ ế ị ử
Equals: so sánh b ngằ
Does not Equals: So sánh không b ng…ằ
12
Bài t pậ
Doosan Heavy Industries Vietnam
CÁCH V BI U Đ
Ẽ Ể
Ồ
Ch n mi n d li u đ a vào v đ th ẽ ồ ị ề ữ ệ ư ọ
N u các mi n d li u không li n k ta ch n mi n 1 gi phím Ctrl ch n các mi n ti p theo ề ữ ệ ề ề ọ ề ế ữ ọ ế ề
Ch n Insert/ Chart ho c ch n bi u t ng Chart Wizar ể ượ ọ ọ ặ
Chart type: Ch n ki u đ th b t kỳ ể ồ ị ấ ọ
Ch n next, n u c n ch nh s a d li u thì ch n vào v trí Data Range ử ữ ệ ế ầ ọ ọ ỉ ị
Ch n next đi n các thông s sau ố ọ ề
Chart title: gõ tiêu đ c a đ th ề ủ ồ ị
Show Legend: n, hi n ph n chú thích ệ ầ ẩ
Data Label: ch n ph n d li u đi kèm v i đ th ớ ồ ị ầ ữ ệ ọ
Ch n next/ Finish ọ
13
Bài t pậ
Doosan Heavy Industries Vietnam
CH C NĂNG L C CAO C P ADVANCE FILTER VÀ PIVOTTABLE
Ọ
Ứ
Ấ
Ch c năng l c cao c p Advance Filter ứ ọ ấ
ứ ạ ể ọ ữ ỏ ề ệ ể ẫ
Công d ng: Dùng đ l c ra danh sách các m u tin th a mãn nh ng đi u ki n ph c t p, có th sao chép chúng ụ đ n v trí khác ế ị
Th c hi n: ự ệ
T o vùng đi u ki n ệ ề ạ
Đ t con tr vào ô b t kỳ trong danh sách ho c ch n mi n d li u l c ề ữ ệ ọ ọ ỏ ấ ặ ặ
Ch n data/ Filter/ Advance filter ọ
Ch n OK ọ
Ch c năng Pivottable ứ
Công d ng: T đ ng hóa quá trình t ng k t theo nhi u lo i nhóm phân tích và đánh giá s li u ự ộ ố ệ ụ ổ ế ề ạ
Th c hi n: Ch n b ng d li u ọ ả ữ ệ ự ệ
Ch n Data/ Pivottable and Pivot Chart Report ọ
Ch n Microsoft Excel list or Database ọ
Ch n next ọ
ng đ đ a vào các vùng c a b ng Ch n Layout xu t hi n h p tho i ch n các tr ệ ộ ọ ọ ấ ạ ườ ủ ả ể ư
Ch n next Finish ọ
14
Bài t pậ
Doosan Heavy Industries Vietnam
T O MACRO VÀ CH C NĂNG CONSOLIDATE
Ứ
Ạ
T o Macro ạ
i các l nh đã th c hi n Công d ng: Ghi l ụ ạ ự ệ ệ
Các b c th c hi n ướ ự ệ
Ch n Tool/ Macro/ Record New Macro ọ
Macro name: Gõ tên Macro c n t o ầ ạ
Shortcut key: t o phím t t ạ ắ
OK
S d ng ch c năng Consolidate ử ụ ứ
Công d ng: T o nên b ng t ng h p d a trên các b ng đã có tr c ợ ự ụ ổ ạ ả ả ướ
Th c hi n: ự ệ
Bôi đen b ng t ng h p ợ ổ ả
Ch n Data/ Consolidate ọ
T i ô Function ch n hàm c n th ng kê ọ ồ ầ ạ
T i ô Reference: ạ
Bôi đen b ng th nh t ch n Add và ti p t c cho các b ng còn l i ứ ấ ế ụ ọ ả ả ạ
Ch n OK ọ
15
Bài t pậ
Doosan Heavy Industries Vietnam
End of document
16
