Bạn đang xem bài viết Hướng Dẫn Sử Dụng Hàm Index Tạo Biểu Đồ Tương Tác Trong Excel đượ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.
Advertisements
Giới thiệu và Cách tạo biểu đồ tương tác trên Excel với Index
Khái niệm Hàm INDEX
Hàm Index là hàm trả về mảng trong Excel. Sử dụng Hàm Index giúp bạn lấy các giá trị tại một ô nào đó giao giữa cột và dòng.
Hàm INDEX dạng mảng trả về giá trị của một 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. Ta dùng dạng mảng nếu đối số thứ nhất của hàm INDEX là một hằng số mảng. Hàm INDEX dạng tham chiếu trả về tham chiếu của ô nằm ở giao của một hàng và cột cụ thể.
Hiện nay, có rất nhiều cách giúp người dùng Tạo biểu đồ tương tác trong Excel, các bạn có thể sử dụng hàm hyperlinks, sử dụng data validation, sử dụng hàm VBA,… , hoặc cũng có thể là sử dụng 1 hàm đơn giản là INDEX trên excel thôi. Với việc sử dụng Index, chúng ta có thể tạo ra biểu đồ tương tác rất đơn giản, dễ dàng bằng hàm index và nó sẽ biến đổi các biểu đồ Excel tĩnh trở thành biểu đồ động.
Bước 1: Đầu tiên, việc cần làm đó chính là thu thập, tạp một bảng dữ liệu.
Bước 3: Tiếp theo các bạn hãy thực hiện Copy (sao chép) & Paste (dán) hàng Tiêu đề xuống bên dưới bảng dữ liệu:
+ $B$10:$I$13 là toàn bộ dữ liệu mà bạn muốn công thức truy cập.
+ $C$18 chỉ đến ô xác định dữ liệu hiển thị (số mà chúng ta đặt bên cạnh ô Dataset). Kết thúc hàm bằng một tham chiếu cột, nhưng ở đây chúng ta đã chỉ ra ô cụ thể nên điền là 0.
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 đồ.
Trong bài viết này, ở ví dụ mà mình nêu ra, mình sẽ chọn biểu đồ hình cột, bởi vì mình cảm thấy những dữ liệu này sẽ phù hợp biểu độ dạng hình cột hơn.
Bước 2: Khi chọn xong, biểu đồ sẽ hiển thị ra như hình dưới. Bạn 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, hay nói cách khác là ‘Nếu các dữ liệu trong biểu đồ sẽ trùng khớp với dữ liệu trong bảng biểu thì điều này chứng tỏ rằng bạn đã THÀNH CÔNG’ rồi đó.
Ví dụ: bạn có thể đặt trình đơn này bên trên biểu đồ:
LỜI KẾT
Vậy là các bạn đã có thể tự cho mình một biểu đồ tương tác trên Excel với hàm INDEX rồi. Ngoài ra, nếu các bạn còn biết cách vẽ biểu đồ Excel hay vẽ đồ thị Excel thì với những thao tác có dài dòng trong bài viết này, chỉ cần bạn bỏ ra một chút thời gian đọc kỹ rồi thực hành mình đảm bảo chắc chắn rằng bạn sẽ thấy dễ hiểu và thấy nó thật đơn giản thôi bạ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 Match
Cách dùng hàm INDEX trong Excel
Hàm INDEX được dùng để lấy và trả dữ liệu của một ô trong 1 vùng dựa trên chỉ số hàng và chỉ số cột của vùng đó. Hàm INDEX có cú pháp như sau:
=INDEX(vùng_dữ_liệu, hàng_thứ_mấy, [cột_thứ_mấy])
vùng_dữ_liệu: là địa chỉ vùng dữ liệu chúng ta muốn “nhặt” ra 1 giá trị
hàng_thứ_mấy: ô cần lấy dữ liệu nằm ở hàng thứ mấy?
cột_thứ_mấy: ô cần lấy dữ liệu nằm ở cột thứ mấy?
Ví dụ: Để thực hành hàm INDEX chúng ta sẽ thử lấy dữ liệu tại địa chỉ giao giữa hàng thứ 3 và cột thứ 4 như sau:
=INDEX(A1:H10,3,4)
Kết quả trả về giá trị là 840000
Cách dùng hàm MATCH trong Excel
Hàm MATCH sử dụng để tìm kiếm vị trí có thể là tương đối hoặc tuyệt đối của 1 giá trị trong 1 dòng hoặc một cột của bảng tính Cú pháp của hàm MATCH như sau:
=MATCH(giá_trị_cần_tìm, mảng_tìm_kiếm, [kiểu_tìm_kiếm])
giá_trị_cần_tìm – giá trị cần được tìm kiếm vị trí trong mảng
mảng_tìm_kiếm – mảng chứa giá trị cần tìm kiếm
kiểu_tìm_kiếm – tìm kiếm giá trị chính xác hay tìm kiếm giá trị gần nhất.
1 – hoặc bỏ qua: tìm giá trị lớn nhất mà giá trị đó bằng hoặc nhỏ hơn giá_trị_cần_tìm. Giá trị trong mảng_tìm_kiếm cần được sắp xếp tăng dần
0 – giá trị bằng, yêu cầu tìm kiếm chính xác
-1 – tìm giá trị nhỏ nhất mà giá trị đó lơn hơn hoặc bằng giá_trị_cần_tìm
Ví dụ sử dụng hàm MATCH để tìm kiếm vị trí của mã sản phẩm ”S04N” tại cột A
=MATCH(“S04N”,A1:A10,0) Kết quả bằng 6.
Nếu chỉ dừng ở đây, bạn sẽ thấy hàm MATCH không có nhiều giá trị trong ứng dụng, tuy nhiên bạn sẽ thấy vai trò của hàm MATCH như thế nào khi sử dụng kết hợp với hàm khác và trong trường hợp này chúng ta kết hợp hàm MATCH cùng hàm INDEX.
Hướng dẫn cách sử dụng kết hợp hàm INDEX và MATCH trong Excel
Để trực quan dễ hiểu, chúng ta sẽ làm một ví dụ như sau:
Chúng ta sẽ tìm kiếm xem Sản phẩm nào có mã hàng là S04N
=INDEX(Table1[#All],MATCH(“S04N”,Table1[[#All],[Mã hàng]],0),2)
Trong công thức này:
– Table1[#AII] là bảng dữ liệu cần tra cứu, trong trường hợp này chúng ta đặt tên vùng A1:H10 là Table1[#AII]
– MATCH(“S04N”,Table1[[#All],[Mã hàng]],0) cho chúng ta biết S04N ở vị trí hàng thứ mấy trong bảng tính trên. Kết quả S04N ở vị trí hàng thứ 6. Công thức với hàm INDEX trở thành: =INDEX(Table1[#All],6,2)
Kết luận chúng ta có công thức như sau:
=INDEX( cột cần tra cứu giá trị, (MATCH ( giá trị dùng để tra cứu, cột chứa giá trị này, 0 ))
Bạn có thể thực hành INDEX và MATCH với bảng dữ liệu được đính kèm tại bài này, khi bạn hiểu bản chất ứng dụng của INDEX và MATCH, thì những ứng dụng sau này sẽ đơn giản hơn rất nhiều.
Vì sao kết hợp hàm INDEX và hàm MATCH tốt hơn hàm VLOOKUP?
Hàm VLOOKUP là hàm chỉ dò tìm 1 chiều
Với hàm VLOOKUP, chúng ta chỉ có thể tra cứu dữ liệu từ trái qua phải, ví dụ cũng là bảng dữ liệu trên để dò tìm Mã của Vải SilK thì Vlookup sẽ không làm được nhưng với INDEX và MATH thì có làm được không? chúng ta thử một ví dụ:
Kết quả tìm được là mã S04N, vậy với hàm INDEX và hàm MATCH thì đã tìm ra kết quả.
Hàm VLOOKUP sai kết quả nếu thêm hoặc bớt cột trong bảng tính
Bởi vì khi dùng VLOOKUP các bạn phải chỉ ra cột nào chúng ta muốn lấy giá trị về. Khi thêm hoặc bớt 1 cột ở giữa cột đầu tiên và cột cần lấy giá trị về thì cột cần lấy giá trị bị xê dịch đi, dẫn đến kết quả của hàm VLOOKUP không đúng nữa. Với INDEX và MATCH thì điều này không xảy ra vì khi thêm/bớt cột thì công thức trong hàm INDEX và MATCH sẽ được điều chỉnh theo.
5
/
5
(
2
votes
)
Tài liệu kèm theo bài viết
Tai lieu thuc ham ham index va match.xlsx
Tải xuống
Hướng Dẫn Sử Dụng Hàm Forecast Dự Đoán Giá Trị Tương Lai Trong Excel
Hàm FORECAST (hay FORECAST.LINEAR) trong Excel trả về dự đoán giá trị tương lai dựa trên các giá trị hiện có được cung cấp. Đây là một loại hàm thống kê.
Hàm chúng tôi trong Excel dự đoán giá trị tương lai bằng cách sử dụng phương pháp liên tuyến lũy thừa Exponential Triple Smoothing mang tính thời vụ.
Lưu ý: Hàm FORECAST là một hàm cũ. Do đó Microsoft Excel khuyên rằng nên sử dụng hàm FORECAST.LINEAR mới vì nó cũng tạo ra kết quả như nhau.
Hàm FORECAST.LINEAR
=FORECAST.LINEAR(A12,$B$2:$B$11,$A$2:$A$11).
Giải thích: Khi ta kéo chốt ô chứa công thức hàm FORECAST.LINEAR xuống thì các tham chiếu tuyệt đối ($B$2:$B$11 và $A$2:$A$11) sẽ được cố định. Trong khi đó các tham chiếu tương đối A12 sẽ thay đổi thành A13 và A14…
Bước 2. Nhập giá trị 89 vào ô C11, chọn phạm vi A1:C14 và chèn vào đó một biểu đồ phân tán gồm đường thẳng và các điểm mốc.
Chú ý: Khi ta thêm đường xu hướng vào biểu đồ Excel, Excel có thể sẽ hiển thị một phương trình trong biểu đồ. Phương trình này sẽ dự đoán các giá trị tương lai giống nhau.
Hàm FORECAST.ETS
Hàm chúng tôi trong Excel 2016 hoặc các phiên bản sau là một hàm cực kỳ tuyệt vời, cho phép ta tìm ra được mô hình theo thời kỳ.
=FORECAST.ETS(A14,$B$2:$B$13,$A$2:$A$13,1)
Chú ý:
3 đối số cuối cùng được tùy chọn.
Đối số thứ tư cho biết độ dài của mô hình theo thời kỳ.
Giá trị mặc định của 1 cho biết thời kỳ được tìm ra tự động.
Bước 2. Nhập giá trị 49 vào ô C13, chọn dải ô A1:C17 và chèn vào đó một biểu đồ phân tán gồm các điểm mốc và đường thẳng nối các điểm.
Bước 3. Lúc này ta có thể sử dụng hàm FORECAST.ETS.SEASONALITY để tìm ra mô hình có thời kỳ dài nhất. Sau khi xem xong biểu đồ, ta có thể biết ngay được đáp án.
Kết luận: Trong ví dụ này, khi sử dụng hàm chúng tôi ta có thể sử dụng giá trị thứ 4 làm đối số thứ tư.
FORECAST SHEET
Ta có thể sử dụng công cụ FORECAST SHEET trong Excel 2016 hoặc sau đó để tự động tạo các trang trính dự báo trực quan.
Bước 1. Chọn dải ô A1:B13 bên trên.
Bước 2. Trên tab Data, chọn nhóm Forecast, nhấn chọn Forecast Sheet.
Lúc này Excel sẽ chạy hộp thoại được hiển thị như hình bên dưới.
Bước 3. Xác định thời điểm kết thúc dự báo và thiết lập Confidence interval (ở chế độ mặc định là 95%). Tích chọn Detect Automatically để tìm ra thời kỳ tự động hoặc tích chọn Set Manually để thiết lập thủ công.
Công cụ này sử dụng hàm chúng tôi và tính ra giá trị tương lai giống nhau.
Giải thích: Tại kỳ thứ 13, ta có thể nắm chắc 95% rằng số lượt khách sẽ nằm giữa 86 và 94.
Với sứ mệnh: ” Mang cơ hội phát triển kỹ năng, phát triển nghề nghiệp tới hàng triệu người “, đội ngũ phát triển đã và đang làm việc với những học viện, trung tâm đào tạo, các chuyên gia đầu ngành để nghiên cứu và xây dựng lên các chương trình đào tạo từ cơ bản đến chuyên sâu xung quanh các lĩnh vực: Tin học văn phòng, Phân tích dữ liệu, Thiết kế, Công nghệ thông tin, Kinh doanh, Marketing, Quản lý dự án…
Gitiho tự hào khi được đồng hành cùng:
50+ khách hàng doanh nghiệp lớn trong nhiều lĩnh vực như: Vietinbank, Vietcombank, BIDV, VP Bank, TH True Milk, VNPT, FPT Software, Samsung SDIV, Ajinomoto Việt Nam, Messer,…
Cập nhật thông tin chi tiết về Hướng Dẫn Sử Dụng Hàm Index Tạo Biểu Đồ Tương Tác Trong Excel 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!