Hướng dẫn excel extract matching data - trích xuất excel dữ liệu phù hợp

Vietnamese (Tiếng Việt) translation by Dai Phong (you can also view the original English article) Dai Phong (you can also view the original English article)

Một phạm vi tên có thể chỉ là một cột, chỉ là một hàng, hoặc thậm chí chỉ là một ô

Đi đến tab Match của bảng tính. Trong B5, nhập hàm MATCH:

Nhập hàm MATCH

Nếu bạn không xác định một tên phạm vi, bạn sẽ viết hàm như sau:

Dù bằng cách nào, bạn có thể thấy rằng điều này là ở vị trí thứ 14 (thứ tự thứ 13).

• Kết quả của hàm MATCH
• Sử dụng INDEX
• Hàm INDEX là sự đối lập với hàm MATCH và tương tự như hàm VLOOKUP. Bạn nói cho hàm biết hàng và cột của dữ liệu nào mà bạn muốn, và nó sẽ nói cho bạn biết giá trị của ô là gì.
• Cú pháp của hàm INDEX là:
• =INDEX(phạm vi dữ liệu, số hàng, [số cột])

Số hàng. Số hàng dữ liệu, không nhất thiết phải là hàng của sheet. Nếu phạm vi bảng bắt đầu từ hàng 10 của sheet, thì đó là hàng thứ 1.

Số cột. Số cột của phạm vi dữ liệu. Nếu phạm vi bắt đầu trên cột E, thì đó là cột số 1.vlookup example.xlsx.

Tài liệu hướng dẫn của Excel sẽ cho bạn biết rằng đối số số cột là không bắt buộc, nhưng số hàng cũng không bắt buộc. Nếu phạm vi bảng chỉ có một hàng hoặc một cột, bạn không cần phải sử dụng các tham số khác.

Vào sheet Index của bảng tính và nhấp vào C6. Đầu tiên chúng ta muốn tìm kiếm những gì đang có trong hàng 9, cột 3 của bảng. Trong công thức, chúng ta sẽ sử dụng tên phạm vi mà chúng ta đã tạo ra trước đó.

Cú pháp

Cú pháp của hàm VLOOKUP là:

=VLOOKUP(giá trị tra cứu, phạm vi bảng, số cột, [true/false])

Dưới đây là ý nghĩa của các tham số:

• Giá trị tra cứu. Cái ô mà có định danh duy nhất.. Cái ô mà có định danh duy nhất.
• Phạm vi bảng. Phạm vi của các ô có định danh trong cột đầu tiên, tiếp theo sau là phần còn lại của dữ liệu trong các cột khác.. Phạm vi của các ô có định danh trong cột đầu tiên, tiếp theo sau là phần còn lại của dữ liệu trong các cột khác.
• Số cột. Số lượng cột có dữ liệu mà bạn đang tìm kiếm. Đừng nhầm lẫn với ký tự của cột. Trong hình minh họa ở trên, ký hiệu các Bang ở trong cột 4.. Số lượng cột có dữ liệu mà bạn đang tìm kiếm. Đừng nhầm lẫn với ký tự của cột. Trong hình minh họa ở trên, ký hiệu các Bang ở trong cột 4.
• True/False. Tham số này là không bắt buộc. True có nghĩa là gần giống thì có thể chấp nhận được, và False nghĩa là chỉ giống hoàn toàn chấp nhận được.. Tham số này là không bắt buộc. True có nghĩa là gần giống thì có thể chấp nhận được, và False nghĩa là chỉ giống hoàn toàn chấp nhận được.

Chúng ta muốn tìm số tiền bán hàng từ bảng ở trong hình minh họa ở trên, vì vậy chúng ta sử dụng các lập luận này:

Xác định một tên phạm vi để tạo một tham chiếu tuyệt đối

Trong vlookup example.xlsx, tìm kiếm ở trong worksheet Sales Amounts. Chúng ta sẽ nhập công thức trong ô B5, sau đó sử dụng các tính năng AutoFill để sao chép công thức xuống sheet. Điều đó có nghĩa là phạm vi bảng trong công thức phải là một tham chiếu tuyệt đối. Một cách hay để làm điều đó là xác định một tên cho phạm vi bảng.vlookup example.xlsx, tìm kiếm ở trong worksheet Sales Amounts. Chúng ta sẽ nhập công thức trong ô B5, sau đó sử dụng các tính năng AutoFill để sao chép công thức xuống sheet. Điều đó có nghĩa là phạm vi bảng trong công thức phải là một tham chiếu tuyệt đối. Một cách hay để làm điều đó là xác định một tên cho phạm vi bảng.

Xác định một tên phạm vi trong Excel

1. Trước khi nhập công thức, hãy vào sheet source data.source data.
2. Chọn tất cả các ô từ A4 (tiêu đề cho cột Order #) xuống tới H203. Một cách nhanh chóng để làm việc đó là nhấp vào A4, sau đó nhấn Ctrl-Shift-End (Command-Shift-End trên Mac).A4 (tiêu đề cho cột Order #) xuống tới H203. Một cách nhanh chóng để làm việc đó là nhấp vào A4, sau đó nhấn Ctrl-Shift-End (Command-Shift-End trên Mac).
3. Click vào trong Name Box trên cột A (Name Box bây giờ hiển thị A4). Name Box trên cột A (Name Box bây giờ hiển thị A4).
4. Gõ data, sau đó nhấn Enter.data, sau đó nhấn Enter.
5. Bây giờ bạn có thể sử dụng tên data ở trong công thức thay vì $A4:$H$203.

Xác định một tên phạm vi trong Google Sheets

Trong Google Sheets, việc xác định một tên có hơi khác một chút.

1. Nhấp vào tiêu đề cột đầu tiên của source data, sau đó nhấn Ctrl-Shift-Right Arrow (Command-Shift-Right Arrow trên Mac). Nó sẽ chọn hàng của tiêu đề cột.Ctrl-Shift-Right Arrow (Command-Shift-Right Arrow trên Mac). Nó sẽ chọn hàng của tiêu đề cột.
2. Nhấn Ctrl-Shift-Down Arrow (Command-Shift-Down Arrow trên Mac). Nó sẽ chọn dữ liệu thật sự.Ctrl-Shift-Down Arrow (Command-Shift-Down Arrow trên Mac). Nó sẽ chọn dữ liệu thật sự.
3. Nhấp vào menu Data, sau đó chọn Named and protected ranges.Data, sau đó chọn Named and protected ranges.
4. Trong hộp Name and protected ranges ở bên phải, gõ data, sau đó nhấn Done.Name and protected ranges ở bên phải, gõ data, sau đó nhấn Done.
   

Trong Google Sheets, việc xác định một tên có hơi khác một chút.

Nhấp vào tiêu đề cột đầu tiên của source data, sau đó nhấn Ctrl-Shift-Right Arrow (Command-Shift-Right Arrow trên Mac). Nó sẽ chọn hàng của tiêu đề cột.Sales Amounts và nhấp vào B5.

Nhấn Ctrl-Shift-Down Arrow (Command-Shift-Down Arrow trên Mac). Nó sẽ chọn dữ liệu thật sự.

=VLOOKUP(A5,data,8,FALSE)

Nhấp vào menu Data, sau đó chọn Named and protected ranges.Enter.

Nhập công thức

Nhập công thức:

Nhập hàm VLOOKUP

Kết quả sẽ là 40. Để điền các giá trị xuống cột, nhấp trở lại B5, nếu cần thiết. Đặt con trỏ chuột vào dấu chấm AutoFill ở góc dưới bên phải của ô, để con trỏ chuột trở thành một biểu tượng nhắm.

Khi bạn đặt con trỏ chuột vào dấu chấm ở góc dưới bên phải của một ô, nó sẽ trở thành một biểu tượng nhắm cho AutoFill

Cú pháp

Nhấp đúp để điền các giá trị xuống cột.

= MATCH (giá trị tra cứu, phạm vi bảng, [kiểu trùng])

Nhấp đúp vào biểu tượng nhắm AutoFill để sao chép công thức xuống cột

• Nếu bạn muốn, bạn có thể chạy hàm VLOOKUP trong vài cột tiếp theo để trích xuất các trường dữ liệu khác, như last name hoặc state.. Ô có định danh duy nhất.
• Sử dụng MATCH. Phạm vi của các ô mà bạn đang tìm kiếm.
• Hàm MATCH không trả về giá trị dữ liệu cho bạn; bạn cung cấp các giá trị mà bạn đang tìm kiếm, và hàm sẽ trả về vị trí của giá trị đó. Nó giống như câu hỏi #135 Main Street là ở đâu, và nhận được câu trả lời rằng đó là tòa nhà thứ 4 ở trên phố.. Không bắt buộc. Đó là cách mà bạn chỉ định cho nó trùng như thế nào tuỳ theo ý bạn muốn, như sau:

Các giá trị phải theo thứ tự giảm dần.Data Source, chọn từ B4 (tiêu đề cột cho Order #) đến phía dưới cùng, nhấp vào trong Name Box trên cột A, và gọi nó là order_number. Lưu ý rằng các giá trị theo thứ tự tăng dần.

Giá trị có thể theo thứ tự bất kỳ.Match của bảng tính. Trong B5, nhập hàm MATCH:

=MATCH(A5,order_number,1)

Kiểu mặc định. Giá trị phải theo thứ tự tăng dần.

=MATCH(A5,'Source Data'!A5:A203,0)

Cũng như hàm VLOOKUP, bạn có thể sẽ thấy hàm MATCH dễ dàng hơn để sử dụng nếu bạn áp dụng một tên phạm vi. Vào sheet Data Source, chọn từ B4 (tiêu đề cột cho Order #) đến phía dưới cùng, nhấp vào trong Name Box trên cột A, và gọi nó là order_number. Lưu ý rằng các giá trị theo thứ tự tăng dần.

Đi đến tab Match của bảng tính. Trong B5, nhập hàm MATCH:

Nhập hàm MATCH

Cú pháp

Nếu bạn không xác định một tên phạm vi, bạn sẽ viết hàm như sau:

=INDEX(phạm vi dữ liệu, số hàng, [số cột])

Các đối số là:

• Phạm vi dữ liệu. Cũng giống như hai hàm kia, đây là bảng dữ liệu.. Cũng giống như hai hàm kia, đây là bảng dữ liệu.
• Số hàng. Số hàng dữ liệu, không nhất thiết phải là hàng của sheet. Nếu phạm vi bảng bắt đầu từ hàng 10 của sheet, thì đó là hàng thứ 1.. Số hàng dữ liệu, không nhất thiết phải là hàng của sheet. Nếu phạm vi bảng bắt đầu từ hàng 10 của sheet, thì đó là hàng thứ 1.
• Số cột. Số cột của phạm vi dữ liệu. Nếu phạm vi bắt đầu trên cột E, thì đó là cột số 1.. Số cột của phạm vi dữ liệu. Nếu phạm vi bắt đầu trên cột E, thì đó là cột số 1.

Tài liệu hướng dẫn của Excel sẽ cho bạn biết rằng đối số số cột là không bắt buộc, nhưng số hàng cũng không bắt buộc. Nếu phạm vi bảng chỉ có một hàng hoặc một cột, bạn không cần phải sử dụng các tham số khác.

Vào sheet Index của bảng tính và nhấp vào C6. Đầu tiên chúng ta muốn tìm kiếm những gì đang có trong hàng 9, cột 3 của bảng. Trong công thức, chúng ta sẽ sử dụng tên phạm vi mà chúng ta đã tạo ra trước đó.Index của bảng tính và nhấp vào C6. Đầu tiên chúng ta muốn tìm kiếm những gì đang có trong hàng 9, cột 3 của bảng. Trong công thức, chúng ta sẽ sử dụng tên phạm vi mà chúng ta đã tạo ra trước đó.

Nhập công thức:

=INDEX(data,A6,B6)

Nó trả về last name của khách hàng: Strevell. Thay đổi các giá trị của A6 và B6, và trong C6 sẽ cho ra kết quả khác (lưu ý rằng nhiều hàng có bang và tên sản phẩm tương tự).

Tổng kết

Khả năng tìm kiếm trong một sheet khác và trích xuất dữ liệu là một công cụ tuyệt vời. Bằng cách này, bạn có thể có một sheet có chứa tất cả các dữ liệu mà bạn cần cho nhiều mục đích, sau đó trích xuất những gì bạn cần trong các trường hợp cụ thể.