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 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à
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..
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
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
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.
Weekday(******_number, return_type) . ******_number là Date(2004,10,0).
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
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)
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,
– Đế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:
trong khoảng thời gian bất kỳ
=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)
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
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:
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
o Dùng hàm MONTH để xác định tháng trong chuỗi ngày tháng rồi dùng VLOOKUP
o Dùng hàm YEAR để phân định năm trong chuỗi ngày tháng rồi dùng VLOOKUP
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
khi chạy sẽ cài tập tin chúng tôi 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
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).
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,
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.
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
– 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ũ) – QT thuế TNCN năm 2007 – 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
( Nguồn : Kê toán Già Gân )
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
* Vui lòng liên hệ và đăng ký ghi danh:
– Hotline: 0918 867 446
– Email: giasuketoantruong@yahoo.com