Cách tạo biểu đồ bảng trong Excel

Hướng dẫn này sẽ trình bày cách tạo biểu đồ bảng trong tất cả các phiên bản Excel: 2007, 2010, 2013, 2016 và 2022.

Biểu đồ bảng (còn được gọi là biểu đồ mắt cáo hoặc một bội số nhỏ) là một tập hợp các biểu đồ nhỏ hơn tương tự được so sánh cạnh nhau và được phân chia bởi các dấu phân cách. Vì các biểu đồ nhỏ này chia sẻ các trục giống nhau và được đo trên cùng một tỷ lệ, về cơ bản, một biểu đồ bảng điều khiển hợp nhất tất cả chúng vào một nơi.

Biểu đồ không chỉ cho phép bạn hiển thị nhiều thông tin hơn một cách gọn gàng mà còn giúp bạn nhanh chóng so sánh hoặc phân tích mối quan hệ giữa nhiều tập dữ liệu cùng một lúc đồng thời giúp bạn tiết kiệm rất nhiều không gian bảng điều khiển.

Ví dụ: hãy xem biểu đồ bảng bên dưới, kết hợp bốn biểu đồ đường riêng biệt so sánh doanh thu hàng năm của Orange Ltd và Banana Ltd nhượng quyền tại bốn tiểu bang trong sáu năm qua.

Như bạn có thể thấy, biểu đồ bảng minh họa một kho dữ liệu hữu ích không đáy, trái ngược với mớ hỗn độn của các dòng mà bạn sẽ nhận được nếu chỉ đơn giản là chọn đối tác tích hợp của nó.

Thật không may, loại biểu đồ này không được hỗ trợ trong Excel, có nghĩa là bạn sẽ phải tự xây dựng nó theo cách thủ công. Nhưng trước khi chúng ta bắt đầu, hãy xem Phần bổ trợ Trình tạo Biểu đồ, một công cụ đa năng để tạo các biểu đồ và đồ thị Excel nâng cao chỉ trong một vài cú nhấp chuột.

Trong hướng dẫn này, bạn sẽ học cách vẽ biểu đồ bảng có thể tùy chỉnh trong Excel từ đầu.

Bắt đầu

Để minh họa các bước để bạn làm theo, chúng ta cần bắt đầu với một số dữ liệu. Như bạn có thể đã đoán, chúng ta sẽ so sánh hồ sơ lịch sử của các thương hiệu nhượng quyền của hai công ty, Orange Ltd và Banana Ltd, ở bốn bang khác nhau-Iowa, Minnesota, Texas và Utah.

Với tất cả những gì đã nói, hãy xem xét bảng sau:

Hãy trình bày chi tiết hơn từng cột.

• Tiểu bang - Cột này đại diện cho các danh mục mà biểu đồ sẽ được chia thành các biểu đồ (bảng) nhỏ hơn. Trong trường hợp của chúng tôi, mọi biểu đồ đường nhỏ minh họa động lực hoạt động cho từng trạng thái.
• Năm - Cột này xác định tỷ lệ trục hoành. Các giá trị và định dạng phải giống nhau trên tất cả các bảng.
• Orange Ltd và Banana Ltd - Đây là những giá trị thực tế của bạn. Chúng tôi sẽ chỉ sử dụng hai bộ dữ liệu cho mục đích minh họa, nhưng với phương pháp được hiển thị trong hướng dẫn này, bầu trời là giới hạn.

Cuối cùng, chúng tôi muốn dữ liệu của mình trông như thế này:

Về mặt kỹ thuật, bạn có thể thao tác dữ liệu theo cách thủ công (nếu vậy, hãy chuyển đến Bước # 5). Cách tiếp cận này có thể thích hợp hơn nếu dữ liệu của bạn đơn giản. Nếu không, chúng tôi sẽ hướng dẫn bạn cách sử dụng bảng tổng hợp để thao tác dữ liệu sang định dạng cần thiết.

Vì vậy, chúng ta hãy đi sâu vào.

Bước # 1: Thêm dấu phân cách.

Trước khi có thể tạo biểu đồ bảng, bạn cần tổ chức dữ liệu của mình theo đúng cách.

Đầu tiên, ở bên phải dữ liệu thực tế của bạn (cột E), thiết lập một cột trợ giúp có tên là “Dấu phân cách.”Mục đích của cột này là chia dữ liệu thành hai danh mục xen kẽ được biểu thị bằng các giá trị 1 và 2-để tạo nền tảng cho bảng tổng hợp trong tương lai.

Kiểu "1”Vào mỗi ô tương ứng của cột Dấu phân cách thuộc loại đầu tiên, Iowa (E2: E7). Sau đó gõ “2”Vào tất cả các ô tương ứng thuộc loại thứ hai, Minnesota (E8: E13).

Theo cách tương tự, hãy điền vào các ô trống còn lại trong cột, xen kẽ giữa hai giá trị phân tách khi bạn chuyển từ trạng thái này sang trạng thái khác.

Bước # 2: Thêm bảng tổng hợp.

Khi bạn đã hoàn thành Bước 1, tạo một bảng tổng hợp.

1. Đánh dấu bất kỳ ô nào trong phạm vi tập dữ liệu (A1: E25).
2. Đi đến Chèn chuyển hướng.
3. Lựa chọn "PivotTable.”

Khi mà Tạo PivotTable hộp thoại xuất hiện, chọn “Bảng tính hiện có,”Đánh dấu bất kỳ ô trống nào gần dữ liệu thực tế của bạn (G1) và nhấp vào “VÂNG.”

Bước # 3: Thiết kế bố cục của bảng tổng hợp.

Ngay sau khi bảng tổng hợp của bạn được tạo, Trường PivotTable ngăn tác vụ sẽ bật lên. Trong ngăn tác vụ này, hãy chuyển các mục trong danh sách trường thành thứ tự sau - thứ tự rất quan trọng - để sửa đổi bố cục của bảng tổng hợp mới của bạn:

1. Di chuyển "Tiểu bang" và "Năm" đến "Hàng.”
2. Di chuyển "Dấu phân cách" đến "Cột.”
3. Di chuyển "Orange Ltd" và "Banana Ltd" đến "Các giá trị.”

Tại thời điểm này, bảng tổng hợp của bạn sẽ trông giống như sau:

Bây giờ, hãy thay đổi kiểu bố cục và loại bỏ các phần tử thừa của bảng.

1. Chọn bất kỳ ô nào trong bảng tổng hợp (G4: L26).
2. Điều hướng đến Thiết kế chuyển hướng.
3. Nhấp chuột "Bố cục Báo cáo.”
4. Lựa chọn "Hiển thị trong Biểu mẫu Bảng.”

Vẫn trong Thiết kế , nhấp vào “Tổng sốBiểu tượng ”và chọn“Tắt cho Hàng và Cột”Từ menu thả xuống.

Tiếp theo, nhấp vào “Tổng phụ" và lựa chọn "Không hiển thị tổng phụ.”

Sau khi bạn đã làm theo tất cả các bước, bảng tổng hợp của bạn sẽ được định dạng theo cách sau:

Bước # 4: Trích xuất dữ liệu từ bảng tổng hợp.

Vì bạn không muốn tạo biểu đồ tổng hợp (không, bạn không làm như vậy), đã đến lúc tách dữ liệu khỏi bảng tổng hợp. Sao chép tất cả các giá trị từ bảng tổng hợp của bạn (G4: L27) vào các ô trống bên cạnh nó. (Một cột trống giữa các tập dữ liệu sẽ giúp tránh nhầm lẫn.)

Hàng tiêu đề (P3: S3) của bảng mới tạo của bạn chịu trách nhiệm hiển thị các mục chú giải biểu đồ, vì vậy hãy nhanh chóng thêm chúng vào.

Vì bảng tổng hợp chia dữ liệu thành hai biểu đồ bán đường một cách hiệu quả, nên hàng tiêu đề của bạn sẽ như sau:

Bước # 5: Tạo biểu đồ đường.

Cuối cùng, vẽ một biểu đồ đường thông thường và xem điều gì sẽ xảy ra.

1. Đánh dấu bảng chứa dữ liệu được trích xuất từ ​​bảng tổng hợp (N3: S27).
2. Đi đến Chèn chuyển hướng.
3. Nhấn vào "Chèn Biểu đồ Đường hoặc Khu vực" cái nút.
4. Lựa chọn "Hàng.”

Bước # 6: Làm cho màu sắc đường kẻ nhất quán.

Như bạn có thể thấy, Excel đã tạo một biểu đồ đường với bốn chuỗi dữ liệu khác nhau. Hợp nhất dữ liệu đó bằng cách sử dụng các màu giống nhau cho mỗi công ty.

Nhấp chuột phải vào bất kỳ chuỗi dữ liệu nào và chọn “Định dạng chuỗi dữ liệu.”

bên trong Định dạng chuỗi dữ liệu ngăn tác vụ, hãy làm như sau:

1. Chuyển sang Điền vào dòng chuyển hướng.
2. Dưới "Hàng," lựa chọn "Đường vẽ liền.”
3. Nhấn vào "Màu phác thảo”Và chọn màu của bạn từ bảng màu. (Lặp lại cho từng dòng.)
4. Kiểm tra kỹ chú giải biểu đồ để đảm bảo bạn đã kết hợp màu đúng cách.

Bước # 7: Tạo các đường phân chia.

Nhiệm vụ trong tầm tay có vẻ như không có gì đặc biệt, nhưng nó không hề dễ dàng như bạn tưởng. Có, bạn có thể chỉ cần vẽ các đường bằng cách sử dụng các hình có sẵn, nhưng chúng sẽ bị đặt sai vị trí mỗi khi bạn thay đổi kích thước biểu đồ.

Vì vậy, thay vào đó, chúng tôi sẽ sử dụng các thanh lỗi dọc, thanh này sẽ được định vị giữa mỗi bảng điều khiển, ngay tại vị trí trục Y cắt trục danh mục.

Tuy nhiên, để giải quyết vấn đề, bạn sẽ phải vượt qua một vài vòng. Trước tiên, hãy tạo dữ liệu biểu đồ trợ giúp:

1. Thiết lập một bảng giả riêng cùng một cách như bạn thấy trên ảnh chụp màn hình bên dưới. Rời khỏi ô U4 trống để làm cho mọi thứ hoạt động và nhập “Ngăn cách" vào trong V4.
2. Kiểu "0" vào trong V5 và sao chép nó xuống. Số hàng chứa các giá trị trong bảng biểu thị số lượng thanh lỗi cuối cùng sẽ được tạo. Trong trường hợp này, chúng tôi cần ba thanh lỗi, vì vậy sẽ có ba ô có “0" trong chúng.
3. Gõ “=COUNTA (O4: O9) +0,5" vào trong U5.

Công thức tính số giá trị tỷ lệ trục x được sử dụng cho một bảng điều khiển và tính toán điểm giữa của bảng điều khiển để định vị các đường trên trục X ngay tại nơi hai trục gặp nhau.

Bây giờ, hãy nhập “=U5 + COUNTA (O4: O9) ” vào trong U6 và sao chép nó vào U7.

Khi bạn đã thiết lập bảng trợ giúp, hãy chèn dữ liệu đó vào biểu đồ bảng của bạn.

1. Đánh dấu tất cả các giá trị trong bảng trợ giúp, ngoại trừ hàng tiêu đề (U4: V7).
2. Chọn vùng biểu đồ.
3. Đi đến Nhà chuyển hướng.
4. Nhấp chuột "Dán.”
5. Lựa chọn "Dán đặc biệt.”

Trong hộp thoại xuất hiện, hãy làm theo một vài bước đơn giản:

1. Dưới "Thêm ô dưới dạng," lựa chọn "Bộ truyện mới.”
2. Dưới "Giá trị (Y) trong," lựa chọn "Cột.”
3. Kiểm tra "Tên sê-ri ở Hàng đầu tiên" và "Danh mục (Nhãn X) trong Cột đầu tiên”Hộp.
4. Nhấp chuột "VÂNG.”

Bước # 8: Thay đổi loại biểu đồ của chuỗi giả.

Nhấp chuột phải vào chuỗi dữ liệu mới được thêm vào (Sê-ri “Bộ chia”) và lựa chọn "Thay đổi loại biểu đồ chuỗi.”

Vì Sê-ri “Bộ chia” thay đổi “Loại biểu đồ” đến "Phân tán với Đường thẳng.”

Bước # 9: Sửa đổi các trục thứ cấp.

Đầu tiên, loại bỏ trục hoành thứ cấp. Nhấp chuột phải vào các số dọc theo đầu biểu đồ và chọn “Xóa bỏ.”

Sau đó, nhấp chuột phải vào trục tung phụ dọc theo bên phải của biểu đồ và chọn “Định dạng trục.”

bên trong Định dạng trục ngăn tác vụ, đặt trong đá các phạm vi tỷ lệ trục:

1. Chuyển sang Tùy chọn trục chuyển hướng.
2. Thay đổi Giới hạn tối thiểu có giá trị đối với "0.”
3. Đặt Giới hạn tối đa có giá trị đối với "1.”

Trong cùng một tab, cuộn xuống Nhãn phần và thay đổi “Vị trí nhãn" đến "Không có”Để ẩn tỷ lệ trục - nếu bạn chỉ xóa nó, điều đó sẽ làm hỏng biểu đồ.

Bước # 10: Thêm thanh lỗi.

Nền tảng hiện đã được thiết lập, có nghĩa là cuối cùng bạn có thể thêm các thanh lỗi.

1. Lựa chọn Sê-ri “Bộ phân chia”.
2. Nhấn vào Thành phần biểu đồ biểu tượng.
3. Nhấp vào mũi tên bên cạnh “Thanh lỗi.”
4. Lựa chọn "Lựa chọn khác.”

bên trong Định dạng thanh lỗi ngăn tác vụ, sửa đổi các dấu phân cách.

1. Chuyển sang Tùy chọn thanh lỗi chuyển hướng.
2. Dưới "Phương hướng," lựa chọn "Thêm.”
3. Dưới "Kiểu kết thúc," lựa chọn "Không có Cap.”
4. Dưới "Số tiền Lỗi," thay đổi "Giá trị cố định" đến "1.”

Bước # 11: Ẩn các phần tử biểu đồ trợ giúp.

Bước cuối cùng, hãy xóa biểu đồ phân tán thứ cấp bên dưới, các thanh lỗi ngang và các mục chú giải trùng lặp.

Trước tiên, hãy đối phó với loạt phim giả. Lựa chọn Loạt "Bộ chia", điều hướng đến Định dạng chuỗi dữ liệu ngăn tác vụ và trong Tùy chọn chuỗi , chọn “Không có dòng.”

Bây giờ, hãy xóa các thanh lỗi ngang và mọi mục chú giải được lưu cho “Orange Ltd" và "Banana Ltd"Bằng cách chọn từng mục, nhấp chuột phải và chọn"Xóa bỏ. ” Đến cuối bước này, biểu đồ bảng điều khiển của bạn sẽ trông giống như sau:

Thay đổi tiêu đề biểu đồ và bạn đã có biểu đồ bảng điều khiển tuyệt đẹp của mình sẵn sàng hoạt động!