Trong ngôi trường hợp buộc phải tính tổng theo điều khiếu nại là rất nhiều ký tự tương đương nhau ở vào 1 ô thì cần sử dụng hàm gì? Đây là thắc mắc rất thú vị mà lại Học Excel Online thường nhận được. Hãy cùng mày mò cách có tác dụng trong trường vừa lòng này nhé.
Bạn đang xem: Cách tính tổng các mã giống nhau trong excel
Ví dụ họ có yêu mong như sau:
Trong cột ID nhân viên, họ thấy các mã ID gồm chứa những ký từ là KD1, KD2. Tuy thế vị trí của các ký tự này sẽ không giống nhau, có nghĩa là nó rất có thể nằm ngẫu nhiên ở bất kỳ vị trí như thế nào trong ô, miễn sao có đúng cam kết tự đó.
Yêu mong là tính tổng lợi nhuận của những nhân viên có mã ID theo lần lượt là KD1 với KD2.
Xem nhanh
Cách tính tổng theo những ô có ký tự tương đương nhau vào Excel
Cách tính tổng theo các ô có ký tự kiểu như nhau trong Excel
Trong việc trên, bắt đầu đọc thì thấy hơi phức tạp. Nhưng thực ra ta có tương đối nhiều cách tính tổng vào Excel và các bạn sẽ bất ngờ khi biết chúng ta có vô số cách giải:Cách 1: tạo thành cột phụ nhận ra giá trị điều khiếu nại tính
Bởi vì những ký tự nằm lẫn vào chuỗi ký tự ở mỗi ô nên bạn cũng có thể dùng hàm xác định riêng từng ký tự xem ô làm sao chứa ký tự đó. Chèn thêm cột phụ phía đằng sau cột doanh thu và sử dụng hàm tìm kiếm xác định như sau:
Trong câu lệnh IFERROR(SEARCH($C$1,A2),0) ta có:
Thứ 1: search giá trị trong ô C1 (là KD1) xem có trong ô A2 xuất xắc không. Tác dụng trả về nếu có sẽ là vị trí ký kết tự bắt đầu lộ diện trong ô A2. Nếu như không sẽ trả về lỗi #VALUEThứ 2: phối kết hợp hàm IFERROR để tránh trường hợp lỗi #VALUE khi không tìm kiếm thấy bởi vì hàm SEARCH, lúc đó quý giá lỗi sẽ được thay ngay số 0Áp dụng tựa như với giá chỉ trị ở ô D1 là KD2
Khi đó tác dụng >0 có nghĩa là có giá chỉ trị đề nghị tìm, =0 là không có giá trị bắt buộc tìm.
Bây giờ câu hỏi trở đề xuất khá đơn giản, chúng ta chỉ cần sử dụng hàm SUMIF để tính công dụng như sau:
Kết quả tổng lệch giá của nhân viên cấp dưới có mã ID là KD1 =SUMIF(C2:C10,”>0″,B2:B10)
Tương tự nhân viên có mã KD2 là =SUMIF(D2:D10,”>0″,B2:B10)
Cách 2: Tính trực tiếp không cần sử dụng cột phụ cùng với hàm SUMIF
Việc thêm cột phụ khiến chúng ta phải thêm mỗi điều kiện là một trong cột, tại từng cột lại thực hiện nhiều công thức. Lý do không làm trực tiếp để tiết kiệm ngân sách tài nguyên của Excel nhỉ? Câu trả lời là trả toàn hoàn toàn có thể làm trực tiếp được.
Cách viết công thức như sau:
Với điều kiện là KD1, chúng ta có:
H3=SUMIF(A2:A10,”*”&F3&”*”,B2:B10)
Vùng điều khiếu nại là ở cột A, từ bỏ A2:A10 là ID nhân viênĐiều khiếu nại là “*”&F3&”*” tức là thêm 2 dấu * ở trước cùng sau quý hiếm trong ô F3. Vì chưng dấu * là ký kết tự đặc biệt nối với ô F3 nên cần đặt trong lốt nháy kép, sau đó sử dụng dấu & để nối ký kết tự.Vùng tính tổng là cột Doanh thu, từ B2:B10
Kết trái cũng bởi với bí quyết thứ 1.
Nếu không muốn sử dụng hàm SUMIF, các bạn có thể thay bằng hàm SUMIFS như sau:
H3=SUMIFS(B2:B10,A2:A10,”*”&F3&”*”)
Các câu chữ trong hàm này vẫn như là hàm SUMIF, dẫu vậy thứ tự bao gồm thay đổi 1 chút lúc vùng tính tổng được đưa lên thành phần máy 1 ở trong hàm.
Cách 3: áp dụng hàm SUMPRODUCT
Hẳn bạn đã nghe nói tới hàm SUMPRODUCT rất có thể thay thế hoàn toàn cho hàm SUMIF, SUMIFS. Trong ví dụ này bọn họ sẽ xem hàm SUMPRODUCT rất có thể sử dụng được ko nhé:
Công thức tại ô I3 thực hiện hàm SUMPRODUCT như sau:
=SUMPRODUCT(($B$2:$B$10)*(IFERROR(SEARCH(F3,$A$2:$A$10),0)>0))
Các chúng ta cũng có thể thấy phần IFERROR(SEARCH(F3,$A$2:$A$10),0) đó là nội dung trong cột phụ mà bọn chúng ta đã làm ở trên.
Khi đặt vào hàm SUMPRODUCT, họ sẽ đối chiếu kết quả đó bao gồm >0 xuất xắc không, với với đa số giá trị >0 đã dùng để tính tổng vào cột Doanh thu.
* Lưu ý:
Cách viết sau đây ko ra kết quả:
=SUMPRODUCT(($B$2:$B$10)*($A$2:$A$10=”*”&F3&”*”)
Rất thú vị đề xuất không nào. Vì thế là Học Excel Online đã giới thiệu cho chính mình 3 giải pháp làm câu hỏi này. Chúng ta có phương pháp nào khác không? Hãy chia sẻ cùng cửa hàng chúng tôi nhé.
Ngoài ra các bạn có thể tham khảo thêm một số nội dung bài viết cùng chủ đề:
Tại sao nên dùng hàm SUMIFS rứa cho hàm SUMPRODUCT tính tổng theo khá nhiều điều kiện
Excel mang lại cya.edu.vn 365 Excel cho web Excel 2019 Excel năm 2016 Excel 2013 xem thêm...Ẩn bớt
Hãy nói rằng bạn cần tính tổng những giá trị với nhiều hơn thế một điều kiện, chẳng hạn như tổng lệch giá sản phẩm vào một quanh vùng cụ thể. Đây là 1 trường hợp giỏi cho việc thực hiện hàm Sumifs vào công thức.
Hãy xem ví dụ như này mà chúng tôi có hai điều kiện: cửa hàng chúng tôi muốn tổng lợi nhuận bán thịt (từ cột C) ở quanh vùng phía nam (từ cột a).
Đây là 1 công thức bạn có thể sử dụng để acomplish này:
= SUMIFS (D2: D11, A2: A11, "miền Nam", C2: C11, "thịt")
Kết trái là cực hiếm 14.719.
Hãy xem xét chặt chẽ hơn sinh hoạt từng phần của công thức.
=SUMIFS là bí quyết số học. Nó giám sát số, vào trường vừa lòng này phía trong cột D. Bước đầu tiên là xác định vị trí của những số:
=SUMIFS(D2:D11,
Nói bí quyết khác, bạn muốn công thức tính tổng các số trong cột kia nếu phù hợp với những điều kiện. Phạm vi ô sẽ là đối số trước tiên trong bí quyết này — đoạn thứ nhất của tài liệu mà hàm đó yêu ước là đầu vào.
Tiếp theo, bạn muốn tìm dữ liệu đáp ứng nhu cầu hai điều kiện, do vậy các bạn nhập điều kiện đầu tiên của mình bằng phương pháp chỉ định các chất trí của dữ liệu (A2: A11) và cũng là câu chữ nào — vốn là "miền Nam". Thông báo dấu phẩy giữa các đối số riêng biệt:
=SUMIFS(D2:D11,A2:A11,”Miền Nam”,
Dấu ngoặc kép xung quanh "miền Nam" xác minh rằng tài liệu văn phiên bản này.
Cuối cùng, bạn nhập đổi mới số cho điều kiện thứ nhì – phạm vi ô (C2:C11) đựng từ “thịt,” cùng với từ đó (nằm trong lốt ngoặc kép) nhằm Excel rất có thể tìm tài liệu phù hợp. Ngừng công thức với lốt đóng ngoặc ) , rồi thừa nhận Enter. Kết quả, một lần nữa, là 14.719.
=SUMIFS(D2:D11,A2:A11,”Miền Nam”,C2:C11,”Thịt”)
Khi chúng ta nhập hàm SUMIFS vào Excel, nếu như khách hàng không nhớ các đối số, trợ giúp đã chuẩn bị sẵn sàng ở bàn tay. Sau khi bạn nhập = Sumifs (, bí quyết Auto
Complete xuất hiện bên dưới công thức, với danh sách các đối số theo thiết bị tự cân xứng của họ.
Xem hình ảnh của công thức Auto
Complete với danh sách các đối số, trong ví dụ như của bọn chúng sum_rangelà D2: D11, cột của các số mà bạn muốn tính tổng; criteria_range1là A2. A11, cột dữ liệu nơi criteria1 "miền Nam" nằm.
Khi chúng ta nhập, phần còn sót lại của đổi mới số sẽ xuất hiện thêm trong phương pháp Auto
Complete (không hiện ở đây); criteria_range2 là C2:C11, cột dữ liệu nơi criteria2 “Thịt” có trong đó.
Nếu bạn nhấp chuột Sumifs trong bí quyết Auto
Complete, một bài viết sẽ mở ra để giúp đỡ bạn tất cả thêm trợ giúp.
Bạn thử đi
Nếu bạn có nhu cầu thử nghiệm cùng với hàm SUMIFS, đây là một số tài liệu mẫu và công thức áp dụng hàm.
Bạn có thể làm bài toán với tài liệu mẫu và công thức ngay trên đây, trong sổ thao tác Excel giành riêng cho web này. Biến đổi các giá trị và công thức, hoặc thêm những giá trị và cách làm của riêng biệt bạn với xem trực tiếp kết quả đổi khác như nạm nào.
Xem thêm: Sửa Nguyện Vọng Đại Học 2020
Sao chép toàn bộ các ô trong bảng dưới và ốp lại ô A1 vào trang tính mới trong Excel. Bạn cũng có thể muốn kiểm soát và điều chỉnh độ rộng cột giúp thấy công thức giỏi hơn
| Khu vực | Nhân viên buôn bán hàng | Loại | Doanh số |
| Miền Nam | Ito | Nước giải khát | 3571 |
| Miền Tây | Lannin | Sản phẩm bơ sữa | 3338 |
| Miền Đông | Makovec | Nước giải khát | 5122 |
| Miền Bắc | Makovec | Sản phẩm bơ sữa | 6239 |
| Miền Nam | Jordan | Nông sản | 8677 |
| Miền Nam | Lannin | Thịt | 450 |
| Miền Nam | Lannin | Thịt | 7673 |
| Miền Đông | Makovec | Nông sản | 664 |
| Miền Bắc | Lannin | Nông sản | 1500 |
| Miền Nam | Jordan | Thịt | 6596 |
| Công thức |
| Mô tả | Kết quả |
| " = SUMIFS (D2: D11, A2: A11,"Miền Nam", C2: C11, "thịt") | Tổng lệch giá thịt trong | = SUMIFS (D2: D11, A2: A11,"Miền Nam", C2: C11, "thịt") |
Lưu ý: Trang này được dịch tự động nên rất có thể chứa các lỗi về ngữ pháp hoặc ngôn từ không chính xác. Mục tiêu của công ty chúng tôi là khiến nội dung này trở đề nghị hữu ích với bạn. Cho chúng tôi biết thông tin này còn có hữu ích tốt không? sau đây là nội dung bài viết bằng giờ Anh để chúng ta tham khảo..