Chức năng:
Phân tích công thức của ô đang chọn (Formula Audit) và liệt kê toàn bộ các vùng dữ liệu, ô tham chiếu, Named Range, Table và liên kết ngoài (External References) được sử dụng trong công thức. Từ danh sách này, bạn có thể nhảy trực tiếp đến từng nguồn dữ liệu chỉ với một cú nhấp chuột.
Hướng dẫn:
Chọn một ô chứa công thức rồi chạy Formula_Audit. Cửa sổ phân tích sẽ tự động:
Hiển thị toàn bộ công thức của ô đang chọn.
Phân tích và liệt kê các thành phần tham chiếu trong công thức, bao gồm:
Ô hoặc vùng dữ liệu.
Named Range.
Excel Table.
Tham chiếu đến worksheet khác.
Tham chiếu đến workbook khác.
Loại bỏ các chuỗi văn bản để chỉ giữ lại các tham chiếu thực sự.
Nếu tham chiếu nằm trong workbook chưa mở, chương trình sẽ tự động mở workbook (nếu tìm thấy file).
Khi chọn hoặc nhấp đúp vào một mục trong danh sách, Excel sẽ tự động chuyển đến đúng ô hoặc vùng dữ liệu tương ứng.
Kết quả là bạn có thể lần theo toàn bộ nguồn dữ liệu của một công thức phức tạp mà không cần đọc từng ký tự hay sử dụng các công cụ Trace Precedents của Excel. Đặc biệt hữu ích với những công thức dài cả trang, nơi việc "soi" bằng mắt thường dễ khiến bạn hoa mắt hơn là tìm được đáp án.
Minh họa chức năng:
Ô công thức
=SUMIFS(...) + XLOOKUP(...) + Data!B5
│
▼
Formula Audit
│
├── Sheet1!A2:A100
├── SalesTable[Amount]
├── ExchangeRate
├── Data!B5
└── [Budget.xlsx]Plan!C10
│
Nhấp vào một mục
▼
Tự động chuyển đến nguồn dữ liệu
Chức năng:
Tự động xử lý lỗi #SPILL! trên các worksheet được chọn bằng cách chèn thêm dòng để tạo đủ khoảng trống cho công thức mảng động (Dynamic Array) mở rộng kết quả.
Hướng dẫn:
Chọn một hoặc nhiều worksheet rồi chạy FixSpillErrors. Chương trình sẽ:
Sao chép toàn bộ dòng ngay bên dưới.
Chèn một dòng mới tại vị trí đó để mở rộng không gian cho công thức.
Giữ nguyên định dạng và cấu trúc của dòng được chèn.
Lệnh đặc biệt hữu ích khi các công thức như FILTER, UNIQUE, SORT, SEQUENCE, XLOOKUP (trả về nhiều kết quả) hoặc các công thức mảng động khác không thể mở rộng do bị các dòng dữ liệu phía dưới chặn.
Kết quả là các khoảng trống cần thiết sẽ được tạo tự động, giúp công thức mảng động hoạt động bình thường mà không phải chèn từng dòng thủ công. Thay vì dò từng lỗi #SPILL! rồi chèn dòng từng cái, bạn chỉ cần bấm một lần và để chương trình "nhường chỗ" cho các công thức tự bung kết quả.
Minh họa chức năng:
Trước khi xử lý
A
────────────────
=FILTER(...)
#SPILL!
Dữ liệu
Dữ liệu
│
▼
FixSpillErrors
│
▼
A
────────────────
=FILTER(...)
(khoảng trống mới)
Dữ liệu
Dữ liệu
✔ Công thức có không gian để Spill
✔ Tự động chèn dòng khi cần
Biểu tượng minh họa:
📊 ➜ ➕📄 ➜ ✅
Chức năng:
Tự động bao bọc các công thức trong vùng được chọn bằng hàm IFERROR(), giúp thay thế các lỗi như #N/A, #DIV/0!, #VALUE!, #REF!, #NAME?... bằng một giá trị hoặc thông báo do bạn chỉ định.
Hướng dẫn:
Chọn một hoặc nhiều ô chứa công thức, sau đó chạy InsertIFERROR.
Chương trình sẽ:
Hỏi nội dung muốn hiển thị khi công thức phát sinh lỗi.
=Công_thức thành: =IFERROR(Công_thức, Giá_trị_thay_thế)
Ví dụ:
| Trước | Sau |
|---|---|
=A1/B1 |
=IFERROR(A1/B1,"") |
=VLOOKUP(A2,Table,2,FALSE) |
=IFERROR(VLOOKUP(A2,Table,2,FALSE),"Not Found") |
=XLOOKUP(...) |
=IFERROR(XLOOKUP(...),0) |
Kết quả là toàn bộ công thức trong vùng được bảo vệ khỏi lỗi hiển thị, giúp báo cáo và dashboard trở nên gọn gàng, dễ đọc và chuyên nghiệp hơn. Chỉ cần nhập một lần nội dung thay thế, chương trình sẽ "mặc áo giáp" IFERROR cho tất cả công thức được chọn.
Minh họa chức năng:
Trước
=A1/B1
=VLOOKUP(...)
=XLOOKUP(...)
│
▼
InsertIFERROR
│
▼
=IFERROR(A1/B1,"")
=IFERROR(VLOOKUP(...),"Not Found")
=IFERROR(XLOOKUP(...),0)
✔ Tự động xử lý lỗi hiển thị
Biểu tượng minh họa:
🛡️ ➜ 📐 ➜ ✅
Chức năng:
Loại bỏ lớp IFERROR() khỏi các công thức trong vùng được chọn, khôi phục lại công thức gốc để hiển thị trực tiếp các lỗi tính toán của Excel.
Hướng dẫn:
Chọn một hoặc nhiều ô chứa công thức có sử dụng IFERROR(), sau đó chạy RemoveIFERROR.
Chương trình sẽ:
Ví dụ:
| Trước | Sau |
|---|---|
=IFERROR(A1/B1,0) |
=A1/B1 |
=IFERROR(VLOOKUP(A2,Table,2,FALSE),"Not Found") |
=VLOOKUP(A2,Table,2,FALSE) |
=IFERROR(XLOOKUP(...),"") |
=XLOOKUP(...) |
Kết quả là các công thức trở về trạng thái ban đầu, cho phép Excel hiển thị đầy đủ các lỗi như #N/A, #DIV/0!, #VALUE!,... để phục vụ việc kiểm tra và gỡ lỗi mô hình tính toán. Khi cần "lột bỏ lớp bảo vệ" để xem lỗi thật đang nằm ở đâu, lệnh này sẽ giúp bạn thực hiện chỉ với một cú nhấp chuột.
Minh họa chức năng:
Trước
=IFERROR(A1/B1,0)
=IFERROR(VLOOKUP(...),"")
=IFERROR(XLOOKUP(...),"N/A")
│
▼
RemoveIFERROR
│
▼
=A1/B1
=VLOOKUP(...)
=XLOOKUP(...)
✔ Khôi phục công thức gốc
✔ Hiển thị lỗi thực tế để kiểm tra
Biểu tượng minh họa:
🛡️ ➖ 📐 ➜ 🐞
Chức năng:
Loại bỏ tham chiếu tên worksheet khỏi các công thức trong những worksheet được chọn, giúp chuyển các công thức từ dạng tham chiếu có tên sheet sang tham chiếu nội bộ trong cùng worksheet.
Hướng dẫn:
Chọn một hoặc nhiều worksheet rồi chạy RemoveSheetReferences. Chương trình sẽ:
Duyệt từng worksheet được chọn.
Tìm các tham chiếu đến chính worksheet đó trong công thức, ví dụ:
'Sheet1'!
Sheet1!
Tự động thay thế các tham chiếu này bằng chuỗi rỗng.
Giữ nguyên toàn bộ phần còn lại của công thức.
Ví dụ:
| Trước | Sau |
|---|---|
='Sales'!A1+B1 |
=A1+B1 (khi chạy trên sheet Sales) |
=SUM(Sales!A:A) |
=SUM(A:A) |
='Report 2025'!C5*D5 |
=C5*D5 |
Lệnh chỉ loại bỏ tham chiếu đến chính worksheet đang xử lý, không thay đổi các tham chiếu đến worksheet khác.
Kết quả là công thức trở nên ngắn gọn hơn và phù hợp khi bạn sao chép, tách hoặc tái sử dụng worksheet mà không cần giữ các tham chiếu dư thừa đến chính nó. Rất tiện sau khi copy một sheet từ workbook khác, khi hàng loạt công thức vẫn "gọi đúng tên chủ cũ" dù đang đứng ngay trên chính sheet đó.
Minh họa chức năng:
Trước
='Report'!A1+B1
=SUM(Report!C:C)
│
▼
RemoveSheetReferences
│
▼
=A1+B1
=SUM(C:C)
✔ Loại bỏ tên sheet
✔ Giữ nguyên logic công thức
Biểu tượng minh họa:
📄 ➖ 🏷️ ➜ 🧮
Chức năng:
Tự động tìm và xóa tất cả worksheet trống trong workbook hiện tại. Một worksheet được xem là trống khi không có dữ liệu và không chứa bất kỳ đối tượng (Shape) nào.
Hướng dẫn:
Chạy lệnh DeleteEmptySheets, chương trình sẽ:
Không có dữ liệu trong bất kỳ ô nào.
Không chứa hình ảnh, biểu đồ, nút bấm hoặc đối tượng đồ họa.
Lệnh chỉ xóa các sheet hoàn toàn trống, vì vậy những worksheet có dữ liệu hoặc có đối tượng vẫn được giữ nguyên.
Kết quả là workbook được dọn dẹp gọn gàng hơn, loại bỏ các sheet rỗng phát sinh trong quá trình làm việc hoặc sao chép dữ liệu. Nếu workbook của bạn có hàng chục sheet "đẻ nhầm" nhưng chẳng chứa gì ngoài... không khí, lệnh này sẽ dọn sạch chỉ trong vài giây.
Minh họa chức năng:
Workbook
✔ Data
✔ Report
○ Sheet1 (trống)
○ Sheet2 (trống)
✔ Dashboard
○ Sheet3 (trống)
│
▼
DeleteEmptySheets
│
▼
Workbook
✔ Data
✔ Report
✔ Dashboard
✔ Các sheet trống đã được xóa
Biểu tượng minh họa:
🗂️ ➜ 🗑️ 📄 ➜ ✅
Chức năng:
Thiết lập Data Validation để ngăn người dùng nhập giá trị trùng lặp trong một vùng dữ liệu. Sau khi áp dụng, Excel sẽ tự động kiểm tra và từ chối các giá trị đã tồn tại trong cùng vùng.
Hướng dẫn:
Chạy lệnh PreventDuplicate, sau đó:
Chọn vùng dữ liệu cần kiểm soát.
Chương trình sẽ kiểm tra xem worksheet có đang được bảo vệ hay không.
Nếu worksheet không bị khóa, lệnh sẽ:
Xóa các quy tắc Data Validation hiện có trong vùng.
Tạo quy tắc mới sử dụng hàm COUNTIF() để kiểm tra trùng lặp.
Hiển thị thông báo "Duplicate Entry" nếu người dùng nhập một giá trị đã tồn tại.
Khi hoàn tất, chương trình thông báo quá trình thiết lập thành công.
Ví dụ:
| Giá trị đã có | Người dùng nhập | Kết quả |
|---|---|---|
| KH001 | KH002 | ✔ Chấp nhận |
| KH001 | KH001 | ❌ Báo "Duplicate Entry" |
Kết quả là vùng dữ liệu luôn duy trì các giá trị duy nhất, giúp hạn chế nhập trùng mã khách hàng, mã sản phẩm, số chứng từ, số hóa đơn hoặc các khóa dữ liệu khác. Thiết lập một lần, sau đó Excel sẽ làm "người gác cổng", không cho bản ghi trùng lọt vào dữ liệu của bạn.
Minh họa chức năng:
Danh sách
KH001
KH002
KH003
│
▼
PreventDuplicate
│
▼
Nhập: KH002
❌ Duplicate Entry
Nhập: KH004
✔ Accepted
Biểu tượng minh họa:
🛡️ 🚫 📋
Chức năng:
Chuyển các công thức tùy chỉnh (Add-in/UDF) chứa một chuỗi xác định thành giá trị tĩnh trên tất cả các worksheet đang được chọn. Lệnh đặc biệt hữu ích khi muốn loại bỏ sự phụ thuộc vào Add-in trước khi chia sẻ hoặc lưu trữ file.
Hướng dẫn:
Khi chạy lệnh, chương trình sẽ yêu cầu nhập một chuỗi nhận diện có trong công thức (ví dụ: miniMis, MyFunc, SQL, API...). Sau đó:
Quét toàn bộ các worksheet đang được chọn.
Kiểm tra từng ô trong vùng dữ liệu.
Nếu ô chứa công thức và công thức có chuỗi vừa nhập, công thức sẽ được thay bằng kết quả hiện tại của nó.
Hiển thị số lượng công thức đã được chuyển đổi sau khi hoàn tất.
Ví dụ:
| Trước | Sau |
|---|---|
=miniMisLookup(A2) |
1250 |
=MyFunc(B5) |
Completed |
=SUM(C:C) |
(Không thay đổi) |
Kết quả là các công thức của Add-in được "đóng băng" thành giá trị, giúp workbook hoạt động độc lập mà không cần cài đặt Add-in hoặc hàm tùy chỉnh. Giống như chụp ảnh kết quả tính toán: vẫn nhìn thấy kết quả nhưng công thức phía sau sẽ không còn nữa.
Minh họa chức năng:
Công thức Add-in
=miniMisLookup(A2)
=miniMisRate(B2)
=SUM(C:C)
│
▼
CustomFormulaToValue
(Nhập: miniMis)
│
▼
1250
3.75%
=SUM(C:C)
Biểu tượng minh họa:
🧩 ➜ 🔒 📄
Chức năng:
Sao chép công thức từ một vùng nguồn sang vùng đích mà không sao chép định dạng, giá trị, màu sắc hay ghi chú. Các tham chiếu trong công thức sẽ được Excel tự điều chỉnh theo vị trí mới như khi sao chép công thức thông thường.
Hướng dẫn:
Nếu trước đó bạn đã Copy (Ctrl+C) một vùng dữ liệu, lệnh sẽ tự sử dụng vùng đó làm nguồn công thức.
Nếu chưa có vùng được sao chép, chương trình sẽ yêu cầu bạn chọn vùng chứa công thức.
Tiếp theo, chọn vùng đích để dán công thức:
Nếu chỉ chọn một ô, chương trình sẽ tự mở rộng vùng đích theo đúng kích thước của vùng nguồn.
Nếu chọn một vùng, kích thước vùng đích nên tương ứng với vùng nguồn để đảm bảo kết quả chính xác.
Sau khi hoàn tất, toàn bộ công thức sẽ được sao chép sang vùng mới, trong khi định dạng và dữ liệu hiện có tại vùng đích vẫn được giữ nguyên.
Kết quả:
Chỉ có công thức được thay thế tại vùng đích. Đây là lựa chọn rất hữu ích khi muốn cập nhật logic tính toán mà không làm thay đổi giao diện bảng tính. Giống như thay "bộ não" của các ô mà vẫn giữ nguyên "ngoại hình" của chúng.
Biểu tượng minh họa:
┌──────────────┐ 📋 Formula Only ┌──────────────┐
│ =SUM(A1:A5) │ ─────────────────────▶ │ =SUM(D1:D5) │
│ =A1*B1 │ │ =D1*E1 │
└──────────────┘ └──────────────┘
🎨 Định dạng giữ nguyên 🎨 Không thay đổi
Chức năng:
Sao chép chỉ các ô đang hiển thị từ một vùng dữ liệu và dán liên tục vào một vùng khác, tự động bỏ qua các hàng đang bị ẩn. Bạn có thể lựa chọn dán giữ nguyên công thức/định dạng hoặc chỉ dán giá trị.
Hướng dẫn:
Chọn hoặc xác nhận vùng nguồn cần sao chép.
Chọn ô hoặc vùng đích để bắt đầu dán dữ liệu.
Chọn hình thức dán:
Yes: Chỉ dán giá trị (Values).
No: Dán theo thao tác sao chép thông thường (bao gồm công thức và định dạng).
Chương trình sẽ lần lượt:
Chỉ lấy các ô nằm trên những hàng đang hiển thị.
Bỏ qua toàn bộ các hàng bị Hide.
Dán dữ liệu liên tiếp vào các hàng đang hiển thị của vùng đích.
Kết quả:
Dữ liệu được sao chép gọn gàng mà không mang theo các hàng bị ẩn, rất phù hợp khi làm việc với bảng đã lọc (Filter) hoặc các báo cáo có nhiều dòng được ẩn. Giống như chỉ mời những người đang có mặt lên xe, còn ai đang "ẩn mình" thì xe cứ thế lướt qua.
Biểu tượng minh họa:
Nguồn Đích
👁 A1 A1
🙈 A2 (ẩn) ───▶ A2
👁 A3 A3
🙈 A4 (ẩn) ...
👁 A5
Chỉ các hàng 👁 được sao chép.
Chức năng:
Đánh số thứ tự (STT) tự động bằng công thức cho cột được chọn. STT được tạo bằng hàm AGGREGATE, giúp tự động cập nhật khi dữ liệu được lọc (Filter) hoặc ẩn hàng, thay vì chỉ đánh số cố định.
Hướng dẫn:
Chọn một cột hoặc vùng một cột cần đánh số thứ tự.
Chương trình sẽ kiểm tra kích thước vùng chọn và hiển thị cảnh báo nếu vùng quá lớn.
Xác nhận để bắt đầu thực hiện.
Toàn bộ dữ liệu hoặc công thức hiện có trong cột được chọn sẽ được xóa và thay bằng công thức đánh số.
Công thức được chèn có dạng:
=AGGREGATE(4,7,R...C:R[-1]C)+1
Nhờ sử dụng AGGREGATE, số thứ tự sẽ tự điều chỉnh theo các dòng đang hiển thị khi áp dụng bộ lọc dữ liệu.
Lưu ý:
Chỉ nên chọn một cột để đánh số.
Dữ liệu hoặc công thức hiện có trong vùng chọn sẽ bị ghi đè.
Phù hợp với các bảng dữ liệu thường xuyên Filter hoặc Hide/Unhide dòng, giúp STT luôn liên tục mà không cần đánh lại.
Kết quả:
Bạn sẽ có một cột STT thông minh, tự cập nhật theo trạng thái hiển thị của bảng dữ liệu. Lọc còn 15 dòng thì STT cũng chỉ chạy từ 1 đến 15, không còn cảnh nhảy số khiến người xem phải "đếm bằng mắt".
Biểu tượng minh họa:
📋 Dữ liệu
□
□
□
□
│
▼
🔢 DanhSTT2
1
2
3
4
(📌 Tự cập nhật khi Filter)
Chức năng:
Tạo danh sách số ngẫu nhiên không trùng lặp trong một khoảng giá trị xác định và điền trực tiếp vào vùng ô được chọn. Đây là công cụ hữu ích để tạo mã số, số thứ tự ngẫu nhiên, bốc thăm hoặc lấy mẫu dữ liệu mà không có giá trị lặp lại.
Hướng dẫn:
Chọn vùng ô sẽ nhận kết quả.
Nhập:
From value: Giá trị bắt đầu của khoảng số.
To value: Giá trị kết thúc của khoảng số.
Chương trình sẽ:
Sinh các số ngẫu nhiên trong khoảng đã nhập.
Đảm bảo mỗi số chỉ xuất hiện một lần.
Chỉ điền vào các ô đang hiển thị (bỏ qua các ô bị ẩn bởi Filter hoặc Hide).
Không ghi đè lên các ô đã có dữ liệu.
Lưu ý:
Số lượng ô cần điền phải nhỏ hơn số lượng giá trị có thể tạo trong khoảng đã chọn. Ví dụ:
Khoảng từ 1 đến 100 chỉ tạo được tối đa 100 số khác nhau.
Không thể điền 150 ô bằng các số không trùng trong khoảng này.
Vùng chọn tối đa 32.768 ô hiển thị.
Các ô đã có dữ liệu sẽ được giữ nguyên, chỉ các ô trống mới được điền số.
Kết quả:
Bạn sẽ nhận được một danh sách số ngẫu nhiên không trùng lặp, sẵn sàng sử dụng cho bốc thăm, phát mã, chia nhóm hoặc kiểm thử dữ liệu. Giống như bốc từng lá thăm khỏi chiếc hộp: đã lấy rồi thì sẽ không thể bốc trúng lần thứ hai.
Biểu tượng minh họa:
🎲 Khoảng số
1000 ─────────► 65000
│
▼
🔄 Sinh số ngẫu nhiên
❌ Không trùng lặp
│
▼
15432
60891
12775
42108
59360
...