8 hàm hữu ích trong Google Sheets có thể bạn chưa biết

Google Sheets là công cụ vô cùng linh hoạt và vượt trội kết hợp giữa thiết lập và tính toán dữ liệu dưới dạng bảng tính. Nó hoạt động dựa trên tính năng đám mây, vì vậy nó cung cấp nhiều chức năng tương tác, thu thập dữ liệu tự động, thậm chí kéo dữ liệu từ APIs bên thứ ba.

Bạn thường làm việc với các bảng tính như Excel, iWork Numbers, Zoho Sheet hoặc Open Office Calc hẳn sẽ dễ dàng sử dụng Google Sheets. Còn nếu bạn mới bắt đầu làm quen với Sheets, hoặc đang tìm hiểu chương trình Spreadsheet thì dưới đây là 8 hàm hữu ích cho dữ liệu của bạn.

Để biết thêm các mẹo khác trong Google SpreadSheets hãy ấn vào đây.

1. COUNTIF và SUMIF trong Google Sheets

SUMIF và COUNTIF dễ hơn hàm tìm kiếm một chút. Nếu mệnh đề logic trong CountIF hoặc SumIF là đúng, Google Sheets có thể đếm số lượng các trường hợp hoặc tổng hợp giá trị tương đương.

Xem ví dụ dưới đây, bạn có thể đếm số lượng táo được bán với phép tính như sau:

=COUNTIF(B2:B10,"Apple")

Nó thể hiện rằng: Đếm số lượng các trường có từ Apple trong từ ô B2 đến B10.

Bạn có thể tính tổng trọng lượng táo đã bán sử dụng hàm SumIF.

=SUMIF(B2:B10,"Apple",C2:C10)

Nó tìm kiếm số từ Apple được nhập trong cột B sau đó tính các giá trị tổng của ô tương đương trong cột C.

2. VLOOKUP và HLOOKUP

Với ví dụ sau sẽ chứng minh được các hàm tìm kiếm của Google Sheets là tốt nhất. Chúng cho phép tìm kiếm để chuỗi của một từ và sau đó đọc giá trị sang cột hoặc dòng tương đương. Điều này thực sự hữu ích nếu bạn có các bộ dữ liệu khác nhau có cùng đối tượng trong bảng tính. Ví dụ, các dòng của dữ liệu về sản phẩm, nhân sự hoặc dự án.

Ở ví dụ dưới đây, hãy tưởng tượng bạn muốn theo dõi sự thay đổi số lượng táo, cam, lê của tháng Giêng và tháng Hai. Thứ tự của những trái cây có sẵn thay đổi theo từng tháng vì vậy không được sử dụng phép tính trừ cho các ô.

Thay vào đó, hàm VLOOKUP tìm kiếm bảng dữ liệu theo hàng dọc cho đến khi nó tìm ra điểm chung của từ và sau đó đọc theo hàng ngang để tìm giá trị tương ứng trong cột liền kề. VLOOKUP viết tắt của Vertical Lookup bởi vì nó tìm từ theo hàng dọc và sau đó theo hàng ngang để cho ra giá trị trong khi Hlook up viết tắt của Horizontal Lookup bởi nó tìm từ theo hàng ngang và sau đó cho giá trị theo hàng dọc.

Đầu tiên, với VLOOKUP sử dụng bảng và phép tính sau:

=VLOOKUP(F2,$A$2:$B$6,2,false)

Trong đó, "F2" giá trị cần tìm của "Apple" ở ô F2. Vì sử dụng VLOOKUP cho nên "$A$2:$B$6" của phép tính để chỉ cho Google Sheet tìm dọc theo bảng dữ liệu của tháng Giêng. "2" chỉ ra tìm "Apple" ở cột thứ 2. "False" là giả định nếu không tìm thấy giá trị tương đương, thì chúng bỏ qua sẽ tìm ở vị trí khác. Cuối cùng, hoàn thành phép tính tìm kiếm Apple trong bảng dữ liệu tháng Giêng và truy tìm giá trị 1003 trong cột thứ 2.

Để tính sự thay đổi, chúng ta cần lấy hàm tìm kiếm của tháng Hai trừ đi hàm tìm kiếm của tháng một do đó, có phép tính như sau:

=VLOOKUP(F2,$C$2:$D$6,2,false)-VLOOKUP(F2,$A$2:$B$6,2,false)

Phép tính tìm kiếm thứ 2 tương tự như phép tính ở phía trên, nhưng bây giờ chúng ta nhập dữ liệu tháng Hai và lấy chúng trừ dữ liệu tháng Giêng. Vì vậy, viết phép tính tìm kiếm của dữ liệu tháng Hai tương tự như của tháng Giêng chỉ thay "$A$2:$B$6" cho "$C$2:$D$6". Bây giờ chúng sẽ lấy số lượng táo của tháng hai (785) trừ đi số lượng của tháng Giêng (785) và truy xuất ra giá trị -218.

Tương tự như vậy, HLOOKUP biểu diễn hàm tương đương nhưng đọc theo hàng ngang và suy tìm theo hàng dọc.

3. IMPORTRANGE

IMPORTRANGE là một hàm hữu ích nếu bạn cần lấy dữ liệu từ các trang tính khác nhau của Google Sheets. Thay vì sao chép dữ liệu từ một trang tính và dán vào trang tính mong muốn khác, bạn có thể sử dụng công thức này để tiết kiệm thời gian.

Nếu ai đó không phải là bạn sở hữu trang tính, bạn phải có quyền truy cập vào trang tính trước khi có thể bắt đầu sử dụng hàm IMPORTRANGE.

Trong quá trình kéo dữ liệu từ các trang tính khác nhau, bất kỳ thay đổi dữ liệu nào trên trang nguồn sẽ tự động phản ánh trên trang đích.

Do đó, đây là công thức cần thiết nếu bạn đang lập báo cáo hoặc trang tổng quan bằng cách tìm nguồn dữ liệu từ các thành viên khác nhau trong nhóm dự án của mình.

Trong video trên, bạn có thể thấy cách dễ dàng nhập dữ liệu nhân khẩu học Hoa Kỳ từ trang tính này sang trang tính khác. Bạn chỉ cần đề cập đến URL của trang tính, tên trang tính và phạm vi dữ liệu mà bạn muốn nhập.

Công thức này không nhập bất kỳ định dạng trực quan nào từ nguồn. Bạn có thể sử dụng cú pháp công thức sau nếu muốn dùng thử:

=IMPORTRANGE("URL","Sheet1!B3:R11")

4. IFERROR

Bảng tính của bạn có thể bị lộn xộn nếu có quá nhiều lỗi. Lỗi thường xảy ra khi bạn áp dụng nhiều công thức trên các cột và trang tính, nhưng không có nhiều dữ liệu để trả về bất kỳ giá trị nào.

Nếu bạn chia sẻ những file như vậy với các thành viên trong nhóm hoặc khách hàng, họ có thể không thấy hài lòng. Hơn nữa, bạn cũng sẽ khó tránh khỏi việc mắc lỗi khi hoàn thành công việc. Hàm IFERROR sẽ giải cứu cho bạn.

Đặt công thức của bạn bên trong hàm IFERROR và đề cập đến văn bản nào sẽ hiển thị nếu có lỗi. Video trên cho thấy việc sử dụng IFERROR trong các tình huống khi bạn quản lý bảng tính về giá sản phẩm hoặc phân loại học sinh.

Dưới đây là các cú pháp công thức có thể hữu ích nếu bạn muốn tự mình thử:

=iferror(D4/C4, 0)
=iferror(VLOOKUP(A23,$A$13:$G$18,7,false),"ID Mismatch")

5. ARRAYFORMULA

Hàm này giúp bạn giảm bớt thời gian bỏ vào việc chỉnh sửa các công thức trong bảng tính của mình. Khi cần áp dụng các hàm cho hàng nghìn hàng và cột trong trang tính, bạn nên sử dụng ARRAYFORMULA thay thế cho các hàm không theo mảng (non-array).

Các hàm không theo mảng là những hàm mà bạn tạo trong một ô và sau đó sao chép-dán chúng vào một ô khác trong trang tính. Chương trình Google Sheets đủ thông minh để sửa đổi công thức theo địa chỉ ô. Tuy nhiên, làm như vậy, bạn sẽ mở tạo ra những vấn đề sau:

• Trang tính trở nên chậm vì các hàm riêng lẻ trong hàng nghìn ô.
• Thực hiện bất kỳ thay đổi nào trong công thức sẽ cần hàng giờ chỉnh sửa.
• Sao chép và dán các công thức không theo mảng vào các ô được chỉ định là một quá trình mệt mỏi.

Giả sử rằng bạn chịu trách nhiệm tạo một tài liệu bảng tính có tên học sinh, điểm theo môn học và tổng điểm của học sinh. Bây giờ, nếu bạn không chỉ phải tính điểm cho một vài học sinh trong lớp, mà là tất cả học sinh trên toàn thành phố, công thức dựa trên toán tử `+` thông thường sẽ tốn thời gian. Bạn có thể sử dụng ARRAYFORMULA như được đề cập bên dưới.

=ArrayFormula(B2:B+C2:C+D2:D+E2:E+F2:F)

Trong công thức này, phạm vi B2:B chỉ định vô hạn. Bạn có thể làm cho nó hữu hạn bằng cách chỉnh sửa phạm vi thành B2:B10, v.v...

6. FILTER

Mặc dù bạn có thể dễ dàng lọc và sắp xếp dữ liệu bằng cách đi tới Data > Create a Filter, nhưng sẽ dễ dàng hơn nếu chỉ sử dụng hàm FILTER để nhận được kết quả mà bạn muốn xem. Các thông số rất đơn giản:

=FILTER(phạm vi, điều kiện1, điều kiện khác)

Phần “các điều kiện khác” là tùy chọn. Về cơ bản, đây là những so sánh đúng/sai của các ô khác để lọc thêm kết quả của bạn.

1. Trong một ô trống, hãy bắt đầu công thức của bạn. Lý tưởng nhất là bạn sẽ tạo các tiêu đề cột giống như dữ liệu bạn đang lọc, sau đó bắt đầu công thức trong ô đầu tiên bên dưới tiêu đề cột đầu tiên của bạn. Ví dụ, bài viết đang lọc ID nhân viên có lương lớn hơn $120.000.

2. Nhập phạm vi của bạn và điều kiện đầu tiên. Bạn có thể đóng công thức tại đây hoặc nhập thêm các điều kiện được phân tách bằng dấu phẩy. Trong ví dụ, điều kiện là chỉ chọn các giá trị trong khoảng F2:F14 trên $120.000.

3. Kết quả của bạn sẽ xuất hiện dưới đầu cột mới (nếu bạn có).

Điều tuyệt vời về công thức này là nếu bạn thực hiện bất kỳ thay đổi nào đối với dữ liệu của mình, kết quả sẽ tự động cập nhật. Thêm một hàng mới trong phạm vi ban đầu và nó sẽ tự động được đưa vào. Đây là một tùy chọn năng động hơn nhiều so với các bộ lọc trong menu Google Sheets.

7. JOIN

Hàm JOIN dùng để sâu chuỗi các giá trị thành một văn bản giúp thuận tiện cho việc sử dụng. Hoặc đơn giản là tổng hợp một vài giá trị chủ chốt hoặc một vài HTML.

Gõ & để kết nối các giá trị của các ô khác nhau và sử dụng dấu ngoặc kép với bất cứ văn bản nào bạn muốn chèn.

Ví dụ, chúng ta sử dụng phép tính sau:

=A1 & " " & B1 & " and " & A2 & " " & B2

và kết quả là "4 Apples and 5 Pears"

Sử dụng hàm JOIN là thích hợp nhất khi liên kết nhiều giá trị. Bạn chỉ cần chỉ ra ký tự bạn muốn thêm vào giữa các giá trị và các giá trị ô bạn muốn.

Ví dụ:

=JOIN(",",A1:A5)

Chúng ta có:

1,2,3,4,5

8. SPLIT

Bạn có gặp vấn đề ngược lại và cần tách các mục ra trong một ô không? Chỉ cần sử dụng hàm SPLIT. Nó ngược lại với hàm JOIN. Chẳng hạn, có thể bạn muốn tách biệt tên và họ để dễ sắp xếp dữ liệu theo thứ tự abc theo họ.

Đối với SPLIT, các tham số là:

SPLIT(văn_bản, dấu_phân_tách, [tách_theo_mỗi], [xóa_văn_bản_trống])

Đơn giản hơn, “văn_bản” là ô bạn muốn tách, dấu_phân_tách là ký tự được sử dụng để chỉ định vị trí tách văn bản và hai tham số cuối cùng là tùy chọn. tách_theo_mỗi đề cập đến việc bạn muốn tách ở mọi ký tự phù hợp. xóa_văn_bản_trống xóa văn bản trống khỏi kết quả của bạn. Nó được đặt thành TRUE theo mặc định.

Bạn sẽ cần hai hoặc nhiều ô trống, một ô cho mỗi phần của văn bản sẽ được chia. Ví dụ đang tách tên đầy đủ thành họ và tên, vì vậy chỉ cần hai ô trống.

1. Trong ô trống đầu tiên của bạn, hãy bắt đầu công thức của bạn bằng =SPLIT(

2. Nhập ô bạn muốn tách.

3. Nhập dấu phân cách bạn muốn. Trong ví dụ này, đó là một khoảng trống, vì vậy " " sẽ được sử dụng, nhưng đây có thể là bất kỳ thứ gì, chẳng hạn như một chữ cái hoặc ký hiệu.

4. Đóng công thức của bạn và điền vào nhiều ô trong cùng một hàng.

Bạn có thể áp dụng các hàm Google Sheets nêu trên trong nhiều trường hợp khác nhau. Chắc chắn rằng bạn có thể tìm thấy nhiều tình huống mới để áp dụng các công thức này. Chúng sẽ tiết kiệm thời gian và giúp bạn diễn giải dữ liệu một cách dễ hiểu.