Bài giảng Tin học - Bài 5: Các hàm trong excel

Bài 5 CÁC HÀM TRONG EXCEL Phần 1 : Sử dụng Cơng Thức và hàmPhần 2 : Các Hàm cơ bản Thường DùngPhần 3 : Các Hàm tính tốn nâng cao1 Phần 1: SỬ DỤNG CÔNG THỨC VÀ HÀM1. Sử dụng Công thức Một công thức bao gồm: - Một hoặc nhiều địa chỉ ô, hoặc giá trị - Các toán tử - Các hàm 2Mỗi công thức bắt đầu bởi một dấu = hoặc dấu + Khi thực hiện tính giá trị trong công thức, thứ tự ưu tiên sẽ là: - Hàm tính trước, toán tử tính sau - Phần trong dấu ngoặc tính trước, phần ngoài dấu ngoặc tính sau - Toán tử ưu tiên tính trước đến các toán tử có mức ưu tiên kế tiếp - Bên trái tính trước, bên phải tính sau. 32. Nhập công thức từ bàn phím Thực hiện các bước sau: - Di chuyển ô hiện hành đến vị trí cần nhập công thức - Gõ dấu = - Nhập toán tử cần thực hiện - Nhấn Enter 43. Sửa đổi công thức Thực hiện các bước sau: - Di chuyển ô hiện hành đến ô có chức công thức cần sửa đổi - Nhấn phím F2, hoặc nhấp đúp con trỏ chuột - Sửa đổi nội dung của công thức - Nhấn phím Enter 54. Sao chép công thức Thực hiện các bước sau: - Chọn ô có công thức muốn sao chép - Rồi thực hiện các lệnh sao chép - Chọn những ô muốn sao chép công thức - Rồi thực hiện các lệnh dán. 65. Địa chỉ tương đối, địa chỉ tuyệt đối Tên của mỗi ô hay vùng được gọi là địa chỉ của ô hay vùng đó. Có 02 loại địa chỉ :- Địa chỉ tương đối - Địa chỉ tuyệt đối 7	a. Địa chỉ tương đối: Địa chỉ bình thường của ô hay vùng khi nhập vào trong công thức được gọi là địa chỉ tương đối. Ví dụ: = B4 + F4 + C4 Khi sao chép công thức các địa chỉ tương đối trong công thức sẽ thay đổi tương ứng ở nơi được chép đến. 8b. Địa chỉ tuyệt đối: Các địa chỉ trong công thức đứng sau dấu $ sẽ trở thành địa chỉ tuyệt đối. Có thể đặt địa chỉ tuyệt đối theo dòng và theo cột. Khi sao chép công thức, các địa chỉ tuyệt đối trong công thức sẽ không thay đổi. Nhấn phím F4 để thực hiện việc chuyển đổi giữa hai địa chỉ. 96. Sử dụng hàm a. Giới thiệu Excel có hơn 300 hàm và được chia thành 10 nhóm:         - Hàm Ngày và Giờ	 - Hàm thống kê         - Hàm Toán học	 - Hàm Tài chính - Hàm Dò tìm và tham chiếu	 - Hàm Cơ sở dữ liệu    - Hàm Xử lý chuổi ký tự	 - Hàm kỹ thuật - Hàm Logic	 - Hàm Thông tin 10b. Cú pháp tổng quát của một hàm = (đối số1, đối số 2, đối số 3,, đối số n) Mỗi hàm bao gồm ba thành phần: Dấu = : Cho biết sau đó là một hàm Tên hàm: Sử dụng các tên gọi theo quy ước của Excel Đối số: Là các giá trị số, chuỗi, địa chỉ ô, tên vùng, công thức, hoặc môït hàm khác 11c. Nhập hàm vào bảng tính Chọn một trong hai cách sau: - Nhập hàm từ bàn phím, gõ tên hàm - Nhấp chuột chọn Insert  Function 12 Phần 2: CÁC HÀM CƠ BẢNI. Hàm Toán học1. Hàm SUM Cho tổng giá trị của một vùngCú pháp:	= SUM(Vùng giá trị) Tính tổng giá trị các ô trong vùng Ví dụ: = SUM(D3:D9) (cho giá trị số từ D3 đến D9)	= SUM(15,15,20) = 50 Vùng giá trị có thể là các giá trị số, địa chỉ các ô chứa trị số, khối ô 13 Phần 2: CÁC HÀM CƠ BẢN2. Hàm AVERAGE Cho giá trị trung bình cộng trong vùngCú pháp:	= AVERAGE (vùng giá trị) Vùng giá trị có thể là các giá trị số, địa chỉ các ô chứa trị số, khối ô Ví dụ: 	= AVERAGE (F4:F12)  (cho giá trị trung bình từ F4 đến F12) 	= AVERAGE(3,6,9) = 6 14 Phần 2: CÁC HÀM CƠ BẢN3. Hàm MAX, MIN Hàm Max cho giá trị cao nhất trong vùng. Hàm Min cho giá trị nhỏ nhất trong vùng. Cú pháp:	= MAX (vùng giá trị)	= MIN (Vùng giá trị) Vùng giá trị có thể là các giá trị số, địa chỉ các ô chứa trị số, khối ô 15 Phần 2: CÁC HÀM CƠ BẢN4. Hàm COUNT Đếm số ô chứa giá trị số trong một phạm vi Cú pháp:	= COUNT(Vùng giá trị) Phạm vi có thể là địa chỉ ô hoặc khối ô 16 Phần 2: CÁC HÀM CƠ BẢN5. Hàm COUNTA Đếm số ô có giá trị không rỗng trong vùng Cú pháp:	= COUNTA(Vùng) Phạm vi có thể là địa chỉ ô hoặc khối ô 17 Phần 2: CÁC HÀM CƠ BẢN6. Hàm INT Cho giá trị phần nguyên của phép chia M cho N Cú pháp:	= INT (M/N) M, N có thể là số, địa chỉ ô chứa trị số Ví dụ:	 = INT(4.8) = 4	 = INT(C2) = cho giá trị phần nguyên của số trong ô C2 18 Phần 2: CÁC HÀM CƠ BẢN7. Hàm MOD Cho trị số dư của phép toán chia M cho N Cú pháp:	= MOD (M, N) M, N có thể là số, địa chỉ ô chứa trị số Ví dụ: = MOD (9,2) = 1 ; = MOD(25,5) = 0 19 Phần 2: CÁC HÀM CƠ BẢN8. Hàm ROUND Tính tròn trị số đến số chỉ định Cú pháp:	= ROUND (Biểu thức số , cách làm tròn n) - Biểu thức số có thể là trị số, địa chỉ chứa ô trị số - Cách làm tròn: 20 n =5 n > 0 : Làm tròn phần thập phân 219. Hàm RANK Tìm thứ bậc của x trong vùng dò theo cách xếp thứ bậc Cú pháp:	= RANK (x, Vùng dò, Cách xếp thứ bậc) Cách xếp thứ tự: - Nếu là 0 hoặc không nêu ra thì thứ bậc được tính theo giá trị số giảm dần - Nếu là 1 thì thứ bậc được tính theo giá trị tăng dần 22II. Nhóm hàm chuỗi 1. Hàm LEFT Trích ký tự ở bên trái chuỗi dữ liệu hoặc trong tọa độ của ô chứa dữ liệu. Cú pháp: = LEFT (Chuỗi, Số ký tự muốn trích) 2. Hàm RIGHTTrích ký tự ở bên phải chuỗi dữ liệu Cú pháp: = RIGHT (Chuỗi, Số ký tự muốn trích) 233. Hàm MID Trích ký tự từ số bắt đầu đến số kết thúc. Cú pháp: = MID (Chuỗi, Số bắt đầu, Số ký tự muốn trích) Trong đó: Chuỗi: Dữ liệu số, nhãn, hoặc địa chỉ ô chứa dữ liệu Số bắt đầu: Số thứ tự của ký tự bắt đầu trích trên chuỗi Số ký tự muốn trích: Số ký tự cần trích trên chuỗi (>0) 24Ví dụ: = LEFT (“Minh Trang”,4) = Minh= LEFT (“Minh Trang”,12) = Minh Trang= LEFT (“356”,1) = 3= RIGHT(“Minh Trang”,5) = Trang= RIGHT (“Minh Trang”,16) = Minh Trang= MID (“68932”,3,2) = 93 25Chú ý: - Chuỗi ký tự ghi trong hàm phải được đặt trong cặp dấu nháy kép - Nếu số ký tự cần trích lớn hơn chiều dài của chuỗi dữ liệu thì Excel cho kết quả đúng bằng chiều dài của chuỗi. - Chiều dài của chuỗi dữ liệu được tính cả khoảng trống trong ô 264. Hàm LEN Cho biết độ dài của chuỗi ký tự Cú pháp:	= LEN(chuỗi) Chuỗi dữ liệu: Dữ liệu loại số, nhãn hoặc địa chỉ ô chứa dữ liệu Ví dụ : = LEN (“Xin chào bạn.”) = 13 = LEN (“Thu”) = 3 = LEN (A6 &B10) = Số ký tự trong 2 ô A6 vàB10 275. Hàm TRIM Cắt bỏ khoảng trắng ở đầu và cuối chuỗi Cú pháp:	= TRIM (chuỗi) 6. Hàm TEXT Biến đổi số thành chuỗi Cú pháp:	 = TEXT (Giá trị số, Cách định dạng ) Ví dụ:	 = TEXT (34567,”dd-mm-yy”) = 21 –8 –94 287. Hàm VALUE Đổi chuỗi chứa số ra trị số. Cú pháp: = VALUE (Chuỗi) Ví dụ:	 = VALUE (“123”) = 123 291. Hàm AND Trả về trị đúng nếu mọi biểu thức điều kiện đều đúngTrả về trị sai nếu có một biểu thức điều kiện sai.Cú pháp: = AND (Biểu thức điều kiện 1, Biểu thức điều kiện 2,) III. Hàm Lôgic302. Hàm OR - Trả về trị đúng nếu có ít nhất một biểu thức điều kiện là đúng Cú pháp: = OR (Biểu thức điều kiện 1, Biểu thức điều kiện 2, ) - Trả về trị sai nếu tất cả các biểu thức điều kiện đều sai. 311. Hàm IF Cú pháp: = IF (Biểu thức điều kiện, Biểu thức 1, Biểu thức 2) Trong đó: Biểu thức điều kiện: Biểu thức có giá trị đúng hoặc sai - Biểu thức điều kiện đúng hàm cho kết quả là: Biểu thức 1- Biểu thức điều kiện sai hàm cho kết quả là: Biểu thức 2Nếu: 32Ví dụ: = IF (ĐTB>=5,”Đậu”, “Rớt”) = Đậu (Nếu ĐTB>=5) 	 = Rớt (Nếu ĐTB<5) Chú ý: - Một biểu thức có thể bao gồm: Các trị số, dữ liệu, Các địa chỉ ô hoặc khối ô cần tham chiếu Các công thức, hàm 33IV. Hàm ngày tháng 1. Hàm DATE Cho kết quả là trị ứng với ngày, tháng, năm Cú pháp: = DATE(năm, tháng, ngày) Ví dụ: = DATE (99,08,15) = 15/08/99  (ô được định dạng DD/MM/YY) 342. Hàm DAY Trả về số thứ tự của ngày trong tháng Cú pháp: = DAY(Trị số tuần tự của dữ liệu ngày tháng) hoặc = DAY(Chuỗi ngày tháng năm) Ví dụ:	= DAY(“10-DEC-96”) = 10 353. Hàm TODAY Cho kết quả ngày hiện hành Cú pháp:	 = TODAY () Ví dụ:	 = TODAY () = 26/03/04 364. Hàm MONTH Cho dữ liệu là tháng trong dữ liệu ngày tháng Cú pháp: = MONTH(Trị số tuần tự của dữ liệu ngày tháng) Hoặc = MONTH (Chuỗi ngày tháng năm) Ví dụ: 	 = MONTH (“10 – DEC - 96”) = 12 375. Hàm YEAR Cho giá trị là năm trong dữ liệu dạng ngày tháng năm. Cú pháp: = YEAR(Trị số tuần tự của dữ liệu ngày tháng) hoặc = YEAR (Chuỗi ngày tháng năm) Ví dụ:	= YEAR (“10 – DEC - 96”) = 1996 38I. Nhóm hàm dò tìm và tham chiếu1. Hàm VLOOKUP (dò tìm và tham chiếu theo cột) 	Thực hiện việc dò tìm giá trị x ở vùng dò để lấy ra giá trị tương ứng của x trên cột dò. Cú pháp: = VLOOKUP (Trị dò x, Vùng dò, Cột dị, Cách dò ) Phần 3: CÁC HÀM NÂNG CAO39Trong đó: - Trị dò x: là giá trị mang đi dò tìm. X có thể là môït chuỗi, địa chỉ ô chứa dữ liệu, biểu thức có kết quả là một giá trị hay một chuỗi. - Vùng dò: Phạm vi khối dữ liệu cần khai thác gồm hai phần Cột đầu tiên: là cột chứa dữ liệu cần  so sánh với x Các cột kế cận bên phải: là những cột chứa dữ liệu cần khai thác 40- Cách dò: Cĩ 2 cách (bằng 0 hoặc bằng 1)	Cách dò 0: Dò tìm x và giá trị tương ứng trong vùng dò phải chính xác tuyệt đối. Cách dò 1: Dò tìm x và giá trị tương ứng trong vùng dò xấp xỉ bằng (tương đối). - Cột dò: Là số thứ tự của cột trong vùng dò chứa giá trị tương ứng với trị dò x41Ví dụ: Cho khối khai thác sau ABCD1552844321032674531542182242083764742Với hàm VLOOKUP sau, sẽ có kết quả tương ứng: = VLOOKUP(17, A1:D4,3) = 18	= VLOOKUP(10, A1:D4,0) = #VALUE!	= VLOOKUP(50, A1:D4,3) = 76= VLOOKUP(“17”, A1:D4,2) = #N/A 	(Không có dữ liệu chuỗi trong khối khai thác)	= VLOOKUP(17, A1:D4,8) = #REF!(Số cột khai thác lớn hơn số cột trong khối  khai thác) 432. Hàm HLOOKUP Cú pháp: = HLOOKUP (Trị dò x, Vùng dò, Hàng dị, Cách dò ) Thực hiện việc dò tìm giá trị x ở vùng dò để lấy ra giá trị tương ứng của x trên hàng dò. Trong đó: - Trị dò x: Có thể là môït chuỗi, địa chỉ ô chứa dữ liệu, biểu thức có kết quả là một giá trị hay một chuỗi. 44- Vùng dò: Phạm vi khối dữ liệu cần khai thác gồm hai phần Hàng đầu tiên: Là hàng chứa dữ liệu cần so sánh với x Các hàng kế cận bên dưới: Những hàng chứa dữ liệu cần khai thác Cách dò: Cĩ 2 cách (bằng 0 hoặc bằng 1)Tương tự như cách dị ở hàm VLOOKUP - Hàng dò: Là số thứ tự của hàng trong vùng dò chứa giá trị tương ứng với trị dò x45Ví dụ: Cho khối khai thác sau ABCD1552844321032674531542182242083764746Với hàm HLOOKUP sau, sẽ có kết quả tương ứng: = HLOOKUP(17, A1:D4,3) = 18	= HLOOKUP(10, A1:D4,0) = #VALUE!	= HLOOKUP(50, A1:D4,3) = 76 = HLOOKUP(“17”, A1:D4,2) = #N/A 	 (Không có dữ liệu chuỗi trong khối khai thác)	= HLOOKUP(17, A1:D4,8) = #REF!	 (Số hàng khai thác lớn hơn số hàng trong khối khai thác) 471. Hàm SUMIF Tính tổng của những ô trong vùng lấy tổng số, mà những ô cùng hàng tương ứng trong vùng dò thỏa cho điều kiện. Cú pháp: = SUMIF (Vùng dò, điều kiện, vùng lấy tổng số ) II. Hàm tính toán có điều kiện482. Hàm COUNTIF Đếm số ô trong vùng thỏa điều kiện Cú pháp: = COUNTIF (Vùng, điều kiện) 491. Hàm INDEX Cho giá trị của ô trong vùng dò lệch đi từ góc trên trái xuống x hàng, và qua phải y hàng Cú pháp:	 = INDEX (Vùng dò, x, y) III. Hàm định vị502. Hàm MATCH Cho giá trị tương đối của ô trong vùng dò có giá trị hợp với trị dò x theo cách dò được chỉ ra. Cú pháp: = MATCH (Trị dò x, Vùng dò, Cách dò) Vùng dò: Gồm các ô liên tục trong một cột hoặc hàng. 51