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

Đỗ Thúy Quỳnh
Đỗ Thúy Quỳnh
Oct 14 2020

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.

Hướng dẫn ngắt trục biểu đồ trên 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:

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

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.

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

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.

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

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.

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

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.

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

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.

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

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:

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

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.

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

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.

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

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.

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

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.

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

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.

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

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.

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

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.

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

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.

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

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

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.

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

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.

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

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.

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

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.

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

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.

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

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.

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

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.

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

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.

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

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.

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

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

Có thể bạn sẽ cần: Học Excel tại Hà Nội

0/5 - (0 bình chọn)

0/5 - (0 bình chọn)

Bài viết liên quan

Hướng dẫn về một số hàm DAX căn bản trong Power Pivot (Phần 2)

Hướng dẫn về một số hàm DAX căn bản trong Power Pivot (Phần 2)

Hướng dẫn cách phân biệt Data Table với Lookup Table trong Power Pivot

Hướng dẫn cách phân biệt Data Table với Lookup Table trong Power Pivot

Hướng dẫn cách mở thẻ Power Pivot trên thanh Menu của Excel

Hướng dẫn cách mở thẻ Power Pivot trên thanh Menu của Excel

Hướng dẫn cách phân biệt giữa Power Pivot và Pivot Table

Hướng dẫn cách phân biệt giữa Power Pivot và Pivot Table

Hướng dẫn cách kết nối các bảng trong Power Pivot

Hướng dẫn cách kết nối các bảng trong Power Pivot

Hướng dẫn cách đổi tên bảng và tên cột trong Power Pivot

Hướng dẫn cách đổi tên bảng và tên cột trong Power Pivot

@ 2020 - Bản quyền của Công ty cổ phần công nghệ giáo dục Gitiho Việt Nam
Giấy chứng nhận Đăng ký doanh nghiệp số: 0109077145, cấp bởi Sở kế hoạch và đầu tư TP. Hà Nội