Trong bài viết này, Studyexcel sẽ hướng dẫn bạn cách sử dụng hàm sumif nhiều sheet trong excel. Mời bạn đọc cùng theo dõi!
Hàm Sumifs là gì?
Hàm SUMIFS của Microsoft Excel thêm tất cả các số trong một phạm vi ô, dựa trên một hoặc nhiều tiêu chí.
Hàm SUMIFS là một hàm tích hợp trong Excel được phân loại là Hàm Math / Trig. Nó có thể được sử dụng như một hàm bảng tính (WS) trong Excel. Là một hàm trang tính, hàm SUMIFS có thể được nhập như một phần của công thức trong một ô của trang tính.
Hàm SUMIFS được sử dụng để tính tổng các dãy số theo các điều kiện nhất định.
Cú pháp của hàm SUMIFS
Cú pháp của hàm SUMIFS nâng cao được mô tả như sau:
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
Trong đó:
– Sum_range: các ô để tổng hợp.
– Critera_range1: phạm vi ô mà muốn áp dụng tiêu chí
– Criteria1: được sử dụng để xác định các ô cần thêm. Criteria1 được áp dụng theo Critera_range1.
– Criteria_range2, Criteria2: phạm vi bổ sung cùng các tiêu chí liên quan của chúng.
Ví dụ cơ bản của hàm SUMIF
Ví dụ 1: Tính tổng thành tiền của các hóa chất có số lượng > 200.
Bước 1: Nhập công thức =SUMIF(D2:D10; “>200”; E2:E10) vào ô tham chiếu muốn hiển thị kết quả (H1).
Trong đó:
- D2:D10: Là vùng dữ liệu Số lượng theo yêu cầu của ví dụ.
- “>200”: Là điều kiện theo yêu cầu của ví dụ (có số lượng > 200).
- E2:E10: Là vùng dữ liệu Thành Tiền để lấy kết quả.
Bước 2: Nhấn phím Enter để hiển thị kết quả.
Ví dụ 2: Tính tổng thành tiền của các hóa chất có số lượng nhỏ hơn ô D5.
Bước 1: Nhập công thức =SUMIF(D2:D10; “<> vào ô tham chiếu muốn hiển thị kết quả (H1).
Trong đó:
- D2:D10: Là vùng dữ liệu Số lượng theo yêu cầu của ví dụ.
- “<> Là điều kiện theo yêu cầu của Ví dụ 2.
- E2:E10: Là vùng dữ liệu Thành Tiền để lấy kết quả.
Bước 2: Nhấn phím Enter để hiển thị kết quả.
Ví dụ 3: Tính tổng thành tiền của các hóa chất có đơn vị tính là mL.
Bước 1: Nhập công thức =SUMIF(B2:B10;”mL”;E2:E10) vào ô tham chiếu muốn hiển thị kết quả (H1).
Trong đó:
- B2:B10: Là vùng dữ liệu dữ liệu Đơn vị tính theo yêu cầu của ví dụ.
- “mL”: Là điều kiện theo yêu cầu của ví dụ (có đơn vị tính là mL).
- E2:E10: Là vùng dữ liệu Thành Tiền để lấy kết quả.
Bước 2: Nhấn phím Enter để hiển thị kết quả.
Ví dụ 4: Tính tổng thành tiền của các hóa chất chưa có số lượng.
Bước 1: Nhập công thức =SUMIF(B2:B10;””;E2:E10) vào ô tham chiếu muốn hiển thị kết quả (H1).
Trong đó:
- D2:D10: Là vùng dữ liệu Số lượng theo yêu cầu của ví dụ.
- “”: Là điều kiện theo yêu cầu của ví dụ (chưa có số lượng).
- E2:E10: Là vùng dữ liệu Thành Tiền để lấy kết quả.
Bước 2: Nhấn phím Enter để hiển thị kết quả.
Làm thế nào để kiểm tra liệu dữ liệu được tính toán từ hai sheet đã đúng hay chưa?
Để kiểm tra tính đúng đắn của dữ liệu được tính toán từ hai sheet, chúng ta có thể sử dụng hai hàm SUMIF và SUMIFS trong Excel và làm theo các bước sau:
- Bước 1: Mở trang tính mà ta muốn thực hiện tính toán.
- Bước 2: Click vào ô cần chứa kết quả tính toán.
- Bước 3: Nhập hàm SUMIF hoặc SUMIFS vào ô.
- Bước 4: Nhập các đối số cho hàm SUMIF hoặc SUMIFS.
Đối số đầu tiên trong cả hai hàm đóng vai trò là phạm vi dữ liệu mà ta muốn tính toán.
Đối số thứ hai là điều kiện để tính toán (Ví dụ: Số lớn hơn 500).
Đối số thứ ba là phạm vi dữ liệu mà ta muốn tính toán kết hợp với điều kiện ở đối số thứ hai.
- Bước 5: Nhấn Enter để tính toán và xem kết quả.
Với hai hàm này, ta có thể trích xuất các giá trị phù hợp với điều kiện nhất định từ cả hai sheet để tính toán và đối chiếu kết quả. Nếu kết quả tính toán từ hai sheet giống nhau, dữ liệu được tính toán là đúng.
Những lưu ý khi tính tổng từ nhiểu sheet
Bạn cần lưu ý những điểm sau khi sử dụng trong tính tổng từ nhiều sheet:
- Các trang tính phải có cùng một định dạng.
- Các dữ liệu trong trang tính để tính tổng cần được sắp xếp hợp lý để đảm bảo kết quả vì do công thức sẽ thực hiện tính dữ liệu liên tiếp.
- Nếu bạn muốn sửa tên sheet (trang tính) mà bị dài, bạn hoàn toàn có thể chèn thêm sheet đầu và sheet cuối với tên ngắn gọn (có thể trống) rồi thực hiện hàm SUM để cộng các sheet trong Excel như bình thường.
- Cách làm này có thể gây khó khăn trong việc lập báo cáo. Bạn có thể coi đây là kiến thức căn bản để làm bàn đạp tiến đến các kiến thức lập báo cáo nâng cao trong Excel nha.
Sử dụng Named Range
Bạn cũng có thể sử dụng một Named Range (vùng được đặt tên) trong hàm SUMIF. Named Range là tên mô tả cho một tập hợp các ô hoặc dải ô trong một trang tính. Nếu bạn không chắc chắn về cách thiết lập Named Range trong bảng tính của mình, hãy đọc hướng dẫn của Quantrimang.com về: Cách đặt tên cho ô hoặc vùng dữ liệu Excel.
Ví dụ, nếu bạn đã tạo một Named Range gọi là years trong bảng tính của mình tham chiếu đến các ô A2:A6 trong Sheet1 (Lưu ý rằng Named Range là tham chiếu tuyệt đối đến =Sheet1!$A$2:$A$6 trong hình ảnh bên dưới):
Bài viết có thể sử dụng Named Range này trong ví dụ hiện tại.
Điều này sẽ cho phép thay thế A2:A6 làm tham số đầu tiên với Named Range years, như sau:
=SUMIF(A2:A6, D2, C2:C6) ‘First parameter uses a standard range
Result: 218.6
=SUMIF(years, D2, C2:C6) ‘First parameter uses a named range called years
Result: 218.6
Những câu hỏi thường gặp hoặc “Tại sao công thức SUMIF của tôi không hoạt động?”
Có thể có vài lý do khiến Hàm SUMIF không hoạt động trong tình huống của bạn. Đôi khi công thức của bạn sẽ không trả lại những gì bạn mong đợi chỉ vì kiểu dữ liệu trong một ô hoặc trong một số đối số không phù hợp với hàm SUMIF. Do đó, dưới đây là một danh sách những điều cần kiểm tra:
1. Các tham số của range và sum_range phải là các dải, chứ không phải mảng:
Các tham số đầu tiên (range) và thứ ba (sum_range) trong công thức SUMIF của bạn phải luôn là một tham chiếu dải, như A1: A10. Nếu bạn bất chấp mà bỏ qua điều này, ví dụ là trường hợp tham chiếu một mảng như {1,2,3}, thì Excel với trả lại với một thông báo lỗi.
Công thức đúng: = SUMIF (A1: A3, “flower”, C1: C3)
Công thức sai: = SUMIF ({1,2,3}, “flower”, C1: C3)
2. Làm thế nào để tính tổng giá trị từ các trang tính hoặc bảng tính khác:
Củng giống như hầu hết các hàm Excel khác, SUMIF có thể tham chiếu đến các trang tính và bảng tính khác, miễn là chúng vẫn đang mở.
Ví dụ: công thức sau đây sẽ tính tổng các giá trị trong các ô F2: F9 trong Sheet 1 (trang tính 1) của Book 1 (bảng tính 1) nếu có một ô tương ứng trong cột A và có trang tính đó cũng có chứa “apples”:
= SUMIF ([Book1.xlsx] Sheet1! $A$2:$A$9, "apples", [Book1.xlsx] Sheet1! $F$2:$F$9)
Tuy nhiên, công thức này sẽ không hoạt động ngay khi Book 1 bị đóng lại. Điều này xảy ra vì các dải được tham chiếu bởi các công thức SUMIF trong bảng tính đã đóng, vậy nên nó sẽ không được tham chiếu đến các mảng. Mặt khác, cũng do không có mảng nào được cho phép trong range và đối số sum_range, nên sau đó, công thức SUMIF sẽ trả về một thông báo lỗi #VALUE!.
3. Hãy chắc chắn range và sum_range có cùng kích cỡ:
Như đã lưu ý ở phần đầu của hướng dẫn này, trong các phiên bản hiện đại của Microsoft Excel, các tham số range và sum_range không nhất thiết phải bằng nhau. Từ phiên bản Excel 2000 trở về trước, range và sum_range có kích thước không đồng đều có thể gây ra vấn đề.
Tuy nhiên, ngay cả trong các phiên bản mới nhất của Excel 2010 và 2013, các công thức phức tạp SUMIF mà ở đó, nếu sum_range có ít hàng và/hoặc cột hơn range, thì sẽ không hợp lệ. Đó là lý do tại sao nó được coi là một minh họa tốt để người dùng chú ý rằng phải luôn luôn giữ đối số range và sum_range cùng kích thước và hình dạng.
4. Làm thế nào để công thức SUMIF của bạn hoạt động nhanh hơn:
Nếu bạn đã điền vào bảng tính với các công thức phức tạp SUMIF làm chậm Excel, hãy xem thử bài viết này và tìm hiểu Làm thế nào để chúng hoạt động nhanh hơn. Bài viết đã được viết cách đây khá lâu, vì vậy đừng ngạc nhiên bởi thời gian tính toán.
Các ví dụ về Excel được trình bày trong hướng dẫn này chỉ liên quan đến một số cách sử dụng cơ bản của hàm này. Trong bài viết tiếp theo, chúng tôi sẽ đưa ra các công thức tiên tiến nhằm khai thác sức mạnh thực của SUMIF và SUMIFS và cho phép bạn tính tổng theo nhiều tiêu chí.
Trên đây là những thông tin hướng dẫn cách Hàm Sumif Nhiều Sheet Trong Excel mà Studyexcel tổng hợp được. Hi vọng bài viết này hữu ích với bạn.
