Lấy dữ liệu từ bảng này sang bảng khác trong Excel từ A-Z
Lấy dữ liệu từ bảng này sang bảng khác trong Excel cùng trang tính, khác trang tính và bằng Power Query.
Lấy dữ liệu từ bảng này sang bảng khác trong Excel cùng trang tính
Để lấy dữ liệu từ bảng này sang bảng khác trong Excel ở cùng một trang tính, bạn có thể sử dụng một trong những hàm dưới đây. Bạn đừng quá lo lắng nếu thấy phần hướng dẫn sử dụng các hàm này khá dài nhé, tuy vậy khi áp dụng vào thực hành bạn sẽ thấy chúng không phức tạp lắm đâu.
Sử dụng hàm VLOOKUP
Đây là hàm được nhiều người ưu tiên khi cần lấy dữ liệu từ bảng này sang bảng khác trong Excel ở cùng một trang tính vì chỉ cần dựa vào một cột để làm tham số đối chiếu.
Công thức hàm Vlookup là: =VLOOKUP(value;table;col_index;[range_lookup])
Trong đó:
Để lấy dữ liệu từ bảng này sang bảng khác trong Excel bằng hàm Vlookup, hãy tưởng tượng rằng các cột trong bảng dữ liệu đều được đánh số từ trái qua phải.
Khi đó, để nhận giá trị từ một cột nào đó, hãy gán số thích hợp làm “chỉ mục cột”. Bạn có thể nhìn vào ví dụ bên dưới, chỉ mục cột để truy xuất ra tên nằm ở cột số 2 của bảng dữ liệu.
Kết quả khi lấy dữ liệu từ bảng này sang bảng khác trong Excel lúc này sẽ được giải thích cụ thể như dưới đây:
Ở bảng bên phải, khi bạn muốn lấy dữ liệu là tên từ người có mã ID là 622, thì hàm Vlookup sẽ dùng giá trị trong ô H3 là “622” để đem sang bảng bên trái (B4:E13), sau đó đối chiếu với cột đầu tiên của của bảng bên phải (cột ID), tìm đến hàng có giá trị trùng với “622” (chính là ô B8).
Sau khi đã xác định được hàng có chứa giá trị đối chiếu tương ứng rồi, theo kiểu so sánh chính xác, hàm Vlookup sẽ lấy dữ liệu từ cột thứ 2 trong bảng (chính là cột First) ở bên phải ô chứa giá trị 622 là “Jonathan”.
Phần Last và Email sẽ được truy xuất theo công thức tương tự như trên, nhưng cột lấy dữ liệu sẽ cần thay bằng cột 3 và cột 4 để cho tương ứng.
Một điều bạn cần chú ý là hàm Vlookup trong Excel cũng có một số hạn chế, đặc biệt là không thể kéo dữ liệu từ cột phía trái cột tra cứu, đồng thời số cột có thể sẽ bị thay đổi khi bạn thêm/xóa các cột trong bảng tra cứu đã sử dụng trong công thức Vlookup.
Bên cạnh đó, bạn chỉ cần thay đổi số trong phần đối số col_index là bạn đã có thể dễ dàng sắp xếp lại các cột được trả về rồi.
Sử dụng hàm HLOOKUP
Hàm Hlookup chuyên dùng để lấy dữ liệu từ bảng này sang bảng khác trong Excel đối với những bảng dữ liệu nằm ngang. Các bước thực hiện gần như tương tự với hàm Vlookup.
Công thức hàm Hlookup là: =HLOOKUP(value;table;row_index;[range_lookup])
Trong đó:
Do có nhiều đặc điểm tương ứng với Vlookup nên việc thực hiện lấy dữ liệu từ bảng này sang bảng khác trong Excel bằng hàm Hlookup cũng có những hạn chế nhất định: bạn sẽ không thể kéo được hàng ở trên hàng đầu tiên của bảng dữ liệu, khi bạn xóa đi một hàng nào đó trong bảng trích xuất dữ liệu thì công thức của nó sẽ bị phá vỡ.
Sử dụng kết hợp hàm INDEX và hàm MATCH
Như bạn có thể thấy, cách lấy dữ liệu từ bảng này sang bảng khác trong Excel với hàm Vlookup và Hlookup có một số hạn chế nhất định nên không được linh hoạt. Do đó tốt nhất bạn nên sử dụng kết hợp hàm Index và Match để tiện lợi hơn trong quá trình sử dụng Excel.
Dưới đây là những hướng dẫn cụ thể cách sử dụng các công thức này để lấy dữ liệu từ bảng này sang bảng khác trong Excel và tra cứu từ phải qua trái (Vlookup không làm được điều này).
Tuy nhiên trước hết, hãy điểm qua về công thức và cách sử dụng của hai hàm này để hiểu rõ hơn về cách chúng “hoạt động”.
Hàm INDEX – Hàm trích xuất dữ liệu trong Excel
Trong Excel, hàm INDEX có chức năng trả về một giá trị trong một mảng, dựa trên số hàng và số cột mà bạn chỉ định.
Công thức của hàm INDEX: =INDEX(array,row_num,[column_num])
Trong đó:
Hãy cùng phân tích một ví dụ về công thức INDEX rất đơn giản sau đây: =INDEX(A1:C10,2,3)
Nhìn vào công thức này, ta thấy Index đang 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).
Với công thức này bạn có thể thấy việc ứng dụng hàm Index không quá khó. Tuy nhiên với lượng dữ liệu lớn thì việc xác định hàng và cột mong muốn lại trở nên phức tạp hơn. Khi đó bạn có thể sử dụng hàm Match.
Hàm MATCH – Hàm Lấy dữ liệu trong Excel
Trong Excel, hàm MATCH có chức năng tìm kiếm giá trị tra cứu trong một phạm vi ô, sau đó trả về vị trí tương đối của giá trị đó trong phạm vi.
Công thức hàm MATCH như sau: =MATCH(lookup_value,lookup_array,[match_type])
Trong đó:
o 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.
o 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.
o -1 – tìm giá trị nhỏ nhất lớn hơn hoặc bằng giá trị lookup_value. Bạn cần sắp xếp mảng tra cứu theo thứ tự giảm dần.
Ví dụ minh họa: nếu phạm vi B1: B3 chứa các giá trị “New-York”, “Paris”, “London”, công thức dưới đây sẽ trả về số 3, vì “London” là mục nhập thứ ba trong phạm vi:
=MATCH(“London”,B1:B3,0)
Khi nhìn vào công thức và ví dụ hàm Match, bạn có thể thắc mắc vì sao cần quan tâm đến vị trí của một giá trị trong một phạm vi? Thực ra những gì chúng ta cần tìm chính là giá trị của chính nó.
Chúng mình cần nhắc lại rằng vị trí tương đối của số hàng và số cột (hay chính là giá trị tra cứu) chính là những gì bạn cần cung cấp cho các đối số hàng và cột (row_num và column_num) của hàm Index.
Hàm Index có thể tìm được giá trị tại điểm nối của một hàng và một cột xác định, nhưng nó lại không thể tìm được chính xác hàng và cột bạn cần.
Cách kết hợp hàm Index và hàm Match
Vừa rồi bạn đã tìm hiểu những điều cơ bản nhất về hàm Index và Match. Tổng kết lại, hàm Index giúp tìm giá trị tra cứu theo số hàng và số cột, còn hàm Match thì giúp tìm ra các con số đó. Đó là những kiến thức bạn cần nắm được để lấy dữ liệu từ bảng này sang bảng khác trong Excel một cách nhanh chóng nhất.
Đối với việc tra cứu theo chiều dọc, chúng ta chỉ cần khai thác hàm match để tìm ra số hàng và cung cấp phạm vi cột trực tiếp cho hàm Index.
Cụ thể là: =INDEX(cột để trả về giá trị từ;MATCH(giá trị tra cứu;cột để tra cứu;0)
Ví dụ minh họa:
Trong bảng tính Excel có cho một danh sách thủ đô và dân số của các quốc gia.
Để tìm ra dân số của một thủ đô nào đó, ví dụ Japan, bạn chỉ cần sử dụng công thức:
=INDEX(C2:C10, MATCH(“Japan”, A2:A10, 0))
Giải thích công thức:
Như vậy, công thức trên biến thành một công thức đơn giản hơ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 do chúng ta bắt đầu đếm từ hàng thứ hai.
Ngoài ra, nếu bạn cần tra cứu dân số theo các thành phố mà không cần muốn thay đổi công thức? hãy nhập nó vào một ô, chẳng hạn như ô F1, sau đó hãy cung cấp tham chiếu ô cho Match để nhận được công thức tra cứu như sau:
=INDEX(C2:C10, MATCH(F1,A2:A10,0))
Chú ý quan trọng:
Số hàng trong đối số mảng của hàm Index cần đảm bảo phải khớp với số hàng trong đối số lookup_array của hàm Match. Nếu không khớp, công thức chắc chắn sẽ cho ra một kết quả không chính xác.
Tại đây chúng ta không nên sử dụng công thức của hàm Vlookup. Lý do là bởi theo kết quả nghiên cứu và thử nghiệm của hầu hết các chuyên gia Excel, việc sử dụng kết hợp hàm INDEX và MATCH hiệu quả hơn nhiều so với sử dụng hàm VLOOKUP.
Tuy vậy thực tế vẫn có nhiều người thích sử dụng hàm VLOOKUP hơn vì nó khá đơn giản hơn và không quá phức tạp như khi sử dụng công thức kết hợp INDEX MATCH trong Excel. Dù nếu biết rằng cách sử dụng kết hợp hai hàm này hữu ích hơn, vẫn rất ít người tình nguyện bỏ nhiều thời gian ra để học cách sử dụng.
Sau đây, chúng mình sẽ chỉ ra những ưu điểm chính của sự kết hợp MATCH INDEX so với chỉ sử dụng hàm VLOOKUP để bạn có cái nhìn rõ nét nhất. Tuy vậy, việc quyết định sử dụng cách dữ liệu từ bảng này sang bảng khác trong Excel nào vẫn hoàn toàn phụ thuộc vào bạn.
- Tra cứu từ phải sang trái
- Chèn hoặc xóa cột một cách an toàn
- Không có giới hạn cho kích thước của giá trị tra cứu
- Tốc độ xử lý cao hơn
Ngoài ra, tác động của VLOOKUP đối với hiệu suất làm việc của Excel có thể cần chú ý nếu trang tính Excel của bạn có chứa các công thức mảng tương đối phức tạp như là VLOOKUP và SUM .
Tuy nhiên 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. Do đó, nếu 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 sẽ càng hoạt động kém hiệu quả.
Lấy dữ liệu từ bảng này sang bảng khác trong Excel khác trang tính
Trong một số trường hợp khi làm việc, một bảng tính Excel có thể gồm rất nhiều các trang tính khác nhau. Để lấy dữ liệu từ bảng này sang bảng khác trong Excel khác trang tính, bạn cần thực hiện một số thao tác hơn là áp dụng các công thức ở trên, cộng với sử dụng 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.
Ví dụ bạn có hai bảng nằm 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).
Nếu muốn lấy dữ liệu từ bảng này sang bảng khác trong Excel bằng hàm, bạn có thể làm như dưới đây:
Như có thể thấy ở trên, thứ tự của các tên ở bảng chính là không tương ứng với thứ tự trong bảng tra cứu, vì vậy mà một thao tác sao chép và dán đơn giản sẽ không cho ra kết quả.
Do đó để kết hợp hai bảng theo một cột phù hợp là Seller, hãy nhập công thức dưới đây vào ô C2 trong bảng chính (Main table):
=VLOOKUP($A2,‘Lookup table’!$A$2:$B$10,2,FALSE)
Trong đó:
Khi sao chép công thức xuống cột, chúng ta sẽ nhận được một bảng kết quả đã hợp nhất bao gồm bảng chính và dữ liệu phù hợp với yêu cầu được lấy từ bảng tra cứu:
Như vậy là bạn đã tìm hiểu xong cách lấy dữ liệu từ bảng này sang bảng khác trong Excel bằng các hàm rồi đấy.
Lấy dữ liệu từ bảng này sang bảng khác trong Excel bằng Power Query
Trong một số trường hợp, bạn cần kết hợp hai hoặc nhiều bảng lại với nhau, nhưng chúng lại có số lượng hàng và cột khác nhau. Điều này nghe có vẻ rất rắc rối, nhưng đừng lo vì Power Query sẽ giúp bạn.
Tuy vậy chúng mình vẫn cần lưu ý với bạn rằng việc tham gia các bảng với Power Query không thể được thực hiện nhanh chóng và đơn giản chỉ với một vài thao tác. Dưới đây là tóm tắt các tính năng chính:
Ví dụ minh họa:
Với nguồn dữ liệu này, nhiệm vụ của bạn là trích xuất dữ liệu ở bảng 1 với các bản ghi có liên quan từ hai bảng còn lại, sau đó kết hợp tất cả dữ liệu vào cùng một bảng mới như bên dưới.
Tạo kết nối Power Query
Để không làm lộn xộn các trang tính làm việc của bạn với quá nhiều các bản sao của bản gốc, chúng mình sẽ chuyển đổi chúng thành các kết nối và thực hiện phối trong Trình soạn thảo Power Query. Cuối cùng bạn chỉ cần tải bảng kết quả.
Để lưu bảng dưới dạng kết nối trong Power Query, hãy làm theo các bước sau:
Tiếp theo, đối với các thực thể khác mà bạn muốn hợp nhất, bạn chỉ cần lặp lại các bước trên (thêm hai bảng, Products và Commissions, trong trường hợp ví dụ trên).
Sau khi hoàn thành, bạn sẽ thấy tất cả các kết nối trên ngăn:
Hợp nhất hai kết nối vào một bảng
Dưới đây là hướng dẫn cách kết nối hai bảng thành một:
Trên tab Data, chọn Get Data => chọn Combine Queries trong danh sách được thả xuống => chọn Merge.
Tiếp theo, trong hộp thoại Phối, bạn cần:
Sau khi kết thúc các bước trên, Power Query sẽ hiển thị bảng đầu tiên (Orders) với một cột bổ sung có tên như bảng thứ hai (Products) được thêm vào cuối. Cột bổ sung này khi đó chưa hề có bất kỳ giá trị nào, thay vào đó nó chỉ có từ “Table” trong tất cả các ô. Tuy vậy đừng lo lắng. Bạn vẫn đang làm đúng và chỉ cần một bước nữa để khắc phục điều này.
Chọn các cột cần thêm từ bảng thứ hai
Sau khi thực hiện chuẩn xác các bước trên, bạn sẽ có một bảng giống như bảng trong ảnh bên dưới đây. Để hoàn thành quá trình hợp nhất, hãy thực hiện thêm một số bước sau trong Power Query.
Trong cột đã thêm (Products), nhấp chuột vào mũi tên hai mặt trong phần tiêu đề.
Trong hộp thoại mở ra, hãy:
Cuối cùng, bạn sẽ nhận được thành quả là 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. Vậy là đã hoàn thành xong cách lấy dữ liệu từ bảng này sang bảng khác trong Excel bằng Power Query.
Nếu bạn chỉ muốn hợp nhất hai bảng, bạn chỉ cần tải bảng kết quả trong Excel về là xong.
Trên đây chúng mình đã giới thiệu đến bạn toàn bộ các cách lấy dữ liệu từ bảng này sang bảng khác trong Excel mà một người sử dụng Excel thành thạo nhất có thể biết. Dù lượng kiến thức là khá lớn nhưng vẫn rất đáng để bạn bỏ thời gian tìm hiểu và thực hành đấy. Cuối cùng, để cập nhật thêm nhiều thủ thuật hữu ích khác, đừng quên bấm theo dõi trang ngay nhé!
Microsoft Office –
Comment trong Word là gì? Cách thêm comment trong Word đơn giản ai cũng có thể làm được
Hàm dò tìm ký tự trong Excel hữu ích và thông dụng nhất
Cách chỉnh khoảng cách dòng trong Word 2016, 2010 hoặc 2003
Cách xóa Header and Footer trong 1 trang Word 2010 nhanh nhất
Hướng dẫn chi tiết cách xóa comment trong Word một cách dễ dàng
Làm việc thông minh hơn với danh sách các phím tắt trong Word mới nhất
Cách bỏ Tracking trong Word 2016 cực đơn giản chỉ với vài thao tác nhấp chuột
Cách tạo header and footer trong Word 2010 cực chi tiết
Tắt gạch chân đỏ trong Word trong chưa đến 10 giây