Bến Hà Trương
Bến Hà Trương
Thảo luận 0 thảo luận
Vỗ tay 0 vỗ tay
Lượt xem 8500 lượt xem

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

Oct 12 2020

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:

      Định dạng số tùy chỉnh trong Google Sheets

        nhập quy tắt định dạng riêng

          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ắcTrướcSau
              0;0;"Zero"0Số 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ắcSố dươngSố âmSố khôngVăn bản
              01-10văn bản
              0;(0)1(1)0văn bản
              [red]01-10văn bản
              0;[red]-01-10văn bản
              0;[red]-0;[blue]0;[green]@1-10vă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ắcTrướcSau
                  0.001,51,50
                  0000072100721

                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ắcTrướcSau
                    ####1515
                    ####15891589
                    #.##1,51,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ắcTrướcSau
                    #,##014951,495
                    #,##0.001234567,891.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ắcTrướcSau
                      0.1010.
                      0.10.110.
                      0.001010.00
                      Hàng nghìn (k hoặc K) hoặc Hàng triệu (m hoặc M)
                        Qui tắcTrướcSau
                        0.0,25002,5
                        0,"k"25003k
                        0.0,"k"25002,5k
                        0.0,,"M"12345671,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 địnhTrướcSau
                        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 địnhTrướcSau
                        *-0100—————— 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 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

                        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:

                        òng đầu tiên KHÔNG có khoảng cách nhưng dòng thứ hai thì có

                        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ắcTrướcSau
                        #01010
                        \#010# 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ắcTrướcSau
                        0;0;0;"Special text value!"Một số tiếp theoGiá trị văn bản đặc biệt!
                        0;0;0;@Một số tiếp theoMộ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ắcTrướcSau
                        # ?/?2,33333333332 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ắcTrướcSau
                        0.00%0,282928,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ắcTrướcSau
                        0.00E+00239769862,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ắcTrướcSau
                        [<100]"Small" ; [>500]"Large" ; "Medium"50Nhỏ
                        [<100]"Small" ; [>500]"Large" ; "Medium"300Trung bình
                        [<100]"Small" ; [>500]"Large" ; "Medium"800Lớ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ắcTrướcSau
                        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

                        Mã màu sử dụng trong Google sheets

                        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.

                        màu sắc trong Định dạng số tùy chỉnh Google sheets

                        màu sắc trong Định dạng số tùy chỉnh Google sheets

                        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ắcTrướcSau
                        0 000-000-0000180045678911 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 địnhTrướcSau
                        [=1]0" day"; 0" days"11 day
                        [=1]0" day"; 0" days"22 days
                        [=1]0" day"; 0" days"100100 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ắcTrướcSau
                        [<250]"Small"* 0 ; [>750]"Large"* 0 ; "Medium"* 070Small 70
                        [<250]"Small"* 0 ; [>750]"Large"* 0 ; "Medium"* 0656Medium 656
                        [<250]"Small"* 0 ; [>750]"Large"* 0 ; "Medium"* 0923Large 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 địnhTrướcSau
                        [color44][<250]"Small"* 0;[color46][>750]"Large"* 0;[color45]"Medium"* 070Small 70
                        [color44][<250]"Small"* 0;[color46][>750]"Large"* 0;[color45]"Medium"* 0656Medium 656
                        [color44][<250]"Small"* 0;[color46][>750]"Large"* 0;[color45]"Medium"* 0923Small 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: 

                        Bảng không có định dạng

                        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:

                          Google Trang tính định dạng các hàng xen kẽ

                          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)

                          Công thức Google Sheets Char

                          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.

                          Cùng tham gia cộng đồng hỏi đáp về chủ đề Tin học văn phòng

                          Thảo luận 0 câu trả lời
                          Lượt xem 8500 lượt xem
                          Vỗ tay vỗ tay

                          0 Bình luận

                          @ 2020 - Bản quyền của Công ty cổ phần công nghệ giáo dục Gitiho Việt Nam
                          Giấy chứng nhận Đăng ký doanh nghiệp số: 0109077145, cấp bởi Sở kế hoạch và đầu tư TP. Hà Nội