Màu biểu đồ thanh Excel dựa trên giá trị

Tương đối dễ áp ​​dụng định dạng có điều kiện trong trang tính Excel. Đó là một tính năng tích hợp sẵn trên tab Trang chủ của dải băng Excel và có nhiều tài nguyên trên web để nhận trợ giúp (xem ví dụ về điều và Chip Pearson nói). Định dạng có điều kiện của biểu đồ là một câu chuyện khác. Mọi người thường hỏi cách định dạng biểu đồ theo điều kiện, nghĩa là cách thay đổi định dạng của các điểm được vẽ trên biểu đồ (điểm đánh dấu, màu tô của thanh, v.v. ) dựa trên giá trị của các điểm. Điều này có thể được thực hiện bằng cách sử dụng VBA để thay đổi các thành phần biểu đồ riêng lẻ (ví dụ: Định dạng có điều kiện của biểu đồ theo giá trị trong VBA), nhưng mã này phải được chạy bất cứ khi nào dữ liệu thay đổi để duy trì định dạng. Kỹ thuật sau hoạt động rất hiệu quả mà không cần dùng đến macro, với một lợi thế bổ sung là bạn không cần phải loay hoay trong VBA

Biểu đồ chưa định dạng

Đây là dữ liệu đơn giản cho ví dụ định dạng biểu đồ có điều kiện của chúng tôi

Dữ liệu tạo một biểu đồ thanh không định dạng đơn giản. .

. . hoặc một biểu đồ đường không định dạng đơn giản

Chúng tôi muốn biểu đồ của mình hiển thị các điểm có màu khác nhau tùy thuộc vào giá trị của điểm. Ngoại trừ một số định dạng tích hợp sẵn đơn giản, định dạng có điều kiện của phạm vi trang tính yêu cầu công thức để xác định ô nào sẽ thực hiện định dạng. Theo cách tương tự, chúng tôi sẽ sử dụng các công thức để xác định định dạng của chuỗi trong biểu đồ. Chúng tôi sẽ thay thế dữ liệu được vẽ ban đầu trong biểu đồ đường và thanh bằng một số chuỗi, mỗi chuỗi cho mỗi nhóm điều kiện quan tâm. Dữ liệu của chúng tôi nằm trong khoảng từ 0 đến 10 và chúng tôi sẽ tạo chuỗi cho từng phạm vi 0-2, 2-4, 4-6, 6-8 và 8-10

Biểu đồ thanh định dạng có điều kiện

Dữ liệu cho biểu đồ thanh được định dạng có điều kiện được hiển thị bên dưới. Giới hạn định dạng được chèn vào hàng 1 và 2. Công thức tiêu đề trong ô C3, được sao chép vào D3. G3, là

=C1&"

Công thức là ô C4 là

=IF(AND(C$1<$B4,$B4<=C$2),$B4,"")

Công thức hiển thị giá trị trong cột B nếu nó nằm giữa các giới hạn ở hàng 1 và 2; . Công thức được điền vào phạm vi C4. G13

Khi biểu đồ thanh được chọn, dữ liệu nguồn của biểu đồ được tô sáng như hình minh họa

Chúng ta cần thay đổi dữ liệu nguồn, bỏ cột B và thêm cột C. G. Điều này được thực hiện dễ dàng bằng cách kéo và thay đổi kích thước các điểm nổi bật màu

Biểu đồ hiện hiển thị năm bộ thanh màu, một bộ cho mỗi phạm vi dữ liệu quan tâm. Tuy nhiên, điều đó không hoàn toàn đúng vì đây là biểu đồ thanh nhóm và mỗi thanh hiển thị được nhóm với bốn giá trị trống

Điều này có thể dễ dàng sửa chữa bằng cách định dạng bất kỳ một trong các thanh và thay đổi thuộc tính Chồng chéo thành 100%. Điều này làm cho các thanh hiển thị chồng lên các thanh trống

Biểu đồ đường được định dạng có điều kiện

Dữ liệu cho biểu đồ thanh được định dạng có điều kiện được hiển thị bên dưới. Giới hạn định dạng được chèn vào hàng 1 và 2. Công thức tiêu đề trong ô C3, được sao chép vào D3. G3, là

=C1&"

Công thức là ô C4 là

=IF(AND(C$1<$B4,$B4<=C$2),$B4,NA())

Công thức hiển thị giá trị trong cột B nếu nó nằm giữa các giới hạn ở hàng 1 và 2; . Công thức được điền vào phạm vi C4. G13

Khi biểu đồ đường được chọn, dữ liệu nguồn của biểu đồ được tô sáng như hình minh họa

Chúng ta cần mở rộng dữ liệu nguồn, giữ nguyên cột B là đường nối tất cả các điểm và thêm cột C. G cho sê-ri được định dạng riêng. Điều này được thực hiện dễ dàng bằng cách thay đổi kích thước các điểm nổi bật màu

Biểu đồ hiện hiển thị năm bộ đánh dấu màu và phân đoạn đường, một bộ cho mỗi phạm vi dữ liệu quan tâm

Một chút định dạng làm sạch nó. Xóa các điểm đánh dấu khỏi sê-ri gốc, xóa các dòng khỏi sê-ri khác và áp dụng các định dạng đánh dấu riêng biệt cho sê-ri đã thêm

Xóa mục nhập chú thích không cần thiết (đối với đường màu xám) bằng cách nhấp một lần để chọn chú giải, nhấp lại để chọn nhãn và nhấp vào Xóa

Tính linh hoạt của định dạng có điều kiện

Ví dụ đơn giản này có các công thức định dạng được xác định dựa trên các giá trị Y trong biểu đồ. Có thể xác định định dạng dựa trên giá trị Y, giá trị X hoặc giá trị trong cột khác thậm chí không được vẽ. Như trong định dạng có điều kiện của trang tính, giới hạn duy nhất là khả năng xây dựng công thức của chính bạn. Kỹ thuật này hoạt động trên hầu hết các loại biểu đồ Excel hữu ích, bao gồm biểu đồ thanh và đường được hiển thị ở đây và biểu đồ XY như được hiển thị trong Biểu đồ XY có điều kiện không có VBA