Bài giảng Tin học văn phòng - Chương 1: Excel nâng cao (Phần 2) - Hoàng Thanh Hòa

á trị ngày giờ đều được quy đổi thành 1 giá 
 trị số để tính toán
 ➢ Giá trị số là số ngày cách mốc quy ước 31/12/1899
 ➢ VD: 06/12/1999 quy ra 36500 ngày
 28/05/2005 quy ra 38500 ngày
 hthoa@cofer.edu.vn Trang 58
1. Hàm DATE
 • Cú pháp: =DATE(year,month,day)
 • Hàm trả về giá trị ngày tháng
 • VD:
 =DATE(2008,1,1) → 01/01/2008
 =DATE(108,1,1) → 01/01/2008
 =DATE(2008,12,32) → 01/01/2009
 =DATE(08,14,01) → 01/02/2009
 hthoa@cofer.edu.vn Trang 59
2. Hàm NOW
 • Cú pháp: =NOW()
 • Hàm trả về giá trị ngày tháng năm hiện hành
 • Tùy theo kiểu định dạng mà kết quả thể hiện khác 
 nhau
 • VD:
 =NOW() → 09/16/14 với định dạng mm/dd/yy
 =NOW() → 16/09/2014 với định dạng dd/mm/yyyy
 hthoa@cofer.edu.vn Trang 60
3. Hàm DAY
 • Cú pháp: =DAY(serial-number)
 • Hàm trả về giá trị ngày trong biểu thức ngày tháng
 • Serial-number: là biểu thức ngày tháng
 • VD:
 =DAY(“01/02/2008”) → 1
 =DAY(A2) → 31 (ô A2 chứa biểu thức 31/01)
 =DAY(39000) → 10
 hthoa@cofer.edu.vn Trang 61
4. Hàm MONTH
 • Cú pháp: =MONTH(serial-number)
 • Hàm trả về giá trị tháng trong biểu thức ngày tháng
 • Serial-number: là biểu thức ngày tháng
 • VD:
 =MONTH(“01/02/2008”) → 2
 =MONTH(A2) → 1 (ô A2 chứa biểu thức 31/01)
 =MONTH(39000) → 10
 hthoa@cofer.edu.vn Trang 62
5. Hàm YEAR
 • Cú pháp: =YEAR(serial-number)
 • Hàm trả về giá trị năm trong biểu thức ngày tháng
 • Serial-number: là biểu thức ngày tháng
 • VD:
 =YEAR(“01/02/2008”) → 2008
 =YEAR(A2) → 2014 (ô A2 chứa biểu thức 31/01)
 =YEAR(39000) → 2006
 hthoa@cofer.edu.vn Trang 63
6. Hàm HOUR
 • Cú pháp: =HOUR(serial-number)
 • Hàm trả về giá trị là số giờ lẻ chưa tròn 1 ngày (từ 0 
 đến 23)
 • Serial-number: là biểu thức thời gian hoặc 1 số nào 
 đó chỉ số ngày (0<number<1)
 • VD:
 =HOUR(“10:30 AM”) → 10
 =HOUR(“10:30 PM”) → 22
 =HOUR(0.5) →12
 hthoa@cofer.edu.vn Trang 64
7. Hàm MINUTE
 • Cú pháp: =MINUTE(serial-number)
 • Hàm trả về giá trị là số phút chưa tròn 1 giờ (từ 0 
 đến 59)
 • Serial-number: là biểu thức thời gian
 • VD:
 =MINUTE(“10:30 AM”) → 30
 =MINUTE(“10:45 PM”) → 45
 =MINUTE(0.32) →40
 hthoa@cofer.edu.vn Trang 65
8. Hàm SECOND
 • Cú pháp: =SECOND(serial-number)
 • Hàm trả về giá trị là số giây chưa tròn 1 phút (từ 0 
 đến 59)
 • Serial-number: là biểu thức thời gian
 • VD:
 =SECOND(0.32) →48
 hthoa@cofer.edu.vn Trang 66
9. Hàm TIME
 • Cú pháp: =TIME(hour,minute,second)
 • Hàm trả về giá trị biểu thức thời gian
 • VD:
 =TIME(13,40,48)→ 1:40 PM
 =TIME(7,40,48) → 7:40 AM
 hthoa@cofer.edu.vn Trang 67
10. Hàm DATEDIF
 • Cú pháp: =DATEDIF(firstdate,enddate,option)
 • Hàm trả về một giá trị, là số ngày, số tháng hay số năm 
 giữa hai khoảng thời gian theo tùy chọn.
 • Với:
 - firstdate: là Ngày bắt đầu của khoảng thời gian cần 
 tính toán
 - Enddate: là Ngày kết thúc của khoảng thời gian cần 
 tính toán
 - Option: là tùy chọn, xác định kết quả tính toán sẽ trả 
 về trong công thức: 
 hthoa@cofer.edu.vn Trang 68
10. Hàm DATEDIF
 + "d": Hàm sẽ trả về số ngày giữa hai khoảng thời gian.
 + "m": Hàm sẽ trả về số tháng (chỉ lần phần nguyên) giữa 
 hai khoảng thời gian.
 + "y": Hàm sẽ trả về số năm (chỉ lần phần nguyên) giữa 
 hai khoảng thời gian.
 + "yd": Hàm sẽ trả về số ngày lẻ của năm (số ngày chưa 
 tròn năm) giữa hai khoảng thời gian.
 + "ym": Hàm sẽ trả về số tháng lẻ của năm (số tháng chưa 
 tròn năm) giữa hai khoảng thời gian.
 + "md": Hàm sẽ trả về số ngày lẻ của tháng (số ngày chưa 
 tròn tháng) giữa hai khoảng thời gian.
 hthoa@cofer.edu.vn Trang 69
11.Hàm WEEKDAY
 • Cú pháp: =WEEKDAY(serial_number, return_type)
 • Hàm trả về một giá trị, là số thứ tự của Ngày trong 
 tuần.
 • Với:
 - Serial_number : là một biểu thức thời gian, 
 Serial_number có thể là Number hay Value_date
 - return_type : là tùy chọn để xác định kiểu giá trị sẽ 
 trả về trong công thức
 hthoa@cofer.edu.vn Trang 70
11.Hàm WEEKDAY
 - return_type :
 + 1 hoặc để trống : Ngày chủ nhật được xem là 1, 
 Ngày thứ bảy là 7.
 + 2 : Ngày thứ hai được xem là 1, Ngày chủ nhật là 7.
 + 3 : Ngày thứ hai được xem là 0, Ngày chủ nhật là 6.
 hthoa@cofer.edu.vn Trang 71
1. Hàm AND
 • Cú pháp: =AND(logical 1, logical 2)
 • Hàm trả về kết quả đúng (TRUE) nếu tất cả các đối 
 số đều đúng
 • Hàm trả về kết quả sai (FALSE) nếu ít nhất 1 đối số 
 sai.
 • Logical: là các biểu thức điều kiện
 • VD: =AND(A2>=20,A2<26)
 hthoa@cofer.edu.vn Trang 72
2. Hàm OR
 • Cú pháp: =OR(logical 1, logical 2)
 • Hàm trả về kết quả sai (FALSE) nếu tất cả các đối số 
 đều sai, 
 • Trả về kết quả đúng (TRUE) nếu ít nhất 1 đối số 
 đúng.
 • Logical là các biểu thức điều kiện
 • VD: =OR(A2>=20,B2<10)
 hthoa@cofer.edu.vn Trang 73
3. Hàm điều kiện
• Cú pháp: =IF(logical_test, value_if_true, 
 value_if_false)
• Hàm trả về kết quả là value_if_true nếu logical_test
 có giá trị TRUE
• Hàm trả về kết quả là value_if_false nếu logical_test
 có giá tri FALSE
• VD: 
Công thứ ô C2 là:
=IF(B2>=5,“đậu”,“rớt”)
 hthoa@cofer.edu.vn Trang 74
3. Hàm điều kiện
 • Trong hàm IF, khi có nhiều điều kiện thì phải liên kết các điều 
 kiện này bằng hàm AND hoặc hàm OR
 • Công thức tính kết quả tại ô F5:
 =IF(AND(D5+E5>=10,D50,E50),"Đậu","Rớt")
 =IF(OR(D5+E5<10,D5=0,E5=0),“Rớt”,“Đậu”)
 hthoa@cofer.edu.vn Trang 75
• Hàm IF có thể được viết lồng vào nhau khi có nhiều kết 
 quả trong phép tính
• Công thức tại ô E5:
 =IF(D5>=5,”Đậu”,IF(D5<=3,”Học lại”,”Thi lại”))
 hthoa@cofer.edu.vn Trang 76
1. Hàm dò tìm theo cột
• Cú pháp:
=VLOOKUP(lookup_value,table_array,col_index_num,option_l
ookup)
• Hàm trả về giá trị của ô nằm trên cột nào đó thỏa mãn 
 điều kiện dò tìm
• Với: 
 ➢ lookup_value: giá trị dò tìm
 ➢ Table_array: là bảng chứa giá trị cần để dò tìm
 ➢ Col_index: số thứ tự cột từ bên trái sang trong bảng
 ➢ Option_lookup: xác định kiểu dò tìm 
 hthoa@cofer.edu.vn Trang 77
1. Hàm dò tìm theo cột
• Option_lookup:
 ➢ Sắp xếp vùng đối chiếu chỉ nhận giá trị logic 0 hoặc 1, 
 nếu bỏ qua thì nhận giá trị 1
 ➢ Nếu để giá trị 0: dò tìm chính xác, lấy giá trị đầu tiên 
 tìm được, nếu không tìm thấy trả về #N/A
 ➢ Nếu để giá trị 1: kiểu dò tìm tương đối, nếu không 
 tìm thấy trả về giá trị nhỏ hơn gần nhất với giá trị cần 
 dò tìm
 hthoa@cofer.edu.vn Trang 78
1. Hàm dò tìm theo cột
• Ví dụ:
 ➢ Tìm điểm LT của sinh viên Cúc:
 =Vlookup(“Cúc”,A1:C5,2,0)
 ➢ Tìm điểm TH của sinh viên Lan:
 =Vlookup(“Lan”,A1:C5,3,0)
 hthoa@cofer.edu.vn Trang 79
2. Hàm dò tìm theo hàng
• Cú pháp: 
 =HLOOKUP(lookup_value,table_array,row_index_num,optio
 n_lookup)
• Hàm trả về giá trị của ô nằm trên HÀNG nào đó thỏa mãn 
 điều kiện dò tìm
• Với: 
 ➢ lookup_value: giá trị dò tìm
 ➢ Table_array: là bảng chứa giá trị cần để dò tìm
 ➢ row_index: số thứ tự hàng từ trên xuống trong bảng
 ➢ Option_lookup: xác định kiểu dò tìm 
 hthoa@cofer.edu.vn Trang 80
2. Hàm dò tìm theo hàng
• Ví dụ:
 ➢ Tìm điểm LT của sinh viên Cúc:
 =Hlookup(“Điểm LT”,A1:C5,4,0)
 ➢ Tìm điểm TH của sinh viên Lan:
 =Hlookup(“Điểm LT”,A1:C5,3,0)
 hthoa@cofer.edu.vn Trang 81
3. Hàm MATCH
• Cú pháp: 
 =MATCH(lookup_value,lookup_array,match_type)
• Hàm trả về vị trí của giá trị dò tìm trong cột (hàng)
• Với: 
 ➢ Lookup_value: là giá trị dùng để dò tìm, có thể là một 
 số, một chuỗi, một giá trị logic hay một tham chiếu.
 ➢ Lookup_array: là vùng dò tìm, có thể là một cột hoặc 
 một dòng, hoặc một mảng các giá trị.
 ➢ Match_type: là tùy chọn để xác định kiểu dò tìm.
 hthoa@cofer.edu.vn Trang 82
3. Hàm MATCH
• Match-type: nhận 3 giá trị tương ứng 
 ➢ =0: dò tìm chính xác
 ➢ =1 (hoặc để trống): tìm vị trí của giá trị lớn nhất 
 nhỏ hơn giá trị cần tìm.
 ➢ =-1: Tìm giá trị nhỏ nhất lớn hơn giá trị cần tìm
 hthoa@cofer.edu.vn Trang 83
4. Hàm INDEX
• Cú pháp: =INDEX(array,row_num,col_num)
• Hàm trả về giá trị trong một bảng (mảng)
• Với: 
 ➢ Array: là một dãy ô hay một hằng mảng.
 ➢ Row_num: là số chỉ dòng của giá trị trong mảng cần 
 trả về. 
 ➢ Col_num: là số chỉ cột của giá trị trong mảng cần trả 
 về. 
 hthoa@cofer.edu.vn Trang 84
1. Hàm TEXT:
• Cú pháp: TEXT(number,format-text)
• Hàm chuyển đổi một số thành dạng văn bản (text) theo 
 định dạng được chỉ định
• Với: 
 ➢ Number: là số cần chuyển đổi sang dạng text
 ➢ Format-text: kiểu định dạng số hoạc ngày tháng năm
• Một số kiểu định dạng:
 ➢ Định dạng số: ###,000 ; #,##0.00..
 ➢ Định dạng ngày tháng: dd/MM/yy; d/m/yyyy; 
 dd/MM/yyyy hh:mm:ss.
 hthoa@cofer.edu.vn Trang 85
1. Hàm TEXT:
 ➢ TEXT(123,“$#,##0.00”) → $123.00
 ➢ TEXT(1234,“#,##0.00”) → 1,234.00
 ➢ TEXT(12345,“###,000”) → 12,345
 ➢ TEXT(23/02/1988,“mm/dd/yy”) → 02/23/88
 ➢ TEXT(23/02/1988,“dd/mm/yy hh:mm:ss”) →
 23/02/88 00:00
 hthoa@cofer.edu.vn Trang 86
2. Hàm VALUE:
 • Cú pháp: VALUE(text)
 • Hàm chuyển một chuỗi đại diện cho một số thành 
 kiểu số
 • Text: phải là dạng số hoặc ngày tháng 
 • VD:
 =VALUE(“$123.00”) →123
 =VALUE(“01/15/14”) → 41654
 hthoa@cofer.edu.vn Trang 87
1. Hàm SUMIF:
• Cú pháp: SUMIF(range,criteria,sum-range)
• Hàm trả về tổng của các ô trong một vùng thỏa mãn 
 điều kiện cho trước
• Với:
 ➢ Range: dãy các ô có chứa điều kiện
 ➢ Criteria: điều kiện để tính tổng
 ➢ Sum-range: vùng chứa các ô cần tính tổng
 hthoa@cofer.edu.vn Trang 88
2. Hàm COUNTIF:
• Cú pháp: COUNTIF(range,criteria)
• Hàm trả về số lượng các ô trong vùng thỏa mãn điều
 kiện cho trước
• Với:
 ➢ Range: dãy các ô để đếm
 ➢ Criteria: điều kiện để đếm
 hthoa@cofer.edu.vn Trang 89
3. Hàm AVERAGEIF:
• Cú pháp: AVERAGEIF(range,criteria,average-range)
• Hàm trả về giá trị trung bình của các ô trong một vùng 
 thỏa mãn điều kiện cho trước
• Với:
 ➢ Range: dãy các ô có chứa điều kiện
 ➢ Criteria: điều kiện để tính giá trị trung bình
 ➢ Average-range: vùng chứa các ô cần tính giá trị trung 
 bình
 hthoa@cofer.edu.vn Trang 90
4. Hàm SUMIFS:
• Cú pháp: 
= SUMIFS(sum_range, criteria_range1, criteria1, 
criteria_range2, criteria2, ...)
• Hàm trả về tổng các ô trong một vùng thỏa nhiều 
 điều kiện cho trước
• Với:
 ➢ Sum-range: Vùng chứa các ô cần tính tổng
 ➢ Criteria_range1: Vùng chứa điều kiện 1
 ➢ Criteria1: Điều kiện 1
 hthoa@cofer.edu.vn Trang 91
5. Hàm COUNTIFS:
• Cú pháp: 
= COUNTIFS(range1, criteria1, range2, criteria2, ...)
• Hàm đếm các ô trong vùng thỏa nhiều điều kiện cho 
 trước.
• Với:
 ➢ Range 1: Vùng chứa điều kiện 1
 ➢ Criteria1: Điều kiện 1
 hthoa@cofer.edu.vn Trang 92
6. Hàm AVERAGEIFS:
• Cú pháp: 
= AVERAGEIFS(average_range, criteria_range1, 
criteria1, criteria_range2, criteria2, ...)
• Hàm trả về giá trị trung bình của các ô thỏa mãn 
 nhiều điều kiện cho trước.
• Với:
 ➢ Average_range : Vùng cần tính giá trị trung bình.
 ➢ Criteria_range 1: Vùng chứa điều kiện 1.
 ➢ Criteria 1: Điều kiện 1
 hthoa@cofer.edu.vn Trang 93
• Sau khi đánh dấu khối ô bảng tính cần định dạng, ta 
 thực hiện lệnh [Menu] Home/Format/Format 
 Cells (hoặc tổ hợp phím Ctrl -1) để tạo định dạng 
 tổng thể. Excel xuất hiện hộp hội thoại bao gồm các 
 thẻ định dạng riêng biệt: 
 hthoa@cofer.edu.vn Trang 94
hthoa@cofer.edu.vn Trang 95
• Định dạng số: Trong hộp Category chọn kiểu dữ 
 liệu cần trình bày và chọn tiếp các thông số tương 
 ứng với kiểu dữ liệu đã chọn:
 ➢ General: định dạng chung. Với kiểu định dạng này, 
 dữ liệu kiểu ký tự gõ vào sẽ được canh hàng bên 
 trái và dữ liệu kiểu số sau khi gõ vào bảng tính sẽ 
 được canh hàng bên phải ô. 
 ➢ Number: dùng để định dạng các số thông thường. 
 Chọn tiếp các thông số sau: 
 hthoa@cofer.edu.vn Trang 96
• Định dạng số:
 ✓ Decimal places: khai báo số chữ số sau dấu 
 chấm thập phân. 
 ✓ Use 1000 Separators(,): click chọn khi muốn 
 dùng dấu phẩy để phân cách hàng nghìn 
 ✓ Negative numbers: chọn cách định dạng số âm. 
 ➢ Currency: Chọn cách hiện ký hiệu tiền tệ. 
 ➢ Date: Chọn cách hiện giá trị ngày tháng. 
 hthoa@cofer.edu.vn Trang 97
• Định dạng số:
 ➢ Percentage: Chọn cách hiện ký hiệu phần trăm. 
 ➢ Fraction: Chọn cách hiện giá trị phân số. 
 ➢ Scientific: Chọn cách hiện số dưới dạng khoa học. 
 ➢ Text: định dạng số theo kiểu ký tự. 
 ➢ Special: định dạng theo mã vùng và số điện thoại 
 ➢ Custom: tuỳ biến theo khuôn dạng được gõ trong 
 hộp Type 
 hthoa@cofer.edu.vn Trang 98
• Excel cho phép ta hiển thị ngày tháng theo nhiều 
 kiểu định dạng khác nhau. Ta có thể sử dụng những 
 mẫu định dạng đã có sẵn hoặc có thể tự định nghĩa 
 những kiểu định dạng mới theo ý thích. 
 ➢ Sử dụng những mẫu định dạng có sẵn: Ở khung 
 Category chọn mục Date, và chọn kiểu hiển thị bên 
 khung Type. Excel thường hiển thị ngày tháng theo 
 dạng tổng quát m/d/yy (tháng/ngày/năm) 
 hthoa@cofer.edu.vn Trang 99
 ➢ Sử dụng những định dạng tự định nghĩa: Nếu 
 không vừa ý với các dạng có sẵn, ta có thể tạo ra 
 một dạng hiển thị riêng bằng cách sau: 
Chọn mục Start/Control Panel xuất hiện hộp thoại 
Control Panel chọn vào biểu tượng xuất hiện hộp thoại 
sau: 
 hthoa@cofer.edu.vn Trang 100
hthoa@cofer.edu.vn Trang 101
• Chọn vào nút 
 Additional settings 
 xuất hiện hộp thoại 
 tiếp theo chọn vào tab 
 Date thì hộp thoại có 
 dạng sau: 
 hthoa@cofer.edu.vn Trang 102
• Trong khung Short date (định nghĩa kiểu thể hiện 
 kiểu ngày rút gọn) 
 ➢ Short date sample: ví dụ về cách thể hiện định 
 dạng ngày đã được định nghĩa. 
 ➢ Short date format: kiểu ngày đang được định 
 nghĩa, ta có thể click chọn vào đây để chọn kiểu 
 định nghĩa mới hoặc có thể gõ trực tiếp vào khung 
 này. 
 ➢ Date separator: Dấu phân cách giữa ngày, tháng và 
 năm (dấu - hoặc /) 
 hthoa@cofer.edu.vn Trang 103
• Trong khung Long date (định nghĩa kiểu thể hiện 
 kiểu ngày chi tiết) 
 ➢ Long date sample: ví dụ về cách thể hiện định 
 dạng ngày đã được định nghĩa. 
 ➢ Long date format: kiểu ngày đang được định 
 nghĩa, ta có thể click chọn vào đây để chọn kiểu 
 định nghĩa mới hoặc có thể gõ trực tiếp vào khung 
 này. 
 hthoa@cofer.edu.vn Trang 104
hthoa@cofer.edu.vn Trang 105
• Mục Horizontal: Dùng để canh chỉnh theo chiều ngang 
 ➢ General: Trở về cách sắp xếp ngầm định 
 ➢ Left: Canh trái các ô. 
 ➢ Center: Canh giữa các ô. 
 ➢ Right: Canh phải các ô. 
 ➢ Justify: Canh đều hai bên các ô. 
• Mục Vertical: Dùng để canh chỉnh theo chiều dọc 
 ➢ Top: Ký tự hiện sát đỉnh ô 
 ➢ Center: Ký tự hiện giữa ô. 
 ➢ Bottom: Ký tự hiện ở đáy ô. 
 hthoa@cofer.edu.vn Trang 106
• Mục Text control:Điều kiển dữ liệu trong khối ô 
 được chọn 
 ➢ Wrap text: Khi click chọn thì độ rộng cột sẽ cố 
 định, dữ liệu nhập vào tự động xuống hàng. 
 ➢ Shrink to fit: khi click chọn vào mục này thì dữ liệu 
 trọng ô sẽ co lại vừa bằng kích thước của ô. 
 ➢ Merge cells: Khi click chọn thì Excel sẽ sát nhập 
 các ô đã chọn lại thành một ô. 
• Hộp Orientation: Dùng để chỉnh độ nghiêng của dữ 
 liệu
 hthoa@cofer.edu.vn Trang 107
 Mọi chi tiết xin vui lòng liên hệ:
 ThS. Hoàng Thanh Hòa
Email: hthoa@cofer.edu.vn
Website: https://sites.google.com/site/thanhhoakhcb/
Di động: 0396935167