Sử dụng Excel 2013 tính lương nhân công theo giờ theo từng bước

Sử dụng Excel 2013 tính lương nhân công theo giờ theo từng bước
access_time 10/30/2015 12:24:55 AM
person Đào Minh Giang

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:
Họ và tên Tiền công theo giờ Số giờ làm việc
Trần Quang Thái 90.000 38
Nguyễn Văn Bạch 105.000 40,5
Lê Đoàn Bảo Trinh 90.000 40
Huỳnh Thị Ngọc 115.000 40
Nguyễn Thành Chính 240.000 26,5
Nguyễn Thiện Khánh 160.000 42
Hoàng Đình Thắng 105 0
Trần Thị Mỹ Dung 105.000 32
Trịnh Bá Mạnh 150.000 39
Lê Thị Như Trâm 90.000 40
 
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ức
  • Tính toán tiền lương cho từng người =  Tiền công theo giờ x Số giờ làm việc
  • Cá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ần
  • Thê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êng
  • Thê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ận
    • Cá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ải
    • Tấ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ảng
Chú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 A1
Dá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áp
  • Cá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ệt
  • Nộ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ẩy
Ví 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 Insert
Vớ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.

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:
Tên hàm Mô tả Ví dụ Kết quả
len Đếm số ký tự chuỗi len(A1)
A1 chứa “Họ và tên”
9
left Cắt chuỗi từ trái left(A1 ; 4) Họ v
right Cắt chuỗi từ phải right(A1 ; 3) tên
mid Cắt chuỗi từ giữa mid(A1 ; 4,2)
find Tìm chuỗi trong chuỗi find(“v” ; A1)
hoặc kèm vị trí bắt đầu tìm
find(“ “ ; A1, 4)
4
 
6
substitute Thay thế chuỗi tìm thấy bằng chuỗi mới, có thể chọn chỉ thay thế tại chuỗi thứ mấy substitute(A4; “ “; “*”, 2)
A4 chứa “Lê Đoàn Bảo Trinh”
Lê Đoàn*Bảo Trinh
 
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;" ";""))))-1
Kết hợp cùng hàm LEFT cho phép ta lấy được Họ và tên đệm
Cô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à C2
Ghi 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 đủ cho
B2: =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.

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 E
Ví 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ày
Vậ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ếp
Và đâ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ên
Thự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ên
  • Nhấn phải vùng chọn và chọn Insert
  • Nhậ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ế ở G2
Cô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$1
Công thức ở G3 : = F3 * $J$1
Tiế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 - G3
Tiế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.

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ột
Ghi 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ới
Thêm tiêu đề cho cột – Thứ tự
Tại ô A3 gõ nội dung thứ tự là 1
Chọ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ụ ribbon
Ghi 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

 
vertical_align_top
share
Chat...