Bạn đã bao giờ phải tạo nhiều câu lệnh IF chưa?
Và tôi có
Tuy nhiên, những gì tôi đã khám phá ra không mạnh bằng câu lệnh IF, tuy nhiên, điều này cung cấp cho bạn một cách khác để tạo câu lệnh IF lớn nếu bạn cần khớp các giá trị chính xác
Về mặt kỹ thuật, tôi chưa thực sự xác định được tất cả các khả năng của kỹ thuật này, nhưng điều tôi biết là nếu bạn có một câu lệnh IF lồng nhau thực hiện so sánh 1-1 cơ bản, thì đây là một giải pháp thay thế khá thú vị và rõ ràng hơn là sử dụng
Chỉ để được rõ ràng
Cho đến nay, tôi thấy rằng điều này hoạt động tốt nhất để khớp chính xác trong câu lệnh IF
Câu lệnh IF theo cách thông thường
Đây là một ví dụ. Giả sử bạn có các yêu cầu sau
Dữ liệu đầu ra mong muốn Có Chắc chắn Không Chắc chắn Không Có thể EhMột công thức IF khá chuẩn để lấy dữ liệu và biến nó thành đầu ra mong muốn sẽ là
=IF(A1="Yes","Definitely",IF(A1="No","Certainly Not",IF(A1="Maybe","Eh")))
Điều này hoạt động tốt, nhưng chú ý cách A1 được lặp lại? . Tuy nhiên, bạn đã bao giờ gặp một hàm INDEX+MATCH phức tạp chưa? . Nói rằng tôi có dữ liệu
NgườiPhản hồiJosephCóJohnCó lẽMeredithKhông có tên nữathêm phản hồiTôi muốn tra cứu phản hồi của một người bằng hàm INDEX+MATCH. tôi sẽ sử dụng một cái gì đó như
=INDEX($A$1:$B$4,MATCH(D2,$A$1:$A$4,0),2)
Giả sử rằng lookup_value mong muốn nằm trong D2 như hình bên dưới
Bây giờ tôi muốn chuyển từ “Có” thành “Chắc chắn rồi” và các câu trả lời khác thành “Chắc chắn là không” và “Ơ” như trước đây. Công thức sẽ giống như
=IF(INDEX($A$1:$B$4, MATCH(D2,$A$1:$A$4,0),2)="Yes","Definitely", IF(INDEX($A$1:$B$4, MATCH(D2,$A$1:$A$4,0),2)="Maybe","Eh", IF(INDEX($A$1:$B$4, MATCH(D2,$A$1:$A$4,0),2)="No","Certainly Not")))
không đẹp lắm. Nó hơi phức tạp và không dễ theo dõi nếu bạn nhìn vào thanh công thức trong Excel với công thức này. Ngoài ra, nếu bạn phải thay đổi công thức INDEX, bạn phải thay đổi nó ở 3 điểm (điều mà tôi luôn làm khó chịu - với tư cách là nhà phát triển, bạn cố gắng không lặp lại bất kỳ mã nào nếu có thể)
Vì vậy, làm thế nào tôi có thể làm cho nó sạch hơn?
Bài viết này có giúp ích gì cho bạn không?
Thay thế cho nhiều câu lệnh IF
Điều này hơi không chính thống, nhưng nó khá gọn gàng cho những thứ như thế này
Sử dụng INDEX và MATCH với các hằng số mảng để ánh xạ các giá trị khớp với kết quả mong muốn
Nếu bạn chưa quen với hằng mảng, thì nó khá đơn giản. Sử dụng cặp dấu ngoặc nhọn { và }, bạn có thể tạo hằng mảng dữ liệu. Trong trường hợp hàm INDEX và MATCH, chúng thực sự chấp nhận các tham số mảng, vì vậy bạn có thể đặt cho nó một Phạm vi (trường hợp này thường xảy ra) hoặc bạn có thể sử dụng các dấu ngoặc nhọn này để tạo các hằng số mảng. John Walkenbach đã viết một bài hay về chủ đề này. Vui lòng xem nếu bạn muốn biết thêm thông tin
Đây là định dạng
=INDEX({desired results}, MATCH(lookup_value, {values to compare},0))
MụcGiải thíchkết quả mong muốnĐây sẽ là danh sách “Chắc chắn,” “Chắc chắn là không,” và “Eh”lookup_valueĐáng ngạc nhiên, đây sẽ là công thức INDEX+MATCH chính mà chúng ta phải lặp lại trong nhiều câu lệnh if trước đó. Về cơ bản, phần này của công thức là phần sẽ được đánh giá, giống như tham số đầu tiên trong câu lệnh IF. các giá trị để so sánh Đây sẽ là danh sách “Có”, “Không” và “Có thể”Đặt cái này lại với nhau, bây giờ chúng ta có công thức sau
=INDEX({"Definitely","Certainly Not","Eh"},MATCH( INDEX($A$1:$B$4,MATCH(D2,$A$1:$A$4,0),2), {"Yes","No","Maybe"},0))
Tôi đã nói rằng nó sẽ là một chút không chính thống. ) nhưng hãy kiểm tra xem. Bạn chỉ phải viết hàm INDEX+MATCH chính một lần và bạn không có dấu ngoặc đơn. Đáng kinh ngạc
Bây giờ, tôi biết rằng đây là một ví dụ rất đơn giản, nhưng nó chỉ để minh họa điểm
Làm thế nào điều này hoạt động
Để làm được điều này, bạn phải tuân theo 3 quy tắc
- Hằng số mảng =INDEX($A$1:$B$4,MATCH(D2,$A$1:$A$4,0),2)0 phải khớp với hằng số mảng đầu ra mong muốn =INDEX($A$1:$B$4,MATCH(D2,$A$1:$A$4,0),2)0. Vì “Chắc chắn” ở vị trí đầu tiên, nên nó khớp với “Có. ” Điều này rất quan trọng để đảm bảo bạn có được đầu ra mong muốn chính xác
- Tham số đầu tiên trong MATCH phải chứa một giá trị được tìm thấy trong hằng số mảng INDEX
- Bạn phải đeo kính mát và ăn bánh sandwich gà tây
Chà, ok, bạn chỉ cần 2 quy tắc đầu tiên, nhưng bạn hiểu rồi. )
Hãy xem chức năng từ E2 bên dưới
Công thức bắt đầu với hàm INDEX bên trong hàm MATCH. Nó tìm thấy “Joseph” và di chuyển qua một ô để tìm “Có”
=INDEX({"Definitely","Certainly Not","Eh"},MATCH("Yes", {"Yes","No","Maybe"},0))
“Có” khớp với “Có” đầu tiên trong hằng số mảng và do đó, nó sẽ trả về 1 vì MATCH sẽ trả về số hàng mà nó tìm thấy
=INDEX({"Definitely","Certainly Not","Eh"},1)
Hàng số 1 trong hàm INDEX sẽ khớp với mục “Chắc chắn” trong hằng số mảng của INDEX, sau đó sẽ trả về “Chắc chắn”
________số 8
Điều này dẫn đến kết quả mong muốn là “Chắc chắn. ”
Đúng là về mặt kỹ thuật, bạn có thể sử dụng Phạm vi thay vì hằng số mảng, tuy nhiên, tôi muốn minh họa nó theo cách này vì hai lý do
- Cách này đơn giản hơn một chút cả trong giảng dạy và thực hành
- Tôi không phải lúc nào cũng đồng ý với việc tạo toàn bộ trang tính chỉ để chứa một danh sách đơn giản như thế này
Tuy nhiên, nếu các danh sách này rất lớn, thì tôi khuyến khích duy trì các phạm vi đã đặt tên cho chúng để các công thức rõ ràng hơn nhiều
Vâng, đó là nó. Tôi hy vọng bạn tìm thấy thông tin này độc đáo và thú vị. ) Cho đến khi chúng ta gặp lại nhau
Wow, bạn đã đọc toàn bộ bài báo. Bạn biết đấy, những người tiến xa đến mức này là những người học thực sự. Và rõ ràng, bạn coi trọng việc học. Bạn có muốn tìm hiểu thêm về Excel? . )