Dùng Copilot viết công thức XLOOKUP trong Excel và cách kiểm tra cho chắc
Tra mã nhân viên, điền dữ liệu từ sheet khác hay cộng theo khoảng: cách nhờ Copilot viết XLOOKUP, mẫu prompt chép được ngay và các bước tự kiểm tra để không nộp báo cáo sai số.
Cuối tháng, bạn có bảng chấm công ở sheet này và danh sách nhân viên ở sheet khác. Việc cần làm là điền họ tên và phòng ban vào bảng chấm công theo mã nhân viên. Nhiều khi cột mã lại nằm bên phải cột cần lấy về, mà VLOOKUP quen tay chỉ tìm được từ trái sang phải.
Lúc này bạn có thể nhờ AI viết công thức rồi tự kiểm tra lại. Các bản Excel mới có hàm XLOOKUP, và Copilot trong Excel có thể viết, giải thích và áp công thức giúp bạn. Bài này là quy trình dùng hằng ngày: nhờ AI viết, đọc hiểu, rồi kiểm tra trước khi gửi sếp.
XLOOKUP là gì và vì sao nên dùng
Theo Microsoft, XLOOKUP tìm một giá trị trong một cột (hoặc hàng) rồi trả về kết quả ở cùng dòng của cột khác, bất kể cột trả về nằm bên trái hay bên phải. Ví dụ: tra giá phụ tùng theo mã hàng, hoặc tìm tên nhân viên theo mã nhân viên.
Cú pháp:
=XLOOKUP(lookupvalue, lookuparray, returnarray, [ifnotfound], [matchmode], [searchmode])
Ba đối số đầu là bắt buộc, ba đối số trong ngoặc vuông là tùy chọn:
lookupvalue: giá trị cần tìm (ví dụ ô chứa mã nhân viên).
lookuparray: cột/vùng để tìm.
returnarray: cột/vùng chứa kết quả cần lấy về.
ifnotfound: chữ hiển thị khi không thấy. Nếu bỏ trống, Excel trả về #N/A.
matchmode: mặc định là 0, tức khớp chính xác. Giá trị -1 là khớp chính xác, không có thì lấy mục nhỏ hơn kế tiếp. Giá trị 1 là lấy mục lớn hơn kế tiếp. Giá trị 2 là khớp ký tự đại diện (wildcard).
searchmode: mặc định 1 là tìm từ đầu đến cuối. Giá trị -1 là tìm ngược từ dòng cuối.
So với VLOOKUP: công thức =VLOOKUP(F2,B2:D11,3,FALSE) phải đếm "cột thứ 3", còn XLOOKUP chỉ thẳng vào cột kết quả. XLOOKUP cũng trả về được nhiều cột cùng lúc, ví dụ cả tên lẫn phòng ban chỉ bằng một công thức.
Lưu ý quan trọng: XLOOKUP không có trong Excel 2016 và Excel 2019. Nếu đồng nghiệp hoặc đối tác còn dùng bản cũ, file có XLOOKUP sẽ không chạy đúng trên máy họ.
Copilot trong Excel giúp được gì
Copilot trong Excel làm việc bằng chính các công cụ của Excel: bảng, biểu đồ, PivotTable và công thức. Nội dung vẫn chỉnh sửa được và bạn kiểm soát những gì bị thay đổi. Theo hướng dẫn của Microsoft, nó có thể:
tạo dữ liệu và công thức, tính toán trên nhiều sheet, tóm tắt dữ liệu;
sắp xếp, lọc, tô màu có điều kiện;
tạo PivotTable và biểu đồ.
Có ba chế độ:
| Chế độ | Làm gì | Khi nào dùng |
|---|---|---|
| Edit (mặc định) | Sửa trực tiếp workbook | File đã sao lưu, việc rõ ràng |
| Plan | Lập kế hoạch để bạn duyệt trước khi làm | Việc nhiều bước, dữ liệu quan trọng |
| Chat | Chỉ trả lời trong khung chat, không đụng vào file | Hỏi cách viết, nhờ giải thích công thức |
Mức hỗ trợ theo nền tảng:
Windows, Mac, web: đủ Edit, Plan, Chat.
iPad: Chat và Plan (Edit đang triển khai).
iPhone: Chat (Edit đang triển khai).
Android: Chat.
Nếu không thấy biểu tượng Copilot, có thể gói Microsoft 365 hoặc cài đặt của công ty bạn chưa bật. Hãy hỏi bộ phận IT.
Các bước thực hiện
Sao lưu file (Save As bản mới) trước khi để Copilot sửa.
Mở Copilot: bấm biểu tượng Copilot ở góc dưới bên phải cửa sổ Excel.
Chọn chế độ. Lần đầu nên dùng Chat để lấy công thức rồi tự dán, hoặc Plan để xem kế hoạch trước.
Gõ yêu cầu, nói rõ sheet, cột, ô và kết quả mong muốn (xem mẫu bên dưới). Các mẫu dưới đây viết bằng tiếng Việt. Nếu Copilot của bạn trả lời không ổn, hãy xem danh sách ngôn ngữ được hỗ trợ trên trang của Microsoft hoặc thử viết prompt bằng tiếng Anh.
Đọc công thức, kiểm tra theo phần "Cách kiểm tra" rồi mới áp dụng.
Cần dừng giữa chừng thì bấm nút Stop ở góc dưới bên phải ô nhập chat.
Mẫu prompt chép được ngay
Tra cứu theo mã:
Trong sheet <tên sheet bảng chấm công>, cột <A> là mã nhân viên.
Trong sheet <tên sheet danh sách NV>, cột <A> là mã, cột <B> là họ tên, cột <C> là phòng ban.
Hãy viết công thức XLOOKUP để lấy họ tên và phòng ban theo mã ở ô <A2>.
Nếu không tìm thấy thì hiện "Không có trong danh sách".
Giải thích từng đối số bằng tiếng Việt đơn giản.
Giải thích công thức người khác để lại:
Giải thích từng bước công thức này làm gì, bằng ngôn ngữ cho người không rành Excel:
<dán công thức>
Chỉ ra chỗ nào có thể gây lỗi nếu dữ liệu thiếu hoặc trùng.
Tổng hợp và lọc:
Từ bảng dữ liệu ở sheet <tên sheet>, vùng <A1:E9>, hãy tạo PivotTable tổng hợp <số tiền> theo <phòng ban>.
Chưa chỉnh sửa gì cả, hãy cho tôi xem kế hoạch trước.
Ví dụ nhỏ
Sheet "Danh sách NV" có mã ở cột A, họ tên ở cột B, phòng ban ở cột C, dữ liệu từ dòng 2 đến dòng 9. Ở bảng chấm công, mã nằm ở ô A2. Công thức hợp lý sẽ trông như sau:
=XLOOKUP(A2,'Danh sách NV'!$A$2:$A$9,'Danh sách NV'!$B$2:$B$9,"Không có trong danh sách")
Ở ví dụ này cột mã nằm bên trái nên VLOOKUP cũng làm được. XLOOKUP vẫn dùng được khi cột mã nằm bên phải cột kết quả.
Muốn lấy cả họ tên lẫn phòng ban bằng một công thức, đặt returnarray là 'Danh sách NV'!$B$2:$C$9. Hai kết quả sẽ hiện ở các ô liền kề.
Với bài toán tổng hợp theo khoảng, Microsoft có ví dụ ghép hai XLOOKUP trong SUM để cộng các giá trị nằm giữa hai mục (từ "nho" đến "chuối", gồm cả mục "lê" ở giữa). Trong công thức, B3 và C3 chứa hai mục đầu và cuối:
=SUM(XLOOKUP(B3,B6:B10,E6:E10):XLOOKUP(C3,B6:B10,E6:E10))
XLOOKUP ở đây trả về một vùng ô, nên Excel hiểu công thức thành dạng =SUM($E$7:$E$9).
Cách kiểm tra kết quả của AI
Đây là phần quyết định. AI viết nhanh nhưng không thay bạn chịu trách nhiệm về con số.
Xem từng bước: chọn ô có công thức, vào Formulas > Formula Auditing > Evaluate Formula, rồi bấm Evaluate để Excel chạy từng bước tính. Microsoft cũng dùng cách này để giải thích các công thức XLOOKUP lồng nhau.
Kiểm tra tham chiếu: vùng tra cứu nên khóa cứng ($A$2:$A$9) để kéo công thức xuống không bị lệch. Ô mã cần tra để tương đối (A2) để nó đổi theo từng dòng.
Tên sheet có dấu cách hoặc có số: cần có dấu nháy đơn, ví dụ 'Danh sách NV'!A1.
Thử ca khó: nhập một mã không tồn tại để xem thông báo ifnotfound có hiện đúng không. Chọn vài dòng, tự tra tay rồi đối chiếu.
Mã trùng: XLOOKUP trả về kết quả khớp đầu tiên nó gặp. Nếu danh sách có mã trùng, hãy rà lại để không lấy nhầm dòng.
Kiểm tra matchmode: khớp chính xác là mặc định. Chỉ dùng -1 hoặc 1 khi thật sự cần tra theo bậc (như bậc thuế), và xác nhận lại AI chọn đúng chiều "nhỏ hơn" hay "lớn hơn".
Phiên bản Excel của người nhận: nếu họ dùng Excel 2016/2019, XLOOKUP sẽ không chạy.
Microsoft cũng ghi chú rằng kết quả tính toán của một số hàm có thể lệch nhẹ giữa máy Windows x86/x86-64 và Windows RT chạy ARM.
Lưu ý bảo mật
Không dán dữ liệu mật hoặc dữ liệu cá nhân (lương, CCCD, số tài khoản, hợp đồng) vào chatbot công cộng. Muốn nhờ viết công thức, hãy đổi bằng dữ liệu mẫu, hoặc chỉ dùng tiêu đề cột và vài dòng giả.
Copilot trong Excel có thể không nằm trong gói của bạn, và việc dùng nó phụ thuộc vào cài đặt của tổ chức. Hỏi IT hoặc xem chính sách công ty trước khi dùng với file nội bộ.
Luôn làm việc trên bản sao. Ở chế độ Edit, Copilot sửa trực tiếp workbook.
Nhận định của chúng tôi: dù Copilot hiển thị các bước nó làm, bạn vẫn là người ký tên dưới báo cáo. Số tiền, tên người, tổng cộng phải do người kiểm tra lại.
Checklist trước khi gửi file
[ ] Đã lưu bản sao trước khi nhờ Copilot sửa.
[ ] Đã mô tả rõ sheet, cột, ô trong prompt.
[ ] Đã đọc phần giải thích công thức và hiểu từng đối số.
[ ] Đã dùng Evaluate Formula ít nhất một lần.
[ ] Đã thử mã không tồn tại và vài dòng tự tra tay.
[ ] Đã kiểm tra mã trùng trong danh sách tra cứu.
[ ] Tham chiếu tuyệt đối/tương đối đúng khi kéo xuống.
[ ] Người nhận không dùng Excel 2016/2019 (hoặc đã có phương án thay thế).
[ ] Không có dữ liệu nhạy cảm bị đưa vào công cụ không được phép.