Trọn bộ các hàm tài chính trong Excel bạn nên biết

Hàm FV, DATE, TIME, HOUR, WORKDAY,... là các hàm tài chính cơ bản cùng cách nhận mã màu trong Excel. Hướng dẫn các bạn cách sử dụng các hàm tài chính này. Click xem ngay!

Trong Excel, các hàm tài chính để tính lợi nhuận, tính ngày công hoặc tính thời gian rất cần thiết trong công việc của hầu hết tất cả doanh nghiệp. Trong bài viết này, chúng ta sẽ theo dõi cách sử dụng các hàm tài chính một cách chi tiết và dễ hiểu nhất.

1. Hàm FV

Công dụng: Dùng để tính giá trị tương lai của một khoản đầu tư dựa trên lãi suất cố định.

Công thức: =FV(rate, nper ,pmt ,[pv], [type])

Trong đó:

+ Rate (bắt buộc): Lãi suất theo kỳ hạn.

+ Nper (bắt buộc): Tổng số kỳ hạn thanh toán.

+ Pmt (bắt buộc): Khoản thanh toán cho mỗi kỳ; khoản này không đổi. Thông thường, pmt có chứa tiền gốc và lãi, nhưng không chứa các khoản phí và thuế khác. Nếu pmt được bỏ qua, bạn phải đưa vào đối số pv.

+ Pv (tùy chọn): Giá trị hiện tại, hoặc số tiền trả một lần hiện tại đáng giá ngang với một chuỗi các khoản thanh toán tương lai. Nếu bỏ qua đối số pv, thì nó được giả định là 0 và bạn phải đưa vào đối số pmt.

+ Type (tùy chọn): Số 0 hoặc 1 chỉ rõ thời điểm thanh toán đến hạn. Nếu đối số kiểu bị bỏ qua, thì nó được giả định là 0.

Ví dụ: Lãi suất hằng năm là 6%, tổng số lần thanh toán là 10, số tiền cần phải thanh toán là 200, giá trị hiện tại là 500, thanh toán vào đầu kỳ. Nhập công thức =FV(0.06/12,10,-200,-500,1) (1) sẽ cho kết quả là $2.581,40 (2).

2. Hàm tạo lập ngày tháng

- Hàm DATE

Công dụng: Trả về một ngày tháng năm nào đó.

Công thức: =DATE(year, month, day)

Ví dụ: Trong ví dụ ta cần tổng hợp ngày tháng năm sinh của các bạn nhưng hiện tại các ô đang tách biệt, ta dùng hàm DATE để tổng hợp ngày tháng năm của các bạn lại. Year ở ô D2, Month ở ô C2, Day ở ô B2. Khi nhập công thức DATE(D2,C2,B2) (1) sẽ cho kết quả 12/01/2002 (2).

- Hàm DATEVALUE

Công dụng: Chuyển đổi một chuỗi văn bản có dạng ngày tháng năm thành một giá trị ngày tháng năm để có thể tính toán được.

Công thức: =DATEVALUE(date_text)

Ví dụ: Dùng hàm DATEVALUE để chuyển các ngày ngày sang số seri, sau đó ta lấy ngày nghỉ việc trừ ngày vào làm sẽ ra số ngày mà nhân viên đó đã làm sẽ cho kết quả (4).

3. Hàm ngày và giờ hiện thời

- Hàm TODAY

Công dụng: Trả về ngày hiện tại. Thường TODAY được dùng để tính thời gian một cách nhanh chóng.

Công thức: =TODAY()

Ví dụ: Giả sử hôm nay là ngày 26/08/2020. Khi nhập công thức =TODAY() (1) sẽ cho kết quả 26/08/2020 (2).

- Hàm NOW

Công dụng: Trả về ngày và thời gian hiện tại.

Công thức: =NOW()

Ví dụ: Giả sử thời gian hiện tại là 20:44 ngày 26/08/2020. Khi nhập công thức =NOW() (1) sẽ cho kết quả (2).

4. Hàm trích ra ngày tháng và các thành phần ngày tháng

- Hàm DAY

Công dụng: Trả về ngày trong tháng.

Công thức: =DAY(serial_number)

Trong đó: Serial_number (bắt buộc): Ngày tháng của ngày bạn đang cố gắng tìm kiếm. Nên nhập ngày bằng cách sử dụng hàm DATE hoặc là kết quả của những công thức hay hàm khác.

Ví dụ: Dùng DAY để trích dẫn ngày trong 22/5/2019. Khi nhập công thức =DAY(“22/5/2019”) (1) sẽ trả về kết quả trả về là ngày 22 (2).

- Hàm MONTH

Công dụng: Trả về tháng của một ngày được chỉ định.

Công thức: =MONTH(serial_number)

Trong đó: Serial_number (bắt buộc): Ngày của tháng mà bạn đang cố gắng tìm kiếm. Nên nhập ngày bằng cách sử dụng hàm DATE hoặc sử dụng kết quả của những công thức hay hàm khác.

Ví dụ: Dùng hàm MONTH để trích dẫn tháng trong 22/5/2019.Khi nhập công thức =MONTH(“22/5/2019”) (1) sẽ trả về kết quả là tháng 5 (2).

- Hàm YEAR

Công dụng: Trả về năm của một ngày nhất định.

Công thức: =YEAR(serial_number)

Trong đó: Serial_number (bắt buộc): Ngày của tháng mà bạn đang cố gắng tìm kiếm. Nên nhập ngày bằng cách sử dụng hàm DATE hoặc sử dụng kết quả của những công thức hay hàm khác.

Ví dụ: Dùng hàm YEAR để trích năm trong 22/5/2019. Khi nhập công thức =YEAR(“22/5/2019”) (1) sẽ cho kết quả trả về là 2019.

- Hàm EOMONTH

Công dụng: Trả về ngày cuối cùng của tháng.

Công thức: =EOMONTH(start_date, months)

Trong đó:

+ Start_date (bắt buộc): Ngày biểu thị ngày bắt đầu. Nên nhập ngày bằng cách sử dụng hàm DATE hoặc sử dụng kết quả của những công thức hay hàm khác.

+ Months (bắt buộc): Số tháng trước hoặc sau start_date. Giá trị dương cho đối số months tạo ra ngày trong tương lai; giá trị âm tạo ra ngày trong quá khứ.

Ví dụ: Để biết ngày cuối tháng của các tháng 8, 9, 10, 11, 12 của năm 2020 ta dùng hàm EOMONTH. Start_date là ngày 26/8/2020, tức là ô B2. Months là số tháng tính kế tiếp kể từ tháng của Start_date, trong ví dụ là 0, 1, 2, 3, 4. Khi nhập công thức =EOMONTH($B$2,C2) (1) kết quả trả về là ngày cuối tháng của các tháng này (2).

- Hàm WEEKDAY

Công dụng: Trả về thứ trong tuần tương ứng với một ngày.

Công thức: =WEEKDAY(serial_number,[return_type])

Trong đó:

+ Serial_number (bắt buộc): Là một số tuần tự thể hiện ngày tháng của ngày bạn đang tìm kiếm. Ngày tháng nên được nhập bằng cách sử dụng hàm DATE hoặc nhập như là kết quả của những công thức hay hàm khác.

+ Return_type (tùy chọn): Là một số xác định kiểu giá trị trả về.

Ví dụ: Khi cần biết ngày 26/8/2020 là ngày thứ mấy trong tuần ta dùng hàm WEEKDAY. Trong đó serial_number là 26/8/2020, return_type chọn là 2 để tính thời gian từ thứ hai đến chủ nhật. Khi nhập công thức =WEEKDAY(“26/8/2020”) kết quả trả về là 3 tức 26/8/2020 (2) là ngày có vị trí thứ 3 trong tuần, tức là thứ tư.

- Hàm WEEKNUM

Công dụng: Trả về số thứ tự của tuần trong năm của một ngày cụ thể.

Công thức: = WEEKNUM(serial_number,[return_type])

Trong đó:

+ Serial_number (bắt buộc): Là một ngày trong tuần. Ngày tháng nên được nhập bằng cách sử dụng hàm DATE hoặc nhập như là kết quả của những công thức hay hàm khác.

+ Return_type (tùy chọn): Là một số để xác định tuần sẽ bắt đầu từ ngày nào. Mặc định là 1.

Ví dụ: Khi cần biết tuần chứa ngày 26/8/2020 là tuần thứ mấy trong năm ta dùng hàm WEEKNUM. Trong đó serial_number là 26/8/2020, return_type chọn là 2 để tính thời gian từ thứ hai. Khi nhập công thức =WEEKNUM(“26/8/2020”,2) kết quả trả về là 35 tức tuần chứa ngày 26/8/2020 là tuần thứ 35 trong năm.

5. Hàm tính chênh lệch ngày

- Hàm EDATE

Công dụng: Trả về một ngày nằm trong tháng đã định trước, có thể đứng trước hoặc sau ngày bắt đầu. Dùng hàm EDATE để tính toán ngày đáo hạn hoặc ngày đến hạn trùng vào ngày phát hành trong tháng.

Công thức: EDATE(start_date, months)

Trong đó:

+ Start_date (bắt buộc) Ngày biểu thị ngày bắt đầu. Nên nhập ngày bằng cách sử dụng hàm DATE hoặc sử dụng kết quả của những công thức hay hàm khác.

+ Months (bắt buộc): Số tháng trước hoặc sau start_date. Giá trị dương cho đối số months tạo ra ngày trong tương lai; giá trị âm tạo ra ngày trong quá khứ.

Ví dụ: Để biết 1 tháng sau của ngày 4/2/2019 là ngày mấy ta dùng hàm EDATE với Start_date là ngày 4/2/2019, Months là 1. Khi nhập công thức =EDATE(A2,B2) (1) sẽ cho kết quả là 4/3/2019 (2).

- Hàm YEARFRAC

Công dụng: Hàm YEARFRAC tính phần trong năm được thể hiện bằng số ngày trọn vẹn nằm giữa hai ngày (start_date và end_date).

Công thức: = YEARFRAC(start_date, end_date, [basis])

Trong đó:

+ Start_date (bắt buộc): Ngày bắt đầu.

+ End_date (bắt buộc): Ngày kết thúc.

+ Basis (tùy chọn): Loại cơ sở đếm ngày sẽ dùng.

Ví dụ: Ta dùng hàm YEARFRAC để tính khoảng thời gian giữ ngày 31/12/2019 và 1/1/2019 là bao nhiêu năm. Star_date là 1/1/2019, End_date là 31/12/2019. Khi nhập công thức =YEARFRAC(A2,B2) (1) sẽ cho kết quả trả về là 1 (2) vì giữa hai khoảng thời gian này là 1 năm.

6. Hàm tính các ngày làm việc

- Hàm WORKDAY

Công dụng: Trả về một số tuần tự thể hiện số ngày làm việc, có thể là trước hay sau ngày bắt đầu làm việc và trừ đi những ngày cuối tuần và ngày nghỉ (nếu có) trong khoảng thời gian đó.

Công thức: WORKDAY(Start_date, Days, [Holidays])

Trong đó:

+ Start_date: Là ngày bắt đầu, là tham số bắt buộc.

+ Days: Là ngày không nằm trong ngày cuối tuần và ngày lễ trước hay sau Start_date, là tham số bắt buộc.

+ Holidays: Ngày cần loại trừ ra khỏi ngày làm việc mà ngày này không nằm trong ngày lễ cố định.

Ví dụ: Để thực hiện nhanh chóng khi xác định ngày kết thúc dự án ta sẽ dùng hàm WORKDAY. Start_date là ngày 1/3/2019, days là 45 ngày, ví dụ như làm việc không có nghỉ những ngày lễ. Khi nhập công thức =WORKDAY(B2,C2) (1) sẽ cho kết quả trả về là ngày 3/5/2019 (2).

- Hàm WORKDAY.INTL

Công dụng: Đây là hàm trả về một ngày trước hoặc sau ngày bắt đầu một số ngày làm việc đã xác định với ngày cuối tuần tùy chỉnh. Tham số ngày cuối tuần cho biết là ngày nào và có bao nhiêu ngày.

Công thức: WORKDAY.INTL(start_date, days, [weekend], [holidays])

Trong đó:

+ Start_date (bắt buộc): Ngày bắt đầu.

+ Days (bắt buộc): Số ngày làm việc trước hoặc sau start_date. Giá trị dương cho kết quả là một ngày trong tương lai; giá trị âm cho kết quả là một ngày trong quá khứ; giá trị 0 cho kết quả là start_date.

+ Weekend (tùy chọn): Cho biết những ngày nào trong tuần là ngày cuối tuần và không được coi là ngày làm việc. Weekend là một số của ngày cuối tuần hoặc một chuỗi chỉ rõ khi nào thì diễn ra ngày cuối tuần.

Ví dụ: Ta cần xác định ngày bắt đầu và ngày kết thúc công việc với xác định ngày cuối tuần theo ý muốn của bạn ta sẽ dùng hàm WORKDAY.INLT. Start_date là ngày 1/3/2019, days là 45 ngày, Weekend là ngày chủ nhật theo mặc định của máy là số 11. Khi nhập công thức =WORKDAY.INTL(B2,C2,11) (1) sẽ cho kết quả là ngày 23/4/2019 (2).

- Hàm NETWORKDAYS

Công dụng: Trả về số ngày làm việc trọn ngày tính từ start_date đến end_date. Ngày làm việc không bao gồm ngày cuối tuần và mọi ngày lễ đã xác định. Dùng hàm NETWORKDAYS để tính toán phúc lợi của nhân viên được dồn tích dựa trên số ngày làm việc trong một thời kỳ cụ thể.

Công thức: = NETWORKDAYS(start_date, end_date, [holidays])

Trong đó:

+ Start_date (bắt buộc): Ngày bắt đầu.

+ End_date (bắt buộc): Ngày kết thúc.

+ Holidays (tùy chọn): Một phạm vi tùy chọn gồm một hoặc nhiều ngày cần loại trừ ra khỏi lịch làm việc, chẳng hạn như ngày lễ liên bang, ngày lễ tiểu bang và ngày lễ không cố định.

Ví dụ: Dùng hàm NETWORKDAYS tính số ngày làm việc. Start_date là ngày 1/3/2019, End_date là ngày 2/4/2019. Khi nhập công thức =NETWORKDAY(B2,C2) (1) sẽ cho kết quả là 23 (2), tức mất 23 ngày để lập kế hoạch. Lưu ý, Excel mặc định không làm việc ngày thứ bảy và chủ nhật.

- Hàm NETWORKDAYS.INTL

Công dụng: Trả về số ngày làm việc trọn vẹn ở giữa hai ngày bằng cách dùng tham số để cho biết có bao nhiêu ngày cuối tuần và đó là những ngày nào. Ngày cuối tuần và bất kỳ ngày nào được chỉ rõ là ngày lễ sẽ không được coi là ngày làm việc.

Công thức: = NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])

Trong đó:

+ Start_date và end_date (bắt buộc): Những ngày cần tính khoảng cách giữa chúng. Start_date có thể sớm hơn, trùng với hoặc muộn hơn end_date.

+ Weekend (tùy chọn): Cho biết những ngày nào là ngày cuối tuần và không được tính vào số ngày làm việc trọn vẹn tính từ start_date đến end_date. Ngày cuối tuần có thể là số ngày cuối tuần hoặc một chuỗi cho biết ngày cuối tuần xảy ra khi nào.

Ví dụ: Dùng hàm NETWORKDAYS.INTL tính số ngày làm việc, xác định ngày cuối tuần theo ý muốn của bạn. Start_date là ngày 1/3/2019, End_date là ngày 2/4/2019, Weekend là ngày chủ nhật theo mặc định là số 11. Khi nhập công thức =NETWORKDAYS.INTL(B2,C2,11) (1) sẽ cho kết quả trả về là 28 (2), tức mất 23 ngày để lập kế hoạch.

7. Hàm thời gian trong Excel

- Hàm TIME

Công dụng: Trả về thời gian dưới dạng số sê ri.

Công thức: =TIME(hour, minute, second)

Trong đó:

+ Hour (bắt buộc): Là một số từ 0 đến 32767 thể hiện giờ. Mọi giá trị lớn hơn 23 sẽ được chia cho 24 và phần còn lại sẽ được coi là giá trị giờ.

+ Minute (bắt buộc): Là một số từ 0 đến 32767 thể hiện phút. Mọi giá trị lớn hơn 59 sẽ được chuyển đổi thành giờ và phút.

+ Second (bắt buộc): Là một số từ 0 đến 32767 thể hiện giây. Mọi giá trị lớn hơn 59 sẽ được chuyển đổi thành giờ, phút và giây.

Ví dụ: Để xác định 9:46 AM ta điền vào Time là 9, Minute là 45, Second là 34. Khi nhập công thức TIME(0,45,34) (1) sẽ cho kết quả 9:45 AM (2).

- Hàm TIMEVALUE

Công dụng: Trả về số thập phân của thời gian được thể hiện bằng một chuỗi văn bản.

Công thức: = TIMEVALUE(time_text)

Trong đó: Time_text (bắt buộc): Là chuỗi văn bản thể hiện thời gian ở một trong các định dạng thời gian của Microsoft Excel; ví dụ, các chuỗi văn bản "6:45 CH" và "18:45" được đặt trong dấu ngoặc kép thể hiện thời gian.

Ví dụ: Khi cần tìm giá trị thập phân tương ứng của 4:45:34 ta nhập công thức =TIMEVALUE(“4:45:34”) (1) sẽ cho kết quả 0.198310185 (2).

- Hàm HOUR

Công dụng: Chuyển đổi một số thành một giờ dạng số sê ri.

Công thức: = HOUR(serial_number)

Trong đó: Serial_number (bắt buộc): Thời gian có chứa giờ mà bạn muốn tìm. Thời gian có thể được nhập vào dưới dạng chuỗi văn bản đặt trong dấu ngoặc kép.

Ví dụ: Khi cần trích dẫn giờ trong một thời gian cụ thể ta dùng hàm HOUR. Khi nhập công thức =HOUR(“4:45:34”) (1) sẽ cho kết quả 4 (2).

- Hàm MINUTE

Công dụng: Trả về phút của một giá trị thời gian. Phút được trả về dưới dạng số nguyên, trong phạm vi từ 0 tới 59.

Công thức: =MINUTE(serial_number)

Trong đó: Serial_number (bắt buộc): Thời gian có chứa phút mà bạn muốn tìm. Thời gian có thể được nhập vào dưới dạng chuỗi văn bản đặt trong dấu ngoặc kép.

Ví dụ: Khi cần trích dẫn phút trong một thời gian cụ thể ta dùng hàm MINUTE. Khi nhập công thức =MINUTE (“4:45:34”) (1). sẽ cho kết quả 45 (2).

8. Hàm đếm và tính tổng các ô theo màu sắc (chức năng do người dùng xác định)

Để thực hiện các hàm này ta cần lập trình VBA trước. VBA là ngôn ngữ lập trình của Excel, ta dùng VBA để các dòng/ câu lệnh để máy tự động thực hiện những thao tác chúng ta muốn làm trong Excel.

Sau đây là các bước để thiết lập VBA để thực hiện các câu lệnh:

Bước 1: Mở bảng tính Excel và nhấn tổ hợp phím Alt + F11 để mở Visual Basic Editor (VBE).

Bước 2: Nhấn chuột phải vào Sheet1 > Chọn Insert > Chọn Module.

Bước 3: Nhập đoạn code > Chọn File > Chọn Close and Return to Microsoft Excel.

Code hàm GetCellColor

Code hàm GetCellFontColor

Code hàm CountCellsByColor

Code hàm SumCellsByColor

Code hàm CountCellsByFontColor

Code hàm SumCellsByFontColor

Code hàm WbkCountCellsByColor

Code hàm WbkSumCellsByColor

Sau khi hoàn tất các bước trên ta có thể sử dụng các hàm đếm và tính tổng các ô theo màu sắc. Cụ thể về tính chất và cách sử dụng các hàm như sau:

- GetCellColor

Công dụng: Trả về mã màu của màu nền thuộc một ô xác định.

Công thức: GetCellColor(Ô cần lấy mã màu)

Ví dụ: Khi cần lấy mã màu nền của một ô màu đỏ, ta dùng hàm GetCellColor. Khi nhập công thức =GETCELLCOLOR(C2) (1) ta sẽ có kết quả 255 (2).

- Hàm GetCellFontColor

Công dụng: Trả về mã màu của màu phông chữ thuộc một ô xác định.

Công thức: GetCellFontColor(Ô cần lấy mã màu)

Ví dụ: Khi cần lấy mã màu của phông chữ màu đỏ, ta dùng GetCellFontColor .Khi nhập công thức GETCELLFONTCOLOR(C2) (1) ta sẽ có kết quả 255 (2).

- Hàm CountCellsByColor

Công dụng: Đếm các ô có màu nền được chỉ định.

Công thức: CountCellsByColor(Vùng cần đếm, Mã màu cần đếm)

Ví dụ: Khi cần đếm số lượng ô màu đỏ trong vùng chọn từ A1 đến A15, ta dùng hàm CountCellsByColor. Khi nhập công thức CountCellsByColor($A$1:$A$15,C2) (1) ta sẽ có kết quả 4 (2).

- Hàm CountCellsByFontColor

Công dụng: Đếm các ô có màu phông chữ được chỉ định.

Công thức: CountCellsByFontColor(Vùng cần đếm, Mã màu của phông chữ cần đếm)

Ví dụ: Khi cần đếm số lượng ô có phông chữ màu đỏ trong vùng chọn từ A1 đến A15, ta dùng hàm CountCellsByFontColor. Khi nhập công thức CountCellsByFontColor($A$1:$A$15,C2) (1) ta sẽ có kết quả 6 (2).

- Hàm SumCellsByColor

Công dụng: Tính tổng của các ô có màu nền nhất định.

Công thức: SumCellsByColor(Vùng cần đếm, Mã màu của ô cần đếm)

Ví dụ: Khi cần tính tổng giá trị của các ô có cùng màu đỏ trong vùng từ A1 đến A15, ta dùng hàm SumCellsByColor. Khi nhập công thức SumCellsByColor($A$1:$A$15,C2) (1) ta sẽ có kết quả 121 (2).

- Hàm SumCellsByFontColor

Công dụng: Trả về tổng của các ô với một màu chữ nhất định.

Công thức: SumCellsByFontColor(Vùng cần đếm, Mã màu của phông chữ cần đếm)

Ví dụ: Khi cần tính tổng giá trị của các ô có cùng phông chữ màu đỏ trong vùng từ A1 đến A15, ta dùng hàm SumCellsByFontColor. Khi nhập công thức SumCellsByFontColor($A$1:$A$15,C2) (1) ta sẽ có kết quả 274 (2).

- Hàm WbkCountCellsByColor(cell)

Công dụng: Tính các ô có màu nền được chỉ định trong toàn bộ bảng tính.

Công thức: WbkCountCellsByColor(Ô cần đếm)

Ví dụ: Khi cần đếm các ô có cùng nền màu đỏ trong toàn bộ bảng tính, ta dùng hàm WbkCountCellsByColor. Khi nhập công thức WbkCountCellsByColor(C2) (1) ta sẽ có kết quả 8 (2).

- Hàm WbkSumCellsByColor(cell)

Công dụng: Tính tổng các ô với màu nền được chỉ định trong toàn bộ bảng tính.

Công thức: WbkSumCellsByColor(Ô mã màu)

Ví dụ: Khi cần đếm các ô có cùng phông chữ màu đỏ trong toàn bộ bảng tính, ta dùng hàm WbkCountCellsByColor. Khi nhập công thức WbkCountCellsByColor(C2) (1) ta sẽ có kết quả 98 (2).

Mong rằng bài viết sẽ giúp cho các bạn biết cách sử dụng các hàm tài chính trong Excel. Cám ơn các bạn đã theo dõi. Hẹn gặp lại ở các bài viết sau!

Trung tâm bảo hành có bán linh kiện và dịch vụ chính hãng, gọi là có ngay hỗ trợ miễn phí, bảo hành suốt đời.