Thủ thuật

Cách kết hợp hàm SUM và hàm OFFSET trong Google Sheet chi tiết, đơn giản nhất

Trương Nguyễn Anh Thư

30-04-2023, 10:36 pm

Hàm SUM kết hợp với hàm OFFSET là cách kết hợp khá phổ biến được sử dụng để tính toán với nhiều mục đích khác nhau với nhiều dữ liệu trong Google Sheet. Để hiểu rõ hơn về cách kết hợp này thì hãy cùng với mình tìm hiểu ngay bài viết dưới đây nhé!

Hướng dẫn cách sử dụng hàm SUM trong Google Sheet đơn giản

Hàm SUM là gì?

Hàm SUM là một hàm được dùng để tính tổng của một dãy số dữ liệu trong Google Sheet.

Cách sử dụng hàm SUM

- Công thức hàm SUM:

=SUM(number1, number2,…)

- Trong đó:

number1 và number2 có nghĩa là những số hoặc dãy số sẽ được tính tổng.

- Ví dụ minh họa: Cho bảng dữ liệu như dưới hình, gồm có 5 trường: MS, Tên sản Phẩm, Giá Sản phẩm, Số lượng SP đã bán, Tổng doanh thu trên một loại sản phẩm.

Cho bảng dữ liệu mẫu

Hãy tính tổng doanh thu tất cả sản phẩm bằng hàm SUM.

=SUM(E2:E9)

Ví dụ minh họa cho hàm SUM như hình trên

Hướng dẫn cách sử dụng hàm OFFSET trong Google Sheet đơn giản

Hàm OFFSET là gì?

Hàm OFFSET là một hàm sẽ trả về giá trị tham chiếu của một dải ô sau khi bạn dịch chuyển khỏi một số lượng hàng và cột ban đầu từ một điểm tham chiếu khởi đầu của ô.

Cách sử dụng hàm OFFSET

- Công thức:

OFFSET(tham_chiếu_ô; hàng_bù_trừ; cột_bù_trừ; chiều_cao;chiều_rộng).

Trong đó:

+ tham_chiếu_ô: chính là điểm xuất phát để đếm số hàng_bù_trừ và cột_bù_trừ.

+ hàng_bù_trừ: là số hàng cần phải thay đổi tính từ vị trí 0 là tham_chiếu_ô. hàng_bù_trừ phải là số nguyên nhưng có thể mang giá trị âm.

+ cột_bù_trừ: là số lượng cột cần phải thay đổi tính từ vị trí 0 là tham_chiếu_ô. cột_bù_trừ phải là số nguyên và có thể mang giá trị âm.

+ chiều_cao: chính là chiều cao của dải ô để trả về, và bắt đầu tại đích bù trừ. Nếu bạn không ghi chiều_cao, thì mặc định nó sẽ là 1.

+ chiều_rộng: chính là chiều rộng của dải ô để trả về, bắt đầu tại đích bù trừ. Nếu không ghi chiều_rộng, thì mặc định nó sẽ là 1.

Ví dụ minh họa: Lấy bảng dữ liệu cũ ở trên, lấy dữ liệu Tên sản phẩm, Giá Sản phẩm và Số lượng SP đã bán ra một bảng riêng.

=OFFSET(B1;1;0;8;3)

Ví dụ mẫu minh họa hàm OFFSET

Hướng dẫn cách kết hợp hàm SUM và hàm OFFSET trong Google Sheet đơn giản

Công thức:

=SUM(OFFSET(tham_chiếu_ô; hàng_bù_trừ; cột_bù_trừ; chiều_cao;chiều_rộng))

Trong đó:

+ tham_chiếu_ô: Điểm xuất phát để đếm số hàng_bù_trừ và cột_bù_trừ.

+ hàng_bù_trừ: chính là số hàng cần phải thay đổi tính từ vị trí 0 là tham_chiếu_ô. hàng_bù_trừ phải là số nguyên nhưng có thể mang giá trị âm.

+ cột_bù_trừ: chính là số lượng cột cần phải thay đổi tính từ vị trí 0 là tham_chiếu_ô. cột_bù_trừ phải là số nguyên, và có thể mang giá trị âm.

+ chiều_cao: chính là chiều cao của dải ô để trả về, và bắt đầu tại đích bù trừ.

+ chiều_rộng: chính là chiều rộng của dải ô để trả về, và bắt đầu tại đích bù trừ.

Ví dụ minh họa: Lấy bảng dữ liệu cũ ở trên, hãy tính tổng doanh thu của từ SP1 đến SP5.

Công thức:

=SUM(OFFSET(B1;1;3;5;1))

Giải thích: sử dụng hàm OFFSET để lấy cột dữ liệu tổng doanh thu từ SP1 đến SP5. Và sau đó, hàm SUM sẽ tính tổng dữ liệu mà hàm OFFSET trả về, kết quả trả về sau khi tính là: 654000.

Tính tổng doanh thu từ SP1 đến SP5 như hình trên

Các lỗi thường gặp khi bạn kết hợp hàm SUM và hàm OFFSET

Lỗi #N/A

Đây chính là lỗi do đối số bạn đưa vào không phù hợp với dữ liệu tính của hàm. Để sửa lỗi, bạn cần nhập lại dữ liệu đúng đưa vào hàm để tính.

Lỗi #REF

Lối #REF xảy ra là do hàm dữ liệu nằm ngoài vùng giới hạn của dữ liệu đưa ra. Bạn cần phải kiểm tra lại vùng dữ liệu sử dụng của mình đã nằm trong giới hạn hay chưa để sửa lại lỗi này nhé!

Lỗi #ERROR

Lỗi xảy ra là do cú pháp mà bạn ghi chưa đúng với cú pháp của hàm, để sửa lại bạn cần kiểm tra và sửa lại cú pháp công thức đã ghi phải khớp với công thức quy định của hàm

Một số lưu ý cần thiết khi kết hợp hàm SUM và hàm OFFSET

- Hàm SUM và hàm OFFSET sẽ không phân biệt chữ hoa hay chữ thường trong lúc nhập công thức, vì vậy mà bạn hoàn toàn có thể nhập offset, sum khi bạn gọi hàm.

- chiều_cao và chiều_rộng trong công thức hàm OFFSET không được là số âm.

Một số bài tập ví dụ cơ bản về kết hợp hàm SUM và hàm OFFSET

Câu 1: Hãy tính tổng doanh thu của tất cả sản phẩm bằng cách kết hợp hàm SUM và hàm OFFSET.

Công thức:

=SUM(OFFSET(E1;1;0;12;1))

Giải thích:

- OFFSET(E1;1;0;12;1): sẽ trả về dữ liệu tổng doanh thu thu được của SP1 đến SP12.

- Hàm SUM: sẽ tính tổng dựa trên dữ liệu đã trả về của hàm OFFSET.

Tính tổng doanh thu bằng cách kết hợp hàm SUM và OFFSET như hình trên

Câu 2: Hãy tính tổng số lượng hàng hóa, sản phẩm đã bán.

Công thức:

=SUM(OFFSET(D1;1;0;12;1))

Giải thích:

- OFFSET(D1;1;0;12;1): sẽ trả về dữ liệu số lượng SP đã bán của SP1 đến SP12.

- Hàm SUM: sẽ tính tổng dựa trên dữ liệu đã trả về của hàm OFFSET.

Tính tổng số lượng hàng hóa, sản phẩm đã bán như hình trên

Câu 3: Hãy tính tổng doanh thu thu từ SP3 đến SP10.

Công thức:

=SUM(OFFSET(A4;0;4;8;1))

Giải thích:

- OFFSET(A4;0;4;8;1): sẽ trả về dữ liệu tổng doanh thu thu được của SP3 đến SP10.

- Hàm SUM: sẽ tính tổng dựa trên dữ liệu đã trả về của hàm OFFSET.

Tính tổng doanh thu từ SP3 đến SP10 như hình trên

Những câu hỏi thường gặp khi bạn kết hợp hàm SUM và hàm OFFSET

Kết hợp hàm SUM với hàm OFFSET sẽ. hoạt động như thế nào?

- Trả lời: Công thức này sẽ tính giá trị trả về bên trong của hàm OFFSET trước. Sau đó, hàm SUM sẽ lấy giá trị của hàm OFFSET đã trả về để tính tổng.

Có thể kết hợp hàm SUM với nhiều hàm OFFSET hay không?

- Trả lời: hoàn toàn có thể kết hợp, ví dụ cùng bảng dữ liệu đã mà ta sử dụng trong mục 1, hãy tính tổng doanh thu SP1 đến SP3, SP7 đến SP8.

Công thức: =SUM(OFFSET(A2;0;4;3;1); OFFSET(A8;0;4;2;1))

Kết quả tính được là: 1910000.

Có thể kết hợp hàm SUM với nhiều hàm OFFSET như hình trên

Trên đây là phần hướng dẫn sử dụng hàm SUM kết hợp với hàm OFFSET chi tiết và đơn giản nhất, hy vọng bài viết sẽ hữu ích với các bạn, giúp các bạn sử dụng phần mềm một cách thông minh và hiệu quả hơn, cảm ơn bạn đã theo dõi!