Hướng dẫn này sẽ trình bày cách tạo biểu đồ Excel với định dạng có điều kiện trong tất cả các phiên bản Excel: 2007, 2010, 2013, 2016 và 2022.
Định dạng có điều kiện là thực hành gán định dạng tùy chỉnh cho các ô Excel-màu, phông chữ, v.v. dựa trên các tiêu chí (điều kiện) được chỉ định. Tính năng này giúp phân tích dữ liệu, tìm kiếm các giá trị có ý nghĩa thống kê và xác định các mẫu trong một tập dữ liệu nhất định.
Nhưng rất tiếc, Excel không cung cấp bất kỳ công cụ tích hợp nào để áp dụng định dạng có điều kiện cho biểu đồ Excel.
Tuy nhiên, với một sự kỳ diệu của bảng tính, không có vấn đề nào là không thể vượt qua. Trong hướng dẫn này, bạn sẽ học cách tạo biểu đồ động với định dạng có điều kiện trong đó các điểm dữ liệu được vẽ biểu đồ được định dạng tự động dựa trên các giá trị thực tế tương ứng của chúng.
Ví dụ: hãy xem biểu đồ cột này, phân tích hiệu suất của người quản lý bán hàng trong khoảng thời gian tám tháng. Lưu ý cách màu của cột tự động điều chỉnh tùy thuộc vào giá trị cơ bản của nó:
Bắt đầu
Hãy xem xét tập dữ liệu mẫu này chứa dữ liệu hiệu suất bán hàng:
Trước khi chúng ta bắt đầu, hãy di chuyển tập dữ liệu của bạn để bạn có ba hàng trống phía trên bảng với dữ liệu thô của bạn. Các hàng sẽ được sử dụng để thiết lập các quy tắc định dạng có điều kiện.
Bước # 1: Chuẩn bị dữ liệu biểu đồ.
Để làm cho mọi thứ hoạt động, bạn cần thêm một số dữ liệu biểu đồ bổ sung vào phương trình. Để thuận tiện cho bạn, hãy xem dữ liệu biểu đồ của bạn trông như thế nào vào cuối giai đoạn chuẩn bị:
Bước đầu tiên của chúng tôi là xác định các giá trị ranh giới (điều kiện) sẽ chia các giá trị thực tế thành nhiều danh mục, giúp chúng ta có thể lập biểu đồ thành các chuỗi dữ liệu riêng biệt.
Ví dụ, từ ảnh chụp màn hình ở trên, bạn có thể thấy rằng số liệu bán hàng trong một tháng nhất định phải nằm trong khoảng 90.000 đến 150.000 đô la mới được coi là đạt yêu cầu.
Để tạo quy tắc, hãy sử dụng ba hàng trống phía trên tập dữ liệu:
- Hàng 1 (Nhãn): Sử dụng các ô này để gắn nhãn các quy tắc định dạng.
- Hàng 2 (Giá trị tối thiểu): Các giá trị trong hàng này xác định ranh giới dưới cho mỗi điều kiện.
- Hàng 3 (Giá trị tối đa): Tương tự như vậy, các giá trị này thiết lập các ranh giới trên.
Với phương pháp này, bạn có thể tạo bao nhiêu quy tắc định dạng có điều kiện tùy ý - chỉ cần đảm bảo các phạm vi giá trị không trùng lặp.
Khi bạn tiếp tục nhích dần lên trong việc thiết lập biểu đồ của mình, hãy thiết kế các mục chú giải biểu đồ tùy chỉnh sẽ cung cấp ngữ cảnh cho biểu đồ. Nhập công thức này vào C4 và sao chép nó sang D4 bằng cách kéo chốt điền:
| 1 | = C1 & ": từ" & TEXT (C2, "$ #, ##") & "tới" & TEXT (C3, "$ #, ##") |
Công thức tập hợp một nhãn động trông gọn gàng dựa trên các quy tắc định dạng có điều kiện đã đặt trước đó. Hàm TEXT định dạng các giá trị dưới dạng tiền tệ. Nhưng nếu kiểu dữ liệu của bạn khác, hãy áp dụng công thức này để thay thế:
| 1 | = C1 & ": từ" & TEXT (C2, "#, ##") & "đến" & TEXT (C3, "#, ##") |
Hoặc cái này khi bạn làm việc với tỷ lệ phần trăm:
| 1 | = C1 & ": từ" & TEXT (C2, "#%") & "tới" & TEXT (C3, "#%") |
Cuối cùng, tìm các giá trị biểu đồ. Nhập công thức sau vào C5, sao chép nó xuống C12 và qua các ô phù hợp trong cột D:
| 1 | = IF (VÀ (C $ 2 <$ B5, $ B5 <= C $ 3), $ B5, NA ()) |
Công thức so sánh một giá trị thực tế nhất định trong cột B chống lại các giá trị ranh giới được chỉ định với sự trợ giúp của các hàm IF và AND. Nếu giá trị nằm trong phạm vi, nó sẽ được sao chép vào cột này. Nếu không, công thức trả về lỗi # N / A để ngăn cùng một giá trị được vẽ nhiều lần.
Bước # 2: Thiết lập biểu đồ cột.
Sau khi thu thập tất cả dữ liệu biểu đồ, hãy thiết lập biểu đồ cột đơn giản - hoặc biểu đồ thanh để thay thế:
- Đánh dấu tất cả dữ liệu biểu đồ ngoại trừ các cột chứa giá trị thực và các quy tắc bằng cách nhấn giữ Điều khiển Chìa khóa (A4: A12 và C4: D12).
- Đi đến Chèn chuyển hướng.
- Lựa chọn "Chèn biểu đồ cột hoặc thanh.”
- Lựa chọn "Clustered Column / Clustered Bar.”
Kết quả là, bạn sẽ có một biểu đồ với các điểm dữ liệu được vẽ dựa trên các quy tắc định dạng có điều kiện - giống như đã hứa:
Bước # 3: Sửa đổi các giá trị Chồng chéo và Khoảng cách rộng.
Trước khi kết thúc hướng dẫn này, hãy khắc phục sự cố của các cột bị đặt sai vị trí gây ra bởi các giá trị trống không nhìn thấy (lỗi # N / A) được vẽ trên biểu đồ.
Nhấp chuột phải vào bất kỳ cột nào và chọn “Định dạng chuỗi dữ liệu”Từ menu ngữ cảnh bật lên.
Trong ngăn tác vụ, hãy thay đổi vị trí và chiều rộng của các cột:
- Chuyển sang Tùy chọn chuỗi chuyển hướng.
- Thay đổi "Chồng chéo chuỗi" đến "100%.”
- Đặt Chiều rộng khoảng cách đến "60%.”
Bước # 4: Điều chỉnh bảng màu.
Cuối cùng, hãy thêm những nét cuối cùng. Mà không cần đóng Định dạng chuỗi dữ liệu ngăn tác vụ, sửa đổi sơ đồ màu của biểu đồ:
- Đi đến Điền vào dòng chuyển hướng.
- Dưới "Lấp đầy," lựa chọn "Điền đặc.”
- Nhấn vào "Tô màuBiểu tượng ”và chọn màu xanh lá từ bảng màu.
Khi đó, hãy tô màu lại chuỗi dữ liệu khác, thay đổi tiêu đề biểu đồ và biểu đồ động hoàn toàn của bạn với định dạng có điều kiện đã sẵn sàng!
