Bảng tính được tạo ra để tiết kiệm thời gian, nhưng đôi khi chúng lại khiến mọi thứ trở nên phức tạp hơn. Việc phải dò tìm qua vô số menu để thực hiện các tác vụ lặp đi lặp lại có thể gây nản lòng. Tuy nhiên, chỉ với một số công thức cơ bản, bạn có thể biến Google Sheets thành một công cụ làm việc hiệu quả đến không ngờ, giải phóng bạn khỏi những công việc thủ công tẻ nhạt.
Là một biên tập viên chuyên sâu về công nghệ tại thuthuathot.com, tôi hiểu rõ giá trị của việc làm chủ các công cụ kỹ thuật số. Bài viết này sẽ giới thiệu 8 công thức Google Sheets “thần kì” mà tôi đã sử dụng và thấy cực kỳ hiệu quả. Chúng không chỉ giúp tôi tiết kiệm hàng giờ làm việc mỗi tuần mà còn đảm bảo độ chính xác và tính tự động hóa cao cho các bảng tính. Hãy cùng khám phá những công thức này để tối ưu hóa quy trình làm việc của bạn ngay hôm nay.
1. VLOOKUP: Công Cụ Tra Cứu Dữ Liệu Mạnh Mẽ
VLOOKUP là một trong những hàm tìm kiếm và tham chiếu mạnh mẽ nhất trong Google Sheets. Chức năng chính của nó là tìm kiếm một giá trị cụ thể trong cột đầu tiên của một dải ô và trả về một giá trị tương ứng từ một cột khác trong cùng hàng đó. Bạn có thể coi đây là công cụ tra cứu giúp bạn không phải cuộn thủ công qua hàng ngàn dòng dữ liệu.
Cú pháp của hàm VLOOKUP như sau:
VLOOKUP(giá_trị_tìm_kiếm, dải_ô, chỉ_mục_cột, [là_đã_sắp_xếp])Trong đó:
- giá_trị_tìm_kiếm: Là giá trị bạn muốn tìm.
- dải_ô: Là bảng hoặc dải ô mà bạn muốn tìm kiếm.
- chỉ_mục_cột: Cho VLOOKUP biết cột nào để trả về dữ liệu (số thứ tự của cột trong dải_ô).
- là_đã_sắp_xếp: Cho biết dữ liệu của bạn có được sắp xếp hay không. Nếu bạn đặt tham số này thành
FALSE(hoặc0), VLOOKUP sẽ tìm kiếm chính xác giá trị, đây là lựa chọn phổ biến nhất cho hầu hết các tác vụ tra cứu.
Ví dụ thực tế về cách VLOOKUP hoạt động:
=VLOOKUP("Saniya", B2:J3000, 9, FALSE)Công thức này sẽ tìm kiếm “Saniya” trong cột B của dải dữ liệu từ B2 đến J3000 và trả về giá trị tương ứng từ cột thứ 9 trong dải đó. Đây là lựa chọn hoàn hảo nếu bạn muốn tìm kiếm chi tiết nhân viên, thông tin sản phẩm hoặc bất kỳ dữ liệu nào được tổ chức theo bảng.
Kết quả hàm VLOOKUP trong Google Sheets minh họa cách tra cứu dữ liệu
Tuy nhiên, VLOOKUP có một hạn chế là nó chỉ tìm kiếm từ trái sang phải; nó không thể nhìn sang trái cột tìm kiếm của bạn. Nhưng đối với hầu hết các tác vụ hàng ngày, nó xử lý việc tra cứu dữ liệu một cách hoàn hảo. Để có thêm ví dụ chi tiết, bạn có thể tham khảo hướng dẫn của chúng tôi về cách sử dụng hàm VLOOKUP trong Google Sheets.
2. SUMIF: Tổng Hợp Dữ Liệu Có Điều Kiện Dễ Dàng
SUMIF là hàm cho phép bạn cộng các giá trị trong một dải ô dựa trên các tiêu chí cụ thể mà bạn đặt ra. Thay vì tính toán thủ công tổng số cho các danh mục khác nhau, công thức này sẽ thực hiện công việc nặng nhọc một cách chính xác.
Trong khi hàm IF cơ bản chỉ trả về TRUE hoặc FALSE dựa trên điều kiện, SUMIF đi xa hơn bằng cách thực hiện phép tính trên dữ liệu đáp ứng tiêu chí của bạn. Nó có cú pháp sau:
SUMIF(dải_ô, tiêu_chí, [dải_tính_tổng])Ở đây:
- dải_ô: Chứa các ô bạn muốn kiểm tra dựa trên tiêu chí của mình.
- tiêu_chí: Là điều kiện của bạn.
- dải_tính_tổng: Là các giá trị thực tế để cộng lại. Nếu bạn bỏ qua
dải_tính_tổng, SUMIF sẽ cộng các giá trị trong chínhdải_ôđó. Nó hoạt động với văn bản, số, và thậm chí cả ký tự đại diện, chẳng hạn như dấu hoa thị (*) để khớp một phần.
Ví dụ:
=SUMIF(V2:V3000, "<1000", Z2:Z3000)Trong ví dụ trên, công thức kiểm tra cột V để tìm các giá trị nhỏ hơn 1000, sau đó tính tổng các số tiền tương ứng trong cột Z. Công thức này rất hữu ích để tính tổng doanh số, các danh mục chi phí hoặc bất kỳ phép tính tổng có điều kiện nào.
Ví dụ hàm SUMIF trong Google Sheets, hiển thị tổng hợp theo điều kiện
Bạn thậm chí có thể kết hợp nhiều tiêu chí, mặc dù bạn sẽ cần sử dụng hàm SUMIFS cho mức độ phức tạp đó. Để có các ví dụ phức tạp hơn và cách kết hợp tiêu chí nâng cao, bạn có thể tham khảo hướng dẫn chi tiết của chúng tôi về cách sử dụng hàm SUMIF trong Google Sheets.
3. CONCATENATE: Ghép Nối Văn Bản Chuyên Nghiệp
Hàm CONCATENATE kết hợp văn bản từ nhiều ô thành một ô duy nhất. Khi bạn cần ghép họ và tên, tạo địa chỉ email hoặc xây dựng các nhãn tùy chỉnh, bạn có thể sử dụng hàm này trong công thức của mình để xử lý việc thao tác văn bản.
Bạn cũng có thể sử dụng ký hiệu dấu và (&) làm phím tắt để ghép nối cơ bản, mặc dù sử dụng hàm CONCATENATE trong Google Sheets mang lại sự linh hoạt hơn cho các kết hợp phức tạp. Cú pháp của CONCATENATE là:
CONCATENATE(chuỗi1, [chuỗi2, ...])Trong đó, chuỗi1 là giá trị văn bản đầu tiên của bạn, và bạn có thể thêm không giới hạn các chuỗi bổ sung. Mỗi tham số có thể là tham chiếu ô, văn bản thực tế trong dấu ngoặc kép hoặc sự kết hợp của cả hai.
Hãy nhớ rằng hàm này không tự động thêm khoảng trắng, vì vậy bạn cần thêm chúng thủ công dưới dạng các tham số riêng biệt, như trong ví dụ sau.
=CONCATENATE(B2, " ", C2)Công thức này kết hợp các giá trị từ ô B2 và C2, được phân tách bằng một khoảng trắng. Nó hoàn hảo để tạo tên đầy đủ, mã sản phẩm, địa chỉ được định dạng, v.v.
Minh họa hàm CONCATENATE trong Google Sheets kết hợp văn bản từ nhiều ô
CONCATENATE có thể trở nên khó sử dụng nếu bạn dùng nhiều tham số, nhưng nó vẫn là cách đáng tin cậy nhất để hợp nhất dữ liệu văn bản. Bạn cũng có thể sử dụng CONCATENATE trong Excel để nối văn bản từ nhiều ô.
4. COUNTIF: Đếm Các Ô Theo Điều Kiện Đặt Trước
COUNTIF đếm các ô đáp ứng tiêu chí cụ thể trong một dải ô. Khi bạn cần theo dõi có bao nhiêu mục thuộc một danh mục nhất định – như đếm số nhiệm vụ đã hoàn thành hoặc tổng hợp các phản hồi cụ thể – công thức này sẽ loại bỏ lỗi đếm thủ công.
Nó hoạt động tốt cho việc phân tích dữ liệu khảo sát, theo dõi hàng tồn kho và các chỉ số hiệu suất. Cú pháp của COUNTIF là:
COUNTIF(dải_ô, tiêu_chí)Trong đó, dải_ô chỉ định các ô bạn muốn kiểm tra, và tiêu_chí xác định điều kiện mà các ô phải đáp ứng để được đếm. Tiêu chí có thể bao gồm văn bản, số hoặc các toán tử logic. Sau đây là một ví dụ:
=COUNTIF(K2:K3000, "Active")Công thức này đếm số lượng ô trong dải K2:K3000 chứa chính xác văn bản “Active”. Bạn có thể sử dụng hàm COUNTIF trong Google Sheets để theo dõi trạng thái dự án, đếm các loại sản phẩm cụ thể hoặc phân tích dữ liệu phân loại.
Kết quả hàm COUNTIF trong Google Sheets, đếm số ô thỏa mãn điều kiện
COUNTIF cũng hỗ trợ các ký tự đại diện như dấu hoa thị (*) cho các kết quả khớp văn bản một phần và dấu hỏi chấm (?) cho các ký tự đơn lẻ.
Bạn cũng có thể sử dụng các toán tử so sánh như “>50” hoặc “>=100” để đếm các dải số. Công thức này rất tiện dụng khi xử lý các tập dữ liệu lớn mà việc đếm thủ công đơn giản là không thực tế.
5. ARRAYFORMULA: Tự Động Hóa Công Thức Cho Toàn Bộ Dải Ô
Tiếp theo trong danh sách là ARRAYFORMULA, công thức này áp dụng một công thức duy nhất cho toàn bộ dải ô một cách tự động. Thay vì sao chép công thức xuống hàng trăm dòng, bạn chỉ cần viết nó một lần và để Google Sheets xử lý phần còn lại.
Tôi đã thấy công thức này cực kỳ hữu ích khi làm việc với các tập dữ liệu lớn liên tục mở rộng. Thay vì phải nhớ kéo công thức xuống mỗi khi có dữ liệu mới xuất hiện, ARRAYFORMULA sẽ tự động cập nhật mọi thứ. Cú pháp cơ bản bao bọc công thức thông thường của bạn:
=ARRAYFORMULA(công_thức_của_bạn_ở_đây)Tuy nhiên, bạn cần tham chiếu toàn bộ cột hoặc dải ô thay vì các ô đơn lẻ. Ví dụ, thay vì A2, bạn sẽ sử dụng A2:A để bao gồm toàn bộ cột từ hàng 2 trở xuống.
Đây là một ví dụ thực tế kết hợp họ và tên:
=ARRAYFORMULA(B2:B & " " & C2:C)Công thức này sẽ ghép nối từng cặp tương ứng trong cột B và C cùng một lúc. Kết quả xuất hiện ngay lập tức trên tất cả các hàng có dữ liệu. Hơn nữa, nó tự động điều chỉnh khi các hàng mới được thêm vào.
Cách hàm ARRAYFORMULA hoạt động trong Google Sheets, áp dụng công thức cho dải ô
Để có thêm ví dụ, bạn có thể tham khảo hướng dẫn của chúng tôi về hàm ARRAYFORMULA trong Google Sheets. Nó chắc chắn đáng để bạn dành thời gian tìm hiểu, đặc biệt nếu bạn thường xuyên làm việc với dữ liệu.
6. FILTER: Lọc Dữ Liệu Động Một Cách Nhanh Chóng
Hàm FILTER trích xuất các hàng cụ thể từ một tập dữ liệu dựa trên các điều kiện bạn định nghĩa. Khi bạn cần xem chỉ những nhân viên, dự án hoặc hồ sơ bán hàng nhất định mà không cần ẩn hàng thủ công, công thức này sẽ tạo ra một tập con động ngay lập tức.
Điều tôi đánh giá cao nhất ở FILTER là kết quả tự động cập nhật khi dữ liệu nguồn của bạn thay đổi, không giống như việc lọc thủ công. Điều này rất hữu ích cho các bảng điều khiển và báo cáo trực tiếp. Cú pháp theo mẫu này:
FILTER(dải_ô, điều_kiện1, [điều_kiện2], ...)dải_ô chứa dữ liệu của bạn, trong khi điều_kiện1 chỉ định tiêu chí đầu tiên của bạn. Ngoài ra, bạn có thể xếp chồng nhiều điều kiện để lọc chính xác hơn, như được hiển thị trong ví dụ dưới đây.
=FILTER(D2:D3000, R2:R3000="Engineers")Công thức này chỉ hiển thị các nhân viên thuộc phòng Kỹ thuật từ cơ sở dữ liệu nhân viên. Do đó, bạn nhận được chính xác tập con mình cần mà không cần cuộn qua các bản ghi không liên quan.
Ví dụ hàm FILTER trong Google Sheets, hiển thị kết quả lọc dữ liệu theo điều kiện
FILTER hoạt động đặc biệt tốt cho việc theo dõi dự án, phân tích chi phí và quản lý khách hàng, vì nó xử lý các tiêu chí văn bản và số liệu đều tốt.
Nếu bạn muốn xem thêm các ví dụ chi tiết và kỹ thuật lọc, hãy tham khảo hướng dẫn của chúng tôi về hàm FILTER trong Google Sheets. Thành thật mà nói, đây là một trong những công thức thực sự giúp tiết kiệm hàng giờ làm việc thủ công khi bạn liên tục phải trích xuất các tập con dữ liệu khác nhau cho báo cáo và cuộc họp.
7. QUERY: Sức Mạnh Truy Vấn Dữ Liệu Chuẩn Cơ Sở Dữ Liệu
QUERY mang khả năng tìm kiếm theo phong cách cơ sở dữ liệu vào Google Sheets bằng cách sử dụng ngôn ngữ giống SQL. Mặc dù ban đầu nghe có vẻ phức tạp, nhưng hàm Google Sheets này có thể giúp bạn tiết kiệm hàng giờ mỗi tuần. Nó khá đơn giản một khi bạn hiểu những điều cơ bản và cực kỳ mạnh mẽ để phân tích dữ liệu văn phòng.
Nó kết hợp lọc, sắp xếp và nhóm vào một công thức duy nhất. Thay vì sử dụng nhiều hàm, bạn có thể trích xuất chính xác những gì bạn cần chỉ với một lệnh. Cú pháp cơ bản trông như thế này:
QUERY(dữ_liệu, truy_vấn, [số_hàng_tiêu_đề])Tham số dữ_liệu chứa dải ô của bạn, truy_vấn chứa lệnh giống SQL của bạn trong dấu ngoặc kép, và số_hàng_tiêu_đề chỉ định bao nhiêu hàng tiêu đề cần bao gồm. Trong ví dụ sau, QUERY phân tích dữ liệu hiệu suất của nhân viên:
=QUERY(A1:AB3000, "SELECT D, Q, AB ORDER BY AB DESC")Công thức này truy xuất tên nhân viên (cột D), phòng ban (cột Q) và điểm hiệu suất (cột AB), sau đó sắp xếp theo điểm hiệu suất theo thứ tự giảm dần. Về cơ bản, bạn đang tạo các báo cáo tùy chỉnh mà nếu không có nó sẽ yêu cầu bạn sử dụng pivot tables trong Excel hoặc Google Sheets.
Kết quả hàm QUERY trong Google Sheets, minh họa truy vấn dữ liệu phức tạp
QUERY có thể hữu ích trong phân tích bán hàng, theo dõi dự án và báo cáo ngân sách vì nó xử lý tìm kiếm văn bản, dải ngày và các phép toán học. Để có thêm ví dụ, bạn có thể tham khảo hướng dẫn chi tiết của chúng tôi về hàm QUERY trong Google Sheets.
8. IMPORTRANGE: Nhập Dữ Liệu Từ Các Tệp Google Sheets Khác
Hàm này trực tiếp nhập dữ liệu từ các tệp Google Sheets khác vào bảng tính hiện tại của bạn. Khi bạn đang quản lý nhiều tệp dự án, công cụ theo dõi ngân sách hoặc báo cáo phòng ban, IMPORTRANGE có thể loại bỏ thói quen sao chép-dán cho bạn.
Nó duy trì kết nối trực tiếp giữa các tệp. Kết quả là, khi đồng nghiệp của bạn cập nhật bảng tính bán hàng tổng thể, bảng điều khiển của bạn sẽ tự động phản ánh những thay đổi đó, không cần cập nhật thủ công. Cú pháp yêu cầu hai thông tin chính:
IMPORTRANGE("url_bảng_tính", "dải_ô")url_bảng_tính là địa chỉ web đầy đủ của tệp nguồn, trong khi dải_ô chỉ định chính xác các ô cần nhập bằng ký hiệu tiêu chuẩn, chẳng hạn như “Sheet1!A1:C10”. Sau đây là một ví dụ:
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/abc123", "B2:AD50")Công thức này nhập dữ liệu nhân viên từ tệp của một phòng ban khác trực tiếp vào báo cáo tổng hợp của bạn. Nhưng bạn sẽ cần cấp quyền lần đầu tiên, vì Google Sheets sẽ nhắc bạn kết nối các tệp.
Minh họa hàm IMPORTRANGE trong Google Sheets, nhập dữ liệu từ bảng tính khác
IMPORTRANGE rất tiện dụng để tạo bảng điều khiển điều hành, hợp nhất báo cáo nhóm và duy trì các chế độ xem dữ liệu tập trung, vì đây là cách dễ nhất để nhập dữ liệu từ một tệp Google Sheets khác. Tuy nhiên, hãy nhớ rằng quá nhiều công thức IMPORTRANGE có thể làm chậm bảng tính của bạn.
Kết Luận
Tám công thức Google Sheets “thần kì” này thực sự biến công việc bảng tính trở nên dễ dàng hơn rất nhiều. Mặc dù có một chút đường cong học tập ban đầu, nhưng lợi ích về thời gian tiết kiệm được sẽ trở nên rõ ràng ngay khi bạn sử dụng chúng thường xuyên. Các tác vụ dữ liệu từng mất hàng giờ giờ đây sẽ hoàn thành chỉ trong vài phút, và đó là một sự đầu tư xứng đáng.
Hãy thử áp dụng những công thức này vào công việc hàng ngày của bạn để trải nghiệm sự khác biệt. Đừng ngần ngại khám phá và thử nghiệm để tìm ra cách tối ưu nhất cho riêng mình. Google Sheets là một công cụ mạnh mẽ, và việc làm chủ các hàm cơ bản này là bước đầu tiên để bạn trở thành một chuyên gia dữ liệu thực thụ!
Nếu bạn muốn tìm hiểu sâu hơn về từng hàm hoặc khám phá thêm các thủ thuật công nghệ hữu ích khác, đừng quên theo dõi thuthuathot.com – nguồn thông tin uy tín hàng đầu về công nghệ tại Việt Nam.
Tài liệu tham khảo:
- Cách sử dụng hàm VLOOKUP trong Google Sheets
- Sử dụng hàm SUMIF trong Google Sheets
- Cách sử dụng hàm CONCATENATE trong Google Sheets
- Cách dùng hàm COUNTIF và COUNTIFS trong Google Sheets
- Sử dụng hàm ARRAYFORMULA trong Google Sheets
- Sử dụng hàm FILTER trong Google Sheets
- Hàm QUERY trong Google Sheets: Tiết kiệm hàng giờ mỗi tuần
- Cách sử dụng hàm QUERY trong Google Sheets
- Cách dễ nhất để nhập dữ liệu từ Google Sheets với IMPORTRANGE
- Sử dụng hàm CONCATENATE/CONCAT trong Excel
- Pivot Tables trong Excel: Giải thích
