Bài giảng môn Tin học văn phòng - Bài 2: Các hàm cơ bản trong Excel - Thiều Quang Trung

BÀI 2 
CÁC HÀM CƠ BẢN TRONG EXCEL 
GV Th.S. Thiều Quang Trung 
Bộ môn Khoa học cơ bản 
Trường Cao đẳng Kinh tế Đối ngoại 
• Các toán tử trong Excel 1 
• Nhóm hàm xử lý số học, chuỗi, ngày 
tháng 2 
• Nhóm hàm logic 3 
• Nhóm hàm dò tìm 4 
Nội dung 
GV. Thiều Quang Trung 2 
Các toán tử đối với dữ liệu dạng số 
• Toán tử số học 
Toán tử Mục đích 
+ Cộng 
- Trừ 
* Nhân 
/ Chia 
% Lấy phần trăm 
^ Luỹ thừa 
3 GV. Thiều Quang Trung 
Các toán tử đối với dữ liệu dạng số 
Độ ưu tiên của các toán tử được thực hiện theo mức 
độ sau: 
Chú ý: 
- Các toán tử trong một biểu thức có cùng độ ưu tiên thì thứ tự 
thực hiện là từ trái sang phải. 
- Có thể thay đổi thứ tự ưu tiên bằng cách thêm dấu ngoặc trong 
các thành phần tạo nên công thức. 
Toán tử Mục đích 
^ Luỹ thừa 
*, / Nhân, chia 
+, - Cộng, trừ 
4 GV. Thiều Quang Trung 
Các toán tử đối với dữ liệu dạng số 
• Toán tử logic 
 NOT : Toán tử phủ định 
 AND : Toán tử và 
 OR : Toán tử hoặc 
Biểu thức logic chỉ có hai giá trị TRUE và FALSE 
5 GV. Thiều Quang Trung 
Các toán tử đối với dữ liệu dạng số 
• Toán tử quan hệ 
Để so sánh các kết quả ta có thể lập các công thức với 
các toán tử so sánh. Chúng sẽ cho giá trị TRUE hoặc 
FALSE tuỳ thuộc vào cách xem xét các điều kiện. 
 TOÁN TỬ MỤC ĐÍCH 
= Bằng nhau 
< Nhỏ hơn 
> Lớn hơn 
<= Nhỏ hơn hoặc bằng 
>= Lớn hơn hoặc bằng 
 Không bằng nhau 
6 GV. Thiều Quang Trung 
Các toán tử đối với kiểu ký tự 
Các toán tử quan hệ: = , ,=, 
Toán tử nối chuỗi : & 
Ví dụ: ="Khoa " & "Công Nghệ" cho kết quả là "Khoa 
Công Nghệ" 
Chú ý: Chuỗi phải được đặt trong cặp dấu nháy kép 
("..."). 
7 GV. Thiều Quang Trung 
Các toán tử đối với dữ liệu ngày tháng 
Ta có thể sử dụng các phép toán cộng, trừ số học và 
các phép toán quan hệ khi sử dụng dữ liệu kiểu ngày 
tháng. 
8 GV. Thiều Quang Trung 
Biến đổi công thức thành giá trị 
 Thứ tự thực hiện như sau: 
- Chọn ô chứa công thức muốn biến đổi. 
- Nhấn phím F2 hoặc click đôi chuột. 
- Nhấn phím F9, Excel sẽ thay công thức 
bằng giá trị của nó. 
9 GV. Thiều Quang Trung 
Điền số thứ tự 
• Có thể điền nhanh số thứ tự bằng các bước: 
– Nhập số thứ tự đầu tiên, click chuột vào ô vừa 
nhập. 
– Đưa chuột đến nút Fill handle của ô vừa nhập, 
lúc này chuột sẽ có dạng dấu + 
– Nhấn và giữ phím Ctrl, rồi kéo rê chuột xuống các 
ô cần điền số thứ tự. 
– Thả nút trái chuột trước, thả phím Ctrl sau, số 
thứ tự sẽ được tự động điền vào đúng ô của nó. 
10 GV. Thiều Quang Trung 
Các lỗi của công thức 
Một số lỗi thường gặp do tạo công thức sai: 
LỖI Ý NGHĨA 
# DIV/O Công thức gặp trường hợp chia cho số không 
# N/A Công thức chứa giá trị không dùng được 
# NAME? Công thức có dùng tên mà Excel không nhận ra 
# NUM! Công thức dùng một số không đúng 
# VALUE Công thức dùng một đối số hoặc toán tử sai kiểu 
11 GV. Thiều Quang Trung 
Hàm trong Excel 
• Hàm là một biểu thức tính toán đã được định 
nghĩa trước để thực hiện các tính toán phức tạp 
thay cách viết công thức cho các ô. 
• Cú pháp chung của hàm như sau: 
 = ( ) 
• Tên hàm có thể viết bằng chữ hoa hay chữ thường. 
• Các đối số thường cách nhau bằng dấu "," hoặc 
dấu “;” tuỳ theo thiết lập trong máy. 
• Đối số có thể là các hằng số, địa chỉ khối, tên khối 
hoặc một hàm khác... 
 12 GV. Thiều Quang Trung 
Hàm trong Excel 
• Nếu đối số là một vùng địa chỉ ô thì cần ghi 
theo dạng: 
:. 
• Ví dụ: 
– A1:B2 là bao gồm các ô A1, A2, B1, B2. 
– Hàm tính tổng: =Sum(A1:A4) 
=> tính tổng giá trị các ô từ ô A1 đến ô A4. 
13 GV. Thiều Quang Trung 
Các nhóm hàm cơ bản trong Excel 
1. Nhóm hàm xử lý toán học 
2. Nhóm hàm xử lý ký tự 
3. Nhóm hàm ngày tháng năm 
4. Nhóm hàm logic 
5. Nhóm hàm dò tìm 
14 GV. Thiều Quang Trung 
1. Nhóm hàm xử lý toán học 
1. Hàm ABS(x) 
Trả về giá trị tuyệt đối của số x. 
Ví dụ: ABS(-2) → 2. 
 ABS(2) → 2. 
2. Hàm SQRT(x) 
Trả về căn bậc hai của số không âm x. 
Ví dụ: SQRT(9) → 3. 
 SQRT(-9) sẽ trả về lỗi #NUM!. 
15 GV. Thiều Quang Trung 
3. Hàm MOD(n,t) 
Trả về số dư của phép chia nguyên n/t. 
Ví dụ: MOD(12,5) → 2. 
4. Hàm INT(number) 
Trả về phần nguyên của số number. Hàm sẽ trả về 
giá trị theo xu hướng lấy số nguyên nhỏ hơn. 
 Ví dụ: INT(3.5) → 3. 
 INT(-3.5) → -4. 
16 
1. Nhóm hàm xử lý toán học 
GV. Thiều Quang Trung 
5. Hàm ROUND(x,n) 
Trả về số x sau khi làm tròn với độ chính xác đến n số 
thập phân (n>0). 
n=0 làm tròn hàng đơn vị. 
n=-1 làm tròn hàng chục... 
Ví dụ: 
ROUND(3.547,2) → 3.55. 
ROUND(14624.47,-2) → 14600 (làm tròn hàng trăm). 
17 
1. Nhóm hàm xử lý toán học 
GV. Thiều Quang Trung 
1. Hàm LEFT(text,number) 
Lấy number ký tự bên trái của text. 
Ví dụ: LEFT(“Thiều Quang Trung",5) → “Thiều". 
2. Hàm RIGHT(text,number) 
Lấy number ký tự bên phải của text. 
Ví dụ: RIGHT(“Thiều Quang Trung”,11) → “Quang 
Trung”. 
18 
2. Nhóm hàm xử lý ký tự 
GV. Thiều Quang Trung 
3. Hàm LEN(text) 
Trả về độ dài của chuỗi ký tự text 
Ví dụ: LEN(“Thiều Quang Trung") → 17. 
4. Hàm LOWER(text) 
Chuyển text thành chữ in thường 
Ví dụ: LOWER("Học Và Hành") → "học và hành". 
19 
2. Nhóm hàm xử lý ký tự 
GV. Thiều Quang Trung 
5. Hàm UPPER(text) 
Chuyển chữ thường thành chữ in hoa. 
Ví dụ: UPPER("Học Và Hành") → "HỌC VÀ HÀNH". 
6. Hàm PROPER(text) 
Chuyển các chữ cái đầu từ của text thành chữ hoa 
và các chữ còn lại thành chữ thường. 
Ví dụ: PROPER("trUng tâM") → "Trung Tâm". 
20 
2. Nhóm hàm xử lý ký tự 
GV. Thiều Quang Trung 
7. Hàm MID(text,m,n) 
Cho kết quả là chuỗi con trích ra từ chuỗi text từ vị 
trí m và gồm n ký tự. 
Ví dụ: MID(“Thiều Quang Trung",7,5) → “Quang". 
8. Hàm TRIM(text) 
Cắt bỏ các ký tự trắng hai phía của text. 
Ví dụ:TRIM(" Quang Trung ") → “Quang Trung". 
21 
2. Nhóm hàm xử lý ký tự 
GV. Thiều Quang Trung 
2. Nhóm hàm xử lý ký tự 
9. Hàm FIND và Hàm SEARCH 
FIND(find_text, within_text [, start_num]) 
SEARCH(find_text, within_text [, start_num]) 
– Tìm vị trí bắt đầu của một chuỗi con trong một 
chuỗi 
find_text: chuỗi văn bản cần tìm (chuỗi con) 
within_text: chuỗi văn bản chứa chuỗi cần tìm 
start_num: vị trí bắt đầu tìm (mặc định là 1) 
– Hàm Search() không phân biệt chữ hoa, còn hàm 
Find() có phân biệt chữ hoa → tìm chính xác 
GV. Thiều Quang Trung 22 
2. Nhóm hàm xử lý ký tự 
10. Hàm SUBSTITUTE(text, old_text, 
new_text, [instance_num]) 
Thay thế chuỗi cũ bằng một chuỗi mới trong 
chuỗi dữ liệu ban đầu. 
Ví dụ: SUBSTITUTE("Chi phí ngân sách năm 
2015", "2015", "2016") → Chi phí ngân sách 
năm 2016 
GV. Thiều Quang Trung 23 
2. Nhóm hàm xử lý ký tự 
11. Hàm REPLACE(old_text, start_num, 
num_chars, new_text) 
Thay thế một phần chuỗi cũ bằng một chuỗi mới, 
phần chuỗi thay thế sẽ được xác định tại vị trí n và 
có chiều dài m 
Ví dụ: ô A1 đang chưa chuỗi ”Chi phí ngân sách 
năm 2015” 
REPLACE(A1, FIND("2015", A1), LEN("2015"), 
"2016") → Chi phí ngân sách năm 2016 
GV. Thiều Quang Trung 24 
1. Hàm DATE(year, month, day) 
Cho giá trị là một dữ liệu kiểu ngày ứng với các đối 
số được đưa vào. 
Ví dụ: DATE(2016,04,30) → 04/30/2016 (định dạng 
theo tháng/ngày/ năm). 
25 
3. Nhóm hàm ngày tháng năm 
GV. Thiều Quang Trung 
2. Hàm DAY(date) 
Trả về giá trị ngày trong tháng của biến ngày tháng 
date. 
Ví dụ: Day(“04/30/2016”) → 30 (định dạng theo 
kiểu tháng/ngày/năm) 
26 
3. Nhóm hàm ngày tháng năm 
GV. Thiều Quang Trung 
3. Hàm MONTH(date) 
Trả về giá trị tháng trong năm của biến ngày tháng 
date. 
Ví dụ: MONTH(“04/30/2016”) → 04 (định dạng theo 
tháng/ngày/ năm). 
27 
3. Nhóm hàm ngày tháng năm 
GV. Thiều Quang Trung 
4. Hàm YEAR(date) 
Trả về giá trị năm của biến ngày tháng date. 
Ví dụ:YEAR(“04/30/2016”) → 2016 (định dạng theo 
tháng/ngày/ năm). 
28 
3. Nhóm hàm ngày tháng năm 
GV. Thiều Quang Trung 
5. Hàm DATEDIF(date1, date2, kiểu trả về) 
Trả về số ngày (số tháng hoặc số năm) tính từ mốc 
thời gian date1 đến date2. Trong đó là: 
“d” : trả về số ngày 
“m” : trả về số tháng 
“y” : trả về số năm 
Ví dụ: 
DATEDIF("1970/06/20","2016/06/20","y") → 46 
29 
3. Nhóm hàm ngày tháng năm 
GV. Thiều Quang Trung 
1. Hàm AND(logical1,logical2, ...) 
Trong đó logical1, logical2, ... là các đối số nhận một 
trong hai giá trị logic TRUE hoặc FALSE. Nếu đối số 
không nhận giá trị logic, hàm AND trả về giá trị lỗi 
#VALUE! 
Hàm sẽ trả về giá trị TRUE (ĐÚNG) nếu tất cả các đối 
số nhận giá trị TRUE; trả về giá trị FALSE (SAI) nếu có 
ít nhất một đối số nhận giá trị FALSE. 
Ví dụ: AND(2+2=4, 2+3=5) → TRUE. 
AND(3>4, 2+3=5) → FALSE. 
30 
4. Nhóm hàm logic 
GV. Thiều Quang Trung 
2. Hàm OR(logical1, logical2...) 
Trong đó logical1, logical2,... là các đối số nhận một 
trong hai giá trị logic TRUE hoặc FALSE. Nếu đối số 
không nhận giá trị logic, hàm OR trả về giá trị lỗi 
#VALUE! 
Trả về giá trị đúng nếu có ít nhất một đối số nhận 
giá trị đúng, cho giá trị sai nếu tất cả đối số đều 
nhận giá trị sai. 
Ví dụ: OR(2+2=4, 2+3>5) → TRUE. 
 OR(3>4, 2+3<5) → FALSE. 
31 
4. Nhóm hàm logic 
GV. Thiều Quang Trung 
3. Hàm IF(biểu thức logic, giá trị đúng, giá trị sai) 
Hàm trả về giá trị đúng nếu biểu thức logic đúng, ngược lại 
trả về giá trị sai. 
Ví dụ: giả sử ô D2 chứa Điểm trung bình, hãy tính Xếp loại 
dựa vào Điểm trung bình theo ba mức: 
 ĐTB >=8: Xếp loại Giỏi 
 8 > ĐTB >=7: Xếp loại Khá 
 7 > ĐTB: Xếp loại Trung bình 
IF(D2>=8, ”Giỏi”, If(D2>=7, ”Khá”, ”Trung bình”)) 
32 
4. Nhóm hàm logic 
GV. Thiều Quang Trung 
1. Hàm VLOOKUP(Giá trị tìm, Bảng tham chiếu, Cột tham 
chiếu, Cách tìm) 
 Thực hiện dò tìm với Giá trị tìm trên cột đầu tiên của 
Bảng tham chiếu, khi tìm thấy thì dịch chuyển qua bên 
phải đến Cột tham chiếu để lấy trị trong ô tương ứng với 
vị trí tìm thấy của Giá trị tìm. 
 Bảng tham chiếu: là một khối ô nhiều hàng nhiều cột. 
Cột đầu tiên luôn luôn chứa các trị để dò tìm, các cột 
khác chứa các trị tương ứng để tham chiếu. 
 Cột tham chiếu: thứ tự của cột tính từ trái qua phải 
trong bảng tham chiếu, cột đầu tiên được đánh số là 1. 
33 
5. Nhóm hàm dò tìm 
GV. Thiều Quang Trung 
 Cách tìm: Có hai giá trị: 
– Nếu là số 0 (hoặc FALSE, còn gọi là dò tìm chính xác): Danh 
sách các trị của cột 1 không cần sắp xếp theo thứ tự tăng dần. 
Nếu không tìm thấy trị dò ở Bảng tham chiếu, hàm sẽ trả về 
lỗi #N/A. 
– Nếu là số 1 (hoặc TRUE, còn gọi là dò tìm lân cận hay dò tìm 
xấp xỉ): Danh sách các trị của cột 1 phải được sắp xếp theo 
thứ tự tăng dần. Nếu không tìm thấy trị dò chính xác nó sẽ lấy 
giá trị nhỏ hơn và gần bằng với trị dò. Nếu trị dò nhỏ hơn trị 
đầu tiên của cột 1 thì hàm sẽ trả về lỗi #N/A. 
– Mặc định cách dò là 1. 
34 
5.1. Hàm VLOOKUP 
GV. Thiều Quang Trung 
Ví dụ: Thiết lập công thức tại ô E2 ? 
B C D E 
TT Họ tên Khu vực Điểm chuẩn 
1 Hoàng Phi 2 
2 Nguyễn Thị Như 3 
3 Trần Đức 1 
Khu vực Điểm chuẩn 
1 24 
2 23 
3 22 
35 
5.1. Hàm VLOOKUP 
GV. Thiều Quang Trung 
HLOOKUP(Giá trị tìm, Bảng tham chiếu, Hàng tham chiếu, 
Cách tìm) 
 Thực hiện tìm kiếm Giá trị tìm trên hàng đầu tiên của 
Bảng tham chiếu, khi tìm thấy thì dịch chuyển xuống 
dòng đến Hàng tham chiếu để lấy trị trong ô tương ứng 
với vị trí tìm thấy của Giá trị tìm. 
 Bảng tham chiếu: là khối ô nhiều hàng nhiều cột. Hàng 
đầu tiên luôn luôn chứa các trị để dò tìm, các hàng khác 
chứa các trị tương ứng để tham chiếu. 
 Hàng tham chiếu: thứ tự của hàng tính từ trên xuống 
dưới trong bảng tham chiếu, hàng đầu tiên được đánh 
số là 1. 
 Cách tìm: tương tự như VLOOKUP 
36 
5.2. Hàm HLOOKUP 
GV. Thiều Quang Trung 
Ví dụ: Thiếp lập công thức tại ô E2 ? 
B C D E 
1 TT Họ tên Khu vực Điểm chuẩn 
2 1 Hoàng Phi 2 
3 2 Nguyễn Thị Như 3 
4 3 Trần Đức 1 
5 
6 Khu vực 1 2 3 
7 Điểm chuẩn 24 23 22 
8 
9 
37 
5.2. Hàm HLOOKUP 
GV. Thiều Quang Trung 
5.3. Hàm INDEX 
INDEX(array, row_num, column_num) 
• Trả về một giá trị nằm bên trong một mảng 
dựa vào chỉ số dòng và cột. 
• array: Là một mảng . 
• row_num: Chỉ số dòng cần trả về giá trị. 
• colum_num: Chỉ số cột cần trả về giá trị. 
GV. Thiều Quang Trung 38 
5.4. Hàm MATCH 
MATCH(lookup_value, lookup_array, match_type) 
• Trả về vị trí (chỉ mục) của một giá trị từ một dãy giá trị. 
• Lookup_value: Là giá trị cần tìm trong bảng giá trị. 
• Lookup_array: Là một dãy ô liên tục để tìm kiếm giá trị. 
• Match_type: Có 3 kiểu tìm kiếm là: 
– 1:Less than (Nhỏ hơn giá trị tìm kiếm) 
– 0: Exact match (Chính xác giá trị tìm kiếm) 
– -1:Greater than ( Lớn hơn giá trị tìm kiếm) 
• Khi bỏ qua không nhập gì thì hàm MATCH mặc định là 1. 
GV. Thiều Quang Trung 39 
GV. Thiều Quang Trung 40 
Câu 1: Công thức =ROUND(136485.22, -3) 
sẽ cho kết quả là: 
a. 136,000.22 
b. 137,000.22 
c. 136000 
d. 137000 
GV. Thiều Quang Trung 41 
Câu 02: Giả sử ô A1 chứa giá trị ngày sinh của 1 
người là 31/12/1996, ô A2 chứa giá trị ngày 
hiện tại, để tính tuổi thì gõ công thức nào sau 
đây: 
a. =YEAR(TIMEVALUE(A1)) 
b. =DATEDIF(A1,A2,"y") 
c. =DATEDIF(A2,A1,"y") 
d. =YEAR(A2) - YEAR(A1) 
GV. Thiều Quang Trung 42 
Câu 3: Hàm OR( ) trả về kết quả đúng khi nào? 
a. Chỉ cần một đối tượng trong hàm trả về TRUE 
b. Nếu có hai đối số trong hàm đều trả về TRUE 
c. Nếu tất cả các đối số trong hàm đều trả về TRUE 
d. Tất cả đều sai 
GV. Thiều Quang Trung 43 
GV. Thiều Quang Trung 44 
Câu 4 : Cho bảng số liệu sau: 
Công thức sau: =VLOOKUP(104,$A$2:$B$6, 2) thì kết quả sẽ là: 
a. Báo lỗi “N/A” 
b. SP. Tin hoc 
c. CN Sinh hoc 
d. Báo lỗi vì công thức sai 
 A B C D E F 
1 MaNganh TenNganh N4 N3 N2 N1 
2 101 SP. Toan 22.5 21.5 20.5 19.5 
3 102 SP. Ly 20.5 19.5 18.5 17.5 
4 103 SP. Tin hoc 19.5 18.0 17.5 16.5 
5 202 SP. Hoa 20.0 19.0 18.0 17.0 
6 309 CN Sinh hoc 22.0 21.0 20.0 19.0 
Câu 5 : Cho bảng số liệu sau: 
Công thức MATCH(202,$B$2:$B$6) cho kết quả là: 
a. 4 
b. 5 
c. Lỗi “N/A” 
d. Báo lỗi vì công thức sai 
 A B C D E F 
1 MaNganh TenNganh N4 N3 N2 N1 
2 101 SP. Toan 22.5 21.5 20.5 19.5 
3 102 SP. Ly 20.5 19.5 18.5 17.5 
4 103 SP. Tin hoc 19.5 18.0 17.5 16.5 
5 202 SP. Hoa 20.0 19.0 18.0 17.0 
6 309 CN Sinh hoc 22.0 21.0 20.0 19.0 
GV. Thiều Quang Trung 45 
Câu 6 : Cho bảng số liệu sau: 
Công thức INDEX($C$2:$F$6,2,3) cho kết quả là: 
a. 18.5 
b. 18.0 
c. 17.5 
d. 19.5 
 A B C D E F 
1 MaNganh TenNganh N4 N3 N2 N1 
2 101 SP. Toan 22.5 21.5 20.5 19.5 
3 102 SP. Ly 20.5 19.5 18.5 17.5 
4 103 SP. Tin hoc 19.5 18.0 17.5 16.5 
5 202 SP. Hoa 20.0 19.0 18.0 17.0 
6 309 CN Sinh hoc 22.0 21.0 20.0 19.0 
GV. Thiều Quang Trung 46 
Câu 7 : Cho bảng số liệu sau: 
Công thức INDEX($C$2:$F$6, MATCH(102, 
$A$2:$A$6, 0), 2) cho kết quả là: 
a. 18.5 
b. 18.0 
c. 17.5 
d. 19.5 
 A B C D E F 
1 MaNganh TenNganh N4 N3 N2 N1 
2 101 SP. Toan 22.5 21.5 20.5 19.5 
3 102 SP. Ly 20.5 19.5 18.5 17.5 
4 103 SP. Tin hoc 19.5 18.0 17.5 16.5 
5 202 SP. Hoa 20.0 19.0 18.0 17.0 
6 309 CN Sinh hoc 22.0 21.0 20.0 19.0 
GV. Thiều Quang Trung 47