Trích xuất dữ liệu vào thời điểm cần thiết là yếu tố hàng đầu khi sử dụng bất kỳ bảng tính nào, Excel cũng không phải là một ngoại lệ. Bạn lưu trữ dữ liệu dưới dạng bảng trong Excel để trích xuất chúng. Hôm nay chúng tôi sẽ hướng dẫn bạn cách trích xuất dữ liệu từ bảng dựa trên nhiều tiêu chí.
Điều đầu tiên, hãy tìm hiểu về tập dữ liệu là cơ sở cho các ví dụ của chúng tôi.
Ở đây chúng ta có một bảng chứa danh sách các phim cùng thể loại và diễn viên chính cùng với năm phát hành. Sử dụng tập dữ liệu này, chúng tôi sẽ truy xuất dữ liệu dựa trên nhiều tiêu chí.
Lưu ý rằng đây là một bảng cơ bản để giữ cho mọi thứ đơn giản. Trong một tình huống thực tế, bạn có thể gặp phải một tập dữ liệu phức tạp và lớn hơn nhiều.
Sách bài tập Thực hành
Bạn có thể tải xuống sách bài tập thực hành từ liên kết sau.
Trích xuất dữ liệu từ bảng dựa trên nhiều tiêu chí
Ví dụ ở đây, chúng tôi sẽ cung cấp thể loại và tên diễn viên làm tiêu chí và dựa trên các tiêu chí này, chúng tôi sẽ trích xuất tên phim.
1. Trả lại giá trị đơn
Trong phần này, chúng tôi sẽ trả về một giá trị duy nhất. Dựa trên các tiêu chí, chỉ một giá trị sẽ được tìm nạp. Hãy khám phá.
Tôi. Công thức mảng INDEX-MATCH
Chúng tôi có thể sử dụng kết hợp INDEX và MATCH chức năng. INDEX trả về giá trị tại một vị trí nhất định trong một phạm vi. TRẬN ĐẤU xác định vị trí của giá trị tra cứu trong một phạm vi.
Để biết các chức năng này, hãy xem các bài viết sau: INDEX, MATCH.
Hãy đặt giá trị tiêu chí. Hiện tại, Thriller là Thể loại và Hugh Jackman tại Diễn viên trường.
Công thức sẽ là công thức sau
=INDEX($B$4:$B$19,MATCH(1,($H$4=$C$4:$C$19)*($H$5=$D$4:$D$19),0)) B4:B19 là mảng mà từ đó chúng ta sẽ trích xuất giá trị. Và TRẬN ĐẤU hàm đặt số hàng sẽ được tìm nạp.
Bạn có thể thấy chúng tôi đã cung cấp 1 dưới dạng lookup_value trong MATCH . Và lookup_array được tạo ra thông qua phép nhân các logic đối sánh tiêu chí.
Thông qua $ H $ 4 =$ C $ 4:$ C $ 19 chúng tôi đã kiểm tra thể loại và $ H $ 5 =$ D $ 4:$ D $ 19 cho tên diễn viên.
Nó tìm thấy 1 trong số mảng kết quả phép nhân. Và trả lại số hàng và INDEX hàm trả về tên phim.
Đó là công thức mảng nên chúng tôi cần sử dụng CTRL + SHIFT + ENTER để thực thi nó.
Thay đổi các giá trị tiêu chí và bạn sẽ tìm thấy các giá trị được cập nhật.
Đọc thêm: Cách trích xuất dữ liệu cụ thể từ một ô trong Excel (3 Ví dụ)
II. INDEX-MATCH Công thức không thuộc mảng
Chúng ta có thể tạo một công thức không phải mảng kết hợp INDEX và MATCH .
Trước tiên hãy xem công thức
=INDEX($B$4:$B$19,MATCH(1,INDEX(($H$4=$C$4:$C$19)*($H$5=$D$4:$D$19),0,1),0)) Ở đây bạn có thể thấy, chúng tôi đã sử dụng một vài INDEX chức năng ở đó. INDEX bên ngoài hàm thực hiện công việc trích xuất trong đó hàm bên trong giúp phát hiện số hàng.
Trong INDEX, bên trong chúng tôi đã kiểm tra giá trị tiêu chí. Đây là hai phép toán logic được nhân trong INDEX và hoạt động như một tham chiếu mảng.
LÀ ENTER sẽ thực thi công thức.
Vui lòng sửa đổi các giá trị tiêu chí, bạn sẽ tìm thấy các giá trị được cập nhật.
Đọc thêm: Cách trích xuất dữ liệu từ Excel dựa trên tiêu chí (5 cách)
III. Kết hợp INDEX-MATCH-IF
Trong các phần trước, chúng tôi đã kiểm tra các điều kiện và nhân chúng lên để buộc chúng phải hành động cùng nhau. Chúng ta có thể bỏ phép nhân bằng cách sử dụng IF chức năng.
NẾU chạy kiểm tra logic và trả về giá trị Boolean ( TRUE hoặc FALSE ) kết quả là. Để biết về hàm, hãy truy cập bài viết IF này.
Công thức của chúng tôi sẽ là công thức sau đây
=INDEX($B$4:$B$16,MATCH(1,IF($C$4:$C$16=$H$4,IF($D$4:$D$16=$H$5,1)),0))
Ở đây, chúng tôi có hai điều kiện để phù hợp với nhau, đó là lý do tại sao hai IF . Chúng đang hoạt động dưới dạng IF lồng nhau (cái này bên trong cái khác). IF bên ngoài hàm kiểm tra điều kiện đầu tiên (bất kể trình tự) và điều kiện thứ hai (bên trong IF) là if_true_value cho IF đầu tiên .
Bạn cần sử dụng CTRL + SHIFT + ENTER để thực thi công thức.
Thay đổi các giá trị tiêu chí và bạn sẽ thấy một giá trị được cập nhật.
Đọc thêm: Cách trích xuất dữ liệu từ trang tính Excel (6 phương pháp hiệu quả)
IV. Hàm LOOKUP
Chúng tôi có thể sử dụng LOOKUP để thực hiện nhiệm vụ trích xuất dữ liệu của chúng tôi dựa trên các tiêu chí.
LOOKUP hàm thực hiện tìm kiếm so khớp trong một phạm vi và trả về giá trị tương ứng. Để biết thêm thông tin, hãy truy cập Hỗ trợ của Microsoft trang web.
Bây giờ chúng ta hãy khám phá công thức
=LOOKUP(2,1/($C$4:$C$19=$H$4)/($D$4:$D$19=$H$5),($B$4:$B$19))
Ở đây chúng tôi đã đặt 2 là lookup_value . Và hai phép toán logic ở dạng chia 1 cho chúng là lookup_vector .
Ở đây, nó chia 1 cho một mảng TRUE / FALSE giá trị ( $ C $ 4:$ C $ 19 =$ H $ 4 ), và sau đó bởi một mảng khác của TRUE / FALSE giá trị ( $ D $ 4:$ D $ 19 =$ H $ 5 ). Điều này sẽ trả về 1 hoặc # DIV / 0! lỗi.
lookup_value cho công thức biết để khớp công thức với giá trị số trong phạm vi, khi tìm thấy kết quả khớp, giá trị sẽ được tính từ mảng B4:B19 .
Không cần nhấn CTRL + SHIFT + ENTER để thực thi.
Thay đổi giá trị tiêu chí để xem liệu công thức có hoạt động hoàn hảo hay không đối với các giá trị khác.
Lưu ý rằng chúng tôi đã sử dụng 2 làm lookup_value . Đây có thể là bất kỳ số nào bắt đầu từ 1.
Đọc thêm: Cách trích xuất dữ liệu từ hình ảnh sang Excel (Với các bước nhanh)
Bài đọc tương tự
- Mã VBA để chuyển đổi tệp văn bản sang Excel (7 phương pháp)
- Excel VBA:Tự động kéo dữ liệu từ trang web (2 phương pháp)
- Cách Nhập Dữ liệu từ Trang web Bảo mật sang Excel (Với các Bước Nhanh)
- Tự động chuyển dữ liệu từ một trang tính Excel sang trang tính khác bằng hàm VLOOKUP
- Cách chuyển đổi Excel thành tệp văn bản với dấu phân cách ống (2 cách)
2. Trả lại nhiều giá trị
Tôi. Kết hợp INDEX-SMALL
Các kết hợp chức năng khác nhau có thể được sử dụng để trích xuất nhiều dữ liệu dựa trên tiêu chí. Một trong các kết hợp là INDEX - NHỎ kết hợp.
NHỎ hàm trả về các giá trị dựa trên vị trí của chúng trong danh sách được xếp hạng theo giá trị. Để biết thêm, hãy kiểm tra mục NHỎ này bài báo.
Cùng với hai hàm này, chúng ta sẽ cần một vài hàm trợ giúp, IF , ROW và IFERROR . Kiểm tra các bài viết để biết thêm thông tin: IF, ROW, IFERROR.
Công thức của chúng tôi sẽ là công thức sau đây
=IFERROR(INDEX($B$2:$B$17,SMALL(IF(($C$2:$C$17=$H$2)*($D$2:$D$17=$H$3), ROW($B$2:$B$17)),ROW(1:1))-1,1),"")
Ở đây mọi chức năng đều có mục đích của nó. INDEX hàm trả về giá trị từ mảng B2:B17 và NHỎ lớn phần cung cấp số hàng sẽ được tìm nạp.
NẾU, trong NHỎ, kiểm tra xem các tiêu chí có phù hợp hay không. Chúng tôi có hai điều kiện để khớp nhau, đó là lý do tại sao chúng tôi nhân cả hai phép toán logic để kiểm tra tiêu chí. Và ROW hàm lặp qua các ô của cột.
Sau đó, ROW bên ngoài biểu thị giá trị thứ k cho giá trị NHỎ hàm số. Các hàm này kết hợp với nhau trả về số hàng và INDEX trả về kết quả.
IFERROR để đối phó với bất kỳ lỗi nào có thể phát sinh từ công thức. Chúng tôi đã đặt nó theo cách mà nếu có lỗi, nó sẽ cung cấp một ô trống.
Kéo nó xuống, bạn sẽ nhận được tất cả các giá trị phù hợp với tiêu chí.
Đọc thêm: Cách trích xuất dữ liệu từ danh sách bằng công thức Excel (5 phương pháp)
II. Kết hợp INDEX-AGGREGATE
ĐỒNG Ý trong Excel cho phép chúng ta thực hiện các tác vụ khác nhau. Một chức năng cho một số hoạt động. Chúng tôi có thể sử dụng hàm này để trả về nhiều giá trị dựa trên nhiều tiêu chí.
Hãy tìm hiểu một chút về chức năng, AGGREGATE hàm trả về một phép tính tổng hợp như AVERAGE, COUNT, MAX, v.v.
Cú pháp cho AGGREGATE chức năng như sau:
AGGREGATE(function_number,behavior_options, range) function_number: Con số này chỉ định phép tính nào nên được thực hiện.
behavior_options: Đặt số này bằng cách sử dụng. Con số này biểu thị cách hàm sẽ hoạt động.
phạm vi: Phạm vi bạn muốn tổng hợp.
ĐỒNG Ý hàm thực hiện một số tác vụ vì vậy số lượng hàm được xác định trước bên trong nó. Chúng tôi liệt kê một vài số hàm thường được sử dụng
| Hàm | Hàm_số |
|---|---|
| AVERAGE | 1 |
| COUNT | 2 |
| COUNTA | 3 |
| MAX | 4 |
| MIN | 5 |
| SẢN PHẨM | 6 |
| SUM | 9 |
| LARGE | 14 |
| NHỎ | 15 |
Để biết thêm về chức năng, hãy truy cập Hỗ trợ của Microsoft trang web.
Bây giờ chúng ta hãy xem công thức, nó sẽ là sự kết hợp của INDEX và AGGREGATE .
=IFERROR(INDEX($B$2:$B$17,AGGREGATE(15,6,IF(($C$2:$C$17=$H$2)*($D$2:$D$17=$H$3), ROW($B$2:$B$17)),ROW(1:1))-1,1),"")
Ở đây chúng tôi đã sử dụng 15 dưới dạng function_number trong AGGREGATE . Từ bảng trên, bạn có thể thấy 15 cuộc gọi cho NHỎ hoạt động chức năng. Bạn có thể thấy ngoài việc sử dụng AGGREGATE (và số chức năng và số tùy chọn hành vi) công thức hoàn toàn giống với INDEX trước đó - NHỎ công thức.
Cơ chế giống nhau, INDEX giữ mảng trả về các giá trị dựa trên các kết quả phù hợp được tìm thấy tại AGGREGATE một phần của công thức.
6 đối với tùy chọn hành vi, biểu thị bỏ qua các giá trị lỗi .
Kéo nó xuống, bạn sẽ nhận được tất cả các giá trị phù hợp với tiêu chí.
Hãy nhớ sử dụng CTRL + SHIFT + ENTER để thực thi công thức.
Đọc thêm: Trả về nhiều giá trị trong Excel dựa trên tiêu chí duy nhất (3 tùy chọn)
III. Kết hợp INDEX-MATCH-COUNTIF
Để trả về nhiều giá trị dựa trên nhiều tiêu chí, chúng tôi có thể sử dụng kết hợp INDEX , TRẬN ĐẤU và COUNTIF .
COUNTIF đếm các ô trong một phạm vi đáp ứng một điều kiện. Để biết thêm về chức năng này, hãy truy cập bài viết này: COUNTIF .
Our formula will be the following one
=IFERROR(INDEX($B$4:$B$19,MATCH(0,COUNTIF(H5:$H$5,$B$4:$B$19)+IF($C$4:$C$19<>$H$4,1,0)+IF($D$4:$D$19<>$H$5,1,0),0)),"")
Within the MATCH function, we provided 0 as the lookup_array, and for lookup_range we have used the IF portion containing COUNTIF .
Here, the COUNTIF function excludes any value that has already been fetched. And two IF functions check two conditions. We have added these functions so that they together form the lookup_range .
The MATCH portion returns the value as long as 0 is found. The value here works as the row number for INDEX .
Drag it down, you will get all the values that match the criteria.
Read More: How to Extract Data from Cell in Excel (5 Methods)
IV. FILTER Function
If you are using Excel 365, then you can perform the task with a single built-in function called FILTER .
The FILTER function filters a range of data based on given criteria and extracts matching records. To know about the function, visit this article:FILTER.
Let’s explore the formula
=FILTER(B4:B19,(H4=C4:C19)*(H5=D4:D19))
B4:B19 is the array that is to be filtered. Then we have provided the condition, based on what we will extract values. Since we need to check two criteria, we have multiplied them.
Here you will not need to drag down the formula, at one go this will provide all the values and fulfill the list.
Read More: Extract Filtered Data in Excel to Another Sheet (4 Methods)
Kết luận
That’s all for today. We have listed several methods to extract data from table based on multiple criteria. Hope you will find this helpful. Feel free to comment if anything seems difficult to comprehend. Let us know any other approaches that we have missed here.
Further Readings
- How to Extract Data from Excel to Word (4 Ways)
- Extract Data from One Sheet to Another Using VBA in Excel (3 Methods)
- How to Pull Data from Multiple Worksheets in Excel VBA
- Transfer Data from One Excel Worksheet to Another Automatically
- How to Pull Data From Another Sheet Based on Criteria in Excel
- Excel Macro:Extract Data from Multiple Excel Files (4 Methods)
- How to Extract Year from Date in Excel (3 Ways)