Google Trang tính tìm tham chiếu ô có giá trị
Trải nghiệm tất cả những lợi ích của bảng tính cổ điển với nhiều tính năng mạnh mẽ hơn giúp tiết kiệm thời gian cho người dùng Show
Nếu bạn đang làm việc với các bộ dữ liệu được lưu trữ trong các sổ làm việc hoặc trang tính khác nhau, thường thì vấn đề đau đầu là tìm cách hợp nhất thông tin từ nhiều trang tính dựa trên một cột khóa được chỉ định Bạn có thể thường xuyên gặp tình huống trong đó một số phần của tập dữ liệu được lưu trữ trong một trang tính và một số phần dữ liệu có liên quan khác nằm trong một trang tính khác Bạn có thể muốn nối dữ liệu trên nhiều trang tính đó lại với nhau trong một trang tính để phân tích thêm Kết hợp dữ liệu trải rộng trên nhiều trang tính thành một trang tính duy nhất thường có nghĩa là bạn cần tra cứu dữ liệu dựa trên một cột chung để khớp dữ liệu đó với dữ liệu liên quan Điều này được gọi là tra cứu dữ liệu. Bạn có thể tra cứu dữ liệu trên nhiều bảng, trang tính hoặc bảng tính. Bạn thậm chí có thể tra cứu hình ảnh bên trong ô trong Google Trang tính Ví dụ: giả sử bạn có tên khách hàng và ID email của họ được lưu trữ trong một trang tính. Một trang tính khác chứa id email và số lượng họ đã đặt hàng một sản phẩm cụ thể từ tổ chức của bạn Bạn muốn kết hợp thông tin này vào một bảng duy nhất chứa tên khách hàng, id email và số lượng họ đặt hàng Để thực hiện tác vụ này trong Google Trang tính, bạn sẽ cần tra cứu dữ liệu từ một trang tính khác dựa trên một cột cụ thể phổ biến trong cả hai bảng (ID email) Xuyên suốt bài viết này, bạn sẽ tìm hiểu về bảy cách khác nhau để tra cứu dữ liệu bên trong Google Trang tính. Tải xuống sổ làm việc ví dụ để làm theo Bộ dữ liệuCác ví dụ xuyên suốt bài đăng này sử dụng cùng hai bảng dữ liệu
Mỗi bảng dữ liệu bao gồm mười khách hàng và ảnh chụp màn hình ở trên sẽ giúp bạn hiểu rõ hơn về cấu trúc của nó Tra cứu dữ liệu bằng hàm VLOOKUP trong Google SheetsHàm VLOOKUP cho đến nay là hàm phổ biến nhất có sẵn trong Google Trang tính Hàm VLOOKUP dùng để tra cứu dữ liệu từ bảng này sang bảng khác. Hàm này có tên từ chữ viết tắt của Tra cứu theo chiều dọc và như tên gợi ý, hàm này tìm kiếm kết quả khớp theo chiều dọc từ đầu bảng đến cuối bảng Hàm VLOOKUP là một phần không thể thiếu trong Google Sheets. Hàm tìm kiếm một khóa hoặc giá trị duy nhất từ cột đầu tiên của một phạm vi đã chỉ định và trả về một giá trị nằm trong một cột khác cho cùng một hàng Ban đầu, bạn có thể thấy chức năng này khó xử lý. Tuy nhiên, một khi bạn trải qua nó, bạn sẽ đồng ý rằng nó không khó hiểu đến thế. Cú pháp hàm VLOOKUP
Lưu ý rằng chỉ mục cho cột bắt đầu từ 1. Nếu bạn chỉ định một giá trị chỉ mục nhỏ hơn 1, thì công thức sẽ báo lỗi #VALUE. Lỗi Mặt khác, nếu bạn chỉ định giá trị chỉ mục nằm ngoài số cột từ phạm vi đã cho, bạn sẽ nhận được lỗi #REF. Lỗi
Thí dụMặc dù những đối số này có vẻ hơi khó hiểu, nhưng thực tế hoạt động của công thức này khá đơn giản. Hãy xem cách bạn có thể sử dụng hàm VLOOKUP để tra cứu các giá trị từ các trang tính khác nhau Vấn đề bạn đang gặp phải hiện tại là bạn có hai cơ sở dữ liệu, như đã thảo luận ở trên. Cơ sở dữ liệu 1 có id email của khách hàng và tên của họ, trong khi Cơ sở dữ liệu 2 có id email của khách hàng và số lượng đơn đặt hàng Bây giờ, bạn muốn lấy số lượng đơn đặt hàng trong Cơ sở dữ liệu 1 dựa trên id email của khách hàng để toàn bộ dữ liệu sẽ ở một nơi để phân tích thêm. Bạn có thể sử dụng hàm VLOOKUP để hoàn thành công việc này
Công thức trên sẽ tìm kiếm email trong bảng 2 và trả về số lượng đơn hàng tương ứng
Lưu ý rằng việc giữ hoặc xóa các ô tiêu đề bên trong công thức VLOOKUP không thay đổi hoặc thay đổi kết quả
Công thức thực hiện tác vụ này như được hiển thị ở trên và bạn có thể sao chép và dán trực tiếp vào bảng tính của mình với điều kiện là bạn có cùng cấu trúc dữ liệu như tôi có trong bảng tính demo Bạn cần kéo công thức từ C2 qua các hàng để lấy Số lượng đơn hàng cho từng khách hàng. Bạn có thể làm điều đó bằng cách đưa con trỏ chuột đến ô vuông màu xanh lam trong ô C2 và nhấp đúp để mở rộng công thức xuống các hàng Bạn cũng có thể chọn phạm vi C2. C11 và sau đó nhấn Ctrl + D như một phím tắt để điền công thức xuống các ô. Hoặc bạn có thể chỉ cần sao chép và dán nó xuống các hàng Sau khi bạn kéo công thức xuống sẽ có một vài giá trị lỗi #N/A. Đây là những lỗi do công thức không thể tìm thấy những email đó bên trong trang Cơ sở dữ liệu 2. Và đúng như vậy, những khách hàng đó hoàn toàn không nằm trong danh sách thứ hai Ngoài ra, đối với những khách hàng có đơn đặt hàng trùng lặp, công thức chỉ trả về giá trị đầu tiên được tìm thấy ghi chú
Tra cứu dữ liệu bằng hàm HLOOKUP trong Google SheetsMặt khác, hàm HLOOKUP không phổ biến và được sử dụng rộng rãi như hàm VLOOKUP. Tuy nhiên, hai chức năng hoạt động gần như giống hệt nhau Có thể sử dụng HLOOKUP thay cho VLOOKUP khi xử lý tập dữ liệu đã chuyển đổi Chữ H bên trong tên chức năng là viết tắt của Ngang. Hàm này tìm kiếm các giá trị theo chiều ngang từ trái sang phải trong một hàng. Các đối số của hàm này cũng giống như đối số của hàm VLOOKUP với những thay đổi nhỏ Hàm được thiết kế rõ ràng để tra cứu các giá trị theo chiều ngang. Hàm tìm kiếm một khóa hoặc giá trị duy nhất từ hàng đầu tiên của một dải ô hoặc bảng đã chỉ định và trả về một giá trị nằm trong một hàng khác cho cùng một cột Cú pháp của Hàm HLOOKUP
Thí dụGiả sử bên trong trang Cơ sở dữ liệu 2, bạn có dữ liệu ngang được lưu trữ như hình trên. Bạn muốn tra cứu và lấy Số lượng đơn đặt hàng dựa trên Email trong trang tính Cơ sở dữ liệu 1 Hãy làm theo các bước bên dưới để tra cứu giá trị bằng hàm HLOOKUP bên trong Google Trang tính
Đối với đối số search_key, hãy nhập ô A20 làm giá trị mà bạn muốn tra cứu kết quả Phạm vi là từ trang tính Cơ sở dữ liệu 2 và đó là bảng được lưu trữ theo chiều ngang. Giá trị cho đối số này phải là B27. K28 Phạm vi này sẽ không thay đổi, và do đó, thật tốt khi sửa nó. Sử dụng nút bàn phím F4 để sửa phạm vi này. Công thức sẽ tìm kiếm khóa-giá trị trong hàng đầu tiên của mỗi cột để tra cứu dữ liệu Vì bạn muốn kéo Số lượng đơn đặt hàng, được lưu trữ trên hàng thứ hai của mỗi cột, hãy sử dụng giá trị 2 cho đối số chỉ mục Cuối cùng, bạn muốn kết quả khớp chính xác được tìm thấy và do đó thêm FALSE cho đối số is_sorted Công thức thực hiện nhiệm vụ này như hình trên. Vui lòng sao chép và sử dụng nó khi bạn đang làm việc với bảng tính demo Bạn phải kéo công thức xuống các ô C20. C29 để áp dụng công thức trên tất cả các ô và bạn nhận được tất cả Số lượng đơn đặt hàng dựa trên Email Một lần nữa, lỗi #N/A dành cho những khách hàng có số lượng đơn đặt hàng không có trong danh sách thứ hai Trong trường hợp các đơn đặt hàng trùng lặp được đặt, kết quả khớp đầu tiên được tìm thấy sẽ được chọn theo công thức HLOOKUP. Tương tự như hàm VLOOKUP ghi chú
Tra cứu dữ liệu bằng hàm INDEX MATCH trong Google SheetsĐể tra cứu dữ liệu bên trong Google Sheets, có thể sử dụng nhiều hàm khác nhau. Nhưng không có cái nào gần với hiệu quả của hàm INDEX và MATCH Hai chức năng này khi sử dụng riêng lẻ không cho ra quá nhiều kết quả có giá trị. Tuy nhiên, nếu được sử dụng cùng nhau, chúng có thể làm nên điều kỳ diệu và tạo ra kết quả tra cứu mạnh mẽ thậm chí còn cao cấp hơn hàm VLOOKUP thông thường Google Trang tính INDEX MATCH là sự kết hợp của hai hàm, nếu được sử dụng theo một trình tự cụ thể, có thể tạo kết quả đầu ra tương tự như kết quả do hàm VLOOKUP hoặc HLOOKUP tạo ra Bạn có thể sử dụng nó thay thế cho hai hàm này với nhiều khả năng tra cứu dữ liệu hơn vì hàm này hoạt động tốt hơn VLOOKUP hoặc HLOOKUP Cú pháp hàm MATCHHàm MATCH đủ đơn giản để hiểu Hàm Google Sheet MATCH tìm kiếm một khóa hoặc giá trị duy nhất trong một phạm vi hoặc mảng ô, sau đó trả về vị trí hoặc chỉ mục tương đối của giá trị đó. Nó có cú pháp như hình bên dưới
Ví dụ MATCH
Đây là một ví dụ mà bạn muốn tìm vị trí tương đối của ID email cho khách hàng Rajan Arora trong danh sách của mình. Công thức tìm chỉ mục hàng chứa ID email như trên
Vì lần xuất hiện đầu tiên của ID email nằm ở hàng thứ sáu nên công thức sẽ trả về kết quả là 6 📝 Lưu ý. ID email này được sao chép và lưu trữ trên hàng thứ sáu và thứ tám. Hàm tìm sự xuất hiện đầu tiên trên hàng thứ sáu và trả về vị trí này. Nó không kiểm tra các hàng tiếp theo sau khi tìm thấy kết quả khớp đầu tiên Cú pháp hàm INDEXKhông giống như hàm MATCH, hàm INDEX trả về giá trị thay vì tham chiếu vị trí
CHỈ SỐ Ví dụGiả sử bạn muốn giá trị cho Số lượng đơn đặt hàng được liên kết với Rajan Arora từ danh sách thứ hai. Bạn có thể lấy nó bằng hàm INDEX như sau
Bạn hoàn thành công thức bằng cách thêm dấu ngoặc đơn đóng và Bingo. Hàm trả về 18 dưới dạng giá trị từ tham chiếu ô Hàm INDEX và MATCH để tra cứu giá trịVì bạn biết cách các hàm INDEX và MATCH hoạt động riêng lẻ nên bạn có thể sử dụng chúng cùng nhau để tra cứu Số lượng đơn đặt hàng từ Cơ sở dữ liệu 2 cho khách hàng từ Cơ sở dữ liệu 1 dựa trên Email của họ Đầu tiên, bạn sẽ sử dụng hàm MATCH để tìm vị trí tương đối của ID email từ Cơ sở dữ liệu 1 sang Cơ sở dữ liệu 2. Công thức để thực hiện nhiệm vụ này như hình dưới đây
Hàm này sẽ trả về vị trí tương đối của Email được lưu trong ô A35 trong sheet Cơ sở dữ liệu 2. Nó ở hàng thứ 4 đó
Bây giờ, bạn sẽ sử dụng công thức MATCH này bên trong hàm INDEX làm đối số hàng
Xin chúc mừng. Bạn đã tra cứu thành công Số lượng đơn đặt hàng cho từng Khách hàng dựa trên Email của họ bằng cách sử dụng kết hợp mạnh mẽ các hàm INDEX và MATCH Bây giờ hãy kéo công thức này xuống các hàng C35. C44 để trả về các giá trị cho từng Khách hàng trong danh sách Bạn sẽ thấy các giá trị #N/A trong một số ô. Đừng lo. Những lỗi đó là do hệ thống không thể tìm thấy những Email đó và Số lượng Đơn đặt hàng tương đối của chúng trong Cơ sở dữ liệu 2 ghi chú
Tra cứu dữ liệu bằng hàm DGET trong Google SheetsMột chức năng mạnh mẽ khác để tra cứu dữ liệu bên trong Google Sheets là DGET Hàm này thuộc họ Cơ sở dữ liệu và là hàm tra cứu duy nhất từ họ đó. Tất cả các chức năng khác đến từ họ Cơ sở dữ liệu là các chức năng tổng hợp Hàm này là một bổ sung đáng giá và thay thế tốt cho hàm VLOOKUP khi cần tra cứu kết quả Hàm DGET giúp bạn trả về một giá trị duy nhất dựa trên các tiêu chí theo kiểu giống như SQL. Lưu ý rằng hàm này có thể tra cứu giá trị từ nhiều cột nhưng không thể trả về từ nhiều hàng Cú pháp hàm DGET
Thí dụBây giờ, hãy xem xét tình huống này. Bạn muốn tra cứu Số lượng đơn hàng của một khách hàng có id email “emily@rbc. com” bên trong ô B65 bằng hàm DGET 0Hàm trên sẽ lấy giá trị bắt buộc từ một danh sách khác bằng cách sử dụng hàm DGET trong Google Trang tính
Bạn có thể sao chép công thức dưới đây trong khi làm việc trên trang demo mà bạn tải xuống. Chỉ cần thay đổi các giá trị cho tiêu chí để lấy Số lượng đơn đặt hàng cho một khách hàng khác ghi chú
Tra cứu dữ liệu bằng hàm LOOKUP trong Google SheetsHàm LOOKUP của Google Trang tính hơi khó hiểu đối với một số người dùng do cú pháp và cách thực hiện tra cứu theo chiều dọc và chiều ngang khác nhau Bên cạnh đó, cú pháp khó hiểu của hàm gây ra một số nghi ngờ. Tuy nhiên, nếu bạn thành thạo chức năng này, nó là một sự thay thế hữu ích cho VLOOKUP và HLOOKUP Hàm LOOKUP của Google Trang tính tìm kiếm một giá trị quan trọng hoặc duy nhất bên trong một phạm vi tìm kiếm nhất định, theo chiều dọc theo hàng hoặc theo chiều ngang theo cột Sau đó, nó trả về một giá trị ô dựa trên vị trí tương ứng của giá trị duy nhất đó từ một phạm vi kết quả Cú pháp của Hàm LOOKUP 1
Hạn chế lớn nhất của hàm LOOKUP là nó không thể tạo kết quả khớp chính xác Hàm này luôn trả về kết quả khớp gần đúng. Nếu 7 không có trong 1. 2, hàm này sẽ tìm kết quả khớp gần nhất có thể và sau đó trả về giá trị tra cứu được liên kết với kết quả khớp gần đúng đóNếu tồn tại các giá trị trùng lặp, hàm này luôn trả về kết quả dựa trên kết quả khớp cuối cùng được tìm thấy. Tất cả điều này là do nó hoạt động trên các phạm vi được sắp xếp (sắp xếp theo thứ tự tăng dần sẽ cho kết quả tốt nhất) Thí dụ 2Bạn có thể thấy cách hàm này trả về Số lượng đơn đặt hàng trong ví dụ trên
ghi chú
Tra cứu dữ liệu bằng hàm FILTER trong Google SheetsMột cách khác để tra cứu dữ liệu cho một giá trị khóa duy nhất là sử dụng hàm FILTER Đầu tiên hàm FILTER lấy toàn bộ dữ liệu từ nơi bạn muốn trích xuất kết quả. Sau đó, nó sẽ lọc kết quả dựa trên các điều kiện được cung cấp Bạn có thể sử dụng điều này để tra cứu Số lượng đơn đặt hàng bằng cách lọc trên một email nhất định Cú pháp hàm FILTER 3
Thí dụ 4
Công thức sẽ trả về Số lượng đơn đặt hàng là 35 cho “emily@rbc. com” ghi chúĐiều thú vị là khi bạn có các giá trị trùng lặp, hàm sẽ trả về tất cả các kết quả Ví dụ: nếu bạn muốn tra cứu Số lượng đơn đặt hàng cho rajan@pzk. com, công thức sẽ trả về hai giá trị lần lượt là 18 và 30. Bạn chỉ cần thay đổi ID email trong ô A18 Tra cứu dữ liệu bằng hàm QUERY trong Google SheetsHàm QUERY của Google Trang tính là một hàm từ Dòng cơ sở dữ liệu như DGET và cho phép bạn viết mã giống như SQL để chuyển đổi dữ liệu của mình Chức năng này cũng có thể được sử dụng để tra cứu dữ liệu. Bạn có thể sử dụng hàm QUERY để lọc dữ liệu của mình theo một giá trị duy nhất tương tự như cách hoạt động của hàm FILTER Thật không may, bạn không thể sử dụng hàm QUERY để tra cứu các giá trị trên các trang tính khác nhau do giới hạn của cấu trúc truy vấn Chức năng này có thể làm được nhiều việc hơn là lọc dữ liệu, vì vậy hãy xem hướng dẫn đầy đủ về chức năng QUERY để tìm hiểu tất cả những gì chức năng này có thể làm Cú pháp hàm QUERY 5
Thí dụ 6Giả sử bạn muốn tra cứu Số lượng đơn đặt hàng cho Email rozy@rbc. com sử dụng hàm QUERY bên trong ô B24. Giá trị tham chiếu hoặc tới hạn được lưu trữ trong ô A24
ghi chú📝 Lưu ý. Tương tự như hàm FILTER, nếu bạn tìm kiếm một giá trị trùng lặp, hàm này sẽ trả về tất cả các kết quả phù hợp có sẵn kết luậnTra cứu dữ liệu từ bảng khác là công việc thường xuyên. Bài viết này cho bạn thấy bảy chức năng khác nhau có thể làm điều này cho bạn
Bạn đang sử dụng chức năng nào để tra cứu dữ liệu? |