Hàm tìm kiếm có điều kiện trong excel
Học Excel Online đi sâu vào cách sử dụng kết hợp hàm index và match. Cho bạn khả năng tìm kiếm các điều kiện cũng tương tự trả về nhiều kết quả
INDEX/MATCH dùng như vậy nào?
Cú pháp hay gặp, hay sử dụng với VLOOKUP:Nếu với VLOOKUP, ta bao gồm công thức như sau:=VLOOKUP(Giá trị dò tìm, Vùng tài liệu
Ta bao gồm ví dụ sau, với hàm VLOOKUP chúng ta lưu ý những vấn đề sau:
Bạn đang xem: Hàm tìm kiếm có điều kiện trong excel
Và từ bây giờ chúng ta bao gồm công thức: =VLOOKUP(E2, $A$1:$C$8, 3, 0). Cùng với E2 là giá trị yêu cầu tìm, vào vùng dữ liệu từ A1:C8, cùng dấu $ mang ý nghĩa cố định vùng tài liệu tìm kiếm để khi kéo bí quyết vùng sẽ vắt định. Cột tài liệu trả về là cột thiết bị 3, tính từ vị trí đếm từ cột chứa giá trị dò search sang mặt phải. Và số 0, là tìm chính xác, luôn luôn luôn là số 0.
Vậy với INDEX thì sẽ chuyển đổi như cố nào:=INDEX(Cột kết quả, MATCH(Giá trị dò tìm, Cột dò tìm đựng giá trị đề xuất tìm, Tìm thiết yếu xác/gần đúng)
Dù đã bao gồm phần mềm, nhưng năng lực Excel vẫn rất là quan trọng cùng với kế toán, các bạn đã vững Excel chưa? Hãy nhằm tôi giúp bạn, đăng ký khoá học tập Excel:
Lúc này các bạn sẽ thấy hàm tất cả cú pháp như sau: =INDEX(Vùng kết quả, Dòng, Cột).
Vùng kết quả: $C$1:$C$8, khác với vùng dữ liệu của hàm VLOOKUP, từ bây giờ ta chỉ lựa chọn mỗi vùng tài liệu cột Điểm thi thay bởi vì cả cột chứa giá trị dò tìm.Hàm MATCH(Giá trị dò tìm, Vùng tra cứu kiếm, Tìm thiết yếu xác/tương đối). Từ bây giờ E3 là cực hiếm dò tìm, với vùng kiếm tìm kiếm chỉ cần đúng vùng cột cất giá trị dò tìm: A1:A8, tương tự VLOOKUP, vùng tìm kiếm kiếm luôn phải cố định vùng $A$1:$A$8. Và luôn luôn tìm chính xác, là số 0 hoặc FALSE.Với cách làm trên, ta thấy hàm MATCH đang trả về quý giá là 2, tương ứng dòng kiếm tìm thấy từ trên xuống. INDEX($C$1:$C$8, 2) => tác dụng là 7.Vì sao lại nói sử dụng INDEX/MATCH dễ dàng hơn VLOOKUP?
VLOOKUP đòi hỏi cột đựng giá trị dò tìm nên nằm quanh đó cùng phía bên trái vùng dữ liệu. Nếu nằm cạnh sát phải thì từ bây giờ phải sử dụng hàm mảng kết hợp với hàm CHOOSE để lấy công dụng tương ứng. Vậy cùng xem lại ví dụ, bạn chỉ việc quét vùng chọn cột kết quả, tìm trong cột cất giá trị dò tìm. Rứa là xong!
Ngược lại với lấy ví dụ trước đó, họ có cột Lớp nằm xung quanh cùng mặt trái, và câu hỏi là tự tên học tập viên, họ sẽ tìm ra lớp của học viên đó. Các bạn sẽ viết hàm VLOOKUP theo như thông thường thế nào? nghĩ xem nhé? Vậy với hàm VLOOKUP, các bạn phải dùng phối hợp hàm CHOOSE, cùng với cú pháp =CHOOSE(1,2, Cột chứa giá trị dò tìm, Cột Kết quả).
Vậy ta có cú pháp tổng quát như sau: =VLOOKUP(Giá trị dò tìm, CHOOSE(1,2, Cột đựng giá trị dò tìm, Cột kết quả), Cột trả về<2>, Tìm chính xác <0>)
Nếu dấu phân cách của công ty là dấu chấm phẩy, thì cách làm sẽ là: =VLOOKUP(Giá trị dò tìm; CHOOSE(1 2; Cột chứa giá trị dò tìm; Cột kết quả); Cột trả về<2>; Tìm chính xác <0>)
Với INDEX/MATCH thì các bạn thấy vẫn như lấy một ví dụ 1, =INDEX(Vùng kết quả, MATCH(Giá trị dò tìm, Vùng dò tìm, Tìm chủ yếu xác). Đơn giản rồi bắt buộc không nào?
Dùng VLOOKUP/CHOOSE hoặc INDEX/MATCH tìm theo không ít điều kiện
Ta có ví như trên, lúc này có 2 các bạn “Nguyễn Thị Đét” thuộc tên học 2 lớp khác nhau, tương tự với 2 điều kiện để họ tìm ra điểm thi của từng bạn. Vậy làm cầm nào để tìm ra? vẫn là hàm VLOOKUP/CHOOSE, từ bây giờ bạn đề xuất ghép 2 đk với nhau bằng dấu và (dấu “and”/”và”), cùng với việc ghép 2 cột chứa giá trị dò search với nhau cũng với dấu &. Ta tất cả cú pháp như sau:
=VLOOKUP(
Và đây là bí quyết mảng, đòi hỏi chúng ta phải nhận CTRL+SHIFT+ENTER, thay do Enter (trả về #NA), bây giờ các các bạn sẽ thấy bao gồm móc sừng trâu xuất hiện trong công thức.
Tại sao lại MATCH dịp tìm 1, cơ hội lại là TRUE với lúc nào buộc phải nhấn CTRL+SHIFT+ENTER?
Ví dụ 3: INDEX/MATCH tra cứu kiếm theo khá nhiều điều kiệnVới INDEX/MATCH, các bạn có cú pháp như sau:
=INDEX(Vùng kết quả, MATCH(1,(
Và đó là công thức mảng, phải phải bao gồm nhấn CTRL+SHIFT+ENTER. Vì sao dịp lại là 1, thời điểm lại TRUE? khi chúng ta chỉ có một biểu thức, lúc này kết quả đã trả về TRUE/FALSE, khi gồm 2 biểu thức TRUE*TRUE, Excel sẽ đưa TRUE thành 1*1 = 1.
Giá trị dò search là TRUE, khi gồm một biểu thức: (Biểu thức so sánh) => (Dùng INDEX/MATCH phối hợp INDEX để thay đổi công thức mảng thành công xuất sắc thức bình thường.
Xem thêm: Những Câu Nói Gì Để Người Yêu Vui Hay Nhất Đánh Bay Cơn Giận
Trong lấy một ví dụ 3, chúng ta làm quen với bí quyết mảng, đòi hỏi thao tác yêu cầu nhấn CTRL+SHIFT+ENTER, để tránh câu hỏi phải làm thao tác làm việc này, chúng ta có thể kết vừa lòng thêm hàm INDEX bên trong hàm MATCH để trả về giá trị thứ nhất trong list MATCH tìm thấy.
Ví dụ 4: INDEX/MATCH dò tìm theo nhiều điều kiệnVới cú pháp từ ô G7, G4 trong ví dụ bên trên ta có:
=INDEX(Vùng kết quả, MATCH(TRUE, INDEX(Biểu thức, 0), 0))=INDEX(Vùng kết quả, MATCH(1, INDEX((Biểu thức 1)*(Biểu thức 2), 0), 0))Lưu ý, luôn có 2 cái số “, 0), 0)”, số 0 thứ nhất cho hàm INDEX(Biểu thức,0). Số 0 cuối cùng cho hàm MATCH(,,0).Làm báo cáo chi tiết, trích lọc hóa đối chọi với INDEX kết hợp COUNTIFS. Công dụng trả về nhiều tác dụng từ một hoặc nhiều điều kiện.
Với quý giá dò tìm kiếm “Nguyễn Thị Đét” bạn có tương đối nhiều kết quả trả về, vậy gồm cách nào liệt kê được tất cả hiệu quả không? Câu vấn đáp là có. Với cú pháp (0=COUNTIFS(<$<Ô đầu tiên trả về kết quả>:<Ô thứ nhất trả về kết quả>>, Vùng kết quả), vào ví dụ: (0=COUNTIFS($F$1:F1,$B$1:$B$8)). Nghĩa là: Xét thêm đk đã trả về hiệu quả trước kia hay chưa? Nếu vẫn trả về tác dụng rồi, thì loại bỏ để lấy cái tiếp theo. Từ bây giờ ta đang có kết quả mong muốn.
Kết quả trả về #NA là không kiếm thấy nữa, nhằm không hiển thị lỗi, bạn cũng có thể dùng hàm IFERROR(Công thức, “”).Biểu thức điều kiện theo thương hiệu đầu tiên, mình cần cố định cả ô $E$2, nhằm khi kéo bí quyết xuống sẽ cố định ô quý hiếm dò tìm.$F$1:F1, bởi vì sao chỉ cố định cái đầu tiên, vị để lúc kéo xuống bên dưới, nó sẽ trở thành $F$1:F<2->n>.Làm cố gắng nào nhằm in phiếu xuất kho có nhiều sản phẩm? và một phiếu, có rất nhiều mã sản phẩm khác nhau
Ví dụ với cùng 1 mã xuất kho, bạn sẽ xuất ra nhiều sản phẩm khác nhau. Lúc này in phiếu xuất kho, các bạn chỉ câu hỏi nhập mã phiếu xuất kho, đã trả về danh sách thành phầm tương ứng.
Ta lập cột phụ tham chiếu theo mã phiếu xuất kho, từ bây giờ COUNTIFS làm nhiệm vụ đánh số đồ vật tự giúp bọn chúng ta. Vẫn luôn là cột dây vào 1 đầu cột, dây còn lại thả thoải mái để diều bay cao: $A$2:A2 => COUNTIF($A$2:A2, $E$9), và $E$9 là cực hiếm dò tìm, cũng phải thắt chặt và cố định để lúc kéo xuống bọn họ không thay đổi điều kiện tìm kiếm. Từ bây giờ các các bạn sẽ thấy số tăng cao theo vùng Mã phiếu xuất kho, nếu không kiếm thấy nữa, thì chỉ là lặp lại cái ở đầu cuối tìm thấy.
Lúc này quay trở lại Sheet Phiếu Xuất Kho để in ấn, chỉ bài toán lập công thức khớp ứng như sau, mình giải thích từ trái sang:
Sản phẩm: =INDEX(Vùng tác dụng < các cột>, MATCH(ROW(1:1), Vùng cột phụ,0), Cột trả về). Ta bao gồm Vùng hiệu quả là B1:D6, cơ hội này chúng ta có thể vận dụng chỉ cột B1:B6 cũng được, nhưng chúng ta cũng có thể trả về cột khớp ứng ta hy vọng muốn. Ở trên đây ta có vùng B1:D6, cột trả về là 1 trong những = cột B. Còn ROW(1:1) là gì? ROW(1:1) trả về 1. Cùng khi chúng ta kéo bí quyết xuống B13, nó biến ROW(2:2) = 2.Số lượng: Ở đây chúng ta thấy chỉ khác mỗi Cột trả về đúng không? tại sao lại là COLUMN(B1)? Hàm COLUMN(B1) sẽ trả về công dụng cột B1 là cột bao nhiêu, tức là 2. Lúc kéo quý phái phải, nó trở nên COLUMN(C1), tức là 3. Vậy lúc kéo quý phái trái địa điểm cột thành phầm thì nó thành gì? chúng ta đoán xem? Là COLUMN(A1), có nghĩa là 1. Tiếng thì bạn hiểu bởi sao bản thân để công thức cho chúng ta thấy rồi yêu cầu không?Chỗ #NA của STT với Sản phẩm, mình cố tình để tác dụng như vậy, nếu bạn có nhu cầu không hiển thị #NA, hãy sử dụng IFERROR theo cột số lượng và Kho nhé!Để tham khảo thêm các chúng ta có thể sử dụng tác dụng tìm kiếm trên web trên ô tìm kiếm kiếm, hoặc tìm kiếm cùng với Google, hãy thêm trường đoản cú khóa “kemhamysophie.com” + “từ khóa”. Ví dụ: “kemhamysophie.com”,”INDEX/MATCH”.
