Tự học Excel - Chương 3: Hàm (Function)

List. 
 - Ví dụ: Để đếm số nhân viên trong cột C ở bảng trên thì dùng công thức: 
 COUNT(C2:C6) ® 4 
 c. Hàm COUNTIF: 
 - Cú pháp: COUNTIF(phạm vi, điều kiện) 
 - Công dụng: Đếm số ô thỏa mãn điều kiện trong phạm vi. 
 - Ví dụ: Để đếm số nhân viên thuộc phòng Kế toán (xem bảng ở mục a) thì dùng 
công thức: 
 COUNTIF(D2:D6, “Kế toán”) ® 3 
 @ Chú ý: Trừ trường hợp điều kiện là một con số chính xác thì các trường hợp còn lại 
đều phải bỏ điều kiện trong một dấu ngoặc kép. 
 Ví dụ 1: Đếm số nhân viên có Lương CB là 1.000.000 
 COUNTIF(E2:E6,1000000) ® 2 
 Ví dụ 2: Đếm số nhân viên có Lương CB nhỏ hơn 1.000.000 
 COUNTIF(E2:E6,”<1000000”) ® 2 
 d. Hàm MAX: 
 - Cú pháp: MAX(phạm vi) 
 - Công dụng: Trả về giá trị là số lớn nhất trong phạm vi. 
 - Ví dụ: Để biết Lương CB cao nhất (xem bảng ở mục a) thì dùng công thức: 
 MAX(E2:E6) ® 1.200.000 
 e. Hàm MIN: 
 A B C D E 
1 STT Họ và tên Giới 
tính 
Phòng 
ban 
Lương CB 
2 1 Nguyễn Văn 
A 
Nam Kế Toán 1,000,000 
3 2 Trần Thị B Nữ Kinh 
doanh 
900,000 
4 3 Phạm Ngọc 
C 
 Kế Toán 1,200,000 
5 4 Lê Văn D Nam Kế Toán 800,000 
6 5 Ngô Thị E Nữ Kinh 
doanh 
1,000,000 
 - Cú pháp: MIN(phạm vi) 
 - Công dụng: Trả về giá trị là số nhỏ nhất trong phạm vi. 
 - Ví dụ: Để biết Lương CB thấp nhất (xem bảng ở mục a) thì dùng công thức: 
 MIN(E2:E6) ® 800.000 
 f. Hàm AVERAGE: 
 - Cú pháp: AVERAGE(phạm vi) 
 - Công dụng: Trả về giá trị là trung bình cộng của các ô trong phạm vi. 
 - Ví dụ: Để biết mức lương trung bình (xem bảng ở mục a) thì dùng công thức: 
 AVERAGE(E2:E6) ® 980.000 
 g. Hàm SUM: 
 - Cú pháp: SUM(phạm vi) 
 - Công dụng: Trả về giá trị là tổng các ô trong phạm vi 
 - Ví dụ: Để tính tổng Lương CB của tất cả các nhân viên (xem bảng ở mục a): 
 SUM(E2:E6) ® 4.900.000 
 h. Hàm SUMIF: 
 - Cú pháp: SUMIF(vùng chứa điều kiện, điều kiện, vùng cần tính tổng) 
 - Công dụng: Hàm dùng để tính tổng có điều kiện. Chỉ những ô nào trên vùng 
chứa điều kiện thoả mãn điều kiện thì sẽ tính tổng những ô tương ứng trên vùng cần 
tính tổng. 
 - Ví dụ: Tính tổng Lương CB của nhân viên phòng Kế toán (xem bảng ở mục a): 
 SUMIF(D2:D6, “Kế toán”, E2:E6) ® 3.000.000 
 4. Hàm xếp vị thứ (RANK) 
 - Cú pháp: RANK(X, Khối, n) 
 - Công dụng: Xếp vị thứ cho giá trị X trong khối. Trong đó, n là tham số qui định cách 
sắp xếp: 
 + Nếu n = 0 (hoặc không có tham số này) thì kết quả sắp xếp theo kiểu lớn đứng 
trước, nhỏ đứng sau 
 + Nếu n khác 0 thì kết quả sắp xếp theo kiểu nhỏ đứng trước, lớn đứng sau 
 - Ví dụ 1: Để xếp vị thứ cho các học sinh trong bảng dưới thì tại ô D2 gõ công thức 
sau: 
 RANK(C2,$C$2:$C$6) 
 A B C D 
1 STT Họ và tên ĐTB Vị thứ 
2 1 Nguyễn Văn 
A 
8.3 
3 2 Trần Thị B 7.5 
4 3 Phạm Ngọc 
C 
9.2 
5 4 Lê Văn D 7.8 
 5. Nhóm hàm xử lý dữ liệu logic: 
 Dữ liệu logic là loại dữ liệu mà chỉ chứa một trong hai giá trị True (đúng) hoặc False 
(sai). 
 a. Hàm IF: 
 - Cú pháp: IF(biểu thức điều kiện, giá trị 1, giá trị 2) 
 - Công dụng: Hàm tiến hành kiểm tra biểu thức điều kiện: 
 + Nếu biểu thức điều kiện là True (đúng) thì trả về giá trị 1. 
 + Ngược lại, nếu biểu thức điều kiện là False (sai) thì trả về giá trị 2. 
 - Ví dụ 1: Hãy điền giá trị cho cột kết quả. Biết rằng: Nếu điểm thi lớn hơn hoặc 
bằng 5 thì ghi “Đậu”. Ngược lại thì ghi “Hỏng” 
 IF(B2>=5, "Đậu", "Hỏng") 
 - Ví dụ 2: IF(2>3,”Sai”, “Đúng”) ® “Đúng” 
 @ Chú ý: 
 v Hàm IF dùng để chọn 1 trong 2 lựa chọn nhưng nếu phải chọn nhiều hơn 2 lựa 
chọn thì dùng hàm IF theo kiểu lồng nhau. 
 Ví dụ 3: Hãy điền giá trị cho cột xếp loại trong bảng dưới. Biết rằng: Nếu ĐTB 
>=9 thì ghi “Giỏi”, nếu ĐTB >=7 thì ghi “Khá”, nếu ĐTB >=5 thì ghi “TB”, còn lại thì ghi 
“Yếu”. 
 IF(B2>=9,"Giỏi",IF(B2>=7,"Khá",IF(B2>=5,"TB","Yếu"))) 
 v Biểu thức điều kiện của hàm IF luôn phải có kết quả trả về 1 trong 2 giá trị True 
(đúng) hoặc False (sai). 
 Ví dụ 4: IF(“A”=“B”, ”C”, ”D”) ® “D” 
 Ví dụ 5: IF(“A”, ”B”, ”C”) ® Lỗi #VALUE 
 v Trong hàm IF, nếu không có đối số thứ 3 thì khi biểu thức điều kiện sai hàm sẽ 
trả về giá trị False. 
 Ví dụ 6: IF(2<3, ”Sai”) ® “Sai” 
 Ví dụ 7: IF(2>3, “Sai”) ® FALSE 
 b. Hàm AND: 
 - Cú pháp: AND(biểu thức điều kiện 1, biểu thức điều kiện 2,...) 
 - Công dụng: Hàm trả về giá trị True (đúng) nếu tất cả các biểu thức điều kiện đều 
đúng và trả về giá trị False (sai) khi có ít nhất một biểu thức điều kiện sai. 
6 5 Ngô Thị E 8.5 
 A B C 
1 Họ tên Điểm thi Kết quả 
2 Nguyên 7 
3 Hồng 4 
4 Anh 6 
5 Thành 9 
6 Xuân 3 
 - Ví dụ: Hãy điền giá trị cho cột kết quả trong bảng dưới. Biết rằng: 
Nếu tổng điểm>=10 và điểm Anh văn>=5 thì ghi “Đậu” 
Các trường hợp còn lại thì ghi “Hỏng”. 
 IF(AND(B2>=10,C2>=5),"Đậu", "Hỏng") 
 c. Hàm OR: 
 - Cú pháp: OR(biểu thức điều kiện 1, biểu thức điều kiện 2,...) 
 - Công dụng: Hàm trả về giá trị True (đúng) nếu có ít nhất một bt điều kiện đúng và 
trả về giá trị False nếu tất cả các bt điều kiện đều sai. 
 - Ví dụ: Hãy điền giá trị cho cột kết quả trong bảng ở mục b. Biết rằng: 
Nếu tổng điểm>=10 hoặc điểm Anh văn>=5 thì ghi “Đậu” 
Các trường hợp còn lại thì ghi “Hỏng”. 
 IF(OR(B2>=10,C2>=5),"Đậu", "Hỏng") 
 d. Hàm NOT: 
 - Cú pháp: NOT(biểu thức logic) 
 - Công dụng: Trả về giá trị là phủ định của biểu thức logic 
 - Ví dụ: NOT(2<3) ® False 
 6. Nhóm hàm xử lý dữ liệu ngày - tháng - năm: 
 a. Hàm TODAY: 
 - Cú pháp: TODAY( ) 
 - Công dụng: Trả về giá trị là ngày tháng năm của hệ thống 
 - Ví dụ: 
 b. Hàm NOW: 
 - Cú pháp: NOW( ) 
 - Công dụng: Trả về giá trị là ngày tháng năm và giờ - phút của hệ thống 
 - Ví dụ: 
 c. Hàm DAY: 
 - Cú pháp: DAY(biểu thức ngày – tháng – năm) 
 - Công dụng: Trả về phần ngày của biểu thức ngày – tháng – năm 
 - Ví dụ: DAY(TODAY( )) 
 d. Hàm MONTH: 
 A B C D 
1 
Họ tên 
Tổng điểm 
thi Anh 
Kết 
quả 
2 Nguyên 13 6 
3 Hồng 8 8 
4 Anh 16 7 
5 Thành 17 3 
6 Xuân 9 5 
 - Cú pháp: MONTH(biểu thức ngày – tháng – năm) 
 - Công dụng: Trả về phần tháng của biểu thức ngày – tháng – năm. 
 - Ví dụ: MONTH(TODAY( )) 
 e. Hàm YEAR: 
 - Cú pháp: YEAR(biểu thức ngày – tháng – năm) 
 - Công dụng: Trả về phần năm của biểu thức ngày – tháng – năm. 
 - Ví dụ: YEAR(TODAY( )) 
b. Hàm WEEKDAY: 
 - Cú pháp: WEEKDAY(biểu thức ngày – tháng - năm , kiểu trả về) 
 - Công dụng: Trả về giá trị là số thứ tự của biểu thức ngày -tháng năm trong một 
tuần tùy thuộc vào kiểu trả về: 
 + Nếu kiểu trả về là 1 (hoặc không có) thì chủ nhật được xem là ngày đầu tiên 
trong tuần và được đánh số thứ tự theo bảng sau: 
 Ví dụ: Giả sử ô A1 chứa ngày 30/07/2006 (tức chủ nhật) thì: 
 WEEKDAY(A1) ® 1 
 + Nếu kiểu trả về là 2 thì thứ 2 được xem là ngày đầu tiên trong tuần và được 
đánh STT theo bảng sau: 
 Ví dụ: Giả sử ô A2 chứa giá trị ngày 31/07/2006 (tức thứ 2) thì 
 WEEKDAY(A2) ® 1 
 + Nếu kiểu trả về là 3 thì thứ 2 được xem là ngày đầu tiên trong tuần và và được 
đánh STT theo bảng sau: 
 Ví dụ: Giả sử ô A2 chứa giá trị ngày 31/07/2006 (thứ 2) thì 
 WEEKDAY(A2) ® 0 
 7. Nhóm hàm xử lý dữ liệu giờ - phút - giây: 
 a. Hàm SECOND: 
 - Cú pháp: SECOND(biểu thức giờ - phút - giây) 
 - Công dụng: Trả về phần giây của biểu thức giờ - phút - giây 
 - Ví dụ: Giả sử tại ô D5 chứa gía trị 08:30:20 thì: 
 SECOND(D5) ® 20 
 b. Hàm MINUTE: 
 - Cú pháp: MINUTE(biểu thức giờ - phút - giây) 
Thứ CN 2 3 4 5 6 7 
STT 1 2 3 4 5 6 7 
Thứ 2 3 4 5 6 7 CN 
STT 1 2 3 4 5 6 7 
Thứ 2 3 4 5 6 7 CN 
STT 0 1 2 3 4 5 6 
 - Công dụng: Trả về phần phút của biểu thức giờ - phút - giây 
 - Ví dụ: Giả sử tại ô D5 chứa gía trị 08:30:20 thì: 
 MINUTE(D5) ® 30 
 c. Hàm HOUR: 
 - Cú pháp: HOUR(biểu thức giờ - phút - giây ) 
 - Công dụng: Trả về phần giờ của biểu thức giờ - phút - giây 
 - Ví dụ: Giả sử tại ô D5 chứa gía trị 08:30:20 thì: 
 HOUR(D5) ® 8 
8. Nhóm hàm tra cứu: 
 a. Hàm VLOOKUP: 
 - Cú pháp: VLOOKUP(n, khối, m, r) 
 - Công dụng: Tiến hành tìm giá trị n trong cột đầu tiên của khối và lấy giá trị tương 
ứng trên cột thứ m. 
 + Nếu r=0 (hoặc FALSE) thì tìm giá trị chính xác bằng với n. Nếu không tìm thầy 
thì trả về lỗi #N/A (lỗi không tìm thấy) 
 Ví dụ: Điền giá trị cho cột Phụ cấp trong bảng dưới dựa vào chức vụ và trả ở 
bảng 1 
 VLOOKUP(C2,$F$3:$G$7,2,0) 
 + Nếu r=1 (hoặc TRUE) thì cột đầu tiên của khối phải được sắp xếp tăng dần và 
lúc đó nếu không tìm thấy giá trị chính xác với n sẽ lấy giá trị tương ứng gần của n. 
 Ví dụ: Điền giá trị cho cột Xếp loại trong bảng sau dựa vào ĐTB và tra ở bảng 
tra 
 VLOOKUP(C2,$F$2:$G$5,2,1) 
 A B C D E F G 
1 STT Họ và tên Chức vụ Phụ cấp BẢNG 1 
2 
1 Nguyễn GĐ 
Chức 
vụ Phụ cấp 
3 2 Lê PGĐ GĐ 50000 
4 3 My TP PGĐ 40000 
5 4 Hoàng NV TP 30000 
6 5 Thanh TP PP 20000 
7 6 Hạnh PP NV 10000 
8 7 Chi NV 
9 8 Hiền NV 
10 9 Trâm NV 
11 10 Tùng NV 
12 BẢNG 2 
13 Chức vụ GĐ PGĐ TP PP NV 
14 Phụ cấp 50000 40000 30000 20000 10000 
 b. Hàm HLOOKUP: 
 HLOOKUP có cú pháp và công dụng tương tự VLOOKUP nhưng được dùng trong 
trường hợp bảng tra được bố trí theo hàng ngang thay vì theo hàng dọc như 
VLOOKUP. 
 Ví dụ: Điền giá trị cho cột Phụ cấp trong bảng ở mục a dựa vào chức vụ và trả ở 
bảng 2 
 HLOOKUP(C2,$C$13:$G$14,2,0) 
 c. Hàm MATCH: 
 - Cú pháp: MATCH(n, Khối, r) 
 - Công dụng: Trả về số thứ tự của n trong khối. Trong đó, r có ý nghĩa tương tự r 
trong hàm VLOOKUP. 
 - Ví dụ: Hãy xác định vị trí của nhân viên tên Chi trong cột B của hình 1 mục a: 
 MATCH(“Chi”, B2:B11,0) ® 7 
 d. Hàm INDEX: 
 - Cú pháp: INDEX(Khối, m, n) 
 - Công dụng: Trả về giá trị của ô nằm giao của hàng m và cột n trong khối. 
 - Ví dụ: Hãy xác định giá thuê phòng Loại B ở tầng 3 trong bảng sau là bao nhiêu? 
 INDEX($C$3:$E$5,2,3) ® 260 
 A B C D E F G 
1 STT Họ và tên ĐTB Xếp loại ĐTB Xếp loại 
2 1 Nguyễn 7.3 4 Yếu 
3 2 Lê 5.6 5 TB 
4 3 My 9.0 7 Khá 
5 4 Hoàng 4.0 9 Giỏi 
6 5 Thanh 5.0 
 A B C D E 
1 BẢNG GIÁ TIỀN PHÒNG 
2 Loại\Tầng T1 T2 T3 TR 
3 A 310 290 270 250 
4 B 300 280 260 240 
5 C 210 190 170 150