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 những công cụ tích hợp sẵn lại không thực sự đáp ứng chính xác yêu cầu 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 số phù hợp với nhu cầu của mình.
Thực tế, có một số tình huống mà việc sử dụng định dạng có điều kiện (Conditional Formatting) được ưu tiên hơn so với định dạng số tùy chỉnh, chẳng hạn như tô màu toàn bộ các hàng dựa trên một giá trị. Tuy nhiên, trong những tình huống mà tôi có thể sử dụng cả hai để tạo ra kết quả tương tự hoặc giống nhau, tôi thường chọn Custom Number Formatting. Trong bài viết này, tôi sẽ giải thích lý do tại sao. Với vai trò là một chuyên gia Excel, tôi tin rằng việc nắm vững kỹ thuật này sẽ giúp bạn tối ưu hóa bảng tính, tiết kiệm thời gian và biến dữ liệu khô khan thành thông tin trực quan, dễ hiểu hơn hẳn.
Định Dạng Số Tùy Chỉnh (Custom Number Formatting) Trong Excel Là Gì và Hoạt Động Như Thế Nào?
Trong Excel, mỗi ô đều có một định dạng số riêng, bạn có thể xem xét bằng cách chọn một ô và nhìn vào nhóm “Number” trên tab “Home” của thanh ribbon. Đây là nơi bạn có thể áp dụng các định dạng phổ biến như General, Number, Currency, Percentage, v.v. để phù hợp với kiểu dữ liệu mà ô đó chứa.
Giao diện Microsoft Excel trên máy tính xách tay, thể hiện các tùy chọn định dạng số trong Home tab
Một trong những tùy chọn định dạng số mạnh mẽ nhất mà 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 tùy chọn này bằng cách nhấp vào biểu tượng khởi chạy hộp thoại “Number Format” (mũi tên nhỏ ở góc dưới bên phải của nhóm “Number”), sau đó chọn “Custom” (Tùy chỉnh) trong hộp thoại “Format Cells” hiện ra.
Hộp thoại Format Cells trong Excel, với tùy chọn Custom (Tùy chỉnh) được chọn để thiết lập định dạng số tùy chỉnh
Nếu bạn là người yêu thích các phím tắt Excel để tối ưu hóa tốc độ làm việc, hãy nhấn tổ hợp phím Ctrl+1 để mở hộp thoại “Format Cells”, sau đó nhấn Tab để kích hoạt menu “Category” và gõ Cus để nhanh chóng nhảy đến mục “Custom”.
Với sự hướng dẫn chi tiết trong bài viết này, bạn sẽ có thể sử dụng định dạng số tùy chỉnh để tạo ra những bảng tính chuyên nghiệp và trực quan, trông giống như ví dụ dưới đây:
Bảng dữ liệu Excel với danh sách các đội thể thao, tổng điểm và phần trăm thay đổi, được định dạng trực quan bằng định dạng số tùy chỉnh
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ẻ hơi khó hiểu, và tôi hiểu tại sao 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. Một khi bạn hiểu được logic của nó, bạn sẽ thấy nó nhanh chóng và dễ dàng sử dụng hơn nhiều.
Cá nhân tôi, một trong những lý do chính khiến tôi ưa thích định dạng số tùy chỉnh là mọi thao tác đều diễn ra ở một nơi duy nhất – không cần phải chuyển đổi qua lại giữa các hộp thoại khác nhau để thêm nhiều quy tắc. Điều này giúp quá trình làm việc trở nên tiết kiệm thời gian và đơn giản hơn rất nhiều.
Bàn làm việc với máy tính xách tay và biểu tượng Excel, gợi ý các mẹo tiết kiệm thời gian khi làm việc với Excel
Trước khi đi sâu vào các ví dụ minh họa, hãy để tôi giải thích cách thức hoạt động của định dạng số tùy chỉnh. Mã đị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”.
Trường Type trống trong phần Custom của hộp thoại Format Cells trong Excel, nơi nhập mã định dạng số tùy chỉnh
Mã bạn cần chèn ở đây tuân theo một thứ tự nghiêm ngặt:
POSITIVE;NEGATIVE;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 các số dương được định dạng, đối số thứ hai quy định cách các số âm được định dạng, đối số thứ ba quy định cách các số 0 được định dạng và đối số thứ tư quy định cách văn bản được định dạng. Lưu ý rằng dấu chấm phẩy (;) được sử dụng để 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 số dương (
#.00). - Một số có hai chữ số thập phân trong dấu ngoặc đơn cho số âm (
(#.00)). - Một dấu gạch ngang cho các giá trị 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 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ẽ hiển thị trống.
Ví dụ, khi nhập:
#.00;;;"TXT"vào trường “Type”, Excel sẽ hiển thị một số có hai chữ số thập phân cho số dương, tạo ra một ô trống cho số âm và số 0, và hiển thị “TXT” cho tất cả các giá trị văn bản.
Bảng tính Excel minh họa các giá trị được chuyển đổi thành định dạng số tùy chỉnh, bao gồm cả các ô trống do bỏ qua đối số
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, các giá trị thực của ô vẫn không thay đổi. Trong ví dụ trên, mặc dù ô C2 hiển thị trống (vì tôi đã bỏ qua đối số NEGATIVE trong trường “Type”), khi tôi chọn ô này, 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à bạn vẫn có thể sử dụng ô này trong các công thức nếu cần thiết, đảm bảo tính toàn vẹn của dữ liệu gốc trong khi vẫn có thể trình bày thông tin một cách trực quan và dễ hiểu hơn.
Bàn làm việc với sách có logo Excel và bàn phím, tượng trưng cho việc học và áp dụng công thức và hàm trong Excel
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òn có thể thay đổi màu sắc của các giá trị và thêm các 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 về khả năng này.
Thay Đổi Màu Phông Chữ Với Định Dạng Số Tùy Chỉnh
Đị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 cơ bản (đen, trắng, đỏ, xanh lá cây, xanh dương, vàng, đỏ tươi và xanh lục lam), bạn không cần phải nhớ bất kỳ mã màu 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 ví dụ này, minh họa 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, các số âm màu đỏ và số 0 màu xanh dương.
Bảng tính Excel hiển thị dữ liệu nhân viên và kết quả doanh số so với mục tiêu, trước khi áp dụng định dạng màu sắc
Để làm điều này, hãy chọn ô C2, mở hộp thoại “Format Cells” (Ctrl+1) và chuyển đến phần “Custom”.
Trong trường “Type”, nhập:
[Green]#;[Red]#;[Blue]0;Mã này yêu cầu Excel giữ nguyên các số dương và âm như chúng vốn có (#) – nhưng định dạng màu xanh lá cây và màu đỏ tương ứng – và các số 0 hiển thị 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, do đó chỉ cần ba đối số đầu tiên.
Khi bạn nhấp vào “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 điểm điền (fill handle) đến 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.
Bảng tính Excel hiển thị kết quả doanh số nhân viên với các số được định dạng màu xanh lá, đỏ hoặc xanh dương tùy theo giá trị, sử dụng định dạng số tùy chỉnh
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ó sẵn cho phông chữ, thay vì gõ tên màu trong dấu ngoặc vuông, hãy nhập mã màu:
Ví dụ, khi nhập:
[Color10]#;[Color30]#;[Color16]0;sẽ sử dụng phông chữ màu xanh lá cây đậm cho số dương, màu đỏ thẫm cho số âm và màu xám cho số 0. Excel có khoảng 56 mã màu nội bộ (Color1 đến Color56) mà bạn có thể thử nghiệm để tìm ra tông màu phù hợp nhất.
Để hoàn tác định dạng số về trạng thái ban đầu, hãy chọn các ô chứa dữ liệu đã định dạng và trong nhóm “Number” trên tab “Home” của thanh ribbon, chọn “General” trong danh sách thả xuống.
Tại thời điểm này, hãy dành một chút để nhận thấy rằng để đạt được kết quả tương tự bằng cách sử dụng định dạng có điều kiện (Conditional Formatting), bạn sẽ cần tạo ba quy tắc riêng biệt. Ngược lại, với đị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 vài ký tự vào trường “Type” một cách nhanh chóng và hiệu quả.
Thêm Ký Hiệu Trực Quan Bằng Định Dạng Số Tùy Chỉnh
Giống 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 tương ứng với các giá trị thông qua định dạng số tùy chỉnh.
Sử dụng cùng ví dụ như 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ố 0.
Để 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 các 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.
Hộp thoại Symbol trong Microsoft Excel, cho phép chèn các ký hiệu đặc biệt vào bảng tính
Ngoài ra, bạn có thể sử dụng phím tắt Windows Windows + Period (.) để mở bàn phím biểu tượng cảm xúc (emoji keyboard) và chèn trực tiếp các ký hiệu này vào trường “Type”.
Cá nhân tôi biết rằng phím tắt Windows cho ▲ là Alt+30 và ▼ là Alt+31, và tôi 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 Cells” (Ctrl+1 > Tab > Cus), nhập dòng mã rất ngắn này:
▲;▼;"=";Mã 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 “=”.
Lưu ý rằ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 điểm điền để áp dụng định dạng số tùy chỉnh cho các ô còn lại trong cột.
Bảng tính Excel hiển thị kết quả doanh số nhân viên với các ký hiệu mũi tên lên, mũi tên xuống hoặc dấu bằng, thể hiện trực quan trạng thái so với mục tiêu
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, hãy nhập:
[Green]▲;[Red]▼;"=";vào trường “Type”. Đây là những gì bạn sẽ thấy:
Bảng tính Excel hiển thị kết quả doanh số nhân viên với các ký hiệu mũi tên lên màu xanh lá, mũi tên xuống màu đỏ hoặc dấu bằng, kết hợp màu sắc và ký hiệu
Kết Hợp Màu Sắc, Số và Ký Hiệu Với Định Dạng Số Tùy Chỉnh
Bây giờ, đã đến 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, số và màu sắc cùng một lúc.
Trong trường “Type” cho ô C2, nhập:
[Green]#▲;[Red]#▼;0" =";Trong đó:
- Đối số đầu tiên (
[Green]#▲) làm cho các giá trị dương có 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]#▼) làm cho các giá trị âm có 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 sẽ áp dụng định dạng ô thủ công), và hiển thị số “0” theo sau là một khoảng trắng và dấu “=”.
Bảng tính Excel minh họa kết quả doanh số nhân viên được định dạng hoàn chỉnh bằng định dạng số tùy chỉnh, với số, màu sắc và ký hiệu
Để căn chỉnh số sang trái của ô và ký hiệu sang 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 lầ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 ô, giúp căn chỉnh văn bản gọn gàng.
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 đ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ố liệu 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ố 0 hiển thị dưới dạng dấu gạch ngang.
Danh sách các đội thể thao trong Excel, tổng điểm qua hai mùa giải và phần trăm thay đổi, trước khi áp dụng định dạng số tùy chỉnh nâng cao
Để 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:
Bảng dữ liệu Excel với danh sách các đội thể thao, tổng điểm và phần trăm thay đổi, được định dạng trực quan bằng định dạng số tùy chỉnh
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 – và 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 hiệu quả công việc trên Excel của mình!
Kết luận
Định dạng số tùy chỉnh trong Excel là một công cụ vô cùng mạnh mẽ, mang lại sự linh hoạt và hiệu quả vượt trội trong việc trực quan hóa dữ liệu so với các phương pháp định dạng truyền thống, thậm chí cả định dạng có điều kiện trong nhiều trường hợp. Bằng cách nắm vững cú pháp POSITIVE;NEGATIVE;ZERO;TEXT và cách kết hợp màu sắc, số, ký hiệu, bạn có thể biến những bảng tính khô khan thành các báo cáo trực quan, dễ đọc và chuyên nghiệp chỉ với vài thao tác đơn giản.
Việc đầu tư thời gian để làm quen và thực hành với Custom Number Formatting không chỉ giúp bạn tiết kiệm thời gian đáng kể mà còn nâng cao kỹ năng xử lý dữ liệu, giúp bạn tạo ra những bảng tính Excel ấn tượng và cung cấp thông tin hiệu quả hơn.
Hãy bắt đầu áp dụng ngay hôm nay để trải nghiệm sự khác biệt! Đừng quên chia sẻ những cách bạn đã sử dụng định dạng số tùy chỉnh để làm cho bảng tính của mình trở nên sống động hơn trong phần bình luận bên dưới.
