Nếu bạn đang mất quá nhiều thời gian vào các tác vụ xử lý dữ liệu lặp đi lặp lại trong Google Sheets, bạn không đơn độc. Tôi từng lãng phí hàng giờ mỗi tuần chỉ vì những bước công việc cũ rích – cho đến khi tình cờ khám phá một hàm duy nhất có thể giải quyết mọi rắc rối đó. Hãy để tôi giới thiệu cho bạn về hàm QUERY, một “phù thủy” thực sự trong Google Sheets.
5. Tự động sắp xếp dữ liệu cập nhật theo thời gian thực
Cách tức thì nhất mà hàm QUERY cải thiện bảng tính của tôi là loại bỏ hoàn toàn cơn đau đầu về việc phải sắp xếp lại liên tục. Bạn biết cái vòng lặp khó chịu này chứ: bạn sắp xếp dữ liệu của mình, rồi một giờ sau, thông tin mới đổ về, và đột nhiên danh sách đã sắp xếp của bạn lại bị lộn xộn hoàn toàn. Chính xác là nó!
Giả sử bạn muốn theo dõi hàng tồn kho của công ty theo giá. Thay vì phải sắp xếp lại liên tục, bạn có thể sử dụng một công thức QUERY như sau:
=QUERY(A:G, "SELECT * ORDER BY E desc")
Trong ví dụ này, bạn đang yêu cầu Google Sheets lấy tất cả dữ liệu từ cột A đến cột G và sắp xếp chúng theo cột E (là cột chứa giá trong trường hợp này) theo thứ tự giảm dần.
Kết quả sắp xếp dữ liệu tự động bằng hàm QUERY với lệnh ORDER BY trong Google Sheets
Ngay lập tức, những mặt hàng đắt tiền nhất sẽ nổi lên đầu danh sách.
Điều làm cho tính năng này trở thành một “game changer” là nó không ngừng hoạt động. Khi một chiếc xe trị giá 250.000 USD mới đượ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 lần nữa.
Những gì trước đây tốn của tôi vài phút nhấp và kéo suốt cả ngày giờ đây diễn ra tự động. Quan trọng hơn, tôi không bao giờ bỏ lỡ các mặt hàng ưu tiên cao nữa vì chúng luôn ở đúng vị trí mà chúng cần phải có.
4. Kết hợp nhiều bước xử lý dữ liệu vào một công thức duy nhất
Tôi không thể đếm hết số lần tôi đã từng thực hiện điệu nhảy này: lọc dữ liệu, sắp xếp nó, ẩn một số cột và có thể nhóm các mục lại với nhau. Giờ đây, nhờ QUERY, tôi có thể thực hiện tất cả những việc đó chỉ trong một lần duy nhất.
Giả sử tôi muốn chuẩn bị một báo cáo tổng quan về doanh số bán hàng và tôi cần thực hiện các thao tác sau:
- Chỉ lọc những chiếc xe đã bán
- Loại bỏ bất kỳ chiếc xe nào dưới 30.000 USD
- Loại trừ những chiếc Tesla
- Sắp xếp theo giá để xem các giao dịch lớn nhất trước
Đó là bốn bước riêng biệt. Nếu tôi phải làm thủ công và mắc lỗi ở bất kỳ đâu, tôi sẽ phải bắt đầu lại từ đầu. Thay vào đó, tôi chỉ cần gõ:
=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 bốn bước. Vì tôi đã chèn hàm QUERY vào một trang tính trống, tôi đã thêm tên trang tính với dấu chấm than (Test!) trước khi chỉ định các cột chứa dữ liệu của mình.
Kết quả tổng hợp dữ liệu từ nhiều điều kiện lọc và sắp xếp bằng hàm QUERY trên trang tính trống
Phần tuyệt vời nhất là tôi còn có thể nâng cao hơn với các mệnh đề như PIVOT, LABEL, v.v. Ví dụ, khi tôi cần xem các sản phẩm của từng năm đang bán như thế nào, tôi thêm GROUP BY:
=QUERY(Test!A:G, "SELECT D, SUM(E) WHERE F = TRUE GROUP BY D ORDER BY SUM(E) desc")
Công thức này cho tôi biết doanh thu từ các sản phẩm của từng năm cho đến nay, tự động sắp xếp từ cao nhất đến thấp nhất.
Việc tiết kiệm thời gian diễn ra rất nhanh. Những gì trước đây tốn của tôi 10-15 phút để nhấp, lọc và sắp xếp giờ đây chỉ mất 30 giây để gõ một công thức. Và không giống như quy trình nhiều bước cũ của tôi, tôi không bao giờ vô tình bỏ qua một bước hoặc làm hỏng thứ tự nữa.
3. Xử lý tập dữ liệu khổng lồ mượt mà, không giật lag
Hãy hình dung thế này: Bạn đang cố gắng tìm 100 khách hàng gần đây nhất từ một cơ sở dữ liệu với hơn 50.000 hàng để gửi một chiến dịch email nhắm mục tiêu. Nghe có vẻ đơn giản phải không?
Không hề. Mỗi khi bạn cố gắng sắp xếp, lọc hoặc thao tác với một tập dữ liệu khổng lồ như vậy, Google Sheets rất có thể sẽ bị treo. Tôi đã phải đối mặt với điều này cho đến khi phát hiện ra QUERY có thể xử lý các tập dữ liệu cồng kềnh.
Đây là một ví dụ:
=QUERY('50000 Sales Records'!A:N, "SELECT * ORDER BY H desc LIMIT 100")
Công thức trên sẽ lấy 100 lô hàng gần đây nhất, vì cột H chứa ngày giao hàng. Trong khi đó, bạn có thể thay đổi LIMIT và OFFSET để lấy các hàng khác (ví dụ, LIMIT 100 OFFSET 60 sẽ lấy đơn hàng từ 61 đến 160).
Thay vì bắt máy tính của bạn xử lý và hiển thị tất cả hơn 50.000 hàng, tốn rất nhiều thời gian, chỉ để bạn 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à bỏ qua phần còn lại.
Hiển thị kết quả QUERY với lệnh ORDER BY và LIMIT trên Google Sheets giúp xử lý dữ liệu lớn hiệu quả
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 vật lộn với trình duyệt của mình và lãng phí thời gian.
2. Phân tích dữ liệu từ nhiều trang tính hoặc tệp khác nhau
Bạn vẫn đang sao chép dữ liệu thủ công giữa các bảng tính hoặc sổ làm việc chỉ để làm một báo cáo? Bạn có thể dừng lại 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 tệp hoàn toàn khác nhau – mà không cần chạm vào Ctrl+C.
Kết hợp nhiều trang tính 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 đó, thực hiện phân tích của bạn như thể bạn đang làm việc 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à Loại mặt hàng, cột 1 là Khu vực, cột 9 là Số lượng đã bán và cột 5 là Mức độ ưu tiên của đơn hàng. Chỉ cần đảm bảo cấu trúc (các cột) của mỗi trang tính khớp nhau là bạn có thể thực hiện.
Tôi vừa kết hợp bốn trang tính với dữ liệu từ các quý khác nhau để lấy tổng số đơn vị đã bán theo mặt hàng và khu vực cho các đơn hàng ưu tiên C. Dễ như ăn kẹo!
Kéo dữ liệu từ Google Sheets khác (không cần tải xuống)
Nếu bạn cần dữ liệu từ một tệp hoàn toàn khác, bạn có thể sử dụng IMPORTRANGE kết hợp với Query để kéo dữ liệu vào. Giả sử bạn muốn đưa dữ liệu doanh số bán xe của chúng tôi vào và đặt cạnh dữ liệu chúng tôi đã có từ bốn 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ể kéo dữ liệu từ bảng tính bên ngoài.
Yêu cầu cấp quyền truy cập để hàm QUERY nhập dữ liệu từ một bảng tính Google Sheets bên ngoài
Sau khi bạn 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à nó sẽ tự động 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 các tab và tệp mà không cần phải mở tab hoặc tệp thứ hai.
1. Sắp xếp và lọc linh hoạt 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 của mình theo cách khác mà không cần viết lại toàn bộ công thức QUERY mỗi lần? Bạn có thể làm được điều đó, với một thiết lập đơn giản sử dụng dấu ngoặc kép và dấu và (&):
=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 của bạn muốn xem dữ liệu từ Châu Âu, bạn chỉ cần gõ “Europe” vào ô G21.
Kết quả lọc dữ liệu linh hoạt bằng hàm QUERY với tham chiếu ô động trong Google Sheets
Khi ông ấy muốn dữ liệu từ Châu Phi cận Sahara, bạn gõ “Sub-Saharan Africa”. Chỉ cần đảm bảo đó là một mục hợp lệ trong cột 1. Công thức vẫn giữ nguyên, nhưng kết quả cập nhật ngay lập tức. Một khi bạn nắm vững nó, nó sẽ cực kỳ mạnh mẽ.
Sức mạnh thực sự bùng nổ khi tôi bắt đầu sử dụng tính năng này cho các khoảng ngày. Ngày tháng trong công thức QUERY nổi tiếng là khó tính. Chúng cần phải ở đúng định dạng (YYYY-MM-DD) hoặc mọi thứ sẽ hỏng. Nhưng với các tham chiếu ô, bạn có thể thiết lập các điều khiển ngày tháng 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 <= date '"&TEXT(I142, "yyyy-mm-dd")&"'", 1)
Bây giờ, tôi có ô G142 cho ngày bắt đầu, ô I142 cho ngày kết thúc và Cột 6 đại diện cho Cột Ngày đặt hàng. Khi ai đó hỏi doanh số của chúng tôi từ ngày 15 tháng 3 năm 2011 đến ngày 30 tháng 4 năm 2015 là bao nhiêu, tôi chỉ cần thay đổi hai ô đó thay vì vật lộn với cú pháp công thức.
Điều tuyệt vời nhất là tôi có thể chia sẻ các 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 sẽ nơi họ có thể thay đổi khu vực hoặc điều chỉnh phạm vi ngày, và họ không hề biết có một công thức QUERY phức tạp đang chạy phía sau.
Hàm QUERY trong Google Sheets không chỉ là một công thức. Đó là một cỗ máy tự động hóa dữ liệu hoàn chỉnh. Cho dù bạn đang xử lý hàng chục nghìn hàng, sao chép dữ liệu giữa các trang tính, hay chỉ đơn giản là đã chán ngấy việc sắp xếp và lọc lặp đi lặp lại, QUERY đều có thể xử lý tất cả một cách dễ dàng và hiệu quả.
Nó nhanh. Nó linh hoạt. Và một khi bạn bắt đầu sử dụng hàm QUERY trong Google Sheets, bạn sẽ thực sự tự hỏi làm thế nào bạn có thể làm việc mà không có nó từ trước đến nay.
