Saturday, January 28, 2023
HomeThủ thuật máy tínhThủ thuật OfficeTạo hoặc xóa định dạng số tùy chỉnh trong Excel

Tạo hoặc xóa định dạng số tùy chỉnh trong Excel

Cách đây cũng khá lâu, mình có sử dụng một vài file excel, trong đó có sử dụng định dạng số tùy chỉnh khá là hay nên sau đó có dành một ít thời gian tham khảo về mảng này. Cho tới mãi bây giờ mới viết thành bài chi tiết nó ra được. Bài viết được thực hiện trên Excel 2013. Các phiên bản khác các bạn làm tương tự.

Mặc định thì Excel đã cung cấp cho ta khá nhiều lựa chọn về định dạng số hiển thị trong các ô.

Các định dạng số có sẵn mặc định trong Excel

Và trong quá trình làm việc,  Excel đủ thông minh để có thể tự động định dạng kiểu dữ liệu cho một ô khi ta nhập dữ liệu nhập vào. Ví dụ: nếu dữ liệu nhập vào là 12.5% thì Excel tự động định dạng cho ô đó kiểu phần trăm Percentage, hay bạn nhập vào 18/9 thì excel tự động hiểu là dữ liệu ngày tháng và lập tức sẽ chuyển nó về thành dạng đầy đủ 18/09/2015.

Excel tự động áp dụng những định dạng có sẳn cho một ô dự vào các điều kiện sau:

  • Nếu số liệu chứa dấu “/” thì có thể được tự động định dạng theo kiểu ngày tháng hay số dạng phân số. VD: 18/09/2015; 2 1/2 (=2.5)
  • Nếu số liệu chứa dấu gạch ngang “-“ thì có thể được tự động định dạng theo kiểu ngày tháng. VD: 18-09-15
  • Nếu số liệu chứa dấu “:” hoặc số liệu có 1 khoảng trắng và chữ A hay P phía sau thì có thể được tự động định dạng theo kiểu thời gian. VD: 10 a (10:00AM)

Từ Excel 2003 trở về trước, việc lựa chọn định dạng số bắt buộc bạn phải mở cửa sổ Format Cells lên thì mới lựa chọn được, nhưng từ Excel 2007 trở về sau này thì việc lựa chọn định dạng số đã trở nên dễ dàng hơn khi nó nằm ngay trên thanh ribbon.

Lựa chọn định dạng số ngay trên thanh ribbon

Mặc dù Excel đã cung cấp khá nhiều định dạng để lựa chọn và phù hợp với một lượng lớn người dùng. Tuy nhiên tùy vào đặc thù công việc của từng người mà sẽ cần những định dạng số khác nhau. Do vậy bạn sẽ phải dùng đến kiểu định dạng số tùy chỉnh này.

Để tạo định dạng số tùy chỉnh, các bạn chọn ô hoặc vùng dữ liệu cần tùy chỉnh số (có thể chọn 1 ô sau đó copy định dạng sang các ô khác cũng được). Tiếp theo đó, các bạn nhấn tổ hợp phím Ctrl + 1 (đây là phím tắt để mở nhanh hộp thoại Format Cells). Chọn tab đầu tiên, là tab Number. Trong danh mục Category, bạn chọn Custom.

Vào mục tùy chỉnh định dạng số

Bây giờ chúng ta sẽ đi tìm hiểu về cấu trúc của định dạng số tùy chỉnh này và cách tạo ra các định dạng phù hợp với nhu cầu.

Cấu trúc của mã định dạng gồm 4 phần và các phần này cách nhau bằng dấu “;”. Theo thứ tự, các đoạn mã này định nghĩa định dạng cho các số dương, số âm, giá trị không và văn bản.

<DƯƠNG>;<ÂM>;<KHÔNG>;<VĂN BẢN>

Bây giờ mới đến phần khó đây. Khó thì không khó nhưng hơi phức tạp và rắc rối. Nếu bạn dành thời gian nghiên cứu kỹ thì nó sẽ không hề khó chút nào. Trước tiên ta làm quen với những mã được quy định để sử dụng trong việc định dạng. Mình sẽ trình bày ở dạng bảng để các bạn dễ hình dung hơn.

Mã định dạngGiải thích
GeneralHiển thị số theo định dạng tổng quát
0 (số không)Biểu thị cho 1 con số.  Chữ số này sẽ đại diện cho chỗ dành sẵn hiển thị những số không vô
nghĩa nếu một số có ít chữ số hơn số các số không trong định dạng.
Ví dụ: nếu bạn nhập 8.9 và bạn muốn nó được hiển thị dưới dạng 8.90, hãy dùng định dạng #.00
#Chỗ dành sẵn cho chữ số này có cùng các quy tắc như 0 (số không). Tuy nhiên, Excel
không hiển thị các số không dư thừa khi số bạn nhập có ít chữ số hơn ở cả hai bên của dấu thập
phân so với số biểu tượng # trong định dạng. Ví dụ: nếu định dạng tùy chỉnh là #.##, và bạn
nhập 8.9 vào ô, số 8.9 sẽ được hiển thị.
?Biểu thị cho 1 con số. Thêm khoảng trống phía trước hoặc phía sau của con số.
Dùng để canh thẳng theo đơn vị, chục, trăm … cho các ô liền kề trên và dưới.
Ví dụ: Định dạng ?
. (Dấu chấm)Dấu chấm phân cách thập phân.
VD: Nếu nhập 6.4 vào một ô có mã định dạng là #.00 thì ô đó sẽ hiển thị là 6.40
%Dấu phần trăm. VD: 6.4%
, (Dấu phẩy)Dấu phân cách hàng ngàn. VD: 256,000
Nếu đặt phía sau mã định dạng là số thì nó sẽ hiển thị theo đơn vị
là ngàn (chú ý: giá trị của ô không thay đổi, nó chỉ thay đổi hiển thị)
VD: Nếu nhập 15000000 vào một ô có mã định dạng là #,, thì ô đó
sẽ hiển thị là 15. Như vậy, nếu muốn định dạng theo đơn vị là tỉ thì
sẽ định dạng là #,,,
E- E+ e- e+Dạng số khoa học.
VD: 1E+01 (10 mũ +1 hay bằng 10), 3E-02 (3 nhân với 10 mũ -2 hay bằng 0.03)
$ – + / ( ) :
khoảng trắng
Tất cả các ký tự này sẽ hiển thị trong ô niếu như nó xuất hiện trong
mã định dạng. VD: Nếu nhập 10.6 vào một ô có kiểu định dạng là
.0$ thì ô đó sẽ được hiển thị là 10.6$
\Hiển thị ký tự tiếp theo trong định dạng. VD: Nếu nhập 10.6 vào
một ô có kiểu định dạng là #.0\Đ thì ô đó sẽ được hiển thị là 10.6Đ
*Hiển thị lặp đi lặp lại ký tự kế tiếp cho đến khi nội dung của ô lấp
đầy chiều rộng của ô đó. VD: Nếu nhập 12.4 vào một ô có định
dạng là #.0*’ thì ô đó sẽ hiển thị 12.4”””’
_ (dấu gạch dưới)Cho một khoảng trắng có chiều rộng bằng với chiều rộng của ký tự
kế tiếp. VD: Nếu nhập 12.4 vào một ô có kiểu định dạng là (__#.0)(sau dấu mở ngoặc là 2 dấu gạch dưới nhé) thì ô đó sẽ hiển thị là ( 12.4)
“nội dung”Hiển thị văn bản bên trong dấu ngoặc kép.
VD: Nếu nhập 10.6 vào một ô có kiểu định dạng là #.0 “đồng” thì ô
đó sẽ hiển thị là 10.6 đồng
@Biểu thị cho dạng văn bản. VD: Nếu nhập vào chữ VN vào một ô có kiểu
định dạng là @*’ thì ô đó sẽ hiển thị là VN”””’
[màu]Số liệu trong ô sẽ được hiển thị màu theo như mã định dạng. Các mã màu
có thể dùng là: Black, Blue. Cyan, Green, Magenta, Red, White, Yellow.
VD: Nếu nhập 12.4 vào một ô có kiểu định dạng là [Red] thì ô đó sẽ hiển thị là 12.4 có màu đỏ
[điều kiện giá trị]Cho phép định dạng ô theo tiêu chuẩn thỏa mãn giá trị nào đó. Có thể áp dụng cho 3 phần đầu trong 4 phần của cấu trúc của mã định dạng.
VD: Nếu nhập 10.5 vào một ô có mã định dạng là [<10]”Low”* 0;[>20]”High”* 0;”Average”* 0;@*’ thì ô đó sẽ hiển thị là |Average 11| (do định dạng không có thập phân nên khi nhập vào 10.6 thì chỉ hiển thị là 12). Nếu nhập vào 22 thì hiển thị là |High 22|. Nếu nhập vào 0 thì hiển thị là |Low 0|.
Còn nếu nhập vào là VNPT thì hiển thị là |VNPT”””’|

 

MỘT SỐ KIỂU ĐỊNH DẠNG NGÀY THÁNG

Mã định dạngGiải thích
mHiển thị số theo định dạng là tháng (từ 1 -> 12). VD: Khi nhập
9/13/2015 vào ô có định dạng là m thì ô đó hiển thỉ là 9
mmHiển thị số theo định dạng là tháng (từ 01 -> 12). VD: Khi nhập
9/13/2015 vào ô có định dạng là mm thì ô đó hiển thỉ là 09
mmmĐịnh dạng là tháng (từ January -> December). VD: Khi nhập
9/13/2015 vào ô có định dạng là mmmm thì ô đó hiển thỉ là September
mmmmmĐịnh dạng là tháng (từ J -> D). VD: Khi nhập 9/13/2015 vào ô có
định dạng là mmmmm thì ô đó hiển thỉ là S (hiển thị ký tự đầu tiên)
ddHiển thị là ngày (từ 01-> 31). VD: 9/5/2015 hiển thị 05
dddHiển thị là ngày trong tuần (từ sun -> sat). VD: 9/5/2015 hiển thị
Sat
ddddHiển thị là ngày trong tuần (từ sunday -> saturday). VD: 5/9/2015
hiển thị saturday
yy hoặc yyyyHiển thỉ là năm. VD: 9/5/2015 hiển thị 15 or 2015
h hoặc hhHiển thị là giờ. VD: 7:30 AM hiển thị là 7 or 07
m hoặc mmHiển thị là phút. VD: 7:05 AM hiển thị là 5 or 05
s hoặc ssHiển thị là giây. VD: 7:30:06 hiển thị là 6 or 06
AM/PMHiển thị giờ sử dụng 12 giờ. Nếu không có định dạng này thì khi
nhập vào ô dữ liệu dạng giờ thì nó sẽ hiển thị giờ là 24 giờ trong ngày.

Cấu trúc của mã định dạng ta đã biết ở đầu bài viết, tất cả các loại mã mình vừa liệt kê ở bảng trên. Vấn đề là ta phải kết hợp chúng lại thành định dạng mà ta cần. Nghe thì đơn giản nhưng kết hợp được cũng mệt đấy. Nào ta bắt đầu nhé.

Nhắc lại cấu trúc tí: <DƯƠNG>;<ÂM>;<KHÔNG>;<VĂN BẢN>. Mặc dù nó có 4 phần nhưng bạn không phải đưa tất cả các phần vào định dạng số tùy chỉnh của bạn. Nếu bạn chỉ xác định hai đoạn mã cho định dạng số tùy chỉnh của bạn thì theo thứ tự của cấu trúc, đoạn đầu tiên sẽ được dùng cho các số dương và số không, đoạn thứ hai được dùng cho các số âm. Nếu định dạng tùy chỉnh của bạn chỉ có một đoạn mã, nó sẽ được dùng cho tất cả các số.

Nói nhiều quá khó hiểu, làm cái ví dụ đơn giản nhé:

Cấu trúc có 4 phần, tuy nhiên bạn không bắt buộc phải đưa vào đầy đủ cả 4 phần. Ví dụ định dạng của bạn là #.##0_);(#.##0) khi nhập số -1234.556 vào, kết quả là (1.235)

Trong định dạng số bạn có thể sử dụng cả văn bản. Ví dụ để hiển thị 11.225.000 đồng, bạn định dạng ô như sau: #.###” đồng”. Dấu #

https://support.office.com/vi-vn/article/T%E1%BA%A1o-ho%E1%BA%B7c-x%C3%B3a-b%E1%BB%8F-%C4%91%E1%BB%8Bnh-d%E1%BA%A1ng-s%E1%BB%91-t%C3%B9y-ch%E1%BB%89nh-83657ca7-9dbe-4ee5-9c89-d8bf836e028e?ui=vi-VN&rs=vi-VN&ad=VN

Sau khi tạo ra một kiểu định dạng mới cho một ô nào đó thì nó sẽ thêm vào trong danh sách các kiểu tùy biến và chúng ta có thể áp dụng định dạng đó cho các ô khác trong workbook đó. Kiểu định dạng do người dụng tạo ra trên workbook nào thì chỉ có thể sử dụng nó để áp dụng cho các ô khác trên workbook đó. Nếu người dùng tạo ra rất nhiều kiểu định dạng cho một workbook thì sẽ làm cho workbook đó trở nên nặng nề khi mở ra, lưu hay đóng lại. Vì vậy, những kiểu định dạng đã tạo ra nào mà không cần thiết thì nên xóa nó khỏi danh sách các kiểu định dạng tùy biến.

RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

- Advertisment -

Most Popular