Thủ thuật Excel “từ A đến Z”: cẩm nang tăng tốc làm việc, phân tích dữ liệu và tự động hóa

Thủ thuật excel

Bạn muốn làm Excel nhanh hơn, sạch hơn, ít lỗi hơn? Bài viết này tổng hợp những thủ thuật Excel thực dụng nhất: từ phím tắt, công thức “chuẩn bài”, tới Power Query, PivotTable, biểu đồ, What-If Analysis, LAMBDA/VBA… Tất cả thủ thuật tiện ích đều có ví dụ ngắn gọn để bạn áp dụng ngay cho công việc văn phòng, kế toán, marketing, phân tích dữ liệu.

1) Tăng tốc thao tác: phím tắt & mẹo “nhỏ mà có võ”

  • Chọn nhanh vùng liên tục:
    Ctrl + Shift + Mũi tên (Windows) / Cmd + Shift + Mũi tên (Mac).

  • Điền chuỗi: kéo Fill Handle (góc phải ô) hoặc dùng Ctrl + E (Flash Fill) để tách/gộp họ tên, email, mã hàng.

  • Paste Special (Ctrl + Alt + V): dán Values, Formats, Transpose… cực kỳ hữu ích khi dán kết quả mà không mang theo công thức.

  • Chèn nhiều hàng/cột: chọn n hàng → Ctrl + + để chèn cùng lúc n hàng.

  • Chọn vùng dữ liệu “đầy đủ”: đứng trong bảng → Ctrl + A chọn khối hiện hữu, Ctrl + A lần nữa chọn toàn sheet.

  • Freeze Panes: Cố định tiêu đề khi cuộn (View → Freeze Panes) để đọc báo cáo dài không bị lạc.

2) Xây “nền móng” dữ liệu chuẩn: Table, Data Validation, dọn sạch

Biến khối dữ liệu thành Table (Ctrl + T)

  • Bảng tự mở rộng khi thêm dòng, giúp công thức, Pivot, biểu đồ tự động cập nhật.

  • Dùng Structured Reference dễ đọc: =SUMIF(Tbl[Nhóm], "A", Tbl[Doanh thu]).

Data Validation (Data → Data Validation)

  • Chặn nhập sai: danh sách chọn (List), giới hạn số, ngày, độ dài ký tự.

  • Tip: tạo Drop-down liên kết (Dependent list) bằng INDIRECT.

Dọn dữ liệu “bẩn”

  • TRIM (xóa khoảng trắng thừa), CLEAN (ký tự không in), SUBSTITUTE (thay ký tự).

  • Excel 365 có TEXTSPLIT / TEXTAFTER / TEXTBEFORE để tách chuỗi cực nhanh.

  • Remove Duplicates hoặc UNIQUE (dynamic array) để lọc trùng.

3) Công thức “xương sống” ai dùng Excel cũng cần

Tổng hợp theo điều kiện

=SUMIF(Vùng_điều_kiện, Điều_kiện, Vùng_cộng)
=SUMIFS(Vùng_cộng, Vùng_đk1, Đk1, Vùng_đk2, Đk2)
=COUNTIFS(...), =AVERAGEIFS(...)

Tra cứu phiên bản hiện đại

  • XLOOKUP (Excel 365/2021): thay thế VLOOKUP, trả về trái/phải, match chính xác/nhỏ hơn/lớn hơn, có xử lý lỗi.

=XLOOKUP(giá_trị_cần_tìm, cột_tìm, cột_trả_về, "Không thấy", 0)
  • Cũ hơn thì dùng INDEX/MATCH (linh hoạt hơn VLOOKUP):

=INDEX(Cột_trả_về, MATCH(giá_trị_cần_tìm, Cột_tìm, 0))

Lọc, sắp xếp, danh sách duy nhất (Excel 365)

=FILTER(Bảng, Điều_kiện)
=SORT(Vùng, Cột, 1/-1)
=UNIQUE(Vùng)
=TAKE/DROP/CHOOSECOLS/WRAPROWS (xử lý mảng linh hoạt)

Ghép chuỗi & trích xuất văn bản

=TEXTJOIN(", ", TRUE, Vùng_cần_ghép)
=LEFT/RIGHT/MID, TEXTBEFORE/TEXTAFTER (365)

Ngày tháng, tiền tệ, định dạng

=EDATE(Ngày, số_tháng) ; =EOMONTH(Ngày, số_tháng)
=NETWORKDAYS(Bắt_đầu, Kết_thúc, [Ngày_lễ]) 'đếm ngày làm việc
=TEXT(Số, "dd/mm/yyyy") ; =TEXT(Số, "#,##0 đ")

Xử lý lỗi gọn gàng

=IFERROR(Công_thức, "Thông điệp khi lỗi")

4) 15 công thức “template” dùng hoài không chán

  1. Top-N theo điều kiện (365):

=TAKE(SORT(FILTER(Tbl, Tbl[Nhóm]="A"), -Tbl[Doanh thu], 1), 5)
  1. Đếm giá trị duy nhất theo điều kiện:

  • 365:

=ROWS(UNIQUE(FILTER(Tbl[Mã KH], Tbl[Khu vực]="HN")))
  • Phiên bản cũ (mảng CSE):

=SUM(1/COUNTIF(IF(Khu_vực="HN", Mã_KH), IF(Khu_vực="HN", Mã_KH)))
  1. Lấy phần domain từ URL:

=TEXTBEFORE(TEXTAFTER(A2,"//"),"/")
  1. Tách Họ / Tên (365):

=TEXTBEFORE(A2," ") ; =TEXTAFTER(A2," ")
  1. Gộp danh sách theo nhóm (365):

=LET(g,UNIQUE(Tbl[Nhóm]),
MAKEARRAY(ROWS(g),2, LAMBDA(r,c, IF(c=1, INDEX(g,r),
TEXTJOIN(", ",, FILTER(Tbl[Sản phẩm], Tbl[Nhóm]=INDEX(g,r)) )))))
  1. Tra cứu gần đúng theo mốc (giá bậc thang):

=XLOOKUP(Giá trị, Mốc_min, Kết_quả, , -1) 'match nhỏ hơn gần nhất
  1. Chuẩn hóa số điện thoại:

=TEXTAFTER(SUBSTITUTE(SUBSTITUTE(A2," ",""),"-",""),"+84")
  1. Chuyển số âm thành 0 khi tổng hợp:

=SUMPRODUCT((Vùng>0)*Vùng)
  1. Pivot “nhẹ” bằng công thức (365):

=LET(d,Tbl, UNPIVOT? 'gợi ý: dùng PIVOT với Power Query/PivotTable)
  1. Tô màu dòng xen kẽ (Conditional Formatting → Formula):

=MOD(ROW(),2)=0
  1. Tô màu hàng có từ khóa:

=ISNUMBER(SEARCH($H$1, $A1))
  1. Gắn thứ tự xếp hạng có tie:

=RANK(E2, E:E, 0) + COUNTIF($E$2:E2, E2) - 1
  1. Lọc nhiều điều kiện OR (365):

=FILTER(Tbl, (Tbl[Khu vực]="HN") + (Tbl[Khu vực]="HCM"))
  1. Ghép bảng 2 chiều (INDEX/MATCH/MATCH):

=INDEX(Vùng_trả, MATCH(Hàng, Vùng_hàng, 0), MATCH(Cột, Vùng_cột, 0))
  1. Phân loại nhãn bằng IFS/SWITCH:

=IFS(Điểm>=8,"A", Điểm>=6.5,"B", Điểm>=5,"C", TRUE,"D")

5) Power Query: “máy rửa dữ liệu” tích hợp sẵn

Power Query (Data → Get Data) cho phép bạn Import–Transform–Load dữ liệu từ Excel, CSV, Web, SQL… và ghi nhớ các bước để Refresh tự động.

  • Các bước hay dùng: Remove Columns, Split Column (by delimiter), Trim/Clean, Change Type, Unpivot/ Pivot, Group By, Merge (JOIN), Append (Union), Remove Duplicates, Replace Values.

  • Unpivot bảng “bề ngang” thành bề dọc để làm PivotTable/biểu đồ cho đúng chuẩn.

  • Merge để nối nhiều bảng theo khóa (giống VLOOKUP nhưng khỏe và minh bạch hơn).

  • Đặt Parameters (tháng/năm, đường dẫn) để tái sử dụng báo cáo.

Mẹo: luôn để dữ liệu nguồn trong Table rồi Get Data → From Table/Range. Sau khi transform xong, Close & Load To…: tạo Table mới, hoặc chỉ tạo Connection và đưa thẳng vào PivotTable.

6) PivotTable & PivotChart: tổng hợp dữ liệu trong 1 phút

  • Nguồn động: dùng Table làm nguồn để Pivot tự bắt dữ liệu mới.

  • Distinct Count: khi tạo Pivot từ Data Model (Add to Data Model) sẽ có Distinct Count (đếm duy nhất) trong Value Field Settings.

  • Group: nhóm Date theo Year/Quarter/Month, nhóm số theo khoảng (binning), hoặc nhóm thủ công các nhãn.

  • Slicer & Timeline: tạo bộ lọc trực quan; nhiều Pivot có thể Sync cùng Slicer.

  • GETPIVOTDATA: rút số cụ thể từ Pivot vào báo cáo; tắt/bật tại PivotTable Options.

7) Biểu đồ & trình bày: trực quan hóa để kể chuyện

  • Combo Chart: ghép cột + đường; đặt trục phụ (Secondary Axis) cho chỉ số có thang đo khác.

  • Sparklines (Insert → Sparklines): mini-chart trong 1 ô để theo dõi xu hướng.

  • Conditional Formatting: Heatmap (Color Scales), Data Bars, Icon Sets (xu hướng, tăng/giảm).

  • Biểu đồ động: nguồn là vùng dynamic array (FILTER/UNIQUE) hoặc Named Range (OFFSET/INDEX).

Tip: chú ý Data-Ink Ratio – bớt chi tiết thừa (viền, hiệu ứng), thêm Data Label có ý nghĩa; người xem hiểu nhanh là mục tiêu số 1.

8) What-If Analysis, Goal Seek, Data Table, Solver

  • Goal Seek (Data → What-If): tìm đầu vào để đạt mục tiêu đầu ra (ví dụ cần giá bán bao nhiêu để lãi = 0).

  • Data Table: phân tích nhạy cảm 1 biến/2 biến, tạo ma trận kết quả khi thay đổi đầu vào.

  • Scenario Manager: lưu nhiều kịch bản (giá cao/thấp, chi phí biến/định).

  • Solver: tối ưu hóa đa ràng buộc (lập kế hoạch sản xuất, phân bổ ngân sách); đặt mục tiêu Max/Min/Value, biến quyết định, ràng buộc (≤, =, ≥).

9) Tự động hóa: Macro Recorder, VBA “vừa đủ xài”, LAMBDA, Office Scripts

  • Macro Recorder: ghi thao tác lặp lại (format, lọc, in ấn…), sau đó gán nút để chạy 1 click.

  • VBA cơ bản (ví dụ: tự chỉnh độ rộng cột, áp format):

Sub CleanSheet()
Cells.EntireColumn.AutoFit
Rows(1).Font.Bold = True
Range("A1").Select
End Sub
  • UDF bằng LAMBDA (Excel 365): tạo hàm tùy biến không cần VBA.

=LAMBDA(txt, delim, TEXTAFTER(txt, delim)) 'ví dụ giản lược
  • Office Scripts (Excel on the web): tự động hóa qua TypeScript, kết hợp Power Automate để chạy theo lịch hoặc khi có file mới.

10) Kiểm soát lỗi & tăng hiệu năng

  • Evaluate Formula (Formulas → Evaluate Formula): “soi” từng bước tính khi công thức lỗi.

  • Trace Dependents/Precedents: xem ô nào ảnh hưởng/được ảnh hưởng.

  • Hạn chế hàm “volatile”: NOW, TODAY, OFFSET, INDIRECT, RAND… vì chúng tính lại liên tục; thay bằng INDEX cho vùng động, tham chiếu trực tiếp.

  • Tắt AutoCalc tạm thời khi dọn dữ liệu nặng (Formulas → Calculation Options → Manual), xong bấm F9.

  • Tách file lớn, dùng Power Query để nạp dữ liệu thay vì công thức “quay vòng”.

11) Bảo mật, kiểm soát & cộng tác

  • Protect Sheet/Workbook: khóa cấu trúc, ẩn công thức (Format Cells → Protection → Hidden), chỉ mở phần cần nhập.

  • Allow Edit Ranges: cho phép người dùng chỉnh một số vùng dù đã Protect.

  • Versioning qua OneDrive/SharePoint: xem lịch sử, khôi phục bản cũ; comment, @mention, co-authoring thời gian thực.

12) In ấn & xuất báo cáo sạch đẹp

  • Page Layout: đặt Print Area, Print Titles (lặp tiêu đề cột), căn lề, canh trang ngang/dọc.

  • Scale to Fit: 1 trang ngang × n trang dọc cho bảng rộng.

  • Header/Footer: thêm số trang, ngày in, tên file; chèn logo.

  • Page Break Preview: kéo đường ngắt trang cho bố cục hợp lý.

13) Bộ “cheat-sheet” công thức hay gặp (copy là chạy)

  • Ghép họ tên “Họ Tên” → “Tên, Họ”:

=TEXTAFTER(A2," ") & ", " & TEXTBEFORE(A2," ")
  • Lấy tháng/năm từ ngày:

=TEXT(A2,"mm/yyyy") ; =MONTH(A2) ; =YEAR(A2)
  • Chuẩn hóa chữ cái đầu:

=PROPER(LOWER(A2)) 'không hoàn hảo với tiếng Việt có dấu, cân nhắc thủ công
  • Tách mô tả “SP-123 | Màu Đỏ | Size M” thành cột (365):

=TEXTSPLIT(A2,"|")
  • Lấy dòng có giá trị lớn nhất theo nhóm:

=FILTER(Tbl, Tbl[Giá]=MAXIFS(Tbl[Giá], Tbl[Nhóm], H2))
  • Gom tất cả mã đơn của 1 khách hàng thành 1 dòng (365):

=TEXTJOIN(", ", TRUE, FILTER(Tbl[Mã đơn], Tbl[KH]=H2))

14) Lộ trình nâng cấp kỹ năng Excel (tự học hiệu quả)

  1. Vững nền tảng: Table, Data Validation, Conditional Formatting, SUMIFS/COUNTIFS, XLOOKUP.

  2. Dynamic Arrays (365): FILTER/UNIQUE/SORT/TAKE/DROP/CHOOSECOLS/TEXTSPLIT.

  3. Power Query: Import, Clean, Transform, Merge/Append, Unpivot.

  4. PivotTable/Chart + Slicer: làm báo cáo động.

  5. What-If/Solver: phân tích nhạy cảm, tối ưu hóa.

  6. Automation: Macro/VBA “vừa đủ xài”, LAMBDA/Office Scripts cho xử lý lặp.

  7. Chuẩn hóa báo cáo: tiêu đề, format, màu sắc nhất quán, ghi chú công thức, quy ước tên.

Kết luận

Muốn giỏi Excel, bạn cần hai yếu tố: (1) dữ liệu gọn – sạch – chuẩn, (2) kho công thức & công cụ vừa đủ để giải quyết 80% công việc thật nhanh. Hãy bắt đầu bằng Table + Data Validation + SUMIFS/XLOOKUP; sau đó học FILTER/UNIQUE/SORT để “động hóa” bảng tính; tiếp theo là Power Query để rửa dữ liệu, Pivot để tổng hợp; cuối cùng, dùng What-If/SolverAutomation để tiết kiệm hàng giờ thao tác lặp.

Để lại một bình luận

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *