Trong bài viết này, chúng ta sẽ tìm hiểu cách sử dụng công thức mảng trong Google Sheets để thực hiện Vlookup và Offset với nhiều điều kiện. Đây là một ví dụ cụ thể mà bạn có thể áp dụng cho dữ liệu của mình.
Lợi ích của Google Sheets so với các ứng dụng bảng tính khác
Trước khi bước vào chi tiết về cách thực hiện Vlookup và Offset với nhiều điều kiện trong Google Sheets, hãy xem xét hai lợi ích chính của Google Sheets so với các ứng dụng bảng tính khác.
-
Công thức mảng: Google Sheets cho phép sử dụng công thức mảng một cách linh hoạt. Trong bài viết này, chúng ta sẽ sử dụng một ví dụ về công thức mảng.
-
Tốc độ hoạt động nhanh: Mặc dù Google Sheets là một ứng dụng trên nền web, nhưng tốc độ hoạt động của nó nhanh hơn so với ứng dụng bảng tính trực tuyến của đối thủ cạnh tranh.
Bước đầu, chuẩn bị dữ liệu mẫu
Trước khi bắt đầu, hãy chuẩn bị dữ liệu mẫu để thực hiện Vlookup và Offset với nhiều điều kiện trong Google Sheets. Dưới đây là một bảng dữ liệu mẫu để bạn sao chép vào sheet của mình và kiểm tra.
(Đính kèm hình ảnh bảng dữ liệu mẫu)
Đặt tên cho sheet này là “Item” và sao chép bảng trên vào phạm vi A1:D10 trên sheet đó.
Ở sheet này, tất cả các P.O. Dates được sắp xếp trong một cột. Nhưng điều mà chúng ta muốn làm là tìm tất cả các P.O. Nos. và Items rồi trả về Qty trong các cột ngày riêng biệt.
Nếu bạn đang tìm kiếm một giải pháp để khớp và tạo khoảng cách giữa nhiều điều kiện trong Google Sheets, thì cách thực hiện này cũng có thể phù hợp.
Tuy nhiên, hãy lưu ý rằng vì chúng ta sử dụng Vlookup, các bản ghi trùng lặp trong sheet “Item” có thể gây ra những vấn đề. Công thức sẽ chỉ khớp với hàng đầu tiên trong các hàng trùng lặp.
Trong trường hợp trên, tất cả các giá trị đều là duy nhất nếu bạn xem xét theo hàng, không phải giá trị trong từng ô riêng lẻ.
Trước khi đi vào công thức và giải thích, hãy xem kết quả mà nó trả về. Kết quả của công thức sẽ được hiển thị trên sheet có tên “Offset Match”.
(Đính kèm hình ảnh kết quả của công thức)
Lưu ý rằng sheet đầu tiên có tên “Item” chứa dữ liệu nguồn của chúng ta.
Công thức mảng để thực hiện Vlookup và Offset với nhiều điều kiện
Nếu bạn nhìn thấy công thức đầu tiên, có thể bạn sẽ nghĩ rằng nó rất phức tạp. Đó là lý do tại sao tôi không hiển thị nó trên hình ảnh ở trên.
Vì vậy, chúng ta sẽ bắt đầu từng bước một để viết công thức mảng để thực hiện Vlookup và Offset với nhiều điều kiện trong Google Sheets.
Có ba bước như sau.
- Vlookup nhiều điều kiện để khớp và trả về số lượng
Bạn có thể thử nghiệm công thức sau trong sheet có tên “Offset Match”.
=ArrayFormula(vlookup(A3:A11&B3:B11, {Item!A2:A10&Item!C2:C10,int(Item!B2:B10),Item!D2:D10}, {2,3}, 0))(Đính kèm hình ảnh công thức bước 1)
Công thức Vlookup (để bạn đọc dễ hiểu công thức): VLOOKUP(search_key, range, index, [is_sorted])
Các đối số trong công thức Vlookup như sau.
- search_key: A3:A11&B3:B11
- range: {Item!A2:A10&Item!C2:C10,int(Item!B2:B10),Item!D2:D10}
- index: {2,3}
- is_sorted: 0
Nếu bạn không hiểu, hãy đọc hướng dẫn liên quan tại đây.
(Không đính kèm liên kết)
Bây giờ, để thực hiện Vlookup và Offset với nhiều điều kiện trong Google Sheets, chúng ta phải chia kết quả trên thành từng cột riêng biệt. Chúng ta có thể sử dụng hàm Index để làm điều đó.
- Chia cột kết quả từ công thức Vlookup
Trên hình ảnh trên, bạn có thể thấy hai cột được trả về bởi công thức Vlookup.
Công thức mảng trả về nhiều cột nằm ở ô C3 và kết quả nằm trong dải cột C3:C11 và D3:D11.
(Đính kèm hình ảnh công thức bước 2)
Trong bước thứ hai để thực hiện Vlookup và Offset với nhiều điều kiện, tôi sử dụng hàm Index như sau để chia cột.
=index(step_1_formula,0,1)Chúng ta có thể loại bỏ công thức mảng từ step_1_formula khi sử dụng hàm Index. Vì vậy công thức sẽ trở thành:
=index(vlookup(A3:A11&B3:B11, {Item!A2:A10&Item!C2:C10,int(Item!B2:B10),Item!D2:D10}, {2,3}, 0), 0,1)Cũng tương tự như công thức trên, chúng ta có thể chia cột thứ hai bằng cách thay đổi số cuối cùng (thứ tự cột) trong công thức.
=index(step_1_formula,0,2)Chúng ta không cần sử dụng công thức này, công thức trước đó hoặc công thức tiếp theo ở bất kỳ ô nào ngay bây giờ. Hãy thử nghiệm và xóa chúng khi chúng ta sẽ sử dụng chúng sau đó trong hàm IF.
Bây giờ đây là bước 3 quan trọng và công thức khó khăn trong công thức mảng của chúng ta để thực hiện Vlookup và Offset với nhiều điều kiện trong Google Sheets.
- Vai trò của hàm IF và Sequence trong công thức Vlookup và Offset với nhiều điều kiện trong Google Sheets
Trước hết, hãy xem khu vực kết quả trên sheet có tên “Offset Match”.
(Đính kèm hình ảnh khu vực kết quả)
Khu vực kết quả của công thức được đánh dấu màu vàng nhạt, và phạm vi là C3:AG11. Có chín hàng và ba mươi một cột trong khu vực được đánh dấu.
Trên dòng C2:AG2, bạn có thể thấy các số từ 1 đến 30. Đó là các ngày từ 01/11/2020 đến 30/11/2020 (định dạng dd/mm/yyyy).
Tôi đã định dạng ngày thành định dạng ‘dd’ bằng cách sử dụng menu Format > Number > Date để thu gọn chiều rộng cột.
Nếu bạn muốn, bạn có thể giữ nguyên các ngày mà không định dạng thành ‘dd’. Tôi đã định dạng nó như vậy để thu gọn chiều rộng cột.
Đây là vai trò của hàm Sequence trong công thức Vlookup và Offset với nhiều điều kiện trong Google Sheets.
- Bạn hãy nhập công thức sau vào ô C3 (xin vui lòng tham khảo hình ảnh bên dưới).
=ArrayFormula(sequence(9,31)^0+$C$2:$AF$2-1)Kết quả:
(Đính kèm hình ảnh công thức bước 3)
Nó sao chép các ngày từ hàng đầu tiên C2:AG2 vào tất cả các ô trong phạm vi (khu vực kết quả). Nếu bạn muốn, tôi có thể giải thích công thức nhỏ này một cách chi tiết trong một bài viết sau.
Tiếp theo là vai trò của hàm IF.
Đây là bước cuối cùng.
Chúng ta nên kết hợp tất cả các công thức mà tôi đã cung cấp trong các bước khác nhau ở trên theo công thức chung sau đây.
=ArrayFormula(if(step_3_formula=step_2_formula_1, step_2_formula_2, ))Theo công thức chung trên, đây là công thức cuối cùng của chúng ta để thực hiện Vlookup và Offset với nhiều điều kiện trong Google Sheets.
=ArrayFormula(IFNA(if(sequence(9,31)^0+$C$2:$AF$2-1=index(vlookup(A3:A11&B3:B11,{Item!A2:A10&Item!C2:C10,int(Item!B2:B10),Item!D2:D10},{2,3},0),0,1),index(vlookup(A3:A11&B3:B11,{Item!A2:A10&Item!C2:C10,int(Item!B2:B10),Item!D2:D10},{2,3},0),0,2),)))Chỉ đơn giản thêm IFNA để tránh lỗi N/A trong kết quả.
Công thức Vlookup và Offset với nhiều điều kiện trong một tập dữ liệu lớn (Một số gợi ý)
Bạn cần thực hiện những thay đổi nào để mở rộng công thức để bao gồm nhiều hàng hơn? Bạn không cần thêm cột nào nữa vì công thức đã bao gồm 31 cột, tức là một tháng đầy đủ.
Tôi sẽ giải thích cách sử dụng công thức để bao phủ tối đa 100 hàng. Các hàng nhiều hơn có thể ảnh hưởng đến hiệu suất của Sheet hoặc không hoạt động vì giới hạn của ký hiệu & trong công thức.
Dưới đây là các thay đổi:
- Thay sequence(9,31) bằng sequence(100,31)
- Thay A3:A11 và B3:B11 trong công thức bằng A3:A100 và B3:B100 tương ứng.
- Bạn có thể thấy số hàng 10 trong các phạm vi Item!A2:A10, Item!B2:B10, Item!C2:C10 và Item!D2:D10 trong công thức. Hãy thay đổi tất cả các số 10 đó thành 100. Nghĩa là thay chúng bằng Item!A2:A100, Item!B2:B100, Item!C2:C100 và Item!D2:D100.
Vậy là xong về cách thực hiện Vlookup và Offset với nhiều điều kiện trong Google Sheets.
Sample_Sheet_181120
Nguồn: Crawlan.com
