Cách sử dụng hàm SUMIF với Ký tự đại diện Wildcard Characters trong Excel

 
 

SUMIF là một trong những hàm Excel yêu thích của tôi và tôi chắc rằng đó cũng là hàm của bạn. Nhưng có một điều mà tôi luôn lo lắng là chúng ta chỉ có thể tính tổng các giá trị khi các tiêu chí hoàn toàn khớp với các giá trị.

Giả sử tên bạn là John Martin.

Trong trường hợp này, nếu chúng tôi muốn tính tổng các giá trị bằng tên của bạn, chúng tôi nên có tên đầy đủ của bạn. Và, nếu chúng tôi chỉ có tên của bạn thì chúng tôi không thể sử dụng tên đó.

Vâng, đó là một vấn đề. Nhưng, tôi cũng có một giải pháp cho việc này. Bạn có nhớ các ký tự đại diện của Excel không? Vâng, bạn hiểu đúng.

Nếu chúng ta sử dụng ký tự đại diện với SUMIF, chúng ta có thể tính tổng các giá trị bằng cách sử dụng tiêu chí từng phần. Tin vui là chúng ta có thể sử dụng cả ba ký tự (Dấu hoa thị [*], Dấu chấm hỏi [?], Dấu ngã [~]) với SUMIF/SUMIFS.

Hôm nay, trong bài đăng này, tôi muốn chia sẻ với bạn một cách đơn giản để sử dụng SUMIF với các ký tự đại diện giải thích cách sử dụng của cả ba ký tự với các ví dụ.

Nếu bạn muốn tìm hiểu về các ký tự đại diện, bạn sẽ tìm hiểu từ hướng dẫn này và đảm bảo tải xuống tệp dữ liệu này từ đây để làm theo.

Và bây giờ, hãy bắt đầu…

1. Dấu hoa thị [*] với SUMIF

Dấu hoa thị là ký tự đại diện phổ biến nhất. Và, đó là cách có thể sử dụng hoàn hảo với SUMIF.

Dấu hoa thị: Giới thiệu nhanh

Bạn có thể sử dụng dấu hoa thị để hiển thị một hoặc nhiều ký tự bị thiếu ở cuối hoặc đầu tiêu chí.

Như tôi đã nói, nếu chúng ta có John làm tiêu chí và chúng ta cần tính tổng các giá trị cho tên John Martin, chúng tôi có thể kết hợp văn bản “John” với dấu hoa thị để hiển thị phần còn lại của văn bản.' với dấu hoa thị để hiển thị phần còn lại của văn bản.

Ví dụ: SUMIF + Asterisk

Sau đây tôi có một ví dụ tốt hơn để bạn hiểu cách hoạt động của dấu hoa thị với SUMIF.

Dưới đây là bảng nơi chúng tôi có một cột chứa tên sản phẩm và số hóa đơn chung hoặc bạn có thể nói đó là số hóa đơn theo sản phẩm.

How to use SUMIF with Wildcard Characters in Excel

Bây giờ từ bảng này, chúng ta cần tạo ra một tổng số sản phẩm khôn ngoan. Nhưng nếu bạn thấy, mỗi tên sản phẩm là duy nhất, bởi vì chúng tôi có một số hóa đơn với nó.

Từ đây, ngay cả khi chúng tôi tạo một bảng tổng hợp, chúng tôi không thể có được tổng số theo sản phẩm. Vì vậy, đã đến lúc sử dụng dấu hoa thị và công thức sẽ là:

=SUMIF(invoice_column,product_name&'*',amount_column)

How to use SUMIF with Wildcard Characters in Excel

Trong công thức trên, chúng ta đã kết hợp tích tên có ký tự đại diện dấu hoa thị giá trị tổng cho mỗi sản phẩm.

Vì vậy, khi SUMIF khớp với tiêu chí từ cột hóa đơn, nó sẽ lấy các ký tự trước dấu hoa thị và thay thế các ký tự còn lại.

< p>Nói một cách đơn giản, nó bỏ qua tất cả các ký tự sau tên sản phẩm từ một ô và trả về tổng từ cột số lượng.

 

Bằng cách này, chúng tôi có thể nhận được tổng số theo sản phẩm ngay cả khi chúng tôi không 'không có tên sản phẩm phù hợp trong cột.

2. Dấu chấm hỏi [?] với SUMIF

Dấu chấm hỏi là ký tự quan trọng tiếp theo sau dấu hoa thị. SUMIF/SUMIFS có thể giúp bạn tạo tiêu chí một phần văn bản.

Dấu hỏi: Giới thiệu nhanh

Bạn có thể sử dụng dấu chấm hỏi để trình bày một ký tự đơn không xác định trong chuỗi văn bản.

Tên tôi là “PUNEET”. Nhưng nếu ai đó viết nó là PUNNET và PUNIIT thì sao? Tôi không thể tính TỔNG bất kỳ giá trị nào liên quan đến hai tiêu chí này hoặc bất kỳ giá trị nào khác.

Nếu tôi sử dụng PUN??T (Hai Dấu hỏi), nó sẽ giúp tôi TỔNG các giá trị từ cả hai tiêu chí.< /p>

Ví dụ: SUMIF + Dấu chấm hỏi

Thông thường, việc sử dụng dấu chấm hỏi không từ đó thường xuyên được so sánh với dấu hoa thị nhưng ngay cả khi đó thì điều quan trọng là phải tìm hiểu và có thể được sử dụng trong một số trường hợp cụ thể.

Hãy xem dữ liệu bên dưới (một lần nữa tôi đang sử dụng tên của mình). Nếu bạn nhìn kỹ, bạn sẽ thấy tên của tôi có trong danh sách.

How to use SUMIF with Wildcard Characters in Excel

Nhưng điểm cần lưu ý là thay vì có khoảng cách giữa họ và tên thì bạn có các ký tự khác nhau.

Và nếu bạn muốn tổng hợp tất cả các khoản tiền thưởng có ở đó, bạn không thể chỉ cần nhắc đến tên của tôi.

Mặc dù tên có trong một số ô nhưng mỗi ô là duy nhất giải quyết vấn đề này và tính tổng tất cả tiền thưởng bạn có thể sử dụng công thức bên dưới.

=SUMIF(tên_column,'Puneet?Gogia',amount_column)

How to use SUMIF with Wildcard Characters in Excel

Bây giờ ở đây bạn đã sử dụng dấu chấm hỏi giữa họ và tên. Vấn đề là khi bạn sử dụng một dấu chấm hỏi, nó sẽ chỉ thay thế ký tự đó bằng bất kỳ ký tự nào có sẵn.

Khi SUMIF sử dụng tiêu chí mà bạn đã chỉ định, nó chỉ khớp với họ và tên và bỏ qua các ký tự trong các giá trị phù hợp cho cùng một vị trí mà bạn đã sử dụng dấu chấm hỏi.

Vậy mấu chốt là: Dấu chấm hỏi đại diện cho một ký tự đơn trong chuỗi văn bản.

3. Dấu ngã [~] với SUMIFS

Tôi không chắc liệu bạn có bao giờ yêu cầu sử dụng dấu ngã làm ký tự đại diện hay không. Nhưng nếu công việc của bạn liên quan đến việc quản lý dữ liệu bẩn nhất thì dấu ngã có thể hữu ích cho bạn vào một ngày nào đó.

Dấu ngã: Giới thiệu nhanh

Nói một cách đơn giản, dấu ngã sẽ vô hiệu tác dụng của các ký tự đại diện (Dấu hoa thị và Dấu hỏi).

Ví dụ: nếu bạn đang cố gắng tính tổng các giá trị bằng văn bản trong đó bạn có dấu hoa thị hoặc dấu chấm hỏi là ký tự thực, SUMIF sẽ coi chúng là ký tự đại diện thay vì ký tự thực.

Ví dụ: SUMIF + Tilde

Ở đây chúng tôi đang sử dụng cùng một dữ liệu như trong ví dụ trên và trong dữ liệu này một lần nữa bạn có tên của tôi. Nhưng tên lần này đã có một ký tự đại diện.

Từ dữ liệu này, bạn cần tính tổng các giá trị tiền thưởng với tên mà bạn có một dấu chấm hỏi. Hãy xem ví dụ dưới đây, nơi chúng tôi chỉ đơn giản sử dụng các tiêu chí để tính tổng các giá trị tiền thưởng.

Nhưng: Nếu bạn nhìn vào kết quả của công thức, nó sẽ hiển thị tổng của tất cả các giá trị tiền thưởng (đối với tên và tên đầy đủ).

How to use SUMIF with Wildcard Characters in Excel

SUMIF cân nhắc rằng dấu chấm hỏi trong tiêu chí dưới dạng ký tự đại diện và trả về tổng giá trị tiền thưởng trong đó văn bản trong tiêu chí là “Puneet”. Như tôi đã nói, chúng ta cần sử dụng dấu ngã với dấu hoa thị để lấy tổng các giá trị. Vì vậy, công thức sẽ là:

=SUMIF(name_column,'Puneet*~',amount_column)

How to use SUMIF with Wildcard Characters in Excel

Vì vậy, khi bạn sử dụng dấu ngã bên cạnh dấu hoa thị, SUMIF sẽ coi đó là ký tự thực thay vì ký tự đại diện ký tự.

Đã giải quyết vấn đề. Bạn đã tính đúng tổng các giá trị.

Tệp mẫu

Tải xuống tệp mẫu này từ đây< /span> để tìm hiểu thêm.

Kết luận< /span>

Điều tôi thích khi sử dụng các ký tự đại diện với SUMIF/SUMIFS là nó giúp tiết kiệm thời gian và bạn có thể tính tổng các giá trị mà không thực hiện bất kỳ thay đổi ban đầu nào đối với các giá trị ban đầu.

Chỉ cần hãy nhớ:

  • Nếu bạn có dữ liệu mà bạn không chắc chắn về chuỗi văn bản đầy đủ, bạn có thể sử dụng dấu hoa thị.
  • Và, nếu bạn muốn thay thế một số lượng ký tự cụ thể thì hãy có thể sử dụng dấu chấm hỏi.
  • Nhưng, như tôi đã nói, công việc của bạn bao gồm quản lý hầu hết dữ liệu bẩn nên dấu ngã có thể hữu ích cho bạn.

Tôi hy vọng mẹo này sẽ giúp ích cho bạn trở nên giỏi hơn ở các công thức. Bạn có thể tìm hiểu một số công thức Excel tuyệt vời nhất từ ​​đây.

Bây giờ hãy cho tôi biết một điều.

Bạn đã bao giờ sử dụng SUMIF với Ký tự đại diện chưa?

Chia sẻ quan điểm của bạn với tôi trong phần bình luận, tôi rất muốn nghe ý kiến ​​từ bạn. Và đừng quên chia sẻ mẹo này với bạn bè của bạn.

Share

Sức khỏe