Ch
̣
ươ ng 2. ̃ Ơ Ở Ư HÀM VÀ C S D LIÊU TRONG EXCEL
1/7/16
MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel
1
Ộ
N I DUNG
ử
• Các toán t
trong Excel
• Các hàm trong Excel
ơ ở ữ ệ
• C s d li u trong Excel
• Đ thồ ị
ề
ả
ổ
ợ
• B ng t ng h p đa chi u
1/7/16
MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel
2
Ử 1. CÁC TOÁN T TRONG EXCEL
1/7/16
MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel
3
2. HÀM
̀
́
• Ha m luân ly
́
̀
̣
• Ha m sô hoc
́
̀
• Ha m thô ng kê
̀
̀
̣
• Ha m điê u kiên
̀
̃ • Ha m chuô i
̀
̀ơ
• Ha m th i gian
̀
̀
̀
• Ha m do ti m
̣
1/7/16
MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel
4
2.1. HÀM LÝ LU NẬ
• AND (đ i 1, đ i 2,…, đ i n)
ố : phép VÀ, là hàm logic,
ố ố ố ị ỉ ố ấ ả t c các đ i s có giá tr đúng. ch đúng khi t
ố ố ứ ể ằ Các đ i s là các h ng, bi u th c logic .
VD: = AND (B3>=23,B3<25)
ố : phép HO C, là hàm logic,
ố ố ố ỉ ị ố Ặ • OR (đ i 1, đ i 2, …, đ i n) ấ ả t c các đ i s có giá tr sai. ch sai khi t
•
VD: = OR (D3>=25,D3<23)
̉ ̣ NOT (logic): phu đinh
1/7/16
MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel
5
Ố Ọ
2.2. HÀM S H C
ệ ố
ấ
ị
o Hàm l y giá tr tuy t đ i
ố
ể
ứ ố
N là s / bi u th c s
ế
ả
ABS(N) Ví d : ụ =ABS(25) k t qu 25
ế
ả
=ABS(5149) k t qu 144
ấ
o Hàm l y căn
ứ ố
ố
ể
SQRT(N) N là s / bi u th c s (N>0)
ụ
ế
ả
Ví d : SQRT(25)
k t qu 5
1/7/16
MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel
6
Ố Ọ
2.2. HÀM S H C
o Hàm l y ph n nguyên
ầ ấ
ứ ố ố ể INT(N) N là s / bi u th c s
ụ ế Ví d : =INT(236.26) ả k t qu 236
o Hàm l y ph n d phép chia nguyên N cho M
ầ ư ấ
MOD(N, M)
ụ ế ả Ví d : = MOD(10,3) k t qu 1
o Hàm l y giá tr PI :
ấ ị PI()
ụ ế Ví d : = PI()*2^2 ả k t qu 12.566..
o Hàm mũ: POWER(x , y) Tr v x mũ y
ả ề
1/7/16
7
MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel ụ
ế ả Ví d : =power(4 , 2) k t qu 16
Ố Ọ
2.2. HÀM S H C
o Hàm làm tròn lên
•
ứ ố ể ROUND(bi u th c s , N)
•
ả ộ ậ N>0: làm tròn bên ph i c t th p phân
•
ậ ộ N<0: làm tròn bên trái c t th p phân
ấ ố ẻ N=0: làm tròn, không l y s l
Ví d :ụ
ế
ả
=ROUND(35123.376, 2) k t qu 35123.38
ế
ả
=ROUND(35123.376, 3)k t qu 35000
ế
ả
=ROUND(35123.376, 0) k t qu 35123
1/7/16
MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel
8
Ố
2.3. HÀM TH NG KÊ
ố
ố
ố
ố ố
ổ
ủ : cho t ng c a các đ i s
• SUM (đ i 1, đ i 2, …, đ i n)
ố ố
ỉ
ị
ề . ằ 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
ố
1/7/16
MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel
9
Ố
2.3. HÀM TH NG KÊ
ố
ố
ố
ị ớ
ấ
: cho giá tr l n nh t.
• MAX (đ i 1, đ i 2, …, đ i n)
ố
ố
ố
ấ
ỏ
ị : cho giá tr nh nh t.
• MIN (đ i 1, đ i 2, …, đ i n)
1/7/16
MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel
10
Ố
2.3. HÀM TH NG KÊ
ố ố ượ ế : đ m s l ứ ố ng các ô có ch a s ố • COUNT(đ i1, đ i2,…)
ố ố
ố
ị
ị
ỉ
ỉ – Các đ i s là các s , ngày tháng, đ a ch ô, đ a ch
mi n.ề
ố ố ố và các s trong các đ i s .
Ví dụ 1 Ví dụ 2
1/7/16
MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel
11
Ố
2.3. HÀM TH NG KÊ
o COUNTA(value1; value2; ….)
ố ượ
ế
ng các ô ch a
ứ d li u
ữ ệ trong
Đ m s l danh sách
1/7/16
MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel
12
Ố
2.3. HÀM TH NG KÊ
ố
• Hàm tính tích các s trong danh sách:
ố ặ
PRODUCT (danh sách các s ho c vùng)
1/7/16
MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel
13
Ố
2.3. HÀM TH NG KÊ
RANK(number , ref , order)
ộ
Tr v th h ng c a s
ủ ố number trong m t danh
ả ề ứ ạ sách các đ i s
ố ố ref ố
ủ
ạ
ố
ế
ế
ộ
n Number: là s mu n tìm h ng c a nó n Ref: là m t dãy hay m t tham chi u đ n m t ộ
ộ danh sách các đ i số ố
ố ỉ
ạ
n Order: là s ch đ nh cách đánh h ng: ị ố ớ ặ
o Order = 0 (ho c không ghi): S l n đ
ượ ế c x p
ạ h ng tr ướ c
1/7/16
14
o Rrder khác 0 : S nh đ
ạ MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel ỏ ượ ế ố ướ c x p h ng tr c
Ố
2.3. HÀM TH NG KÊ
Khi thứ thự xếp bằng 1 Khi thứ thự xếp bằng 0
1/7/16
MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel
15
Ố
2.3. HÀM TH NG KÊ
ủ
ổ o Hàm tính t ng c a các tích
SUMPRODUCT (array1; [array2]; …)
= 3*2 + 4*7 + 8*6 + 6*7 + 1*5 + 9*3.
1/7/16
MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel
16
Ệ
Ề
2.4. CÁC HÀM ĐI U KI N
•
ị
:
ị IF (bt logic, tr đúng, tr sai)
ị
ể – Hi n th
ị tr đúng
n u ế BT logic có g/t True
ể – Hi n th
ị ị tr sai
n u ế BT logic có g/t False
ỗ
ượ
VD: =IF(A3>=5,“Đ ”,“Tr
t”)
ể ế ồ
Ø Hàm IF có th vi
t l ng nhau.
VD: = IF(C6<=300,1,IF(C6>400,3,2))
1
ả ủ
ử
ế
Hàm trên cho k t qu c a phép th sau:
(cid:0)
(cid:0)
ữ ệ
300
(cid:0) (cid:0) (cid:0)
(cid:0)
ữ ệ
2 ế n u [d li u trong ô C6] 3 ế n u 300
400
< [d li u trong ô C6]
ữ ệ
ế
n u [d li u trong ô C6]
> 400
(cid:0)
1/7/16
MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel
17
Ệ
Ề
2.4. CÁC HÀM ĐI U KI N
ề ổ
ề
: hàm tính
• SUMIF (mi n_đ/k, đ/k, mi n_t ng)
ề
ệ ổ t ng có đi u ki n
1/7/16
MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel
18
Ệ
Ề
2.4. CÁC HÀM ĐI U KI N
ế
ề
• COUNTIF(mi n_đ m, đi u_ki n) ả
ề ế
ề
ề
ố ế ệ : đ m s ng các ô trong mi n đ m tho mãn đi u
ượ l ki n.ệ
Ví dụ 1
Ví dụ 2
1/7/16
MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel
19
2.5. HÀM CHU IỖ
ự
ỗ
ự
ỗ
Cho n ký t
ủ bên trái c a chu i.
• LEFT(“Chu i ký t ”, n):
ộ
– 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 chu i.
• RIGHT(“Chu i ký t ”, n):
ộ
– VD: =RIGHT(“Gia Lâm – Hà N i”,6)
ế
ả
ộ
ỗ
cho k t qu là chu i “Hà N i”
ự
ự
ừ
ự ứ
Cho n ký t
tính t
ký t
th m
ỗ • MID(“Chu i ký t ”, m, n):
ỗ
ủ c a chu i.
ộ
– VD: =MID(“Gia Lâm–Hà N i”,9,2)
ế
ả
ỗ
cho k t qu là chu i “Hà”
1/7/16
MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel
20
2.5. HÀM CHU IỖ
ủ ề o Hàm tính chi u dài c a chu i: ỗ LEN (“chu i”)ỗ
ả ỗ ứ ố ự có
ề Tr là chi u dài trong chu i, t c là s ký t trong chu iỗ
ụ Ví d : =LEN (“Excel”) kq: 5
ổ o Hàm đ i chu i th ỗ ườ : LOWER (“chu i”)ỗ ng
Ví d :ụ
Ế Ọ ế ọ =LOWER(“KINH T H C”) kq: “kinh t h c”
1/7/16
MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel
21
2.5. HÀM CHU IỖ
ỗ ổ o Hàm đ i chu i hoa: UPPER (“chu i”)ỗ
Ví d : ụ
ế ọ Ế Ọ =UPPER(“kinh t h c”) kq: “KINH T H C”
ự ầ ủ ổ o Hàm đ i các ký t đ u c a “word” thành hoa
PROPER (“chu i”)ỗ
Ví d : ụ
ế ọ ế ọ =PROPER(“kinh t h c”) kq: “Kinh T H c”
1/7/16
MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel
22
2.5. HÀM CHU IỖ
o
ừ ỗ ả ố Hàm xoá kho ng tr ng th a trong chu i
TRIM (“chu i”)ỗ
Ví d : ụ
=TRIM (“ Xin Chào ”) kq: “Xin chào”
o Hàm đ i chu i s thành tr s
ỗ ố ị ố ổ
ỗ ố VALUE (“chu i s ”)
ụ Ví d : =VALUE (“123”) kq: 123
1/7/16
MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel
23
Ể
2.6. HÀM KI U NGÀY
o YEAR (“chu i ngày”)
ỗ
ả ề ố ươ ứ ế Tr v s năm t ng ng (1900 đ n 2078)
ụ Ví d : =YEAR(“24/12/2004”) kq: 2004
ế ắ ầ o DAYS360(“ngày b t đ u”;”ngày k t thúc”)
ố ừ ắ ầ ế “ngày b t đ u” đ n “ngày
ế ả ề ổ Tr v t ng s ngày t k t thúc”
1/7/16
MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel
24
2.7. HÀM DÒ TÌM
ị ứ
ữ ệ
ứ ả b ng_tra_c u, ớ : tra c u gứ /t /t v i các g ị ữ ệ ươ ng
(tr _tra_c u, • VLOOKUP ộ ấ c t_l y_d _li u, [True/False]) ủ ả trong c t đ u tiên c a b ng và hi n th d li u t ứ ằ ứ ộ ầ ả ể ộ ở ố ố ng trong b ng tra c u n m trên c t đ i s 3.
VD: = VLOOKUP(E3, $E$12:$F$16, 2, True)
ả ệ ấ :
ứ ự
hàm VLOOKUP ế ng t ị ặ ỗ i
n HLOOKUP(g/t, b ng_g/t, hàng_l y_d.li u, [1/0]) ươ n ISNA(value): Tr v giá tr TRUE n u g p l i tr v FALSE.
hàm tra c u theo hàng, t ả ề ượ ạ ả ề #N/A, ng c l
1/7/16
MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel
25
2.7. HÀM DÒ TÌM
• Match(lookup_value, lookup_ref, type)
•
ỗ ố ủ
ể
ị ầ ố
ứ ữ ệ
ộ
ị lookup_value: giá tr c n tìm, giá tr này có th là: chu i, s c a ỗ m t ô ch a d li u là s hay chu i
•
ữ ệ
ỉ ộ
ạ
ộ
lookup_ref: ph m vi vùng d li u (ch m t dòng hay c t)
•
ể type: các ki u so sánh
– 1:
ị ớ
ằ
ấ
ỏ ơ • Tìm giá tr l n nh t nh h n hay b ng lookup_value
ả ượ ắ
ứ ự
ầ
• Lookup_ref: ph i đ
c s p theo th t
tăng d n
ế ấ ả
ả ề
ỏ ơ
ề
• N u t
t c trong lookup_ref đ u nh h n lookup_value thì hàm tr v #NA
– 1:
ấ ớ
ằ
ỏ
ơ
ị
• Tìm giá tr nh nh t l n h n hay b ng lookup_value
1/7/16
MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel
ả ượ ắ
ứ ự ả
ầ
• Lookup_ref: ph i đ
c s p theo th t
gi m d n
26
ế ấ ả
ả ề
ỏ ơ
ề
• N u t
t c trong lookup_ref đ u nh h n lookup_value thì hàm tr v #NA
ả ề
ế
– 0: dò tìm chính xác đúng, n u không tr v #N/A
2.7. HÀM DÒ TÌM
1/7/16
MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel
27
2.7. HÀM DÒ TÌM
•
ả ề
ị ủ
ạ
i
ộ
ộ
INDEX(array,row_num,column_num): Tr v giá tr c a ô t dòng và c t trong m t dãy ô
Pear
1/7/16
MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel
28
Ơ Ở Ữ Ệ
Ủ
3. C S D LI U C A EXCEL
• Khái ni mệ
ế ắ • S p x p
• Tìm ki mế
• Subtotal
1/7/16
MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel
29
3.1. KHÁI NI MỆ
ườ
ả
ồ • CSDL g m các tr
ng (field) và b n ghi
(record).
ộ ộ
ỗ
ườ
ng là m t c t CSDL, m i tr
ủ ố ượ
ị ể ng bi u th ữ ệ ể ng và có ki u d li u
ườ • 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.
ề
ườ
• 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
1/7/16
MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel
30
Ế
Ắ 3.2. S P X P
ứ ự ế • Khi x p th t ả ch n t ọ ấ ả t c
ữ ệ ể 1 danh sách (CSDL), ph i ự ấ 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 .
• Tr
•
ườ ứ ự ọ ế ị ng quy đ nh cách x p th t g i là khoá.
ề
ọ – B1: Ch n mi n.
– B2: Ch n ọ Menu Data sau
ể ượ
ọ
đó ch n bi u t
ng
Sort
Cách làm:
1/7/16
MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel
31
Ế
Ắ 3.2. S P X P
Thêm khoá m iớ
ắ
ắ ế S p x p tăng ả ặ ầ d n (ho c gi m d n)ầ
ầ Dòng đ u là tên ườ tr ng (ko s p x p)ế
ứ
ọ Ch n khoá ứ ấ th nh t ọ [Ch n khoá th hai]
ọ [Ch n khoá th ba]ứ
1/7/16
MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel
32
3.3. TÌM KI MẾ
ụ • 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ác b
c th c hi n:
ọ
ể ượ
ng
ề ọ – B1: Ch n mi n – B2: Vào Menu Data ch n bi u t
Filter
1/7/16
MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel
33
AUTOFILTER
ề
ả
ồ
ọ
ườ
• Ch n mi n CSDL g m c dòng tên tr
ng
ườ
ầ
ng có đ u
• Menu Data/Filter/AutoFilter, ô tên tr
ủ ộ
ả
ố
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:
ể ệ ạ
– All: đ hi n l
ọ ả i m i b n ghi
ị ớ
ấ – Top 10…: các giá tr l n nh t
ự ị
ề
– Custom…: t
ệ ọ đ nh đi u ki n l c
ị ủ ộ – Các giá tr c a c t
1/7/16
MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel
34
AUTOFILTER
ể ượ ọ ng • Ch n bi u t ả ở góc ph i bên ề ướ ủ i c a ô tiêu đ
d
• Number/Text
ế t ệ ọ
Filters Custom ể Filter đ thi ề ậ l p đi u ki n l c:
1/7/16
MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel
35
CÁC HÀM LIÊN QUAN Đ N Ế CSDL
DSUM(Database, Field, Criteria)
DMAX(Database, Field, Criteria)
DMIN(Database, Field, Criteria)
DCOUNT(Database, Field, Criteria)
DCOUNTA(Database, Field, Criteria)
ầ
ố
» G n gi ng hàm DCOUNT
DAVERAGE(Database, Field, Criteria)
1/7/16
MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel
36
ổ
ạ
3.4. T o các dòng t ng (Subtotal)
• Subtotal dùng đ t ng h p d li u theo nhóm trong
ợ ữ ệ ể ổ
danh sách
ữ ệ
ầ
ượ ắ
c s p
Vùng d li u ban đ u đã đ ứ ự ế Phòng ban. x p theo th t
1/7/16
MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel
37
ổ
ạ
3.4. T o các dòng t ng (Subtotal)
2
ể
ừ
ọ
Vào Menu Data ch n ọ Subtotal ấ (Có th click các nút phân c p ặ ể đ thu g n ho c bung ra t ng ấ c p subtotal)
1
3
1/7/16
MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel
38
4. Đ THỒ Ị
• Muc tiêu
̀
́
̣
• Chon đu ng kiêu đô thi
̀
̣ ̉ ̣
• Tao đô thi
̀
̣ ̣
• Thay đôi đô thi
̉ ̣
1/7/16
MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel
39
Ụ 4.1. M C TIÊU
ể
ồ ị
ủ ạ
ả
ấ ướ
ưở
ớ ồ ị ạ ả ng tăng tr
ệ ạ ớ
ắ
ộ • Đ th giúp trình bày quan đi m c a b n m t ể ữ cách nhanh chóng. V i đ th b n chuy n d li u trong b ng tính thành hình nh, và cho phép ỉ ng ch trong b n so sánh và th y h ch p m t.
1/7/16
MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel
40
4.2. CHỌN ĐÚNG KIỂU ĐỒ THỊ
ứ ỗ ồ ị ề
ồ ị ệ ố
ủ ử ế ế ườ
ệ ả ấ ả ự • M i đ th đ u ch a đ ng ý nghĩa bên trong c a nó – ườ ạ i t o đ th mu n g i đ n đó là thông đi p mà ng ể ế ặ ng i xem. Vì th đ c đi m đánh giá chính y u là ệ ề thông đi p ph i truy n đi hi u qu nh t.
• Các ki u đ th khác nhau có th truy n đi các thông
ồ ị ể
Tiền xe
Ti ền xe
Tiền xe
70000
70000
60000
60000
50000
50000
Đà Lạt
40000
40000
Nha Trang
Tiền xe
Tiền xe
30000
30000
Vũng tàu
20000
20000
P hú Quốc
10000
10000
Huế
0
0
Đà Lạt
Nha
Vũng tàu Phú Quốc
Huế
0
1
2
3
4
5
6
Trang
ủ ệ đi p khác nhau c a cùng 1 đ i t ề ể ữ ệ ố ượ ng d li u.
1/7/16
MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel
41
4.2. CHỌN ĐÚNG KIỂU ĐỒ THỊ
• Đ th c t (column)
ồ ị ộ
• Đ th c t là l a ch n m c đ nh c a wizard. Đ th c t
ồ ị ộ ồ ị ộ ự ủ ọ ị
ị ớ ể ợ ặ ự ế thích h p đ so sánh tr c ti p các giá tr v i nhau.
Diem chuan theo khu vuc
20
15
ĐIỂM CHUẨN K.VỰC1
10
i
m ể đ
5
ĐIỂM CHUẨN K.VỰC2
0
MÁY TÍNH
ĐIỆN TỬ XÂY DỰNG
HÓA
ngành
1/7/16
MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel
42
4.2. CHỌN ĐÚNG KIỂU ĐỒ THỊ
• Pie chart so sánh t ng ph n t
ầ ử ủ ổ ừ ể c a 1 t ng th
• Đ th bánh đ so sánh các ph n t
ồ ị ầ ử ể
ể ộ
Thượng hạng
ỏ ứ ưở ể ể ủ ừ ơ ấ ễ ng đ bi u di n c c u, t ể ớ ổ trong 1 t ng th v i ầ ử . tr ng
Thượng hạng, 80,000 đ
Thượng hạng, 100,000 đ
TEA
COFFEE
CACAO
Thượng hạng, 120,000 đ
ồ ị ố ượ nhau đ làm r m c đ đóng góp c a t ng ph n t ỷ ọ Đây là đ th lý t ủ c a 1 đ i t ng
1/7/16
MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel
43
4.2. CHỌN ĐÚNG KIỂU ĐỒ THỊ
• Line so sánh các giá tr qua th i gian
ờ ị
ồ ị ườ
• Đ th đ ng (Line) ể ợ ễ – phù h p bi u di n ộ ế ướ ng bi n đ ng xu h ư ờ theo th i gian nh ả ượ ng, doanh s n l ử ậ ợ i nhu n. S thu, và l ụ ồ ị d ng đ th Line khi ụ tr c hoành là th i gian.
ờ
1/7/16
MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel
44
4.2. CHỌN ĐÚNG KIỂU ĐỒ THỊ
ạ
• XY Scatter – m i ố ệ quan h 2 đ i ngượ l
• XY Scatter – phù h p ợ
ố ể ể ệ
ị ữ ệ ọ
•
đ th hi n m i ệ ữ quan h gi a các giá tr d li u khoa h c ố hay th ng kê.
ể ệ
ụ Th hi n 2 thang đo ồ ị trong 1 đ th qua 2 tr c X và Y
1/7/16
MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel
45
Ạ
Ồ Ị 4.3. T O Đ TH
ọ
ề
ữ ệ
ả
• B1: 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.
ồ ầ
ự
ệ
ể
ạ
ọ
• B2: Vào Menu Insert ch n lo i bi u đ c n th c hi n
ử ụ
ề
ấ ể ệ
ể ị
ể
ỉ
Chart Layout, Select Data ho cặ nh p vào ạ đ đ nh d ng các ki u th hi n
• B3: S d ng ố các vùng mu n đi u ch nh nh :ư
ị
ồ ị 1. Đ nh ki u đ th
1/7/16
46
ể MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel ữ ệ
ị
2. Đ nh d li u
ụ
ự
ề
ọ
ả
3. Các l a ch n: tiêu đ , các tr c, chú gi
i …
Ổ Ồ Ị 4.4. THAY Đ I Đ TH
ượ ạ
ớ ằ ồ ị ớ ị ươ
ồ ị Khi đ th đã đ ể 1. Chuy n đ th t
ể c t o, có th : i v trí m i b ng ph
ứ ng th c Drag
& Drop.
ổ 2. Thay đ i kích th ướ ồ ị c đ th
ồ ị ủ ề ổ ộ ả 3. Thay đ i các thu c tính c a đ th (tiêu đ , chú gi i,
…)
ầ ộ ồ ị 4. Thay đ i các thu c tính c a các thành ph n đ th
ổ ữ ỷ ệ ắ ề ụ l ủ các tr c, màu s c n n,…) (font ch , t
1/7/16
MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel
47
Ả
Ợ
Ổ
Ề
5. B NG T NG H P ĐA CHI U (PIVOT TABLE)
• Khái ni mệ ạ ậ
• T o l p pivot table
• Tiêu chí phân tích
ươ
ố
• Các ph
ng pháp th ng kê
ụ • Công c Group
1/7/16
MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel
48
5.1. KHÁI NI MỆ
̉
̀
ề
ợ
ớ
̉
ộ
ụ • Pivot Table là 1 công c cua Excel dùng ữ ệ tông h p va phân tích d li u v i nhi u ề ấ góc đ và nhi u c p khác nhau.
1/7/16
MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel
49
Ậ Ứ
Ụ
BÀI T P NG D NG
Đại lý Tỉnh
Mặt hàng Năm Doanh thu 2001 22,449,000 B HCM
B HCM 2002 26,739,000
A HCM A HCM 2002 82,091,000 2003 39,071,000
D An Giang 2005 51,881,000
C An Giang 2005 70,568,000
Điện tử Điện gia dụng Điện cơ Điện tử Điện gia dụng Điện gia dụng
2005 59,568,000 2004 24,546,000
F Hà Nội 2005 41,352,000
1/7/16
MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel
50
C An Giang Điện cơ Điện cơ F Hà Nội Điện gia dụng Điện gia
F Hà Nội 2005 41,373,000
dụng
Ậ Ứ
Ụ
BÀI T P NG D NG
ợ
• Tông h p:
ừ
ỉ
– Doanh thu t ng t nh
ừ – Doanh thu t ng nhóm hàng
ừ
̉
– Doanh thu t ng đai lý
ừ
– Doanh thu t ng năm
́
• Phân ti ch:
̀
̀
̀
ư
ư
̣
– Doanh thu t ng tinh theo t ng măt ha ng
́
̀
̀
̀
ư
ư
̉ ̣
– Doanh thu t ng đai ly theo t ng măt ha ng
̀
̀
̉ ư
ư
̣ ̣
– Doanh thu t ng năm cua t ng tinh
́
̀
ư
̉
– Doanh thu t ng đai ly theo năm
̣
1/7/16
MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel
51
́
̀
̀
ư
– Doanh thu t ng năm theo nho m ha ng
̀
̀
́
̀
́
́
ư
ư
– Chi tiê t Doanh thu t ng nho m ha ng cho t ng đai ly ,
́
̣
nho m theo tinh.
̉
Ậ
Ạ
5.2. T O L P PIVOT TABLE
q B
ị ỉ ồ ữ ệ c 1ướ : Ch đ nh ngu n d li u
•
ơ ở ữ ệ ầ ạ Bôi đen c s d li u c n t o
ể ượ ọ ng Menu Insert ch n bi u t PivotTable
1/7/16
MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel
52
Ậ
Ạ
5.2. T O L P PIVOT TABLE
B
ữ ệ c 2ướ : Ch đ nh d li u ị ỉ ơ ặ ị c 3ướ : Ch đ nh n i đ t ỉ
B PivotTable
1/7/16
MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel
53
Ậ
Ạ
5.2. T O L P PIVOT TABLE
̀
́
: Ke o va tha
• B
c 4́ươ
̉
• Tr
́ ̉ tông h p câ p cao
̀ ươ ng ́ nhâ t va o
ợ ̀ Report Filter (Năm) ́ ̣ ̉
• 1 hoăc 2 Tr ́ ơ thâ p h n va o ly )́
ươ ̀ ̉ ̣ ̀ ợ ng tông h p câ p Row (Tinh, Đai
• 1 hoăc 2 Tr
́ ươ ̣ ̀ ng phân ti ch
̀ ̣
• Tr
va o ̀ Column (Măt ha ng) ́ ̃ ̀ ư ̣ ̀ ươ ng d liêu phân ti ch va o
Values (Doanh thu)
1/7/16
MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel
54
5.3. TIÊU CHÍ PHÂN TÍCH
ấ ỳ
ể – Tiêu chí phân tích là b t k tiêu chí nào có th
ạ ữ ệ
ể
dùng đ phân lo i d li u....
́
ế
ấ
– Nê u gõ sai chính t
ư
ề
ạ
ả ư ấ , d d u space, thi u d u ớ ấ ắ s c, d u huy n; Pivot cũng coi nh 1 lo i m i và tách riêng ra.
ạ ữ ệ ầ
– Phân lo i d li u c n chính xác, không trùng
ơ ồ
ắ l p, không m h .
1/7/16
MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel
55
5.4. CÁC PHƯƠNG PHÁP THỐNG KÊ
́ • Thô ng kê
́
̃
ự
́ ơ
– Sô hoc sinh mô i l p d thi
̃
̣
́ ́ ơ – Điêm cao nhâ t cua mô i l p
́
̃
̉ ̉
́ ́ ơ – Điêm thâ p nhâ t cua mô i l p
̀
̃
̉ ̉
́ ơ – Điêm trung bi nh mô i l p
̉
1/7/16
MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel
56
5.4. CÁC PHƯƠNG PHÁP THỐNG KÊ
̃
́
́
́
ư
ơ
ả ớ
ể
• Ha y tao 1 Pivot table v i câ u tru c nh sau: Kéo th L p vào Rows, Tên HS và đi m vào Data.
̀
̀ • Vi tr
̣
MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel
1/7/16
57
̀
́
̀ ươ
̀ ươ ng tên HS la text, nên Excel t ́ ́ ơ ư công th c la count: 2 l p co 6 HS va 1 l p co 5 HS. Tr
́ ự ga n ́ ́ ̀ ơ ́ ̀ ng Điêm la sô (number) nên
́
́
ự
ư
Excel t
ga n công th c Sum.
̉
5.4. CÁC PHƯƠNG PHÁP THỐNG KÊ
ả
ể
ộ • Nh n chu t ph i vào tr
ấ ọ
ườ ng “Sum of Đi m”, ọ ạ i là Max thay vìSum, ớ ể
ch n field setting. Ch n l ấ ử s a tên field thành Đi m l n nh t
1/7/16
MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel
58
5.4. CÁC PHƯƠNG PHÁP THỐNG KÊ
• Kéo th Tr ầ
ả ườ ầ ữ ộ ầ ể ng đi m vào 2 l n n a, m t l n là Min và
ẽ 1 l n là Average ta s có:
́ ̀ ́ ̀ ́ ơ ̀ ươ ng “L p”, ta ke o tr ̀ ươ ng Môn va o
• Nê u thay vi tr ́ Row, ta se co :
̃
1/7/16
MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel
59
5.5. CÔNG CỤ GROUP
s v i d li u
• Group trường loại s : ố Gi
ư
ả ử ớ ữ ệ ể
ng Năm
ư
ặ
ườ Doanh thu nh trên ta có th Group tr thành nhóm 2 năm, ho c 3 năm nh sau:
ả
ấ
ườ
ộ • B m chu t ph i vào tr
ng Năm trên Pivot
Table, Vào Menu Data Group:
1/7/16
MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel
60
5.5. CÔNG CỤ GROUP
Date
ậ ứ ụ Bài t p ng d ng:
• Group tr
ườ ạ ng lo i
Jan 2, 2013 65 Jan 3, 2013 23 Jan 4, 2013 67 Mar 5, 2013 64 Mar 6, 2013 24 May 7, 2013 76 Jun 5, 2013 42 Jul 6, 2013 65 Jul 7, 2013 24 Aug 11, 2013 56 Aug 12, 2013 34 Oct 13, 2013 24 Oct 14, 2013 77 Dec 15, 2013 24
67 57 43 56 34 76 42 67 64 76 42 64 22 55
42 64 24 64 67 57 43 57 86 67 67 56 34 43
ngày tháng
1/7/16
MaMH: 701005 - Ch.2: Hàm và biểu thức trong Excel
USA Japan UK VN 55 67 57 31 42 64 24 76 42 42 64 24 76 42 61
ứ
ừ
5.6. Các ng d ng khác t
Pivot
ụ Table
ự
ệ
• Pivot Table còn đ
ượ ử ụ ụ
c s d ng trong vi c th c ứ
ệ
ấ
ư hi n các thao tác và ng d ng cao c p khác nh :
̀
̀
ấ ữ ̣ ừ
– L y d liêu t
nhiê u nguô n
́
̀
́
́
–
́ Ư ng dung PivotTable trong ba o ca o ta i chi nh
̣
