gacon883
Tổng số bài gửi : 148
Điểm tích lũy : 4856
Thích : 3
Tham gia : 03/02/2012
 |  Tiêu đề: Tạo các định dạng số cho riêng bạn  Mon Feb 27, 2012 11:57 am | |
| 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.)
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 có sự khác biệt.
Đị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.... | |
Bảng 2.4: Các mã định dạng về thời gian
Mã ngày |
Mô tả | |
H | Chỉ giờ với 1 chữ số | |
Hh | Chỉ giở với 2 chữ số | |
m | Chỉ phút với 1 chữ số | |
mm | Chỉ phút với 2 chữ số | |
s | Chỉ giây với 1 chữ số | |
ss | Chỉ giây với 2 chữ số | |
AM/PM am/pm | Hiển thị giờ với 12 tiếng, và hiển thị AM, PM hay bạn cũng có thể dùng am/pm, A/P, a/p, S/C | |
Bảng 2.5: Các mã định dạng khác:
Mã định dạng |
Mô tả | |
[BLACK],[BLUE],[CYAN],[GREEN],[MAGENTA],[RED], [WHITE],[YELLOW], [CÁC MÀU KHÁC TÙY BẠN CHỌN] | Dùng để thể hiện màu. Màu bạn chọn phải là màu trong 56 màu của bảng màu. | |
[giá trị điều kiện] | Dùng
để ra điều kiện vơi các toán tử so sánh: >, <, =, >=, <= ,
<> (không bằng). Ví du: [>100] : điều kiện lớn hơn 100 | |
Hãy chú ý đến điểm đặc biệt ở mã định dạng cuối cùng trong bảng 2-5: các
toán tử so sánh. Giả sử bạn muốn định dạng số là: 0.00_ ;[Red](-0.00)
để hiển thị số âm là màu đỏ và trong ngoặc đơn nếu số nhỏ hơn -100. Để
làm điều này, bạn làm như sau: 0.00_ ;[Red][<-100](-0.00);0.00
Mã định dạng [Red][<-100](-0.00) được đặt trong phần số âm tạo nên
kết quả này. Việc sử dụng phương pháp này kết hợp với định dạng có điều
kiện bạn có thể nhân đôi số điều kiện định dạng có điều kiện từ 3 lên
đến 6 điều kiện.
Thường người sử dụng muốn hiện thị giá trị dollar ở dạng chữ. Để làm
điều này, ở khung type của Format cells/number/custom, bạn gõ: 0
"Dollars and" .00 "Cents"
Định dạng này sẽ ảnh hưởng đến một số được nhập vào như 55.25 được hiển
thị là "55 Dollars and .25 Cents". Nếu bạn muốn đổi số thành dollars và
cents, hãy tham khảo thêm hai hàm tự tạo từ link sau của Microsoft: http://www.ozgrid.com/VBA/ValueToWords.htm and http://www.ozgrid.com/VBA/CurrencyToWords.htm.
Bạn cũng có thể sự dụng một định dạng riêng để hiển thị các từ như :
Low, Average, hay High cùng với số được gõ vào. Đơn giản chỉ việc sử
dụng mã định dạng sau: [<11]"Low"* 0;[>20]"High"* 0;"Average"* 0
Hãy chú ý đên việc sử dụng dấu "*". Dấu này sẽ lặp lại các ký tự tiếp
theo trong định dạng để điền đầy ô theo độ rộng cột, nghĩa là tât cả các
từ Low, Average, or High sẽ bị dồn về bên phải, trong khi số sẽ bị dồn
về bên trái.
| |
|
