Cách lọc lấy năm sinh trong excel

Hàm YEAR trong Excel là một hàm cơ bản giúp bạn xác định số năm trong 1 giá trị thời gian. Trong bài viết này chúng ta cùng tìm hiểu cấu trúc của hàm YEAR cũng như cách dùng hàm YEAR trong Excel để lập báo cáo theo năm nhé.

Cấu trúc của hàm YEAR

Giả sử tại ô A1 bạn có 1 giá trị thời gian gồm Ngày/Tháng/Năm.

Muốn nhận biết giá trị Năm trong ô A1 là năm bao nhiêu, chúng ta dùng hàm YEAR như sau:

Ví dụ về cách sử dụng hàm YEAR như sau:

Cách dùng hàm YEAR để lập báo cáo theo năm

Hãy xét 1 ví dụ về yêu cầu báo cáo theo năm như sau:

Trong bảng báo cáo doanh thu A2:C11, chúng ta có thống kê chi tiết sốốtiền doanh thu theo từng ngày. Nhưng không có sẵn dữ liệu Năm.

Yêu cầu báo cáo lại là báo cáo doanh thu theo năm, cụ thể gồm năm 2018 và 2019.

Cách thực hiện như sau:

Cách 1: Sử dụng cột phụ tạo giá trị Năm và kết hợp với hàm SUMIF

Vì điều kiện của yêu cầu là tính theo số Năm, do đó chúng ta không thể tính trực tiếp trên dữ liệu ở cột A là dữ liệu Ngày/tháng/năm. Cần phải tách giá trị số Năm trong mỗi giá trị Ngày tại cột A thì mới có thể tính toán được.

Do đó tại cột D, chúng ta dùng hàm YEAR như sau:

=YEAR[A3]

• Bắt đầu từ ô D3, sử dụng hàm YEAR tham chiếu tới ô A3 để tính ra số năm trong ô A3. Kết quả là năm 2018
• Tiếp theo chúng ta Filldown công thức từ ô D3 tới ô D11
• Sau khi có kết quả trong cột D, dùng hàm SUMIF như sau:

Như vậy chúng ta đã tính ra được kết quả doanh thu của năm 2018 bằng công thức:

=SUMIF[D3:D11,E2,C3:C11]

Tương tự tính cho năm 2019 như sau:

=SUMIF[D3:D11,E3,C3:C11]

Như vậy chúng ta đã có thể tính ra kết quả đúng trong trường hợp này rồi.

Cách 2: Sử dụng hàm SUMPRODUCT kết hợp hàm YEAR

Chúng ta có thể sử dụng hàm SUMPRODUCT để tính một cách trực tiếp mà không cần tới cột phụ [cột D]. Cách làm như sau:

=SUMPRODUCT[[YEAR[A3:A11]=G2]*C3:C11]

Hàm YEAR[A3:A11] được hiểu là xét giá trị năm của từng ô trong vùng A3:A11. Khi viết YEAR[A3:A11] là dùng hàm xác định năm cho 1 mảng. Cách viết này chỉ áp dụng được khi đặt bên trong hàm SUMPRODUCT.

Với các giá trị Năm = giá trị ô E2 [là 2018] thì sẽ lấy kết quả theo dòng tương ứng trong cột Số tiền.

Tổng tất cả các giá trị thỏa mãn điều kiện là kết quả cần tìm.

Như vậy khi điều kiện cần tính không có sẵn, chúng ta có thể sử dụng các hàm như hàm YEAR để gọi ra các giá trị đó. Sau đó chỉ cần kết hợp trong các hàm tính toán theo điều kiện như SUMIF, COUNTIF, SUMPRODUCT là chúng ta đã có thể tính ra đúng kết quả theo yêu cầu.

Các bạn có thể xem thêm các bài viết sau để hiểu rõ hơn cách sử dụng các hàm SUMIF, COUNTIF, SUMPRODUCT:

Hướng dẫn sử dụng hàm SUMPRODUCT nâng cao trong Excel

Cách lập báo cáo tổng hợp theo từng tháng với hàm SUMIFS

Hướng dẫn cách dùng hàm SUMPRODUCT để đếm theo nhiều điều kiện

Hướng dẫn cách dùng hàm MONTH trong Excel để tìm số tháng