Chiêu 33: Tạo các định dạng số cho riêng bạn
Excel đã có những định dạng số của riêng nó nhưng thỉnh thoảng bạn cần sử dụng một
định dạng số không có sẵn trong Excel. Sử dụng chiêu này, bạn có thể tạo ra các định
dạng số mà bạn có thể điều chỉnh cho nhu cầu riêng của bạn.
Trước khi bạn thử những chiêu này, sẽ có ích cho bạn nếu bạn hiểu được làm thế nào
Excel nhận biết được các định dạng của ô. Excel nhận biết một định dạng của ô khi có 4
phần sau (từ trái sang phải): số dương, số âm, giá trị 0, và giá trị chuỗi. Mỗi phần được
ngăn cách bởi một dấu chấm phẩy (;).
Khi bạn tạo ra một định dạng số cho riêng bạn, bạn không cần phải định dạng tất cả 4
phần này. Nói cách khác, nếu định dạng của bạn chỉ có hai phần thì phần đầu tiên được
sử dụng cho cả các số dương và các giá trị 0, trong khi phần thứ 2 sẽ được sử dụng cho
số âm.
Nếu định dạng của bạn chỉ bao gồm một phần, tất cả các loại số sẽ dùng một định dạng.
Chuỗi bị ảnh hưởng bởi các định dạng riêng chỉ khi bạn sử dụng tất cả 4 phần, phần
cuối sẽ sử dụng cho chuỗi .
Đừng cho rằng những định dạng riêng chỉ áp dụng chỉ cho các dữ liệu kiểu số. Các định
dạng số cũng áp dụng cho các dữ liệu kiểu chữ.
Định dạng riêng cho số được hiển thị ở hình 2-18 là định dạng tiền tệ chuẩn của Excel,
nó chỉ ra số tiền âm là màu đỏ.
Nếu bạn gõ một số dương tương ứng với một giá trị tiền tệ, Excel sẽ định dạng nó ngay
lập tức bao gồm dấu phẩy (,) ngăn cách hàng ngàn, theo sau hai số lẻ thập phân.
Excel sẽ định dạng tương tự cho giá trị âm, ngoại trừ số âm được hiển thị là màu đỏ. Bất
kỳ giá trị 0 nào cũng sẽ không có ký hiệu tiền tệ và sẽ được hiển thị hai số lẻ thập phân
(0.00). Nếu bạn gõ một chuỗi vào một ô, Excel sẽ hiển thị dòng chữ “Chuỗi hiển thị,”
bất kể giá trị thật sự của chuỗi bạn gõ vào là gì.
Điều quan trọng là phải chú ý, việc định dạng một giá trị của ô không ảnh hưởng đến
giá trị đích thực của ô.
Ví dụ, gõ bất kỳ số nào vào ô A1. Nhấn chuột phải và chọn Format Cells ➝ ➝ Number
Custom, và sử dụng bất kỳ định dạng nào ngay tại con trỏ chuột đầu tiên (trong hộp
"Type), gõ "Hello" (với dấu ngoặc kép ("")). Sau đó nhấn OK.
Mặc dù ô hiển thị từ "Hello" nhưng bạn có thể thấy giá trị thật sự của ô bằng cách chọn
ô và xem trên thanh công thức (Formula bar), hoặc nhấn F2. Nếu bạn tham chiếu ô này
trong một công thức - ví dụ, =A1+20 - kết quả của ô sẽ dẫn đến định dạng lúc nãy,
nghĩa là sẽ trả về kết quả là "Hello" (bạn hãy làm thử, bạn sẽ thấy kết quả ngay tức
khắc!)
Nếu bạn đã tham chiếu ô A1 cùng với những ô khác mà có bất kỳ định dạng chuẩn
Excel nào - ví dụ, =Sum(A1:A10)- kết quả trả về sẽ vẫn theo định dạng riêng của ô A1.
Excel đưa ra một giả thiết đã được thiết lập trước rằng bạn muốn kết quả của ô đã được
định dạng giống như cách tham chiếu đến một hay nhiều ô. Nếu các ô tham chiếu chứa
nhiều hơn một loại định dạng thì bất cứ định dạng riêng nào cũng sẽ được ưu tiên trước.
Điều này có nghĩa là, bạn phải luôn luôn nhớ rằng Excel sử dụng một giá trị thật sự của
ô để tính toán, và sẽ không lấy giá trị thể hiện của nó.
Điều này có thể tạo ra ngạc nhiên khi Excel tính toán dựa trên các ô đã được định dạng
không có số lẻ thập phân hoặc có một vài số lẻ thập phân, thí dụ:
Để thấy được điều này, bạn gõ 1.4 vào ô A1 và 1.4 vào ô A2, định dạng cho cả hai ô là
không có số lẻ thập phân theo sau bằng cách bạn chọn vùng A1:A2, click chuột phải,
chọn Format Cells ➝ Number, trong khung "decimal places" chọn 0. Sau đó gõ công
thức =A1+A2 vào một ô khác. Dĩ nhiên kết quả trả về sẽ là 3 vì Excel đã làm tròn.
Excel có một lựa chọn được gọi là “Precision as Displayed”, bạn có thể tìm thấy bằng
cách chọn Office button ➝ ➝ Excel Options Advanced (Với phiên bản trước Excel
2007, chọn Tools ➝ ➝ Options Calculation), nhưng bạn nên biết rằng định dạng này sẽ
thay đổi vĩnh viễn các giá trị trong các ô từ đầy đủ các số lẻ thập phân (gồm 15 ký số)
đến bất kỳ định dạng nào, bao gồm các số lẻ thập phân, được thể hiện. Nói cách khác,
khi bạn đánh dấu chọn “Precision as Displayed” và nhấn OK thì nó sẽ không trả lại kết
quả ban đầu. (Bạn có thể thử nhưng các thông tin về các số lẽ thập phân mở rộng sẽ bị
mất đi.)
Chiêu 33: Tạo các định dạng số cho riêng bạn (Tiếp theo)
Định dạng mặc định cho tất cả các ô trong Excel là General. Nếu bạn gõ một con số vào
một ô, Excel thường sẽ đoán định dạng số nào là phù hợp nhất. Ví dụ, nếu bạn gõ 10%
vào một ô, Excel sẽ định dạng ô đó là kiểu Percentage. Trong hầu hết các trường hợp thì
Excel đoán khá chính xác nhưng thỉnh thoảng bạn cần thay đổi lại.
Khi sử dụng định dạng cho các ô, hãy tránh việc bị cám dỗ để định dạng canh trái, canh
phải hay canh giữa. Theo mặc định các số sẽ được canh phải và chuỗi sẽ được canh trái.
Nếu bạn bỏ điều này, bạn có thể nhận biết thoáng qua dù cho ô đó là chuỗi hay số vì
trong trường hợp ví dụ trước, ô A1 bạn thấy là chuỗi ("Hello") nhưng thực sự nó là một
số.
Mỗi phần của một định dạng được đưa ra sử dụng những mã định dạng riêng của nó.
Các mã này ảnh hưởng đến Excel trong việc thể hiện dữ liệu theo cách mà bạn muốn.
Vì vậy, thí dụ, giả sử bạn muốn số âm xuât hiện trong ngoặc đơn(), và tất cả các số, số
dương, số âm và số 0 đều hiển thị 2 số lẻ thập phân.
Để làm điều này, bạn click chuột phải chọn Format Cells, tab Number mục Custom, ở
khung Type bạn gõ: 0.00_ ;(-0.00)
Nếu bạn muốn số âm màu đỏ, bạn làm tương tự như trên nhưng trong khung Type bạn
gõ: 0.00_ ;[Red](-0.00)
Chú ý là hãy sử dụng dấu ngoặc vuông ([]) trước code. Code sử dụng cho định dạng nói
cho Excel biết để tạo ra số màu đỏ. Bạn có thể sử dụng nhiều code định dạng khác nhau
trong các mục ở Format cells/number/custom.
Bảng 2-1 đến bảng 2-5 được lấy từ tài liệu của Microsoft đã giải thích những code này.
Bảng 2.1: các mã về định dạng số:
Mã số Mô tả
General
Định dạng số chung (gõ như thế nào thấy như thế ấy)
0 (số 0)
Ký hiệu 0 đóng vai trò như số thế chổ cho một ký hiệu số. Ký hiệu 0 chỉ ra
rằng nếu số được định dạng không có nhiều ký tự số như số 0 trong mã
định dạng thì số 0 sẽ thế chổ số đó. Thí dụ nếu mã định dạng là 0000.000
thì số 123.45 mà bạn gõ vào sẽ được hiển thị là 0123.450
# Ký hiệu # đóng vai trò như ký hiệu 0 chỉ khác là nó không buộc một ký số
phải thế chổ nếu không có số nào tương ứng. Ví dụ nếu mã định dạng là
#,###.## thì số 1234.5 sẽ được hiển thị là 1,234.5.
?
Ký hiệu này cũng giống ký hiệu 0 chí có khác là nếu trống chổ thì nó thay
bằng một khoản trắng chứ không phải là số 0. Điều này hữu ích nếu bạn
muốn canh thẳng đấu phân cách thập phân trong bảng. Ngoài ra ký hiệu ?
trong cũng được sử dụng trong phần định dạng phân số. Ví dụ: # ???/???
thì số được thể hiện sẽ đuợc trình bày chính xác đến 3 con số và nếu có thể
giản ước tốt thì nó hiện ra đến mức dưới 3 và có thêm khoản trắng.
% Nếu bạn sử dụng ký hiệu này thì khi hiển thi số Excel sẽ tự động nhân số
đó với 100 và thêm dấu này đằng sau.
,(dấu
phẩy) Dấu phân cách hàng ngàn.
E+, E-
,e+,e- Thể hiện một số dưới dạng scientific thí dụ: mã là 0.00E+00 thì số
12345.56 được thể hiên 1.23E+04
Bảng 2.2: Các mã định dạng chuỗi
Mã chuỗi Mô tả
%,-,+,/,:,(),khoảng
trắng
Các ký hiệu này được thể hiện trong mã định dạng thì sẽ được
thể hiện trong phần hiển thị cúa số đó. Các ký hiệu khác muốn
được thể hiện ra thì bạn phải tham khảo ký tự \ bên dưới
\
Đây là ký hiệu đặc biệt không thể hiện trong định dạ
ng nhưng nó
bắt buộc Excel thể hiện ký tự tiếp theo sau nó dù ký tự đó là ký
tự gì ví dụ: \” thì dấu ” sẽ được thể hiện. Nếu bạn muốn thể hiện
dấu \ thì bạn phải ghi trong mã \\
"text"
Trong trường hợp bạn muốn thể hiện một chuổi ký tự sau hay
trước một số thì bạn bỏ chuỗi ký tự đó trong dấu ngoặc kép như
thế này."VND" #,##0.00 thì một số bất kỳ sẽ được thêm vào
đằng trước “VND”
* Giống như ký hiệu \ nhưng khác một điều là Excel sẽ hiện ra ký
tự sau nó chiếm đến hết ô
_ (dấu gạch dưới)
Ký hiệu này thường được dùng là _), nó nhắc nhở Excel chèn
một khoảng trắng vào vị trí của nó. Ví dụ: trong một định dạng
có dấu ngoặc bao quanh số âm, dấu chấm thập phân của một số
dương không được canh thẳng hàng với dấu chấm thập phân của
số âm, bởi vì số âm sẽ mất nhiều vị trí hơn bên phải dấu thập
phân vì phải thể hiện dấu đóng ngoặc. Trong trường hợp này bạn
dùng một dấu _ tại cuối phần định dạng của số dương để Excel
dành một khoảng trắng sau số dương: một khoảng trắng tương
đương với vị trí dấu đóng ngoặc trong số âm. Ví dụ:
#,##0_);(#,##0).
@ Ký hiệu @ dùng để chỉ chuổi ký tự đã được nhậ
p vào ô. Trong ví
dụ ở phần trên ký hiệu @ thể hiện những gì đã nhập vào ô.
Bảng 2.3: Các mã về định dạng ngày tháng năm
Mã ngày
Mô tả
M Chỉ ra số tháng mà không có số 0 ở đầu với số tháng có một chữ số. Ví dụ
:
1, 2,...12.
Mm Chỉ ra số tháng và có số 0 ở đầu với số tháng có một chữ số. Ví dụ: 01,
02,03...12
Mmm Chỉ tháng nhưng là với 3 chữ đầu của tháng (theo tiếng Anh) thí dụ tháng
1 = Jan
Mmmm
Chỉ tháng và hiện đầy đủ tên tháng thíí dụ tháng 1 = January
D Chỉ ngày với 1 chữ số đối với số nhỏ hơn 10
Dd Chỉ ngày vơi 2 chữ số đối với số nhỏ hơn 10 ví dụ ngày 8 thành 08
Ddd Chỉ thứ trong tuần với 3 ký tự thí dụ thứ Hai = Mon, thứ Ba = Tue
Dddd Chỉ thứ trong tuần với đầy đủ các ký tự. Thí dụ: Monday, Tuesday,
Wednesday...
Yy Chỉ năm với 2 số cuối của năm. Ví dụ: năm 2008 = 08, năm 2009 = 09...
Yyyy Chỉ năm vơi đủ 4 số của năm. Ví dụ: 2008, 2009, 2010....
Ghi chú thêm: để hiểu thêm điều này, bạn làm ví dụ như sau ô A1 bạn gõ 1.53, A2:
2.75, A3: 5.68, A4: 3.25, A5: sum(A1:A4). Kết quả bạn thấy được ở ô A5 là 13.21. Giờ
bạn vào Office button ➝ ➝ Excel Options Advanced và chọn "Set precision as display".
Chọn vùng A1:A4, bạn nhấn nút Decrease Decimal , bạn sẽ thấy phần thập phân bị làm
tròn dần lên. Khi còn 1 số thập phân thì tổng là 13.30; khi không còn chữ số thập phân
nào nữa thì tổng là 14. Bây giờ, bạn lại dùng nút Increase Decimal, bạn sẽ thấy phần số
lẻ ở sau các số ban đầu bạn gõ vào đã trở thành sô 0 hết. Đây là điều bạn nên chú ý khi
lựa chọn tùy chọn này. Tuy nhiên nếu ở các ô A1:A4 là công thức thì dữ liệu ban đầu
của bạn vẫn còn nguyên. Ví dụ, thay vì gõ trực tiếp ô A1 là 1.53, bạn gõ =1.53 và tương
tự như thế cho các ô khác và làm theo các bước hồi nãy và kiểm tra kết quả bạn sẽ thấy