Microsoft Excel cung cấp vô số công cụ để định dạng dữ liệu theo nhiều cách khác nhau. Tuy nhiên, đôi khi các công cụ tích hợp sẵn không hoàn toàn đáp ứng được yêu cầu cụ thể của tôi hoặc tốn quá nhiều thời gian để thực hiện. Trong những trường hợp này, tôi thường sử dụng định dạng số tùy chỉnh (Custom Number Formatting) để nhanh chóng tạo ra một định dạng đáp ứng chính xác nhu cầu. Mặc dù có những tình huống định dạng có điều kiện (Conditional Formatting) tỏ ra vượt trội hơn, chẳng hạn như tô màu toàn bộ hàng dựa trên một giá trị, nhưng trong các trường hợp mà cả hai phương pháp đều có thể đạt được kết quả tương tự, tôi ưu tiên Custom Number Formatting. Lý do sẽ được giải thích chi tiết trong bài viết này, tập trung vào cách nó giúp tối ưu hóa hiệu quả công việc và mang lại khả năng trình bày dữ liệu trực quan vượt trội cho người dùng Excel tại Việt Nam.
Định Dạng Số Tùy Chỉnh (Custom Number Formatting) Trong Excel Là Gì?
Trong Excel, mỗi ô đều có một định dạng số riêng, bạn có thể kiểm tra bằng cách chọn một ô và nhìn vào nhóm Number (Số) trên tab Home (Trang chủ) của thanh công cụ ribbon.
A laptop with the Microsoft Excel app.
Một trong những tùy chọn định dạng số bạn có thể áp dụng cho một ô là định dạng số tùy chỉnh. Bạn có thể truy cập nó bằng cách nhấp vào công cụ mở hộp thoại Number Format (Định dạng Số) trong nhóm Number, sau đó nhấp vào “Custom” (Tùy chỉnh) trong hộp thoại Format Cells (Định dạng Ô).
The Number Format dialog box launcher in Excel is highlighted, and the Custom number format in the Format Cells dialog box is selected.
Nếu bạn là người thường xuyên sử dụng các phím tắt trong Excel, hãy nhấn Ctrl+1 để mở hộp thoại Format Cells, sau đó nhấn Tab để kích hoạt menu Category (Thể loại) và gõ Cus để nhanh chóng chuyển đến Custom.
Khi kết thúc bài hướng dẫn này, bạn sẽ có thể sử dụng định dạng số tùy chỉnh để tạo ra các bảng tính trông giống như ví dụ sau:
A list of sports teams in Excel, their points totals over two seasons, and the percentage change formatted through custom number formatting.
Nếu bạn chưa từng sử dụng định dạng tùy chỉnh trước đây, ban đầu nó có vẻ khó hiểu. Thậm chí bạn có thể nghĩ rằng sử dụng định dạng có điều kiện sẽ là lựa chọn tốt hơn. Tuy nhiên, Excel xử lý định dạng số tùy chỉnh nhanh hơn đáng kể so với định dạng có điều kiện. Và một khi bạn đã hiểu được logic của nó, bạn có thể sẽ thấy nó nhanh hơn và dễ sử dụng hơn nhiều.
Một trong những lý do chính khiến tôi thích sử dụng định dạng số tùy chỉnh là mọi thứ đều diễn ra ở một nơi duy nhất. Không cần phải chuyển qua lại giữa các hộp thoại khác nhau để thêm các quy tắc đa dạng, giúp quá trình này tiết kiệm thời gian và đơn giản hóa công việc.
A work desk with a laptop, pen holder, and small clock on top, with the Excel icon hovering above the desk.
Trước khi đi sâu vào các ví dụ minh họa, hãy cùng tìm hiểu cách thức hoạt động của định dạng số tùy chỉnh trong Excel.
Các định dạng số tùy chỉnh được nhập vào trường Type (Kiểu) trong phần Custom của hộp thoại Format Cells.
The Type field in the Custom section of Excel
Mã bạn cần chèn vào đây tuân theo một thứ tự nghiêm ngặt:
DƯƠNG;ÂM;ZERO;TEXTNói cách khác, đối số đầu tiên trong trường định dạng số tùy chỉnh quy định cách định dạng các số dương, đối số thứ hai quy định cách định dạng các số âm, đối số thứ ba quy định cách định dạng các số 0, và đối số thứ tư quy định cách định dạng văn bản. Cũng cần lưu ý cách sử dụng dấu chấm phẩy để phân tách từng đối số.
Ví dụ, khi nhập:
#.00;(#.00);-;"TXT"vào trường Type, Excel sẽ hiển thị:
- Một số có hai chữ số thập phân cho các số dương (
#.00). - Một số có hai chữ số thập phân trong dấu ngoặc đơn cho các số âm (
(#.00)). - Một dấu gạch ngang cho các số 0 (
-). - Và chuỗi ký tự “TXT” cho tất cả các giá trị văn bản (
"TXT").
Nếu bạn bỏ qua bất kỳ đối số nào bằng cách thêm dấu chấm phẩy nhưng không nhập bất kỳ mã nào, các ô liên quan trong bảng tính của bạn sẽ xuất hiện trống.
Ví dụ, nhập:
#.00;;;"TXT"vào trường Type sẽ hiển thị một số có hai chữ số thập phân cho các số dương, tạo ra một ô trống cho các số âm và số không, và hiển thị “TXT” cho tất cả các giá trị văn bản.
An Excel spreadsheet containing various values converted into custom number formats, with some values resulting in blank cells.
Mặc dù các giá trị hiển thị trong mỗi ô phụ thuộc vào những gì bạn chỉ định trong trường Type của định dạng số tùy chỉnh, giá trị thực của các ô vẫn không thay đổi. Trong ví dụ trên, mặc dù ô C2 xuất hiện trống (vì tôi đã bỏ qua đối số cho số âm trong trường Type), khi tôi chọn ô đó, thanh công thức vẫn hiển thị giá trị thực của nó.
Điều này có nghĩa là tôi vẫn có thể sử dụng ô này trong các công thức nếu cần.
An office desk with a book featuring the Excel logo on the cover, a function icon next to it and a keyboard.
Ngoài việc sử dụng định dạng số tùy chỉnh để thay đổi giá trị hiển thị trong một ô, bạn cũng có thể thay đổi màu sắc của các giá trị và thêm ký hiệu để trực quan hóa dữ liệu của mình. Hãy cùng tìm hiểu chi tiết hơn.
Thay Đổi Màu Sắc Phông Chữ Bằng Custom Number Formatting
Tính năng định dạng số tùy chỉnh của Microsoft Excel cho phép bạn xác định màu sắc của các giá trị trong các ô đã chọn. Hơn nữa, đối với tám màu chính (đen, trắng, đỏ, xanh lá cây, xanh dương, vàng, tím và xanh lơ), bạn không cần nhớ bất kỳ mã nào – bạn chỉ cần gõ tên màu. Các màu sắc phải là đối số đầu tiên cho mỗi kiểu dữ liệu và chúng phải được đặt trong dấu ngoặc vuông.
Hãy xem xét ví dụ này, hiển thị liệu mười nhân viên có đạt được mục tiêu doanh số của họ hay không. Bạn muốn tất cả các số dương trong cột C có màu xanh lá cây, số âm màu đỏ và số không màu xanh dương.
An Excel spreadsheet showing employees
Để làm điều này, hãy chọn ô C2, mở hộp thoại “Format Cells” (Ctrl+1), và truy cập phần “Custom”.
Trong trường Type, nhập:
[Green]#;[Red]#;[Blue]0;để yêu cầu Excel giữ nguyên các số dương và âm (#) – nhưng định dạng màu xanh lá cây và đỏ tương ứng – và số không là “0” – nhưng định dạng màu xanh dương. Vì không có văn bản trong phạm vi này, bạn không cần chỉ định định dạng số cho các giá trị văn bản, nên chỉ cần ba đối số đầu tiên.
Khi bạn nhấp “OK”, bạn sẽ thấy văn bản thay đổi màu sắc tương ứng. Sau đó, bạn có thể nhấp và kéo Fill Handle (Tay nắm điền) xuống hàng dữ liệu cuối cùng để áp dụng định dạng số tùy chỉnh này cho các giá trị còn lại. Thao tác này sẽ không thay đổi giá trị trong cột C, vì chúng đã được tính toán bằng một công thức.
An Excel sheet showing employees
Nếu bạn muốn sử dụng các màu khác ngoài tám màu tiêu chuẩn được cung cấp cho phông chữ, thay vì gõ tên màu trong dấu ngoặc vuông, hãy gõ mã màu tương ứng:
| Mã Màu | Ví dụ màu sắc |
|---|---|
| Color1 | Đen |
| Color2 | Trắng |
| Color3 | Đỏ |
| Color4 | Xanh lá cây đậm |
| Color5 | Xanh dương |
| Color6 | Vàng |
| Color7 | Tím |
| Color8 | Xanh lơ |
| Color9 | Đỏ sẫm |
| Color10 | Xanh lá cây đậm hơn |
| … | … |
| Color56 | Đen nhạt |
Ví dụ, nhập:
[Color10]#;[Color30]#;[Color16]0;sẽ sử dụng phông chữ màu xanh lá cây đậm cho các số dương, màu đỏ thẫm cho các số âm và màu xám cho các số không.
Để khôi phục định dạng số về ban đầu, hãy chọn các ô chứa dữ liệu đã định dạng, và trong nhóm Number của tab Home trên ribbon, chọn “General” (Chung) trong danh sách thả xuống.
Đến đây, hãy dành một chút thời gian để nhận ra rằng việc đạt được kết quả tương tự bằng cách sử dụng định dạng có điều kiện sẽ yêu cầu bạn tạo ba quy tắc riêng biệt. Mặt khác, sử dụng định dạng tùy chỉnh, bạn có thể thực hiện những thay đổi này chỉ bằng cách thêm một vài ký tự vào trường Type.
Hiển Thị Ký Hiệu Bằng Custom Number Formatting
Tương tự như khi sử dụng định dạng có điều kiện, bạn có thể hiển thị các ký hiệu phù hợp với các giá trị tương ứng thông qua định dạng số tùy chỉnh.
Sử dụng cùng ví dụ trên, giả sử bạn muốn chuyển đổi các số trong cột Result (Cột C) thành mũi tên lên cho số dương, mũi tên xuống cho số âm và dấu bằng (=) cho số không.
Để làm điều này, trước tiên bạn cần tìm các ký hiệu hoặc biết phím tắt của chúng.
Trong tab Insert (Chèn), nhấp vào “Symbol” (Ký hiệu) và chèn các ký hiệu vào một khu vực khác của bảng tính để bạn có thể sao chép và dán chúng vào mã tùy chỉnh của mình.
The Symbol dialog box in Microsoft Excel.
Ngoài ra, bạn có thể sử dụng phím tắt Windows + Dấu chấm (.) để mở bàn phím biểu tượng cảm xúc và chèn trực tiếp các ký hiệu này vào trường Type.
Hoặc nếu bạn biết phím tắt Alt code, ví dụ như phím tắt Windows cho ▲ là Alt+30, và ▼ là Alt+31, bạn có thể sử dụng các phím tắt này khi gõ vào trường Type.
Vì vậy, hãy chọn ô C2, và trong phần Custom của hộp thoại Format Dialog (Ctrl+1 > Tab > Cus), nhập dòng mã rất ngắn gọn này:
▲;▼;"=";để yêu cầu Excel thay thế các số dương bằng “▲”, các số âm bằng “▼”, và các số 0 bằng “=”.
Bất kỳ chuỗi văn bản hoặc ký hiệu nào được gõ (bao gồm các ký hiệu toán học hoặc bất kỳ ký hiệu bàn phím nào) phải được đặt trong dấu ngoặc kép.
Bây giờ, nhấn Enter và sử dụng Fill Handle để áp dụng định dạng số tùy chỉnh cho các ô còn lại trong cột.
An Excel spreadsheet showing employees
Tiếp theo, bạn có thể muốn tô màu cho các mũi tên để trực quan hóa dữ liệu rõ ràng hơn. Đây là lúc bạn kết hợp mã màu với các ký hiệu mũi tên, nhớ rằng mã màu phải luôn được đặt ở đầu đối số.
Để làm điều này, nhập:
[Green]▲;[Red]▼;"=";vào trường Type. Đây là những gì bạn sẽ thấy:
An Excel spreadsheet showing employees
Kết Hợp Màu Sắc, Số Liệu Và Ký Hiệu Với Custom Number Formatting
Bây giờ là lúc kết hợp tất cả các bước trên để tạo ra một kết quả hiển thị cả ký hiệu và màu sắc cùng lúc.
Trong trường Type cho ô C2, nhập:
[Green]#▲;[Red]#▼;0" =";Trong đó:
- Đối số đầu tiên (
[Green]#▲) chuyển các giá trị dương thành màu xanh lá cây, hiển thị cả số và một mũi tên lên. - Đối số thứ hai (
[Red]#▼) chuyển các giá trị âm thành màu đỏ, hiển thị cả số và một mũi tên xuống. - Đối số thứ ba (
0" =") không có định dạng màu (nên nó sẽ áp dụng định dạng ô thủ công), và hiển thị số “0” theo sau bởi một khoảng trắng và dấu “=”.
An Excel sheet showing employees
Để căn chỉnh số sang bên trái của ô, và ký hiệu sang bên phải của ô, hãy nhập dấu hoa thị (*) và một khoảng trắng giữa ký hiệu và số trong mỗi đối số (ví dụ: [Green]#* ▲;[Red]#* ▼;0* " =";). Dấu hoa thị yêu cầu Excel lặp lại ký tự theo sau vô hạn (cho đến khi đạt đến giới hạn của ô), và vì ký tự theo sau là một khoảng trắng, khoảng cách giữa số và ký hiệu sẽ tăng hoặc giảm theo chiều rộng của ô.
Trong một ví dụ cuối cùng, giả sử bạn đã tính toán phần trăm thay đổi tổng số điểm cho các đội thể thao khác nhau, và bạn muốn nhanh chóng định dạng các số dương và âm sao cho chúng chuyển sang màu xanh lá cây hoặc đỏ, có mũi tên lên hoặc xuống, bao gồm cả số, và được coi là phần trăm. Bạn cũng muốn số không hiển thị dưới dạng dấu gạch ngang.
A list of sports teams in Excel, their points totals over two seasons, and the percentage change.
Để làm điều này, trong trường Type cho các ô trong cột C, nhập:
[Green]▲#%;[Red]▼#%;"-"Đây là kết quả bạn sẽ nhận được:
A list of sports teams in Excel, their points totals over two seasons, and the percentage change formatted through custom number formatting.
Khi bạn đã thực hành sử dụng định dạng số tùy chỉnh để thêm màu sắc và ký hiệu vào các ô của mình, bạn sẽ nhận ra quá trình này nhanh chóng và dễ dàng đến mức nào. Hy vọng, bạn sẽ sử dụng định dạng số tùy chỉnh thường xuyên hơn trong tương lai để tối ưu hóa công việc Excel của mình!
