Cách sử dụng hàm QUERY trong Google Sheets

Nếu thấy mình đang dành quá nhiều thời gian cho các tác vụ dữ liệu lặp đi lặp lại trong Google Sheets, bạn không phải là người duy nhất. Nhiều người từng mất hàng giờ mỗi tuần cho những bước cũ rích - cho đến khi tình cờ tìm thấy một hàm duy nhất có thể giúp họ làm những việc nặng nhọc. Đó chính là hàm QUERY!

5. Sắp xếp tự động tự cập nhật theo thời gian thực

Cách trực tiếp nhất mà hàm QUERY cải thiện bảng tính là loại bỏ tình trạng đau đầu liên tục khi phải sắp xếp lại. Bạn biết đấy, thật bực bội khi bạn sắp xếp dữ liệu, rồi một giờ sau, thông tin mới xuất hiện và đột nhiên danh sách đã sắp xếp của bạn lại hoàn toàn không theo thứ tự.

Giả sử bạn muốn theo dõi hàng tồn kho của công ty theo giá. Thay vì sắp xếp lại vô tận, bạn có thể sử dụng QUERY như thế này:

=QUERY(A:G, "SELECT * ORDER BY E desc")

Trong ví dụ này, bạn đang yêu cầu Google Sheets kéo mọi thứ từ cột A đến G và sắp xếp theo cột E (trong trường hợp này là cột chứa giá) theo thứ tự giảm dần.

Ngay lập tức, những chiếc xe đắt tiền nhất sẽ được đưa thẳng lên đầu.

Điều làm nên sự thay đổi này là nó không ngừng hoạt động. Khi một chiếc xe mới trị giá 250.000 USD được thêm vào bảng tính của bạn, nó sẽ ngay lập tức xuất hiện ở đầu kết quả QUERY. Không cần phải sắp xếp lại nữa.

Những gì trước đây bạn phải mất vài phút để nhấp và kéo trong suốt cả ngày giờ đây diễn ra tự động. Quan trọng hơn, bạn không bao giờ bỏ lỡ các mục có mức độ ưu tiên cao nữa vì chúng luôn ở đúng vị trí cần có.

4. Kết hợp nhiều bước thành một công thức

Bạn có biết mình đã từng thực hiện những điều này bao nhiêu lần không: Lọc dữ liệu, sắp xếp dữ liệu, ẩn một số cột nhất định và có thể nhóm các mục lại với nhau. Bây giờ, nhờ QUERY, bạn có thể thực hiện tất cả những điều đó chỉ trong một lần.

Giả sử bạn muốn chuẩn bị đánh giá bán hàng và cần thực hiện những điều sau:

• Chỉ lọc những chiếc xe đã bán
• Xóa bất kỳ chiếc xe nào dưới 30.000 USD
• Loại trừ Tesla
• Sắp xếp theo giá để xem những chiếc xe bán chạy nhất trước

Đó là 4 bước riêng biệt. Nếu phải thực hiện thủ công và mắc lỗi ở bất kỳ đâu, bạn sẽ phải bắt đầu lại. Thay vào đó, hãy nhập:

=QUERY(Test!A:G, "SELECT * WHERE F = TRUE AND E > 30000 AND NOT B contains 'Tesla' ORDER BY E desc")

Một công thức thay thế cho 4 bước. Vì đã chèn hàm QUERY vào một trang tính trống nên tác giả đã thêm tên trang tính bằng dấu chấm than (Test!) trước khi chỉ định các cột có dữ liệu của mình.

Điều tuyệt vời nhất là bạn có thể làm cho nó trở nên cầu kỳ hơn với các mệnh đề như PIVOT, LABEL, v.v... Ví dụ, khi cần xem doanh số bán hàng của từng năm, hãy thêm GROUP BY:

=QUERY(Test!A:G, "SELECT D, SUM(E) WHERE F = TRUE GROUP BY D ORDER BY SUM(E) desc")

Điều này cho bạn biết doanh thu cho đến nay từ các bản phát hành của từng năm, được tự động sắp xếp từ cao nhất đến thấp nhất.

Thời gian tiết kiệm được tăng lên nhanh chóng. Những gì trước đây bạn phải mất 10 – 15 phút để nhấp, lọc và sắp xếp thì giờ chỉ mất 30 giây để nhập công thức. Và không giống như quy trình nhiều bước cũ, bạn không bao giờ vô tình bỏ qua một bước hoặc làm hỏng thứ tự.

3. Xử lý các tập dữ liệu lớn mà không bị trễ

Hãy tưởng tượng thế này: Bạn đang cố gắng tìm 100 khách hàng gần đây nhất của mình từ cơ sở dữ liệu có hơn 50.000 hàng để gửi chiến dịch email có mục tiêu. Đơn giản phải không?

Sai rồi. Mỗi lần bạn cố gắng sắp xếp, lọc hoặc điều hướng loại tập dữ liệu lớn như vậy, Google Sheets có thể sẽ bị treo. Nhiều người đã phải tự xử lý vấn đề này cho đến khi phát hiện ra QUERY có thể xử lý các tập dữ liệu lớn.

Sau đây là một ví dụ:

=QUERY('50000 Sales Records'!A:N, "SELECT * ORDER BY H desc LIMIT 100")

Công thức đầu tiên sẽ lấy 100 lô hàng gần đây nhất, vì cột H bao gồm ngày giao hàng. Trong khi đó, lệnh thứ hai sẽ lấy các lô từ 61 đến 160.

Thay vì để máy tính của bạn xử lý và hiển thị tất cả hơn 50.000 hàng, mất nhiều thời gian, chỉ để có thể xem 100 hàng đầu, QUERY (với mệnh đề LIMIT và OFFSET) đủ thông minh để lấy chính xác những gì bạn cần và để nguyên phần còn lại.

Bạn thậm chí có thể sử dụng LIMIT và OFFSET với tất cả các tính năng QUERY khác - nhóm, sắp xếp, lọc - mà không phải đấu tranh với trình duyệt và lãng phí thời gian.

2. Phân tích dữ liệu trên nhiều trang tính hoặc file

Bạn vẫn đang sao chép dữ liệu thủ công giữa các bảng tính hoặc workbook chỉ để tạo một báo cáo? Bạn có thể dừng ngay bây giờ. QUERY cho phép bạn phân tích dữ liệu trên nhiều trang tính - hoặc thậm chí trên các file hoàn toàn khác nhau - mà không cần phải nhấn Ctrl + C.

Kết hợp nhiều tab trong một lần

Giả sử bạn có dữ liệu hàng quý được chia thành các tab như Sales_Q1 và Sales_Q2, bạn có thể hợp nhất chúng thành một tập dữ liệu duy nhất bằng cách sử dụng dấu ngoặc nhọn. Sau đó, chạy phân tích giống như bạn sẽ làm trên một trang tính duy nhất.

=QUERY({Sales_Q1!A:N; Sales_Q2!A:N; Sales_Q3!A:N; Sales_Q4!A:N}, "SELECT Col3, Col1, SUM(Col9) WHERE Col5 = 'C' GROUP BY Col3, Col1")

Giả sử cột 3 (Col3) là Item Type, cột 1 là Region, cột 9 là Units Sold và cột 5 là Order Priority. Chỉ cần đảm bảo cấu trúc (các cột) của mỗi trang tính khớp với nhau là bạn đã sẵn sàng.

Kết hợp 4 trang tính với dữ liệu từ các quý khác nhau để có được tổng số đơn vị đã bán cho mỗi mặt hàng và khu vực cho những đơn hàng ưu tiên C. Quá dễ dàng!

Lấy dữ liệu từ một trang tính Google khác (Không cần tải xuống)

Nếu cần dữ liệu từ một file hoàn toàn khác, bạn có thể sử dụng IMPORTRANGE với Query để đưa dữ liệu đó vào. Giả sử bạn muốn đưa dữ liệu từ doanh số bán ô tô vào và đối chiếu với dữ liệu đã lấy được từ 4 trang tính, bạn có thể sử dụng công thức này:

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/yoursheetID/edit", "Test!A:G"), "SELECT Col4, SUM(Col5) GROUP BY Col4")

Bạn sẽ cần cấp quyền trước khi có thể lấy dữ liệu từ bảng tính bên ngoài.

Sau khi cấp quyền truy cập, bạn có thể lấy dữ liệu từ bảng tính bên ngoài theo thời gian thực và dữ liệu này sẽ cập nhật nếu dữ liệu nguồn thay đổi.

QUERY cho phép bạn phân tích dữ liệu trên nhiều tab và file mà không cần phải mở tab hoặc file thứ hai.

1. Sắp xếp và lọc mà không cần viết lại công thức

Bạn muốn sắp xếp hoặc lọc dữ liệu theo cách khác mà không cần phải viết lại toàn bộ công thức QUERY của mình mỗi lần? Bạn có thể làm được, chỉ với thiết lập đơn giản bằng dấu ngoặc kép và ký hiệu &:

=QUERY({Sales_Q1!A:N; Sales_Q2!A:N; Sales_Q3!A:N; Sales_Q4!A:N}, "SELECT * WHERE Col1 = '"&G21&"'", 1)

Khi sếp muốn xem dữ liệu của Châu Âu, bạn chỉ cần nhập "Europe" vào ô G21.

Khi sếp muốn dữ liệu về Châu Phi cận Sahara, hãy nhập "Sub-Saharan Africa". Chỉ cần đảm bảo đó là mục nhập hợp lệ trong cột 1. Công thức vẫn giữ nguyên, nhưng kết quả sẽ cập nhật ngay lập tức. Khi bạn đã quen, công thức này cực kỳ mạnh mẽ.

Điều kỳ diệu thực sự đã xảy ra khi bạn bắt đầu sử dụng công thức này cho các phạm vi ngày. Ngày trong công thức QUERY nổi tiếng là rất kén chọn. Chúng cần phải có định dạng chính xác (YYYY-MM-DD) nếu không mọi thứ sẽ hỏng. Nhưng với tham chiếu ô, bạn có thể thiết lập các điều khiển ngày thân thiện với người dùng:

=QUERY({Sales_Q1!A:N; Sales_Q2!A:N; Sales_Q3!A:N; Sales_Q4!A:N}, "SELECT * WHERE Col6 >= date '"&TEXT(G142, "yyyy-mm-dd")&"' AND Col6

Bây giờ, bạn có ô G142 cho ngày bắt đầu, ô I142 cho ngày kết thúc và cột 6 biểu thị Order Date Column. Khi ai đó hỏi doanh số từ ngày 15 tháng 3 năm 2011 đến ngày 30 tháng 4 năm 2015 là bao nhiêu, bạn chỉ cần thay đổi hai ô đó thay vì vật lộn với cú pháp công thức.

Phần tuyệt nhất là bạn có thể chia sẻ những bảng tính này với những đồng nghiệp không phải là chuyên gia về công thức. Họ thấy các ô nhập liệu sạch mà mình có thể thay đổi vùng hoặc điều chỉnh phạm vi ngày và họ không biết rằng có một QUERY phức tạp đang chạy đằng sau.

Hàm QUERY trong Google Sheets không chỉ là một công thức. Đó là một công cụ tự động hóa dữ liệu hoàn chỉnh. Cho dù bạn đang vật lộn với hàng chục nghìn hàng, sao chép dữ liệu giữa các trang tính hay chỉ phát ngán với việc sắp xếp và lọc lặp đi lặp lại, QUERY đều xử lý mọi thứ một cách dễ dàng (và phong cách).

Nhanh chóng. Linh hoạt. Và khi bắt đầu sử dụng hàm QUERY trong Google Sheets, bạn sẽ thực sự tự hỏi làm sao mình có thể xoay xở mà không có nó.