PDA

View Full Version : Cách tạo bảng chấm công trong Excel



skyonline
20-01-07, 09:50 AM
PCWorld VN 7/99

Nếu không hiểu rõ cách tạo ngày giờ trong Excel, cố gắng dùng chúng theo trực giác cũng sẽ giống như chỉnh đồng hồ trên một VCR. Bài kỳ này mô tả cách tạo một bảng chấm công hữu dụng và giải thích rõ hơn về việc cộng trừ giờ giấc trong một bảng tính.

Hình dưới đây là bảng chấm công hoàn chỉnh: một bảng tính tính toán tổng số giờ làm việc mỗi ngày, mỗi tuần của nhân viên làm trong giờ hành chính cũng như ngoài giờ.

Bắt đầu bằng việc nhập tiêu đề bảng. Từ dòng 2 đến dòng 8 chỉ có chữ mà thôi (không có công thức). Dĩ nhiên, bạn có thể định dạng chữ tùy thích. Nhập nội dung cho hộp "Tổng số giờ làm việc trong tuần".
http://www.echip.com.vn/echiproot/weblh/suutam/2000/pcworld/799/excel/1.gif

Bây giờ, chuẩn bị nhập công thức. Vào ô B9 và nhập =D5. Công thức này sẽ lấy ngày bắt đầu trong tuần đã được nhập vào ô D5. Vào ô B10, nhập =B9+1 rồi sao chép công thức này vào vùng B11:B15. Kế tiếp, nhập thứ trong tuần vào ô C9:C15, nếu bảng chấm công được làm bằng tiếng Anh, nhập =B9 vào ô C9 rồi chép công thức này vào vùng C10:C15. Bạn cần định dạng ngày trong cột C để hiển thị ngày trong tuần (Mon, Tue, Wed...). Muốn thế, chọn ô C9:C15 và chọn Format.Cells. Vào khung hội thoại Format Cells, nhấn mục Number. Chọn Custom rồi gõ dddd trong khung Type. Nhấn OK để đóng khung hội thoại này.

Trước khi nhập bất kỳ công thức nào thực hiện tính toán thực tế, bạn nên kiểm tra vài giá trị thời gian mẫu trong vùng D9:G15. Nếu dùng AM hay PM, nhớ chừa một khoảng trắng trước đó.

Kế tiếp, vào ô H9 rồi nhập công thức =IF(E9<D9,E9+1-D9,E9-D9)+IF(G9<F9,G9+1-G9,G9-F9)

Giải thích

Phần đầu của công thức tính tổng số giờ làm việc trong buổi sáng bằng cách lấy giờ trong cột E trừ đi giờ trong cột D. Phần thứ hai tính tổng số giờ làm việc trong buổi chiều bằng cách lấy giờ trong cột G trừ đi giờ trong cột F. Các hàm IF áp dụng cho trường hợp làm ca ba đến nửa đêm, ví dụ, một công nhân có thể bắt đầu làm việc lúc 10 giờ đêm và "ăn trưa" lúc 2 giờ sáng. Không có lệnh IF, kết quả công thức sẽ bị âm.

Chép công thức trong ô H9 vào H10:H15. Bây giờ bạn cần định dạng những ô này để hiển thị giờ giấc. Đánh dấu vùng H9:H15, chọn Format.Cells rồi nhấn mục Number. Chọn Time trong danh sách Category. Chọn tùy chọn đầu tiên trong danh sách Type rồi nhấn OK.

Bước kế tiếp là tính tổng số giờ làm việc trong tuần. Vào ô H18, nhập =SUM (H9:H15). Công thức này tính tổng số giờ làm việc trong cột H. Bây giờ, nhập vào ô H18 công thức =MIN(H18,1+TIME (40,0,0)). Công thức này giả định rằng một tuần có 40 giờ làm việc, kết quả công thức sẽ hoặc nhỏ hơn tổng số giờ đã làm việc hoặc nhỏ hơn 40 giờ. Công thức cuối cùng trong ô H20 là =H18-H19. Công thức này tính số giờ làm thêm bằng cách lấy tổng số giờ làm việc thực tế trừ đi số giờ qui định. Nếu công ty bạn qui định một tuần làm việc hơn 40 giờ, bạn phải đổi công thức trong ô H19.

Còn một bước quan trọng nữa: định dạng giờ trong vùng H18:H20. Vì những ô này có thể hiển thị giá trị giờ lớn hơn 24 nên bạn cần tùy biến định dạng giờ. Chọn vùng H18:H20, chọn Format.Cells rồi nhấn mục Number. Chọn Custom từ danh sách Category, nhập [h]:mm vào khung Type rồi nhấn OK. Dùng dấu ngoặc vuông cho phần giờ để bảng tính hiển thị được giá trị lớn hơn 24 giờ.

Nếu bạn cảm thấy công việc này quá khó khăn và không muốn tạo bảng chấm công cho riêng mình, bạn có thể tải bảng tính hoàn chỉnh từ địa chỉ www.fileworld.com/magazine (tìm tập tin TimeSheet.zip) rồi điều chỉnh thêm nếu cần.

Diễm Trang
PC World Mỹ 7/1999

Không biết post chỗ này có đúng box không?

Thien
20-01-07, 10:08 AM
Cái file này mình tìm không ra.
TimeSheet.zip

Bạn nào có up lên nhé.

Thân chào

Trần Văn Bình
22-01-07, 10:49 AM
Theo minh tinh luong thoi gian =IF(COUNTIF($D8:$AH8;"+")+COUNTIF($D8:$AH8;"-")/2=0;"";COUNTIF($D8:$AH8;"+")+COUNTIF($D8:$AH8;"-")/2)
Tuong tu nhu vay ban co the thy the di tinh duc tat ca cac cong #

hai2hai
22-01-07, 12:45 PM
Thế mỗi nhân viên phải nhập chi tiết thế này à? Hay đây là kết quả lọc ra từ phần mềm chấm công theo thẻ quẹt vậy?

Theo mình được biết ít ai làm chấm công chi tiết trên Excel vì quá tốn thời gian. Nếu chi tiết chỉ là tổng số giờ làm (các loại) theo từng ngày và trải dài trong tháng thôi. Còn nếu chấm công chi tiết đến từng giây thì chỉ áp dụng hoặc là chấm công thẻ, hoặc là chấm công bằng phần mềm (nhưng chi áp dụng với những người làm nghề văn phòng, mỗi người 1 máy tính, và hàng ngày cứ sáng đến check in, chiều về check out (thường là chạy 1 trang web timesheet)). Chứ làm gì có ai đi chấm đến từng phút cho mỗi con người mà ko dùng đến máy móc hoặc phần mềm.

lovely83
23-06-08, 09:10 AM
Skyonline cho mình hỏi với. Mình làm theo hướng dẫn của bạn nhưng đến ô H18, nhập = Sum (H9:H15) thì kết quả của mình lại cứ ra là 80:45 chứ không ra là 42:45. Như vậy là sao hả bạn?

luonchinguoc
23-06-08, 10:36 AM
Thế mỗi nhân viên phải nhập chi tiết thế này à? Hay đây là kết quả lọc ra từ phần mềm chấm công theo thẻ quẹt vậy?

Theo mình được biết ít ai làm chấm công chi tiết trên Excel vì quá tốn thời gian. Nếu chi tiết chỉ là tổng số giờ làm (các loại) theo từng ngày và trải dài trong tháng thôi. Còn nếu chấm công chi tiết đến từng giây thì chỉ áp dụng hoặc là chấm công thẻ, hoặc là chấm công bằng phần mềm (nhưng chi áp dụng với những người làm nghề văn phòng, mỗi người 1 máy tính, và hàng ngày cứ sáng đến check in, chiều về check out (thường là chạy 1 trang web timesheet)). Chứ làm gì có ai đi chấm đến từng phút cho mỗi con người mà ko dùng đến máy móc hoặc phần mềm.

Một số dự án chỗ mình họ chấm công từng người một, nhưng theo số giờ trong ngày thôi.

hai2hai
23-06-08, 11:35 AM
Một số dự án chỗ mình họ chấm công từng người một, nhưng theo số giờ trong ngày thôi.

Có lẽ bạn ko hiểu ý của tôi rồi.

Chấm theo phút (thậm chí là theo giây) vẫn có và rất nhiều nơi áp dụng. Nhưng vấn đề là chấm bằng cách nào và với số lượng là bao nhiêu người.

Ví dụ:
1 dự án có 10 người thì có 1 người đi "gõ đầu người" hàng ngày (sáng, chiều...) thì chuyện đó vẫn có thể làm được tới từng phút (thuê 1 người gác cửa ấy)

Nhưng có tới trên 100 người thôi thì chả có ai vào file excel ngồi gõ ai đi làm mấy giờ mấy phút, ai về mấy giờ mấy phút (nhớ là cho cả trăm người nhé). Chỉ có 2 giải pháp:
- Áp dụng web enabled application like timesheet (cứ bật máy tính là phải check in, về là phải check out)
- Áp dụng chấm công thẻ (ra vào quẹt thẻ)

hongtuyet2311
29-08-09, 09:00 AM
ban ơi, sao vùng D9:G15 mình dinh dang ma k duoc, ban vui lòng chỉ cụ thể duoc không? mình rất cần gấp mong các bạn giúp mình với nhé

thao_nguyen5070
02-02-10, 04:18 PM
cho mình xin hỏi : cách dấu hàm trong cell, và không cho sao chép dử liệu được tạo ra bằng cách nào. mà file tạo ra vẫn cho họ sử dụng được.

DuongLeAnh
20-05-10, 04:01 PM
Bác nào có file của Bảng chấm công bằng excel ko? update lên cho em với...

linhngoc
21-05-10, 09:47 AM
Uhm, mình có 1 bảng đây bạn xem thử nhé!

Delfy_Coltech
24-05-10, 03:14 PM
Chào mọi người,
Mình đang muốn xử lý bảng thời gian vào/ra cơ quan thu được bằng máy quẹt thẻ ở dạng file Excel.
Sau mỗi tháng, phần mềm sẽ xuất ra file excel thông tin vào/ra của nhân viên. (File Cham_cong_Excel.xlsx đính kèm)

Các cao thủ xin chỉ giáo! Làm thế nào để:

1. Tính tổng thời gian làm việc cho từng người trong tháng.
2. Khi thông tin tổng hợp được xuất vào Sheet 1 (Bảng tổng hợp chi tiết...) thì sẽ có các Sheet khác tự động lọc dữ liệu cho từng phòng ban?

Mình lập trình cái phần mềm bằng C#, đang vướng mắc khâu xử lý dữ liệu thu được.
Nếu ACE nào có phương kế giải quyết xin chia sẻ!

Cảm ơn mọi người nhiều!

Lê Duy Thương
24-05-10, 03:33 PM
bác nào có file của bảng chấm công bằng excel ko? Update lên cho em với...
Đây là bảng công mà công ty tôi vẫn áp dụng trong nhiều năm qua

Delfy_Coltech
25-05-10, 02:12 PM
Anh có thể giải thích rõ hơn được không?

Trong file của Anh có sheet "QUET THE". Vậy Anh tính tổng công cho nhân viên theo thời gian thu được ntn?

Lê Duy Thương
25-05-10, 02:32 PM
Anh có thể giải thích rõ hơn được không?

Trong file của Anh có sheet "QUET THE". Vậy Anh tính tổng công cho nhân viên theo thời gian thu được ntn?
thời gian làm viec =hc từ 7h30--->16h30
tăng ca nếu lớn hơn =30 phút thì tính tang ca x 1.5
chủ nhật tính số giờ x 200%
nếu CN vào lúc 7h30 và ra lúc 8h30 sau đó quay lại lúc 9h30 và làm việc đến 20h30 thì tính như sau
thời gian làm việc HC=16h30-7h30-(9h30-8h30)=7h nhân hệ số 1
tăng ca=20h30-16h30=4 h nhân hệ số 1.5
tất cả đều theo máy chấm công
trong trường hợp máy quẹt thẻ không nhận dạng được thì ngay lúc đó có giấy xác nhận của bảo vệ

Delfy_Coltech
25-05-10, 04:18 PM
thời gian làm viec =hc từ 7h30--->16h30
tăng ca nếu lớn hơn =30 phút thì tính tang ca x 1.5
chủ nhật tính số giờ x 200%
nếu CN vào lúc 7h30 và ra lúc 8h30 sau đó quay lại lúc 9h30 và làm việc đến 20h30 thì tính như sau
thời gian làm việc HC=16h30-7h30-(9h30-8h30)=7h nhân hệ số 1
tăng ca=20h30-16h30=4 h nhân hệ số 1.5
tất cả đều theo máy chấm công
trong trường hợp máy quẹt thẻ không nhận dạng được thì ngay lúc đó có giấy xác nhận của bảo vệ

--> Cái này là tuỳ vào luật chấm công của mỗi nơi thui Anh ah!
Ý Em hỏi là từ thời gian đi làm và ra về của nhân viên mà đầu đọc thẻ ghi lại theo ngày đó. Anh tính tổng theo tháng như thế nào ạ?

Hay là cái máy chấm công Anh dùng đã có phần mềm tính toán hết rồi?

Em đang mắc chỗ xử lý dữ liệu thời gian đi làm (ra về) của nhân viên. Phần mềm đã xuất được ra Excel, nhưng chỉ tính được thời gian làm việc trong ngày.
Bây giờ từ cái bảng tổng công chi tiết, làm sao trích lọc ra được tổng công cho từng người? từng phòng ban?

Mong mọi người chia sẻ!

Thanks alot!

Lê Duy Thương
25-05-10, 04:24 PM
--> Cái này là tuỳ vào luật chấm công của mỗi nơi thui Anh ah!
Ý Em hỏi là từ thời gian đi làm và ra về của nhân viên mà đầu đọc thẻ ghi lại theo ngày đó. Anh tính tổng theo tháng như thế nào ạ?

Hay là cái máy chấm công Anh dùng đã có phần mềm tính toán hết rồi?

Em đang mắc chỗ xử lý dữ liệu thời gian đi làm (ra về) của nhân viên. Phần mềm đã xuất được ra Excel, nhưng chỉ tính được thời gian làm việc trong ngày.
Bây giờ từ cái bảng tổng công chi tiết, làm sao trích lọc ra được tổng công cho từng người? từng phòng ban?

Mong mọi người chia sẻ!

Thanks alot!
bạn thử gửi file lên đi
xem mình có giúp gì được không

Delfy_Coltech
25-05-10, 05:18 PM
bạn thử gửi file lên đi
xem mình có giúp gì được không

File Em đã gửi ở #12 đó Anh! Anh xem hộ Em nhé!
Em thấy trong Excel có thể chèn thêm được các Macro (lập trình theo ngôn ngữ VB).
Có lẽ Em phải tìm hiểu cái này để giải quyết vấn đề.

Hi vọng được A Thưởng và các bạn giúp đỡ!

Delfy_Coltech
26-05-10, 12:21 PM
Anh xem lại giúp Em nhé! !$@!!

Lê Duy Thương
26-05-10, 01:52 PM
anh xem lại giúp em nhé! !$@!!
bạn hãy xem file nhé
CHÚ Ý CỘT THỜI GIAN CỦA BẠN MÀ KHÔNG NHÂN VỚI 1 THÌ ANH BILL GATE CŨNG KHÔNG SUM ĐƯỢC ĐÂU NHÉ

Delfy_Coltech
26-05-10, 02:09 PM
Cảm ơn Anh nhiều!
Dưng mà lọc theo phòng ban vẫn chưa đúng người. Ta thêm 1 cột Mã phòng ban vào cho nó dễ xử lý đc không A? (Thay cho cái cột phụ của A)
Anh xem lại hộ Em nhé.

Lê Duy Thương
26-05-10, 03:10 PM
cảm ơn anh nhiều!
Dưng mà lọc theo phòng ban vẫn chưa đúng người. Ta thêm 1 cột mã phòng ban vào cho nó dễ xử lý đc không a? (thay cho cái cột phụ của a)
anh xem lại hộ em nhé.
bạn hãy tự sắp xếp tên , mã số thẻ ,phòng ban cho chính xác bên sheet chi tiêt đi thì hãy nói là đúng hay chưa nhé

Delfy_Coltech
26-05-10, 03:24 PM
Hj, có trường hợp 1 người hiển thị ở hai dòng mà Anh!
Nhưng dù sao rất cảm ơn Anh đã giúp đỡ.
Em sẽ tìm hiểu thêm.

Have a nice day!

Tống Văn Đệ
26-05-10, 03:37 PM
Xin lõi vì có sửa sheet của bạn
Theo mình thì tổ chức dữ liệu logic thì file mới xử lý nhanh được
Mình làm thử cho bạn 1 trang In ( Mục đích là để in cho thống nhất )
Vào sheet InBangLuong
Chọn nhân viên trong C6
Bài chỉ sử dụng công thức
Nếu bạn biết VBA thì công việc của bạn sẽ nhanh hơn nhiều
Mong ràng có ích cho bạn!

Delfy_Coltech
26-05-10, 04:28 PM
Hj, cảm ơn mọi người!
Đúng là mỗi người có một cách làm riêng và đều rất hay!
Em sẽ học hỏi dần.

nguyenzhan
18-02-11, 08:49 PM
Bác nào có file của Bảng chấm công bằng excel ko? update lên cho em với...
Qua một thời gian thay đổi cuối cùng mình có một bảng tính giờ tăng ca như vầy nhờ các bạn xem hộ trợ thêm để được một bảng chấm công hoàn chỉnh nhé.
TKS!

nguyenhoangk41
30-05-11, 10:40 PM
cho mình hỏi chút, khi mình sum(H9:H15) nếu tổng số giờ >24h, ví dụ: 24:30 thì nó sẽ thành 0:30; 25:45 thì sẽ thành 1:30; nó chỉ hiện kết quả đúng khi mình cho nó hiện cả giây, nhưng mình ko muốn thế, vậy có cách nào khắc phục không vậy?

buithinhvan77
31-05-11, 08:10 AM
cho mình hỏi chút, khi mình sum(H9:H15) nếu tổng số giờ >24h, ví dụ: 24:30 thì nó sẽ thành 0:30; 25:45 thì sẽ thành 1:30; nó chỉ hiện kết quả đúng khi mình cho nó hiện cả giây, nhưng mình ko muốn thế, vậy có cách nào khắc phục không vậy?
Định dạng trong Format -> Cell ->Time -> Custom kiểu: [h]:mm là được mà!

bibi_09
26-09-12, 10:45 AM
Công ty em quy định làm 8h/ngày, 6ngày/tuần. Làm thêm ngoài giờ ngày thường x1.5, ngày chủ nhật x2, ngày lễ x3. có cách nào để em tính được cột giờ quy định và cột ngoài giờ. đồng thời bảng lương cũng cập nhật được công và công thức tính lương hiệu quả. Em up bảng chấm công và tính lương kèm nhờ nhà mình gỡ rối giúp em với. Em chân thành cảm ơn!

SA_DQ
27-09-12, 05:41 AM
Giúp em hoàn thiện cái bảng chấm công và bảng lương với các bác ơi! . . . cảm ơn!

Bạn thử thay số liệu & kiểm tiếp xem sao nha

(Mà hình như số liệu trang tính của bạn sao, sao í! hi,hi, . . . Trở ngại này mình chưa vượt qua được)