Cách lấy dữ liệu từ bảng này sang bảng khác trong Excel

Việc chuyển dữ liệu trong Excel là công việc khá phổ biến. Trong bài viết này, Excel.net.vn sẽ hướng dẫn bạn cách lấy dữ liệu từ bảng này sang bảng khác trong Excel thông qua một số cách đơn giản dưới đây.

1. Dùng hàm VLOOKUP để chuyển dữ liệu giữa các bảng theo chiều ngang

Cấu trúc:

Trong đó:

• Lookup_value: Giá trị tìm kiếm.
• Table_array: Phạm vi vùng dữ liệu chứa giá trị cần tìm. Phạm vi này phải có ít nhất 2 cột. Bạn nên sử dụng phím F4 sau khi chọn phạm vi để cố định nó. Cột đầu tiên là cột chứa giá trị tìm kiếm.
• Col_index_num: Số thứ tự của cột trong vùng tìm kiếm chứa giá trị cần trả về.
• Range_lookup: Cách thức tìm kiếm. Đặt range_lookup là 0 hoặc FALSE để tìm kiếm chính xác; là 1 hoặc TRUE hoặc không nhập để tìm giá trị gần đúng.

Tác dụng: Hàm VLOOKUP trả về kết quả là nội dung trong một bảng hoặc dải ô theo hàng theo tham số tìm kiếm.

Ví dụ: Bạn có bảng chi tiết trên, bạn cần lấy dữ liệu kho chứa hàng của từng loại hàng hóa.

Ở ô Q5, bạn sử dụng công thức =VLOOKUP(P5,$D$4:$E$19,2,FALSE) thu được kết quả trả về kho 12 cho mặt hàng Đại mạch. Sao chép công thức cho ô kế tiếp và thu được kết quả kho 11 chứa mặt hàng Tiểu mạch.

2. Dùng hàm HLOOKUP để chuyển dữ liệu giữa các bảng theo chiều dọc

Tương tự như hàm VLOOKUP, Hàm HLOOKUP trả về kết quả là nội dung trong một bảng hoặc dải ô theo cột theo tham số tìm kiếm.

Cấu trúc:

Trong đó:

• Lookup_value: Giá trị tìm kiếm.
• Table_array: Phạm vi vùng dữ liệu chứa giá trị cần tìm. Phạm vi này phải có ít nhất 2 cột. Bạn nên sử dụng phím F4 sau khi chọn phạm vi để cố định nó. Cột đầu tiên là cột chứa giá trị tìm kiếm.
• Col_index_num: Số thứ tự của cột trong vùng tìm kiếm chứa giá trị cần trả về.
• Range_lookup: Cách thức tìm kiếm. Đặt range_lookup là 0 hoặc FALSE để tìm kiếm chính xác; là 1 hoặc TRUE hoặc không nhập để tìm giá trị gần đúng.

Ví dụ: Bạn muốn tìm dữ liệu ở dòng số 5 thuộc cột tham chiếu là "NK ưu đãi".

Bạn nhập công thức =HLOOKUP("NK ưu đãi",$A$1:$V$18,6,FALSE) vào ô tính bất kỳ rồi nhận được kết quả như hình dưới:

Nếu bạn muốn tìm dữ liệu ở dòng số 5 thuộc cột tham chiếu là "NK TT" thì thay giá trị tham chiếu là "NK TT" rồi nhấn Enter.

3. Dùng hàm MATCH, INDEX để chuyển dữ liệu giữa các bảng

3.1 Hàm MATCH

Cấu trúc:

Trong đó:

• Lookup_value: Giá trị cần tìm kiếm.
• Lookup_array: Phạm vi dữ liệu chứa giá trị cần tìm kiếm.
• Match_type: Kiểu tìm kiếm. Sử dụng 0 để tìm kiếm chính xác, 1 hoặc không nhập gì để tìm vị trí của giá trị nhỏ hơn hoặc bằng giá trị cần tìm, -1 để tìm vị trí của giá trị lớn hơn hoặc bằng giá trị cần tìm.

Tương tự với bảng dữ liệu trên, chúng ta tìm kiếm vị trí của giá trị “Đại mạch” và “Tiểu Mạch” trong cột dữ liệu từ D1:D19 và từ D5:D19 để so sánh sự khác biệt về kết quả:

Hàm MATCH trả về vị trí của một dữ liệu cần xác định trong vùng dữ liệu. Khi sử dụng hàm MATCH chúng ta cần lưu ý một số điểm sau:

• Hàm MATCH trả về vị trí của giá trị khớp đúng trong lookup_array, chứ không trả về giá trị đó. Thông thường hàm MATCH được sử dụng với hàm INDEX để trích xuất dữ liệu từ vị trí đã xác định được của hàm MATCH;
• Hàm MATCH không phân biệt chữ hoa và chữ thường khi so sánh độ trùng khớp các giá trị văn bản. Ví dụ trên chúng ta tìm kiếm vị trí của “Tiểu Mạch” trong khi bảng dữ liệu là “Tiểu mạch”;
• Nếu hàm MATCH không tìm thấy giá trị khớp nào, hàm trả về giá trị lỗi #N/A;
• Vùng dữ liệu tìm kiếm Lookup_array phải là một cột hoặc một dòng. Nếu không hàm sẽ trả về giá trị lỗi #N/A.

3.2 Hàm INDEX

Cấu trúc:

Trong đó:

• Array: Phạm vi dữ liệu cần tiến hành tìm kiếm.
• Row_num: Số thứ tự của hàng trong phạm vi dữ liệu, chứa giá trị cần trích xuất.
• Column_num: Số thứ tự của cột trong phạm vi dữ liệu, chứa giá trị cần trích xuất.

Ví dụ kết hợp hàm INDEX và MATCH để lấy dữ liệu:

Trong một số yêu cầu, chúng ta không thể tìm kiếm dữ liệu bằng hàm VLOOKUP (tìm tham chiếu cả dòng và cột) mà phải sử dụng kết hợp hàm INDEX và MATCH.

4. Sử dụng công cụ Power Query

Nhược điểm của các cách làm trên là hàm tính chỉ trả về 1 dữ liệu đầu tiên mà Excel tìm được, để liệt kê toàn bộ các giá trị mà trong bảng dữ liệu có chúng ta không thể không nghĩ tới công cụ Power Query.

Bước 1: Chọn vùng dữ liệu cần xử lý => Vào mục Data (1) => Nhấn chuột vào From Table/Range (2).

Bước 2: Cửa sổ Power Query hiện ra => Chọn Group By (1) => Cửa sổ Group By hiện ra => (2) Thiết lập một cột mới theo tính năng mà bạn cần (ví dụ như hình dưới) => Nhấn OK (3).

Bước 3: Phần dữ liệu ngày chạy tháng được hiện ra thể hiện dưới dạng bảng tính (table).

Bước 4: Bạn cần thay đổi kết quả ở dạng Table => List bằng cách.

- Trong ô công thức, xóa phần dữ liệu từ sau chữ “Each_” cho tới trước dấu đóng ngoặc vuông “]”.

- Gõ cấu trúc [“tên cột mà muốn thống kê”]. Ví dụ chúng ta cần thống kê theo cột “Ngày cân vào”.

Nhấn Enter bạn thu được kết quả sau:

Bước 5: Thay đổi cách thể hiện dữ liệu, bạn nhấn chuột vào mũi tên hai chiều của cột Ngày chạy hàng => Chọn Extract Values...

Bước 6: Cửa sổ Extract values from list hiện ra bạn lựa chọn mục Custom (1) và gõ ký hiệu phân cách giữa các dữ liệu mà bạn muốn thể hiện. Ví dụ chúng ta sử dụng dấu chấm phẩy “;” (2) và nhấn OK.

Bước 7: Kết quả sau các bước thực hiện trong tính năng Power Query.

Bước 8: Trên thanh công cụ của Power Query bạn nhấn chuột vào mũi tên cạnh Close & Load (1) > Nhấn chọn Close & Load To (2).

Bước 9: Trong cửa sổ Import Data bạn nhấn chuột chọn mục Existing worksheet => nhấn chuột vào vị trí ô muốn thể hiện dữ liệu trên Sheet => Nhấn OK.

Và bạn thu được bảng liệt kê như sau:

Trên đây là các cách lấy dữ liệu từ bảng này sang bảng khác trong excel. Hy vọng bài viết sẽ giúp bạn thực hiện được trong công việc cũng như học tập!