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 lộn xộn hơn. Việc phải mày mò qua vô số menu để tìm kiếm thông tin có thể gây nản chí. Là một chuyên gia công nghệ tại thuthuatmobile.net, chúng tôi hiểu rõ thách thức này. Đó là lý do vì sao chúng tôi đã chọn lọc và tổng hợp 8 công thức Google Sheets “đáng đồng tiền bát gạo” dưới đây – những công cụ mạnh mẽ sẽ đảm nhận phần lớn công việc nặng nhọc, giúp bạn tự động hóa quy trình, tối ưu hóa dữ liệu và biến Google Sheets thành trợ thủ đắc lực trong công việc hàng ngày. Dù bạn là người mới hay đã có kinh nghiệm, việc nắm vững các hàm này chắc chắn sẽ nâng tầm kỹ năng xử lý bảng tính của bạn.
8. VLOOKUP
Hàm VLOOKUP 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ề giá trị tương ứng từ một cột khác trong cùng hàng. Bạn có thể coi nó là một công cụ tra cứu giúp bạn không phải cuộn thủ công qua các tập dữ liệu khổng lồ. Việc sử dụng VLOOKUP sẽ tiết kiệm đáng kể thời gian khi cần truy xuất thông tin nhanh chóng. Cú pháp của VLOOKUP là:
VLOOKUP(search_key, range, index, [is_sorted])Ở đây, search_key là giá trị bạn muốn tìm, và range là bảng nơi bạn đang tìm kiếm. Tham số index cho VLOOKUP biết cột nào để trả về dữ liệu, trong khi is_sorted cho biết liệu dữ liệu của bạn có được sắp xếp hay không.
Nếu bạn đặt is_sorted thành FALSE, hàm sẽ hiển thị các kết quả khớp chính xác, đây thường là điều bạn mong muốn cho hầu hết các tác vụ tra cứu dữ liệu.
Đây là cách VLOOKUP hoạt động trong thực tế:
=VLOOKUP("Saniya", B2:J3000, 9, FALSE)Công thức này tìm kiếm “Saniya” trong cột B và trả về giá trị tương ứng từ cột thứ chín. Hàm VLOOKUP hoàn hảo nếu bạn muốn tì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 trong các bảng.
Kết quả của hàm VLOOKUP hiển thị trong Google Sheets, minh họa cách tìm kiếm và trả về dữ liệu chính xác.
Tuy nhiên, có một hạn chế của VLOOKUP là nó chỉ tìm kiếm sang bên phải. Nó không thể nhìn sang bên trái của 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ó các ví dụ chi tiết hơn, bạn có thể tham khảo hướng dẫn chuyên sâu của chúng tôi về cách sử dụng hàm VLOOKUP trong Google Sheets.
7. SUMIF
SUMIF 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ẽ làm công việc nặng nhọc, và nó rất chính xác.
Trong khi hàm IF cơ bản trả về TRUE hoặc FALSE dựa trên điều kiện, SUMIF tiến xa hơn bằng cách thực hiện các 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(range, criteria, [sum_range])Ở đây, range chứa các ô bạn đang kiểm tra theo tiêu chí của mình, criteria là điều kiện của bạn, và sum_range là các giá trị thực tế để cộng lại. Nếu bạn bỏ qua sum_range, SUMIF sẽ cộng các giá trị trong chính dải ô đó. Hàm này chỉ 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ị (*) cho các kết quả khớp một phần.
=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 đó cộ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.
Minh họa cách hàm SUMIF tính tổng các giá trị trong Google Sheets dựa trên điều kiện cụ thể.
Bạn thậm chí có thể kết hợp nhiều tiêu chí, mặc dù bạn sẽ cần SUMIFS cho mức độ phức tạp đó. Để có các ví dụ phức tạp hơn và 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.
6. CONCATENATE
Hàm CONCATENATE này 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 tên và họ, 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 và (&) làm lối tắt cho việc nối chuỗ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(string1, [string2, ...])Ở đây, string1 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à một 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ư được hiển thị 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à nhiều hơn nữa.
Kết quả của hàm CONCATENATE khi ghép nối văn bản từ nhiều ô thành một chuỗi duy nhất trong Google Sheets.
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 ô.
5. COUNTIF
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ố tác 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 để 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(range, criteria)Ở đây, range chỉ định các ô bạn muốn kiểm tra, và criteria định nghĩa điều kiện mà các ô phải đáp ứng để được đếm. Các 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.
Ví dụ về hàm COUNTIF đếm số lượng ô thỏa mãn một tiêu chí nhất định trong Google Sheets.
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 (?) cho các thay thế ký tự đơn.
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 hữu ích 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ế.
4. ARRAYFORMULA
Tiếp theo trong danh sách là ARRAYFORMULA, hàm này tự động áp dụng một công thức duy nhất cho toàn bộ một dải ô. Thay vì sao chép công thức xuống hàng trăm hàng, bạn chỉ cần viết một lần và để Google Sheets xử lý phần còn lại.
Tôi thấy điều này 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 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(your_formula_here)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.
Dưới đây là một ví dụ thực tế kết hợp tên và họ:
=ARRAYFORMULA(B2:B & " " & C2:C)Công thức này nối mọi 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.
ARRAYFORMULA tự động áp dụng công thức cho một dải ô lớn, hiển thị kết quả đồng loạt trong Google Sheets.
Để 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 để học, đặc biệt nếu bạn thường xuyên làm việc với dữ liệu.
3. FILTER
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 hợ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ư 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 tuân theo mẫu sau:
FILTER(range, condition1, [condition2], ...)Range chứa dữ liệu của bạn, trong khi condition1 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 ban Kỹ sư từ cơ sở dữ liệu nhân viên. Do đó, bạn nhận được chính xác tập hợp con mà bạn cần mà không cần cuộn qua các bản ghi không liên quan.
Kết quả của hàm FILTER khi trích xuất các hàng dữ liệu cụ thể dựa trên điều kiện trong Google Sheets.
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ả tiêu chí văn bản và số một cách hiệu quả.
Nếu bạn muốn xem các ví dụ chi tiết hơn và kỹ thuật lọc, hãy xem 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ự tiết kiệm hàng giờ làm việc thủ công khi bạn liên tục kéo các tập hợp con dữ liệu khác nhau cho các báo cáo và cuộc họp.
2. QUERY
QUERY mang tính năng tìm kiếm kiểu cơ sở dữ liệu vào Google Sheets bằng ngôn ngữ giống SQL. Mặc dù ban đầu nghe có vẻ đáng sợ, nhưng hàm Google Sheets này có thể tiết kiệm cho bạn hàng giờ mỗi tuần. Nó rất đơn giản một khi bạn hiểu những điều cơ bản và mạnh mẽ cho việc 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ỉ bằng một lệnh. Cú pháp cơ bản trông như thế này:
QUERY(data, query, [headers])Tham số data chứa dải ô của bạn, query chứa lệnh giống SQL của bạn trong dấu ngoặc kép, và headers chỉ định số 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 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, bạn sẽ cần sử dụng bảng Pivot trong Excel hoặc Google Sheets.
Hàm QUERY thực hiện truy vấn dữ liệu phức tạp với cú pháp giống SQL, hiển thị kết quả trong Google Sheets.
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, phạm vi ngày và các phép toán số 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.
1. IMPORTRANGE
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 đó và không cần cập nhật thủ công. Cú pháp yêu cầu hai phần thông tin chính.
IMPORTRANGE("spreadsheet_url", "range")Spreadsheet_url là địa chỉ web đầy đủ của tệp nguồn, trong khi range 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 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.
Kết quả của hàm IMPORTRANGE khi nhập trực tiếp dữ liệu từ một tệp Google Sheets khác.
IMPORTRANGE rất hữu ích để tạo các bảng điều khiển cho giám đốc điều hành, hợp nhất các báo cáo nhóm và duy trì 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.
Tóm lại, 8 công thức Google Sheets thiết yếu này sẽ giúp công việc với bảng tính trở nên dễ dàng hơn rất nhiều. Mặc dù có một quá trình 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 tốn nhiều thời gian giờ đây có thể hoàn thành chỉ trong vài phút, và điều đó thực sự xứng đáng với nỗ lực bỏ ra. Hãy bắt đầu áp dụng các thủ thuật Google Sheets này để nâng cao hiệu suất công việc của bạn ngay hôm nay!
