Sumproduct excel là gì
Cũng lâu ghê mình chưa viết bài về Excel đấy nhỉ. Thời gian vừa qua mình cũng bận việc với phải dành thời gian thay đổi địa chỉ cho blog và chỉnh sửa lại blog 1 chút. Hôm nay cuối tuần rảnh mình cố gắng ngồi viết bài để cho các bạn có bài để đọc 😀 Show
Trong bài này mình sẽ viết hướng dẫn chi tiết về cách dùng hàm SUMPRODUCT trong Excel qua ví dụ cụ thể để các bạn biết cách sử dụng hàm này khi làm việc. Về hàm SUMPRODUCT thì nó là hàm dùng để tính tổng các tích của các phần tử số trong các mảng. Về cơ bản bạn chỉ hiểu sơ sơ như vậy thôi còn về cách sử dụng hàm SUMPRODUCT trong Excel như nào thì chúng ta đi vào tìm hiểu công thức và ví dụ cụ thể dưới đây. Hàm SUMPRODUCT là hàm được dùng khá nhiều trong kế toán chính vì thế nó có thể áp dụng được trên hầu hết các phiên bản Excel như Excel 2007, 2010, 2013, 2016 và kể cả Excel cho điện thoại iPhone, Android,…Về cách dùng thì trên các phiên bản nó đều tương tự nhau, ở đây mình hướng dẫn các bạn làm trên Excel 2016 còn bạn nào dùng các phiên bản khác cũng làm tương tự không có gì khác cả. =SUMPRODUCT(array1, [array2], [array3], …) Chú ý: Tùy theo cài đặt của từng máy tính mà bạn dùng dấu phân cách là dấu phẩy (,) hoặc chấm phẩy (;) nhé.
Lưu ý
Tham khảo thêm Ví dụ 1: Cách dùng hàm SUMPRODUCT cơ bản Để hiểu về cách dùng hàm SUMPRODUCT cơ bản chúng ta cùng làm ví dụ sau đây để hiểu về cách sử dụng hàm này nhé. Yêu cầu: Chúng ta có 1 bảng thống kê đơn hàng ở 1 cửa hàng. Chúng ta phải tính tổng doanh thu của tất cả các đơn hàng trong cửa hàng đó. Chúng ta sẽ áp dụng hàm SUMPRODUCT để tính tổng doanh thu. Như trong hình trên bạn cũng thấy, để tính được tổng doanh thu chúng ta cứ lấy (Số lượng bán * Đơn giá) rồi cộng tổng vào là ra. Nhưng ở đây chúng ta có sự hỗ trợ của hàm SUMPRODUCT thì sao phải nhân rồi cộng tổng bằng chi cho vất vả. Tại ô F4 chúng ta nhập vào công thức hàm SUMPRODUCT như sau: =SUMPRODUCT(D4:D9,E4:E9) = 1657.5 => Diễn giải chút về cơ chế của hàm SUMPRODUCT như sau: Hàm sẽ lấy giá trị trong ô đầu tiên của mảng đầu tiên nhân với giá trị trong ô đầu tiên của mảng thứ 2, rồi lấy giá trị trong ô thứ 2 của mảng đầu tiên nhân với giá trị trong ô thứ 2 của mảng thứ 2, nó cứ làm thế cho đến khi nhân hết các giá trị trong mảng. Sau khi đã nhân hết các thành phần trong mảng nó sẽ cộng tổng lại và cho ra kết quả. Để các bạn hiểu hơn mình sẽ dựa vào ví dụ trên và nó sẽ có công thức về cơ chế tính toán như sau: =D4*E4 + D5*E5 + D6*E6 + D7*E7 + D8*E8 + D9*E9 Ngoài ra, kết quả trên nó cũng tương đương khi bạn dùng hàm SUM để tính với công thức như sau: =SUM(D4*E4,D5*E5,D6*E6,D7*E7,D8*E8,D9*E9)=1657.5 Ví dụ 2: Cách dùng hàm SUMPRODUCT với 1 điều kiện Với hàm SUMPRODUCT chúng ta sẽ không tận dụng hết khả năng của nó nếu chỉ dùng để tính toán như ví dụ cơ bản trên. Để hàm này phát huy hết khả năng của nó thì bạn cần cho nó thêm những yêu cầu khó hơn bằng cách chèn thêm điều kiện vào cho nó. Ở ví dụ này mình sẽ bắt đầu với 1 điều kiện để bạn làm quen dần nhé. Yêu cầu: Tính tổng số sản phẩm bán ra có số lượng KHÔNG đạt chỉ tiêu. Ở đây, nếu bạn không biết dùng hàm SUMPRODUCT bạn cũng có thể sử dụng hàm COUNTIF để đếm, nhưng nó sẽ rất vất vả nếu trong bảng tính có tới hàng nghìn đầu mục. Còn khi bạn đã biết cách dùng hàm SUMPRODUCT rồi thì việc tính toàn đơn giản như sau: =SUMPRODUCT(–(E4:E9 hoặc công thức =SUMPRODUCT((E4:E9 Trong công thức đầu tiên, bạn thấy có 2 dấu trừ (–) về mặt kỹ thuật thì đây là hai toán tử đơn phân, nó giúp biến TRUE thành 1 và FALSE thành 0. Ví dụ trong công thức =SUMPRODUCT(–(E4:E9 Ví dụ 3: Cách dùng hàm SUMPRODUCT với nhiều điều kiện Ở đây, chúng ta sẽ sử dụng lại ví dụ 2 nhưng sẽ thêm 1 điều kiện nữa cho nó khó 😀 Yêu cầu: Tính tổng số sản phẩm SONY bán ra có số lượng KHÔNG đạt chỉ tiêu. Sử dụng hàm SUMPRODUCT chúng ta có công thức tính như sau: =SUMPRODUCT((B4:B9=”SONY”)*(E4:E9>F4:F9)) Trong công thức này bạn để ý dấu * là dùng để nối 2 điều kiện với nhau nhé. Ngoài ra, bạn có thể sử dụng công thức tương tự như trong ví dụ 2 như sau: =SUMPRODUCT(-(B4:B9=”SONY”),-(E4:E9>F4:F9)) Cả 2 công thức trên đều cho ra kết quả giống nhau nhé. Trên đây là các ví dụ về cách sử dụng hàm SUMPRODUCT để áp dụng trong công việc giúp bạn làm việc dễ dàng và hiệu quả hơn. Để sử dụng thành thạo được hàm SUMPRODUCT thì đòi hỏi bạn cần luyện tập thật nhiều về hàm này. Dưới đây, mình sẽ đính kèm file ví dụ về hàm SUMPRODUCT các bạn tải về và thực hành nhé.
Download file ví dụ về hàm SUMPRODUCT Lời kết Trên đây là toàn bộ lý thuyết và ví dụ về hàm SUMPRODUCT rất chi tiết. Hi vọng với những gì mình chia sẻ sẽ giúp bạn sử dụng được thành thạo hàm SUMPRODUCT để vận dụng được vào trong công việc của bạn. Nếu thấy bài viết hay hãy like và share cho mọi người nhé. Và có thắc mắc gì hãy để lại ý kiến dưới khung bình luận để được giải đáp nhé. Nhớ theo dõi chuyên mục thủ thuật Excel trên NguyenHung.Net để học nhiều hơn các hàm Excel nhé. Chúc các bạn thành công! Hàm sumproduct là gì? Cách sử dụng hàm sumproduct trong excelKhi đã nghe được tên hàm SUM PRODUCT lần đầu tiên, có thể bạn nghĩ nó là công thức vô ích – công thức tính tổng thông thường. Thực tế, định nghĩa này không cho thấy thậm chí là chỉ một phần nhỏ trong số tính năng của hàm SUMPRODUCT. Bản chất hàm sumproduct là gì? Thực ra, hàm SUM PRODUCT là hàm thực chất sở hữu rất nhiều tính năng nổi trội so với các hàm trong Excel. Do khả năng đặc biệt trong việc có thể xử lý mảng một cách tinh tế và cực kỳ thông minh, nên hàm SUM PRODUCT ngược lại rất hữu ích, nếu không phải là bắt buộc, khi nói đến việc so sánh dữ liệu ở hai hay nhiều dải ô và việc tính toán dữ liệu trong các bài toán có nhiều điều kiện. Những ví dụ trong bài viết dưới đây sẽ cho bạn thấy toàn bộ khả năng cực quan trọng của hàm SUMPRODUCT và sự hữu ích của nó sẽ sớm trở nên cực kỳ rõ ràng... I. Hàm sumproduct là gì?1. Hàm sumproduct là gì?Hàm SUMPRODUCT trong Excel là hàm được dùng để tính tổng tích các phần tử số trong một mảng hoặc nhiều mảng khác nhau, khác với hàm PRODUCT chỉ được dùng để tính tích các giá trị. Bên cạnh đó, hàm SUMPRODUCT tính tổng nhiều điều kiện cũng được nhiều người ưa chuộng sử dụng Mảng các hàm trongExcel cơ bản sẽ là 1 dãy những số liệu với các giá trị liên tiếp mà người dùng khoanh vùng trên bảng dữ liệu. Hàm SUMPRODUCT có trên tất cả các phiên bản các hàm Excel cơ bản, với cách dùng khá là tương tự như nhau. Cách sử dụng chi tiết hàm SUMPRODUCT trong Excel sẽ được 123job.vn dẫn trong bài dưới đây. 2. Cú phápHàm SUMPRODUCT có cú pháp cụ thể là =SUMPRODUCT(array1, [array2], [array3], …). Trong đó:
Các đối số tồn tại trong mỗi mảng đều phải có cùng kích thước, cùng số phần tử trong đó. Trong trường hợp các phần tử ở các mảng tương ứng không phải được hiển thị dưới dạng số thì hàm SUMPRODUCT trong Excel cơ bản sẽ mặc định coi đó là số 0. 3. Ví dụQua ví dụ sau, chúng ta sẽ hiểu rõ hơn về hàm SUMPRODUCT là gì. Chúng ta sẽ tính số lượng doanh thu trong hoạt động bán sách của 1 cửa hàng trong bảng ví dụ cụ thể dưới đây. Trong đó, bảng đã cho số lượng được cho cho từng loại sách và giá thành của từng loại trong đó tính theo đơn vị nghìn đồng. Bảng đã cho số lượngChúng ta sẽ nhập công thức tính cụ thể của bài tập là =SUMPRODUCT(C2:C4,D2:D4) rồi thực hiện nhấn Enter để khởi chạy công thức tính. Khởi chạy công thức tính các hàm trong excelNgay sau đó người dùng sẽ có kết quả hiển thị doanh thu của cửa hàng khi bán tổng số lượng sách đã cho bên trên. Kết quả hiển thị doanh thuII. Cách sử dụng hàm sumproduct trong excel cơ bản1. Cách dùng cơ bản của hàm sumproduct là gìĐể có cái nhìn tổng quát về cách hàm SUMPRODUCT trong Excel cơ bản hoạt động, hãy xem qua ví dụ sau. Giả sử bạn có số lượng ở các ô A2:A4, và qua đó bạn muốn tính tổng. Nếu bạn đang giải một phép toán ở trường, thì bạn sẽ thực hiện nhân số lượng với giá tiền của mỗi sản phẩm, rồi cộng chúng lại. Trong Microsoft Excel, bạn hoàn toàn có thể nhận được kết quả nhanh chóng chỉ với một công thức SUMPRODUCT: =SUMPRODUCT(A2:A4,B2:B4) Kết quả nhanh chóng chỉ với một công thức SUMPRODUCTĐây là chi tiết về những gì đang diễn ra trong Excel về mặt toán học: Công thức hàm sumproduct tính tổng nhiều điều kiện chọn số đầu tiên trong mảng đầu tiên rồi thực hiện nhân nó cho số đầu tiên trong mảng thứ hai, rồi tiếp tục lấy số thứ hai trong mảng đầu tiên nhân cho số thứ hai trong mảng thứ hai rồi cứ tiếp tục quay vòng như thế. Khi đã thực hiện việc nhân tất các thành phần mảng trong hàm sumproduct tính tổng nhiều điều kiện, công thức sẽ tính tổng các tích số rồi trả về kết quả là tổng số. Nói cách khác, công thức hàm SUMPRODUCT tính tổng nhiều điều kiện biểu diễn công thức toán học sau đây: =A2*B2 + A3*B3 + A4*B4 2. Cách dùng hàm sumproduct có nhiều điều kiệnThông thường trong Microsoft Excel cơ bản, luôn có nhiều hơn một cách để hoàn thành công việc được chỉ định. Nhưng khi nói đến việc thực hiện quá trình so sánh hai hay nhiều mảng, đặc biệt là có nhiều điều kiện, nếu không nói quá lên nó là duy nhất, thì hàm SUMPRODUCT tính tổng nhiều điều kiện là hàm hiệu quả nhất. Giả sử bạn có danh sách các món hàng được hiển thị ở cột A, doanh số bán hàng được dự kiến ở cột B, và doanh số bán thực sẽ là dữ kiện ở cột C. Mục tiêu của bạn là cần phải tìm xem có bao nhiêu có bao nhiêu món bán được thực hiện lệnh ít hơn dự kiến. Đối với trường hợp này, hãy sử dụng lần lượt một trong những biến phân của công thức SUMPRODUCT: =SUMPRODUCT(–(C2:C10 hay =SUMPRODUCT((C2:C10 Trong đó C2:C10 là thể hiện phần doanh số thực và B2:B10 là phần doanh số dự kiến. Hàm sumproduct là gìNhưng nếu bạn trong trường hợp này có nhiều hơn một điều kiện thì sao? Giả sử bạn muốn đếm số lần bán được Táo ít hơn so với dự kiến. Giải pháp đó là thêm một điều kiện cụ thể nữa vào công thức SUMPRODUCT tính tổng nhiều điều kiện: =SUMPRODUCT(–(C2:C10 Hay, bạn có thể sử dụng cú pháp khác như sau: =SUMPRODUCT((C2:C10 Sử dụng cú pháp khác3. Đếm tổng, tính trung bình có điều kiện các ô với nhiều tiêu chuẩnTrong phiên bản Excel 2003 và các phiên bản trước thì không tồn tại hàm IFS, một trong những cách dùng thông dụng nhất của hàm SUMPRODUCT đó chính là việc tính tổng hay đếm có điều kiện các ô với nhiều tiêu chuẩn. Bắt đầu với phiên bản Excel 2007, Microsoft chính thức giới thiệu một chuỗi hàm được đặc biệt thiết kế cho những nhiệm vụ này – hàm SUMIFS, cùng với hàm COUNTIFS và hàm AVERAGEIFS. Nhưng thậm chí ở các phiên bản hiện đại hơn nữa của các hàm trong excel Excel cơ bản, công thức SUMPRODUCT là gì hoàn toàn có thể là một lựa chọn thay thế, ví dụ, trong việc tính tổng và đếm có điều kiện các ô có chứa đựng cả hàm logic OR. Dưới đây, bạn sẽ thấy vài ví dụ công thức cho thấy khả năng đặc biệt này. 4. Cách dùng hàm sumproduct nâng cao trong excelLồng ghép hàng khác trong hàm SUMPRODUCT để thực hiện thay đổi giá trị của mảng cần tính Lồng ghép hàng khácTrong ví dụ trên, để có thể thực hiện tính tổng tiền của những mặt hàng bán được trong tháng 3, nhưng dữ liệu được hiển thị chỉ có cột Ngày bán, không có sẵn cột Tháng. Để tính được những thông tin này, chúng ta phải tách được Tháng trong cột Ngày. Ở cách tính toàn và ứng dụng các hàm trongExcel cơ bản thông thường, chúng ta phải làm những thao tác như sau:
Cách làm như vậy thực sự sẽ tốn nhiều công thức và thời gian phải không? Nếu nga lúc đó ta sử dụng hàm SUMPRODUCT thì chúng ta chỉ cần áp dụng 1 hàm là ra ngay kết quả, trong đó xét 3 mảng:
Nhân 3 mảng này lại với nhau, ta thu được kết quả Hàm SUMPRODUCT tính toán cho bảng dữ liệu 2 chiều Hàm SUMPRODUCT tính toán cho bảng dữ liệu 2 chiềuTrong bảng tính đã cho ở trên, ta có yêu cầu xác định đơn giá cụ thể của 1 mặt hàng có bao gồm 2 điều kiện: Mã hàng và Mã công ty. Bảng chứa thông tin cụ thể cần tìm (Bảng đơn giá) là bảng được xác định 2 chiều:
Khi đó chúng ta sử dụng hàm SUMPRODUCT trong Excel như sau: G3=SUMPRODUCT(($B$14:$B$17=C3)*($C$13:$F$13=B3)*$C$14:$F$17)
III. Kết luậnNhư vậy qua một số ví dụ trên, chúng ta đã có thể biết được hàm sumproduct là gì, cách sử dụng hàm sumproduct trong excel và hàm sumproduct tính tổng nhiều điều kiện... Hy vọng những kiến thức này sẽ giúp ích thật tốt cho các bạn trong công việc của nhân viên văn phòng nhé! Xem tiếp: Ý nghĩa và cách dùng hàm ROUND trong excel đơn giản, chi tiết Tag: Bài viết nhiều người đọc
123job.vn - Dream jobs, great places to work, high salary 123job.vn - Trao cơ hội cho hàng triệu người với những công việc mơ ước với môi trường làm việc chuyên nghiệp và mức lương tốt nhất. Với sứ mệnh: Cung cấp các thông tin việc làm, review công ty hấp dẫn, dịch vụ tư vấn tuyển dụng xác thực và chất lượng cho nhà tuyển dụng và người lao động, chúng tôi luôn tận tâm tận lực, không ngừng sáng tạo nhằm đem lại chất lượng dịch vụ hàng đầu, giúp tất cả mọi người có được một công việc phù hợp nhất. Tự hào: Là trang tuyển dụng uy tín, là cầu nối của hàng triệu người tìm việc và nhà tuyển dụng. Giá trị cốt lõi:
Nếu bạn đang muốn kết nối với những nhà tuyển dụng uy tín hàng đầu Việt Nam, đừng ngần ngại hãy TẠO CV NGAY để tăng gấp 5 lần cơ hội có được công việc với mức lương tốt nhất nhé! |