VietNetwork.Vn

Làm cho hai tính năng tốt nhất của Excel hoạt động hài hòa chỉ trong năm bước.

Nếu muốn gây ấn tượng với bạn bè và đồng nghiệp, bạn có thể thực hiện thay đổi biểu đồ Excel dựa trên hộp thả xuống mà bạn đã thêm vào trang tính của mình. Có nhiều cách khác nhau để bạn có thể đạt được điều này nhưng chúng tôi thích phương pháp sau hơn vì sẽ dễ dàng xác định bất kỳ vấn đề nào hơn nếu có sự cố xảy ra.

Chúng tôi sẽ sử dụng một bảng dữ liệu chứa tên năm cầu thủ bóng đá và xếp hạng trận đấu của họ trong năm trận đấu khi chúng tôi hướng dẫn bạn về quy trình này.

Bước 1: Tạo danh sách thả xuống

Bước đầu tiên là tạo danh sách thả xuống để làm cho biểu đồ của bạn trở nên động. Bắt đầu bằng cách nhập một từ hành động vào ô bên cạnh nơi trình đơn thả xuống của bạn sẽ xuất hiện. Trong ví dụ của chúng tôi, chúng tôi sẽ đặt menu thả xuống vào ô B9, vì vậy chúng tôi sẽ nhập từ hành động vào ô A9. Sau đó, nhấp vào ô nơi trình đơn thả xuống của bạn sẽ xuất hiện, đi tới tab Dữ liệu trên dải băng và nhấp vào "Xác thực dữ liệu" trong nhóm Công cụ dữ liệu.


Trong cửa sổ Xác thực dữ liệu, chọn "Danh sách" trong Cho phép. Sau đó, nhấp vào hộp trường "Nguồn" và đánh dấu các tiêu đề cột trong bảng của bạn (dữ liệu sẽ được hiển thị trong danh sách thả xuống mà bạn đang tạo). Trong ví dụ của chúng tôi, chúng tôi muốn chọn số trò chơi, vì trong biểu đồ chúng tôi sẽ tạo sau này, chúng tôi muốn xem xếp hạng của người chơi cho mỗi trò chơi. Sau đó, nhấp vào "OK."


Bây giờ bạn sẽ thấy danh sách thả xuống hiển thị dữ liệu bạn đã chọn khi nhấp vào mũi tên. Chúng tôi cũng đã định dạng ô Chọn để làm nổi bật ô này.


Bước 2: Tạo bảng truy xuất dữ liệu

Bây giờ chúng ta cần tạo một bảng thứ hai tách biệt với dữ liệu thô nhưng lấy thông tin từ đó khi chúng ta điều chỉnh menu thả xuống mà chúng ta đã tạo. Biểu đồ chúng ta tạo sau này sẽ được tạo bằng cách sử dụng thông tin trong bảng truy xuất dữ liệu này.

Từ thời điểm này, chúng ta sẽ gọi bảng dữ liệu chính là Bảng 1 và bảng truy xuất dữ liệu là Bảng 2.

Đánh dấu tiêu đề hàng trong Bảng 1, nhấn Ctrl+C và sử dụng Ctrl+V để sao chép danh sách vào nơi bạn muốn có Bảng 2.


Chọn và nhấp chuột phải vào dữ liệu đã dán, di chuột qua "Sắp xếp" và nhấp vào "Sắp xếp từ A đến Z".


Để tiếp tục tạo Bảng 2, bạn cần đặt tiêu đề cho cột tiếp theo. Dữ liệu xuất hiện trong Bảng 2 sẽ được quyết định bởi những gì bạn chọn trong hộp thả xuống, do đó, trong ô nơi bạn muốn tiêu đề xuất hiện, hãy nhập = và nhấp vào ô thả xuống. Trong trường hợp của chúng tôi, chúng tôi muốn tiêu đề của mình ở ô I2 và trong ô đó, chúng tôi sẽ nhập = và nhấp vào ô B9.


Nhấn Enter để xem tiêu đề cột trong Bảng 2 áp dụng lựa chọn thả xuống. Hãy thử thay đổi lựa chọn thả xuống để xem tiêu đề cột trong Bảng 2 thay đổi.

Bước 3: Trích xuất dữ liệu từ Bảng 1 sang Bảng 2

Bây giờ chúng ta cần trích xuất dữ liệu liên quan từ Bảng 1 đến Bảng 2 bằng INDEX và MATCH. Đây là phần phức tạp nhất của quy trình, nhưng hãy chia nhỏ nó ra để bạn có thể thực hiện quy trình dễ dàng hơn. Đây là khớp thần kinh:

=INDEX(a,MATCH(b,c,0),MATCH(d,e,0))

• a là tất cả dữ liệu trong Bảng 1 (không bao gồm tiêu đề hàng và cột),
• b là giá trị đầu tiên chúng ta tra cứu trong Bảng 2,
• c là khoảng giá trị ở cột đầu tiên trong Bảng 1,
• d là tiêu đề cột trong Bảng 2, và
• e đại diện cho các tiêu đề cột dữ liệu trong Bảng 1.

Đảm bảo tham chiếu ô a, c, d và e là tham chiếu tuyệt đối bằng cách nhấn F4 trên bàn phím sau khi chọn từng tham chiếu. Nếu không, công thức của bạn sẽ không hoạt động khi bạn hoàn thành phần dữ liệu còn lại trong Bảng 2.

Hãy xem điều này trong thực tế.

Trong ô I3, chúng tôi muốn yêu cầu Excel tìm trong Bảng 1 và kéo một giá trị vào Bảng 2 thông qua hàm INDEX.

=INDEX
Bây giờ chúng ta cần mở dấu ngoặc đơn và cho Excel biết vị trí của dữ liệu trong Bảng 1. Để thực hiện việc này, hãy đánh dấu tất cả dữ liệu trong Bảng 1, nhưng không đánh dấu tiêu đề cột và hàng. Nhấn F4 để biến đây thành tham chiếu tuyệt đối và thêm dấu phẩy.

=INDEX($B$3:$F$7,
Bước tiếp theo là yêu cầu Excel khớp dữ liệu trong Bảng 2 với dữ liệu chúng ta có trong Bảng 1. Nhập MATCH, mở dấu ngoặc đơn mới và nhấp vào mục nhập đầu tiên trong cột đầu tiên của Bảng 2. Trong trường hợp của chúng tôi, đó là Davies trong tế bào H3. Thêm dấu phẩy.

=INDEX($B$3:$F$7,MATCH(H3,
Tiếp theo, Excel cần biết nó sẽ sử dụng tham chiếu gì để tra cứu trong Bảng 1. Trong trường hợp của chúng tôi, đó là dữ liệu từ ô A3 đến ô A7 (tên của những người chơi), vì vậy hãy đánh dấu phạm vi này, nhấn F4 và sau đó thêm dấu phẩy. Sau đó, nhập 0 (không) để cho Excel biết rằng chúng tôi đang tìm kiếm kết quả khớp chính xác và đóng dấu ngoặc đơn. Thêm một dấu phẩy khác.

=INDEX($B$3:$F$7,MATCH(H3,$A$3:$A$7,0),
Bây giờ, chúng ta phải lặp lại quy trình MATCH cho dữ liệu sẽ thay đổi khi chúng ta chọn một tùy chọn khác trong trình đơn thả xuống. Trong trường hợp của chúng tôi, đó là số trò chơi, vì vậy sau khi nhập MATCH vào công thức của chúng tôi lần thứ hai, chúng tôi sẽ nhấp vào I2, nhấn F4 và thêm dấu phẩy.

=INDEX($B$3:$F$7,MATCH(H3,$A$3:$A$7,0),MATCH($I$2,
Và một lần nữa, chúng ta cần cho Excel biết nơi tìm dữ liệu đó trong Bảng 1. Trong ví dụ của chúng ta, đó là các ô từ B2 đến F4. Đừng quên nhấn F4 sau khi bạn đã tham chiếu các ô này. Sau đó, thêm dấu phẩy, số 0 và đóng hai dấu ngoặc đơn lại với nhau. Cuối cùng nhấn Enter.

=INDEX($B$3:$F$7,MATCH(H3,$A$3:$A$7,0),MATCH($I$2,$B$2:$F$2,0))
Bây giờ, hãy nhấp và kéo tay cầm ở góc dưới bên phải của ô nơi bạn vừa nhập công thức để tự động điền phần dữ liệu còn lại trong Bảng 2.


Hãy thử thay đổi tùy chọn thả xuống của bạn thành một giá trị khác và xem sự thay đổi dữ liệu trong Bảng 2, đồng thời kiểm tra xem nó có khớp với thông tin trong Bảng 1 hay không. Hãy nhớ rằng chúng tôi đã sắp xếp cột đầu tiên trong Bảng 2 theo thứ tự bảng chữ cái, vì vậy hãy kiểm tra cẩn thận, vì cột đầu tiên trong Bảng 1 sẽ có thứ tự khác!


Bây giờ Bảng 2 đã thay đổi thành công tùy thuộc vào lựa chọn thả xuống của chúng tôi, chúng tôi đã sẵn sàng tạo biểu đồ.

Bước 4: Chèn và định dạng biểu đồ của bạn

Đánh dấu tất cả dữ liệu trong Bảng 2 (bao gồm tiêu đề cột và hàng), mở tab "Chèn" và chọn biểu đồ phù hợp với bạn.


Chúng ta đã sử dụng biểu đồ cột 2D đơn giản.


Định dạng biểu đồ để xuất hiện như bạn muốn và một lần nữa, hãy thử sử dụng trình đơn thả xuống để xem Bảng 2—và do đó, biểu đồ của bạn—điều chỉnh theo lựa chọn của bạn. Bạn cũng sẽ thấy tiêu đề biểu đồ của mình thay đổi.

Bước 5: Thêm đường trung bình vào biểu đồ của bạn

Để thêm một đường trung bình vào biểu đồ, bạn cần thêm nhiều dữ liệu hơn vào Bảng 2. Thêm một tiêu đề cột khác vào Bảng 2 và gọi nó là Trung bình.


Bây giờ, hãy sử dụng hàm AVERAGE để tính giá trị trung bình của dữ liệu đã chọn. Trong trường hợp của chúng tôi, chúng tôi đã chọn trò chơi 4, vì vậy chúng tôi muốn tìm xếp hạng trung bình của tất cả năm người chơi cho trò chơi này. Để làm điều này, gõ

=AVERAGE
vào ô đầu tiên của cột mới này, sau đó chọn dữ liệu ở cột trước đó. Nhấn F4 để biến tham chiếu này thành tham chiếu tuyệt đối, sau đó đóng dấu ngoặc đơn rồi nhấn Enter.


Sau đó, sử dụng tay cầm AutoFill để nhấp và kéo công thức xuống cuối cột.

Bây giờ chúng tôi muốn thêm dữ liệu trung bình vào biểu đồ của mình.

Nhấp vào bất kỳ vị trí nào về phía mép biểu đồ của bạn và nhấp vào "Thiết kế biểu đồ" trên dải băng. Sau đó, đi tới nhóm Dữ liệu và nhấp vào "Chọn Dữ liệu".


Trong cửa sổ Chọn nguồn dữ liệu, nhấp vào "Thêm".


Sau đó, trong cửa sổ Chỉnh sửa chuỗi, hãy xóa trường Giá trị chuỗi, rồi chọn dữ liệu trong cột Trung bình của Bảng 2. Nhấp vào "OK" và bạn sẽ thấy giá trị trung bình xuất hiện dưới dạng thanh bổ sung trong biểu đồ của mình.


Để thay đổi dữ liệu trung bình này thành một đường trên các thanh hiện có của bạn, hãy nhấp chuột phải vào một trong các thanh mới và nhấp vào "Thay đổi loại biểu đồ chuỗi".


Mở tab "Tất cả biểu đồ", nhấp vào "Kết hợp" trong menu bên trái và thay đổi chuỗi dữ liệu trung bình thành "Dòng".


Nhấp vào OK để xem kết quả và thay đổi lựa chọn thả xuống để xem biểu đồ của bạn thay đổi linh hoạt!


Sau đó, bạn có thể định dạng dòng của mình để nó nổi bật theo cách bạn muốn.

Bây giờ bạn đã có được biểu đồ năng động, ấn tượng đó, hãy xem một số cách để làm cho nó nổi bật hơn nữa.