Khi bạn cần truy cập dữ liệu từ nhiều trang tính hoặc bảng, việc tìm kiếm dữ liệu về mặt vật lý có thể rất tẻ nhạt. Google Trang tính giúp giải quyết vấn đề này với tính năng VLOOKUP.
Sử dụng hàm VLOOKUP hoặc tính năng tra cứu theo chiều dọc, bạn có thể để Google Trang tính tự động tra cứu các giá trị và trích xuất dữ liệu phù hợp từ một bảng khác. Bảng này có thể nằm trên cùng một trang tính hoặc trên một trang tính khác.
Vấn đề duy nhất là cú pháp của hàm VLOOKUP chỉ cho phép bạn tra cứu từng cột một.
Vì vậy, nếu bạn có nhiều tiêu chí hoặc muốn kiểm tra nhiều cột liên quan cùng một lúc, bạn cần tìm cách khắc phục hạn chế này. Điều này có thể được thực hiện bằng cách kết hợp tính năng VLOOKUP với các tính năng khác của Google Trang tính.
Cú pháp của hàm VLOOKUP thông thường
Thông thường, hàm VLOOKUP có cú pháp sau:
= VLOOKUP (khóa_ tìm kiếm, phạm vi, chỉ mục, [is_sorted])
Đây,
- khóa_tìm_kiếm là giá trị khoá bạn muốn tìm kiếm trong bảng khác. Nó có thể là một giá trị hoặc một tham chiếu đến một ô chứa một giá trị.
- phạm vi là phạm vi ô (trong bảng nguồn) mà bạn muốn hàm VLOOKUP tìm kiếm khóa tìm kiếm. Đừng quên đảm bảo rằng phạm vi này chứa một cột với khóa_tìm_kiếm là cột đầu tiên. Đồng thời đảm bảo rằng nó chứa một cột có giá trị mục tiêu của bạn (mà bạn muốn lấy).
- chỉ mục là số cột trong phạm vi chứa giá trị mục tiêu mà bạn muốn lấy. Lưu ý rằng cột đầu tiên trong phạm vi có chỉ số 1, cột thứ hai có chỉ số 2, v.v.
- is_sorted là một tham số tùy chọn. Nó có thể là TRUE hoặc FALSE. Nó chỉ định xem cột tìm kiếm có nên được sắp xếp hay không.
Ví dụ, trong hình ảnh sau đây, chúng ta có hai bảng. Một chứa thông tin cá nhân về nhân viên và một chứa tổng doanh số bán hàng của họ trong một tháng cụ thể.
Cả hai bảng đều có một cột chung, id nhân viên, vì vậy nó có thể được coi là một khóa-giá trị hoặc id duy nhất trong cả hai bảng.
Để hiển thị tỷ lệ theo giờ, chẳng hạn như ID nhân viên “E010”, bạn cần lấy ID đó từ bảng “Nhân viên” bằng cách sử dụng hàm VLOOKUP. Đây là những gì bạn sẽ nhập vào ô E3:
= VLOOKUP (A3, $ A $ 3: $ C $ 8,3, sai)
Vì vậy, bạn nhận được kết quả sau:
Khi nào chúng ta cần hàm VLOOKUP theo nhiều tiêu chí trong Google Trang tính?
Trường hợp trên chỉ liên quan đến việc tìm kiếm một tiêu chí phù hợp với id nhân viên. Tuy nhiên, trong hầu hết các trường hợp, tiêu chí này phức tạp hơn.
Sự đa dạng của tiêu chí VLOOKUP có thể do một số nguyên nhân. Dưới đây là một số ví dụ:
- Bạn có thể có các cột riêng biệt cho họ và tên trong bảng nguồn của mình. Vì vậy, bạn sẽ cần phải nhìn vào cả hai cột để nhận được giá trị tương ứng, như hình dưới đây:
- Có thể cần phải kiểm tra 2 hoặc nhiều điều kiện để nhận giá trị. Ví dụ: bạn có thể chỉ muốn điểm của những sinh viên đã làm bài kiểm tra và học tiếng Pháp, như được hiển thị bên dưới:
- Bạn có thể cần phải tìm kiếm một bảng riêng để tìm mức lương của nhân viên thuộc một bộ phận cụ thể trong một mã vùng cụ thể.
Có nhiều tình huống khác giống như những trường hợp được liệt kê ở trên. Nhưng bạn sẽ có được điểm.
Bạn có thể tranh luận rằng sử dụng FILTER sẽ là một lựa chọn dễ dàng hơn cho các tình huống trên. Tuy nhiên, FILTER không thể lấy dữ liệu từ trang tính khác.
Việc sử dụng các hàm IF cũng sẽ là dư thừa, vì khi đó bạn sẽ phải đối phó với nhiều IF lồng nhau (và không ai thích điều đó!).
Cách VLOOKUP nhiều tiêu chí trong Google Trang tính
Hãy lấy ví dụ sau, trong đó bạn có một bảng chứa tiền thưởng tương ứng với từng bộ phận và mã vùng.
Trong Bảng 2, chúng ta cần tìm tiền thưởng tương ứng với một bộ phận cụ thể trong một mã vùng cụ thể và hiển thị giá trị kết quả trong cột Tiền thưởng (cột E).
Điều này có thể được thực hiện theo hai cách bằng cách sử dụng hàm Vlookup:
- Sử dụng một cột trợ giúp
- Sử dụng hàm ARRAYFORMULA
Sử dụng cột trợ giúp cho VLOOKUP theo nhiều tiêu chí trong Google Trang tính
Phương pháp đầu tiên liên quan đến việc sử dụng cột “Trình trợ giúp” bổ sung, cột này sẽ chứa kết hợp các ô trong tiêu chí.
Trong ví dụ của chúng tôi, chúng tôi có thể chèn cột Trợ lý ngay trước cột Bộ phận để nó có thể là cột đầu tiên trong phạm vi tìm kiếm.
Cột Trợ lý có thể chứa sự kết hợp giữa bộ phận và mã vùng cho mỗi hàng, được phân tách bằng dấu cách.
Hãy cùng xem cách tạo và sử dụng cột trợ giúp này với hàm VLOOKUP để nhận giá trị tiền thưởng tương ứng cho bảng 2:
- Chèn một cột mới ngay trước cột đầu tiên của bảng 1. Nó sẽ hoạt động như một cột trợ giúp của chúng tôi. Để làm điều này, bạn cần nhấp chuột phải vào tiêu đề của cột đầu tiên (cột A) và chọn “Chèn 1 bên trái” từ trình đơn ngữ cảnh kết quả.
- Chọn ô đầu tiên của cột mới tạo (ô A4) và nhập công thức: = B4 & “” & C4.
- Bấm phím quay lại. Ô A4 bây giờ sẽ chứa nội dung của các ô B4 và C5 cùng với nhau, chỉ được phân tách bằng dấu cách.
- Bấm đúp vào ô điều khiển điền vào ô A4 để sao chép công thức vào phần còn lại của các ô trong cột A. Đây là cột A sẽ trông như thế nào tại thời điểm này:
- Bây giờ cột trợ giúp đã sẵn sàng, chúng ta có thể tiếp tục và sử dụng hàm VLOOKUP. Bạn sẽ nhận thấy rằng việc thêm một cột mới sẽ dịch chuyển nội dung của bảng 2 sang bên phải một ô. Nếu muốn, bạn có thể chọn nội dung và di chuyển nó sang trái một ô.
- Sau đó chọn ô E17 (cột thưởng của Bảng 2) và nhập công thức: = VLOOKUP (B17 & ”“ & 7, $ A $ 4: $ D $ 12,4, false).
- Bấm phím quay lại.
- Bây giờ bạn sẽ thấy giá trị tiền thưởng tương ứng với “Hoạt động BH12” từ Bảng 1.
- Bấm đúp vào ô điều khiển điền vào ô E17 để sao chép công thức vào phần còn lại của các ô trong cột A.
- Bây giờ bạn sẽ thấy tất cả các giá trị tiền thưởng tương ứng với từng bộ phận và mã vùng trong Bảng 2.
Ghi chú. Khi nhập công thức VLOOKUP, hãy nhớ khóa các liên kết trong tham số thứ hai bằng cách nhấn F4. Điều này đảm bảo rằng phạm vi tìm kiếm không thay đổi khi bạn sao chép công thức sang các ô khác.
Giải thích công thức
Hãy chia nhỏ công thức VLOOKUP dưới đây để hiểu từng tham số được sử dụng:
= VLOOKUP (B17 & “” & C17, $ A $ 4: $ D $ 12,4, sai)
- search_key: Khóa tìm kiếm mà chúng tôi đã sử dụng ở đây là sự kết hợp của các giá trị mã sở và quận mà chúng tôi muốn tìm kiếm, được phân tách bằng dấu cách (chính xác là định dạng chúng tôi đã sử dụng trong cột Người trợ giúp của mình).
- phạm vi: Chúng tôi biết rằng phạm vi tìm kiếm của chúng tôi phải luôn có cột tra cứu là cột đầu tiên. Phạm vi tìm kiếm hiện bắt đầu từ A4 đến D12 vì A4 là ô đầu tiên trong cột trợ giúp của chúng tôi.
- chỉ mục: Do có thêm một cột bổ sung ở bên trái, cột mục tiêu của chúng tôi hiện đã di chuyển một ô sang bên phải. Vì vậy, cột “Tiền thưởng” hiện ở chỉ mục 4 trong phạm vi tìm kiếm của chúng tôi.
- is_sorted: FALSE cho tham số này chỉ định rằng cột đầu tiên của phạm vi tìm kiếm không được sắp xếp theo thứ tự tăng dần.
Vì các cột search_key và Helper có cùng định dạng (với một nhánh theo sau là khoảng trắng theo sau là mã vùng), nên hàm VLOOKUP có thể dễ dàng tìm thấy phần thưởng phù hợp và trả lại.
Sử dụng hàm ARRAYFORMULA cho VLOOKUP với nhiều tiêu chí trong Google Trang tính
Phương pháp này thực hiện ít nhiều tương tự như phương pháp đầu tiên. Sự khác biệt duy nhất là lần này cột trợ giúp được tạo động thay vì phải tạo một cột bổ sung cho nó.
Phương pháp này sử dụng hàm ARRAYFORMULA để tạo một bảng “ảo” chứa các cột sau:
- Một cột chứa sự kết hợp của các ô trong tiêu chí.
- Nhắm mục tiêu cột từ phạm vi tìm kiếm
Hãy áp dụng điều này vào trường hợp của chúng ta. Dưới đây là các bước để tạo và sử dụng hàm ARRAYFORMULA với hàm VLOOKUP để nhận giá trị thưởng phù hợp cho bảng 2:
- Chọn ô E17 (cột thưởng trong Bảng 2) và nhập công thức: = ARRAYFORMULA (VLOOKUP (B17 & “” & C17, {$ A $ 4: $ A $ 12 & “” & $ B $ 4: $ B $ 12, $ C $ 4: $ C $ 12}, 2, sai))
- Bấm phím quay lại.
- Bây giờ bạn sẽ thấy giá trị tiền thưởng tương ứng với Phòng: Nghiệp vụ và Mã điện thoại: BH12 từ Bảng 1.
- Bấm đúp vào ô điều khiển điền vào ô E17 để sao chép công thức vào phần còn lại của các ô trong cột A.
- Bây giờ bạn sẽ thấy tất cả các giá trị tiền thưởng tương ứng với từng bộ phận và mã vùng trong Bảng 2.
Ghi chú. Khi nhập công thức VLOOKUP, hãy nhớ khóa các liên kết trong tham số thứ hai bằng cách nhấn F4. Điều này đảm bảo rằng phạm vi tìm kiếm không thay đổi khi bạn sao chép công thức sang các ô khác.
Giải thích công thức
Hãy chia nhỏ công thức chúng ta đã sử dụng để tìm ra chính xác điều gì đã xảy ra:
= ARRAYFORMULA (VLOOKUP (B17 & “” & C17, {$ A $ 4: $ A $ 12 & “” & $ B $ 4: $ B $ 12, $ C $ 4: $ C $ 12}, 2, sai))
- Đầu tiên, chúng tôi sử dụng ARRAYFORMULA để tạo một loại bảng ảo. Bảng ảo này phải chứa các cột sau:
- Một cột chứa kết hợp các giá trị từ các ô $ A $ 4: $ A $ 12 và các ô $ B $ 4: $ B $ 12, được phân tách bằng dấu cách giữa các giá trị của mỗi ô: A $ 2: $ A $ 9 & ”“ & $ B $ 2: $ B $ 9
- Một cột chứa các giá trị từ cột “Tiền thưởng” trong bảng 1: C $ 4: $ C $ 12.
- Nó nằm trong dấu ngoặc nhọn vì chúng ta muốn trả về một mảng hoặc bảng ảo gồm các ô: = ArrayFormula ({$ A $ 2: $ A $ 9 & “” & $ B $ 2: $ B $ 9, $ C $ 2: $ D $ 9.})
Lưu ý rằng chúng tôi đã sử dụng “,” làm dấu phân cách cho công thức mảng vì chúng tôi muốn coi các phần là cột cạnh nhau, như được hiển thị bên dưới:
- Nếu chúng ta muốn chúng xếp chồng lên nhau, chúng ta sẽ sử dụng dấu chấm phẩy “;” thay vì dấu phẩy ‘,’.
- Sau đó, chúng tôi áp dụng hàm Vlookup bằng cách sử dụng công thức mảng ở trên làm tham số thứ hai. Các thông số còn lại giống như trước:
- search_key: Phím tìm kiếm lại là sự kết hợp của các giá trị mã sở và quận mà chúng tôi muốn tìm kiếm, được phân tách bằng dấu cách.
- dải ô: Bây giờ dải ô là một mảng hoặc bảng ảo: {$ A $ 2: $ A $ 9 & “” & $ B $ 2: $ B $ 9, $ C $ 2: $ D $ 9}.
- index: vì bảng ảo được tạo chỉ bao gồm hai cột, trong đó Bonus là cột thứ hai, chúng tôi sử dụng 2 làm giá trị chỉ số.
- is_sorted: FALSE cho tham số này chỉ định rằng cột đầu tiên của phạm vi tìm kiếm không được sắp xếp theo thứ tự tăng dần.
Vì cả khóa tìm kiếm và cột đầu tiên của mảng được trả về trong phạm vi có định dạng chính xác (với bộ phận theo sau là dấu cách theo sau là mã vùng), nên hàm Vlookup có thể dễ dàng tìm thấy phần thưởng phù hợp và trả lại.
Đây chỉ là một tổng quan ngắn gọn về hai cách sử dụng hàm Vlookup khi cần xem xét nhiều tiêu chí.
Chúng tôi khuyến khích bạn tự mình thử các ví dụ mà chúng tôi đã thảo luận trong hướng dẫn này. Điều này sẽ giúp bạn hiểu cách hoạt động của các công thức và giúp bạn hiểu sâu hơn về nó.
Hy vọng bạn thấy hướng dẫn này hữu ích!
