Microsoft Excel (Bài 22): Định dạng dữ liệu toàn tập trong Excel

Không có phụ nữ xấu, chỉ có người photoshop không đẹp. Không có bảng Excel xấu, chỉ có người trình bày không đẹp. Hôm nay, chúng ta sẽ đi thăm một chủ đề về làm đẹp – định dạng dữ liệu trong Excel. Những thủ thuật này các bạn có thể áp dụng được cho các phiên bản hiện nay.

Về cơ bản thì Excel hỗ trợ chúng ta những kiểu định dạng sau đây:

1. Định dạng số (Number)
2. Định dạng chữ (Text)
3. Định dạng tiền tệ (Currency)
4. Định dạng phần trăm (Percentage)
5. Định dạng trong kế toán (Accounting)
6. Định dạng số trong khoa học (Scientific)
7. Định dạng người dùng tự định nghĩa (Custom)

ĐỊNH DẠNG DỮ LIỆU CƠ BẢN TRONG EXCEL:

Mặc định thì Excel sẽ luôn định dạng bất kì những gì bạn gõ vào 1 ô với kiểu General. Excel cũng hỗ trợ rất nhiều kiểu định dạng mà các bạn có thể bấm ngay vào ô Format như trên hình và trọn. Trong hình minh hoạ, các bạn có thể thấy được, cùng 1 dữ liệu – con số 0,234, với mỗi kiểu định dạng chúng ta sẽ thấy con số này được thể hiện khác đi.

Thủ thuật: muốn biết được dữ liệu thực sự đằng sau 1 ô là gì, chúng ta cần nhìn vào thanh công thức.

CÓ TỚI 4 CÁCH ĐỂ MỞ HỘP THOẠI FORMAT CELLS

Để định dạng dữ liệu một cách chi tiết nhất, chúng ta sẽ sử dụng hộp thoại Format Cells. Có 4 cách để có thể mở được hộp thoại này như sau:

1. Chọn vùng cần định dạng và bấm phím tắt CTRL + 1 (Nhẫn giữ CTRL và bấm số 1)
2. Chuột phải vào vùng cần định dạng và chọn trong menu này Format Cells…

   

   menu chuột phải format cells

    

3. Bấm vào vị trí các mũi tên ở đã được khoanh tròn đỏ như trong hình vẽ

   

   Mở hộp thoại Format Cells

4. Trong thẻ Home, chọn Format rồi chọn Format Cells …

   

   Mở hộp thoại Format Cells

    

CÓ TỚI 6 THẺ CẦN QUAN TÂM TRONG HỘP THOẠI FORMAT CELLS

Sau khi đã mở hộp thoại Format Cells rồi, chúng ta sẽ thấy 6 thẻ để các bạn có thể định dạng hầu như toàn bộ một vùng trong Excel:

• Thẻ Number
• Thẻ Alignment
• Thẻ Font
• Thẻ Border
• Thẻ Fill
• Thẻ Protection

Trong bài viết này, chúng ta sẽ tìm hiểu về thẻ Number – định dạng số liệu.

ĐỊNH DẠNG SỐ – NUMBER

Trong thẻ Number, mục Categories bên tay trái, chúng ta có thể thấy được lần lượt các lựa chọn để phục vụ công việc định dạng dữ liệu trong Excel.

Trong mục này, mỗi phần bên tay phải lần lượt có ý nghĩa là:

1. Sample: cho chúng ta xem trước, sự thay đổi trong định dạng sẽ ảnh hưởng lên dữ liệu thế nào
2. Decimal places: dùng để chỉ ra chúng ta cần bao nhiêu chữ số sau dấu phẩy ở phần thập phân
3. Dấu tích ở mục Use 1000 Separator (,): nếu cần ngăn cách hàng nghìn, bạn cần đánh dấu tích vào đây
4. Negative numbers: sẽ giúp bạn điều chỉnh một số âm sẽ trông như thế nào

ĐỊNH DẠNG TIỀN TỆ VÀ KẾ TOÁN

Với kiểu định dạng tiền tệ và kế toán, bạn có 2 sự lựa chọn
Decimal places: như phía trên đã giải thích, phần này cho biết chúng ta cần bao nhiêu chữ số đằng sau dấu phẩy
Symbol: Ký hiệu tiền tệ mà chúng ta muốn sử dụng

Sự khác nhau giữa kiểu định dạng tiền tệ và kiểu định dạng kế toán là: với kiểu định dạng tiền tệ, ký hiệu đồng tiền sẽ được viết liền ngay phía trước con số, với kiểu định dạng kế toán, ký hiệu tiền tệ sẽ đưọc viết theo 1 hàng căn lề phía bên trái

ĐỊNH DẠNG NGÀY THÁNG VÀ THỜI GIAN:

Định dạng ngày tháng và thời gian trong Excel rất phong phú, lý do là vì mỗi nước trên thế giới có kiểu định dạng ngày tháng và thời gian riêng và Excel hỗ trợ hầu hết các định dạng đó. Việc còn lại là các bạn sẽ phải tìm định dạng phù hợp qua sự lựa chọn trong danh sách Locale (Location) và Type của Excel như trong hình mà thôi.

ĐỊNH DẠNG PHẦN TRĂM TRONG EXCEL:

Kiểu định dạng phần trăm này chỉ cho phép bạn thay đổi 1 lựa chọn, đó là số chữ số thập phân đằng sau dấu phẩy

ĐỊNH DẠNG PHÂN SỐ TRONG EXCEL:

Kiểu định dạng này sẽ giúp các bạn viết phân số trong Excel. Lưu ý là khi bạn muốn nhập dữ liệu là phân số trong Excel, bạn không thể nhập được trực tiếp phân số đó. VD: để nhập phân số “1/4” thì bạn phải nhập vào 0,25 rồi sử dụng tính năng định dạng phân số của Excel.

ĐỊNH DẠNG KHOA HỌC:

Định dạng khoa học là cách Excel dùng để gọi khi biểu diễn những con số rất lớn hoặc rất nhỏ. VD: chúng ta có 1 con số rất lớn là 20 tỉ, 20 tỉ là một con số có 10 số 0. Thay vì viết là 20,000,000,000 thì Excel sẽ biểu diễn con số này dưới dạng 2E+10. Ngoài cách đọc là 20 tỉ, thì chúng ta có thể đọc con số này là 2 nhân 10 mũ 10.
Lựa chọn duy nhất trong phần này chúng ta có thể thay đổi là số chữ số thập phân đằng sau dấu phẩy.

ĐỊNH DẠNG DỮ LIỆU KIỂU CHỮ:

Bạn có thể chuyển nội dung 1 ô sang định dạng Text, chỉ cần chọn những ô cần chuyển và chọn định dạng Text. Mặc định trong Excel sẽ căn lề trái cho nội dung Text.

ĐỊNH DẠNG ĐẶC BIỆT – SPECIAL:

Với mỗi nước, lại có 1 số dãy số đặc trưng như số điện thoại, mã số bưu điện, số bảo hiểm an sinh xã hội. Để định dạng các số này, chúng ta sử dụng mục Specia

Định dạng dữ liệu sử dụng Custom Format

Để mở được cửa sổ này:

• Chúng ta chọn ô cần định dạng sau đó bấm phím tắt CTRL + 1 (Bấm giữ CTRL rồi nhấn phím số 1 trên bàn phím).
• Trong thẻ Number, mục Category, bấm vào Custom là bạn đã có thể nhìn thấy Custom Number Format như hình minh hoạ gồm mục Sample giúp chúng ta nhìn thấy định dạng đó sẽ trông thế nào, mục Type được Excel định nghĩa sẵn 1 số kiểu chúng ta sẽ dùng.

Các bạn nên thử qua các định dạng mà Excel đã cho sẵn ở đây trước khi tạo ra 1 định dạng của riêng bạn, như vậy chúng ta sẽ làm việc hiệu quả hơn trên những gì có sẵn. NHƯNG, bạn cần phải biết là nên dùng định dạng nào? Và bài này chúng ta sẽ đi tìm hiểu về điều đó.

EXCEL ĐỊNH DẠNG SỐ NHƯ THẾ NÀO?

Excel định dạng số bằng một ngôn ngữ nhỏ như sau, ngôn ngữ này gồm 4 phần theo thứ tự, mỗi phần là 1 định dạng áp dụng cho lần lượt các trường hợp số dương, số âm, số 0 và text như sau đây:

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

Điều này nghĩa là, khi nội dung trong vùng bảng tính của các bạn là số dương, Excel sẽ định dạng theo những gì quy định trong mục số dương rồi tương tự cho các phần còn lại.

Xét ví dụ sau đây

Trong ví dụ này

1. Số dương được định dạng với 2 chữ số sau dấu phẩy và sử dụng dấu phẩy để ngăn cách hàng ngàn
2. Số âm được định dạng tương tự như số dương nhưng được đặt ở trong ngoặc
3. Sô 0 được viết dưới dạng một dấu gạch nối
4. Dữ liệu text được tô màu tím.

NHỮNG LƯU Ý:

Trước khi tìm hiểu làm sao để tạo ra được một định dạng như vậy, chúng ta có 1 vài lưu ý sau đây

1. Định dạng chỉ thay đổi những gì chúng ta nhìn thấy trên màn hình, nội dung thực sự được lưu trong bảng dữ liệu Excel không thay đổi
2. Khi các bạn sửa những định dạng có sẵn của Excel, những định dạng này không bị mất đi hay ghi đè lên, vậy nên, hãy dũng cảm thử những định dạng tự mình tạo ra
3. Định dạng do bạn bạn tạo ra không nhất thiết phải có cả 4 phần
4. Để sử dụng định dạng mặc định của Excel cho phần nào trong 4 phần đã nói ở trên, chúng ta chỉ cần viết vào phần đó kiểu General. VD. Muốn số 0 sẽ được viết bằng dấu gạch nối – , số dương, số âm và text được định dạng mặc định thì chúng ta dùng mã định dạng sau đây General;-General;”-”;General
5. Để ẩn một giá trị trong 4 phần trên, chúng ta chỉ cần bỏ qua định dạng cho phần tương ứng đó. VD. Nếu chúng ta không muốn hiển thị số 0, thì mã định dạng sẽ như sau General;-General;;General
6. Những định dạng mà bạn tạo ra, có hiệu lực trong 1 tài liệu Excel hiện thời mà thôi

CUSTOM FORMAT CƠ BẢN MỘT SỐ VÍ DỤ:

Trong ví dụ trên

• Cột 1 là định dạng mặc định của Excel
• Cột thứ 2 có định dạng custom là #.00 có ý nghĩa: luôn hiển thị 2 chữ số đằng sau dấu thập phân dù chữ số đó có là số 0 đi nữa, các số thừa ra sẽ được làm tròn theo quy tắc.
• Cột thứ 3 có định dạng custom là #.## có ý nghĩa: hiện thị tới 2 chữ số đằng sau dấu thập phân, bỏ qua số 0, các số thừa ra sẽ được làm tròn theo quy tắc.
• Cột thứ 4 có định dạng customer là #.??? có ý nghĩa: hiển thị tới 3 chữ số sau dấu phẩy, căn thẳng hàng với các dòng có cùng định dạng bởi dấu thập phân

CUSTOM FORMAT THAY THẾ 000 BẰNG K

Trong hình minh hoạ trên: các số nhỏ hơn 1000 sẽ được Excel hiển thị bình thường, số 0 được hiển thị bằng 0, các số lớn hơn 1000 sẽ được cắt đi 3 số 0 ở cuối và thay bằng chữ K. Một số ví dụ khác như hình minh hoạ sau đây:

Tương tự như vậy khi làm tròn đến hàng triệu với chữ M

CÁC VÍ DỤ VỀ CUSTOM FORMAT VỚI CHỮ, MÀU VÀ ĐIỀU KIỆN

Trong 2 ví dụ trên, với custom format code theo thứ tự <số dương>;<số âm>;<số 0>; các bạn có thể dễ dàng nhận thấy quy luật ở đây: Tăng sẽ đi với số dương, giảm đi với số âm, số 0 thì được giữ nguyên.

SỬ DỤNG CUSTOM FORMAT VỚI KÍ TỰ ĐẶC BIỆT – #”°C”

SỬ DỤNG CUSTOM FORMAT ĐỂ ĐƯA RA THÔNG ĐIỆP

Kỹ thuật này có thể được sử dụng trong việc tạo Dashboard, trong bài này chúng ta sẽ không tạo dashboard mà dùng custom format code sau đây:

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

và kết quả như sau

2 ô chứa nhiệt độ của Hà Nội và Thái Nguyên đã được định dạng với custom format code, cứ nhỏ hơn hoặc bằng 18 độ thì chữ sẽ được tô màu tím và có thông điệp như trên.

Các màu được hỗ trợ là:

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

DÙNG CUSTOM FORMAT ĐỂ CHÈN SỐ 0 ĐẰNG TRƯỚC CÁC SỐ KHÁC

Đôi khi chúng ta có cột ID có 6 số chẳng hạn, chúng ta muốn cột này được hiển thị theo định dạng 000001, 000002, 0000003, … chẳng hạn thì định dạng 000000 sẽ giúp bạn làm điều đó

Có 1 định dạng đặc biệt trong Excel với custom format code các bạn nên thử như sau. Sau khi định dạng như vậy rồi, các bạn thử thay đổi độ rộng của cột xem