Thủ Thuật

Hàm SUMIF trong Excel và cách khắc phục một số lỗi thường gặp khi sử dụng

Hàm SUMIF là một hàm có ích giúp tất cả chúng ta tính tổng những giá trị dựa trên một số ít điều kiện kèm theo nhất định. Nhưng có những lúc bạn sẽ gặp khó khăn vất vả khi thao tác với hàm này. Bạn sẽ nhận thấy rằng hàm SUMIF không hoạt động giải trí thông thường hoặc trả về tác dụng không đúng chuẩn .Những sai sót này hầu hết xảy ra khi bạn mới sử dụng hàm hàm SUMIF và chưa quen thuộc với nó. Thể nhưng, nhiều người dùng Excel lâu năm cũng gặp những lỗi tương tự như. Có nhiều nguyên do khiếm hàm SUMIF không hoạt động giải trí đúng mực. Trong bài viết này, tất cả chúng ta sẽ luận bàn về những lỗi thường gặp khi sử dụng hàm SUMIF và cách khắc phục .

Xem thêm khóa học tin học văn phòng online Tuyệt đỉnh Excel – Trở thành bậc thầy Excel trong 16 giờ để ung dụng thành thạo 150 hàm Excel, Pivot Table, kỹ thuật lọc, định dạng dữ liệu, vẽ biểu đồ chuyên nghiệp…vào công việc

Trước khi tất cả chúng ta luận bàn chi tiết cụ thể về những điểm yếu tố với hàm SUMIF, hãy kiểm tra công thức SUMIF của bạn, xem hoàn toàn có thể khắc phục lỗi nhanh gọn hay không .

các lỗi thường gặp khi sử dụng hàm SUMIF và cách khắc phục.

  • Nếu SUMIF trả về lỗi # N / A hoặc bất kỳ lỗi nào khác, hãy đánh giá công thức. Có 80% cơ hội là bạn sẽ làm cho công thức của mình hoạt động. Để đánh giá công thức, hãy chọn ô công thức và chuyển đến tab Formula  trong ribbon. Tại đây tìm tùy chọn Evaluate Formula, bên dưới phần  Auditing.
  • Nếu bạn đang viết đúng công thức và khi bạn cập nhật trang tính, hàm SUMIF không trả về giá trị đúng. Hãy nhấn phím F9 để tải và tính toán lại trang tính.
  • Kiểm tra định dạng của các giá trị liên quan đến phép tính. Nếu bạn nhập dữ liệu từ nguồn khác, có rất có thể có định dạng không đúng trong bảng tính.

Nếu sau ba bước kiểm tra trên, hàm SUMIF của bạn vẫn không hoạt động giải trí. Hãy thử những giải pháp bên dưới .

SUMIF không hoạt động do lỗi cú pháp

Lỗi cú pháp là lỗi thường gặp ở những người dùng mới làm quen với Excel. Nhưng, ngay cả những người dùng có kinh nghiệm tay nghề cũng hoàn toàn có thể mắc lỗi cú pháp khi sử dụng hàm SUMIF. Vì vậy, thứ nhất tất cả chúng ta hãy xem cú pháp của hàm SUMIF .

Công thức SUMIF chung trong Excel:

= SUMIF ( condition_range, condition, sum range )Ngoài ra, bạn hoàn toàn có thể xem thêm những bài viết này để hiểu rõ hơn về hàm SUMIFNhư bạn hoàn toàn có thể thấy, đối số tiên phong là một khoanh vùng phạm vi chứa điều kiện kèm theo của bạn. Điều kiện sẽ chỉ được kiểm tra trong khoanh vùng phạm vi này. Đối số thứ hai là chính điều kiện kèm theo. Đây là điều kiện kèm theo mà bạn muốn kiểm tra trong condition_range. Đối số thứ 3 sum_range là khoanh vùng phạm vi trong thực tiễn bạn muốn triển khai những phép cộng. Nếu đối số sum_range bị bỏ lỡ, Excel cộng những ô được xác lập trong đối số range ( chính những ô đã được vận dụng tiêu chuẩn ) .Bây giờ tất cả chúng ta hãy xem những lỗi cú pháp bạn hoàn toàn có thể mắc phải khi sử dụng hàm SUMIF .

Sai định dạng ngày tháng trong công thức

Hầu hết những lỗi gặp phải khi sử dụng Sumif đều do tất cả chúng ta xác lập tiêu chuẩn sai. Các tiêu chuẩn trong SUMIF được xác lập khác nhau trong những trường hợp khác nhau. Để hiểu rõ hơn, tất cả chúng ta hãy xem qua 1 số ít ví dụ .

Ở đây tôi có một tập dữ liệu.

SUMIF không hoạt động do lỗi cú pháp

Giả sử tôi cần tính tổng số lượng của ngày 1 – Mar-13 với công thức này := SUMIF ( A2 : A20, 1 – mar-13, C2 : C20 )Công thức SUMIF sẽ không hoạt động giải trí trong trường hợp này. Nó sẽ trả về 0. Tại sao lại như vậy ?

  • Dữ liệu chúng ta đang làm việc được định dạng là ngày tháng. Và ngày tháng trong Excel thực sự là một con số. Các số thực có thể được viết mà không có dấu ngoặc kép làm tiêu chí. Nhưng excel vẫn không trả về câu trả lời chính xác.
  • Trên thực tế, SUMIF trong excel, chấp nhận ngày dưới dạng văn bản trong tiêu chí (nếu không được định dạng là số sê-ri). Vì vậy, nếu bạn viết một trong 3 công thức bên dưới,  (số tương đương của 1-mar-13 của 41334) nó sẽ trả về câu trả lời chính xác.

= SUMIF ( A2 : A20, ” 1 – mar-13 “, C2 : C20 ) hoặc= SUMIF ( A2 : A20, ” 1 – mar-2013 “, C2 : C20 )= SUMIF ( A2 : A20, 41334, C2 : C20 )

SUMIF không hoạt động do lỗi cú pháp

Bạn cần đặc biệt quan trọng cẩn trọng khi thao tác với ngày tháng trong Excel. Chúng rất dễ lộn xộn. Vì vậy, nếu công thức của bạn gồm có ngày tháng trong đó, hãy kiểm tra xem nó có đúng định dạng không. Đôi khi khi tất cả chúng ta nhập tài liệu từ những nguồn khác, ngày tháng không được nhập ở những định dạng đúng. Vì vậy, tiên phong bạn hãy kiểm tra và sửa định dạng ngày trước khi sử dụng .

Sử dụng toán tử so sánh sai trong hàm SUMIF

Hãy lấy một ví dụ khác. Lần này, chúng ta hãy tổng hợp số lượng các ngày muộn hơn 1-tháng 3-13. Đối với điều này, cú pháp chính xác là :

= SUMIF ( A2 : A20, ” > 1 – Mar-13 “, C2 : C20 )Dấu > phải nằm trong ngoặc kép .Bây giờ nếu tiêu chuẩn nằm trong 1 số ít ô, giả sử trong F3, bạn sẽ sử dụng hàm SUMIF như sau :

= SUMIF (A2: A20, “>” & F3, C2: C20)

Hàm SUMIF trong Excel và cách khắc phục một số lỗi thường gặp khi sử dụng 4

Khi bạn cần tính tổng những giá trị nếu một giá trị khớp đúng chuẩn trong khoanh vùng phạm vi tiêu chuẩn, bạn không cần sử dụng dấu ” = “. Bạn chỉ cần viết giá trị đó hoặc phân phối tham chiếu của giá trị đó tại vị trí của tiêu chuẩn, như tất cả chúng ta đã làm trong ví dụ tiên phong .

Tôi nhận thấy rằng một số người dùng mới sử dụng cú pháp dưới đây khi họ muốn có kết quả khớp chính xác.

= SUMIF (A2: A20, A2: A20 = F3, C2: C20)

Công thức SUMIF này sẽ không hoạt động giải trí. Khi sử dụng tham chiếu làm tiêu chuẩn cho những đối sánh tương quan đúng chuẩn, bạn chỉ cần đề cập đến tham chiếu. Công thức dưới đây sẽ tính tổng toàn bộ số lượng ngày được viết trong ô F3 :

= SUMIF (A2: A20, F3, C2: C20)

Vậy là bạn đã biết cách sử dụng toán tử so sánh trong hàm Sumif. Chúng ta hãy xem thêm một vài lỗi thường gặp khác bên dưới .

SUMIF không hoạt động do định dạng dữ liệu bất thường

Khi tất cả chúng ta nhập tài liệu từ những nguồn khác, thường có những định dạng tài liệu không bình thường. Rất hoàn toàn có thể có những số được định dạng dưới dạng văn bản. Trong trường hợp đó, những số lượng sẽ không được tính tổng. Xem ví dụ dưới đây .

Hàm SUMIF trong Excel và cách khắc phục một số lỗi thường gặp khi sử dụng 6

Công thức Sumif khi dữ liệu có định dạng văn bản

Bạn có thể thấy rằng phạm vi tổng chứa các giá trị văn bản thay vì số. Và vì các giá trị văn bản không thể được tính tổng, kết quả chúng ta nhận được là 0. 

Có thể phạm vi của bạn chứa các định dạng hỗn hợp. Có thể chỉ có một số số được định dạng dưới dạng văn bản và số còn lại là số. Trong trường hợp đó, các số được định dạng văn bản đó sẽ không được tổng hợp và bạn sẽ nhận được kết quả không chính xác.

Để khắc phục sự cố này, hãy chọn một ô có chứa số và văn bản và CTRL + SPACE để chọn toàn bộ cột. Bây giờ hãy nhấp vào dấu chấm than nhỏ ở bên trái của ô. Ở đây bạn sẽ thấy một tùy chọn “Convert to numbers” ở vị trí thứ hai. Nhấp vào nó và bạn sẽ có tất cả các số trong phạm vi đã chọn được chuyển đổi sang định dạng số.

chuyển văn bản thành số

Nhưng nếu bạn không hề quy đổi định dạng từ văn bản sang số theo cách này. Hãy sử dụng hàm VALUE để chuyển văn bản thành số. Hàm VALUE hoàn toàn có thể chuyển bất kể chuỗi nào thành số ( gồm có cả ngày và giờ ) .Đầu tiên, bạn cần sử dụng một cột để quy đổi tài liệu của bạn sang định dạng số cách sử dụng hàm VALUE và sau đó dán giá trị đó vào, trước khi sử dụng nó trong công thức .

SUMIF không hoạt động do định dạng dữ liệu bất thường

Công thức Sumif khi tính tổng thời gian

Bạn hoàn toàn có thể sử dụng SUMIF để tính tổng thời hạn không ? Câu vấn đáp là có, nhưng rất nhiều người dùng không hề tính đúng chuẩn tổng thời hạn với Sumif. Hãy quan sát ví dụ bên dưới nhé .

Ở đây tôi có thời gian ở định dạng HH: MM: SS. Và tôi muốn tổng hợp số giờ của ngày 1 tháng 3 năm 13. Vì vậy, tôi sử dụng công thức:

= SUMIF (A2: A20, F3, C2: C20)

Công thức là trọn vẹn đúng mực nhưng câu vấn đáp tôi nhận được có vẻ như không đúng .

Công thức Sumif khi tính tổng thời gian

Tại sao tất cả chúng ta nhận được 0,5. Lẽ ra hiệu quả phải là 12:00:00. Nó không đúng chuẩn ? Không phải, tác dụng như trên là đúng, yếu tố ở đây chỉnh là cách viết mà thôi. Như tất cả chúng ta đã bàn luận trước đó, ngày và giờ trong Excel được giải quyết và xử lý khác nhau .Trong Excel, 1 giờ bằng 1/24 đơn vị chức năng. Vì vậy, 12 giờ sẽ bằng 0,5. Nếu bạn muốn xem tác dụng theo giờ, chỉ cần quy đổi định dạng ô của G3 sang định dạng thời hạn .Nhấp chuột phải vào ô cần định định dạng. Tại đây, bạn chọn định dạng thời hạn. Bạn hoàn toàn có thể thấy rằng tác dụng được quy đổi sang định dạng đúng chuẩn và trả về một tác dụng dễ hiểu .

Công thức Sumif khi tính tổng thời gian

Nếu SUMIF vẫn không hoạt động, hãy sử dụng SUMPRODUCT

Nếu vì bất kể nguyên do gì, hàm SUMIF không hoạt động giải trí, bất kể bạn làm gì, hãy sử dụng một công thức thay thế sửa chữa. Ở đây công thức này sử dụng hàm SUMPRODUCT .Ví dụ, nếu bạn muốn làm tính tổng như những ví dụ trên, tất cả chúng ta hoàn toàn có thể sử dụng hàm SUMPRODUCT để thay thế sửa chữa. Để tính tổng khoanh vùng phạm vi D2 : D20 nếu ngày bằng F3, hãy viết công thức này .

= SUMPRODUCT (D2: D20, – (A2: A20 = F3))

Thứ tự của những biến không quan trọng. Công thức dưới đây sẽ hoạt động giải trí trọn vẹn tốt :

= SUMPRODUCT (- (A2: A20 = F3), D2: D20)

hoặc cái này,

= SUMPRODUCT (- (F3 = A2: A20), D2: D20)

Hàm SUMPRODUCT rất linh động và đa năng công dụng, hoàn toàn có thể giúp bạn thuận tiện tính tổng theo điều kiện kèm theo .Hy vọng bài viết này đã giúp bạn xử lý yếu tố hàm SUMIF không hoạt động giải trí và những lựa chọn sửa chữa thay thế. Bên cạnh đó, để không bỏ lỡ những mẹo và thủ pháp tin học văn phòng có ích khác, hãy theo dõi Gitiho ngay ngày hôm nay .

Những bài viết liên quan

Nút quay lại đầu trang