Cách sử dụng các hàm kế toán tiền lương trong excel

Để thuận lợi hơn cho các bạn tìm kiếm những vấn đề liên quan về lương,

BHXH, BHYT, bài viết này sẽ giúp người dùng giải quyết thắc mắc trên. Kế toán tiền lương bạn phải biết các vấn đề sau:

Hàm kế toán tiền lương trong excel

Kế toán tiền lương cần biết gì?

1. Những hàm về thời gian của Excel : 
- Hàm Weeknum – cho biết hôm nay là tuần thứ mấy trong năm
=WEEKNUM(Today(),1) … cho biết hôm nay thuộc tuần thiết bị mấy trong năm,

với tuần bắt đầu là ngày chủ nhật
=WEEKNUM(Today(),2)… cho biết hôm nay thuộc tuần thứ mấy trong năm,

với tuần bắt đầu là ngày vật dụng hai
Còn theo đề nghị của bạn thì làm như sau:
A1= “HÔM NAY LÀ TUẦN vật dụng “&WEEKNUM(Today(),1)&” CỦA NĂM “&YEAR(TODAY())
- Hàm DATEDIF – tính số ngày từ hôm nay đến tương lai
Từ hôm nay tới tương lai phương pháp bao nhiêu đây nè:
=DATEDIF(Today(),TUONG LAI,”D”) … là số ngày phương pháp sở hữu hôm nay
=DATEDIF(Today(),TUONG LAI,”M”) … là số tháng cách mang hôm nay
=DATEDIF(Today(),TUONG LAI,”Y”) … là số năm cách với hôm nay
Hàm này tiêu dùng đễ tính tuổi là tuyệt đối chính xác…
- Hiển thị Ngày, tháng, năm hiện hành
TODAY () : Trả về số tuần tự thể hiện ngày tháng hiện tại. nếu định dạng của ô là

General trước khi hàm được nhập vào, kết quả sẽ được thể hiện ở dạng ngày tháng năm.
- Hàm Weekday – Tính ngày thuộc vật dụng mấy trong tuần
Bạn thử Công thức sau nhé :
=CHOOSE(WEEKDAY(A1),”Chủ nhật”,”Thứ hai”,”Thứ ba”,”Thứ tư”,”Thứ năm”,”Thứ sáu”,”Thứ bảy”)
- Hàm NETWORKDAYS – Số ngày làm cho việc trong tháng
ấy là hàm NETWORKDAYS bạn à… nhái sử A1 là ngày đầu tháng (1/9/2007),

B1 là ngày cuối tháng (30/9/2007) vậy sẽ mang hàm:
NETWORKDAYS(ngày đầu,ngày cuối, các ngày nghĩ lễ)
Ở đây trường hợp bạn ko sở hữu ngày nghĩ lễ nào thì bỏ tham số đồ vật 3:
=NETWORKDAYS(A1,B1).. kết quả là 20..

Trong này mặc định nó đã trừ đi đồ vật bảy và CN rồi đấy!

để ý là đễ sử dụng dc hàm này bạn buộc phải vào menu ToolsAdd-Ins và đánh dấu vào mục

Analysis Toolpak nhé!
- Hàm WORKDAY – Số ngày làm việc trừ ngày lễ giữa 2 khoảng thời gian
Bạn cần vào Tool AddIns cài thêm Analysis ToolPak-VBA lúc đó sẽ mang hàm workday,

cho ta ngày khiến cho việc cuối cộng trừ ngày lễ.

=Workday(ngaydau,songaythuviec,ngayle)

Trong đấy ngày lễ là names ngayle ta quy định ở cột A1:A9 (chín ngày lễ) ngày AL thì quy ra DL.
Còn trường hợp không cài thêm AddIns thì tiêu dùng công thức dài lắm.
- hài hòa giữa hàm DATE và hàm INT để thêm dấu “/” vào một dãy số để thành ngày tháng

TH1 : Chỉ mang những năm >=2000: 

=DATE(RIGHT(A2;2)+2000;MID(A2;LEN(A2)-3;2);LEFT(A2;LEN(A2)-4))
TH 2: trường hợp với các năm <2000: 
=DATE(RIGHT(A2;2)+IF(VALUE(RIGHT(A2;2))<10;2000;19 00);

MID(A2;LEN(A2)-3;2);LEFT(A2;LEN(A2)-4))
- Công thức trả về ngày cuối tháng khác thiết bị 7, CN
làm sao khi nhập số tháng và số năm sẽ ra cho ra kết quả là ngày cuối tháng

ấy ko trùng trang bị bảy, chủ nhật. nếu ngày cuối tháng ấy là T7. CN thì lấy ngày Tsáu.
Có 2 phương pháp : 
- Phương pháp1 : 
Công thức như sau :
=DATE(YEAR(Ngay),MONTH(Ngay)+1,0)-(MAX(0,WEEKDAY(DATE(YEAR(Ngay),

MONTH(Ngay)+1,0),2) -5))
trong đó Ngay sẽ là 1 cell đựng ngày tháng năm bạn buộc phải biết
Thí dụ hàm : =DATE(2004,9+1,0) sẽ trả về ngày cuối tháng 30/09/2004.

giả dụ bạn không cộng thêm 1 mà chỉ nhập tháng là 9, hàm sẽ trả về ngày cuối tháng 31/08/2004.
Hàm WEEKDAY(DATE(2004,10,0),2) sẽ cho ta biết ngày cuối tháng là thứ mấy trong tuần.

Weekday(******_number, return_type) . ******_number là Date(2004,10,0).

Còn return_type là 1 giả dụ quy định chủ nhật là 1–> vật dụng bảy là 7,

return_type là 2 giả dụ quy định thứ hai là 1–> chủ nhật là 7,

return type là 0 trường hợp quy định vật dụng hai là 1–>thứ bảy là 6.

giả dụ tôi chọn weekday(date(2004,10,0),2) thì khi ngày cuối tháng rơi vào đồ vật 6,

hàm sẽ cho kết quả là 5.

HàmMax(0,weekday(date(2004,10,0),2)-5) sẽ cho kết quả là 0 ví như ngày

cuối tháng rơi vào thiết bị 2 đến vật dụng 6 trong tuần

và cho kết quả là 1 trường hợp là thứ bảy, 2 trường hợp là chủ nhật.

bởi thế ví như ta chọn lùi lại 1 hoặc 2 ngày giả dụ ngày cuối tháng rơi vào trang bị bảy

hay chủ nhật thì bạn chỉ bắt buộc khiến phép trừ ngày cuối tháng cho 1 hoặc 2 nữa là xong.

khi ấy hàm sẽ là : =DATE(2004,10,0)-(MAX(0,WEEKDAY(Date(2004,10,0,2) -5)).
- Phương pháp 2 :
DATE(Năm, Tháng, Ngày)
DATE(2004,02, 15) = 15 / 02 / 2004
DATE(2004,02, 0 ) = 0 / 02 / 2004 = 31/01/2004 .Là ngày cuối của Tháng -1
do vậy, ví như muốn trả về ngày cuối tháng 02 thì cần Tháng +1 (02+1).

Thực chất là việc bù trừ (02 +1) – 1=02
DATE(2004,02+1, 0 ) = 0 / 03 / 2004 = 29/02/2004 (Tháng -1)
A2=10/01/2004
Ngày cuối của tháng Month(A2) là: DATE(Year(A2), Month(A2)+1, 0)

Để giải bài toán trên tư duy bắt đầy như sau:
=Ngày cuối tháng -1 (nếu thiết bị của ngày cuối tháng là T7 )

hoặc -2 (nếu thứ của ngày cuối tháng là CN)
T7-1= CN-2 = T6
WEEKDAY(Ngay) = Ngày trong tuần: T7 là 7, CN là 1
Diễn giải theo CT như sau:
=DATE(Year(A2), Month(A2)+1, 0)
+IF(WEEKDAY( DATE(Year(A2), Month(A2)+1, 0) )=7,-1,0)
+IF(WEEKDAY( DATE(Year(A2), Month(A2)+1, 0) )=1,-2,0)
(Ngày đầu tháng này = ngày cuối của tháng trước)
- cách 3 : kết hợp với hàm EOMONTH()
=IF(WEEKDAY(EOMONTH(A1,0),1)=1,EOMONTH(A1,0)-2,

IF(WEEKDAY(EOMONTH(A1,0),1)=7,EOMONTH(A1,0)-1,EOMONTH(A1,0)))

- Đếm số ngày lẻ và chẵn trong một tháng bất kỳ
Ngày lẻ
=SUM(IF(MOD(DAY(ROW(INDIRECT(“A”&A1&”:A”& B1)));2) =1;1;0))
Ngày chẵn
=SUM(IF(MOD(DAY(ROW(INDIRECT(“A”&A1&”:A”& B1)));2) =1;0;0))
Ta có thể dùng hàm sumproduct thay thế như sau
=SUMPRODUCT((MOD(DAY(ROW(INDIRECT(“A”&A1&”:A ”&B1)) ),2)=1)*1)
=SUMPRODUCT((MOD(DAY(ROW(INDIRECT(“A”&A1&”:A ”&B1)) ),2)=0)*1)
tiêu dùng names rút gọn:

=> CT như sau:
=SUMPRODUCT((MOD(DaysRng,2) =1)*1)
=SUMPRODUCT((MOD(DaysRng,2) =0)*1)
· - Đếm xem mang bao nhiêu ngày bất kỳ (1,2,…31 hay thiết bị 2, thứ 3,…CN)

trong khoảng thời gian bất kỳ

=> CT như sau:
=SUMPRODUCT((MOD(DaysRng,2) =1)*1)
=SUMPRODUCT((MOD(DaysRng,2) =0)*1)
- Xác định ngày nào lúc biết đó là đồ vật mấy của tuần vật dụng bao nhiêu trong năm
=DATE(G8,1,1) +(H8-1)*7-WEEKDAY(DATE(G8,1,1) +(H8-1)*7)+2 xem file
- Công thức tính ngày cuối cộng trong tháng
Mình xin góp vui về hàm ngày chót sau : (xin chép đoạn code sau vào trong 1 module của file excel)
Public Function NgayChot(Thang, Nam) As Date
Dim StartDate As Date, StopDate As Date
StartDate = DateSerial(Nam, Thang, 1)
StopDate = DateAdd(“m”, 1, StartDate) – 1
NgayChot = StopDate
End Function
Sau ấy quay ra sheet thử như sau :
VD tại D10 bạn gỏ bất kỳ ngày nào của tháng 01/2008. (VD 01/01/2008)

sau đấy tại D11 nhập công thức sau : =ngaychot(MONTH(D10),YEAR(D10))
sẽ với kết quả như ý.
- Công thức trả về ngày cuối tháng trước
Thí dụ hôm nay là ngày 03/06/2006, công thức sẽ trả về ngày 31/05/2006.
sở hữu phổ biến phương pháp :
1/ tiêu dùng VBA : Code như sau
Function SongayTrongthang(Thang As Long, Nam As Long) As Byte
Select Case Thang
Case 1, 3, 5, 7, 8, 10, 12: SongayTrongthang = 31
Case 4, 6, 9, 11: SongayTrongthang = 30
Case 2
Select Case Nam
Case (Nam Mod 4 = 0 And Nam Mod 100 = 0) Or Nam Mod 400 = 0: SongayTrongthang = 29
Case Else: SongayTrongthang = 28
End Select
End Select
End Function
2/ Ví dụ ngày tham chiếu ở ô A1:
=Date(year(A1),month(A1),1)-1
3/ tiêu dùng công thức sau : A1 – day(A1) thì sẽ được ngày cuối tháng trước. quan tâm format cell cho đúng
4/Công thức : =EOMONTH(“03/05/2006″,-1)
=EOMONTH(Start_date,months)
giả dụ k thấy hàm này, vui lòng vào Tools->add-in->chọn Analysis ToolPak !
EOMONTH mang nghĩa là End of Month : ngày cuối cùng của tháng
- Công thức để tính tuổi
tính bằng công thức sau =ROUND((TODAY()-A1)/365,0) sở hữu A1 là ô mang giá trị của ngày sinh
- Dịch ngày tháng năm ra chữ
Bạn bắt buộc rút kinh nghiệm trong việc đặt vấn đề và đặt tên đề tài.

Như bạn đã thấy từ 1 vấn đề đơn giản nhưng trường hợp không đặt vấn đề 1 bí quyết rõ ràng

và cụ thể thì vấn đề sẽ trở lên phức tạp.
Vì đây là chuyên mục Excel dành cho người mới bắt đâu bắt buộc bạn cần chọn hiểu

các hàm và vận dụng công thức.

Ở đây mình gợi í bạn tiêu dùng các hàm ngày tháng:

DAY, MONTH, YEAR, kết hợp với hàm VLOOKUP.

phương pháp tổ chức dữ liệu:
- Bạn sở hữu danh sách liệt kê 31 ngày tương ứng mang cách đọc 31 số:
[BANG1]
một – 1
2 – hai
3 – ba
4 – bốn
…
31 – ba mốt
- 1 danh sách khác để đọc năm dựa vào danh sách những số ở trên
(BANG2 – bảng này bạn tự nghĩ phương pháp làm kết hợp những hàm LEFT, RIGHT, MID)
o dùng hàm DAY để xác định ngày trong chuỗi ngày tháng rồi sử dụng VLOOKUP

để chọn ra chuỗi đọc ngày trong BANG1

o tiêu dùng hàm MONTH để xác định tháng trong chuỗi ngày tháng rồi sử dụng VLOOKUP

để chọn ra chuỗi đọc tháng trong BANG1

o dùng hàm YEAR để phân định năm trong chuỗi ngày tháng rồi dùng VLOOKUP

để tìm ra chuỗi đọc năm trong BANG2

kết hợp 3 kết quả này bạn sẽ được chuỗi bắt buộc đọc.

Hoặc sử dụng add-in VnTools Tiện ích Excel chuyển số sang chữ 

Đây là công cụ bổ sung (Add-in) cho Excel, siêu thuận tiện đặc biệt đối có quý khách

Excel trong công việc kế toán-tài chính và ko rành lập trình VBA.
Công cụ vnTools được cung ứng ở dạng tập tin tự giải nén .exe (WinRAR),

khi chạy sẽ cài tập tin Ufunctions.xla vào thư mục mặc định Program FilesvnTools

(bạn mang thể chỉ định thư mục khác). Bạn buộc phải tự thêm công cụ này vào Excel

(trong Excel, vào menu Tools>Add-Ins

và tiêu dùng Browse để duyệt tìm tới thư mục cất tập tin Ufunctions.xla).

vnTools xuất hiện ở dạng thanh công cụ, có hộp thoại thao tác tương tự như những hàm của Excel

– trong ấy bạn có thể chọn ô chứa số buộc phải chuyển đổi, ô đựng chuỗi kết quả… (xem hình).

Bạn cũng với thể nhập trực tiếp hàm chuyển đổi mà ko nên thực hiện qua hộp thoại.
vnTools hỗ trợ chuyển đổi tiền VND (đồng) và USD (đô la),

với khả năng xử lý tới 15 chữ số (trăm nghìn tỷ); hỗ trợ bảng mã tiếng Việt Unicode,

VNI và TCVN3 (lưu ý, việc thiết lập bảng mã sẽ tác động tới hầu hết những ô kết quả dùng hàm chuyển đổi).

ko kể hàm chuyển đổi số sang chữ, vnTools còn phân phối hàm

chuyển đổi ngày tháng sang chữ (DATETOTEXT).

Công cụ mang phần hướng dẫn hơi chi tiết về cách tiêu dùng những hàm.
2. những hàm chuỗi, hàm đếm, hàm mảng để đếm tên NV:

• - Đếm số người sở hữu tên bắt đầu bằng chữ chiếc

=COUNTIF(D46:G54,CHAR(65+A141) & “*”)

• - Hàm văn bản – Tách tên họ ra 2 cột khác nhau

>>>> XEM chi tiết TẠI ĐÂY

• - Đếm số ký tự xác định ở bất cứ vị trí nào của chuỗi sở hữu mặt trong 1 mảng

=SUM(LEN(A1:B1)-LEN(SUBSTITUTE(A1:B1,”c”,”")))
Ctr Sh Enter
Thông thường các hàm sum mảng hay Sum(If()), bắt buộc Ctr Sh Enter,

ta phải chuyển qua sumproduct thì khỏi Ctr Sh Enter. Vd ta mang thể thay ct trên như sau:
=SUMPRODUCT((LEN(A1:B1)-LEN(SUBSTITUTE(A1:B1,”c”,”")))*1)
Hay là đặt một name: TotalC
Refer to:
=SUM(LEN(A1:B1)-LEN(SUBSTITUTE(A1:B1,”c”,”")))
không mang Ctr Sh Enter
Vậy ta chỉ buộc phải =TotalC là OK.

3. các hàm UDF, công thức tính lương, thưởng, trừ lương :

• - Hàm tính số giờ bị phạt do trễ giờ
• - Trừ lương
• - Hàm chấm công (1)
• - Hàm chấm công (2)
• - Hàm tính thuế TNCN
• - Bảng kê tiền bằng Excel
• - Giúp lập công tức theo dõi CN nghỉ chế độ
• - Chấm công theo giờ trong XD
• - Đếm số ngày nghỉ phép của NV
• - Công thức tính ngày nghỉ hưu
• - Tự động cập nhật vào Bảng chấm công lúc có DS những ngày nghỉ phép,
• nghỉ lễ, ngày làm cho việc của NV trong tháng
• - Bảng chấm công
• - dùng VBA để đếm số ngày chẵn, lẻ trong 1 tháng bất kỳ – Code nvson
• - dùng VBA để đếm số ngày chẵn, lẻ trong một tháng bất kỳ – Code MrOkebap
• - tiêu dùng VBA để đếm số ngày chẵn, lẻ trong một tháng bất kỳ – Code ThuNghi
• - Tự điền dấu “/” vào lúc nhập một chuỗi số có dạng ddmmyy
• - Tính ngày tháng nâng cao lương để xác định NV nào sắp tới hạn nâng lương ?

4. tiêu dùng các công cụ tăng của Excel trong :

• - Quy đổi giữa lương Net và lương Gross bằng Goal Seek

5. File ứng dụng thực tế :

• - In phiếu lĩnh lương cho NV từ bảng lương – File của YeuDoi
• - In nhãn lương – File của Phạm Duy Long
• - xây dựng hệ thống thang lương, bảng lương – File của Kế Toán Già Gân

6. các văn bản, biểu chiếc liên quan đến lương và BHXH, BHYT
6.1/ Hợp Đồng Lao Động- Nội quy LĐ, thoả ước LĐ tạp thể
6.2/ Lương và những quy định trả lương, cung ứng lương
- Tham khảo về lương và những quy định tính toán trả lương cho KVSX
- Dự thảo quy chế tạm thời về cung ứng trả lương của Cty CP tăng trưởng nhân lực,

thương mại du lịch Viwaseen

- Tập hợp các CT KT lương ban hành theo QĐ 48/2006/QĐ-BTC

- những NĐ 166/2007/NĐ-CP, 167/2007/NĐ-CP, 168/2007/NĐ-CP quy định về các mức lương tối thiểu
6.3/ BHXH và BHYT

- EFC và BHXH, BHYT (tham khảo – Biểu dòng đã cũ)
- Quyết định 815/QĐ-BHXH
- Biểu mẫu BHXH mới theo Qđ 815/QĐ-BHXH ngày 06/06/2007
6.4/ Thuế TNCN
- QT thuế TNCN năm
- QT thuế TNCN năm 2007