Bạn đang xem bài viết Dùng Hàm Vlookup Tìm Dữ Liệu Trong Nhiều Sheet đượ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.
Tên hàng là Lookup_Value trong hàm Vlookup.
Tháng là Lookup_Array trong hàm Vlookup.
Ở đây bạn có thể thấy yêu cầu của bài toán được xét trong 3 bảng: tháng 1, tháng 2 và tháng 3. Ba bảng này có thể sẽ nằm cùng một sheet hoặc khác sheet nhau. Nêu bạn muốn thay đổi lookup_array thường sẽ phải sử dụng hàm IF.
2. CÁCH SỬ DỤNG HÀM IF
Các bảng sẽ có tọa độ lần lượt là:
Bảng 1: A2:B5
Bảng 2: D2:E5
Bảng 3: G2:H5
Logic của việc thay đổi này sẽ như sau:
Nếu B8 là Tháng 1 thì tham chiếu tới vùng A2:B5
Nếu B8 là Tháng 2 thì tham chiếu tới vùng D2:E5
Nếu B8 là Tháng 3 thì tham chiếu tới vùng G2:H5
Như vậy thì từ logic trên bạn có thể viết hàm IF như sau:
IF(B8=”Tháng 1″,A2:B5,IF(B8=”Tháng 2″,D2:E5,IF(B8=”Tháng 3″,G2:H5,””)))
Khi đặt hàm Vlookup tại vị trí Table_Array bạn sẽ nhận được có kết quả như sau:
3. CÁCH SỬ DỤNG HÀM SWITCH THAY CHO HÀM IF
Hàm SWITCH được xem là hàm mới, thường được dùng thay cho hàm IF. Trong trường hợp này, bạn có thể sử dụng hàm này như bên dưới:
= SWITCH(B8,B1,A2:B5,E1,D2:E5,H1,G2:H5)
B8: vị trí làm căn cứ xác định cho việc thay đổi bảng tham chiếu.
B1: đối tượng tham chiếu đầu tiên
A2:B5: vùng tham chiếu đầu tiên. Nếu như B8=B1 thì sẽ tham chiếu tới vùng này.
E1: đối tượng tham chiếu thứ hai
D2:E5: vùng tham chiếu thứ hai. Nếu như giá trị ở B8=E1 thì sẽ tham chiếu tới vùng này.
H1: đối tượng tham chiếu thứ ba
G2:H5: vùng tham chiếu thứ ba. Nếu như giá trị ở B8=H1 thì sẽ tham chiếu tới vùng này.
Như vậy có thể căn cứ vào giá trị ở B8, bạn sẽ tùy chọn được vùng tham chiếu mà không cần phải tới logic như hàm IF.
Kết quả thu được sẽ như sau:
Cả 2 cách trên đều cho ra kết quả giống nhau. Tuy nhiên những đối tượng hàm SWITCH sẽ viết dễ và gọn hơn so với hàm IF. Việc phát triển hàm SWITCH cũng giống như hàm SUMIFS, COUNTIFS vậy.
Đánh giá bài viết
Cách Dùng Hàm Match Để Tìm Vị Trí Của Dữ Liệu Trong Excel
Nếu như bạn đang làm việc với Microsoft Excel, bạn có thể sử dụng hàm MATCH để xác định vị trí của một giá trị trong một hàng, cột hay bảng dữ liệu. Hàm này có nhiệm vụ sẽ trả về một số chỉ vị trí tương đối đầu tiên của dữ liệu trong danh sách, mảng hoặc phạm vi ô được chọn. Đây là một hàm dùng để tra cứu và tham chiếu dữ liệu trong Excel.
Ghi chú: Hướng dẫn trong bài viết này áp dụng cho Excel 2019, 2016, 2013, 2010, và 2007; Excel cho Microsoft 365; Excel Online; Excel cho Mac; Excel cho iPad; Excel cho iPhone; và Excel cho Android.
Tại sao bạn cần sử dụng hàm MATCH trong Excel?
Trong các bảng tính lớn, đôi khi sẽ vô cùng phức tạp để tìm kiếm một giá trị cụ thể. Mặc dù Excel (và các ứng dụng Office khác) cũng hỗ trợ cung cấp chức năng tìm kiếm nhưng đôi khi vẫn không thể mang lại kết quả chính xác. Nếu bạn muốn biết một giá trị cụ thể đang nằm ở vị trí thứ mấy trong bảng dữ liệu và sau đó sẽ chuyển dữ liệu này sang một công thức Excel khác, bạn cần sử dụng chức năng của hàm MATCH trong Excel.
Kết quả tìm kiếm được hiển hị ở dạng vị trí tương đối và luôn đề cập đến vị trí của khu vực tìm kiếm. Bạn có thể tìm kiếm giá trị trong một cột hoặc một hàng. Kết quả sẽ cho thấy có bao nhiêu giá trị đếm từ phía bên trái (cho một hàng) hoặc đếm từ phía trên xuống (cho một cột) của tiêu chí tìm kiếm được lựa chọn.
Công thức hàm MATCH trong Excel
Cú pháp của hàm để cập đến cách bố trí của hàm và được bao gồm tên, dấu ngoặc, dấu tách, dấu phẩy và các đối số của hàm. Công thức của hàm MATCH là:
=MATCH(Lookup_value,Lookup_array,Match_type)Lookup_value (bắt buộc): Là giá trị mà bạn muốn tìm kiếm trong danh sách dữ liệu. Đối số này có thể là một số, văn bản, giá trị logic hay tham chiếu ô.
Lookup_array (bắt buộc): Phạm vi các ô đang được tìm kiếm
Match_type (tùy chọn): Cho Excel biết cách tra cứu dữ liệu của Lookup_value với những giá trị trong Lookup_array. Giá trị mặc định của đối số này là 1. Người dùng có các lựa chọn là -1, 0 hoặc 1.
Nếu đối số Match_type bằng 1 hoặc bị bỏ qua. hàm MATCH sẽ tìm giá trị lớn nhất thỏa điều kiện nhỏ hơn hoặc bằng đối số Lookup_value. Dữ liệu Lookup_array sẽ được sắp xếp theo thứ tự tăng dần.
Nếu đối số Match_type bằng 0 thì nó sẽ tìm giá trị chính xác bằng với đối số Lookup_value. Dữ liệu Lookup_array có thể được sắp xếp theo bất kỳ thứ tự nào.
Nếu đối số Match_type bằng -1, hàm MATCH sẽ tìm giá trị nhỏ nhất phù hợp với điều kiện nhỏ hơn hoặc bằng đối số Lookup_value. Dữ liệu Lookup_array sẽ được sắp xếp theo thứ tự giảm dần.
Hàm MATCH trả về số vị trí của giá trị tra cứu trong một phạm vi hoặc bảng dữ liệu.
Nói cách khác, Excel sẽ tìm kiếm giá trị được người dùng cung cấp trong phạm vi cụ thể (Lookup_Array), sau đó trả về số vị trí của nó được tính từ đầu danh sách.
Chú ý: Hãy nhớ rằng bạn luôn bắt đầu các công thức hàm trong Excel bằng dấu bằng (=). Ngoài việc nhập công thức trực tiếp vào ô của bảng tính trong Excel, bạn cũng có thể nhập công thức hàm Excel bằng hộp thoại chức năng.
Cách sử dụng hàm MATCH trong Excel
Để có thể nhập hàm MATCH và các đối số:
Bước 1: Bạn mở một bảng tính Excel mới và nhập dữ liệu vào các cột A, B, C (như hình ảnh bên dưới). Để trống ô B2, vì ô đó sẽ là ô bạn nhập hàm MATCH vào.
Bước 3: Chọn tab Formulas từ Menu chính.
Bước 4: Chọn Lookup & Reference để mở danh sách hộp thoại chức năng.
Bước 5: Chọn MATCH trong danh sách để mở hộp thoại Function Arguments (Trong Excel cho Mac thì Formula Builder cũng sẽ được mở ra).
Bước 7: Chọn ô A2 trong bảng tính để nhập tham chiếu ô.
Bước 8: Tiếp theo, bạn hãy đặt con trỏ vào mục Lookup_array.
Bước 9: Tô đen các ô từ C2 đến C7 trong bảng tính để chọn làm phạm vi tham chiếu dữ liệu.
Bước 10: Cuối cùng, đặt con trỏ chuột vào hộp văn bản Match_type.
Bước 11: Nhập số 0 vào đối số này để tìm kết quả khớp chính xác với dữ liệu trong ô B2.
Bước 13: Kết quả là 5 sẽ xuất hiện trong ô B2 vì cụm từ “Máy giặt” đứng thứ năm trong danh sách.
=MATCH(A2,C2:C7,0)
Hàm MATCH trong Excel thường được sử dụng cùng với các hàm tra cứu dữ liệu khác, chẳng hạn như hàm VLOOKUP và hàm INDEX và được sử dụng làm đầu vào cho các đối số khác, ví dụ như:
Làm đối số col_index_num của hàm VLOOKUP.
Làm đối số row_num của hàm INDEX.
Hẹn gặp lại bạn trong những bài viết sắp tới trên website của chúng tôi.
Pivottable Nâng Cao: Kết Hợp Dữ Liệu Từ Nhiều Sheet
Khi bạn muốn tạo một PivotTable, bạn sẽ làm gì nếu bạn có dữ liệu trong các sheet khác nhau? Nếu bạn đang sử dụng Excel 2013, thì bạn có một phương pháp được tổ chức hợp lý để thực hiện việc này. Có một kỹ thuật được gọi là Mô hình Dữ liệu và nó sử dụng dữ liệu quan hệ theo cách cơ sở dữ liệu làm việc.
Trong bài này, tôi sẽ hướng dẫn cho bạn mọi thứ mà bạn cần để tạo một PivotTable trong Excel 2013 từ dữ liệu trong nhiều sheet, sử dụng Mô hình Dữ liệu.
Video Hướng dẫn
Bạn có thể làm theo hướng dẫn này bằng cách sử dụng tập tin Excel của riêng bạn. Hoặc nếu bạn thích, hãy tải xuống tập tin zip kèm theo hướng dẫn này, nó có chứa một bảng tính mẫu có tên gọi là Pivot Consolidate.xlsx.
Kiểm tra Dữ liệu
Bảng tính này có ba sheet: Customer Info, Order Info và Payment Info.
Nhấp vào sheet Customer Info và xem nó có chứa số đơn hàng và tên và trạng thái của khách hàng.
Nhấp vào bảng Order Info và xem nó có chứa số đơn hàng, cũng như các trường ngày tháng, các sản phẩm được đặt, và các sản phẩm có phải là hữu cơ hay không.
Nhấp vào sheet Payment Info và xem nó có chứa số đơn hàng, giá mỗi đơn hàng, phương thức thanh toán và đơn hàng được đặt bởi một khách hàng mới hay khách hàng cũ.
Bằng cách kết nối tất cả các sheet này trong bảng tác vụ của PivotTable, chúng ta có thể chọn dữ liệu từ mỗi sheet. Vì số đơn hàng tồn tại trong cả ba sheet, nên chúng sẽ trở thành các điểm kết nối. Cơ sở dữ liệu gọi đây là một primary key (khoá chính). Lưu ý rằng không phải lúc nào cũng cần thiết phải có một khóa chính, nhưng nó làm giảm khả năng xảy ra lỗi.
Tạo bảng
Trước khi tạo ra PivotTable, chúng ta hãy tạo ra một bảng từ mỗi sheet.
Quay trở lại Bảng Khách hàng, sau đó nhấp vào bất kỳ nơi nào trong vùng dữ liệu. Chuyển đến tab Insert trên thanh ribbon, sau đó nhấp vào biểu tượng Table.
Hộp thoại Create Table xác định chính xác khu vực của bảng. Hộp đánh dấu ở phía dưới cũng sẽ xác định rằng hàng đầu tiên của bảng là dành cho tiêu đề. (Nếu không, hãy chọn tùy chọn đó.)
Hộp thoại Create Table sẽ lấy chính xác vùng dữ liệu
Nhấp OK, và bây giờ bạn có một bảng với các nút lọc dữ liệu. Bạn có thể nhấp vào bên trong nó để bỏ chọn, nếu bạn muốn xem nó rõ hơn (chỉ cần không bấm bên ngoài bảng). Thanh ribbon cũng hiển thị tab Design cho bảng. Ở phía bên trái của dải ribbon, hộp Table Name hiển thị tên tạm thời của Table 1. Xóa nó, và gọi nó là Customer_Info (sử dụng một dấu gạch dưới thay vì một khoảng trắng). Sau đó nhấn Enter.
Áp dụng một cái tên cho mỗi bảng
Lặp lại quy trình này trên sheet Order Info và Payment Info. Đặt tên các bảng tương ứng là Order_Info và Payment_Info.
Bây giờ chúng ta đã sẵn sàng để thêm PivotTable.
Chèn PivotTable
Trên sheet Payment Info, đảm bảo con trỏ ở đâu đó trong bảng. Quay trở lại tab Insert trên ribbon, và nhấp vào biểu tượng PivotTable (đó là biểu tượng đầu tiên).
Hộp thoại xuất hiện sẽ xác định bảng phù hợp và chọn PivotTable sẽ nằm trong một bảng tính mới. Ở phía dưới cùng, nhấp vào hộp đánh dấu Add this data to the Data Model. Sau đó bấm OK.
Adding the data to the Data Model là thứ cho phép các kết nối hoạt động
Bạn sẽ có một PivotTable trên một sheet mới, sẽ có một cửa sổ tác vụ ở phía bên phải màn hình, và thanh ribbon sẽ hiển thị tab Analyze.
Cửa sổ tác vụ hiển thị bảng và các trường chỉ cho sheet hiện tại, do đó hãy nhấp vào ALL để thấy tất cả các bảng mà bạn đã tạo. Nhưng trước khi có thể sử dụng chúng, chúng ta phải kết nối chúng lại với nhau, và điều đó có nghĩa là tạo ra các mối quan hệ. Nhấp vào nút Relationships trên thanh ribbon.
Thiết lập Quan hệ Bảng
Nhấp vào nút đó sẽ hiển thị hộp thoại Manage Relationships. Nhấp vào nút New và hiển thị hộp thoại Create Relationships. Chúng ta sẽ tạo ra hai mối quan hệ bằng cách sử dụng trường Order # làm yếu tố kết nối.
Từ danh sách sổ xuống, chọn Payment_Info cho table, và bên cạnh nó, chọn Order # từ trình đơn thả xuống Column. Trên hàng thứ hai, chọn Customer_Info từ danh sách sổ xuống Related Table và bên cạnh nó, chọn Order # từ danh sách Related Column.
Có ba bảng, từ đó tạo ra hai mối quan hệ
Nhấp vào OK và chúng ta thấy rằng mối quan hệ được liệt kê trong hộp Manage Relationships.
Lặp lại quá trình này để tạo ra một mối quan hệ kết nối Payment_Info với Order_Info, tương tự sử dụng trường Order #. Hộp Manage Relationships bây giờ sẽ giống như sau:
Hộp thoại Manage Relationships sẽ hiển thị các mối quan hệ mà bạn tạo ra
Lưu ý rằng không cần phải tạo mối quan hệ giữa bảng Order_Info và Customer_Info, vì chúng được tự động kết nối thông qua bảng Payment_Info.
Nhấp vào nút Close ở dưới cùng của cái hộp. Bây giờ cuối cùng chúng ta có thể kéo các trường vào bảng PivotTable.
Chèn các Trường vào Bảng PivotTable
Trong phần ALL của bảng tác vụ, nhấp vào các mũi tên nhỏ để thấy cả ba bảng, để bạn thấy các trường của chúng. Kéo các trường vào các vùng PivotTable như sau:
Bây giờ bạn có thể sử dụng và sửa đổi điều này giống như bất kỳ PivotTable khác.
Phần tóm tắt
Nếu bạn đang tìm kiếm các cách tối ưu để trình bày dữ liệu của mình, thì Envato Market có nhiều template Excel và PowerPoint, cũng như các script và ứng dụng để chuyển đổi dữ liệu Excel sang các định dạng thân thiện với web và ngược lại.
Hướng Dẫn Cách Dùng Hàm Vlookup Giữa 2 Sheet Đơn Giản Nhất
Cách dùng hàm Vlookup giữa 2 sheet
Hàm Vlookup thường được dùng trong Excel để truy xuất dữ liệu dựa trên các điều kiện thỏa mãn cho trước. Hàm cho phép người sử dụng hỗ trợ tìm kiếm dữ liệu liên kết từ 2 hay nhiều sheet khác nhau và rộng hơn là các file khác nhau. Việc này sẽ giúp người dùng thuận lợi hơn trong việc liên kết các file, quản lý cũng như tính toán dễ dàng hơn đối với các file cũ mà không cần thiết phải làm lại dữ liệu.
Giả sử bạn có bảng dữ liệu: Danh sách nhân viên trong Sheet 1.
Bảng dữ liệu mã chức vụ và tiền lương ứng với vị trí Sheet 2.
Yêu Cầu: Dò tìm mã vị trí ở bảng dữ liệu trong Sheet 1 với mã vị trí ở bảng dữ liệu trong Sheet 2. Nếu giống nhau thì trả về tiền lương tương ứng.
Để dò tìm giá trị của một cột bạn cần sử dụng hàm VLOOKUP để dò tìm. Tuy nhiên bây giờ 2 bảng dữ liệu chính và bảng chứa kết quả dò tìm nằm ở 2 sheet khác nhau, vậy làm thế nào để có thể kết nối dữ liệu của 2 bảng ở 2 sheet khác nhau được?
Đầu tiên các bạn vẫn áp dụng hàm dò tìm VLOOKUP cho ô giá trị E4 trong bảng DANH SÁCH NHÂN VIÊN. Công thức cho ô giá trị E4 như sau:
+ Lookup_value: giá trị dò tìm. Ở đây bạn sẽ chọn cột D4. Nhưng vì đây là ô dữ liệu nằm trong Sheet1 nên bạn sẽ nhập là Sheet1!D4. Vì vậy công thức bây giờ là =VLOOKUP(Sheet1!D4)
+ Table_array: bảng dò tìm. Bạn mở qua sheet2 và chọn bảng dò tìm ở Sheet2. Từ A4:B8 là vùng giá trị. Nhưng đây là bảng giá trị ở Sheet2 nên bạn sẽ phải nhập là Sheet2!A4:B8. Thêm ký tự $ để cố định bảng dò tìm này lại. Vậy công thức bây giờ sẽ là =VLOOKUP (Sheet1!D4, Sheet2!$A$4:$B$8)
+ Col_index_num: cột chứa giá trị tham chiếu. Ở đây là cột Lương là cột thứ 2 của bảng lương nên bạn nhập là 2. Công thức bây giờ sẽ là =VLOOKUP(Sheet!D4,Sheet2!$A$4:$B$8,2)
+ Range_lookup: kết quả trả về chúng ta cần phải chính xác với dữ liệu trong bảng dò tìm cho nên Range_lookup sẽ là 0. Công thức ở đây sẽ là: =VLOOKUP(Sheet1!D4, Sheet2!$A$4:$B$8,2,0)
Kết quả thu được ta có bảng như sau:
Sao chép công thức cho các ô còn lại bằng cách di chuột xuống dưới, ta có kết quả cuối cùng là:
Vậy để lấy giá trị từ các Sheet khác ta sẽ nhập ô giá trị hoặc mảng dữ liệu tham chiếu để kết nối dữ liệu với các sheet khác nhau.
Bài viết này giới thiệu với các bạn một thủ thuật nhỏ khi kết hợp dữ liệu giữa các Sheet khác nhau bằng cách nhập ô giá trị hoặc mảng dữ liệu tham chiếu vào công thức tính toán thay vì chỉ nhập ô giá trị hoặc mảng dữ liệu đơn thuần. Ngoài ra, đối với bảng tính chứa nhiều dữ liệu thì việc trùng lặp dữ liệu là việc khó tránh khỏi. Vì thế nếu như bạn đọc đang gặp phải tình huống như vậy thì bằng chúng tôi khuyên các bạn là nên sử dụng công cụ tích hợp sẵn trong Excel để lọc dữ liệu trùng trong Excel trước đó.
Cập nhật thông tin chi tiết về Dùng Hàm Vlookup Tìm Dữ Liệu Trong Nhiều Sheet 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!