Tìm hiểu định dạng số tùy chỉnh trong Google Sheets là gì và ví dụ minh họa

Quy tắc định dạng số tùy chỉnh của Google Sheets được sử dụng để chỉ định các quy tắc định dạng đặc biệt cho các con số. Các quy tắc tùy chỉnh này kiểm soát cách các số được hiển thị trong Trang tính của bạn mà không thay đổi chính số đó. Chúng là một lớp trực quan giúp trang tính của bạn hấp dẫn hơn. Bạn có thể kết hợp các hiệu ứng hình ảnh mà không làm thay đổi dữ liệu của mình.

Google Sheets đã có sẵn nhiều định dạng (ví dụ: phần trăm, tiền tệ, số thập phân) nhưng bạn cũng có thể tạo một dịnh dạng riêng của mình để sử dụng nếu cần.

Cách sử dụng định dạng số tùy chỉnh của Google Trang tính

Bạn có thể truy cập các định dạng số tùy chỉnh thông qua menu:

Format > Number > More Formats > Custom number format. Cửa sổ trình chỉnh sửa định dạng số tùy chỉnh trông giống như sau:

Bạn nhập quy tắc của mình vào hộp trên cùng và nhấn “Áp dụng” để áp dụng quy tắc đó cho ô hoặc phạm vi bạn đã đánh dấu.

Dưới hộp nhập liệu, bạn sẽ thấy bản xem trước quy tắc sẽ thực hiện, cung cấp cho bạn một dấu hiệu hữu ích và khá chính xác về việc những con số của bạn sẽ trông như thế nào khi áp dụng quy tắc này.

Các quy tắc trước đó được hiển thị trong ngăn xem trước. Bạn có thể nhấp để khôi phục và sử dụng lại bất kỳ thứ nào trong số này.

Cấu trúc định dạng số tùy chỉnh của Google Sheets

Bốn quy tắc định dạng số tùy chỉnh của Google Sheets

Bạn có bốn “quy tắc” bạn có thể áp dụng, được nhập theo thứ tự sau và phân tách bằng dấu chấm phẩy:

1. Định dạng cho số dương
2. Định dạng cho số âm
3. Định dạng cho số không
4. Định dạng cho văn bản

1. Định dạng cho số dương

#,##0.00 ; [red](#,##0.00) ; 0.00 ; “some text “@

Quy tắc đầu tiên, đứng trước dấu chấm phẩy đầu tiên (;), cho Google Sheets biết cách hiển thị số dương.

2. Định dạng cho số âm

#,##0.00 ; [red](#,##0.00) ; 0.00 ; “some text “@

Quy tắc thứ hai, nằm giữa dấu chấm phẩy thứ nhất và thứ hai, cho Google Sheets biết cách hiển thị số âm.

3. Định dạng cho số không

#,##0.00 ; [red](#,##0.00) ; 0.00 ; “some text “@

Quy tắc thứ ba, nằm giữa dấu chấm phẩy thứ hai và thứ ba, cho Google Sheets biết cách hiển thị các giá trị bằng không.

Qui tắc	Trước	Sau
0;0;"Zero"	0	Số không

4. Định dạng cho văn bản

#,##0.00 ; [red](#,##0.00) ; 0.00 ; “some text “@

Quy tắc thứ tư, đứng sau dấu chấm phẩy thứ ba, cho Google Sheets biết cách hiển thị giá trị văn bản.

Bạn có bắt buộc phải sử dụng cả bốn quy tắc không?

Bạn không cần chỉ định đủ 4 quy tắc định dạng trên trong bảng tính của mình. Nếu bạn chỉ chỉ định một quy tắc thì nó sẽ được áp dụng cho tất cả các giá trị. Ví dụ, ếu bạn chỉ định quy tắc dương và âm, thì bất kỳ giá trị 0 nào cũng có định dạng giá trị dương.

Dưới đây là một số ví dụ về định dạng đơn và đa quy tắc:

Qui tắc	Số dương	Số âm	Số không	Văn bản
0	1	-1	0	văn bản
0;(0)	1	(1)	0	văn bản
[red]0	1	-1	0	văn bản
0;[red]-0	1	-1	0	văn bản
0;[red]-0;[blue]0;[green]@	1	-1	0	văn bản

Quy tắc định dạng số tùy chỉnh của Google Sheets

Quy tắc chữ số 0 (0)

Số không (0) được sử dụng để buộc hiển thị một chữ số hoặc số không, khi số có ít chữ số hơn được hiển thị trong quy tắc định dạng. Sử dụng quy tắc chữ số 0 (0) để buộc các số phải có độ dài nhất định và hiển thị (các) số 0 đứng đầu.

Ví dụ:

Qui tắc	Trước	Sau
0.00	1,5	1,50
00000	721	00721

Quy tắc dấu Thăng (#)

Dấu thăng (#) là phần giữ chỗ cho các chữ số tùy chọn. Nếu giá trị của bạn có ít chữ số hơn # ký hiệu trong quy tắc định dạng, thì dấu # bổ sung sẽ không hiển thị bất kỳ thứ gì.

Qui tắc	Trước	Sau
####	15	15
####	1589	1589
#.##	1,5	1,5

Dấu phân cách hàng nghìn (,)

Dấu phẩy (,) được sử dụng để thêm hàng nghìn dấu phân cách vào quy tắc định dạng của bạn. Quy tắc #,##0 sẽ hoạt động cho hàng nghìn và hàng triệu số.

Qui tắc	Trước	Sau
#,##0	1495	1,495
#,##0.00	1234567,89	1.234.567,89

Dấu chấm (.)

Dấu chấm (.) được dùng để hiển thị dấu thập phân. Khi bạn bao gồm dấu chấm trong quy tắc định dạng của mình, dấu thập phân sẽ luôn hiển thị, bất kể có bất kỳ giá trị nào sau số thập phân hay không.

Qui tắc	Trước	Sau
0.	10	10.
0.	10.1	10.
0.00	10	10.00

Hàng nghìn (k hoặc K) hoặc Hàng triệu (m hoặc M)

Qui tắc	Trước	Sau
0.0,	2500	2,5
0,"k"	2500	3k
0.0,"k"	2500	2,5k
0.0,,"M"	1234567	1,2 M

Số âm có dấu ngoặc ()

Có thể thêm dấu ngoặc vào quy tắc số âm để thay đổi định dạng từ -100 thành (100), thường thấy trong các kịch bản kế toán và tài chính.

Qui định	Trước	Sau
0;(0)	-100	(100)

Dấu hoa thị (*)

Dấu hoa thị (*) được sử dụng để lặp lại các chữ số trong quy tắc định dạng của bạn. Ký tự theo sau dấu hoa thị được lặp lại để lấp đầy chiều rộng của ô.

Trong ví dụ sau, dấu gạch ngang được lặp lại để lấp đầy chiều rộng của ô trong Google Trang tính:

Qui định	Trước	Sau
*-0	100	—————— 100

Dấu chấm hỏi (?)

Dấu chấm hỏi (?) được sử dụng để căn chỉnh các giá trị một cách chính xác bằng cách thêm khoảng trắng cần thiết, ngay cả khi số chữ số không khớp.

Xem ví dụ này:

Dấu gạch dưới (_)

Dấu gạch dưới (_) cũng thêm không gian cho các định dạng số của bạn.

Trong trường hợp này, ký tự theo sau dấu gạch dưới xác định kích thước của khoảng trắng cần thêm (nhưng không được hiển thị). Vì vậy, quy tắc này cho phép bạn thêm lượng không gian chính xác.

Ví dụ: #,##0.00_);(#,##0.00) thêm một khoảng trắng sau số dương là độ rộng của một dấu ngoặc, để dấu thập phân thẳng hàng với các số âm có dấu ngoặc.

Bạn có thể thấy rõ điều này trong hình ảnh sau đây, trong đó dòng đầu tiên KHÔNG có khoảng cách nhưng dòng thứ hai thì có. Đánh dấu màu đỏ đã được thêm vào để hiển thị kết quả của khoảng cách:

 

 

Dấu ngạch chéo ngược (\)

Giả sử bạn muốn thực sự hiển thị một dấu thăng ở định dạng của mình. Nếu bạn chỉ cần thêm nó vào quy tắc định dạng của mình, thì Google Sheets sẽ diễn giải nó như một trình giữ chỗ cho các chữ số tùy chọn (xem ở trên).

Để thực sự hiển thị dấu thăng, hãy đặt trước nó bằng dấu gạch chéo ngược (\) để đảm bảo nó hiển thị.

Điều này cũng áp dụng cho bất kỳ ký tự đặc biệt nào khác.

Qui tắc	Trước	Sau
#0	10	10
\#0	10	# 10

At (@)

Biểu tượng At (@) được sử dụng làm trình giữ chỗ cho văn bản, có nghĩa là không thay đổi văn bản đã nhập.

Qui tắc	Trước	Sau
0;0;0;"Special text value!"	Một số tiếp theo	Giá trị văn bản đặc biệt!
0;0;0;@	Một số tiếp theo	Một số tiếp theo

Phân số (/)

Dấu gạch chéo phía trước (/) được sử dụng để biểu thị phân số.

Ví dụ: quy tắc # ?/?sẽ hiển thị các số dưới dạng phân số:

Qui tắc	Trước	Sau
# ?/?	2,3333333333	2 1/3

Phần trăm (%)

Dấu phần trăm (%) được sử dụng để định dạng các giá trị dưới dạng%. Như với các quy tắc khác, trước tiên bạn chỉ định các chữ số và sau đó sử dụng dấu% để thay đổi thành phần trăm, ví dụ:0.00%

Qui tắc	Trước	Sau
0.00%	0,2829	28,29%

Số mũ (E)

Đối với các số rất lớn (hoặc rất nhỏ), hãy sử dụng quy tắc định dạng số mũ để hiển thị chúng gọn gàng hơn.

Quy tắc là: số * E + n, trong đó E (số mũ) nhân số đứng trước với 10 thành lũy thừa thứ n.

Hãy xem một ví dụ:

Qui tắc	Trước	Sau
0.00E+00	23976986	2,40E + 07

Quy tắc có điều kiện về định dạng số tùy chỉnh của Google Trang tính

Việc thêm điều kiện vào bên trong dấu ngoặc vuông sẽ thay thế các quy tắc dương, âm và 0 mặc định bằng các biểu thức điều kiện.

Ví dụ:

Qui tắc	Trước	Sau
[<100]"Small" ; [>500]"Large" ; "Medium"	50	Nhỏ
[<100]"Small" ; [>500]"Large" ; "Medium"	300	Trung bình
[<100]"Small" ; [>500]"Large" ; "Medium"	800	Lớn

Lưu ý: Nếu bạn thêm dấu phân cách hàng nghìn nhưng không chỉ định định dạng sau dấu phẩy (ví dụ: 0,) thì hàng trăm sẽ bị cắt ra khỏi số. Kết hợp chữ này với chữ “k” hoặc “K” để biểu thị hàng nghìn và bạn có một cách hay để hiển thị các số viết tắt. Để đạt được điều này với hàng triệu, bạn cần chỉ định hai dấu phẩy.

Lưu ý:

• Điều kiện chỉ có thể được chỉ định trong hai quy tắc đầu tiên
• Quy tắc thứ ba được sử dụng làm định dạng cho mọi thứ khác không thỏa mãn hai điều kiện đầu tiên
• Quy tắc thứ tư luôn được sử dụng cho văn bản, vì vậy không thể được sử dụng để định dạng có điều kiện

Màu sắc trong Định dạng số tùy chỉnh của Google Trang tính

Bạn có thể thêm màu sắc vào quy tắc của bạn bằng dấu ngoặc vuông [].

Có 8 màu được đặt tên mà bạn có thể sử dụng:

[black], [white], [red], [green], [blue], [yellow], [magenta], [cyan]

Để có nhiều màu hơn, hãy sử dụng mã màu gồm 2 chữ số được viết:

[Color1], [Color2], [Color3], ..., [Color56]

Qui tắc	Trước	Sau
0;[red](0)	-100	(100)

Dưới dây là một số mã màu sắc mà bạn có thể sử dụng trong Google Sheets và cả Excel

Với bảng màu này, bạn có thể kiến trang tính của mình hấp dẫn hơn.

Bây giờ, chúng ta hãy tìm hiểu một số ví dụ vầ định dạng số tùy chỉnh trong Google Sheets.

Ví dụ về định dạng số tùy chỉnh của Google Sheets

Định dạng Số Điện thoại 

Chuyển bất kỳ số 11 chữ số nào thành số điện thoại được định dạng với quy tắc số 0 và dấu gạch ngang:

Qui tắc	Trước	Sau
0 000-000-0000	18004567891	1 800-456-7891

Định dạng ngày tháng

Sử dụng quy tắc điều kiện để đa dạng hóa các từ. Hãy nhớ rằng, đây vẫn là những con số nên bạn vẫn có thể tính toán với chúng. Phần định dạng (“day” hoặc “days”) chỉ được thêm vào như một lớp trên cùng.

Qui định	Trước	Sau
[=1]0" day"; 0" days"	1	1 day
[=1]0" day"; 0" days"	2	2 days
[=1]0" day"; 0" days"	100	100 days

Định dạng có điều kiện

Sử dụng các điều kiện để phân loại số trực tiếp:

Quy tắc	Trước	Sau
[<250]"Small"* 0 ; [>750]"Large"* 0 ; "Medium"* 0	70	Small 70
[<250]"Small"* 0 ; [>750]"Large"* 0 ; "Medium"* 0	656	Medium 656
[<250]"Small"* 0 ; [>750]"Large"* 0 ; "Medium"* 0	923	Large 923

Lưu ý: đây vẫn là những con số thực, vì vậy bạn có thể thực hiện các phép tính với chúng. Ngoài ra, “Small”, “Medium” và “Large” chỉ tồn tại trong lớp định dạng và không thể được truy cập trong công thức. Ví dụ: bạn không thể sử dụng COUNTIF để đếm tất cả các giá trị có "Large". Để làm điều đó, bạn cần thực sự thay đổi giá trị để từ "Large" có trong ô hoặc thêm một cột trợ giúp.

Phần “*” của quy tắc thêm khoảng cách giữa từ và số để nó lấp đầy toàn bộ chiều rộng của ô.

Định dạng có điều kiện + Màu sắc

Thêm thang màu vào ví dụ có điều kiện:

Qui định	Trước	Sau
[color44][<250]"Small"* 0;[color46][>750]"Large"* 0;[color45]"Medium"* 0	70	Small 70
[color44][<250]"Small"* 0;[color46][>750]"Large"* 0;[color45]"Medium"* 0	656	Medium 656
[color44][<250]"Small"* 0;[color46][>750]"Large"* 0;[color45]"Medium"* 0	923	Small 70

Định dạng nhiệt độ 

Kết hợp các điều kiện với biểu tượng cảm xúc để biến các số thành thang biểu tượng cảm xúc, như ví dụ về nhiệt độ sau:

Cách tạo bảng và định dạng trong Google Sheets

Bây giờ, chúng ta cùng thử sức trên một ví dụ phức tạp hơn một chút. Hãy bắt đầu với một bảng tính đơn giản, hoàn toàn không có bất kỳ định dạng nào:

Mục tiêu của phần này là bạn phải thực hiện định dạng lại bảng dữ liệu trên, để bảng dữ liệu của bạn hiển thị đẹp mắt như hình bên dưới.

1. Định dạng bảng

 Hàng tiêu đề

• Tô đậm, căn giữa và chọn "wrap the text" để tất cả hiển thị tất cả nội dung trong ô.

Căn chỉnh các cột cho đẹp mắt

• Tiêu đề cột ở giữa, số ID hoặc các mục nhập tiêu chuẩn hóa khác.
• Căn trái văn bản .
• Căn phải các số (theo mặc định). Thực sự, ngoại lệ duy nhất cho quy tắc này là đối với các số không thực sự là số, tức là chúng không được so sánh với nhau hoặc được sử dụng trong bất kỳ loại tính toán nào. Ví dụ: các số ID ở trên có thể được căn giữa.
• Căn chỉnh đúng ngày (theo mặc định). Tuy nhiên, nếu bạn đang làm việc chỉ với một năm, như trong ví dụ trên, bạn có thể bỏ qua việc căn giữa, chỉ cần nhất quán

2. Định dạng dữ liệu

• Chọn các tùy chọn định dạng thích hợp cho dữ liệu trong bảng của bạn.
• Thêm dấu phân cách nghìn cho các số lớn trên một nghìn. Thêm dấu hiệu tiền tệ vào số tài chính để thêm ngữ cảnh.
• Chọn một số chữ số thập phân thích hợp. Ví dụ: 2 chữ số thập phân nếu bạn cần mức độ chi tiết đó để hiển thị xu trên đồng đô la, nhưng hãy xóa các chữ số thập phân nếu chúng không cần thiết cho các số lớn:

3. Định dạng Google Sheets dưới dạng đơn vị tiền tệ
 

Tới bước này, bảng của bạn đã tương đối đẹp mắt. Tuy nhiên, đối với các bảng là một phần của báo cáo tổng quan (dashboard) hoặc đang được hiển thị cho khách hàng, bạn có thể thêm màu sắc để phù hợp với thương hiệu của mình hoặc mang lại bối cảnh bổ sung cho các con số của bạn.

4. Cách thêm màu sắc tùy chỉnh trong Google Sheets

Chắc chắn bạn có thể thực hiện việc này theo cách thủ công, nhưng cách này dễ dàng và nhanh chóng hơn khi thực hiện với công cụ Alternating Colors trong tab Format.

Chỉ cần đánh dấu toàn bộ bảng của bạn và sau đó mở thanh bên tùy chọn Alternating Colors. Chọn bảng màu bạn muốn và thậm chí chọn màu tùy chỉnh nếu cần:

5. Cách tạo bảng trong Google Trang tính với màu sắc và mũi tên chỉ báo

Ẩn trong menu Định dạng số tùy chỉnh là tùy chọn định dạng có điều kiện để đặt các định dạng khác nhau cho các số lớn hơn 0, bằng 0 hoặc nhỏ hơn 0.

Đây là một công cụ tuyệt vời để áp dụng cho các bảng trong Trang tính của bạn, ví dụ, khi dữ liệu của bạn có sự thay đổi. Bằng cách thay đổi màu văn bản của ô trong bảng khi dữ liệu thay đổi, bạn có thể thu hút sự chú ý của người dùng.

Cách thêm định dạng tùy chỉnh này

Hãy sao chép ba công thức CHAR sau (bạn có thể xóa nó sau này nếu cần)

Công thức Google Sheets Char

=char(A1)

=char(A2)

=char(A3)

Bây giờ, hãy sao chép và dán chúng dưới dạng giá trị trong Trang tính của bạn để chúng trông giống như cột C và không còn là công thức nữa.

(Bạn sao chép dưới dạng các giá trị bằng cách sao chép, sau đó nhấp chuột phải vào một ô và chọn Paste special > Paste values only… )

Bạn sẽ cần sao chép các ô này vào khay nhớ tạm để có thể dán chúng vào công cụ định dạng số tùy chỉnh.

Đánh dấu cột % và chuyển đến menu định dạng số tùy chỉnh.

Thay đổi 0,00% trong hộp nhập Định dạng số tùy chỉnh thành:

[color50]0% ▲;[color3]-0% ▼;[blue]0% ▬
 

như thể hiện trong hình ảnh này:

Những gì bạn đang làm là chỉ định định dạng số cho số dương trước tiên, sau đó là số âm và sau đó là giá trị 0, mỗi giá trị được phân tách bằng dấu chấm phẩy. Sao chép các ký hiệu từ bước 1 (bạn sẽ phải thực hiện việc này riêng cho từng ký hiệu). Sử dụng dấu ngoặc vuông để chỉ định màu bạn muốn, ví dụ [color50] cho màu xanh lục.

Như vậy, bản của bạn sẽ được định dạng thành công như hình bên dưới:

Hy vọng, với bài viết này  bạn đã biết cách sử dụng tính năng định dạng số tùy chỉnh trong Google Sheets để tùy biến bảng tính của mình chuyên nghiệp hơn. Bên cạnh đó, để không bỏ lỡ những mẹo và thủ thuật tin học văn phòng hữu ích khác, hãy tham gia Gitiho ngay hôm nay.