Excel Bài Giảng toàn tập IV

Chia sẻ: Roni Simon | Ngày: | Loại File: PDF | Số trang:12

Thêm vào BST

Báo xấu

411
lượt xem 252
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Tham khảo tài liệu 'excel bài giảng toàn tập iv', công nghệ thông tin, tin học văn phòng phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Excel Bài Giảng toàn tập IV

Microsoft Excel HÀM TRONG B NG TÍNH EXCEL STT Bài h c S ti t 01 Khái ni m v hàm 02 Khái ni m v tham chi u 03 Các hàm cơ b n 1
Bài 3: Các hàm cơ b n I Hàm Th ng kê: 1- Count(Value1,Value2…): Hàm tr v s ph n t ki u s 2- Counta(Value1,Value2…): Hàm tr v s ph n t khác tr ng A B C D E F 1 H tên ði m Tóan ði m Hóa ði m Văn COUNT COUNTA 2 Tú 8 9 V ng =Count(B2:D2) = Counta(B2:D2) 3 H 10 7 =Count(B3:D3) = Counta(B3:D3) 4 Thanh 7 5 =Count(B4:D4) = Counta(B4:D4) A B C D E F 1 H tên ði m Tóan ði m Hóa ði m Văn COUNT COUNTA 2 Tú 8 9 V ng 2 3 3 H 10 7 2 2 4 Thanh 7 5 2 2 2
Bài 3: Các hàm cơ b n I Hàm Th ng kê: 3- Max(Number1, Number2…): Hàm tr v s l n nh t c a các number 4- Min(Number1, Number2…): Hàm tr v s nh nh t c a các number 5- Average(Number1, Number2…): Hàm tr v trung bình c ng c a các number A B C D E F G 1 H tên Tóan Hóa Văn Max Min Average 2 Tú 8 9 V ng =Max(B2:D2) =Min(B2:D2) = Average(B2:D2) 3 H 10 7 =Max(B3:D3) =Min(B3:D3) = Average(B3:D3) 4 Thanh 7 5 =Max(B4:D4) =Min(B4:D4) = Average(B4:D4) A B C D E F G 1 H tên Tóan Hóa Văn Max Min Average 2 Tú 8 9 V ng 9 8 8.5 3 H 10 7 10 7 8.5 4 Thanh 7 5 7 5 6 3
Bài 3: Các hàm cơ b n I Hàm Th ng kê: 6- Sum(Number1, Number2…): Hàm tr v T ng c a các number E A B C D E 1 1 Sum H tên Tóan Hóa Văn Sum 2 2 17 Tú 8 9 V ng =Sum(B2:D2) 3 3 17 H 10 7 =Sum(B3:D3) 4
Bài 3: Các hàm cơ b n II.Hàm Th ng Kê Theo ði u ki n: 2- COUNTIF(Range,Criteria): Hàm tr v giá tr ñ m nh ng ph n t c a vùng Range th a mãn ñi u ki n c a Criteria Trong ñó ði u ki n là m t bi u th c s ho c chu i, ñư c ñ t trong d u “ “. N u ñi u ki n so sánh là toán t b ng thì không c n ghi toán t . Lưu ý: n u ñi u ki n 2 c t khác nhau, không th dùng hàm Countif. Ví d : ð m các m t hàng có s lư ng l n hơn 15 A B C D 1 M.Hàng S.Lư ng ð.Giá Các m t hàng có s lư ng l n hơn 15 2 B001 27 10 =COUNTIF(B2:B8,”>15”) 3 B002 10 57 4 A001 12 40 ð m các m t hàng có ký t b t ñ u b ng ch B 5 B001 16 50 =COUNTIF(A2:A8,”B*”) 6 A002 25 20 7 B002 26 40 8 B001 15 50 5 Trung Tâm Tin H c Trư ng ðHKHTN
Bài 3: Các hàm cơ b n IIHàm Th ng Kê Theo ði u ki n: 2- SUMIF(Range, Criteria, Sum_Range): Hàm tr v giá tr t ng c a nh ng ph n t ñư c ch n c a vùng Sum_Range, nh ng ph n t này ñư c ch n ng v i nh ng dòng c a vùng Range có giá tr th a mãn ñi u ki n c a Criteria Lưu ý: n u ñi u ki n 2 c t khác nhau, không th dùng hàm SumIF. Ví d : T ng ñơn giá c a các m t hàng có ký t là B A B C D 1 M.Hàng S.Lư ng ð.Giá T ng ðơn giá m t hàng có ký t là B 2 B001 27 10 =SUMIF(A2:A8,"B*",C2:C8) 3 B002 10 57 4 A001 12 40 5 B001 16 50 6 A002 25 20 7 B002 26 40 8 B001 15 50 6
Bài 3: Các hàm cơ b n VII Hàm chuy n ki u d li u: 1- TEXT(Value, Format_Text): Hàm ñ i s thành chu i 2- Value(Text): Hàm ñ i m t chu i d ng s thành s =Mid(A2,3,3)*1 A B 1 1234.56 =Text(A1,”#,##0.00”) 2 AB123 =Value(Mid(A2,3,3)) A B 1 1234.56 1,234.56 2 AB123 123 7
Bài 3: Các hàm cơ b n III Hàm tìm ki m 1- VLOOKUP(Lookup_val, Table, Col [,FALSE]): Vertical Look Up hàm dò tìm theo chi u d c Tìm giá tr trong b ng dò c t th 1, n u tìm th y s tr v dòng giá tr tương ng trong b ng dò c t th N tùy theo cách dò. Trong ñó : Lookup_val: là giá tr ñư c ñem ra dò. Table: là m t vùng có ñ a ch tuy t ñ i, c t th nh t bên trái ch a các giá tr dò tìm, các c t còn l i ch a các giá tr tương ng ñ tham chi u. Col: s th t c a c t có ch a giá tr tương ng c a giá tr dò tìm (tính t c t th nh t bên trái c a b ng qua). Col ph i là m t con s >1. N u tham chi u ñ n m t c t bên ngoài ngoài ph m vi c a b ng, hàm s cho tr #REF (Vd : b ng dò có 3 c t, khai báo ñ n c t th 4…) Cách dò: 0|False: dò tìm chính xác: c t ñ u tiên bên trái c a b ng dò không c n ph i x p theo th t tăng d n và n u c t này không ch a giá tr dò tìm, hàm s tr v tr #N/A (value Not Available). 1|True: dò tìm g n ñúng : c t ñ u tiên bên trái c a b ng dò ph i x p theo th t tăng d n (VD: -2, -1, 0, 1, 2, a - z, False, True…)và n u c t này không ch a giá tr dò tìm, hàm s dò theo giá tr l n nh t trong s nh ng giá tr g n b ng gía tr dò tìm. GT m c ñ nh c a cách dò là 1. 8
Bài 3: Các hàm cơ b n III Hàm tìm ki m 1- VLOOKUP(Lookup_val, Table, Col [,FALSE]): A B C 1 Mã nhân viên Tên ðơn v (D a vào ký t ñ u c a Mã tra trong b ng DMðV) H Tên 2 A001 T Minh H i =VLOOKUP(LEFT(A2),$A$11:$B$13,2,0) 3 B001 Ph m Thái 4 C001 Nguy n Bi u 5 C002 Nguy n Văn 6 A002 Lê Sơn 7 B002 Phan Ng c Liên 8 9 DANH M C ðƠN V 10 ðơn v (C t 1) Tên ðơn v (C t 2) 11 A Ban Giám ð c 12 B Phòng T Ch c 13 C Phòng Tài v 9
Bài 3: Các hàm cơ b n III Hàm tìm ki m 2- HLOOKUP(Lookup_val, Table, Row [,FALSE]): Horizontal Look Up hàm dò tìm theo chi u ngang Tương t như hàm Vlookup, nhưng hàm Hlookup dò tìm dòng trên cùng và tham chi u s li u các dòng phía dư i. A B C D E 1 Mã nhân viên Tên ðơn v (D a vào ký t ñ u c a Mã tra trong b ng DMðV) H Tên 2 A001 T Minh H i =HLOOKUP(LEFT(A2),$A$10:$D$11,2,0) 3 B001 Ph m Thái 4 C001 Nguy n Bi u 5 C002 Nguy n Văn 6 A002 Lê Sơn 7 B002 Phan Ng c Liên 8 9 DANH M C ðƠN V 10 ðv (Dòng 1) A B C 11 Tên ðơn v Ban Giám ð c Phòng T Ch c Phòng Tài v 10
Bài 3: Các hàm cơ b n III Hàm tìm ki m 3- Các l i thư ng g p trong khi s d ng Hàm tìm ki m A B C 1 Mã nhân viên Tên ðơn v (D a vào ký t ñ u c a Mã tra trong b ng DMðV) H Tên 2 A001 T Minh H i =VLOOKUP(LEFT(A2),$A$11:$B$13,2,0) 3 B001 Ph m Thái =VLOOKUP(LEFT(A2),$A$11:$B$13,3,0) 4 C001 Nguy n Bi u #REF : Giá tr c t tr v 5 C002 Nguy n Văn vư t quá ph m vi c a 6 A002 Lê Sơn b ng dò 7 B002 Phan Ng c Liên 8 #N/A (value Not Available) – Không tìm th y giá tr dò trong b ng dò E002 9 DANH M C ðƠN V 10 ðơn v (C t 1) Tên ðơn v (C t 2) 11 A Ban Giám ð c 12 B Phòng T Ch c 13 C Phòng Tài v 11
Bài 3: Các hàm cơ b n III Hàm tìm ki m 4- Ví d dò tìm g n ñúng Xeáp loaïi HOÏ&TEÂN Ñieåm Xeáp loaïi Yeáu Huøng 4 =VLOOKUP(LEFT(A2),$A$11:$B$14,2,1) TB Lan 6 Khaù Dieãm 8 Gioûi Hoaø 10 TB Thaønh 5 Ñieåm moác Loaïi 0 Yeáu 5 TB 7 Khaù 9 Gioûi Lưu ý: C t ch a giá tr ph i ñư c s p tăng d n 12 Bài T p