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

Bạn có thể tìm hiểu về cả mối quan hệ bảng và hiển thị thời gian thông minh bằng cách dùng dữ liệu miễn phí trên Microsoft Azure Marketplace. Một vài bộ trong số các bộ dữ liệu này rất lớn, đòi hỏi phải có kết nối internet nhanh để hoàn thành tải xuống dữ liệu trong một khoảng thời gian hợp lý.

Nội dung chính Show

Giới thiệu về hàm số VLOOKUP trên Excel
Cách dùng hàm số VLOOKUP để lấy dữ liệu trong Excel
Như vậy là chỉ với 2 bước đơn giản chúng ta đã có thể lấy dữ liệu từ bảng này sang bảng khác trong Excel.
2. Cách lấy dữ liệu từ bảng này sang bảng khác bằng hàm MATCH trong excel
3. Cách lấy dữ liệu từ bảng này sang bảng khác bằng hàm INDEX trong excel
4. Cách lấy dữ liệu từ bảng này sang bảng khác trong Excel bằng Power Query

Bắt đầu Power Pivot trong bổ trợ Microsoft Excel và mở cửa sổ Power Pivot.
Bấm vào Nhận Dữ liệu Ngoài > Từ Dịch vụ Dữ liệu > Từ Microsoft Azure Marketplace. Trang đầu Microsoft Azure Marketplace mở ra trong Trình hướng dẫn Nhập Bảng.
Dưới Giá, hãy bấm Miễn phí.
Dưới Thể loại, hãy bấm Khoa học & Thống kê.
Tìm DateStream và bấm Đăng ký.
Nhập tài khoản Microsoft của bạn và bấm Đăng nhập. Xem trước dữ liệu sẽ xuất hiện trong cửa sổ.
Cuộn xuống dưới và bấm Chọn Truy vấn.
Bấm Tiếp.
Chọn BasicCalendarUS rồi bấm Kết thúc để nhập dữ liệu. Với kết nối internet nhanh, quá trình nhập sẽ mất khoảng một phút. Khi kết thúc, bạn sẽ thấy báo cáo trạng thái có 73.414 hàng được truyền. Bấm Đóng.
Bấm Nhận Dữ liệu Ngoài > Từ Dịch vụ Dữ liệu > Từ Microsoft Azure Marketplace để nhập bộ dữ liệu thứ hai.
Dưới Kiểu, hãy bấm Dữ liệu.
Dưới Giá, hãy bấm Miễn phí.
Tìm Các Chuyến bay Trễ của US Air Carrier và bấm Chọn.
Cuộn xuống dưới và bấm Chọn Truy vấn.
Bấm Tiếp.
Bấm Kết thúc để nhập dữ liệu. Với kết nối internet nhanh, quá trình nhập có thể mất 15 phút. Khi kết thúc, bạn sẽ thấy báo cáo trạng thái 2.427.284 hàng được truyền. Bấm Đóng. Bây giờ hẳn là bạn đã có hai bảng trong mô hình dữ liệu. Để liên kết các bảng này, chúng ta sẽ cần các cột tương thích trong mỗi bảng.
Lưu ý rằng DateKey trong BasicCalendarUS có định dạng 1/1/2012 12:00:00 SA. Bảng On_Time_Performance cũng có cột ngàygiờ, FlightDate, mà các giá trị trong cột này được xác định ở cùng một định dạng: 1/1/2012 12:00:00 SA. Hai cột chứa dữ liệu khớp nhau, có cùng một kiểu dữ liệu và tối thiểu một trong các cột (DateKey) chỉ chứa giá trị duy nhất. Trong một vài bước tiếp theo, bạn sẽ dùng các cột này để liên kết bảng.
Trong cửa sổ Power Pivot, hãy bấmPivotTable để tạo PivotTable trong trang tính mới hoặc trang tính hiện có.
Trong Danh sách Trường, hãy bung rộng On_Time_Performance và bấm ArrDelayMinutes để thêm vào vùng Giá trị. Trong PivotTable, bạn sẽ thấy tổng số thời gian các chuyến bay bị hoãn, được tính bằng phút.
Hãy bung rộng BasicCalendarUS và bấm MonthInCalendar để thêm nó vào vùng Hàng.
Lưu ý rằng bây giờ PivotTable liệt kê các tháng nhưng tổng cộng số phút giống nhau cho mọi tháng. Các giá trị lặp lại, giống hệt nhau cho thấy rằng cần có một mối quan hệ.
Trong Danh sách Trường, trong “Có thể cần mối quan hệ giữa các bảng”, hãy bấm Tạo.
Trong Bảng Liên quan, hãy chọn On_Time_Performance và trong Cột Liên quan (Chính) chọn FlightDate.
Trong Bảng, hãy chọn BasicCalendarUS và trong Cột (Ngoại) chọn DateKey. Bấm OK để tạo mối quan hệ.
Lưu ý rằng tổng số phút bị hủy của mỗi tháng bây giờ đã khác nhau.
Trong BasicCalendarUS và kéo YearKey đến vùng Hàng, ở trên MonthInCalendar.

Bây giờ bạn có thể phân chia các chuyến bay đến bị hoãn theo năm và tháng hoặc các giá trị khác trong lịch.

Mẹo: Theo mặc định, các tháng được liệt kê theo thứ tự bảng chữ cái. Dùng bổ trợ Power Pivot, bạn có thể thay đổi cách sắp xếp để các tháng xuất hiện theo thứ tự thời gian.

Hãy bảo đảm bảng BasicCalendarUS đang mở trong cửa sổ Power Pivot.
Trên bảng Trang đầu, hãy bấm Sắp xếp theo Cột.
Trong Sắp xếp, hãy chọn MonthInCalendar
Trong Theo, hãy chọn MonthOfYear.

Bây giờ PivotTable sắp xếp từng kết hợp tháng-năm (Tháng 10 năm 2011, Tháng 11 năm 2011) theo số tháng trong năm (10, 11). Thay đổi thứ tự sắp xếp thật dễ dàng vì nguồn cấp DateStream cung cấp tất cả các cột cần thiết để thực hiện thành công tình huống này. Nếu bạn đang dùng bảng hiển thị thời gian thông minh khác, bạn sẽ thực hiện bước khác.

Bạn muốn lấy dữ liệu từ bảng này sang bảng khác trong excel mà chưa biết làm cách nào nhanh chóng, hiệu quả, không mất thời gian căn chỉnh số liệu sai lệch. Bài viết dưới đây sẽ chỉ dẫn bạn thao tác tìm lấy dữ liệu bằng lệnh và cài đặt hàm số chuẩn xác.

Giới thiệu về hàm số VLOOKUP trên Excel

VLOOKUP là một hàm số thông dụng trên phần mềm tin học văn phòng excel dùng để tìm kiếm một giá trị bất kì tại một cột trên excel và trả về giá trị tương ứng ở một cột khác.

Hàm VLOOKUP được phân biệt với hàng HLOOKUP bởi chữ “V” từ tiếng anh “vertical” – nghĩa là “thẳng đứng”, chữ “H” của từ tiếng anh “horizontal” (nằm ngang). Như vậy, hàm VLOOKUP là tìm tất cả thông tin dữ liệu trên các cột, và các hàng.

Lấy dữ liệu từ bảng này sang bảng khác trong excel như từ cột, hàng excel này sang cột, hàng excel khác bạn có thể áp dụng hàm VLOOKUP hoặc HLOOKUP để thao tác nhanh, chính xác hơn thay vì thao tác nhấn Ctr+ F dò tìm cột, hàng rồi thực hiện Ctrl + C để sao chép và Ctrl + V để dán, di chuyển đến vị trí mong muốn.

Trong excel, hàm VlOOKUP được đánh với cú pháp như sau: VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
Trước khi áp dụng một hàm số tìm kiếm, bạn cần định nghĩa được các thành phần trong cú pháp để dễ dàng ghi nhớ

Lookup_value: giá trị cần dò tìm. Giá trị này có thể là chữ số, ngày tháng hoặc ký tự, chuỗi các ký tự bất kỳ. Bạn cũng có thể dò tìm một ô chứa giá trị cần tìm.
Col_index_num: Số thứ tự của cột dữ liệu trong bảng cần dò tìm để khi thực hiện lệnh VLOOKUP sẽ lấy được dữ liệu tương ứng trả về.

Table_array: Bảng giới hạn để dò tìm dữ liệu. Bảng giới hạn này có thể chứa nhiều dữ liệu dưới dạng chữ viết, số liệu, ngày, tháng không phân biệt trong quá trình thực hiện hàm số tìm kiếm.

Range_lookup: Xác định sự phù hợp tuyệt đối của dữ liệu đang muốn tìm kiếm – True, phù hợp tương đối là False. Range_lookup còn được gọi là giá trị logic (True=1, False=0). Nếu bạn không ghi đầy đủ Range_lookup trong công thức hàm số thì Excel sẽ tự động cài đặt Range_lookup = 1

Như vậy, khi hiểu về chức năng của hàm VLOOKUP, bạn có thể lấy dữ liệu từ bảng này sang bảng khác trong excel một cách thuận tiện, dễ dàng.

Cách dùng hàm số VLOOKUP để lấy dữ liệu trong Excel

Khi lấy dữ liệu từ bảng này sang bảng khác trong excel bằng hàm số VLOOKUP, bạn cần chú ý khi nhập tên bảng tính bạn nên kèm theo một dấu lệnh ký hiệu cảm thán (!) trong câu lệnh table_array trước giới hạn ô.

Ví dụ =VLOOKUP(20, Sheet1!A4:B16, 1,0). Công thức này chỉ ra rằng:

Giá trị cần dò tìm là 20 Dải ô tìm kiếm A4:B16 nằm ở Sheet1. Số thứ tự cột dữ liệu cần dò tìm là 1

Số 0 là Trả về chính xác giá trị cần tìm

Trong một số trường hợp nếu muốn lấy dữ liệu từ bảng này sang bảng khác trong Excel nhanh chóng thì chúng ta sẽ cần sử dụng các hàm hoặc một số thủ thuật trong Excel. Trong bài viết hôm nay, mình sẽ chia sẻ với các bạn các cách để có thể lấy dữ liệu từ bảng này sang bảng khác trong Excel nhé.

Cách lấy dữ liệu từ bảng này sang bảng khác bằng hàm VLOOKUP trong excel được sử dụng khi số lượng dữ liệu ít và trong phạm vi nhỏ.

Hàm VLOOKUP có cú pháp như sau:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Trong đó:

Lookup_value có nghĩa là giá trị dùng để dò tìm.
Table_array có nghĩa là vùng dữ liệu chứa giá trị cần dò tìm, có từ 2 cột trở lên và sau khi chọn vùng chúng ta có thể nhấn phím F4 để cố định vùng.
Col_index_num có nghĩa là số thứ tự cột đếm từ vùng dò tìm chứa giá trị cần trả về.
Range_lookup có nghĩa là phương pháp dò tìm, Range_lookup có thể là 0 hoặc FALSE để tìm giá trị chính xác và range_lookup có thể là 1 hoặc TRUE hoặc bỏ qua khi tìm giá trị gần đúng.

Ví dụ: Hãy sử dụng hàm VLOOKUP để lấy dữ liệu trong bảng dữ liệu sheet 2 sang bảng dữ liệu sheet 1.

Bước 1: Đầu tiên, trong bảng dữ liệu sheet 1, chúng ta nhập hàm tại ô E4 như sau

=VLOOKUP(D4,$G$8:$H$11,2,0)

Bước 2: Sau đó, chúng ta nhấn phím Enter để hiển thị kết quả và kéo chuột xuống các ô bên dưới để hiển thị các kết quả còn lại như hình minh họa bên dưới.

Như vậy là chỉ với 2 bước đơn giản chúng ta đã có thể lấy dữ liệu từ bảng này sang bảng khác trong Excel.

2. Cách lấy dữ liệu từ bảng này sang bảng khác bằng hàm MATCH trong excel

Cách lấy dữ liệu từ bảng này sang bảng khác bằng hàm MATCH trong excel được dùng để xác định vị trí dữ liệu cần tìm, không trả về dữ liệu và được dùng khi số lượng dữ liệu ít, trong phạm vi nhỏ và cần xác định nhanh vị trí dữ liệu.

Hàm Match có cú pháp như sau:

=MATCH(lookup_value, lookup_array, [match_type])

Trong đó:

Lookup_value có nghĩa là giá trị dùng để dò tìm.
Lookup_array có nghĩa là vùng dữ liệu chứa giá trị cần dò tìm, có từ 2 cột trở lên.
Match_type có nghĩa là kiểu dò tìm, với Match_type là 0 để tìm giá trị chính xác, còn Match_type là 1 hoặc bỏ qua để tìm vị trí giá trị lớn nhất, nhỏ hơn hoặc bằng giá trị cần tìm và nếu Match_type là -1 để tìm vị trí giá trị nhỏ nhất và lớn hơn giá trị cần tìm.

Ví dụ: Hãy sử dụng hàm MATCH để tìm vị trí giá trị của lê trong bảng dữ liệu rồi điền vị trí vào bảng tra cứu.

Bước 1: Đầu tiên, chúng ta sẽ nhập hàm vào ô H8 như sau:

=MATCH(30,E8:E13,0)

Bước 2: Sau đó chúng ta nhấn phím Enter để hiển thị kết quả trong bảng tra cứu

3. Cách lấy dữ liệu từ bảng này sang bảng khác bằng hàm INDEX trong excel

Hàm INDEX có cú pháp như sau:

=INDEX(array, row_num, [column_num])

Trong đó:

Array có nghĩa là vùng dữ liệu để tìm kiếm.
Row_num có nghĩa là vị trí hàng trong vùng dữ liệu có chứa giá trị cần tìm kiếm.
Column_num có nghĩa là vị trí cột trong vùng dữ liệu có chứa giá trị cần tìm kiếm.

Ví dụ: Hãy sử dụng hàm INDEX để lấy dữ liệu trong bảng dữ liệu sau.

Đầu tiên, chúng ta nhập hàm tại ô G8 như sau

=INDEX(C7:E13,6,2)

Trong đó:

C7:E13 là vùng dữ liệu để tìm kiếm
6 là vị trí hàng trong vùng dữ liệu có chứa giá trị cần tìm kiếm.
2 là vị trí cột trong vùng dữ liệu có chứa giá trị cần tìm kiếm.

Sau đó chúng ta nhấn phím Enter thì kết quả hiển thị như sau:

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

Cách lấy dữ liệu từ bảng này sang bảng khác trong Excel bằng Power Query được sử dụng khi chúng ta có quá nhiều dữ liệu, số lượng dữ liệu trải dài trên một sheet.

Ví dụ:

Bước 1: Đầu tiên, chúng ta hãy chuyển đổi cả hai bảng TABLE 1 và TABLE 2 thành bảng Excel bằng cách sử dụng Control + T hoặc nhấn Insert rồi chọn Table

Bước 2: Tiếp theo, chúng ta cần tải dữ liệu vào trình soạn thảo truy vấn nguồn bằng cách chọn Data Tab, tiếp tục chọn Get & Transform Data rồi chọn From Table.

Bước 3: Tiếp đó, chúng ta đóng truy vấn chọn Home tab, nhấn chọn Close and load to rồi chọn connection only và chúng ta hãy lặp lại bước 2 bước 3 tương tự ở bảng thứ 2.

Bước 4: Tiếp theo, nhấp chuột phải vào truy vấn và nhấp vào “Merge”

Khi chúng ta nhấp vào “Merge” thì nó sẽ hiển thị cửa sổ hợp nhất.

Khi đó ở cửa sổ này, chúng ta chọn “Quantity Table” ở phần trên và trong “Category Table” ở phần dưới. Sau đó, chọn cột chung trong cả hai bảng và ở ví dụ này là ID sản phẩm

Lúc này, chúng ta có một bảng mới trong trình soạn thảo truy vấn nguồn. Khi đó hãy nhấp vào nút bộ lọc của cột cuối cùng của bảng và chỉ chọn danh mục (bỏ chọn ID sản phẩm). Cuối cùng, chúng ta nhấp vào “Close & Load” để tải bảng vào trang tính.

Như vậy là chúng ta đã có thể lấy dữ liệu từ bảng này sang bảng khác trong Excel và hai bảng này có sự liên kết mật thiết với nhau.