Thủ thuật Excel này cho phép bạn ghi chú công thức giống như mã lập trình

[Skynet]

Thêm chú thích trực tiếp vào thanh công thức để tăng tính rõ ràng.

Ghi chú ô là thứ gây rối trong thế giới Excel — chúng che khuất tầm nhìn và cản trở các công thức. Đó là lý do tại sao bạn nên áp dụng tư duy của nhà phát triển bằng cách đặt ghi chú bên trong công thức, giữ cho logic gọn gàng, dữ liệu dễ nhìn và bảng tính chuyên nghiệp.

1. Hàm N(): Sổ tay vô hình mới của bạn

Cách đơn giản nhất để bắt đầu ghi chép công thức Excel như một lập trình viên là sử dụng hàm N(). Mặc dù chức năng chính của nó là chuyển đổi các giá trị không phải số thành số, nhưng nó có một đặc điểm thú vị: vì các chuỗi văn bản bên trong hàm này được đánh giá là bằng không, bạn có thể thêm chúng vào bất kỳ công thức toán học nào mà không làm thay đổi kết quả cuối cùng.

Giả sử bạn đang tính toán cột Hoa hồng trong bảng T_Comm, trong đó hoa hồng của nhân viên cấp cao được tính khác với nhân viên cấp thấp. Với một công thức không được ghi chép đầy đủ, bất kỳ ai xem bảng tính đều phải phân tích ngược lại phép toán để hiểu logic nghiệp vụ:

Mã nguồn [Chọn] Expand

=IF([@Rank]="Senior",[@Profit]*0.2,[@Profit]*0.15)

Đây là lúc hàm N() phát huy tác dụng. Bằng cách thêm nó vào cuối phép tính, bạn tạo ra một khoảng trống để ghi chú bằng ngôn ngữ thông thường, những ghi chú này sẽ ẩn khỏi lưới bảng tính nhưng vẫn hiển thị trong thanh công thức. Luôn luôn đặt ghi chú của bạn trong dấu ngoặc kép — nếu không, Excel sẽ coi đó là một công thức bị lỗi và trả về lỗi #NAME?:

Mã nguồn [Chọn] Expand

=IF([@Rank]="Senior",[@Profit]*0.2,[@Profit]*0.15)+N("Senior rate is 20%; Junior/Unknown is 15%")

Giờ đây, bạn đã gắn cố định phần giải thích vào phép tính, vì vậy câu trả lời cho mọi thắc mắc về lý do đã có sẵn trong công thức.

Sử dụng tổ hợp phím Alt+Enter để di chuyển phần +N() của công thức sang một dòng riêng. Điều này tạo ra một bố cục trực quan rõ ràng, trong đó phép toán nằm ở trên cùng và phần giải thích nằm ngay bên dưới. Mở rộng thanh công thức (Ctrl+Shift+U) sẽ hiển thị chế độ xem đầy đủ dành cho nhà phát triển.

3. Vấn đề về văn bản: Làm thế nào để thêm chú thích mà không làm hỏng công thức của bạn

Giờ hãy tưởng tượng bạn thêm một cột Trạng thái để đánh dấu các cấp bậc không xác định trong bảng T_Comm, để nhóm Nhân sự có thể sửa chúng. Nếu bạn cố gắng thêm một chú thích +N() — ví dụ như "Kiểm tra Hồ sơ Nhân sự" — vào kết quả dạng văn bản, Excel sẽ báo lỗi #VALUE! vì nó không thể thực hiện phép cộng trên một từ. Để ghi chú các công thức dựa trên văn bản, bạn cần "ghép" chú thích vào bằng cách sử dụng một chuỗi rỗng vô hình.

Đây là kỹ thuật xuống dòng kép giúp chuyển đổi bình luận của bạn thành định dạng không làm hỏng chuỗi văn bản:

Mã nguồn [Chọn] Expand

=IF([@Rank]="Unknown","Check HR Profile","Verified")&T(N("Flagging for HR cleanup; rank must be Senior or Junior"))

Ở đây, hàm N() chuyển đổi văn bản thành số không. Sau đó, hàm T() xem xét số không đó và—vì nó không phải là văn bản—trả về một chuỗi rỗng (""). Về cơ bản, bạn đang thêm "không có gì" vào thông báo trạng thái của mình, vì vậy ô vẫn không thay đổi, nhưng ghi chú của bạn được lưu trữ an toàn trong thanh công thức.

Không giống như phiên bản dựa trên toán học, nơi bạn sử dụng dấu "+" để cộng với số 0, tài liệu dạng văn bản yêu cầu toán tử nối chuỗi (&), vì bạn đang ghép hai chuỗi văn bản lại với nhau (thay vì cộng các số). Bằng cách đặt toán tử nối chuỗi ở đầu dòng mới (sử dụng Alt+Enter), bạn đang báo hiệu cho bất kỳ ai đọc công thức rằng logic đã kết thúc và phần chú thích bắt đầu.

4. Ghi chép logic: Cú pháp +0*N()

Đôi khi, công thức Excel của bạn không trả về một số hoặc chuỗi văn bản mà thay vào đó, nó cho bạn biết liệu một điều kiện có được đáp ứng hay không.

Trong bảng T_Comm của bạn, lần này, bạn muốn thêm một cột Thưởng để đánh dấu các dòng có lợi nhuận cao cho bộ phận tính lương. Nếu bạn thêm nhận xét trực tiếp bằng cách sử dụng +N(), Excel có thể bị nhầm lẫn vì nó coi TRUE là 1 và FALSE là 0 trong các trường hợp Boolean. Do đó, việc thêm nhận xét có thể chuyển đổi logic của bạn thành một con số. Điều này đặc biệt có vấn đề nếu bạn sử dụng hộp kiểm, vì chúng yêu cầu giá trị TRUE và FALSE để hoạt động. Vì vậy, để giữ cho logic được thuần túy, bạn cần nhân nhận xét với 0:

Mã nguồn [Chọn] Expand

=[@Profit]>300+0*N("Profits over $300 qualify for a bonus")

Hãy lưu ý rằng công thức không bao bọc dòng logic trong dấu ngoặc đơn. Nếu làm vậy, kết quả sẽ chuyển đổi thành số, và các ô chọn sẽ không hoạt động như mong đợi.

Tiếp theo, chọn cột và trong tab Chèn, nhấp vào "Ô chọn".

5. Ngoài phần bình luận: Logic đặt tên với hàm LET

Tôi biết bạn đang nghĩ, "Sao không dùng hàm LET?" và đó là một câu hỏi hợp lý! Thật vậy, LET là tiêu chuẩn vàng cho việc lập tài liệu Excel hiện đại vì nó cho phép bạn đặt tên cho logic của mình khi xây dựng nó. Tuy nhiên, có một sự khác biệt cơ bản giữa một chú thích và một biến.

Trong khi hàm N() cho phép bạn diễn giải công thức của mình bằng ngữ cảnh bên ngoài, hàm LET cho phép bạn định nghĩa cấu trúc bên trong của công thức. Một biến cho bạn biết giá trị đó đại diện cho điều gì, trong khi một chú thích cho bạn biết lý do tại sao giá trị đó tồn tại ngay từ đầu.

Giả sử bạn muốn dọn dẹp bảng T_Comm bằng cách tính toán khoản thanh toán cuối cùng bao gồm:

    Hoa hồng 15% cho nhân viên cấp dưới.
    Hoa hồng 20% dành cho nhân viên cấp cao.
    Thưởng thêm 50 đô la nếu lợi nhuận của ai đó vượt quá 300 đô la.
    Thưởng 50 đô la cho bất kỳ ai ở cấp bậc cao cấp.

Sử dụng LET, bạn có thể định nghĩa các biến có tên này trước khi bắt đầu thực hiện các phép toán:

Mã nguồn [Chọn] Expand

=LET(
JuniorRate, 0.15,
SeniorRate, 0.2,
BonusAmt, 50,
IsSenior, [@Rank]="Senior",
HighProfit, [@Profit]>300,
([@Profit]*IF(IsSenior, SeniorRate, JuniorRate))
+IF(HighProfit, BonusAmt, 0)
+IF(IsSenior, BonusAmt, 0)
)

Cấu trúc này cho phép bạn thay thế các con số quan trọng và các tham chiếu ô gây nhầm lẫn bằng các biến được đặt tên rõ ràng như SeniorRate và BonusAmt, giúp công thức dễ đọc hơn, giống như một câu chuyện mạch lạc. Kết quả là, bạn có thể cập nhật tỷ lệ ở một vị trí duy nhất mà không làm hỏng các phép tính tiếp theo, đồng thời tách biệt "ý nghĩa" của các biến khỏi "lý do" trong tài liệu của bạn.

6. Tóm tắt: Hướng dẫn dành cho nhà phát triển về tài liệu công thức Excel

Tóm lại, đây là hướng dẫn tham khảo nhanh giúp bạn lựa chọn chiến lược lập tài liệu phù hợp dựa trên loại dữ liệu của mình:

Nếu bạn nhất thiết phải thêm ghi chú vào bảng tính nhưng không muốn có thẻ đỏ khó chịu ở góc ô, thì có một mẹo nhỏ nữa. Mặc dù N() và LET rất phù hợp để ghi chú logic bên trong công thức, đôi khi bạn cần cung cấp hướng dẫn cho người sử dụng bảng tính. Bằng cách sử dụng Xác thực dữ liệu, bạn có thể tạo các ghi chú bật lên bí mật chỉ xuất hiện khi ai đó chọn một ô cụ thể. Điều này giúp bảng tính của bạn gọn gàng và không bị rối mắt, đồng thời vẫn cung cấp hướng dẫn đúng nơi cần thiết.