Cách nhận nhiều giá trị từ cùng tiêu chí trong Microsoft Excel 2010
Trong bài viết này, chúng ta sẽ tìm hiểu Cách Nhận Nhiều Giá trị từ Cùng một Tiêu chí trong Microsoft Excel 2010.
Dễ dàng tìm kiếm một giá trị bằng một khóa duy nhất trong bảng. Chúng ta chỉ có thể sử dụng hàm VLOOKUP. Nhưng khi bạn không có cột duy nhất đó trong dữ liệu của mình và cần phải tra cứu trong nhiều cột để khớp với một giá trị, thì hàm VLOOKUP không giúp được gì.
Vì vậy, để tra cứu một giá trị trong một bảng có nhiều tiêu chí, chúng ta sẽ sử dụng công thức INDEX-MATCH-INDEX.
Công thức chung để tra cứu nhiều tiêu chí
|
=INDEX(lookup_range,MATCH(1,INDEX((criteria1 =range1)(criteria2=range2)(criteriaN=rangeN),0,1),0)) |
_lookup_range: _ Đây là phạm vi mà bạn muốn lấy giá trị.
_Criteria1, Criteria2, Criteria N: _ Đây là những tiêu chí bạn muốn đối sánh trong dải ô1, dải ô2 và dải ô N. Bạn có thể có tối đa 270 tiêu chí – cặp phạm vi.
_Range1, range2, rangeN: _ Đây là những phạm vi mà bạn sẽ phù hợp với tiêu chí tương ứng của mình.
Nó sẽ hoạt động như thế nào? Hãy xem… ===== INDEX và MATCH với nhiều ví dụ về tiêu chí Ở đây tôi có một bảng dữ liệu. Tôi muốn kéo Tên khách hàng bằng Ngày đặt phòng, Nhà xây dựng và Khu vực. Vì vậy, ở đây tôi có ba tiêu chí và một phạm vi tra cứu.
Viết công thức này vào ô I4 nhấn enter.
|
=INDEX(E2:E16,MATCH(1,INDEXI1=A2:A16)(I2=B2:B16)(I3=C2:C16),0,1),0 |
Cách thức hoạt động:
Chúng tôi đã biết cách INDEX và hàm MATCH hoạt động trong EXCEL, vì vậy tôi sẽ không giải thích điều đó ở đây. Chúng tôi sẽ nói về thủ thuật mà chúng tôi đã sử dụng ở đây.
(I1 = A2: A16) (I2 = B2: B16) (I3 = C2: C16): Phần chính là cái này. Mỗi phần của câu lệnh này trả về một mảng true false.
Khi các giá trị boolean được nhân lên, chúng sẽ trả về mảng 0 và 1.
Phép nhân hoạt động như toán tử AND. Rất tiếc khi tất cả giá trị chỉ đúng thì nó trả về 1 khác 0 (I1 = A2: A16) (I2 = B2: B16) (I3 = C2: C16) Giá trị này hoàn toàn sẽ trả về [width = “100%”, cols = “100 % “,]
|
\{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}* \{FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE}* \{FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE} |
Nó sẽ dịch thành
|
\{0;0;0;0;0;0;0;1;0;0;0;0;0;0;0} |
INDEX ((I1 = A2: A16) (I2 = B2: B16) (I3 = C2: C16), 0,1): Hàm INDEX sẽ trả về cùng một mảng (\ {0; 0; 0; 0; 0; 0 ; 0; 1; 0; 0; 0; 0; 0; 0; 0}) thành hàm MATCH dưới dạng mảng tra cứu.
MATCH (1, INDEX ((I1 = A2: A16) (I2 = B2: B16) (I3 = C2: C16), 0,1): Hàm MATCH sẽ tìm kiếm 1 trong mảng \ {0; 0; 0; 0 ; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0}. Và sẽ trả về số chỉ mục của 1 đầu tiên được tìm thấy trong mảng. Ở đây là 8.
INDEX (E2: E16, MATCH (1, INDEX I1 = A2: A16) (I2 = B2: B16) (I3 = C2: C16), 0,1), 0:
Cuối cùng, INDEX sẽ trả về giá trị từ phạm vi đã cho (E2: E16) tại chỉ mục tìm thấy (8).
Đơn giản?. Xin lỗi, không thể làm cho nó đơn giản hơn.
Giải pháp mảng Nếu bạn có thể nhấn CTRL + SHIFT + ENTER sau đó bạn có thể loại bỏ hàm INDEX bên trong. Chỉ cần viết công thức này và nhấn CTRL + SHIFT ENTER.
|
=INDEX(E2:E16,MATCH(1,(I1=A2:A16)(I2=B2:B16)(I3=C2:C16),0)) |
Công thức mảng chung cho Tra cứu nhiều tiêu chí
|
=INDEX(lookup_range,MATCH(1,(criteria1 =range1)(criteria2=range2)(criteriaN=rangeN),0)) |
Công thức hoạt động giống nhau như giải thích ở trên.
Hy vọng bài viết này về Cách Nhận Nhiều Giá trị từ Cùng một Tiêu chí trong Microsoft Excel 2010 là giải thích. Tìm thêm bài viết về trích xuất giá trị và các công thức Excel liên quan tại đây. Nếu bạn thích blog của chúng tôi, hãy chia sẻ nó với bạn bè trên Facebook. Và bạn cũng có thể theo dõi chúng tôi trên Twitter và Facebook. Chúng tôi rất muốn nghe ý kiến của bạn, hãy cho chúng tôi biết cách chúng tôi có thể cải thiện, bổ sung hoặc đổi mới công việc của mình và làm cho nó tốt hơn cho bạn. Viết thư cho chúng tôi tại [email protected].