Google Sheets cung cấp nhiều tính năng nâng cao giúp trích xuất ý nghĩa từ một tập dữ liệu. Một trong những tính năng này, đơn giản và mạnh mẽ cùng một lúc, là định dạng điều kiện. Nó cho phép bạn biến đổi các dòng và cột văn bản màu đen trên nền trắng thành một tập dữ liệu màu sắc và hấp dẫn mắt. Điều này giúp tiết kiệm thời gian và làm cho dữ liệu trở nên dễ đọc và có ý nghĩa hơn.
Trong bài viết này, chúng ta sẽ giải thích về định dạng điều kiện trong Google Sheets và cách bạn có thể tận dụng nó. Nếu bạn thích xem hơn là đọc, hãy xem video hướng dẫn của chúng tôi.
Định dạng điều kiện trong Google Sheets là gì?
Định dạng điều kiện trong Google Sheets là một tính năng cho phép thay đổi tự động thuộc tính phông chữ của một ô cụ thể, một dòng, một cột hoặc thậm chí màu nền của ô, dựa trên các quy tắc mà bạn đặt ra. Nói cách khác, công cụ này sử dụng sức mạnh của trực quan hóa để làm nổi bật dữ liệu của bạn. Bằng cách tô màu ô, bạn có thể làm nổi bật các giá trị cụ thể, làm cho chúng dễ nhìn và dễ hiểu trong các bảng phức tạp. Với định dạng điều kiện, dữ liệu trở nên hấp dẫn mắt hơn và dễ đọc hơn đối với con người.
Khi nào nên áp dụng định dạng điều kiện trong Google Sheets?
Định dạng điều kiện có thể được áp dụng trong gần như mọi quy trình làm việc để trực quan hóa thông tin: từ các mẫu dữ liệu, vùng có vấn đề, tin tức tốt cho đến dữ liệu sai hoặc không chính xác. Có thể nói rằng không có công cụ nào khác trong Google Sheets có thể được sử dụng trong nhiều ứng dụng như vậy.
- Người quản lý bán hàng cần nhanh chóng trích xuất thông tin từ một lượng lớn dữ liệu bán hàng => họ sử dụng định dạng điều kiện.
- Kế toán muốn làm nổi bật các giá trị âm bằng màu đỏ trong các tính toán lợi nhuận / lỗ => không có vấn đề, họ áp dụng định dạng điều kiện.
- Các nhà quản lý dự án cố gắng hiểu việc sử dụng tài nguyên của họ => một lần nữa, định dạng điều kiện có thể giúp đỡ.
Nó cũng phục vụ là một cách hiệu quả để theo dõi mục tiêu, đưa ra hướng dẫn trực quan về tiến trình so với các chỉ số cụ thể. Và đó chưa nói đến các báo cáo hàng ngày và hàng tuần, mà rất phổ biến trong các tổ chức lớn. Nếu bạn bận rộn và không định dạng điều kiện, họ thậm chí có thể trở thành một công việc cực kỳ khó khăn và không có ý nghĩa khi nhìn thoáng qua.
Do đó, định dạng điều kiện có thể hữu ích bất cứ khi nào bạn muốn mang danh sách Google Sheets của mình trở nên sống động.
Cách sử dụng định dạng điều kiện trong Google Sheets
Trước khi bước vào chi tiết, quan trọng là hiểu rằng mọi công việc với định dạng điều kiện theo cùng một mô hình, bao gồm ba yếu tố chính:
- Phạm vi: Đây là một ô hoặc nhóm ô mà quy tắc sẽ được áp dụng.
- Trình kích hoạt: Nó xác định điều kiện để quy tắc được áp dụng. Ví dụ, trình kích hoạt có thể là “Ít hơn”.
- Kiểu: Khi quy tắc được áp dụng, nó thay đổi kiểu của ô theo lựa chọn của bạn.
Các bước để thực hiện là:
- Nhấp chuột vào “Định dạng” -> “Định dạng điều kiện”.
- Chọn một phạm vi.
- Định nghĩa các quy tắc định dạng điều kiện:
- Chọn kiểu của bạn trong menu thả xuống “Kiểu định dạng”.
- Chọn trình kích hoạt của bạn từ danh sách thả xuống “Định dạng ô nếu…”.
- Nhấp vào “Hoàn tất” mỗi khi bạn muốn áp dụng quy tắc của mình :)
Hãy xem chi tiết từng bước. Chúng ta sẽ sử dụng một tập dữ liệu đã được nhập từ Airtable vào Google Sheets với sự trợ giúp của Coupler.io.
Coupler.io là một công cụ tích hợp dữ liệu cho phép tự động xuất dữ liệu từ các ứng dụng khác nhau như Pipedrive, Jira, HubSpot và nhiều ứng dụng khác vào Google Sheets. Bên cạnh đó, bạn có thể sử dụng tích hợp Google Sheets của Coupler.io để kết hợp dữ liệu từ nhiều trang thành một trang duy nhất mà không cần sử dụng các công thức.
Tìm hiểu các tích hợp Google Sheets có sẵn.
Bước 1. Nhấp vào “Định dạng” -> “Định dạng điều kiện”
Bước đầu tiên rất đơn giản và hợp lý.
Bước 2. Chọn một phạm vi
Với bước thứ hai, bạn có hai tùy chọn:
-
Tùy chọn 1: Chọn một phạm vi (ô, cột hoặc hàng) và sau đó nhấp chuột vào “Định dạng” -> “Định dạng điều kiện”. Điều này sẽ hiển thị thanh công cụ định dạng điều kiện trong phần bên phải của màn hình của bạn. Nếu tập dữ liệu của bạn nhỏ, đây là cách tốt nhất.
-
Tùy chọn 2: Nhấp chuột vào “Định dạng” -> “Định dạng điều kiện” sau đó nhập phạm vi của bạn trong tab “Áp dụng cho phạm vi”. Điều này rất tiện lợi khi bạn làm việc với số lượng lớn dữ liệu và có nguy cơ sai lầm khi chọn phạm vi.
Nếu bạn định dẫn tới một phạm vi ô, bạn cần nhập ô đầu tiên và ô cuối cùng của phạm vi bằng dấu hai chấm, ví dụ: G2:G21 trong ví dụ trên. Nếu bạn muốn làm nổi bật một ô duy nhất, hãy nhập nhãn của ô thay vì phạm vi (ví dụ: G2).
Có thể bạn cũng sẽ làm việc với nhiều phạm vi. Cách đơn giản nhất để làm điều này là nhấp vào biểu tượng bên phải của ô phạm vi và chọn “Thêm phạm vi khác”.
Ví dụ, tôi đã chọn phạm vi H2:H21 để làm nổi bật.
Điều này cho phép bạn làm nổi bật càng nhiều phạm vi như bạn muốn.
Bước 3. Định nghĩa các quy tắc định dạng điều kiện trong Google Sheets
Kiểu định dạng
Việc tiếp theo là chọn kiểu định dạng. Dường như việc bỏ qua “Định dạng ô nếu…” và bắt đầu bằng kiểu đầu tiên là không đúng, nhưng khi bạn đặt điều kiện, ô sẽ thay đổi kiểu.
Để chọn kiểu, nhấp vào “Mặc định” trong danh sách thả xuống “Kiểu định dạng” và bạn sẽ thấy các tùy chọn kiểu mặc định.
Nếu không có tùy chọn nào phù hợp với bạn, hãy tạo kiểu tùy chỉnh. Bạn có thể chọn phông chữ đậm, nghiêng, gạch ngang hoặc chọn màu chữ và màu nền bằng cách nhấp vào nút chọn màu.
Định dạng ô nếu… – chọn trình kích hoạt của bạn trong danh sách thả xuống
Hãy xem cách phần điều kiện của định dạng hoạt động cho từng trường hợp. “Định dạng ô nếu…” mặc định là “Không trống”, nhưng nếu bạn nhấp vào nó, bạn sẽ thấy hàng loạt các quy tắc được áp dụng trong định dạng điều kiện trong Google Sheets.
Ví dụ, để ví dụ đầu tiên của chúng ta, hãy chọn “Lớn hơn” trong tùy chọn danh sách thả xuống và áp dụng quy tắc “Lớn hơn 10” cho phạm vi G2:G21, cột giá sản phẩm.
Cũng hãy sử dụng màu sắc để làm cho số trở nên rõ ràng và có ý nghĩa hơn. Để làm điều này, chúng tôi sẽ áp dụng hai quy tắc riêng biệt:
- Làm nổi bật tất cả các giá trị lớn hơn 10 màu xanh lá cây.
- Làm nổi bật tất cả các giá trị nhỏ hơn hoặc bằng 10 màu đỏ.
Và đúng vậy, đừng quên nhấp vào “Hoàn tất” mỗi khi bạn muốn áp dụng các quy tắc của mình :)
Thang màu trong định dạng điều kiện trong Google Sheets
Một điều tốt khác về việc tô màu các ô của bạn là bạn có thể sử dụng một thang màu để làm cho dữ liệu của bạn trở nên nguyên và hấp dẫn hơn. Theo mặc định, Google Sheets sẽ sử dụng thang màu giữa giá trị tối thiểu và tối đa, nhưng bạn có thể tùy chỉnh giao diện của mình bằng cách định nghĩa giá trị tối đa và tối thiểu tùy chỉnh. Ví dụ, bạn có thể muốn có điều này.
Định dạng điều kiện tùy chỉnh trong Google Sheets sử dụng “Công thức tùy chỉnh là”
“Công thức tùy chỉnh là” là một trong những tùy chọn hữu ích nhất được cung cấp bởi “Định dạng ô nếu…” Nó cho phép bạn linh hoạt hơn với dữ liệu của bạn và mở rộng phạm vi các thao tác có sẵn.
Để truy cập vào nó, bạn cần chọn “Là” trong danh sách thả xuống “Quy tắc định dạng rồi sau đó cuộn xuống và chọn “Công thức tùy chỉnh là…”
Hãy nhớ rằng tất cả các công thức tùy chỉnh đều bắt đầu bằng dấu bằng (=). Đồng thời, nếu bạn đã quen dùng các hàm AND và OR trong các công thức Google Sheets thông thường của bạn, tin tốt là chúng hoạt động trong định dạng điều kiện.
Các ví dụ về quy tắc định dạng điều kiện khác trong Google Sheets
Hãy nhìn vào các tình huống phức tạp hơn và thường được sử dụng nhiều nhất trong cuộc sống thực.
Quy tắc định dạng điều kiện trong Google Sheets – Văn bản chứa
Bạn thường muốn tìm kiếm hoặc làm nổi bật một từ hoặc một số từ trong các bộ dữ liệu của mình. Điều này dễ dàng thực hiện với một quy tắc dựa trên văn bản – một ô sẽ thay đổi dựa trên văn bản bạn nhập vào. Các tùy chọn cho quy tắc dựa trên văn bản là:
- Văn bản chứa
- Văn bản không chứa
- Văn bản bắt đầu bằng
- Văn bản kết thúc bằng
- Văn bản chính xác
Ví dụ, giả sử bạn muốn xem loại sandwich nào được cung cấp trong menu. Trong trường hợp này, bạn có thể chọn toàn bộ bảng (như trong trường hợp của tôi) của cột sản phẩm và chọn “Văn bản chứa” và nhập “sandwich”. Tất cả các loại sandwich sẽ được làm nổi bật.
Định dạng điều kiện trong Google Sheets dựa trên giá trị ô khác
Đôi khi, bạn cũng muốn áp dụng định dạng điều kiện dựa trên một ô khác. Giả sử bạn muốn biết khách hàng hài lòng với sản phẩm của mình. Đây là nơi tùy chọn công thức tùy chỉnh xuất hiện. Công thức sẽ là:
=L2=TRUENhư vậy, mỗi khi định dạng điều kiện kiểm tra một ô trong phạm vi C2:C21, nó sẽ kiểm tra điều kiện =L2=TRUE, trong đó L là cột chứa các giá trị thể hiện sự hài lòng của khách hàng.
Cùng một lôgic cũng có thể được sử dụng nếu bạn cần biết ai là người mua các sản phẩm đắt đỏ nhất, trong trường hợp của chúng tôi là 13 $. Công thức cho điều này là:
=G2>12Nếu bạn muốn làm lựa chọn của bạn chính xác hơn, bạn có thể sử dụng hàm AND hoặc OR trong công thức. Ví dụ, công thức sau sẽ làm nổi bật khách hàng mua các sản phẩm đắt đỏ nhất và hoàn toàn hài lòng.
=AND(L2=TRUE, G2>12)Làm nổi bật các dòng toàn bộ trong Google Sheets
Nhưng nếu bạn muốn làm nổi bật toàn bộ một dòng, dựa trên một giá trị của ô? Giả sử bạn muốn làm nổi bật toàn bộ dòng dựa trên giá trị ô. Hãy nói rằng bạn muốn xem ngược lại cái gì khách hàng khó tính nhất, Federico Coria, mua. Một lần nữa, hãy chọn “Công thức tùy chỉnh là …” trong danh sách thả xuống và nhập:
=$C2="Federico Coria"Tất cả các dòng chứa Federico Coria trong cột sẽ được làm nổi bật. Hãy nhớ chọn toàn bộ dữ liệu của bạn, trong trường hợp này A2:L21.
Đôi khi bạn muốn làm nổi bật các dòng không bao gồm giá trị nhất định. Thay = bằng <> . Trong trường hợp của chúng tôi, công thức mới trông như thế này:
=$C2<>"Federico Coria"Làm nổi bật các dòng xen kẽ bằng định dạng điều kiện trong Google Sheets
Cái gọi là các dòng xen kẽ thường được sử dụng khi các báo cáo cần được in. Điều này giúp cải thiện tính khả dụng và mang lại cái nhìn chuyên nghiệp hơn.
Để làm nổi bật các ô xen kẽ, hãy làm theo thuật toán đã biết. Đừng quên chọn toàn bộ dữ liệu của bạn, trong trường hợp này là A2:L21. Và trong danh sách thả xuống “Định dạng ô nếu …”, chọn tùy chọn “Công thức tùy chỉnh là”. Công thức bạn cần nhập là:
=MOD(ROW(),2)=1Điều này sẽ làm nổi bật các hàng xen kẽ trong phạm vi đã chọn.
Cách hoạt động: Hàm ROW() trả về số dòng của một ô cụ thể. Hàm MOD() sau đó chia nó cho 2 và trả về phần dư. Khi phần dư là 1 (nếu có một số lẻ dòng), điều kiện trở thành TRUE và định dạng điều kiện được áp dụng.
Nếu bạn muốn làm nổi bật một số dòng chẵn, hãy sử dụng:
=MOD(ROW(),2)=0Định dạng điều kiện cho các bản sao trong Google Sheets
Đôi khi, bạn có thể muốn xem các bản sao trong Google Sheets của bạn – ví dụ: để xóa chúng. Với tính năng định dạng điều kiện, bạn thực sự không thể xóa các bản sao, nhưng bạn có thể xác định và làm nổi bật chúng. Để làm điều này, bạn cần sử dụng “Công thức tùy chỉnh là” như là một quy tắc định dạng và áp dụng một công thức COUNTIF cho một phạm vi dữ liệu được chọn. Ví dụ, để xác định các bản sao trong cột G1:G, công thức sau sẽ hoạt động:
=COUNTIF($G$1:$G1,G1)>1Định dạng điều kiện cho các ngày trong Google Sheets
Dưới đây là một ví dụ về định dạng điều kiện dựa trên ngày:
=DATEVALUE(K2)<DATEVALUE("2022-01-01")Chúng tôi đã làm nổi bật các ô chứa các giá trị trước một ngày cụ thể được chỉ định.
Định dạng điều kiện dựa trên thời gian trong Google Sheets
Bạn cũng có thể muốn định dạng điều kiện bảng tính của mình dựa trên thời gian. Điều này dễ thực hiện nếu bạn có thời gian trong cột riêng, như trong cột J của tập dữ liệu mẫu. Sau đó, bạn có thể chọn bất kỳ điều kiện nào áp dụng cho số. Ví dụ, nếu bạn muốn làm nổi bật các ô giữa 15:00:00 và 20:00:00, bạn cần chọn “Nằm trong” trong danh sách thả xuống và nhập các giá trị của bạn.
Nếu thời gian của bạn không nằm trong một cột riêng, mà thay vào đó được kết hợp với ngày, việc này sẽ không dễ dàng hơn. Trong trường hợp này, bạn cần chọn “Công thức tùy chỉnh là” và nhập công thức sau:
=AND(K2-INT(K2)>=TIME(15,0,0),K2-INT(K2)<=TIME(20,0,0))Định dạng điều kiện với nhiều quy tắc trong Google Sheets
Như đã đề cập, bạn có thể đặt nhiều quy tắc định dạng điều kiện trong Google Sheets. Hãy nhớ sử dụng nút “Thêm quy tắc khác” dưới quy tắc định dạng điều kiện của bạn. Dưới đây là giao diện người dùng của nó.
Cách làm nổi bật giá trị nhỏ nhất hoặc lớn nhất bằng định dạng điều kiện
Đôi khi, có thể hữu ích để biết cách làm nổi bật các giá trị cụ thể trong một bảng tính. Hãy xem cách bạn có thể xác định các giá trị nhỏ nhất và lớn nhất. Bạn cần tuân theo cùng một thuật toán đã biết và trong danh sách thả xuống “Định dạng ô nếu …”, chọn “Công thức tùy chỉnh là”. Công thức cho giá trị lớn nhất là:
=G:G=max(G:G)Trong đó, G đại diện cho cột bạn muốn tìm giá trị lớn nhất.
Để tìm giá trị nhỏ nhất, logic tương tự. Công thức để nhập sẽ là:
=G:G=min(G:G)Định dạng điều kiện cho checkbox trong Google Sheets
Checkbox và định dạng điều kiện là một tính năng khác thú vị của Google Sheets. Nếu checkbox được chọn, toàn bộ dòng sẽ thay đổi màu hoặc kiểu phông chữ. Hãy xem cách áp dụng nó trong thực tế.
Đầu tiên, chèn một checkbox từ tab “Chèn”. Đối với điều này, chúng tôi đã tạo một cột riêng, H.
Sau đó, hãy chọn phạm vi mà định dạng điều kiện sẽ được áp dụng. Trong trường hợp của chúng tôi, đó là toàn bộ dữ liệu (A2:L21). Và để có cái nhìn rõ ràng hơn, chúng tôi chọn một màu khác và kiểu chữ gạch ngang khi checkbox được chọn. Điều cuối cùng là chọn “Công thức tùy chỉnh là” và nhập:
=$H2=TRUEBây giờ, mỗi khi bạn đánh dấu checkbox của mình, quy tắc sẽ được áp dụng.
Định dạng điều kiện trong Google Sheets giữa các tab khác nhau
Nếu bạn có thể áp dụng định dạng điều kiện cho các tab trên các tab khác nhau, điều đó sẽ thật tuyệt vời. Ví dụ, nếu bạn làm nổi bật một sản phẩm trong một tab bảng, điều này sẽ được áp dụng tự động cho tất cả các tab bảng mà không cần chỉnh sửa thủ công.
Rất tiếc, quy tắc định dạng điều kiện chỉ áp dụng cho tab nó được nhập vào. Vì vậy, không thể nhập chúng cho nhiều tab, chưa kể các bảng tính khác nhau.
Định dạng điều kiện của Google Sheets không hoạt động
Nếu bạn cũng là người hâm mộ của định dạng điều kiện giống như chúng tôi và bạn thường xuyên áp dụng nó hoặc bạn mới chỉ khám phá công cụ hữu ích này, bạn có thể gặp tình huống định dạng điều kiện không hoạt động. Chúng tôi đã xác định bốn lý do chính để điều này xảy ra:
-
Thứ tự của định dạng điều kiện của bạn là quan trọng. Định dạng điều kiện chỉ áp dụng quy tắc đầu tiên (từ trên xuống dưới) trả về TRUE. Mọi kết quả TRUE khác không được tính toán. Bạn có thể thay đổi thứ tự bằng cách kéo hộp bằng ba dấu chấm xuất hiện bên trái.
-
Định dạng điều kiện hoạt động một chút khác biệt so với các công thức chuẩn của Google Sheets. Ví dụ, nếu bạn nhập =G2<10 trong công thức tùy chỉnh của bạn và áp dụng nó cho phạm vi G2:I21, trong ô G2 nó sẽ đánh giá chính xác là G2<10. Nhưng trong các ô tiếp theo, nó sẽ đánh giá là =G3<10, =G4<10, vv, và điều đó có nghĩa là định dạng điều kiện sẽ điều chỉnh công thức cho bạn.
Nếu bạn không muốn điều chỉnh, bạn cần sử dụng ký hiệu $. Nếu bạn sử dụng $G$2, nó sẽ giữ cả hàng và cột không đổi trong phạm vi. G$2 chỉ giữ hàng không đổi và $G2 chỉ giữ cột không đổi. Để làm nổi bật một hàng dựa trên một cột duy nhất, bạn cần sử dụng tùy chọn $G2.
-
Khác với các công thức tiêu chuẩn của Google Sheets, cách đóng ngoặc cuối cùng sẽ không được tự động chèn trong định dạng điều kiện. Bạn cần chú ý đến điều này và tự chèn chúng vào. Đối với các công thức phức tạp, thường hữu ích để thử công thức trong ô trống để đảm bảo cú pháp của bạn đúng.
-
Định dạng điều kiện, như đã đề cập trước đó, không thể áp dụng cho các tab khác trực tiếp trên bảng. Bạn cần sử dụng hàm INDIRECT để tham chiếu đến một tab khác trong bảng tính của bạn.
Cách xóa định dạng điều kiện trong Google Sheets
Trong một số trường hợp, bạn có thể muốn xóa định dạng điều kiện hoàn toàn khỏi Google Sheets của mình. Điều này có thể xảy ra khi bạn nhận dữ liệu từ người khác hoặc muốn bắt đầu làm việc với bảng tính của mình từ đầu.
Để xóa định dạng điều kiện, hãy trước tiên chọn phạm vi các ô có định dạng điều kiện, sau đó chọn “Định dạng” -> “Định dạng điều kiện” và bạn sẽ thấy tất cả các quy tắc bạn đã tạo trong thanh bên. Nhấp vào biểu tượng “thùng rác” của quy tắc bạn muốn xóa. Định dạng điều kiện sẽ được xóa bỏ.
Tóm lại và tiếp tục học về định dạng điều kiện nâng cao trong Google Sheets
Không quan trọng dự án bạn đang làm việc hoặc dữ liệu bạn đang làm việc, bảng tính có thể là công cụ hoàn hảo để chơi với dữ liệu và tìm ra ý nghĩa từ đó. Với định dạng điều kiện trong Google Sheets, bạn có thể biến dữ liệu của mình trở nên hấp dẫn mắt và hiển thị ý nghĩa của nó để tạo ra báo cáo trực quan và dễ sử dụng cho người đọc. Vì vậy, hãy tiếp tục học và áp dụng tính năng tuyệt vời này và bạn có thể tìm thấy hàng chục trường hợp sử dụng cho định dạng điều kiện.
