CÁCH SỬ DỤNG HÀM TIME VÀ HÀM SUMPRODUCT NÂNG CAO TRONG EXCEL

Hàm TIME trong Excel là một hàm tích hợp, cho phép bạn thiết lập thời gian với các thành phần giờ, phút và giây đơn lẻ. Hàm TIME hữu ích khi bạn muốn tập hợp một thời gian thích hợp bên trong một công thức khác. Ngoài ra, có thể bạn chưa biết, hàm SUMPRODUCT trả về tổng các tích của các dải hoặc vùng dữ liệu tương ứng. Phép toán mặc định là phép nhân, nhưng cũng có thể cộng, trừ và chia. Trong bài viết dưới đây, Gitiho sẽ giới thiệu tới các bạn cách sử dụng hàm TIME và hàm SUMPRODUCT nâng cao.

Xem thêm: Cách viết hàm trong Excel chính xác và chi tiết nhất

Hàm TIME trong Excel

Hàm TIME

Kết quả của hàm TIME là một số thập phân của một giá trị thời gian cụ thể trong khoảng từ 0 đến nhỏ hơn 1. Nếu định dạng của ô là ''thời gian'' thì kết quả sẽ được trả về dưới dạng giờ phút giây.

Công thức của hàm là: =TIME(hour,minute,second)

Trong đó:

• hour: Là giá trị giờ
• minute: Là giá trị phút
• second: Là giá trị giây

Ví dụ: Trong hình dưới đây, Gitiho có sử dụng công thức: ''=TIME(16;40;50)" để thể hiện giá trị thời gian 16:40:50.

Hàm NOW

Kết quả của hàm NOW là ngày tháng năm và giờ phút hiện tại của hệ thống.

Công thức hàm là: =NOW()

Lưu ý: Nếu bạn nhập công thức ''=NOW()-1'' thì hàm sẽ trả kết quả là ngày tháng năm và giờ phút giảm đi 1 ngày so với ngày, giờ hiện tại của hệ thống. 

Ví dụ: Trong hình dưới đây, Gitiho có sử dụng công thức là: ''=NOW()'' và ''=NOW()-1'' để tra ngày hôm nay và ngày hôm trước như sau:

Xem thêm: Hướng dẫn cách dùng hàm NOW TODAY TIME trong VBA Excel

Hàm HOUR, MINUTE, SECOND

Kết quả của các hàm HOUR, MINUTE và SECOND là giá trị giờ, phút, giây tương ứng của một thời gian.

Công thức hàm là: 

• Hàm HOUR: =HOUR(serial_number)
• Hàm MINUTE: =MINUTE(serial_number)
• Hàm SECOND: =SECOND(Serial_number)

Trong đó, serial_number là thời gian các bạn muốn chuyển đổi thành giờ, phút, giây tương ứng, là tham số bắt buộc.

Xem thêm: Hướng dẫn cách dùng hàm HOUR trong Power BI DAX

Ví dụ: Trong hình dưới đây, Gitiho có sử dụng 3 hàm trên để xác định chính xác giờ, phút, giây của thời gian 16:40:50

Hàm SUMPRODUCT nâng cao trong Excel

Hãy nhìn vào ví dụ sau: 

Yêu cầu: Hãy tính tổng số tiền nhận được từ 4 sản phẩm là Cam, Lê, Táo và Cam.

Các làm thông thường: Bạn sẽ tính số tiền của mỗi sản phẩm bằng cách lấy Đơn giá x Thành tiền, hay nói cách khác là tích của từng đối tượng (với Cam là =F6*G6). Sau đó, cộng tổng các số tiền của từng đối tượng đó bằng hàm SUM (tổng các tích)

Cách tính trên dường như khá phức tạp và mất nhiều thời gian. Hãy xem cách dưới đây

Cách tính với SUMPRODUCT: Bạn có thể sử dụng hàm SUMPRODUCT để đơn giản hóa phép tính, chỉ với 2 mảng sau:

• Số lượng là vùng F6:F9
• Đơn giá là vùng G6:G9
• Hai mảng này nhân với nhau, cho bạn kết quả như mong muốn.

Trên đây chỉ là nguyên tắc hoạt động cơ bản của hàm SUMPRODUCT, hãy cùng đọc tiếp để biết các ứng dụng nâng cao nhé.

Xem thêm: Cách dùng hàm SUMPRODUCT để tính trung bình cộng trong Excel

Lồng hàm SUMPRODUCT với hàm khác để thay đổi giá trị của mảng dữ liệu cần tính

Ta có ví dụ minh họa sau:

Yêu cầu:  ''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 chỉ có cột Ngày bán, không có sẵn cột Tháng''. Để tính được, chúng ta phải tách được Tháng trong cột Ngày.

Các làm thông thường: Bạn cần dùng hàm IF để xét xem tháng của từng ngày bán có phải là 3 không. Nếu đúng thì sẽ lấy số lượng nhân đơn giá, còn không thì trả kết quả là số 0. Sau đó dùng hàm SUM để cộng tổng lại các kết quả thu được từ hàm IF trên từng dòng.

Cách tính với SUMPRODUCT: Nếu dùng hàm SUMPRODUCT bạn chỉ cần 1 hàm là cho ngay kết quả, trong công thức này xét 3 mảng:
 

• Mảng Ngày bán: B2:B5 sẽ kết hợp hàm MONTH cho mảng này để có thể đưa về giá trị số tháng, từ đó so sánh với số 3
• Mảng Số lượng: C2:C5
• Mảng Đơn giá: D2:D5

Nhân 3 mảng này lại với nhau, bạn sẽ thu được kết quả như mong muốn.

Tính toán cho bảng dữ liệu 2 chiều

Cùng xét ví dụ này nhé:

 

Yêu cầu: Xác định đơn giá của 1 mặt hàng gồm 2 điều kiện: Mã hàng và Mã công ty.

Trong đó, bảng chứa thông tin cần tìm (Bảng đơn giá) là bảng 2 chiều với:

• Chiều dọc là thông tin mã Công ty
• Chiều ngang là thông tin mã hàng

Giao điểm của các dòng, các cột trong bảng này chính là đơn giá cần tìm của sản phẩm.

Cách tính: Khi đó chúng ta sử dụng hàm SUMPRODUCT bằng cách nhập công thức sau vào ô G3:

=SUMPRODUCT(($B$14:$B$17=C3)*($C$13:$F$13=B3)*$C$14:$F$17) 

Công thức có nghĩa là:

• Xét mảng Mã công ty (B14:B17) có chứa mã công ty tại ô C3 không
• Xét mảng Mã hàng (C13:F13) có chứa mã hàng tại ô B3 không

Nếu chứa kết quả đúng ở cả 2 mảng trên, thì sẽ lấy tương ứng theo đơn giá trong vùng C14:F17 theo dòng và cột chứa các mã đó. Và bạn sẽ được kết quả như ý.

Tổng kết

Trong bài viết trên, Gitiho đã giới thiệu đến các bạn cách sử dụng các hàm để trích xuất dữ liệu thời gian như hàm TIME, hàm NOW... và ứng dụng hàm SUMPRODUCT nâng cao để kết hợp tính toán các dữ liệu có điều kiện. 

Hy vọng qua bài viết trên, Gitiho đã giúp bạn hiểu cách sử dụng và ứng dụng 2 hàm này. Tuy nhiên để thành thạo và có thể làm việc nhanh gọn trên Excel, bạn đọc cần đọc các bài viết về hàm VLOOKUP hay hàm IF, AND,.. cũng như luyện tập các bài tập Excel kèm đáp án từ Gitiho nhé.