Tra cứu giữa các trang tính

Kết quả bạn sẽ xây dựng

Tra cứu đơn giá cho nhiều SKU trên tab Orders bằng bảng giá nằm ở tab Catalog, nhưng chỉ nhập một công thức tại ô neo C2. Bài dùng dữ liệu giả để bạn có thể lặp lại từng bước an toàn.

Mục tiêu học tập

Dùng ARRAYFORMULA, IFNA và VLOOKUP để tra cứu khớp chính xác giữa hai tab, hiểu từng tham số, cách các SKU được đánh giá theo hàng và cách xử lý SKU không tồn tại.

Chuẩn bị dữ liệu mẫu

Mở workbook nguồn chính xác bên dưới hoặc tạo bản sao của riêng bạn. Tab Orders chứa SKU và Qty; tab Catalog chứa SKU và Price. Giữ workbook nguồn nguyên vẹn và thực hành trên bản sao.

HANDS-ON PRACTICE

Thực hành trên bản sao của riêng bạn

Mở bản mẫu Google Sheets để tạo bản sao giữ nguyên dữ liệu, công thức, định dạng, bộ lọc và hàng cố định. Bản gốc không bị thay đổi.

Tạo bản sao trên Google Sheets (cần quyền truy cập) ↗Xem mẫu gốc

Bạn cần đăng nhập Google; nếu bản gốc chưa được chia sẻ công khai, hãy tải bảng mẫu đầy đủ rồi nhập vào Google Sheets, hoặc dán dữ liệu vào trang tính của bạn.

Workbook nguồn practical-formulas-10 trong Google Sheets với tab Orders được chọn, hiển thị SKU, Qty và cột Price còn trống.

Mở đúng workbook nguồn trong Google Sheets ↗

Thực hành từng bước

Bước 1: Trên bản sao, mở tab Catalog và xác nhận bảng tra cứu là A1:B4 với ba SKU duy nhất: P01 = 12, P02 = 35, P03 = 22.

Workbook _result với tab Catalog được chọn, hiển thị đúng bảng SKU và Price dùng cho VLOOKUP.

Bước 2: Mở tab Orders, chọn C2 và nhập công thức bên dưới đúng một lần. Kết quả tự tràn xuống C2:C4; không kéo chốt điền và không copy công thức xuống các hàng dưới.

Công thức cần thử

Tại Orders!C2, nhập công thức sau. Để C2:C4 trống trước khi nhập để vùng tràn không bị chặn.

=ARRAYFORMULA(IF(A2:A4="","",IFNA(VLOOKUP(A2:A4,Catalog!$A$2:$B$4,2,FALSE),"Check SKU")))

Workbook _result với Orders!C2 được chọn; thanh công thức hiển thị đầy đủ ARRAYFORMULA và kết quả tràn lần lượt là 35, 12 và 22.

Cách công thức hoạt động=ARRAYFORMULA(IF(A2:A4="","",IFNA(VLOOKUP(A2:A4,Catalog!$A$2:$B$4,2,FALSE),"Check SKU")))

Ý nghĩa tổng quát của toàn bộ công thức
- Duyệt toàn bộ A2:A4 cùng lúc: bỏ trống kết quả khi SKU nguồn trống; với SKU có dữ liệu, tra giá trị tương ứng trong tab Catalog; nếu không tìm thấy thì hiển thị "Check SKU".
ARRAYFORMULA(array_formula)
- array_formula là tham số bắt buộc; nó nhận biểu thức có thể tạo một hoặc nhiều giá trị. Trong bài này là IF(A2:A4="","",IFNA(VLOOKUP(A2:A4,Catalog!$A$2:$B$4,2,FALSE),"Check SKU")).
- ARRAYFORMULA cho phép một công thức ở C2 xử lý đồng thời ba SKU trong A2:A4.
- Kết quả là một mảng ba phần tử và tự tràn xuống C2:C4, vì vậy không cần kéo chốt điền hoặc copy công thức.
IF(logical_expression, value_if_true, [value_if_false])
- logical_expression là bắt buộc; trong bài này là A2:A4="", tạo một mảng TRUE/FALSE để kiểm tra từng SKU có trống hay không.
- value_if_true là bắt buộc; bài này truyền "" để hàng có SKU trống cũng trả về trống.
- value_if_false là tùy chọn; bài này truyền biểu thức IFNA(VLOOKUP(...),"Check SKU") để chỉ tra cứu những hàng có SKU.
VLOOKUP(search_key, range, index, [is_sorted])
- search_key là bắt buộc; bài này truyền A2:A4, tức ba SKU P02, P01 và P03 được đánh giá theo đúng thứ tự hàng.
- range là bắt buộc; bài này truyền Catalog!$A$2:$B$4. Cột đầu tiên của vùng là SKU cần tìm và cột thứ hai là Price cần trả về.
- index là bắt buộc; giá trị 2 yêu cầu VLOOKUP trả về cột thứ hai của vùng Catalog.
- is_sorted là tùy chọn; bài này truyền FALSE để yêu cầu khớp chính xác SKU.
- Dấu $ trong Catalog!$A$2:$B$4 cố định biên của bảng tra cứu. Dù bài này dùng công thức mảng và không fill down, vùng Catalog vẫn được chỉ rõ cố định.
IFNA(value, value_if_na)
- value là bắt buộc; bài này là VLOOKUP(A2:A4,Catalog!$A$2:$B$4,2,FALSE).
- value_if_na là bắt buộc; bài này là "Check SKU". Nó chỉ được dùng khi VLOOKUP trả lỗi #N/A do không tìm thấy SKU.
- IFNA không thay các loại lỗi khác, nên phù hợp hơn với mục đích riêng là nhận biết mã không tồn tại.
Đánh giá toàn bộ biểu thức
- A2 = P02: VLOOKUP tìm P02 trong Catalog!A2:A4 và lấy Price ở cột thứ hai là 35.
- A3 = P01: kết quả tra cứu là 12.
- A4 = P03: kết quả tra cứu là 22.
- Ba SKU và ba kết quả căn theo cùng thứ tự hàng, tạo mảng {35; 12; 22}.
- Nếu một SKU bị đổi thành mã không có trong Catalog, VLOOKUP trả #N/A cho phần tử đó và IFNA thay riêng phần tử đó bằng Check SKU.
Vì sao xuất hiện kết quả cuối cùng
- Mảng kết quả cuối cùng là {35; 12; 22} và tràn từ C2 đến C4.
- Trong audit, đổi Orders!A2 từ P02 thành P99 làm C2 đổi thành Check SKU; sau khi khôi phục P02, C2 trở lại 35.
- Điều này xác nhận kết quả đến từ bảng Catalog thật và công thức được nhập một lần tại C2.

Điểm kiểm tra

SKU	Qty	Price
P02	2	35
P01	3	12
P03	1	22

Kết quả mong đợi: Price tràn từ C2:C4 lần lượt là 35, 12 và 22.

Lỗi thường gặp và cách xử lý

Nếu xuất hiện Check SKU, hãy kiểm tra mã trong Orders có đúng với SKU ở Catalog hay không. FALSE yêu cầu khớp chính xác, nên khác chữ hoặc mã không tồn tại sẽ không được ghép gần đúng.

Bài tập tự thực hành

Đổi tạm Orders!A2 từ P02 thành P99 và xác nhận C2 hiển thị Check SKU; sau đó khôi phục P02 và kiểm tra C2 trở lại 35.

Tóm tắt kiến thức

✓Catalog giữ bảng SKU–Price; Orders dùng bảng đó để tra giá.
✓FALSE buộc VLOOKUP khớp SKU chính xác.
✓IFNA chỉ thay trường hợp không tìm thấy bằng Check SKU.
✓Một ARRAYFORMULA tại C2 tạo cả ba kết quả 35, 12 và 22.
✓Không kéo hoặc copy công thức xuống C3:C4 vì kết quả đã tự tràn.

Bài học tiếp theo

Bài trước: So sánh hai danh sách.

Kết quả bạn sẽ xây dựng

Mục tiêu học tập

Chuẩn bị dữ liệu mẫu

HANDS-ON PRACTICE

Thực hành trên bản sao của riêng bạn

Mở bản mẫu Google Sheets để tạo bản sao giữ nguyên dữ liệu, công thức, định dạng, bộ lọc và hàng cố định. Bản gốc không bị thay đổi.

Tạo bản sao trên Google Sheets (cần quyền truy cập) ↗Xem mẫu gốc

Mở đúng workbook nguồn trong Google Sheets ↗

Thực hành từng bước

Bước 1: Trên bản sao, mở tab Catalog và xác nhận bảng tra cứu là A1:B4 với ba SKU duy nhất: P01 = 12, P02 = 35, P03 = 22.

Công thức cần thử

Tại Orders!C2, nhập công thức sau. Để C2:C4 trống trước khi nhập để vùng tràn không bị chặn.

=ARRAYFORMULA(IF(A2:A4="","",IFNA(VLOOKUP(A2:A4,Catalog!$A$2:$B$4,2,FALSE),"Check SKU")))

Cách công thức hoạt động=ARRAYFORMULA(IF(A2:A4="","",IFNA(VLOOKUP(A2:A4,Catalog!$A$2:$B$4,2,FALSE),"Check SKU")))

Ý nghĩa tổng quát của toàn bộ công thức
- Duyệt toàn bộ A2:A4 cùng lúc: bỏ trống kết quả khi SKU nguồn trống; với SKU có dữ liệu, tra giá trị tương ứng trong tab Catalog; nếu không tìm thấy thì hiển thị "Check SKU".
ARRAYFORMULA(array_formula)
- array_formula là tham số bắt buộc; nó nhận biểu thức có thể tạo một hoặc nhiều giá trị. Trong bài này là IF(A2:A4="","",IFNA(VLOOKUP(A2:A4,Catalog!$A$2:$B$4,2,FALSE),"Check SKU")).
- ARRAYFORMULA cho phép một công thức ở C2 xử lý đồng thời ba SKU trong A2:A4.
- Kết quả là một mảng ba phần tử và tự tràn xuống C2:C4, vì vậy không cần kéo chốt điền hoặc copy công thức.
IF(logical_expression, value_if_true, [value_if_false])
- logical_expression là bắt buộc; trong bài này là A2:A4="", tạo một mảng TRUE/FALSE để kiểm tra từng SKU có trống hay không.
- value_if_true là bắt buộc; bài này truyền "" để hàng có SKU trống cũng trả về trống.
- value_if_false là tùy chọn; bài này truyền biểu thức IFNA(VLOOKUP(...),"Check SKU") để chỉ tra cứu những hàng có SKU.
VLOOKUP(search_key, range, index, [is_sorted])
- search_key là bắt buộc; bài này truyền A2:A4, tức ba SKU P02, P01 và P03 được đánh giá theo đúng thứ tự hàng.
- range là bắt buộc; bài này truyền Catalog!$A$2:$B$4. Cột đầu tiên của vùng là SKU cần tìm và cột thứ hai là Price cần trả về.
- index là bắt buộc; giá trị 2 yêu cầu VLOOKUP trả về cột thứ hai của vùng Catalog.
- is_sorted là tùy chọn; bài này truyền FALSE để yêu cầu khớp chính xác SKU.
- Dấu $ trong Catalog!$A$2:$B$4 cố định biên của bảng tra cứu. Dù bài này dùng công thức mảng và không fill down, vùng Catalog vẫn được chỉ rõ cố định.
IFNA(value, value_if_na)
- value là bắt buộc; bài này là VLOOKUP(A2:A4,Catalog!$A$2:$B$4,2,FALSE).
- value_if_na là bắt buộc; bài này là "Check SKU". Nó chỉ được dùng khi VLOOKUP trả lỗi #N/A do không tìm thấy SKU.
- IFNA không thay các loại lỗi khác, nên phù hợp hơn với mục đích riêng là nhận biết mã không tồn tại.
Đánh giá toàn bộ biểu thức
- A2 = P02: VLOOKUP tìm P02 trong Catalog!A2:A4 và lấy Price ở cột thứ hai là 35.
- A3 = P01: kết quả tra cứu là 12.
- A4 = P03: kết quả tra cứu là 22.
- Ba SKU và ba kết quả căn theo cùng thứ tự hàng, tạo mảng {35; 12; 22}.
- Nếu một SKU bị đổi thành mã không có trong Catalog, VLOOKUP trả #N/A cho phần tử đó và IFNA thay riêng phần tử đó bằng Check SKU.
Vì sao xuất hiện kết quả cuối cùng
- Mảng kết quả cuối cùng là {35; 12; 22} và tràn từ C2 đến C4.
- Trong audit, đổi Orders!A2 từ P02 thành P99 làm C2 đổi thành Check SKU; sau khi khôi phục P02, C2 trở lại 35.
- Điều này xác nhận kết quả đến từ bảng Catalog thật và công thức được nhập một lần tại C2.

Điểm kiểm tra

SKU	Qty	Price
P02	2	35
P01	3	12
P03	1	22

Kết quả mong đợi: Price tràn từ C2:C4 lần lượt là 35, 12 và 22.

Lỗi thường gặp và cách xử lý

Bài tập tự thực hành

Đổi tạm Orders!A2 từ P02 thành P99 và xác nhận C2 hiển thị Check SKU; sau đó khôi phục P02 và kiểm tra C2 trở lại 35.

Tóm tắt kiến thức

✓Catalog giữ bảng SKU–Price; Orders dùng bảng đó để tra giá.
✓FALSE buộc VLOOKUP khớp SKU chính xác.
✓IFNA chỉ thay trường hợp không tìm thấy bằng Check SKU.
✓Một ARRAYFORMULA tại C2 tạo cả ba kết quả 35, 12 và 22.
✓Không kéo hoặc copy công thức xuống C3:C4 vì kết quả đã tự tràn.

Bài học tiếp theo

Bài trước: So sánh hai danh sách.

Tra cứu giữa các trang tính

Kết quả bạn sẽ xây dựng

Mục tiêu học tập

Chuẩn bị dữ liệu mẫu

Thực hành trên bản sao của riêng bạn

Thực hành từng bước

Công thức cần thử

Điểm kiểm tra

Lỗi thường gặp và cách xử lý

Bài tập tự thực hành

Tóm tắt kiến thức

Tự kiểm tra kiến thức

Bài học tiếp theo

Bạn nên học gì tiếp theo?

Bình luận

Tra cứu giữa các trang tính

Kết quả bạn sẽ xây dựng

Mục tiêu học tập

Chuẩn bị dữ liệu mẫu

Thực hành trên bản sao của riêng bạn

Thực hành từng bước

Công thức cần thử

Điểm kiểm tra

Lỗi thường gặp và cách xử lý

Bài tập tự thực hành

Tóm tắt kiến thức

Tự kiểm tra kiến thức

Bài học tiếp theo

Bạn nên học gì tiếp theo?

Bình luận