Lấy dữ liệu từ bảng này sang bảng khác trong Excel từ cơ bản đến nâng cao

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

Đối với những dữ liệu trong cùng một trang tính, bạn có nhiều hàm để lấy dữ liệu từ bảng này sang bảng khác trong Excel dễ dàng. Cùng tìm hiểu từng hàm trích xuất dữ liệu trong Excel sau đây nhé.

Nó có thể rất dài dòng và điều này là không thể tránh khỏi vì chúng tôi phải giới thiệu và trình bày cách sử dụng cho từng hàm trong Excel.

Dùng hàm VLOOKUP

Nếu bạn muốn lấy dữ liệu từ bảng này sang bảng khác trong Excel trên cùng trang tính dựa vào một cột làm tham số đối chiếu, thì hàm VLOOKUP nên được sử dụng trong tình huống này.

Cú pháp của hàm VLOOKUP như sau: =VLOOKUP(value ; table ; col_index ; [range_lookup])

Trong đó:

• value: Giá trị cần tìm để đối chiếu trong cột đầu tiên của bảng cần lấy giá trị.
• table: Bảng dùng để lấy dữ liệu.
• col_index: vị trí số cột trong bảng cần lấy dữ liệu.
• range_lookup [tùy chọn có hoặc không]: Nếu dùng TRUE đối chiếu sẽ là gần đúng, còn nếu dùng FALSE đối chiếu sẽ là chính xác.

Khi bạn sử dụng hàm VLOOKUP, hãy tưởng tượng rằng mọi cột trong bảng đều được đánh số, bắt đầu từ bên trái qua. Để nhận giá trị từ một cột cụ thể, hãy cung cấp số thích hợp làm “chỉ mục cột”. Ví dụ, chỉ mục cột để truy xuất tên bên dưới nằm ở cột số 2 trong bảng.

Kết quả lấy dữ liệu từ bảng này sang bảng khác trong Excel khi dùng hàm VLOOKUP được giải thích như sau:

Trong bảng bên phải, khi bạn muốn lấy tên từ người có mã số ID là 622, hàm VLOOKUP sẽ sử dụng giá trị trong ô H3 là “622” đem qua bảng bên trái (B4:E13) và đối chiếu với cột đầu tiên của nó (cột ID) tìm đến hàng có giá trị trùng với 622 là ô B8.

Sau khi đã xác định được hàng có giá trị đối chiếu chính xác tương ứng, tiếp theo VLOOKUP sẽ lấy dữ liệu từ cột thứ 2 trong bảng (cột First) bên phải ô có giá trị “622” (B8) là “Jonathan” theo kiểu so sánh chính xác.

Họ (Last) và Email có thể được truy xuất với theo công thức như trên nhưng cột lấy dữ liệu sẽ thay bằng 3 và 4 tương ứng.

Xin lưu ý rằng hàm VLOOKUP trong Excel có một số hạn chế, trong đó quan trọng nhất là không thể kéo dữ liệu từ cột bên trái cột tra cứu và số cột sẽ bị phá vỡ khi bạn thêm hoặc xóa các cột trong bảng tra cứu dùng trong công thức VLOOKUP.

Mặt khác, bạn có thể dễ dàng sắp xếp lại các cột được trả về chỉ bằng cách thay đổi số trong đối số col_index.

Dùng hàm HLOOKUP

Đối với những bảng dữ liệu nằm ngang, để lấy dữ liệu từ bảng này sang bảng khác trong Excel cũng được thực hiện tương tự như hàm VLOOKUP ở trên như khác ở chỗ là thay thế nó bằng một hàm tên là HLOOKUP.

Cú pháp tổng quát là: =HLOOKUP(value ; table ; row_index ; [range_lookup])

Trong đó:

• value: Giá trị cần đối chiếu để lấy dữ liệu.
• table: Bảng dữ liệu cần truy xuất.
• row_index: Số hàng để lấy dữ liệu.
• range_lookup [tùy chọn có hoặc không]: giá trị TRUE hoặc FALSE tương ứng với đối chiếu gần đúng hoặc chính xác. Mặc định nếu bạn không nhập trường này sẽ là TRUE.

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, [column_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:

• array: một dải ô mà bạn muốn trả về giá trị.
• row_num: số hàng trong mảng mà bạn muốn trả về một giá trị. Nếu bỏ qua, thì column_num là bắt buộc.
• column_num: số cột trong mảng mà bạn muốn trả về một giá trị. Nếu bỏ qua, row_num là bắt buộc.

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, [match_type])

Trong đó:

• lookup_value: số hoặc giá trị văn bản bạn đang tìm kiếm.
• lookup_array: một dải ô đang được tìm kiếm.
• match_type: chỉ định trả về một kết quả khớp chính xác hay một kết quả khớp gần nhất.
  • 1 hoặc bị bỏ qua – tìm giá trị lớn nhất nhỏ hơn hoặc bằng giá trị tra cứu. Yêu cầu sắp xếp mảng tra cứu theo thứ tự tăng dần.
  • 0 – tìm giá trị đầu tiên chính xác bằng giá trị tra cứu. Trong kết hợp INDEX / MATCH, hầu như bạn luôn cần một kết hợp chính xác, vì vậy bạn đặt đối số thứ ba của hàm MATCH thành 0.
  • -1 – tìm giá trị nhỏ nhất lớn hơn hoặc bằng giá trị lookup_value. Yêu cầu sắp xếp mảng tra cứu theo thứ tự giảm dần.

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:

• Hàm MATCH tìm kiếm giá trị tra cứu “Japan” trong phạm vi A2:A10 và trả về số 3, vì “Japan” đứng thứ ba trong mảng tra cứu.
• Số hàng chuyển trực tiếp đến đối số row_num của INDEX hướng dẫn nó trả về giá trị từ hàng đó.

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.

• Như bất kỳ người dùng thông thạo nào cũng biết, hàm VLOOKUP không thể lấy dữ liệu sang bên trái, có nghĩa là giá trị tra cứu của bạn phải luôn nằm ở cột ngoài cùng bên trái của bảng.
• Hàm kết hợp INDEX MATCH có thể thực hiện tra cứu bên trái một cách dễ dàng.

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

• Công thức VLOOKUP bị hỏng hoặc cung cấp kết quả không chính xác khi một cột mới bị xóa hoặc được thêm vào bảng tra cứu vì cú pháp của hàm VLOOKUP yêu cầu chỉ định số chỉ mục của cột mà bạn muốn lấy dữ liệu từ đó. Đương nhiên, khi bạn thêm hoặc xóa các cột, số chỉ mục sẽ thay đổi.
• Với INDEX MATCH, bạn chỉ định phạm vi cột trả về, không phải số chỉ mục. Do đó, bạn có thể tự do chèn và xóa bao nhiêu cột tùy thích mà không cần lo lắng về việc cập nhật mọi công thức liên quan.

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

• Khi sử dụng hàm Vlookup, tổng độ dài của tiêu chí tra cứu của bạn không được vượt quá 255 ký tự, nếu không bạn sẽ có lỗi #VALUE! lỗi. Vì vậy, nếu tập dữ liệu của bạn chứa các chuỗi dài, INDEX MATCH là giải pháp hiệu quả duy nhất.

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

• Nếu các bảng của bạn tương đối nhỏ, sẽ hầu như không có bất kỳ sự khác biệt đáng kể nào về hiệu suất Excel.
• Nhưng nếu trang tính của bạn chứa hàng trăm hoặc hàng nghìn hàng và do đó là hàng trăm hoặc hàng nghìn công thức, MATCH INDEX sẽ hoạt động nhanh hơn nhiều so với hàm VLOOKUP vì Excel sẽ chỉ phải xử lý các cột tra cứu và trả về chứ không phải toàn bộ mảng bảng.

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 đó:

• $A2 là giá trị bạn đang tìm kiếm.
• ‘Lookup table’!$A$2:$B$10 là bảng để tìm kiếm, lưu ý rằng các dấu $ thể hiện địa chỉ tuyệt đối cho ô đó.
• 2 là số cột mà từ đó lấy giá trị.

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:

• Power Query có thể hợp nhất hai bảng bằng cách khớp một hoặc một số cột.
• Các bảng nguồn có thể nằm trên cùng một trang tính hoặc trong các trang tính khác nhau.
• Các bảng ban đầu không được thay đổi. Dữ liệu được kết hợp thành một bảng mới có thể được nhập vào một trang tính hiện có hoặc một trang tính mới.
• Trong Excel 2016 và Excel 2019, Power Query là một tính năng có sẵn. Trong Excel 2010 và Excel 2013, nó có thể được tải xuống dưới dạng bổ trợ.

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:

• Chọn bảng đầu tiên của bạn (Đơn đặt hàng) từ menu thả xuống đầu tiên.
• Chọn bảng thứ 2 (Sản phẩm) từ menu thả xuống thứ hai.
• Trong cả hai bản xem trước, nhấp vào cột phù hợp ( ID đơn hàng) để chọn nó. Cột đã chọn sẽ được tô sáng bằng màu xanh lục.
• Trong danh sách thả xuống Join Kind, để lại tùy chọn mặc định: Left Outer (all from first, matching from second).
• Bấm OK.

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:

• Bỏ chọn tất cả các cột, rồi chỉ chọn (các) cột bạn muốn sao chép từ bảng thứ hai. Trong ví dụ này, chúng tôi chỉ chọn cột Products vì thực thể đầu tiên của chúng tôi đã có Seller và Order ID.
• Bỏ chọn hộp Use original column name as prefix (trừ khi bạn muốn tên cột được tiền tố với tên bảng mà từ đó cột này được lấy).
• Nhấp OK.

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à Phanmemgoc.vn đã giới thiệu ở trên nhé.

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 Phanmemgoc.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é.