Cách tạo biểu đồ Bump chart (biểu đồ đường gấp khúc) động trong Excel

Bạn có muốn tạo một biểu đồ Bump chart động tuyệt đẹp trong Excel không?

Một biểu đồ Bump chart hay biểu đồ đường gấp khúc là một biến thể của biểu đồ đường, giúp bạn khám phá những thay đổi về thứ hạng theo thời gian. Bump chart cũng thường được sử dụng để so sánh vị trí hoặc hiệu suất của nhiều đối tượng quan sát khác nhau.

Dưới đây là một ví dụ về biểu đồ Bump chart động được tạo trong Excel.

Trong bài đăng này, chúng tôi sẽ hướng dẫn bạn cách tạo biểu đồ Bump chart với những đường gấp khúc động như ở trên, sử dụng dữ liệu lịch sử của Giải Ngoại hạng Anh kể từ mùa giải 1992-93. Tệp dữ liệu được sử dụng trong ví dụ này được đính kèm ngay bên dưới, bạn có thể tải xuống để tiện thao tác.

Thu thập và làm sạch dữ liệu thô

Dữ liệu cho ví dụ này được tập hợp trên các trang Premier League của Wikipedia. Chúng tôi đã xếp tất cả các bảng cho mỗi mùa giải thành một bảng khổng lồ gồm 486 hàng, có tất cả dữ liệu 24 mùa. Chúng tôi cũng đã thêm một cột để xác định từng mùa, ví dụ: “1992-93”.

Tiếp theo, chúng ta cần làm sạch dữ liệu để chuẩn hóa các tên gọi, ví dụ như đổi Arsenal! Arsenal * (C) * thành Arsenal.

Đầu tiên, bạn hãy tạo một bản sao của cột tên nhóm, nhằm giúp kiểm tra dữ liệu gốc.

Sau đó, chúng ta chọn cột tên mới của mình và mở chức năng Tìm và Thay thế ( Ctrl + F). Sử dụng Replace All để thay đổi thống nhất tên cho các đội, như hình bên dưới.

Tìm *Arsenal* và thay thế bằng Arsenal .

Bằng cách thêm dấu * vào trước và sau chuỗi “Arsenal”, chúng ta có thể sửa tất cả các biến thể trong một lần tìm kiếm.

Các bước tạo biểu đồ Bump chart động trong Excel

Định dạng dữ liệu

1. Chọn toàn bộ bảng dữ liệu Premier League ( Ctrl + A) và Insert > PivotTable. Thêm Cleaned Team vào Rows, YEAR vào Columns và Position vào Values, như được hiển thị bên dưới.

2. Tạo bản sao dữ liệu bảng tổng hợp trong một tab mới. Chọn toàn bộ bảng ( Ctrl + A), sau đó điền vào tất cả các ô trống có giá trị 23, tức là đối với bất kỳ đội nào không thuộc Premier League mùa đó, hãy xếp họ ngoài top 22 (số đội Premier League tối đa trong đầu mùa). Sử dụng tính năng Tìm và Thay thế một lần nữa, lần này để trống hộp tìm kiếm (trống) và đặt 23 vào hộp Thay thế. Sau đó chọn Replace All.

3. Bên dưới bảng này, chúng ta sẽ thêm một dòng cho các giá trị thay đổi khi người dùng chọn một xem một đội khác.

Bây giờ, hãy lặp lại tên đội cuối cùng (Wolverhampton Wanderers) trong ô trống đầu tiên dưới tên đội (xem hình ảnh bên dưới). Sau đó, thêm công thức sau vào ô liền kề:

=VLOOKUP($AB51,$AB$4:$AZ$50,2,FALSE)

Bạn có thể sử dụng một phím tắt để lưu việc nhập số tham chiếu cột vào công thức VLOOKUP của mình. Nếu bạn đang ở một cột khác với AC, hãy điều chỉnh số 27 trong công thức để tính cho số cột trước bảng dữ liệu hiện tại của bạn (tức là từ A đến AA có 27 cột, trước khi bảng dữ liệu biểu đồ của chúng ta bắt đầu ở cột AB).

=VLOOKUP($AB51,$AB$4:$AZ$50,COLUMN(AC1)-27,FALSE)

Đánh dấu hàng cuối cùng này để phân biệt nó là dòng động. Tập dữ liệu bây giờ như sau:

Chúng ta sẽ thay đổi tên nhóm thành động trong các bước sau, sau đó dữ liệu sẽ thay đổi thành động theo công thức này.

Đã đến lúc chúng ta bắt tay vào vẽ biểu đồ rồi!

Vẽ biểu đồ Bump chart trong Excel

4. Đánh dấu toàn bộ bảng dữ liệu ( Ctrl + A) và chèn biểu đồ đường với các điểm đánh dấu. Thao tác này sẽ cung cấp cho bạn biểu đồ nhỏ xíu, trong không hề đẹp mắt chút nào.

5. Excel đã không diễn giải dữ liệu theo cách chúng ta muốn, vì vậy, hãy hoán đổi các hàng và cột bằng cách sử dụng Chart Design > Switch Rows/Columns, để có được một biểu đồ khác:

6. Mở rộng biểu đồ và xóa chú thích (nhấp vào biểu đồ để chọn, sau đó nhấn xóa) và bạn có thể bắt đầu thấy các đường biểu diễn đan xen vào nhau.

7. Bây giờ, chúng ta cần thay đổi màu của tất cả các đường kẻ và điểm đánh dấu này thành màu xám, đồng thời thêm nhãn dữ liệu cho tên các đội bóng. Bạn có thể thực hiện việc này theo cách thủ công bằng cách lần lượt nhấp vào từng dòng và:

•  thay đổi màu sắc
• thay đổi đường viền và màu tô của các điểm đánh dấu
• thêm nhãn dữ liệu và chỉ cần thay đổi “Tên chuỗi”
• nhấp vào từng nhãn dữ liệu và xóa tất cả trừ nhãn cuối cùng.

Tất cả các bước trên thật tẻ nhạt. Vì vậy, chúng ta hãy sử dụng VBA, mọi thứ sẽ hoàn thiện trong chưa đầy 1 giây.

Bạn hãy đi tới Developer menu tab > Visual Basic menu option > Modules > Module 1, như được hiển thị trong hình bên dưới

Trong cửa sổ nhập code, bạn hãy sao chép và dán đoạn code VBA sau:

Option Explicit
Sub formatBumpChart()
  Dim mySrs As Series
  Dim nPts As Long
  For Each mySrs In ActiveChart.SeriesCollection
        With mySrs
        nPts = .Points.Count
        mySrs.Points(nPts).ApplyDataLabels _
          Type:=xlDataLabelsShowValue, _
          AutoText:=True, LegendKey:=False
        mySrs.Points(nPts).DataLabel.Text = mySrs.Name
        .MarkerStyle = xlMarkerStyleSquare
        .MarkerSize = 7
        .MarkerForegroundColorIndex = 15
        .MarkerBackgroundColorIndex = 15
        .DataLabels.Font.Size = 14
    End With
        With mySrs.Border
        .ColorIndex = 15 ' grey lines
        .Weight = xlThin
        .LineStyle = xlContinuous
    End With
  Next
End Sub

Đoạn code trên có chứa một vòng lặp chạy trên tất cả các dòng (chuỗi) trong biểu đồ và định dạng chúng thành các đường màu xám mảnh với tên đội được thêm vào dưới dạng nhãn dữ liệu:

Bây giờ tệp Excel của bạn đã chứa VBA, bạn sẽ cần Save As và chọn file hỗ trợ macro .xlsm.

Nhấp vào vùng biểu đồ và di chuyển để cung cấp thêm chỗ cho các nhãn dữ liệu.

8. Tiếp theo, chúng ta sẽ cần lật bảng đồ lên để hiển thị các đội xếp top đầu lên trên. Bạn hãy nhấp vào trục y, nhấp

• Ctrl + 1 để hiển thị các tùy chọn định dạng.
• Chọn ô  Maximum axis value bên dưới mục Horizontal Axis Crosses.
• Tick chọn vào ô Values in reverse order để cố định trục, sao cho số 1 luôn ở trên cùng.
• Đặt các giới hạn trên trục y thành từ 0 đến 22.
• Đặt Major Units thành 1,0 như hình bên dưới.

Cuối cùng, trong bảng điều khiển này, hãy thay đổi định dạng số thành Custom với Type là #,##0;; mẹo này giúp chúng ta ẩn số 0 

9. Đã đến lúc thu gọn trục X. Bạn hãy chọn trục x, sau đó trong hộp định dạng, check vào hộp Text Axis và Between tick marks.

10. Thêm một tab trống mới vào sổ làm việc của bạn. Cắt và dán biểu đồ vào tab mới này.

11. Chúng ta sẽ thay đổi định dạng theo màu sắc của đội bóng đang đứng đầu bảng. Vậy nên, chúng ta sẽ chuyển biểu đồ thành màu xanh nhạt tương tự màu áo của đội Leicester City. Để làm điều này, hãy nhấp vào dòng chuỗi của Leicester (điểm dữ liệu trên cùng bên phải trong biểu đồ) và định dạng sao cho nó nổi bật. Trong ví dụ này, chúng tôi đã chọn:

• vạch màu xanh lam nhạt
• độ dày vạch 2pt
• điểm đánh dấu hình tròn có kích thước 20
• điểm đánh dấu hình tròn màu xanh lam nhạt

Chúng ta cũng sẽ định dạng nhãn dữ liệu tên nhóm thành màu đậm và màu xanh lam nhạt:

Bạn có thể nhấp vào bất kỳ đường nào trong biểu đồ này và xóa chúng nếu muốn.

Định dạng và thêm hiệu ứng động cho biểu đồ Bump chart

12. Thêm phần tử dòng động.

Phía trên biểu đồ của bạn, hãy tạo một menu thả xuống cho phép người dùng chọn một đội bóng, thao tác như sau:

• Data> Data Validataion
• Settings > Validation Criteria > Allow: List
• Nhấp vào hộp nhập Source (số 5 trong hình bên dưới).

Sau đó, đánh dấu tên nhóm trong bảng dữ liệu biểu đồ của bạn, ngoại trừ tên động ở cuối danh sách, để chọn tất cả tên nhóm:

Quay lại ô nơi bạn đặt xác thực dữ liệu và bạn sẽ thấy một menu thả xuống (hình tam giác sẽ xuất hiện khi bạn di chuột qua ô):

13. Quay lại bảng dữ liệu biểu đồ, chuyển đến ô ở mà chúng ta nhập tất cả các công thức VLOOKUP trong Bước 3.

Nhấp vào ô tên nhóm cuối cùng (ô cuối cùng trong cột tên nhóm, ô mà chúng tôi đã nhập bên cạnh các VLOOKUP) và nhập “=”, sau đó nhấp vào ô xác thực dữ liệu,  công thức của bạn lúc này sẽ có dạng:

='EPL Chart'!J2

trong đó “EPL Char” là tên trang biểu đồ có xác thực dữ liệu. Bây giờ ô này sẽ được liên kết với menu thả xuống, vì vậy khi bạn chọn một đội bóng, giá trị ô này sẽ thay đổi và dữ liệu trong các ô VLOOKUP cũng sẽ thay đổi vì chúng ta đang sử dụng giá trị tra cứu khác. Bạn có thể kiểm tra bằng cách chọn một đội khác trong menu thả xuống.

14. Định dạng dòng động đại diện cho lựa chọn của người dùng.

Dòng này đã có trong biểu đồ dưới dạng bản sao của bất kỳ đội nào bạn đã chọn. Vì vậy, bạn chỉ cần chọn và định dạng để nó trở nên nổi bật hơn.

• Chọn dòng bạn muốn thay đổi trong menu biểu đồ
• Chọn Format 
  
• Chọn chuỗi tên nhóm chính xác (không phải nhãn dữ liệu).
• Nhấn Ctrl + 1 để hiển thị menu định dạng và thay đổi định dạng dòng (màu đỏ đậm + 2pt + điểm đánh dấu lớn).

15. Định dạng các nhãn dữ liệu dòng động.

Bước này có đôi chút phức tạp. Trước tiên, bạn thực hiện tương tự như bước 13, chọn Nhãn dữ liệu nhóm cuối cùng trong menu Format chart. Nhấn vào nút Delete để xóa dòng đó, vì thật ra chúng ta không cần dòng này. Sau đó, chúng ta sẽ thêm nhãn dữ liệu khác vào để thay thế.

• Chọn dòng động ở bước 13 (hãy xem lại bước 13 nếu bạn không nhớ dòng động ở đâu)
• Chart Design > Add Chart Element > Data Labels > Center
• Thêm nhãn dữ liệu giá trị
• Ctrl + 1 để hiển thị menu định dạng
• Check vào hộp Value (đây là hộp duy nhất được chọn dưới menu này)
• Trên menu chính, hãy thay đổi kích thước phông chữ thành 14pt và in đậm, để các giá trị này nổi bật.
  

Các giá trị  này nhằm thể hiện vị trí mà đội đã hoàn thành trong mùa giải đó. Bây giờ biểu đồ của bạn sẽ trông như sau:

16. Để biểu đồ đẹp mắt hơn, bạn có thể thêm các hiệu ứng khác như bóng mờ, thay đổi kiểu đường viền,... Sau đó, bạn có thể thêm tiêu đề bên trên biểu đồ và di chuyển menu thả xuống sang ô bên cạnh tiêu đề nếu thích.

Hy vọng, qua bài viết này, bạn đã biết cách tạo biểu đồ Bump chart (biểu đồ đường gấp khúc) động trong Excel để dễ dàng so sánh hiệu suất, cũng như thứ hạng của nhiều đối tượng khác nhau theo thời gian.

Bên cạnh đó, để không bỏ lỡ các mẹo và thủ thuật tin học văn phòng hữu ích khác, hãy tham gia  Gitiho ngay hôm nay.