Hướng dẫn ngắt trục biểu đồ trên Excel

Khi có những điểm hoặc chuỗi bất thường trong dữ liệu nguồn, điều này sẽ không được thể hiện rõ ràng qua biểu đồ. Trong những trường hợp này, chúng ta có thể ngắt trục biểu đồ và thể hiện những điểm hay chuỗi bất thường trong dữ liệu này đồng thời trên cùng một biểu đồ. Từ đó khiến biểu đồ trình bày được hết các khía cạnh của dữ liệu, dễ hiểu và dễ phân tích hơn. Bài này Gitiho.com sẽ hướng dẫn bạn 2 cách để ngắt trục biểu đồ trong Excel.

Dùng trục phụ khác để ngắt trục biểu đồ

Giả thiết rằng chúng ta có 2 chuỗi dữ liệu trong nguồn dữ liệu như hình minh họa dưới. Ta có thể thêm một biểu đồ và ngắt trục biểu đồ bằng cách thêm một trục phụ trong biểu đồ. Để làm được điều này chúng ta thực hiện các bước sau:

Bước 1: Chọn nguồn dữ liệu và thêm một biểu đồ đường bằng cách nhấn chọn Insert Line or Area Chart. Hoặc trên tab Insert, chọn Line.

Bước 2: Trong biểu đồ, kích chuột phải trên chuỗi dữ liệu và nhấn chọn Format Data Serie từ danh sách tùy chọn được thả xuống. Giống như hình minh họa.

Bước 3: Lúc này sẽ xuất hiện một hộp thoại mới tên là Format Data Series, hãy tích vào ô tùy chọn Secondary Axis và sau đó đóng hộp thoại này lại.

Bước 4: Trên đồ thị, nhấn chuột phải trên cột phụ vừa được tạo và nhấn chọn tùy chọn  Format Axis trong danh sách tùy chọn vừa được thả xuống.

Bước 5: Trong bảng điều khiển Format Axis, gõ 160 vào ô Maximum trong mục Bounds. Và trong nhóm Number nhập [<=80]0;;; vào ô Format Code và nhấn chọn nút Add. Đóng bảng điều khiển lại.

Lời khuyên: Trong Excel phiên bản 2010 hay các bản trước đó, nó sẽ mở  cho bạn hộp thoại Format Axis dialog. Hãy nhấn chọn Axis Option tren thanh bên trái, tích vào ô tùy chọn Fixed đằng trước Maximum. Sau đó gõ 200 vào ô tiếp theo. Nhấn chọn Number trên thanh bên trái, gõ [<=80]0;;; vào ô Format code và nhấn chọn Add.

Bước 6: Nhấn chuột phải vào trục tung (trục chính ở bên trái) của biểu đồ và chọn Format Axis để mở kênh Format Axis. Sau đó nhập [>=500]0;;; vào trong ô Format Code. Nhấn nút Add và đóng kênh điều khiển lại.

Bây giờ bạn cso thể thấy có 2 trục Y trong một biểu đồ, giống như là một trục Y bị ngắt. Giống như hình dưới đây:

Thêm cột giả định để ngắt trục biểu đồ

Giả sử chúng ta đang có một bảng dữ liệu lớn khác thường như hình minh họa bên dưới. Và chúng ta cần thêm một trục giả định để ngắt trục biểu đồ, giúp cho biểu đồ của chúng ta được gia tăng độ chính xác. Để thực hiện điều này ta làm theo các thao tác sau:

Bước 1: Để ngắt trục Y chúng ta phải xác định giá trị nhỏ nhất, giá trị cần ngắt, giá trị bắt đầu lại và giá trj lớn nhất trong trục mới được ngắt. Như trong ví dụ của bài này chúng ta có 4 giá trị trong pham vi A11:B14.

Bước 2: Chúng ta cần tính toán lại dữ liệu nguồn giống như hình minh họa bên dưới:

1. Trong ô C2 nhập công thức =IF(B2>$B$13,$B$13,B2) và nhấn chuột kéo tay chốt của ô xuống dưới để ta áp dụng công thức cho toàn bộ phạm vi C2:C7.

2. Trong ô D2 nhập công thức =IF(B2>$B$13,100,NA()) và nhấn chuột kéo tay chốt của ô xuống dưới để ta áp dụng công thức cho toàn bộ phạm vi D2:D7.

3. Trong ô E2 nhập công thức =IF(B2>$B$13,B2-$B$12-1,NA()) và nhấn chuột kéo tay chốt của ô xuống dưới để ta áp dụng công thức cho toàn bộ phạm vi E2:E7.

Bước 3: Tạo một biểu đồ dựa theo nguồn dữ liệu mới. Bôi đen phạm vi A1:A7, sau đó bôi đen tiếp phạm vi C1:E7 bằng cách nhấn dữ phím Ctrl đồng thời rê chuột. Tiếp theo thêm một biểu đồ mới bằng cách nhấn chọn Insert Column or Bar Chart hoặc Column. Nhấn chọn Stacked Column.

Bước 4: Trong biểu đồ mới, nhấn chuột phải vào chuỗi ngắt (chỉ màu đỏ) và nhấn chọn tùy chọn Format Data Series  từ danh sách tùy chọn được thả xuống.

Bước 5: Lúc này một kênh mới sẽ được mở ra tên là Format Data Series, nhấn nút Color trên tab Fill & Line. Và sau đó chọn một màu giống như màu nền (trong minh họa ở đây của chúng ta là màu trắng).

Lời khuyên: Nếu bạn đang sử dụng Excel 2007 và 2010, nó sẽ mở hộp thoại Format Data Series cho bạn, lúc này hãy nhấn chọn Fill trên thanh bên trái, và sau đó nhấn chọn tùy chọn No fill. Cuối cùng đóng hộp thoại lại.

Và thay đổi màu chuỗi sau cùng màu với chuỗi trước tương tự. Trong ví dụ này chúng ta sẽ chọn cùng màu xanh nước biển.

Bước 6: Chúng ta cần tính toán nguồn dữ liệu cho trục giả định. Ta điểm lại danh sách dữ liệu trong phạm vi I1:K13 giống như hình dưới đây:

1. Trong cột Labels, điểm lại các nhãn dựa trên giá trị nhỏ nhất, giá trị ngắt, giá trị bắt đầu lại và giá trị lớn nhất giống như cách chúng ta đã làm trong bước 1.

2. Trong cột Xpos, gõ 0 vào tất cả các ô trừ ô ngắt. Trong ô ngắt gõ 0,25. Giống như hình minh họa bên dưới.

3. Trong cột Ypos, gõ các số dựa trên các nhãn của trục Y trong biểu đồ chồng.

Bước 7: Nhấn chuột phải vào biểu đồ và chọn Select Data từ danh sách tùy chọn được thả xuống.

Bước 8: Trong hộp thoại Select Data Source vừa được mở ra, nhấn nút Add. Bây giờ một hộp thoại mới tên là Edit Series xuất hiện, chọn ô I1 (cho ngắt trục Y) vào ô series name. Và chọn phạm vi K3:K13 (cột Ypos) vào ô series values, và nhấn chọn OK. Chọn tiếp OK để đóng 2 hộp thoại này lại.

Bước 9: Bây giờ chúng ta sẽ trở lại với biểu đồ, nhấn chuột phải vào chuỗi vừa được thêm, sau đó chọn Change Series Chart Type trong danh sách tùy chọn vừa được thả xuống.

Bước 10: Một hộp thoại mới sẽ xuất hiện tên là Change Chart Type. Đến mục Choose the chart type and axis for your data series, nhấn chọn hộp For Broken Y axis. Và chọn tiếp Scatter with Straight Line từ danh sách tùy chọn được thả xuống. Cuối cùng nhấn nút OK.

Chú ý: Nếu bạn đang sử dụng Excel phiên bản năm 2007 và 2010, trong hộp thoại Change Chart Type, nhấn chọn X Y (Scatter)  trên thanh bên trái. Và nhấn chọn Scatter with Straight Line từ danh sách tùy chọn được thả xuống. Rồi nhấn nút OK.

Bước 11: Kích chuột phải vào chuỗi dữ liệu mới một lần nữa, và chọn Select Data từ danh sách tùy chọn được thả xuống.

Bước 12. Trong hộp thoại Select Data Source, tại mục Legend Entries (Series), nhấn chọn For broken Y axis và nhấn nút Edit. Sau đó hộp thoại mới được mở ra tên là Edit Series, tại đây trong ô Series X values chọn J3:J13 (Xpos column) và nhấn chọn OK. Ấn tiếp OK một lần nữa để đóng hai hộp thoại này lại.

Bước 13: Nhấn chuột phải vào đường thẳng bị ngắt mới và chọn Format Data Series từ danh sách tùy chọn được thả xuống.

Bước 14: Trong hộp thoại Format Data Series trên Excel 2013, nhấn nút Color trên tab Fill & Line. Sau đó chọn màu giống với màu cột trước. Trong ví dụ này của chúng ta là màu xanh nước biển. 

Chú ý: Với các phiên bản Excel 2007 và 2010, trong hộp thoại Format Data Series, nhấn chọn Line color ở thanh bên trái, tích vào tùy chọn solid line. Nhấn nút Color và chọn màu tương tự với màu các cột trước. Cuối cùng đóng hộp thoại lại.

Bước 15: Giữ đường thẳng bị ngắt và nhấn chọn Add Chart Element > Data Labels > Left trên tab Design.

Mẹo: Nhấn chọn Data Labels > Left trên tab Layout trong Excel 2007 và 2010.

Bước 16: Thay đổi tất cả các nhãn dựa trên cột Labels. Ví dụ như ở đây chọn nhãn trên cùng biểu đồ và tiếp theo gõ = trong thanh định dạng, sau đó chọn ô I13 và nhấn phím Enter.

Bước 17: Xóa một vài thành phần không cần thiết trong biểu đồ. Ví dụ như chọn trục tung Y ban đầu và nhấn phím Delete để xóa.

Cuối cùng thì chúng ta đã có một biểu đồ mà trục tung Y bị ngắt.