viết 10 hàm excel nâng cao và cho ví dụ chi tiết
- User
viết 10 hàm excel nâng cao và cho ví dụ chi tiết
- Assistant
1. Hàm XLOOKUP
Chức năng: Tìm kiếm một giá trị trong một phạm vi hoặc mảng và trả về một giá trị tương ứng từ một cột hoặc hàng khác.
Ví dụ chi tiết:
Giả sử bạn có bảng dữ liệu về sản phẩm:
A B C Mã SP Tên Sản Phẩm Giá SP001 Bút bi 5,000 SP002 Sổ tay 15,000 SP003 Thước kẻ 8,000 Bạn muốn tìm Giá của sản phẩm có Mã SP là "SP002".
Công thức:
=XLOOKUP("SP002", A2:A4, C2:C4)Kết quả: 15,000
2. Hàm FILTER
Chức năng: Lọc các hàng trong một phạm vi dựa trên một hoặc nhiều điều kiện.
Ví dụ chi tiết:
Giả sử bạn có bảng điểm của học sinh:
A B C Họ tên Lớp Điểm TB An 10A1 8.5 Bình 10A2 7.0 Cường 10A1 9.0 Dung 10A2 6.5 Bạn muốn lọc ra danh sách học sinh lớp 10A1 có Điểm TB lớn hơn 8.0.
Công thức:
=FILTER(A2:C5, (B2:B5="10A1")*(C2:C5>8))Kết quả:
| An | 10A1 | 8.5 | | Cường| 10A1 | 9.0 |
3. Hàm UNIQUE
Chức năng: Trích xuất các giá trị duy nhất từ một phạm vi hoặc mảng.
Ví dụ chi tiết:
Giả sử bạn có danh sách các lớp:
A 10A1 10A2 10A1 10A3 10A2 Bạn muốn lấy danh sách các lớp không trùng lặp.
Công thức:
=UNIQUE(A2:A6)Kết quả:
| 10A1 | | 10A2 | | 10A3 |
4. Hàm SORT
Chức năng: Sắp xếp dữ liệu trong một phạm vi hoặc mảng.
Ví dụ chi tiết:
Sử dụng bảng điểm học sinh ở trên, bạn muốn sắp xếp danh sách theo Điểm TB giảm dần.
Công thức:
=SORT(A2:C5, 3, -1)Giải thích:
- 3: Cột thứ 3 (Điểm TB)
- -1: Sắp xếp giảm dần
Kết quả:
| Cường| 10A1 | 9.0 | | An | 10A1 | 8.5 | | Bình | 10A2 | 7.0 | | Dung | 10A2 | 6.5 |
5. Hàm SEQUENCE
Chức năng: Tạo ra một mảng số liên tiếp.
Ví dụ chi tiết:
Bạn muốn tạo một danh sách số từ 1 đến 10.
Công thức:
=SEQUENCE(10)Kết quả:
| 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | | 8 | | 9 | | 10|
6. Hàm TEXTJOIN
Chức năng: Kết hợp văn bản từ nhiều ô với nhau, có sử dụng dấu phân cách.
Ví dụ chi tiết:
Giả sử bạn có thông tin họ tên học sinh:
A B C Họ Tên Đệm Tên Nguyễn Văn An Trần Thị Bình Bạn muốn kết hợp thành họ tên đầy đủ.
Công thức:
=TEXTJOIN(" ", TRUE, A2:C2)Kết quả cho hàng 2: Nguyễn Văn An
7. Hàm LET
Chức năng: Gán tên cho kết quả tính toán trung gian trong công thức để dễ đọc và hiệu quả hơn.
Ví dụ chi tiết:
Bạn muốn tính thuế thu nhập cá nhân với công thức phức tạp, giả sử:
- Thu nhập chịu thuế nằm ở ô A1
- Thuế suất là 10% nếu thu nhập <= 10,000,000
- Thuế suất là 20% nếu thu nhập > 10,000,000
Sử dụng hàm LET để đơn giản hóa công thức.
Công thức:
=LET( thu_nhap, A1, nguong, 10000000, thue_suat, IF(thu_nhap <= nguong, 0.1, 0.2), thue, thu_nhap * thue_suat, thue )Kết quả: Thuế phải nộp dựa trên thu nhập ở ô A1.
8. Hàm IFS
Chức năng: Kiểm tra nhiều điều kiện và trả về giá trị tương ứng với điều kiện đúng đầu tiên.
Ví dụ chi tiết:
Bạn muốn xếp loại học sinh dựa trên Điểm TB:
-
= 8.5: Giỏi
-
= 7.0: Khá
-
= 5.0: Trung bình
- < 5.0: Yếu
Công thức:
=IFS( C2 >= 8.5, "Giỏi", C2 >= 7.0, "Khá", C2 >= 5.0, "Trung bình", TRUE, "Yếu" )Áp dụng cho ô C2 (Điểm TB của học sinh đầu tiên).
9. Hàm SWITCH
Chức năng: So khớp một biểu thức với danh sách giá trị và trả về kết quả tương ứng.
Ví dụ chi tiết:
Giả sử bạn có mã khu vực trong ô A2, bạn muốn hiển thị tên khu vực tương ứng:
- 1: Miền Bắc
- 2: Miền Trung
- 3: Miền Nam
Công thức:
=SWITCH(A2, 1, "Miền Bắc", 2, "Miền Trung", 3, "Miền Nam", "Không xác định" )
10. Hàm XMATCH
Chức năng: Tìm kiếm một mục trong mảng hoặc phạm vi và trả về vị trí tương đối của mục đó. Hỗ trợ nhiều phương thức so khớp.
Ví dụ chi tiết:
Bạn có danh sách sản phẩm:
A Bút bi Sổ tay Thước kẻ Tẩy Bạn muốn tìm vị trí của sản phẩm "Thước kẻ".
Công thức:
=XMATCH("Thước kẻ", A2:A5)Kết quả: 3
Giải thích: "Thước kẻ" nằm ở vị trí thứ 3 trong danh sách.
Lưu ý: Các hàm như XLOOKUP, FILTER, UNIQUE, SORT, SEQUENCE, LET, IFS, SWITCH, XMATCH yêu cầu phiên bản Excel 365 hoặc Excel 2021 trở lên.
Hy vọng với 10 hàm Excel nâng cao và ví dụ chi tiết trên, bạn sẽ áp dụng hiệu quả trong công việc và học tập.