Bên cạnh kiến thức lý thuyết, việc thực hành là vô cùng cần thiết để bạn nâng cao khả năng sử dụng Excel và áp dụng nó vào thực tế. Bài viết này sẽ giới thiệu 50 Bài Tập Excel Có Lời Giải, giúp bạn củng cố kiến thức, trau dồi kỹ năng, và tự tin hơn khi làm việc với phần mềm này.
Bài Tập Cơ Bản (1-10)
1. Tính Tổng, Trung Bình, Tìm Giá Trị Lớn Nhất, Nhỏ Nhất
Bài tập: Cho bảng dữ liệu điểm thi của học sinh, hãy tính tổng điểm, điểm trung bình, điểm cao nhất và điểm thấp nhất của mỗi môn.
Lời giải:
- Tổng điểm: Sử dụng hàm
SUM() - Trung bình: Sử dụng hàm
AVERAGE() - Giá trị lớn nhất: Sử dụng hàm
MAX() - Giá trị nhỏ nhất: Sử dụng hàm
MIN()
2. Sử Dụng Hàm IF để Kiểm Tra Điều Kiện
Bài tập: Cho bảng dữ liệu bán hàng, hãy kiểm tra xem doanh thu của mỗi sản phẩm có đạt chỉ tiêu hay không. Nếu đạt chỉ tiêu, in ra “Đạt”, ngược lại in ra “Chưa đạt”.
Lời giải: Sử dụng hàm IF() để kiểm tra điều kiện, với công thức: =IF(Doanh thu >= Chỉ tiêu, "Đạt", "Chưa đạt")
3. Sử Dụng Hàm VLOOKUP để Tra Cứu Dữ Liệu
Bài tập: Cho bảng dữ liệu danh sách nhân viên và bảng dữ liệu thông tin lương. Hãy tra cứu thông tin lương của mỗi nhân viên dựa trên mã số nhân viên.
Lời giải: Sử dụng hàm VLOOKUP() với công thức: =VLOOKUP(Mã số nhân viên, Bảng dữ liệu lương, Cột lương, FALSE)
4. Định Dạng Bảng Dữ Liệu
Bài tập: Hãy định dạng bảng dữ liệu về điểm thi của học sinh, bao gồm:
- Căn chỉnh: Căn giữa tiêu đề, căn phải cột điểm, căn trái cột tên học sinh.
- Font chữ: Chọn font chữ phù hợp, cỡ chữ 12.
- Màu sắc: Chọn màu sắc phù hợp cho tiêu đề, cột điểm và dòng dữ liệu.
- Khung viền: Tạo khung viền cho bảng dữ liệu.
5. Tạo Biểu Đồ
Bài tập: Hãy tạo biểu đồ cột thể hiện doanh thu của mỗi sản phẩm trong bảng dữ liệu bán hàng.
Lời giải:
- Chọn dữ liệu cần tạo biểu đồ.
- Chọn tab “Insert” trên thanh công cụ.
- Chọn loại biểu đồ cột.
6. Sử Dụng Hàm COUNTIF để Đếm Số Lượng Dữ Liệu
Bài tập: Hãy đếm số lượng học sinh đạt điểm giỏi (trên 8), khá (từ 7 đến 8), trung bình (từ 5 đến 7) và yếu (dưới 5) trong bảng dữ liệu điểm thi.
Lời giải: Sử dụng hàm COUNTIF() với công thức: =COUNTIF(Cột điểm, ">8"), =COUNTIF(Cột điểm, ">=7,<=8"), =COUNTIF(Cột điểm, ">=5,<=7"), =COUNTIF(Cột điểm, "<5")
7. Sử Dụng Hàm SUMIF để Tính Tổng Dữ Liệu Có Điều Kiện
Bài tập: Hãy tính tổng doanh thu của sản phẩm “A” trong bảng dữ liệu bán hàng.
Lời giải: Sử dụng hàm SUMIF() với công thức: =SUMIF(Cột sản phẩm, "A", Cột doanh thu)
8. Sử Dụng Hàm AVERAGEIF để Tính Trung Bình Dữ Liệu Có Điều Kiện
Bài tập: Hãy tính điểm trung bình của học sinh có tên “Nguyễn Văn A” trong bảng dữ liệu điểm thi.
Lời giải: Sử dụng hàm AVERAGEIF() với công thức: =AVERAGEIF(Cột tên học sinh, "Nguyễn Văn A", Cột điểm)
9. Sử Dụng Hàm DATE để Chuyển Đổi Dữ Liệu Sang Định Dạng Ngày
Bài tập: Cho bảng dữ liệu ngày sinh của nhân viên, hãy chuyển đổi dữ liệu sang định dạng ngày tháng năm.
Lời giải: Sử dụng hàm DATE() với công thức: =DATE(Năm, Tháng, Ngày)
10. Sử Dụng Hàm TIME để Chuyển Đổi Dữ Liệu Sang Định Dạng Giờ
Bài tập: Cho bảng dữ liệu giờ làm việc của nhân viên, hãy chuyển đổi dữ liệu sang định dạng giờ phút giây.
Lời giải: Sử dụng hàm TIME() với công thức: =TIME(Giờ, Phút, Giây)
Bài Tập Nâng Cao (11-20)
11. Tạo Pivot Table
Bài tập: Hãy tạo Pivot Table để phân tích doanh thu bán hàng theo sản phẩm, khu vực, thời gian.
Lời giải:
- Chọn dữ liệu cần tạo Pivot Table.
- Chọn tab “Insert” trên thanh công cụ.
- Chọn “PivotTable”.
- Kéo thả các trường dữ liệu vào các khu vực tương ứng trong Pivot Table.
12. Sử Dụng Hàm INDEX và MATCH để Tra Cứu Dữ Liệu
Bài tập: Cho bảng dữ liệu danh sách học sinh và bảng dữ liệu điểm thi. Hãy tra cứu điểm thi của mỗi học sinh dựa trên tên học sinh.
Lời giải: Sử dụng hàm INDEX() và MATCH() với công thức: =INDEX(Cột điểm, MATCH(Tên học sinh, Cột tên học sinh, 0))
13. Sử Dụng Hàm LEFT, RIGHT, MID để Trích Xuất Dữ Liệu
Bài tập: Cho bảng dữ liệu mã sản phẩm, hãy trích xuất 3 chữ số đầu tiên, 3 chữ số cuối cùng và 5 chữ số ở giữa của mã sản phẩm.
Lời giải:
- Trích xuất 3 chữ số đầu tiên:
=LEFT(Mã sản phẩm, 3) - Trích xuất 3 chữ số cuối cùng:
=RIGHT(Mã sản phẩm, 3) - Trích xuất 5 chữ số ở giữa:
=MID(Mã sản phẩm, 4, 5)
14. Sử Dụng Hàm TRIM để Loại Bỏ Khoảng Trống
Bài tập: Cho bảng dữ liệu tên học sinh có nhiều khoảng trống thừa, hãy loại bỏ khoảng trống thừa để chuẩn hóa dữ liệu.
Lời giải: Sử dụng hàm TRIM() với công thức: =TRIM(Tên học sinh)
15. Sử Dụng Hàm PROPER để Chỉnh Chữ Hoa
Bài tập: Cho bảng dữ liệu tên học sinh viết thường, hãy chỉnh sửa để tên học sinh viết hoa chữ cái đầu mỗi từ.
Lời giải: Sử dụng hàm PROPER() với công thức: =PROPER(Tên học sinh)
16. Sử Dụng Hàm CONCATENATE để Nối Chuỗi
Bài tập: Cho bảng dữ liệu tên học sinh và lớp học, hãy nối chuỗi để tạo thành cột “Họ và tên – Lớp”
Lời giải: Sử dụng hàm CONCATENATE() với công thức: =CONCATENATE(Tên học sinh, " - ", Lớp học)
17. Sử Dụng Hàm FIND để Tìm Vị Trí Của Ký Tự
Bài tập: Cho bảng dữ liệu email, hãy tìm vị trí của ký tự “@” trong địa chỉ email.
Lời giải: Sử dụng hàm FIND() với công thức: =FIND("@", Email)
18. Sử Dụng Hàm SUBSTITUTE để Thay Thế Ký Tự
Bài tập: Cho bảng dữ liệu địa chỉ, hãy thay thế ký tự “.” bằng ký tự “,” trong địa chỉ.
Lời giải: Sử dụng hàm SUBSTITUTE() với công thức: =SUBSTITUTE(Địa chỉ, ".", ",")
19. Sử Dụng Hàm LEN để Tính Độ Dài Chuỗi
Bài tập: Cho bảng dữ liệu số điện thoại, hãy tính độ dài của số điện thoại.
Lời giải: Sử dụng hàm LEN() với công thức: =LEN(Số điện thoại)
20. Sử Dụng Hàm UPPER và LOWER để Chuyển Đổi Chữ
Bài tập: Cho bảng dữ liệu tên học sinh, hãy chuyển đổi tên học sinh thành chữ hoa và chữ thường.
Lời giải:
- Chuyển đổi thành chữ hoa:
=UPPER(Tên học sinh) - Chuyển đổi thành chữ thường:
=LOWER(Tên học sinh)
Bài Tập Nâng Cao Hơn (21-30)
21. Sử Dụng Hàm SUMIFS để Tính Tổng Dữ Liệu Theo Nhiều Điều Kiện
Bài tập: Hãy tính tổng doanh thu của sản phẩm “A” trong khu vực “Hà Nội” trong bảng dữ liệu bán hàng.
Lời giải: Sử dụng hàm SUMIFS() với công thức: =SUMIFS(Cột doanh thu, Cột sản phẩm, "A", Cột khu vực, "Hà Nội")
22. Sử Dụng Hàm COUNTIFS để Đếm Số Lượng Dữ Liệu Theo Nhiều Điều Kiện
Bài tập: Hãy đếm số lượng học sinh đạt điểm giỏi (trên 8) và học lớp 12 trong bảng dữ liệu điểm thi.
Lời giải: Sử dụng hàm COUNTIFS() với công thức: =COUNTIFS(Cột điểm, ">8", Cột lớp học, "12")
23. Sử Dụng Hàm AVERAGEIFS để Tính Trung Bình Dữ Liệu Theo Nhiều Điều Kiện
Bài tập: Hãy tính điểm trung bình của học sinh nam đạt điểm giỏi (trên 8) trong bảng dữ liệu điểm thi.
Lời giải: Sử dụng hàm AVERAGEIFS() với công thức: =AVERAGEIFS(Cột điểm, Cột giới tính, "Nam", Cột điểm, ">8")
24. Sử Dụng Hàm AND và OR để Kết Hợp Các Điều Kiện
Bài tập: Hãy kiểm tra xem doanh thu của mỗi sản phẩm có đạt chỉ tiêu và doanh thu có cao hơn 100 triệu đồng hay không. Nếu cả hai điều kiện đều đúng, in ra “Đạt”, ngược lại in ra “Chưa đạt”.
Lời giải: Sử dụng hàm AND() hoặc OR() để kết hợp các điều kiện, với công thức: =IF(AND(Doanh thu >= Chỉ tiêu, Doanh thu > 100000000), "Đạt", "Chưa đạt")
25. Sử Dụng Hàm IFERROR để Xử Lý Lỗi
Bài tập: Khi sử dụng hàm VLOOKUP() để tra cứu thông tin lương, có trường hợp mã số nhân viên không tồn tại trong bảng dữ liệu lương, dẫn đến lỗi #N/A. Hãy sử dụng hàm IFERROR() để hiển thị thông báo “Không tìm thấy” thay vì lỗi #N/A.
Lời giải: Sử dụng hàm IFERROR() với công thức: =IFERROR(VLOOKUP(Mã số nhân viên, Bảng dữ liệu lương, Cột lương, FALSE), "Không tìm thấy")
26. Sử Dụng Hàm CHOOSE để Chọn Giá Trị Theo Số Thứ Tự
Bài tập: Cho bảng dữ liệu điểm thi, hãy hiển thị mức độ học lực của mỗi học sinh dựa trên điểm thi.
- Điểm >= 8: Giỏi
- Điểm >= 7: Khá
- Điểm >= 5: Trung bình
- Điểm < 5: Yếu
Lời giải: Sử dụng hàm CHOOSE() với công thức: =CHOOSE(MATCH(Điểm, {5;7;8}, 1), "Yếu", "Trung bình", "Khá", "Giỏi")
27. Sử Dụng Hàm DATEVALUE để Chuyển Đổi Chuỗi Sang Định Dạng Ngày
Bài tập: Cho bảng dữ liệu ngày sinh của nhân viên ở dạng chuỗi “dd/mm/yyyy”, hãy chuyển đổi sang định dạng ngày.
Lời giải: Sử dụng hàm DATEVALUE() với công thức: =DATEVALUE(Ngày sinh)
28. Sử Dụng Hàm TEXT để Định Dạng Dữ Liệu
Bài tập: Cho bảng dữ liệu số tiền, hãy định dạng số tiền theo dạng “##,###,### VND”.
Lời giải: Sử dụng hàm TEXT() với công thức: =TEXT(Số tiền, "#,##0,### VND")
29. Sử Dụng Hàm ROUND để Làm Tròn Số
Bài tập: Cho bảng dữ liệu giá bán, hãy làm tròn giá bán lên số nguyên gần nhất.
Lời giải: Sử dụng hàm ROUND() với công thức: =ROUND(Giá bán, 0)
30. Sử Dụng Hàm SUMPRODUCT để Tính Tổng Tích
Bài tập: Cho bảng dữ liệu số lượng bán hàng và giá bán, hãy tính tổng doanh thu của mỗi sản phẩm.
Lời giải: Sử dụng hàm SUMPRODUCT() với công thức: =SUMPRODUCT(Số lượng, Giá bán)
Bài Tập Nâng Cao (31-40)
31. Tạo Macro
Bài tập: Hãy tạo Macro để tự động định dạng bảng dữ liệu, tạo biểu đồ, in ấn báo cáo.
Lời giải:
- Chọn tab “Developer” trên thanh công cụ.
- Nhấn “Record Macro”.
- Thực hiện các thao tác cần tự động hóa.
- Nhấn “Stop Recording”.
32. Sử Dụng Hàm OFFSET để Di Chuyển Ô
Bài tập: Cho bảng dữ liệu điểm thi, hãy lấy điểm thi của học sinh ở hàng thứ 5, cột thứ 3.
Lời giải: Sử dụng hàm OFFSET() với công thức: =OFFSET(A1, 4, 2)
33. Sử Dụng Hàm INDIRECT để Tham Chiếu Ô Bằng Chuỗi
Bài tập: Cho bảng dữ liệu điểm thi, hãy tạo một ô nhập tên học sinh, sau đó sử dụng hàm INDIRECT() để lấy điểm thi của học sinh đó.
Lời giải: Sử dụng hàm INDIRECT() với công thức: =INDIRECT("C" & H1), trong đó H1 là ô nhập tên học sinh.
34. Sử Dụng Hàm ROW và COLUMN để Lấy Số Hàng Và Số Cột
Bài tập: Cho bảng dữ liệu điểm thi, hãy lấy số hàng và số cột của ô đang được chọn.
Lời giải:
- Lấy số hàng:
=ROW() - Lấy số cột:
=COLUMN()
35. Sử Dụng Hàm ADDRESS để Lấy Địa Chỉ Ô
Bài tập: Cho bảng dữ liệu điểm thi, hãy lấy địa chỉ ô của điểm thi cao nhất.
Lời giải: Sử dụng hàm ADDRESS() với công thức: =ADDRESS(ROW(MAX(Cột điểm)), COLUMN(MAX(Cột điểm)))
36. Sử Dụng Hàm MATCH để Tìm Vị Trí Của Giá Trị
Bài tập: Cho bảng dữ liệu điểm thi, hãy tìm vị trí của điểm 10 trong cột điểm.
Lời giải: Sử dụng hàm MATCH() với công thức: =MATCH(10, Cột điểm, 0)
37. Sử Dụng Hàm RANK để Xếp Hạng
Bài tập: Cho bảng dữ liệu điểm thi, hãy xếp hạng điểm thi của mỗi học sinh.
Lời giải: Sử dụng hàm RANK() với công thức: =RANK(Điểm, Cột điểm, 0)
38. Sử Dụng Hàm SMALL và LARGE để Lấy Giá Trị Nhỏ Nhất Và Lớn Nhất
Bài tập: Cho bảng dữ liệu điểm thi, hãy lấy điểm thi nhỏ nhất thứ 2 và điểm thi lớn nhất thứ 3.
Lời giải:
- Lấy điểm thi nhỏ nhất thứ 2:
=SMALL(Cột điểm, 2) - Lấy điểm thi lớn nhất thứ 3:
=LARGE(Cột điểm, 3)
39. Sử Dụng Hàm FREQUENCY để Tạo Bảng Tần Số
Bài tập: Cho bảng dữ liệu điểm thi, hãy tạo bảng tần số cho các điểm thi.
Lời giải: Sử dụng hàm FREQUENCY() với công thức: =FREQUENCY(Cột điểm, {5;7;8;10}), trong đó {5;7;8;10} là các điểm thi cần tính tần số.
40. Sử Dụng Hàm PERCENTILE để Tính Phần Trăm
Bài tập: Cho bảng dữ liệu điểm thi, hãy tính điểm thi ở vị trí percentile 25, 50, 75.
Lời giải: Sử dụng hàm PERCENTILE() với công thức: =PERCENTILE(Cột điểm, 0.25), =PERCENTILE(Cột điểm, 0.5), =PERCENTILE(Cột điểm, 0.75)
Bài Tập Nâng Cao (41-50)
41. Sử Dụng Hàm SUBTOTAL để Tính Tổng, Trung Bình Dữ Liệu Có Điều Kiện
Bài tập: Cho bảng dữ liệu điểm thi, hãy tính tổng điểm, điểm trung bình của học sinh có tên “Nguyễn Văn A”.
Lời giải: Sử dụng hàm SUBTOTAL() với công thức: =SUBTOTAL(9, Cột điểm), =SUBTOTAL(1, Cột điểm)
42. Sử Dụng Hàm AGGREGATE để Tính Tổng, Trung Bình Dữ Liệu Bỏ Qua Lỗi
Bài tập: Cho bảng dữ liệu điểm thi, có một số ô chứa lỗi #N/A. Hãy tính tổng điểm, điểm trung bình của học sinh bỏ qua các ô chứa lỗi.
Lời giải: Sử dụng hàm AGGREGATE() với công thức: =AGGREGATE(9, 6, Cột điểm), =AGGREGATE(1, 6, Cột điểm)
43. Sử Dụng Hàm HYPERLINK để Tạo Liên Kết
Bài tập: Cho bảng dữ liệu danh sách website, hãy tạo liên kết đến các website đó.
Lời giải: Sử dụng hàm HYPERLINK() với công thức: =HYPERLINK(Địa chỉ website, Tên website)
44. Sử Dụng Hàm GETPIVOTDATA để Lấy Dữ Liệu Từ Pivot Table
Bài tập: Cho Pivot Table về doanh thu bán hàng, hãy lấy doanh thu của sản phẩm “A” trong khu vực “Hà Nội”.
Lời giải: Sử dụng hàm GETPIVOTDATA() với công thức: =GETPIVOTDATA("Doanh thu", PivotTable, "Sản phẩm", "A", "Khu vực", "Hà Nội")
45. Sử Dụng Hàm SUMPRODUCT để Tính Tổng Dữ Liệu Theo Nhiều Điều Kiện
Bài tập: Cho bảng dữ liệu điểm thi, hãy tính tổng điểm của học sinh có tên “Nguyễn Văn A” và học lớp 12.
Lời giải: Sử dụng hàm SUMPRODUCT() với công thức: =SUMPRODUCT(--(Cột tên học sinh = "Nguyễn Văn A"), --(Cột lớp học = 12), Cột điểm)
46. Sử Dụng Hàm TRANSPOSE để Chuyển Dòng Sang Cột Và Ngược Lại
Bài tập: Cho bảng dữ liệu điểm thi, hãy chuyển đổi bảng dữ liệu từ dạng dòng sang cột.
Lời giải: Sử dụng hàm TRANSPOSE() với công thức: =TRANSPOSE(Cột điểm)
47. Sử Dụng Hàm LOOKUP để Tra Cứu Dữ Liệu Theo Vị Trí
Bài tập: Cho bảng dữ liệu điểm thi, hãy tra cứu điểm thi của học sinh ở hàng thứ 5.
Lời giải: Sử dụng hàm LOOKUP() với công thức: =LOOKUP(5, ROW(Cột điểm), Cột điểm)
48. Sử Dụng Hàm COUNTBLANK để Đếm Số Lượng Ô Trống
Bài tập: Cho bảng dữ liệu điểm thi, hãy đếm số lượng ô trống trong cột điểm.
Lời giải: Sử dụng hàm COUNTBLANK() với công thức: =COUNTBLANK(Cột điểm)
49. Sử Dụng Hàm ISBLANK để Kiểm Tra Ô Trống
Bài tập: Cho bảng dữ liệu điểm thi, hãy kiểm tra xem ô đang được chọn có phải là ô trống hay không. Nếu là ô trống, in ra “Trống”, ngược lại in ra “Không trống”.
Lời giải: Sử dụng hàm ISBLANK() với công thức: =IF(ISBLANK(Ô đang chọn), "Trống", "Không trống")
50. Sử Dụng Hàm ISNUMBER để Kiểm Tra Loại Dữ Liệu
Bài tập: Cho bảng dữ liệu điểm thi, hãy kiểm tra xem ô đang được chọn có phải là ô chứa số hay không. Nếu là ô chứa số, in ra “Số”, ngược lại in ra “Không phải số”.
Lời giải: Sử dụng hàm ISNUMBER() với công thức: =IF(ISNUMBER(Ô đang chọn), "Số", "Không phải số")
Kết Luận
50 bài tập Excel có lời giải được chia sẻ trên đây sẽ giúp bạn nâng cao kỹ năng sử dụng phần mềm này, từ cơ bản đến nâng cao. Hãy thử thực hành các bài tập này để củng cố kiến thức, trau dồi kỹ năng, và tự tin hơn khi làm việc với Excel.
FAQ
1. Làm sao để học Excel hiệu quả?
Để học Excel hiệu quả, bạn cần xác định mục tiêu học tập, lựa chọn tài liệu học phù hợp, thực hành thường xuyên, và ứng dụng kiến thức vào thực tế.
2. Có phần mềm nào hỗ trợ học Excel?
Có nhiều phần mềm hỗ trợ học Excel, ví dụ như Microsoft Excel, Google Sheets, LibreOffice Calc.
3. Nên học những hàm Excel nào?
Nên học những hàm Excel phổ biến và thường được sử dụng như SUM, AVERAGE, VLOOKUP, IF, COUNTIF, SUMIF, AVERAGEIF, DATE, TIME, TEXT, ROUND, SUMPRODUCT, INDEX, MATCH, CHOOSE, TRIM, PROPER, CONCATENATE, FIND, SUBSTITUTE, LEN, UPPER, LOWER, RANK, SMALL, LARGE, FREQUENCY, PERCENTILE, SUBTOTAL, AGGREGATE, HYPERLINK, GETPIVOTDATA, TRANSPOSE, LOOKUP, COUNTBLANK, ISBLANK, ISNUMBER.
4. Có sách nào hay về học Excel?
Có nhiều sách hay về học Excel, ví dụ như “Excel 2019 & 365: All-in-One For Dummies”, “Excel VBA Programming For Dummies”, “Excel Formulas and Functions For Dummies”.
5. Có khóa học Excel nào uy tín?
Có nhiều khóa học Excel uy tín trên các nền tảng học trực tuyến như Udemy, Coursera, edX.