›13:36:04 03-05-2024 | Lượt xem: 640
Trong hướng dẫn này, bạn sẽ tìm hiểu cách tạo công thức SUMIF hoặc SUMIFS phân biệt chữ hoa chữ thường trong Excel và Google Trang tính.
Không có nó không phải là. Trong cả Excel và Google Trang tính, cả SUMIF và SUMIFS đều không thể nhận dạng chữ cái viết hoa. Để đảm bảo điều này, vui lòng xem xét ví dụ đơn giản này:
Giả sử bạn có danh sách id trong cột A, trong đó chữ hoa và chữ thường biểu thị các mục khác nhau. Số liệu bán hàng tương ứng nằm ở cột B. Mục tiêu là đạt được tổng doanh số bán hàng cho một mặt hàng cụ thể, chẳng hạn như A-01 .
Với id mục tiêu trong E1, xây dựng công thức SUMIF cổ điển này:
=SUMIF(A2:A6, E1, B2:B6)
Và nhận được một kết quả hoàn toàn sai.
Để tính tổng các ô có một điều kiện xét theo kiểu chữ cái, bạn có thể sử dụng kết hợp các hàm SUMPRODVEL và EXACT:
SUMPRODUCT(--(EXACT(criteria, range)), sum_range)
Với id mục tiêu trong E1( criteria ), danh sách các mặt hàng trong A2:A10 ( range ) và số lượng bán hàng trong B2:B10 ( sum_range ), các công thức có dạng sau:
=SUMPRODUCT(--(EXACT(E1, A2:A10)), B2:B10)
Nếu cần, bạn có thể "mã hóa cứng" tiêu chí trực tiếp trong công thức:
=SUMPRODUCT(--(EXACT("A-01", A2:A10)), B2:B10)
Công thức này hoạt động như thế nào:
Trọng tâm của công thức, hàm EXACT so sánh mục đích (E1) với từng mục trong danh sách và trả về TRUE nếu các giá trị được so sánh hoàn toàn giống nhau bao gồm cả kiểu chữ, FALSE nếu không:
{TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE}
Toán tử một ngôi (--) chuyển đổi TRUE và FALSE tương ứng thành 1 và 0:
{1;0;0;0;1;0;1;0;0}
Hàm SUMPRODVEL nhân các phần tử của mảng trên với các mục tương ứng trong B2:B10:
SUMPRODUCT({1;0;0;0;1;0;1;0;0}, {250;155;130;255;160;280;170;285;110})
Và vì nhân với 0 sẽ bằng 0, nên chỉ các mục có kết quả CHÍNH XÁC trả về ĐÚNG mới tồn tại:
SUMPRODUCT({250;0;0;0;160;0;170;0;0})
Cuối cùng, SUMPRODVEL cộng các sản phẩm và đưa ra tổng.
Trong trường hợp bạn đang tìm kiếm một công thức SUMIFS phân biệt chữ hoa chữ thường với hai tiêu chí trở lên, bạn có thể mô phỏng hành vi này bằng cách xác định một cặp phạm vi/tiêu chí bổ sung trong một hàm CHÍNH XÁC khác:
SUMPRODUCT(--(EXACT(criteria1, range1)), --(EXACT(criteria2, range2)), sum_range)
Ví dụ: để tính tổng doanh số bán hàng của một mặt hàng cụ thể (F1) trong một khu vực nhất định (F2), công thức sẽ như sau:
=SUMPRODUCT(--(EXACT(F1, A2:A10)), --(EXACT(F2, B2:B10)), C2:C10)
Trong trường hợp bạn cần cộng các giá trị trong một cột nếu một ô trong cột khác chứa văn bản nhất định như một phần nội dung của ô, thì hãy sử dụng hàm SUMPRODVEL cùng với TÌM:
SUMPRODUCT(--(ISNUMBER(FIND(criteria, range))), sum_range)
Ví dụ: để tính tổng doanh số bán hàng cho mặt hàng trong E1 (có thể khớp với toàn bộ ô trong A2:A10 hoặc chỉ là một phần của chuỗi văn bản), công thức là:
=SUMPRODUCT(--(ISNUMBER(FIND(E1, A2:A10))), B2:B10)
Để tính tổng các ô dựa trên nhiều điều kiện , hãy thêm một tổ hợp ISNUMBER/FIND nữa:
SUMPRODUCT(--(ISNUMBER(FIND(criteria1, range1))), --(ISNUMBER(FIND(criteria2, range2))), sum_range)
Ví dụ: để tính tổng doanh số với hai điều kiện (id mặt hàng ở F1 và khu vực ở F2), công thức là:
=SUMPRODUCT(--(ISNUMBER(FIND(F1, A2:A10))), --(ISNUMBER(FIND(F2, B2:B10))), C2:C10)
Xin lưu ý rằng công thức cộng doanh số bán hàng cho một mặt hàng cụ thể ở bất kỳ khu vực "phía bắc" nào, chẳng hạn như Bắc , Đông Bắc hoặc Tây Bắc.
Công thức này hoạt động như thế nào:
Ở đây, sử dụng hàm FIND phân biệt chữ hoa chữ thường để tìm kiếm mục mục tiêu (E1). Khi tìm thấy mục, hàm sẽ trả về vị trí tương đối của nó trong chuỗi nguồn, nếu không sẽ xảy ra lỗi #VALUE.
{2;#VALUE!;#VALUE!;#VALUE!;1;#VALUE!;1;#VALUE!;#VALUE!}
Hàm ISNUMBER chuyển đổi bất kỳ số nào thành TRUE và các giá trị lỗi thành FALSE:
{TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE}
Tiếp theo, bạn thực hiện "phủ định kép" (--) để ép các giá trị logic thành 1 và 0:
{1;0;0;0;1;0;1;0;0}
Cuối cùng, hàm SUMPRODVEL nhân các phần tử của hai mảng và đưa ra kết quả:
SUMPRODUCT({1;0;0;0;1;0;1;0;0}, {250;155;130;255;160;280;170;285;110})
Nếu công thức của bạn xử lý nhiều điều kiện thì SUMPRODVEL sẽ nhân các phần tử của ba mảng trở lên. Trong trường hợp này, đó là:
=SUMPRODUCT({1;0;0;0;1;0;1;0;0}, {1;0;1;1;1;1;0;1;0}, {250;155;130;255;160;280;170;285;110})
Công thức SUMIF phân biệt chữ hoa chữ thường đã tạo cho Excel cũng sẽ hoạt động trong Google Trang tính. Ngoài ra, bạn có thể lấy chính hàm SUMIF của Google Sheet để phân biệt ký tự viết hoa và viết thường. Đây là cách thực hiện:
SUMIF(ArrayFormula(EXACT(criterion, range)), TRUE, sum_range)
Hoặc
SUMIF(ArrayFormula(FIND(criterion, range)), 1, sum_range)
Đối với tập dữ liệu mẫu, các công thức thực sự trông như sau:
=SUMIF(ArrayFormula(EXACT(E1, A2:A10)), TRUE, B2:B10)
=SUMIF(ArrayFormula(FIND(E1, A2:A10)), 1, B2:B10)
Cách thức hoạt động của công thức SUMIF/EXACT:
Để làm cho hàm SUMIF(range, Criteria, [sum_range]) của Google Sheets nhận dạng chữ cái, sử dụng công thức sau cho đối số phạm vi:
ArrayFormula(EXACT(E1, A2:A10))
ArrayFormula buộc EXACT so sánh giá trị trong E1 với từng giá trị trong A2:A10. Nếu tìm thấy kết quả khớp chính xác, công thức sẽ trả về TRUE, nếu không - FALSE.
Trong phạm vi giá trị TRUE và FALSE, SUMIF tìm kiếm TRUE và cộng các giá trị tương ứng trong B2:B10.
Cách thức hoạt động của công thức SUMIF/FIND:
Ở đây, sử dụng kết hợp ArrayFormula và FIND để tìm giá trị đích (E1) trong phạm vi A2:A10:
ArrayFormula(FIND(E1, A2:A10))
Nếu sử dụng riêng, hàm FIND sẽ ngừng tìm kiếm sau khi tìm thấy kết quả khớp đầu tiên.
Bất cứ nơi nào tìm thấy giá trị đích, công thức sẽ trả về 1 (là vị trí tương đối của nó trong chuỗi tìm kiếm). Đối với các ô không tìm thấy giá trị, lỗi #VALUE! lỗi được trả về.
Tiếp theo bạn dùng số 1 làm tiêu chí của SUMIF là xong nhé.
Để tính tổng các ô có nhiều điều kiện trong Google Trang tính, bạn có thể sử dụng công thức SUMPRODVEL phân biệt chữ hoa chữ thường được thảo luận trong phần hướng dẫn của Excel hoặc SUMIFS của Google Trang tính kết hợp với EXACT hoặc FIND:
SUMIFS(sum_range, ArrayFormula(EXACT(criterion1, range1)), TRUE, ArrayFormula(EXACT(criterion2, range2)), TRUE)
Hoặc
SUMIFS(sum_range, ArrayFormula(FIND(criterion1, range1)), 1, ArrayFormula(FIND(criterion2, range2)), 1)
Như bạn thấy, logic cũng giống như hàm SUMIF. Sự khác biệt là bạn sử dụng hai hoặc nhiều cặp phạm vi/tiêu chí.
Ví dụ: để tính tổng doanh số của mặt hàng ở F1 và khu vực ở F2, công thức là:
=SUMIFS(C2:C10, ArrayFormula(EXACT(F1, A2:A10)), TRUE, ArrayFormula(EXACT(F2, B2:B10)), TRUE)
=SUMIFS(C2:C10, ArrayFormula(FIND(F1, A2:A10)), 1, ArrayFormula(FIND(F2, B2:B10)), 1)
Đây là cách bạn có thể tạo công thức SUMIF hoặc SUMIFS phân biệt chữ hoa chữ thường trong Excel và Google Trang tính. Hy vọng bạn sẽ thực hiện thành công.
