Trong bài viết này, chúng ta sẽ thảo luận về ba tình huống mà bạn có thể cần tăng các tham chiếu cột (định danh) trong các công thức QUERY trong Google Sheets:
- Khi bạn sao chép một công thức QUERY sang phải hoặc trái.
- Khi bạn xóa bất kỳ cột nào trong phạm vi dữ liệu.
- Khi bạn chèn các cột mới.
Bằng tham chiếu cột, chúng ta đề cập đến các định danh cột trong các mệnh đề query. Ví dụ:
select A
select Col1
where B
where Col2Các định danh cột trong QUERY của Google Sheets có thể không di chuyển khi bạn sao chép và dán công thức QUERY. Điều này bởi vì chúng là chuỗi văn bản, không phải các tham chiếu ô như A1, B1, v.v. Tuy nhiên, bạn có thể tăng các cột trong QUERY bằng cách sử dụng một phương thức đơn giản.
Bạn có thể làm cho định danh cột tương đối trong chức năng QUERY của Google Sheets. Điều này có nghĩa là định danh cột sẽ điều chỉnh khi bạn sao chép công thức sang phải hoặc trái, hoặc khi bạn xóa hoặc chèn các cột mới vào phạm vi dữ liệu.
Định danh (IDs) trong hàm QUERY
Chúng ta sử dụng định danh cột (IDs) để tham chiếu đến các cột trong hàm QUERY. Cú pháp của hàm QUERY như sau:
QUERY(data, query, [headers])Ở đây:
datalà phạm vi các ô được truy vấn. Đối sốdatatrong hàm QUERY có thể là:- Một phạm vi ô cụ thể (ví dụ: A1:Z1000)
- Một phạm vi được đặt tên (ví dụ: sales_data)
- Một biểu thức (ví dụ: {A1:Z1000} hoặc IMPORTRANGE(spreadsheet_url, range_string))
querylà một chuỗi chỉ định truy vấn cần thực hiện.headers(tùy chọn) là một phạm vi các ô chứa nhãn tiêu đề cho các cột trong kết quả của hàm QUERY.
Nếu bạn muốn tăng tự động các cột khi sao chép một công thức QUERY hoặc chèn hoặc xóa một hoặc nhiều cột trong hàm QUERY data, bạn phải biết cách chỉ định các cột một cách chính xác trong hàm QUERY.
Cách Chỉ Định IDs trong Hàm QUERY
Có hai cách để chỉ định các cột trong hàm QUERY, tùy thuộc vào dữ liệu của bạn là một phạm vi tùy chỉnh, một phạm vi được đặt tên hoặc một biểu thức:
- Phạm vi tùy chỉnh hoặc phạm vi được đặt tên: Bạn có thể chỉ định một cột bằng cách sử dụng tiêu đề của nó là tiêu đề xác định một cột của một bảng tính. Chúng được gắn nhãn là A, B, C, …, Z, và còn nữa. Ví dụ, công thức sau sẽ chọn cột C từ dữ liệu trong A1: D:
=QUERY($A$1:$D, "select C")- Biểu thức: Bạn có thể chỉ định một cột bằng số của nó. Định dạng là
Col(phân biệt chữ hoa chữ thường) và theo sau là số cột. Điều này cần thiết vì các biểu thức không có tiêu đề cột. Ví dụ, công thức sau sẽ chọn cột thứ 5 từ dữ liệu được nhập từ một bảng tính khác:
=Query({importrange("URL","Sheet1!A1:G")},"select Col5",1)Bạn có thể biến phạm vi tùy chỉnh hoặc phạm vi được đặt tên thành một biểu thức bằng cách bọc nó trong dấu ngoặc nhọn. Điều này có thể hữu ích để tăng các tham chiếu cột trong QUERY. Ví dụ, các công thức sau sẽ chọn cột 3:
=QUERY({$A$1:$D},"select Col3") // phạm vi tùy chỉnh thành biểu thức
=QUERY({sales_data},"select Col3") // phạm vi được đặt tên thành biểu thứcLưu ý: Google Sheets đã bắt đầu hỗ trợ các định danh số cột với các phạm vi tùy chỉnh. Nhưng chúng ta sẽ tiếp tục sử dụng cách tiếp cận dấu ngoặc nhọn vì chúng tôi không chắc liệu có sự thay đổi trở lại không.
Tăng Các Tham Chiếu Cột Trong Mệnh Đề SELECT của QUERY Trong Google Sheets
Chúng ta sẽ sử dụng biểu thức thay vì phạm vi tùy chỉnh hoặc phạm vi được đặt tên để tăng các tham chiếu cột trong QUERY.
Để tăng các tham chiếu cột trong QUERY trong Google Sheets, bạn có thể phụ thuộc vào cú pháp sau:
=QUERY({data},"select Col"&COLUMN(reference_cell))Ở đây:
datalà phạm vi các ô cần truy vấn.reference_celllà ô đầu tiên của cột mà bạn muốn tăng hoặc tự điều chỉnh.
Ví dụ, công thức sau sẽ chọn cột đầu tiên từ phạm vi dữ liệu A1: D:
=QUERY({$A$1:$D}, "select Col"&COLUMN(A1))Vui lòng sử dụng các tham chiếu tuyệt đối trong data. Thay vì A1: D, bạn phải sử dụng $A$1:$D.
Hãy xem giải thích và hiệu ứng của công thức này khi chúng ta sao chép nó sang phải hoặc trái, xóa một cột trong dữ liệu hoặc thêm một cột vào dữ liệu.
1. Cách Tự Động Tăng Các Tham Chiếu Cột Trong QUERY Khi Sao Chép Sang Phải
Chúng ta đã thấy công thức để tăng các tham chiếu cột trong mệnh đề SELECT của QUERY. Hãy làm rõ hơn về nó.
Tôi có công thức sau trong ô F1:
=QUERY({$A$1:$D},"select Col"&COLUMN(A1))Khi tôi sao chép công thức này sang phải, nó sẽ tạo các cột tiếp theo, điều này có nghĩa là nó tăng các tham chiếu cột lên một đơn vị.
Cách công thức này tăng cột trong QUERY là rất đơn giản. Thay vì số 1 trong Col1, tôi đã sử dụng hàm COLUMN.
Hàm COLUMN(A1) trong QUERY trả về số 1. Tôi đã nối chuỗi văn bản select Col với nó. Vì vậy nó trở thành select Col1.
Điều này làm cho tham chiếu cột linh hoạt trong QUERY. Khi bạn kéo công thức sang phải, hàm COLUMN(A1) sẽ trở thành COLUMN(B1), đương nhiên sẽ trả về số 2.
Điều này giúp chúng ta tăng cột trong QUERY. Nếu bạn sao chép công thức sang trái, tham chiếu cột nhất định sẽ giảm.
Bây giờ hãy xem xét những gì xảy ra với công thức trên khi chúng ta thêm hoặc xóa các cột trong dữ liệu nguồn.
2. Cách Di Chuyển Cột Tham Chiếu Tự Động Trong QUERY Khi Xóa Một Cột
Trong GIF dưới đây, bạn có thể thấy hai công thức QUERY sau đây:
- Công thức trong ô F1 di chuyển tham chiếu cột khi vị trí cột thay đổi:
=QUERY({$A$1:$D},"select Col"&COLUMN(C1))- Công thức QUERY thông thường trong ô H1 không thay đổi:
=QUERY({$A$1:$D},"select Col3")Khi tôi xóa cột B, công thức F1 giữ nguyên các giá trị cột. Công thức tự động điều chỉnh điều này vì hàm COLUMN(C1) trả về số cột của ô C1, đổi thành B1 sau khi cột B bị xóa. Điều này đảm bảo rằng công thức luôn giữ các giá trị cột, bất kể các cột được thêm hoặc xóa.
Tuy nhiên, công thức QUERY thông thường trong ô H1 không giữ các giá trị cột vì định danh cột là không đổi trong nó. Điều này có nghĩa là công thức sẽ luôn chọn cột thứ ba, ngay cả khi các cột được thêm hoặc xóa.
3. Cách Tự Động Điều Chỉnh Tham Chiếu Cột Trong QUERY Khi Chèn Một Cột Mới
Dữ liệu chúng ta đang kiểm tra hiện đang ở phạm vi A1: D. Đây là công thức động của tôi để tự điều chỉnh tham chiếu cột:
=QUERY({$A$1:$D},"select Col"&COLUMN(B1))Công thức này hiện đang ở ô F1, nó sẽ lấy các giá trị cột B. Nếu tôi chèn một cột mới giữa các cột A và B, công thức sẽ tự động tăng cột trong QUERY.
Vị trí dữ liệu trong cột thứ hai di chuyển sang cột thứ ba. Tương tự, sự thay đổi này được phản ánh trong công thức của chúng tôi. COLUMN(B1) trở thành COLUMN(C1).
Tăng Các Tham Chiếu Cột Trong Mệnh Đề WHERE và Các Mệnh Đề Khác Của QUERY
Hãy viết một công thức QUERY khác liên quan đến các mệnh đề SELECT và WHERE. Điều này sẽ cho bạn một cái nhìn tổng quan về cách tăng tham chiếu cột trong tất cả các mệnh đề trong QUERY.
Chúng ta có dữ liệu sau trong ô A1: F:
Công thức sau trong ô H1 trả về các ID công việc nếu ngày bắt đầu công việc trong cột C nhỏ hơn ngày hôm nay và trạng thái là “Đang chờ”:
=QUERY({$A$1:$F},"select Col1 where Col3 <= date '"&TEXT(TODAY(),"yyyy-mm-dd")&"' and Col6 = 'Đang chờ'")Lưu ý: Ngày hôm nay là ngày 25-09-2023 (DD-MM-YYYY) trong ví dụ trên. Kết quả của công thức dựa trên ngày đó.
Để tăng các tham chiếu cột trong các mệnh đề SELECT và WHERE, chúng ta có thể sử dụng hàm COLUMN như trước đó.
Để công thức trở nên linh hoạt hơn, chúng ta có thể thay thế các tham chiếu cột thành các công thức sau:
Col"&COLUMN(A1)&"thay vìCol1trong mệnh đề SELECT.Col"&COLUMN(C1)&"thay vìCol3trong mệnh đề WHERE.
Điều này đảm bảo rằng công thức tăng tham chiếu cột khi bạn sao chép nó qua các ô bên cạnh.
Dưới đây là công thức đã được chỉnh sửa:
=QUERY({$A$1:$E},"select Col"&COLUMN(A1)&" where Col"&COLUMN(C1)&" <= date '"&TEXT(TODAY(),"yyyy-mm-dd")&"' and Col6 = 'Đang chờ'")Bây giờ, nếu chúng ta kéo công thức sang phải, nó sẽ tự động tăng các tham chiếu cột và trả về các tên công việc trong đó ngày kết thúc nhỏ hơn ngày hôm nay và trạng thái là “Đang chờ”.
Kết Luận
Nhiều người dùng Bảng tính Google nghĩ rằng việc sử dụng phạm vi đặt tên trong QUERY là bắt buộc để tham chiếu đến một cột theo cách động.
Tuy nhiên, phương pháp trên là một phương pháp thay thế cho việc sử dụng phạm vi đặt tên trong các mệnh đề SELECT và WHERE trong QUERY.
Tất nhiên, còn nhiều cách sử dụng phạm vi đặt tên trong QUERY, ví dụ như trong mệnh đề WHERE. Tôi sẽ giải thích điều này trong một bài viết hướng dẫn sau.
Tài liệu tham khảo:
