Bạn đang xem bài viết Hướng Dẫn Tạo Biểu Đồ Tương Tác Trong Excel Với Hàm Index được cập nhật mới nhất trên website Hoisinhvienqnam.edu.vn. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất.
Hướng dẫn tạo biểu đồ tương tác trong Excel với hàm INDEX
Tạo dữ liệu
Bạn chỉ cần áp dụng theo các bước tương tự ví dụ sau đây với số liệu của bạn. Sắp xếp dữ liệu theo hàng, không có khoảng trống giữa chúng. Khi hoàn tất biểu đồ, bạn có thể chuyển đổi vị trí giữa C-3PO và Darth Vader và biểu đồ sẽ cập nhật với dữ liệu chính xác.
Tiếp theo, sao chép và dán hàng tiêu đề bên dưới bảng dữ liệu.
Bây giờ, ở hàng thứ ba tính từ tiêu đề bạn vừa dán xong, nhập từ ” Dataset” vào một ô và điền một số giữ chỗ (placeholder number) vào ô bên phải cạnh nó. Từ giờ chúng sẽ là các phần giữ chỗ để làm cơ sở cho trình đơn thả xuống.
Khi tất cả đã đúng vị trí của nó, chúng ta sẽ nối các phần tử này bằng hàm INDEX. Nhập công thức hàm sau vào hai ô bên trên Dataset:
Chỉ mũi tên vào góc của ô có chứa công thức hàm, bạn sẽ thấy mũi tên biến thành dấu cộng, sau đó kéo nó ra trên toàn bộ hàng.
Tạo biểu đồ
Bây giờ bước tiếp theo sẽ dễ dàng hơn một chút. Chúng ta sẽ chọn dữ liệu có được sau khi sử dụng hàm INDEX – KHÔNG phải là dữ liệu mà chúng ta nhập bằng tay – biến nó thành một biểu đồ.
Bạn có thể sử dụng bất cứ loại biểu đồ nào phù hợp với dữ liệu của bạn nhất. Ở ví dụ này, biểu đồ cột có vẻ hợp lý nhất.
Khi chọn xong, biểu đồ thể hiện dữ liệu sẽ hiện ra, hãy thử nghiệm rằng mọi thứ đang hoạt động bằng cách thay đổi số bên cạnh Dataset. Nếu biểu đồ thay đổi theo tập dữ liệu mà bạn chọn thì có nghĩa là bạn đã thành công.
Thêm một trình đơn thả xuống
Tạo trình đơn thả xuống bất cứ nơi nào bạn muốn đặt nó nhưng phải nằm trên bảng tính. Ở ví dụ này, trình đơn thả xuống được đặt dưới biểu đồ.
Trường Cell link cần trùng khớp với ô có chứa số chỉ ra tập dữ liệu được chọn.
Nhấp OK và kiểm tra trình đơn thả xuống. Bạn sẽ có thể chọn tập dữ liệu theo tên và biểu đồ sẽ được cập nhật tự động.
Sau khi hoàn tất, bạn có thể tinh chỉnh “diện mạo” để tăng tính thẩm mỹ cho bảng tính của bạn, dọn dẹp một số dữ liệu để làm cho biểu đồ trở nên gọn gàng hơn.
Tất cả những gì bạn cần làm là thêm Sheet1! ở phía trước ô đã chọn trước đó. Điều này sẽ thông báo cho Excel biết để tìm các ô trên một trang tính khác với biểu đồ và trình đơn thả xuống. Nhấp OK và chúng ta đã có một biểu đồ động trên một trang tính mới gọn gàng và ngăn nắp hơn.
Hướng Dẫn Tạo Biểu Đồ Tương Tác Đầu Tiên Trong Excel
Hướng dẫn tương tác với biểu đồ trong Excel
Có một số cách để tạo một biểu đồ tương tác trong Excel. Bạn có thể sử dụng data validation, từ controls, slicers, timelines, VBA hoặc hyperlinks. Trong hướng dẫn này, chúng ta sẽ tìm hiểu cách tạo biểu đồ tương tác với data validation và slicers.
1. Biểu đồ tương tác với Data Validation
Tạo một biểu đồ pivot table nâng cao với tất cả những số liệu này sẽ rất lâu và khó. Bạn muốn tạo một biểu đồ năng động hoặc tương tác để sếp của bạn có thể chọn sản phẩm nào cô ấy muốn phân tích và hiểu xu hướng đặt hàng. Bạn có thể thực hiện theo các bước sau:
Bước 1: Lập danh sách tất cả các lựa chọn
Chọn khoảng chứa tất cả tên sản phẩm và đi đến Namebox (góc trên cùng bên trái) và nhập tên là products.
Ngoài ra, bạn cũng có thể liệt kê tên sản phẩm trong một phạm vi riêng biệt và đặt tên đó. Hãy chắc chắn rằng tên sản phẩm đó phải nằm trong khoảng chọn sản phẩm mà bạn đã chọn ban đầu.
Bước 2: Thiết lập cơ chế lựa chọn
Quyết định ô nào sẽ có lựa chọn người dùng. Ví dụ chọn ô Q5.
Thay đổi tiêu chí xác nhận trong Allow chọn List.Nhập “products” trong Nguồn.
Nhấp vào ok.
Bâygiờ, chúng ta có cách chọn sản phẩm trong ô Q5
Bước 3: chọn sản phẩm cho ô Q5
Nếu chúng ta muốn tên của sản phẩm được chọn, chúng ta chỉ cần sử dụng “= Q5”. Đối với phần còn lại của tính toán, chúng ta cần số lượng sản phẩm (nghĩa là vị trí của sản phẩm được chọn trong products). Đối với điều này, chúng ta có thể sử dụng công thức MATCH, như sau: Nhập công thức MATCH này vào một ô trống ví dụ ô I3= MATCH (Q5, products, 0). Sau đó sẽ trả về một số, phù hợp với sản phẩm người dùng đã chọn.
Bước 4: Tính số lượng đặt hàng để hiển thị trong biểu đồ
Bước 5: Tạo biểu đồ
Tất cả các công việc ban đầu sẽ được hoàn thành khi bạn thực hiện chèn một biểu đồ. Chỉ cần chọn cột sản phẩm đã chọn và chèn một cột hoặc biểu đồ đường. Chúng ta có như sau:
Đầu tiên, hãy để thêm nhãn trục:
Nhấp chuột phải vào chart và đi đến select data.
Chỉnh sửa nhãn ngang (Edit hoeizontal /category) và chọn cột tháng.
Bây giờ, xóa chart title và chart border (đặt thành không có dòng). Kết quả như sau:
Bước 6: Bước hoàn tất
Chúng ta chỉ cần gộp tất cả lại với nhau và biểu đồ tương tác đầu tiên của chúng ta lại thì ta sẽ được biều đồ Pivot table nhiều cột hoàn thành.
Chọn biểu đồ cho kiểu dữ liệu biểu trong ô Q5 (ô chọn data validation)
Khi bạn đó với mỗi tùy chọn tại Q5 thì sẽ cho bạn một biểu đồ được cập nhật.
Biểu đồ tương tác với Pivot table và Slicer
Nếu bạn e ngại với INDEX + MATCH, bạn có thể thử phương pháp Pivot Table. Nó cũng hoạt động rất tốt và cho phép bạn tạo ra các biểu đồ tương tác tuyệt vời không kém. Hãy nhớ rằng dữ liệu của bạn cần phải phù hợp. Sắp xếp lại để nó trông như bảng sau:
Hãy nhớ rằng dữ liệu của bạn cần phải phù hợp. Sắp xếp lại để nó trông như thế này:
Bước 1: Chèn một Pivot từ dữ liệu của bạn
Chọn dữ liệu của bạn (cột tháng, sản phẩm và số lượng) và chèn một Pivot table.
Thêm tháng vào khu vực labels.
Trong các phiên bản cao hơn của Excel thì sẽ xuất hiện phân cấp ngày – năm, quý và thán. Khi đó, hãy chọn quý.
Thêm số lượng vào khu vực giá trị.
Nhấp chuột phải vào products và thêm nó dưới dạng như một slicers.
Bước 2: Chèn 1 pivot chart
Bước 3: Định dạng cho pivot chart
Chọn pivot chart và đi đến Analyze và tắt nút Field Buttons.
Thay thế tiêu đề biểu đồ bằng Total Order Quantity trong 13 tháng qua.
Đặt đường viền biểu đồ thành No line.
Vị trí slicer liền kề với biểu đồ.
Vẽ một hình chữ nhật tròn xung quanh cho tất cảBiểu đồ tương tác của chúng tôi đã sẵn sàng để hoạt động.
Gợi ý trang trí tùy chọn
Thêm một tiêu đề phụ mô tả xu hướng.Đặt giới hạn cho trục tung. Theo mặc định, Excel sẽ thay đổi giới hạn trục Y bất cứ khi nào bạn chọn sản phẩm. Đây có thể là một cách tạo ra một số biến dạng của các con số và gây nhầm lẫn cho người dùng của bạn nếu họ muốn so sánh các sản phẩm.
Điều chỉnh độ rộng khe hở. Excel sẽ chọn một số giá trị vô lý như 219%. Điều chỉnh này thành 100% hoặc tương tự như vậy để có ít khoảng trắng hơn trên biểu đồ. Để thực hiện việc này, nhấp vào các cột, nhấn CTRL + 1 và từ Series options điều chỉnh độ rộng khoảng cách.
Hướng Dẫn Sử Dụng Hàm Index Trong Excel Cơ Bản.
Hàm INDEX dạng mảng
Cú pháp: =INDEX(array, row_num, [column_num])
Trong đó:
Array: Bắt buộc phải điền, phạm vi ô hoặc một hằng số mảng.
Row_num: Bắt buộc phải điền. chọn hàm trong mảng mà từ đó trả về một giá trị đang tìm.
Column_num: chọn cột trong mảng mà từ đó trả về một giá trị đang tìm.
Lưu ý:
Nếu mảng chỉ chứa một hàng hoặc cột, thì đối số Row_num hoặc Column_num tương ứng chỉ cần nhập 1 trong 2.
Nếu mảng có nhiều hàng, nhiều cột và chỉ có đối số Row_num hoặc Column_num được dùng, thì hàm INDEX trả về mảng có toàn bộ hàng hoặc cột.
Chức năng: Trả về giá trị của một thành phần trong bảng hoặc trong mảng, được chọn bởi các chỉ mục số hàng và cột.
Row_num và Column_num phải trỏ tới một ô trong mảng dã chọn . Nếu không, hàm INDEX trả về giá trị lỗi #REF! .
Ví dụ: Hướng dẫn sử dụng hàm INDEX trong Excel
Cho một nhóm học sinh, tìm lớp của 1 học sinh đó. Biết tên học sinh đó ở hàng 2, cột 1.
Hàm INDEX dạng biểu mẫu tham số
Cú pháp: = INDEX(reference, row_num, [column_num], [area_num])
Trong đó:
Reference : Bắt buộc phải điền. Đây là vùng tham chiếu.
Row_num : Bắt buộc phải điền. Chỉ số hàng từ đó trả về một tham chiếu.
Column_num : Tùy chọn, có thể không điền. Chỉ số cột từ đó trả về một tham chiếu
Area_num: Tùy chọn, có thể không điền. Số của vùng ô đã chọn sẽ trả về giá trị trong Reference.
Chức năng: Trả về tham chiếu của ô nằm ở giao cắt của một hàng và cột đã chọn. Nếu tham chiếu được tạo thành bởi các vùng chọn không liền kề, bạn có thể chọn vùng chọn để tìm xem ở đó.
Lưu ý:
Nếu bạn nhập một phạm vi không liền kề làm tham chiếu, hãy đặt tham chiếu trong dấu ngoặc đơn.
Nếu mỗi vùng trong tham chiếu chỉ chứa một hàng hoặc cột, thì Row_num hoặc Column_num tương ứng là tùy chọn.
Ví dụ: Hướng dẫn sử dụng hàm INDEX trong Excel
Cho một nhóm học sinh, tìm tên học sinh đó.
Hướng Dẫn Cách Dùng Hàm Index Trong Excel Chi Tiết Nhất
Hàm INDEX là hàm trả về giá trị hoặc tham chiếu của 01 ô trong excel là giao nhau giữa dòng và cột được xác định bởi các thông số cho trước. Hàm này cũng được coi là một hàm tìm kiếm với khả năng vận dụng và kết hợp cùng các hàm khác khá mạnh mẽ, có thể áp dụng để giải quyết nhiều hơn các vấn đề phức tạp.
Cú pháp của hàm Index
Hàm INDEX có 2 dạng:
Một là Hàm INDEX Dạng Mảng: Kết quả của hàm INDEX sẽ trả về giá trị của một ô (Khi bạn đã cho các giá trị tham chiếu kèm theo)
Hai là Hàm INDEX Dạng Tham Chiếu: Kết quả sẽ trả về một dải ô tham chiếu, được quy định bởi [area_num]
Cách viết hàm INDEX dạng mảng
Cú Pháp: (Array,Row_num,[Column_num])
Trong đó:
– Array: bắt buộc Phạm vi ô hoặc một hằng số mảng.
– Row_num: Thứ tự dòng cần tìm (tính từ dòng đầu tiên của Array đến dòng chứa giá trị cần tìm kiếm).
– Column_num: Thứ tự cột cần tìm (tính từ cột của Array đến cột chứa giá trị cần tìm kiếm).
Phải có ít nhất một trong hai đối số Row_num, Column_num. Nếu đối số nào để trống sẽ được hiểu là chọn vị trí cột đầu hoặc hàng đầu.
Ví dụ 1: Cho danh sách nhóm học sinh,tìm tên học sinh biết tên học sinh đó ở hàng 2 cột 2.
Ô C9 Công thức: =INDEX(B4:C7,2,2)
Hàm INDEX Dạng Tham Chiếu
Cú Pháp: INDEX(Reference,Row_num,[Column_num],[Area_num])
Trong đó:
– Reference: bắt buộc Vùng tham chiếu,.
– Row_num: Thứ tự dòng cần tìm (tính từ dòng đầu tiên của Array đến dòng chứa giá trị cần tìm kiếm).
– Column_num: Thứ tự cột cần tìm (tính từ cột của Array đến cột chứa giá trị cần tìm kiếm).
– Area_num: tùy chọn Số của vùng ô sẽ trả về giá trị trong reference.
Nếu Area_num được bỏ trống thì hàm INDEX dùng vùng 1.
Tiếp ví dụ 1:
Ô C9 Công thức: =INDEX(B4:C7,2,1,1)
KẾT HỢP HÀM INDEX VÀ MATCH
Hãy tìm hiểu hàm Index kết hợp Match với yêu cầu sau:
Tìm doanh số của cửa hàng D
Tìm tên cửa hàng khi biết trước doanh số
Để giải quyết yêu cầu này sử dụng Cú pháp hàm INDEX kết hợp hàm MATCH sau:
=INDEX($B$2:$C$7,MATCH(C9,$B$2:$B$7,0),2)
Trong đó:
– $B$2:$C$7: Array/ vùng dữ liệu
– MATCH(C9,$B$2:$B$7,0): Kết quả của hàm MATCH sẽ trả về số thứ tự dòng của cửa hàng D trong Array
– 2: Là cột thứ 2 tính từ cột đầu tiên mà cột đó chứa giá trị tìm kiếm = Doanh số
Kết quả ta nhận được như sau:
Khi thay đổi giá trị trong ô C9, kết quả trong ô C10 sẽ tự động đổi theo đó bạn.
Hoặc thay đổi tương ứng doanh thu bạn sẽ thấy kết quả thay đổi theo.
YÊU CẦU SỐ 2: TÌM TÊN CỬA HÀNG KHI BIẾT TRƯỚC DOANH SỐ
Tìm vị trí của Doanh số biết trước số bằng hàm MATCH trong cột doanh
= MATCH(C12,$C$2:$C$7,0)
Tìm trong cột tên cửa hàng: cửa hàng đang có doanh số tương ứng
$B$2:$B$7: Array/ Vùng chứa Tên cửa hàng cần tìm.
Kết hợp lại:
=INDEX($B$2:$B$7,MATCH(C12,$C$2:$C$7,0))
Cụ thể cách sử dụng hàm trong hình sau:
KẾT HỢP HÀM INDEX CÙNG HÀM SUM
Bài toán cũ, nhưng đổi yêu cầu:
Tính doanh số cột C:
=SUM(C2:C7)
=SUM(INDEX(A2:C7,,3))
Tính doanh số 3 cửa hàng trên cùng
=SUM(C2:C4)
=SUM(C2:INDEX(A2:C7,3,3))
=SUM(INDEX(A2:C7,1,3):INDEX(A2:C7,3,3))
KẾT HỢP HÀM INDEX CÙNG HÀM MIN/ MAX
Bài toán cũ, nhưng đổi yêu cầu khi kết hợp MIN/ MAX
Tìm cửa hàng doanh số thấp nhất:
=INDEX(B2:B7,MATCH(MIN(C2:C7),C2:C7,0))
Tìm cửa hàng doanh số cao nhất:
=INDEX(B2:B7,MATCH(MAX(C2:C7),C2:C7,0))
Cập nhật thông tin chi tiết về Hướng Dẫn Tạo Biểu Đồ Tương Tác Trong Excel Với Hàm Index trên website Hoisinhvienqnam.edu.vn. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất. Chúc bạn một ngày tốt lành!