Nếu bạn đ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 hề đơn độc. Trước đây, tôi cũng từng mất hàng giờ mỗi tuần chỉ để thực hiện các bước tương tự – cho đến khi tôi tìm thấy một hàm duy nhất có thể giải quyết hầu hết các công việc nặng nhọc đó. Hãy để tôi giới thiệu cho bạn hàm QUERY trong Google Sheets, một công cụ mạnh mẽ sẽ thay đổi cách bạn làm việc với dữ liệu. Hàm này không chỉ giúp bạn tiết kiệm thời gian đáng kể mà còn nâng cao độ chính xác và hiệu quả trong việc xử lý các bảng tính phức tạp, từ việc sắp xếp tự động đến phân tích dữ liệu khổng lồ hay kết nối các tệp khác nhau.
5. Tự động sắp xếp dữ liệu theo thời gian thực
Cách nhanh 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 những cơn đau đầu do việc phải sắp xếp lại dữ liệu liên tục. Bạn có biết chu trình khó chịu khi bạn sắp xếp dữ liệu, rồi một giờ sau, thông tin mới được thêm vào và danh sách đã sắp xếp của bạn đột nhiên trở nên lộn xộn trở lại? Đó chính là vấn đề mà QUERY giải quyế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 liên tục, bạn có thể sử dụng hàm 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 G và sắp xếp chúng theo cột E (đây là cột chứa giá) theo thứ tự giảm dần.
Kết quả sắp xếp dữ liệu tồn kho theo giá giảm dần bằng hàm QUERY ORDER BY trong Google Sheets.
Ngay lập tức, những mặt hàng đắt tiền nhất của bạn sẽ nổi lên đầu danh sách. Điều khiến điều này trở thành một yếu tố thay đổi cuộc chơi là nó không ngừng hoạt động. Khi một chiếc xe trị giá 250.000 đô la 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ả của hàm QUERY. Không cần phải sắp xếp lại.
Những gì trước đây tô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, tôi không bao giờ bỏ lỡ các mục ư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
Tôi không thể nhớ bao nhiêu lần tôi phải thực hiện một chuỗi các thao tác: 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ờ hàm 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ản đánh giá bán hàng và tôi cần thực hiện các yêu cầu sau:
- Chỉ lọc ra những chiếc xe đã bán.
- Loại bỏ bất kỳ chiếc xe nào dưới 30.000 đô la.
- Loại trừ các dòng xe 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ế 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ả truy vấn đa điều kiện với hàm QUERY trên một trang tính trống trong Google Sheets.
Phần tốt nhất là tôi thậm chí có thể sử dụng các mệnh đề nâng cao hơn như PIVOT, LABEL, v.v. Chẳng hạn, 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 thấy doanh thu cho đến nay từ các sản phẩm của mỗi năm, 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 rất nhanh. Những gì trước đây tôi mất 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 lộn xộn thứ tự nữa.
3. Xử lý các tập dữ liệu khổng lồ 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 của mình từ một cơ sở dữ liệu có hơn 50.000 hàng để gửi một chiến dịch email mục tiêu. Nghe có vẻ đơn giản, đúng không?
Sai. 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 lớn 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 tôi phát hiện ra hàm QUERY có thể xử lý các tập dữ liệu cồng kềnh.
Dưới đâ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 gửi hàng. Trong khi đó, công thức thứ hai sẽ lấy các đơ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, trong khi phải chờ đợi rất lâu, chỉ để bạn có thể xem 100 hàng đầu, hàm 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.
Kết quả truy xuất 100 bản ghi mới nhất từ tập dữ liệu lớn bằng hàm QUERY kết hợp ORDER BY và LIMIT.
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 cần phải vật lộn 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 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ờ. Hàm 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 phải chạm vào 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 đó, thực hiện phân tích của bạn giống như bạn 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à 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. Thật dễ dàng!
Kéo dữ liệu từ một Google Spreadsheet 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 với Query để đưa nó vào. Giả sử bạn muốn lấy dữ liệu bán xe của chúng ta và đối chiếu nó với dữ liệu chúng ta đã lấy 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 để kéo dữ liệu từ một bảng tính Google Sheets bên ngoài sử dụng IMPORTRANGE và QUERY.
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ự cập nhật nếu dữ liệu nguồn thay đổi.
Hàm QUERY cho phép bạn phân tích dữ liệu trên nhiều tab và tệp mà không cần phải mở một 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 một 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 hoàn toàn có thể, 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 bạn muốn xem dữ liệu từ khu vực Châu Âu, bạn chỉ cần nhập “Europe” vào ô G21.
Kết quả truy vấn động theo điều kiện ô tham chiếu trong Google Sheets sử dụng hàm QUERY.
Khi sếp muốn dữ liệu khu vực Châu Phi Hạ 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 điều này, nó sẽ cực kỳ mạnh mẽ.
Sự kỳ diệu thực sự xảy ra khi tôi bắt đầu sử dụng điều này cho các khoảng ngày. Các ngày 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â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")&"'")
Giờ đây, 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 các đồ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 khoảng ngày, và họ không hề biết có một hàm 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 đơn thuần. Nó là một cỗ máy tự động hóa dữ liệu toàn diện. 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 với việc sắp xếp và lọc lặp đi lặp lại, hàm QUERY sẽ xử lý tất cả một cách dễ dàng và hiệu quả.
Nó nhanh chóng. 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 mình có thể xoay sở được mà không có nó. Hãy thử nghiệm ngay hôm nay để khám phá toàn bộ tiềm năng của công cụ này và đưa kỹ năng quản lý dữ liệu của bạn lên một tầm cao mới!
