Hướng dẫn cách lấy thông tin dựa trên nhiều điều kiện trong các cột khác nhau

Kỹ năng vận dụng Excel để thực hiện tham chiếu dữ liệu thông qua hàm từ cột điều kiện là điều phổ biến. Nhưng làm thế nào nếu tiến hành theo cách ngược lại? Siêu Marketing trong bài này sẽ chỉ dẫn bạn cách để tìm ra giá trị từ nhiều cột điều kiện cùng một lúc.

Khởi đầu với vấn đề tìm kiếm từ nhiều điều kiện khác nhau trong các cột

Thời đi học, chúng ta thường được học về việc áp dụng hàm VLOOKUP – một phương pháp căn bản:

Bài toán đặt ra: Áp dụng hàm VLOOKUP để xác định Phương tiện 1 liên kết với Đường thủy.

Quá trình này được tiến hành đơn giản qua công thức: =VLOOKUP(giá trị cần tìm, phạm vi tìm kiếm, số thứ tự cột có dữ liệu cần lấy, kiểu tìm kiếm)

Sau khi thành thạo, chúng ta có thể sử dụng các hàm phức hợp hơn như tổ hợp INDEX/MATCH để thay thế cho VLOOKUP: =INDEX(cột muốn tìm kiếm, MATCH(giá trị cần tìm, cột tìm kiếm, kiểu tìm kiếm))

 

Chúng ta giờ sẽ nhìn vào một thách thức lớn hơn: Danh sách tất cả phương tiện theo từng loại hình vận chuyển.

Lời giải cho bài toán này đã sẵn sàng, bạn có thể xem tại đây hoặc đây.

Lý thuyết đã rõ, bây giờ ta sẽ giải quyết như thế nào với:

Làm sao để tìm kiếm dữ liệu một cách tổng quát và ngược lại?

Phát biểu cụ thể: Dựa vào tên của phương tiện dưới đây, hãy kiến lập bảng liệt kê loại hình phù hợp.

Tra cứu ngược bằng thủ công cho từng item cụ thể

INDEX/MATCH

Phương pháp INDEX và MATCH luôn hữu ích trong tìm kiếm thông tin. Trong ví dụ này, công thức có thể được áp dụng đối với mỗi phương tiện như sau:

=INDEX($A$2:$A$5,MATCH(A8,$B$2:$B$5,0))

VLOOKUP cùng CHOOSE

Công cụ VLOOKUP phối hợp CHOOSE cũng là một giải pháp tra cứu ngược. Ta thực hiện như sau:

=VLOOKUP(A8,CHOOSE({1,2},$B$2:$B$5,$A$2:$A$5),2,0)

 

Sau đây ta sẽ xử lý tiếp như thế nào?

Chắc chắn là không thể sử dụng chức năng Autofill để điền tiếp xuống dưới.

Ở tình huống này, ta cần xem xét đến các phương án thay thế khác. Bài viết dưới đây của Siêu Marketing sẽ giới thiệu cho bạn cách sử dụng hàm INDEX.

Xác định giá trị từ nhiều cột điều kiện khác nhau và trả về kết quả tương ứng

Thiết lập logic cho công thức tìm kiếm

Dưới đây là bố cục mong đợi cho công thức:

 

Phải làm sao để “tự xác định” giá trị của row_num? Chú ý đến 2 yếu tố sau đây:

1. row_num thực chất là số thứ tự của dòng nơi có các phương tiện, với Ô tô ở dòng 2, Tàu thuỷ ở dòng 3, Tàu hỏa ở dòng 4,…

2. Dữ liệu liên quan đến phương tiện được lấy từ cột “Phương tiện” dưới cùng và có thể biến đổi theo cả hai hướng, ngang và dọc.

Do không thể sử dụng MATCHđối tượng cần tham khảo là một mảng đa hướng, chúng ta phải tìm cách liên kết như sau:

Lấy thông tin tham khảo từ cột Phương tiện đề cập đến table giá trị gốc, sau đó tạo mảng mới và nhân với số dòng tương ứng, kết hợp với row_num để trả lại kết quả thông qua hàm INDEX.

  • Công cụ để tạo một mảng trả kết quả được gọi là công thức mảng.
  • Sử dụng số dòng từ bảng Loại hình đối sánh với cột Phương tiện và ngược lại.

Rèn luyện công thức

Công thức, từ trong ra ngoài, được mô tả như sau:

=$B$2:$E$5=A8

Kế đến, bạn nhấn Ctrl + Shift + Enter. Mảng sẽ hiển thị như sau (nhấn F9 để xem trong thanh công thức):

Bây giờ, để thay đổi TRUE thành 1 và FALSE thành 0, ta dùng cách sau:

=–($B$2:$E$5=A8) hoặc =N($B$2:$E$5=A8)

Xem thêm thông tin về hàm N tại đây

Với những giá trị số đã có, ta sẽ tiếp tục nhân chúng với số thứ tự của các dòng để thu được giá trị mong muốn:

Chúng ta sử dụng hàm để lấy số thứ tự dòng là Hàm ROW

Mạch lạc cho hàm ROW được hiểu là: =ROW($A$2:$A$5).

Phép nhân mảng diễn ra như sau:

=N($B$2:$E$5=A8)*ROW($A$2:$A$5) (Nhấn Ctrl+Shift+Enter)

Kết quả thu được sẽ trình bày như sau:

Để thu được kết quả dưới dạng số, chúng ta cần áp dụng phương pháp nào? Một giải pháp là sử dụng hàm SUMPRODUCT hoặc hàm SUM trong trường hợp của mảng.

Khi dùng hàm SUMPRODUCT ta chỉ việc nhấn Enter, trong khi đó với hàm SUM lại yêu cầu tổ hợp phím Ctrl + Shift + Enter.

Nếu bạn vẫn muốn dùng hàm SUM thì sao? Bạn cần phải thêm một hàm INDEX để chuyển công thức từ mảng sang dạng thông thường.

=INDEX(N($B$2:$E$5=A8)*ROW($A$2:$A$5),0)

  • =SUM(INDEX(N($B$2:$E$5=A8)*ROW($A$2:$A$5),0))

Sau cùng, bạn cần đưa công thức vào hàm INDEX mà chúng ta đã sử dụng lúc đầu để xuất ra kết quả cần tìm và sử dụng Autofill cho các ô khác:


Tìm hiểu thêm về các hàm khác:

Bí quyết võ công 1: INDEX/MATCH

Bí quyết võ công 2: SUMPRODUCT

Những mẹo tra cứu thông tin trong Excel

VLOOKUP, INDEX trả kết quả dạng hình ảnh

 

 

 

 

Trương Thành Tài

    [submission_id id-lien-he]

    0
      0
      Đơn hàng
      Đơn hàng trốngQuay lại Shop