HÀM TRUY XUẤT DỮ LIỆU TRONG EXCEL

Mang trong mình những đặc điểm tương tự với VLOOKUP nên các hạn chế của hàm HLOOKUP được nêu ra như sau.

Nó sẽ không thể kéo được hàng ở trên hàng đầu tiên trong bảng, và khi bạn xóa đi bất kỳ một hàng nào trong bảng trích xuất dữ liệu sẽ ảnh hưởng và phá vỡ công thức của nó.

Dùng hàm INDEX và hàm MATCH kết hợp

Hàm trích xuất dữ liệu trong Excel cơ bản như VLOOKUP hay HLOOKUP không được linh hoạt vì một số mặt hạn chế của nó. Thế nên việc kết hợp của 2 hàm INDEX và MATCH sẽ đem lại cho bạn một hàm lấy dữ liệu trong Excel vô cùng mạnh mẽ.

Và sau đây tôi sẽ hướng dẫn các bạn cách sử dụng công thức này để tra cứu từ phải sang trái, điều mà hàm VLOOKUP không thể làm được.

Nhưng trước tiên phải nói sơ qua về công thức và cách sử dụng riêng biệt của 2 hàm này trước rồi sau đó chúng ta sẽ đi tới việc sử dụng kết hợp 2 hàm này sẽ như thế nào.

Hàm INDEX – Hàm trích xuất dữ liệu trong Excel

Hàm INDEX trong Excel trả về một giá trị trong một mảng dựa trên số hàng và cột mà bạn chỉ định. Cú pháp của hàm INDEX rất đơn giản.

=INDEX (array, row_num, )

Dưới đây là một lời giải thích rất đơn giản về từng tham số cho công thức ở trên:

Và đây là một ví dụ về công thức INDEX ở dạng đơn giản nhất: =INDEX(A1:C10,2,3)

Công thức tìm kiếm trong các ô từ A1 đến C10 và trả về một giá trị của ô ở hàng thứ 2 và cột thứ 3, tức là ô C2.

Rất dễ dàng, phải không? Tuy nhiên, khi làm việc với dữ liệu thực, bạn sẽ khó biết hàng và cột nào bạn muốn, đó là nơi mà hàm MATCH có ích.

Hàm Match – hàm lấy dữ liệu trong Excel

Hàm MATCH trong Excel tìm kiếm giá trị tra cứu trong một phạm vi ô và trả về vị trí tương đối của giá trị đó trong phạm vi.

Cú pháp của hàm MATCH như sau:

=MATCH (lookup_value, lookup_array, )

Trong đó:

Ví dụ: nếu phạm vi B1: B3 chứa các giá trị “New-York”, “Paris”, “London”, công thức dưới đây trả về số 3, vì “London” là mục nhập thứ ba trong phạm vi:

=MATCH(“London”,B1:B3,0)

Ngay từ cái nhìn đầu tiên, tính hữu dụng của hàm MATCH có vẻ đáng nghi ngờ. Ai quan tâm đến vị trí của một giá trị trong một phạm vi? Những gì chúng ta muốn biết là giá trị của chính nó.

Hãy để tôi nhắc nhở bạn rằng vị trí tương đối của giá trị tra cứu (tức là số hàng và cột) là chính xác những gì bạn cần cung cấp cho các đối số row_num và column_num của hàm INDEX.

Như bạn nhớ, hàm INDEX trong Excel có thể tìm giá trị tại điểm nối của một hàng và cột nhất định, nhưng nó không thể xác định chính xác hàng và cột nào bạn muốn.

Cách sử dụng hàm INDEX và hàm MATCH kết hợp trong Excel

Bây giờ bạn đã biết những điều cơ bản, tôi tin rằng nó đã bắt đầu có ý nghĩa về cách MATCH và INDEX hoạt động cùng nhau. Tóm lại, INDEX tìm giá trị tra cứu theo số cột và hàng, và MATCH cung cấp các số đó. Đó là những gì bạn cần quan tâm để lấy dữ liệu từ bảng này sang bảng khác trong Excel một cách linh hoạt nhất.

Đối với tra cứu theo chiều dọc, bạn chỉ sử dụng hàm MATCH để xác định số hàng và cung cấp phạm vi cột trực tiếp cho INDEX:

=INDEX ( cột để trả về giá trị từ ; MATCH ( giá trị tra cứu ; cột để tra cứu ; 0 )

Nếu bạn vẫn gặp khó khăn để hiểu được cách nó hoạt động ra sao? Sẽ dễ hiểu hơn từ một ví dụ. Giả sử bạn có một danh sách các thủ đô quốc gia và dân số của họ.

Để tìm dân số của một thủ đô nhất định, chẳng hạn như thủ đô của Nhật Bản (Japan), hãy sử dụng công thức sau:

=INDEX(C2:C10, MATCH(“Japan”, A2:A10, 0))

Bây giờ, hãy phân tích những gì mỗi thành phần của công thức này thực sự làm:

Xem thêm: " Đêm Chưa Ngủ Nghe Ngoài Trời Đổ Mưa Quang Le, Đêm Chưa Ngủ Nghe Ngoài Trời

Vì vậy, công thức trên biến thành một INDEX đơn giản là INDEX(C2:C10,3) cho biết tìm kiếm trong các ô từ C2 đến C10 và kéo giá trị từ ô thứ 3 trong phạm vi đó, tức là C4 vì chúng ta bắt đầu đếm từ hàng thứ hai.

Bạn muốn tra dân số theo các thành phố khác nhau mà không cần phải thay đổi trong công thức? Nhập nó vào một ô, chẳng hạn F1, cung cấp tham chiếu ô cho MATCH và bạn sẽ nhận được công thức tra cứu động:

=INDEX(C2:C10, MATCH(F1,A2:A10,0))

Lưu ý quan trọng: Số hàng trong đối số mảng của INDEX phải khớp với số hàng trong đối số lookup_array của MATCH, nếu không công thức sẽ tạo ra kết quả không chính xác.

Chờ đã, đợi đã… tại sao chúng ta không sử dụng công thức Vlookup sau đây? Có ích gì khi lãng phí thời gian khi cố gắng tìm ra những điểm khó hiểu của hàm Excel là MATCH và INDEX?

Khi quyết định sử dụng hàm nào để tra cứu theo chiều dọc, hầu hết các chuyên gia Excel đều đồng ý rằng hàm INDEX MATCH tốt hơn nhiều so với hàm VLOOKUP.

Tuy nhiên, nhiều người vẫn ở lại với hàm VLOOKUP, thứ nhất là vì nó đơn giản hơn và thứ hai, vì họ không hiểu đầy đủ tất cả các lợi ích của việc sử dụng công thức INDEX MATCH trong Excel. Nếu không có sự hiểu biết như vậy, không ai sẵn sàng đầu tư thời gian của họ để học một cú pháp phức tạp hơn.

Dưới đây, tôi sẽ chỉ ra những ưu điểm chính của MATCH INDEX so với VLOOKUP và bạn quyết định xem đó có phải là một bổ sung xứng đáng cho kho vũ khí Excel của bạn hay không.

1. Tra cứu từ phải sang trái.

2. Chèn hoặc xóa cột một cách an toàn.

3. Không có giới hạn cho kích thước của giá trị tra cứu.

4. Tốc độ xử lý cao hơn.

Tác động của VLOOKUP đối với hiệu suất của Excel có thể đặc biệt đáng chú ý nếu sổ làm việc của bạn chứa các công thức mảng phức tạp như VLOOKUP và SUM .

Vấn đề là việc kiểm tra từng giá trị trong mảng yêu cầu một lệnh gọi riêng của hàm VLOOKUP. Vì vậy, mảng của bạn càng chứa nhiều giá trị và càng có nhiều công thức mảng trong sổ làm việc, thì Excel càng hoạt động chậm hơn.

Lấy dữ liệu từ bảng này sang bảng khác trong Excel khác trang tính

Việc trong một tệp bảng tính Excel có thể có rất nhiều các trang tính khác nhau, việc bạn muốn lấy dữ liệu từ bảng này sang bảng khác trong Excel tại các trang tính khác nhau là tương đối mất một chút thao tác hơn với việc áp dụng các công thức ở trên và thêm một hàm trích xuất dữ liệu trong Excel là ‘Tên trang tính’! kèm theo sau đó là địa chỉ dải ô bạn muốn lấy dữ liệu.

Giả sử bạn có hai bảng trong hai trang tính khác nhau. Bảng chính chứa tên người bán (Seller) và sản phẩm (Product), và bảng tra cứu chứa tên (Seller) và số tiền (Amount).

Bạn muốn dùng hàm lấy dữ liệu từ bảng này sang bảng khác trong Excel thì có thể làm như sau, theo ví dụ sau.

Như bạn thấy, thứ tự của các tên trong bảng chính không tương ứng với thứ tự trong bảng tra cứu, do đó, một kỹ thuật sao chép, dán đơn giản sẽ không hoạt động.

Để kết hợp hai bảng theo một cột phù hợp là Seller, bạn nhập công thức này vào ô C2 trong bảng chính (Main table):

=VLOOKUP($A2 , ‘Lookup table’!$A$2:$B$10 , 2 , FALSE)

Trong đó:

Sao chép công thức xuống cột và bạn sẽ nhận được một bảng đã hợp nhất bao gồm bảng chính, cộng với dữ liệu phù hợp được lấy từ bảng tra cứu:

Lấy dữ liệu từ bảng này sang bảng khác trong Excel bằng Power Query

Trong các tình huống khi bạn cần kết hợp hai hoặc nhiều bảng với số lượng hàng và cột khác nhau, Excel Power Query có thể hữu ích.

Tuy nhiên, xin lưu ý rằng việc tham gia các bảng với Power Query không thể được thực hiện chỉ với một vài cú nhấp chuột. Giải thích tất cả các sắc thái sẽ tốn nhiều không gian hơn chúng ta có ở đây, vì vậy tôi sẽ chỉ phác thảo ngắn gọn các tính năng chính:

Ví dụ ta có một nguồn dữ liệu sau.

Nhiệm vụ của chúng ta là trích xuất dữ liệu trong bảng 1 với các bản ghi có liên quan từ hai bảng còn lại và kết hợp tất cả dữ liệu vào một bảng mới như thế này.

1. Tạo kết nối Power Query

Không làm lộn xộn sổ làm việc của bạn với các bản sao của bảng gốc, chúng tôi sẽ chuyển đổi chúng thành các kết nối, thực hiện phối trong Trình soạn thảo Power Query, rồi chỉ tải bảng kết quả.

Để lưu bảng dưới dạng kết nối trong Power Query, đây là những gì bạn làm:

1. Chọn bảng đầu tiên của bạn Orders (Đơn đặt hàng) hoặc bất kỳ ô nào trong bảng đó.

2. Đi tới tab Data > Nhóm Get & Transform Data và bấm From Table/Range.

3. Trong Trình soạn thảo Power Query mở ra, nhấp vào mũi tên thả xuống Close & Load và chọn tùy chọn Close & Load…

4. Trong hộp thoại Import Data, hãy chọn tùy chọn Only Create Connection và bấm OK.

5. Lặp lại các bước trên cho tất cả các thực thể khác mà bạn muốn hợp nhất (thêm hai bảng, Products và Commissions, trong trường hợp của chúng tôi).

Khi hoàn tất, bạn sẽ thấy tất cả các kết nối trên ngăn:

2. Hợp nhất hai kết nối vào một bảng

Với các kết nối tại chỗ, hãy xem cách bạn có thể nối hai bảng thành một:

1. Trên tab Data, hãy bấm nút Get Data, chọn Combine Queries trong danh sách thả xuống và bấm Merge.

2. Trong hộp thoại Phối, hãy làm như sau:

Sau khi hoàn thành các bước trên, Trình soạn thảo Power Query sẽ hiển thị bảng đầu tiên của bạn (Orders) với một cột bổ sung có tên như bảng thứ hai của bạn (Products) được thêm vào cuối. Cột bổ sung này chưa có bất kỳ giá trị nào, chỉ có từ “Table” trong tất cả các ô. Nhưng đừng cảm thấy nản lòng, bạn đã làm mọi thứ đúng, và chúng tôi sẽ khắc phục điều đó ngay sau đây.

3. Chọn các cột cần thêm từ bảng thứ hai

Tại thời điểm này, bạn có một bảng giống như bảng trong ảnh chụp màn hình bên dưới. Để hoàn tất quá trình hợp nhất, hãy thực hiện các bước sau trong Trình soạn thảo Power Query.

1. Trong cột đã thêm (Products), nhấp vào mũi tên hai mặt trong tiêu đề.

2. Trong hộp mở ra, hãy làm như sau:

Kết quả là, bạn sẽ nhận được một bảng mới chứa mọi bản ghi từ bảng đầu tiên của bạn và (các) cột bổ sung từ bảng thứ hai.

Nếu bạn chỉ cần hợp nhất hai bảng, bạn có thể xem xét công việc gần như đã hoàn thành và tải bảng kết quả trong Excel.

Trên đây là hầu hết các thủ thuật giúp bạn lấy dữ liệu từ bảng này sang bảng khác trong Excel. Bây giờ đây, mọi thứ về các hàm trích xuất dữ liệu trong Excel hay bất kỳ điều gì khác liên quan tới nó cũng không khiến bạn phải loay hoay suy nghĩ nên làm gì và tìm cách ở đâu nữa.

Chúc bạn thành công với những hướng dẫn cách lấy dữ liệu từ bảng này sang bảng khác trong Excel mà hocketoanthue.edu.vn đã giới thiệu ở trên nhé.

Nếu bạn đang muốn trao dồi thêm cho mình những kỹ năng và thủ thuật Excel có thể tham khảo một số bài viết dưới đây:

Nếu thấy bài viết hướng dẫn cách lấy dữ liệu từ bảng này sang bảng khác trong Excel này hữu ích hãy để lại Like & Share cũng như bình luận ở phần bên dưới nếu có bất kỳ thắc mắc cần giải đáp nào khác.

Cảm ơn đã theo dõi và đừng quên ghé thăm thường xuyên trang hocketoanthue.edu.vn để tìm hiểu thêm nhiều kiến thức và hướng dẫn hữu ích khác nữa nhé.