Định dạng số âm, dài và đặc biệt trong Excel

01 trên 04

Định dạng số trong tổng quan về Excel

Thông tin về các định dạng số cụ thể có thể được tìm thấy trên các trang sau:

Trang 1: Số âm (bên dưới);
Trang 2: Hiển thị số thập phân dưới dạng phân số;
Trang 3: Các số đặc biệt - mã zip và định dạng số điện thoại;
Trang 4: Định dạng số dài - chẳng hạn như số thẻ tín dụng - dưới dạng văn bản.

Định dạng số trong Excel được sử dụng để thay đổi diện mạo của một số hoặc giá trị trong một ô trong trang tính.

Định dạng số được gắn vào ô chứ không phải giá trị trong ô. Nói cách khác, định dạng số không thay đổi số thực trong ô, mà chỉ là cách nó xuất hiện.

Ví dụ: việc áp dụng đơn vị tiền tệ, phần trăm hoặc định dạng số cho dữ liệu chỉ hiển thị trong ô có số. Nhấp vào ô đó sẽ hiển thị số đơn giản, chưa định dạng trong thanh công thức phía trên trang tính.

Mặc định chung

Định dạng mặc định cho các ô có chứa tất cả dữ liệu là kiểu Chung . Kiểu này không có định dạng cụ thể và, theo mặc định, hiển thị số không có ký hiệu đô la hoặc dấu phẩy và số hỗn hợp - số có chứa thành phần phân đoạn - không giới hạn ở số vị trí thập phân cụ thể.

Định dạng số có thể được áp dụng cho một ô, toàn bộ cột hoặc hàng, phạm vi ô được chọn hoặc toàn bộ trang tính .

Định dạng số âm

Theo mặc định, số âm được xác định bằng ký hiệu âm hoặc dấu gạch ngang (-) ở bên trái của số. Excel có một số tùy chọn định dạng khác để hiển thị số âm nằm trong hộp thoại Định dạng ô . Bao gồm các:

• văn bản màu đỏ
• vòng chân đế
• dấu ngoặc tròn và văn bản màu đỏ

Hiển thị số âm bằng màu đỏ có thể giúp tìm chúng dễ dàng hơn - đặc biệt nếu chúng là kết quả của các công thức có thể khó theo dõi trong một trang tính lớn.

Chân đế thường được sử dụng để làm cho các số âm dễ dàng hơn để xác định cho dữ liệu được in bằng màu đen và trắng.

Thay đổi định dạng số âm trong hộp thoại định dạng ô

1. Đánh dấu dữ liệu được định dạng
2. Nhấp vào tab Trang chủ của ruy-băng
3. Nhấp vào trình khởi chạy hộp thoại - mũi tên chỉ xuống nhỏ ở góc dưới cùng bên phải của nhóm biểu tượng Số trên ruy-băng để mở hộp thoại Định dạng ô
4. Nhấp vào Number bên dưới phần Category của hộp thoại
5. Chọn tùy chọn hiển thị số âm - đỏ, ngoặc vuông hoặc màu đỏ và ngoặc vuông
6. Nhấn OK để đóng hộp thoại và quay lại trang tính
7. Các giá trị âm trong dữ liệu được chọn sẽ được định dạng với các tùy chọn đã chọn

02 trên 04

Định dạng số dưới dạng phân số trong Excel

Hiển thị số thập phân dưới dạng phân số

Sử dụng định dạng Phân số để hiển thị số dưới dạng phân số thực, thay vì số thập phân. Như được liệt kê trong cột Mô tả trong hình trên, các tùy chọn có sẵn cho phân số bao gồm:

• hiển thị số thập phân dưới dạng một, hai hoặc ba chữ số trong phần mẫu số của số;
• hiển thị số thập phân như phân số thường được sử dụng - chẳng hạn như một nửa và một phần tư.

Định dạng đầu tiên, dữ liệu thứ hai

Thông thường, tốt nhất là áp dụng định dạng phân số cho các ô trước khi nhập dữ liệu để tránh các kết quả không mong muốn.

Ví dụ: nếu các phân số có số tử từ một đến 12 - chẳng hạn như 1/2 hoặc 12/64 - được nhập vào các ô có định dạng Chung , các số sẽ được thay đổi thành các ngày như:

• 1/2 sẽ được trả lại vào ngày 2 tháng 1
• 12/64 sẽ được trả lại là Jan-64 (tháng 1 năm 1964)

Đồng thời, các phân số với tử số lớn hơn 12 sẽ được chuyển thành văn bản và có thể gây ra các vấn đề nếu được sử dụng trong tính toán.

Định dạng số dưới dạng phân số trong hộp thoại định dạng ô

1. Đánh dấu các ô được định dạng dưới dạng phân số
2. Nhấp vào tab Trang chủ của ruy-băng
3. Nhấp vào trình khởi chạy hộp thoại - mũi tên chỉ xuống nhỏ ở góc dưới cùng bên phải của nhóm biểu tượng Số trên ruy-băng để mở hộp thoại Định dạng ô
4. Nhấp vào Phân số trong phần Danh mục của hộp thoại để hiển thị danh sách các định dạng phân số có sẵn ở phía bên tay phải của hộp thoại
5. Chọn định dạng để hiển thị số thập phân dưới dạng phân số từ danh sách
6. Nhấn OK để đóng hộp thoại và quay lại trang tính
7. Số thập phân được nhập vào phạm vi được định dạng sẽ được hiển thị dưới dạng phân số

03 trên 04

Định dạng số đặc biệt trong Excel

Giới hạn định dạng chung và số

Nếu bạn sử dụng Excel để lưu trữ số nhận dạng - chẳng hạn như mã zip hoặc số điện thoại - bạn có thể thấy số đang được thay đổi hoặc hiển thị với kết quả không mong muốn.

Theo mặc định, tất cả các ô trong trang tính Excel sử dụng định dạng Chung và các đặc điểm của định dạng này bao gồm:

• số 0 đứng đầu được xóa,
• các số có hơn 11 chữ số được chuyển thành ký hiệu khoa học (hoặc theo hàm mũ).

Tương tự, định dạng Số được giới hạn hiển thị số có 15 chữ số. Bất kỳ chữ số nào vượt quá giới hạn này được làm tròn xuống 0

Để tránh các vấn đề với các số đặc biệt, có thể sử dụng hai tùy chọn tùy thuộc vào loại số đang được lưu trữ trong một trang tính:

• thể loại định dạng đặc biệt trong hộp thoại Định dạng ô (xem bên dưới);
• định dạng số dưới dạng văn bản (trang tiếp theo).

Để đảm bảo rằng các số đặc biệt được hiển thị chính xác khi được nhập, hãy định dạng ô hoặc các ô bằng một trong hai định dạng dưới đây trước khi nhập số.

Danh mục định dạng đặc biệt

Danh mục Đặc biệt trong hộp thoại Định dạng ô tự động áp dụng định dạng đặc biệt cho các số như sau:

• số điện thoại - bao quanh ba chữ số đầu tiên của một số gồm 10 chữ số trong dấu ngoặc đơn và phân tách bảy chữ số còn lại thành hai nhóm cách nhau bằng dấu gạch nối. Ví dụ: (800) 555-1212
• Số an sinh xã hội - phân tách chín chữ số thành các nhóm ba, hai và bốn chữ số cách nhau bằng dấu gạch nối. Ví dụ: 555-00-9999

• mã zip - giữ lại số 0 ở đầu số - được xóa bằng định dạng số thông thường. Ví dụ: 00987

• mã zip + 4 : phân tách chín chữ số thành các nhóm gồm năm chữ số và bốn chữ số được phân tách bằng dấu gạch nối. Cũng giữ lại bất kỳ số 0 ở đầu. Ví dụ: 00987-5555

Locale Sensitive

Danh sách thả xuống dưới Locale cung cấp các tùy chọn để định dạng các số đặc biệt phù hợp với các quốc gia cụ thể. Ví dụ: nếu Ngôn ngữ được thay đổi thành tiếng Anh (Canada), các tùy chọn có sẵn là Số điện thoại và Số bảo hiểm xã hội - số thường được sử dụng cho các quốc gia đó.

Sử dụng định dạng đặc biệt cho số trong hộp thoại định dạng ô

1. Đánh dấu các ô được định dạng dưới dạng phân số
2. Nhấp vào tab Trang chủ của ruy-băng
3. Nhấp vào trình khởi chạy hộp thoại - mũi tên chỉ xuống nhỏ ở góc dưới cùng bên phải của nhóm biểu tượng Số trên ruy-băng để mở hộp thoại Định dạng ô
4. Nhấp vào Đặc biệt trong phần Danh mục của hộp thoại để hiển thị danh sách các định dạng đặc biệt có sẵn ở phía bên tay phải của hộp thoại
5. Nếu cần, hãy nhấp vào tùy chọn Ngôn ngữ để thay đổi vị trí
6. Chọn một trong các tùy chọn định dạng để hiển thị các số đặc biệt từ danh sách
7. Nhấn OK để đóng hộp thoại và quay lại trang tính
8. Các số thích hợp được nhập vào phạm vi được định dạng sẽ được hiển thị như với định dạng đặc biệt được chọn

04/04

Định dạng số dưới dạng văn bản trong Excel

Giới hạn định dạng chung và số

Để đảm bảo số dài - chẳng hạn như số thẻ tín dụng 16 chữ số và số thẻ ngân hàng - được hiển thị chính xác khi được nhập, định dạng ô hoặc ô bằng định dạng Văn bản - tốt nhất là trước khi nhập dữ liệu.

Theo mặc định, tất cả các ô trong trang tính Excel sử dụng định dạng Chung và một trong các đặc điểm của định dạng này là các số có hơn 11 chữ số được chuyển thành ký hiệu khoa học (hoặc theo hàm mũ) - như được hiển thị trong ô A2 trong hình trên.

Tương tự, định dạng Số được giới hạn hiển thị số có 15 chữ số. Bất kỳ chữ số nào vượt quá giới hạn này được làm tròn xuống số không.

Trong ô A3 ở trên, số 1234567891234567 được đổi thành 123456789123450 khi ô được đặt cho định dạng số.

Sử dụng dữ liệu văn bản trong các công thức và hàm

Ngược lại, khi định dạng văn bản được sử dụng - ô A4 ở trên - số tương tự hiển thị chính xác và vì giới hạn ký tự trên mỗi ô cho định dạng văn bản là 1.024, có thể chỉ là số không hợp lý như Pi (Π) và Phi (Φ) không thể hiển thị toàn bộ.

Ngoài việc giữ số giống với cách nó được nhập, các số được định dạng dưới dạng văn bản vẫn có thể được sử dụng trong các công thức sử dụng các phép toán cơ bản - chẳng hạn như cộng và trừ như được hiển thị trong ô A8 ở trên.

Tuy nhiên, họ không thể sử dụng nó trong các phép tính với một số hàm của Excel - chẳng hạn như SUM và AVERAGE , vì các ô chứa dữ liệu được coi là rỗng và trả về:

• kết quả bằng không cho SUM - ô A9
• giá trị lỗi # DIV / 0! cho AVERAGE - ô A10

Các bước để định dạng ô cho văn bản

Như với các định dạng khác, điều quan trọng là định dạng ô cho dữ liệu văn bản trước khi nhập số - nếu không, nó sẽ bị ảnh hưởng bởi định dạng ô hiện tại.

1. Nhấp vào ô hoặc chọn một dải ô mà bạn muốn chuyển đổi thành định dạng văn bản
2. Nhấp vào tab Trang chủ của ruy-băng
3. Nhấp vào mũi tên xuống bên cạnh hộp Định dạng số - hiển thị chung theo mặc định - để mở trình đơn thả xuống của các tùy chọn định dạng
4. Cuộn xuống cuối trình đơn và nhấp vào tùy chọn Văn bản - không có tùy chọn bổ sung cho định dạng văn bản

Văn bản bên trái, số sang phải

Một đầu mối trực quan để giúp bạn xác định định dạng của ô là xem xét căn chỉnh của dữ liệu.

Theo mặc định trong Excel, dữ liệu văn bản được căn chỉnh bên trái trong dữ liệu ô và số ở bên phải. Nếu căn chỉnh mặc định cho một phạm vi được định dạng là văn bản chưa được thay đổi, các số được nhập vào phạm vi đó sẽ được hiển thị ở phía bên trái của các ô như được hiển thị trong ô C5 trong hình trên.

Ngoài ra, như được hiển thị trong các ô từ A4 đến A7, các số được định dạng dưới dạng văn bản cũng sẽ hiển thị hình tam giác nhỏ màu xanh ở góc trên bên trái của ô cho biết dữ liệu có thể được định dạng không chính xác.