Master Excel Copilot:Xây dựng và giải mã các công thức phức tạp từng bước

Thông thường, hầu hết các hướng dẫn đều dừng lại ở việc “yêu cầu Copilot viết công thức”. Điều này rất hữu ích, nhưng giá trị thực sự là ở chỗ:bạn có thể mô tả một kịch bản kinh doanh lộn xộn bằng tiếng Anh đơn giản, để Copilot soạn thảo công thức, sau đó sử dụng tính năng Giải thích công thức này của Excel để hiểu chính xác những gì nó được xây dựng và tại sao. Điều này sẽ giúp bạn sử dụng sức mạnh thực sự của Copilot, nó đang dạy cho bạn cách hoạt động của các công thức thực sự phức tạp.

Trong hướng dẫn này, chúng tôi sẽ hướng dẫn cách sử dụng Copilot trong Excel để xây dựng và hiểu các công thức phức tạp. Bạn sẽ mô tả mục tiêu kinh doanh bằng ngôn ngữ hàng ngày và để Copilot tạo ra công thức phức tạp.

Điều kiện tiên quyết: Đăng ký Microsoft 365 đã bật Copilot (giấy phép Copilot trả phí cung cấp đầy đủ các tính năng trong lưới và giải thích được hiển thị ở đây).

Bước 1:Chuẩn bị dữ liệu của bạn

Điều quan trọng là phải cấu trúc dữ liệu của bạn trước khi sử dụng Copilot. Dữ liệu của bạn phải ở dạng bảng hoặc phạm vi được hỗ trợ để Copilot có thể hiểu chính xác. Microsoft đặc biệt khuyên bạn nên định dạng dữ liệu trong bảng hoặc phạm vi được hỗ trợ trước khi sử dụng Copilot trong Excel.

• Chọn toàn bộ phạm vi
• Đi tới Chèn tab>> chọn Bảng hoặc nhấn Ctrl+T
• Kiểm tra “Bảng của tôi có tiêu đề”
• Nhấp vào OK

• Đi tới Thiết kế bảng tab>> chọn Hộp tên bảng>> đặt tên cho bảng SalesData

Bây giờ bạn đã có dữ liệu có cấu trúc mà Copilot có thể tham khảo dễ dàng.

Bước 2:Mô tả mục tiêu của bạn bằng tiếng Anh đơn giản (Không cần cú pháp công thức)

Đừng bắt đầu bằng việc “viết cho tôi một công thức”. Bắt đầu với mục tiêu. Giả sử bạn muốn một công thức tràn (một công thức tự động mở rộng) để trả lời một câu hỏi kinh doanh thực tế. Hãy cùng khám phá câu hỏi kinh doanh sau:

"I want a formula for the Sales Log column E that looks up the rep's Region,
then checks if their Revenue is above $10,000 — if yes, use the 'High' tier rate
from the Commission table; if no, use the 'Standard' tier rate. Both Region and Tier
need to match."

Hãy chú ý những gì bạn đã làm ở đó:bạn đã mô tả logic chứ không phải cú pháp. Bạn đã nói với Copilot về cấu trúc dữ liệu, điều kiện và kết quả mong đợi — chính xác là những việc mà bạn thường phải mất 10–15 phút để viết công thức thủ công.

Cách hỏi Phi công phụ:

• Truy cập Trang chủ tab>> chọn Phi công phụ
• Nhập hoặc dán chính xác lời nhắc bằng tiếng Anh đơn giản ở trên
• Nhấn Enter hoặc nhấp vào nút gợi ý
• Copilot ngay lập tức tạo công thức và chèn nó vào ô Excel
• Hiển thị bản xem trước kết quả trên lưới

Trả về công thức phụ:

=XLOOKUP(C2&IF(D2>10000,"High","Standard"),'Commission Table'!$A$2:$A$9&'Commission Table'!$B$2:$B$9,'Commission Table'!$C$2:$C$9)

Số cột chính xác có thể thay đổi đôi chút tùy theo bảng của bạn; Phi công phụ thường làm đúng.

Bước 3:Yêu cầu phi công phụ giải thích công thức (Khoảnh khắc học tập thực sự)

Bây giờ, hãy khám phá phần thú vị mà hầu hết mọi người thường bỏ qua.

• Nhấp vào bất kỳ ô nào chứa công thức bị đổ
• Nhấp chuột phải>> chọn Đề xuất phi công phụ>> chọn “Giải thích công thức này” từ danh sách thả xuống

Copilot ngay lập tức mở một thẻ (hoặc ngăn) giải thích với phần phân tích rõ ràng, từng bước bằng tiếng Anh đơn giản.

Lời giải thích điển hình về Copilot bạn sẽ thấy:

Công thức =XLOOKUP(C2&IF(D2>10000,”High”,,”Standard”),’Bảng hoa hồng’!$A$2:$A$9&’Bảng hoa hồng’!$B$2:$B$9,’Bảng hoa hồng'!$C$2:$C$9) tìm ra tỷ lệ hoa hồng cho bản ghi bán hàng ở hàng 2 bằng cách khớp khu vực và cấp doanh thu với “Bảng hoa hồng”.

• Nó kết hợp vùng trong ô C2 (“Bắc”) với một cấp được xác định bằng việc liệu doanh thu trong D2 (12.500) có lớn hơn 10.000 (“Cao” nếu đúng, “Chuẩn” nếu sai).
• Nó tìm kiếm giá trị kết hợp này (“NorthHigh”) trong “Bảng hoa hồng” bằng cách nối cột Khu vực (A2:A9) và cột Cấp (B2:B9) cho mỗi hàng.
• Khi tìm thấy kết quả khớp, nó sẽ trả về tỷ lệ hoa hồng tương ứng từ cột C2:C9 của “Bảng hoa hồng”.

Ví dụ:vì D2 là 12.500 (>10.000), nên công thức tìm kiếm “NorthHigh” và trả về tỷ lệ từ C3 là 0,08 (8%).

Yêu cầu phi công phụ giải thích từng lớp một

Nếu bạn không làm theo công thức giải thích ở trên thì cũng đừng lo lắng. Trong cùng cuộc trò chuyện Copilot, hãy nhập:

"Can you explain this formula to me one part at a time, starting from the inside out?"

Phi công phụ sẽ hướng dẫn bạn thực hiện điều đó. Đây là nội dung và ý nghĩa thực sự của lời giải thích đó:

Phân tích từng bước (Từ trong ra ngoài)

Bước 1:D2>10000

Đây là sự so sánh sâu sắc nhất.

Bước 2:IF(D2>10000,”Cao”,”Tiêu chuẩn”)

Kết thúc việc so sánh trong câu lệnh IF để trả về tên cấp.

Bước 3:C2&IF(D2>10000,”Cao”,”Tiêu chuẩn”)

Kết hợp Khu vực với Cấp để tạo khóa tra cứu .

Đây là những gì chúng tôi đang tìm kiếm trong Bảng hoa hồng.

Bước 4:‘Bảng hoa hồng’!$A$2:$A$9&’Bảng hoa hồng’!$B$2:$B$9

Tạo mảng tra cứu bằng cách ghép Khu vực + Cấp từ Bảng hoa hồng.

Đây là nội dung chúng tôi đang tìm kiếm .

Bước 5:‘Bảng hoa hồng’!$C$2:$C$9

Đây là mảng trả về — tỷ lệ hoa hồng.

Bước 6:XLOOKUP đầy đủ(…)

Đặt tất cả lại với nhau!

=XLOOKUP( “NorthHigh”, {“NorthStandard”;”NorthHigh”;…}, {0.05;0.08;…} )

Kịch bản kinh doanh với công thức phức tạp

Bạn có thể sử dụng nhiều loại lời nhắc khác nhau. Bạn đề cập đến tên cột và tiêu chí càng chính xác thì khả năng nhận được công thức chính xác càng cao. Hãy cùng khám phá câu hỏi kinh doanh sau:

"Chỉ cho tôi xem doanh số bán hàng Máy tính ở khu vực phía Đông. Liệt kê từng nhân viên bán hàng duy nhất, tổng doanh số bán hàng của họ trong khoảng thời gian đó và trạng thái hiệu suất:'Người có hiệu suất cao' nếu trên 500 đô la, 'Trung bình' nếu trên 100–150 đô la, nếu không thì 'Cần cải thiện'. Sắp xếp danh sách theo tổng doanh số giảm dần, với tiêu đề rõ ràng."

Điều này phức tạp vì nó yêu cầu lọc theo nhiều điều kiện (khu vực + danh mục), tổng hợp theo từng người, áp dụng logic theo cấp bậc và sắp xếp — chính xác là loại công việc thường mất 10–15 phút để viết công thức thủ công.

• Nhập hoặc dán chính xác lời nhắc bằng tiếng Anh đơn giản này:

"Create a dynamic spill formula for table SalesData. Filter to Region = "East",
Product Category = "Computers". Return unique Salesperson, their Total Sales Amount,
and a Performance Status column ("High Performer" if total > 2000, "Average" if >= 1000,
otherwise "Needs Improvement"). Sort by Total Sales descending. Include headers:
Salesperson, Total Sales, Performance Status."

• Nhấn Enter hoặc nhấp vào nút gợi ý

Trả về công thức phụ:

=LET(
filtered, FILTER(SalesData, (SalesData[Region]="East")*(SalesData[Category]="Computers"), ""),
salespersons, UNIQUE(INDEX(filtered,,4)),
totals, MAP(salespersons, LAMBDA(sp, SUMIFS(SalesData[SalesAmount], SalesData[Region], "East", SalesData[Category], "Computers", SalesData[Salesperson], sp))),
status, MAP(totals, LAMBDA(t, IFS(t>2000, "High Performer", t>=1000, "Average", TRUE, "Needs Improvement"))),
combined, HSTACK(salespersons, totals, status),
sorted, SORT(combined, 2, -1),
VSTACK({"Salesperson","Total Sales","Performance Status"}, sorted)
)

• Nhấp vào Xong
• Tự động chèn công thức vào ô Excel

Copilot tự động hiển thị công thức được giải thích trong Cách thức hoạt động của công thức này:

Cách thức hoạt động của công thức:

1. LỌC – Nhận tất cả các hàng trong đó Vùng =“Đông” VÀ Danh mục =“Máy tính”
2. ĐỘC ĐÁO – Trích xuất tên nhân viên bán hàng riêng biệt từ dữ liệu đã lọc
3. MAP + SUMIFS – Tính tổng doanh số bán hàng cho từng nhân viên bán hàng duy nhất
4. MAP + IFS – Gán trạng thái hiệu suất dựa trên ngưỡng
5. HSTACK – Kết hợp ba cột theo chiều ngang
6. Sắp xếp – Đơn hàng theo Tổng doanh số (cột 2) theo thứ tự giảm dần (-1)
7. VSTACK – Thêm dòng tiêu đề lên trên cùng

Công thức hoàn toàn linh hoạt và sẽ tự động tính toán lại nếu bạn thêm nhiều dữ liệu bán hàng ở Miền Đông/Máy tính vào bảng!

Bạn cũng có thể sử dụng tính năng Giải thích công thức này cho công thức này. Dù thế nào đi nữa, mục tiêu vẫn là học tập — hãy chọn bất kỳ phương pháp nào phù hợp nhất với bạn.

Bước 4:Lặp lại và tinh chỉnh (Xây dựng độ phức tạp hơn nữa)

Ở trong ngăn Copilot (hoặc gõ =lần nữa) và đưa ra các lời nhắc tiếp theo như:

• “Sửa đổi công thức để hiển thị số lượng đơn đặt hàng của mỗi nhân viên bán hàng ở cột thứ tư.”
• “Tạo các ô tham chiếu ngưỡng $2000 và $1000 K1 và K2 để tôi có thể thay đổi chúng dễ dàng.”
• “Thêm cột hiển thị doanh số trung bình trên mỗi đơn hàng bằng cách sử dụng số ID đơn hàng.”

Mỗi lần, Copilot cập nhật công thức và bạn có thể yêu cầu giải thích lại công thức để xem chính xác những gì đã thay đổi. Vòng lặp lặp đi lặp lại này là cách các chuyên gia chuyển từ công thức cơ bản sang trang tổng quan động cấp sản xuất.

Mẹo chuyên nghiệp để có giá trị tối đa

• Hãy nêu cụ thể lời nhắc bằng tiếng Anh đơn giản của bạn:đặt tên cho bảng, liệt kê các điều kiện chính xác và nói “công thức tràn động” hoặc “bao gồm các tiêu đề”.
• Luôn xác minh kết quả dựa trên dữ liệu của bạn. Phi công phụ cực kỳ có năng lực nhưng không phải không thể sai lầm.
• Học bằng cách sao chép các phần được giải thích vào công thức của riêng bạn sau này; bạn sẽ tiếp thu LET, BYROW, LAMBDA, v.v. nhanh hơn bất kỳ khóa học nào.
• Hoạt động tốt trên các tập dữ liệu lớn (hàng chục nghìn hàng) vì đây là các hàm mảng động được tối ưu hóa.

Kết luận

Bằng cách làm theo các bước trên, bạn có thể sử dụng Copilot trong Excel để xây dựng và hiểu các công thức phức tạp. Bạn có thể sử dụng nó để dịch logic nghiệp vụ thành các công thức, sau đó sử dụng Giải thích công thức này để hiểu kết quả đầu ra. Copilot có thể là một trong những cách dễ dàng và hiệu quả nhất để học các công thức Excel trong khi hoàn thành công việc thực tế. Thông qua quá trình này, bạn sẽ không chỉ đạt được một công thức phức tạp - bạn sẽ hiểu lý do tại sao mọi phần đều tồn tại và cách điều chỉnh nó cho bất kỳ tình huống nào trong tương lai. Đó chính là sức mạnh thực sự của Copilot trong Excel. Hãy thử ngay với dữ liệu mẫu ở trên. Nếu dữ liệu của bạn khác, chỉ cần mô tả mục tiêu của riêng bạn theo cùng một phong cách tiếng Anh đơn giản; quá trình này giống hệt nhau.