Các Hàm và bài tập EXCEL cơ bản và thông dụng
https://www.giaiphap5s.com/2017/02/cac-ham-va-bai-tap-excel-co-ban.html
- CÁC HÀM EXCEL THÔNG DỤNG
I. NHÓM
HÀM CHUỖI (TEXT):
1.
Toán
tử nối chuỗi: &
VD:
ô B1, C1 có giá trị lần lượt là AB, EF. Công thức =B1&C1 có kết quả là: Chuỗi ADEF
2.
UPPER(chuỗi): chuyển tất cả các kí tự
chữ trong chuỗi thành chữ in hoa.
3.
PROPER(chuỗi): chuyển các kí tự chữ đầu
mỗi từ thành chữ in hoa
4.
LOWER(chuỗi): chuyển tất cả các kí tự
chữ trong chuỗi thành chữ thường.
VD:
Ô G3 chứa giá trị “Tin học văn phòng”.
=UPPER(G3)
có kết quả: TIN HỌC VĂN PHÒNG
=PROPER(G3)
có kết quả: Tin Học Văn Phòng
=LOWER(G3)
có kết quả: tin học văn phòng
5.
VALUE(chuỗi số): đổi chuỗi gồm các chữ
số thành một giá trị số.
6.
TEXT(giá trị, dạng thức): chuyển đổi
giá trị thành chuỗi theo dạng thức.
7.
LEFT(chuỗi, số): trích một số kí tự từ
bên trái chuỗi.
8.
RIGHT(chuỗi, số): trích một số kí tự từ
bên phải chuỗi.
·
Hàm
left, right không có số thì hiểu là chỉ lấy 1 kí tự.
9.
MID(chuỗi, vị trí, số): trích một số
kí tự của chuỗi từ vị trí chỉ định.
VD:
Ô C3 chứa giá trị chuỗi “GNK001”.
-
Lấy
kí tự đầu tiên của chuỗi: =LEFT(C3,1)=LEFT(C3) có kết quả là: “G”
-
Lấy
2 kí tự cuối cùng của chuỗi: =RIGHT(C3,2)=”01”
-
Lấy
3 kí tự thứ 2, 3, 4 (ở giữa chuỗi): =MID(C3,2,3) có kết quả là: “NK0”
10. LEN(chuỗi): trả về độ dài_ số kí tự
tính cả khoảng trắng_ của chuỗi.
11. TRIM(chuỗi): trả về chuỗi sau khi đã
xóa các khoảng trống trước và sau.
12. CONCATENATE(chuỗi 1, chuỗi 2, …, chuỗi n):
trả về chuỗi mới là chuỗi nối các chuỗi: chuỗi 1, chuỗi 2, … (tương đương khi
dùng toán tử ghép chuỗi &)
VD:
ô A1, B1, C1 lần lượt chứa các chuỗi “Trần-“, “Văn-”, “Giàu”
=CONCATENATE(A1,B1,C1)=”Trần-Văn-Giàu”
=CONCATENATE(A1:C1)
=> Báo lỗi
II. NHÓM
HÀM TÍNH TOÁN TRÊN SỐ (MATH):
13. ABS(số): trả về giá trị tuyệt đối của 1 số.
14. SUM(các
giá trị số cần tính tổng):
tổng các ô số.
15. INT(số): trả về phần nguyên của một số.
VD:
=INT(62/5)=12; =INT(62,5) => Báo lỗi
16. MOD(số
bị chia, số chia):
trả về số dư của một phép chia nguyên.
VD:
=MOD(62,5)=2; =MOD(62/5) => Báo lỗi
17. ROUND(số,
vị trí làm tròn của số):
làm tròn số đến vị trí chỉ định.
·
Số
ở hàng đơn vị có vị trí là 0, về bên phải dương, bên trái âm.
VD:
Ô A1 có giá trị: 3748.548
=ROUND(A1,
0) = 3749 (làm tròn đến số 8, sau số 8 là 5 nên tăng 8 lên 1 = 9)
=ROUND(A1,
-1) = 3750 (Làm tròn đến số 4, sau 4 là 8 nên tăng lên 1 =5)
=
ROUND(A1, 1) = 3748.5 (Làm tròn đến số 5, sau 5 là 4 nên giữ nguyên)
18. SQRT(số): trả về giá trị căn bậc hai của
1 số.
VD:
=SQRT(25)=5; =SQRT(16)=4; =SQRT(-16) =
#NUM! (Báo lỗi #NUM!)
III. NHÓM
HÀM NGÀY GIỜ (DATE & TIME):
19. TODAY(): trả về ngày hiện thời của hệ
thống.
20. DAY(giá trị ngày): trả về số thứ tự
của ngày trong tháng.
21. MONTH(giá trị ngày): trả về số thứ tự
của tháng trong năm.
22. YEAR(giá trị ngày): trả về số thứ tự
của năm.
23. DATE(năm, tháng, ngày): trả về một
ngày cụ thể.
24. NOW(): trả về ngày giờ hiện thời của
hệ thống.
25. SECOND(giá trị thời gian): trả về số thứ
tự của giây trong thời gian.
26. MINUTE(giá trị thời gian): trả về số thứ
tự của phút trong thời gian.
27. HOUR(giá trị thời gian): trả về số thứ
tự của giờ trong thời gian.
28. TIME(giờ, phút, giây): trả về một giờ
cụ thể.
·
Giá
trị ngày/giờ phải nhập theo đúng quy ước của hệ thống.
IV. NHÓM
HÀM ĐIỀU KIỆN:
29. IF(điều
kiện, giá trị đúng, giá trị sai): trả
về giá trị tương ứng với kết quả của điều kiện.
VD: B2 có giá trị x
Công thức:
=IF(B2=”X”, LEFT(“A001,2), RIGHT (“A001,2)) => kết quả là: “A0”
Vì điều kiện B2=” X”
(không phân biệt hoa, thường) nên lấy giá trị đúng là LEFT(“A001,2) = “A0”
30. AND(điều
kiện 1, điều kiện 2,…):
chỉ trả về giá trị TRUE khi các điều kiện cùng đúng.
31. OR(điều
kiện 1, điều kiện 2,…): chỉ
trả về giá trị FALSE khi các điều kiện đều sai.
VD: IF(OR(3=”2”,
1=1), “true”, “false”) => true (vì điều kiện OR(3=”2”, 1=1) đúng nên lấy giá
trị đúng là: “true”)
V. HÀM
DÒ TÌM:
32. VLOOKUP(giá trị dò tìm, bảng dò, số thứ
tự cột lấy giá trị, cách dò)
HLOOKUP(giá trị dò tìm, bảng dò, số thứ
tự hàng lấy giá trị, cách dò)
-
giá
trị dò tìm: thuộc bảng dữ liệu (bảng chính) có thể là toàn bộ ô, hoặc 1 phần (dùng hàm trích chuỗi: LEFT, RIGHT, MID và kết hợp với hàm VALUE để đổi kiểu chuỗi
số sang kiểu số nếu cần)
-
bảng
dò: chọn toàn bộ bảng trừ dòng tiêu đề của bảng dò. Dùng GIÁ TRỊ TUYỆT ĐỐI.
-
số
thứ tự hàng/cột lấy giá trị: là thứ tự hàng/cột trong bảng dò.
-
cách
dò:
o
0 là dò chính xác, nếu không tìm
thấy sẽ báo lỗi #N/A,
o
số
khác 0 là dò gần đúng. Vd: 1
VI. HÀM
THỐNG KÊ:
33. COUNT(các giá trị cần đếm): đếm các ô
số.
34. COUNTA(các giá trị cần đếm): đếm các ô
có giá trị.
35. MIN(các giá trị cần xét): tìm giá trị
số nhỏ nhất.
36. MAX(các giá trị cần xét): tìm giá trị
số lớn nhất.
37. AVERAGE(các giá trị): tính trung bình.
A
|
B
|
C
|
D
|
E
|
F
|
|
1
|
8
|
4
|
Áo
|
3
|
1
|
|
2
|
=COUNT(A1:B1)
= 4 =MIN(A1:B1)
= 1
=COUNTA(A1:B1)=5 =MAX(A1:B1) =
8
=AVERAGE(A1:B1)
= 4 (8+4+3+1=16 : 4 = 4)
38. RANK(giá trị xếp hạng, vùng, cách xếp
hạng): xếp hạng.
·
Vùng:
phải dùng địa chỉ tuyệt đối.
·
Cách
xếp hạng:
o
0:
xếp hạng học tập hay xếp hạng giảm.
o
Khác
0: xếp hạng tăng. VD: 1
Không viết tham số này thì hiểu là 0.
39. COUNTIF(vùng giá trị xét điều kiện, điều
kiện đếm): Đếm có điều kiện. Nếu điều kiện đếm là dạng chuỗi thì phải để trong dấu nháy kép, còn số thì không cần.
Vd:
countif(vùng đk, “tìm”) hay countif(vùng đk, “>=5”) ,….
40. SUMIF(vùng giá trị xét điều kiện, điều
kiện đếm, vùng giá trị tính tổng): đếm có điều kiện.
·
Các vùng giá trị nếu muốn sao
chép phải dùng địa chỉ tuyệt đối
hoặc dùng tên.
VII. ĐỊA CHỈ Ô
-
Địa
chỉ tương đối: thay đổi khi sao chép công thức, không có kí hiệu $. VD: A5
-
Địa
chỉ tuyệt đối: không thay đổi khi sao chép công thức, có kí hiệu $ cả hàng và cột.
VD: $A$5
-
Địa
chỉ hỗn hợp: chỉ cố định cột hay hàng: VD: $A5 hay A$5
VIII.
SẮP XẾP TRONG BẢNG TÍNH:
IX. LỌC DỮ LIỆU (RÚT TRÍCH, TRÍCH LỌC DANH
SÁCH,…):
