Tải xuống sổ làm việc mẫu
Hướng dẫn này sẽ trình bày cách sử dụng Hàm SUMIFS và các ký tự đại diện để tính tổng dữ liệu tương ứng với các ô chứa văn bản cụ thể trong Excel và Google Trang tính.
Tính tổng nếu chứa văn bản
Đầu tiên, chúng tôi sẽ trình bày cách tính tổng dữ liệu liên quan đến các ô chứa văn bản cụ thể bằng cách sử dụng Hàm SUMIFS.
Hàm SUMIFS tính tổng các hàng dữ liệu đáp ứng các tiêu chí nhất định. Cú pháp của nó là:
Ví dụ này sẽ tổng hợp tất cả Điểm với một Tên nhà nước chứa “Dakota” bằng cách sử dụng Hàm SUMIFS và ký tự đại diện *.
| 1 | = SUMIFS (C3: C9, B3: B9, "* Dakota *") |
Ký tự * cho phép bất kỳ số nào (kể cả số 0) của các ký tự khác thay thế cho nó.
Trong ví dụ này, nó được sử dụng để tìm tất cả các ô có văn bản “Dakota”. Tìm kiếm này không phân biệt chữ hoa chữ thường, vì vậy “dakota” được coi giống như “Dakota” hoặc “DAKOTA”. Bắc Dakota và Nam Dakota chứa "Dakota" và do đó được bao gồm trong tổng.
Tính tổng nếu văn bản bắt đầu bằng
Ký tự * cũng có thể được sử dụng để tìm kiếm các ô bắt đầu bằng văn bản được chỉ định:
| 1 | = SUMIFS (C3: C9, B3: B9, "Mới *") |
Newyork, Áo mới, và New Mexico bắt đầu bằng "Mới" và do đó được bao gồm trong tổng. Lưu ý rằng, với cụm từ tìm kiếm “Mới *”, ô văn bản phải bắt đầu bằng “Mới”; chỉ chứa những ký tự đó là không đủ.
Tính tổng nếu văn bản kết thúc bằng
Tương tự, chúng ta có thể tổng hợp tất cả Điểm vì Những trạng thái kết thúc bằng “o” sử dụng:
| 1 | = SUMIFS (C3: C9, B3: B9, "* o") |
New Mexico và Ohio kết thúc bằng "o" và do đó được bao gồm trong tổng.
Sử dụng ? Ký tự đại diện
Các ? ký tự có thể được sử dụng để đại diện cho bất kỳ ký tự đơn nào trong chuỗi văn bản.
Ví dụ này tìm thấy tất cả Tên tiểu bang bắt đầu bằng "Mới", theo sau là chính xác 7 ký tự (bao gồm cả dấu cách).
| 1 | = SUMIFS (C3: C9, B3: B9, "Mới ???????") |
Áo mới và New Mexico đáp ứng các tiêu chí này, nhưng Newyork không, vì chỉ có 5 ký tự theo sau "Mới" trong Newyork.
Lưu ý rằng * và? các ký tự đại diện có thể được kết hợp nếu được yêu cầu để thực hiện các lệnh tìm kiếm rất cụ thể. Ví dụ tiếp theo này tìm thấy Tên tiểu bang bắt đầu bằng “N” và chứa “o” trước ký tự cuối cùng của chuỗi. Điều này không bao gồm New Mexico; nó bắt đầu bằng “N” nhưng không có “o” trước ký tự cuối cùng.
| 1 | = SUMIFS (C3: C9, B3: B9, "N * o? *") |
Chức năng này đặc biệt hữu ích khi tìm kiếm thông qua mã sản phẩm, mã ZIP hoặc số sê-ri trong đó vị trí của mỗi ký tự có ý nghĩa cụ thể.
Sử dụng ký tự ~ (Dấu ngã)
Ký tự đặc biệt ~ (được gọi là dấu ngã) cho phép chúng tôi xử lý dấu * hoặc? các ký tự như thể chúng là các giá trị văn bản đơn giản và không hoạt động như các ký tự đại diện.
Trong ví dụ dưới đây, chúng ta cần tính tổng Mức cổ phiếu khi mà tên sản phẩm đối sánh cụ thể với văn bản “Sản phẩm?”:
| 1 | = SUMIFS (C3: C8, B3: B8, "Sản phẩm ~?") |
Dấu ~ ngay trước dấu * hay dấu? ký tự biến nó trở lại thành giá trị văn bản, vì vậy cụm từ tìm kiếm “Sản phẩm ~?” được sử dụng để tìm đối sánh văn bản chính xác với “Sản phẩm?”.
Kết hợp Ký tự đại diện SUMIFS với Tham chiếu ô
Thông thường, việc viết mã các giá trị vào công thức là không tốt. Thay vào đó, sẽ linh hoạt hơn khi sử dụng các ô riêng biệt để chỉ định giá trị cho các cụm từ tìm kiếm của chúng tôi.
Để tìm kiếm xem các ô có chứa văn bản được giữ trong ô E3 hay không, chúng ta có thể sử dụng Hàm SUMIFS với tham chiếu ô và * ký tự đại diện:
| 1 | = SUMIFS (C3: C9, B3: B9, "*" & E3 & "*") |
Lưu ý rằng văn bản “Dakota” đã được thay thế bằng tham chiếu ô & E3 & và các ký tự * đã được đặt trong dấu ngoặc kép (”“).
Nhiều tham chiếu ô và các ký tự đại diện cũng có thể được kết hợp với nhau. Để tìm Tên tiểu bang bắt đầu bằng văn bản trong ô E3 và chứa văn bản trong ô F3 theo sau là ít nhất 1 ký tự nữa, có thể sử dụng công thức sau:
| 1 | = SUMIFS (C3: C9, B3: B9, E3 & "*" & F3 & "? *") |
Khóa tham chiếu ô
Để làm cho các công thức của chúng tôi dễ đọc hơn, chúng tôi đã hiển thị các công thức mà không có tham chiếu ô bị khóa:
| 1 | = SUMIFS (C3: C9, B3: B9, "*" & E3 & "*") |
Nhưng những công thức này sẽ không hoạt động bình thường khi sao chép và dán ở nơi khác trong tệp của bạn. Thay vào đó, bạn nên sử dụng các tham chiếu ô bị khóa như sau:
| 1 | = SUMIFS ($ C $ 3: $ C $ 9, $ B $ 3: $ B $ 9, "*" & E3 & "*") |
Đọc bài viết của chúng tôi về Khóa tham chiếu ô để tìm hiểu thêm.
Tính tổng nếu Ô chứa Văn bản Cụ thể Sử dụng Ký tự Đại diện trong Google Trang tính
Các công thức này hoạt động giống hệt nhau trong Google Trang tính cũng như trong Excel.
