Chuyện nghề HCNS: Đợt này bên công ty mình có 1 em thực tập về chuyên đề kế toán tiền lương do mình phụ trách. Hôm nay em ý hỏi mình 1 câu như sau: Ở dữ liệu máy chấm công em chỉ thấy “Giờ vào làm” và “Giờ kết thúc”, vậy làm sao để tính được người ta làm bao nhiêu giờ trong ngày trên excel? Ví dụ: A = Giờ vào = 07:56 [ 7 giờ, 56 phút] , định dạng excel là dạng ngày tháng hh:mm B = Giờ ra = 17:08 [17 giờ, 08 phút], định dạng excel là dạng ngày tháng hh:mm C = B – A , định dạng excel là dạng Number Ban đầu mình nghĩ bụng: “Thì lấy B – A, sau đó Format cells và chọn định dạng Number chứ sao” Thế nhưng khi làm thử thì không ra kết quả mong muốn [kết quả ra 0.38 chứ lị, đang lý phải là 9.2 mới đúng] Chết rồi, bị đàn em hỏi câu khó quá, biết làm sao giờ? HEO ơi giúp mình với ————- Với câu hỏi của bạn thì HEO Blog học excel online xin trả lời bạn như sau: Cách 1: Khá đơn giản: Lấy [B-A] * 24 là xong Cách 2: Dùng các hàm Hour , Minute để luyện thêm về hàm xử lý giờ, phút trong excel: [làm khó em thực tập chơi, ai bảo nó hỏi khó] Bước 1: Bạn phải quy đổi số giờ của B ra bao nhiêu phút , của A ra bao nhiêu phút
Tổng số phút B = Hour[B]*60 + Minute[B] Tổng số phút A = Hour[A]*60 + Minute[A]
Bước 2: Bạn lấy tổng số phút B – Tổng số phút A Tổng số phút C =[Hour[B]*60 + Minute[B]] – [Hour[A]*60 + Minute[A]] Xem thêm: Tổng hợp bộ tài liệu excel cơ bản Bước 3: Bạn quy đổi lại về số giờ bằng cách: Tổng số phút C / 60 = [[Hour[B]*60 + Minute[B]] – [Hour[A]*60 + Minute[A]]]/60 = 9.2
Hướng dẫn cách theo dõi đi làm muộn từ dữ liệu máy chấm công
Hướng dẫn cách theo dõi số ngày phép còn lại trong năm trên Excel
Hướng dẫn cách lập bảng chuyển đổi lương Gross sang Net trên Excel
Ngoài việc theo dõi chấm công, công việc của hành chính-nhân sự còn phải theo dõi nhiều nội dung khác như: Công văn giấy tờ, hợp đồng lao động, quản lý cấp phát đồ dùng văn phòng phẩm, thưởng phạt, đi muộn… Để nắm được những kiến thức và cách ứng dụng Excel trong công việc hành chính-nhân sự, mời bạn tham gia khóa học Hành chính nhân sự từ A-Z của Học Excel Online. Bật mí là bạn còn được tặng kèm phần mềm quản lý nhân sự khi tham gia khóa học này nhé. Chi tiết xem tại:
Tác giả: dtnguyen [Nguyễn Đức Thanh]
@ Học Excel Online | DTNguyen.business
Không phải lúc nào việc chấm công trên máy chấm công cũng đem lại hiệu quả ngay. Trong một số trường hợp chúng ta phải đưa dữ liệu từ máy chấm công sang Excel thì mới tính được số giờ công làm việc của nhân viên. Trong bài học này Học Excel Online sẽ hướng dẫn các bạn cách tính số giờ làm việc trên bảng chấm công Excel với dữ liệu được trích xuất từ máy chấm công. Nội dung cụ thể như sau: Chúng ta có bảng dữ liệu trích xuất từ máy chấm công tháng 07 năm 2017 gồm:
- Cách làm
- Cách tối ưu
Cách làm
Bước 1: Khảo sát, đánh giá và phân tích dữ liệu nguồn [bảng dữ liệu kết xuất từ máy chấm công ra excel] Đây là bước rất quan trọng, Việc phân tích bảng dữ liệu nguồn sẽ giúp chúng ta có phương án xử lý phù hợp và chính xác. Để thực hiện công việc này, chúng ta cần nắm được các nguyên tắc sau:
- Mục đích công việc: Tính công làm việc theo thời gian
- Đối tượng liên quan: Mã nhân viên, Ngày trong tháng, Giờ vào, Giờ ra
- Tiêu chí đánh giá [dựa trên mục đích, kết quả cần đạt được để đặt ra tiêu chí cho các đối tượng liên quan]
- Mã Nhân viên: Liên tục theo dòng, không được để dòng trống
- Ngày trong tháng: Bao gồm xác định ngày trong tháng, thứ trong tuần, phân biệt ngày nghỉ lễ, nghỉ bù
- Giờ vào, giờ ra: Các ngày làm việc đều phải có giờ vào, giờ ra. Khi tính công sẽ dựa trên việc đánh giá giờ vào, giờ ra để tính ra các loại công.
Ta có thể thấy, để tính toán được, cần phải điều chỉnh lại cấu trúc bảng dữ liệu sao cho đúng các tiêu chí. Việc tính toán trên nhiều điều kiện, nên hàm sẽ sử dụng để tính toán là SUMIFS, COUNTIFS và hàm IF Bước 2: Cấu trúc lại bảng dữ liệu Để rút gọn bài viết, mình sẽ không nêu từng thao tác, mà sẽ đưa hình mẫu và gợi ý các cách làm để các bạn có thể tự làm được: [Bảng chấm công này có cấu trúc mỗi ngày gồm 2 cột, khác bài trước là mỗi ngày 1 cột]
- Phần Tháng và Năm [dòng 2, 3]: Đặt bên ngoài để tiện việc thay đổi
- Ngày trong tháng [dòng 4]: Dùng hàm Date, bắt đầu với ngày 26 tháng trước đó [vì bảng nguyên liệu kết xuất tính từ ngày 26 nên ta sẽ giữ nguyên yêu cầu này. Tính công từ ngày 26 tháng trước đến ngày 25 tháng sau]. Riêng 2 ngày cuối có thể sang tháng tiếp theo [trường hợp tháng có 28-29 ngày] nên có thể xử lý để bỏ trống nếu sang tháng sau, tức là ngày 27 tháng này trở đi là đã bước sang tháng sau của bảng tính công.
- Phần Mã NV [cột A, B, C]: Mỗi mã 1 dòng, để tập hợp theo mã NV chính xác hơn.
- Thứ trong tuần [dòng 5]: Dùng hàm Choose kết hợp với hàm Weekday để xác định. Kết hợp tính năng Conditional Formatting để tô màu phân biệt ngày CN với ngày thường.
- Giờ vào, giờ ra [dòng 6]: có thể quy ước số 1 là giờ vào, số 2 là giờ ra.
- Lưu ý: 1 ngày có 2 giờ, nên 2 cột sẽ tính cho 1 ngày. Vì vậy khi xử lý ngày ở dòng 4 cần chú ý nội dung này.
Tất cả những nội dung trên, mình đã hướng dẫn chi tiết trong bài: Tạo bảng chấm công trên excel. Vui lòng xem lại bài này để được hướng dẫn chi tiết. Bước 3: Lọc và lấy dữ liệu Tại bảng kết xuất, dùng chức năng Data/Filter để lọc bỏ dòng trống trong cột Mã NV, sau đó copy dữ liệu sang bảng xử lý. Sử dụng chức năng Paste Special/Value để dán dữ liệu bỏ qua dòng bị ẩn.
BN7 =ROUND[[[HOUR[SUMIFS[$D7:$BM7,$D$4:$BM$4,BN$4,$D$6:$BM$6,2]]*60+MINUTE[SUMIFS[$D7:$BM7,$D$4:$BM$4,BN$4,$D$6:$BM$6,2]]]-[HOUR[SUMIFS[$D7:$BM7,$D$4:$BM$4,BN$4,$D$6:$BM$6,1]]*60+MINUTE[SUMIFS[$D7:$BM7,$D$4:$BM$4,BN$4,$D$6:$BM$6,1]]]]/60,2]
Bạn có thể filldown, fillright công thức từ BN7 cho các ô khác. * Lưu ý: Có thể có số âm. Số âm là trường hợp Chỉ có giờ vào mà không có giờ ra => Lý do: Quên chấm công. Việc này thường xảy ra Bước 5: Xác định kết quả chấm công Ví dụ chúng ta cần 1 số kết quả chấm công như sau:
- Ngày công đủ 8h tính dựa trên vùng đã xác định cụ thể số giờ công, với số giờ làm >=8 [mục 2.4]
CS7 = COUNTIF[BN7:CR7,”>=”&8]
- Quên chấm công tính dựa trên vùng đã xác định số giờ công, với số giờ ”&”8:05″,$D7:$BM7,” CX7 = COUNTIFS[$D7:$BM7,”>”&”8:10″,$D7:$BM7,” CY7 = COUNTIFS[$D7:$BM7,”>”&”8:15″,$D$6:$BM$6,1]
Dù đã có phần mềm, nhưng kỹ năng Excel vẫn cực kỳ quan trọng với kế toán, bạn đã vững Excel chưa? Hãy để tôi giúp bạn, đăng ký khoá học Excel:
=> Bạn có thể filldown công thức xuống cho các mã NV tiếp theo Kết quả của mục 2.5 là kết quả cuối cùng, có thể sử dụng để tính lương cho NV
Cách tối ưu
Mình sẽ gợi ý một vài kỹ thuật giúp tối ưu hoá bảng xử lý: [Ưu tiên cho những bạn biết sử dụng VBA, nếu không biết VBA thì làm thủ công bằng tay]
- Sử dụng VBA để lưu thông tin xử lý và kết quả tính được sang 1 Sheet riêng để lưu trữ kết quả. Bảng xử lý có thể làm tiếp cho các tháng sau mà không ảnh hưởng tới kết quả tháng trước.
- Sử dụng VBA ẩn/hiện các vùng: Dữ liệu chấm công theo giờ vào-ra [cột D đến cột BM], Dữ liệu xử lý tính giờ làm việc [Cột BN đến cột CR] để làm gọn bảng xử lý. Khi cần làm việc với bảng nào thì ta mở ẩn vùng bảng đó ra. Như vậy sẽ gọn mắt hơn. Vì mục tiêu cuối cùng là kết quả tính công mà thôi.
Công việc chấm công, tính lương khá phức tạp và đa dạng. Để tìm hiểu thêm nhiều kiến thức liên quan tới công việc này, Học Excel Online xin giới thiệu với các bạn khóa học “Ứng dụng Excel VBA trong công việc Tiền lương – nhân sự”. Học xong khóa học này bạn có thể nắm vững được quy trình chấm công, tính lương trong doanh nghiệp, tự tay xây dựng 1 phần mềm quản lý lao động, chấm công tính lương trên Excel. Chi tiết xem tại: