Bạn vẫn đang lọc dữ liệu, sao chép và dán các báo cáo trên Google Sheets một cách thủ công? Nếu câu trả lời là có, bạn đang lãng phí hàng giờ đồng hồ quý báu mỗi tuần. Đã đến lúc thay đổi điều đó. Hãy cùng khám phá 5 hàm Google Sheets nâng cao sẽ thay đổi hoàn toàn cách bạn làm việc, giúp tự động hóa các tác vụ lặp đi lặp lại và biến bạn thành một chuyên gia xử lý dữ liệu thực thụ.
Hàm QUERY – “SQL” ngay trong Google Sheets
Hàm QUERY là hàm mạnh mẽ nhất trong Google Sheets, cho phép bạn sử dụng các truy vấn tương tự ngôn ngữ SQL để lọc, sắp xếp, nhóm và tổng hợp dữ liệu từ một vùng dữ liệu lớn một cách linh hoạt mà không cần thay đổi dữ liệu gốc.
Đây là công cụ “tất cả trong một”, có thể thay thế cho nhiều hàm khác khi bạn đã sử dụng thành thạo.
Xem thêm:<Hướng dẫn sử dụng workflow n8n để tạo sự kiện tự động trong Google Calendar từ dữ liệu Google Sheets>
Cú pháp cơ bản của hàm QUERY
Cú pháp của hàm QUERY trông có vẻ phức tạp lúc đầu, nhưng thực ra lại rất logic:
=QUERY(data, query, [headers])
Trong đó:
- data: Vùng dữ liệu bạn muốn truy vấn (ví dụ:
A1:F100).
- query: Câu lệnh truy vấn được đặt trong dấu ngoặc kép
"". Đây chính là “bộ não” của hàm, nơi bạn ra lệnh cho Google Sheets phải làm gì. Các mệnh đề phổ biến nhất bao gồm:
- SELECT: Chọn các cột bạn muốn hiển thị. Dùng
SELECT * để chọn tất cả các cột.
- WHERE: Đặt điều kiện để lọc dữ liệu (ví dụ: chỉ lấy các dòng có doanh thu > 5,000,000).
- ORDER BY: Sắp xếp dữ liệu theo một cột nào đó (ví dụ:
ORDER BY B asc để sắp xếp cột B tăng dần).
- headers (tùy chọn): Số lượng hàng tiêu đề trong vùng dữ liệu của bạn.
Ví dụ thực tế
Giả sử bạn có một danh sách khách hàng trong dải ô A1:E20 với các cột: Họ tên (A), Email (B), Tỉnh thành (C), Ngày đăng ký (D), Doanh thu (E).
Để lọc ra danh sách tất cả khách hàng ở “Hà Nội” và sắp xếp theo doanh thu giảm dần, bạn chỉ cần dùng một công thức duy nhất:
=QUERY(A1:E20, "SELECT * WHERE C = 'Hà Nội' ORDER BY E desc")
Hàm IMPORTRANGE – Kết nối các trang tính
Hàm IMPORTRANGE cho phép bạn nhập một dải ô từ một trang tính này sang một trang tính khác. Đây là công cụ thiết yếu để xây dựng các dashboard tổng hợp dữ liệu từ nhiều nguồn khác nhau, chẳng hạn như kết nối dữ liệu từ các file báo cáo riêng lẻ của từng nhân viên.
Cách cấp quyền truy cập
Lần đầu tiên bạn sử dụng IMPORTRANGE để kết nối hai trang tính, bạn sẽ thấy lỗi #REF!. Đừng lo lắng, đây là một bước bảo mật của Google.
- Viết công thức IMPORTRANGE hoàn chỉnh.
- Di chuột vào ô chứa lỗi
#REF!.
- Một nút “Allow access” (Cho phép truy cập) sẽ hiện ra. Hãy nhấp vào đó.
- Sau khi cấp quyền, dữ liệu sẽ tự động được tải về. Bạn chỉ cần làm việc này một lần duy nhất cho mỗi cặp trang tính.
Ứng dụng
Giả sử phòng kinh doanh của bạn có 3 nhân viên, mỗi người có một file Google Sheets báo cáo doanh thu riêng. Bạn muốn tạo một trang tổng hợp để xem tất cả dữ liệu.
- Trong file báo cáo của bạn, bạn có thể dùng 3 công thức IMPORTRANGE ở 3 sheet khác nhau để lấy dữ liệu từ file của từng nhân viên.
- Công thức sẽ có dạng:
=IMPORTRANGE("URL_của_trang_tính_nhân_viên", "Sheet1!A2:D100")
Trong sheet Data_An:
- Sao chép URL của file
BaoCao_An_T8.
- Tại ô A1, nhập công thức:
=IMPORTRANGE("URL_của_file_An", "Sheet1!A1:D100")
- Lưu ý: Lần đầu tiên, bạn cần di chuột vào ô và nhấn nút “Allow access” (Cho phép truy cập) để kết nối hai trang tính.
2. Trong sheet Data_Binh:
- Sao chép URL của file
BaoCao_Binh_T8.
- Tại ô A1, nhập công thức:
=IMPORTRANGE("URL_của_file_Binh", "Sheet1!A1:D100")
(Bạn cũng cần “Allow access” nếu đây là lần đầu kết nối với file này).
3. Trong sheet Data_Cuong:
- Sao chép URL của file
BaoCao_Cuong_T8.
- Tại ô A1, nhập công thức:
=IMPORTRANGE("URL_của_file_Cuong", "Sheet1!A1:D100")
Các hàm hữu ích khác cần biết
Bên cạnh hai “gã khổng lồ” trên, việc nắm vững các hàm sau sẽ giúp bạn xử lý dữ liệu nhanh và chính xác hơn rất nhiều.
-
Hàm FILTER
Đây là cách lọc dữ liệu nhanh và đơn giản hơn QUERY khi bạn chỉ có nhu cầu lọc thuần túy. Bạn có thể lọc dữ liệu dựa trên một hoặc nhiều điều kiện. Ví dụ: =FILTER(A2:D100, C2:C100 > 5000000) sẽ lọc ra tất cả các dòng có giá trị ở cột C lớn hơn 5,000,000.
Lọc các đơn hàng có doanh thu trên 20,000,000
Lọc các đơn hàng ở “TP.HCM”
Lọc các đơn hàng ở “Hà Nội” VÀ có doanh thu dưới 15,000,000 (kết hợp nhiều điều kiện)
-
Hàm SORT
Đúng như tên gọi, hàm SORT giúp bạn sắp xếp dữ liệu một cách linh hoạt. Điểm mạnh của nó là khả năng sắp xếp theo nhiều cột và nhiều tiêu chí cùng lúc (ví dụ: sắp xếp theo Tỉnh thành từ A-Z, sau đó trong mỗi tỉnh lại sắp xếp theo Doanh thu từ cao đến thấp).
Sắp xếp theo Doanh thu giảm dần
=SORT(A2:E9, E2:E9, FALSE)
Sắp xếp theo Tỉnh thành A→Z, rồi Doanh thu giảm dần trong từng tỉnh
=SORT(A2:E9, C2:C9, TRUE, E2:E9, FALSE)
Sắp xếp theo Ngày tăng dần, sau đó theo Khách hàng A→Z
=SORT(A2:E9, A2:A9, TRUE, B2:B9, TRUE)
Chỉ sắp xếp cột hiển thị Họ tên, Tỉnh thành, Doanh thu và theo Doanh thu tăng dần
=SORT({B2:B9, C2:C9, E2:E9}, 3, TRUE)
Kết hợp FILTER + SORT: các đơn ở TP.HCM, sắp theo Doanh thu giảm dần
=SORT(FILTER(A2:E9, C2:C9=”TP.HCM”), 5, FALSE)
Lấy danh sách khách hàng duy nhất
- Tại một ô trống (ví dụ: F1), nhập công thức:
=UNIQUE(B2:B9)
Lấy danh sách các sản phẩm đã bán duy nhất
- Tại một ô trống khác, nhập công thức:
=UNIQUE(C2:C9)
Lấy danh sách các dòng dữ liệu hoàn toàn duy nhất
- Hàm
UNIQUE cũng có thể áp dụng trên nhiều cột. Nó sẽ chỉ loại bỏ những dòng nào có TẤT CẢ các cột đều trùng khớp y hệt. Trong ví dụ này, không có dòng nào bị trùng hoàn toàn.
=UNIQUE(A2:D9)
Lấy danh sách khách hàng duy nhất và sắp xếp theo A-Z
- Bạn có thể lồng hàm
UNIQUE bên trong hàm SORT.
=SORT(UNIQUE(B2:B9))
Các câu hỏi thường gặp (FAQ)
Sự khác biệt giữa hàm FILTER và QUERY là gì?
- FILTER đơn giản và nhanh chóng hơn, chuyên dùng để lọc dữ liệu dựa trên các điều kiện.
- QUERY là một công cụ mạnh mẽ hơn rất nhiều, nó không chỉ lọc (mệnh đề
WHERE) mà còn có thể chọn các cột cụ thể (SELECT), sắp xếp (ORDER BY), nhóm dữ liệu (GROUP BY) và thực hiện các phép tính tổng hợp (sum, avg, count). Hãy dùng QUERY khi bạn cần làm nhiều việc hơn là chỉ lọc.
Làm thế nào để kết hợp nhiều hàm với nhau?
Bạn hoàn toàn có thể lồng các hàm vào nhau để tạo ra các công thức mạnh mẽ. Đây là một mẹo Google Sheets nâng cao rất hữu ích.
Ví dụ: Bạn muốn sắp xếp một danh sách khách hàng duy nhất từ một vùng dữ liệu bị trùng lặp.
=SORT(UNIQUE(A2:A100))
Trong công thức này, hàm UNIQUE sẽ chạy trước để tạo ra danh sách khách hàng không trùng lặp, sau đó hàm SORT sẽ sắp xếp danh sách kết quả đó.
Kết bài
Việc dành thời gian để học và thành thạo 5 hàm Google Sheets trên không chỉ là một khoản đầu tư vào kỹ năng cá nhân mà còn là một bước tiến lớn trong việc tối ưu hóa quy trình làm việc. Từ việc tự động hóa các báo cáo nhàm chán đến việc phân tích dữ liệu chuyên sâu, sức mạnh của QUERY, IMPORTRANGE, FILTER, SORT, và UNIQUE sẽ giúp bạn trở thành một chuyên gia phân tích dữ liệu thực thụ ngay trong công việc hàng ngày của mình.
