Excel là một trong những công cụ văn phòng mạnh mẽ và linh hoạt nhất hiện nay. Dù bạn là một nhân viên kế toán, nhà phân tích dữ liệu, nhà quản lý dự án hay một doanh nhân muốn tối ưu hoá quy trình làm việc, việc nắm vững các tính năng nâng cao của Excel sẽ giúp bạn tiết kiệm thời gian, giảm thiểu sai sót và tạo ra những báo cáo chuyên nghiệp, thuyết phục. Bài viết dưới đây sẽ cung cấp cho bạn một hướng dẫn chi tiết, từ các công thức phức tạp, các hàm mảng, PivotTable, Power Query, Power Pivot, cho tới việc tự động hoá quy trình bằng VBA (Visual Basic for Applications). Hãy cùng khám phá và nâng tầm kỹ năng Excel của mình nhé!
Có thể bạn quan tâm: Số Thứ Tự Tự Động Trong Excel: Cách Tạo, Ứng Dụng Và Mẹo Thực Tiễn
1. Tổng Quan Về Các Công Cụ Nâng Cao Trong Excel
Trước khi đi sâu vào từng phần, chúng ta cần nắm rõ các nhóm công cụ mà Excel cung cấp:
| Nhóm công cụ | Mô tả ngắn gọn | Ứng dụng thực tế |
|---|---|---|
| Hàm & Công thức | Các hàm tính toán, logic, tìm kiếm, thời gian… | Tính toán tài chính, xử lý dữ liệu chuỗi, phân tích thời gian |
| Mảng & Hàm mảng | Cho phép thực hiện nhiều phép tính đồng thời trên một dải ô | Tính tổng có điều kiện, lọc dữ liệu phức tạp |
| PivotTable & PivotChart | Tổng hợp, phân tích dữ liệu lớn nhanh chóng | Báo cáo bán hàng, phân tích chi phí |
| Power Query (Get & Transform) | Nhập, làm sạch, biến đổi dữ liệu từ nhiều nguồn | Kết hợp dữ liệu từ các file CSV, DB, web |
| Power Pivot & Data Model | Xây dựng mô hình dữ liệu quan hệ, DAX (Data Analysis Expressions) | Phân tích đa chiều, KPI |
| VBA & Macro | Tự động hoá các tác vụ lặp lại, tạo giao diện người dùng | Tự động tạo báo cáo, gửi email, nhập dữ liệu tự động |
| Power BI Desktop (liên kết) | Kết nối Excel với Power BI để tạo dashboard | Trực quan hoá dữ liệu doanh nghiệp |
Trong phần tiếp theo, chúng ta sẽ đi sâu vào từng công cụ, cung cấp ví dụ thực tế và hướng dẫn cách áp dụng.
Có thể bạn quan tâm: Cách Đặt Tên Vùng Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao
2. Các Hàm và Công Thức Phức Tạp
2.1. Hàm LỌC (FILTER) và SẮP XẾP (SORT)
Excel 365/2026 cung cấp hàm FILTER và SORT giúp lọc và sắp xếp dữ liệu mà không cần dùng tới công cụ Advanced Filter.
Ví dụ: Giả sử bảng Bán hàng có các cột: Ngày, Sản phẩm, Khu vực, Doanh thu. Bạn muốn lấy danh sách các giao dịch trong khu vực “Hà Nội” và sắp xếp theo doanh thu giảm dần.
=FILTER( SORT( Bán_hàngDoanh thu, -1 // -1 để sắp xếp giảm dần ), Bán_hàngKhu vực="Hà Nội"
)
Kết quả sẽ trả về một mảng các giá trị doanh thu thỏa mãn, đã được sắp xếp giảm dần.
2.2. Hàm XÁC ĐỊNH ĐỊNH DẠNG (LET) và LAMBDA
LET giúp bạn đặt tên cho các biểu thức trung gian, giảm lặp lại và tăng tốc tính toán.
=LET( giá_trị, A2:A100, trung_bình, AVERAGE(giá_trị), MAX(giá_trị) - trung_bình
)
LAMBDA cho phép tạo hàm tùy chỉnh ngay trong Excel mà không cần VBA.
= LAMBDA(x, y, x^2 + y^2)(3, 4) // Kết quả 25
Bạn có thể lưu hàm LAMBDA dưới dạng “Tên hàm” trong Formulas > Name Manager để tái sử dụng.
2.3. Hàm XỬ LÝ CHUỖI MẠNH MẼ
| Hàm | Mô tả | Ví dụ |
|---|---|---|
| TEXTJOIN | Nối nhiều chuỗi với dấu phân cách, bỏ qua ô trống | =TEXTJOIN(", ",TRUE, A1:A5) |
| REGEXMATCH, REGEXEXTRACT, REGEXREPLACE (Excel 365) | Tìm kiếm, trích xuất, thay thế bằng biểu thức chính quy | =REGEXEXTRACT(A2, "\d{4}") để lấy năm |
| MID, LEFT, RIGHT | Lấy một phần chuỗi | =MID(A2,3,5) |
2.4. Hàm TÌM KIẾM NÂNG CAO: XLOOKUP & XMATCH
XLOOKUP thay thế VLOOK/HLOOK, hỗ trợ tìm kiếm theo chiều ngang hoặc dọc, trả về mặc định nếu không tìm thấy.
=XLOOKUP("Sản phẩm A", B2:B100, C2:C100, "Không tìm thấy")
XMATCH trả về vị trí (số thứ tự) của giá trị trong mảng.
=XMATCH("Hà Nội", Khu_vực!A:A, 0, -1) // tìm vị trí cuối cùng
Có thể bạn quan tâm: Hàm Xác Suất Trong Excel: Cách Tính, Ứng Dụng Và Thực Hành Chi Tiết
3. Làm Việc Với Hàm Mảng (Dynamic Array)
Kể từ Excel 365, các hàm mảng đã trở nên “động” – kết quả sẽ tự động “đổ” ra các ô lân cận. Dưới đây là một số công thức mảng phổ biến.
3.1. SUMIFS + FILTER
Bạn muốn tính tổng doanh thu của các sản phẩm “A” trong tháng 1.
=SUM(FILTER(Doanh_thu, (Sản_phẩm="A")(Tháng=1)))
3.2. UNIQUE + SORT
Lấy danh sách các khu vực duy nhất, sắp xếp alphabet.
=SORT(UNIQUE(Khu_vực!A2:A200))
3.3. SEQUENCE
Tạo dãy số tự động, hữu ích cho việc tạo bảng tính mẫu.
=SEQUENCE(10,1,1,1) // 1-10 dọc
3.4. Xử lý điều kiện phức tạp với MAP và REDUCE
=REDUCE(0, A2:A10, LAMBDA(acc, x, acc + IF(x>100, x, 0))) // Tổng các giá trị >100
4. PivotTable và PivotChart: Phân Tích Dữ Liệu Nhanh
4.1. Tạo PivotTable
- Chọn bất kỳ ô nào trong bảng dữ liệu.
- Vào Insert > PivotTable.
- Chọn nguồn dữ liệu (có thể là bảng hoặc Data Model) và vị trí đặt PivotTable.
4.2. Các trường thường dùng
| Khu vực | Sản phẩm | Tháng | Doanh thu |
|---|---|---|---|
- Rows: Khu vực → Tháng
- Columns: Sản phẩm
- Values: Tổng Doanh thu (Sum)
- Filters: Năm
4.3. Tính toán tùy chỉnh với Calculated Field
Nếu muốn tính lợi nhuận = Doanh thu – Chi phí, bạn tạo Calculated Field:
- Click vào PivotTable Analyze > Fields, Items & Sets > Calculated Field.
- Đặt tên “Lợi nhuận”.
- Công thức:
=Doanh_thu - Chi_phi.
4.4. PivotChart
Sau khi tạo PivotTable, chọn PivotTable Analyze > PivotChart để tạo biểu đồ tương tác. Bạn có thể chọn loại biểu đồ: Column, Line, Pie… và tùy chỉnh màu sắc.
4.5. Slicer và Timeline
- Slicer: Thêm bộ lọc trực quan cho các trường như Khu vực, Sản phẩm.
- Timeline: Bộ lọc thời gian cho trường ngày, tháng, năm.
5. Power Query (Get & Transform)
Power Query là công cụ mạnh mẽ để nhập, làm sạch và biến đổi dữ liệu mà không cần viết mã.
5.1. Nhập Dữ Liệu Từ Nhiều Nguồn
- File > CSV/Excel: Nhập file.
- Data > Get Data > From Database: Kết nối SQL Server, Access, Oracle.
- From Web: Lấy dữ liệu bảng từ trang web.
- From Folder: Nhập tất cả file trong một thư mục (hữu ích khi có nhiều file hàng tháng).
5.2. Các bước biến đổi cơ bản
| Bước | Mô tả | Thao tác |
|---|---|---|
| Remove Columns | Xóa cột không cần thiết | Right-click > Remove |
| Filter Rows | Lọc dữ liệu | Filter icon |
| Split Column | Tách cột (ví dụ: tách ngày/tháng) | Split Column > By Delimiter |
| Pivot/Unpivot | Chuyển đổi dạng dữ liệu | Transform > Pivot Column / Unpivot Columns |
| Group By | Tổng hợp dữ liệu | Home > Group By |
| Merge Queries | Kết hợp 2 bảng (JOIN) | Home > Merge Queries |
| Append Queries | Nối các bảng (UNION) | Home > Append Queries |
5.3. Mẫu Power Query thực tế
Kịch bản: Bạn có 12 file CSV, mỗi file là doanh thu tháng. Bạn muốn gộp lại thành một bảng tổng hợp, loại bỏ các dòng trống, và tính tổng doanh thu theo khu vực.
Có thể bạn quan tâm: Cách Giữ Hàng Cố Định Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao
let Source = Folder.Files("C:\Data\DoanhThu\"), #"Added Custom" = Table.AddColumn(Source, "Custom", each Csv.Document(File.Contents(Folder Path & Name),Delimiter=",", Columns=5, Encoding=1252, QuoteStyle=QuoteStyle.None)), #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Ngày","Khu vực","Sản phẩm","Doanh thu","Chi phí"}, {"Ngày","Khu vực","Sản phẩm","Doanh thu","Chi phí"}), #"Removed Empty" = Table.SelectRows(#"Expanded Custom", each (Doanh thu <> null)), #"Changed Type" = Table.TransformColumnTypes(#"Removed Empty",{{"Doanh thu", type number}, {"Chi phí", type number}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Khu vực"}, {{"Tổng Doanh thu", each List.Sum(Doanh thu), type number}})
in #"Grouped Rows"
Sau khi hoàn thành, nhấn Close & Load để đưa dữ liệu vào Excel.
6. Power Pivot & Data Model: Phân Tích Đa Chiều
Power Pivot cho phép tạo mô hình dữ liệu quan hệ, sử dụng ngôn ngữ DAX để tính toán phức tạp.
6.1. Kích hoạt Power Pivot
- File > Options > Add-ins → Manage: COM Add-ins → Go → Check Microsoft Power Pivot for Excel.
6.2. Tạo Data Model
- Khi nhập dữ liệu qua Power Query, tick Add this data to the Data Model.
- Hoặc trong Excel, chọn Power Pivot > Manage và Add to Data Model.
6.3. Quan hệ (Relationships)
Giả sử bạn có 2 bảng: Bán_hàng (với Mã_Sản_phẩm) và Sản_phẩm (với Mã_Sản_phẩm, Nhóm). Tạo quan hệ 1-nhiều giữa chúng.
- Diagram View → Drag
Mã_Sản_phẩmtừ bảng Bán_hàng tới bảng Sản_phẩm.
6.4. Các hàm DAX cơ bản
| Hàm DAX | Mô tả | Ví dụ |
|---|---|---|
| CALCULATE | Thay đổi ngữ cảnh tính toán | CALCULATE(SUM(Bán_hàngDoanh thu), Sản_phẩmNhóm="Điện tử") |
| SUMX | Tính tổng của một biểu thức trên mỗi dòng | SUMX(Bán_hàng, Bán_hàngDoanh thu Bán_hàngHệ_số) |
| FILTER | Lọc bảng | FILTER(ALL(Bán_hàng), Bán_hàngTháng=1) |
| RELATED | Lấy giá trị từ bảng liên quan | RELATED(Sản_phẩmNhóm) |
| TIMEINTELLIGENCE (TOTALYTD, SAMEPERIODLASTYEAR) | Tính toán thời gian | TOTALYTD(SUM(Bán_hàngDoanh thu), Bán_hàngNgày) |
6.5. PivotTable dựa trên Data Model
Sau khi mô hình dữ liệu đã sẵn sàng, tạo PivotTable và chọn Use this workbook’s Data Model. Bạn có thể kéo các trường từ nhiều bảng khác nhau, thực hiện tính toán DAX ngay trong PivotTable.
7. VBA và Macro: Tự Động Hóa Các Tác Vụ Lặp Lại
7.1. Ghi Macro Cơ Bản
- View > Macros > Record Macro.
- Đặt tên (không có dấu cách) và chọn Store macro in This Workbook.
- Thực hiện các thao tác muốn tự động hoá (ví dụ: định dạng bảng, chèn công thức).
- Dừng ghi.
Sau khi ghi, bạn có thể View > Macros > Edit để xem mã VBA.
7.2. Ví dụ: Tự Động Tạo Báo Cáo Hàng Tháng
Sub TaoBaoCaoThang() Dim wsSrc As Worksheet, wsDst As Worksheet Dim lastRow As Long, i As Long Dim thang As String Set wsSrc = ThisWorkbook.Sheets("Dulieu") Set wsDst = ThisWorkbook.Sheets.Add(After:=Sheets(Sheets.Count)) wsDst.Name = "BaoCao_" & Format(Date, "yyyymm") ' Sao chép tiêu đề wsSrc.Rows(1).Copy Destination:=wsDst.Rows(1) ' Lọc dữ liệu tháng hiện tại thang = Format(Date, "mm") lastRow = wsSrc.Cells(wsSrc.Rows.Count, "A").End(xlUp).Row For i = 2 To lastRow If Format(wsSrc.Cells(i, "A").Value, "mm") = thang Then wsSrc.Rows(i).Copy Destination:=wsDst.Cells(wsDst.Rows.Count, "A").End(xlUp).Offset(1) End If Next i ' Định dạng bảng With wsDst .ListObjects.Add(xlSrcRange, .Range("A1").CurrentRegion, , xlYes).Name = "tblBaoCao" .ListObjects("tblBaoCao").TableStyle = "TableStyleMedium9" End With MsgBox "Báo cáo tháng " & thang & " đã được tạo thành công!", vbInformation
End Sub
7.3. Gửi Email Tự Động với Outlook
Sub GuiMailBaoCao() Dim OutApp As Object, OutMail As Object Dim wb As Workbook, filePath As String Set wb = ThisWorkbook filePath = wb.Path & "\" & wb.Name Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(0) With OutMail .To = "kehoach@congty.com" .CC = "" .Subject = "Báo cáo doanh thu tháng " & Format(Date, "mm/yyyy") .Body = "Kính gửi, " & vbCrLf & vbCrLf & _ "Đính kèm là báo cáo doanh thu tháng hiện tại." & vbCrLf & vbCrLf & _ "Trân trọng." .Attachments.Add filePath .Display 'Hoặc .Send để gửi ngay End With Set OutMail = Nothing Set OutApp = Nothing
End Sub
7.4. Tạo Form UserForm Để Nhập Dữ Liệu
- VBA Editor > Insert > UserForm.
- Thêm các TextBox, ComboBox, CommandButton.
- Viết mã để ghi dữ liệu vào bảng.
Private Sub btnLuu_Click() Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Dulieu") Dim nextRow As Long nextRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1 ws.Cells(nextRow, 1).Value = Me.txtNgay.Value ws.Cells(nextRow, 2).Value = Me.txtKhuVuc.Value ws.Cells(nextRow, 3).Value = Me.txtSanPham.Value ws.Cells(nextRow, 4).Value = Me.txtDoanhThu.Value MsgBox "Đã lưu dữ liệu!", vbInformation Me.Hide
End Sub
7.5. Bảo Mật Macro
- File > Options > Trust Center > Trust Center Settings > Macro Settings.
- Chọn Disable all macros with notification hoặc Digital Signature để bảo vệ mã.
8. Kỹ Thuật Tối Ưu Hóa & Xử Lý Lỗi
8.1. Tối ưu công thức
- Tránh dùng volatile functions như
NOW(),TODAY(),OFFSET(),INDIRECT()nếu không cần thiết – chúng làm chậm tính toán. - Sử dụng Table References (
@Column) thay vì phạm vi cố định để công thức tự động mở rộng. - Khi dùng SUMIFS, COUNTIFS, cố gắng giảm số điều kiện nếu có thể.
8.2. Kiểm tra lỗi công thức
- Formulas > Error Checking.
- Sử dụng hàm IFERROR(value, alternative) để tránh hiển thị
#DIV/0!.
=IFERROR(A2/B2, 0)
8.3. Kiểm tra dữ liệu trùng lặp
- Data > Remove Duplicates.
- Hoặc dùng công thức COUNTIF để đánh dấu.
=IF(COUNTIF($A$2:$A$1000, A2)>1, "Trùng", "")
8.4. Giới hạn kích thước tệp
- Xóa các pivot cache không sử dụng:
PivotTable Options > Data > Clear All. - Loại bỏ format conditional không cần thiết.
- Sử dụng Power Query để nén dữ liệu trước khi tải vào Excel.
9. Các Bước Thực Hành – Dự Án Mini: Xây Dựng Dashboard Doanh Thu
Bước 1: Thu thập dữ liệu
- Nhập 3 file CSV (tháng 1‑3) qua Power Query > From Folder.
- Thực hiện làm sạch: xóa cột không cần, chuyển kiểu dữ liệu, ghép ngày.
Bước 2: Tạo Data Model
- Thêm các bảng:
Bán_hàng,Sản_phẩm,Khu_vực. - Tạo quan hệ:
Mã_Sản_phẩm,Mã_Khu_vực.
Bước 3: Tính toán DAX
- Total Sales:
Total Sales = SUM(Bán_hàngDoanh thu) - YoY Growth:
YoY Growth = DIVIDE(Total Sales - CALCULATE(Total Sales, SAMEPERIODLASTYEAR(Bán_hàngNgày)), CALCULATE(Total Sales, SAMEPERIODLASTYEAR(Bán_hàngNgày)))
Bước 4: PivotTable + Slicer
- Tạo PivotTable từ Data Model.
- Thêm slicer cho
Năm,Khu vực,Nhóm sản phẩm.
Bước 5: PivotChart
- Chọn biểu đồ cột cho “Doanh thu theo tháng”.
- Thêm đường line cho “YoY Growth”.
Bước 6: Tự động cập nhật
- Ghi macro để refresh tất cả PivotTables và Power Query khi mở file.
Private Sub Workbook_Open() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets Dim pt As PivotTable For Each pt In ws.PivotTables pt.RefreshTable Next pt Next ws ThisWorkbook.Connections("Query - Bán_hàng").Refresh
End Sub
Sau khi hoàn thành, bạn sẽ có một dashboard tương tác, cập nhật tự động khi có dữ liệu mới.
10. Kết Luận
Việc nâng cao kỹ năng Excel không chỉ giúp bạn thực hiện các tác vụ hàng ngày nhanh hơn mà còn mở ra cánh cửa cho việc phân tích dữ liệu sâu hơn, tự động hoá quy trình và đưa ra quyết định dựa trên dữ liệu một cách chính xác. Bằng cách nắm vững:
- Các hàm và công thức phức tạp (XLOOKUP, LAMBDA, LET, hàm mảng),
- Power Query để xử lý dữ liệu đa nguồn,
- Power Pivot & DAX cho phân tích đa chiều,
- PivotTable & PivotChart để tạo báo cáo nhanh,
- VBA để tự động hoá mọi thao tác lặp lại,
bạn sẽ biến Excel thành một “công cụ đa năng” đáp ứng mọi nhu cầu công việc.
Lời khuyên cuối cùng: Hãy thực hành thường xuyên. Bắt đầu bằng các dự án nhỏ (ví dụ: tổng hợp chi phí cá nhân), sau đó dần dần mở rộng sang các dự án lớn hơn như báo cáo tài chính doanh nghiệp hoặc dashboard KPI. Khi gặp khó khăn, đừng ngại tìm kiếm tài liệu, tham gia cộng đồng (Stack Overflow, MrExcel, Reddit r/excel) và chia sẻ kiến thức với đồng nghiệp. Chúc bạn thành công trên hành trình chinh phục Excel nâng cao!
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ẻ.