Nội dung

23/06/2011 Chương 4 HÀM Excel có rất nhiều hàm số sử dụng trong các lĩnh vực: toán học, thống kê, tài chính, logic, xử lý chuỗi ký tự, ngày tháng … Dạng thức tổng quát của hàm: =(Danh sách đối số) Danh sách đối số: có thể là các trị số, dãy các ô, địa chỉ ô, tên vùng, công thức, tên hàm. TRONG EXCEL Ví dụ: =SUM(D7; D13:D14; 10; 2+D16) Trong hàm có xử lý các hằng ký tự hoặc hằng xâu ký tự thì chúng phải được bao trong cặp dấu “ ”. Các hàm số có thể lồng nhau. Ví dụ: =IF(AND(A2=10; A3>=8);“G”;IF(A2<7;“TB”;“K”)) 2.1. HÀM SỐ HỌC 1. Hàm ABS Công dụng: Lấy trị tuyệt đối của một số. Công thức: number là số muốn lấy trị tuyệt đối. Ví dụ Công thức Kết quả =ABS(5-7) 2 2. Hàm SQRT Công dụng: Tính căn bậc 2 của một số SQRT viết của từ SQUARE ROOT: Căn bậc 2. Công thức: number số thực bất kỳ. Nếu number là số âm, hàm trả về lỗi #NUM!. Ví dụ Công thức Kết quả =SQRT(100) Bùi Thành Khoa Có thể nhập hàm số bằng cách ấn nút Paste Function fx trên Toolbar, rồi theo hướng dẫn từng bước. Chú ý: Hàm phải bắt đầu bởi dấu bằng (=), tên hàm không phân biệt chữ thường và chữ hoa. Đối số phải đặt trong ngoặc đơn ( ), giữa các đối số phân cách nhau bởi dấu chấm phẩy (hoặc dấu phẩy). 3. Hàm INT Công dụng: Làm tròn một số xuống số nguyên gần nhất. Công thức: number là số muốn làm tròn. Ví dụ Công thức =INT(1.5) =INT(-1.5) Kết quả 1 -2 4. Hàm PI Công dụng: Trả về số pi chính xác đến 15 số: 3.141592653589790 Công thức: Sử dụng hàm PI() trong các công thức lượng giác. 10 1 23/06/2011 5. Hàm MOD Công dụng: Lấy phần dư sau khi chia một số cho số chia. Công thức: number số bị chia divisor số chia. Lưu ý! Ø Nếu divisor bằng zero, hàm trả về lỗi #DIV/0! Ø Hàm Mod có thể biểu diễn qua hàm Int theo công thức: MOD(n; d) = n - d*INT(n/d) Ví dụ 6. Hàm ROUND Công dụng: Làm tròn một số đến phần ký số do bạn đưa ra. Công thức: number: số muốn làm tròn. num_digits: số ký số muốn làm tròn num_digits >0 Giải thích làm tròn về bên phải cột thập phân =0 <0 làm tròn đến số nguyên gần nhất làm tròn về bên trái cột thập phân Ví dụ Công thức Kết quả 2.2 Công thức =MOD(10;3) Kết quả 1 =ROUND(2.15; 1) =ROUND(2.149; 1) 2.1 =MOD(8;2) 0 =ROUND(21.5; -1) 20 (làm tròn đến hàng đơn vị) 7. Hàm SUM Công dụng: Tính tổng các đối số. Công thức: argument1, argument2,… có thể là các hằng, địa chỉ ô, miền. Ví dụ: Kết quả Công thức =SUM(3; 2) 5 21 (Giá trị text được chuyển =SUM("5“; 15; TRUE) sang số, giá trị logical TRUE được chuyển sang số 1) =SUM(A2:A5) =SUM(A2:A4;15) 40 55 8. Hàm SUMPRODUCT Công dụng: Nhân các phần tử tương ứng trong các mảng với nhau và trả về tổng của chúng. Công thức: array1, array2,.... có thể có từ 2 đến 30 mảng cùng kích thước. Lưu ý! Nếu các mảng giá trị không cùng kích thước hàm sẽ trả về lỗi #VALUE! Một phần tử bất kỳ trong mảng không phải là số thì coi là zero. Ví dụ: sumproduct.xls Bùi Thành Khoa 2 23/06/2011 9. Hàm AVERAGE Công dụng: Tính trung bình cộng của các đối số. Công thức: number1, number2 có thể có từ 1 đến 30 đối số cần tính trung bình cộng. 10. Hàm MAX Công dụng: Trả về giá trị lớn nhất của các giá trị kể cả kiểu số, chữ, logic. Công thức: value1,value2,... có thể có từ 1 đến 30 giá trị muốn tìm giá trị lớn nhất. Lưu ý! Các đối số có thể là số, tên, mảng hoặc vùng tham chiếu. Các thành phần của vùng tham chiếu, mảng giá trị nếu là kiểu chuỗi thì chuyển thành 0. Nếu đối số là giá trị logic TRUE thì được hiểu là 1, FALSE hiểu là 0. Ví dụ: Công thức =MAX(-2;-5;0;TRUE) Kết quả 1 (TRUE chuyển thành 1) =AVERAGE(5;10;TRUE;FALSE) cho giá trị 4. 11. Hàm MIN Công dụng: Trả về giá trị nhỏ nhất của các giá trị kể cả kiểu số, chữ, logic. Công thức: value1,value2,... có thể có từ 1 đến 30 giá trị muốn tìm giá trị lớn nhất. Lưu ý! Nếu các đối số là giá trị logic thì TRUE được hiểu là 1, FALSE hiểu là 0. Ví dụ: Công thức =MIN(21;5;0.5;TRUE;FALSE) Bùi Thành Khoa Lưu ý! Nếu các đối số là giá trị logic thì TRUE được hiểu là 1, FALSE hiểu là 0. Ví dụ: Kết quả 0 (FALSE, chuyển thành 0) Ví dụ: Tính thuế thu nhập cá nhân. thue-thu-nhap.xls Ghi chú: Trong Excel có sẵn tính năng tính toán nhanh như sau: Trước tiên đánh dấu vùng cần tính toán rồi right click vào thanh trạng thái của Excel sẽ hiện menu có các mục chọn là các giá trị cần tính: None: huỷ giá trị đã tính. Average: tính trị trung bình. Count: đếm số ô. Count Nums: chỉ đếm số ô chứa giá trị số. Max: tìm giá trị lớn nhất. Min: tìm giá trị nhỏ nhất. Sum: tính tổng các giá trị. Tiếp theo chỉ cần click một mục tương ứng thì kết quả sẽ được hiển thị ngay trên thanh trạng thái. 3 23/06/2011 2.2. HÀM ĐIỀU KIỆN 1. Hàm AND Công dụng: Trả về kết quả TRUE nếu tất cả điều kiện đều TRUE, trả về FALSE nếu một trong các điều kiện là FALSE. Công thức: logical_1, logical_2 là các điều kiện cần kiểm tra. Ví dụ: Công thức Kết quả logical_1, logical_2 là các điều kiện cần kiểm tra. Ví dụ: Công thức Kết quả =AND(TRUE; FALSE) FALSE =OR(TRUE; FALSE; FALSE) TRUE =AND(2+2=4;2+3=5) TRUE =OR(2+2=5; 2+3<5) FALSE =AND(1A2; A2<100) TRUE 3. Hàm NOT Công dụng: Trả về phủ định của một biểu thức Logic. Công thức: logical là một biểu thức điều kiện kiểu logic. Ví dụ: Bùi Thành Khoa 2. Hàm OR Công dụng: Trả về TRUE nếu một trong các điều kiện là TRUE, trả về FALSE nếu tất cả các điều kiện là FALSE. Công thức: Công thức Kết quả =NOT(FALSE) 1 (TRUE) =NOT(1+1=2) 0 (FALSE) 4. Hàm IF Công dụng: Trả về một giá trị nếu điều kiện là đúng, trả về một giá trị khác nếu điều kiện là sai. Công thức: logical_test: điều kiện để xét. value_if_true: giá trị trả về nếu logical_test là TRUE. value_if_false: giá trị trả về nếu logical_test là FALSE. Lưu ý! Có thể có 7 hàm IF được lồng vào nhau để tạo nên công thức phức tạp hơn. Các điều kiện phải có giá trị là TRUE hoặc FALSE. 4 23/06/2011 • IF(Logical_test,Value_if_true,Value_if_false) • Ví dụ: • IF(A1>2,”A”,”B”) • Nếu A1 lớn hơn 2, giá trị trả về là A • Nếu A1 nhỏ hơn hay bằng 2, giá trị trả về là B • Thành tiền=Số lượng x Đơn giá • Nếu MÃ HÀNG bắt đầu bằng B và SỐ LƯỢNG lớn hơn 20 thì giảm THÀNH TIỀN 10% • =IF(AND(LEFT(A2,1)="B",B2>20),B2*C2*90%,B2*C2) — Ví dụ SUMIF 5. Hàm SUMIF Công dụng: hàm trả về giá trị tổng của những phần tử được chọn trong vùng Sum_Range, những phần tử này được chọn tương ứng với những dòng của vùng Range có giá trị thoả mãn điều kiện của Criteria Công thức: range là dãy ô muốn tính toán. Nó có thể là dãy ô chứa điều kiện hoặc dãy ô vừa chứa điều kiện, vừa chứa các giá trị để tính tổng. criteria là điều kiện để tính tổng có thể là số, biểu thức, hoặc kiểu văn bản ví dụ: 5,“<=25","CPU" sum_range là dãy giá trị cần tính tổng. Nếu không có sum_range thì range là dãy chứa giá trị để tính tổng. 6. Hàm COUNTIF Công dụng: COUNTIF(Range, Criteria): hàm trả về giá trị đếm những phần tử của vùng Range thoả mãn điều kiện của Criteria Công thức: range là dãy ô muốn đếm. criteria là điều kiện: số, biểu thức logic, hay kiểu chữ. Ví dụ: 10, ">=10",“5" Ví dụ: ◦ Các giá trị vùng A1:A4 bằng 100, 200, 300, 400 Giá trị vùng A1:A4 bằng 100, 200, 300, 400 =COUNTIF(A1:A4,“>160”) sẽ cho giá trị 3 ◦ Các giá trị vùng B1:B4 bằng 7, 4, 21, 28 ◦ =SUMIF(A1:A4,">160",B1:B4) có kết quả là 63 Bùi Thành Khoa 5 23/06/2011 2.3. HÀM THỐNG KÊ 1. Hàm COUNT Công dụng: Đếm số ô dữ liệu kiểu số trong vùng tham chiếu. Công thức: value1,value2,... có thể có từ 1 đến 30 vùng giá trị mà bạn muốn đếm số ô chứa dữ liệu kiểu số. Lưu ý: Các tham số có thể là số, ngày tháng, hoặc chuỗi đại diện cho số đều được tính. Giá trị lỗi, hoặc chuỗi không thể chuyển đổi sang số được bỏ qua. Ví dụ: Công thức =COUNT(A2:A7) =COUNT(A5:A7) =COUNT(A2:A7,2) Kết quả 3 2 4 3. Hàm RANK Công dụng: Tìm vị thứ của một số trong dãy số. Công thức: number giá trị mà bạn cần tìm vị thứ. ref là mảng hoặc vùng tham chiếu đến một danh sách giá trị kiểu số. Những giá trị không phải là số được bỏ qua. order: phương thức sắp xếp. – – order = 0, hoặc bỏ qua thì số lớn nhất có vị trí nhỏ nhất 1 order = 1 thì số nhỏ nhất có vị trí nhỏ nhất 1. Lưu ý! Nếu 2 số cùng vị thứ thì vị thứ tiếp theo được bỏ qua. Ví dụ: Có 2 giá trị ở vị thứ số 1 thì sẽ không có vị thứ số 2 mà chỉ có vị thứ số 3. Ví dụ: ham-rank.xls 2. Hàm COUNTA và COUNTIF a) COUNTA Công dụng: Đếm số ô có dữ liệu (không phải là ô rỗng) trong vùng tham chiếu. Công thức: value1,value2,... có thể có từ 1 đến 30 vùng giá trị mà bạn muốn đếm số ô chứa dữ liệu kiểu số. Lưu ý: Để đếm số ô chứa dữ liệu kiểu số dùng hàm COUNT, để đếm ô rỗng dùng hàm COUNTBLANK. Ví dụ: =COUNTA(-2;"VTD“;5;8) {kết quả 4} B) COUNTIF COUNTIF (X1,X2,…, điều_kiện) hay COUNTIF(miền, điều_kiện) Đếm số lượng giá trị thoả mãn điều kiện COUNTIF(C3:C11,">=5"): Số ô có giá trị ≥5 trong C3:C11 COUNTIF(C3..C11,”5”): Số ô có giá trị = 5 trong C3..C11 2.4. HÀM CHUỖI 1. Hàm LEFT Công dụng: Trích bên trái một chuỗi một hoặc nhiều ký tự dựa vào số ký tự được chỉ định. Công thức: text là chuỗi cần trích ký tự num_chars là ký tự mà bạn cần trích bên trái chuỗi text. Lưu ý: num_chars nếu lớn hơn độ dài của chuỗi thì sẽ trả về toàn bộ chuỗi text. num_chars nếu bỏ qua thì mặc định là 1. Ví dụ 1: =LEFT("ABCD“; 2) Bùi Thành Khoa {kết quả : "AB"} 6 23/06/2011 Ví dụ 2: Ví dụ 2: Dựa vào cột Mã hàng điền vào cột Tên hàng theo chữ đầu của Mã hàng: C → “Café, T → “Trà”, G → “Gạo” Công thức ô C3: =IF(LEFT(B3;1)=“C”;”Cafe”;IF(LEFT(B3;1)=“T”;”Trà”;”Gạo”)) 2. Hàm RIGHT Công dụng: Trích bên phải một chuỗi một hoặc nhiều ký tự dựa vào số ký tự được chỉ định. Công thức: text là chuỗi cần trích ký tự num_chars là ký tự mà bạn cần trích bên trái chuỗi text. Lưu ý: num_chars nếu lớn hơn độ dài của chuỗi thì sẽ trả về toàn bộ chuỗi text. num_chars nếu bỏ qua thì mặc định là 1. Ví dụ: =RIGHT("ABCD",2) Bùi Thành Khoa {kết quả : "CD"} 3. Hàm MID Công dụng: Trích một chuỗi con từ một chuỗi text, bắt đầu từ vị trí start_num với số ký tự được chỉ định num_chars. Công thức: text: chuỗi hoặc tham chiếu đến chuỗi. start_num: vị trí bắt đầu trích lọc chuỗi con trong text. num_chars: số ký tự của chuỗi mới cần trích từ chuỗi text. Lưu ý: start_num > chiều dài chuỗi text thì hàm trả về chuỗi rỗng. start_num < 1 hàm trả về lỗi #VALUE! Ví dụ: =MID(”mùa thu Hà Nội”; 5; 3) {kết quả : “thu”} 7 23/06/2011 4. Hàm LEN Công dụng: Tính độ dài (số ký tự) của một chuỗi. Công thức: text: nội dung mà bạn cần xác định độ dài text: chuỗi văn bản cần chuyển định dạng. Ví dụ: Ví dụ: =LEN("ABCD") {kết quả là 4} 5. Hàm LOWER Công dụng: Đổi tất cả các ký tự trong chuỗi sang chữ thường. Công thức: text: chuỗi văn bản cần chuyển định dạng. Ví dụ: =LOWER("TRUNG Tam") 6. Hàm UPPER Công dụng: Chuyển tất cả các ký tự trong chuỗi thành ký tự hoa. Công thức: {kết quả : “trung =UPPER("Trung tam") {kết quả: "TRUNG TAM"} 7. Hàm PROPER Công dụng: Chuyển ký tự đầu tiên của mỗi từ thành chữ hoa. Công thức: text: chuỗi văn bản cần chuyển định dạng. Ví dụ: =PROPER("trung tam”) {kết quả : “Trung Tam”} tam”} 8. Hàm TRIM Công dụng: Xóa tất cả các ký tự trắng của chuỗi trừ những khoảng đơn dùng để làm khoảng cách bên trong chuỗi. Công thức: text: chuỗi cần xóa các ký tự trắng. Ví dụ: =TRIM(" Thủ đô Hà Nội ") {kết quả: "Thủ đô Hà Nội"} 9. Hàm FIND Công dụng: cho vị trí của chuỗi con find_text trong chuỗi text bắt đầu tìm từ vị trí start_num, nếu bỏ qua start_num nó cho giá trị bằng 1. Hàm này phân biệt chữ HOA và thường. Công thức: Bùi Thành Khoa Ví dụ: =FIND("e","MS. Excel 6.0") {kết quả : 8 } =FIND("E","MS. Excel 6.0") {kết quả : } 5 =FIND("Excel","MS. Excel 6.0") {kết quả : 5 } Giả sử giá trị trong ô A1 là chuỗi “Lê Văn Hùng” =LEFT(A1;Find(" ";A1)-1) {kết quả: “Lê” } 10. Hàm SEARCH Tương tự hàm FIND nhưng không phân biệt chữ HOA và thường. 8 23/06/2011 11. Hàm SUBSTITUTE Công dụng: Thay thế một chuỗi cụ thể bên trong chuỗi bằng chuỗi khác. Dùng SUBSTITUTE khi muốn thay thế một chuỗi cụ thể. Công thức: 2.5. HÀM TÌM KiẾM 1. Hàm VLOOKUP Công dụng: Dò tìm một giá trị ở cột đầu tiên bên trái của 1 bảng dữ liệu. Nếu tìm thấy sẽ trả về giá trị ở cùng trên dòng với giá trị tìm thấy trên cột mà bạn chỉ định. Công thức: text: chuỗi văn bản cần thay thế nội dung. old_text: nội dung bên trong chuỗi text cần thay thế. new_text: chuỗi văn bản mới để thay chuỗi cũ instance_num: chỉ định thay thế ở lần mà tìm thấy chuỗi old_text trong chuỗi text. Nếu bỏ qua thì sẽ thay thế tất cả các old_text được tìm thấy trong chuỗi text. lookup_value: giá trị dùng để tìm kiếm, nó có thể là một giá trị, một tham chiếu hay một chuỗi ký tự. table_array: vùng chứa dữ liệu cần tìm. Đây là bảng dữ liệu phụ có nội dung thường cố định để lấy dữ liệu. Các giá trị ở cột đầu tiên có thể là giá trị số, chuỗi ký tự, hoặc logic. Nếu range_lookup là TRUE (hay 1) thì giá trị ở cột đầu tiên của bảng dữ liệu phụ này phải được sắp xếp tăng dần. Ví dụ 1: =SUBSTITUTE("CD001";"CD";"CDRW";1) =SUBSTITUTE("DVD002";"0";"A") Ví dụ 2: Tách riêng tên và họ lót. {kết quả: CDRW001} {kết quả: DVDAA2} tach Ho va ten.xls row_index_num: số thứ tự cột trên bảng dữ liệu phụ mà dữ liệu bạn cần lấy. Giá trị trả về nằm trên cột bạn chỉ định này và ở dòng mà hàm tìm thấy giá trị dò tìm lookup_value. range_lookup: giá trị logic được chỉ định muốn VLOOKUP tìm kiếm chính xác hay là tương đối. Nếu range_lookup là TRUE (hay 1) hàm sẽ trả về kết quả tìm kiếm tương đối; khi đó nếu lookup_value lớn hơn phần tử cuối cùng trong danh sách, xem như tìm thấy ở phần tử cuối cùng. Nếu range_lookup là FALSE (hay 0) hàm tìm kiếm chính xác, nếu không có trả về lỗi #N/A! (Not Available: bất khả thi). Lưu ý: Nếu lookup_value nhỏ hơn giá trị nhỏ nhất trong cột đầu tiên của bảng dữ liệu phụ, VLOOKUP trả về lỗi #N/A!. VLOOKUP xuất phát từ vertical lookup: dò tìm theo phương đứng, hay theo cột. Ví dụ: — Ví dụ 1 B C D E F 10 M 2 3 11 N 5 7 5 2 12 O 7 1 7 13 P 2 9 9 14 ◦ ◦ ◦ ◦ =VLOOKUP(“N”,$B$10:$D$13,2,FALSE) =VLOOKUP(“P”,$B$10:$D$13,2,FALSE) =VLOOKUP(“N”,$B$10:$D$13,3,FALSE) =VLOOKUP(“P”,$B$10:$D$13,3,FALSE) ham-vlookup.xls Bùi Thành Khoa 9 23/06/2011 2. Hàm HLOOKUP — Ví dụ 2 =VLOOKUP(A2,$B$12:$C$14,2,FALSE) A B C D Mọi nguyên tắc hoạt động của hàm HLOOKUP (Horizontal Look Up) giống như hàm VLOOKUP (Vertical Look Up), chỉ khác là hàm VLOOKUP dò tìm ở cột bên trái, tham chiếu số liệu ở các cột bên phải, còn hàm HLOOKUP dò tìm ở hàng trên cùng, tham chiếu số liệu ở các hàng phía dưới. 1 DV TÊN DV HỌ TÊN 2 A Ban GĐ Tạ Minh Hải 3 B Phạm Thái 4 C Ng. Biểu Ví dụ: 5 C Ng. Thanh ham-Hlookup.xls 6 A Lê Sơn 7 B Kim Liên DANH MỤC ĐƠN VỊ 8 9 DV TÊN ĐƠN VỊ 10 A Ban GĐ 11 B P. Tổ chức 12 C P. Tài vụ 2.6. HÀM NGÀY THÁNG =HLOOKUP(A2,$B$11:$D$12,2,FALSE) — Ví dụ A B C 1 DV TÊN DV 2 A 3 B Phạm Thái 4 C Ng, Biểu 5 C Ng. Thanh 6 B Lê Sơn D HỌ TÊN Ban GĐ 7 Tạ Minh Hải Kim Liên 8 9 DANH MỤC ĐƠN VỊ 10 11 DV A C B 12 TÊN ĐV Ban GĐ P. Tổ chức P. Tài vụ Excel hỗ trợ tính toán ngày tháng cho Windows và Macintosh. Windows dùng hệ ngày bắt đầu từ 1900. Macitosh dùng hệ ngày bắt đầu từ 1904. Tài liệu này được diễn giải theo hệ ngày 1900 dành cho Windows. Hệ thống ngày giờ Excel phụ thuộc vào thiết lập trong Regional Options của Control Panel. Mặc định là hệ thống của Mỹ "Tháng/Ngày/Năm" (M/d/yyyy). Bạn có thể sửa lại thành hệ thống ngày của VN "Ngày/Tháng/Năm" (dd/MM/yyyy). Khi bạn nhập một giá trị ngày tháng không hợp lệ nó sẽ trở thành một chuỗi văn bản. Công thức tham chiếu tới giá trị đó sẽ trả về lỗi. 13 14 Bùi Thành Khoa 10