Những hàm kế toán tiền lương hay dùng trong excel | Thư viện kế toán

Cảm nhận học viên

Cảm nhận sau khi học xong khóa học của bạn Bảo Ngọc

News image

Lớp học mang lại nhiều kiến thức thực tế, không khí vui tươi, được sự hỗ trợ rất nhiệt tình của các anh chị tại Trung Tâm Gia Sư Kế ...

Cảm nhận của các bạn học viên sau khóa học kế toán thực hành

News image

Cám ơn các bạn Hoàng Anh và bạn Tú Duyên đã tin tưởng vào uy tín của Gia sư kế toán trưởng. Sau đây là đôi dòng cảm nhận của ...

Tìm thông tin trong web bằng Google

Hỗ trợ tư vấn các khóa dạy tại DN, dạy tại nhà học viên và dạy qua internet


Hotline: 0918 867 446 - Tel: (028) 38 336 446

Ghi danh học kế toán TH tại Quận 1


Hotline: 0962 283 630 - Tel: (028) 38 336 446

Gọi Voice Skype online đến chúng tôi

 Đăng ký ghi danh online tại đây.




Khóa học cho kế toán mới xin việc

Khóa học làm kế toán tổng hợp thực hành

News image

Bạn đang lo lắng liệu khối lượng kiến thức hiện tại có đáp ứng được yêu cầu của nhà tuyển dụng không? Hay bạn đang mong muốn một mức lương ...

Khóa dạy làm kế toán thuế, lập sổ sách

Như các bạn đã biết thuế là nguồn thu chủ yếu của nhà nước trong khi đó các DN thì luôn yêu cầu kế toán phải làm sao để DN ph...

Khóa dạy làm kế toán thanh toán, công nợ

Cung cấp cho học viên những kiến thức từ căn bản đến nâng cao về nghiệp vụ phần hành kế toán thanh toán, đáp ứng nhu cầu công việc thực tế...

Khóa dạy kèm kế toán kho, tính giá thành

Cung cấp cho học viên những kiến thức từ căn bản đến nâng cao về nghiệp vụ phần hành kế toán kho, hướng dẫn phương pháp tính gia thành theo ...

Công cụ quan trọng dành cho kế toán

Việc làm kế toán (hỗ trợ học viên)

Tuyển kế toán không kinh nghiệm - Đào tạo từ đầu

News image

Tuyển kế toán không kinh nghiệm - Đào tạo từ đầu. Đối tác trung tâm đang cần tuyển kế toán chưa kinh nghiệm được hướng dẫn công việc tận ...

Tuyển gấp kế toán part-time, kế toán học việc, thực tập sinh kế toán

News image

Đối tác báo vẫn chưa tuyển được kế toán không kinh nghiệm làm việc part-time các bạn liên hệ ứng tuyển ngay ...

Cần tuyển nhân viên nhập liệu part-time không kinh nghiệm

News image

Cần tuyển nhân viên nhập liệu part-time không kinh nghiệm, các bạn nhanh chóng liên hệ ứng tuyển ngay ...

dang-ky-hoc

uu-dai-danh-sinh-vien-ke-toan

 

 

mod_vvisit_countermod_vvisit_countermod_vvisit_countermod_vvisit_countermod_vvisit_countermod_vvisit_countermod_vvisit_countermod_vvisit_counter
mod_vvisit_counterToday7768
mod_vvisit_counterYesterday6324
mod_vvisit_counterThis week32735
mod_vvisit_counterLast week42922
mod_vvisit_counterThis month113546
mod_vvisit_counterLast month170511
mod_vvisit_counterAll days11170357

lienketdaotaovoimisa-1



Những hàm kế toán tiền lương hay dùng trong excel

hoc-ke-toan-tai-chinh

Để 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 các bạn giải quyết thắc mắc trên.

Kế toán tiền lương bạn cần biết những vấn đề sau:


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

1. Các 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 thứ 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 thứ hai
Còn theo yêu cầu của bạn thì làm như sau:
A1= “HÔM NAY LÀ TUẦN THỨ “&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 đến tương lai cách bao nhiêu đây nè:
=DATEDIF(Today(),TUONG LAI,”D”) … là số ngày cách với hôm nay
=DATEDIF(Today(),TUONG LAI,”M”) … là số tháng cách với hôm nay
=DATEDIF(Today(),TUONG LAI,”Y”) … là số năm cách với hôm nay


Hàm này 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 thứ 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 việc trong tháng
Đó là hàm NETWORKDAYS bạn à… giả 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ẽ có hàm:
NETWORKDAYS(ngày đầu,ngày cuối, các ngày nghĩ lễ)
Ở đây nếu bạn ko có ngày nghĩ lễ nào thì bỏ tham số thứ 3:

=NETWORKDAYS(A1,B1).. kết quả là 20..

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

Lưu ý là đễ sử dụng dc hàm này bạn 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 nên vào Tool AddIns cài thêm Analysis ToolPak-VBA lúc đó sẽ có hàm workday,

cho ta ngày làm việc cuối cùng trừ ngày lễ.
 

=Workday(ngaydau,songaythuviec,ngayle)
 

Trong đó 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 nếu không cài thêm AddIns thì dùng công thức dài lắm.

- Kết hợp giữa hàm DATE và hàm INT để thêm dấu “/” vào 1 dãy số để thành ngày tháng

DSC03856

Bạn xem nhé (có 2 TH đấy)

TH1 : Chỉ có các năm >=2000:
PHP Code:
=DATE(RIGHT(A2;2)+2000;MID(A2;LEN(A2)-3;2);LEFT(A2;LEN(A2)-4))

TH 2: Nếu có các năm <2000:
PHP Code:
=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 thứ 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

đó không trùng thứ bảy, chủ nhật. Nếu ngày cuối tháng đó là T7. CN thì lấy ngày Tsáu.
Có nhiều cách :

- Cách 1 :
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à một cell chứa ngày tháng năm bạn cần biết
Thí dụ hàm : =DATE(2004,9+1,0) sẽ trả về ngày cuối tháng 30/09/2004.

Nếu 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 nếu quy định chủ nhật là 1–> thứ bảy là 7,

return_type là 2 nếu quy định thứ hai là 1–> chủ nhật là 7,

return type là 0 nếu quy định thứ hai là 1–>thứ bảy là 6.
 

Nếu tôi chọn weekday(date(2004,10,0),2) thì khi ngày cuối tháng rơi vào thứ 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 nếu ngày

cuối tháng rơi vào thứ 2 đến thứ 6 trong tuần

và cho kết quả là 1 nếu là thứ bảy, 2 nếu là chủ nhật.
 

Do đó nếu ta chọn lùi lại 1 hoặc 2 ngày nếu ngày cuối tháng rơi vào thứ bảy

hay chủ nhật thì bạn chỉ cần làm 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)).

- Cách 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
Vì vậy, nếu muốn trả về ngày cuối tháng 02 thì phải 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 thứ 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 1 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)

 

Dùng names rút gọn:

12 



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

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

 

12 


=> CT như sau:
=SUMPRODUCT((MOD(DaysRng,2) =1)*1)
=SUMPRODUCT((MOD(DaysRng,2) =0)*1)

- Xác định ngày nào khi biết đó là thứ mấy của tuần thứ 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 đó 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 đó tại D11 nhập công thức sau : =ngaychot(MONTH(D10),YEAR(D10))
sẽ có 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.
Có nhiều cách :
1/ 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/ Dùng công thức sau : A1 – day(A1) thì sẽ được ngày cuối tháng trước. Lưu ý format cell cho đúng
4/Công thức : =EOMONTH(“03/05/2006″,-1)
=EOMONTH(Start_date,months)
Nếu k thấy hàm này, vui lòng vào Tools->add-in->chọn Analysis ToolPak !
EOMONTH có 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) với A1 là ô có giá trị của ngày sinh

- Dịch ngày tháng năm ra chữ
Bạn nên 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 nếu không đặt vấn đề một cách 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 nên bạn cần tìm hiểu

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

Ở đây mình gợi í bạn sử dụng các hàm ngày tháng:

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

Cách tổ chức dữ liệu:
- Bạn có danh sách liệt kê 31 ngày tương ứng với cách đọc 31 số:
[BANG1]
1 – một
2 – hai
3 – ba
4 – bốn


31 – ba mốt
- Một danh sách khác để đọc năm dựa vào danh sách các số ở trên
(BANG2 – bảng này bạn tự nghĩ cách thiết kế kết hợp các 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 dùng VLOOKUP

để tìm ra chuỗi đọc ngày trong BANG1
 

o Dùng hàm MONTH để xác định tháng trong chuỗi ngày tháng rồi dùng VLOOKUP

để tìm 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 cần đọc.
 

Bạn cứ làm thử xem đã nhé!


Hoặc 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, rất tiện lợi đặc biệt đối với người dùng

Excel trong công việc kế toán-tài chính và không rành lập trình VBA.
Công cụ vnTools được cung cấp ở 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 có thể chỉ định thư mục khác). Bạn phải tự thêm công cụ này vào Excel

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

và dùng Browse để duyệt tìm đến thư mục chứa 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ư các hàm của Excel

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

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

có 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 tất cả các ô kết quả dùng hàm chuyển đổi).
 

Ngoài hàm chuyển đổi số sang chữ, vnTools còn cung cấp hàm

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

Công cụ có phần hướng dẫn khá chi tiết về cách sử dụng các hàm.



2. Các hàm chuỗi, hàm đếm, hàm mảng để đếm tên NV:

  • Đếm số người có tên bắt đầu bằng chữ cái


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

  • Hàm văn bản – Tách tên họ ra 2 cột khác nhau
  • Đếm số ký tự xác định ở bất cứ vị trí nào của chuỗi có mặt trong một mảng


=SUM(LEN(A1:B1)-LEN(SUBSTITUTE(A1:B1,”c”,”")))
Ctr Sh Enter
Thông thường những hàm sum mảng hay Sum(If()), phải Ctr Sh Enter,

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

thuc tap ke toan hcm 18

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 khi có DS các ngày nghỉ phép,
  •  nghỉ lễ, ngày làm 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 một 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
  •  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 khi nhập 1 chuỗi số có dạng ddmmyy
  •  Tính ngày tháng tăng lương để xác định NV nào sắp đến hạn nâng lương ?


4. Sử dụng các công cụ nâng cao 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

DSC03806


6. Các văn bản, biểu mẫu 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à các quy định trả lương, phân phối lương
- Tham khảo về lương và các quy định tính toán trả lương cho KVSX
- Dự thảo quy chế tạm thời về phân phối trả lương của Cty CP phát triển 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

- Các 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 mẫu đã 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    
( Nguồn : Kê toán Già Gân ) 

 

Group 1 copy 6

 NẾU BẠN THẤY HAY HÃY SHARE CHO BẠN BÈ NHÉ

 

CẢM NHẬN CỦA HỌC VIÊN KHI THAM GIA KHÓA HỌC

phan-huynh-hieu-thao

 

 

Vui lòng liên hệ và đăng ký ghi danh:

 1. Hotline: (028) 38 336 446

 2. Tel: 0962 283 630 

 3. Yahoo chat online: hocketoanthuchanh

 4. Email:    hocketoanthuchanh@yahoo.com

 5. Skype: giasuketoantruong

 

 * ĐỊA CHỈ GHI DANH VÀ HỌC TẠI QUẬN 1:

17 Tôn Thất Tùng, Phường Phạm Ngũ Lão, quận 1, Hồ Chí Minh

* ĐỊA CHỈ GHI DANH VÀ HỌC TẠI QUẬN TÂN BÌNH (GẦN CÔNG VIÊN GIA ĐỊNH VÀ SÂN BAY TÂN SƠN NHẤT):

123 Bạch Đằng, Phường 2, Quận Tân Bình, TPHCM

 

 

Thêm bình luận


Comments 

 
avatar
#2 trần ánh tuyết 2017-07-22 07:32
add gửi cho e các hàm này với ạ! afctuyetkt@gmai l.com
Quote
 
 
avatar
#1 hà vân 2017-04-14 11:31
cho mình xin file nha!!!
Quote