Bài Tập Excel Tổng Hợp: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

Có thể bạn quan tâm: Bài Tập Các Hàm Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

Lời mở đầu

Microsoft Excel là một trong những công cụ phần mềm văn phòng mạnh mẽ và phổ biến nhất hiện nay, không chỉ được sử dụng trong môi trường doanh nghiệp mà còn trong giáo dục, nghiên cứu khoa học và thậm chí trong các hoạt động cá nhân. Để nắm vững Excel, việc thực hành qua bài tập Excel tổng hợp là điều không thể thiếu. Bài viết này sẽ cung cấp cho bạn một bộ tài liệu chi tiết, bao gồm các bài tập từ cơ bản đến nâng cao, kèm theo giải pháp, lời giải và các mẹo tối ưu hoá công việc. Hy vọng qua bộ bài tập này, người đọc sẽ:

1. Nắm vững các hàm cơ bản và nâng cao.
2. Hiểu rõ cách xử lý dữ liệu, tạo báo cáo và biểu đồ.
3. Áp dụng các kỹ thuật tự động hoá như PivotTable, Power Query và VBA.
4. Cải thiện tốc độ và độ chính xác trong công việc hàng ngày.

Có thể bạn quan tâm: Giải Bài Toán Vận Tải Bằng Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

Phần 1: Các Bài Tập Cơ Bản – Xây Dựng Nền Tảng Vững Vàng

Bài tập 1: Nhập dữ liệu và định dạng bảng

Mục tiêu:
– Nhập dữ liệu vào bảng tính.
– Áp dụng định dạng số, ngày tháng, màu nền, viền ô.

Yêu cầu:
1. Tạo một bảng “Danh sách nhân viên” gồm các cột: Mã NV, Họ và tên, Ngày sinh, Giới tính, Lương cơ bản.
2. Nhập dữ liệu mẫu cho 10 nhân viên.
3. Định dạng cột Ngày sinh dưới dạng dd/mm/yyyy.
4. Định dạng cột Lương cơ bản dưới dạng tiền tệ (VND).
5. Thêm viền cho toàn bộ bảng và tô màu nền cho tiêu đề.

Giải pháp:
– Sử dụng Format Cells (Ctrl+1) → Tab Number → chọn Date và Currency.
– Để tạo viền, chọn vùng dữ liệu → Home → Borders → All Borders.
– Để tô màu, chọn tiêu đề → Home → Fill Color.

Bài tập 2: Sử dụng hàm SUM, AVERAGE, MIN, MAX

Mục tiêu:
– Tính tổng, trung bình, giá trị nhỏ nhất và lớn nhất của cột lương.

Yêu cầu:
1. Ở dòng cuối bảng, tính Tổng lương bằng hàm SUM.
2. Tính Lương trung bình bằng AVERAGE.
3. Xác định Lương thấp nhất và Lương cao nhất bằng MIN và MAX.

Giải pháp:

=SUM(E2:E11) // Tổng lương
=AVERAGE(E2:E11) // Trung bình
=MIN(E2:E11) // Lương thấp nhất
=MAX(E2:E11) // Lương cao nhất

• Đặt công thức ở các ô tương ứng dưới bảng và dùng AutoSum (Alt+=) để nhanh chóng.

Bài tập 3: Hàm IF – Kiểm tra điều kiện

Mục tiêu:
– Xây dựng công thức điều kiện để phân loại nhân viên dựa trên lương.

Yêu cầu:
1. Thêm cột Phân loại (cột F).
2. Nếu lương ≥ 15,000,000 → “Cao”. Nếu lương từ 10,000,000 đến <15,000,000 → “Trung bình”. Ngược lại → “Thấp”.

Giải pháp:

=IF(E2>=15000000,"Cao",IF(E2>=10000000,"Trung bình","Thấp"))

• Kéo công thức xuống hết danh sách.

Bài tập 4: Định dạng có điều kiện (Conditional Formatting)

Mục tiêu:
– Tô màu tự động cho các ô lương dựa trên mức độ.

Yêu cầu:
1. Đối với cột Lương cơ bản, sử dụng Conditional Formatting để:
– Lương ≥ 15,000,000 → nền xanh lá.
– Lương < 10,000,000 → nền đỏ.

Giải pháp:
– Chọn cột E → Home → Conditional Formatting → New Rule → Use a formula.
– Công thức cho màu xanh: =$E2>=15000000.
– Công thức cho màu đỏ: =$E2<10000000.

Bài tập 5: Sắp xếp và lọc dữ liệu (Sort & Filter)

Mục tiêu:
– Thực hiện sắp xếp tăng/giảm và lọc dữ liệu theo tiêu chí.

Yêu cầu:
1. Sắp xếp danh sách nhân viên theo Lương cơ bản giảm dần.
2. Lọc để chỉ hiển thị những nhân viên có Phân loại = "Cao".

Giải pháp:
– Sort: Chọn bảng → Data → Sort → Chọn Lương cơ bản → Order: Largest to Smallest.
– Filter: Bật Filter (Data → Filter) → Ở cột Phân loại → Chọn “Cao”.

Có thể bạn quan tâm: Bài Toán Solver Trong Excel: Khám Phá, Ứng Dụng Và Các Bước Thực Hiện Chi Tiết

Phần 2: Bài Tập Trung Cấp – Xử Lý Dữ Liệu và Tạo Báo Cáo

Bài tập 6: Tìm kiếm và tra cứu – VLOOKUP & HLOOKUP

Mục tiêu:
– Sử dụng hàm tra cứu để lấy thông tin nhân viên dựa trên mã.

Yêu cầu:
1. Tạo một sheet “Tra cứu”.
2. Nhập một ô để người dùng nhập Mã NV.
3. Sử dụng VLOOKUP để hiện Họ và tên, Ngày sinh, Lương tương ứng.

Giải pháp:
Giả sử ô nhập mã là A2:

=VLOOKUP(A2, DanhSachNV!$A$2:$E$11, 2, FALSE) // Họ và tên
=VLOOKUP(A2, DanhSachNV!$A$2:$E$11, 3, FALSE) // Ngày sinh
=VLOOKUP(A2, DanhSachNV!$A$2:$E$11, 5, FALSE) // Lương

• Đảm bảo dữ liệu trong DanhSachNV được sắp xếp không cần thiết vì dùng FALSE (tìm chính xác).

Bài tập 7: Hàm INDEX-MATCH – Tra cứu linh hoạt

Mục tiêu:
– Thay thế VLOOKUP bằng cách kết hợp INDEX và MATCH để tra cứu trái và phải.

Yêu cầu:
– Tương tự bài 6, nhưng sử dụng công thức INDEX-MATCH.

Giải pháp:

=INDEX(DanhSachNV!$B$2:$B$11, MATCH(A2, DanhSachNV!$A$2:$A$11, 0)) // Họ và tên
=INDEX(DanhSachNV!$C$2:$C$11, MATCH(A2, DanhSachNV!$A$2:$A$11, 0)) // Ngày sinh
=INDEX(DanhSachNV!$E$2:$E$11, MATCH(A2, DanhSachNV!$A$2:$A$11, 0)) // Lương

• Ưu điểm: không giới hạn vị trí cột tra cứu, tốc độ nhanh hơn với dữ liệu lớn.

Bài tập 8: PivotTable – Tổng hợp và phân tích nhanh

Mục tiêu:
– Tạo PivotTable để tổng hợp lương theo phòng ban (giả sử thêm cột Phòng ban).

Yêu cầu:
1. Thêm cột Phòng ban vào bảng nhân viên (ví dụ: “Kinh doanh”, “Kế toán”, “IT”).
2. Tạo PivotTable:
– Rows: Phòng ban.
– Values: Lương cơ bản (Sum).
– Thêm Phân loại vào Columns để xem phân bố mức lương.

Giải pháp:
– Chọn bảng → Insert → PivotTable → Chọn vị trí mới.
– Kéo các trường vào khu vực tương ứng.
– Định dạng số tiền trong PivotTable bằng cách Value Field Settings → Number Format → Currency.

Bài tập 9: Sử dụng Power Query – Nhập và làm sạch dữ liệu

Có thể bạn quan tâm: Bài Tập Excel Pivot Table: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

Mục tiêu:
– Nhập dữ liệu từ file CSV, loại bỏ các bản ghi trùng lặp và lỗi.

Yêu cầu:
1. Tải file employees.csv (có 5000 bản ghi).
2. Loại bỏ các hàng có Mã NV trùng lặp.
3. Đổi định dạng ngày sinh sang dd/mm/yyyy.
4. Lưu lại thành bảng trong Excel.

Giải pháp:
– Data → Get Data → From Text/CSV → Chọn file.
– Trong Power Query Editor:
– Home → Remove Rows → Remove Duplicates → Chọn cột Mã NV.
– Chọn cột Ngày sinh → Transform → Data Type → Date.
– Home → Close & Load → Đưa dữ liệu vào sheet mới.

Bài tập 10: Tạo biểu đồ động (Dynamic Chart)

Mục tiêu:
– Biểu đồ cột hiển thị tổng lương theo phòng ban, tự động cập nhật khi thêm dữ liệu mới.

Yêu cầu:
1. Dựa trên bảng tổng hợp (PivotTable) ở Bài 8, tạo biểu đồ cột.
2. Đảm bảo biểu đồ tự động mở rộng khi thêm phòng ban mới.

Giải pháp:
– Chọn PivotTable → Insert → Column Chart.
– Đối với dữ liệu không phải Pivot, tạo Table (Ctrl+T) → Biểu đồ sẽ tự cập nhật khi bảng mở rộng.

Phần 3: Bài Tập Nâng Cao – Tự Động Hoá và Tối Ưu Hóa

Bài tập 11: Hàm ARRAY (Dynamic Array) – FILTER, SORT, UNIQUE

Mục tiêu:
– Sử dụng các hàm mảng động để lọc và sắp xếp dữ liệu không cần VBA.

Yêu cầu:
1. Tạo danh sách duy nhất các Phòng ban từ bảng nhân viên.
2. Lọc ra các nhân viên có lương > 12,000,000 và sắp xếp giảm dần.

Giải pháp:

=UNIQUE(DanhSachNV!F2:F1000) // Danh sách phòng ban duy nhất
=SORT(FILTER(DanhSachNV!A2:E1000, DanhSachNV!E2:E1000>12000000), 5, -1) // Lọc và sắp xếp

• Hàm FILTER trả về mảng các hàng thỏa mãn, SORT sắp xếp dựa trên cột 5 (Lương) giảm dần.

Bài tập 12: Hàm XLOOKUP – Thay thế VLOOKUP hiện đại

Mục tiêu:
– Sử dụng XLOOKUP để tra cứu hai chiều, hỗ trợ giá trị mặc định khi không tìm thấy.

Yêu cầu:
– Tìm Họ và tên và Lương dựa trên Mã NV, nếu không có trả về “Không tồn tại”.

Giải pháp:

=XLOOKUP(A2, DanhSachNV!$A$2:$A$1000, DanhSachNV!$B$2:$B$1000, "Không tồn tại")
=XLOOKUP(A2, DanhSachNV!$A$2:$A$1000, DanhSachNV!$E$2:$E$1000, "Không tồn tại")

• XLOOKUP tự động tìm ở cả chiều dọc và ngang, không cần sắp xếp dữ liệu.

Bài tập 13: VBA – Tự động tạo báo cáo tháng

Mục tiêu:
– Viết macro VBA để tổng hợp lương tháng, tạo file PDF và gửi email (giả sử có Outlook).

Yêu cầu:
1. Khi người dùng nhấn nút “Tạo báo cáo”, macro sẽ:
– Lọc dữ liệu tháng hiện tại.
– Tính tổng lương, số nhân viên, lương trung bình.
– Đặt kết quả vào sheet “Báo cáo”.
– Xuất sheet này thành PDF tên BaoCao_ThangYYMM.pdf.
– Gửi email đính kèm PDF tới hr@company.com.

Giải pháp (Mã VBA):

Sub TaoBaoCaoThang() Dim wsData As Worksheet, wsReport As Worksheet Dim rng As Range, lastRow As Long Dim monthNow As String, filePath As String Dim outlookApp As Object, mailItem As Object Set wsData = ThisWorkbook.Sheets("DanhSachNV") Set wsReport = ThisWorkbook.Sheets("BaoCao") monthNow = Format(Date, "mm/yyyy") 'Xóa báo cáo cũ wsReport.Cells.Clear 'Lọc dữ liệu tháng hiện tại (giả sử cột G là ngày vào làm) lastRow = wsData.Cells(wsData.Rows.Count, "A").End(xlUp).Row Set rng = wsData.Range("A1:E" & lastRow) rng.AutoFilter Field:=7, Criteria1:=monthNow 'Copy dữ liệu lọc sang báo cáo rng.SpecialCells(xlCellTypeVisible).Copy Destination:=wsReport.Range("A2") wsData.AutoFilterMode = False 'Tính toán tổng, trung bình Dim total As Double, avg As Double, countEmp As Long With wsReport countEmp = Application.WorksheetFunction.CountA(.Range("A2:A" & .Cells(.Rows.Count, "A").End(xlUp).Row)) total = Application.WorksheetFunction.Sum(.Range("E2:E" & .Cells(.Rows.Count, "E").End(xlUp).Row)) avg = total / countEmp .Range("G2").Value = "Tổng lương" .Range("H2").Value = total .Range("G3").Value = "Số nhân viên" .Range("H3").Value = countEmp .Range("G4").Value = "Lương trung bình" .Range("H4").Value = avg End With 'Xuất PDF filePath = ThisWorkbook.Path & "\BaoCao_" & Format(Date, "YYMM") & ".pdf" wsReport.ExportAsFixedFormat Type:=xlTypePDF, Filename:=filePath, Quality:=xlQualityStandard 'Gửi email (Outlook) On Error Resume Next Set outlookApp = GetObject(, "Outlook.Application") If outlookApp Is Nothing Then Set outlookApp = CreateObject("Outlook.Application") On Error GoTo 0 Set mailItem = outlookApp.CreateItem(0) With mailItem .To = "hr@company.com" .Subject = "Báo cáo lương tháng " & Format(Date, "mm/yyyy") .Body = "Kính gửi bộ phận nhân sự," & vbCrLf & vbCrLf & _ "Đính kèm là báo cáo lương tháng " & Format(Date, "mm/yyyy") & "." & vbCrLf & vbCrLf & _ "Trân trọng," .Attachments.Add filePath .Send End With MsgBox "Báo cáo đã được tạo và gửi thành công!", vbInformation
End Sub

• Đặt macro này vào ThisWorkbook hoặc Module và tạo Button (Insert → Shapes → Assign Macro).

Bài tập 14: Data Validation & Drop-down List

Mục tiêu:
– Giới hạn nhập liệu và tạo danh sách thả xuống cho cột Giới tính, Phòng ban.

Yêu cầu:
1. Tạo danh sách tĩnh cho Giới tính = {Nam, Nữ, Khác}.
2. Tạo danh sách động cho Phòng ban dựa trên cột đã có (Unique).

Giải pháp:
– Giới tính: Chọn cột D → Data → Data Validation → List → Source: Nam,Nữ,Khác.
– Phòng ban động:
– Đặt công thức =UNIQUE(DanhSachNV!F2:F1000) vào một vùng tên DanhSachPhongBan.
– Chọn cột F → Data Validation → List → Source: =DanhSachPhongBan.

Bài tập 15: Sử dụng hàm TEXT, CONCAT, TEXTJOIN để tạo mã nhân viên tự động

Mục tiêu:
– Tự động tạo mã nhân viên dựa trên tên, ngày sinh và số thứ tự.

Yêu cầu:
1. Ở cột Mã NV (A), công thức tạo mã:
– Định dạng: NV-YYMMDD-XXX trong đó YYMMDD là ngày sinh, XXX là số thứ tự (001, 002,…).

Giải pháp:
Giả sử ngày sinh ở cột C, và dữ liệu bắt đầu ở hàng 2:

="NV-" & TEXT(C2,"yymmdd") & "-" & TEXT(ROW(A2)-1,"000")

• Khi sao chép xuống, ROW sẽ tăng dần, tạo số thứ tự tự động.

Tổng kết

Qua 1500+ chữ này, chúng ta đã trải qua một hành trình đầy đủ từ các thao tác nhập liệu, định dạng, hàm cơ bản, tới những kỹ năng phân tích dữ liệu nâng cao như PivotTable, Power Query, hàm mảng động, và cuối cùng là tự động hoá bằng VBA. Khi thực hành bài tập Excel tổng hợp này, bạn sẽ:

• Nắm vững các công cụ chuẩn của Excel.
• Phát triển tư duy xử lý dữ liệu nhanh, chính xác.
• Tự tin tạo báo cáo chuyên nghiệp, hỗ trợ quyết định kinh doanh.
• Tiết kiệm thời gian nhờ tự động hoá và giảm thiểu lỗi nhập liệu.

Lời khuyên cuối cùng:
– Luôn lưu bản sao (backup) trước khi chạy macro.
– Thường xuyên cập nhật kiến thức vì Microsoft liên tục thêm tính năng mới (ví dụ: Dynamic Arrays, LET, LAMBDA).
– Tham gia cộng đồng (Reddit r/excel, Microsoft Tech Community) để học hỏi các mẹo thực tế.

Chúc bạn thành công trong việc làm chủ Excel và áp dụng những kỹ năng này vào công việc và dự án cá nhân!

Tiến sĩ Lê Thống Nhất

Tiến sĩ Lê Thống Nhất sinh năm 1955 tại Nghệ An, là nhà giáo, nhà báo và doanh nhân trong lĩnh vực giáo dục. Ông từng giảng dạy Toán học tại Trường THPT Chuyên Phan Bội Châu và đảm nhiệm nhiều vai trò trong ngành giáo dục, từ viết sách, biên soạn tài liệu đến sáng lập các dự án hỗ trợ học tập trực tuyến.

Ông là người sáng lập BigSchool – nền tảng giáo dục trực tuyến kết nối giáo viên và học sinh cả nước. Ngoài ra, ông còn nổi tiếng với vai trò tác giả, diễn giả truyền cảm hứng và khách mời trên nhiều chương trình truyền hình, trong đó có “Ai là triệu phú” trên truyền hình VTV.

Trong suốt sự nghiệp, Tiến sĩ Lê Thống Nhất đã nhận nhiều giải thưởng giáo dục và được biết đến như một người luôn đổi mới, ứng dụng công nghệ vào dạy và học, góp phần lan tỏa tri thức và niềm say mê học tập cho thế hệ trẻ.