Hướng dẫn này giải thích cách chỉ đếm các giá trị duy nhất giữa các bản sao trong danh sách trong Excel với các công thức được chỉ định.
Làm cách nào để đếm các giá trị duy nhất trong một cột trong Excel?
Giả sử bạn có bảng sản phẩm như hình minh họa bên dưới, để chỉ tính các giá trị duy nhất trong cột Sản phẩm, bạn có thể áp dụng một trong các công thức dưới đây.
Công thức chung
\=SUMPRODUCT[--[FREQUENCY[MATCH[range,range,0],ROW[range]-ROW[range.firstcell]+1]>0]]
\=SUMPRODUCT[1/COUNTIF[range,range]]
Lập luận
Phạm vi: Phạm vi ô bạn muốn chỉ đếm các giá trị duy nhất;
Phạm vi.firstcell: Ô đầu tiên của phạm vi.
Làm thế nào để sử dụng các công thức này?
1. Chọn một ô trống để xuất kết quả.
2. Nhập một trong các công thức dưới đây vào ô đã chọn và nhấn đăng ký hạng mục thi Chìa khóa.
\=SUMPRODUCT[--[FREQUENCY[MATCH[D3:D16,D3:D16,0],ROW[D3:D16]-ROW[D3]+1]>0]]
\=SUMPRODUCT[1/COUNTIF[D3:D16,D3:D16]]
Ghi chú:
- Trong các công thức này, D3: D16 là phạm vi ô bạn muốn chỉ đếm các giá trị duy nhất và D3 là ô đầu tiên của phạm vi. Bạn có thể thay đổi chúng khi bạn cần.
- Nếu các ô trống tồn tại trong phạm vi được chỉ định, công thức đầu tiên sẽ trả về lỗi # N / A và công thức thứ hai sẽ trả về lỗi # DIV / 0.
Giải thích công thức
\=SUMPRODUCT[--[FREQUENCY[MATCH[D3:D16,D3:D16,0],ROW[D3:D16]-ROW[D3]+1]>0]]
- TRẬN ĐẤU [D3: D16, D3: D16,0]: Hàm MATCH lấy vị trí của từng mục trong phạm vi D3: D16, nếu các giá trị xuất hiện nhiều hơn một lần trong phạm vi dữ liệu, nó sẽ trả về cùng một vị trí để có được mảng như thế này {1; 2; 3; 2; 1 ; 1; 3; 2; 1; 1; 1; 2; 3; 2}.
- ROW [D3: D16] -ROW [D3] +1: Ở đây, hàm ROW trả về số hàng tham chiếu D3: D16 và D3, và bạn sẽ nhận được {3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16} - {3} +1.
- Mỗi số trong mảng trừ số 3 sau đó cộng 1 và cuối cùng trả về {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14}.
- FREQUENCY[{1;2;3;2;1;1;3;2;1;1;1;2;3;2},{1;2;3;4;5;6;7;8;9;10;11;12;13;14}]: Hàm tần số tính toán tần suất xuất hiện của mỗi số trong mảng dữ liệu và trả về một mảng các số như sau {6; 5; 3; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0 }.
- SUMPRODUCT[--{6;5;3;0;0;0;0;0;0;0;0;0;0;0}>0]:
{6;5;3;0;0;0;0;0;0;0;0;0;0;0}>0: Mỗi số trong mảng so sánh với 0 và trả về TRUE nếu lớn hơn 0, ngược lại, trả về FALSE. Và bạn sẽ nhận được một mảng TRUE FALSE như sau {TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE};
- {TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}: Hai dấu trừ này chuyển “TRUE” thành 1 và “FALSE” thành 0. Ở đây bạn sẽ nhận được một mảng mới là {1; 1; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0 ; 0; 0}.
SUMPRODUCT[{1;1;1;0;0;0;0;0;0;0;0;0;0;0}]: Hàm SUMPRODUCT tính tổng tất cả các số trong mảng và trả về kết quả cuối cùng là 3.
. Bởi đây là hàm rất quen thuộc và có tính ứng dụng cao trong hệ thống Excel. Nhưng với những “tân binh” đang tìm hiểu về phần mềm này chắc sẽ còn bỡ ngỡ với hàm Sumproduct. Nếu bạn đang thắc mắc về hàm tính này và chưa biết công dụng cơ bản khi áp dụng trong Excel như thế nào thì đừng bỏ qua những chia sẻ dưới đây của Teky.
Bạn đang thắc mắc hàm Sumproduct là gì? Đây thực ra là một hàm trong Excel có sự kết hợp giữa 2 hàm lại với nhau. Đó chính là hàm Sum và hàm Product. Theo đó, hàm này sẽ giúp trả kết quả về giá trị tổng của nhiều tích trong cùng 1 dãy số được cho trong phần mềm. Việc ứng dụng hàm Sumproduct giúp việc nhân tích các phép cộng được thực hiện nhanh và có độ chính xác cao nhất.
Công thức hàm Sumproduct
Muốn tính được hàm Sumproduct thì bạn phải nắm được cú pháp của hàm. Cụ thể như sau:
SUMPRODUCT[array1, [array2], [array3], …]
Theo đó, trong cú pháp này, các giá trị array chính là dải số liên tục hoặc là một mảng có các thành phần cần được tính toán. Việc nhập các giá trị array cần phải được thực hiện cẩn thận với hàm này. Đặc biệt, các mảng được chọn đòi hỏi phải có cùng số hàng hay cùng số cột. Nếu chọn sai, hàm khi tính sẽ trả kết quả
VALUE.
Hướng dẫn cách sử dụng hàm Sumproduct trong Excel cơ bản nhất
Sử dụng hàm Sumproduct trong excel không quá khó. Ví dụ, nếu bạn muốn tính tổng tiền trong danh sách mua trái cây theo ví dụ trong hình thì bạn sẽ cần áp dụng theo các bước sau:
- Chọn vùng số lượng từ F6:F9
- Chọn vùng đơn giá từ G6:G9
- Dùng hàm Sumproduct để nhân 2 vùng này lại với nhau để có được kết quả chính xác nhất.
Hướng dẫn cách sử dụng hàm Sumproduct có điều kiện
Nếu bạn đã biết được hàm Sumproduct là gì thì cũng cần biết cách sử dụng hàm này trong Excel với các điều kiện khác nhau. Nếu áp dụng chính xác hàm Sumproduct có điều kiện sẽ giúp bạn có thể tiến hành tính toán các con số trong phần mềm Excel được nhanh chóng và dễ dàng hơn rất nhiều. Dưới đây là một số ví dụ cơ bản để bạn tham khảo.
Tính tổng các mã giống nhau bằng hàm Sumproduct
Cho bảng Doanh Thu thực phẩm ở các khu vực khác nhau theo bảng dưới. Bạn cần sử dụng hàm Sumproduct để tính tổng doanh thu trà tại khu vực phía Bắc trong bảng.
Với yêu cầu tính trên, bạn cần sử dụng hàm Sumproduct theo công thức: =SUMPRODUCT[[B3:B10=”Trà”*[C3:C10=”Bắc”]*D3:D10]. Theo đó, kết quả hiển thị trong phần mềm Excel sẽ là:
Tính tổng dựa vào điều kiện bằng hàm Sumproduct kết hợp với “OR”
Vẫn với ví dụ tính bảng doanh thu trên nhưng bạn có thể áp dụng hàm Sumproduct nhiều điều kiện nếu yêu cầu tính nhiều hơn 1 dữ kiện. Cụ thể là từ bảng trên, bạn cần tính tổng doanh thu của trà hay của cafe ở phía Bắc. Với yêu cầu tính này, bạn vẫn sẽ sử dụng hàm Sumproduct nhưng cần kết hợp thêm “OR” để có được kết quả tính tổng chuẩn nhất.
Công thức tính như sau: =SUMPRODUCT[OR[[B3:B10=”Trà”;[B3:B10=”Cà phê”]]*[C3:C10=”Bắc”]*D3:D10]. Kết quả trả về sẽ có như bảng sau:
Hướng dẫn sử dụng hàm Sumproduct nâng cao
Có nhiều trường hợp cần phải sử dụng đến hàm Sumproduct nâng cao. Đây là loại hàm cần phải có sự kết hợp giữa hàm Sumproduct với các hàm khác để tính được kết quả chính xác mà vẫn đáp ứng được điều kiện tính. Có nhiều cách áp dụng hàm Sumproduct nâng cao mà bạn có thể tham khảo theo các hướng dẫn sau:
Cách tính hàm Sumproduct nâng cao bằng cách lồng ghép thêm các hàm khác
Quay trở lại ví dụ danh sách trái cây ở mục tính hàm Sumproduct cơ bản, chúng ta có thể sử dụng thêm các hàm khác để tính tổng tiền các trái cây đã bán được trong tháng 3. Nhưng trong bảng lại chỉ có cột ngày mà không có cột tháng. Điều bạn cần làm là sử dụng thêm hàm MONTH. Cụ thể như sau:
- Ở cột Ngày bán sẽ tính từ B2;B5 kết hợp hàm MONTH và đem so sánh với 3
- Cột số lượng chọn tính từ C2:C5
- Cột đơn giá tính từ D2:D6=5
- Sau khi có được kết quả của 3 cột, bạn dùng hàm Sumproduct để nhân lại với nhau sẽ có được kết quả như bảng sau:
Sử dụng hàm Sumproduct nâng cao để tính bảng dữ liệu 2 chiều
Có một cách áp dụng hàm Sumproduct nâng cao mà nhiều người vẫn chưa biết. Đó chính là tính bảng có dữ liệu 2 chiều. Bạn có thể xem ví dụ như hình sau:
Theo đó, bạn cần phải xác định được đơn giá của 1 mặt hàng nhưng nó phải đáp ứng được 2 điều kiện. Đó là Mã công ty và Mã hàng. Như vậy, tại “Bảng đơn giá hàng hóa’ sẽ có 2 chiều. 1 chiều dọc là Mã công ty và 1 chiều ngang là Mã hàng. Hai chiều này có các điểm giao nhau chính là các đơn giá của sản phẩm.
Với yêu cầu này, bạn có thể sử dụng hàm Sumproduct với các dữ kiện được lấy như sau:
- Mảng Mã công ty sẽ lấy từ B14:B17 có mã được lấy ở ô C3
- Mảng Mã hàng sẽ lấy từ C13:F13 với mã được lấy ở ô B3
- Tương ứng theo 2 điều kiện trên, bạn sẽ lấy đơn giá từ C14:F17
Từ những dữ kiện được lấy trên, bạn có thể nhập hàm Sumproduct theo công thức sau:
G3=SUMPRODUCT[[$B$14:$B$17=C3]*[$C$13:$F$13=B3]*$C$14:$F$17]
Kết quả tìm được sẽ thể hiện rõ trong hình dưới đây:
So với các hàm Sumproduct khác thì hàm nâng cao này có phần phức tạp hơn. Vì thế, khi tính toán, bạn cần phải chọn chính xác các vùng dữ liệu để viết công thức hàm. Nếu chọn sai vùng sẽ khiến Excel trả kết quả không chính xác.
Trên đây, Teky đã giúp bạn tìm hiểu rõ thông tin hàm Sumproduct là gì cũng như cách sử dụng các hàm Sumproduct trong excel với điều kiện và nâng cao chi tiết nhất. Nếu bạn muốn được tư vấn hay đăng ký các khóa học Excel chất lượng thì đừng quên liên hệ đến Teky để có được sự hỗ trợ uy tín, chuyên nghiệp nhất. Dịch vụ cam kết mang đến bạn các khóa đào tạo giá tốt, kết quả vượt trội.