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

  • Nhập dữ liệu bằng một cú nhấp chuột từ mọi nơi
  • Chuyển đổi bảng tính thành phần mềm trong vài giây
  • Mẫu dựng sẵn & tùy chỉnh
  • Trực quan hóa dữ liệu thời gian thực trong Bảng điều khiển tập trung

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ệu

Các ví dụ xuyên suốt bài đăng này sử dụng cùng hai bảng dữ liệu

  1. Có một bảng lưu ID Email và Tên khách hàng
  2. Và một bảng khác lưu trữ ID Email và Số lượng đơn hàng

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 Sheets

Hà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

VLOOKUP ( search_key, range, index, [is_sorted] )
  • = VLOOKUP ( A4, 'Database 2'!$A$1:$B$11, 2, FALSE )
    7 – Đây là đối số bắt buộc chỉ định giá trị bạn muốn tìm kiếm trong một cột nhất định. Nó còn được gọi là giá trị tra cứu hoặc giá trị duy nhất. Bán tại. Bạn muốn tìm kiếm “Lalit,” 12, hoặc giá trị hiện diện bên trong ô A2
  • = VLOOKUP ( A4, 'Database 2'!$A$1:$B$11, 2, FALSE )
    8 – Phạm vi bên trong mà bạn muốn thao tác tìm kiếm diễn ra. Thông thường, phạm vi có nhiều hơn hai cột và cột đầu tiên là nơi hệ thống tìm kiếm giá trị duy nhất. Đó là một đối số bắt buộc
  • = VLOOKUP ( A4, 'Database 2'!$A$1:$B$11, 2, FALSE )
    9 – Số cột mà từ đó bạn muốn trả về một giá trị sau khi tra cứu bên trong phạm vi. Lập luận này cũng bắt buộc

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

  • [
    HLOOKUP(search_key, range, index, [is_sorted])
    0] – Đây là một đối số tùy chọn xác định xem cột đầu tiên trong phạm vi (i. e. , cột diễn ra thao tác tìm kiếm) có được sắp xếp hay không. TRUE có nghĩa là đã sắp xếp;
    • Nếu bạn bỏ qua đối số này (vì nó là tùy chọn) thì theo mặc định TRUE sẽ được xem xét và trong trường hợp đó, cột nơi diễn ra tìm kiếm cần được sắp xếp theo thứ tự tăng dần từ A đến Z hoặc nhỏ nhất đến lớn nhất
    • Đối số này quyết định xem kết quả khớp sẽ chính xác hay một phần. Nếu được cung cấp giá trị TRUE, đối số này sẽ tìm kiếm kết quả khớp gần đúng. Nếu không tìm thấy, nó sẽ trả về giá trị khớp gần nhất với
      = VLOOKUP ( A4, 'Database 2'!$A$1:$B$11, 2, FALSE )
      7
    • Nếu được cung cấp FALSE, công thức sẽ tìm kiếm kết quả khớp chính xác và không cần sắp xếp cột theo thứ tự tăng dần. Nếu không tìm thấy kết quả khớp chính xác, công thức sẽ trả về Lỗi #N/A

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

= VLOOKUP ( A4, 'Database 2'!$A$1:$B$11, 2, FALSE )

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

  • Vì bạn muốn tìm kiếm các giá trị dựa trên id email của khách hàng, ô A2 từ Cơ sở dữ liệu 1 là đối số khóa_tìm kiếm bên trong công thức
  • Phạm vi là từ nơi bạn muốn kéo các giá trị dựa trên giá trị duy nhất. Trong ví dụ này,
    = VLOOKUP ( A4, 'Database 2'!$A$1:$B$11, 2, FALSE )
    8 phải là A1. B11 từ sheet Cơ sở dữ liệu 2.
    = VLOOKUP ( A4, 'Database 2'!$A$1:$B$11, 2, FALSE )
    8 này sẽ không thay đổi. Do đó, tốt nhất là bạn nên sửa nó bằng cách nhấn nút F4 từ bàn phím của mình

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ả

  • Bây giờ, bạn muốn tìm nạp cột Đơn hàng từ Cơ sở dữ liệu 2. Đây là cột thứ hai trong
    = VLOOKUP ( A4, 'Database 2'!$A$1:$B$11, 2, FALSE )
    8 và do đó bạn nên cung cấp giá trị cho tham số
    = VLOOKUP ( A4, 'Database 2'!$A$1:$B$11, 2, FALSE )
    9 là 2
  • Cuối cùng, bạn muốn tra cứu các giá trị ở bất cứ nơi nào xuất hiện kết quả khớp chính xác cho id email. Do đó, hãy nhập FALSE làm giá trị cho đối số
    HLOOKUP(search_key, range, index, [is_sorted])
    0

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ú

  • lưu ý 1. Trong khi tìm kiếm một kết quả khớp chính xác, nếu hai hoặc nhiều giá trị khớp với khóa_tìm_kiếm, thì hàm sẽ trả về giá trị xuất hiện đầu tiên trong cột
  • Lưu ý 2. Hàm VLOOKUP chỉ có thể tra cứu dữ liệu từ bên phải của nó. Và bạn không thể tra cứu các giá trị từ bên trái của cột search_key. Đây là một trong những nhược điểm chính của chức năng
  • lưu ý 3. Chức năng VLOOKUP của Google Sheets không thành công khi bạn muốn tra cứu dữ liệu phân biệt chữ hoa chữ thường. Chức năng không thể phân biệt giữa các chuỗi văn bản chữ hoa và chữ thường. Đối với hàm VLOOKUP, chuỗi “lalit,” “Lalit,” và “LALIT” giống hệt nhau

Tra cứu dữ liệu bằng hàm HLOOKUP trong Google Sheets

Mặ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

HLOOKUP(search_key, range, index, [is_sorted])
  • = VLOOKUP ( A4, 'Database 2'!$A$1:$B$11, 2, FALSE )
    7 – Đây là đối số bắt buộc và chỉ định giá trị duy nhất hoặc khóa-giá trị mà bạn muốn thực hiện tra cứu dựa vào đó
  • = VLOOKUP ( A4, 'Database 2'!$A$1:$B$11, 2, FALSE )
    8 – Đây là đối số bắt buộc chỉ định phạm vi ô hoặc bảng theo chiều ngang mà từ đó bạn muốn trả về giá trị dựa trên đối số đầu tiên được cung cấp
  • = VLOOKUP ( A4, 'Database 2'!$A$1:$B$11, 2, FALSE )
    9 – Chỉ định số hàng mà từ đó bạn muốn lấy dữ liệu sang một bảng khác. Đó là một đối số bắt buộc
  • HLOOKUP(search_key, range, index, [is_sorted])
    0 – Một đối số logic xác định liệu hàng đầu tiên trong phạm vi có được sắp xếp (TRUE hoặc để trống) hay không (FALSE). Nếu hàng được sắp xếp, công thức sẽ trả về kết quả khớp gần đúng và nếu hàng không được sắp xếp, công thức sẽ tạo kết quả khớp chính xác. Nó là một đối số tùy chọn

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

= HLOOKUP ( A20, 'Database 2'!$B$27:$K$28, 2, FALSE )

Đố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ú

  • lưu ý 1. Các quy tắc cho đối số is_sorted giống với quy tắc của hàm VLOOKUP. Nếu giá trị của đối số là TRUE, thì hàng đầu tiên trong phạm vi phải được sắp xếp theo thứ tự tăng dần
  • Lưu ý 2. Hàm HLOOKUP chỉ có thể trả về các hàng bên dưới hàng đầu tiên của đối số phạm vi
  • lưu ý 3. Hàm HLOOKUP cũng không phân biệt chữ hoa chữ thường. Nó không thể phân biệt giữa văn bản chữ hoa và chữ thường. Văn bản “lalit,” “Lalit,” và “LALIT. ” là như nhau đối với chức năng

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 MATCH

Hà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

MATCH ( search_key, range, [search_type] )
  • = VLOOKUP ( A4, 'Database 2'!$A$1:$B$11, 2, FALSE )
    7 – là đối số bắt buộc chỉ định giá trị duy nhất mà bạn muốn tìm kiếm. Nó có thể là một văn bản, một ô chứa giá trị hoặc thậm chí là một công thức trả về một chuỗi hoặc một số
  • = VLOOKUP ( A4, 'Database 2'!$A$1:$B$11, 2, FALSE )
    8 – chỉ định mảng một chiều mà bạn tìm kiếm giá trị khóa trong đó. Đó là một đối số bắt buộc
  • [______17_______3] – là đối số tùy chọn cho phép hàm quyết định cách tìm kiếm giá trị. Nếu bỏ qua, giá trị mặc định được đặt thành 1
    • Nếu
      = HLOOKUP ( A20, 'Database 2'!$B$27:$K$28, 2, FALSE )
      3 là 1, hàm giả định rằng
      = VLOOKUP ( A4, 'Database 2'!$A$1:$B$11, 2, FALSE )
      8 cung cấp được sắp xếp theo thứ tự tăng dần và trả về giá trị lớn nhất nhỏ hơn hoặc bằng
      = VLOOKUP ( A4, 'Database 2'!$A$1:$B$11, 2, FALSE )
      7
    • nếu
      = HLOOKUP ( A20, 'Database 2'!$B$27:$K$28, 2, FALSE )
      3 là 0, hàm sẽ tìm kiếm kết quả khớp chính xác cho
      = VLOOKUP ( A4, 'Database 2'!$A$1:$B$11, 2, FALSE )
      7
    • nếu
      = HLOOKUP ( A20, 'Database 2'!$B$27:$K$28, 2, FALSE )
      3 là -1, hàm giả định rằng ____1_______8 được cung cấp được sắp xếp theo thứ tự giảm dần và sau đó trả về giá trị nhỏ nhất nhỏ hơn hoặc bằng ____1_______7

Ví dụ MATCH

= MATCH ( "[email protected]", $A$2:$A$11, 0 )

Đâ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ì bạn muốn tìm kiếm vị trí tương đối của ID email cho Rajan Arora, nên đây sẽ là search_key bên trong hàm MATCH
  • Phạm vi mà bạn muốn tìm kiếm giá trị là A1. A11 từ danh sách thứ hai
  • Cuối cùng, bạn muốn tìm kết quả khớp chính xác và do đó lấy số 0 làm giá trị cho đối số
    = HLOOKUP ( A20, 'Database 2'!$B$27:$K$28, 2, FALSE )
    3

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 INDEX

Không giống như hàm MATCH, hàm INDEX trả về giá trị thay vì tham chiếu vị trí

INDEX ( reference, [row], [column] )
  • MATCH ( search_key, range, [search_type] )
    3 – là một đối số bắt buộc chỉ định phạm vi mà bạn muốn tìm kiếm giá trị trong phạm vi đó
  • [______18_______4] – một đối số tùy chọn chỉ định chỉ mục hàng hoặc số hàng bạn muốn bù trừ từ đầu phạm vi. Nếu bỏ qua, giá trị mặc định là 0
  • [______18_______5] – một đối số tùy chọn chỉ định chỉ số cột hoặc số cột mà bạn muốn dịch chuyển từ trái sang phải. Giá trị mặc định là 0

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 trong ô D6, Hàm INDEX trước tiên lấy tham chiếu mà từ đó nó muốn tra cứu các giá trị. Vì, chúng tôi muốn Đếm đơn đặt hàng, nó nằm trong cột B, cụ thể hơn là trong phạm vi B2. B11
  • Chúng tôi muốn Đếm đơn đặt hàng cho Rajan Arora. Lần xuất hiện đầu tiên cho id email của anh ấy là ở hàng thứ sáu. Do đó, chúng tôi sử dụng 6 làm đối số hàng. Bạn thực sự đang nói công thức để bù các hàng si từ đầu phạm vi
  • Đối số cột là 1 vì đó là cột duy nhất có trong tham chiếu ô

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

= MATCH (A35, 'Database 2'!$A$2:$A$11, 0 )
  • Thứ nhất, search_key nằm trong Cơ sở dữ liệu 1 trang tính trong ô A35
  • Đối với đối số thứ hai, phạm vi là từ trang Cơ sở dữ liệu 2 trải dài trên A2. A11. Bạn nên sửa phạm vi này vì nó sẽ không thay đổi
  • Cuối cùng, giá trị 0 được sử dụng cho search_type và cho phép hàm tìm kiếm giá trị khớp chính xác dựa trên ID email

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 đó

=INDEX('Database 2'!$A$2:$B$11, MATCH(A35, 'Database 2'!$A$2:$A$11,0),2)

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

  • Đối với đối số đầu tiên, hãy cung cấp toàn bộ phạm vi A2. B11 từ trang Cơ sở dữ liệu 2 làm tài liệu tham khảo. Đảm bảo sửa phạm vi này vì nó sẽ không thay đổi
  • Đối với đối số hàng, hãy đặt công thức MATCH đã thảo luận ở trên
    MATCH ( search_key, range, [search_type] )
    6
  • Và cuối cùng, bạn muốn tra cứu Số lượng đơn đặt hàng là cột thứ hai trong phạm vi. Do đó, hãy đặt 2 làm giá trị cho đối số cột

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ú

  • lưu ý 1. Có nhiều ưu điểm khi sử dụng hàm INDEX MATCH. Cái chính là khả năng tra cứu các giá trị từ phía bên trái của cột tìm kiếm
  • Lưu ý 2. Một ưu điểm khác của chức năng này là khả năng phân biệt giữa trường hợp văn bản. Bạn đã nghe nó đúng. Hàm INDEX và MATCH có thể phân biệt chữ hoa và chữ thường. Với khả năng này, chức năng cung cấp một phương pháp tra cứu nâng cao hơn cho những người có dữ liệu phân biệt chữ hoa chữ thường để tra cứu từ

Tra cứu dữ liệu bằng hàm DGET trong Google Sheets

Mộ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

DGET ( database, field, criteria )
  • MATCH ( search_key, range, [search_type] )
    7 – một bảng hoặc một phạm vi dạng bảng hoặc mảng có tiêu đề cho mỗi cột. Đó là một đối số bắt buộc
  • MATCH ( search_key, range, [search_type] )
    8 – một cột bạn muốn tra cứu các giá trị từ đó. Nó có thể là một chỉ mục cột chẳng hạn như 1, 2, v.v. hoặc tên cột bên trong dấu ngoặc kép, chẳng hạn như “Số lượng đơn đặt hàng” hoặc tham chiếu ô chứa tiêu đề cột, chẳng hạn như B1
  • MATCH ( search_key, range, [search_type] )
    9 – một dải ô chứa tiêu chí có tiêu đề. Nó thường có hai hàng (hàng đầu tiên cho tiêu đề, hàng thứ hai cho giá trị tiêu chí). Tuy nhiên, bạn cũng có thể coi ô trống là tiêu chí

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

= VLOOKUP ( A4, 'Database 2'!$A$1:$B$11, 2, FALSE )
0

Hà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 trong hàm, đối số đầu tiên là cơ sở dữ liệu từ nơi bạn muốn tìm kiếm các giá trị. Trong trường hợp này, bạn muốn kéo các giá trị từ phạm vi A1. B11 từ sheet Cơ sở dữ liệu 2. Do đó nó là đối số đầu tiên
  • Sau đó đến trường mà bạn muốn tra cứu. Bạn có thể tham khảo tên trường hoặc tham chiếu ô cho tiêu đề cột. Ví dụ này tham chiếu "Số lượng đơn đặt hàng" vì đó là trường bạn muốn tìm nạp. Nhớ đặt nó bên trong dấu ngoặc kép
  • Đề cập đến giá trị đối số tiêu chí là A64. A65 khi bạn muốn tìm nạp Số lượng đơn đặt hàng cho id email cụ thể đó. Giá trị tiêu đề trong ô A64 sẽ cần khớp chính xác với giá trị được sử dụng trong ô A49

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ú

  • Lưu ý1. Điều quan trọng nhất bạn cần lưu ý với chức năng này là nó chỉ có thể tra cứu và trả về giá trị cho tiêu chí đầu tiên. Nó sẽ không hoạt động như hàm VLOOKUP hoặc INDEX MATCH nếu bạn cố kéo công thức này xuống
  • Lưu ý 2. Nếu có nhiều kết quả phù hợp cho cùng một tiêu chí bên trong cơ sở dữ liệu, hàm này sẽ tạo lỗi #NUM. lỗi. Trong những trường hợp như vậy, bạn phải cung cấp một điều kiện bổ sung để công thức này hoạt động

Tra cứu dữ liệu bằng hàm LOOKUP trong Google Sheets

Hà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

= VLOOKUP ( A4, 'Database 2'!$A$1:$B$11, 2, FALSE )
1
  • = VLOOKUP ( A4, 'Database 2'!$A$1:$B$11, 2, FALSE )
    7 – là đối số bắt buộc chỉ định giá trị khóa mà bạn đang tìm kiếm
  • = MATCH ( "[email protected]", $A$2:$A$11, 0 )
    1.
    = MATCH ( "[email protected]", $A$2:$A$11, 0 )
    2 – Bạn có thể sử dụng search_array từ nơi bạn có thể tìm kiếm giá trị. Hoặc dùng cả dãy chứa mảng kết quả cũng được. Nếu bạn sử dụng tùy chọn đầu tiên từ đối số này, bạn sẽ cần chỉ định
    = MATCH ( "[email protected]", $A$2:$A$11, 0 )
    3 để nhận kết quả. Tuy nhiên, nếu bạn sử dụng
    = MATCH ( "[email protected]", $A$2:$A$11, 0 )
    4, thì bạn không cần chỉ định
    = MATCH ( "[email protected]", $A$2:$A$11, 0 )
    3 nữa vì nó đã là một phần của lập luận này. Bạn có thể sử dụng một trong hai tùy chọn này
  • = MATCH ( "[email protected]", $A$2:$A$11, 0 )
    3 – đó là một mảng mà bạn muốn trả về kết quả từ đó. Đối số này không bắt buộc khi một phạm vi được chỉ định làm đối số thứ hai

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

= MATCH ( "[email protected]", $A$2:$A$11, 0 )
7 không có trong
= MATCH ( "[email protected]", $A$2:$A$11, 0 )
1.
= MATCH ( "[email protected]", $A$2:$A$11, 0 )
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ụ

= VLOOKUP ( A4, 'Database 2'!$A$1:$B$11, 2, FALSE )
2

Bạ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

  • Đối với khóa_tìm_kiếm, hãy sử dụng tham chiếu ô A71 làm đối số
  • Để đơn giản hóa mọi thứ, hãy sử dụng phạm vi tìm kiếm chứa cả giá trị tra cứu và giá trị kết quả. Trong ví dụ này, đó là phạm vi A2. B11 từ bảng Cơ sở dữ liệu 2. Đảm bảo dữ liệu được sắp xếp theo thứ tự tăng dần dựa trên cột A (Email)

ghi chú

  • Ghi chú. Hàm này không nhận các giá trị khớp chính xác, nó sẽ tìm kiếm các giá trị khớp gần đúng và trả về kết quả. Ví dụ: hãy xem điều đó cho “patt@cdk. com,” Số lượng đơn đặt hàng được trả về là 35 vì Email có kết quả gần đúng với “emily@rbc. com. ” Hai id này có chữ “c” và “. com” chung

Tra cứu dữ liệu bằng hàm FILTER trong Google Sheets

Mộ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

= VLOOKUP ( A4, 'Database 2'!$A$1:$B$11, 2, FALSE )
3
  • = VLOOKUP ( A4, 'Database 2'!$A$1:$B$11, 2, FALSE )
    8 – là đối số bắt buộc chỉ định phạm vi mà bạn muốn lọc dữ liệu từ đó
  • INDEX ( reference, [row], [column] )
    1 – là đối số bắt buộc và chỉ định điều kiện dựa vào đó hàm sẽ lọc dữ liệu ra
  • [______32_______2] – là một đối số tùy chọn và chỉ định bất kỳ điều kiện tiếp theo nào, nếu có

Thí dụ

= VLOOKUP ( A4, 'Database 2'!$A$1:$B$11, 2, FALSE )
4
  • Sử dụng B2. B11 làm đối số phạm vi. Bạn nên sửa phạm vi này vì nó sẽ không thay đổi
  • Đối với đối số điều kiện 1, bạn muốn tìm kiếm giá trị trong ô A18 trong phạm vi A2. A11. Do đó, hãy sử dụng
    INDEX ( reference, [row], [column] )
    3 làm điều kiện đầu tiên. Bạn có thể sửa phạm vi vì nó sẽ không thay đổi

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 Sheets

Hà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

= VLOOKUP ( A4, 'Database 2'!$A$1:$B$11, 2, FALSE )
5
  • INDEX ( reference, [row], [column] )
    4 – chỉ định phạm vi ô mà bạn muốn áp dụng hàm
  • INDEX ( reference, [row], [column] )
    5 – chỉ định phương pháp hoặc cách thao tác dữ liệu. Ngôn ngữ được sử dụng để phát triển các mệnh đề trong đối số này là Ngôn ngữ truy vấn Google Visualization API. Các lệnh hoặc mệnh đề tương tự như các truy vấn SQL, đôi khi đơn giản hơn SQL
  • INDEX ( reference, [row], [column] )
    6 – là một đối số tùy chọn và có tác dụng hiển thị số lượng hàng tiêu đề mà dữ liệu của bạn có

Thí dụ

= VLOOKUP ( A4, 'Database 2'!$A$1:$B$11, 2, FALSE )
6

Giả 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

  • Đối với đối số dữ liệu, bạn cần có toàn bộ dữ liệu mà bạn muốn tra cứu giá trị. Trong trường hợp này, nó sẽ là A2. B11 từ bảng Cơ sở dữ liệu 2
  • Bạn muốn chọn các giá trị từ cột B với điều kiện từ cột A. Để đạt được điều này, hãy sử dụng truy vấn SQL like
    INDEX ( reference, [row], [column] )
    7. Đây sẽ là giá trị cho đối số truy vấn của bạn. Đảm bảo toàn bộ truy vấn bắt đầu từ mệnh đề SELECT được đặt trong dấu ngoặc kép. Ở đây, bạn đang yêu cầu hàm trả về giá trị từ cột B dựa trên điều kiện cho cột A tìm kiếm giá trị từ ô 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ận

Tra 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

  • Hàm VLOOKUP phổ biến tìm kiếm dữ liệu theo chiều dọc và trả về kết quả khớp từ phía bên phải của phạm vi tìm kiếm. Nó cũng trả về kết quả khớp đầu tiên được tìm thấy trong trường hợp có các giá trị trùng lặp
  • Hàm HLOOKUP hoạt động tương tự như hàm VLOOKUP. Sự khác biệt duy nhất là chức năng tìm kiếm dữ liệu theo chiều ngang chứ không phải theo chiều dọc
  • Sự kết hợp INDEX và MATCH cho phép bạn tra cứu dữ liệu ở bên trái hoặc bên trên. Hai hạn chế này đến từ các hàm VLOOKUP và HLOOKUP tương ứng có thể được loại bỏ nếu bạn sử dụng hàm INDEX và MATCH
  • Hàm DGET thuộc họ Cơ sở dữ liệu và tìm kiếm một giá trị cho bạn dựa trên một giá trị tiêu chí tra cứu duy nhất
  • Hàm LOOKUP giúp tìm kiếm các giá trị theo cả chiều dọc và chiều ngang. Hạn chế của chức năng này là không có khả năng tạo ra các kết quả khớp chính xác
  • BỘ LỌC và QUERY có thể lọc dữ liệu cho bạn dựa trên bất kỳ điều kiện nào và điều này có thể được sử dụng theo cách tương tự như tra cứu

Bạn đang sử dụng chức năng nào để tra cứu dữ liệu?