Một số công thức tính tổng "độc", "lạ" trong excel

Ketoan.biz 19.6.17   Excel-Kế toán   Ứng dụng Excel-Kế toán

Ketoan.biz

>> Ứng dụng Excel trong kế toán với các hàm tính tổng

Excel là một công cụ tính toán quen thuộc nhưng khá thú vị cho dân văn phòng, kế toán.

1. Cộng trong một khoản cho trước

Trong công việc đôi khi chúng ta cần tính tổng với một khoản nào đó, chẳng hạn tính tổng các giá trị nhóm lương nhân viên 5.000.000 đến 20.000.000 đồng, hay tính giá trị doanh số của nhóm có doanh thu trong tháng từ 120.000.000 đến 160.000.000 đồng ...

Trong bảng lương sau, chúng ta sẽ tính tổng lương của những người làm việc ở bộ phận quản lý có mức lương từ 10.000.000 đồng đến 20.000.000 đồng, tại ô F21 chúng ta gỏ vào công thức sau:

=SUM(IF(F8:F14>=10000000,IF(F8:F14<=200000000,F8:F14,0),0))

Xong, nhấn tổ hợp phím Ctr + Shift + Enter để xem kết quả. Kết quả chính xác là 37.905.385 như hình trên.

Lưu ý, đây là công thức mảng { } nên các bạn phải nhấn Ctr + Shift + Enter khi kết thúc lệnh, không được nhấn enter.

2. Cộng số có chứa chữ (text)

Thoạt nghe có vẻ không hợp lý lắm bởi số viết kèm với chữ (thường là ký tự) được excel hiểu đó là dạng text nên không thể thực hiện các phép tính thông thường lên chúng được. Để xử lý số dạng này, thường chúng ta hay dùng các công cụ, hàm để tách số, chữ riêng- nghe như có vẻ khá phức tạp nhỉ?

Liệu có cách nào cộng trực tiếp những số có chứa chữ giống nhau được hay không? Câu trả lời là được, hoàn toàn có thể cộng chúng lại với nhau và một lần nữa, công thức mảng lại được phát huy tác dụng.

Tại ô M6 (hình trên) tính tổng tăng ca (C) gỏ vào công thức mảng sau:

=SUM(1*IF(RIGHT($B6:$J6)="C",LEFT($B6:$J6,LEN($B6:$J6)-1),0))

* Lưu ý: $B6:$J6 là vùng chọn, "C" là ký tự cần tính tổng. Nhấn tổ hợp phím Ctr + Shift + Enter để xem kết quả.

Tương tự, tính tổng tăng ca (T), tại ô L7 gỏ vào công thức sau:

=SUM(1*IF(RIGHT($B6:$J6)="T",LEFT($B6:$J6,LEN($B6:$J6)-1),0))

Tức thay "C" bằng "T" và đừng quên nhấn tổ hợp phím Ctr + Shift + Enter để xem kết quả.

Còn tính tổng công thường (không chứa text) thì chúng ta dùng hàm sum để tính như bình thường, xong copy các công thức xuống các ô bên dưới.

3. Tính tổng theo tháng

Chẳng hạn chúng ta có bảng báo cáo bán hàng sau, yêu cầu tính tổng giá trị hàng bán ra trong từng tháng.

Có nhiều cách để giải quyết tình huống này, ở đây chúng tôi muốn giới thiệu tính tổng theo tháng theo công thức sau khá nhanh chóng:

Tại ô cần tính tổng theo tháng, gỏ vào công thức:

=SUMPRODUCT((MONTH($A$4:$A$23)=1)*($F$4:$F$23))

Trong đó MONTH($A$4:$A$23) là vùng chứa các tháng cần xử lý; $F$4:$F$23 là vùng cần tính tổng và "1" là tháng cần tính tổng.

Nếu muốn tính tổng giá trị bán hàng của tháng 2, chỉ việc thay số "1" thành số "2" cho công thức trên. Lưu ý, công thức trên chỉ đúng khi dữ liệu cần tính trong một năm.

4. Tính tổng đến ngày hiện tại

Nếu bạn cần tính tổng đến ngày hiện tại, hãy sử dụng công thức sau:

Tại ô cần tính toán, bạn gỏ vào công thức:

=SUMIF(A1:A9,"<="&TODAY(),E1:E9)

Trong đó: A1:A9 vùng chứa tháng cần tính, E1:E9 vùng cần tính tổng.

Do ngày hiện tại khi tính toán là ngày 6/19/2017 nên công thức trên chỉ tính tổng từ E5:E7 (tức cộng từ ngày 1/1/2017-5/26/2017) và kết quả là 24.000.

Công thức này rất hữu dụng khi bạn có một bảng kế hoạch trong tháng/ năm mà hàng hằng ngày khi có kết quả thực tế, bạn edit dữ liệu kế hoạch bằng dữ liệu thực tế và bạn muốn cộng số thực tế.

Vâng, công thức thức trong excel khá đa dạng và thú vị, ketoan.biz sẽ tiếp tục cập nhật thêm một số công thức hay khác áp dụng trong một số tình huống cụ thể trong thời gian tới.

Ketoan.biz tổng hợp

Bài viết liên quan