Hướng Dẫn Cách Tính Lương Theo Giờ Trong Excel Và Lương Làm Thêm Giờ

It-Pro Training

Mô tả yêu cầu

Chúng ta sẽ bắt đầu với việc giả sử ta có một bản ghi chép trên Word về thời gian làm việc và mức lương tương ứng của từng người trong công ty như sau:

Và yêu cầu bài toán trình bày trên Excel:Copy nội dung sang Excel và thực hiện tách cột Họ và tên thành hai cột Họ và tên đệm, Tên, thao tác tách phải được xây dựng bằng công thứcTính toán tiền lương cho từng người = Tiền công theo giờ x Số giờ làm việcCác cá nhân có số giờ làm vượt quá 40 giờ, thì phần dư được tính là giờ làm thêm với mức Tiền công theo giờ x 1,5 lầnThêm cột Thuế tính toán thuế cá nhân phải trừ cho từng cá nhân: lập công thức tính với dữ liệu tỷ lệ thuế được nhập ở một vị trí cố định trên bảng (worksheet)Thêm cột Tiền thực nhận để tính tiền của từng người sẽ nhận được bằng cách lấy Tiền lương trừ thuếSắp xếp theo thứ tự tăng dần theo Tên riêngThêm cột Số thứ tự vào đầu tiên sử dụng chức năng Fill để điền chỉ số thứ tự.Bổ xung dòng ở cuối để tính Tổng số tiền cho 3 cột Tiền lương, Thuế, Tiền thực nhậnĐịnh dạng bảng với một số tiêu chí sau:Thêm tiêu đề “Bảng lương theo tuần”Chỉ hiển thị cột: Số thứ tự, Họ và tên đệm, Tên, Tiền công theo giờ, Số giờ làm việc, Lương, Thuế, Tiền thực nhậnCác nội dung chữ canh lề bên trái, số định dạng theo Tiếng Việt làm tròn không có lẻ thập phân, canh lề bên phảiTất cả các giá trị số bằng 0 thể hiện bằng dấu gạch ngang ( – )Định dạng đường viền kẻ ô cho bảngChúng ta bắt đầu thực hiện theo các yêu cầu

Sao chép dữ liệu vào Excel và thực hiện tách Họ và Tên

Các bạn chọn vùng dữ liệu trên Word chứa thông tin và chọn copy

Mở tạo mới một workbook excel đặt vị trí cell tại ô đầu tiên A1Dán nội dung vừa sao chép, Excel sẽ tự động sao chép dữ liệu vào các ô tương ứng trên bảng

Như các bạn thấy ở đây có vấn đề nảy sinh và chúng ta cần giải phápCác con số có phân tách hàng ngàn bị mất dữ liệu (vd 90.000 chỉ còn 90)Trường hợp này xảy ra do nếu Excel của bạn cài đặt sử dụng định dạng theo tiếng ViệtNội dung tự động format theo Word (sẽ bị mất công để ta chỉnh lại về format mong muốn hơn việc nội dung chưa format và tạo format từ đầu)Trường hợp này có thể Paste nội dung ở trường hợp đặc biệt là không Paste kèm format (unformat)Vậy chúng ta sẽ thực hiện lại bước Paste trên với các thao tác khác đi chút, ta bắt đầu lại từ việc tạo mới một workbook

Cấu hình định dạng lại theo tiếng Việt

Mở menu File -> Options để xuất hiện cửa sổ Excel Options. Tại đây chọn mục Advanced và thay đổi ký tự phân tách lẻ thập phân (Decimal separator), phân tách hàng ngàn (Thousands speparator) theo dạng tiếng Việt

Chú ý: Trong Excel mặc định định dạng theo dạng tiếng Anh, ký tự dấu phẩy ( , ) được dùng để phân tách hàng ngàn, dấu chấm ( . ) dùng để phân tách số thập phân, khi đó các công thức tính toán có các hàm với nhiều tham số thì các tham số sẽ được phân tách nhau bởi dấu phẩy ( , ).Tuy nhiên khi chúng đa chuyển đổi sang dạng tiếng Việt thì khi đó dấu phẩy ( , ) sẽ không được dùng để phân tách các tham số nữa (vì nó được dùng để phân tách số thập phân), khi đó các tham số sẽ phân tách nhau bởi dấu chấm phẩyVí dụ: hàm if nhận 3 tham số phân tách nhau bởi 2 ký tự (tách), ta có cống thức đánh giá điểm đạt hay không đạt khi so sánh với giá trị 5:Theo format tiếng Anh:if(A7 > 5 , “Đạt” , “Không đạt”)Theo format tiếng Việt:if(A7 > 5 ; “Đạt” ; “Không đạt”)Giữa các thành phần chúng ta có thể viết liền nhau không ảnh hưởng đến kết quả, ở đây ta viết cách nhau bởi một khoảng trắng cho dễ nhìn – so sánh

Dán nội dung đặc biệt (Paste Special) – không chứa định dạng (unformat)

Ghi chú: Cách thức paste như hình vẽ trên là chức năng chọn paste special nhanh, còn bình thường ta có thể chọn mục Paste Special … ở ngay dưới. Tùy theo ngữ cảnh hiện tại nội dung đang lưu trữ trong clipboard (bộ nhớ đệm sao chép, cắt) mà các tùy chọn có thể khác nhau. Ở tình huống này ta có thể thấy các tùy chọn như hình sau

Chèn thêm cột vào giữa nội dung

Để chèn thêm cột vào giữa các cột đã có sẵn ta có thể thực hiện như sau:Dùng chuột chọn toàn bộ hai cột dự kiến sẽ được chèn vào bằng cách click giữ rê chuột trên hai cột (trường hợp của chúng ta là cột B và C)

Tại vùng được chọn, nhấn phải chuột để hiển thị menu ngữ cảnh. Tại đây bạn chọn InsertVới hai cột mới ta thêm tiêu đề vào cho chúng “Họ và tên đệm”, “Tên”Ghi chú:Các bạn có thể tăng độ rộng các cột bằng cách đặt con trỏ chuột tại vùng tiếp giáp hai cột, con trỏ sẽ biến hình thành mũi tên hai chiều (trái – phải), khi đó ta có thể nhấn rê để thay đổi độ rộng cột.Trường hợp cùng lúc muốn thay đổi độ rộng cùng lúc nhiều cột (các cột cùng chiều rộng) thì ta chọn các cột đó và thao tác thực hiện thay đổi như mô tả trên – các cột này sẽ có cùng chiều rộng.

Đang xem: Cách tính lương theo giờ trong excel

Lập công thức tách “Họ và tên đệm”, “Tên” từ cột “Họ và tên”

Phân tích yêu cầu: Họ và tên thông thường được cấu tạo bởi từ ít nhất 2 cụm từ, Họ và tên đệm là các từ đặt trước Tên, chúng được phân tách bởi khoảng trắng, vậy để có Họ và tên đệm chúng ta cần cắt nội dung từ đầu đến khoảng trắng cuồi cùng.Trong Excel, với bài toán này ta có thể sử dụng các hàm thao tác chuỗi sau:

Như phân tích ở trên các bạn thấy điểm quan trọng trong công thức tách này chính là xác định vị trí khoảng trắng cuối cùng (phân tách giữa Họ và tên đệm với Tên)Áp dụng các hàm trên ta có công thức xác định vị trí đó như sau (giả sử ta đang ở cell B2):FIND(“#”;SUBSTITUTE(A2;” “;”#”;LEN(A2)-LEN(SUBSTITUTE(A2;” “;””))))-1Kết hợp cùng hàm LEFT cho phép ta lấy được Họ và tên đệmCông thức để lấy Họ và Tên đệm được mô tả ở hình sau:

Áp dụng cách thức lấy vị trí khoảng trắng cuối, ta lấy tên bằng hàm RIGHT

Ta lần lượt đưa hai công thức vào hai ô B2 và C2Ghi chú: Trong Excel để tạo một công thức thì ký tự đầu tiên sẽ luôn là dấu =. Để nhập công thức vào ta có hai cách:1 – Gõ trực tiếp tại ô hoặc nhấn F2 để chỉnh sửa ô đó,2 – Thao tác trên hộp nhập công thức phía trên worksheet

Ở ví dụ này ta sẽ có công thức đầy đủ choB2: =LEFT(A2;FIND(“#”;SUBSTITUTE(A2;” “;”#”;LEN(A2)-LEN(SUBSTITUTE(A2;” “;””))))-1)C2: =RIGHT(A2; LEN(A2) – FIND(“#”;SUBSTITUTE(A2;” “;”#”;LEN(A2)-LEN(SUBSTITUTE(A2;” “;””)))))

Sử dụng AutoFill để sao chép, điền dữ liệu thông minh

Để thực hiện ta tạo vùng chọn B2:C2 bằng cách đặt ô được chọn là B2 rồi dùng chuột kéo rê mở rộng vùng chọn sang C2.Ghi chú: Việc sử dụng nhấn tổ hợp phím Shift cùng các một trong các phím di chuyển 4 chiều trên bàn phím cũng có tác dụng tương tựSau đó di chuột tới vị trí góc dưới cùng bên tay phải con trỏ chuột sẽ chuyển sang dạng chữ thập +. Khi đó nhấn giữ phím chuột và kéo rê theo chiều dọc để mở rộng vùng chọn đến dòng dữ liệu cuối cùng.

Xem thêm: Cách Tính Thuế Đăng Ký Xe Ô Tô, Cách Tính Thuế Ô Tô Nhập Khẩu 2021

Lập công thức tính toán tiền lương

Công thức tính lương được xây dựng bằng cách nhân giá trị cột D với cột EVí dụ tại F2: = D2 * E2Ở đây có một điều kiện ta cần xét khi tính toán đó là Tiền công cho 1 giờ sẽ nhân 1,5 lần cho giờ làm thêm (phần dư vượt quá 40 giờ làm việc)Trong Excel ta có thể sử dụng hàm If để lập công thức trong trường hợp nàyVậy công thức tại F2 sẽ được điều chỉnh lại là:F2: =IF( E2 > 40 ; 40 * D2 + (E2 – 40) * D2 * 1,5; E2 * D2)Thực hiện AutoFill tương tự trước cho phép ta điền công thức vào các dòng F kế tiếpVà đây là kết quả sau khi thực hiện các bước trên

Tính tiền Thuế với tỷ lệ thuế lấy từ một vị trí cố định (địa chỉ tuyệt đối)

Ta sẽ đặt chỗ nhật tỷ lệ tính thuế ở vị trí cố định, và để dễ cập nhật ta sẽ để ở dòng đầu tiênThực hiện chèn một dòng vào trước nội dung đã có:Thao tác này tương tự như khi bạn chèn cột ở trên:Chọn dòng đầu tiênNhấn phải vùng chọn và chọn InsertNhập tiêu đề cho ô chứa tỷ lệ thuế ở cell I1, nhập giá trị tính (vd: 0,1) vào J1.Để thể hiện giá trị theo tỷ lệ phần trăm ta có thể chọn format cho J1 bằng cách click nút % trên thanh công cụ Ribbon.

Gõ nội dung tiêu đề cột Thuế ở G2Công thức sử dụng phép tính Tiền lương với tỷ lệ thuế, vì giá trị tỷ lệ thuế ở cố định nên để thuận tiện cho việc sử dụng chức năng sao chép công thức ta cần lập công thức với tham chiếu J1 ở dạng tuyệt đối là $J$1Công thức ở G3 : = F3 * $J$1Tiếp tục fill công thức xuống các dòng kế tiếp

Tính Tiền thực nhận và tổng các loại tiền

Công thức tính tiền thực nhận ở cột H3: = F3 – G3Tiếp tục thực hiện fill công thức cho các dòng kế tiếp.Trong Excel có một số hàm cung cấp cho ta tính toán tổng hợp dữ liệu như tính tổng (sum), trung bình (sum), lấy giá trị tối đa (max), tối thiểu (min) mà tham số là tham chiếu tới khoảng chọn.Ở đây sẽ nhập công thức tính tổng cho các cột Tiền lương, Thuế, Tiền thực nhận như sau:Chọn vùng chứa dữ liệu Tiền lương, và click nút xích ma ∑ trên thanh công cụ ribbon.

Xem thêm: Đồ Án Chuẩn Bị Sản Xuất Ngành May, Bộ Gd&Đt Trường Đại Học Spkt Tp

Ngay sau đó, Excel đã tự động tạo một công thức tính tổng cho các ô dữ liệu được chọn ở ngay ô kế tiếp trong cộtGhi chú: Với thao tác này trên phiên bản Excel 2013, ta sẽ còn nhận được một kết quả ngạc nhiên một chút là Excel cũng sẽ tự động bổ xung hàm tính sum cho các cột còn lại của chúng ta.

Sắp xếp kết quả theo Tên

Để sắp xếp kết quả ta chọn toàn bộ bảng dữ liệu (không chọn dòng cuối chứa công thức tính tổng)Sau đó mở mũi tên nhỏ biểu tượng Sort có chữ A – Z trên thanh công cụ ribbon, chọn Custom Sort.Tại cửa sổ này giữ có chọn “My data has headers” để báo có tiêu đề trong vùng chọn,Trong hộp chọn Sort by mở ra và chọn Tên rồi click OK để xác nhận

Thêm cột thứ tự và sử dụng AutoFill để điền số thự tự

Chọn cột A và nhấn phải tại vùng chọn để chọn lệnh Insert – thêm cột mớiThêm tiêu đề cho cột – Thứ tựTại ô A3 gõ nội dung thứ tự là 1Chọn toàn các dòng trong cột A cần tiếp tục điền số thứ tự – kể từ vị trí A3, click biểu tượng nút Fill trên công cụ ribbon, ở menu xổ xuống chọn Series…Cửa sổ Series xuất hiện với các tùy chọn cho phép tiếp tục điền số tuyến tính (Linear) theo cột với bước tăng (Step value) là 1, ta giữ nguyên các tùy chọn này click OK

Định dạng tiêu đề bảng, sử dụng chức năng merge để mở rộng ô sang các ô lân cận

Tại vị trí A1 ta gõ tiêu đề của bảng “Bảng lương theo tuần”,Để mở rộng hiển thị tiêu đề A1 sang các ô kế tiếp B1, C1, .. , I1 ta mở rộng vùng chọn từ A1 tới I1 sau đó click nút “Merge & Center” trên thanh công cụ ribbonGhi chú: Excel sẽ cảnh báo ta nếu việc merge gây ra mất dữ liệu bởi khi các ô bị merge bởi ô khác thì nội dung trong đó sẽ bị xóa bỏ

Định dạng tiêu đề và dòng tổng cộng

Tiêu đề bảng, tiêu đề của cột cần được định dạng để phân biệt rõ ràng hơn với các nội dung khác.Thao tác định dạng rất đơn giản bằng cách ta chọn vùng cần định dạng và chọn các nút thay đổi định dạng kích thước font (font size), in đậm (bold), nghiêng (italic), hay canh lềDòng tổng cộng ta cũng thực hiện đặt tiêu đề và merge nội dung từ cột A tới FĐịnh dạng in đậm cho các kết quả tính tổng

Định dạng số

Chọn vùng cần định dạng và sử dụng các nút chức năng format số trên thanh công cụ ribbon như hình sau:

Ẩn cột “Họ và tên” và tạo đường kẻ viền cho bảng

Để ẩn cột đi ta chọn toàn bộ cột Họ và Tên (cột B), rồi nhấn phải chuột trên vùng chọn click chọn Hide (để hiển thị lại thì bạn cần chọn các cột bao xung quanh cột bị ẩn rồi nhấn phải, chọn UnHide)Thao tác tạo được kẻ cho các ô trong bảng:Chọn toàn bộ vùng cần kẻ ôClick mũi tên nhỏ ở biểu tượng đường viền (border)Chọn All Borders ở menu xổ xuống

Để thuận tiện xem trên màn hình hay in ấn ta cần thu nhỏ các cột lại cho vừa đủ, khi đó ta có thể gặp một vấn đề là tiêu đề dài sẽ bị mất chữ.Giải pháp ở đây là ta sử dụng chức năng tự động ngắt dòng cho tiêu đề (Wrap Text)Và đây là kết quả cuối cùng