Tự Học Excel Bài 08: Hàm Index, Match, Sumproduct, Thời Gian

Video hướng dẫn

Xem bài học trước

>> Tự Học Excel Bài 01: Các Thao Tác Với Excel

>> Tự Học Excel Bài 02: Hướng Dẫn Nhập Liệu Và Tìm Hiểu Các Thẻ

>> Tự Học Excel Bài 03: Các Hàm Tính Tổng SUM SUMIF SUMIFs

>> Tự Học Excel Bài 04: Các Hàm ĐẾM (Count, CountA, CountIFs,...)

>> Tự Học Excel Bài 05: Hàm Điều Kiện IF

>> Tự Học Excel Bài 06: Toàn Bộ 18 Hàm Xử Lý Chuỗi

>> Tự Học Excel Bài 07: Hàm VLOOKUP Và HLOOKUP

>> Tự Học Excel Bài 09: Kỹ Năng Sort, Filter Và Advanced Filter

>> Tự Học Excel Bài 10: Kỹ Năng Định Dạng Cell (Format Cells)

>> Tự Học Excel Bài 11: Định Dạng Có Điều Kiện

>> Tự Học Excel Bài 12: Paste Special

>> Tự Học Excel Bài 13: Flash Fill, Quick Analysis, Freeze Pane, Remove Duplicate

>> Tự Học Excel Bài 14: Các Thủ Thuật Excel Hay (1 of 4)

>> Tự Học Excel Bài 15: Các Thủ Thuật Excel Hay (2 of 4)

>> Tự Học Excel Bài 16: Các Thủ Thuật Excel Hay (3 Of 4)

>> Tự Học Excel Bài 17: Bảo Vệ Dữ Liệu, Khóa Dữ Liệu Trong Excel

>> Tự Học Excel Bài 18: Hướng Dẫn Vẽ Biểu Đồ Chi Tiết

>> Tự Học Excel: Bài 19 Toàn Bộ Về In Ấn (18 Thủ Thuật)

>> Tự Học Excel: Bài 20 Pivottable Trong Excel

>> Tự Học Excel: Bài 21 Công Thức Mảng (10 Ví Dụ Mẫu)

>> Tự Học Excel: Bài 22 Hướng Dẫn Tạo Báo Cáo Động (Dashboard)

>> Tự Học Excel: Bài 23 Các Phím Tắt Excel Thông Dụng Nhất

Hàm Sumproduct

Giả sử mình có một bảng số liệu cần tính tổng thành tiền của một sản phẩm nào đó. Cách giải quyết thông thường bạn sẽ lấy số lượng nhân cho đơn giá sau đó cộng tất cả lại để có được tổng thành tiền. Ví dụ về cách tính thông thường của đa số người dùng như hình dưới đây. 

Tuy nhiên có một cách tính tổng thành tiền nhanh hơn, tiết kiệm thời gian nhiều hơn khi bạn áp dụng hàm Sumproduct vào trong trường hợp này. Bạn sẽ nhập như sau để tính tổng thành tiền =SUMPRODUCT(C4:C15,D4:D15). Hàm Sumproduct bao gồm các mảng array1, [array2], [array3],... trong công thức trên mảng 1 chính là từ C4 đến C15 và mảng 2 là từ D4 đến D15. Hàm sẽ lấy C4 nhân D4, C5 nhân D5 cho đến khi C15 nhân D15 thì cộng tất cả lại để được tổng thành tiền.

Một trường hợp khác mà hàm Sumproduct giải quyết rất tốt trong bảng dữ liệu này đó là chỉ tính tổng một sản phẩm nhất định. Ví dụ mình chỉ muốn tính tổng thành tiền của sản phẩm 1 (G8) thì chỉ cần nhập vào công thức sau =SUMPRODUCT((B4:B15=G8)*1,C4:C15,D4:D15). Do hàm Sumproduct cho phép tính tổng rất nhiều mảng nên mình sẽ dựa vào điểm này để tính. Đầu tiên sẽ kiểm tra điều kiện ở mảng Sản Phẩm nếu giá trị nào bằng G8 (Sản phẩm 1) thì sẽ trả về là true, thực hiện nhân cho 1 để đổi kiểu dữ liệu từ true/false sang kiểu số. Sau khi duyệt điều kiện hoàn tất thì hàm Sumproduct chỉ thực hiện nhân các giá trị liên quan để trả về tổng thành tiền của Sản phẩm 1.

Hàm Thời Gian

Giả sử trong trường hợp bạn có một mốc thời gian cụ thể của một nhân viên bao gồm ngày, tháng và năm. Để biết được nhân viên đó đã làm được bao nhiêu ngày trong công ty thì bạn cần làm như sau. Đầu tiên dùng hàm TODAY() để có được ngày hiện tại cú pháp nhập bao gồm =TODAY().

Để tính số ngày làm việc không bao gồm thứ bảy và chủ nhật thì bạn nhập vào công thức như sau: =NETWORKDAYS(C6, D6). Hàm NETWORKDAYS sẽ tính tất cả các ngày làm việc từ khi bắt đầu làm việc (C6) đến thời điểm hiện tại (D6), tự động loại bỏ các ngày thuộc thứ bảy và chủ nhật.

Để biết được chính xác hơn về số năm và tháng mà nhân viên đã làm việc trong công ty, từ đó tính bảo hiểm hoặc các vấn đề pháp lý khác thì bạn áp dụng hàm DATEDIF(). Cú pháp để tính số năm và tháng làm việc như sau: =DATEDIF(C6,D6,"Y")&" Năm "&DATEDIF(C6,D6,"YM")&" Tháng".

Công thức trên có hai hàm DATEDIF, hàm đầu tiên là dùng để tính số năm làm việc được xác định dựa vào điều kiện “Y” giữa hai mốc thời gian. Hàm thứ hai là dùng để tính tháng làm việc dư ra mà chưa tròn 1 năm với điều kiện “YM”. Hàm trên chỉ nối chuỗi lại với nhau mà không dùng bất kỳ toán tử nào nên bạn cần lưu ý khi thực hiện.

Hàm INDEX và hàm MATCH

Hai hàm cuối cùng là sự kết hợp với nhau để tạo nên một công cụ tìm kiếm dữ liệu hiệu quả. Ví dụ mình có bốn trường là Nơi Sinh, Họ Tên, Điện Thoại, Ngày Tuyển Dụng và Lương. Để tìm kiếm Nơi sinh của “Nhân Viên 1” thì bạn nhập vào công thức sau: =INDEX(B3:F8,2,1). Các tham số của hàm INDEX bao gồm vùng dữ liệu (B3:F8), hàng trong mảng được chỉ định trả về một giá trị (Nhân Viên 1 đang ở hàng thứ hai nên điền 2) và cột trong mảng trả về giá trị (do cần tìm Nơi sinh ở cột thứ 1 nên mình điền 1).

Để bạn không cần phải tính toán các hàng hay cột ở công thức trên thì ta cần học thêm hàm MATCH. Cú pháp của hàm này sẽ là MATCH(giá trị tìm kiếm, vùng tìm kiếm, [kiểu trả về]). Trong đó kiểu trả về thường là 0 (tìm kiếm chính xác), mặc định khi không nhập sẽ là 1. Mình sẽ áp dụng hàm MATCH để tìm vị trí hàng của “Nhân Viên 1”, bạn nhập vào công thức như sau: =MATCH(B14,C3:C8,0). Bên trong hàm MATCH, B14 là ô giá trị của Nhân Viên 1, C3:C8 là cột Họ Tên và 0 là tìm chính xác.

Cuối cùng là kết hợp hai hàm này lại với nhau để tạo nên một công cụ tìm kiếm hiệu quả, công thức sẽ là: =INDEX(B3:F8,MATCH(B14,C3:C8,0),1). Bạn chỉ cần copy công thức ở hàm MATCH và dán vào công thức hàm INDEX là hoàn tất. Hãy thử so sánh hai kết quả khi kết hợp hai hàm này lại với nhau nhé.

Hai hàm INDEX và MATCH sẽ khắc phục được nhược điểm của hàm VLOOKUP khi hàm này chỉ tìm kiếm được giá trị nằm ở bên phải của giá trị dò tìm. Mình đã có bài viết về cách sử dụng về hàm VLOOKUP, bạn có thể tham khảo thêm để tìm kiếm dữ liệu tốt hơn nhé.

Như vậy mình đã hướng dẫn các bạn sử dụng 4 hàm thông dụng là SUMPRODUCT, thời gian, INDEX và MATCH. Cả bốn hàm này sẽ giúp bạn tiết kiệm rất nhiều thời gian khi cần phải tính toán hay thống kế ở một vùng dữ liệu khổng lồ. Nếu muốn học thêm các bài học khác về Excel thì đừng ngài tìm kiếm trên website này nhé.