Douglas đang gặp một số vấn đề trong việc tìm định dạng tùy chỉnh mà anh ấy cần. Anh ấy đã tạo một định dạng tùy chỉnh hiển thị số lượng lớn theo cách anh ấy muốn, chẳng hạn như 1.000.000 đô la được hiển thị dưới dạng 1,0 triệu đô la và 1.000 đô la được hiển thị là 1,0 nghìn đô la. Định dạng này như sau:

[> 1000000] $ . 0 ,, “M”; [> 1000] $ #. 0, “K”; $ #, # 0.0. Douglas muốn biết cách điều chỉnh định dạng tùy chỉnh để các số âm xuất hiện theo cùng kiểu này, nhưng có màu đỏ với dấu ngoặc đơn xung quanh chúng, chẳng hạn như ($ 1,0K).

Thật không may, những gì bạn muốn làm không thể thực hiện được với một định dạng tùy chỉnh duy nhất. Lý do là vì một định dạng tùy chỉnh duy nhất chỉ có thể có bốn điều kiện, mỗi điều kiện được phân tách bằng dấu chấm phẩy. Đây là cú pháp chung của định dạng tùy chỉnh:

positive; negative; zero; text

Lưu ý rằng định dạng đầu tiên được sử dụng khi giá trị là dương, định dạng thứ hai khi là âm, định dạng thứ ba khi giá trị chính xác bằng 0 và định dạng thứ tư khi giá trị là văn bản. Mặc dù đây là cú pháp chung cho các định dạng tùy chỉnh, nhưng bạn có thể “sửa đổi” các định dạng một chút theo cách bạn đang làm. Xem xét định dạng bạn đang sử dụng:

[>1000000]$#.0,,"M";[>1000]$#.0,"K";$#,##0.0

Lưu ý rằng theo cú pháp chung, định dạng trước dấu chấm phẩy đầu tiên sẽ được sử dụng cho giá trị dương, định dạng tiếp theo cho giá trị âm và định dạng thứ ba cho giá trị 0. Tuy nhiên, đây không phải là cách Excel dịch định dạng tùy chỉnh này. Nó dịch là “nếu lớn hơn 1.000.000, hãy làm điều này; nếu lớn hơn 1.000 hãy làm điều này; người khác làm điều này”. Không có ý nghĩa tích cực hoặc tiêu cực trong định dạng; trên thực tế, bất kỳ giá trị âm nào đều được xử lý mặc định, là định dạng thứ ba.

Những gì bạn đang cố gắng làm là xác định hai điều kiện tích cực (một cho hàng triệu và một cho hàng nghìn) và hai điều kiện tiêu cực (một lần nữa, cho hàng triệu và hàng nghìn). Điều này không thể được thực hiện trong một định dạng tùy chỉnh duy nhất, bất kể bạn cố gắng kết hợp chúng như thế nào. Thay vào đó, bạn nên sử dụng hai định dạng tùy chỉnh, chẳng hạn như sau:

[>=1000000]$#.0,,"M ";[>=1000]$#.0,"K ";$#,##0.0 [Red][<=-1000000]($#.0,,"M");[Red][<=-1000]($#.0,"K");[Red]($#,##0.0)

Định dạng đầu tiên được sử dụng trong trường hợp giá trị dương; nó là một biến thể trên định dạng ban đầu được đề xuất ở đầu tiên của mẹo. Định dạng thứ hai sẽ được sử dụng với các giá trị âm. Các định dạng tùy chỉnh này sẽ cần được áp dụng theo cách thủ công, dựa trên giá trị trong ô.

Điều này có vẻ như rất nhiều công việc phải trải qua để có được định dạng bạn muốn. Có thể tạo macro áp dụng các định dạng, nhưng macro sẽ không phải là một nỗ lực tầm thường. Nó sẽ cần phải kiểm tra giá trị trong ô là gì, chọn định dạng thích hợp, xây dựng định dạng, đưa nó vào định dạng tùy chỉnh cho ô, rồi chuyển sang ô tiếp theo.

Tuy nhiên, có một điều bạn có thể làm — bạn có thể kết hợp việc sử dụng định dạng tùy chỉnh với khả năng định dạng có điều kiện của Excel. Thiết lập ba định dạng tùy chỉnh sau trong trang tính của bạn:

_($#.0_K_);[Red]($#.0_K);;@ _($#.0,"K"_);[Red]($#.0,"K");;@ _($#.0,,"M"_);[Red]($#.0,,"M");;@

Sau đó, bạn có thể sử dụng khả năng định dạng có điều kiện (tab Trang đầu của ruy-băng | Định dạng có điều kiện | Quản lý quy tắc) để xác định sáu quy tắc định dạng khác nhau. Khi bạn nhấp vào nút Quy tắc Mới để bắt đầu xác định từng quy tắc, bạn sẽ chọn Chỉ Định dạng Ô Chứa ở đầu hộp thoại Quy tắc Định dạng Mới. Đây là sáu quy tắc bạn sẽ xác định:

Cell Value <= -1000000 Cell Value >= 1000000 Cell Value between -999999 and -1000 Cell Value between 1000 and 999999 Cell Value between -999 and -1 Cell Value between 0 and 999

Khi bạn xác định từng quy tắc này, bạn sẽ nhấp vào nút Định dạng trong hộp thoại Quy tắc Định dạng Mới. Điều này trình bày hộp thoại Định dạng Ô trong đó bạn nên nhấp vào tab Số. Ở đó, bạn có thể chọn danh mục Tùy chỉnh và chọn một trong ba định dạng tùy chỉnh mà bạn đã xác định.

Đây là những cái bạn nên chọn:

  • Đối với hai quy tắc định dạng có điều kiện đầu tiên được liệt kê ở trên, hãy chọn định dạng tùy chỉnh thứ ba được liệt kê ở trên.

  • Đối với các quy tắc định dạng có điều kiện thứ ba và thứ tư được liệt kê ở trên, hãy chọn định dạng tùy chỉnh thứ hai được liệt kê ở trên.

  • Đối với các quy tắc định dạng có điều kiện thứ năm và thứ sáu được liệt kê ở trên, hãy chọn định dạng tùy chỉnh đầu tiên được liệt kê ở trên.

Đó là nó; các quy tắc định dạng có điều kiện thực hiện kiểm tra các phạm vi giá trị của bạn và sau đó áp dụng các định dạng tùy chỉnh thích hợp cho các số đó.

ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.

Mẹo này (10227) áp dụng cho Microsoft Excel 2007, 2010, 2013, 2016, 2019 và Excel trong Office 365. Bạn có thể tìm thấy phiên bản của mẹo này cho giao diện menu cũ hơn của Excel tại đây: