Định dạng số kiểm soát cách hiển thị số trong Excel. Lợi ích chính của định dạng số là chúng thay đổi hình thức của một số mà không thay đổi bất kỳ dữ liệu nào. Chúng là một cách tuyệt vời để tiết kiệm thời gian trong Excel vì chúng tự động thực hiện một lượng lớn định dạng. Như một phần thưởng, chúng làm cho bảng tính trông nhất quán và chuyên nghiệp hơn
Video. định dạng số là gì
Bạn có thể làm gì với các định dạng số tùy chỉnh?
Định dạng số tùy chỉnh có thể kiểm soát việc hiển thị số, ngày tháng, thời gian, phân số, tỷ lệ phần trăm và các giá trị số khác. Sử dụng các định dạng tùy chỉnh, bạn có thể thực hiện những việc như định dạng ngày để chỉ hiển thị tên tháng, định dạng số lớn theo hàng triệu hoặc hàng nghìn và hiển thị số âm có màu đỏ
Bạn có thể sử dụng các định dạng số tùy chỉnh ở đâu?
Nhiều vùng trong Excel hỗ trợ định dạng số. Bạn có thể sử dụng chúng trong bảng, biểu đồ, bảng tổng hợp, công thức và trực tiếp trên trang tính
- Bảng tính - hộp thoại định dạng ô
- Bảng Pivot - thông qua cài đặt trường giá trị
- Biểu đồ - nhãn dữ liệu và tùy chọn trục
- Công thức - thông qua chức năng TEXT
Định dạng số là gì?
Định dạng số là một mã đặc biệt để kiểm soát cách hiển thị giá trị trong Excel. Ví dụ: bảng bên dưới hiển thị 7 định dạng số khác nhau được áp dụng cho cùng một ngày, ngày 1 tháng 1 năm 2019
InputCodeResult1-Jan-2019yyyy20191-Jan-2019yy191-Jan-2019mmmJan1-Jan-2019mmmmJan1-Jan-2019d11-Jan-2019dddTue1-Jan-2019ddddTuesdayĐiều quan trọng cần hiểu là các định dạng số thay đổi cách hiển thị các giá trị số, nhưng chúng không thay đổi các giá trị thực
Bạn có thể tìm định dạng số ở đâu?
Trên tab trang chủ của dải băng, bạn sẽ tìm thấy menu các định dạng số dựng sẵn. Bên dưới trình đơn này ở bên phải, có một nút nhỏ để truy cập tất cả các định dạng số, bao gồm các định dạng tùy chỉnh
Nút này mở hộp thoại Định dạng ô. Bạn sẽ tìm thấy danh sách đầy đủ các định dạng số, được sắp xếp theo danh mục, trên tab Số
Ghi chú. bạn có thể mở hộp thoại Format Cells bằng phím tắt Control + 1
Chung là mặc định
Theo mặc định, các ô bắt đầu với định dạng Chung được áp dụng. Việc hiển thị các số bằng định dạng số chung hơi "linh động". Excel sẽ hiển thị nhiều vị trí thập phân như không gian cho phép, đồng thời sẽ làm tròn số thập phân và sử dụng định dạng số khoa học khi không gian bị giới hạn. Màn hình bên dưới hiển thị các giá trị giống nhau trong cột B và D, nhưng D hẹp hơn và Excel thực hiện điều chỉnh nhanh chóng
Cách thay đổi định dạng số
Bạn có thể chọn các định dạng số tiêu chuẩn [Chung, Số, Tiền tệ, Kế toán, Ngày ngắn, Ngày dài, Thời gian, Tỷ lệ phần trăm, Phân số, Khoa học, Văn bản] trên tab trang chủ của dải băng bằng menu Định dạng số
Ghi chú. Khi bạn nhập dữ liệu, đôi khi Excel sẽ tự động thay đổi định dạng số. Ví dụ: nếu bạn nhập ngày hợp lệ, Excel sẽ chuyển sang định dạng "Ngày". Nếu bạn nhập một tỷ lệ phần trăm như 5%, Excel sẽ thay đổi thành Tỷ lệ phần trăm, v.v.
Phím tắt cho các định dạng số
Excel cung cấp một số phím tắt cho một số định dạng phổ biến
Xem thêm. 222 Phím tắt Excel cho Windows và Mac
Nơi nhập các định dạng tùy chỉnh
Ở dưới cùng của các định dạng được xác định trước, bạn sẽ thấy một danh mục có tên là tùy chỉnh. Danh mục Tùy chỉnh hiển thị danh sách mã bạn có thể sử dụng cho các định dạng số tùy chỉnh, cùng với khu vực nhập để nhập mã theo cách thủ công theo nhiều cách kết hợp khác nhau
Khi chọn một mã từ danh sách, bạn sẽ thấy mã đó xuất hiện trong hộp Nhập loại. Tại đây, bạn có thể sửa đổi mã tùy chỉnh hiện có hoặc nhập mã của riêng bạn từ đầu. Excel sẽ hiển thị một bản xem trước nhỏ của mã được áp dụng cho giá trị được chọn đầu tiên phía trên vùng nhập liệu
Ghi chú. Các định dạng số tùy chỉnh nằm trong sổ làm việc, không phải trong Excel nói chung. Nếu bạn sao chép một giá trị được định dạng bằng định dạng tùy chỉnh từ sổ làm việc này sang sổ làm việc khác, định dạng số tùy chỉnh sẽ được chuyển vào sổ làm việc cùng với giá trị
Cách tạo định dạng số tùy chỉnh
Để tạo định dạng số tùy chỉnh, hãy làm theo quy trình 4 bước đơn giản này
- Chọn các ô] có giá trị bạn muốn định dạng
- Điều khiển + 1 > Số > Tùy chỉnh
- Nhập mã và xem khu vực xem trước để xem kết quả
- Nhấn OK để lưu và áp dụng
Mẹo. nếu bạn muốn định dạng tùy chỉnh của mình dựa trên định dạng hiện có, trước tiên hãy áp dụng định dạng cơ sở, sau đó nhấp vào danh mục "Tùy chỉnh" và chỉnh sửa mã theo ý muốn
Cách chỉnh sửa định dạng số tùy chỉnh
Bạn thực sự không thể chỉnh sửa định dạng số tùy chỉnh mỗi lần. Khi bạn thay đổi định dạng số tùy chỉnh hiện tại, một định dạng mới sẽ được tạo và sẽ xuất hiện trong danh sách trong danh mục Tùy chỉnh. Bạn có thể sử dụng nút Xóa để xóa các định dạng tùy chỉnh mà bạn không cần nữa
Cảnh báo. không có "hoàn tác" sau khi xóa định dạng số tùy chỉnh
Cấu trúc và tài liệu tham khảo
Định dạng số tùy chỉnh Excel có cấu trúc cụ thể. Mỗi định dạng số có thể có tối đa bốn phần, được phân tách bằng dấu chấm phẩy như sau
Cấu trúc này có thể làm cho các định dạng số tùy chỉnh trông cực kỳ phức tạp. Để đọc định dạng số tùy chỉnh, hãy tìm hiểu cách phát hiện dấu chấm phẩy và phân tích cú pháp mã thành các phần này
- Giá trị dương
- giá trị âm
- Giá trị bằng không
- Giá trị văn bản
Không phải tất cả các phần bắt buộc
Mặc dù định dạng số có thể bao gồm tối đa bốn phần nhưng chỉ một phần là bắt buộc. Theo mặc định, phần đầu tiên áp dụng cho số dương, phần thứ hai áp dụng cho số âm, phần thứ ba áp dụng cho giá trị 0 và phần thứ tư áp dụng cho văn bản
- Khi chỉ có một định dạng được cung cấp, Excel sẽ sử dụng định dạng đó cho tất cả các giá trị
- Nếu bạn cung cấp một định dạng số chỉ có hai phần, thì phần đầu tiên được sử dụng cho các số dương và số 0, còn phần thứ hai được sử dụng cho các số âm
- Để bỏ qua một phần, hãy bao gồm dấu chấm phẩy ở vị trí thích hợp, nhưng không chỉ định mã định dạng
Các ký tự hiển thị tự nhiên
Một số ký tự xuất hiện bình thường ở định dạng số, trong khi những ký tự khác yêu cầu xử lý đặc biệt. Bạn có thể sử dụng các ký tự sau mà không cần xử lý đặc biệt
CharacterComment$Dollar+-Cộng, trừ[] Dấu ngoặc đơn{}Dấu ngoặc nhọnNhỏ hơn, lớn hơn=Bằng. Dấu hai chấm^Caret'Apostrophe/Dấu gạch chéo về phía trước. Dấu chấm than&Dấu và~Dấu ngã Ký tự khoảng trắngNhân vật trốn thoát
Một số ký tự sẽ không hoạt động chính xác ở định dạng số tùy chỉnh mà không thoát. Ví dụ: không thể sử dụng trực tiếp các ký tự dấu hoa thị [*], dấu thăng [#] và phần trăm [%] ở định dạng số tùy chỉnh – chúng sẽ không xuất hiện trong kết quả. Ký tự thoát trong định dạng số tùy chỉnh là dấu gạch chéo ngược [\]. Bằng cách đặt dấu gạch chéo ngược trước ký tự, bạn có thể sử dụng chúng ở định dạng số tùy chỉnh
ValueCodeResult100\#0#100100\*0*100100\%0%100Trình giữ chỗ
Một số ký tự có ý nghĩa đặc biệt trong mã định dạng số tùy chỉnh. Các ký tự sau đây là các khối xây dựng chính
CharacterPurpose0Hiển thị số 0 không đáng kể#Hiển thị chữ số có nghĩa?Hiển thị số thập phân được căn chỉnh. Dấu thập phân, Dấu phân cách hàng nghìn*Lặp lại ký tự sau_Thêm dấu cách@Placeholder cho văn bảnSố không [0] được sử dụng để buộc hiển thị các số 0 không đáng kể khi một số có ít chữ số hơn số không ở định dạng. Ví dụ: định dạng tùy chỉnh 0. 00 sẽ hiển thị số 0 là 0. 00, 1. 1 như 1. 10 và. 5 như 0. 50
Dấu thăng [#] là phần giữ chỗ cho các chữ số tùy chọn. Khi một số có ít chữ số hơn # ký hiệu ở định dạng, sẽ không có gì được hiển thị. Ví dụ: định dạng tùy chỉnh #. ## sẽ hiển thị 1. 15 như 1. 15 và 1. 1 như 1. 1
Dấu chấm hỏi [?] dùng để căn giữa các chữ số. Khi một dấu chấm hỏi chiếm một vị trí không cần thiết trong một số, một khoảng trắng sẽ được thêm vào để duy trì sự liên kết trực quan.
Giai đoạn = Stage [. ] là phần giữ chỗ cho dấu thập phân trong một số. Khi một khoảng thời gian được sử dụng ở định dạng số tùy chỉnh, nó sẽ luôn được hiển thị, bất kể số đó có chứa giá trị thập phân hay không
Dấu phẩy [,] là phần giữ chỗ cho dấu phân cách hàng nghìn trong số được hiển thị. Nó có thể được sử dụng để xác định hành vi của các chữ số liên quan đến các chữ số hàng nghìn hoặc hàng triệu
Dấu sao [*] dùng để lặp lại các ký tự. Ký tự ngay sau dấu hoa thị sẽ được lặp lại để lấp đầy khoảng trống còn lại trong một ô
Dấu gạch dưới [_] được sử dụng để thêm khoảng trắng ở định dạng số. Ký tự ngay sau ký tự gạch dưới kiểm soát lượng không gian cần thêm. Một cách sử dụng phổ biến của ký tự gạch dưới là thêm khoảng trắng để căn chỉnh các giá trị dương và âm khi định dạng số chỉ thêm dấu ngoặc đơn vào các số âm. Ví dụ: định dạng số "0_];[0]" đang thêm một chút khoảng trắng vào bên phải của các số dương để chúng luôn thẳng hàng với các số âm, được đặt trong dấu ngoặc đơn
Tại [@] - trình giữ chỗ cho văn bản. Ví dụ: định dạng số sau sẽ hiển thị các giá trị văn bản có màu xanh lam
0;0;0;[Blue]@
Xem bên dưới để biết thêm thông tin về cách sử dụng màu sắc
tự động làm tròn
Điều quan trọng là phải hiểu rằng Excel sẽ thực hiện "làm tròn trực quan" với tất cả các định dạng số tùy chỉnh. Khi một số có nhiều chữ số hơn số chỗ dành sẵn ở bên phải dấu thập phân, số đó được làm tròn thành số chỗ dành sẵn. Khi một số có nhiều chữ số hơn chỗ dành sẵn ở phía bên trái của dấu thập phân, các chữ số thừa sẽ được hiển thị. Đây chỉ là một hiệu ứng hình ảnh;
Định dạng số cho TEXT
Để hiển thị cả văn bản cùng với số, hãy đặt văn bản trong dấu ngoặc kép [""]. Bạn có thể sử dụng phương pháp này để nối thêm hoặc thêm vào trước chuỗi văn bản ở định dạng số tùy chỉnh, như minh họa trong bảng bên dưới
Giá trị MãKết quả10Chung" đơn vị"10 đơn vị100. 0" đơn vị"10. 0 đơn vị5. 50. 0"chân"5. 5 feet300000" feet"30000 feet95. 2"Điểm. "0. 0Điểm. 95. 21-Jun"Ngày. "mmmm dDate. 1 tháng 6Định dạng số cho NGÀY
Ngày trong Excel chỉ là số, vì vậy bạn có thể sử dụng định dạng số tùy chỉnh để thay đổi cách chúng hiển thị. Excel có nhiều mã cụ thể mà bạn có thể sử dụng để hiển thị các thành phần của ngày theo nhiều cách khác nhau. Màn hình dưới đây cho thấy cách Excel hiển thị ngày trong D5, ngày 3 tháng 9 năm 2018, với nhiều định dạng số tùy chỉnh
Định dạng số cho TIME
Thời gian trong Excel là các phần phân số của một ngày. Ví dụ, 12. 00 giờ tối là 0. 5 và 6. 00 giờ tối là 0. 75. Bạn có thể sử dụng các mã sau ở định dạng thời gian tùy chỉnh để hiển thị các thành phần của thời gian theo những cách khác nhau. Màn hình dưới đây cho thấy cách Excel hiển thị thời gian trong D5, 9. 35. 07 giờ sáng, với nhiều định dạng số tùy chỉnh
Ghi chú. m và mm không thể được sử dụng một mình ở định dạng số tùy chỉnh vì chúng xung đột với mã số tháng trong mã định dạng ngày
Định dạng số cho THỜI GIAN ĐÃ TRÒN
Thời gian đã trôi qua là một trường hợp đặc biệt và cần xử lý đặc biệt. Bằng cách sử dụng dấu ngoặc vuông, Excel cung cấp một cách đặc biệt để hiển thị giờ, phút và giây đã trôi qua. Màn hình sau đây cho thấy cách Excel hiển thị thời gian đã trôi qua dựa trên giá trị trong D5, đại diện cho 1. 25 ngày
Định dạng số cho COLORS
Excel cung cấp hỗ trợ cơ bản cho màu ở định dạng số tùy chỉnh. 8 màu sau có thể được chỉ định theo tên ở định dạng số. [đen] [trắng] [đỏ][lục] [lam] [vàng] [đỏ tươi] [lục lam]. Tên màu phải xuất hiện trong ngoặc
Màu sắc theo chỉ số
Ngoài tên màu, bạn cũng có thể chỉ định màu theo số chỉ mục [Color1,Color2,Color3, v.v. ] Các ví dụ bên dưới đang sử dụng định dạng số tùy chỉnh. [ColorX]0"▲▼", trong đó X là một số trong khoảng 1-56
[Color1]0"▲▼" // black
[Color2]0"▲▼" // white
[Color3]0"▲▼" // red
[Color4]0"▲▼" // green
etc.
Các biểu tượng hình tam giác đã được thêm vào chỉ để làm cho màu sắc dễ nhìn hơn. Hình ảnh đầu tiên hiển thị tất cả 56 màu trên nền trắng tiêu chuẩn. Hình ảnh thứ hai hiển thị cùng màu trên nền xám. Lưu ý 8 màu đầu tiên được hiển thị tương ứng với danh sách màu được đặt tên ở trên
Áp dụng định dạng số trong công thức
Mặc dù hầu hết các định dạng số được áp dụng trực tiếp cho các ô trong trang tính, nhưng bạn cũng có thể áp dụng các định dạng số bên trong công thức bằng hàm TEXT. Ví dụ: với ngày hợp lệ trong A1, công thức sau sẽ chỉ hiển thị tên tháng
=TEXT[A1,"mmmm"]
Kết quả của hàm TEXT luôn là văn bản, vì vậy bạn có thể tự do nối kết quả của TEXT với các chuỗi khác
="The contract expires in "&TEXT[A1,"mmmm"]
Màn hình bên dưới hiển thị các định dạng số trong cột C đang được áp dụng cho các số trong cột B bằng cách sử dụng hàm TEXT
Đo
Bạn có thể sử dụng định dạng số tùy chỉnh để hiển thị các số có dấu inch ["] hoặc dấu feet [']. Trong màn hình bên dưới, các định dạng số được sử dụng cho inch và feet là
0.00 \' // feet
0.00 \" // inches
Những kết quả này rất đơn giản và không thể kết hợp ở một định dạng số duy nhất. Tuy nhiên, bạn có thể sử dụng công thức để hiển thị foot cùng với inch
điều kiện
Định dạng số tùy chỉnh cũng có tối đa hai điều kiện, được viết trong dấu ngoặc vuông như [>100] hoặc [