Những Hàm và bài tập EXCEL cơ bản và thông dụng


Khi bạn học Tin học văn phòng và môn học Excel là môn tạo cho bạn khó khăn và có nhiều thứ cần nhớ đó là các hàm trong Excel và đây mình xin list ra một số hàm cơ bản nhất, thông dụng nhất cho bạn mong rằng nó sẽ giúp được bạn phần nào trong công việc. Trân trọ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) = 4                                                                 

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,…)


Tải về máy (Bản đầy đủ)
Hỏi Ai Đây Blog
Hỏi Ai Đây Blog
Designer & Developer

Chào các bạn. Với đam mê và sở thích, mình lập nên blog này để lưu trữ những kiến thức, đồng thời cũng chia sẻ những gì mình biết và sưu tầm được tới bạn đọc.

  • Cùng Bình Luận:

    Hiện tại có 2 comments:

    // Điều kiện cho trang và không load trên mobile