Hàm SUMPRODUCT trong Excel và một số ví vụ công thức

Bài hướng dẫn này của Học Excel Online sẽ giải thích cách sử dụng cơ bản và nâng cao của hàm SUMPRODUCT trong Excel. Bạn sẽ thấy một vài ví dụ công thức dùng để so sánh mảng, tính tổng có điều kiện và đếm số ô có nhiều điều kiện, tính trung bình cộng có trọng lượng và hơn thế nữa.

Khi bạn nghe cái tên hàm SUMPRODUCT lần tiên phong, nó có vẻ như là một công thức vô ích – công thức tính tổng thường thì của các tích số. Nhưng định nghĩa này không cho thấy thậm chí còn là chỉ một phần nhỏ tính năng của hàm SUMPRODUCT .
Thực ra, hàm SUMPRODUCT trong Excel là hàm có rất nhiều tính năng. Do năng lực đặc biệt quan trọng hoàn toàn có thể giải quyết và xử lý mảng một cách tinh xảo và mưu trí, nên hàm SUMPRODUCT trong Excel rất hữu dụng, nếu không bắt buộc, khi nói đến việc so sánh tài liệu ở hai hay nhiều dải ô và việc đo lường và thống kê tài liệu có nhiều điều kiện kèm theo. Những ví dụ dưới đây sẽ cho bạn thấy hàng loạt năng lực của hàm SUMPRODUCT và sự hữu dụng của nó sẽ sớm trở nên rõ ràng thôi .

HÀM SUMPRODUCT TRONG EXCEL – CÚ PHÁP VÀ CÔNG DỤNG:

Về mặt kỹ thuật, hàm SUMPRODUCT trong Excel nhân các con số trong mảng xác định, rồi trả về tổng của các tích số đó.

Cú pháp của hàm SUMPRODUCT rất đơn giản:

SUMPRODUCT ( array1, [ array2 ], [ array3 ], … )
Trong đó, mảng 1, mảng 2, … là các dải ô liên tục hay là các mảng có chứa thành phần bạn muốn nhân, rồi cộng lại .Số mảng tối thiểu là 1. Trong trường hợp này, hàm SUMPRODUCT trong Excel chỉ đơn thuần tính tổng các thành phần mảng rồi trả về tổng số .
Số mảng tối đa là 255 trong Excel năm nay, Excel 2013, Excel 2010, và Excel 2007, và 30 trong các phiên bản trước của Excel .
Mặc dù hàm SUMPRODUCT giải quyết và xử lý mảng, nhưng nó không nhu yếu phím tắt mảng ( Ctrl + Shift + Enter ). Bạn hoàn thành xong một công thức SUMPRODUCT theo cách thường thì bẳng cách nhấn phím Enter .

Lưu ý:

Tất cả mảng trong công thức SUMPRODUCT phải có cùng số hàng và số cột, nếu không thì bạn sẽ nhận lỗi # VALUE ! .
Nếu bất kỳ câu lệnh mảng nào chứa giá trị phi số, chúng sẽ được giải quyết và xử lý như số 0 .
Nếu mảng là phép thử lô gic, thì nó sẽ trả về giá trị TRUE và FALSE. Thông thường, bạn cần phải đổi các giá trị TRUE và FALSE này thành 1 và 0 bằng cách sử dụng toán tử đơn phân ( – ). Hãy xem qua ví dụ hàm SUMPRODUCT có nhiều điều kiện kèm theo để nắm rõ hơn .

CÁCH DÙNG CƠ BẢN CỦA HÀM SUMPRODUCT TRONG EXCEL:

Để có cái nhìn tổng quát về cách hàm SUMPRODUCT trong Excel hoạt động giải trí, hãy xem qua ví dụ sau .
Giả sử bạn có số lượng ở các ô A2 : A4, và 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ẽ nhân số lượng với giá tiền của mỗi loạ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 tác dụng chỉ với một công thức SUMPRODUCT :
= SUMPRODUCT ( A2 : A4, B2 : B4 )
Ảnh chụp màn hình hiển thị dưới đây cho thấy công thức trên trong thực tiễn :

Đây là cụ thể những gì đang diễn ra về mặt toán học :
Công thức chọn số tiên phong trong mảng tiên phong rồi nhân nó cho số tiên phong trong mảng thứ hai, rồi lấy số thứ hai trong mảng tiên phong nhân cho số thứ hai trong mảng thứ hai rồi cứ liên tục như vậy .
Khi đã nhân tất các thành phần mảng, công thức sẽ tính tổng các tích số rồi trả về tổng số .
Nói cách khác, công thức SUMPRODUCT trình diễn công thức toán học sau đây :
= A2 * B2 + A3 * B3 + A4 * B4
Hãy nghĩ đến việc bạn hoàn toàn có thể tiết kiệm chi phí bao nhiều thời hạn nếu bảng của bạn không chứa 3 hàng tài liệu, mà là 3 trăm hay 3 ngàn hàng !

CÁCH SỬ DỤNG HÀM SUMPRODUCT TRONG EXCEL – VÍ DỤ CÔNG THỨC:

Nhân hai hay nhiều dải ô với nhau rồi cộng các tích số là cách dùng đơn thuần và rõ ràng nhất của hàm SUBTOTAL trong Excel, nhưng đây không phải là cách dùng duy nhất. Vẻ đẹp thực sự của hàm SUMPRODUCT trong Excel đó là nó hoàn toàn có thể làm nhiều hơn các tính năng đã được nêu rõ. Sâu hơn trong bài hướng dẫn này, bạn sẽ thấy vài công thức cho thấy những cách dùng mê hoặc và nâng cao hơn nhiều, cho nên vì thế hãy liên tục đọc bài viết nhé !

HÀM SUMPRODUCT CÓ NHIỀU ĐIỀU KIỆN

Thông thường trong Microsoft Excel, luôn có nhiều hơn một cách để triển khai xong việc làm. Nhưng khi nói đến việc so sánh hai hay nhiều mảng, đặc biệt quan trọng là có nhiều điều kiện kèm theo, nếu không nói là duy nhất, hàm SUMPRODUCT trong Excel là hàm hiệu suất cao nhất. À thì, hàm SUMPRODUCT hay công thức mảng đều được .
Giả sử bạn có list các món hàng ở cột A, doanh thu bán dự kiến ở cột B, và doanh thu bán thực ở cột C. Mục tiêu của bạn là tìm xem có bao nhiêu có bao nhiêu món bán ít hơn dự kiến. Đối với trường hợp này, hãy sử dụng 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à doanh số thực và B2 : B10 là doanh thu dự kiến .

Nhưng nếu bạn có nhiều hơn một điều kiện kèm theo thì sao ? Giả sử bạn muốn đếm số lần Táo bán ít hơn dự kiến. Giải pháp đó là thêm một điều kiện kèm theo nữa vào công thức SUMPRODUCT :

=SUMPRODUCT(–(C2:C10 Hay, bạn hoàn toàn có thể sử dụng cú pháp sau :

=SUMPRODUCT((C2:C10

Và giờ đây, hãy dành ít phút để hiểu rõ công thức trên đang giải quyết và xử lý điều gì. Tôi tin rằng nó đáng vài phút suy ngẫm đấy do tại nhiều công thức SUMPRODUCT khác có quy luật tương tự như .

Xem thêm : Học excel ở đâu tốt

CÁCH CÔNG THỨC SUMPRODUCT CÓ MỘT ĐIỀU KIỆN HOẠT ĐỘNG:

Đối với người mới khởi đầu, hãy chia nhỏ công thức đơn thuần hơn – công thức so sánh các số lượng trong hai cột hàng theo hàng, và nói cho tất cả chúng ta biết số lần cột C ít hơn cột B :

=SUMPRODUCT(–(C2:C10

Nếu bạn chọn phần (C2:C10

Cái tất cả chúng ta có ở đây đó là giá trị hàm BOOLEAN TRUE và FALSE, trong đó, TRUE có nghĩa là đã cung ứng điều kiện kèm theo xác lập ( ví dụ, một giá trị ở cột B ít hơn một giá trị ở cột C trong cùng một hàng ), và FALSE cho thấy điều kiện kèm theo vẫn chưa được cung ứng .
Hai dấu trừ ( – ), về mặt kỹ thuật được gọi là hai toán tử đơn phân, nó ép buộc TRUE và FALSE thành 1 và 0 : { 0 ; 1 ; 0 ; 0 ; 1 ; 0 ; 1 ; 0 ; 0 } .
Cách khác để đổi giá trị lôgic thành giá trị số đó là nhân mảng với 1 :

=SUMPRODUCT((C2:C10
Cách nào cũng được, vì chỉ có một mảng trong công thức SUMPRODUCT, nên nó chỉ nhân 1 trong mảng cho hiệu quả và tất cả chúng ta nhận được tác dụng đếm mong ước. Rất dễ, đúng không nào ?

CÁCH CÔNG THỨC SUMPRODUCT CÓ NHIỀU ĐIỀU KIỆN HOẠT ĐỘNG:

Khi công thức SUMPRODUCT trong Excel chứa hai hay nhiều mảng, nó nhân các thành phần của mảng, rồi cộng các tích số .
Có lẽ bạn vẫn còn nhớ, tất cả chúng ta đã sử dụng công thức dưới đây để đếm số lần doanh số thực ( cột C ) ít hơn doanh thu dự kiến ( cột B ) so với Táo ( cột A ) :

=SUMPRODUCT(–(C2:C10 hay

=SUMPRODUCT((C2:C10
Điểm độc lạ duy nhất về mặt kỹ thuật giữa hai công thức đó là giải pháp ép buộc TRUE và FALSE thành 1 và 0 – bằng cách sử dụng hai toán tử đơn phân hay phép nhân. Kết quả là, tất cả chúng ta nhận được hai mảng 1 và 0 :

Phép nhân triển khai bởi hàm SUMPRODUCT tích hợp chúng thành một mảng riêng. Và vì nhân 0 thì bằng 0, nên 1 chỉ Open khi cả hai điều kiện kèm theo được cung ứng, và do đó chỉ những hàng đó mới được đếm :

ĐẾM/TÍNH TỔNG/TÍNH TRUNG BÌNH CÓ ĐIỀU KIỆN CÁC Ô VỚI NHIỀU TIÊU CHUẨN

Trong Excel 2003 và các phiên bản trước thì không có hàm IFS, một trong những cách dùng thông dụng nhất của hàm SUMPRODUCT đó là tính tổng hay đếm có điều kiện kèm theo các ô với nhiều tiêu chuẩn. Bắt đầu với Excel 2007, Microsoft trình làng một chuỗi hàm được đặc biệt quan trọng phong cách thiết kế cho những trách nhiệm này – hàm SUMIFS, hàm COUNTIFS và hàm AVERAGEIFS .
Nhưng thậm chí còn ở các phiên bản tân tiến của Excel, công thức SUMPRODUCT trọn vẹn hoàn toàn có thể là một lựa chọn thay thế sửa chữa, ví dụ, tính tổng và đếm có điều kiện kèm theo các ô có hàm lôgic OR. Dưới đây, bạn sẽ thấy vài ví dụ công thức cho thấy năng lực này .

CÔNG THỨC SUMPRODUCT VỚI HÀM LÔGIC AND:

Giả sử bạn có chuỗi tài liệu dưới đây, trong đó cột A liệt kê vùng miền, cột B – món hàng và cột C – doanh thu bán ra :

Điều bạn muốn triển khai đó là đếm, tính tổng và tính trung bình cộng doanh thu táo bán ra ở khu vực phía Bắc .
Trong các phiên bản Excel gần đây : Excel năm nay, 2013, 2010 và 2007, bạn hoàn toàn có thể thuận tiện triển khai xong trách nhiệm bằng cách sử dụng công thức SUMIFS, COUNTIFS và AVERAGEIFS. Nếu bạn đang tìm một hướng đi khó khăn vất vả hơn, hay nếu bạn vẫn sử dụng Excel 2003 hay phiên bản cũ hơn, thì bạn vẫn đạt được hiệu quả mong ước với hàm SUMPRODUCT trong Excel .
Để đếm doanh thu táo bán ra ở khu vực phía Bắc :
= SUMPRODUCT ( – ( A2 : A12 = ” khu vực phía Bắc ” ), – ( B2 : B12 = ” táo ” ) )
hay
= SUMPRODUCT ( ( A2 : A12 = ” khu vực phía Bắc ” ) * ( B2 : B12 = ” táo ” ) )
Để tính tổng doanh thu táo bán ra ở khu vực phía Bắc :
= SUMPRODUCT ( – ( A2 : A12 = ” khu vực phía Bắc ” ), – ( B2 : B12 = ” táo ” ), C2 : C12 )
hay
= SUMPRODUCT ( ( A2 : A12 = ” khu vực phía Bắc ” ) * ( B2 : B12 = ” táo ” ) * C2 : C12 )

Để tính trung bình cộng doanh số táo bán ra ở khu vực phía Bắc:

Để tính trung bình cộng, tất cả chúng ta chỉ chia tổng được cho phép đếm như thế này :
= SUMPRODUCT ( – ( A2 : A12 = ” khu vực phía Bắc ” ), – ( B2 : B12 = ” táo ” ), C2 : C12 ) / SUMPRODUCT ( – ( A2 : A12 = ” khu vực phía Bắc ” ), – ( B2 : B12 = ” táo ” ) )
Để công thức SUMPRODUCT linh động hơn, bạn hoàn toàn có thể định rõ khu vực và món hàng mong ước ở các ô riêng, rồi tham chiếu các ô đó vào công thức giống như ảnh chụp màn hình hiển thị dưới đây :

CÔNG THỨC SUMPRODUCT DÙNG CHO VIỆC TÍNH TỔNG CÓ ĐIỀU KIỆN HOẠT ĐỘNG NHƯ THẾ NÀO

Từ ví dụ trước, bạn đã biết cách công thức SUMPRODUCT trong Excel đếm số ô cùng với hiều điều kiện kèm theo. Nếu bạn đã hiểu rõ cách đó, sẽ rất thuận tiện để bạn nắm rõ quy luật tính tổng .
Để tôi nhắc bạn nhớ rằng tất cả chúng ta đã dùng công thức dưới đây để tính tổng doanh thu Táo ở khu vực phía Bắc :
= SUMPRODUCT ( – ( A2 : A12 = ” khu vực phía Bắc ” ), – ( B2 : B12 = ” táo ” ), C2 : C12 )
Công thức trực tiếp của công thức trên chính là 3 mảng dưới đây :

Trong mảng tiên phong, 1 đại diện thay mặt cho khu vực phía Bắc, và 0 đại diện thay mặt cho các khu vực khác .
Trong mảng thứ hai, 1 đại diện thay mặt cho Táo, và 0 đại diện thay mặt cho các món hàng khác .
Mảng thứ ba chứa chính các số lượng Open từ ô C2 đến C12 .
Hãy nhớ rằng nhân 0 thì luôn bằng 0, và nhân 1 thì bằng chính nó, tất cả chúng ta có mảng ở đầu cuối chứa doanh thu và những số lượng 0 – doanh thu chỉ Open nếu hai mảng tiên phong có số 1 ở cùng một vị trí, đơn cử là cả hai điều kiện kèm theo xác lập đều được phân phối ; còn không thì sẽ hiển thị số 0 :

Cộng các số lượng trong mảng trên cho hiệu quả mong ước – doanh thu Táo bán ra ở khu vực phía Bắc .

VÍ DỤ 2. CÔNG THỨC SUMPRODUCT VỚI HÀM LÔGIC OR

Để công hay đếm có điều kiện kèm theo các ô sử dụng hàm lôgic OR, hãy sử dụng dấu cộng ( + ) giữa các mảng .
Trong công thức Excel SUMPRODUCT, cũng như trong công thức mảng, dấu cộng đóng vai trò làm toán tử OR hướng dẫn Excel trả về giá trị TRUE nếu bất kể điều kiện kèm theo nào trong biểu thức xác lập được phân phối .
Ví dụ, để đếm tổng doanh thu Táo và Chanh không phân biệt vùng miền, hãy sử dụng công thức này :
= SUMPRODUCT ( ( B2 : B12 = ” táo ” ) + ( B2 : B12 = ” chanh ” ) )
Nói đơn thuần, công thức trên có nghĩa là : Hãy đếm số ô nếu dải ô B2 : B12 = ” táo ” OR B2 : B12 = ” chanh ” .
Để tính tổng doanh thu Táo và Chanh, hãy thêm một câu lệnh chứa dải ô Doanh số :
= SUMPRODUCT ( ( B2 : B12 = ” táo ” ) + ( B2 : B12 = ” chanh ” ), C2 : C12 )
Ảnh chụp màn hình hiển thị dưới đây diễn giải công thức tựa như :

VÍ DỤ 3. CÔNG THỨC SUMPRODUCT CÙNG VỚI HÀM LÔGIC AND VÀ HÀM LÔGIC OR

Trong nhiều trường hợp, hoàn toàn có thể bạn cần phải đếm hay tính tổng có điều kiện kèm theo sử dụng cả hàm lôgic AND và OR. Thậm chí ở phiên bản Excel mới nhất, chuỗi hàm tương quan đến hàm IF cũng không hề làm được điều đó .
Một trong những giải pháp khả thi đó là tích hợp hai hay nhiều hàm SUMIFS + SUMIFS hay COUNTIFS + COUNTIFS .
Cách khác đó là sử dụng hàm SUMPRODUCT trong đó :
Dấu hoa thị ( * ) được dùng như toán tử AND .
Dấu cộng ( + ) được dùng như toán tử OR .
Để khiến mọi thứ đơn thuần hơn, hãy xem xét các ví dụ dưới đây :
Để đếm số lần Táo và Chanh được bán ở khu vực phía Bắc, hãy lập công thức với hàm lôgic sau :
= Count If ( ( Vùng miền = ” miền Bắc ” ) AND ( ( Món hàng = ” Táo ” ) OR ( Món hàng = ” Chanh ” ) ) )
Khi vừa sử dụng theo cú pháp hàm SUMPRODUCT, công thức sẽ có dạng như sau :
= SUMPRODUCT ( ( A2 : A12 = ” miền Bắc ” ) * ( ( B2 : B12 = ” táo ” ) + ( B2 : B12 = ” chanh ” ) ) )
Để tính tổng doanh thu Táo và Chanh bán ra ở khu vục phía Bắc, hãy dùng công thức trên rồi thêm dải ô Doanh số cùng với hàm lôgic AND :
= SUMPRODUCT ( ( A2 : A12 = ” miền Bắc ” ) * ( ( B2 : B12 = ” táo ” ) + ( B2 : B12 = ” chanh ” ) ) * C2 : C12 )
Để công thức trông gọn hơn, bạn hoàn toàn có thể gõ các biến vào các ô riêng không liên quan gì đến nhau – Khu vực ở ô F1 và Món hàng ở ô F2 và H2 – rồi tham chiếu các ô này vào công thức :

CÔNG THỨC SUMPRODUCT DÙNG ĐỂ TÍNH TRUNG BÌNH CÓ TRỌNG LƯỢNG

Ở một trong những ví dụ trước, tất cả chúng ta đã bàn về công thức SUMPRODUCT dùng để tính trung bình có điều kiện kèm theo. Cách dùng phổ cập khác của hàm SUMPRODUCT trong Excel đó là tính trung bình có khối lượng trong đó mỗi giá trị có một khối lượng nhất định .
Công thức SUMPRODUCT tính trung bình có khối lượng tổng quát có dạng như sau :
SUMPRODUCT ( values, weights ) / SUM ( weights )
Giả sử các giá trị nằm ở dải ô B2 : B7 và khối lượng ở dải ô C2 : C7, công thức SUMPRODUCT tính trung bình có khối lượng có dạng như sau :
= SUMPRODUCT ( B2 : B7, C2 : C7 ) / SUM ( C2 : C7 )

Tôi tin rằng ngay giờ đây, bạn sẽ không gặp khó khăn vất vả trong việc hiểu quy luật công thức nữa .

HÀM SUMPRODUCT ĐƯỢC DÙNG LÀM HÀM THAY THẾ CHO CÔNG THỨC MẢNG

Ngay cả khi bạn đọc bài viết này chỉ để có thêm thông tin và bạn không còn nhớ rõ các chi tiết cụ thể nữa, hãy nhớ một điểm mấu chốt quan trọng – hàm SUMPRODUCT trong Excel giải quyết và xử lý mảng. Và vì hàm SUMPRODUCT có các năng lực của công thức mảng, nên nó hoàn toàn có thể trở thành hàm thay thế sửa chữa dễ sử dụng cho công thức mảng .
Ưu điểm là gì ? Về mặt cơ bản, bạn có năng lực quản trị công thức một cách thuận tiện mà không cần phải nhấp Ctrl + Shift + Enter mỗi lần bạn nhập một công thức mảng mới hay chỉnh sửa công thức mảng .
Ví dụ, tất cả chúng ta hoàn toàn có thể sử dụng một công thức mảng đơn thuần để đếm số ký tự trong dải ô xác lập :
{ = SUM ( LEN ( range ) ) }
và biến nó thành công thức thường thì :
= SUMPRODUCT ( LEN ( range ) )

Để thực hành thực tế, bạn hoàn toàn có thể chọn các công thức mảng này trong Excel rồi nỗ lực viết lại sử dụng hàm SUMPRODUCT .

MOD và SUMPRODUCT trong Excel đếm ô chứa số lẻ

Ta đều đã biết số lẻ là những số chia 2 dư 1. Trong trường hợp này, ta sẽ tích hợp 2 hàm là MOD và SUMPRODUCT để thực thi đếm những ô chứa số lẻ trong Excel .
Dữ liệu mẫu của ta như sau :

Yêu cầu : Đếm những ô chứa số lẻ .
Công thức hàm SUMPRODUCT excel trong trường hợp này của ta như sau :

=SUMPRODUCT(–(MOD(A1:A15,2)=1))

Công thức (MOD(A1:A15,2)=1) sẽ trả về giá trị dạng mảng {TRUE; FALSE} tương ứng: TRUE nếu ô không chia hết cho 2 và ngược lại. — biến TRUE thành 1, FALSE thành 0. Cuối cùng ta lồng vào SUMPRODUCT trả về kết quả là số ô chứa giá trị số lẻ.

Các ứng dụng thường gặp, và các hàm thường dùng với SUMPRODUCT.

• Các hàm kiểm tra logic: ISNUMBER/ISERROR + SEARCH

  • Hàm SEARCH giúp bạn tìm kiếm điều kiện có trong vùng tìm kiếm hay không? Nếu tìm thấy sẽ trả về số của vị trí tìm thấy, còn không tìm thấy hàm sẽ trả về #VALUE. Lúc này để tránh lỗi, ta dùng hàm ISNUMBER để bỏ qua lỗi, chỉ tính cái tìm thấy.

    Ví dụ, ta tính tổng số lượng sản phẩm chứa chữ “á“, trong ví dụ bạn lưu ý khác biệt giữa dấu * với dấu phẩy (,).

  • Hàm SEARCH giúp chúng ta tìm kiếm không phân biệt chữ Hoa/thường. Nếu bạn cần tìm chữ giống nhau thì lúc này có hàm FIND.

• Tính tổng tất cả các sheet với SUMIFS

  • Khi bạn có các sheet khác nhau đồng nhất về cấu trúc, ví dụ bảng chấm công từng tháng trong năm. Bạn làm báo cáo tổng kết cuối năm, lúc này hàm SUMPRODUCT+SUMIFS+INDIRECT sẽ giúp bạn làm việc này.
    Hoặc bạn có danh sách sản phẩm từng tháng, bạn cần tính số lượng tương ứng. Lúc này bạn chỉ cần liệt kê danh sách tên các sheet, và vùng dữ liệu tương ứng. Vậy là ta có được tính tổng một cách nhanh chóng.
• Tách chuỗi ra để lấy số liệu tính toán:
  • Bài toán đặt ra bạn có chuỗi ký tự: u, x, 3g, nhuộm. Và làm thế nào để tách ra theo các đơn vị tính tương ứng.
  • Vận dụng kết hợp hàm TEXT để chuyển đổi các số, chữ, và số 0 thành con số tương ứng để tính toán.

• Chuyển chuỗi ký tự biểu thức cộng trừ thành kết quả tính toán:
  • Bước 1: Chúng ta sẽ tìm thay thế ký tự “-” thành “+-“.
  • Bước 2: Chúng ta sẽ tìm thay thế ký tự “+” với hàm REPT(” “,99) nhằm chèn thay thế 99 ký tự ” “.
  • Bước 3: Hàm MID sẽ tách các phần ra giữa 99 ký tự, bạn có thể tìm hiểu thêm qua bài viết SUBSTITUTE và 40 tên cướp.
  • Bước 4: Khác với hàm TRIM, chúng ta sẽ dùng hàm TEXT nhận dạng ký tự.
  • Bước 5: Với hàm SUMPRODUCT kết hợp dấu -- (dùng để chuyển chữ thành số), sau đó cộng dồn lại với nhau.
  • Kết quả:=SUMPRODUCT(--TEXT(MID(SUBSTITUTE(SUBSTITUTE(A2,"-","+-"),"+",REPT(" ",99)),ROW(INDIRECT("1:"&LEN(A2)))*99-98,99),"0.00;-0.00;0;\0"))

• Đếm duy nhất với SUMPRODUCT:

• Xem thêm: Cách dùng hàm COUNTIF và COUNTIFS với nhiều điều kiện

  Tính số giờ đi muộn, về sớm, giờ làm của nhân viên với hàm SUMPRODUCT.

  • Ta quy ước giờ đi muộn vào ô BM1, là 8:00, nếu đi sau giờ đó tính là muộn.
  • SUMPRODUCT lúc này lấy giờ checkin, trừ cho giờ quy định.
  • Để quy ra số phút, ta cần nhân cho 24 giờ x 60 phút. Vì thời gian trong Excel là một con số thập phân từ 0 -> 1.

• Ứng dụng SUMPRODUCT trong việc chấm công, tính công cho nhân viên.

  • Bạn có thể lập bảng ký hiệu, và quy đổi ký hiệu đó sang ngày công tương ứng. Rồi từ đó bạn hoàn toàn có thể tính ngày công.
  • Ví dụ như sau, ta thấy có 4x = 4 ngày công, 1np = 1 ngày công, x2 = 2 ngày công. Kết quả 7 là chính xác.
  • Ứng dụng chấm công theo giờ, tính tăng ca ngoài giờ và Chủ nhật.
  • Ứng dụng SUMPRODUCT kết hợp SUBTOTAL để tính toán bỏ qua các dòng ẩn, cột ẩn:
    Bài toán đặt ra: Sẽ tính tổng số nhân công theo từng ngày, và họ có thể nghỉ lễ nhưng vẫn đi làm nên cần tính công từng ngày.

Để hoàn toàn có thể ứng dụng tốt Excel vào trong việc làm, tất cả chúng ta không chỉ nắm vững được các hàm mà còn phải sử dụng tốt cả các công cụ của Excel. Những hàm nâng cao giúp vận dụng tốt vào việc làm như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH … Những công cụ thường sử dụng là Data validation, Conditional formatting, Pivot table …
Toàn bộ những kiến thức và kỹ năng này các bạn đều hoàn toàn có thể học được trong khóa học Excel từ cơ bản đến chuyên viên dành cho người đi làm

Source: https://tbdn.com.vn
Category: Tin Học