Custom Format là gì?

Custom Format giống như việc bạn trang điểm, mặc quần áo theo các kiểu phong cách khác nhau. Nhưng bản chất, bạn vẫn là chính bạn. Và Custom Format gọi chính xác sẽ là Custom Number Format (định dạng tùy chỉnh dành cho số). Rất nhiều bạn lầm tưởng Custom Format sẽ giúp bạn thay đổi bản chất dữ liệu. Nhưng thực tế không phải vậy. Bản chất dữ liệu là TEXT thì không thể chuyển thành số thông qua Custom Format. Phải tiến hành đi Thái Lan, à mình nhầm, là chuyển đổi. Tương tự, nếu muốn dữ liệu dạng số, chuyển sang chữ chúng ta cũng phải áp dụng chuyển đổi.

Cú pháp căn bản của Custom Format.

Custom Format bao gồm 4 phần căn bản như sau, ngăn cách giữa 4 phần là dấu chấm phẩy “;”.

• Số dương hoặc điều kiện logic [>=Số]: Số lớn hơn 0 hoặc theo biểu thức logic.
• Số âm hoặc điều kiện logic [<=Số]: Số nhỏ hơn 0 hoặc theo biểu thức logic
• Số 0
• Chữ

Custom Format làm những gì?

• Cách định dạng ngày tháng năm với Custom Format.

  • d/m/yy: Viết tắt theo ngày/tháng/năm. Nếu số nhỏ hơn 10, thì không có số 0 đằng trước.
    Ví dụ: 30/11/18 hoặc 5/2/19.
  • dd/mm/yyyy: Viết đầy đủ theo ngày/tháng/năm. Nếu số nhỏ hơn 10, sẽ có số 0 đằng trước. Ví dụ: 08/03/2017 hoặc 05/02/2019.
  • dd/mmm/yyyy: Tháng viết dạng chữ viết tắt, ví dụ: 05/Feb/2019.
  • dd mmmm yyyy: Tháng viết dạng chữ viết đủ, ví dụ: 05 February 2019
  • ddd, mmm dd, yyyy: Ngày và tháng dạng chữ viết tắt, ví dụ: Tue, Feb 05, 2019
  • dddd, mmmm dd, yyyy: Ngày tháng viết đầy đủ, ví dụ: Tuesday, February 04, 2019

• Cách nhanh nhất để viết ra thứ trong tuần từ ngày cho trước.

  • Bạn có thể lập một bảng chấm công và hiển thị các Thứ của ngày trong tuần trên bảng chấm công, cũng như áp dụng hàm WEEKDAY để tìm ra ngày cuối tuần dễ dàng như sau.
  • Bản thân dữ liệu ngày các dòng 3 và 9, các bạn thấy nó chỉ có 2 chữ số dạng ngày “dd”, nhưng thực chất đó là 1 ngày chính xác. Và từ dữ liệu ngày chính xác chúng ta mới có thể làm các phép tính toán, vận dụng các hàm liên quan.
  • Riêng về hàm SEQUENCE là hàm mới của Excel 365, bạn có thể tạo ra 1 dãy số liên tục với các hàm số SEQUENCE(số dòng, số cột, [giá trị bắt đầu], [bước tăng]).
    
  • Hàm WEEKDAY(Ngày, tham số trả về). Với tham số là 1: Tương ứng 1-Chủ nhật, 2-Thứ 2… 7-Thứ 7. Với tham số là 2: 1-Thứ 2, 2-Thứ 3,… 6-Thứ 7, 7-Chủ nhật. Bạn sẽ gặp tham số 2, khi cần áp dụng định dạng tô màu theo điều kiện cho 2 ngày cuối tuần. Ví dụ: =WEEKDAY(Ngày,2)>5

• Cách hiển thị ngày tháng năm bằng tiếng Việt với Custom Format.

  • Tùy chọn Locale trong Custom: Với Office 2007 trở đi, bạn có thể vào Custom Format, sau đó chọn Date => Rồi chọn Locale là Vietnamese, rồi chọn định dạng bất kỳ. Sau đó vào Custom, bạn sẽ thấy dấu [$-mã locale] hiện ra, để tùy chỉnh Locale của Custom Format theo quốc gia tùy chọn.
  • Với Office 2007, tương ứng là: [$-101042A].
  • Office 2010 – 2013, tương ứng là: [$-vi-VN]
  • Và Office 2016/Office 365, bạn có thể viết gọn: [$-vi].

• Cách để viết cụm từ “Ngày”, “tháng”, “năm” trong Custom Format.

  • Và để chèn thêm chữ trong Custom Format bạn dùng dấu nháy kép giữa các “chữ”, ví dụ ta có: dddd, “ngày” dd “tháng” mm “năm” yyyy.
  • Ngoài ra, bạn có thể chèn thêm địa danh: “Hà Nội, ngày” dd “tháng” mm “năm” yyyy. Ví dụ, với ngày 08/03/2017, ta có: Hà Nội, ngày 08 tháng 03 năm 2017.
  • Nếu dùng với hàm TEXT, bạn có thể viết kèm dấu “\” hoặc viết 2 cặp dấu “” như ví dụ bên dưới đây: 

• Cách hiển thị số Chứng minh nhân dân (9 số) và Căn cước công dân (12 số) trong Excel.

  • Bạn có thể tùy chỉnh hiển thị các số CMND, Căn cước công dân với Custom Format để dễ nhìn nhất. 
    

• Công thức tính tuổi trong Excel

Chúng ta có hàm DATEDIF để tính ra số ngày, tháng, năm giữa hai ngày cho trước. Công thức này có trong Microsoft Excel, tuy nhiên không được gợi ý như bao hàm khác. Bạn chỉ việc gõ đúng công thức sẽ vận dụng được.

=DATEDIF(Ngày sinh, Ngày hiện tại, “Y”)*10000 + DATEDIF(Ngày sinh, Ngày hiện tại, “YM”)*100+DATEDIF(Ngày sinh, Ngày hiện tại, “MD”)

Ví dụ ta có, ngày sinh là: 24/01/1987, và ngày hiện tại là: 28/02/2020, ta có kết quả: 330104.

Lúc này áp dụng Custom Format ta sẽ có: 33 tuổi 01 tháng 04 ngày.

=TEXT(DATEDIF(“1987/01/24”, “2020/02/28″,”Y”)*10000+DATEDIF(“1987/01/24”, “2020/02/28″,”YM”)*100+DATEDIF(“1987/01/24”, “2020/02/28″,”MD”),”00 \t\u\ổ\i 00 \t\h\á\n\g 00 \n\g\à\y”)

Tương tự ta sẽ có cách hiển thị cho tiếng Anh khi vận dụng với hàm TEXT khi phân biệt số nhiều và số ít.

• Cách hiển thị số 0 ở số điện thoại 10 – 11 số trong Excel.

Ngoài ví dụ đầu tiên về cú pháp mình có viết ví dụ về số điện thoại giữa 10 số và 11 số. Tuy nhiên bạn có thể dùng cách gọn hơn với: 0#00 000 0000. Với dấu # đại diện cho số có thể có hoặc không có, như vậy với trường hợp 10 số, thì không cần thiết phải thêm số ở sau số 0 đầu tiên. Và với 11 số, thì sẽ hiển thị rõ ra. Nhưng chuyện đó cũng xưa cũ rồi, vì Bộ “Tư Tê (4T)” để chuyển đổi tất cả số điện thoại về 10 số rồi.

Kết hợp giữa hàm TEXT và CUSTOM FORMAT

Như đã nói từ đầu, việc áp dụng CUSTOM FORMAT không làm thay đổi bản chất dữ liệu. Vì vậy, dữ liệu sẽ lưu dạng số, và bạn sẽ dễ nhìn hơn thôi. Vậy làm thế nào để chuyển đổi sang hẳn dạng TEXT theo định dạng trên. Rất may, Excel có hẳn hàm TEXT để chuyển đổi với áp dụng theo cú pháp Custom Format tương ứng.

Ví dụ, bạn có dữ liệu ngày: 05/02/2019, bạn muốn ghi nhận dạng TEXT vì lý do nào đó. Bạn có thể viết công thức như sau: =TEXT(DATE(2019,05,02), “dd/mm/yyyy”). Vì sao lại dùng hàm DATE(Năm, Tháng, Ngày) mà không dùng chữ, hoặc tham chiếu ô? Đơn giản mình sợ các bạn làm sai! Hãy dùng hàm DATE khi tham chiếu đến ngày, nếu bạn không chắc chắn việc nhập ngày tháng năm đúng chuẩn thiết lập trong REGION.

Để tránh sai về ngày bạn nhớ 2 cách nhập sau sẽ tránh sai sót.

• Dùng hàm DATE(Năm, Tháng, Ngày), ví dụ: Date(2018,12,31)
• Nhập ngày theo nguyên tắc yyyy/mm/dd, ví dụ: 2018/12/31

Kết hợp CUSTOM FORMAT với CONDITIONAL FORMATTING.

Ngoài Fill trong Conditional Formatting để tô màu nền, bạn hoàn toàn có thể áp dụng Custom Format để hiển thị ra kết quả mong muốn của mình khi điều kiện định dạng thỏa.

Làm thế nào để có được các ký tự đặc biệt như: ▲▼ trong Custom Format?

Bạn có thể sao chép từ bên ngoài, ví dụ từ các ký tự bên trên, hoặc vào Insert => Chọn Symbol, sau đó chọn Arial, và chọn Subnet: Geometric Shape. Và chọn ký tự tương ứng để chèn vào ô, sau đó copy ra là được.

Áp dụng CUSTOM FORMAT cho FIELD CODE trong Mail Merge.

Trong Word bạn có thể tùy chỉnh cho định dạng Ngày và số trong Mail Merge. Tương ứng tuần tự như sau:

• Ngày: \@.
  • Ví dụ: {MERGEFIELD NgayCap \@ yyyyMMdd}
  • Cách thể hiện chữ “ngày tháng năm” trong Word, ta dùng dấu nháy đơn cho chữ muốn thêm vào: {MERGEFIELD NgayCap \@ ” ‘ngày’ dd ‘tháng’ MM ‘năm’ yyyy”}
• Số: \#. Ví dụ: {MERGEFIELD TrongLuong \# #,##0.00kg}

Ví dụ minh họa:

Định dạng tô màu sắc trong CUSTOM FORMAT

Bạn có thể sử dụng bảng màu trong Custom Format, mã màu [Color1] – [Color56], bảng màu sắc tương ứng bên dưới.

Các phím tắt thông dụng dùng để định dạng trong Excel

Nếu nội dung trên vẫn chưa đáp ứng tìm kiếm của bạn. Bạn có thể dùng chức năng tìm kiếm trên trang web này, với từ khóa: Custom Format, bạn nhé! Và các bài hay khác bạn có thể tham khảo:

• Định dạng dữ liệu sử dụng Custom Format
• Định dạng custom trong Excel – Custom format trong Excel
• Định dạng số tiền trong Excel với Custom Formats