Xin chào và mời các anh chị đến với Phần 3/6: Bộ 150+ thủ thuật Excel cho dân văn phòng, trong bài này mình tổng hợp 45 mẹo tối ưu hiệu suất và xử lý lỗi, phần dài nhất trong bộ thủ thuật, dành cho ai muốn nâng trình lên hẳn một bậc.Mình vào việc hen!
Mục lục.
Absolute Reference ($)
Ghim chặt một ô lại, kéo công thức đi đâu nó cũng không nhúc nhích
CÚ PHÁP
$A$1 (ghim cả cột lẫn dòng), $A1 hoặc A$1 (ghim một phần)
CÔNG DỤNG
Cố định địa chỉ một ô (hoặc một phần địa chỉ) khi copy công thức sang ô khác, tránh tình trạng tham chiếu bị lệch theo hướng kéo.
VÍ DỤ
=B2*$D$1
KẾT QUẢ
Khi kéo công thức xuống các dòng khác, B2 tự đổi theo từng dòng nhưng $D$1 luôn giữ nguyên – đúng ý đồ khi cần nhân với một hệ số cố định.
AutoFit Column Width
Chỉnh độ rộng cột vừa khít nội dung chỉ với một cú nháy đúp
CÚ PHÁP
Nháy đúp vào đường viền phải của tiêu đề cột (hoặc Home → Format → AutoFit Column Width)
CÔNG DỤNG
Tự động điều chỉnh độ rộng cột vừa đủ để hiển thị toàn bộ nội dung dài nhất trong cột đó, không cần kéo tay ước lượng.
VÍ DỤ
Chọn các cột cần chỉnh → Home → Format → AutoFit Column Width.
KẾT QUẢ
Toàn bộ các cột đã chọn tự động co giãn vừa khít với nội dung dài nhất – không còn cảnh chữ bị cắt hoặc cột thừa khoảng trống.
Ẩn ô bằng định dạng tuỳ chỉnh
Cho giá trị “tàng hình” mà không cần xoá đi đâu cả
CÚ PHÁP
Ctrl + 1 → Number → Custom → gõ ;;;
CÔNG DỤNG
Ẩn nội dung hiển thị của một ô mà vẫn giữ nguyên giá trị thật bên trong (vẫn tính toán bình thường), hữu ích khi cần giấu dữ liệu nhạy cảm lúc trình chiếu hoặc in ấn.
VÍ DỤ
Chọn ô cần ẩn → Ctrl + 1 → Custom → gõ ;;; → OK.
KẾT QUẢ
Ô trông như trống trơn nhưng giá trị bên trong vẫn còn nguyên và vẫn được các công thức khác tính toán bình thường.
Chart Treemap / Sunburst / Waterfall
Bộ biểu đồ đời mới, kể câu chuyện dữ liệu theo cách không nhàm chán
Các kiểu biểu đồ nâng cao giúp thể hiện dữ liệu phân cấp (Treemap, Sunburst) hoặc thể hiện sự tăng giảm tích luỹ theo từng bước (Waterfall) – trực quan hơn hẳn biểu đồ cột/tròn truyền thống với một số loại dữ liệu.
Biểu đồ thể hiện rõ từng khoản tăng/giảm cộng dồn dẫn tới kết quả cuối cùng – trực quan hơn hẳn khi giải trình biến động lợi nhuận.
CHOOSE
Chọn giá trị theo số thứ tự, như bốc thăm trúng thưởng
CÚ PHÁP
=CHOOSE(index_num, value1, value2, …)
CÔNG DỤNG
Chọn ra một giá trị trong danh sách dựa theo một số thứ tự (index) – hữu ích khi cần xây dựng các kịch bản phân tích “nếu chọn phương án A/B/C thì sao”.
VÍ DỤ
=CHOOSE(2,”Kịch bản thấp”,”Kịch bản cơ sở”,”Kịch bản cao”)
KẾT QUẢ
Trả về “Kịch bản cơ sở” vì index_num = 2, đúng vị trí thứ hai trong danh sách các kịch bản đưa ra.
CLEAN / CODE / CHAR
Đội vệ sinh dữ liệu, dọn sạch mọi ký tự vô hình gây rối công thức
CÚ PHÁP
=CLEAN(text) / =CODE(text) / =CHAR(number)
CÔNG DỤNG
CLEAN loại bỏ các ký tự không in được thường lẫn trong dữ liệu copy-paste; CODE và CHAR giúp xác định và xử lý các ký tự đặc biệt theo mã ASCII khi cần chẩn đoán lỗi dữ liệu “bẩn”.
VÍ DỤ
=CLEAN(A1)
KẾT QUẢ
Trả về nội dung của A1 nhưng đã loại bỏ hết các ký tự ẩn không in được – dữ liệu giờ “sạch” để tính toán chính xác.
Clipboard History
Bộ nhớ tạm biết nhớ nhiều thứ cùng lúc, không chỉ mỗi thứ vừa copy gần nhất
CÚ PHÁP
Home → Clipboard (mũi tên mở rộng) hoặc Windows + V
CÔNG DỤNG
Lưu lại lịch sử nhiều mục đã copy gần đây (không chỉ mục cuối cùng), cho phép dán lại bất kỳ mục nào trong danh sách thay vì phải copy lại từ đầu.
VÍ DỤ
Copy lần lượt 5 vùng dữ liệu khác nhau → mở Clipboard History → chọn mục cần dán.
KẾT QUẢ
Danh sách 5 mục đã copy hiện ra đầy đủ, click vào mục nào là dán được ngay mục đó – không cần quay lại copy từ nguồn gốc.
Ctrl + D
Copy ô phía trên xuống ô hiện tại, nhanh hơn cả Ctrl + C rồi Ctrl + V
CÚ PHÁP
Ctrl + D
CÔNG DỤNG
Sao chép nhanh nội dung (và định dạng) của ô ngay phía trên xuống ô hoặc vùng đang chọn, tiết kiệm một bước so với copy-paste truyền thống.
VÍ DỤ
Chọn vùng ô trống ngay dưới một ô có công thức → Ctrl + D.
KẾT QUẢ
Nội dung và công thức của ô phía trên được sao chép xuống toàn bộ vùng đã chọn chỉ trong một cú bấm.
Data Validation + Custom Format
Vừa kiểm soát cái được nhập, vừa ép nó hiển thị đúng khuôn mẫu
CÚ PHÁP
Data Validation (Custom formula) kết hợp Format Cells → Custom
CÔNG DỤNG
Kết hợp Data Validation để giới hạn định dạng dữ liệu được phép nhập (ví dụ đúng 10 chữ số cho số điện thoại) với Custom Format để hiển thị đúng khuôn mẫu mong muốn (ví dụ có dấu gạch ngang phân cách).
VÍ DỤ
Data Validation Custom: =AND(LEN(A1)=10,ISNUMBER(VALUE(A1)))
KẾT QUẢ
Ô chỉ chấp nhận đúng 10 chữ số hợp lệ cho số điện thoại; nhập sai định dạng sẽ bị Excel từ chối ngay lập tức.
Dọn dẹp Named Ranges
Tổng vệ sinh những cái tên vùng dữ liệu đã “chết” nhưng chưa ai dọn
CÚ PHÁP
Formulas → Name Manager
CÔNG DỤNG
Rà soát và xoá các Named Ranges không còn được sử dụng hoặc bị lỗi tham chiếu (#REF!), giúp workbook gọn gàng hơn và giảm nguy cơ lỗi khi mở file.
VÍ DỤ
Formulas → Name Manager → lọc và chọn các tên bị lỗi #REF! → Delete.
KẾT QUẢ
Danh sách Named Ranges chỉ còn lại các tên đang thực sự được sử dụng – workbook nhẹ hơn, ít cảnh báo lỗi hơn khi mở lại.
Approximate Match trong VLOOKUP
Tìm giá trị gần đúng khi không có ai khớp 100% với yêu cầu
Khi tham số cuối của VLOOKUP là TRUE (hoặc bỏ trống), hàm sẽ tìm giá trị GẦN ĐÚNG NHẤT thay vì phải khớp chính xác 100% – hữu ích cho các bảng tra cứu theo khoảng (ví dụ bậc thuế, bậc hoa hồng).
VÍ DỤ
=VLOOKUP(15000000,$A$2:$B$6,2,TRUE)
KẾT QUẢ
Trả về mức tương ứng với khoảng chứa giá trị 15.000.000 trong bảng tra cứu theo bậc – dù không có dòng nào khớp chính xác con số này.
Export CSV/PDF tự động
Xuất báo cáo ra định dạng khác chỉ với vài cú click, không cần “Save As” thủ công lặp lại
CÚ PHÁP
File → Export → Change File Type (hoặc macro VBA tự động hoá)
CÔNG DỤNG
Xuất workbook (hoặc từng sheet) sang định dạng CSV để trao đổi dữ liệu, hoặc PDF để gửi báo cáo – có thể tự động hoá bằng VBA nếu cần lặp lại thường xuyên.
VÍ DỤ
File → Export → Create PDF/XPS Document → chọn phạm vi in.
KẾT QUẢ
Một file PDF được tạo ra từ đúng phạm vi dữ liệu đã chọn, giữ nguyên định dạng hiển thị như trên Excel.
Filter by Value
Lọc nhanh theo đúng giá trị đang nhìn thấy, không cần mở dropdown filter
CÚ PHÁP
chuột phải vào ô → Filter → Filter by Selected Cell’s Value
CÔNG DỤNG
Lọc nhanh toàn bộ dữ liệu chỉ giữ lại các dòng có giá trị giống hệt ô vừa nhấp chuột phải vào, tiết kiệm bước mở dropdown rồi tick chọn thủ công.
VÍ DỤ
Chuột phải vào một ô có giá trị “Hà Nội” → Filter → Filter by Selected Cell’s Value.
KẾT QUẢ
Bảng dữ liệu ngay lập tức chỉ hiển thị các dòng có giá trị “Hà Nội” ở đúng cột đó.
Find All
Tìm hết một lượt, xử lý hàng loạt thay vì bấm Find Next cả trăm lần
CÚ PHÁP
Ctrl + F → Find All
CÔNG DỤNG
Liệt kê TOÀN BỘ các ô chứa từ khoá tìm kiếm trong một danh sách, cho phép chọn tất cả rồi thao tác hàng loạt (xoá, đổi màu…) thay vì tìm từng ô một bằng Find Next.
VÍ DỤ
Ctrl + F → gõ từ khoá → Find All → Ctrl + A trong danh sách kết quả để chọn hết.
KẾT QUẢ
Tất cả các ô chứa từ khoá được chọn cùng lúc trên bảng tính – sẵn sàng xoá, tô màu, hoặc chỉnh sửa hàng loạt.
Forecast Sheet
Dự báo tương lai chỉ với một cú nhấp, khỏi cần học mô hình thống kê
CÚ PHÁP
Data → Forecast Sheet
CÔNG DỤNG
Tự động tạo một bảng và biểu đồ dự báo xu hướng tương lai dựa trên dữ liệu lịch sử theo thời gian, sử dụng thuật toán làm mượt theo mùa vụ có sẵn trong Excel.
VÍ DỤ
Chọn dữ liệu doanh số theo tháng → Data → Forecast Sheet → chọn số kỳ dự báo.
KẾT QUẢ
Một sheet mới xuất hiện với biểu đồ thể hiện xu hướng dự báo cho các tháng tiếp theo, kèm khoảng tin cậy trên/dưới.
Goal Seek
Biết trước muốn có kết quả gì, để Excel dò ngược ra đầu vào cần thiết
CÚ PHÁP
Data → What-If Analysis → Goal Seek
CÔNG DỤNG
Xác định giá trị đầu vào cần thiết để đạt được một kết quả mục tiêu cụ thể – làm ngược lại so với cách tính công thức thông thường (từ input ra output).
VÍ DỤ
Data → Goal Seek → Set cell: Lợi nhuận, To value: 100.000.000, By changing cell: Số lượng bán
KẾT QUẢ
Excel tự tính ra số lượng cần bán để đạt đúng mức lợi nhuận mục tiêu 100 triệu – thay vì phải tự mò bằng cách thử nhiều số khác nhau.
Công thức CELL hiển thị đường dẫn file
Ghi lại “địa chỉ nhà” của file ngay trong một ô, tiện đối chiếu khi in ấn
CÚ PHÁP
=CELL(“filename”)
CÔNG DỤNG
Trả về đường dẫn đầy đủ và tên file hiện tại ngay trong một ô, hữu ích khi in báo cáo cần ghi rõ nguồn file, hoặc khi cần kiểm tra nhanh đang làm việc trên file/sheet nào.
VÍ DỤ
=CELL(“filename”,A1)
KẾT QUẢ
Trả về chuỗi đường dẫn đầy đủ dạng ổ đĩa:\thư mục\[tên file.xlsx]tên sheet – biết ngay đang ở file nào mà không cần nhìn thanh tiêu đề.
Import từ ảnh (“From Picture”)
Chụp ảnh bảng số liệu là có ngay dữ liệu, không cần gõ tay lại từng ô
CÚ PHÁP
Data → From Picture → From File/Clipboard
CÔNG DỤNG
Chuyển đổi dữ liệu dạng bảng trong một bức ảnh (chụp màn hình, ảnh chụp giấy tờ…) thành dữ liệu Excel có thể chỉnh sửa được, nhờ công nghệ nhận diện ký tự quang học (OCR).
VÍ DỤ
Chụp ảnh một bảng số liệu giấy → Data → From Picture → From File → chọn ảnh.
KẾT QUẢ
Excel nhận diện và chuyển bảng trong ảnh thành dữ liệu dạng ô tính, có thể chỉnh sửa lại nếu OCR nhận nhầm vài ký tự.
In nhiều sheet / cả workbook
In một lần cho cả tập hồ sơ, không cần mở từng sheet bấm in riêng lẻ
CÚ PHÁP
Chọn nhiều sheet tab (giữ Ctrl khi click) → File → print
CÔNG DỤNG
In đồng thời nhiều sheet đã chọn hoặc toàn bộ workbook trong một lệnh in duy nhất, thay vì phải vào từng sheet bấm in riêng lẻ.
VÍ DỤ
Giữ Ctrl → click chọn các tab sheet cần in → File → Print → Print Active Sheets.
KẾT QUẢ
Tất cả các sheet đã chọn được gộp in liên tục trong một lần in, tiết kiệm thời gian so với in từng sheet riêng.
Khoá workbook/sheet
Đóng cửa cẩn thận trước khi gửi file đi cho người khác xem
CÚ PHÁP
Review → Protect Sheet / Protect Workbook
CÔNG DỤNG
Đặt mật khẩu bảo vệ để ngăn người khác chỉnh sửa cấu trúc workbook (thêm/xoá/ẩn sheet) hoặc nội dung của một sheet cụ thể, bảo mật dữ liệu quan trọng.
VÍ DỤ
Review → Protect Workbook → nhập mật khẩu → OK.
KẾT QUẢ
Người mở file không thể thêm, xoá, đổi tên hay sắp xếp lại các sheet nếu không có đúng mật khẩu.
Evaluate Formula
Mổ xẻ công thức từng bước một, bắt tận gốc chỗ tính sai
CÚ PHÁP
Formulas → Evaluate Formula
CÔNG DỤNG
Chạy từng bước tính toán của một công thức phức tạp để xem giá trị trung gian ở mỗi bước, giúp xác định chính xác chỗ nào trong công thức đang gây ra kết quả sai.
VÍ DỤ
Chọn ô chứa công thức lồng nhiều hàm → Formulas → Evaluate Formula → bấm Evaluate liên tục.
KẾT QUẢ
Từng phần của công thức được tính toán và hiển thị tuần tự – dễ dàng phát hiện đúng bước nào đang trả về giá trị không mong muốn.
LAMBDA kết hợp LET
Công thức động, dễ đọc, dễ bảo trì – không còn là mớ bòng bong lồng hàm
CÚ PHÁP
=LAMBDA(tham_số, LET(biến1, giá_trị1, biểu_thức))
CÔNG DỤNG
Kết hợp LAMBDA (tạo hàm tuỳ chỉnh) với LET (đặt tên cho các giá trị trung gian) giúp viết công thức phức tạp dễ đọc hơn hẳn, vì mỗi bước tính toán trung gian được đặt tên rõ ràng thay vì lặp lại công thức con nhiều lần.
Công thức tính tổng tiền có thuế được chia rõ thành các bước tạm_tính và thuế có tên gọi cụ thể – đọc lại dễ hiểu hơn nhiều so với một công thức viết liền một mạch.
LAMBDA
Tự chế hàm riêng cho mình, không cần đụng tới VBA
CÚ PHÁP
=LAMBDA(tham_số1, tham_số2, …, biểu_thức)
CÔNG DỤNG
Tạo một hàm tuỳ chỉnh hoàn toàn mới bằng công thức Excel thuần, có thể đặt tên và tái sử dụng nhiều lần như một hàm có sẵn – không cần viết một dòng code VBA nào.
VÍ DỤ
Đặt tên TinhThue = LAMBDA(luong, luong*0.1) trong Name Manager, sau đó dùng =TinhThue(A1)
KẾT QUẢ
Công thức =TinhThue(A1) hoạt động y hệt một hàm Excel gốc, tự động tính 10% của giá trị ở A1 – tái sử dụng ở bất kỳ đâu trong workbook.
Lookup 2 chiều (INDEX/MATCH kép)
Tìm đúng ô giao giữa một hàng và một cột, như toạ độ trên bản đồ
Kết hợp hai lần MATCH (một cho hàng, một cho cột) lồng trong INDEX để tra cứu giá trị tại đúng giao điểm của một hàng và một cột cụ thể trong bảng hai chiều, như bảng lương theo cả cấp bậc và thâm niên.
Trả về đúng giá trị nằm ở giao điểm giữa hàng “Cấp 3” và cột “Năm 5” trong bảng lương hai chiều.
Mở nhiều Workbook song song
Đặt hai file cạnh nhau để so sánh, không cần Alt+Tab qua lại liên tục
CÚ PHÁP
View → View Side by Side
CÔNG DỤNG
Mở hai workbook cạnh nhau trên cùng màn hình và đồng bộ cuộn trang, giúp so sánh dữ liệu giữa hai file dễ dàng mà không cần chuyển đổi cửa sổ liên tục.
VÍ DỤ
Mở cả hai file cần so sánh → View → View Side by Side → View → Synchronous Scrolling.
KẾT QUẢ
Hai workbook hiển thị cạnh nhau và cuộn đồng bộ cùng lúc – so sánh từng dòng dữ liệu tương ứng dễ dàng hơn hẳn.
OFFSET kết hợp SUM/AVERAGE
Tính toán theo một vùng “biết co giãn” tuỳ theo dữ liệu hiện có
OFFSET tạo ra một vùng tham chiếu ĐỘNG dựa trên một điểm gốc và độ lệch, cho phép SUM hoặc AVERAGE tính toán trên một vùng có kích thước thay đổi theo lượng dữ liệu thực tế, không cần sửa công thức tay mỗi khi thêm dòng.
VÍ DỤ
=SUM(OFFSET($A$1,1,0,COUNTA(A:A)-1,1))
KẾT QUẢ
Tự động cộng tổng toàn bộ dữ liệu trong cột A (trừ tiêu đề), dù bạn thêm bao nhiêu dòng mới thì công thức vẫn tự mở rộng theo.
PMT / IPMT
Cặp hàm tài chính tính lãi vay, khỏi cần mở máy tính tài chính riêng
CÚ PHÁP
=PMT(rate, nper, pv) / =IPMT(rate, per, nper, pv)
CÔNG DỤNG
PMT tính số tiền phải trả cố định mỗi kỳ cho một khoản vay trả góp; IPMT tính riêng phần LÃI phải trả trong một kỳ cụ thể của khoản vay đó.
VÍ DỤ
=PMT(8%/12, 24, 50000000)
KẾT QUẢ
Trả về số tiền phải trả hàng tháng (dạng số âm, thể hiện dòng tiền ra) cho khoản vay 50 triệu, lãi suất 8%/năm, trả trong 24 tháng.
Print Titles
In tiêu đề trên mọi trang, không để trang sau “mồ côi” không biết cột nào là cột nào
CÚ PHÁP
Page Layout → Print Titles → Rows to repeat at top
CÔNG DỤNG
Đặt một hàng (thường là dòng tiêu đề) để tự động lặp lại trên MỌI TRANG khi in một bảng dữ liệu dài nhiều trang, giúp người đọc không bị lạc khi xem các trang sau.
VÍ DỤ
Page Layout → Print Titles → Rows to repeat at top: chọn dòng 1.
KẾT QUẢ
Dòng tiêu đề xuất hiện lặp lại ở đầu mỗi trang in, kể cả trang thứ 5, thứ 10 của bảng dữ liệu dài.
Quick Access Toolbar
Thanh công cụ cá nhân hoá, gom sẵn những lệnh bạn dùng nhiều nhất
CÚ PHÁP
Click mũi tên xổ xuống ở góc trên trái → Customize Quick Access Toolbar
CÔNG DỤNG
Thêm các lệnh dùng thường xuyên (New, Open, Quick Print, hoặc bất kỳ lệnh nào) vào thanh công cụ nhỏ ở góc trên cùng, truy cập nhanh hơn hẳn so với lục tìm trong các tab Ribbon.
VÍ DỤ
Click mũi tên xổ xuống → tick chọn thêm “Quick Print” và “Format Painter”.
KẾT QUẢ
Các lệnh vừa chọn xuất hiện ngay trên thanh Quick Access Toolbar, chỉ cần một cú click là dùng được, không cần chuyển tab.
Quick Analysis
Nút thần kỳ hiện ra ngay khi bôi đen dữ liệu, gợi ý cả biểu đồ lẫn định dạng
CÚ PHÁP
Bôi đen vùng dữ liệu → biểu tượng Quick Analysis góc dưới phải (hoặc Ctrl + Q)
CÔNG DỤNG
Gợi ý nhanh các tuỳ chọn định dạng, biểu đồ, tổng hợp (Totals), bảng (Tables) và Sparklines phù hợp với vùng dữ liệu vừa chọn, giúp phân tích nhanh mà không cần vào từng tab riêng lẻ.
VÍ DỤ
Bôi đen vùng số liệu → Ctrl + Q → chọn tab Charts → chọn biểu đồ gợi ý.
KẾT QUẢ
Một bảng xem trước các loại biểu đồ phù hợp hiện ra ngay lập tức – chọn một cái là có biểu đồ, không cần vào tận tab Insert.
Remove Duplicates
Dọn sạch bản ghi trùng lặp chỉ với một cú click, không cần dò tay
CÚ PHÁP
Data → Remove Duplicates
CÔNG DỤNG
Tự động rà soát và xoá các dòng dữ liệu bị trùng lặp hoàn toàn (hoặc trùng theo một số cột chỉ định) trong một vùng dữ liệu, giúp dữ liệu sạch sẽ trước khi phân tích.
VÍ DỤ
Chọn vùng dữ liệu → Data → Remove Duplicates → chọn các cột cần kiểm tra trùng → OK.
KẾT QUẢ
Excel báo số dòng trùng đã bị xoá và số dòng duy nhất còn lại – dữ liệu sạch, sẵn sàng cho các bước phân tích tiếp theo.
Set Print Area + Fit Sheet to One Page
Ép cả bảng dữ liệu gọn vừa một trang giấy, không bị cắt xén lung tung
CÚ PHÁP
Page Layout → Print Area → Set Print Area, sau đó Page Setup → Fit to 1 page
CÔNG DỤNG
Giới hạn đúng vùng dữ liệu cần in (Print Area) và tự động co giãn tỷ lệ để vừa khít trong một trang giấy (Fit to Page), tránh tình trạng bảng bị cắt ngang sang nhiều trang khi in.
VÍ DỤ
Chọn vùng dữ liệu cần in → Page Layout → Print Area → Set Print Area → Page Setup → Fit to: 1 page wide by 1 tall.
KẾT QUẢ
Toàn bộ vùng dữ liệu đã chọn được in gọn trong đúng một trang giấy, tỷ lệ co giãn tự động cho vừa khít.
Inquire Add-in
Đối chiếu hai workbook như soi gương, phát hiện ngay chỗ khác biệt
Add-in chuyên dụng của Excel giúp so sánh chi tiết hai workbook, chỉ ra chính xác những ô nào có công thức, giá trị hoặc định dạng khác nhau giữa hai phiên bản file.
VÍ DỤ
Kích hoạt Inquire → chọn hai file cần so sánh → Compare Files.
KẾT QUẢ
Một báo cáo chi tiết liệt kê từng ô khác biệt giữa hai file, kèm màu sắc phân loại theo loại thay đổi (công thức, giá trị, định dạng).
Sửa lỗi #VALUE / #REF / #NAME? bằng IFERROR
Vá mọi lỗi công thức thường gặp chỉ với một hàm bọc bên ngoài
CÚ PHÁP
=IFERROR(công_thức_gốc, giá_trị_thay_thế)
CÔNG DỤNG
Bọc IFERROR bên ngoài bất kỳ công thức nào có nguy cơ trả về lỗi #VALUE! (sai kiểu dữ liệu), #REF! (tham chiếu bị mất), hay #NAME? (gõ sai tên hàm) để thay bằng một giá trị dễ hiểu hơn.
VÍ DỤ
=IFERROR(A1/B1,”Kiểm tra lại dữ liệu”)
KẾT QUẢ
Nếu công thức gốc lỗi vì bất kỳ lý do gì, ô sẽ hiển thị “Kiểm tra lại dữ liệu” thay vì các mã lỗi khó hiểu.
Bar Chart mini bằng REPT
Vẽ biểu đồ cột ngay trong ô chỉ bằng cách lặp lại một ký tự
CÚ PHÁP
=REPT(“█”, giá_trị/tỷ_lệ)
CÔNG DỤNG
Dùng hàm REPT lặp lại một ký tự (thường là khối vuông █) theo tỷ lệ với giá trị số, tạo hiệu ứng biểu đồ cột ngay trong một ô mà không cần chèn hẳn một Chart object.
VÍ DỤ
=REPT(“█”, B2/10)
KẾT QUẢ
Ô hiển thị một chuỗi ký tự khối vuông có độ dài tỷ lệ thuận với giá trị ở B2 – một dạng biểu đồ cột “mini” ngay trong bảng dữ liệu.
Tạo nhiều worksheet từ danh sách
Tách một danh sách dài thành nhiều sheet riêng chỉ trong vài giây
CÚ PHÁP
VBA vòng lặp tạo sheet, hoặc PivotTable → Show Report Filter Pages
CÔNG DỤNG
Tự động tạo nhiều worksheet riêng biệt (mỗi sheet ứng với một giá trị trong danh sách, ví dụ mỗi khu vực một sheet) thay vì tách tay từng sheet một.
VÍ DỤ
Tạo PivotTable với trường “Khu vực” ở Report Filter → PivotTable Analyze → Options → Show Report Filter Pages.
KẾT QUẢ
Excel tự động tạo một sheet riêng cho mỗi khu vực, mỗi sheet chỉ chứa dữ liệu PivotTable tương ứng với khu vực đó.
Phím tắt Macro
Gán một tổ hợp phím cho cả một chuỗi thao tác, bấm phát là chạy hết
Gán một tổ hợp phím tắt tuỳ chọn cho một macro đã ghi hoặc viết sẵn, giúp chạy lại thao tác lặp đi lặp lại chỉ bằng một cú bấm phím thay vì vào menu tìm macro.
Từ giờ chỉ cần bấm Ctrl + Shift + M là toàn bộ chuỗi thao tác trong macro chạy lại ngay lập tức.
Tắt tính toán tự động
Cho workbook “thở” bớt khi phải gánh hàng nghìn công thức nặng
CÚ PHÁP
Formulas → Calculation Options → Manual
CÔNG DỤNG
Tắt chế độ tự động tính lại toàn bộ công thức mỗi khi có thay đổi, giúp workbook lớn (nhiều công thức phức tạp) chạy mượt hơn hẳn khi đang nhập liệu – chỉ tính lại khi bấm F9.
VÍ DỤ
Formulas → Calculation Options → Manual, rồi bấm F9 khi cần tính lại toàn bộ.
KẾT QUẢ
Việc nhập liệu trở nên mượt mà hơn hẳn vì Excel không phải tính lại hàng nghìn công thức sau mỗi ký tự gõ vào – chỉ tính khi bạn chủ động bấm F9.
Tính tổng nhanh (Alt + =)
Cộng tổng một vùng chỉ với một tổ hợp phím, không cần gõ =SUM
CÚ PHÁP
Alt + = (AutoSum)
CÔNG DỤNG
Tự động chèn công thức SUM cho vùng dữ liệu số liền kề phía trên hoặc bên trái ô đang chọn, mà không cần gõ tay =SUM(…).
VÍ DỤ
Chọn ô trống ngay dưới một cột số → Alt + = → Enter.
KẾT QUẢ
Công thức SUM tự động được chèn với đúng vùng dữ liệu được nhận diện – tổng được tính ngay không cần gõ công thức tay.
Tối ưu file lớn
Giảm tải cho workbook “béo phì”, mở file nhanh hơn thấy rõ
CÚ PHÁP
Xoá Style thừa (Home → Cell Styles → Merge Styles), hạn chế số lượng Chart/Object
CÔNG DỤNG
Dọn dẹp các yếu tố khiến file Excel trở nên nặng nề và chậm chạp: quá nhiều Cell Style không dùng, quá nhiều biểu đồ/hình ảnh, hoặc vùng định dạng thừa vượt xa vùng dữ liệu thực tế.
VÍ DỤ
Home → Cell Styles → Merge Styles để loại các style thừa từ file được copy qua lại nhiều lần.
KẾT QUẢ
File giảm đáng kể dung lượng và mở/lưu nhanh hơn hẳn, đặc biệt với các file đã qua nhiều lần chỉnh sửa và copy dữ liệu từ nơi khác về.
Track Changes
Ghi lại nhật ký chỉnh sửa, biết chính xác ai đã đổi gì khi làm việc nhóm
CÚ PHÁP
Review → Track Changes → Highlight Changes
CÔNG DỤNG
Theo dõi và ghi lại lịch sử các thay đổi khi nhiều người cùng chỉnh sửa một workbook, giúp dễ dàng xem lại ai đã sửa ô nào, giá trị cũ là gì.
Mỗi thay đổi trong file được đánh dấu kèm tên người sửa và thời gian – dễ dàng đối chiếu lại lịch sử chỉnh sửa khi làm việc nhóm.
AutoSave / Backup tự động
Mạng lưới an toàn cho công sức của bạn, phòng khi máy “dở chứng”
CÚ PHÁP
File → Options → Save → Save AutoRecover information every … minutes
CÔNG DỤNG
Thiết lập tự động lưu bản backup định kỳ trong lúc làm việc, giúp khôi phục lại phần lớn dữ liệu nếu Excel bị treo hoặc mất điện đột ngột trước khi kịp lưu tay.
VÍ DỤ
File → Options → Save → đặt tần suất lưu tự động là mỗi 5 phút.
KẾT QUẢ
Nếu Excel gặp sự cố, khi mở lại sẽ có bản khôi phục gần nhất (tối đa cách 5 phút) thay vì mất trắng toàn bộ công sức từ lần lưu tay cuối cùng.
Tự động hoá gửi báo cáo qua email
Đặt lịch, để báo cáo tự bay đi mà không cần bạn ngồi canh giờ
CÚ PHÁP
Kết hợp VBA (gửi qua Outlook) với Windows Task Scheduler để chạy theo lịch
CÔNG DỤNG
Thiết lập một quy trình tự động chạy theo lịch định kỳ (ví dụ mỗi sáng thứ Hai), tự tạo báo cáo mới nhất và gửi email cho danh sách người nhận mà không cần thao tác tay.
VÍ DỤ
Viết macro xuất báo cáo + gửi email → dùng Task Scheduler của Windows để chạy file macro theo lịch mỗi tuần.
KẾT QUẢ
Đúng giờ đã đặt lịch, báo cáo mới nhất tự động được tạo và gửi email cho toàn bộ danh sách người nhận, không cần ai canh giờ bấm nút.
OFFSET xác định vùng động
Vùng tham chiếu tự “phình to” theo dữ liệu, không cần sửa công thức tay
Tạo ra một vùng tham chiếu có kích thước thay đổi linh hoạt dựa trên các tham số lệch hàng/cột, thường kết hợp với COUNTA để vùng tự mở rộng theo lượng dữ liệu thực tế đang có.
VÍ DỤ
=OFFSET($A$1,0,0,COUNTA($A:$A),1)
KẾT QUẢ
Vùng tham chiếu tự động bao trọn toàn bộ dữ liệu hiện có trong cột A, dù bạn thêm bớt bao nhiêu dòng thì vùng vẫn tự cập nhật theo.
XNPV và XIRR
Bộ đôi tài chính xử lý dòng tiền thực tế, không cần các mốc ngày đều tăm tắp
CÚ PHÁP
=XNPV(rate, values, dates) / =XIRR(values, dates)
CÔNG DỤNG
Khác với NPV và IRR thông thường (giả định các kỳ dòng tiền cách đều nhau), XNPV và XIRR cho phép tính toán với các mốc ngày dòng tiền thực tế KHÔNG ĐỀU – sát với thực tế đầu tư/kinh doanh hơn nhiều.
VÍ DỤ
=XIRR(B2:B6, A2:A6)
KẾT QUẢ
Trả về tỷ suất sinh lời nội bộ (IRR) tính chính xác theo đúng các mốc ngày dòng tiền thực tế trong A2:A6, dù các khoảng cách ngày không đều nhau.
Lời kết cho Phần 3/6: Bộ 150+ thủ thuật Excel cho dân văn phòng
45 mẹo này là ranh giới giữa người biết dùng Excel và người làm chủ Excel. Bỏ túi dần, không cần nhớ hết trong một ngày. 💪Làm riết thành quen tay thôi anh chị ơi!
