Cách sử dụng Conditional Formatting trong Excel để xử lý dữ liệu lớn

0
255

Việc phải làm việc với một khối dữ liệu khổng lồ sẽ rất khó cho việc phân tích và sử dụng dữ liệu hiệu quả. Để có thể phân tích hoặc tạo ra những báo cáo thuyết phục bạn cần một hệ thống dữ liệu trực quan và đáng tin cậy hơn. Đây là lúc chức năng Conditional Formatting phát huy tác dụng.

Nếu bạn đang theo học Excel tin học văn phòng, Conditional Formatting là một chức năng không thể bỏ qua. Nó có thể giúp bạn định dạng kiểu dáng, màu sắc,… của các ô để phân loại và sử dụng khối dữ liệu lớn dễ dàng hơn.

Để truy cập Conditional Formatting, chọn tab Home > Styles group (Home > Format đối với Macbook). Sau đây là một số tùy chọn phổ biến.

Thanh dữ liệu (Data Bars)

Cách đơn giản nhất để bắt đầu với Conditional Formatting là sử dụng thanh dữ liệu (data bars).  Bạn chỉ cần chọn cột cần định dạng, chọn Conditional Formatting > Data Bars > chọn mẫu thích hợp.

thanh dữ liệu trong excel

Excel cho phép bạn tùy chỉnh thanh dữ liệu bằng cách chọn More Rules ở phía dưới thanh menu. Ví dụ: nếu dữ liệu có những giá trị quá lớn hoặc quá nhỏ làm ảnh hưởng đến biểu thị của thanh dữ liệu, bạn có thể điều chỉnh chúng bằng cách tùy chỉnh giá trị Min và Max.

Ngoài ra, có nhiều tùy chọn khác có thể tối ưu cho thanh dữ liệu như chỉnh màu gradient, đổi màu, định dạng kiểu số hiển thị, tùy chỉnh khung viền, hướng thanh,…

hộp thoại new formatting rule

Định dạng biểu tượng (Icon Sets)

Một chức năng phổ biến khác của Conditional Formatting là đặt biểu tượng (icon) cho dữ liệu. Bằng cách chọn Formatting > Icon Sets. Các biểu tượng tiêu chuẩn mà Excel cung cấp thường khá nhàm chán.

Ví dụ: bạn có thể dùng biểu tượng đèn giao thông để xác định độ lớn của dữ liệu. Excel sẽ tự động chia dữ liệu thành 3 mức ⅓  top đầu, ⅓ top giữa, và ⅓ top cuối. Bạn có thể tự tùy chỉnh hạn mức giá trị trong More Rules. Trong ví dụ này, chúng ta sẽ đặt các mức 1-10, 11-20 và 21+.

  • Bước 1: đổi biểu tượng Icon Style thành biểu tượng đèn giao thông
  • Bước 2: chọn Reverse Icon Order vì trong trường hợp này dữ liệu nhỏ sẽ được hiển thị màu xanh
  • Bước 3: đổi dạng dữ liệu (Type) thành Number
  • Bước 4: đặt giá trị cho biểu tượng đèn đỏ thành >=21
  • Bước 5: đặt giá trị cho biểu tượng đèn vàng thành >=11

hộp thoại edit formatting rule

Chúng ta sẽ được kết quả như sau:

bảng ranking data

Nếu bạn sử dụng table formatting, bạn có thể sắp xếp (sort) dữ liệu theo Conditional Formatting . Click vào biểu tượng mũi tên trên đầu cột và chọn Sort by Color > Custom Sort (đối với Macbook: Sort > By Color > Cell Icon > Custom Sort).

hộp thoại sort by color

Bạn sẽ không bị giới hạn trong việc kết hợp các bộ biểu tượng trong Excel. Ví dụ, nếu muốn đặt biểu tượng mũi tên màu xanh cho giá trị lớn hơn 1%, mũi tên đỏ cho giá trị nhỏ hơn -1%, nhưng lại không muốn mũi tên màu vàng hiển thị cho giá trị -1% và 1%. Bạn có thể chọn tùy chọn No Cell Icon cho giá trị của mũi tên vàng như hình bên dưới.

Menu Icon Style sẽ hiển thị Custom thay vì hiện 3 mũi tên như trường hợp bên trên. Trên lý thuyết, bạn có thể sử dụng bất kì kết hợp biểu tượng nào bạn muốn, nhưng cũng đừng nên lạm dụng vì hãy nhớ, mục tiêu ban đầu của chúng ta là làm cho dữ liệu trực quan và dễ nhìn hơn.

Menu Icon Style

Tô đậm các ô theo điều kiện (Highlight Cell Rules)

Excel cung cấp rất nhiều tùy chọn để bạn định dạng dữ liệu trong ô. Chọn Conditional Formatting > Highlight Cell Rules, sẽ có các tùy chỉnh sau:

  • Giá trị lớn hơn (Greater Than)
  • Giá trị nhỏ hơn (Less Than)
  • Giá trị nằm trong khoản (Between)
  • Giá trị bằng (Equal To)

Các tùy chọn giá trị lớn/nhỏ hơn sẽ rất có ích cho việc tạo các báo cáo, như báo cáo doanh thu qua từng tháng, báo cáo lượt truy cập website qua các năm,… Bạn có thể hiển thị các trị số xấu bằng màu đỏ và ngược lại. Từ đó sẽ giúp báo cáo dễ nhìn hơn.

  • Văn bản chứa nội dung(Text That Contains)

Tùy chọn này sẽ có ích khi bạn cần tô đậm một từ khóa cụ thể trong báo cáo, ví dụ tô đậm một mã đơn hàng cụ thể, tô đậm các trang bị lỗi “404” trên website,…

  • Ngày cụ thể (A Date Occurring)
  • Giá trị trùng lắp (Duplicate Values)

Tùy chọn Duplicate Values có thể giúp bạn loại bỏ hoặc phân nhóm những dữ liệu bị trùng lắp như tên khách hàng, mã sản phẩm,…

Top/Bottom Rules

Excel cung cấp 6 tùy chọn mặc định:

  • Top 10
  • Top 10%
  • Bottom 10
  • Bottom 10%
  • Trên trung bình (Above Average)
  • Dưới trung bình (Below Average)

Bạn có thể tự đặt những tùy chọn khác trong mục More Rules.

Đặt tỉ lệ màu (Color Scales)

Chức năng này sử dụng tương tự như thanh dữ liệu (Data bars). Nó có thể giúp xác định sự phân bổ của dữ liệu, như tỉ lệ ROI theo thời gian. Bạn cũng có thể đặt màu gradient theo các giá trị minimum, midpoint và maximum. Để sử dụng chọn ô, hoặc bảng bạn muốn định dạng, chọn Home > Conditional Formatting > Color Scales và kết hợp sử dụng công thức.

tab conditional formatting

Nếu bạn đã quen với Conditional Formatting , hãy kết hợp nó với các công thức để tiết kiệm thời gian. Nó cũng rất cần thiết khi bạn muốn định dạng dữ liệu của một cột dựa trên giá trị của cột khác.

Ví dụ, nếu bạn muốn tô vàng các ô trong cột landing page tạo ra được tối thiểu 5 triệu doanh thu trong cột doanh thu, bạn sẽ phải kết hợp Conditional Formatting với công thức. Điều này thực ra không hề phức tạp. Như trường hợp trên, giả sử cột doanh thu là cột E (bắt đầu từ hàng 4), công thức Conditional Formatting sẽ là =E4>=5000. Bạn có thể đặt nhiều điều kiện nếu muốn, ví dụ trong báo cáo có các cột chứa các giá trị:

  • Cột B: Lượt truy cập
  • Cột C: Lợi nhuận
  • Cột D: Bounce rate

Bạn muốn tô đậm landing page tạo ra được ít nhất 100 lượt truy cập, tạo ra 5 triệu tiền lợi nhuận và có tỉ lệ bound rate thấp hơn 30%, các bước thực hiện cụ thể sẽ như sau:

  • Bước 1: chọn ô/vùng trong cột landing page cần định dạng
  • Bước 2: chọn Conditional Formatting > New Rule
  • Bước 3: dưới mục Select a Rule Type, chọn Use a formula (đối với Macbook, chọ 
  • Bước 4: giả sử dữ liệu bắt đầu từ hàng thứ 4, nhập công thức =AND(B4>=100, C4>=5000, D4<30%)
  • Bước 5: chọn định dạng bạn muốn trong mục Format

Điều chỉnh Conditional Formatting

Nếu cần điều chỉnh Conditional Formatting, bạn chỉ cần chọn Conditional Formatting > Manage Rules, và double click vào lệnh bạn muốn điều chỉnh hoặc chọn Edit Rule. Bạn cũng có thể điều chỉnh thứ tự ưu tiên của các điều kiện trong Rules Manager.

Conditional Formatting là một công cụ vô cùng mạnh mẽ để xử lý dữ liệu, đặc biệt là trong Excel văn phòng. Việc nắm bắt được kỹ năng tuyệt vời này sẽ giúp bạn nâng cao tối đa hiệu quả công việc. Tham khảo khóa học Excel Và Ứng Dụng Trong Thực Tiễn Microsoft Excel 2013 Nâng Cao từ VietnamWorks Learning, được thiết kế đặc biệt từ những tình huống thực tế của doanh nghiệp, bám sát với nhu cầu, sẽ giúp bạn bứt phá mạnh mẽ.

TRẢ LỜI

Please enter your comment!
Please enter your name here