Cách định dạng dữ liệu bằng Custom Format chỉ trong 5 bước đơn giản

Chủ đề hôm qua đã giới thiệu về việc định dạng toàn diện dữ liệu, hôm nay chúng ta tiếp tục khám phá việc tạo định dạng dữ liệu do người dùng tự xác định trong Excel. Với quyền kiểm soát hoàn toàn cách hiển thị dữ liệu, việc nắm bắt một số kỹ thuật nhất định là cần thiết cho việc ứng dụng vào công việc. Chuẩn bị tách trà hoặc cà phê và chúng ta bắt đầu nhé.

Cách mở cửa sổ định dạng số tùy chỉnh:

Để truy cập cửa sổ này, bạn cần:

  • Lựa chọn ô cần định dạng và ấn tổ hợp phím CTRL + 1 (giữ phím CTRL kèm theo nhấn phím 1).
  • Trong tab Number, mục Category, chọn Custom, bạn sẽ thấy giao diện Custom Number Format hiển thị ra với phần Sample giúp bạn được xem trước dạng định dạng và phần Type với các định nghĩa mà Excel đã cung cấp sẵn.

Trước khi tự tạo ra định dạng riêng, bạn nên thử nghiệm với những tùy chọn đã được Excel cài đặt. Nhưng để làm điều này hiệu quả, bạn cần biết dùng định dạng nào cho hợp lý. Chúng ta sẽ cùng tìm hiểu vấn đề đó trong bài viết này.

create-custom-number-format

Cách Excel thực hiện định dạng số:

Excel sử dụng cú pháp định dạng dùng ngôn ngữ đơn giản, bao gồm bốn phần cơ bản: định dạng số dương, số âm, số 0 và văn bản, theo thứ tự như sau:

<Số dương>;<Số âm>;<Số 0>;<Text>

Điều này có nghĩa là khi ô có giá trị là số dương, Excel sẽ áp dụng định dạng được xác định trước cho số dương, và cứ thế lần lượt với các trường hợp khác.

Tham khảo ví dụ sau:

dinh dang so trong excel - 01
Hình minh hoạ cách định dạng số trong Excel

Với ví dụ trên:

  1. Số dương được định dạng như sau: 2 chữ số thập phân và dấu phảy ngăn cách hàng nghìn.
  2. Số âm được định dạng giống như số dương nhưng được bọc trong cặp dấu ngoặc đơn.
  3. Số không được biểu diễn bằng dấu gạch nối.
  4. Kiểu văn bản được tô màu tím.

Những điểm cần lưu ý:

Trước khi đi sâu vào cách tạo định dạng như thế, cần chú ý một số điểm sau:

  1. Định dạng chỉ thay đổi cách các dữ liệu được hiển thị trên màn hình, chứ không làm thay đổi nội dung thực tế trong bảng tính Excel.
  2. Khi bạn chỉnh sửa định dạng có sẵn của Excel, chúng sẽ không bị mất hay được ghi đè, vì thế hãy thoải mái thử nghiệm với định dạng bạn tự tạo.
  3. Không nhất thiết phải sử dụng đủ bốn phần trong định dạng.
  4. Để áp dụng định dạng mặc định của Excel cho bất kỳ phần nào trong bốn phần đã đề cập, chúng ta chỉ viết vào General. Ví dụ: nếu muốn số 0 hiển thị dưới dạng dấu gạch nối, các giá trị khác như số dương, số âm và văn bản dùng định dạng mặc định, ta dùng cú pháp sau: General;-General;”-“;General
  5. Để giấu bất kỳ giá trị nào trong bốn phần, ta có thể bỏ qua phần định dạng tương ứng. Ví dụ: Nếu không muốn”
  1. Khi muốn biểu diễn số 0, mã định dạng được thiết lập như sau là General;-General;;General
  2. Định dạng bạn tạo chỉ áp dụng trong bộ tài liệu Excel đang mở.

Giải thích về các ký tự trong mã định dạng:

Giải thích Dẫn chứng
0 Cho hiện chữ số 0, cả những số 0 không đáng kể, và sẽ làm tròn số nếu cần. #.00 – luôn cho thấy 2 chữ số sau dấu thập phân kể cả khi là số 0
Ví dụ: Khi bạn nhập 5.5 vào ô, Excel sẽ chuyển nó thành 5.50
# Chỉ hiển thị các chữ số quan trọng, loại bỏ số 0 phía sau và làm tròn nếu cần #.## – chỉ hiển thị tới hai chữ số phía sau dấu thập phân

Ví dụ: Nếu gõ 5.5 vào ô Excel, sẽ chỉ xuất hiện là 5.5

Khi nhập 5.555, Excel sẽ làm tròn lên 5.56

? Dùng để canh hàng các chữ số theo dấu thập phân #.??? – Cho thấy tối đa là ba chữ số sau dấu thập phân và canh hàng nếu ít hơn ba chữ số
@ Là nơi thay thế cho kiểu dữ liệu Text 0.00; -0.00; 0; [Red]@ – Ô với định dạng này sẽ hiển thị dữ liệu text màu đỏ

Một số ví dụ:

Định dạng số custom cơ bản

excel-custom-number-format

Bài toán trong hình trên:

  • Cột số 1 sử dụng định dạng mặc định của Excel
  • Cột thứ hai được áp dụng định dạng tùy chỉnh là #.00, nghĩa là luôn hiển thị hai số đằng sau dấu thập phân, bất chấp số 0 đằng sau, và làm tròn số thừa.
  • Cột thứ ba dùng định dạng tùy chỉnh là #.##, có nghĩa là hiển thị tối đa hai chữ số phía sau dấu thập phân, không kể số 0 thừa, và cũng làm tròn số thừa.
  • Cột thứ tư có định dạng tùy chọn là #.???, có nghĩa là hiển thị tới ba chữ số phía sau dấu phẩy, và căn thẳng số dựa trên dấu thập phân.

Định dạng số custom thay thế 000 bằng chữ K

Screen Shot 2016-07-12 at 23.17.59

Hình ảnh minh hoạ trên cho thấy Excel sẽ hiển thị số nhỏ hơn 1000 một cách bình thường, số 0 được biểu diễn như số 0, còn số lớn hơn 1000 sẽ bị chuyển đổi ba số không cuối cùng thành chữ K. Cũng có ví dụ khác như trong hình sau:

Screen Shot 2016-07-12 at 23.22.03

Việc làm tròn số tới hàng triệu và biểu diễn bằng chữ M được thực hiện tương tự như sau:

Screen Shot 2016-07-12 at 23.23.41

Các thí dụ về định dạng tùy chỉnh với chữ, màu và điều kiện

Screen Shot 2016-07-12 at 23.25.04

Ở hai thí dụ trên, các mã định dạng tự chọn được sắp xếp theo tự tự như sau <số dương>;<số

Các bạn sẽ thấy một quy luật rõ ràng: Số dương sẽ đi cùng với việc tăng, số âm đồng hành cùng việc giảm và số 0 vẫn giữ nguyên giá trị.

Sử dụng định dạng tùy chỉnh với cụm từ đặc biệt – #”°C”

Screen Shot 2016-07-12 at 23.34.09

Áp dụng định dạng tùy biến để hiển thị thông điệp

Kỹ thuật này rất hữu ích trong việc xây dựng Dashboard, nhưng trong bài học này chúng tôi chỉ minh họa việc sử dụng mã định dạng tùy biến như sau:

[Magenta][<=18] “Hôm nay” # “độ. Em mặc ấm nhé”;#” độ”;#” độ”

với kết quả được hiển thị như dưới đây

Screen Shot 2016-07-12 at 23.38.18

Nhiệt độ ở Hà Nội và Thái Nguyên đã được định dạng theo mã tùy biến này, khi nhiệt độ nhỏ hơn hoặc bằng 18 độ, chữ thể hiện màu tím và có thông điệp như đã nêu.

Mã màu hỗ trợ bao gồm:

  • [Black]
  • [Green]
  • [White]
  • [Blue]
  • [Magenta]
  • [Yellow]
  • [Cyan]
  • [Red]

Thêm số 0 vào phía trước các con số khác với định dạng tùy chỉnh

Khi muốn hiển thị cột ID với 6 chữ số, ví dụ: 000001, 000002, 000003,… bạn có thể làm được nhờ định dạng 000000

Screen Shot 2016-07-12 at 23.41.58

Hãy thử áp dụng định dạng đặc biệt này trong Excel và điều chỉnh độ rộng của cột xem sao:

Screen Shot 2016-07-12 at 23.43.37

Tải file thực hành tại đây:

[sociallocker id=457]

Tải xuống

[/sociallocker]

Nhằm nâng cao kiến thức về Excel, các bạn có thể tham khảo khóa học SM90 – Excel từ cơ bản đến nâng cao dành cho người đi làm của Siêu Marketing. Bạn sẽ được học những kiến thức toàn diện và hệ thống về các hàm, công cụ trong Excel và cách áp dụng chúng vào công việc. Hiện nay có nhiều ưu đãi cho những ai đăng ký khóa học này. Xem thông tin chi tiết tại:

Trương Thành Tài
0
    0
    Đơn hàng
    Đơn hàng trốngQuay lại Shop