Note: The other languages of the website are Google-translated. Back to English

Làm cách nào để trả về nhiều giá trị phù hợp dựa trên một hoặc nhiều tiêu chí trong Excel?

Thông thường, hầu hết chúng ta đều dễ dàng tra cứu một giá trị cụ thể và trả về mục phù hợp bằng cách sử dụng hàm VLOOKUP. Tuy nhiên, bạn đã bao giờ cố gắng trả về nhiều giá trị phù hợp dựa trên một hoặc nhiều tiêu chí như ảnh chụp màn hình sau đây chưa? Trong bài viết này, tôi sẽ giới thiệu một số công thức để giải quyết công việc phức tạp này trong Excel.

Trả về nhiều giá trị phù hợp dựa trên một hoặc nhiều tiêu chí với công thức mảng


Trả về nhiều giá trị phù hợp dựa trên một hoặc nhiều tiêu chí với công thức mảng

Ví dụ: tôi muốn trích xuất tất cả các tên có tuổi 28 và đến từ Hoa Kỳ, vui lòng áp dụng công thức sau:

1. Sao chép hoặc nhập công thức dưới đây vào một ô trống mà bạn muốn xác định kết quả:

=INDEX($B$2:$B$11, SMALL(IF(COUNTIF($F$2, $C$2:$C$11)*COUNTIF($G$2, $D$2:$D$11), ROW($A$2:$D$11)-MIN(ROW($A$2:$D$11))+1), ROW(A1)), COLUMN(A1))

Chú thích: Trong công thức trên, B2: B11 là cột mà giá trị khớp được trả về từ đó; F2, C2: C11 là điều kiện đầu tiên và dữ liệu cột chứa điều kiện đầu tiên; G2, D2: D11 là điều kiện thứ hai và dữ liệu cột chứa điều kiện này, vui lòng thay đổi chúng theo nhu cầu của bạn.

2. Sau đó nhấn Ctrl + Shift + Enter để nhận kết quả phù hợp đầu tiên, sau đó chọn ô công thức đầu tiên và kéo chốt điền xuống các ô cho đến khi giá trị lỗi được hiển thị, bây giờ, tất cả các giá trị phù hợp được trả về như hình minh họa bên dưới:

Lời khuyên: Nếu bạn chỉ cần trả về tất cả các giá trị phù hợp dựa trên một điều kiện, vui lòng áp dụng công thức mảng bên dưới:

=IFERROR(INDEX($B$2:$B$11, SMALL(IF($F$2=$D$2:$D$11, ROW($D$2:$D$11)-ROW($D$2)+1), ROW(1:1))),"" )


Các bài viết tương đối hơn:

  • Trả lại nhiều giá trị tra cứu trong một ô được phân tách bằng dấu phẩy
  • Trong Excel, chúng ta có thể áp dụng hàm Vlookup để trả về giá trị phù hợp đầu tiên từ một ô trong bảng, nhưng đôi khi, chúng ta cần trích xuất tất cả các giá trị phù hợp và sau đó được phân tách bằng dấu phân cách cụ thể, chẳng hạn như dấu phẩy, dấu gạch ngang, v.v. thành một ô như ảnh chụp màn hình sau được hiển thị. Làm cách nào chúng ta có thể lấy và trả về nhiều giá trị tra cứu trong một ô được phân tách bằng dấu phẩy trong Excel?
  • Vlookup và trả lại nhiều giá trị phù hợp cùng một lúc trong Google Trang tính
  • Chức năng Vlookup thông thường trong Google sheet có thể giúp bạn tìm và trả về giá trị khớp đầu tiên dựa trên một dữ liệu nhất định. Tuy nhiên, đôi khi, bạn có thể cần vlookup và trả về tất cả các giá trị phù hợp như hình minh họa sau. Bạn có cách nào hay và dễ dàng để giải quyết công việc này trong Google sheet không?
  • Vlookup và trả lại nhiều giá trị từ danh sách thả xuống
  • Trong Excel, làm thế nào bạn có thể vlookup và trả về nhiều giá trị tương ứng từ danh sách thả xuống, có nghĩa là khi bạn chọn một mục từ danh sách thả xuống, tất cả các giá trị tương đối của nó được hiển thị cùng một lúc như ảnh chụp màn hình sau. Bài viết này, tôi sẽ giới thiệu giải pháp từng bước.
  • Vlookup và trả về nhiều giá trị theo chiều dọc trong Excel
  • Thông thường, bạn có thể sử dụng hàm Vlookup để nhận giá trị tương ứng đầu tiên, nhưng đôi khi, bạn muốn trả về tất cả các bản ghi phù hợp dựa trên một tiêu chí cụ thể. Bài viết này, tôi sẽ nói về cách vlookup và trả về tất cả các giá trị phù hợp theo chiều dọc, chiều ngang hoặc vào một ô duy nhất.
  • Vlookup và trả về dữ liệu khớp giữa hai giá trị trong Excel
  • Trong Excel, chúng ta có thể áp dụng hàm Vlookup thông thường để nhận giá trị tương ứng dựa trên một dữ liệu nhất định. Tuy nhiên, đôi khi, chúng tôi muốn vlookup và trả về giá trị khớp giữa hai giá trị như ảnh chụp màn hình sau được hiển thị, làm thế nào bạn có thể giải quyết tác vụ này trong Excel?

 


  • Thanh siêu công thức (dễ dàng chỉnh sửa nhiều dòng văn bản và công thức); Bố cục đọc (dễ dàng đọc và chỉnh sửa số lượng ô lớn); Dán vào Dải ô đã Lọchữu ích. Cảm ơn !
  • Hợp nhất các ô / hàng / cột và Lưu giữ dữ liệu; Nội dung phân chia ô; Kết hợp các hàng trùng lặp và Tổng / Trung bình... Ngăn chặn các ô trùng lặp; So sánh các dãyhữu ích. Cảm ơn !
  • Chọn trùng lặp hoặc duy nhất Hàng; Chọn hàng trống (tất cả các ô đều trống); Tìm siêu và Tìm mờ trong Nhiều Sổ làm việc; Chọn ngẫu nhiên ...
  • Bản sao chính xác Nhiều ô mà không thay đổi tham chiếu công thức; Tự động tạo tài liệu tham khảo sang Nhiều Trang tính; Chèn Bullets, Hộp kiểm và hơn thế nữa ...
  • Yêu thích và Chèn công thức nhanh chóng, Dãy, Biểu đồ và Hình ảnh; Mã hóa ô với mật khẩu; Tạo danh sách gửi thư và gửi email ...
  • Trích xuất văn bản, Thêm Văn bản, Xóa theo Vị trí, Xóa không gian; Tạo và In Tổng số phân trang; Chuyển đổi giữa nội dung ô và nhận xéthữu ích. Cảm ơn !
  • Siêu lọc (lưu và áp dụng các lược đồ lọc cho các trang tính khác); Sắp xếp nâng cao theo tháng / tuần / ngày, tần suất và hơn thế nữa; Bộ lọc đặc biệt bằng cách in đậm, in nghiêng ...
  • Kết hợp Workbook và WorkSheets; Hợp nhất các bảng dựa trên các cột chính; Chia dữ liệu thành nhiều trang tính; Chuyển đổi hàng loạt xls, xlsx và PDFhữu ích. Cảm ơn !
  • Nhóm bảng tổng hợp theo số tuần, ngày trong tuần và hơn thế nữa ... Hiển thị các ô đã mở khóa, đã khóa bởi các màu sắc khác nhau; Đánh dấu các ô có công thức / tênhữu ích. Cảm ơn !
tab kte 201905
  • Cho phép chỉnh sửa và đọc theo thẻ trong Word, Excel, PowerPoint, Publisher, Access, Visio và Project.
  • Mở và tạo nhiều tài liệu trong các tab mới của cùng một cửa sổ, thay vì trong các cửa sổ mới.
  • Tăng 50% năng suất của bạn và giảm hàng trăm cú nhấp chuột cho bạn mỗi ngày!
officetab dưới cùng

 

Nhận xét (25)
Chưa có xếp hạng. Hãy là người đầu tiên xếp hạng!
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Tôi đã thử chính xác công thức này; đã sao chép 100%. Điều duy nhất tôi thay đổi là dữ liệu được khớp và trả về. Khi tôi sử dụng công thức này, Excel sẽ thông báo "Bạn đã nhập quá nhiều đối số cho hàm này). = INDEX ('Báo cáo số lượng 2020'! $ B $ 3: $ B $ 100, SMALL (IF (COUNTIF ($ A $ 1, 'Số lượng' 2020) Báo cáo '! $ A $ 3: $ A $ 100) * COUNTIF ($ A $ 3,' Báo cáo số lượng năm 2020 '! $ D $ 3: $ D $ 100), ROW (' Báo cáo số lượng năm 2020 '! $ A $ 3: $ G $ 100) - MIN (ROW ('Báo cáo số lượng năm 2020'! $ A $ 3: $ G $ 100)) + 1, "0"), ROW (A1), COLUMN (A1))
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Xin chào, Bạn có thể cung cấp dữ liệu và lỗi công thức của mình dưới dạng ảnh chụp thử nghiệm ở đây không?
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Xin chào, Làm thế nào tôi có thể sử dụng nó cho điều kiện Ngang.
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Số "0" sau +1 trong công thức là gì? Điều đó không có trong ví dụ.
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Xin chào, tôi đã thử cùng một công thức. đang nhận được kết quả nhưng khi cung cấp CSE, nó không cung cấp bất kỳ phản hồi nào
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web

Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Về Trả lại Nhiều Giá trị Đối sánh Dựa trên Một hoặc Nhiều Tiêu chí Với Công thức Mảng: Tại sao nếu tôi có dữ liệu ở bất kỳ nơi nào khác ngoại trừ bắt đầu bằng A1 mà nó không hoạt động mặc dù tôi cập nhật tất cả các tham chiếu ô trong công thức?
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Trong ví dụ đầu tiên, thay đổi nào đối với công thức sẽ cần thiết để trả về tất cả những người nhỏ hơn 28 tuổi?
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Chào,

Tôi đã tự hỏi liệu có thể nhập tiêu chí thứ 2 nhưng từ cùng phạm vi với tiêu chí thứ nhất hay không,

Ví dụ với ví dụ đã sử dụng ở trên, tôi muốn tìm kiếm tên của những người từ cả Mỹ và Pháp Vì vậy, ô F3 sẽ có Pháp, Scarlett & Andrew cũng sẽ điền vào danh sách trong Cột G

Cảm ơn bạn đã hỗ trợ trước.
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Xin chào nick,

Rất vui được giúp đỡ. Nếu bạn muốn lấy tên của những người từ cả Mỹ và Pháp, tôi khuyên bạn nên sử dụng công thức của chúng tôi hai lần để có được kết quả. Vui lòng xem ảnh chụp màn hình, Ở F2 và G2 là các giá trị "Hoa Kỳ" và "Pháp". Áp dụng công thức = IFERROR (INDEX ($ B $ 2: $ B $ 11, NHỎ (IF ($ F $ 2 = $ D $ 2: $ D $ 11, ROW ($ D $ 2: $ D $ 11)) -ROW ($ D $ 2) +1 ), ROW (1: 1))), "") để nhận kết quả cho Mỹ. Và áp dụng công thức = IFERROR (INDEX ($ B $ 2: $ B $ 11, NHỎ (IF ($ G $ 2 = $ D $ 2: $ D $ 11, ROW ($ D $ 2: $ D $ 11)) -ROW ($ D $ 2) + 1), ROW (1: 1))), "") để nhận kết quả cho Pháp. Nó đơn giản. Xin vui lòng có một thử.

Trân trọng,
Mandy
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Khi tôi sử dụng công thức thứ hai và kéo xuống, không có gì xuất hiện. Kết quả công thức (fx) cho biết nó phải trả về một cái gì đó nhưng nó để trống. Làm cách nào để sửa lỗi này?
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Xin chào Alysia,

Rất vui được giúp đỡ. Tôi đã thử công thức thứ hai trong bài viết và kéo công thức xuống, phần còn lại của kết quả được trả về. Tôi nghĩ rằng có thể có hai lý do cho vấn đề của bạn. Đầu tiên, có thể bạn quên nhấn tổ hợp phím Ctrl + Shift + Enter để nhập công thức. Thứ hai, kết quả đối sánh chỉ là một, vì vậy không có kết quả nào khác không được trả về. Vui lòng kiểm tra.

Trân trọng,
Mandy
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
chào bạn,
tôi đã thử sử dụng công thức và nó tạo ra giá trị 0 hoặc hình ảnh được đính kèm
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Xin chào, Milku
Ảnh chụp màn hình của bạn hiển thị phần mềm WPS của phiên bản MAC, vì vậy tôi không chắc liệu công thức của chúng tôi có khả dụng hay không.
Tôi đã tải một tệp Excel lên đây, bạn có thể thử xem nó có thể tính toán chính xác trong môi trường của bạn hay không.
Cảm ơn bạn!
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Chào bạn,
những gì sẽ cần thiết để mở rộng công thức đầu tiên trong trường hợp sau:
Một số ID trống (ví dụ: ô A5 trống) và tôi muốn có thêm điều kiện xuất dòng chỉ khi các ID không trống. (Vì vậy, đầu ra sau đó phải là James và Abdul.
Cảm ơn!
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Xin chào, Jo,
Để giải quyết vấn đề của bạn, vui lòng áp dụng công thức dưới đây:
=INDEX($B$2:$B$11, SMALL(IF(COUNTIF($F$2, $C$2:$C$11)*COUNTIF($G$2, $D$2:$D$11)*($A$2:$A$11<>0), ROW($A$2:$D$11)-MIN(ROW($A$2:$D$11))+1), ROW(A1)), COLUMN(A1))

Xin vui lòng có một ry, hy vọng nó có thể giúp bạn!
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Chào,

nếu trong ô H1 tôi viết "Tên" và muốn liên kết tên đó với công thức, điều đó sẽ hoạt động như thế nào?
Sau đó, tôi có thể viết "ID" trong ô H1 và kết quả sẽ tự động nhận được: AA1004; DD1009; PP1023 (đối với công thức đầu tiên)

Cảm ơn bạn trước!
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Xin chào, Marie
Xin lỗi, tôi không thể hiểu vấn đề đầu tiên của bạn, bạn có thể giải thích vấn đề của bạn rõ ràng và chi tiết hơn được không? Hoặc bạn có thể chèn ảnh chụp màn hình vào đây để mô tả vấn đề của mình.
Đối với câu hỏi thứ hai, bạn chỉ cần thay đổi tham chiếu ô như sau:
=INDEX($A$2:$A$11, SMALL(IF(COUNTIF($F$2, $C$2:$C$11)*COUNTIF($G$2, $D$2:$D$11), ROW($A$2:$D$11)-MIN(ROW($A$2:$D$11))+1), ROW(A1)), COLUMN(A1))

Nhớ nhấn Ctrl + Shift + Enter chìa khóa với nhau.
Hãy cố gắng, hy vọng nó có thể giúp bạn!
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Heyi, cảm ơn bạn vì công thức. Nó hoạt động cho các giá trị / văn bản "cố định" làm tiêu chí. Tuy nhiên, một trong những tiêu chí tôi đang cố gắng sử dụng là điều kiện (giá trị <> 0), nhưng không hoạt động với công thức được mô tả. Các bạn có biết tôi nên thay đổi những gì để điều chỉnh công thức để tôi có thể có một điều kiện như một trong các tiêu chí không?

Tốt nhất,

João
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Xin chào, Marcus
Để giải quyết vấn đề của bạn, vui lòng xem bài viết này:
https://www.extendoffice.com/documents/excel/6393-excel-vlookup-function.html#b3-2
Có một số giải thích chi tiết về nhiệm vụ này. Bạn chỉ cần thay đổi tiêu chí của riêng bạn.
Cảm ơn bạn!
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Chào,

Đầu tiên, xin cảm ơn bạn đã chia sẻ!

Bạn có thể vui lòng cung cấp giải pháp cho trường hợp dưới đây:

Tôi có 3 cột (A: Chứa thông tin tham khảo, B: Chứa thông tin cần tìm, C: Kết quả tìm kiếm)

Url hình ảnh được cung cấp bên dưới

https://ibb.co/VHCd09K

Cột A ------------------------- Cột B ------------ Cột C
Tên tệp ------------------------- Tên ---------------- Tên tệp, Tên tài liệu, Tên phần tử, Tên
Phần tử đã thay đổi ----------------- Phần tử -------------- Phần tử đã thay đổi, Tên phần tử, ID phần tử
Vị trí cột
Tên tài liệu
Tên phần tử
Họ tên
Phân loại
Chính sách bảo hành
Slope
ID phần tử

Những gì tôi cần là tìm kiếm trong cột A bất kỳ kết quả phù hợp từng phần nào với ô B2 (Tên) hoặc B3 (Phần tử) và nhận kết quả trong một ô,

Cảm ơn bạn, Behzad
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Xin chào, Behzad
Có thể Hàm do người dùng xác định bên dưới có thể giúp bạn.
Public Function ConcatPartLookUp(rngInput As Range, rngSource As Range, Optional strDelimiter As String, Optional blCaseSensitive)
Dim rng As Range
If strDelimiter = "" Then strDelimiter = ","
If IsMissing(blCaseSensitive) Then
    blCaseSensitive = False
Else
    blCaseSensitive = True
End If
For Each rng In rngSource
    If blCaseSensitive Then
        If InStr(1, rng.Value, rngInput.Value, vbBinaryCompare) > 0 Then ConcatPartLookUp = ConcatPartLookUp & strDelimiter & rng.Value
    Else
        If InStr(1, rng.Value, rngInput.Value, vbTextCompare) > 0 Then ConcatPartLookUp = ConcatPartLookUp & strDelimiter & rng.Value
    End If
Next
If Len(ConcatPartLookUp) > 0 Then ConcatPartLookUp = Mid(ConcatPartLookUp, 2, Len(ConcatPartLookUp))
End Function


Sau khi sao chép và dán mã này, sau đó sử dụng công thức này:=ConcatPartLookUp(B2,$A$2:$A$8) để có được kết quả bạn cần.
Xin vui lòng có một thử, hy vọng nó có thể giúp bạn!
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Chào,

Cảm ơn vì đã đăng những ví dụ này.
Tôi đang cố gắng triển khai điều này trong trang tính của riêng mình, nhưng không làm cho nó hoạt động (có thể vì tôi đang sử dụng phiên bản excel của châu Âu)?

Tôi muốn biết ngày của những ngày tôi có ca làm việc của mình hoặc tôi đã làm việc 'một số' (> 0) giờ cho một khách hàng.

Vì vậy, trong I3 là tên và trong J3 là tháng. K3 và L3 là ca (1 đã làm việc) và giờ (không biết làm thế nào để đặt điều này, phải nhiều hơn XNUMX)

Kết quả mong đợi của tôi là:
Dịch chuyển: I7 và I8
giờ: J7

Vì vậy, tôi đã làm việc hơn 0 giờ cho 'người 2' trong oktober vào ngày 3-10-2022
có ca cho người 2 vào '10-10-2022' và 28-10-2022

Khi tôi thêm '= INDEX ($ B $ 2: $ B $ 11, NHỎ (IF (COUNTIF ($ F $ 2, $ C $ 2: $ C $ 11) * COUNTIF ($ G $ 2, $ D $ 2: $ D $ 11), ROW ($ A $ 2: $ D $ 11) -MIN (ROW ($ A $ 2: $ D $ 11)) + 1), ROW (A1)), COLUMN (A1)) 'trong trang tính excel của tôi, nó không cho phép dấu phẩy giữa các phần khác nhau của công thức.
Vì vậy, tôi cần thay đổi chúng thành ';'.
Nhưng khi tôi thử, nó luôn nói: '#NAME?'

Vì vậy, ai đó có thể giúp tôi với điều này?

Kind regards,

Bas
[img] https://drive.google.com/file/d/1iIPQKuj_PNhqWyWlwJ4IQTqGNEd6B9Hw/view?usp=share_link [/ img]
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Xin chào, nếu có các giá trị trùng lặp (ví dụ: hai adam), làm cách nào để đảm bảo rằng nó chỉ trả về 1 adam chứ không phải 2?
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Chào Bobby,
Để chỉ trích xuất các giá trị khớp duy nhất, bạn nên áp dụng công thức dưới đây:
Sau khi dán công thức, vui lòng nhấn Ctrl + Shift + Enter các phím với nhau để có được kết quả chính xác.
=IFERROR(INDEX($B$2:$B$5, MATCH(0, COUNTIF(H1:$H$1, $B$2:$B$5)+IF($D$2:$D$5<>$G$2, 1 , 0)+IF($C$2:$C$5<>$F$2, 1, 0), 0)), "")

Xin vui lòng có một thử, hy vọng nó có thể giúp bạn!
Không có bình luận nào được đăng ở đây
Để lại ý kiến ​​của bạn
Đăng với tư cách khách
×
Đánh giá bài viết này:
0   Nhân vật
Các vị trí được đề xuất

Kết nối với chúng tôi

Bản quyền © 2009 - www.extendoffice.com. | Đã đăng ký Bản quyền. cung cấp bởi ExtendOffice. | BẢN ĐỒ CHI NHÁNH
Microsoft và logo Office là các nhãn hiệu hoặc nhãn hiệu đã đăng ký của Microsoft Corporation tại Hoa Kỳ và / hoặc các quốc gia khác.
Được bảo vệ bởi Sectigo SSL