Eszter có một danh sách dài các ô trong cột A chứa một loạt mã đột biến, chẳng hạn như “AKT 142” hoặc “BRAF 1975.” Trong cột B là các giá trị liên quan đến các mã đột biến này. Cô ấy cần một công thức sẽ tính tổng các giá trị trong cột B mà mã đột biến tương ứng trong cột A bắt đầu với cùng một trình tự, như trong tất cả các mã bắt đầu bằng AKT hoặc BRAF. Eszter nghi ngờ điều này có thể được thực hiện với hàm SUMIF, nhưng cô ấy không biết làm thế nào để khiến nó chỉ chú ý đến phần đầu tiên của mã đột biến.

Có nhiều cách bạn có thể tiếp cận vấn đề này, nhưng trong mẹo này, tôi chỉ tập trung vào ba giải pháp tiềm năng.

Sử dụng cột trợ giúp

Nếu bố cục trang tính của bạn cho phép, bạn có thể thêm cột trợ giúp chỉ chứa phần đầu tiên của mã đột biến. Vì mã đột biến của bạn nằm trong cột A, bạn có thể chèn công thức sau vào ô đầu tiên của cột trợ giúp:

=LEFT(A1, SEARCH(" ",A1,1)-1)

Sao chép nó xuống cho nhiều ô nếu cần và bạn sẽ thấy cột trợ giúp chứa mọi thứ trong các mã đột biến trước khoảng trắng. Sau đó, bạn có thể sử dụng công thức SUMIF mong muốn của mình để tính tổng dựa trên nội dung của cột trợ giúp.

Sử dụng SUMPRODUCT

Một cách tiếp cận khá độc đáo để giải quyết vấn đề là sử dụng hàm SUMPRODUCT. Giả sử rằng bạn đặt, trong ô E1, mã mở đầu mà bạn quan tâm. (Ví dụ: bạn có thể đặt “AKT” vào ô E1.) Sau đó, bạn có thể tính tổng mong muốn bằng cách sử dụng công thức sau:

=SUMPRODUCT(--(LEFT(A:A,LEN($E$1))=$E$1) * B:B)

Điều này hoạt động vì SUMPRODUCT kiểm tra xem phần ngoài cùng bên trái của ô trong cột A có khớp với bất kỳ phần nào bạn đặt trong ô E1 hay không. Nếu đúng, thì phép so sánh trả về 1; nếu không, nó trả về 0. Sau đó, giá trị này được nhân với ô tương ứng trong cột B và tính tổng.

Sử dụng SUMIF Trực tiếp

Có lẽ cách tiếp cận rõ ràng nhất là sử dụng trực tiếp SUMIF. Bạn biết đấy, từ việc sử dụng phương pháp cột trợ giúp, bạn có thể sử dụng SUMIF để xem nội dung của một ô và sau đó tính tổng một cách chọn lọc cột khác. Bạn làm theo cách chung này:

=SUMIF(Check_Range, Criterion, Sum_Range)

Vì vậy, nếu bạn muốn tính tổng các giá trị trong cột B dựa trên những gì trong cột A, bạn có thể làm như sau:

=SUMIF(A:A, "AKT", B:B)

Tất nhiên, điều này sẽ chỉ khớp với những ô trong cột A chỉ chứa AKT. Tuy nhiên, đây không phải là tình huống của Eszter — các mã đột biến trong cột A không chỉ chứa AKT. Đây là lúc việc sử dụng các ký tự đại diện trong đặc tả tiêu chí phát huy tác dụng. Tất cả những gì Eszter cần làm là thêm dấu hoa thị, theo cách này:

=SUMIF(A:A, "AKT*", B:B)

Bây giờ SUMIF trả về tổng thích hợp chỉ dựa trên các ô trong cột A bắt đầu bằng các chữ cái AKT. Điều gì theo sau các ký tự AKT trong mỗi ô không quan trọng vì dấu hoa thị nói với Excel rằng nó nên “chấp nhận bất kỳ thứ gì theo sau ba ký tự đó.”

Bạn thậm chí có thể làm cho cách tiếp cận này tổng quát hơn về bản chất. Giả sử rằng bạn đặt mã lời nói đầu mong muốn (mã mà bạn muốn tổng hợp)

vào ô E1. Sau đó, bạn có thể đặt nội dung sau vào ô E2:

=SUMIF(A:A, E1 & "*", B:B)

Bây giờ, nếu E1 chứa “AKT”, bạn sẽ kết thúc bằng tổng giá trị cho mã lời nói đầu đó. Nếu bạn thay đổi E1 thành “BRAF” thì bạn nhận được một tổng cho mã lời nói đầu đó mà không cần thay đổi công thức trong E2.

ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.

Mẹo này (13614) áp dụng cho Microsoft Excel 2007, 2010, 2013, 2016, 2019 và Excel trong Office 365.